２つの条件から１個のデータを導き出す関数が分かりません。

質問

excelで売上集計表を作っているのですが、関数が分からなくて困っています。VLOOKUPやDSUMなどは知っているのですが、２つの条件から１個のデータ抽出する方法が分かりません。

（例）サイズ＋色　→　該当する値段　　など

【列】カラー　【行】サイズ　→　【交わるセル】値段　という表(1)が参照元として有ります。
その参照表とは別に表(2)として、【Ａ列】には購入者を【Ｂ列】カラー　【Ｃ列】サイズ　と入力していく管理表で【Ｄ列】に同じ行の「カラー」と「サイズ」を参照して、別表(1)の価格を自動表示できるような関数を入力したいのです。このようなケースの場合どのような関数を入力すればよいのでしょうか。

回答No.5

mshr1962

表(1)が1行目がカラーの名称、A列がサイズでB2からその値段
　　赤　青　黄　緑　ピンク
SS　100　105　110　110　120　
S_　110　115　120　125　140
M_　120　125　135　140　160
L_　130　135　150　155　180
LL　140　145　165　170　200
なら
=VLOOKUP(C2,表(1)!$A$2:$F$6,MATCH(B2,表(1)!$B$1:$F$1,0),FALSE)
または
=HLOOKUP(B2,表(1)!$B$1:$F$6,MATCH(C2,表(1)!$A$1:$A$6,0),FALSE)
または
=INDEX(表(1)!$B$2:$F$6,MATCH(C2,表(1)!$A$1:$A$6,0),MATCH(B2,表(1)!$B$1:$F$1,0))

お礼

表の例示までつけて頂いて、大変分かりやすかったです。
必ずしも１つしか方法がないとは限らないのですね。すべて試してみます。

回答No.4

kikei

sumproduct関数を用いても計算できます。
例えば　サイズ=120、色＝青　の値段を抜き出したい場合
表1にA列:カラー　B列:サイズ　C列:値段 としていた場合

=sumproduct((A1:A1000="青")*(B1:B1000=120)*(C1:C1000))

とすれば値段が抜き出せます。
(但し、この表1に同カラー同サイズの行が2行以上あった場合
その2行の値段を足し合わせてしまいますので注意が必要です。)

お礼

ありがとうございます。その関数は初めて聞きました。しかし、kikei様のご指摘通り、今回は同条件のデータが複数存在するため、教えていただいた関数ではうまく処理できなそうです。
けれどとても参考になりました！別のケースで活用してみたいです。

回答No.3

mu2011

列・行番号から検索できるINDEX、OFFSET関数になると思います。この関数の大きな違いは相対番号がINDEX関数は１、OFFSET関数は０ぐらいでしょうか。
又、行・列番号はMATCH関数で確定しますのでこの二つの関数を組み合わせる事になります。
INDEX関数は回答済みですのでOFFSET関数例です。
=OFFSET(表1!$A$1,MATCH(C2,表1!$A$1:$A$10,0)-1,MATCH(B2,表1!$A$1:$F$1,0)-1)

回答No.2

Yosha

>VLOOKUPやDSUMなどは知っている

VLOOKUP(/HLOOKUP)では、検査値(ここでは、サイズ、色など)は、昇順に並べられていることが必須条件ですので、この関数を使っても出せますが、データを並べ替える必要がでてきます。
DSUMでは出せませんが、DGETという関数もありますが、ストレートには答えが出せません。

この場合は、行と列との交差しているセルの値を取り出せる、INDEX関数の配列形式用の関数を使います。書式は、INDEX　書式２の
　INDEX(配列 [,行番号][,列番号])＝配列要素の値
を使います。あなたの場合、配列はデータの範囲全体を絶対指定にします。行番号、列番号は書式では、単独又は双方とも省略できますが、必ず両方指定します。

行番号(【Ｂ列】カラー)、列番号(【Ｃ列】サイズ)の取得は、MATCH関数を使います。書式は、
　MATCH(検査値, 検査範囲 [,照合の型])＝相対的な位置
です。
検査値は、行番号(【Ｂ列】カラー)/列番号(【Ｃ列】サイズ)
検査範囲は、参照表の【列】カラー/【行】サイズ　の各タイトルが格納されている範囲
照合の型は、必ず「0」を指定します。これは、検査値と一致した値のみを検索します。検索範囲のデータの並びはランダムでOKです。省略するとデータは昇順でないといけません。

具体的には、参照表の【行】サイズ欄のタイトルが“A2”～“A20”、【Ｂ列】カラー欄のタイトルが“B2”～“G2”に記載されていて、管理表のデータが“A50”から始まっているとすると、“D50”セルに、次式
　＝INDEX($B$2:$G$20,MATCH(C50,$A$2:$A$20,0),MATCH(B50,$B$2:$G$2,0))
を入力し、下方にドラッグすればOKとなります。

不明な点があるときは、エクセルのヘルプを見てください。

お礼

＞DGETという関数もあります
　初めて知りました。

＞不明な点があるときは、エクセルのヘルプを見てください
　　その通りですね。改めてヘルプを見ると思いの他、詳しい解説が出てきて、もっと活用すべきと思いました。

どうやらINDEX関数を中心に用いた使い方が一番よさそうです。ありがとうございました。もっとexcelの勉強に励む必要を感じました。

回答No.1

keirika

MATCH関数とINDEX関数の組み合わせが有効だと思います。

MATCH関数を使い、列数と行数を割り出し、それを元にINDEX関数
で値を取り出します。

以上です。