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

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

ログ内検索

キーワードを複数指定する場合は半角スペースで区切ってください。
検索条件は、(AND)=[A かつ B] (OR)=[A または B] となっています。
[返信]をクリックすると返信ページへ移動します。

キーワード	/	検索条件	/
検索範囲	/	強調表示	/ ON (自動リンクOFF)
結果表示件数	/	記事No検索	/ ON
大文字と小文字を区別する

No.91573 の関連記事表示

ヒット / 10件

<< 0 >>

■91573

　EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(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)

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

親記事 /過去ログ158より / 関連記事表示
削除チェック/

■91574

　Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ kiku -(2019/07/09(Tue) 12:06:15)

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

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

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

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91576

　Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(2019/07/09(Tue) 13:06:46)

■No91574 (kiku さん) に返信
> ■No91573 (鏡月さん) に返信
>>日付、科目、点数の箇所にフィルターを設定しフィルターで抽出したデータを国語、数学、英語毎の合計を算出する
>>関数を作成したいのですがどのようにすればよろしいでしょうか。
>
> ここで言っているフィルターとは、オートフィルターのことでしょうか？
>
> 下記で代用できませんか？
> https://dekiru.net/article/4365/
>
kiku様
ご回答ありがとうございます。

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

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91575

　Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 魔界の仮面弁士 -(2019/07/09(Tue) 12:22:54)

2019/07/09(Tue) 12:23:14 編集(投稿者)

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

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

2. ピボットテーブルのフィールドとして、[科目]と[点数]をチェックする。

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91579

　Re[1]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 魔界の仮面弁士 -(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]で確定

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91577

　Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(2019/07/09(Tue) 13:15:14)

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

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

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

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91578

　Re[3]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(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))

なぜそうなるのか分かりませんのでわかる方がいましたら教えていただけますと幸いです。

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91582

　Re[4]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 魔界の仮面弁士 -(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))

０： =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 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

■91583

　Re[5]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(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))
>
> ０： =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 つだけなので積は求めず、配列内のすべての要素の合計値を求めることになる。

魔界の仮面弁士様

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

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

記事No.91573 のレス / END /過去ログ158より / 関連記事表示
削除チェック/

■91580

　Re[2]: EXCEL SUMIF関数をフィルターで動的に使用したい

□投稿者/ 鏡月 -(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時頃【解決済み】に変更したいと思います。

記事No.91573 のレス /過去ログ158より / 関連記事表示
削除チェック/

<< 0 >>

パスワード/

- Child Tree -