Excel(エクセル）のIFS関数は、IF関数を複数の条件で利用できる新関数です。
また、HLOOKUP関数は表を横方向に検索して検索値を抽出する関数です。
このIFS関数の中に、HLOOKUP関数を組み合わせて利用することで、『抽出値を別の値に変換して表示』するなど、便利な使い方が存在しています。
今回は、『IFS関数とHLOOKUP関数を組み合わせて利用』する便利な活用方法を解説します。
IF関数とHLOOKUP関数の組み合わせ方法は以下を参考👇
IFS関数とHLOOKUP関数の使い方(おさらい）
IFS関数は、IF関数を複数条件で容易に利用することができるようになったIF関数の新関数です。
IFS関数は、1 つ以上の条件が満たされているかどうかをチェックして、最初の条件に対応する値を優先して表示します。
IFS関数の構文と引数は以下のとおりです。
=IFS(論理式1, 真の場合1, 論理式2, 真の場合2,論理式3, 真の場合3, ..., ）
実際にIFS関数の使った実例を１つ紹介します。
下の表のD3セルにIFS関数を利用して、点数を『80点以上は〇』、『40～79点は△』、『40未満は×』の３つで評価します。
IFS関数で「それ以外」を表すには、最後の２つの引数を「TRUE,文字列)」と指定します。
=IFS(C3>=80,"〇",C3>=40,"△",TRUE,"×")
最後の引数に「それ以外」を表す”TRUE”を使わずに、「C3<40」という条件を設定しても結果は同じです。
=IFS(C3>=80,"〇",C3>=40,"△",C3<40,"×")
IFS関数の使い方を詳しく確認したい方は、以下の記事を参考にしてください。
続いて、HLOOKUP関数の使い方をおさらいします。
HLOOKUP関数(エイチルックアップ)は、表を横方向に検索して情報を抽出する関数です。
HLOOKUP関数の構文は、検索値、検索範囲、行番号、検索方法の４つの引数で構成されています。
＝HLOOKUP(検索値、検索範囲、行番号、[検索方法]）
例えば、下の商品リスト表から商品番号”25”の商品名を検索するために、C9セルにHLOOKUP関数を挿入します。
=HLOOKUP(B11,$C$3:$L$6,3,FALSE)
そうすると、HLOOKUP関数を使った結果、商品番号”25”の商品名である”風邪薬”が返されます。
HLOOKUP関数の詳しい使い方は以下の記事で紹介していますので、参考にしてみてください。
IFS関数とHLOOKUP関数を組み合わせて出来ること
使い方の実例を紹介する前に、『IFS関数とHLOOKUP関数を組み合わせる』ことで、実現できることを紹介します。
IFS関数の中にHLOOKUP関数を挿入しますが、組み合わせのパターンが２つあるので、それぞれの内容を紹介します。
IFS関数の論理式にHLOOKUP関数を利用
HLOOKUP関数は、『表を検索して情報を抽出する関数』です。
一方で、IFS関数は『条件を指定して、セルの値によって他のセルの値を変換する関数』です。
IFS関数の１つ目の引数「論理式」にHLOOKUP関数を挿入することで、
『表を検索して該当する値を抽出し、その抽出値を条件によって別の値を変換する』
ことが出来ます。
HLOOKUP関数を挿入する引数の位置は、以下のとおりです。
下の図解で説明すると、上半分がHLOOKUP関数の領域で「表から値を抽出」します。
下半分がIFS関数の領域で抽出された値に条件を設定して、条件に合うか合わないかで表示する結果を変換します。
IFS関数を使って、HLOOKUP関数の結果を直接違う値に変換する、ってことだね。
IFS関数の「真の値」にHLOOKUP関数を利用
つづいて、２つ目の組み合わせパターンです。
IFS関数の引数「真の値1」や「真の値２」にHLOOKUP関数を挿入することで、
『IFS関数の論理式に合った値を、表の中から抽出』
ことが出来ます。
HLOOKUP関数を挿入する引数の位置は、以下のとおりです。
下の図解で説明すると上半分がIFS関数で、下半分がHLOOKUP関数の領域です。
IFS関数のどの論理式の条件に当てはまるかによって、表から抽出する値を変更します。
IFS関数とHLOOKUP関数を組み合わせた使い方
それでは、IF関数とHLOOKUP関数を組み合わせた事例を３つ紹介します。
事例１：商品番号を販売高で判定し〇×で評価
下の販売高リストを使って、IFS関数とHLOOKUP関数を組わせた事例を紹介します。
B8セルの商品番号の販売高が『600(千円）以上であれば「〇」』、『600(千円）未満であれば「×」』をC8セルに直接表示させます。
それでは、関数を作成する手順を紹介します。
手順１．IFS関数の「論理式１」にHLOOKUP関数を挿入
C8セルにIFS関数を挿入します。
IFS関数の引数「論理式１」に、売上高が600千円以上の商品番号かどうかを表から判定するために、HLOOKUP関数を挿入します。
=IFS(HLOOKUP(B8,$B$2:$H$4,3,FALSE)>=600,
手順２．IFS関数の「真の値１」に”〇”を指定
IFS関数の「論理式１」に当てはまる場合は『〇』と表示するため、引数「真の値１」に”〇”と入力します。
=IFS(HLOOKUP(B8,$B$2:$H$4,3,FALSE)>=600,"○",
手順３．IFS関数の２つ目の条件を指定
IFS関数で「それ以外」を設定するために、最後の２つの引数は「TRUE,”×”」を指定します。
=IFS(HLOOKUP(B8,$B$2:$H$4,3,FALSE)>=600,"○",TRUE,"×")
手順４．IFS関数を確定
IFS関数の最後の条件を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、下のように、商品番号「78」の販売高は500(千円）と600(千円）未満のため、C8セルは「×」と自動で表示されます。
販売高をHLOOKUP関数で検索してから、IFS関数で〇×判定するより、この方法を使った方が１つのセルでダイレクトに答えを求めることができます。
事例２：商品番号を販売高で判定し〇△×で評価
続いて、２つ目の事例を紹介します。
１つ目の事例に、１つ条件を増やして〇△×の３つで評価します。
下の販売高リストを使って、選択した商品番号の販売高が『700(千円）以上であれば「〇」』、『500以上700(千円)未満であれば「△」』、『500(千円）未満であれば「×」』をC9セルに直接表示させます。
手順１．IFS関数の「論理式１」にHLOOKUP関数を挿入
C8セルにIFS関数を挿入します。
IFS関数の引数「論理式１」に、売上高が700千円以上の商品番号かどうかを表から判定するために、HLOOKUP関数を挿入します。
=IFS(HLOOKUP(B9,$B$2:$H$4,3,FALSE)>=700,
手順２．IFS関数の「真の値１」に”〇”を指定
IFS関数の「論理式１」に当てはまる場合は『〇』と表示するため、引数「真の値１」に”〇”と入力します。
=IFS(HLOOKUP(B9,$B$2:$H$4,3,FALSE)>=700,"○",
手順３．IFS関数の２つ目の条件を指定
手順１，２と同じ要領で、IFS関数の２つ目の条件に、売上高が500千円未満の商品番号であれば「×」となるように引数を設定します。
=IFS(HLOOKUP(B9,$B$2:$H$4,3,FALSE)>=700,"○",HLOOKUP(B9,$B$2:$H$4,3,FALSE)<500,"×",
手順４．IFS関数の３つ目の条件を指定
IFS関数で「それ以外」の売上高は”△”と表示させるために、最後の２つの引数は「TRUE,△”」を指定します。
=IFS(HLOOKUP(B9,$B$2:$H$4,3,FALSE)>=700,"○",HLOOKUP(B9,$B$2:$H$4,3,FALSE)<500,"×",TRUE,"△")
手順５．IFS関数を確定
IFS関数の最後の条件を入力したら、「Enter」キーを押して関数を確定させてください。
そうすると、下のように、商品番号「15」の販売高は550(千円）と500～699(千円）の範囲の値のため、C9セルは「△」と自動で表示されます。
事例３：会員区分に合った価格を表示
IFS関数とHLOOKUP関数を組み合わせた３つ目の使い方をご紹介します。
下の価格リストを使って、C9セルの会員区分(会員、非会員)に合わせて、表から該当する価格を抽出しD9セルに表示させます。
手順１．IFS関数を挿入
D9セルにIFS関数を挿入します。
IFS関数の引数「論理式１」に、C9セルが”会員”という条件を設定します。
=IFS(C9="会員",
手順２．IFS関数の「真の値１」にHLOOKUP関数を挿入
IFS関数の引数「真の値１」に、HLOOKUP関数を挿入して、表の中から商品番号にあった会員価格を抽出するよう設定します。
=IFS(C9="会員",HLOOKUP($B$9,$B$2:$H$5,3,FALSE),
手順３．IFS関数の２つ目の条件を指定
手順１，２と同じ要領で、IFS関数の２つ目の条件に、C9セルが非会員であれば、非会員価格が表示されるように引数を設定します。
=IFS(C9="会員",HLOOKUP($B$9,$B$2:$H$5,3,FALSE),C9="非会員",HLOOKUP(B9,$B$2:$H$5,4,FALSE))
手順５．IFS関数を確定
「Enter」キーを押して関数を確定します。
そうすると、下図のように、商品番号「18」の非会員価格550円が自動で表示されます。
C9セルを会員に変更すると、会員価格500円も自動で表示されます。
