・はじめに
この記事では、VLOOKUP関数の使い方を整理します。
VLOOKUP関数は、検索した値が存在するときに、その行の別の列から値を取得することができます。
以下のような使い方があります。
・あるデータが、別の表に存在するかを確認する
・2つの表で共通する列名とデータをキーにしてつき合わせる
・一方の表に、もう一方の表からデータを追加する
詳しい説明や使い方は後述します。
使い方のポイント
=VLOOKUP(検索値 , 検索対象の列, 値を取得したい列の番号, 検索オプション)
・検索値は、ある表での検索したい値です。先ほどの例ではA君の学籍番号に当たります。
・検索対象の列は、学籍番号の列から欲しいデータがある列までを全て指定します。
・取得したい列の番号は、学籍番号の列から何列右にあるかを入力します。
・検索オプションは、完全一致などの選択ができますが、基本はFALSEを入力すればOKです。
VLOOKUP関数でデータを取得する
書式・書き方
書式は以下の通りです。カッコの中に合計したい数字をセル選択によって入れます。
=VLOOKUP(検索値,検索対象,列番号,FALSE)
具体的には以下のように入力します。
セルの選択は、直接セル番号を書いても良いですし、マウスやキーボードでで選択しても良いです。
具体的は後述します。
=VLOOKUP(1001, A:C, 3, FALSE)
この例では、A列に1001があれば、1001がある行のC列(A列から数えて3番目)のデータを表示します。
次の項目から、ある大学の学生情報を記録した「学生リスト」があると仮定して、具体的な使い方を考えていきます。
具体例：あるデータが、別の表に存在するかを確かめる
学生リストには、3人の学生が記録されています。
G列にある、A君とD君が学生リストに含まれているかを確認します。
画像のように入力することで、A君・D君が学生リストに含まれているかを確認することができます。
入力例
=VLOOKUP(G3,C:C,1,FALSE)
・「G3」は、「A君」という氏名を選択しました。打ち込んでも良いですし、セルをクリックして選択することもできます。
・「C:C」は検索範囲を指定しています。後で説明していますが、列ごと指定した方が後々困ることが少ないです。
・「1」は、C列を含んで右に何列目のデータを取得するかを指します。今回は、C列を含んで1列目なので、C列にある「A君」の名前が表示されます。
・「FALSE」は完全一致した場合にデータを取得することを指定しています。基本的にFALSEを選んでおけば間違いありません。
D君の場合は、以下のように入力します。
入力例
=VLOOKUP(G4,C:C,1,FALSE)
D君の氏名はG4セルにあるので、今回はG4セルを選択します。
そのほかはA君と入力内容は同じです。
ちなみに、H3セルに入力した内容をコピーして、H4セルに貼り付けすることで、同じ結果を得ることができます。
最終的には以下のようになります。
D君は、学生リストには存在しないので、「#N/A」を表示して存在しないことを示しています。
2つの表で共通する列名とデータをキーにしてつき合わせる
ある学生向けイベントの登壇者リストがあるとします。
その中から、ある学校に所属しているか、所属している場合の成績はどうかを調べます。
H列は、上記の具体例と同じで、登壇者がある学校に所属しているかをVLOOKUP関数で調べています。
成績を調べるには、学生リストで名前を検索し、該当した場合にその成績を取得する必要があります。入力例は以下の通りです。
=VLOOKUP(G3,C:E,3,FALSE)
・「G3」は登壇者の氏名です。クリックで選択しても、直接入力しても構いません。
・「C:E」は赤い検索範囲です。今回は、C列で氏名検索し、一致した場合はE列から成績を取得します。取得したデータはI列の関数を入力しているセルに表示されます。
・「3」はC列を含めて3列目、つまりE列からデータを取得することを意味します。
・「FALSE」は先ほどと同様に氏名が完全一致した場合のみ処理することを指定します。
I3列にデータを入力したら、I3セルをコピーしてI7セルまでまとめて貼り付けすると以下のような結果になります。
D、E、F君は学校に所属していないので「#N/A」となり、
A、C君は所属しているので「本校所属」列に氏名、「成績」列に各自の成績が表示されました。
具体例：あるデータを含む表から別のデータを取り出す
学籍番号1001の学生の年次を調べます。
検索値：A2のセル選択(1001です。)
検索対象：A列からC列までを列ごと選択します
列番号：A列から数えてC列は3列目です。
検索オプション：FALSEで良いです。(1001と完全に一致するデータだけを取得します。)
↑このように入力すると以下のように、1年生というデータを取得することができます。
具体例：2つの表から共通する列を使って必要な情報を抜き出す
2つの表がある時、一方の表に、もう一方のデータを追加する使い方があります。
2つの表に同じ値を持つデータがあると実現できます。
以下の画像を使って考えてみます。
左の表に学生の一覧、右の表に期末試験結果の一覧があります。
左の表にある氏名を、右の表の右端に追加してみます。
以下の画像では「=VLOOKUP(G3, B:C, 2, FALSE)」という関数をI3セルに入力しています。
検索値：G列の値(青いセル)
検索対象：B～C列(赤い列)
列数：2 (C列(氏名)はB列から数えて2列目にある)
FALSE：1001と完全に一致した場合のみ表示させる
上記のように入力すると、以下のようになります。
I3セルにA君の名前が表示されています。
さらに、I3セルをコピーして、I4,I5セルにコピーすると、同じことができます。
データが表のように整理されて並んでいれば、1回入力した後はコピーすればまとめて処理できます。
注意点1：検索値がない
これは失敗ではありませんが、検索した値が、検索対象の列にない場合は「#N/A」と表示されます。
以下の画像では、A列から1004を検索しようとして、「#N/A」が表示されています。
注意点2：検索範囲の指定方法
検索範囲は列ごと指定した方が良いです。
以下の例でその理由をお伝えします。
右側の表に赤い文字で「#N/A」と表示されているセルがあります。
学籍番号1001は左側にの表にあるので、A君が表示されるはずです。
このように表示される理由は検索範囲の指定方法にあります。
I5セルの関数を詳しく見てみると、以下のようになっています。
赤い枠内が検索範囲ですが、3行目が含まれていません。
I3セル(B君)でVLOOKUP関数を入力したときの検索範囲の指定の方法がまずく、さらにその状態でI5セル(本当はA君が表示されてほしい)までコピペしたことが理由です。
I3セルでの入力内容は以下の画像です。
検索範囲がA列～B列ではなく、B2:C5で指定されています。
この状態で、I3セルの値をI4、I5へコピーすると、検索範囲も同じように下へずれていきます。
そのため、検索範囲を指定するときは列ごと指定した方が良いです。
まとめ
この記事では、VLOOKUP関数でのデータの取得方法について確認しました。
Excelを使い始めてしばらくしたころ、複数のデータや表を使って集計したりデータを整理したりすることが出てきます。
そんな時に必ず役に立つので、この記事でしっかりと身に着けておきましょう。
コメント