月別、項目の合計金額を その２

質問

月別、項目の合計金額を その２
この前と同じですか添付画像を入れたのですが入っていませんでした。
追加になり済みません。

いつもお世話になります。
WINDOWS XP EXCELL2003
例えば
年間の合計の数式はなんとか、やっとできました。
C4 =SUMIF(C$20:C41,B4,D$20:D41)

ただ
ただD4 E4 F4 ～ O4 等月別　項目の無合計金額を出すのに困っています。
D4 D5 等にどのような数式を入れればいいかご指導願えれば幸です。
宜しく御願いします。

回答No.3

tom04 · ベストアンサー

こんばんは！
すでに回答は出ていて、重複すると思いますが、
まず、1月・2月・・・のデータをシリアル値にしておきます。

今年の場合、仮にD3セルは　1/1　E3セルに　2/1　と入力し、セルの書式設定から
ユーザー定義で　　m"月"　　とします。
D3・E3セルを範囲指定し、E3セルのフィルハンドルで　O3セルまでオートフィルでコピーします。

そしてD4セルに
=SUMPRODUCT((MONTH($A$20:$A$41)=MONTH(D$3))*($C$20:$C$41=$C4)*($D$20:$D$41))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
希望に近い形にならないでしょうか？

尚、質問どおりの行数での範囲指定にしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば幸いです。m(__)m

お礼

お世話になります。
早速テストしました結果バッチリうまくできました。
有難うございました。
これからも何かありましたらご協力宜しく御願いします。

回答No.4

KURUMITO

難しい式を考えるよりもできるだけ簡単で分かりやすい式を作って対応することが計算の動きを考える上でもよいことです。次のようにしてはどうでしょう。
なお、お示しの表は1月から12月までの表で考えているのですね。そうでない場合には式がより複雑になりますね。

初めに作業列としてＧ20セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A20="","",MONTH(A20)&C20)

Ｄ4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR($B4="",D$3=""),"",SUMIF($G$20:$G$10000,COLUMN(A1)&$B4,IF(INDEX($B$20:$B$1000,MATCH($B4,$C$20:$C$1000,0))="収入",$D$20:$D$10000,$E$20:$E$10000)))

これで収入の場合も支出の場合も区別されて集計することができます。

年間の合計は次の式をＣ4セルに入力して下方にオートフィルドラッグします。

=IF(COUNT(D4:O4)=0,"",SUM(D4:O4))

参考になりましたら幸いです。

補足

いつもMr.KURIMOTO お世話になります。
下記を入力してオートフィルドラッグをしました。
=IF(OR($B4="",D$3=""),"",SUMIF($G$20:$G$10000,COLUMN(A1)&$B4,IF(INDEX($B$20:$B$1000,MATCH($B4,$C$20:$C$1000,0))="収入",$D$20:$D$10000,$E$20:$E$10000)))

その結果、　B4 B5 B6・・の項目の特に支出で入力のない項目は　＃Ｎ／Ａ とでます。
一度でも入力があるとこの問題は解決します。
年初にはこの問題が出ると合計金額に影響します。

回答No.2

keithin

D3は「1月」と半角の１と月で文字で記入して，右向けにオートフィルドラッグで2月3月と入れておく
A20以下は「年/月/日」できちんとデータを入れ，表示は今のように01/01などでも構わない

D4には
=SUMPRODUCT((TEXT($A$20:$A$1000,"m月")=D$3)*($C$20:$C$1000=$B4)*($D$20:$D$1000+$E$20:$E$1000))
のように作成してみる。

＃別の方法
ピボットテーブルレポートを使い，数式を一個も使わずに集計する。

補足

ご回答有難うございます。
下記のままではうまくいかなくいろいろ考えました。

=SUMPRODUCT((TEXT($A$20:$A$1000,"m月")=D$3)*($C$20:$C$1000=$B4)*($D$20:$D$1000+$E$20:$E$1000))

下記のように変更させていただいたらうまくいきました。
=SUMPRODUCT((TEXT($A$20:$A$1000,"m月")=D$3)*($C$20:$C$1000=$B4)*($D$20:$D$1000))

有難うございました。
今後とも宜しく御願いします。

追記
ピボットテーブルも考えましたが知識不足の人もいるのでこの案(関数)にしました。

回答No.1

mshr1962

D4=SUMPRODUCT((MONTH($A$20:$A$41)&"月"=D$3)*($C$20:$C$41=$B4)*($D$20:$D$41))
これを左にコピー
D5=SUMPRODUCT((MONTH($A$20:$A$41)&"月"=D$3)*($C$20:$C$41=$B5)*($E$20:$E$41))
これを必要分下にコピーの後、左にコピー

補足

ご回答いただき有難うございます。
例えば下記を
=SUMPRODUCT((MONTH($A$20:$A$41)&"月"=D$3)*($C$20:$C$41=$B4)*($D$20:$D$41))

１=SUMPRODUCT((MONTH($A$20:$A$41)=D$3)*($C$20:$C$41=$B4)*($D$20:$D$41))
２=SUMPRODUCT((MONTH($A$20:$A$41)&"m月"=D$3)*($C$20:$C$41=$B4)*($D$20:$D$41))
３D3を　単純に1月とした　その前はmm/ddでしたが
色々試しましたがなんのマーク　値などが出ませんでした。