Excel(エクセル）のIFS関数は、複数の条件で表示を分岐させることができるIF関数の新関数です。
また、XLOOKUP関数は、VLOOKUP関数の後継関数として登場した、表から検索値を抽出する大変便利な関数です。
このIFS関数の中に、XLOOKUP関数を組み合わせて利用することで、『抽出値を別の値に変換して表示』するなど、便利な使い方が存在しています。
今回は、『IFS関数とXLOOKUP関数を組み合わせて利用』する便利な活用方法を解説します。
IFS関数とVLOOKUP関数の組み合わせ方法は以下を参考👇
エクセルのIFS関数は、IF関数の進化版の関数
IFS関数は、IF関数を複数条件で利用することができるようになったIF関数の新関数です。
IFS関数は、1 つ以上の条件が満たされているかどうかをチェックして、最初の条件に対応する値を優先して表示します。
IFS関数の構文と引数は以下のとおりです。
=IFS(論理式1, 真の場合1, 論理式2, 真の場合2,論理式3, 真の場合3, ..., ）
「論理式１」に当てはまる場合は「真の場合１」を表示します。
次に「論理式２」に当てはまる場合は「真の場合２」を表示します。次に「論理式３」に当てはまる場合は「真の場合３」を表示する・・・といった関数の仕組みです。(最大127個の条件設定が可能）
下の図がIFS関数のイメージ図です。
論理式１に当てはまる場合は「◎」、論理式２に当てはまる場合は「〇」、論理式３に当てはまる場合は「△」、どれにも当てはまらない場合は「×」といったように、条件に当てはまる場合の値を自在に設定することができます。
IFS関数で「条件に当てはまらない場合」「それ以外」を設定する際は、最後の２つの引数を「TRUE,それ以外の場合の値」を指定します。
例えば、下のようにIFS関数を作成すると、C3セルが80以上以外であれば、「×」を表示させることができます。
実際にIFS関数の使った実例を１つ紹介します。
下の表のD3セルにIFS関数を利用して、点数を『80点以上は〇』、『40～79点は△』、『40未満は×』の３つで評価します。
=IFS(C3>=80,"〇",C3>=40,"△",TRUE,"×")
最後の引数が「TRUE」っていうのがIFS関数の特徴だよ
最後の引数に「それ以外」を表す”TRUE”を使わずに、「C3<40」という条件を設定しても結果は同じです。
=IFS(C3>=80,"〇",C3>=40,"△",C3<40,"×")
IFS関数の使い方は以下を参考👇
エクセルのXLOOLUP関数の使い方(おさらい)
次に、エクセルのXLOOKUP関数(エックスルックアップ)の使い方をおさらいします。
XLOOKUP関数の基礎について熟知されている方は、次の見出しまで読み飛ばしてください。
XLOOKUP関数は、VLOOKUP関数とHLOOKUP関数の両方の機能を兼ね備え、さらに大幅に機能が強化された新関数です。
XLOOKUP関数は、
指定した検索値に『当てはまる値』を、指定した検索範囲から縦方向や横方向で調べて、指定した位置から抽出することが出来る
関数です。
XLOOKUP関数の構文は下のとおりで、６つの引数が存在します。
＝XLOOKUP(検索値,検索範囲,戻り範囲, [見つからない場合] , [一致モード] , [検索モード] ）
長く感じるかもしれませんが、XLOOKUP関数の後半３つの引数は省略可能な上、VLOOKUP関数より引数の設定が簡単なので、XLOOKUP関数の方が断然便利です。
XLOOKUP関数を使った簡単な事例を１つ紹介します。
下の表から、商品名が「風邪薬」の商品番号をXLOOKUP関数を使ってH3セルに抽出することができます。
=XLOOKUP(G3,D3:D8,B3:B8)
IFS関数とXLOOKUP関数を組み合わせて出来ること
使い方の実例を紹介する前に、『IFS関数とXLOOKUP関数を組み合わせる』ことで、実現できることを紹介します。
IFS関数の中にXLOOKUP関数を挿入しますが、組み合わせのパターンが２つあるので、それぞれの内容を紹介します。
IFS関数の論理式にXLOOKUP関数を利用
XLOOKUP関数は、『表を検索して情報を抽出する関数』です。
一方で、IFS関数は『条件を指定して、セルの値によって他のセルの値を変換する関数』です。
IFS関数の１つ目の引数「論理式」にXLOOKUP関数を挿入することで、
『表を検索して該当する値を抽出し、その抽出値を条件によって別の値を変換する』
ことが出来ます。
XLOOKUP関数を挿入する引数の位置は、以下のとおりです。
下の図解で説明すると、上半分がXLOOKUP関数の領域で「表から値を抽出」します。
下半分がIFS関数の領域で抽出された値に条件を設定して、条件に合うか合わないかで表示する結果を変換します。
IFS関数を使って、XLOOKUP関数の結果を直接違う値に変換する、ってことだね。
IFS関数の「真の値」にXLOOKUP関数を利用
IFS関数の引数「真の値1」や「真の値２」にXLOOKUP関数を挿入することで、
『IFS関数の論理式に合った値を、表の中から抽出』
ことも出来ます。
XLOOKUP関数を挿入する引数の位置は、以下のとおりです。
下の図解で説明すると上半分がIFS関数で、下半分がXLOOKUP関数の領域です。
IFS関数のどの論理式の条件に当てはまるかによって、表から抽出する値を変更します。
IFS関数とXLOOKUP関数の組み合わせる方法（実例１）
それでは、IF関数とXLOOKUP関数を組み合わせた実例を紹介します。
下の販売高リストを使って、選択した商品番号の販売高が『1000(千円）以上であれば「〇」』、『600以上1,000(千円)未満であれば「△」』、『600(千円）未満であれば「×」』をG3セルに直接表示させます。
それでは、関数を作成する手順を紹介します。
手順１．IFS関数の「論理式１」にXLOOKUP関数を挿入
G3セルにIFS関数を挿入します。
IFS関数の引数「論理式１」に、売上高が1,000千円以上の商品番号かどうかを表から判定するために、XLOOKUP関数を挿入します。
=IFS(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=1000,
手順２．IFS関数の「真の値１」を設定
IFS関数の「論理式１」に当てはまる場合は『〇』と表示するため、引数「真の値１」に”〇”と入力します。
=IFS(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=1000,"○",
手順３．IFS関数の「論理式２」にXLOOKUP関数を挿入
次に引数「論理式２」に、売上高が600千円以上の商品番号かどうかを判定するために、２つ目のXLOOKUP関数を挿入します。
=IFS(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=1000,"○",
XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,
手順４．IFS関数の「真の値２」を設定
IFS関数の「論理式２」に当てはまる場合は『△』と表示するため、引数「真の値２」に”△”と入力します。
=IFS(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=1000,"○",
XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,"△",
手順５．IFS関数の「論理式３」「真の値３」を設定
IFS関数で「それ以外」を設定するために、最後の２つの引数は「TRUE,”×”」を指定します。
３つ目のXLOOKUP関数を挿入しても関数は作成出来ますが、関数が長くなるので、TRUEを利用します。
=IFS(XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=1000,"○",
XLOOKUP(F3,$B$2:$B$10,$D$2:$D$10)>=600,"△",TRUE,"×")
手順６．IFS関数とXLOOKUP関数の組み合わせが完成
IFS関数の最後の条件を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、下のように、商品番号「20」の販売高は600(千円）と800(千円）未満のため、Ｇ3セルは「△」と自動で表示されます。
IFS関数とXLOOKUP関数の組み合わせる方法（実例２）
IFS関数とXLOOKUP関数を組み合わせた２つ目の使い方をご紹介します。
下の価格リストを使って、H3セルの会員区分に合わせて、表から該当する価格を抽出しI3セルに表示させます。
手順１．IFS関数の「論理式１」を設定
I3セルにIFS関数を挿入します。
IFS関数の引数「論理式１」に、H3セルが”会員”という条件を設定します。
=IFS(H3="会員",
手順２．IFS関数の「真の値１」にXLOOKUP関数を挿入
IFS関数の引数「真の値１」に、XLOOKUP関数を挿入して、表の中から商品番号にあった会員価格を抽出するよう設定します。
=IFS(H3="会員",XLOOKUP($G$3,$B$2:$B$8,$D$2:$D$8),
手順３．IFS関数の「論理式２」を設定
次に、IFS関数の引数「論理式２」に、H3セルが”非会員”という条件を設定します。
=IFS(H3="会員",XLOOKUP($G$3,$B$2:$B$8,$D$2:$D$8),H3="非会員",
手順４．IFS関数の「真の値２」にXLOOKUP関数を挿入
IFS関数の引数「真の値2」に、XLOOKUP関数を挿入して、表の中から商品番号にあった非会員価格を抽出するよう設定します。
=IFS(H3="会員",XLOOKUP($G$3,$B$2:$B$8,$D$2:$D$8),H3="非会員",XLOOKUP($G$3,$B$2:$B$8,$E$2:$E$8))
手順５．IFS関数とXLOOKUP関数の組み合わせが完成
「Enter」キーを押して関数を確定します。
そうすると、下図のように、商品番号「25」の非会員価格1,100円が自動で表示されます。
H3セルを会員に変更すると、会員価格900円も自動で表示されます。
