「コピペ de 関数 」FILE_01
今回は、「一覧の中から一致するデータを取り出したい、抽出したい！」という時に使う関数について紹介いたします。
これは、こんな方法を探している方に使える方法です。
- リストを作成して、その中から条件に合うデータを取り出したい
- リスト内で検索する値が入っている列の、左側にある列のデータを取り出したい
- 巨大なリストから必要なデータだけを抽出して、リストを作成したい
このような場合、多くのケースではVLOOKUP関数を使いますが、今回はINDEX関数とMATCH関数を使った方法をお伝えします。
VLOOKUPは使わない！
VLOOKUPを使わない理由は下記の2つです。
表が大きいと、検索数が多くなり処理が遅くなることがあること。
もう一つは、VLOOKUPの方が簡単に使えますが、VLOOKUPは指定した文字を検索する列の左側の値を持ってくることができないからです。
どういうことかと言いますと、このように、検索範囲とする列（表の場合C列）より左側の列の値（B列の商品No.）を引っ張り出したいときに、VLOOKUPは使えません。
ですが、INDEXとMATCHを組み合わせたら左右どちらでも使えるので、こちらの方法を覚えておきましょう。
まずはコピペでやって見てください！
| 商品名 | 仕入れ値 | 個数 | 売値 | 売上個数 |
|---|---|---|---|---|
| りんご | 40 | 100 | 160 | 33 |
| さくらんぼ | 80 | 30 | 320 | 15 |
| バナナ | 30 | 100 | 120 | 80 |
| みかん | 80 | 50 | 320 | 40 |
| スイカ | 300 | 20 | 1200 | 10 |
| メロン | 200 | 20 | 800 | 10 |
| ドラゴンフルーツ | 100 | 20 | 400 | 5 |
| パイナップル | 100 | 20 | 400 | 7 |
こちらのデータをコピペしてご自身のExcelのA1セルに貼り付けてください。
その上で、G3セルにバナナと入力し、G5セルに下記の数式をコピペしてください。
=INDEX(E2:E9,MATCH(G3,A2:A9,0))
数式の中はこのようになっています。
=INDEX(“取ってきたい値が入っている範囲”,MATCH(“取ってくるときに使用するキーワード”,”キーワードを参照する範囲”,0))
最後の”0″は、「照合の種類」とか言うみたいですが、完全に一致するものを取ってくるのがほとんどだと思いますので、基本的には”0″でOKです。
INDEX関数とMATCH関数の説明はあとでするとして、この２つを組み合わせるとこのような挙動になります。
「A2からA9の中から”バナナ”という言葉が上から何行目にあるのか調べ、E2からE9の中で同じ行数に当たるデータをとってくる。」
A2からA9の範囲でバナナが3行目なら、E2から3行目であるE4のデータが表示されるというわけです。
ポイントは、何行目なのか調べる範囲（A2からA9）と、取り出すデータが入ってる範囲（E2からE9）の行の数を同じにすることです。
あくまでMATCH関数は、”何行目なのか”ということしか調べないので、行数が一致してないと下の方のデータをとってくるときにずれたり、エラーになったりします。
これを応用して、「必要なデータを取り出した表」を作る人はこちらも使ってください。
こんな感じの表ですね。
=INDEX($E$2:$E$9,MATCH(G2,$A$2:$A$9,0))
これをつくる際は、H2セルに上に書いた数式をコピペしてください。
G2,G3,G4,G5に取り出したい商品名を入力することで画像のようになります。
検索範囲とデータを取り出す範囲を絶対参照にして固定し、検索に使う言葉が入ったセルは「$」をつけません。
そうすることで、H2セルを下に向けてH3,H4,H5とコピーしていってもイメージしたようにデータが取り出せると思います。
「$」は絶対参照という意味です。
絶対参照を知らない方は前回の記事をみてみてください。
今回使った関数
INDEX関数・・・指定した行の値を取ってくる
MATCH関数・・・指定したデータが何行目にあるか調べる
次回は、データの中から文字列を取り出す（RIGHT,LEFT,MID）を書いていきます！
