こんにちは！
いつもブログをご覧いただきありがとうございます。
今回はExcel関数についての上級編をお送りします。
これまでに投稿した初級編・中級編も併せてご覧いただけると嬉しいです!!
Excel関数：上級編 一覧
| 関数 | 概要 |
| xlookup | 指定範囲内のデータの検索を行い、対応する値を出力する |
| vlookup | 指定範囲内で縦方向のデータの検索を行い、対応する値を出力する |
| hlookup | 指定範囲内で横方向のデータの検索を行い、対応する値を出力する |
| index + match | 指定範囲内のデータの検索を行い、対応する値を出力する (複数条件可) |
Excel関数：上級編 個別解説
xlookup
“=xlookup(検索値, 検索範囲(配列), 戻り範囲(配列), [見つからない場合], [一致モード], [検索モード])”と
入力することで、検索範囲内で最初に見つかった検索値に対応する項目を出力します。
一致するものがない場合には最も近似するものに対応する項目を出力します。
一部ぱっと見で理解しにくそうな引数について個別にご説明します。
[見つからない場合]
検索値が見つからなかった場合、この引数に入力した文字列が出力されます。
省略した場合は”#N/A”と出力されます。
[一致モード]
以下の数値を入力することで対応するモードが指定されます。
・0 : 完全一致する値を検索します。見つからない場合は”N/A”と出力されます。
省略した場合は既定でこのモードになります。
・-1 : 完全一致または次に小さい値を検索します。
・1 : 完全一致または次に大きい値を検索します。
・2 : “*”、”?”、”~”を利用したワイルドカード一致で検索します。
”*”は任意の文字列、”?”は任意の一文字を意味します。
”~”は、検索値で”*”、”?”、”~”をそのまま文字として扱いたい場合にその文字の前に挿入します。
[検索モード]
以下の数値を入力することで対応するモードが指定されます。
・1 : 検索範囲の先頭から検索を行います。省略した場合は既定でこのモードになります。
・-1 : 検索範囲の末尾から検索を行います。
・2 : 昇順で並び替えられた検索範囲に対してバイナリ検索を行います。
検索範囲が昇順になっていない場合は無効な結果が返されます。
・-2 : 降順で並び替えられた検索範囲に対してバイナリ検索を行います。
検索範囲が降順になっていない場合は無効な結果が返されます。
・=xlookup(“バナナ”,A4:A7, B4:B7)
検索範囲 A4~A7で検索値”バナナ”に対応するB4~B7の値である100が出力されています。
・=xlookup(“みかん”,A4:A7, B4:B7)
検索範囲に”みかん”が存在しないため、”#N/A”と出力されています。
・=XLOOKUP(“みかん”,A4:A7, B4:B7,”リストに存在しない商品です”)
先ほどと同様、”みかん”が存在しないため、[見つからない場合]として入力した文字列が出力されています。
・=xlookup(E4,A4:A7,B4:B7,,-1)
検索値をE4セルに入力した”105″、一致モードを”-1″として検索しています。
“105”は検索範囲に存在しないため、一致モードに従って105より小さく、最も近似した値である”104″に
対応した”ぶどう”が出力されています。
・=xlookup(E4,B4:B7,A4:A7,,2)
一致モードを”2″としてワイルドカード検索を実行しています。
この場合、”ナ”を含む文字列を探しているので、該当する”バナナ”に対応する商品ID”102″
が出力されています。
・=xlookup(E4,B4:B7,B4:B7,,2)
一致モードを”2″としてワイルドカード検索を実行しています。
検索値”???”は3文字の値を意味するので、検索範囲で最初に該当するりんごが出力されています。
・=xlookup(E4,B4:B7,B4:B7,,2,-1)
先ほどと同様に三文字の値を検索していますが、検索モードで”-1″を指定しているため、
検索範囲の後ろから検索を行い、最初に該当するぶどうが出力されています。
・=xlookup(E4,A4:A7,B4:B7,,,2)
検索モードで”2″を指定しているため、検索範囲に対してバイナリ検索を実行しています。
バイナリ検索についての説明は省きますが、効率的に検索を行うモード、くらいに考えてください。
代わりに検索範囲が昇順に並んでいる必要があり、そうでないと正確な出力が得られない場合があります。
・=xlookup(E4,A4:A7,B4:B7,,,-2)
検索モードで”-2″を指定しているため、検索範囲に対してバイナリ検索を実行しています。
“-2″の場合は降順に並んでいる必要があるのですが、検索範囲は昇順になっているため正しい値が
得られていません。
関数はそのまま、検索範囲を降順に並び替えることで正しい結果が得られるようになりました。
vlookup
“=vlookup(検索値、検索範囲、列番号、[検索方法])”と入力することで、検索範囲の再左列を検索し、見つかった検索値に対応する項目を出力します。
[検索範囲]
指定した検索範囲の一番左の列に対して検索が実行されます。
例えば”A3:C6″とした場合、検索は”A3:A6″に対して実行されます。
[列番号]
検索範囲の列番号を指定することでヒットした検索値に対応する列番号の値が出力されます。
検索範囲が”A3:F6″の場合、A列が1でB列が2…F列が6となります。
[検索方法]
“TRUE”または”FALSE”で指定します。
・TRUE : 近似一致で検索します。完全一致しない場合は検索値より小さい値で最も近いものがヒットします。
この引数を省略した場合は既定でこちらになります。
なお、”こちらの場合は左端列が文字列順または数値順に並べ替えてある必要があります。
・FALSE : 完全一致で検索を行います。
先に解説したxlookupがvlookup改良版の関数となっていて、より柔軟なため基本的にはそちらが利用できればあまり利用することはないかと思います。
ただし、xlookupが使えない環境(Excel 2016やExcel 2019など)もあるので、そういった場合はこちらを利用する機会もあるかもしれません。
・=vlookup(D3,A3:B6,2)
[検索方法]を省略=近似一致で検索しているため、”140″より小さく近い値である”130″に対応する
“オレンジ”が出力されています。
・=vlookup(D3,A3:B6,2,FALSE)
先ほど検索値は同じですが、[検索方法]を”FALSE”=完全一致で検索しており、該当する値がないため
“#N/A”と出力されています。
hlookup
“=hlookup(検索値, 範囲, 行番号, [検索方法])”と入力することで、検索範囲の再上行を検索し、見つかった検索値に対応する項目を出力します。
vlookupが列(縦方向)に検索を行っていたのに対し、こちらは行(横方向)に検索を行う関数となっています。
[検索範囲]
指定した検索範囲の一番上の行に対して検索が実行されます。
例えば”B3:E4″とした場合、検索は”B3:E3″に対して実行されます。
[行番号]
検索範囲の行番号を指定することでヒットした検索値に対応する行番号の値が出力されます。
検索範囲が”B3:E7″の場合、B3:E3が1でB7:E7が5となります。
こちらもvlookup同様、xlookupが改良版の関数となっていますので、基本的にはそちらを利用することが
多いかと思います。
・=hlookup(B6,B3:E4,2)
検索値”140″が範囲内に存在しないため、近似値である”130″に対応する”オレンジ”が出力されています。
index + macth
index関数とmatch関数を組み合わせることで検索を行います。
部分一致での検索や複数条件の指定など、柔軟な検索が可能です。
index関数は”=index(配列, 行番号, [列番号])”と入力することで、指定した範囲内の特定の位置にある
値を出力します。
match関数は“=match(検査値, 検査範囲, [照合の型])”と入力することで検索値の相対的な位置を
出力します。
index関数の引数としてmatch関数を入れ込むことで、match関数で出力した相対位置を利用して
配列内の検索を実行します。
・=index(D4:D7,match(F4&G4,(C4:C7&A4:A7),0),0)
match関数でC4:C7がF4=”りんご”で、A4:A7がG4=”2025/1/2″である記録の相対位置を探しています。
該当するのは上から3番目の値なので、index関数で指定したD4:D7にある売上のうち、3行目の値である”1200″
が出力されています。
・=INDEX(D4:D7,MATCH(F4&G4,(C4:C7&A4:A7),0),0)
“*”や”?”を利用したワイルドカード検索も可能です。
今回の場合、”か”の前に任意の文字列、後ろに任意の1文字が入る値を検索しています。
該当する”みかん”ともう一つの検索条件である”2025/1/2″でヒットするのは4行目の値なので、
その売り上げ900が出力されています。
最後に
Excelでよく使う関数：上級編 をご覧いただきありがとうございます。
使い慣れていないと少しわかりにくい部分もあるかもしれませんが、ご理解の一助になれていれば幸いです。
また、初級編から通してみていただいた方には多大なる感謝を申し上げます。
今後ともお役立ち情報をお届けしていく予定ですので、当ブログをよろしくお願いいたします!!
本記事は検証結果を提供することが目的であり、閲覧者様側での作業を保証するものではありません。自己責任においてあくまで参考としてください。 当サイトの閲覧・利用によって発生した損害に関しては一切責任を負いかねます。また本記事の内容は投稿時点のものであり、差異が発生する場合がございます。ご了承ください。
