SUM関数で合計範囲を可変にする方法｜INDIRECTと組合せ
ここでは、エクセルの「SUM関数で合計範囲を可変にする方法」を解説します。
OFFSET関数で同じことはできますが、誰でも使ったことのあるSUM関数でのやり方の説明です。
１．まずはやりたいことを確認
始めに、やりたいことを例を含めて確認します。
１－１．SUM関数の合計範囲を可変にしたい
やりたいことは、「SUM関数の合計範囲を可変にする」です。
例えば、次のような場合に使います。
- 締め日を場合によって変えたい
- 合計対象者を変えて比較したい
このように、「合計する範囲を任意に変更したい」時に使います。
１－２
．例で確認
ひとつ例を見てみましょう。
上の例は、「日ごとの売上個数と金額の一覧表」です。
このような表から、締め日を「まずは5日で」とか「次は15日で」といったように簡単に変えられるようにします。
２．数式の形と簡単な作り方
では、SUM関数で合計範囲を可変にできる数式の形と簡単な作り方を見てみます。
２－１．数式の形
数式の形は、次の通りです。
それぞれの引数は、次のように指定します。
（１）セル番号
セル番号には、合計したい範囲の「左上のセル番号」を入れます。
先程の例で、金額の合計を求めるのであれば「セル番号」は「C3」となります。
（２）列番号
列番号には、合計したい範囲の「右端の列番号」を入れます。
先程の例だと合計したいのは「金額」の1列だけなので、「列番号」は「C」となります。
（３）検索対象
検索対象には、合計範囲の中で「末尾の基準になる値」を入れます。
先程の例だと末尾は「4月10日」にしたいので、「検索対象」は「A12」となります。
（４）検索範囲
検索範囲には、「検索対象を検索する範囲」を「1行目から」入れます。
先程の例だと日付を検索したいので、「検索範囲」は「A1:A9」となります。
セルC12に入れた数式は、次の通りです。
=SUM(C3:INDIRECT("C"&MATCH(A12,A1:A9)))
２－２．数式の簡単な作り方
次に、数式の簡単な作り方を見てみます。
先程の例の表を使い、個数の合計を求めてみます。以下手順です。
（１）以下の数式をコピーし、セルに貼り付ける
【コピーする数式】
（２）「セル番号」の文字を消し、「合計範囲の左上のセル番号」を入れる
（３）「列番号」の文字を消し、「合計範囲の右端の列番号」を入れる
（４）「検索対象」の文字を消し、「末尾の基準にある値」を入れる
（５）「検索範囲」の文字を消し、「検索対象を検索する範囲」を「1行目から」入れる
（６）Enterキーを押せば完了
数式は、次の通りです。
=SUM(B3:INDIRECT("B"&MATCH(A12,A1:A9)))
これで、セルA12の値が変わると「合計範囲も変わり」ます。
締め日を「4月5日」にした場合が、上の通りです。
なお、検索対象がない場合には「その一つ上まで合計」されます。
これで気兼ねなく、「合計範囲を可変で使う」ことができます。
３．数式や計算の流れを解説
最後に、数式や計算の流れを解説します。
３－１．INDIRECT関数・MATCH関数とは
INDIRECT関数は、「数式の一部（全部）をセルの値や計算結果で指定できる」関数です。
ここでは、「MATCH関数で取得した末尾の行番号をSUM関数に組み込ませる」ために使っています。
また、MATCH関数は、「検索してその位置を返す」関数です。
ここでは、「『末尾の基準になる値』を検索し、その行番号を取得する」ために使っています。
３－２．計算の流れを確認
次に、計算の流れを確認します。
まず、セルB12に入れた数式は、以下の通りでした。
=SUM(B3:INDIRECT("B"&MATCH(A12,A1:A9)))
（１）MATCH関数の部分
MATCH関数では、「検索対象が何行目にあるか？」を求めます。
表では「4月10日が8行目にある」ので、「8」と返ってきます。
すると数式は、次のようになります。
=SUM(B3:INDIRECT("B"&9))
（２）INDIRECT関数の部分
INDIRECT関数は「値や計算結果を式に組み込む」役割をします。
INDIRECT関数のカッコ内は「"B"&9」なので、SUM関数の「：」の後ろが「B9」になります。
すると数式は、次のようになります。
=SUM(B3:B9)
（３）SUM関数の部分
SUM関数は、ご存じの通り「範囲を合計」する関数です。
そのため、4月1日から4月10日までの個数の合計を求めることができます。
以上、参考になれば幸いです。
【口コミ・評判のいいExcel本の紹介】