Excelで列を追加したら、合計がズレてしまった——そんな経験はありませんか？
VLOOKUPの列番号をハードコードしていたせいで、表に1列挿入しただけで数式が狂い、気づかずに誤った集計を報告してしまった。実務では笑えないこの失敗、実は多くの人が一度は通る道です。
この記事では、SUM・INDEX・MATCHの3関数を組み合わせて、列の追加・削除が起きても壊れない動的な列合計を作る方法を解説します。最初は少し複雑に見えますが、構造を理解すれば応用の幅は格段に広がります。
なぜVLOOKUPではなくINDEX＋MATCHなのか？
VLOOKUPが便利なのは事実ですが、列番号を数字でハードコードするという構造上の弱点があります。
=VLOOKUP("大阪", A1:D4, 3, 0) ← 「3」という数字が命綱
この3という数字、表に列を1本挿入した瞬間にズレます。数十列・数百行のテーブルが相手なら、修正漏れによるミスは時間の問題です。
INDEX＋MATCHなら、列番号の代わりに列名（文字列）で指定できます。表の構成が変わっても数式は自動で正しい列を探しに行くため、メンテナンスコストが大幅に下がります。さらに今回はSUMと組み合わせることで、「1セルの値を取得する」ではなく「列全体をまるごと合計する」という一段上の使い方を実現します。
3関数の役割を整理する
数式全体を理解するために、まず3つの関数が何をしているかを確認しましょう。
INDEX関数：行・列を指定して値を返す
=INDEX(配列, 行番号, 列番号)
通常は特定の1セルの値を返しますが、行番号に0を指定すると列全体を配列として返すという特別な挙動があります。これが今回のテクニックの核心です。
MATCH関数：検索値が何番目にあるかを返す
=MATCH(検索値, 検索範囲, 照合の種類)
「大阪」がヘッダー行の左から何列目にあるかを数値で返します。完全一致で検索する場合は照合の種類に0を指定します。
SUM関数：配列をまるごと合計する
=SUM(数値1, 数値2, ...)
単純な合計関数ですが、配列を引数として受け取れる点が今回のポイントです。INDEXが返した列全体の配列をそのままSUMに渡すことで、列全体の合計が求まります。
数式全体の構造イメージ
各パーツがどう連携しているかを先に俯瞰しておくと、以降の説明が格段にスムーズになります。
SUM( INDEX( データ範囲, 0, MATCH( 列名, ヘッダー行, 0 ) ) ) ↑ ↑ ↑ 合計する 列全体の配列を返す 「列名」が何列目かを調べる
MATCHで列番号を動的に取得 → INDEXでその列全体を配列として取り出す → SUMで合計する、という3段構えです。
実践：列名を指定して列全体を合計する
サンプルデータ
以下のような月別・地域別の売上データを使います。実務では行数が数十〜数百に及ぶことを想定してください。このサンプルは構造を確認するための最小例です。
| 月 | 東京 | 大阪 | 名古屋 |
|---|---|---|---|
| 1月 | 120 | 95 | 80 |
| 2月 | 135 | 110 | 72 |
| 3月 | 148 | 103 | 91 |
- ヘッダー行：
A1:D1（月、東京、大阪、名古屋） - データ部分：
A2:D4
基本の数式
「大阪の合計」を求めるには次のように入力します。
=SUM(INDEX(B2:D4, 0, MATCH("大阪", B1:D1, 0)))
各パーツが何をしているか、表で整理します。
| パーツ | 処理内容 | 返す値 |
|---|---|---|
MATCH("大阪", B1:D1, 0) |
ヘッダーから「大阪」の位置を検索 | 2（2列目） |
INDEX(B2:D4, 0, 2) |
データ範囲の2列目全体を配列で返す | {95; 110; 103} |
SUM({95; 110; 103}) |
配列を合計する | 308 |
検索値をセル参照にして実用度を上げる
"大阪"という文字列を数式に直書きするのではなく、セルF1から読み取るようにすると実用的です。
=SUM(INDEX(B2:D4, 0, MATCH(F1, B1:D1, 0)))
F1にドロップダウンリストを設定して「東京」「大阪」「名古屋」を切り替えるだけで、合計値がリアルタイムに更新されます。集計ダッシュボードのインタラクティブな切り替え機能として、そのまま実務に使える形です。
💡 実装ヒント：
F1にデータの入力規則（リスト）を設定し、ヘッダー行のセル範囲を参照元にするとメンテナンスも楽になります。
応用：特定の行だけを絞り込んで列合計する
「1月と3月のみ、大阪の合計を求めたい」など、行方向にも条件を加えたいケースがあります。この場合はSUMPRODUCTと組み合わせます。
=SUMPRODUCT((A2:A4={"1月","3月"}) * INDEX(B2:D4, 0, MATCH("大阪", B1:D1, 0)))
数式の解説
{"1月","3月"}は配列定数です。複数の条件をまとめて指定できます(A2:A4={"1月","3月"})は各行が条件に一致するかをTRUE/FALSEの配列で返します*（アスタリスク）は論理積として機能し、条件に一致した行のみ売上値が残り、それ以外は0になりますSUMPRODUCTが最終的にその配列を合計します
Ctrl＋Shift＋Enter不要の通常数式として入力できる点も実用上のメリットです。
よくあるエラーと具体的な対処法
#N/A エラー：MATCHが検索値を見つけられない
最多の原因はスペースや全角・半角のズレです。見た目は同じでも内部が違うことがあります。
=IFERROR(SUM(INDEX(B2:D4, 0, MATCH(F1, B1:D1, 0))), "該当なし")
IFERRORでラップし、エラー時に「該当なし」や0を返すようにしておくと、ダッシュボードの表示が崩れません。
#VALUE! エラー：データ範囲に文字列が混入している
数値のはずのセルに文字列が入り込むと合計できずエラーになります。まずISNUMBER関数で数値かどうかを確認し、文字列になっているセルはVALUE関数で数値に変換してから数式を適用してください。
=ISNUMBER(B2) ← FALSEなら文字列が混入している
まとめ
SUM＋INDEX＋MATCHの組み合わせが特に力を発揮するのは、次のような場面です。
- ✅ 列の追加・削除が頻繁なテーブルの集計
- ✅ ドロップダウンと連動させたダッシュボードの構築
- ✅
VLOOKUPの列番号ハードコードによるミスを根本からなくしたい
最初は3つの関数を重ねることに抵抗を感じるかもしれませんが、「MATCHで列番号を動的に取得 → INDEXで列全体を配列化 → SUMで合計」 という3段構えを頭に入れてしまえば、あとは応用するだけです。
VLOOKUPに頼りきりだった方も、ぜひ一度この数式を実際のシートで試してみてください。Excelの集計に対する考え方が、きっと変わるはずです。
