Excel上級者が教えるINDEX関数の基本と使える応用ワザ+トラブル対処術

スポンサードリンク
excel index エクセル
「エクセルでデータを抽出するのにINDEX関数を使えるようになりたい」
「INDEX関数でエラーが出てしまうんだけどなぜか分からない…」

エクセルの表からデータを抜き出すことができるINDEX関数。他の関数と組み合わせれば、条件を設定したり行ごと簡単に抜き出せたりします。
でも注意しないとエラーになってしまうことも…。

ここではそんな方のためにINDEX関数の基本的な使い方と、より便利になる他の関数との組み合わせ方、そしてトラブル対処法について詳しく解説します。
これでINDEX関数が使いこなせるようになりますよ!

1.INDEX関数の使い方

では始めにINDEX関数の使い方を確認しましょう。
ここではINDEX関数の概要と指定する引数の解説をします。

1-1.INDEX関数ってどんな関数?

INDEX関数とは、「指定した行と列が交差する位置にあるセルの値を返す」、とても便利な関数です。
では具体的な例を見てみましょう。

excel index

INDEX関数で指定した範囲「A1:E11」において、3行目3列目の値を抜き出しています。
この結果「滋賀」が表示されました。

このように、元の表から行列の番号を指定することで必要な情報を取り出せるのがINDEX関数なのです。

1-2.INDEX関数の構文

ではINDEX関数ではどのようなことを指定すればいいのでしょうか?
INDEX関数の構文は次の通りです。

INDEX(配列(参照), 行番号, 列番号,領域番号)
配列(参照) データの範囲を指定
行番号 値を返す行を数値で指定
列番号 値を返す列を数値で指定
領域番号 (複数の領域を指定した場合のみ)
領域の番号を数値で指定

では数式を作ってみましょう。

(1)数式バーに「=INDEX(」と入力しfxボタンを押す
excel index

(2)使用する引数を選択する
excel index

※範囲(領域)を複数指定する場合以外は「配列,行番号,列番号」の使用がおすすめ

(3)関数の引数ウィンドウで配列、行番号、列番号を指定する
excel index

(4)完了
excel index

このようにすればINDEX関数を使うことができます。

2.INDEX関数応用編!もっと複雑な使い方を知ろう!

次にINDEX関数応用編として、他の関数との組み合わせ方を確認します。
ここではROW関数やMATCH関数、SUMPRODUCT関数との組み合わせを見てみましょう。

2-1.ROW関数との組み合わせ

まずはROW関数との組み合わせ方です。

ROW関数は、「指定したセルの行番号」を返す関数です。
INDEX関数とROW関数を組み合わせることで、n行ごとのデータを抜き出すことができます。

excel index

例えば2行目から2行ごとにデータを抜き出したい場合、表示させたい一番上のセルには図のように「=INDEX($A$1:$E$11,ROW(A1)*2,1)」と、行番号の所へROW関数を使います。

この場合ROW関数の結果は「1×2=2」。
INDEX関数で指定した範囲は「$A$1: $E$11」、列番号は「1」ですから、最終的に結果は「1」となります。

そしてこの数式を下にコピーすると、行番号の所のみが「ROW(A2)*2」に変化します。

excel index

この場合行番号は「4」となるので、最終的にINDEX関数の結果は「3」となります。

excel index

先ほどの数式をコピーすると1つ飛びでデータを取り出せているのが分かると思います。
つまりINDEX関数カッコ内の行番号を「ROW()×n」とすることで、n行おきのデータが抜き出せるのです。

2-2.MATCH関数との組み合わせ

次にMATCH関数との組み合わせ方です。MATCH関数は、「指定したセルの範囲を検索し、その項目の相対的な位置(範囲において上から何番目か?)を返す」関数です。

excel index

上の例は、INDEX関数とMATCH関数を組み合わせて元の表から№4の「牧田 槇一」さんのデータを抜き出したものです。

セルA15に検索値を入力することで該当するデータを抜き出すことができ、例えばセルA15を「6」とすると「佐藤 幸三」さんのデータに変わります。

excel index

なお、MATCH関数の構文は次の通りです。

MATCH(検査値, 検査範囲, 照合の種類)
検査値 検索する値を指定
検査範囲 検査値が入力されている範囲を指定
照合の種類 FALSE→完全一致、TURE→近似値

そして数式は次のようになります。

=INDEX(配列,MATCH(検索値,検査範囲,0),列番号)

「これだったらVLOOKUP関数の動きと同じだよ?」と思う方もいるかもしれません。
しかし、VLOOKUP関数との大きな違いは、「検査範囲が左端でなくてもOK」な所です。

VLOOKUP関数は検査範囲が必ず左端でなければなりませんが、INDEX+MATCH関数はその必要がありません。
つまり数式の入力は面倒ですが、様々な場面で使える組み合わせなのです。

覚えておくととても便利なのでぜひ使い方を身に付けましょう。

2-3.複数条件にはSUMPRODUCT関数を使う

続いてSUMPRODUCT関数との組み合わせ方を紹介します。

SUMPRODUCT関数は、「範囲の対応する要素の積を合計した結果を返す」関数で、構文は次の通りです。

SUMPRODUCT(配列1,配列2)
配列1 積の合計を求めたい配列の1つ目を指定
配列2 積の合計を求めたい配列の2つ目を指定

※255個まで指定可能

SUMPRODUCT関数とINDEX関数を組み合わせると、複数条件の指定ができるようになります。

excel index

上の例は、表から「①出身が東京」で「②受講料が2,200円」の人を抜き出したもので、狙い通り№7の「小田 勝」さんのデータが抜き出せました。

数式は次のように入力します。

=INDEX(配列,SUMPRODUCT((配列1=条件1)*(配列2=条件2),ROW(配列1)),列番号)

ちなみにセルB15へ入れている数式は「=INDEX($A$1:$E$11,SUMPRODUCT(($C$1:$C$11=”東京”)*($E$1:$E$11=2200),ROW($A$1:$A$11)),2)」です。

SUMPRODUCT関数はこのようにすることで、どちらの条件にも合う行の番号が取得できるのです。

こちらも覚えておくと便利ですよ。

3.INDEX関数のエラー原因と対処法

最後にINDEX関数のエラー原因と対処法です。
「#REF!」エラーと「#N/A」エラーについてみてみましょう。

3-1.#REF!エラーの場合

まずは#REF!エラーからです。

excel index

上の例では表A2:E11の範囲から、行番号1、列番号6のデータを抜き出そうとしていますが、#REF!エラーとなりました。
なぜでしょうか?

原因は、「列番号が範囲を超えている」からです。
よく見てみてください。

範囲はA列からE列までの5列なのに対して、列番号は6としています。
つまり範囲外となっているのです。

ですからこの場合には、範囲か列番号を直して正しく指定しましょう。

3-2.#N/Aエラーの場合

次に#N/Aエラーの場合です。

excel index

上の例ではINDEX+MATCH関数で、指定した検査値のデータを抜き出そうとしていますが、#N/Aエラーとなりました。
なぜでしょうか?

原因は「検査値が検査範囲にない」からです。
数式を見ると検査値を「11」としていますが、検査範囲A列の中に「11」はありません。

つまり「探したのですがありませんでしたよ」ということなのです。

ですからこの場合は、MATCH関数の検査値か検査範囲を正しく直しましょう。

なお、検査値が検査範囲にあるはずなのに#N/Aエラーになる、という場合もあるかもしれません。
その際には検査値と検査範囲の「大文字と小文字の違い」や「半角と全角の違い」も確認してみてください。

4.まとめ

以上エクセルのINDEX関数について、基本的な使い方と他の関数との組み合わせ方、そしてトラブル対応術を解説しました。

INDEX関数は紹介したように他の関数と組み合わせてよく使われます。
使い方をマスターすればエクセルがこれまでよりももっと便利になるので、是非身に付けてくださいね!

コメント