日々の業務で、大量のデータから必要な情報を探し出す作業に時間を取られていませんか？特に、人事異動の多い時期や、頻繁に更新される商品リストなどを扱う際、手作業での検索は非効率的です。以前はVLOOKUP関数が主流でしたが、現在ではより強力な「XLOOKUP」が利用可能です。この記事では、Excel歴15年の筆者が、実務で役立つXLOOKUP 使い方を詳しく解説します。
XLOOKUP 使い方：基本と実例
XLOOKUP関数は、指定した検索値に基づいて、表や範囲から対応する値を抽出する関数です。VLOOKUP関数に比べて、柔軟性が高く、使いやすいのが特徴です。例えば、社員番号から氏名や部署名を検索したり、商品コードから価格や在庫数を検索したりする際に役立ちます。
XLOOKUP関数の構文
XLOOKUP関数の基本的な構文は以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])- 検索値：検索する値。例えば、社員番号や商品コードなど。
- 検索範囲：検索値を探す範囲。社員番号が入力されている列や、商品コードが入力されている列など。
- 戻り範囲：対応する値を返す範囲。氏名が入力されている列や、価格が入力されている列など。
- [見つからない場合]：検索値が見つからなかった場合に返す値。省略可能。
- [一致モード]：検索値との一致方法を指定。省略可能。
- [検索モード]：検索の方向を指定。省略可能。
VLOOKUP関数では、左から数えて何番目の列の値を返すかを指定する必要がありましたが、XLOOKUP関数では、戻り範囲を直接指定できるため、列の挿入や削除があっても数式を修正する必要がありません。筆者の経験では、VLOOKUPで列番号を間違えて、違う値を参照してしまうというミスが非常に多かったのですが、XLOOKUPを使うことで、そのようなミスを大幅に減らすことができました。
簡単な使用例：社員番号から氏名を検索
社員番号がA列、氏名がB列に入力されている社員名簿があるとします。C2セルに社員番号を入力し、D2セルに氏名を表示させるには、以下の数式を入力します。
=XLOOKUP(C2, A:A, B:B)この数式は、C2セルの社員番号をA列から検索し、対応するB列の氏名をD2セルに表示します。
実務で役立つXLOOKUPの活用シーン
XLOOKUP関数は、様々な業務で活用できます。ここでは、筆者が実務でよく使う3つの活用シーンを紹介します。研修で教えていると、これらの例を参考に、自分の業務にどのように応用できるかを熱心に質問される方が多いです。
請求書作成時の商品情報自動入力
請求書を作成する際、商品コードを入力するだけで、商品名、単価、数量などを自動的に入力できます。XLOOKUP関数を使用することで、手入力によるミスを減らし、作業効率を向上させることができます。戻り範囲を複数列指定することで、商品名と単価を同時に取得することも可能です。
勤怠管理システムでの残業時間集計
勤怠管理システムから出力されたデータをもとに、社員ごとの残業時間を集計する際に、XLOOKUP関数を使用できます。社員番号をキーにして、残業時間を集計シートに自動的に転記することができます。経理部では、この作業を毎月行うため、XLOOKUP関数は必須ツールとなっています。
顧客リストの住所自動入力
顧客リストに顧客IDを入力するだけで、顧客名、住所、電話番号などを自動的に入力できます。XLOOKUP関数を使用することで、顧客情報の入力ミスを減らし、顧客管理を効率化することができます。
XLOOKUPでエラーが発生した場合の対処法
XLOOKUP関数を使用する際に、エラーが発生することがあります。ここでは、よくあるエラーとその対処法を紹介します。特に、#N/Aエラーは初心者の方がつまずきやすいポイントです。
#N/Aエラーの主な原因と対処法
#N/Aエラーは、検索値が検索範囲に見つからなかった場合に発生します。このエラーが発生する主な原因と対処法は以下の通りです。
- 検索値が間違っている：検索値が正しいかどうかを確認してください。
- 検索範囲に検索値が存在しない：検索範囲に検索値が存在するかどうかを確認してください。
- 検索値と検索範囲のデータの型が異なる：検索値と検索範囲のデータの型が一致しているか確認してください。例えば、検索値が数値なのに、検索範囲が文字列になっている場合などがあります。
実務でよく見かけるのは、CSVファイルをインポートした際に、数値が文字列として認識されてしまうケースです。このような場合は、VALUE関数を使って数値を文字列から数値に変換するか、区切り位置指定ウィザードでデータの型を修正する必要があります。
#VALUE!エラーの主な原因と対処法
#VALUE!エラーは、検索範囲と戻り範囲のサイズが異なる場合に発生します。検索範囲と戻り範囲の行数または列数が一致しているか確認してください。
その他のエラー
その他のエラーが発生した場合は、Microsoft公式サイトでエラーメッセージを検索し、対処法を確認してください。 Microsoft公式サイト: XLOOKUP関数 にも詳しい解説があります。
XLOOKUPの応用テクニック：さらに便利に使いこなす
XLOOKUP関数は、基本的な使い方をマスターするだけでなく、応用テクニックを身につけることで、さらに便利に使いこなすことができます。ここでは、筆者が実務でよく使う応用テクニックを紹介します。
「見つからない場合」の引数を活用する
XLOOKUP関数の第4引数「見つからない場合」を指定することで、検索値が見つからなかった場合に表示する値を設定できます。例えば、商品コードが見つからなかった場合に「商品が見つかりません」と表示したり、社員番号が見つからなかった場合に「該当社員なし」と表示したりすることができます。これにより、エラーメッセージを表示する代わりに、ユーザーに分かりやすいメッセージを表示することができます。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, "該当なし")ワイルドカードを使用してあいまい検索を行う
XLOOKUP関数の第5引数「一致モード」を2に設定すると、ワイルドカード文字（、?）を使用してあいまい検索を行うことができます。例えば、「田中」と検索すると、「田中一郎」「田中花子」など、「田中」で始まるすべての値を検索できます。ただし、ワイルドカード検索は、検索速度が遅くなる可能性があるため、注意が必要です。
実務では、顧客名が一部しか分からない場合に、ワイルドカード検索を利用することがあります。例えば、「株式*会社」と検索して、「株式会社〇〇」「株式△△会社」などの候補を表示させ、正しい顧客名を選択してもらうといった使い方です。
検索モードを使いこなす
XLOOKUP関数の第6引数「検索モード」を指定することで、検索の方向を制御できます。デフォルトでは、先頭から検索しますが、末尾から検索したり、バイナリ検索を行ったりすることも可能です。 Microsoft公式サイト に詳細な情報が記載されています。
プロが教えるXLOOKUPの裏技と時短術
ここでは、他のExcel解説サイトにはない、筆者が15年の実務経験から得たXLOOKUPの裏技と時短術を紹介します。これらをマスターすれば、XLOOKUPをさらに効率的に使いこなせるようになります。
数式バーで引数を編集する
XLOOKUP関数を入力した後、数式バーで引数を直接編集することができます。引数の順番を入れ替えたり、範囲を変更したりする際に、マウスで操作するよりも素早く編集できます。F2キーを押すと、数式バーが編集モードになり、引数を自由に編集できます。
名前定義と組み合わせる
検索範囲と戻り範囲に名前を定義しておくと、XLOOKUP関数の数式が格段に読みやすくなります。例えば、社員番号が入力されている列に「社員番号」、氏名が入力されている列に「氏名」という名前を定義しておくと、数式は以下のようになります。
=XLOOKUP(C2, 社員番号, 氏名)数式を見ただけで、何をしているのかがすぐに分かるため、メンテナンスも容易になります。
スピルを活用する
Microsoft 365以降のExcelでは、XLOOKUP関数で複数の値を返す「スピル」という機能が利用できます。たとえば、ある部署の社員全員の名前と役職を一度に取得したい場合、戻り範囲に名前と役職の列をまとめて指定することで、複数のセルに結果を自動的に展開できます。筆者の経験では、この機能を使うことで、VLOOKUPを複数回使う必要がなくなり、数式が非常にシンプルになりました。
まとめ：XLOOKUPをマスターして業務効率を向上させよう
この記事では、XLOOKUP 使い方について、基本的な使い方から応用テクニック、プロの裏技まで詳しく解説しました。XLOOKUP関数は、VLOOKUP関数よりも柔軟性が高く、使いやすい関数です。ぜひ、XLOOKUP関数をマスターして、業務効率を向上させてください。
- XLOOKUP関数は、VLOOKUP関数よりも柔軟性が高く、使いやすい。
- 検索範囲と戻り範囲を直接指定できるため、列の挿入や削除があっても数式を修正する必要がない。
- 「見つからない場合」の引数を活用することで、エラーメッセージの代わりに分かりやすいメッセージを表示できる。
- ワイルドカードを使用してあいまい検索を行うことができる。
- 名前定義と組み合わせることで、数式が格段に読みやすくなる。
コメント