Excel

Excel VLOOKUP関数【応用編】条件を満たすデータの一覧表を表示する

2021年7月22日

person holding a camera

多くの場面で役に立つVLOOKUP関数の応用例を紹介しています。VLOOKUP関数を使いこなすことができれば、タスク処理が効率化すること間違いありません!!

VLOOKUP関数【基本編】は後日まとめるので、初心者の方はまずそちらを参考にしましょう!!

VLOOKUP関数とは

VLOOKUP関数とはあらかじめ用意した表から検索し、データを取り出す関数である。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法] )

下の画像ではB4:D9をもとのデータベース(表)として、用意してある。この際、H4やI4にVLOOKUP関数を入れておくことで、F3の検索欄に管理番号を入力すると、データベースから適当なデータがH4やI4に表示されるようになっている。

現在は検索欄(F4)に、管理番号「3」が入力されているので、右のデータベース(表)より検索結果として商品名:なし、価格:¥130円が与えられている。検索欄(F4)の値を変えることで、任意の検索結果を表示できるようになっている。

VLOOKUP関数基本

H4には次のように関数が挿入されている。

=VLOOKUP(F4,$B$4:$D$9,2,FALSE)

検索値として、F4の管理番号、範囲としてB4:D9を選択している。また、列番号は今回検索したいデータが商品名なのでB4:D9のうち二列目に当てはまるので2を与えている。最後の検索方法について、FALSEとなっているがこれは検索値とデータベース(表)が完全に一致した場合のみ結果を返すという意味となっている。

つまり、I4の価格の検索結果ではH4に入力された関数のうち列番号を「3」に変更したのみである。これは、左のデータベース(表)では3列目に価格が記載されているためである。

このようにVLOOKUP関数は一つのキーとなる情報から、今回の商品名や価格のような登録された情報を返すことができる関数である。応用方法次第では、複数の関数と組み合わせて使用することでExcel作業をより効率化できる。今回は、この応用例の一例として、VLOOKUP関数により、条件に合うデータの一覧表を作成しましょう。

VLOOKUP 関数 - Office サポート

VLOOKUP関数で一覧表作成

今回作る機能の紹介

VLOOKUP関数では、表示したいデータのキーとなるものを一つ入力することで、一つの回答を表示してくれます。しかし、それでは検索したいデータベース(表)の中に条件を満たすデータが複数あっても、単純にVLOOKUP関数を使用するのでは、データベース(表)のなかにある最も上でかつ、条件を満たすものしか返してくれません。

文章だけ見ても、伝わりにくいと思うので画像を用いて説明します。

例えば、この下の画像では商品名を検索キーワードとています。しかしながら、左のデータベース(表)をみると商品名に「りんご」が二つあるのにもかかわらず、価格:¥100、産地:青森県とデータベースの上側の「りんご」のデータしか返していません。

そこで今回は以下の画像のようにデータベースの下側にある「りんご」のデータも表示できるように、データベースの条件を満たすデータを一覧表示できるようにしていきます。

使用する関数

  • VLOOKUP関数

  • COUNTIF関数
    条件を満たすセルの数を数える関数
    ※詳細な使用方法は後日、まとめます!!
  • IFERROR関数
    関数がエラーを返した場合の表示を設定する関数

実際につくってみる

以下のような3つのステップの順で実現していきます。

ステップ3は補足的な機能ですが、エラー処理は資料の見やすさを向上してくれます。

ステップ1・2を中心にまずは見てみましょう。

データベース(表)の検索に使用する値を作成する

VLOOKUP関数は前述したとおり、一つの検索値に対して、一つの値しか返すことができません。データベースに同様のデータがあった場合は、上側にあるデータが適応されてしまいます。こうした事態を防ぐために、はじめにデータベース(表)を少し工夫します。

データベースの検索値に使用するデータに重複が起きなければ、複数データがあっても区別して検索することができます。したがって、以下の画像のように検索に用いるデータは、データベースの中で何個目に出現したのかを表す列(C列)を追加します。わかりやすくするため、データの数を少し増やしています。

C4には、次のような関数を入力しています。

=COUNTIF($B$4:B4,B4)

関数の第一引数である範囲を $B$4:B4 にすることで、データベース(表)の先頭から現在のセルの行までを選択するようにしています。そして、第二引数は、数えるデータのため、B列のデータを入れています。

次に、商品名と前述の「何個目」を表すデータを&を用いて結合します。すると、検索するべきデータの値に重複がなくなります!下記の画像ではD列にて商品名と何個目のデータを結んでいます。

検索結果一覧の準備

検索結果一覧は、VLOOKUP関数を用いて表示します。データベースから検索される値の重複もなくなりましたが、このままでは一覧表示はできません。

いままでは、VLOOKUP関数にいれる検索値はH4を参照するので問題ありませんでしたが、検索データには「何個目」の値も結び付けられているので、 VLOOKUP関数を用いるにも、検索値に「何個目」かという数値を結び付ける必要があります。

したがって、検索結果一覧の表にまず、「No(先頭からの番号)」を追加します。こうすることで、下記の画像のようにK4のVLOOKUP関数の検索値は$H$4&J4とできます。検索欄の商品名がりんごになっているので、K1では「りんご1」のデータが表示されていることがわかります。

また、VLOOKUP関数の範囲はデータベースのD列が先頭になっていることに気を付けましょう。

検索結果がない場合のエラーを取り除く

ここまでで、おおまかの作業は完成です。しかしながら、検索結果一覧に表示されているエラーが少し邪魔ですね。最後に仕上げで、このエラーを消せるようにIFERROR関数でエラー処理をしましょう。

ここではIFERROR関数を使用します。K4には下記のような関数を挿入します。

=IFERROR(VLOOKUP($H$4&J4,$D$4:$F$13,2,FALSE),"")

IFERROR関数の第一引数にはステップ2で作成したVLOOKUP関数が入力されています。第二引数は、第一引数がエラーの場合に返す値になります。今回は空白を返したいので、""となっています。

以上より、下記の画像のようになります。

しっかりと商品名「りんご」の価格と産地を一覧表示できています。当然、検索値を「みかん」に変更すれば、結果も変化します。また、ステップ2の段階と異なり、エラーがなくなったのですっきりしました!!

 VLOOKUP関数を使いこなして一覧表示しよう!!

今回は、VLOOKUP関数を使って条件を満たすデータの一覧表を表示する方法を紹介しました。名簿からクラス別名簿を作成や部署別名簿にするなど、様々な所属で分けたいときや、フィルタ機能を使用できないときに効果的です。

  1. COUNTIF関数を使って、検索されるデータに重複がないようにする

  2. VLOOKUP関数の検索値に、ナンバリングを結合する

  3. エラー処理をする

皆さんもぜひ使ってみてください!!

おすすめ記事一覧

1

Uber Eats の配達パートナーの登録は終わったけど実際に配達ってどうやって行えばいいの?ってことがあるかもしれません。そこで今回はUber Eats での配達をどのように行うか、最低限必要なことをピックアップしていきます。

アイキャッチ画像 2

2021年最新のWi-Fiルーター(無線LAN)の選び方や知っておくべき知識を基礎からあったらよい機能まで紹介しています。ルーターには多くの機能が搭載されているので、自分の使い方に合った機能を選ぶ際にぜひご確認ください!

ピアノと楽譜 3

動画投稿にあたって音楽関係の権利に気を付けるべき理由を踏まえて、おすすめ音源サイトとそれらの探し方をまとめています。

-Excel
-, , ,