20 October, 2020

Vlookupに代わって、Xlookup関数の使用方法(初級+中級+上級)

XLookup関数の発表はExcelの一大事ですが、XLookup関数に関わるチュートリアルは1つもありません。したがって著者はXLookup関数に関わるチュートリアルを作成しました。

関数の解説

範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。

ヒント:簡単に言うと、条件を検索して値を返します。引数と順次をご覧ください。

書式の解説

Xlookup関数には合計6つの引数があり、そのうちの123は必須で、456はオプションです。(角括弧の“[]“は引数がオプションであることを示す記号です)

日本語に通訳すると、理解しやすくなります。

=Xlookup(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

引数の解説

1、検索値

Xlookup(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

ヒント:Xlookupの検索値は単一の値、複数の値またはワイルドカードに設定できます。

[例1]名前によって対応する国語の成績を検索します。

=XLOOKUP(A11,A2:A7,C2:C7)

[例2]白原俊介、成田信長と半沢花子の国語の成績を検索します。

=XLOOKUP(A11:A13,A2:A7,C2:C7)

ヒント:次の図から見ると、B11に設定した数式の中で範囲A11:A13の値を検索すると、対応する検索結果が3つのセルに返されます。 つまり、xlookup関数で検索する場合は、最初のセルに数式を設定すればいいです。

平均成績を計算したい場合は、averageを数式に追加すればいいです。これより、ただ1つの値が返されます。

=AVERAGE(XLOOKUP(A11:A13,A2:A7,C2:C7))

[例3]名前に“介”漢字が含まれる学生の国語成績を検索します。

=XLOOKUP(A11,A2:A7,C2:C7,2)

ヒント:4番目の引数が2の場合、検索値にワイルドカードを使用できます。 “*”は任意の文字を意味し、“?”は1つの文字を意味します。 検索結果は条件を満たす最初の値のみを返します。

名前が4文字で、“介”で終わる学生の国語の成績を検索したい場合は、“介”の前に???を使用し、任意の3字を意味します。

2、範囲または配列を検索し、範囲または配列を返す

Xlookup(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

ヒント:2、3番目の引数は、セルまたは配列に対応できます。検索の原理は、検索範囲または配列内の値を検索し、値が見つかった場合は結果を返します。

[例1]右から左方向に検索する

=Xlookup(A11,B2:B7,A2:A7)

[例2]下から上方向に検索する

=Xlookup(B5,A1:D1,A2:D2)

[例3]複数の条件を検索する

=Xlookup(A11&B11,A2:A7&B2:B7,D2:D7)

ヒント:1、2番目の引数は、複数の値と範囲に対応し、複数の値を検索できます。

[例4]同時に複数の列を検索する

=XLOOKUP(A11,A2:A7,B2:D7)

ヒント:3番目の引数が複数の列の範囲または配列である場合、数式は同時に複数の列の値を返します。

3、一致モード

Xlookup(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

ヒント:省略された5番目の引数のデフォルト値は0であり、完全一致を意味します。2の場合には、ワイルドカードを使用できます。(上記の例と同じです。)引数が -1と1の場合には、間隔を検索します。-1は小さいアイテムを返します。(Vlookupと同じ、最後の引数を省略する使用方法です。)、1は大きいアイテムを返します。

[例1]売上高からボーナスを検索する

=XLOOKUP(B9:B12,B2:B5,C2:C5,-1)

ヒント:5番目の引数が-1の場合に、最初の配列で小さなアイテムまたは最も近い数値が検出され、2番目の引数は昇順で並べ替える必要があります。

5番目の引数が1の場合には、列Bから大きなアイテムまたは最も近い数値を返します。

4、検索モード

Xlookup(検索値,検索範囲,戻り配列,[見つからない場合],[一致モード],[検索モード])

ヒント:6番目の引数が1の場合、先頭の項目から検索し、(デフォルト)、-1は末尾の項目から先頭へ検索します。2と-2はバイナリ検索であり、稀に使わないため、詳しい解説しません。この引数は主に条件に満たす最後の値を返します。

[例1]最後のA商品の価格を検索する

=xlookup(A11,B2:B7,C2:C7,0,-1)

Xlookup関数はoffice365の最新バージョンのみ使用できます。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です