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

わんくま同盟

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

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

ツリー一括表示

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
  │  └ Re[4]: EXCEL SUMIF関数をフィルターで動的に使用したい /魔界の仮面弁士 (19/07/09(Tue) 14:11) #91582
  │    └ Re[5]: EXCEL SUMIF関数をフィルターで動的に使用したい /鏡月 (19/07/09(Tue) 14:52) #91583 解決済み
  └ Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい /鏡月 (19/07/09(Tue) 14:00) #91580


親記事 / ▼[ 91574 ] ▼[ 91575 ] ▼[ 91579 ]
■91573 / 親階層)  EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (12回)-(2019/07/09(Tue) 10:34:47)

分類:[Microsoft Office 全般] 

お世話になっております。

色々と考えて作成してみたのですが思っている動作にならないため、ご教授ください。

Office365のExcelで下記データを入力しております。
※x列、y行目はExcelの行列になります。

C列 D列 E列
4行目 日付 科目 点数
5行目 4月2日 国語 10
6行目 4月5日 数学 20
7行目 4月8日 英語 30
8行目 4月9日 国語 40
9行目 4月16日 数学 50
10行目 5月14日 英語 60
11行目 5月15日 国語 70
12行目 5月20日 数学 80
13行目 5月25日 英語 90
14行目 5月26日 国語 100
15行目 6月1日 数学 90
16行目 6月2日 英語 80
17行目 6月3日 国語 70
18行目 6月4日 数学 60
19行目 6月5日 英語 50
20行目 6月5日 国語 40

Sumif関数を使用し各項目の点数の合計を算出しております。
=SUMIF($D$5:$D$20,"国語",$E$5:$E$20)
=SUMIF($D$5:$D$20,"数学",$E$5:$E$20)
=SUMIF($D$5:$D$20,"英語",$E$5:$E$20)

日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
関数を作成したいのですがどのようにすればよろしいでしょうか。

[ □ Tree ] 返信 編集キー/

▲[ 91573 ] / ▼[ 91576 ]
■91574 / 1階層)  Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ kiku (119回)-(2019/07/09(Tue) 12:06:15)
No91573 (鏡月 さん) に返信
> 日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
> 関数を作成したいのですがどのようにすればよろしいでしょうか。

ここで言っているフィルターとは、オートフィルターのことでしょうか?

下記で代用できませんか?
https://dekiru.net/article/4365/

[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91574 ] / 返信無し
■91576 / 2階層)  Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (13回)-(2019/07/09(Tue) 13:06:46)
No91574 (kiku さん) に返信
> ■No91573 (鏡月 さん) に返信
>>日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
>>関数を作成したいのですがどのようにすればよろしいでしょうか。
>
> ここで言っているフィルターとは、オートフィルターのことでしょうか?
>
> 下記で代用できませんか?
> https://dekiru.net/article/4365/
>
kiku様
ご回答ありがとうございます。

言葉足らずで申し訳ございません。
教えていただきましたサイトを閲覧しましたがsumifの条件を事前にしてする必要があると認識しました。
フィルターである程度自由に抽出したいと考えております。

[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91573 ] / 返信無し
■91575 / 1階層)  Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 魔界の仮面弁士 (2227回)-(2019/07/09(Tue) 12:22:54)
2019/07/09(Tue) 12:23:14 編集(投稿者)

No91573 (鏡月 さん) に返信
> 日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する

1. [挿入]リボンの[ピボット テーブル]を選択して、
  分析範囲:C4:E20 のオートフィルター範囲(またはテーブル)
  配置先:任意、たとえば D25
 を指定して [OK] をクリック。

2. ピボット テーブルのフィールドとして、[科目]と[点数]をチェックする。
[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91573 ] / ▼[ 91580 ]
■91579 / 1階層)  Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 魔界の仮面弁士 (2228回)-(2019/07/09(Tue) 13:19:08)
No91573 (鏡月 さん) に返信
> 日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する

作業列を作っても良いのなら:

(1) F4 に "国語"、G4 に "数学"、H4 に "英語" と記述

(2) F5 に 『=IF($D5=F$4,$E5,0)』という式を入力

(3) F5 をコピーして、F5:H20 の範囲にペースト

(4) F3:H3 の範囲 を選択し、『=SUBTOTAL(9,F$4:F$20)』という式を入れて、[Ctrl]+[Shift]+[Enter]で確定
[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91575 ] / ▼[ 91578 ]
■91577 / 2階層)  Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (14回)-(2019/07/09(Tue) 13:15:14)
No91575 (魔界の仮面弁士 さん) に返信
> 2019/07/09(Tue) 12:23:14 編集(投稿者)
>
> ■No91573 (鏡月 さん) に返信
>>日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
>
> 1. [挿入]リボンの[ピボット テーブル]を選択して、
>   分析範囲:C4:E20 のオートフィルター範囲(またはテーブル)
>   配置先:任意、たとえば D25
>  を指定して [OK] をクリック。
>
> 2. ピボット テーブルのフィールドとして、[科目]と[点数]をチェックする。

魔界の仮面弁士様
ご回答ありがとうございます。


教えいただきました通りやってみたのですが私のやり方が悪いとは思いますがうまくいきませんでした。


[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91577 ] / ▼[ 91582 ]
■91578 / 3階層)  Re[3]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (15回)-(2019/07/09(Tue) 13:17:57)
よくわかりませんが下記の数式を挿入したところ、私が行いたかった動作になりました。

=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))


なぜそうなるのか分かりませんのでわかる方がいましたら教えていただけますと幸いです。
[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91578 ] / ▼[ 91583 ]
■91582 / 4階層)  Re[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 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。

[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91582 ] / 返信無し
■91583 / 5階層)  Re[5]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (17回)-(2019/07/09(Tue) 14:52:24)
No91582 (魔界の仮面弁士 さん) に返信
> ■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 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。

魔界の仮面弁士様

大変わかりやすい説明ありがとうございます。
記載していただいた内容での解説がとてもわかりやすい為、今後わからないことがありましたら上記のように展開して部分的に考えたいと思います。

ありがとうございました。

解決済み
[ 親 91573 / □ Tree ] 返信 編集キー/

▲[ 91579 ] / 返信無し
■91580 / 2階層)  Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい
□投稿者/ 鏡月 (16回)-(2019/07/09(Tue) 14:00:40)
No91579 (魔界の仮面弁士 さん) に返信
> ■No91573 (鏡月 さん) に返信
>>日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
>
> 作業列を作っても良いのなら:
>
> (1) F4 に "国語"、G4 に "数学"、H4 に "英語" と記述
>
> (2) F5 に 『=IF($D5=F$4,$E5,0)』という式を入力
>
> (3) F5 をコピーして、F5:H20 の範囲にペースト
>
> (4) F3:H3 の範囲 を選択し、『=SUBTOTAL(9,F$4:F$20)』という式を入れて、[Ctrl]+[Shift]+[Enter]で確定

魔界の仮面弁士様
ご回答ありがとうございます。

教えていただきましたやり方で私が行いたかった動作を確認いたしました。
ただ、作業列を追加しなくても同一の動作が下記の数式で確認できました。
=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))

なぜそのような動作になるのか現在調べておりますが分かっておりません。
ご存知でしたらご教授願います。

尚、本質問はある意味解決しておりますので16時頃【解決済み】に変更したいと思います。



[ 親 91573 / □ Tree ] 返信 編集キー/


管理者用

- Child Tree -