「エクセル使いこなせていますか？」
サラリーマン歴13年で培ったエクセルスキルを基に、エクセルの使いこなし方を紹介していきます。
今回は、エクセル関数のうち最初につまずきそうな関数No.1(個人の感想)である「VLOOKUP関数」の【実践編】です。
例えば、次のような人にぜひ読んでもらいたいです。
- 使い方は分かったけど、使いどころのイメージが湧かない…
- VLOOKUP関数の検索方法で「TRUE」の使い方がいまいちわからない…
それでは一緒に見ていきましょう！
【実践編】VLOOKUP関数を使いこなすコツ
【基礎編】に続いて、今回は【実践編】ということで、実際にありそうな場面でVLOOKUP関数を使う方法について紹介していきます。
具体的には、
- VLOOKUP関数をコピペするときに注意すること
- 検索方法が「TRUE(または1)」の時の使い方
の2本立てです。
「絶対参照」か「相対参照」か意識してコピペ！
例えば、次のような店舗リスト(シート名「店舗リスト」)と販売リスト(シート名「販売リスト」)があったとしましょう。
そして、「販売リストのE列に店舗名を返すVLOOKUP関数」を入力してみましょう。
【失敗パターン】コピペするだけだど…
例の場合、販売リストに店舗コードが含まれていますので、店舗コードを検索値としてVLOOKUP関数で店舗名を返すようにすればよいことがわかるかと思います。
すなわち、E2セルには次のようなVLOOKUP関数を書けばよいことがわかります。
=VLOOKUP(D2,店舗リスト!A1:B8,2,FALSE) ※FALSEの代わりに0でもOK
うまく反映されましたね。あとは、E2セルのVLOOKUP関数をE3～E16セルにコピペすればいいはずです。早速コピペしてみましょう。
残念ながら、ただコピペするだけだとエラーが出てしまうのです。
何が間違いだったのか、エラーになったセルの関数を見てみましょう。ここでは、E6セルの関数を見てみます。
=VLOOKUP(D6,店舗リスト!A5:B12,2,FALSE) ※FALSEの代わりに0でもOK
どうやらE2セルに入力したVLOOKUP関数とは、検索値と範囲のセル参照がずれています。
検索値については、E列の各セルのすぐ左のD列のセルであって欲しいのですが、ここについては問題なさそうです。問題は範囲です。あるべき範囲よりも下にずれた範囲になってしまっていますね。
これは、範囲のセル参照が「相対参照」だったことが原因です。相対参照だと、コピペしたときにセル参照も一緒についてきてしまうわけです。
【成功パターン】相対参照・絶対参照を確認してからコピペ！
関数をコピペするときには、必ず相対参照がよいのか絶対参照がよいのか確認してから行いましょう。
今回の例では、範囲のセル参照がずれないようにしたいですね。すなわち、範囲のセル参照を絶対参照にすればよいのです。E2セルには次のような式が入るべきです。
=VLOOKUP(D2,店舗リスト!$A$1:$B$8,2,FALSE) ※FALSEの代わりに0でもOK
では、この式をE3～E16にコピペしてみましょう。
エラーなしで反映されましたね！試しに、先ほどエラーのため確認したE6セルの式を見てみると、範囲はずれていないことがわかります。
これで店舗名をVLOOKUP関数で間違いなく反映することができました。
検索方法で「TRUE」を指定する使い方
【基礎編】も含めて、ここまで検索方法が「FALSE(または0)」の場合、つまり完全一致する値を検索する方法のみ紹介してきました。一方で、検索方法が「TRUE(または1)」の場合は紹介してきませんでした。
というのも、個人的な経験からしても完全一致する値を検索するケースがほとんどで、近似値を検索するという場面に遭遇したことがないからなのですが、これを知っているのと知らないのでは、いざという時の対応に差が出るので一応押さえておきましょう。
VLOOKUP関数の検索方法を「TRUE」とするだけではダメ！
それでは、例えば次のようなケースを考えてみましょう。
とあるA君の期末テストが、左の表のような得点の状況だったとしましょう。この得点を基に、B先生は0～49点は不可、50～69点が可、70～79点が良、80点以上が優、と評価をつけるものとします。
このとき、C列に評価が反映されるようにVLOOKUP関数を書いてみましょう。
=VLOOKUP(B2,$E$1:$F$5,2,TRUE) ※TRUEの代わりに1でもOK
このような形で、検索方法を「TRUE(または1)」とすればOKです。うまく反映できましたね…なーんて、これで終わらせるわけにはいきません。
果たして範囲（右の表）はどうやって用意すればいいのでしょうか？？？
実は、検索方法を「TRUE(または1)」とする場合、範囲の作り方が非常に重要となります。それでは、そのポイントについて説明していきますね。
範囲の作り方のポイント
VLOOKUP関数の検索方法を「TRUE(または1)」とする場合の範囲の作り方のポイントは次の2つです。
- 目的のデータを、基準の値が小さい順に入力
- それぞれの目的のデータを得るための下限値を1列目に入力
では、ポイントに沿って範囲を作ってみましょう。
この例での目的のデータとは、「優・良・可・不可」です。そして、上記のポイント1によると、目的のデータの基準の値が小さい順にするということなので、「不可＜可＜良＜優」の順になりますね。これを入力すると次のようになります。
次に、「優・良・可・不可」の値を得るための下限値を考えてみると、優は80点以上なので下限値は80点、良は70～79点なので下限値は70点、可は50～69点なので下限値は50点、不可は0～49点なので下限値は0点、ということになりますね。これを範囲の1列目にくるように入力します。
このようにして、めでたく範囲が完成しましたね！この方法を覚えておくと、様々なことに応用できる…かもしれません。頭の片隅にでも置いといてください！
VLOOKUP関数を使いこなそう
ということで、VLOOKUP関数を使いこなすための2つのスキルをご紹介しました。
【基礎編】からいきなりジャンプアップしたように感じるかもしれませんが、今回紹介したスキルは重要となりますので、徐々に慣れていきましょうね。
そして、次回はいよいよVLOOKUP関数の【応用編】です。他の関数と組み合わせて使う方法などをご紹介します。バリバリ使えるようになりましょう！
コメント