データを扱う仕事をしている人は、大量のデータから検索したい時があります。
例として、下記のテーブルをご覧ください。
「A2にあるデータを基に、任意のデータをA3セルに反映させたい」
という場面です。
| A列 | B列 | C列 | D列 | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | 未入力 | ラーメン | 120 | 2000 |
| 3 | チャーハン | 230 | 125 |
この記事では「ラーメンのvolを知りたい」というときに、A2に「ラーメン」と入れれば、対応する値である120がA3に反映させる関数を組んでいきます。
例のデータ量なら目視の方が速いですが、実際に使う場面は大量のデータ群であることが多く、検索する方が効率的です。
タイトルにあるように、index関数とmatch関数を使った検索方法が一番おすすめなのですが、他にも検索方法はあります。
ご自身に合う関数を使って業務効率を改善して下さい。
vlookup関数で検索する場合
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | 未入力 | ラーメン | 120 | 2000 |
| 3 | チャーハン | 230 | 125 |
vlookup
（検索語句、 検索する範囲、返したい値がある列番号、一致条件））
- 検索語句…検索するキー。ここでは「ラーメン」もしくはA2
- 検索する範囲…検索する語句と、返したい値がある範囲。ここではB1:D3
- 返したい値がある列番号…先ほど指定した範囲のどこの列を返したいか。今回はB1:D3が検索する範囲であり、返したい値volがあるのは2列目。
※検索する範囲に依存する要素です。仮に検索する範囲をA1:D3にした場合、volがあるのは3列目になります。 - 一致条件…検索語句が「一致する」と判断するレベルを指定します。
完全一致（FALSE）、あるいは近似一致(TRUE)の２つがありますが、完全一致しかほぼ使いません。
実例：vlookupを使う場合にA3に入れる関数式
=VLOOKUP(“ラーメン”,B1:D3,2,false)
=VLOOKUP(“ラーメン”,B1:D3,2,0)
あるいは
=VLOOKUP(A2,B1:D3,2,false)
=VLOOKUP(A2,B1:D3,2,0)
上記の計算式の結果は「120」になります。下に表貼っておきます。
※falseは0と同義です。trueは1と同義。
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | ラーメン | ラーメン | 120 | 2000 |
| 3 | 120 | チャーハン | 230 | 125 |
vlookupは正直めんどくさい
vlookupが面倒なのは3つ目の因数である「返したい値がある列番号」です。
検索する範囲において、何列目を返したいかを指定するのが手間です。
でもなぜか人気。僕の使い方が悪いのかな。
[voice icon=”https://life-workist.com/wp-content/uploads/2019/11/face_default.jpeg” name=”ゆった” type=”l”]とはいえvlookup関数も慣れれば便利な関数です[/voice]
index関数とmatch関数を組み合わせる
直感的で分かりやすいのはindex関数とmatch関数を組み合わせた方法です。
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | 未入力 | ラーメン | 120 | 2000 |
| 3 | チャーハン | 230 | 125 |
index( 返したい値がある列、
match(検索語句、検索語句がある列,一致条件））
- 返したい値がある列…今回はvolの列なので、C:C で列範囲を指定
- 検索語句…ここではラーメン。あるいはA2にする参照型でも可。
- 検索語句がある列…今回はkeywordの列なので、B:Bで列範囲を指定
- 一致条件…完全一致か、近似一致。
実例：indexとmatch関数を使う場合にA3に入れる関数式
=index(C:C,match(“ラーメン”,B:B,falese))
=index(C:C,match(“ラーメン”,B:B,0))
あるいは
=index(C:C,match(A2,B:B,false))
=index(C:C,match(A2,B:B,0))
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | ラーメン | ラーメン | 120 | 2000 |
| 3 | 120 | チャーハン | 230 | 125 |
同じ結果が得られますが、index関数とmatch関数の方が理解しやすいです。
index（どの列の値を返したいか？、検索条件の指定）
であり
match（検索する語句やキーワードは何か、検索する範囲や条件を指定）
と機能が分かれてるから分かりやすいのかもしれません。
もう少し詳しく知りたいよ！と思う方に向けて、それぞれの関数を解説します。
[voice icon=”https://life-workist.com/wp-content/uploads/2019/11/face_default.jpeg” name=”ゆった” type=”l”]僕はindex・matchなしには仕事できません[/voice]
index関数でデータを検索
index
（返したい値がある列、返したい行番号）
先ほどの表を用いて具体的に説明します。
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | ラーメン | ラーメン | 120 | 2000 |
| 3 | =index(C:C,2) →120 | チャーハン | 230 | 125 |
セルA３に
=index(C:C,2)
を入力した場合、返ってくる値は「120」です。
これは行番号、つまり縦軸の番号を参照しているわけです。
120があるのはC列の2行目ですよね。
index関数は、返したい値がある縦の列を指定して、返したい値がある横の行を指定するという使い方をします。
セル値を返してくれる関数なので、使いこなせば作業効率を上げてくれる関数です。
index関数のネックは返したい行番号を指定すること。めんどくさすぎます。
2行目を数えるのは非効率な行動です。
今回は「ラーメン」を検索語句として使いたいのです。
そしてここで出てくるのが、次に紹介するmatch関数です。
match関数でデータを検索
| A | B | C | D | |
| 1 | search keyword | keyword | vol | 難易度 |
| 2 | ラーメン | ラーメン | 120 | 2000 |
| 3 | =match(ラーメン,b:b,0) →2 | チャーハン | 230 | 125 |
match
（検索語句、検索語句がある列、一致条件）
match関数は、検索語が何行目にあるかを返す関数です。
ラーメンがある行はB列において2行目なので、今回は2がかえってくるのです。
もちろん、チャーハンを入れると3を返します。
match関数のネックは「2」と行番号だけ返すこと。何にも役に立ちません。
もう一度index・match関数を見てみる
index関数のネックは返したい行番号を指定すること。めんどくさすぎます。
match関数のネックは「2」と行番号だけ返すこと。何にも役に立ちません。
と書きましたが、組み合わせると、お互いのデメリットを補完します。
index関数のめんどくささは、match関数が対応してくれる。
match関数の役に立たなさは、index関数が補完してくれる。
非常に便利な関数ですので、是非使ってみてください。
おまけ
ちなみに1つ目の引数の「返したい値がある列」をB列からC列まで、などの複数列を指定した場合、３つ目の「返したい値がある行」を指定する必要があります。
つまりセルA3に
=index(B:C,2,3)
と入力しなければならず、vlookupの劣化関数になってしまいます。
[voice icon=”https://life-workist.com/wp-content/uploads/2019/11/face_default.jpeg” name=”ゆった” type=”l”]他にもいい関数があれば、Twitterなどで教えてもらえると助かります！[/voice]
ゆったのTwitter
