みらしん

読者の未来に、少しでも役立つネタを配信するブログ。

エクセルのOFFSET関数を知ってスマートに場合分けをしよう!

更新日:


エクセルのOFFSET関数のブログ_アイキャッチ画像

エクセルでスマートに場合分けをしたい方に、読んでほしいブログです。

エクセルで収支計画などを作るときに、売上が伸びた場合、伸びなかった場合など計画の場合分けをすることがあります。

そのときに、多くの人がケースごとにシートを分けたり、ファイルを分けていると思います。

場合分けの数が2〜3程度であれば、そのやり方でも良いと思います。

しかし、たくさんの場合分けが必要になってくると、シートやファイルをたくさん用意して、エクセルの管理がとても大変になってきます。

エクセルのOFFSET関数のブログ_画像1_4

一方で、今回紹介する OFFSET関数を使えば、1つのシートで無数の場合分けができるようになります!

例えば、売上成長率が100%、かつ、従業員数が10人ずつ増加する場合

エクセルのOFFSET関数のブログ_画像2_2

例えば、売上成長率が50%、かつ、従業員数が5人ずつ増加する場合

エクセルのOFFSET関数のブログ_画像3_2

今回のブログでは、このスマートに場合分けができるOFFSET関数の使い方を紹介し、 OFFSET関数を使った収支計画を作っていきたいと思います。

#もう少しシンプルなやり方がありましたので、近日記事を更新します。

スポンサーリンク

PC300×250




OFFSET関数とは?

そもそも、 OFFSET関数とはなんぞや?という感じですよね。

Microsoft Officeの公式サイトには、以下のように説明されています。

セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。

簡単に説明すると、指定したセルのデータを抽出することができる関数です。

まだよくわからないと思うので、次のOFFSET関数の使い方を見ながら、イメージを膨らませてもらえればと思います。

OFFSET関数の使い方

OFFSET関数の使い方は、参照結果を入力したいセルを選択し、「=OFFSET(起点となるセル, 起点から参照のセルまでに進む行の数, 起点から参照のセルまでに進む列の数)」を入力します。

エクセルのOFFSET関数のブログ_画像4

上の画像の場合、
・起点となるセル:B4
・起点から参照のセルまでに進む行の数:2
・起点から参照のセルまでに進む列の数:1
となり、C6にある「100」がC4に入力されます。

エクセルのOFFSET関数のブログ_画像5

もし、「起点から参照のセルまでに進む行の数」が「3」になったらどうなるか。

行の数が1つ進んで、C7にある「200」がC4に入力されます。

エクセルのOFFSET関数のブログ_画像6

エクセルのOFFSET関数のブログ_画像6_5

同じように、行の数が「4」であれば、C8にある「300」がC4に入力されます。

なんとなく、OFFSET関数の使い方はイメージはできたでしょうか?

それでは、このOFFSET関数をどのように収支計画で使うのか、実際に作りながら見ていきましょう。

OFFSET関数を使って収支計画を作ってみる

元となる収支計画はこちらです。
(青色の数字はベタ打ち、黒色の数字は計算式が入っています。「N/A」は数字が入っていないことを意味します。)

エクセルのOFFSET関数のブログ_画像7

OFFSET関数を使って、以下の場合分けができるようにします。

---------------
・売上成長率
パターン1:100% ※元の収支計画どおり
パターン2:50%
パターン3:10%

・従業員数
パターン1:10人ずつ増加 ※元の収支計画どおり
パターン2:5人ずつ増加
パターン3:変化なし(10人のまま)
---------------

それでは、エクセルで作業していきます。

成長率のセルの下に「パターン1」「パターン2」「パターン3」の3行を挿入し、パターンごとの売上成長率を入力していきます。

エクセルのOFFSET関数のブログ_画像8

ここでは収支計画の見栄えをよくするために、成長率(D列)の右に1列挿入し、「パターン1」「パターン2」「パターン3」をE列に入力しています。

エクセルのOFFSET関数のブログ_画像9_2

次に、パターンの下に後ほどOFFSET関数の「起点から参照のセルまでに進む列の数」で参照するための「参照列数」を1行挿入します。

エクセルのOFFSET関数のブログ_画像10_2

参照列数には、後ほど参照するときにわかりますが、H10に「4」、I10に「5」を入力しておきます。

エクセルのOFFSET関数のブログ_画像11_3

次に、パターンを「1」「2」「3」で選択できるように、新しいシートを作ります。
(ここでは、シート名を「パターンの選択」にしておきます。)

エクセルのOFFSET関数のブログ_画像12

上の画像にあるように、パターンを「1」「2」「3」で選択できるだけでなく、パターンの説明を書いておくと良いと思います。

次に、収支計画のシートに戻り、いよいよOFFSET関数を入れていきます。

OFFSET関数を入れるために、成長率の行である「H6」「I6」のベタ打ち数字を消しておきます。

エクセルのOFFSET関数のブログ_画像13

「H6」に計算式「=OFFSET($D$6,パターンの選択!$C$5,H10)」を入れます。

エクセルのOFFSET関数のブログ_画像14

上の画像の場合、
・起点となるセル:$D$6(成長率)
・起点から参照のセルまでに進む行の数:パターンの選択!$C$5(1)
・起点から参照のセルまでに進む列の数:H10(4)
となり、H7にある「100%」がH6に入力されます。

エクセルのOFFSET関数のブログ_画像16

エクセルのOFFSET関数のブログ_画像17

エクセルのOFFSET関数のブログ_画像18_4

今はパターン1(100%)が参照されていますが、「パターンの選択」のシートでパターンを「2」に変えれば、「起点から参照のセルまでに進む行の数」が1つ移動し、パターン2(50%)が参照されます。

エクセルのOFFSET関数のブログ_画像19

エクセルのOFFSET関数のブログ_画像20

「H6」の計算式を「I6」にコピペすれば、「起点から参照のセルまでに進む列の数」だけ「I10」(5)に変更できます。

エクセルのOFFSET関数のブログ_画像21

エクセルのOFFSET関数のブログ_画像22

コピペするだけで計算式がうまく移動するのは、「起点となるセル」「起点から参照のセルまでに進む行の数」を絶対参照で固定し、「起点から参照のセルまでに進む列の数」を相対参照で移動させているからです。

【参考】エクセルの基本!相対参照と絶対参照をわかりやすく解説するよ

これで売上成長率の場合分けは完成しました!

同じ手順で、「従業員数」の場合分けも作ることができます。

エクセルのOFFSET関数のブログ_画像23

エクセルのOFFSET関数のブログ_画像24

これで従業員数の場合分けも完成しました!

最後に、OFFSET関数の「パターン」や「参照列数」が表示されたままだと見栄えが悪いので、「グループ化」を使って非表示にしておきます。

「パターン」「参照列数」の行を選択します。

エクセルのOFFSET関数のブログ_画像25

エクセルのメニューにある「データ」をクリックします。

エクセルのOFFSET関数のブログ_画像26

データのメニューにある「グループ化」をクリックします。

エクセルのOFFSET関数のブログ_画像27

もういっちょ「グループ化」をクリックします。

エクセルのOFFSET関数のブログ_画像28

「パターン」「参照列数」の行に「マイナス(-)」が表示されます。

エクセルのOFFSET関数のブログ_画像29_2

「マイナス(-)」をクリックすれば、「パターン」や「参照列数」を非表示にできます。

エクセルのOFFSET関数のブログ_画像30_2

同じ手順で、従業員数の「パターン」や「参照列数」も非表示にすれば完成です。

エクセルのOFFSET関数のブログ_画像31

これで、「パターンの選択」のシートでパターンの数字を変えるだけで、ケースごとの収支計画を確認することができます。

以上、 OFFSET関数を使ったスマートな場合分けのやり方でした。

エクセルで収支計画を作る機会があれば、ぜひ使ってみてください!

エクセルでスマートな収支計画を作りたいのであれば、こちらのブログもオススメです!
【参考】プロから学ぶ!エクセルで見やすい表を作成する方法

お付き合い頂き、ありがとうございました!
何かあれば、ツイッターメールまで。

PC300×250




PC300×250




-エクセル

Copyright© みらしん , 2019 All Rights Reserved.