まだVLOOKUP関数を知らないなら、INDEX&MATCH関数を覚えてしまおう。

2020年9月14日日進月歩Chromebook(クロームブック),Excel&Spreadsheet

「まだVLOOKUP関数を知らないなら、INDEX&MATCH関数を覚えてしまおう。」のアイキャッチ画像
読了時間: 約556
エクセルやGoogleスプレッドシートなどの表計算ソフトには、いろいろと便利な関数があります。
入力した値に対して、参照範囲からデータを返してくれる関数として、「VLOOKUP関数」というのがあります。
長年、VLOOKUP関数を使ってきましたが、便利さにおいて、VLOOKUP関数を軽く上回る関数を知ってしまいました。

それは・・・・
ズバリ!  INDEX&MATCH関数です。


Lukia_74
Lukia
汎用性が高いので、「VLOOKUP?なにそれ、おいしいの?」というような方には、
できれば、最初から、INDEX&MATCH関数を覚えることをオススメします。
以下の記事は、詳しくはないけれど、そこそこ関数は使える。というレベルの私が、実際にVLOOKUP関数とINDEX&MATCH関数使ってみての比較や感想について記したものになります。専門的で詳しい比較などは、専門家の方々が動画にて公開なさっていますので、そちらをご参照ください。

架空のお店の顧客管理を例にしてみます。

以下は、二つのシートに分けて作成するとわかりやすいと思います。

シート「A」:値を入力し、それに対応するデータを返す(表示させる)ためのシート。
シート「B」:入力する値、返すデータなどすべてが含まれているシート。住所録や価格表などをイメージしてもらうとよい。

ちなみに、シート「B」については、以下のようなデータがあると思ってください。

架空のネットショップの顧客管理シートを想定してみます。
ある顧客に関するデータが、横に並んでいます。そして、項目は26個あります。
さらに、そのショップの顧客は現在49名いるものとします。
そして、その後9999名まで顧客が増えるものとします。

行\列 A B C ・・・ Z
1 顧客氏名 顧客番号 住所 ・・・ 購入額
2 A2 B2 C2 ・・・ Z2
(中略)          
50 A50 B50 C50 ・・・ Z50
(中略)          
10000 A10000 B10000 C10000 ・・・ Z10000

どちらも、入力した値に対応するデータを返す関数です。

ではまず、「顧客番号」を入力したら、「購入額」が表示されるようにしてみましょう。

VLOOKUP関数の場合

シート「A」には、以下のようなシートを作るとしてください。
行\列 A列 B列
1(行) 顧客番号 購入額
2 A2 B2
     
     

そして、シート「A」のB2セルに以下の関数を書いてみましょう。イコール記号やカッコ、コンマを省かないように注意してください。また、関数は半角英数字で書きます。

=VLOOKUP(A2,シート「B」シートにおいて、B2からZ50セルを選択し、F4キーを押して、絶対参照にする,25,FALSE)

A2 顧客番号を入力するセルです。
シート「B」のB2からZ50 49名の顧客データが存在する範囲です。データには顧客番号も含んでおく必要があります。
F4キー(絶対参照) データの参照範囲を固定するために必ずしておきましょう。
25 「購入額」データは、参照範囲の何列目に存在するかを示します。
FALSE 完全一致
関数の意味としては、
「A2セルに顧客番号を入力したら、シートBの絶対参照されたB2からZ50の範囲からデータを返す。そのデータは、左から25番めにある。」

という感じですね。

Lukia_74
Lukia
管理する顧客数が49と少ないので、VLOOKUP関数でも十分対応できます。
ただ、各顧客のデータは26項目もありますので、Z列までドラッグして範囲を設定するのはなかなかしんどいと思います。

INDEX&MATCH関数の場合

INDEX&MATCH関数でも、同じことができます。
カッコが二重になっているところもありますので、大変ですが、気をつけて入力してください。
VLOOKUP関数と同じく、イコール記号、関数名、カッコやコンマは、半角英数字で入力します。

=IFERROR(INDEX(シート「B」のZ列,MATCH(シート「A」のA2セル,シート「B」の「顧客番号列(B列)」,0)),””)

意味:もし、シート「A」の顧客番号セルとシート「B」の「顧客番号」列に完全一致のデータがあったら、「購入額」データを返して。
でも、もしエラー(完全一致でない)があったら、そのセルには何も表示しないで。

Lukia_74
Lukia
INDEX関数とMATCH関数を合わせた上、IFERROR関数まで書いてしまったので、
見た目や、書くときにはちょっと苦労しますが、参照範囲が、列レベルで指定できるので、そのぶん楽だと思います。

 

VLOOKUP関数に限界がくるとき

今のところ、関数の書きやすさの点では、VLOOKUP関数に軍配が上がっていると思うのですが、顧客データが入っているシート「B」がこのままの場合、限界が来ます。

その限界例を挙げてみます。

顧客番号を入力して顧客氏名を表す場合

シート「B」は、左から、「顧客氏名」「顧客番号」と並んでいます。
表現が難しいのですが、入力データと合致する「顧客番号」列よりも左にある「顧客氏名」は返せません。

入力データと合致させたい参照データは、最も左に置いておく必要があります。

今回の場合、VLOOKUP関数をどうしても使いたいなら、「顧客番号」列をA列に、「顧客氏名」列をB列に入れ替える必要があります。

Lukia_74
Lukia
ちなみに、INDEX&MATCH関数は、参照列よりも左にあるデータでも返すことができます。

顧客人数が増えた(9999名)

嬉しいことに、顧客人数が9999名まで増えました。
VLOOKUP関数の場合、参照範囲をB2からZ10000まで指定し直す必要があります。

10000行までドラッグするって、なかなか大変です。(時間もかかるし、マウスでドラッグをキープする人差し指が痛くなる)

もし、来年また1万人顧客が増えたら?

このように、VLOOKUP関数は、行(横方向)が増えると、都度直す必要が出てきます。

それに対し、INDEX&MATCH関数は、参照は「列」レベルで行っているので、行が増えてもまったく問題ないんですね。

項目が32に増えた!

9999名と、行が増えた上、管理するべき項目が、26項目から32項目まで増えてしまいました。

となると、もう言うまでもないのですが、VLOOKUP関数は、参照範囲を32列まで広げるとともに、
1万行までドラッグしていく必要が出てきます。

Lukia_74
Lukia
これはもう一日仕事になるでしょうね。
もし、あなたがショップのオーナーなら、
夕方ごろには、「データ管理ソフト、買ってください!(涙)」とスタッフに泣きつかれるかもしれません。
くどいようですが、INDEX&MATCH関数は、参照範囲を「列」レベルで指定しますので、参照したいのが32列めなら、そこだけを指定するだけで済みます。

Lukia_74
Lukia
つまり、縦横無尽にデータを扱えるのが、INDEX&MATCH関数のいいところなんです。

まだVLOOKUP関数を知らないなら、INDEX&MATCH関数を覚えてしまおう。

私自身、VLOOKUP関数には、かれこれ20年近くお世話になってきました。
プライベートで扱うデータなんて少ないので、VLOOKUP関数で十分間に合っていました。

しかし、今年に入って、行数はさほどでもないけれど、項目が多い(横長長方形型)データを扱うことがあり、

Lukia_74
Lukia
参照しない列も含まないといけないなんて、めんどくさい。
参照する列が何列目かをきちんと数えないといけないなんて、もっとめんどくさい。

と思いました。さらにその後、項目はさほどでもないけれど、3万行ぐらいのデータを扱うことがあり、(縦長長方形型)そこで、VLOOKUP関数の限界を感じました。

VLOOKUP関数は、参照データの範囲が狭い(行・列ともに)場合には便利ですが、「広い」場合は、地獄を見るということですね。

Lukia_74
Lukia
範囲の指定は、マウスでドラッグしなくても、キーボードで右下端のセルを入力すれば解決できますが、
データの増減に左右されるというのは、データ管理においては痛いポイントですね。

その点、INDEX&MATCH関数は、行や列に、確実にデータを入力しておけば、参照列を1つだけ指定するだけで済むので、関数を書く際の手間はかかりますが、そのあとの管理は楽です。

VLOOKUP関数にもいいところはありますので、もちろん覚えるに越したことはないのですが、
もしも、大量のデータを扱わなければならないけれど、
「VLOOKUP?なにそれ、おいしいの?」という段階の方は、まずはINDEX&MATCH関数を覚えてしまいましょう。
両方知っておいて、用途に応じて使い分けられるようになると、いいでしょうね。


 

 


プロフィール

Author Profile
Lukia_74

元・再受験生、元塾講師、元高校非常勤講師。広島育ち。
中・高国語の教員免許を取得するも、塾講師時代は英語や数学ばかり教えていた。
思うところあって大学再受験を決意。理転し、数学Ⅲ、化学、生物を独習する。国立大学へ合格するも、2018年3月に再受験生生活にピリオドを打つ。
モットーは「自分の予定はキャンセルできても、生徒の予定はキャンセルできない」と「主婦(夫)こそ理系たれ」。
広島のお好み焼きとグレープフルーツが大好き。どっちかというと左党。楽しみはひとりカラオケ。
高校で教鞭を取った経験から、現在は「現代文」と「小論文」の指導力アップを目指し、自己研鑽中。最近は趣味として高校数学を解く。

カテゴリー

2020年9月14日日進月歩Chromebook(クロームブック),Excel&Spreadsheet

Posted by Lukia_74