INDEX:INDEXで可変範囲を作る:SUMとMATCHで使う参照の仕組み
目次(8項目)
💡 この記事で理解すること
INDEXは「指定した位置の値を取り出す関数」として紹介されることが多いですが、セル範囲を渡した場合に返しているのはセルへの参照です。この性質を使うと、集計範囲や検索範囲を数式の中で切り替えられます。
=SUM(INDEX(B2:K2,1,M2):INDEX(B2:K2,1,N2)) は、M2とN2の列位置を両端とする範囲を作ってSUMへ渡します。作業列を増やさずに範囲を切り替えられますが、範囲を作ることと、その範囲を調べることは別の処理です。検索を速くするかどうかは条件によって変わります。
1. INDEXは値だけでなく参照も返す
A2:E2に 10、20、30、40、50 が入っているとします。次の2つの数式は、どちらもINDEXを使っていますが、やっていることが違います。
使用する表(2行目)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 2 | 10 | 20 | 30 | 40 | 50 |
結果は 30。C2の参照が返り、セルに表示するときに値になります。
結果は 140。B2とE2の参照を「:」でつないで B2:E2 を作り、SUMへ渡しています。上の図の濃い緑が両端です。
参照を返していることを確かめる
行番号や列番号を返す関数に渡すと、値ではなくセルの位置が渡っていることが分かります。
| 数式(Excel 2016以降) | 結果 | 読み方 |
|---|---|---|
=ROW(INDEX(A2:E2,1,3)) | 2 | C2の行番号 |
=COLUMN(INDEX(A2:E2,1,3)) | 3 | C2の列番号 |
=CELL("address",INDEX(A2:E2,1,3)) | $C$2 | 参照先のセル番地 |
配列形式とセル範囲形式
Microsoftの説明では、INDEXには配列形式とセル範囲形式があります。第1引数に渡すものによって、返るものが変わります。
| 形式 | 構文 | 第1引数の例 | 返るもの | 「:」でつなぐと |
|---|---|---|---|---|
| セル範囲形式 | INDEX(reference, row_num, [column_num], [area_num]) | A2:E2 | セル参照 | 範囲になる(結果 140) |
| 配列形式 | INDEX(array, row_num, [column_num]) | {1,2,3}、A2:E2+1 | 値 | #VALUE! |
計算した結果の配列はセルではないので、参照を作れません。=SUM(INDEX(A2:E2+1,1,2):INDEX(A2:E2+1,1,5)) は確認した環境で #VALUE! になりました。
0は「行全体」「列全体」を意味します。
=SUM(INDEX(A2:E2,1,0)) はA2:E2全体を合計し、結果は 150 です。位置を計算で求める場合、誤って0になると、1つのセルではなく全体が返る点に注意してください。
2. 「:」「&」「+」は別の演算子
「INDEXを結合する」という言い方では、次の3つの違いが分かりません。どの記号を使うかで、結果はまったく変わります。A2:E2は上と同じです。
| 記号 | 数式(Excel 2016以降) | 結果 | 何をしているか |
|---|---|---|---|
| :(参照演算子) | =SUM(INDEX(A2:E2,1,2):INDEX(A2:E2,1,5)) | 140 | 2つの参照の間のセル範囲を作る |
| &(文字列演算子) | =INDEX(A2:E2,1,2)&INDEX(A2:E2,1,5) | 2050 | 2つの値を文字列としてつなぐ |
| +(算術演算子) | =INDEX(A2:E2,1,2)+INDEX(A2:E2,1,5) | 70 | 2つの値を足す(B2とE2だけ) |
=SUM(INDEX(A2:E2,1,2)&":"&INDEX(A2:E2,1,5)) の結果は 140 ではなく、確認した環境では 0.868056 でした。B2とE2の値がつながって文字列「20:50」になり、これが時刻の20時50分(1日の約0.868)として数値に変換されたものと考えられます。エラーにならないので気づきにくい誤りです。
「&」を使う複合キー検索は、INDEX&INDEXと複合検索の記事で扱います。
3. INDEX:INDEXで可変範囲を作る
月別の表を例にします。B2:K2に4月から1月までの数値があり、途中の期間だけを合計します。
B2:K2の2番目(C2)から6番目(G2)まで、つまりC2:G2を合計します。結果は 2000 です。
開始位置と終了位置をセルで指定する
位置をセルから受け取れば、数式を書き換えずに対象を変えられます。
図1:M2=2、N2=6のとき(C2:G2、合計 2000)
| B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | 10月 | 11月 | 12月 | 1月 |
| 2 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
図2:M2=4、N2=8に書き換えたとき(E2:I2、合計 3000)
| B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 4月 | 5月 | 6月 | 7月 | 8月 | 9月 | 10月 | 11月 | 12月 | 1月 |
| 2 | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 | 900 | 1000 |
MATCHで開始列と終了列を求める
列位置の代わりに「5月」「9月」のような見出しで指定するには、MATCHで位置を求めます。M3に「5月」、N3に「9月」を入れた例です。
結果は 2000。どちらかの見出しが見つからないと #N/A になります。
数式の組み立て方
長い式を一度に書くと、どこで間違えたか分かりにくくなります。外側へ1段ずつ組み立てます。
- 端の位置を単独で確かめる:
=MATCH(M3,B1:K1,0)が 2、=MATCH(N3,B1:K1,0)が 6 になることを見ます。 - 端のセルを確かめる:
=CELL("address",INDEX(B2:K2,1,2))が $C$2 を返すことを見ます。 - 「:」でつないでROWS・COLUMNSで幅を確かめる:
=COLUMNS(INDEX(B2:K2,1,2):INDEX(B2:K2,1,6))が 5 なら、5列の範囲ができています。 - 目的の関数で包む:SUMに置き換え、位置の数字をMATCHの式に差し替えます。
=INDEX(…):INDEX(…) だけをセルに入力すると、Excel 2016では1つの値に絞られるか #VALUE! になります(確認した環境の旧来の評価方式では #VALUE!)。SUM、ROWS、COLUMNSなどの範囲を受け取る関数で包んで確かめてください。よく使う範囲は、「数式」タブの「名前の管理」で名前を付けると、数式を短くできます。
この書き方が向いている場面
- 列数が変わる帳票:端の位置を見出しから求めれば、列が増えても範囲の指定を直さずに済みます。
- 見出しの位置が動く表:列が挿入されても、MATCHが見出しの新しい位置を返します。
- 集計の開始列・終了列を利用者に選ばせたい場合:入力セルを変えるだけで対象が変わります。
- 特定期間だけを集計する場合:開始月・終了月から範囲を作れます。
- 作業列を減らしたい場合:1つの数式の中で範囲を作れます。
INDEX:INDEXは両端の間にあるセルをすべて含みます。「5月」と「9月」の間に「合計」列が挿入されると、その列も合計に入ります。また月の並びが入れ替わると、意図しない月が範囲に入ります。時系列の順に並んでいることが前提です。
4. 作った範囲をMATCHで検索する
INDEX:INDEXで作った範囲はセル範囲なので、MATCHの検索範囲としても渡せます。
A2:A100の3番目はA4、10番目はA11です。A4:A11の中で500を探し、結果は 3 です。
相対位置に注意する
MATCHが返すのは渡された範囲の中での位置です。下の図の500はA6にあります。同じセルでも、基準によって数字が変わります。
図3:同じセルを指す3つの番号
| シートの行 | A列の値 | A2:A100での位置 | A4:A11での位置 |
|---|---|---|---|
| 2 | 100 | 1 | - |
| 3 | 200 | 2 | - |
| 4 | 300 | 3 | 1 |
| 5 | 400 | 4 | 2 |
| 6 | 500 | 5 | 3 |
| 7 | 600 | 6 | 4 |
| 8 | 700 | 7 | 5 |
| 9 | 800 | 8 | 6 |
| 10 | 900 | 9 | 7 |
| 11 | 500 | 10 | 8 |
| 12 | 1100 | 11 | - |
切り出した範囲での 3 を元の範囲での位置に直すには、開始位置 - 1 + 相対位置、つまり 3 - 1 + 3 = 5 と計算します。シートの行番号が必要な場合は、さらに元の範囲の先頭行との差(この例では 1)を足します。
開始点と終了点もMATCHで求める
J2に開始キー(300)、K2に終了キー(900)、H2に探す値(500)を入れた例です。
- 1つ目の内側のMATCHが、開始キーの位置 3 を求めます。
- 2つ目の内側のMATCHが、終了キーの位置 9 を求めます。
- 2つのINDEXが、それぞれA4とA10の参照を返します。
- 「:」がA4:A10という範囲を作ります。
- 外側のMATCHがA4:A10の中で500を探し、相対位置 3 を返します。
別の列から結果を取り出す
B列の値を取り出す方法は2つあります。どちらも結果は同じ「品5」でした。
方法1:元の範囲での位置に直してからINDEXで取り出す
方法2:B列も同じ行範囲で切り出して、相対位置のまま使う
同じMATCHを数式の中で何度も書くので、読みにくくなります。
実務での使い方:位置を別のセルに置く
開始位置と終了位置をセルに分けると、数式が短くなり、途中の結果も目で確かめられます。作業列を使うことになりますが、1本の式より直しやすくなります。
| セル | 数式(Excel 2016以降・通常のEnter) | 結果 |
|---|---|---|
| M2(開始位置) | =MATCH(J2,$A$2:$A$100,0) | 3 |
| N2(終了位置) | =MATCH(K2,$A$2:$A$100,0) | 9 |
| O2(元の範囲での位置) | =M2-1+MATCH(H2,INDEX($A$2:$A$100,M2):INDEX($A$2:$A$100,N2),0) | 5 |
| P2(取得する値) | =INDEX($B$2:$B$100,O2) | 品5 |
5. 逆転・未一致・重複の動作
データは図3と同じです(A2:A15に100〜1400、ただしA11は重複する500)。確認した環境での結果です。
| 条件 | 数式の要点 | 結果 | 解説 |
|---|---|---|---|
| 開始と終了が逆 | MATCH(500,INDEX(…,MATCH(900,…)):INDEX(…,MATCH(300,…)),0) | 3 | 範囲はA4:A10として扱われ、相対位置は上から数えます。SUMも逆順で同じ合計になります。 |
| 逆のまま元の位置へ変換 | MATCH(900,…)-1+相対位置 | 11(誤り) | エラーにならず、誤った位置になります。 |
| 逆になる可能性がある場合 | MIN(MATCH(J2,…),MATCH(K2,…))-1+相対位置 | 5 | 小さい方の位置を起点にします。 |
| 探す値が範囲内にない | MATCH(999,…) | #N/A | 範囲の外にあっても見つかりません。1400はA2:A100にはありますが、A4:A11で探すと #N/A です。 |
| 開始キー・終了キーがない | INDEX(…,MATCH(999,…)) | #N/A | 内側のエラーがそのまま返ります。 |
| 探す値が重複 | MATCH(500,700〜1400の範囲,0) | 4 | 範囲内で最初の一致(A11)を返します。範囲全体で探すと最初のA6(位置5)です。 |
| 端のキーが重複 | MATCH(500,A2:A100,0) | 5 | 開始にも終了にも最初の位置が使われます。2つ目の500を端にすることはできません。 |
| 列番号が表の外 | INDEX(A2:E2,1,6) | #REF! | INDEXの範囲を超える位置は指定できません。 |
| 列番号が0 | SUM(INDEX(B2:K2,1,0):INDEX(B2:K2,1,3)) | 5500 | 0は行全体を指すため、範囲もB2:K2全体になります。 |
=IFERROR(MATCH(…),"範囲内になし") と書くと、探す値がない場合だけでなく、開始キーや終了キーが見つからない場合にも「範囲内になし」と表示されます。原因を分けたいときは、開始位置と終了位置を別のセルで確認してください。
MATCHの照合の種類が0(完全一致)なら、検索範囲は並べ替えられていなくてもかまいません。一方、INDEX:INDEXで作る範囲そのものは、両端の間にあるセルを含むため、探したいデータがその間にまとまっていることが前提です。
6. 計算の負荷について
Microsoft公式Microsoftの性能ガイドでは、INDEXは非揮発性、OFFSETは揮発性の関数とされ、揮発性の関数を減らす方法としてINDEXが挙げられています。
実測10万行を合計する式を2,000個入れ、無関係なセルを変更して再計算しました(Microsoft 365版ビルド16.0.20625、2026-11-05、5回の中央値)。通常の範囲は0.9ミリ秒、INDEX:INDEXは0.8ミリ秒、OFFSETは517ミリ秒でした。この条件では、INDEX:INDEXは無関係なセルの変更では再計算されず、OFFSETは再計算されました。
注意したいのは、INDEX:INDEXは範囲の参照を作っているだけで、その中の値を読むのはSUMやMATCHだという点です。範囲を狭めれば調べるセルは減りますが、開始位置と終了位置を毎回MATCHで探すと、その分の検索が増えます。
実測45万行の測定(Microsoft 365版、2,000回の検索)では、端の位置が分かっている1,000行の範囲を調べると約0.04秒、端を全域のMATCHで探すと約9〜10秒で、全域検索の約8秒より短くなりませんでした。条件の詳細は複合検索・大量データ編の実測をご覧ください。Excel 2016では測っていないため、同じ結果になるとは限りません。
7. まとめと次に読む記事
- INDEXをセル範囲に使うと、返るのは参照です。そのため「:」で範囲を作れます。
- 「:」は範囲を作り、「&」は文字列をつなぎ、「+」は値を足します。
- 計算結果の配列を渡した配列形式のINDEXからは、範囲を作れません。
- MATCHの結果は渡した範囲の中での位置です。元の範囲での位置には
開始位置 - 1 + 相対位置で直します。 - 開始と終了の逆転はエラーになりませんが、位置を変換するときに誤りの原因になります。
出典(Microsoft公式)
- INDEX 関数:配列形式とセル範囲形式、0の意味
- MATCH 関数:相対位置、照合の種類
- 演算子と Excel での優先順位:「:」「&」「+」の定義
- Excel performance: Tips for optimizing performance obstructions:揮発性とINDEX(英語)
- Range.Formula vs Range.Formula2:旧来の評価方式(英語)