スポンサードリンク | |
作成:2020/2/3,更新:2023/3/13
XLOOKUP関数の構文 | 検索値と一致する行の値をXLOOKUP関数で返す |
#スピル! エラーについて | |
一致モードの使い方 | 検索モードの使い方 |
検索条件が2列(複数)ある場合 | XLOOKUP関数で縦横に検索する |
必要な列だけをXLOOKUP関数で取り出す | |
テーブルでXLOOKUP関数を使う | テーブルから2つのデータを取り出す |
VLOOKUP関数も機能アップしていました |
XLOOKUP関数はLOOKUP関数系の新しい関数で、VLOOKUP関数やHLOOKUP関数の後継となる関数です。
スピルが使用できる関数となっています。
XLOOKUP関数の使い方を詳細に説明しています。
Excel for Microsoft 365 (サブスクリプション版) でXLOOKUP関数が使えるようになりました。
Office365は2020/4/22からは Microsoft365となりました。
なお、2021/10/5から市販されている Excel2021(永続ライセンス版:Office2021)でも使用できます。
Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
Spill(スピル) | XLOOKUP関数 | |
Excel2016 | × | × |
Excel2019 | × | × |
Excel2021 | ○ | ○ |
Excel for Microsoft365 | ○ | ○ |
検索値 | 参照する値、セル | |
検索範囲 | 検索する配列または範囲 | |
戻り範囲 | 返す配列または範囲 | |
[見つからない場合] | 一致するものが見つからない場合は、[見つからない場合] に指定したテキストを返します。 | |
[一致モード] | 一致の種類を指定します | |
0 | 完全一致。(これが既定の設定です) 見つからない場合は、#N/A が返されます。 | |
-1 | 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 | |
1 | 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 | |
2 | *、?、および 〜 が特別な意味を持つワイルドカードになります。 ワイルドカードを使った検索が可能になります。 |
|
[検索モード] | 使用する検索モードを指定します。 | |
1 | (これが既定の設定です)先頭の項目から検索を実行します。 | |
-1 | 末尾の項目から逆方向に検索を実行します。 | |
2 | 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
|
-2 | 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
VLOOKUP関数と同じような使い方ですが、検索値が左端列に無くてもOKです。
XLOOKUP関数の[一致モード]は省略すると、既定値の 0とみなされ 、完全一致で検索されます。
ちなみに、VLOOKUP関数の引数の検索の型を省略すると、既定値のTRUEとみなされて検索値を超えない最大値が返されます。
完全一致で検索するにはFALSEまたは 0 を指定する必要がありました。
XLOOKUP関数は見つからない場合は、#N/A が返されますが、引数の[見つからない場合]を設定すると、エラーの回避ができます。次の例では"該当なし"としています。
VLOOKUP関数などではIFERROR関数などと組み合わせてエラー処理をする必要がありました。
VLOOKUP関数と同じような使い方です。
検索範囲の指定方法が異なります、また戻り範囲に複数列を指定できます。
B | C | D | E | F | G | H | |
2 | 商品一覧 | ||||||
3 | コード | 商品名 | 価格 | コード | 商品名 | 単価 | |
4 | K-01 | りんご | 250 | K-03 | |||
5 | K-02 | みかん | 210 | ||||
6 | K-03 | バナナ | 300 | ||||
7 | K-04 | 柿 | 320 |
検索値が見つからない場合は "該当なし" が返されます。
VLOOKUP関数では #N/A エラーが返されていましたが、XLOOKUP関数ではエラー処理まで可能になっています。
VLOOKUP関数では処理できなかった使い方です。
検索する範囲が返す値の列より右側にある場合です。(検索値が左端列ではない場合)
B | C | D | E | F | G | |
2 | 商品一覧 | |||||
3 | コード | 商品名 | 価格 | 商品名 | コード | |
4 | K-01 | りんご | 250 | バナナ | ||
5 | K-02 | みかん | 210 | |||
6 | K-03 | バナナ | 300 | |||
7 | K-04 | 柿 | 320 |
以前は、INDEX関数とMATCH関数で =INDEX(B4:B7,MATCH(F4,C4:C7,0)) としていたと思われます。
商品名が昇順に並んでいないので、MATCH(F4,C4:C7,0) と完全一致で検索する必要があります。
XLOOKUP関数の方がかなり簡略な数式になるのがわかります。
0 | 完全一致。(これが既定の設定です) 見つからない場合は、#N/A が返されます。 |
-1 | 完全一致。 見つからない場合は、次の小さなアイテムが返されます。 |
1 | 完全一致。 見つからない場合は、次の大きなアイテムが返されます。 |
2 | *、?、および 〜 が特別な意味を持つワイルドカードになります。 |
C4セルの数式は =XLOOKUP(B4:B12,G4:G6,I4:I6,"なし",0) とします。
検索値と完全一致しないと[見つからない場合]の「なし」が表示されます。
検索範囲に完全一致するものがあるときだけ、値が返されます。
なお、0 は既定値なので省略することができます。
数式は =XLOOKUP(B4:B12,G4:G6,I4:I6,"なし") とすることができます。
見つからない場合は、次の小さなアイテムが返されます。
C4セルの数式は =XLOOKUP(B4:B12,G4:G6,I4:I6,"なし",-1) とします。
完全に一致する値がないときは、下の値が選ばれます。
つまり、D7セルの 50 の場合、一致するのがG4:G6に無いので、下の値 40 を選び、その同じ行のI列の値 △ が返されます。
なお、VLOOKUP関数の場合は、=VLOOKUP(B4:B12,G4:I6,3,TRUE) とします。
なお、VLOOKUP関数は既定値がTRUEナノで省略して、=VLOOKUP(B4:B12,G4:I6,3) とすることができます。
(この数式は引数をB4:B12として、スピルの機能を使っています。)
XLOOKUP関数の[一致モード]が -1 と同じ結果が得られます。
(VLOOKUP関数では表引きのキー(下図では右の表のG列の値 G4:G6 が昇順になっている必要があります)
見つからない場合は、次の大きなアイテムが返されます。
C4セルの数式は評価Bの表を使っています。
数式は =XLOOKUP(B4:B12,G10:G13,I10:I13,"なし",1) とします。
完全に一致する値がないときは、上の値(大きい値)が選ばれます。
つまり、E7セルの 50 の場合、一致するのがG10:G13に無いので、上の値(大きい値) 70 を選び、その同じ行のI列の値 「良」 が返されます。
表引きのキーが降順の場合(評価一覧の得点が降順になっています)
結果は上記と同じでした。XLOOKUP関数は完全一致で検索するので検索範囲は昇順でも降順でも関係ないということです。
[一致モード]が 2 の場合は、ワイルドカード文字を使った検索が可能になります。
ここでは、該当するデータが複数ある場合に、データの先頭から、末尾からといった[検索モード]を使います。
ワイルドカード文字の使い方は Excel 2016で関数の検索条件にワイルドカード文字を使う をご覧ください。
1 | (これが既定の設定です)先頭の項目から検索を実行します。 |
-1 | 末尾の項目から逆方向に検索を実行します。 |
2 | 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
-2 | 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。 |
E4セルには =XLOOKUP("*田",B4:B9,B4:C9,"なし",2,1) と入力されています。
末尾が田 というのをワイルドカードを使って *田 として検索しています。
一致モードが2、検索モードが1 と設定しています。
よって、先頭から検索して最初に見つかった「今田」が表示されています。
E4セルには =XLOOKUP("*田",B4:B9,B4:C9,"なし",2,-1) と入力されています。
一致モードが2、検索モードが-1 と設定しています。
よって、末尾から検索して最初に見つかった「山田」が表示されています。
XLOOKUP関数を使うと作業列は不要になりました。
H4セルには =XLOOKUP(H2&"_"&H3,C3:C8&"_"&D3:D8,E3:E8) と入力されています。
「&"_"&」は万が一を考えて、わざわざ付け加えています。無くてもかまわない場合が多いとは思いますが・・・。
VLOOKUP関数の時には、検索条件に2列の値を使うときは作業列を作成する必要がありました。
H4セルの数式は =VLOOKUP(H2&"_"&H3,B3:E8,4,FALSE) とします。
下図のC12セルには、=XLOOKUP(C10,B4:B7,XLOOKUP(C9,C3:E3,C4:E7)) と入力されています。
XLOOKUP(C9,C3:E3,C4:E7) で D4:D7 が返されます。
=XLOOKUP(C10,B4:B7,D4:D7) → 35000 となります。
XLOOUP関数だけで計算できるようになりましたが、従来のように INDEX,MATCH関数で求めることもできます。
数式は =INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0)) としました。
下図の成績表から必要な列だけを取り出します。
氏名を入力すると国語と社会の得点を取り出します。
D15セルに =CHOOSECOLS(XLOOKUP(C15,C3:C11,D3:H11),XMATCH(D14:E14,D2:H2)) と入力します。
XLOOKUP関数で氏名にっ該当するデータを取り出し、CHOOSECOLS関数で必要な列のデータを取り出します。
必要な列はXMATCH関数でその位置を検索しています。
CHOOSECOLS関数はExcel for Microsoft365で使用できる関数です。(2022/9/2に使用できるのを確認しました)
指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方
FILTER関数を使うと下の数式のようになります。
=CHOOSECOLS(FILTER(D3:H11,C3:C11=C15),XMATCH(D14:E14,D2:H2))
FILTER関数とCOUNTIF関数でも同じことが可能です。
=FILTER(FILTER(D3:H11,C3:C11=C15),COUNTIF(D14:E14,D2:H2))
B2:H11をテーブルに変換しています。テーブル名は「成績表」としています。
氏名を検索して国語の得点を返す数式は =XLOOKUP($C15,成績表[氏名],成績表[国語]) となります。
氏名から国語〜合計の得点を表示する例です。
数式は =XLOOKUP(C15,成績表[氏名],成績表[[国語]:[合計]]) としました。
戻り範囲に 成績表[[国語]:[合計]] と複数列を指定するのがポイントです。
すみません・・・ここでは XLOOKUP関数は使いません。
VLOOKUP関数の時にも2つの条件でデータを取り出したいといった要望がありました。
XLOOKUP関数でも同様な要望があると想像されますが、スピルが使えるようになり、FILTER関数でこれが実現されます。
フィルター関数で2つの条件でデータを取り出した例です。
氏名が「近藤」または「植田」のデータを取り出す例です。
ここで使ったテーブルのテーブル名は「成績表」です。
数式は =FILTER(成績表,(成績表[氏名]=”近藤")+(成績表[氏名]="植田")) としました。
FILTER関数は FILTER関数の使い方 をご覧ください。
従来のVLOOKUP関数で複数の検索値を検索することができてしまいました。(SPILLのおかげです)
D15セル数式は =VLOOKUP($C$15:$C$18,$C$3:$H$11,COLUMN(B2),FALSE) としました。
下図では、D15セルをオートフィルで、H15セルまで数式をコピーしています。
XLOOKUP関数では、複数列の値を返すことができるので、
D15セルに =XLOOKUP(C15,$C$3:$C$11,$D$3:$H$11) とします。
下図では、D15セルをオートフィルで、D18セルまで数式をコピーしています。
スポンサードリンク
PageViewCounter
Since2006/2/27