大量にあるデータの中から必要な情報を取り出したい時、1つ１つを見比べて手作業で探してしまっている…という方！手間がかかり、作業効率が非常に悪いですよね。
実はその問題、『VLOOKUP』関数という機能を使えば、一気に解決することが可能なんですよ。
そこで、この記事では、関数に苦手意識を持っている方でもすぐにマスターできるよう、「VLOOKUP関数の使い方」を画像付きで解説したいと思います。Excelだけでなく、Google社のスプレッドシートでも活用できる方法ですので、ぜひマスターしてはいかがでしょうか。
それではまず初めに、VLOOKUPで何ができるのかを簡単にご紹介したいと思います。
【VLOOKUP】でできること
Microsoft社によると、VLOOKUPはこのように解説されています。
VLOOKUP は、テーブルまたは行別の範囲で検索する必要がある場合に使用します。 たとえば、部品番号で自動車部品の価格を検索したり、従業員 ID に基づいて従業員名を検索したりします。
つまり、大量にあるデータの中から特定の情報を抽出したい際、VLOOKUP関数を使うと、一瞬でデータが弾き出されるというわけです。
すごい便利な機能ですよね。
データの数が数個〜数十個程度であれば、わざわざ関数を使う必要もありませんが、数百、数千になってくると、調べる作業だけで1日を無駄にしてしまいます。
VLOOKUP関数を知っているだけで、その時間を一瞬に短縮することが可能ですので、どんどん活用して、作業効率をあげていきましょう。
【VLOOKUP】の使い方
それでは早速、VLOOKUPの使い方と具体的な方法を紹介していきます。
まず初めにVLOOKUP関数は下記のような書式を使います。
=VLOOKUP(検索する場所,返す値を含む範囲内の列番号,近似値の場合1/TRUE or 完全一致の場合 0/FALSE)
【利用方法】
- 検索値の選択します
- 検索するセルの範囲を選択します
- 指定した範囲の列番号を指定します
- 必要に応じて近似値か、完全一致か指定します
【具体的な使い方】
今回は、画像右側の表にある「単価」列（F列）を埋める作業を行います。
手作業の状態だと、「りんごは240円」、「いちごは210円」…と左の表を見ながら1つ1つ空欄を埋めていくしかありませんでしたが、VLOOKUP関数を使用すれば、一瞬で単価を検索することが可能になります。
では、具体的な方法を見ていきましょう。
1.検索値の指定
まずは検索値の選択です。
検索結果を返したいセルを指定して、【=VLOOKUP()】を入力。
()内にカーソルを合わせクリックすると、検索値を指定できる状態になるので、検索値を指定しましょう。
今回は、フルーツの単価を算出したいので、検索値は商品名です。
2.検索するセルの範囲を選択
次に検索する範囲を指定しましょう。
各フルーツの単価を出したいので、選択範囲はA2〜B5を入力します。
この場合に注意したいことは、選択範囲が固定されるよう、【$A$2:$B$5】のように「$」をつけて絶対参照を行うことと範囲の間に必ず「:」を入力することです。
いずれか1つでも入力漏れがあると、エラーになるので注意してください。
絶対参照をしていないと、選択範囲がずれてしまうため、正しい検索結果が反映されません。そのため今回は、【$A$2:$B$5】の範囲を選択しています。
3.指定した範囲の列番号を指定
次に、列番号の指定を行います。
先ほど指定した選択範囲には商品名の列と、単価の列が含まれています。この2列のうち、算出したいのは単価の値なので、ここでは2列目の【2】を列番号として指定しましょう。
4.必要に応じて近似値（TRUE）か、完全一致（FALSE）か指定する
必要に応じて、近似値か完全一致かを指定することができます。
検索値に近い値も結果に反映させたい場合は、【TRUE】を入力。
完全一致のみ反映させたい場合は、【FALSE】を入力しましょう。
ここでは「りんご」という完全一致の言葉を反映させたいので【FALSE】を入力します。
最後に【）】で閉じ、Enterキーを押すと、りんごの単価が表示されました。
全てに単価を反映させたい場合は、1つずつやっていくと時間がかかり、作業効率が悪いので、手間を省くため「オートフィル機能」を活用しましょう。
F2のセルの右下にマウスポインターを合わせると、太文字の十字架【＋】が表示されます。それを反映させたいセルのところまで、ドラッグ＆ドロップをすると、全ての単価を反映することが出来ます。
【VLOOKUP関数】の応用編
①商品名の反映方法
下のような画像は商品番号・商品名・値段が表になっています。もし商品名を忘れてしまった場合に、何百、何千ものデータの中から1つ1つ探すのはすごく時間がかかります。
そこで【VLOOKUP関数】を使い、商品番号を打つと商品名が反映するといった画期的なことができます。その方法は先ほどと同様に
B4のセルに【=VLOOKUP(B4,$G$4:$I$12,2,FALSE)】と入力します。
改めて説明していきます。
- VLOOKUP関数で求めた値を表示したいセル（ここではC4のセル）を選択して「=VLOOKUP」と入力します。
- 「(」を入力し、［検索値］となるセル（ここではB3のセル）を入力します。クリックも可能です。
- 「,」を入力し、［範囲］となるセル（ここではG4～I12のセル）を入力します。ドラッグ＆ドロップでも選択可能です。
- 「,」を入力し、［列番号］を指定します。ここではH列目は指定範囲の2列目にあたるので、「2」と入力します。
- 「,」を入力し、［検索の型］として「FALSE」を指定します。これで［検索値］と完全一致する値のみが検索されるようになります。
- 「)」を入力して［Enter］キーを押し、VLOOKUP関数の数式の入力を完了です。
Enterキーを押すと、商品名が反映されました。
商品番号を変更して全て反映されているか、下の画像のように確認しましょう。
商品名を忘れてしまったときに非常に便利な機能と言えます。
②値段の反映方法
次に商品と共に値段も表示されたら、より便利ですよね。
その方法は簡単です。
先ほど入力した関数の列目番号「2」を→「3」に変更するだけです。
これは、求めたい値段（I列）が指定した範囲の3列目にあたるので「3」と入力します。
Enterキーを押すと、値段も反映されました。
商品番号を変更して全て反映されているか、下の画像のように確認しましょう。
【#N/Aエラー】が出た時の対処法
VLOOKUP関数を使用してエラーが発生した場合、下の画像のように検索結果には「#N/Aエラー」と表示されます。
この場合、なにかしらどこかに間違いがあるということを示しています。
今、一度間違いがないかじっくり確認しましょう。
下記に間違いが起こりやすい項目をまとめましたので、間違いがないかを確かめて見てください。
- 検索値を1番左の列に置いていていない
- 範囲を指定した際、相対参照になっている
- 検索値の入力間違い
- 「,」「$」「:」「(」「)」などの記号が抜けている
①検索値を1番左の列に置いていていない場合
検索値が1番左の列になく、2番目以降にある場合、正しい結果が表示されません。検索値の場所を左側に変えることでこのエラーは解消されます。
この画像の場合、B列にある商品名をA列に移動させると反映されます。
②範囲を指定した際、相対参照になっている場合
範囲を指定後、絶対参照せずにオートフィル機能を行うと、選択範囲が1行ずつずれてしまいます。これは、数式のセル指定が相対参照になっているからです。
LOOKUPの参照範囲を絶対参照にするとエラーは解消されます。
そのためには、参照範囲のセルに【$】を付けましょう。
- （誤）=VLOOKUP(E3,A2:B5,2,FALSE)
- （正）=VLOOKUP(E3,$A$2:$B$5,2,FALSE)
ちなみに使用しているOSがWindowsで「F4」キーを使用している場合は、参照範囲の部分にマウスポインターを合わせて「F4」キーを押すだけで絶対参照になります。
【絶対参照と相対参照の違い】
ここで先ほど出てきた「絶対参照」「相対参照」という言葉について、簡単に説明したいと思います。
「相対参照」とは、例えばB1のセルにて「=A1」と入力すると、Excelは「マウスポインターから見て一つ左に存在するセル」と認識しています。なのでB1のセルをB2のセルにコピーすると一つ左のセル（=A2）に数値がずれます。
対して「絶対参照」とは、「$」を付けて「$A$1」と入力すると、「どのセルから見てもA1セル」と認識させることができます。
【行または列のみに「絶対参照」にする】
「絶対参照」を行または列のみに指定することも可能です。オートフィル機能を活用した際に、行はずれてほしくないけど、列は相対参照で自動的にずらしたい場合などに便利な方法です。
◇「行」のみに絶対参照にする
- （誤）=VLOOKUP(E3,A2:B5,2,FALSE)
- （正）=VLOOKUP(E3,A$2:B$5,2,FALSE)
「行」のみに絶対参照をすると縦方向の「列」のコピーは選択範囲が固定されて移動しませんが、横方向の「行」のコピーは相対的に選択範囲が移動します。
◇「列」のみに絶対参照にする
- （誤）=VLOOKUP(E3,A2:B5,2,FALSE)
- （正）=VLOOKUP(E3,$A2:$B5,2,FALSE)
「列」のみに絶対参照をすると横方向の「行」のコピーは選択範囲が固定されて移動しませんが、縦方向の「列」のコピーは相対的に選択範囲が移動します。
③検索値の入力間違いの場合
検索値に間違いがないかどうかを確認しましょう。
上の画像をよく見ると、A2のセルのりんごが大文字のローマ字「RINGO」の表記になっています。
このような場合は「りんご」と「RINNGO」は同じではないので、「#N/A」のエラー表示になるのです。
A2のセルを右の表と同じひらがなの「りんご」に変えるとエラーは解消されます。
このように検索値を完全一致(FALSE)で指定している場合は、1文字でも間違っていれば結果が反映されません。よくある多い間違いは、英語の表記ミスや半角と全角の表記ミスなどです。もう1度データを読み返して確認してみましょう。
④「,」「$」「:」「(」「)」などの記号が抜けている
上のような画像の場合、今までの「#N/A」のエラー表示ではなく、「この数式には問題があります。」といったダイアログボックスが表示されます。
入力したVLOOKUP関数をもう一度確認してみると、範囲指定する際の$4と$Iの間の「:」コロンが抜けていることが分かりました。
この場合、「:」コロンを入力することでエラーは解消し、正常に反映します。
エラーになる原因の多くは入力漏れです。正常に範囲指定したところは上の画像のように赤に色がつくので、全部入力し終えて最後にEnterキーを押す直前に色付いているか最終確認をしましょう。
範囲指定のところに色がついていない状態でEnterキーを押すと、先ほどのダイアログボックスが表示され、どこか間違いがあるということになります。
また、数字や記号は大文字になっていても反映可能ですので、その点は安心してください。
まとめ
今回は、【VLOOKUP】の使い方やエラーの対処法についてお伝えしましたが、いかがでしたでしょうか。【VLOOKUP関数】を使えば、作業効率をあげることが可能になり、とても便利な機能であるとわかりました。【VLOOKUP】という言葉だけを聞くと、よく分からない、数学が苦手で難しいそうというマイナスなイメージがあるかと思います。しかし、この記事を読んで実際に一緒に進めていくと、案外簡単に出来ましたよね。
Excelには数多くたくさんの便利な機能があり、それらをいかに屈指するかによって作業効率が変わってきます。
子どもの頃に苦手だった食べ物が大人になって好きになったりするように、関数と聞いて自然と苦手意識をしてしまうかもしれませんが、意外と実践するとできるかもしれません。今一度、先入観を取っ払い、上記を参考に、ぜひ試してみてください。
