検索関数としてこれまでLOOKUP関数、INDEX関数+MATCH関数を紹介してきました。今回はOFFSET関数の特性・出来ることを見ていきます。>/p>
LOOKUP関数に関してはこちら
INDEX関数+MATCH関数に関してはこちら
今回もサンプルファイルを貼っておきます。OFFSET関数まで使いこなせれば、主要な検索関数は使いこなせるといっても過言ではないので、しっかり身に付けていきましょう。
OFFSET関数とは？
=OFFSET(参照,行数,列数,[高さ],[幅])という関数式になります。
INDEX関数と似ています。
=INDEX(範囲,縦位置,横位置)
違い①、基準点が異なる
違いをいくつか見てみます。まず、行数(縦位置)と列数(横位置)の差です。赤枠が範囲です。
INDEX関数では基準点は1ですが、OFFSET関数の基準点は0になります。
違い②、OFFSET関数は「範囲」をしている
そもそも、OFFSET関数は
=OFFSET(参照,行数,列数,[高さ],[幅])
で[高さ][幅]がカギになります。今度は高さと幅をそれそれ『2』に変更します。
＃VALUE!とエラーになります。これは基準点から縦に1、横に1移動した『50』から高さ2セル分、幅2セル分を参照するという意味になります。
実際の使い方
しかし、参照しただけでは意味がないので参照範囲を合計してみます。
=SUM(OFFSET(C3:E5,C7,C8,C9,C10))
『50』『60』『80』『90』を合計して『280』という値で確かに参照範囲として成立しています。
ただ、SUMやAVERAGEなどならOFFSET関数なしでそのまま使えばいいので、あまり必要性が内容に見えます。しかしOFFSET関数の最大のメリットは、範囲を自由に変更できる」という点です。
OFFSET+他の関数の組み合わせ
さっきの使い方で見たように、他の関数と組み合わせないとほとんど力を発揮しません。しかし組み合わせによってはVLOOKUP関数やINDEX関数を大幅に超える使い勝手のよい関数になります。
OFFSET関数+MATCH関数
組み合わせの1つ目はMATCH関数との組み合わせです。実際に具体例で見てみます。
2018年8月のあるお店の売上・客数・客単価を1か月分まとめました。日々のデータだけでなく特定の期間で売上・客数・客単価といった項目の平均を見られるようにしたいです。
AVERAGE関数でその期間や項目を見ることはできますが、いちいち範囲を指定しないといけないので面倒です。
しかし、OFFSET関数+MATCH関数で以下のように指定してしまえば、わざわざAVERAGE関数を設定する必要がなくなります。
=AVERAGE(OFFSET(A2,MATCH(E4,A3:A33,0),MATCH(E6,B2:D2,0),E5-E4+1,1))
AVERAGE(OFFSET(基準点、(縦の開始位置をE4セルに入力した日から)、(E6で指定した項目で)、終了日～開始日までの日数+開始日の1日分、幅1列分)を平均する
こうやって訳すとわかりやすいかと思います。
もちろん開始日・終了日を変えればMATCH関数が自動的に期間(範囲)を変更しますし、売上ではなく客数・客単価に変えれば対象期間のそれぞれを調べられます。また、開始日や終了日、項目をドロップダウンリストにしてしまえば更に簡略化できます。
COUNTA関数との組み合わせ
リストを作ってもどんどん対象が増えて参照範囲が広がっていくケースなどがあります。その度にいちいち範囲を変えるのも面倒なので、以下のようにしてしまいます。
=AVERAGE(OFFSET(A1,1,1,COUNTA(A:A)-1,1))
これにより、A列に新しく名前が入った際に平均される試験結果の範囲も増えるという形にすることができます。(※AVERAGE関数は空白セルは計算対象としていないので、欠席などで試験を受けていても点数入れなければ計算結果から除外されます)
使いこなせると幅が広がるので、検索関数3点セットとしてLOOKUP・INDEX＋MATCH・OFFSET+他の関数は積極的に使っていきましょう。
