今回は、ExcelのXLOOKUPで検索作業を分かりやすくする方法を紹介します。
XLOOKUPを使う目的
Excelで商品コードから商品名を表示したり、社員番号から部署名を引いたり、顧客番号から担当者を確認したりする場面があります。このような検索作業では、XLOOKUPを使うと、指定した値に対応する情報を取り出せます。
従来の検索関数では、表の左端に検索列を置く必要があるなど、表の作り方に制約が出ることがありました。XLOOKUPは検索する列と返す列を別々に指定できるため、表の構成を保ったまま使いやすいのが特徴です。
XLOOKUPは、一覧表から必要な情報を取り出すための関数です。検索する値、検索する範囲、返したい範囲を分けて考えると理解しやすくなります。
基本の考え方
XLOOKUPでは、何を探すか、どこから探すか、見つかったときに何を返すかを指定します。複雑に見える式も、この3つに分けると作りやすくなります。
検索値を決める
検索値は、探したい値です。商品コード、社員番号、メールアドレス、注文番号など、一覧表の中で一致するものを指定します。
検索値は、できるだけ重複しない項目を使います。同じコードが複数行にあると、どの情報を返せばよいか分かりにくくなります。検索用の項目は、管理番号のように一意に近いものを選ぶと扱いやすくなります。
検索範囲と戻り範囲を分ける
検索範囲は、検索値を探す列です。戻り範囲は、見つかった行から返したい情報の列です。たとえば商品コードで商品名を返す場合、検索範囲は商品コード列、戻り範囲は商品名列になります。
この2つの範囲は、行数をそろえる必要があります。検索範囲と戻り範囲の位置がずれていると、正しい結果が返りません。
エラー表示を分かりやすくする
検索値が見つからない場合、エラーが表示されることがあります。エラー自体は問題の発見に役立ちますが、共有する表では意味が分かりにくい場合があります。
見つからない場合の表示を指定する
XLOOKUPでは、見つからない場合に表示する文字を指定できます。たとえば「未登録」「該当なし」「コード確認」など、入力者が次に何をすればよいか分かる表示にします。
「該当なし」と表示されれば、検索値が一覧にないことが分かります。単なるエラー表示より、確認すべき内容が伝わりやすくなります。
入力ミスを見つける
検索結果が出ない場合は、検索値の入力ミスや表記ゆれを確認します。全角と半角、余分なスペース、コードの桁数違いなどが原因になることがあります。
検索値を手入力する表では、入力規則と組み合わせるとミスを減らせます。候補から選ばせる形にすれば、検索結果が出ない原因を減らしやすくなります。
複数列を返す使い方
XLOOKUPでは、戻り範囲を複数列にすることで、関連する情報をまとめて返すこともできます。商品コードから商品名、単価、分類をまとめて表示したい場合に便利です。
関連情報をまとめて表示する
検索値に対して複数の項目を返す場合、戻り範囲を横に広げます。これにより、同じ検索値を使った式を何度も作らずに済む場合があります。
ただし、返す列が多すぎると表が広がります。必要な情報だけを選び、入力欄と表示欄の役割を分けておくと見やすくなります。
返された範囲の上書きに注意する
複数列を返す式では、結果が隣のセルにも表示されます。隣のセルに別の値が入っていると、結果を表示できないことがあります。
式を入れる前に、結果を表示する範囲を空けておきます。共有ファイルでは、式の結果が出る範囲を入力禁止にしておくと、誤って上書きされにくくなります。
表を更新するときの注意点
XLOOKUPは元の一覧表を参照します。一覧表が更新されたときに、検索式が正しく参照できているか確認する必要があります。
テーブル形式にする
参照する一覧表をExcelのテーブル形式にしておくと、行を追加したときに範囲が広がりやすくなります。検索範囲や戻り範囲を固定のセル範囲で指定するより、管理しやすくなります。
テーブル名や列名を分かりやすくしておけば、式を見たときに何を参照しているか判断しやすくなります。
参照先の変更を確認する
一覧表の列を移動したり、列名を変えたりすると、式に影響することがあります。特に複数人で表を編集する場合は、参照元の表を変更する前に影響範囲を確認します。
参照元の一覧表は、入力用の表とは別シートに置き、必要な人だけが編集する運用にすると安定しやすくなります。
実務で使うときのチェックポイント
XLOOKUPを使った表は、検索結果が正しいかだけでなく、入力者が使いやすいかも確認します。
- 検索値に重複がないか確認する
- 検索範囲と戻り範囲の行数がそろっているか確認する
- 見つからない場合の表示が分かりやすいか確認する
- 結果を表示する範囲に上書きされるセルがないか確認する
- 参照元の一覧表を更新しても式が崩れないか確認する
検索式を入れたセルは、誤って消されると表の機能が失われます。必要に応じて、入力欄と計算欄を分け、計算欄を保護します。
検索結果を確認しやすくする工夫
XLOOKUPを使う表では、検索結果が正しいかを確認しやすい配置にしておくことも大切です。式が正しくても、入力欄と結果欄の関係が分かりにくいと、利用者が迷います。
入力欄と結果欄を分ける
検索値を入力する欄と、XLOOKUPで返す欄は見た目を分けます。入力欄には淡い背景色を付け、結果欄は保護された計算欄として扱うと分かりやすくなります。
検索結果の列名も具体的にします。「結果」だけではなく「商品名」「担当部署」「単価」のように、返される情報が分かる名前にします。
確認用の列を用意する
検索結果に不安がある場合は、確認用の列を用意する方法もあります。たとえば、該当なしの場合に「確認」と表示する列を作ると、修正が必要な行を見つけやすくなります。
条件付き書式で「未登録」や「該当なし」を色付けすれば、検索できなかった行をすぐに確認できます。
まとめ
ExcelのXLOOKUPは、一覧表から対応する情報を取り出すために便利な関数です。検索値、検索範囲、戻り範囲を分けて考えると、式を作りやすくなります。
見つからない場合の表示を設定すれば、入力ミスや未登録データに気づきやすくなります。複数列を返す使い方や、テーブル形式との組み合わせも実務で役立ちます。
運用時には、検索値の重複、参照範囲、エラー表示、上書き防止を確認しましょう。XLOOKUPを活用すれば、Excelの検索作業を分かりやすく整えられます。