1章: HLOOKUP関数とVLOOKUP関数の基本
今回の記事では、Excelの検索と参照を行うための2つの関数「HLOOKUP関数」「VLOOKUP関数」について、基本的な使い方から応用技法まで解説していきます。データ検索や参照作業はビジネスシーンにおいて頻繁に行われる作業の一つですので、ぜひこの機会にマスターしてください。
HLOOKUP関数の概要
HLOOKUP関数は、水平方向（行）でキーワードが含まれるセルを検索し、指定した行の値を返す関数です。これにより、データが横方向に並んでいる場合に、特定のデータを見つけ出すことができます。
VLOOKUP関数の概要
一方、VLOOKUP関数は、垂直方向（列）でキーワードが含まれるセルを検索し、指定した列の値を返す関数です。これにより、データが縦方向に並んでいる場合に、特定のデータを簡単に見つけ出すことができます。
関数の書式と使用方法
HLOOKUP関数の書式は以下のようになります。
HLOOKUP(検索値, 範囲, 行, 真偽値)
ここで、「検索値」は検索したいキーワード、「範囲」はデータがある範囲、「行」は取得したい値がある行、そして「真偽値」は完全一致と部分一致の設定をオン・オフにする項目です。
VLOOKUP関数の書式は以下のようになります。
VLOOKUP(検索値, 範囲, 列, 真偽値)
ここで、「検索値」は検索したいキーワード、「範囲」はデータがある範囲、「列」は取得したい値がある列、そして「真偽値」は完全一致と部分一致の設定をオン・オフにする項目です。
それでは、実際にHLOOKUP関数とVLOOKUP関数を使ってデータの検索と参照を行ってみましょう。
以下のデータがあるとします。
A B C 1 100 高橋 S 2 200 田中 A 3 300 鈴木 B
HLOOKUP関数で100の成績を取得するには、「=HLOOKUP(100, A1:C3, 1)」と入力します。すると「S」という結果が得られます。
同様にVLOOKUP関数で高橋さんの成績を取得するには、「=VLOOKUP(“高橋”, A1:C3, 3)」と入力します。するとこちらも同じく「S」という結果が得られます。
これがHLOOKUP関数とVLOOKUP関数の基本的な使い方です。次の章では、それぞれの関数の応用例を解説していきます。
2章: HLOOKUP関数の応用例
この章では、HLOOKUP関数の応用例について具体的に解説していきます。以下の3つの応用例を学ぶことで、HLOOKUP関数をより効果的に活用できるようになります。
条件に合ったデータの検索方法
HLOOKUP関数で条件に合ったデータを検索する方法をみていきましょう。例えば、年度ごとの売上データがあり、特定の年度の特定の月のデータを抽出したい場合があるでしょう。
下記のようなデータがあるとします。
A B C D 1 年度 1月 2月 12月 2 2019 3000 3200 4500 3 2020 3300 3050 5000 4 2021 3100 3250 5200
2020年2月の売上データを取得するには、「=HLOOKUP(2020,A1:D4,3,0)」と入力します。すると「3050」という結果が得られます。ここで注意するのは、「真偽値」という引数である第4引数が0にして完全一致を指定することです。
複数のシートや範囲を対象にした検索
複数のシートや範囲を対象にデータを検索する場合、IFERROR関数と組み合わせることで対応できます。以下の例では、Sheet1とSheet2に存在するデータを検索対象としています。
以下のフォーミュラを入力します。
=IFERROR(HLOOKUP(検索値,Sheet1!範囲, 行, 真偽値),HLOOKUP(検索値,Sheet2!範囲, 行, 真偽値))
IFERROR関数は、第1引数であるHLOOKUP関数がエラーを返す場合に、第2引数で指定したもう一つのHLOOKUP関数を実行します。このようにして、複数のシートや範囲からデータを検索することが可能になります。
エラー処理と結果の表示改善
検索値が見つからない場合、HLOOKUP関数は標準ではエラーを表示します。このような場合には、IFERROR関数を使用してエラーメッセージを表示するか、空白にすることができます。
以下のフォーミュラを入力し、検索値が見つからない場合に「データなし」と表示させてみましょう。
=IFERROR(HLOOKUP(検索値,範囲, 行, 真偽値),"データなし")
このようにして、HLOOKUP関数を使いやすく改善できます。
本章では、HLOOKUP関数の応用例を3つ紹介しました。次の章では、VLOOKUP関数の応用例について解説していきます。
3章: VLOOKUP関数の応用例
VLOOKUP関数の応用例についても、具体的なケースを通じて学んでいきましょう。以下の3つの応用例を理解することで、VLOOKUP関数の活用範囲が広がります。
複数の条件に合わせたデータの検索
単一の検索条件だけでなく、複数の条件に基づいてデータを検索する場合もあります。その際、VLOOKUP関数を>&と組み合わせて検索条件を連結させます。
例えば、以下のデータがあります。
A B C 1 部門 社員番号 名前 2 営業 100 山田 3 営業 110 高橋 4 経理 120 田中 5 経理 130 佐藤
営業部門の社員番号100番の名前を検索する場合、「=VLOOKUP(“営業”&”100”, A2:C5, 3, FALSE)」と入力します。すると「山田」という結果が得られます。
部分一致検索とワイルドカードを使った検索
検索したいデータが完全一致ではなく部分一致の場合、VLOOKUP関数の第4引数にTRUEを指定します。また、検索条件にワイルドカード（?、*）を組み合わせることで、部分一致の範囲を調整できます。
例えば、姓が「田」の人物を検索する場合、「=VLOOKUP(“田*”,A2:C5, 3, TRUE)」と入力します。すると「田中」という結果が得られます。ただし、この方法はデータ範囲が昇順にソートされている必要があります。
INDEX関数やMATCH関数との組み合わせ
INDEX関数は、指定された範囲から行番号と列番号に基づいて値を返します。MATCH関数は、指定された検索値の位置を返します。これら2つの関数を組み合わせて、VLOOKUP関数の機能を拡張できます。
上記のデータを用いて、以下のフォーミュラを入力し、営業部門100番の名前を取得します。
=INDEX(A2:C5,MATCH(100,B2:B5,0),3)
この組み合わせを使用すると、1つの条件だけでなく複数の条件を考慮した検索が可能になり、VLOOKUP関数以上の応用ができます。
本章では、VLOOKUP関数の応用例を3つ紹介しました。次の章では、HLOOKUP・VLOOKUP関数の限界と解決策について解説していきます。
4章: HLOOKUP・VLOOKUP関数の限界と解決策
これまでHLOOKUP関数とVLOOKUP関数の機能と応用方法について学びましたが、これらの関数にはいくつかの制限があります。この章では、それらの限界と対処法について解説し、より効率的にデータ検索が行えるようになりましょう。
問題点と対処法の紹介
まず、HLOOKUP関数とVLOOKUP関数の問題点と対処法を整理してみましょう。
- 検索値がデータの左端または上端にある必要がある。
- 対処法: INDEX関数とMATCH関数を組み合わせて使用する。
- 検索値が範囲内に存在しない場合エラーが発生する。
- 対処法: IFERROR関数と組み合わせエラー時の表示を変更する。
- 検索範囲が昇順にソートされている必要がある（部分一致検索の場合）。
- 対処法: データをソートするか、真偽値を0(FALSE)に設定して完全一致検索を使用する。
データの構造や並び替えの影響
HLOOKUP関数やVLOOKUP関数は、データの構造や並び替えによって検索結果が変わる場合があります。データが追加・削除されたり、並び順が変わると、関数が返す値が正しいものとは限らなくなります。これを避けるためには、検索範囲に対してインデックス番号ではなく列・行名を用いたり、Excelのテーブル機能を活用することがあります。
参照関数であるXLOOKUP関数の活用
Excelでは、HLOOKUP関数やVLOOKUP関数の代替となる新しい関数「XLOOKUP関数」が利用可能です。この関数は、HLOOKUP関数やVLOOKUP関数の制限を克服するために設計されています。
XLOOKUP関数の書式は以下のようになります。
XLOOKUP(検索値, 検索範囲, 結果範囲, エラー時の値, 検索オプション)
XLOOKUP関数は、検索値がデータの左端または上端にある必要がなく、エラー時の値を設定できるため、HLOOKUP関数やVLOOKUP関数に比べて柔軟かつ強力です。また、検索オプションを指定することで、昇順・降順ソートの制限も緩和されます。
これらの点を踏まえ、データの検索・参照作業を効率化するためには、HLOOKUP関数やVLOOKUP関数の制限を理解し、対処法やXLOOKUP関数の利用も検討することが重要です。
ここまでで、HLOOKUP関数とVLOOKUP関数の基本・応用方法および制限と対処法について学びました。次の章では、実践的な業務での応用例を紹介していきます。
5章: 実践！業務で役立つ応用例
最後に、実際の業務で役立つHLOOKUP関数とVLOOKUP関数の応用例を紹介します。以下の3つのシチュエーションでの活用方法を学びましょう。
社員の成績やランク付けの検索
社員の評価やランク付けを行う場合、VLOOKUP関数を活用して社員番号から成績やランクを検索することができます。
以下のようなデータがあるとします。
A B C 1 社員番号 成績 ランク 2 100 85 A 3 200 70 B 4 300 60 C
社員番号100番の成績を調べるには、「=VLOOKUP(100, A2:C4, 2, FALSE)」と入力します。また、ランクを調べるには、「=VLOOKUP(100, A2:C4, 3, FALSE)」と入力します。
売上データに基づいた商品情報の検索
売上データに基づいて商品情報を調べる場合も、VLOOKUP関数が役立ちます。商品コードから商品名や単価を検索できます。
以下のようなデータがあるとします。
A B C 1 商品コード 商品名 単価 2 P01 りんご 100 3 P02 みかん 80 4 P03 バナナ 120
商品コード「P02」の商品名を調べるには、「=VLOOKUP(“P02”, A2:C4, 2, FALSE)」と入力します。単価を調べるには、「=VLOOKUP(“P02”, A2:C4, 3, FALSE)」と入力します。
顧客管理や在庫管理での活用方法
顧客情報や在庫情報を管理する場合でも、VLOOKUP関数を活用できます。顧客IDから顧客の氏名や連絡先、商品コードから在庫数や納入日を検索できます。
顧客情報の例をご紹介します。
A B C 1 顧客ID 氏名 連絡先 2 C01 山田太郎 090-XXXX-XXXX 3 C02 鈴木一郎 080-YYYY-YYYY 4 C03 佐々木二郎 070-ZZZZ-ZZZZ
顧客ID「C01」の氏名を調べるには、「=VLOOKUP(“C01”, A2:C4, 2, FALSE)」と入力します。連絡先を調べるには、「=VLOOKUP(“C01”, A2:C4, 3, FALSE)」と入力します。
これでHLOOKUP関数とVLOOKUP関数の応用例について学ぶことができました。ぜひこれらの知識を活かして、日々の業務を効率化させましょう。
コメント