今回は、ExcelのVLOOKUPを使って、参照表を整理する方法を紹介します。
VLOOKUPが役立つ場面
Excelで商品コードから商品名を表示したり、社員番号から部署名を引いたり、顧客コードから担当者を表示したりするとき、手入力で情報を写すとミスが起きやすくなります。そのようなときに使えるのがVLOOKUPによる参照整理です。
VLOOKUPは、指定した値を表の左端の列から探し、同じ行にある別の列の値を取り出す関数です。コードと名称の対応表、単価表、部署一覧、分類表など、基準になる表を用意しておくと、入力用の表へ必要な情報を自動で表示できます。
参照表を先に整える
VLOOKUPを使う前に、参照先となる表を整えます。検索するキーになる列は、表の一番左に置きます。たとえば商品コードで検索するなら、商品コードを左端にし、その右に商品名、分類、単価などを並べます。
参照表では、キーが重複しないようにします。同じ商品コードが複数行にあると、どの情報を参照しているのか分かりにくくなります。また、余分な空白や全角半角の違いがあると一致しないことがあります。VLOOKUPの前に、参照表のデータを整えることが大切です。
VLOOKUPの基本の考え方
VLOOKUPでは、検索値、範囲、列番号、検索方法を指定します。検索値は探したいコードや番号です。範囲は参照表です。列番号は、参照表の左から何列目を取り出すかを示します。検索方法は、完全一致か近似一致かを指定します。
業務でコードや番号を使って参照する場合は、完全一致を使うことが多くなります。完全一致にしておくと、指定した値と同じものが見つかった場合だけ結果を返します。意図しない近い値が表示されることを避けやすくなります。
列番号のずれに注意する
VLOOKUPでよくあるミスの一つが、列番号のずれです。参照表の途中に列を追加すると、取り出したい列の位置が変わることがあります。数式内の列番号が古いままだと、別の列の値を表示してしまいます。
参照表の列構成を頻繁に変える場合は、列番号を見直す作業が必要です。表の設計段階で、検索キーとよく参照する列を決め、むやみに列順を変更しないようにします。引き継ぎ用の表では、参照表の上に列の意味を分かりやすく書いておくと確認しやすくなります。
エラー表示への対応
検索値が参照表にない場合、VLOOKUPはエラーを返します。エラーが表示されると表が見づらくなるため、IFERROR関数と組み合わせて、空白や「未登録」と表示する方法があります。
ただし、エラーを完全に隠してしまうと、参照表に登録漏れがあることに気づきにくくなります。入力中の管理表では「未登録」と表示し、後で参照表を更新できるようにするのがおすすめです。完成版の提出資料では、必要に応じて空白表示にするなど、目的に合わせて調整します。
参照表を別シートに置く
入力用の表と参照表を同じシートに置くと、行や列が増えて見づらくなることがあります。参照表は別シートにまとめ、「マスタ」「コード表」「参照一覧」などの名前を付けて管理すると扱いやすくなります。
別シートに置く場合は、参照表を誤って編集しないように保護する方法もあります。入力者が触るシートと、管理者が更新するシートを分けることで、データの整合性を保ちやすくなります。
テーブル化して範囲を管理する
参照表に行を追加する予定がある場合は、テーブル化しておくと便利です。通常の固定範囲を参照していると、新しい行が数式の範囲に含まれないことがあります。テーブル化すると、行を追加したときに範囲が広がりやすくなります。
テーブル名を付けておくと、数式を見たときにどの表を参照しているか分かりやすくなります。商品マスタ、社員マスタ、単価表など、用途に合った名前を付けると、後から確認しやすくなります。
XLOOKUPとの使い分け
新しいExcel環境ではXLOOKUPを使える場合があります。XLOOKUPは、検索列と戻り列を直接指定できるため、VLOOKUPより柔軟に使える場面があります。一方で、古い環境と共有するファイルでは、VLOOKUPの方が互換性を保ちやすいことがあります。
社内で使うファイルでは、利用者のExcel環境を考えて関数を選びます。互換性を重視するならVLOOKUP、表の列構成が変わりやすいならXLOOKUPも検討します。大切なのは、関数よりも参照表を分かりやすく整えることです。
コード列の形式をそろえる
VLOOKUPで参照するコード列は、数値なのか文字列なのかをそろえておきます。見た目が同じ「001」と「1」でも、Excelでは別の値として扱われることがあります。社員番号、商品コード、郵便番号のように先頭ゼロが必要な値は、文字列として管理する方がよい場合があります。
検索値側と参照表側の形式が違うと、該当する値があるのに見つからないことがあります。エラーが出たときは、コードの見た目だけでなく、セルの表示形式や余分な空白も確認します。参照表を作る段階で形式を決め、入力規則やテンプレートでそろえると安定します。
参照表の更新履歴を残す
単価表や担当者表のように内容が変わる参照表では、いつ誰が更新したのか分かるようにしておくと便利です。更新日、更新者、変更内容を小さなメモ欄に残しておくと、集計結果が変わった理由を追いやすくなります。
参照表が古いままだと、VLOOKUPの数式が正しくても表示される情報は古くなります。関数の確認だけでなく、参照表そのものが最新かどうかを確認する運用が必要です。定期的に見直す表では、更新担当者を決めておくと管理しやすくなります。
参照結果を確認する列を作る
VLOOKUPで取得した値が正しいか確認するために、チェック用の列を作る方法もあります。未登録や空白が出ている行だけをフィルターで確認すれば、参照表への登録漏れを見つけやすくなります。大量のデータを扱うときほど、エラー確認の仕組みを用意しておくことが役立ちます。
まとめ
ExcelのVLOOKUPは、コードや番号をもとに参照表から情報を取り出すための関数です。商品名、部署名、単価、担当者などを自動表示できるため、手入力のミスを減らしやすくなります。
使うときは、検索キーを左端に置き、重複や表記ゆれを避け、完全一致で参照することを意識します。参照表を別シートやテーブルで管理し、更新しやすいExcel表に整えましょう。