Articles

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

この記事をdel.icio.usに登録する この記事をはてなブックマークに登録する この記事をGoogleブックマークに登録する この記事をYahoo!ブックマークに登録する この記事をdiggに登録する この記事をlivedoor clipに登録する この記事をNewsGattor Onlineに登録する

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

これで完成!!
関連記事




この記事をdel.icio.usに登録する この記事をはてなブックマークに登録する この記事をGoogleブックマークに登録する この記事をYahoo!ブックマークに登録する この記事をdiggに登録する この記事をlivedoor clipに登録する この記事をNewsGattor Onlineに登録する

トラックバック一覧

コメント一覧

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

さん

最近パソコンやAndroidのアプリを作ってます!
よかったら、覗いて見てください☆

http://reves-phantasm.com

  • 2011/01/29
  • 名無しのExcel使 ◆ -
  • [ URL ]
  • [ 編集 ]
コメント投稿フォーム
Name
Subject
Mail
URL
Comment

Password
Secret
管理者にだけ表示を許可する

Navigations, etc.

Navigations

ブログ記事を検索

SPONSORS

人気記事ランキング

ブログパーツ

メールフォーム

名前:
メール:
件名:
本文:

Tweet

最新記事