導入: なぜこの技術が重要なのか
実務において「指定した期間分だけの合計を算出したい」というケースは非常に頻繁に発生します。例えば、今日から過去7日間、あるいは今月から指定した月数分だけの売上を合計したいといった場面です。
通常、合計範囲を固定してしまうと、期間が変わるたびに数式を書き換えたり、範囲をドラッグし直したりする手間が発生します。今回紹介する「SUM関数とINDEX関数を組み合わせる」手法を覚えれば、可変的な範囲の集計を自動化でき、ミスを減らし、業務効率を劇的に向上させることが可能です。
基礎知識: INDEX関数とは
INDEX関数は、指定した範囲（配列）の中から、特定の行番号や列番号にある値を取り出す関数です。
通常は「単一の値を取得する」ために使われますが、Excelの数式において「範囲の終端を指定する」という役割を持たせることが可能です。このテクニックを利用することで、数式の中で柔軟に参照範囲を変化させることができます。
実装/解決策: 数式の論理構成
今回の目的は、開始セルを固定し、終了セルをリスト選択に応じて変動させることです。
一般的なSUM関数の構文は「=SUM(開始セル:終了セル)」ですが、この「終了セル」の部分をINDEX関数に置き換えます。
論理式は以下の通りです。
=SUM(開始セル:INDEX(参照範囲, 行番号, 列番号))
これにより、INDEX関数が計算結果として「セル番地」を返すため、SUM関数がそれを範囲として認識し、動的に合計を算出します。
サンプルプログラム: 実装コードと解説
以下のコードは、セルJ6で指定した「日数」に応じて、D7から始まる注文数（F列）の合計範囲を自動で調整する仕組みです。
コード例:
‘ セルK7に動的なSUM関数を入力するVBAコード
Sub SetDynamicSumFormula()
Dim ws As Worksheet
Set ws = ActiveSheet
‘ K7セルに、指定範囲の合計を計算する数式を書き込む
‘ D7を固定の開始点とし、INDEX関数で終了位置を決定
‘ F7:F21の中から、J6で指定された行番号のセルを終端として指定
ws.Range(“K7”).Formula = “=SUM(D7:INDEX(F7:F21,J6,1))”
‘ 結果を確認するためのメッセージ
MsgBox “セルK7に動的な集計数式を設定しました。”
End Sub
コードのポイント:
・Formulaプロパティを使用することで、セルに数式を直接書き込むことができます。
・INDEX関数の引数「J6」が、リストで選択された数値と連動するため、ユーザーがJ6を変更するだけで合計範囲が自動追従します。
応用・注意点: 現場で役立つアドバイス
1. 範囲の整合性: INDEX関数で指定する「配列（F7:F21）」の行数と、リスト（J6）の最大値が一致しているか確認してください。範囲外の行番号を指定するとエラー（#REF!）になる可能性があります。
2. エラー回避: 実務では、J6が空欄だったり、数値以外が入力されたりする場合も想定されます。必要に応じて「IF関数」でJ6が空の場合は合計を出さない等の制御を入れると、より堅牢なツールになります。
3. メンテナンス性: この手法は、OFFSET関数でも実現可能ですが、OFFSET関数は「揮発性関数」と呼ばれ、シート上のどこかが変更されるたびに再計算が走り、ブックが重くなる原因になります。その点、INDEX関数は比較的動作が軽く、大規模なデータシートではこちらの方が推奨されます。
ぜひ、日々の集計業務にこのテクニックを取り入れてみてください。計算の手間を一つ減らすだけでも、積み重なれば大きな時間短縮につながります。
