エクセルでスマートに場合分けをしたい方に、読んでほしいブログです。
エクセルで収支計画などを作るときに、売上が伸びた場合、伸びなかった場合など計画の場合分けをすることがあります。
そのときに、多くの人がケースごとにシートを分けたり、ファイルを分けていると思います。
場合分けの数が2〜3程度であれば、そのやり方でも良いと思います。
しかし、たくさんの場合分けが必要になってくると、シートやファイルをたくさん用意して、エクセルの管理がとても大変になってきます。
一方で、今回紹介する OFFSET関数を使えば、1つのシートで無数の場合分けができるようになります！
例えば、売上成長率が100%、かつ、従業員数が10人ずつ増加する場合
例えば、売上成長率が50%、かつ、従業員数が5人ずつ増加する場合
今回のブログでは、このスマートに場合分けができるOFFSET関数の使い方を紹介し、 OFFSET関数を使った収支計画を作っていきたいと思います。
＃もう少しシンプルなやり方がありましたので、近日記事を更新します。
スポンサーリンク
OFFSET関数とは？
そもそも、 OFFSET関数とはなんぞや？という感じですよね。
Microsoft Officeの公式サイトには、以下のように説明されています。
セルまたはセル範囲から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返します。返されるセル参照は、単一のセル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。
簡単に説明すると、指定したセルのデータを抽出することができる関数です。
まだよくわからないと思うので、次のOFFSET関数の使い方を見ながら、イメージを膨らませてもらえればと思います。
OFFSET関数の使い方
OFFSET関数の使い方は、参照結果を入力したいセルを選択し、「=OFFSET(起点となるセル, 起点から参照のセルまでに進む行の数, 起点から参照のセルまでに進む列の数)」を入力します。
上の画像の場合、
・起点となるセル：B4
・起点から参照のセルまでに進む行の数：2
・起点から参照のセルまでに進む列の数：1
となり、C6にある「100」がC4に入力されます。
もし、「起点から参照のセルまでに進む行の数」が「3」になったらどうなるか。
行の数が1つ進んで、C7にある「200」がC4に入力されます。
同じように、行の数が「4」であれば、C8にある「300」がC4に入力されます。
なんとなく、OFFSET関数の使い方はイメージはできたでしょうか？
それでは、このOFFSET関数をどのように収支計画で使うのか、実際に作りながら見ていきましょう。
OFFSET関数を使って収支計画を作ってみる
元となる収支計画はこちらです。
（青色の数字はベタ打ち、黒色の数字は計算式が入っています。「N/A」は数字が入っていないことを意味します。）
OFFSET関数を使って、以下の場合分けができるようにします。
---------------
・売上成長率
パターン1：100% ※元の収支計画どおり
パターン2：50%
パターン3：10%
・従業員数
パターン1：10人ずつ増加 ※元の収支計画どおり
パターン2：5人ずつ増加
パターン3：変化なし（10人のまま）
---------------
それでは、エクセルで作業していきます。
成長率のセルの下に「パターン1」「パターン2」「パターン3」の3行を挿入し、パターンごとの売上成長率を入力していきます。
ここでは収支計画の見栄えをよくするために、成長率（D列）の右に1列挿入し、「パターン1」「パターン2」「パターン3」をE列に入力しています。
次に、パターンの下に後ほどOFFSET関数の「起点から参照のセルまでに進む列の数」で参照するための「参照列数」を1行挿入します。
参照列数には、後ほど参照するときにわかりますが、H10に「4」、I10に「5」を入力しておきます。
次に、パターンを「1」「2」「3」で選択できるように、新しいシートを作ります。
（ここでは、シート名を「パターンの選択」にしておきます。）
上の画像にあるように、パターンを「1」「2」「3」で選択できるだけでなく、パターンの説明を書いておくと良いと思います。
次に、収支計画のシートに戻り、いよいよOFFSET関数を入れていきます。
OFFSET関数を入れるために、成長率の行である「H6」「I6」のベタ打ち数字を消しておきます。
「H6」に計算式「=OFFSET($D$6,パターンの選択!$C$5,H10)」を入れます。
上の画像の場合、
・起点となるセル：$D$6（成長率）
・起点から参照のセルまでに進む行の数：パターンの選択!$C$5（1）
・起点から参照のセルまでに進む列の数：H10（4）
となり、H7にある「100%」がH6に入力されます。
今はパターン1（100%）が参照されていますが、「パターンの選択」のシートでパターンを「2」に変えれば、「起点から参照のセルまでに進む行の数」が1つ移動し、パターン2（50%）が参照されます。
「H6」の計算式を「I6」にコピペすれば、「起点から参照のセルまでに進む列の数」だけ「I10」（5）に変更できます。
コピペするだけで計算式がうまく移動するのは、「起点となるセル」「起点から参照のセルまでに進む行の数」を絶対参照で固定し、「起点から参照のセルまでに進む列の数」を相対参照で移動させているからです。
【参考】エクセルの基本！相対参照と絶対参照をわかりやすく解説するよ
これで売上成長率の場合分けは完成しました！
同じ手順で、「従業員数」の場合分けも作ることができます。
これで従業員数の場合分けも完成しました！
最後に、OFFSET関数の「パターン」や「参照列数」が表示されたままだと見栄えが悪いので、「グループ化」を使って非表示にしておきます。
「パターン」「参照列数」の行を選択します。
エクセルのメニューにある「データ」をクリックします。
データのメニューにある「グループ化」をクリックします。
もういっちょ「グループ化」をクリックします。
「パターン」「参照列数」の行に「マイナス（-）」が表示されます。
「マイナス（-）」をクリックすれば、「パターン」や「参照列数」を非表示にできます。
同じ手順で、従業員数の「パターン」や「参照列数」も非表示にすれば完成です。
これで、「パターンの選択」のシートでパターンの数字を変えるだけで、ケースごとの収支計画を確認することができます。
以上、 OFFSET関数を使ったスマートな場合分けのやり方でした。
エクセルで収支計画を作る機会があれば、ぜひ使ってみてください！
エクセルでスマートな収支計画を作りたいのであれば、こちらのブログもオススメです！
【参考】プロから学ぶ！エクセルで見やすい表を作成する方法