C# と VB.NET の質問掲示板

わんくま同盟

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

■91582 / 4階層)  EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 魔界の仮面弁士 (2230回)-(2019/07/09(Tue) 14:11:09)
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))

0: =SUMPRODUCT(
1:   (
2:     SUBTOTAL(
3:       3,
4:       INDIRECT("D"&ROW($D$5:$D$20))
5:     )
6:   )
7:   *($D$5:$D$20="国語")
8:   *($E$5:$E$20)
9: )

----

2:SUBTOTAL(3, 配列) は、データの個数を求める(COUNTA)に相当。
  ただしオートフィルターで非表示の場合はカウントされないという特性がある。

4: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; …} となる。

7:($D$5:$D$20="国語") は {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; … } な配列。

8:($E$5:$E$20) は {10; 20; 30; 40; 50; 60; …} な配列なので、
  7と8を乗じた結果は {10; 0; 0; 40; 0; 0; …} な配列になる。
  そこにさらに 4 を乗じれば、フィルター条件に沿ってない行の値は 0 になる。

0:SUMPRODUCT は、各配列の要素の積をまず計算し、さらにその和を返す関数。
  今回は引数が 1 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。

編集キー/

前の記事(元になった記事) 次の記事(この記事の返信)
←Re[3]: EXCEL SUMIF関数をフィルターで動的に使用したい /鏡月 →Re[5]: EXCEL SUMIF関数をフィルターで動的に使用したい /鏡月
 
上記関連ツリー

EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 10:34) #91573
Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい / kiku (19/07/09(Tue) 12:06) #91574
│└ Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 13:06) #91576
Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい / 魔界の仮面弁士 (19/07/09(Tue) 12:22) #91575
Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい / 魔界の仮面弁士 (19/07/09(Tue) 13:19) #91579
  ├ Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 13:15) #91577
  │└ Re[3]: EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 13:17) #91578
  │  └ EXCEL SUMIF関数をフィルターで動的に使用したい / 魔界の仮面弁士 (19/07/09(Tue) 14:11) #91582 ←Now
  │    └ Re[5]: EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 14:52) #91583 解決済み
  └ Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい / 鏡月 (19/07/09(Tue) 14:00) #91580

上記ツリーを一括表示 / 上記ツリーをトピック表示
 
上記の記事へ返信