(Excel エクセル) 複数条件の参照方法について

(Excel エクセル) 複数条件の参照方法について

条件が2つの複合参照はINDEX関数とMATCH関数で出来たのですが
条件が多い場合の参照方法で躓いています。
見よう見真似でSUMPRODUCT関数やROW関数を使ったのですが、上手く出来ませんでした…

・前回の判定
・今回の判定
・組
・年齢
を入力すると、年齢別加点が表示されるようにしたいです。

(例)
前回:A
今回:B
　組:3
年齢:20
=年齢別加点:15

何か良い方法はありますでしょうか？
よろしくお願いします。

No.5 回答者： banzaiA 回答日時： 2020/11/03 22:13

＃３です。

＞データによってはかなり膨大な表になってしまうのとアナログ過ぎると思ったので
膨大な表になる･･･そうですか？列一つ挿入するだけですが。
アナログ過ぎるってだめですか？シンプルでわかりやすいと思いますが。

で、Excelのバージョンが記されていないので、最新バージョン（Microsoft365）だとして、
添付で例示されている表の
M7のセルに、
=INDEX(FILTER(A2:G11,(A2:A11=I7)*(B2:B11=J7)*(C2:C11=K7),""),,MATCH(L7,A2:G2))
でできると思います。

この回答へのお礼

ありがとうございます。
FILTER関数、私のバージョンでは無理ですが、非常に簡単そうな数式ですね。
いつか機会があれば試してみます。

お礼日時：2020/11/06 06:55

No.4 ベストアンサー 回答者： enunokokoro 回答日時： 2020/11/03 18:48

> これがA101からA109だと実際の数式はどうなるのでしょうか？

基本的には同じですよ。
範囲の1行目から9行目までを求めるのが ROW(A1:A9) の指定です。
1から9までの数値を求めると考えると解りやすいと思います。

リンク先のサイトは1行目から7行目までの範囲で、同じ値になる行を
求めるためのROW関数なのですが、表が開始する行が違っても行数
そのものは同じなので、ROW関数は同じものを使います。
私が最初に書いた数式では、見出しの行を除いてありますが、入れた場合は ROW(A1:A10) になります。
開始行が違う場合も考慮するとROW($A$1:$A$9)にすべきでしたね。

希望する実際の式（列数は同じ場合でサイトと同じ見出し行を含む）
表そのものは前回と同じものを101行目から開始したものです。
I107からM107までが条件の値がある場合です。

　=INDEX(D103:G111,(SUMPRODUCT((A103:A111=I107)*(B103:B111=J107)*(C103:C111=K107),ROW($A$1:$A$9))),MATCH(L107,D102:G102))

ROW関数以外は、範囲指定なので理解できると思います。
ROW関数は〇行目を求めるものなので、COUNTA関数を使って個数
から範囲行を求めることで対応できますね。
（この場合は見出し行を含めたほうが簡単）

この回答へのお礼

ありがとうございます。
ROW(A○:A○)は、ほぼ固定なんですね(言葉のチョイスは間違っているかも知れませんが)。

今まではROW(AA101:A109)とか試して失敗していたのですが
ようやく理解出来ました。

どのサイトよりも分かりやすかったです。

何度も丁寧にありがとうございました。
また御縁がありましたら、よろしくお願い致します。

お礼日時：2020/11/06 06:56

No.3 回答者： banzaiA 回答日時： 2020/11/03 18:12

すでに回答されていますが。

＞条件が2つの複合参照はINDEX関数とMATCH関数で出来たのです
ということなので、
C列とD列の間に（またはA列の前に）列を挿入して、
=前回＆今回＆組　（つまり　AA1、AA2,AA3　など）
という列を作ればいいでしょう。
AA3 と　20　で参照すれば　1.5 が表示されます。

この回答へのお礼

ありがとうございます。
その方法は頭にありましたが、データによってはかなり膨大な表になってしまうのと
アナログ過ぎると思ったので、Excelに精通してる方の意見を聞きたく質問しました。

お礼日時：2020/11/03 19:48

No.2 回答者： mike_g 回答日時： 2020/11/03 15:11

添附圖參照(Excel 2019)

１．範圍 A2:C100 を選擇⇒Alt+MC⇒“上端行”だけに
￣￣チェック入れ⇒[OK]
２．セル D1 を選擇⇒[名前ボックス]内にマウスカーソル
￣￣を放り込み⇒其処に在った D1 を全て半角文字で
￣￣D1:D100 に上書き⇒Enter
３．セル M7 に次式を入力
￣￣ =IFERROR(INDEX(INDIRECT(M$6),MAX(IF((前回=I7)*(今回=J7)*(組=K7),ROW(組))),MATCH(L7,D$2:G$2,0)),"")
￣￣【御斷はり】上式は必ず配列數式として入力の事

No.1 回答者： enunokokoro 回答日時： 2020/11/03 14:27

> 見よう見真似でSUMPRODUCT関数やROW関数を使った

例えば、質問画像の範囲のみなら
　=INDEX(D3:G11,(SUMPRODUCT((A3:A11=I7)*(B3:B11=J7)*(C3:C11=K7),ROW(A1:A9))),MATCH(L7,D2:G2))
でも、求めることができると思います。
http://office-qa.com/Excel/ex352.htm
行の複数条件はSUMPRODUCT関数やROW関数を使い、年齢の列は
MATCH関数で求めています。（絶対参照にしていませんので注意）

ROW関数の指定範囲は、3行目から11行目である9行を求めるための
指定になっているので、行が増えたらROW(A1:A9)の範囲数も増やして
ください。
（COUNTA関数などでA列の入力された行数を求めるとか）

この回答へのお礼

ありがとうございます。
情けないのですが、ROW関数の仕組みが上手く理解出来ないです…

今回A1:A11(1行目から11行目まで)の表でしたが、これがA101からA109だと
実際の数式はどうなるのでしょうか？

貼って頂いたリンクは自分でも見ておりましたが
中々理解出来ず、こちらで質問した次第でして…

お礼日時：2020/11/03 16:55