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の最新バージョンのみ使用できます。
コメントを残す