月別の合計の出し方

質問

A 　　B　　　　 C　　　　 D
１　月度　日付　顧客管理ID 売上金額

です。　但しA列(月度)のセルは色別で表示しています。
この場合B列の日付を利用してD列の月毎の合計金額をE列に　可能なら
B列のその月の最終行(月々により行数は不規則)に表示されれば最高なんですが。
可能でしょうか。
もし可能ならば是非ご教授を御願いします。

回答No.6

web2525 · ベストアンサー

=IF(MONTH(B5)=MONTH(B6),"",SUMPRODUCT((MONTH(OFFSET($B$2,0,0,COUNT(B:B)))=MONTH(B5))*OFFSET($D$2,0,0,COUNT(B:B))))
A列の月度が日付のシリアル値なら前回の計算式はうまく働かないので、B列の日付からの計算式に変更しました。

お礼

ご協力感謝します。
小生の力不足をご指導いただき有難うございました。
今後ともよろしく御願いします。
最終行にも合計金額表示され感謝です。

回答No.7

hallo-2007

>B列に入力されていないとき　A列に　「01」　が表示されます。
IF文で　=IF(B2="","",今の式)　といった感じです。
=IF(B2="","",TEXT(B2,"yy年mm月度"))

=IF(A2=A3,"",SUMIF(A:A,A2,D:D))
は
=IF(B3="","",IF(A2=A3,"",SUMIF(A:A,A2,D:D)))
でも良いですが
=IF(OR(B3="",A2=A3),"",SUMIF(A:A,A2,D:D)))
でも良いと思います。

お礼

ご協力感謝します。
小生の力不足をご指導いただき有難うございました。

=IF(B2="","",TEXT(B2,"yy年mm月度"))は
=IF(B2="","",TEXT(B2,"mm"))　にしました。

=IF(OR(B3="",A2=A3),"",SUMIF(A:A,A2,D:D)))は
=IF(OR(B3="",A2=A3),"",SUMIF(A:A,A2,D:D))　にしました。

回答No.5

hallo-2007

>A２セルに　=B２　として　ユーザー定義で　mmとしました。
>例えば　B2セルが「０２／２０」の時A2セルに「０２」としました。
>これではいけないですか。
これでは、表示だけは月数ですが、実際の値は日付です。
月だけでよければ、=MONTH(B1) です。
但し、上記の方法では、使えるのは1年になります。
A列は　=TEXT(B2,"yy年mm月度")　が必要です。

>それとE列から　K列に変更したので、Eの部分をKに置き換えたのですが
>K3に　　=IF(K2=K3,"",SUMIF(A:A,A2,D:D))
ここでいう　IF(K2=K3・・　というのは月が入っているセルです。
=IF(A2=A3,"",SUMIF(A:A,A2,D:D)) という式を　K3に入れるに　なると思います。

補足

下の式できまりました。　
但しもう一点だけ質問ご回答いただければ幸甚です。
=IF(A2=A3,"",SUMIF(A:A,A2,D:D))

=TEXT(B2,"yy年mm月度")　フィルでコピーしたときにまだ　B列に入力されていないとき　A列に　「01」　が表示されます。

できるならこの「01」が表示されないようにできればうれしいです。
可能ならご指導いたたけませんか。
ほんとうに宜しく御願いします。

回答No.4

web2525

No2です、計算式で考えてみました。
=IF(A2=A3,"",SUMPRODUCT((OFFSET($A$2,0,0,COUNT(A:A))=A2)*(OFFSET($D$2,0,0,COUNT(A:A)))))
A列に月度が数値データとして入っていれば上記式をE列に張り付ければ最終日行にその月のデータが表示されるはず。

でもお勧めは集計機能ですが。

補足

B列月度　C列売上金額　K列月別売上
A2　０２　　　１０００　　１０００
A3　０２ 　 　１０００　　１０００
A4　０２　　　１０００　　１０００
A5　０３　　　１０００　　１０００
A6　０３　　　１０００　　１０００

=IF(A2=A3,"",SUMPRODUCT((OFFSET($A$2,0,0,COUNT(A:A))=A2)*(OFFSET($D$2,0,0,COUNT(A:A)))))　K2に入れました。
をK列に適用しましたか上のようになります。
ご指導いただけませんか。
宜しく御願いします。

回答No.3

hallo-2007

すみません、訂正です。
=IF(A2=A3,"",SUMIF(A:A,A2,D:D)) でA列に月度が1月度とか2月度とか
文字、或いは数値が入っている条件です。
残念ながら関数では、セルの色は認識できません。
せっかく、B列に日付が入っているのであれば、A列の月度に
=IF(DAY(B2)>20,TEXT(DATE(YEAR(B2),MONTH(B2)+1,1),"yy年mm月度"),TEXT(B2,"yy年mm月度"))
下へコピィします（上記式では20日締め、21日以降の場合は翌月度としています）
文字が自動で入ります。文字が不要であれば文字色を白にしてしまう方法もありますが・・

補足

A２セルに　=B２　として　ユーザー定義で　mmとしました。
例えば　B2セルが「０２／２０」の時A2セルに「０２」としました。
これではいけないですか。

それとE列から　K列に変更したので、Eの部分をKに置き換えたのですが

K3に　　=IF(K2=K3,"",SUMIF(A:A,A2,D:D))
どうしても上手くできません。
重ねてご指導宜しく御願いします。

回答No.2

web2525

データの集計はExcelの集計機能を利用するとわかりやすいのです。
メニューの【データ】から集計を選択し基準を月度（月の数値入力が必要）、集計方法を合計、集計するフィールドを売上金額として設定すると、自動的に集計表が作成されます。

関数だけでE列に表示するには難しいかな。

補足

おっしゃる事はよく分かり理解ができました。
他の列にも記入欄があって集計の行が切れてしまいます。
それで悩んでいます。

回答No.1

hallo-2007

>この場合B列の日付を利用してD列の月毎の合計金額
A列に月度の情報があるので（=SUMIF(A:A,A2,D:D) で月ごとの合計金額がでると思います。
>B列のその月の最終行(月々により行数は不規則)に表示されれば最高なんですが
例えば、A列の月度が上の行と変わった行に合計の式が計算されるようにします。
式をE3セルから入れますが、=IF(E2=E3,"",合計の式)となります。
E３セルに　=IF(E2=E3,"",SUMIF(A:A,A2,D:D))　といれて下までコピィしては如何でしょうか。

補足

質問
１　A列はセルの色でそれぞれの月を表しています。
　　例えば　１月を黄色　２月を紫　でも読み込むことができますか。
２　当初はE列にと予定していました。
　　これをK列に変更して下記の如く入力、
　　K3に　　=IF(K2=K3,"",SUMIF(A:A,A2,D:D))
　と挿入しました。
　　エラーと出ます。
　　A列を色でなく　１月度を黄色から　１　２月度を紫から　２
　　　と数字変えてやり直しましたがやはりエラーが出ます。
なにが原因か
恐れいりますか再度ご指導宜しく御願いします。