Googleスプレッドシートをより効率的に活用するために、INDEX関数とMATCH関数を組み合わせた方法を解説していきます。
この記事では、データの特定の値や条件に基づいて情報を素早く検索するための手法を解説し、スプレッドシートの機能を最大限に活かしたデータ管理のヒントとなれば幸いです。
MATCH関数とは？
MATCH関数のヘルプには「指定した値と一致する範囲内のアイテムの相対的な位置を返す」とあります。
相対的な位置って何？ってなるかと思いますが、行か列の値を返すと理解してください。
構文は以下の通りです。
＝MATCH(検索する値, 検索する範囲, [検索の種類])
引数は「検索する値」「検索する範囲」「検索の種類」の3つ指定しますが、最後の「検索の種類」は省略もできます。
- 「検索の値」は、”りんご”のように指定するか、検索したい語句の入ったセルを指定できます。
- 「検索する範囲」は、一次元の範囲を指定します。A1:A10や、A1:G1のように1列もしくは1行の範囲を指定できます。A1:C5のように二次元の範囲は指定できません。
- 「検索の種類」は、「1」検索の範囲が昇順にソートされていると想定し最も大きい値を返す、「0」完全一致、「-1」検索の範囲が降順にソートされている想定し最も小さい値を返すの3種類指定できます。デフォルトは「1」なので、引数を省略すると「1」として処理されます。
例えば下図のような表があるとします。
B列にある「品種」を検索する範囲として、MATCH関数を使ってみましょう。
下図のようにセルF2の値を「検索の値」として、セルG2にMATCH関数を記述していきます。
セルG２に記述したMATCH関数は下記のとおりです。
=MATCH(F2,B1:B10,0)
- 「検索の値」をセルF2の値とします。
- 「検索する範囲」をB1:B10（品種が入っている列）とします。
- 「検索の種類」は完全一致をしめす「0」とします。
「検索の種類」はソートされているか不明で、重複する値が無い場合などに有効です。データの状態がよくわからないときは「0」を指定するのが無難でしょう。
MATCH関数が実行された結果は、「4」となっています。
「王林」は4行目にあるので「4」の値が返ってきたということですね。
以上がMATCH関数の使い方になります。
MATCH関数を使うことで、検索したい値の行番号（何行目か？）がわかりましたね。
「検索の範囲」をB1:B10とB列を指定したので行番号がわかりましたが、A4:D4のように4行目を指定した場合は、「2」という列番号（王林という名称は2番目のB列にあるため）が返ってきますよ！
MATCH関数を使って、行番号（もしくは列番号）がわかることで、INDEX関数との組み合わせがより有効活用できるようになります。
INDEX関数とは？
指定した範囲の行と列番号を指定することで、そのセルの値を返す関数です。
例えば上図のような表があったときに、参照する範囲をA1:D10とした場合・・・
- 5行目：2列目の値は「シナノゴールド」（セルB5の値）
- 2行目：4列目の値は「3,000」（セルD2の値）
のように、行と列の値を指定するとそのセルの値を返してくれます。
INDEX関数の構文は以下の通りです。
=INDEX(参照する範囲, [行], [列])
行と列は省略すると「0」として扱われます。「0」が指定されると、行または列の値を全て返してくれます。
先ほどの表を使って、下記のようにINDEX関数を記述すると、それぞれ次のように値が返ってきます。
=INDEX(A1:D10,5,2) → 「シナノゴールド」
=INDEX(A1:D10,2,4) → 「3,000」
INDEX関数の使い方は以上となります。
行と列の値がわかればその指定したセルの値を返す関数がINDEX関数です。
INDEX関数とMATCH関数を組み合わせる
INDEX関数では、行と列を指定することで、そのセルの値を返すことができます。
その指定する行や列の値をMATCH関数で検索した値を利用することで、一致したデータの別な列（または行）の値を出力することができます。
つまり、VLOOKUP関数やHLOOKUP関数のような仕事が、INDEX関数とMATCH関数の組み合わせで実現できます。
先ほどまでも使っていた、下図の表から「品種」を指定してその品種の「商品コード」「単価」「在庫」を表示できるようにしてみましょう。
完成イメージは下図のような感じです。
「品種」を入力することで、その品種の「商品コード」「単価」「在庫」をそれぞれ出力されます。
セルG2に記述している構文は以下の通りです。
=INDEX(A1:D10,MATCH(F2,B1:B10,0),1)
INDEX関数の「参照する範囲」をA1:D10とします。
INDEX関数の「行の値」をMATCH関数を利用して、検索した値があるセルの行番号を返してもらいます。
INDEX関数の「列の値」は、「商品コード」は1列目なので、「1」とします。
このように、INDEX関数にMATCH関数を組み合わせることで、検索した値の他の情報を表示することができるようになります。
ここで説明した検索機能は、VLOOKUP関数でも実現できそうに見えますが、2列目の「品種」を検索キーとして、1列目の値を表示する、ということはVLOOKUP関数では実現できません。
なぜなら、VLOOKUP関数は指定した範囲の一番左の値を検索する関数なので、検索キーより前の値をひょうじできないのです。
まとめ
今回は、INDEX関数とMATCH関数を組み合わせた検索機能を解説しました。
関数の組み合わせとしては代表的な組み合わせで、機能としてもわりち多用される使い方になります。
VLOOKUP関数を使えば事足りることも多いかもしれませんが、INDEX関数とMATCH関数を組み合わせた書き方を覚えておくことで、いろんなアレンジができるようになります。
（実際、VLOOKUP関数ではできないこともできますし・・・）
難しそうに見えて、一つ一つ理解していくとそれほど複雑な構文ではないので、関数を組み合わせて使う練習にかつようしてみてください。
コメント