複数行・列ある検索範囲のLOOKUP方法

数式 / 関数

e22-1.png
LOOKUP、VLOOKUP、HLOOKUP関数の検索範囲は一列もしくは一行しか検索することが出来ません。
こういった表があった場合に「パーツ7」から何が出来るのか関数で出したい場合の処理です。HLOOKUPで検索結果から横2列ずれた値を返す方法もありますが、これでは今回は出来ません。
e22-2.png
検索範囲が複数の配列になる場合を今回解説します。

最終的にしたい事 & 回答

e22-3.png
こちらの表1を検索してパーツ1.2.3.4と何が完成品になるかを返したい処理です。
LOOKUPではB列を検索してA列を返すという事は出来ますが、B列~F列となるとLOOKUPでは対応出来ません。またMATCH関数も複数の配列に対応していない為、これも没。
以下説明が長くなるので先に回答を明記しておきます。
B6セル、パーツ1の完成品になるところの関数は
=INDEX ( $A$1:$A$3 , SUMPRODUCT ( ( $B$1:$F$3=A6 ) * ROW ( $B$1:$F$3 ) ) )
これで求めれます。
以下SUMPRODUCTも含めて解説していきます。

SUMPRODUCT

あまり使われる事のないSUMPRODUCT。結構色んな使い方があって便利です。今回もコレを使う事で検索が可能になります。

=SUMPRODUCT ( 配列1 , [配列2] , [配列3] ,… )
範囲または配列の対応する要素の積を合計した結果を返します。(Excel関数説明より)

基本的な使い方は配列1と配列2のそれぞれの要素を掛けて、その合計を出すという事です。
e22-4.png
関数を使わなかった場合こうなりますよね。
=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 と掛けあわせているんですね。配列1の要素1個目と配列2の要素1個目で掛けあわしています。で最後に合計しています。
配列1 = B2:B6
配列2 = C2:C6

条件に合う数値を集計

SUMPRODUCTの応用になります。条件に合う種類のみを集計する事が出来ます。
e22-5.png
この表で果物だけを集計する場合
=SUMPRODUCT ( ( D2:D6 = “果物” ) * B2:B6 , C2:C6 )
単価の配列1に D2:D6=”果物” を掛けているのが特徴です。
何故条件を掛けるのか?この例も分解してみましょう。
前提として理解しておいてほしいのはコレ
「もし D2=”果物” であった場合TRUE(真)である1が返ります。もし”果物”でなかったら0が返ります。」
さらに解りやすく6行目まで指定せず2行目だけでSUMPRODUCTを書いた場合。
=SUMPRODUCT ( D2=”果物” * B2 , C2 )
こうなりますね。2行目は「りんご」でありD2が”果物”という種類になっていますので 1*B2 となります。
配列なので6行目まで指定しても上記の繰り返しになります。そして果物でない行についてはB2に0が掛かりますので、SUMPRODUCTの合計に計算されないという仕組みです。

何行目にあるかを返す

応用2です。
検索値が検索範囲の中で調べ、何行目のセルなのか?
e22-6.png
=SUMPRODUCT( ( A1:B5 = “牛乳” ) * ROW ( A1:B5 ) )
先程の応用1が理解出来ればここの理解も早いのではないでしょうか?
A1が”牛乳”ならば 1 、さらにROW(A1)は1行目なので 1 で結果 1 となります。
この例ではB3に牛乳がありますので B3=”牛乳”の結果 1 とROW(B3)の 3 行目を掛けて3が返ります。
仮に複数”牛乳”が存在する場合、その行数が足されます。(A4が牛乳であれば4なので上記の3と足されて7が返ります)

INDEXとSUMPRODUCT

最終章です。
INDEXはこちらで解説しているものやHLOOKUPで検索結果から横2列ずれた値を返すで応用を説明していますので、詳しい事は省きます。
INDEXの配列範囲をA1:A3の完成品列に指定し、その何行目にパーツがあるかを返せればいいだけなので、上記応用2を使い行数を指定します。
もう一度最終的な式を示します。
=INDEX ( $A$1:$A$3 , SUMPRODUCT ( ( $B$1:$F$3=A6 ) * ROW ( $B$1:$F$3 ) ) )
e22-7.png
これで完成!!

Comment

  1. 匿名 より:

    さん
    最近パソコンやAndroidのアプリを作ってます!
    よかったら、覗いて見てください☆
    http://reves-phantasm.com

コメントをどうぞ

メールアドレスが公開されることはありません。

SUM 封筒 作業効率UP 標準報酬月額 図形 ユーザー定義関数 STDEV ワイルドカード 目標 フロー図 TODAY 無料 SUMIF 偏差値 配列数式 履歴書 条件付き書式 テンプレート コピペ COLUMN 印刷 検索 XLSTART NA() COUNTIF VLOOKUP クイズ グラフ ネットプリント バーコード
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑