| ■No91578 (鏡月 さん) に返信
> =SUMPRODUCT((SUBTOTAL(3,INDIRECT("D"&ROW($D$5:$D$20))))*($D$5:$D$20="国語")*($E$5:$E$20))
> =SUMPRODUCT((SUBTOTAL(3,INDIRECT("D"&ROW($D$5:$D$20))))*($D$5:$D$20="数学")*($E$5:$E$20))
> =SUMPRODUCT((SUBTOTAL(3,INDIRECT("D"&ROW($D$5:$D$20))))*($D$5:$D$20="英語")*($E$5:$E$20))
０： =SUMPRODUCT(
１： (
２： SUBTOTAL(
３： 3,
４： INDIRECT("D"&ROW($D$5:$D$20))
５： )
６： )
７： *($D$5:$D$20="国語")
８： *($E$5:$E$20)
９： )
----
２：SUBTOTAL(3, 配列) は、データの個数を求める(COUNTA)に相当。
ただしオートフィルターで非表示の場合はカウントされないという特性がある。
４：ROW($D$5:$D$20) なら {5; 6; 7; 8; 9; 10; …} という配列になり、
INDIRECT("D"&ROW($D$5:$D$20)) は「INDIRECT("D5:D20")」に相当するが、
SUBTOTAL の中で ROW が使われているので、非表示列はカウント対象外となる。
「SUBTOTAL(3, INDIRECT("D"&ROW($D$5:$D$20)))」という式は、
フィルターがかかっていなければ {1; 1; 1; 1; 1; 1; …} となり、
[4月以外] なフィルターなら {0; 0; 0; 0; 0; 1; …} となり、
[数学のみ] なフィルターなら {0; 1; 0; 0; 1; 0; …} となる。
７：($D$5:$D$20="国語") は {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; … } な配列。
８：($E$5:$E$20) は {10; 20; 30; 40; 50; 60; …} な配列なので、
７と８を乗じた結果は {10; 0; 0; 40; 0; 0; …} な配列になる。
そこにさらに ４ を乗じれば、フィルター条件に沿ってない行の値は 0 になる。
０：SUMPRODUCT は、各配列の要素の積をまず計算し、さらにその和を返す関数。
今回は引数が 1 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。 |