オントラックの財務モデリング担当 北川です。
今日は前回に続いて「選択関数」について、ご紹介します。

「選択関数」とは、リストなどの中から選んだ値を選択する関数です。
前回は、IF関数とCHOOSE関数を使った場合の例をご紹介しました。
今回は、OFFSET関数とINDEX関数を使った場合をご紹介します

前回と同じ具体例を使って、見てみましょう。
今、売上成長率に3つ（楽観、ベース、悲観）のシナリオを考えているとします。
F5セルで1、2、3と入力すれば、選択したシナリオにあった各年の売上成長率が17行目に表示されるようにしたいとします。

まずは、OFFSET関数です。
OFFSET関数の書式は、以前ご紹介した通り、IF(基準、行数、列数、[高さ]、[幅])です。
ここでは、高さ、幅は使わず、行数、列数だけを使ってやってみましょう。

今回の例でF17セルは、=OFFSET(F9,$F$5,0)となります。
これは、F9セルを基準に、F5セルで指定したシナリオ番号だけ下に移動するセルを参照しています。
すなわち、F5セルが1で楽観シナリオの場合、F9セルから一つ下のF10セルを参照することになります。

ここで注目していただきたいことは、このOFFSET関数を使った方法だと、9行目の下から13行目の上のどこに新しいシナリオを入れても売上高成長率の数式を変化させる必要がないということです。
A10セルからA12セルにある番号は場合によっては変える必要があるかもしれませんが、シナリオを増やしてもモデル上の数式を変化させる必要がないのは、大きな利点です。

ちなみに前回ご紹介したIF関数やCHOOSE関数を使った方法だと、シナリオを増やした際には必ず数式の修正が必要になります。

次に、INDEX関数でやってみましょう。
INDEX関数の書式は、以前ご紹介した通り、INDEX（配列、行番号、[列番号]）です。

 

今回の例では、F5セルでシナリオの番号を入力しますので、これを行番号として使い、配列は各年の3つのシナリオの範囲を選べば、完成です。

ここでひとまず完成ですが、このままでは9行目の下や13行目の上に新しいシナリオを追加すると、新しいシナリオは配列の範囲から外れてしまうので、配列を指定し直す必要があります（下図）。

そこで、INDEX関数で指定する配列の範囲をシナリオデータのある10行目から12行目だけでなく、上下もう一列ずつ選び、それに合わせて行番号に1を加えます。

すると、9行目の下や13行目の上にシナリオを追加しても配列の範囲が同じように増えてくれるので、シナリオを追加しても先ほどのOFFSET関数と同じように、数式を修正しなくて済むようになります。

以上、選択関数として4つの関数をご紹介しました。

今回の例では、シナリオを増やしても数式を修正する必要性がないという柔軟性の面では、OFFSET関数、INDEX関数がおススメ、
数式を理解しやすいかという読解性の面では、CHOOSE関数、INDEX関数がおススメ、かと思います。

とはいえ、それぞれメリット、デメリットはありますので、用途に見合った方法を選んでいただければと思います。

これまで一年四か月にわたって連載してきましたこのエクセルブログですが、今回が最終回となります。
次回からは、企業の決算書を解説するブログを始めます。エクセルのテクニックからは離れますが、引き続きよろしくお願い致します。

エクセルのスキルアップで働き方改革を！

---

次回の財務モデリング基礎講座は9月15日（日）です。お申し込みはこちらからどうぞ。