新人SEのExcel術～VLOOKUP関数を活用しよう～

VLOOKUPでExcelの生産性が3倍に!?

業務でExcelを利用するSEは多い。Excelには様々な便利な関数が用意されているが、そのうちの一つにVLOOKUP関数というものがある。VLOOKUP関数を使うと、検索条件を与え、指定範囲の中から検索条件に一致したキーを持つデータを参照することが出来る。

SEの業務では勿論のこと、それ以外の仕事でも非常に役立つVLOOKUP関数を使いこなすことが出来れば生産性は3倍以上に跳ね上がるといっても過言ではない。本稿では、VLOOKUP関数の使い方と、SEの業務で具体的にそれを活用する方法を紹介したい。

VLOOKUP関数の使い方

関数の定義：
VLOOKUP(検索値,範囲,列番号,[検索方法])

検索値　：検索する際のキーとなる値を入力する。参照先の表と紐付けるための値を入力する。
範囲　　：参照する先の範囲を指定する。検索値に入力した値が、この範囲一番左の列に来るように指定する必要がある。
列番号　：参照して取り出す値が、参照先の範囲の何列目にあるかを指定する項目
検索方法：曖昧な値の検索を許可するかどうかの指定。FALSEを指定すると完全に一致した場合のみ取得する。TRUEを指定すると、完全一致する項目が無い場合は一番近い値を取得する。

＜利用例＞

最終形は上記の画像のようになる。

利用例で使用するシート

[顧客名簿]シート

顧客の情報を管理するマスタ資料で、顧客は顧客IDで重複しないように管理されている。

[営業状況]シート

営業担当者が顧客に訪問した際に更新する資料で、いつ誰がどの顧客に訪問したかということを記録するシート。営業担当者が毎回、顧客名称を記入する必要がないように、訪問した顧客の顧客IDを入力するだけでマスタ資料から自動的に表示させたい。

※わかりやすくするため、今回の例では上記2つのシートの内容を1シートに表示して説明する

＜手順＞

１．VLOOKUPで参照した値を入れたいセルに関数を入力する。

今回の場合では、[営業状況]シートの顧客IDをもとに検索したいので、J4を指定する。

２．参照先の範囲を指定する。

今回の場合では、[顧客名簿]シートから顧客名を表示させたいので、その範囲を指定する。一番左の列に、検索値と紐づく値が入る列（今回は顧客ID列）がくるように範囲を指定しよう。

３．取得したい列の番号を指定する。

今回の場合では、顧客名を表示させたいので、指定した範囲の中での列番号を指定する。今回は取得したい顧客名が左から2番目の列にあるので、2を指定する。

４．検索方法を指定する。

参照先の範囲に曖昧な値が存在する場合はTRUEを指定するが、今回は存在しないのでFALSEを指定する。

顧客ID:A00001で[顧客名簿]シートの情報と紐付けて顧客名を表示することが出来た。

５．入力した数式を他の行にもコピーする。

顧客名が正しく取得できていることを確認したら、最後に入力したセルの数式を他の行にもコピーしよう。

他の行に対しても自動的に顧客名の取得が行われた。顧客IDが空白の行に対しても数式をコピーしているため#N/Aが表示されている業があるが、あとで顧客IDを入力すれば顧客名が表示される。

VLOOKUP関数をさらに使いこなすためのテクニック

○検索値と参照先の列の値が一致しているはずなのに取得できない

・セルの入力値に表記の揺らぎがあるケース
セルにスペースなどが入っていると上手く値が取得できない場合がある。
その場合には、検索方法の指定をTRUEに指定することで値を取得できる場合がある。

[顧客情報]シートの顧客ID列にスペースが入っているためVLOOKUP関数でうまく値が取れていない。

検索方法にTRUEを指定したらうまく取得できた。

・セルの表示形式によって同一に見えているケース

以下の画像の２つのセルは一見すると同一の値に見えるが、表示形式によって同一に見えているだけで実は別々の値である。

表Aの値と表Bの日付は、一見すると同じ値に見える。

VLOOKUPで値を取得できない。

二つのセルの表示形式を見比べると、異なっていることがわかる。

セルの表示形式を文字列形式にあわせることで、上手く取得できるようになる。

※表Bのセルを日付形式にあわせることでも対応可能

○取得したい列が検索値の列より左にある

VLOOKUP関数では、検索値の列が参照先の範囲の一番左にないと利用できない。
そのため、検索値より左側にある値を取得したい場合は列を入れ替える必要がある。

しかし、資料によっては、マクロや他の関数で使用していたりして、既存の列の場所が変わってしまうと問題が発生する場合がある。そういった場合にオススメしたいのが、ワーク用の列を右側に追加する方法である。例を使って説明しよう。

顧客IDから顧客名を取得してたいが、[顧客名簿]シートの顧客名列が顧客ID列の左側にあるため、そのままでは取得できない。

ワーク用の列を追加することで、既存の表に変更を加えずに値を取得できた。

○複数の列の値を結合してキーにする

セルの値を結合して検索することで、データベースで言うところの複合主キーのような使い方をすることが可能だ。

顧客IDと支店IDの組み合わせから、顧客名を取得したい。

マスタ資料にワーク列を追加し、顧客IDと支店IDを組み合わせた値の列を作成する。

VLOOKUP関数の検索値にも、&で結合した顧客IDと支店IDを与えることで顧客名を取得することが出来た。

業務のどんな場面で役に立つのか

○マスタの管理資料から、特定の情報を紐付けて参照したい場合

今回の例であげたように、マスタ資料とは別の表で、マスタ資料で管理している情報を使いたい場合にはセルをコピーするのではなく、VLOOKUP関数を使って参照すると管理しよう。もし直接入力していると、マスタ資料の値が変更になった場合に、いちいち修正しなければならないからだ。

今回の例で言うと、もし[営業状況]シートに直接顧客名を入力していた場合、社名が変わった場合などに[営業状況]シートの値も変更しなければならないが、VLOOKUP関数を使っておけば[顧客名簿]シートの顧客名を修正するだけで自動的に[営業状況]シートにも反映される。

○データの検証をしたい場合

SEの業務で意外と役に立つのがデータの検証作業だ。

例えば、管理資料の値と、データベースやログの内容の突合せを行う場合などにも有効である。SEの仕事においてミス無く作業することは非常に重要であり、さらにそれを効率的に確認し担保することも質の高い仕事をするためには必要である。

データベースのデータ内容を出力したものや、作業のログなど様々なものを突き合わせて確認し、クライアントに説明するための資料に落とす際に、VLOOKUP関数を使うことで機械的にミス無く突合せを行うことが可能だ。

まとめ

VLOOKUP関数はExcelを業務で使うならば是非、覚えておいて欲しい関数だ。

まずは細かい仕様を覚えなくとも、どんなことが出来るかを知っておこう。そして、資料を作成する際に使い方を検索したり、使ってみながら覚えていくと良いだろう。

慣れてきたら、ミーティングなどで資料を参照している際など、必要になったときにその場でぱっと使いこなしてみせると、さらににスマートだ。ぜひ本稿を参考にして、使い方を習熟して欲しい。