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行目)

A2からE2に10から50が入った表
ABCDE
21020304050
=INDEX(A2:E2,1,3)

結果は 30。C2の参照が返り、セルに表示するときに値になります。

=SUM(INDEX(A2:E2,1,2):INDEX(A2:E2,1,5))

結果は 140。B2とE2の参照を「:」でつないで B2:E2 を作り、SUMへ渡しています。上の図の濃い緑が両端です。

参照を返していることを確かめる

行番号や列番号を返す関数に渡すと、値ではなくセルの位置が渡っていることが分かります。

INDEXの参照を確認する数式と結果
数式(Excel 2016以降)結果読み方
=ROW(INDEX(A2:E2,1,3))2C2の行番号
=COLUMN(INDEX(A2:E2,1,3))3C2の列番号
=CELL("address",INDEX(A2:E2,1,3))$C$2参照先のセル番地

配列形式とセル範囲形式

Microsoftの説明では、INDEXには配列形式とセル範囲形式があります。第1引数に渡すものによって、返るものが変わります。

INDEXの配列形式とセル範囲形式の比較
形式構文第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を指定した場合
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))1402つの参照の間のセル範囲を作る
&(文字列演算子)=INDEX(A2:E2,1,2)&INDEX(A2:E2,1,5)20502つの値を文字列としてつなぐ
+(算術演算子)=INDEX(A2:E2,1,2)+INDEX(A2:E2,1,5)702つの値を足す(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月までの数値があり、途中の期間だけを合計します。

=SUM(INDEX(B2:K2,1,2):INDEX(B2:K2,1,6))

B2:K2の2番目(C2)から6番目(G2)まで、つまりC2:G2を合計します。結果は 2000 です。

開始位置と終了位置をセルで指定する

位置をセルから受け取れば、数式を書き換えずに対象を変えられます。

=SUM(INDEX(B2:K2,1,M2):INDEX(B2:K2,1,N2))

図1:M2=2、N2=6のとき(C2:G2、合計 2000)

C2からG2までが対象になった状態
BCDEFGHIJK
14月5月6月7月8月9月10月11月12月1月
21002003004005006007008009001000

図2:M2=4、N2=8に書き換えたとき(E2:I2、合計 3000)

E2からI2までが対象になった状態
BCDEFGHIJK
14月5月6月7月8月9月10月11月12月1月
21002003004005006007008009001000

MATCHで開始列と終了列を求める

列位置の代わりに「5月」「9月」のような見出しで指定するには、MATCHで位置を求めます。M3に「5月」、N3に「9月」を入れた例です。

=SUM(INDEX(B2:K2,1,MATCH(M3,B1:K1,0)):INDEX(B2:K2,1,MATCH(N3,B1:K1,0)))

結果は 2000。どちらかの見出しが見つからないと #N/A になります。

数式の組み立て方

長い式を一度に書くと、どこで間違えたか分かりにくくなります。外側へ1段ずつ組み立てます。

  1. 端の位置を単独で確かめる:=MATCH(M3,B1:K1,0) が 2、=MATCH(N3,B1:K1,0) が 6 になることを見ます。
  2. 端のセルを確かめる:=CELL("address",INDEX(B2:K2,1,2)) が $C$2 を返すことを見ます。
  3. 「:」でつないでROWS・COLUMNSで幅を確かめる:=COLUMNS(INDEX(B2:K2,1,2):INDEX(B2:K2,1,6)) が 5 なら、5列の範囲ができています。
  4. 目的の関数で包む:SUMに置き換え、位置の数字をMATCHの式に差し替えます。
範囲だけを確かめるときの注意
=INDEX(…):INDEX(…) だけをセルに入力すると、Excel 2016では1つの値に絞られるか #VALUE! になります(確認した環境の旧来の評価方式では #VALUE!)。SUM、ROWS、COLUMNSなどの範囲を受け取る関数で包んで確かめてください。
よく使う範囲は、「数式」タブの「名前の管理」で名前を付けると、数式を短くできます。

この書き方が向いている場面

  • 列数が変わる帳票:端の位置を見出しから求めれば、列が増えても範囲の指定を直さずに済みます。
  • 見出しの位置が動く表:列が挿入されても、MATCHが見出しの新しい位置を返します。
  • 集計の開始列・終了列を利用者に選ばせたい場合:入力セルを変えるだけで対象が変わります。
  • 特定期間だけを集計する場合:開始月・終了月から範囲を作れます。
  • 作業列を減らしたい場合:1つの数式の中で範囲を作れます。
見出しの順序が変わると、間に挟まる列も変わります
INDEX:INDEXは両端の間にあるセルをすべて含みます。「5月」と「9月」の間に「合計」列が挿入されると、その列も合計に入ります。また月の並びが入れ替わると、意図しない月が範囲に入ります。時系列の順に並んでいることが前提です。

4. 作った範囲をMATCHで検索する

INDEX:INDEXで作った範囲はセル範囲なので、MATCHの検索範囲としても渡せます。

=MATCH(500,INDEX(A2:A100,3):INDEX(A2:A100,10),0)

A2:A100の3番目はA4、10番目はA11です。A4:A11の中で500を探し、結果は 3 です。

相対位置に注意する

MATCHが返すのは渡された範囲の中での位置です。下の図の500はA6にあります。同じセルでも、基準によって数字が変わります。

図3:同じセルを指す3つの番号

シート行、元の範囲の位置、切り出した範囲の位置の対応
シートの行A列の値A2:A100での位置A4:A11での位置
21001-
32002-
430031
540042
650053
760064
870075
980086
1090097
11500108
12110011-

切り出した範囲での 3 を元の範囲での位置に直すには、開始位置 - 1 + 相対位置、つまり 3 - 1 + 3 = 5 と計算します。シートの行番号が必要な場合は、さらに元の範囲の先頭行との差(この例では 1)を足します。

開始点と終了点もMATCHで求める

J2に開始キー(300)、K2に終了キー(900)、H2に探す値(500)を入れた例です。

=MATCH(H2,INDEX(A2:A100,MATCH(J2,A2:A100,0)):INDEX(A2:A100,MATCH(K2,A2:A100,0)),0)
  1. 1つ目の内側のMATCHが、開始キーの位置 3 を求めます。
  2. 2つ目の内側のMATCHが、終了キーの位置 9 を求めます。
  3. 2つのINDEXが、それぞれA4とA10の参照を返します。
  4. 「:」がA4:A10という範囲を作ります。
  5. 外側のMATCHがA4:A10の中で500を探し、相対位置 3 を返します。

別の列から結果を取り出す

B列の値を取り出す方法は2つあります。どちらも結果は同じ「品5」でした。

方法1:元の範囲での位置に直してからINDEXで取り出す

=INDEX(B2:B100,MATCH(J2,A2:A100,0)-1+MATCH(H2,INDEX(A2:A100,MATCH(J2,A2:A100,0)):INDEX(A2:A100,MATCH(K2,A2:A100,0)),0))

方法2:B列も同じ行範囲で切り出して、相対位置のまま使う

=INDEX(INDEX(B2:B100,MATCH(J2,A2:A100,0)):INDEX(B2:B100,MATCH(K2,A2:A100,0)),MATCH(H2,INDEX(A2:A100,MATCH(J2,A2:A100,0)):INDEX(A2:A100,MATCH(K2,A2:A100,0)),0))

同じ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)。確認した環境での結果です。

INDEX:INDEXとMATCHの特殊な条件での結果
条件数式の要点結果解説
開始と終了が逆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の範囲を超える位置は指定できません。
列番号が0SUM(INDEX(B2:K2,1,0):INDEX(B2:K2,1,3))55000は行全体を指すため、範囲もB2:K2全体になります。
IFERRORは原因を区別しません
=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公式)

🔗 関連記事