先日OFFSET関数とMATCH関数を組み合わせてVLOOKUP関数より柔軟に表から検索値を返す方法について解説しました。
OFFSET関数とMATCH関数を組み合わせてVLOOKUP関数より柔軟に表から検索値を返す方法 | Excelを制する者は人生を制す ～No Excel No Life～
今回はOFFSET関数の応用テクニックの第2弾として、OFFSET関数とMATCH関数を組み合わせてSUM関数やAVERAGE関数の範囲を可変にする方法について解説していきます。
使用例
今回は表の金額一覧（C3:C32）から、任意の期間の合計値をG6セルに表示したいとします。
具体的にはワークシートの中に期間の起点日（G3セル）と終点日（G4セル）を選択できるようにし、それにあわせてG6セルのSUM関数の計算範囲を可変にします。
可変にするにはOFFSET関数とMATCH関数を組み合わせて求めていきます。
OFFSET関数の基本的な部分は下記記事をご参照ください。
OFFSET関数で指定のセルから指定の行数・列数をスライドした先のセルの値を参照する方法 | Excelを制する者は人生を制す ～No Excel No Life～
では、実際に関数を記述していきます。
まず、G6セルはSUM関数を記述します。
SUM関数の()[かっこ]内にSUM関数の計算範囲としてOFFSET関数を記述します。
OFFSET関数の[参照]は表の中の検索する列の開始値のひとつ上のセルとなるB2セルを指定します。
今回はG6セルだけに返すので相対参照でも良いですが、複数セルにこの数式をコピペしたい場合、ここは絶対参照にしておくと良いです。
続いて、OFFSET関数の[行数]の部分にMATCH関数を使用して可変する値にします。
MATCH関数の[検査値]は検索をかける値を指定するG3セルを指定、[検査範囲]は表中のすべての日付を選択できるようB3:B32を絶対参照で指定、[照合の種類]は絶対値を検索したいので「0」を指定します。
OFFSET関数は[参照]で選択したセルを「0」から起算しますが、MATCH関数は[検査範囲]の最初のセルから指定値を「1」から起算と相違があるため今回はそれぞれ1セル分ずらしていいます。
OFFSET関数の[列数]はC列の金額が計算対象であるため、「1」を指定します。
OFFSET関数の[高さ]は別途あらかじめ計算しておく必要があるため、G5セルを指定しておきます。
G5セル内では、G4セルの終点日からG3セルの起点日を除算（引き算）したものに「+1」で補正しています。
OFFSET関数の[高さ]は[参照]から[行数][列数]分移動して返った値（セル）を含んでカウントされるためです。
最後に、OFFSET関数の[幅]は計算対象が1列分のため「1」を指定しておきます。
これでG6セルに指定した期間の合計値が無事表示されました！
念のためG3・G4セルで指定した期間を実際に範囲選択（C3:C11）し、ステータスバーに表示される合計値と照合するとG6セルに表示された値と一致するため、関数は問題なく計算されていることがわかります。
必ず実際に使用する際は検算もセットで行なうと良いですね(・∀・)
まとめ
今回は数式がかなり複雑なもののため、Excel関数に慣れている方向けの記事でした。
こちらは順番におって学んでいけば、誰でも使用できるものだと思いますので、焦らず順を追って理解していきましょう。
なお、今回のテクニックは範囲を指定する関数すべてに応用できるものであるため、使い勝手が良いです。
一例をあげると、今回例示したSUM関数以外だと、AVEREAGE関数系やCOUNT関数系、MAX/MIN関数などですね。
ただ、注意点としては、連続したデータ範囲しか可変にできないということです。（一個抜かしの範囲などを選択できません）
なので、日付や時刻などの一部分の期間を取得したいなどの連続したデータで使用する、ということだけ留意して使用しましょうね(・∀・)
