2017年05月14日

VLOOKUPとAND関数で複数の検索条件からデータを抽出して表示させてみた / Excel / 関数の応用

Excelの関数を組み合わせて複数の検索条件からデータを抽出して表示させてみます。
VLOOKUP関数とAND関数を使用します。

この様なフォトブック(デジカメのデータから写真本を作るサービス)の「価格表」があります。
本の形、大きさ、ページ数で値段が変わってきます。
20170514_01.JPG

別のシート「価格抽出」でこの様に“形状”、“サイズ”、“ページ数”の3つの検索条件を入力すると、「価格表」から価格をVLOOKUP関数を使って表示させるようにしてみます。
20170514_02.JPG

「価格表」の左端に列(“条件一致”)を追加して論理関数から“AND”を入力します。
20170514_03.JPG

これは、「価格表」に入力されているデータが、「価格抽出」で入力された条件と一致するかどうかを調べます。“AND”関数を使うのは、3つの条件が全て一致しないといけないので“AND(且つ)”を使用しています。間違って“OR(又は)”を使ってしまうと、1つでも合っていると条件一致とみなされてしまいます。
「価格表」の一行毎のデータ(レコード)の“形状”、“サイズ”、“ページ数”が「価格抽出」の“形状”、“サイズ”、“ページ数”と等しいか(“=”か)調べて、それを“AND”で繋げます。
3つ全て一致していると“TRUE”、一つでも違っていると“FALSE”が表示されるというわけです。
20170514_00.jpg

実際の引数の設定はこうなります。
ここの入力した式は「価格表」の最後の行までコピーするので「価格抽出」の“Sheet2”の参照セルは“$”を付けて絶対参照にして固定しておきます。
20170514_04.JPG

するとこの様に、1行目は“FALSE”になりました。
サイズは一致してますが、形状とページ数が違ってますよね。
20170514_05.JPG

で、この式をコピーしていくと1つだ一致したデータが見つかりました。
「条件付き書式」で“TRUE”の時に背景色を付ける様にしておくと目立って分かりやすくなりますね。
20170514_06.JPG

で、ここまで出来たらあとは「価格抽出」で“VLOOKUP関数”を使って「価格表」の“TRUE”のデータの“価格”データを表示させてやればいいだけです。
つまり、VLOOKUP関数の設定できる検索条件は1つだけなので、その前にAND関数で複数の条件での検索結果を調べておいてしまうわけです。
20170514_07.JPG

引数の設定です。
「検索値」は手入力で“TRUE”を入力します。
20170514_08.JPG

「範囲」は「価格表」のデータの範囲を指定します。
20170514_09.JPG

「列番号」は“価格”が入力しされている列の位置(5番目)
「検索結果」は“FALSE”を入力します。
20170514_10.JPG

と、この様に“\4,500”と表示されました。
20170514_11.JPG

「形状」を“正方形”に変えてみます。
このように予めドロップダウンリストを設定しておくと選択しやすくなりますし、入力ミスによる誤検索も防止できます。
20170514_12.JPG

と、一瞬で(当たり前か。。)“\3,000”と表示されました。
20170514_13.JPG

「価格表」を見てみると。。。“\3,000”が“TRUE”になってました。。
20170514_14.JPG



関連ページ
VLOOKUPで列方向の検索をして表示させてみた
VLOOKUPとCOUNTIF関数で複数の重複データを抽出して表示させてみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
ラベル:VLOOKUP AND
posted by haku1569 at 11:09| Excel関数応用 | このブログの読者になる | 更新情報をチェックする