VLOOKUPは検索によく使われる関数です。しかし、弱点もいくつかあります。その中でも、「検索条件に一致するセルが複数ある場合は、一致するセルの中で一番上にあるセルを抽出する」ということがネックになることがよくあります。
今回は条件に一致する複数の検索結果をまとめて表示する方法を見ていきます。
実際に下の表で見てみます。
今回は大阪府出身者を検索しています。該当するのは、「佐藤 拡」さんと「飯田 小枝子」さんの2名です。しかし、検索結果欄には「佐藤 拡」さんしか入っていません。
これは、VLOOKUP関数は『該当するセルで、1番上に来るセルを選択する」という仕様になっているのが原因です。しかし今回のように該当者が複数人いる場合には使えないのでしょうか？
今回はVLOOKUP関数で複数の該当件数を取り出す方法を見ていきます。なお、今回の方法はINDEX関数+MATCH関数でも使えます。併せて確認してみてください。
今回はタイトル通り、VLOOKUPとCOUNTIFを使いますのでまだよくわからない方はこちらからご覧ください。
今回も練習用ファイルを貼っておくので、活用してみてください。ちなみに今回のレベルがクリアできれば、中級者といってもいいレベルだと思います。難しい部分もあると思いますが頑張って解説していきますのであきらめずにやってみましょう。
VLOOKUPに「違うもの」と認識させる
先程も言いましたが、「VLLOKUP関数は同じ検索値で2つ以上の情報があった場合、1番上にある情報を取り出す」という仕様です。
なので、「同じ検索値」ではなく「個別の検索値」として認識させてしまえばいいのです。
手順１、同じ都道府県出身の人が何人いるかをCOUNTIF関数で洗い出す
基本的な考え方としては、何人いるかというよりも『該当するのは何人目』なのかという考え方です。例えば「大阪府1」「大阪府2」という形で別の検索値にするという意味です。
A列に1列挿入します。
挿入した列にCOUNTIF関数で都道府県毎に数をカウントしていきますが、先程言ったように「○○県何番目」という意味にするために以下のように数式を入れます。
＝COUNTIF([相対参照で]C2～C2の範囲でC2の個数を調べる)という意味になります。これはいくつかに分解して考えるとわかりやすいです。
- 最初だけ相対参照にしていることで、COUNTIFの個数を数える選択範囲が増えるのに対応している。
- 「～C2」の部分は、基準点は1で説明したように固定されたが「～○○まで」の部分を固定しないことで、「～C3」「～C4」とオートフィルで増やしていける。
- 検索条件をC2にすることで、オートフィルをした際に検索条件もC3、C4と可変になり初めて出てきたものには1、2回目に出てきたものには2と回数を記録する機能となる。
実際にやってみると以下のようになります。
複数回出ている都道府県には色掛けしてみると、それぞれ2回目が出た時には2、3回目が出た時には3という数字が入ります。これで「○○県何番目」が作れるようになります。
手順2、「＆」を使って「○○県1」と都道府県に番号を振る
今度は、演算子の「＆」を使って、都道府県名と何番目という数字を合体させて1つのセルに表示させます。もう1列分追加をして、そこに今度は
という数式を入れることでA2は「東京1」、オートフィルで表の1番下まで引っ張ることでA14は「東京3」となっていればOKです。
これでA列に入っている情報を検索値にすることで、「東京都」ではなく「東京都1」「東京都2」といった形で別の情報が入ったセルとしてVLOOKUP関数の検索を掛けることができるようになります。
「＆」を使って検索値を可変にする
ここまでくれば、あとは簡単です。
このような形で、検索結果を抽出する数式を先程の表の右側にでも用意しておきましょう。今回はこちらの表を検索結果一覧表と呼んでいきます。
用意が終わったら次の手順に入ります。
手順3、検索結果の合計人数が全体で何人いるのかを表記する
正直これを飛ばしてもいいのですが、あったほうが表としてはきれいになるのでやっておきましょう。
まず、新しく作った検索結果一覧表のJ2セルに「○○出身」が何名いるのかをCOUNTIF関数と演算子「＆」を使って確認します。なお今回は東京都出身の人を検索していきます。
これをJ2セルに入れるとH2セル(今回は東京都)出身の人を探します。続いて、
これをH4セルに入れてオートフィルで検索結果一覧表の一番下まで引っ張りましょう。以下のようになっていれば完璧です。
今回、東京都出身の方は3名なので該当者の列は3までの表示があれば困りません。わざわざ該当者の欄に番号を振らないでも、自動的にその番号までしか表示されなくなります。
逆に該当者のところに数字が入り、出身地や氏名などの他の項目に数字が入っていないと数式のエラーなのか、該当者が本当にいないのか表を見ただけではぱっと見ただけでは判別しづらいです。
こういった部分までこだわると、見やすい表を作るヒントになります。
手順4、演算子「＆」でVLOOKUP関数の検索値を可変にする
そのまんまです。今回の検索結果一覧表で検索したいのは出身・氏・名なのでそこにVLOOKUP関数を入れていきます。
H2セルの出身地と該当者の番号を「＆」で結合させると、手順2で作った「○○県何番目」という形になります。なのでそれを検索値に当てはまればと「東京都1」「東京都2」「東京都3」をそれぞれ検索できます。
あとは検索範囲$A$2:$F$14のなかで、出身地なら4列目、氏名なら5・6列目となっているのでオートフィルで引っ張った後に列の数値だけ変えれば該当者１の項目は完成になります。
そして、今回は該当者が3名しかいませんし該当者は出身によって変化します。
また該当者の数も必要分しか表示されないのでVLOOKUP関数がエラーの時は空欄になるように、IFERROR(VLOOKUP関数,””)とすることで該当者が不在の時は空欄になるようにしておくと該当者数ともリンクしてきれいな表にすることができます。
見た目が美しくないので、最後にAB列を非表示にしてしまいましょう。列の非表示は列のところで右クリック→非表示を選べば隠すことが可能です。
(なお、数式に関連するデータなどが残っているので削除してしまうと検索結果一覧表が機能しなくなります。ご注意ください)
今回のようにVLOOKUP関数とCOUNTIF関数、更に演算子「&」などを組み合わせることでExcelで出来ることの幅は広がっていきます。
いろいろな組み合わせを作れるようになることが、初級者から中級者へのステップであることは間違いないので、いろいろな関数やExcelの機能を身に付けていきましょう。
