IF関数とVLOOKUP関数を組合せて条件付きで検索するにはどうしたら良いの？
やり方が複雑でいまいち理解できない
VLOOKUP関数で検索に引っかからない時のエラー対策がしたい
検索結果が「エラー」だった場合の表示のさせ方が知りたい
こういった疑問に答えます
✔関数同士の組合せのテーマ
①関数同士を組合せる時はそれぞれの結果をくっつけるのが正解です
②エラー対策にはエラーになる条件に合わせて設定するのが正解です
この記事を書いているのはOffice歴約20年
現在、エステサロン経営をしながらITを使った現場のマネジメントをしています
「関数同士の組合せ」に関して実体験に基づいて解説しています
関数同士を組合せる時はそれぞれの結果をくっつけるのが正解です
関数のネストを理解しやすくするには、それぞれの関数の結果を確認した後で2つの関数をくっつけるのが一番効率的です
なぜなら、ネストが分かりづらい最大の原因は「段階を踏んで組合せることを意識できていない」ことだからです
実際に「IF関数とVLOOKUP関数のネストのさせかた」について具体的に見ていきましょう
今回は左側のキャンプ場の一覧表から、右側の設備名検索の表に「施設名をリストから選択」することで「キャンプ場の名前」と「キャンプ場がある市町村名」を自動で表示することが目的です
まずは、IF関数とVLOOKUP関数のネストの方法についてもう少し分かりやすい例で基本を押さえましょう
今回の例は左の「果物の種類と色の表」を基に右側の「果物を選択すると色が自動的に表示される表」を作ってみます
これはVLOOKUP関数が使えれば特に難しくは無いですね
今回はE2のセルに「=VLOOKUP(D2,$A$2:$B$11,2,0)」と入力してあります
確認のために関数の内容を日本語にしてみましょう
=VLOOKUP(D2セルの内容を検索します,A2～B11セルの範囲で,2列目を見てね,調べたいのはD2の文字と完全に一致する文字があったらね)
こんなところでしょうか
VLOOKUP関数はむずかしそうに見えますが、順番通りに設定すれば良いので落ち着いて設定してみましょう
詳しくはVLOOKUP関数をこちらで詳しく説明しているので参考にしてみて下さい
次に果物の名前が「メロン」の時だけ検索結果を表示するように設定してみましょう
大切なのは慌てずに一つずつ結果を確かめることです
特に関数を使い慣れないうちは混乱しやすいので、使いこなせるまで基本を守るようにするのが上達の近道ですよ
では条件を設定したいのでIF関数を設定してみましょう
ただし、VLOOKUP関数も後で使うので取っておきましょう
Excelで関数を取っておく方法も解説するので一緒に作業をしていきましょう
まずは、E2セルの関数を「=を含まないようにコピー」しましょう
「＝」を含むと他のセルに移動した時に自動的に式の内容を変更してしまうので「=以降の関数の内容」をコピーするのが大切です
コピー後は「F2セル」に貼り付けておきましょう(仮置しておきます)
では、次にIF関数を使って「D2セルがメロンだったら」という式を作っていきましょう
IFの結果は分かりやすければ何でもOKです
こんな感じでOKです
今回のIF関数の内容を日本語にすると「=IF(D2=”メロン”,“検索結果を表示”,“何も表示しない”)」なので
「=もし(D2セルの内容が「メロン」だったら,「検索結果を表示」と表示してね,そうでなければ「何も表示しない」と表示してね)」
ということになります
感がいい人はココで気づいたと思いますが、「検索結果を表示」という条件にさっきのVLOOKUP関数をくっつければ完成すると思いませんか？
最初に条件を設定して「条件に合った場合はその結果を表示する関数をくっつける」これがネストのコツです
では、今作ったIF関数の式の中にさっきのVLOOKUP関数の式を貼り付けちゃいましょう
やり方は、F2セルの式を「”検索結果を表示”」の部分に貼り付けるだけです
こんな風に設定出来ればOKです
式は「=IF(D2=”メロン”,VLOOKUP(D2,$A$2:$B$11,2,0),”何も表示しない”)」
つまり、D2のセルが「メロン」だったら色を表示して、そうでなければ「何も表示しない」という「条件分岐と検索を組合せる」ことが出来ました
こんな風にネストを混乱せずに組合せる方法は、一つずつ作った関数を後でくっつけるのが混乱しないためのコツです
次はキャンプ場の検索内容を基にもう少し応用をきかせてみましょう
繰り返しになりますが、関数のネストを理解しやすくするにはそれぞれの関数の結果を確認した後で2つの関数をくっつけるのが一番効率的です
エラー対策にはエラーになる条件に合わせて設定するのが正解です
VLOOKUP関数の悩みの一つにエラーが表示されて困るというケースがありますが、エラーになる条件を先に対策しておくことが正解です
なぜなら、先にエラーになる原因の対策をしておくことで数式が分かりやすくなるからです
具体的な例をキャンプ場の検索表を使って見ていきましょう
まずは、もう一度表を確認しておきましょう
この表が検索先の表ですね
この表が施設名を検索するための表です
F5のセル(施設名)を変更することで「キャンプ場」と「場所」を自動的に表示してくれるように設定してみましょう
まずは「キャンプ場にエラーが表示されないための数式を設定」してみましょう
「G5セル」に「=IF(F5=””,””,VLOOKUP($F$5,$B$5:$D$14,2,0))」と入力しています
分かりやすいように日本語化してみましょう
「=もし(F5セルが空白だったら,空白を表示してね,そうじゃない時はVLOOKUP(F5セルの内容を見て,B5セル～D14セルの範囲で,2列目の値を表示してね,F5セルと全く同じ内容だったら))」
こんな感じでしょうか
基本的なネストの考え方は最初に解説した通りです
２つの関数を別々に作っておいて後でくっつけるだけですね
今回のコツは「=IF(F5=””,””,」の部分です
先にエラーの原因になるF5セルが空白だった場合は、空白を表示してねという対策を立ておけば数式が長くなっても分かりやすいですよね
ちなみにこの対策をしていないとこんな風にエラーになります
こんな風に「施設名が空白」だと調べる内容が無いのでエラーになってしまいます
設定した関数は「=VLOOKUP($F$5,$B$5:$D$14,2,0)」なのでIF関数を抜いただけです
これだとかっこ悪いので「=IF(F5=””,””,」を頭につければ短くて簡単なので覚えておくとすごく便利ですよ
続いて検索結果が「0」と表示される場合の対処法も合わせてみておきましょう
「0」と表示されるのはこんなケースです
この場合「検索先の場所名が空白になっている」ことが原因です
さっきとは逆ですよね
これがちょっと厄介です
なぜなら、検索先ということは検索した結果を判断しないといけないからです
いまの数式は「=VLOOKUP($F$5,$B$5:$D$14,3,0)」なので普通は何も問題ありません
問題は検索した先が空白かどうかを判断するには式が複雑になってしまうことです
ただ、落ち着いて対処すれば問題ないので慌てずに対処してみるとこんな風になります
今回の式を分かりやすいように日本語にしてみましょう
「=IF(VLOOKUP($F$5,$B$5:$D$14,3,0)=0,”場所が空白です”,VLOOKUP($F$5,$B$5:$D$14,3,0))」
「=もし(VLOOKUP(F5セルの内容を検索して,B5～D14セルの,3列目が,0)=0だったら,「場所が空白です」って表示してね,そうでなければVLOOKUP($F$5,$B$5:$D$14,3,0)の結果を表示してね)」
といったところでしょうか
つまり、VLOOKUP関数が２回出てくることになります
理由はVLOOKUP関数の結果が「０」になるかどうかを先に調べておく必要があるからです
要は調べた先に問題がある場合は、調べた結果を基に判断する必要があるので今回のケースのように同じ内容のVLOOKUP関数を２回設定することがあります
実務でも上記の２パターンは非常によく出てくるので対策方法を知っておくとすごく便利です
繰り返しになりますが、エラーになる条件を先に対策しておくことが正解です
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
コメントを残す