数値データを集計するとき、常に同じ範囲を集計する場合ならSUM関数で固定したセル範囲を指定しておけば問題ありませんが、「この期間だけの集計が欲しい」という範囲が変動するケースには対応できません。範囲変更が必要な集計の場合はOFFSET関数を組み込みます。
基本的な考え方
給与テーブルのような条件の区分が細かい表の場合にはVLOOKUP関数などを使いますが、もっとシンプルな表から該当箇所を指定するにはOFFSET関数が便利です。
OFFSET関数
OFFSET関数は、２つの機能を同時に持つ関数です。
１．基準となるセルを指定して、そこから縦、横にどれだけ移動するかの指示を数字で入れることによって、指定するセルを移動させる
２．１．で指定したセルを基準にして、高さと幅を指定し範囲を決める
言葉にすると分かりにくいので具体的に説明します。下の表を見て下さい。今年の愛媛FCホームゲームの観客動員を示した表です
（す、少なすぎ…。地元の皆さん、お時間あるときに是非とも足を運んであげて下さい。選手が涙目です！！）
冒頭の分を見ていただきたいのですが、この表からゲームを指定すると、その期間の累計の観客数が分かるようになっています。ここにOFFSET関数が使われているのです。
具体的には次のようになっています。
SUM関数の中にOFFSET関数を組み込んで、合計する範囲をOFFSET関数で指定する形を取ります。OFFSET関数の要素を順番に見ていきます。
OFFSET関数は先述の通り２つに機能が別れていますのでそれを分けて説明します。
まず、
「１．基準となるセルを指定して、そこから縦、横にどれだけ移動するかの指示を数字で入れることによって、指定するセルを移動させる」
からです。
①は基準となるセルを入力。ここでは「$R$142」として”観客数”の見出しにあたるセルを指定しています。
②は基準からどれだけ移動するかの指示。基準から下に向けていくつ分セルを移動するかを入力します。
ここでは「第2節」にあたる「R143セル」を指定したいので基準である「R142セル」に対して1つだけ下に下がると言う意味で”１”を指定しています。
③も基準からどれだけ移動するかの指示。基準から右に向けていくつ分セルを移動するかを入力します。
ここでは「第2節」にあたる「R143セル」を指定したいので基準である「R142セル」に対して０右に移動するという意味で”０”を指定しています。
これで、基準となるセルが「R142セル」から「R143セル」へと移動しました。
ここで疑問に思われた方もいらっしゃるかもしれません。「何でわざわざ基準を動かして指定するのか」「最初からR143を指定すれば良い」と考えられたのではないでしょうか。
確かに、範囲指定の始点が必ず「R143セル」になるのならそれでいいのです。ですが、今回の観客動員数の集計は「第何節から第何節まで」と始点となるゲームが、指定する条件によって異なる形になっていますので、始点を固定する方法では上手くいかないのです。そこでOFFSET関数を使う意味が出てきます。
OFFSET関数を組み込んでおけば、「基準から下にいくつ、右にいくつ」と言う方法で自由に範囲指定の始点となるセルを変えることができますから、条件が変化しても手動で範囲を変える必要が無くなるのです。
つぎに
「２．１．で指定したセルを基準にして、高さと幅を指定し範囲を決める」
です。範囲指定は１．で決めた始点のセルからの「④高さ」と「⑤幅」をセルの個数を指定して決めています。
④の高さですが、第2節から第36節までだと、「R143セル」から「R160セル」までの18個のセルですから”18”と指定します。
⑤の幅は、R列だけを集計していますから、R列の1列分ということで”１”を指定しています。
以上をまとめると、①から③の入力で基準セルの移動。
④から⑤の入力で、移動後の基準セルから範囲指定。
となります。
MATCH関数
次にMATCH関数です。今回のケースでは基準セルの移動の「②基準から下に向けていくつ分セルを移動するか」と範囲指定の「④高さ」のところでMATCH関数を使います。
もう一度下の図を見て下さい。
ここからは発想の問題になります。範囲指定をセルの個数で指定するのがOFFSET関数でした。
もし仮に、観客数のデータが1節から順番に記載されているとすれば「第○節から第×節まで」で指定される○や×に入力される数字をそのまま使って「②基準セルの移動」や「④高さ」を指定すれば解決します。
ところが今回の表はホームゲーム限定で「第○節」とゲーム数が対応しなくなっています。そこでMATCH関数が威力を発揮します。
MATCH関数は検索したいデータが指定した範囲で何番目に出てくるかを示してくれる関数です。
下の図でMATCH関数の要素を見ていきます。
緑の枠で囲ったH13セルに入力したMATCH関数の式を抜き出したのが、D10セルで示した式です。
①は検索値を示します。この場合、H12セルが指定されていますから、H12セルのデータである”36”が指定されたことになります。
②は検索範囲です。①のデータを検索する範囲を指定します。$D$17：$D$36となっています。下図のようなデータの配置になっていますので、第何節かを示した列が指定されていることが分かります。
③は検索の型です。”０”は完全に一致する場合だけ回答してそれ以外はエラーとする指示です。
この3つの要素についてそれぞれ入力した結果が、緑の枠で囲ったH13にある”19″です。つまり、第36節は上から数えて19番目に当たるという答えを出してくれているのです。
OFFSET関数＋MATCH関数を使ってみる
このようにMATCH関数を使えば。第何節が何番目に当たるかを指定することが分かりましたので、実際に使ってみましょう。
OFFSET関数の要素を指定していきます。
まず、
「１．基準となるセルを指定して、そこから縦、横にどれだけ移動するかの指示を数字で入れることによって、指定するセルを移動させる」
からです。
①は基準となるセルを入力。ここでは「$G$16」として”観客数”の見出しにあたるセルを指定します。
②は基準からどれだけ移動するかの指示。基準から下に向けていくつ分セルを移動するかを入力します。
ここでMATCH関数を使います。第何節が何番目のゲームに当たるかが分かれば始点のセルを指定できます。
M−①の検索値を「第何節から」を指定するF12セルに
Mー②の検索範囲を節を表示するD17セルからD36セルにします。
（M-③の検索の型は０にします。）
③も基準からどれだけ移動するかの指示。基準から右に向けていくつ分セルを移動するかを入力します。
列は基準のG16セルと同じG列で良いので”0”とします。
つぎに
「２．１．で指定したセルを基準にして、高さと幅を指定し範囲を決める」
です。
④の高さですが、「第○節まで」の集計範囲の終点に当たる節のゲーム数（何番目のゲームか）から集計範囲の始点に当たる節のゲーム数（何番目のゲームか）に1をたせば集計すべき範囲が指定できます。
たとえば、第７節から第27節までだと、第７節が4ゲーム目、第27節が15ゲーム目なので、15-4+1で12ゲーム分が集計範囲と分かります。ですので、ここでもMATCH関数を使い、集計範囲の終点に当たる節のゲーム数（何番目のゲームか）を示すようにします。
MATCH（H12,$D$17:$D$36,0）
検索値を「第何節まで」を指定するH12セルに
検索範囲を節を表示するD17セルからD36セルにします。
（検索の型は０にします。）
⑤の幅は、D列だけを集計していますから、D列の1列分ということで”１”を指定しています。
以上を踏まえて作った式がこれです。
まとめ
Excel関数は組み合わせて使うと便利です。
一見無理そうなものでも発想と関数の組み合わせで何とかなるケースは多いです。仕事や勉強の現場で色々なやり方を試してみて下さい。Excelでできることがどんどん広がります。
＜おまけ＞
何も意識せずにサンプルとして使わせてもらいましたが、愛媛FCの観客動員の苦戦ぶりに辛い気持ちになりました。
地元の皆さんに愛されなければクラブの存続も危ういと思います。
クラブが着実に発展していくためにはファン、サポーターを増やす以外にありません。クラブの皆さんにはいっそうの奮闘をお願いしたいと思います。
