今回は、ExcelのXLOOKUPを使って、検索表から必要な値を取り出しやすくする方法を紹介します。
XLOOKUPでできることを整理する
Excelで商品コードから商品名を表示する、社員番号から所属を返す、管理表から単価を取り出す、といった作業では検索関数が役立ちます。以前から使われてきた検索方法では、参照表の左端に検索キーを置く必要があるなど、表の作り方に制約が出ることがありました。XLOOKUPは、検索する列と返す列を別々に指定できるため、表の配置に合わせて式を組み立てやすい関数です。
XLOOKUPの基本は、探したい値、探す範囲、返す範囲を指定することです。たとえば、A列の商品コードをD列のコード一覧から探し、E列の商品名を返す、という形で使えます。式の考え方が素直なので、後から見た人も何を検索しているか判断しやすくなります。検索値、検索範囲、戻り範囲を分けて読むことが、XLOOKUPを理解する近道です。
参照表を整えてから式を書く
XLOOKUPを使う前に、参照表を整えておくと式が安定します。検索に使うキーは、重複や余分な空白がない状態にします。コードやIDのように同じ値が1件だけ存在する列を検索範囲にすると、結果の意味が明確になります。同じコードが複数行にある表で検索すると、最初に見つかった値が返るため、意図しない結果になることがあります。
参照表は、できればExcelのテーブルとして扱います。テーブルにしておくと、行が増えても参照範囲が自動で広がります。列名を使った構造化参照にすれば、式の中でどの列を見ているかも分かりやすくなります。たとえば、商品マスターのコード列を探し、商品名列を返すように書けば、列番号を数える必要がありません。
式を読みやすくする工夫
XLOOKUPの式は短く書けますが、業務ファイルでは読みやすさも大切です。次の点を意識すると、修正しやすい表になります。
- 検索値のセルを固定するかどうかを決めてからコピーする
- 参照表はテーブル化して列名で指定する
- 見つからない場合の表示を指定する
- 検索用のコード列は表示形式をそろえる
- 式を入れる列の見出しに返す内容を明記する
特に、コードが数値として扱われている表と文字列として扱われている表が混在すると、見た目が同じでも一致しないことがあります。先頭にゼロが付くコードを扱う場合は、文字列として統一するなど、入力段階でルールを決めておくと検索の失敗を減らせます。
見つからない場合の表示を指定する
検索値が参照表にない場合、何も対策をしていない式ではエラーが表示されます。エラーは原因に気づくために役立つこともありますが、提出用の一覧では見た目を乱すことがあります。XLOOKUPでは、見つからない場合に表示する内容を式の中で指定できます。たとえば「未登録」や空欄を返すようにしておけば、確認がしやすくなります。
ただし、空欄にするとエラーに気づきにくくなる場合があります。入力漏れを確認したい表では「未登録」と表示し、印刷用の帳票では空欄にするなど、用途によって使い分けます。エラーを隠すのではなく、確認しやすい表示に変えるという考え方が大切です。未登録の行だけをフィルターで抽出すれば、参照表の追加やコードの修正を進めやすくなります。
左右どちらの列にも検索できる
XLOOKUPの利点の一つは、検索列より左側の列も返せることです。参照表の右端にコードがあり、左側に名称がある場合でも、列の並び替えをせずに検索できます。既存の管理表を崩したくないときや、他のシステムから出力された表をそのまま使いたいときに便利です。
表の列を無理に動かすと、他の式やピボットテーブルに影響することがあります。XLOOKUPなら、検索範囲と戻り範囲を明示できるため、表の構造を保ったまま値を取り出せます。検索表が複数のシートに分かれている場合でも、シート名と範囲を指定すれば利用できます。シート名が長い場合は、分かりやすい名前にしておくと式の確認が楽になります。
近似一致は目的を決めて使う
XLOOKUPは完全一致だけでなく、近い値を探す使い方もできます。料金表、ランク表、基準値表のように、入力値がどの範囲に入るかを調べたい場合に使えます。ただし、近似一致は参照表の並びや基準の作り方が結果に影響します。完全一致のつもりで近似一致を使うと、誤った値が返ることがあります。
業務で使う表では、まず完全一致で運用できるかを考えます。ランク判定のように近似一致が必要な場合は、基準表の見出しや説明を添え、どの方向に近い値を取るのかを分かるようにします。式だけでは意図が伝わりにくい場合、近くのセルに短いメモを置くのも有効です。後から確認する人が、検索のルールを追える状態にしておきます。
コピーしても崩れにくい表にする
XLOOKUPを一覧表に入れるときは、式を下方向にコピーすることが多くなります。このとき、検索値は行に合わせて変わるようにし、参照表の範囲は固定します。テーブルを使っていない場合は、絶対参照で範囲を固定する必要があります。範囲がずれると、下の行ほど検索対象が変わってしまいます。
また、戻り範囲の行数と検索範囲の行数はそろえます。検索範囲が100行で戻り範囲が99行のようにずれていると、式が正しく動きません。参照表に空白行を混ぜないことも大切です。マスター表を別シートに分け、入力表から参照する形にすると、検索用の情報と作業用の一覧を分けて管理できます。
複数の値をまとめて返す
XLOOKUPは、戻り範囲に複数列を指定して、商品名、単価、区分などをまとめて返す使い方もできます。必要な列が横に並んでいる参照表なら、1つの検索値から複数の情報を取り出せます。入力用の一覧に関連情報を並べたいとき、同じ検索式を何本も作るより管理しやすくなる場合があります。
ただし、複数列を返す式は、出力先の右側に既存データがあると展開できません。式を入れる前に、結果が広がる範囲を空けておきます。返す情報が後から増えそうな表では、出力列の見出しを先に決めておくと整理しやすくなります。式の便利さより、表として読み取れる形を優先して設計します。
検索ミスを見つける補助列を作る
検索結果をそのまま使うだけでなく、確認用の補助列を作るとミスに気づきやすくなります。たとえば、XLOOKUPで返した名称が「未登録」の行だけを抽出する、コードの文字数を確認する、前後の空白を取り除いた値と比較する、といった方法があります。検索関数は便利ですが、元データの表記が乱れていると正しい結果を返せません。
提出用の表では補助列を非表示にしてもかまいませんが、作業中は残しておくと原因調査に役立ちます。共有するファイルなら、補助列の見出しに目的を書いておきます。後から開いた人が削除してよい列か判断しやすくなります。
まとめ
ExcelのXLOOKUPは、検索値、検索範囲、戻り範囲を指定して必要な値を取り出す関数です。参照表を整え、テーブルや列名を使うと、行が増えても扱いやすい式になります。見つからない場合の表示を指定すれば、未登録の確認もしやすくなります。左右どちらの列にも検索できるため、既存の表を動かさずに使える点も実務向きです。表のルールと式の役割を見える形にしておくことで、検索表を長く使いやすく保てます。