Excelには、特定の条件に基づいてデータを検索するためのさまざまな関数が用意されています。中でも、VLOOKUP、HLOOKUP、およびXLOOKUP関数は非常に便利です。このブログでは、これらの関数の使い方を具体的な例を用いて説明します。
VLOOKUP関数（Excel 2010以降）
VLOOKUP関数は、指定したキーを垂直（列方向）に検索し、対応するセルの値を返します。
構文
=VLOOKUP(検索値, 範囲, 列番号, [範囲検索])- 検索値: 検索する値
- 範囲: データが含まれる範囲
- 列番号: 戻り値を取得する列番号
- 範囲検索: （オプション）正確一致ならFALSE、近似一致ならTRUE
例
以下の例では、「D-101」という商品番号を持つ商品の価格を検索します：
=VLOOKUP("D-101", A2:D5, 4, FALSE)HLOOKUP関数（Excel 2010以降）
HLOOKUP関数は、指定したキーを水平（行方向）に検索し、対応するセルの値を返します。
構文
=HLOOKUP(検索値, 範囲, 行番号, [範囲検索])- 検索値: 検索する値
- 範囲: データが含まれる範囲
- 行番号: 戻り値を取得する行番号
- 範囲検索: （オプション）正確一致ならFALSE、近似一致ならTRUE
例
以下の例では、「D-101」という商品番号を持つ商品の価格を検索します：
=HLOOKUP("D-101", A2:A5, 3, FALSE)XLOOKUP関数（Microsoft 365およびExcel 2021）
XLOOKUP関数は、VLOOKUPやHLOOKUPの機能を拡張した新しい関数で、どの方向にも検索が可能です。Microsoft 365やExcel 2021で利用できます。
構文
=XLOOKUP(検索値, 検索範囲, 戻り値範囲, [見つからない場合], [一致条件], [検索モード])- 検索値: 検索する値
- 検索範囲: 検索範囲
- 戻り値範囲: 戻り値を取得する範囲
- 見つからない場合: （オプション）見つからない場合の値
- 一致条件: （オプション）一致条件
- 検索モード: （オプション）検索モード
例
以下の例では、「D-101」という商品番号を持つ商品の価格を検索します：
=XLOOKUP("D-101", A2:A5, D2:D5, "Not found", 0, 1)MATCH関数（Excel 2010以降）
MATCH関数は、指定した値が範囲内で最初に出現する位置を返します。検索した値の位置を取得する際に使用します。
構文
=MATCH(検索値, 検索範囲, [一致の種類])- 検索値: 検索する値
- 検索範囲: 検索範囲
- 一致の種類: 一致の種類（1:以下、0:完全一致、-1:以上）
例
以下の例では、「プリン」という商品の位置を検索します：
=MATCH("プリン", B2:B5, 0)INDEX関数（Excel 2010以降）
INDEX関数は、指定した行番号と列番号に基づいて範囲内のセルの値を返します。MATCH関数と組み合わせることで、特定のデータを取得する際に便利です。
構文
=INDEX(範囲, 行番号, [列番号])- 範囲: データ範囲
- 行番号: 行番号
- 列番号: （オプション）列番号
例
以下の例では、B2:D5範囲内の3行目、2列目の値を返します：
=INDEX(B2:D5, 3, 2)サンプルデータ
以下に、VLOOKUP、HLOOKUP、XLOOKUP、および関連するMATCH関数とINDEX関数を使用したサンプルデータを提供します。このデータをExcel関数の練習に使用してください。
Excel形式データダウンロード
以下は、Excelデータですので、ダウンロードして練習などに使用してください。
Excel-g701-1.xlsx （ダウンロード）
表形式
| 行 | 列A | 列B | 列C | 列D |
|---|---|---|---|---|
| 1 | 商品番号 | 商品名 | カテゴリ | 単価 |
| 2 | B-101 | 和菓子 | 食品 | 300 |
| 3 | B-102 | 洋菓子 | 食品 | 450 |
| 4 | D-101 | プリン | デザート | 600 |
| 5 | D-102 | 杏仁豆腐 | デザート | 160 |
| 6 | 検索結果 | |||
| 7 | VLOOKUP | =VLOOKUP(“D-101”, A2:D5, 4, FALSE) | ||
| 8 | HLOOKUP | =HLOOKUP(“D-101”, A2:A5, 3, FALSE) | ||
| 9 | XLOOKUP | =XLOOKUP(“D-101”, A2:A5, D2:D5, “Not found”, 0, 1) | ||
| 10 | MATCH | =MATCH(“プリン”, B2:B5, 0) | ||
| 11 | INDEX | =INDEX(B2:D5, 3, 2) |
CSV形式
以下は、上記のデータをカンマ区切りで記載したものです。このデータをコピーしてExcelに貼り付けて使用してください。
"商品番号","商品名","カテゴリ","単価"
"B-101","和菓子","食品","300"
"B-102","洋菓子","食品","450"
"D-101","プリン","デザート","600"
"D-102","杏仁豆腐","デザート","160"
"検索結果",,,
"VLOOKUP",,,"=VLOOKUP(""D-101"",A2:D5,4,FALSE)"
"HLOOKUP",,,"=HLOOKUP(""D-101"",A2:A5,3,FALSE)"
"XLOOKUP",,,"=XLOOKUP(""D-101"",A2:A5,D2:D5,""Not found"",0,1)"
"MATCH",,,"=MATCH(""プリン"",B2:B5,0)"
"INDEX",,,"=INDEX(B2:D5,3,2)"まとめ
ExcelでVLOOKUP、HLOOKUP、XLOOKUP関数、MATCH関数、およびINDEX関数を使用することで、特定の条件に基づいてデータを効率的に検索し、取得することができます。これらの関数を理解し活用することで、データの分析や管理をより効果的に行うことができます。このブログを参考にして、Excelを最大限に活用してください。
このブログが役に立ちましたら幸いです。さらにサポートが必要なことや質問があれば、お知らせください。Happy Excel-ing!
コメント