LOOKUP、VLOOKUP、HLOOKUP関数の検索範囲は一列もしくは一行しか検索することが出来ません。
こういった表があった場合に「パーツ７」から何が出来るのか関数で出したい場合の処理です。HLOOKUPで検索結果から横2列ずれた値を返す方法もありますが、これでは今回は出来ません。
検索範囲が複数の配列になる場合を今回解説します。
最終的にしたい事 ＆ 回答
こちらの表１を検索してパーツ１．２．３．４と何が完成品になるかを返したい処理です。
LOOKUPではＢ列を検索してＡ列を返すという事は出来ますが、Ｂ列～Ｆ列となるとLOOKUPでは対応出来ません。またMATCH関数も複数の配列に対応していない為、これも没。
以下説明が長くなるので先に回答を明記しておきます。
B6セル、パーツ１の完成品になるところの関数は
=INDEX ( $A$1:$A$3 , SUMPRODUCT ( ( $B$1:$F$3=A6 ) * ROW ( $B$1:$F$3 ) ) )
これで求めれます。
以下SUMPRODUCTも含めて解説していきます。
SUMPRODUCT
あまり使われる事のないSUMPRODUCT。結構色んな使い方があって便利です。今回もコレを使う事で検索が可能になります。
| =SUMPRODUCT ( 配列1 , [配列2] , [配列3] ,… ) 範囲または配列の対応する要素の積を合計した結果を返します。（Excel関数説明より） |
基本的な使い方は配列１と配列２のそれぞれの要素を掛けて、その合計を出すという事です。
関数を使わなかった場合こうなりますよね。
=B2*C2 + B3*C3 + B4*C4 + B5*C5 + B6*C6 これだと数が増えたときに面倒なのでSUMPRODUCTを使います。
=SUMPRODUCT(B2:B6,C2:C6)
以上が基本的な使い方です。これをちょっと分解してみます。配列である B2:B6 と C2:C6 がどのように処理されているのか。
B2:B6 とは言わば B2,B3,B4,B5,B6 ですね。これを C2,C3,C4,C5,C6 と掛けあわせているんですね。配列１の要素１個目と配列２の要素１個目で掛けあわしています。で最後に合計しています。
配列１ ＝ B2:B6
配列２ ＝ C2:C6
条件に合う数値を集計
SUMPRODUCTの応用になります。条件に合う種類のみを集計する事が出来ます。
この表で果物だけを集計する場合
=SUMPRODUCT ( ( D2:D6 = “果物” ) * B2:B6 , C2:C6 )
単価の配列１に D2:D6=”果物” を掛けているのが特徴です。
何故条件を掛けるのか？この例も分解してみましょう。
前提として理解しておいてほしいのはコレ
「もし D2=”果物” であった場合TRUE（真）である１が返ります。もし”果物”でなかったら0が返ります。」
さらに解りやすく６行目まで指定せず２行目だけでSUMPRODUCTを書いた場合。
=SUMPRODUCT ( D2=”果物” * B2 , C2 )
こうなりますね。２行目は「りんご」でありD2が”果物”という種類になっていますので 1*B2 となります。
配列なので６行目まで指定しても上記の繰り返しになります。そして果物でない行についてはB2に0が掛かりますので、SUMPRODUCTの合計に計算されないという仕組みです。
何行目にあるかを返す
応用２です。
検索値が検索範囲の中で調べ、何行目のセルなのか？
=SUMPRODUCT( ( A1:B5 = “牛乳” ) * ROW ( A1:B5 ) )
先程の応用１が理解出来ればここの理解も早いのではないでしょうか？
A1が”牛乳”ならば 1 、さらにROW(A1)は１行目なので 1 で結果 1 となります。
この例ではB3に牛乳がありますので B3=”牛乳”の結果 1 とROW(B3)の 3 行目を掛けて3が返ります。
仮に複数”牛乳”が存在する場合、その行数が足されます。（A4が牛乳であれば4なので上記の3と足されて７が返ります）
INDEXとSUMPRODUCT
最終章です。
INDEXはこちらで解説しているものやHLOOKUPで検索結果から横2列ずれた値を返すで応用を説明していますので、詳しい事は省きます。
INDEXの配列範囲をA1:A3の完成品列に指定し、その何行目にパーツがあるかを返せればいいだけなので、上記応用２を使い行数を指定します。
もう一度最終的な式を示します。
=INDEX ( $A$1:$A$3 , SUMPRODUCT ( ( $B$1:$F$3=A6 ) * ROW ( $B$1:$F$3 ) ) )
これで完成！！
