Excel関数は組み合わせて使う 検索列の左側を抽出できないVLOOKUP関数の弱点を、INDEX関数、MATCH関数で克服します

シェアする

  • このエントリーをはてなブックマークに追加

Excel関数の中でも、利用価値の高いVLOOKUP関数ですが、弱点もあります。INDEX関数とMATCH関数を組み合わせて使うと、その弱点をクリアすることができるのです。

スポンサーリンク
自動サイズレスボンシブ

VLOOKUP関数の弱点

VLOOKUP関数は、

①指定したデータを調べて

②一致したら、右側に移動して、セルを表示する 
(いくつ右に移動するかは事前に指定する)

と言う関数です。


図で示すと、

スクリーンショット 2015 02 01 12 58 04
(VLOOKUP関数の動きのイメージ)


表の上を、赤の矢印に沿って動くイメージです。
(関数はこの「動きのイメージ」が重要)


VLOOKUP関数は、表の中から必要なデータを抽出するのに使う、非常に便利な関数ですが、先述の通り弱点があります。


それは、

「調査するデータが、表の一番左の列にないと機能しないこと」

です。


VLOOKUP関数では、


「一致したセルから、右にいくつ進んだセルを表示せよ」


と指示しなければいけません。


その結果、表示したいデータが、一致したセルの左側にある場合は、表示させることができないのです。

スクリーンショット 2015 02 01 13 16 03


もちろん、表を加工すれば、VLOOKUP関数で対応できますが、表に手を加えずに、データを抽出したい場合もあるはず。


そのようなときに、VLOOKUP関数の弱点を克服してくれる関数があります。


INDEX関数&MATCH関数の組み合わせです。

INDEX関数とMATCH関数

それぞれの関数について説明します。

INDEX関数

INDEX関数は、範囲を指定して、行番号と列番号を指定すると、
指定したセルを表示してくれる関数です。

スクリーンショット 2015 02 01 13 28 43


=INDEX($L$18:$O$25,5,3)
と入力されています。


この関数の要素は、

①範囲:$L$18:$O$25

②行番号:5

③列番号:3


の3つです。


ポイントは、基準が指定範囲の”左上角のセル”であること。
(上の例では「L18セル」が該当)


このセルを基準に「5行下がる(=23行)」「3列分右(N列)」と指示すれば、「N23セル(=吉田類)」が表示されることになります。


関数の仕組みはシンプルです。
上の図の「矢印の動き」でイメージできるようになると、関数の理解がさらに進みます。

MATCH関数

MATCH関数は、「探したいデータ」と「範囲」を指定すると、
指定した範囲の中で、「何番目のセルにあるか」を表示してくれる関数です。

スクリーンショット 2015 02 01 13 40 53


=MATCH(“吉田類”,$L$30:$O$30,0)
と入力しています。


MATCH関数の要素は、


①調査するデータ:吉田類

②調査する範囲:$L$30:$O$30

③検索の型:0(ピッタリ一致するものだけを表示)


の3つです。


上の例は、


「吉田類」は、「$L$30:$O$30」の範囲で、

何番目にあるか、を表示せよ、


という意味です。


ポイントは、「行」だけでなく、「列」にも使えること。

スクリーンショット 2015 02 01 13 55 14


「列」の場合は、一番上のセルを基準にして、何番目のセルにあるかを表示します。

INDEX関数&MATCH関数で、検索列の左にあるデータを表示

VLOOKUP関数の弱点だった、「調査するデータが、一番左の列にないと機能しないこと」を克服します。


サンプルの表を見て下さい。

スクリーンショット 2015 02 01 14 03 04


この表から、「名前」を調査するデータにして、その左側の列にある「職業」を表示させてみましょう。


大まかな方針は、INDEX関数を使って、

①範囲:表全体


②行:調査する「名前」のデータがある行番号


③列:「職業」が入力されている列番号


を入力することで、指定した「名前」(「倉本康子」さん)の、「職業」を表示させることを目指します。

MATCH関数で「行」、「列」を表示

まずは、調査する「名前」のある行を表示させます。


今回は「倉本康子」さんを探します。
調査するデータとして、”倉本康子”を入力。


名前が入力されている範囲は、「O5セル」から「O10セル」ですから、範囲を「$O$5:$O$10」に指定します。

=MATCH(“倉本康子”,$O$5:$O$10,0)

すると、

スクリーンショット 2015 02 01 14 15 23


「4」と表示されました。


次に、「職業」を表示させたいので、表の何列目に入力されているかを表示させます。ここも、MATCH関数です。


調査するデータは「職業」ですが、表の項目では「Career」になっていますから、 
調査するデータに”Career”を入力。


項目が並んでいるのは、表の一番上の行ですから、ここを範囲指定します。「$L$5:$P$5」ですね。

=MATCH(“Career”,$L$5:$P$5,0)

すると、

スクリーンショット 2015 02 01 17 00 50


「2」と表示されました。


これで、


・該当するデータ(今回は「名前」)の行

・表示させたいデータ(今回は「Career」)の列


をMATCH関数で表示することができました。


これを、INDEX関数に組み込めば完成です。


=INDEX($L$6:$P$11,MATCH($L$4,$O$6:$O$11,0),MATCH(M$6,$L$6:$P$6,0))


最初に、表全体を範囲指定して、MATCH関数で行・列を指定しています。


その結果が、こちら。

スクリーンショット 2015 02 01 17 12 13


正しく、「ファッションモデル」と表示されています。


難しく見えますが、大事なのはイメージです。


INDEX関数としては、③で示された、「行+列」の矢印の動きが該当します。


まずは、ココをおさえる。


その上で、INDEX関数に出てくる、「行」、「列」を調査するために、

①、②の矢印で表示された、MATCH関数の動き
がある。


と、分けてイメージするようにしましょう。

まとめ

VLOOKUP関数の弱点である、「検索列の左側にあるデータ抽出」も、INDEX関数&MATCH関数の組み合わせで、克服できます。
2つの関数の動きを、イメージできるようになることが、スムーズに使いこなすための鍵です。


<おまけ>
『おんな酒場放浪記』は出演者のキャスティングが絶妙です。
倉本康子さんが最大のヒットですが、それ以外にも、
棋士、ハーモニカ奏者、写真家、料理家、と「いったい、どこから探してきたんだ!」と言いたくなるような、渋いお酒好きがブックされています。

【セミナー情報】
実務で使えるExcel入門セミナー 基礎編

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする