EXCEL 一覧のデータベースから月・日単位の合計を算出したい

質問

FXの損益表を作成しているのですが、

A1～A1000には日時が入力されています。
B1～B1000のは収支が入力されています。
　　　A　　　　　　　B
2008/10/1 12:15　　+500
2008/10/1 15:18　　-100
2008/10/2 10:20　　+600
2008/10/3 15:20　　-500
　　　・　　　　
　　　・
　　　・
といった感じでデータベースがあります。

このデータベースを例えばCの欄に日ごとの集計、Dの欄に月ごとの集計を表示したいのですが
どういった関数を使用すればいいのかわかりません。

何かよい方法があればご教授ください。

回答No.8

gyouda1114

>日毎の集計に関してですが、こちらがうまく集計できません。データリストの日付データとSUMIF関数で指定したセルと等しくないようで０の値となってしまいます。

A列の日付の書式をコピーして、D列に貼り付けてみてください。そうするとＡ列とＤ列の日付データが同じになると思います。

又は
画像の　D2に =A2　　D3に　=A4　と日付をリンクしてみてください

お礼

再度ご回答いただき、大変感謝しております。
書式をコピーしても出来ませんでした・・
恐らく日付データに時間が入っているのが原因だと思います。

日付データから時間を抜き出したものを別セルに作成し、
そのセルの日付データを使用するとうまくできました。

大変参考になるアドバイス、ありがとうございました。

回答No.7

gyouda1114

回答番号：No.4です
＞空欄のセルがあると０となってしまいます。
どこに空欄があると０になりますか
検証しても０にはなりませんが

＞囲指定はできれば空欄のセルも含めて（今後入力する予定のあるセル）計算したいのですが

下の式は99行まで入力可能です
=SUMPRODUCT((MONTH($A$2:$A$100)=F2)*$C2:$C100)

$A$2:$A$100及び$C2:$C100を変えれば範囲指定できると思いますが

補足

再度ありがとうございます。
何故かもう一度挑戦したら算出できました！
月の集計はSUMPRODUCT関数でバッチリです。

日毎の集計に関してですが、こちらがうまく集計できません。
データリストの日付データとSUMIF関数で指定したセルと等しくないようで
０の値となってしまいます。

データリストの日付では、(2009/1/1 0:33:00)と表示されているので
一致しないのでしょうか。

確認のためデータリストの日付欄を手入力にて（2009/1/1）と入力したら
ちゃんとデータが反映されたので、参照間違いはないと思うのですが・・

回答No.6

noname#99913

(1)Ｃ・Ｄ・Ｅ列に次の式を入力し、下へコピー
Ｃ　　　　　　　　　　Ｄ　　　　　　　　　Ｅ
=MONTH(A1)　　　=DAY(A1)　　　=C1&D1

(2)Ｆ１に月数、Ｈ１に日数、Ｇ１・Ｉ１に下の式を入力する。
Ｆ　　　　Ｇ　　　　　　　　　　　　　　　　　　Ｈ　　　Ｉ
１０　　　=SUMIF($C:$C,F1,$B:$B)　　　１　　　=SUMIF($E:$E,F1&H1,$B:$B)

この場合、Ｇ１には10月の合計が、Ｉ１には10月1日の合計が入ります。Ｆ１とＨ１の値を変えれば、目的の月、目的の月日の合計に変わります。

すべての月、すべての日についての結果を同時に見たい場合は、Ｆ列とＨ列にすべての月日を入力し、下へコピーしてください。

回答No.5

imogasi

これは実務的な作業だし、エクセル操作の、データーピボットテーブルと・・を使うことをお勧めします。
手軽です。
年月や年月日の列をわざわざ関数などで作らなくても
ピボットテーブルで日別表を作成後
下記の例の日時セルで右クリックして
グループ化と詳細の表示ーグループ化ー「月」などの指定で
合計 / 損益
日時 合計
10月 1200
11月 1200
12月 800
総計 3200
のような集計表などが出ます。
ーー
例データは
A列　　　　　　　B列
日時 　　　　損益
2008/10/1 10:30 800
2008/10/1 1:20 400
2008/10/2 10:20 -200
2008/10/3 15:20 200
2008/11/1 10:30 800
2008/11/1 1:20 400
2008/12/2 10:20 600
2008/12/3 15:20 200
関数ならSUMPRODUCT
か
２００７ならSUMIFS
http://www.becoolusers.com/excel/sumifs.htmlの後半
を使えば出ます。
月初日より大、月末日より小という複数条件で、両者をAND条件で使えます。

お礼

参考になるＵＲＬ、ありがとうございます。
あいにく2003ですので、使用できませんでした・・
スペックを掲載しておらず、申しわけありません。

ピボットテーブル、今後も活用できそうです。
しかし、今回は入力欄に数値データを入力すれば
いろいろな集計表にデータを飛ばそうと考えていますので、
手動で作業が必要なピボットテーブルは活用しそうにありません。
ご丁寧な回答、ありがとうございました。

回答No.4

gyouda1114

SUMIFとSUMPRODUCT関数で

日毎の合計　D2に日付　=SUMIF($A:$A,D2,$C:$C)
月毎の合計　G2に月の数値　=SUMPRODUCT((MONTH($A$2:$A$100)=F2)*$C2:$C100)

画像で確認ください

お礼

画像付きで丁寧なご回答ありがとうございます。
SUMPRODUCT関数を入力して試してみたところ、うまくいったのですが
空欄のセルがあると０となってしまいます。

範囲指定はできれば空欄のセルも含めて（今後入力する予定のあるセル）計算したいのですが
何か良い方法はありませんでしょうか。

回答No.3

noname#204879

D　　　　E
1　2008/10/1　 400
2　2008/10/2　 600
3　2008/10/3　-500
4

E1: =SUMIF(A$1:A$1000,">="&D1,B$1:B$1000)-SUMIF(A$1:A$1000,">="&(D1+1),B$1:B$1000)

お礼

ご回答ありがとうございます。
SUMIF関数をセルに入力してみたのですが、合計が０になってしまいます。
ワークシートに問題があるのでしょうか・・・

回答No.2

noname#99913

こういう場合は、作業列を使うと、やりやすくなります。Ａ列は文字列ではなく、日付のシリアル値であると仮定します。

(1)Ｃ列、Ｄ列に次の式を入力し、下へコピー
Ｃ　　　　　　Ｄ
=DAY(A1)　　　=MONTH(A1)　　　

(2)Ｅ・Ｇ列に日数・月数、Ｆ・Ｈ列に下の式を入力する。
Ｅ　　　Ｆ　　　　　　　　　　　Ｇ　　　　Ｈ
１　　　=SUMIF(C:C,E1,B:B)　　　１０　　　=SUMIF(D:D,G1,B:B)

この場合は、1日の合計と、10月の合計を表示します。このままでは、日付のほうは、月に関わらず合計を出しますので、複数の月が混じる場合は、適宜、演算範囲を指定してください。

お礼

ご回答いただきありがとうございます。
１月～１２月まで毎月の損益を入力しているので
範囲変更をするのであればドラッグで足して計算をする方が早そうです。
SUMIF関数を使用してどうにかならないか色々試行錯誤していましたが
不要な計算式がワークシート内に溜まりすぎて訳がわからなくなってしまいました・・ｗ

回答No.1

noname#245936

A列が日付時間型のデータであれば、
別列に、年月、年月日の記述列を追加。
ピボットテーブルで集計かければ一撃でできます。

お礼

ビボットテーブルについて検索してみました。
便利な機能で今後も活用できそうです。
ありがとうございました。