【EXCEL関数】使い方色々!VLOOKUP便利技!

EXCEL

前回、VLOOKUPの使い方について学習しました。
まだ見てない人はここから見てね。

今回は前回の内容に付け加えて、
VLOOKUPをもっと便利に色々な場面で使えるようにする便利技を紹介いたします。

スポンサーリンク

1 複数条件から検索をおこなう

表の中にある情報を複数条件で検索し、検索結果として当てはまるものを表示します。

今回はこの表の「性別」と「毛の色」の複数条件に合致する名前を検索してみましょう。

まず初めに、少しVLOOKUPについておさらいです。
VLOOKUPは
「選択した範囲の先頭行を検索し、合致した情報を表示する」というものです。
なので、表の先頭行に検索したい内容が入力されていないといけません

複数条件の場合、
検索したい内容を『&』で繋いでしましましょう。

まず、表の先頭行に行を挿入し、条件を入力することができるセルを準備します。

次に、作成した先頭行に検索したい情報を入力していきます。
今回は「性別」と「毛の色」の2つなので、この2つの情報を&で結びましょう。

A2 =D2&E2

A2には「性別」と「毛の色」の2つの情報が合体した内容が入力されました。
下のセルにもコピーします。

表すべてに反映させたら、次は実際に検索を行ってみましょう。

シートに「性別」、「毛の色」を記入する欄。
検索結果として名前を表示する欄を作成します。
「性別」、「毛の色」の欄には、自分が検索したい内容を記入しておきましょう。
今回は「性別」は「オス」、「毛の色」は「黒」にします。

次に関数を入力します。
複数の条件を検索しますが、VLOOKUPを用いた関数式の作り方は特に変わりません。

例:I4で『指定した性別と毛の色に該当する名前を検索する』場合。

I4 =VLOOKUP(I2&I3,A2:F5,3,0)

条件に当てはまる「あるぱか1」が表示されました。

今度は「性別」を「メス」、「毛の色」を「黒」に変更すると、
「あるぱか3」が表示されました。

2 条件が含まれる内容を検索する

長い内容が記載されたセルと完全に一致する内容を検索するのは至難の業です。
どうせなら、少し楽に必要な情報だけを入力して、その内容と一部合致する情報をを検索しましょう。

検索内容は特徴欄の「足が長いか短いか」と「背が高いか低いか」の2つにします。

今回はIF関数を用います。

使い方はここから↓

A2  =IF(COUNTIF(F2,”“&$I$2&”“),IF(COUNTIF(F2,”“&$I$3&”“),1,0),)

関数式の内容:セルF2にI2の内容、およびI3の内容が含まれていれば1、そうでなければ0を表示する。

「〇〇を含む」とする場合

関数式に直接条件を入力する場合は”○○“となりますが、
セルを指定する場合は”*“&〇〇&”*”というふうに、

セルを『&(半角アンド)』で囲み(&セル&)、
『*(半角アスタリスク)』を『”(半角ダブルクォーテーション)』で囲んだもので(”*”)
さらに囲む必要があります。(”*”&セル&”*”)
(囲むが多い…)

事前に検索する内容が固定されているのであれば、直接書き込んだ方が楽です。

COUNTIFについて

COUNTIFは「該当するセルの個数を数える」というものになります。

つまり、指定したセルに条件の内容が含まれていれば戻り値は1となり、
含まれていなければ0となります。

EXCELにおける真偽の判断は、返された値が0か1以上かによって決まります。

真偽については、返された値が0であれば偽、1以上であれば真となるのです。

今回の場合、COUNTIFによって返された値が1以上の場合、IFの内容は真となる、という仕組みになります。
もっと簡単に言えば、COUNTIFで指定した範囲内に指定した条件が1個以上あれば、IFの結果は真になる、ということになります。

入力した結果を見てみましょう。

A2は条件に当てはまっていませんので、判定は偽、0が戻り値として表示されます。
今度は関数式の内容を下のセルにコピーしていきましょう。

条件に合うA3は1が表示されましたね。

では、VLOOKUPを用いて検索しましょう。

VLOOKUPで1を検索し、同じ行の名前を検索します。

I4  =VLOOKUP(1,A2:F5,3,0)

戻り値として「あるぱか2」が表示されました。

3 複数条件+条件が含まれる内容を検索する。

最後は今までのやり方をすべて組み合わせてみましょう。

記入した「性別」「毛の色」「特徴」すべてと合致する名前を表示させます。
まず、各項目で検索したい内容を記入しましょう。

次に関数式の入力です。

A2 =IF(D2=$I$2,IF(E2=$I$3,IF(COUNTIF(F2,”“&$I$4&”“),1,0),0),0)

関数式の意味:もし、「性別」の検索内容が合致しており、「毛の色」の検索内容も合致しており、「特徴」に検索内容の一部が含まれていたら1を表示、それ以外だと0を表示する。

A2は内容がすべて合致しているので1、それ以外は合致していないので0が表示されました。

次にVLOOKUPで検索しましょう。

やり方は2の内容と同じなので省略します。

すべての条件に合致している「あるぱか1」が表示されました。

条件を変えても表示されました!

どの方法もひと手間必要にはなりますが、
使いこなすことができれば今後大いに役に立ちます。

習得して多くの方よりも1歩先に進んでください!

コメント

タイトルとURLをコピーしました