今回は、ExcelのVLOOKUPと参照表を使って、入力内容を確認しやすくする方法を紹介します。
VLOOKUPでできること
VLOOKUPは、表の左端にある値を手がかりにして、同じ行にある別の情報を取り出す関数です。商品コードから商品名を表示する、社員番号から所属部署を表示する、顧客IDから担当者名を表示するなど、業務の表でよく使われます。毎回手入力で名称を入力すると表記ゆれや入力ミスが起きやすいため、コードと参照表を組み合わせて情報を呼び出す形にすると確認しやすくなります。
たとえば、A列に商品コード、B列に商品名を表示したい場合、別シートに商品コードと商品名の一覧を用意し、B列でVLOOKUPを使います。コードを入力するだけで名称が表示されるため、入力作業と確認作業を分けられます。参照表を整えておけば、名称変更があった場合も参照表を直すだけで反映できます。
参照表を作るときの基本
VLOOKUPを安定して使うには、参照表の作り方が大切です。検索する値は参照範囲の左端に置きます。商品コードで検索するなら、参照表の一番左の列に商品コードを置き、その右側に商品名、分類、単価などを並べます。検索値が左端にないと、VLOOKUPでは扱いづらくなります。
- 検索に使うコードは参照表の左端に置く
- コードは重複させない
- 余分な空白を入れない
- 見出し行を分かりやすい名前にする
- 参照表だけを別シートにまとめる
コードに全角と半角が混ざっていたり、末尾に空白が入っていたりすると、見た目は同じでも一致しないことがあります。参照表を作る段階で、入力ルールをそろえることが重要です。コード列は文字列として扱うのか、数値として扱うのかも決めておきます。
基本の式を組み立てる
VLOOKUPの式は、検索値、参照範囲、取り出す列番号、検索方法を指定します。実務では、完全一致を使う場面が多くなります。完全一致にするには、最後の引数にFALSEを指定します。これにより、同じコードが見つかったときだけ結果を返します。
式の考え方
商品コードを入力して商品名を出す場合、考える順番は次の通りです。
- どのセルの値で検索するかを決める
- どの参照表から探すかを決める
- 参照表の何列目を表示するかを数える
- 完全一致で探す設定にする
列番号は、シート全体の列番号ではなく、参照範囲の中で何列目かを数えます。たとえば、参照範囲がA列からD列までで、C列の商品名を取り出すなら3列目です。この数え方を間違えると、別の情報が表示されます。
参照範囲を固定する
VLOOKUPの式を下方向へコピーするとき、参照表の範囲がずれてしまうことがあります。これを防ぐには、参照範囲を絶対参照にします。セル範囲にドル記号を付けると、式をコピーしても参照表の位置が変わりません。範囲を選択した状態でF4キーを押すと、絶対参照に切り替えやすくなります。
参照表をテーブル化しておく方法も便利です。テーブル名を付けると、行が増えても参照範囲に含めやすくなります。商品や担当者の一覧が増える表では、テーブルとして管理すると後から追加しやすくなります。
エラー表示を見やすくする
検索値が参照表にない場合、VLOOKUPはエラーを表示します。エラーがそのまま出ると、利用者には原因が分かりにくいことがあります。IFERRORと組み合わせて、「未登録」や空欄を表示するようにしておくと、確認しやすくなります。
ただし、エラーをすべて空欄にすると、入力漏れなのか未登録なのか判断しにくくなります。チェック用の表では「未登録」と表示し、印刷用の表では空欄にするなど、用途に合わせるとよいでしょう。エラーを隠すことより、原因を見つけやすい表示にすることが大切です。
未登録を見つける工夫
未登録の表示に条件付き書式を組み合わせると、確認対象を見つけやすくなります。「未登録」と表示されたセルに色を付ける、または入力コードのセルに色を付けるなど、確認する場所を決めます。参照表にないコードが入力されたとき、担当者がすぐ修正できる形にしておくと運用しやすくなります。
VLOOKUPを使う表の注意点
VLOOKUPは参照表の左端から右側へ情報を取り出す関数です。検索したい列が右側にあり、表示したい列が左側にある構成では使いにくくなります。その場合は、参照表の列順を見直すか、別の関数を検討します。関数を複雑にする前に、表の設計を変えられないか確認すると、後の管理が楽になります。
また、参照表の途中に空白行を入れると、一覧として扱いにくくなります。見出し、データ、メモを混在させず、参照表は参照表としてシンプルに保ちます。補足説明が必要な場合は、別の列にメモ欄を作るか、別シートに説明をまとめます。
運用しやすい参照表にする
参照表は作って終わりではなく、追加や変更が発生します。担当者が増える、商品名が変わる、部署名が整理されるなど、参照元の情報が変わったときに更新しやすい形にしておくことが大切です。参照表の上部に更新日や管理担当を置く場合は、VLOOKUPの参照範囲に含めないようにします。データ一覧と説明欄を分けると、式が読みやすくなります。
- コードの追加は一覧の末尾に行う
- 古いコードを消す前に利用中の表を確認する
- 名称変更は参照表側で管理する
- 入力用シートでは参照表を直接編集しない
- 参照表の列順をむやみに変えない
列を追加するときは、VLOOKUPの列番号にも影響がないか確認します。参照表の途中に列を差し込むと、式が別の列を参照する場合があります。変更が多い表では、式を確認する列を作る、または表示結果を数件チェックする習慣を持つと、意図しない表示を見つけやすくなります。
入力側の見せ方も整える
入力用シートでは、コードを入力する列と、VLOOKUPで表示する列を分けます。表示列には色を付けない、または保護をかけて編集対象ではないことを示すと、利用者が式を上書きしにくくなります。見出しに「自動表示」と添えておく方法もあります。
参照表の値が表示されたら終わりではなく、表示結果が入力者の意図と合っているかを確認できる配置にします。コード、名称、分類を近くに置くと、入力後の確認がしやすくなります。
まとめ
ExcelのVLOOKUPは、コードやIDをもとに参照表から情報を取り出すときに便利です。参照表の左端に検索値を置き、重複や余分な空白を避け、完全一致で検索する設定にすると扱いやすくなります。式をコピーする場合は参照範囲を固定し、未登録のときは原因が分かる表示にすることが大切です。参照表を整えておけば、入力ミスや表記ゆれを減らし、確認しやすいExcel表を作れます。