[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『SUMIFS & DATE の組合せについて』（KLY）
B C D E F G H
2 2017年
4 日付 項目 数量 項目集計 月別集計
5 1月20日 あ 10 あ 20 1月 50
6 1月15日 い 15 い 45 2月 45
7 1月10日 う 25 う 50 3月 20
8 2月11日 え 30 え 60 4月 20
9 3月1日 お 5 お 15 5月 55
10 3月15日 い 15 6月
11 2月13日 い 15 7月
12 4月6日 お 5 8月
13 4月10日 あ 10 9月
14 4月28日 お 5 10月
15 5月2日 う 25 11月
16 5月9日 え 30 12月
月別明細集計
1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
20 あ 10 10
21 い 15 15 15
22 う 25 25
23 え 30 30
24 お 5 10
※ 月別集計、月別明細集計は手動計算値です。
※ 項目集計 F5 には『=IF(E5="","",SUMIFS($D$5:$D$16,$C$5:$C$16,"="&E5))』として入力しています。
エラーメッセージが表示され SUMIFS & DATE の組合せがうまくできません。
月別集計 H5 =SUMIFS($D$5:$D$16,$B$5:$B$16,DATE($B$2,G5,1),$B$5:$B$16,($B$2,G5+1))
月別明細集計 C20 =IF($B20="","",SUMIFS($D$5:$D$16,$B$5:$B$16,DATE($B$2,C$19,1)
,$B$5:$B$16,DATE($B$2,C$19,1),$C$5:$C$16,"="&$B20))
また4月～3月までの年度集計にしたい場合1月の式はとのようになりますか。
どこをどのように修正追加したらよいでしょうか。合わせて解説も願います。
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
B2は「2017」とだけ入力（「年」は入力しない） G5:G16、C19:N19は「1～12」だけ入力（「月」は入力しない）
H5 =SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2,G5,1),$B$5:$B$16,"<"&DATE($B$2,G5+1,1))
下コピー
C20 =IF($B20="","",SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2,C$19,1),$B$5:$B$16,"<"&DATE($B$2,C$19+1,1),$C$5:$C$16,$B20))
下と右コピー
こういうこと？
ちなみにF5は「SUMIF」でもできます。
F5 =IF(E5="","",SUMIF($C$5:$C$16,E5,$D$5:$D$16))
＞また4月～3月までの年度集計にしたい場合1月の式はとのようになりますか。
そのときの並び順は、4月～3月ですよね、当然。
とりあえず提示の式で希望通りになるか確認してください。
ひとまず以上です （笑） 2018/03/01(木) 20:26
＞また4月～3月までの年度集計にしたい場合1月の式はとのようになりますか。
H5 =SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2+(G5<4),G5,1),$B$5:$B$16,"<"&DATE($B$2+(G5<4),G5+1,1))
下コピー
これでいいんだったら、C20も同じことです。
参考まで （笑） 2018/03/01(木) 20:38
＞G5:G16、C19:N19は「1～12」だけ入力（「月」は入力しない）
表示形式～ユーザー定義 0"月"
そうしてるのかもしれませんが、念のため。 （笑） 2018/03/02(金) 09:58
＞これでいいんだったら、C20も同じことです。
H5 =SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2+(G5<4),G5,1)
,$B$5:$B$16,"<"&DATE($B$2+(G5<4),G5+1,1))
の参照先を変更して
C20 =SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2+(C$19<4),C$19,1)
,$B$5:$B$16,"<"&DATE($B$2+(C$19<4),C$19+1,1))
下へへ右へコピーしたら
4月 5月 6月 7月 8月 9月 10月 11月 12月 1月 2月 3月 あ 20 25 30 15 35 45 20 い 20 25 30 15 35 45 20 う 20 25 30 15 35 45 20 え 20 25 30 15 35 45 20 お 20 25 30 15 35 45 20
月別明細集計の表示が違った。ので項目の条件範囲と条件を末尾に追加しました。
C20 =SUMIFS($D$5:$D$16,$B$5:$B$16,">="&DATE($B$2+(C$19<4),C$19,1)
,$B$5:$B$16,"<"&DATE($B$2+(C$19<4),C$19+1,1),$C$5:$C$16,$B20)
4月 5月 6月 7月 8月 9月 10月 11月 12月 1月 2月 3月 あ 10 10 い 15 15 15 う 25 25 え 30 30 お 10 5
＞そうしてるのかもしれませんが、念のため。
はい、ユーザー定義で設定しています。
提示してくださった関数のなかで疑問です。
DATE($B$2+(G5<4),G5,1)の部分で+(G5<4)はなにを意味しているのですか。
（KLY） 2018/03/03(土) 20:34
＞DATE($B$2+(G5<4),G5,1)の部分で+(G5<4)はなにを意味しているのですか。
2017年度なら「2017/4/1～2018/3/31」ですよね。
4月～12月は2017年、1月～3月は2018年の日付が対象ですから 1月～3月は DATE($B$2+1,･･･) として、B2の翌年にしないといけない。
=G5<4 は、G5が「4未満」（1～3）ならTRUE、「4以上」（4～12）ならFALSE ↓ $B$2+(G5<4) は、G5が「4未満」なら $B$2+TRUE、「4以上」なら $B$2+FALSE ↓ 四則演算すると、TRUEは「1」、FALSEは「0」として扱われる よって$B$2+TRUE（1～3）は「$B$2+1」、$B$2+FALSE（4～12）は「$B$2+0」の計算になる ↓ 1月～3月は2018、4月～12月は2017になる
$B$2+IF(G5<4,1,0) としても同じことです。
今回の表のように並び順が固定なら、4～12月と1～3月で数式を変えてもいいですけどね。 1～3月のみ「$B$2+1」にする。
参考まで （笑） 2018/03/04(日) 09:28
（KLY） 2018/03/04(日) 09:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.