Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2013)
複数の一致条件
投稿日時: 19/07/03 14:43:10
投稿者: TANPOPO

下記のような表があります。
 
Sheet2を参照し、
「クラス」が一致して、金額が「以上」「以下」の範囲内の行の金額、且つ、「評価」が一致する値を
Sheet2のSABCDから求めたいです。
 
Sheet1
クラス    金額    評価    出したい答え
1     230,000     A    3,000←この値を出したい
2     280,000     B    2,000←この値を出したい
3     370,000     D    -3,000←この値を出したい
 
 
Sheet2
クラス    以上    以下    S       A       B       C       D   
1    188,000     207,999     4,000     3,000     2,000     1,000     0
1    208,000     227,999     4,000     3,000     2,000     0     -2,000
1    228,000     247,999     4,000     3,000     0     -2,000     -3,000
1    248,000     268,000     2,000     0     -2,000     -3,000     -4,000
2    260,000     279,999     4,000     3,000     2,000     1,000     0
2    280,000     299,999     4,000     3,000     2,000     0     -2,000
2    300,000     319,999     4,000     3,000     0     -2,000     -3,000
2    320,000     340,000     2,000     0     -2,000     -3,000     -4,000
3    330,000     349,999     4,000     3,000     2,000     1,000     0
3    350,000     369,999     4,000     3,000     2,000     0     -2,000
3    370,000     389,999     4,000     3,000     0     -2,000     -3,000
3    390,000     410,000     2,000     0     -2,000     -3,000     -4,000
 
良い方法がございましたらご教授いただければ幸いです。
よろしくお願いします。

回答
投稿日時: 19/07/03 16:07:19
投稿者: メジロ2

十分整理できていないように思いますが・・・・
 
D2: =INDEX(Sheet2!$D$2:$H$13,SUMPRODUCT((Sheet2!$A$2:$A$13=A2)*(Sheet2!$B$2:$B$13<=B2)*(Sheet2!$C$2:$C$13>B2)*ROW($A$1:$A$12)),MATCH(C2,Sheet2!$D$1:$H$1,0))

投稿日時: 19/07/03 17:17:00
投稿者: TANPOPO

メジロ2さんありがとうございました。
やりたいことができました。
 
「クラス」・「評価」が一致していても、「以上」「以下」の数値の範囲に含まれない場合、
”該当なし”とするには、どのような式になりますでしょうか。
 
いただいた関数を分解してみたものの、わかりませんでした。
ヒントだけでも教えていただければ幸いです。
 
宜しくお願い致します。

回答
投稿日時: 19/07/03 17:59:48
投稿者: メジロ2

書き込みをしていいのかどうか?の状態になっています(酔眼)
 
>クラス」・「評価」が一致していても、「以上」「以下」の数値の範囲に含まれない場合
このケースでは式がエラーになると思います。
 
Excel2013 ですので「IFERROR」関数をつかったらどうでしょうか?
 
D2: =IFERROR(INDEX(Sheet2!$D$2:$H$13,SUMPRODUCT((Sheet2!$A$2:$A$13=A2)*(Sheet2!$B$2:$B$13<=B2)*(Sheet2!$C$2:$C$13>B2)*ROW($A$1:$A$12)),MATCH(C2,Sheet2!$D$1:$H$1,0)),"該当なし")

回答
投稿日時: 19/07/03 18:11:29
投稿者: んなっと

こんなのも。
 
    A    B   C     D
1 クラス   金額 評価   答え
2    1 100,000   A 該当なし
3    2 299,999   B    25
4    3 370,000   D    10
 
D2
=IFERROR(AGGREGATE(15,6,INDEX(Sheet2!$2:$500,,MATCH(C2,Sheet2!$1:$1,0))/(Sheet2!A$2:A$500=A2)/(Sheet2!B$2:B$500<=B2)/(Sheet2!C$2:C$500>=B2),1),"該当なし")
下方向・↓

投稿日時: 19/07/03 18:18:40
投稿者: TANPOPO

メジロさん
 
ご返信ありがとうございます。
配列関数への緊張感からIFERRORをすっかり忘れていました。
再度確認させていただきます。
 
 
ありがとうございました。

投稿日時: 19/07/03 18:19:57
投稿者: TANPOPO

んなっとさん、ありがとうございます。
検証させていただきます。
感謝感謝です。

回答
投稿日時: 19/07/04 10:44:33
投稿者: Mike

Sheet1
  A   B   C   D  E F   G    H   I  J
1              ←←←←←作業列→→→→→→
2 クラス 金額  評価 答え   EvlC Min   Max   Rws StRw
3  1 230,000 A   3,000   2 188,000 268,000  4  1
4  2 280,000 B   2,000   3 260,000 340,000  4  5
5  3 370,000 D  -3,000   5 330,000 410,000  4  9
 
F3: =MATCH(C3,Sheet2!D$1:H$1,0)
G3: 配列数式→ =MIN(IF(Sheet2!A$2:A$13=A3,Sheet2!B$2:B$13,""))
H3: 配列数式→ =MAX(IF(Sheet2!A$2:A$13=A3,Sheet2!C$2:C$13))
I3: =COUNTIF(Sheet2!A:A,A3)
J3: =MATCH(A3,Sheet2!A$2:A$13,0)
D3: =IF(AND(B3>=G3,B3<=H3),VLOOKUP(B3,OFFSET(Sheet2!A$2,J3-1,1,I3,7),F3+2),"")
 
Sheet2
  A   B    C   D   E   F   G    H
1 クラス 以上  以下   S   A   B   C    D
2  1 188,000 207,999 4,000 3,000 2,000 1,000   0
3  1 208,000 227,999 4,000 3,000 2,000   0 -2,000
4  1 228,000 247,999 4,000 3,000   0 -2,000 -3,000
5  1 248,000 268,000 2,000   0 -2,000 -3,000 -4,000
6  2 260,000 279,999 4,000 3,000 2,000 1,000   0
7  2 280,000 299,999 4,000 3,000 2,000   0 -2,000
8  2 300,000 319,999 4,000 3,000 0   -2,000 -3,000
9  2 320,000 340,000 2,000   0 -2,000 -3,000 -4,000
10  3 330,000 349,999 4,000 3,000 2,000 1,000   0
11  3 350,000 369,999 4,000 3,000 2,000   0 -2,000
12  3 370,000 389,999 4,000 3,000   0 -2,000 -3,000
13  3 390,000 410,000 2,000   0 -2,000 -3,000 -4,000

投稿日時: 19/07/05 17:17:31
投稿者: TANPOPO

Mikeさん、わざわざ解体した式を・・
ありがとうございます。
 
皆様のおかげでやりたい事が100%完成しました。
あとは配列関数への苦手意識を克服できるよう、みなさんから教えていただいた式を自分で考えられるように復習させていただきます。
 
メジロ2さん、んなっとさん、Mikeさん、ありがとうございました。