日々増えるデータの自動集計

質問

お願いします。長文です。
Windows2000、Office2000です。

このようなデータがあります。
毎日データを追加していきます。

6月1日(水) EDY チャージ -10000
6月1日(水) EDY A定食 300
6月2日(木) EDY A定食 320
6月2日(木) EDY お茶 100
6月6日(月) EDY 特別定食 300
6月6日(月) EDY お茶 250
6月6日(月) EDY コーヒー 400
6月7日(火) Suica チャージ -5000
6月7日(火) Suica 東京→横浜 130
6月7日(火) Suica 横浜→東京 450
6月8日(水) EDY A定食 300
6月8日(水) Suica 新聞 250
6月9日(木) EDY うどん 300
6月9日(木) EDY 水 450
6月9日(木) EDY パン 250
6月9日(木) Suica 東京→千葉 300
6月9日(木) Suica 千葉→東京 500

このデータから次のような集計結果を作成したいのですが、

EDYの日ごとの合計
6月1日(水) 300
6月2日(木) 420
6月3日(金) 0
6月4日(土) 0
6月5日(日) 0
6月6日(月) 950
6月7日(火) 0
6月8日(水) 300
6月9日(木) 1000

Suicaの日ごとの合計
6月7日(火) 580
6月8日(水) 250
6月9日(木) 800

どういう方法があるでしょうか。

【要点１】
やはりマクロを使うしかないのでしょうか？

【要点２】
利用のない日も０円の日として認識させたい。
このためには集計する際に期間を指定する必要がありますよね。

【要点３】
データシートを更新したら、リアルタイムで集計結果も更新したい。

【要点４】
集計結果を手動で更新するとしたら、どんな方法になりますか？

欲張りな質問かもしれませんがよろしくおねがいします。

回答No.5

AloneAgain · ベストアンサー

こんにちは。

>Sheet1の日付が6月1日かつカード種類がEDYの行の金額の合計
↑なら SUMPRODUCT関数を使えばできますよ。
（元データを変更する必要もありません）
*
元データのレイアウトがわかりにくいのですが、
日付が A列、カード種類が B列、項目名が C列、金額が D列で、
2行目からデータがあるとすれば、次のようにしてください。
*
集計するシートにあらかじめ日付を入れておいてください。
1行目が見出しとすれば、
A2 に　2005/6/1 と入れて、以下オートフィルでコピー
（月単位なら A31まで）

集計シートの B2に↓
----------------------------------------
=SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100="EDY"),Sheet1!$D$2:$D$100)
----------------------------------------
以下、B31までコピー
これで自動的に集計されます。
*
"EDY" を "Suica" にすれば "Suica" の集計になります。
元データは最大100行までと仮定しています。
データがもっと増えそうなら、数式の 100（3ヶ所）を 200とか300に変更してください。
*
ところで・・
質問にある元データの
1行目：6月1日(水) EDY チャージ -10000　と
8行目：6月7日(火) Suica チャージ -5000
が集計では無視されているようですが、なぜでしょうか？

補足

回答、ありがとうです。m(__)m

>元データのレイアウトがわかりにくいのですが、
>日付が A列、カード種類が B列、項目名が C列、金額が D列で、
>2行目からデータがあるとすれば

まさにそのとおりです。

>質問にある元データの
>1行目：6月1日(水) EDY チャージ -10000　と
>8行目：6月7日(火) Suica チャージ -5000
>が集計では無視されているようですが、なぜでしょうか

あ、ほんとだ。
この見本は手作業で集計したので抜けてしまいました。

夕飯食べたら、早速やってみます。(^_^);

回答No.4

kokorone

A1の事後報告です。
Ｑ３の自動処理でめげています。
あと、終了日が、入力日以降の処理が抜けていますが、
手動では、動作するマクロを作ってみました。
Function data_sum() As Integer

Dim strt_d As Variant
Dim end_d As Variant
Dim strt_d1 As Variant
Dim end_d1 As Variant
Dim loop_d As Variant
Dim check_d As Variant
Dim w_str As Variant
Dim w_len As Integer
Dim line1 As Integer
Dim line2 As Integer
Dim esum As Integer
Dim ssum As Integer

strt_d = Sheets("Sheet1").Cells(1, 7).Value
end_d = Sheets("Sheet1").Cells(1, 9).Value

strt_d1 = DateValue(strt_d)
end_d1 = DateValue(end_d)

line1 = 1
line2 = 2
esum = 0
ssum = 0
For loop_d = strt_d1 To end_d1
w_str = Sheets("Sheet1").Cells(line1, 1).Value
w_len = Len(w_str)
w_str = Left(w_str, w_len - 3)
check_d = DateValue(w_str)
If loop_d > check_d Then
'範囲外
While loop_d > check_d
line1 = line1 + 1
w_str = Sheets("Sheet1").Cells(line1, 1).Value
w_len = Len(w_str)
w_str = Left(w_str, w_len - 3)
check_d = DateValue(w_str)
Wend
End If
If loop_d = check_d Then
'該当日：集計処理
While loop_d = check_d
If Sheets("Sheet1").Cells(line1, 2).Value = "EDY" Then
esum = esum + Sheets("Sheet1").Cells(line1, 4).Value
End If
If Sheets("Sheet1").Cells(line1, 2).Value = "Suica" Then
ssum = ssum + Sheets("Sheet1").Cells(line1, 4).Value
End If
line1 = line1 + 1
w_str = Sheets("Sheet1").Cells(line1, 1).Value
w_len = Len(w_str)
w_str = Left(w_str, w_len - 3)
check_d = DateValue(w_str)
Wend
End If
'集計結果出力
Sheets("Sheet2").Cells(line2, 1).Value = loop_d
Sheets("Sheet2").Cells(line2, 2).Value = esum
Sheets("Sheet2").Cells(line2, 3).Value = ssum
line2 = line2 + 1
esum = 0
ssum = 0
Next loop_d
data_sum = 0
End Function

補足

さきほどは、どうも。(^_^);
まさにプログラミングですね。
理解するのにちょっと時間かかりそうです。

EDYの日ごとの合計
6月1日　Sheet1の日付が6月1日かつカード種類がEDYの行の金額の合計
6月2日　Sheet1の日付が6月2日かつカード種類がEDYの行の金額の合計
6月3日　Sheet1の日付が6月3日かつカード種類がEDYの行の金額の合計

セルにこんな内容の関数？を書けばいいんだろうって思っていたので、
本格的なプログラムにびびってます。

回答No.3

tana59

#2の方の回答の方が良いと思いますが、こんな方法もあります。
A1:日付 B1:利用 C1:内容 D1:金額 F1:EDY G1:Suica
F2:=IF(AND($B2=F$1,$D2>0),$D2,0)　←EDYの利用額
G2:=IF(AND($B2=G$1,$D2>0),$D2,0)　←Suicaの利用額
F2,G2を必要な行までオートフィル

データの最下行が１４行として、その下に集計結果を置くとすると。
A16から
6月1日(水)
6月2日(木)
6月3日(金)
6月4日(土)
6月5日(日)
6月6日(月)
6月7日(火)
・・・
B16に　=SUMIF($A$2:$A$14,$A16,F$2:F$14)　←EDYの日別利用額
B16を下方向にオートフィル
C列がSuicaで良いなら、C列にオートフィル

Suicaの集計行を別の行にしたいのであれば、
Suicaの日別利用額は更にその下に同様で、集計対象をF列からG列に変更

補足

またまた回答ありがとうございます。取り急ぎお礼まで。

いま、#2のkomet163さんのやり方を試してます。
ただし、データがない日も
　6月5日　なし　０
　6月6日　なし　０
としておかないとピボットテーブル上に日付が出ないみたいなので、もう少し調べてみます。

回答No.2

komet163

こんにちは、ピボットテーブルを利用してはどうでしょう。

A列は 日付のシリアルに変更して、
A1:日付 B1:利用 C1:内容 D1:金額 と項目名をつけます。
A列には、開始日から終了日まで、もれなく記入し、
D列には、既定値として 0 を入力しておきます。
上の表をピボットテーブルにします。
行フィールドには 日付、列フィールドには 利用、データアイテムには 金額 を指定します。
上の表に日々の支出を入力します。１日に複数行が必要なときは、行挿入で対応します。

集計結果の更新は手動になりますが、ピポットテーブル ツールバーの「データの更新」で随時可能です。
利用項目 に「Suica」「EDY」以外を入力しても柔軟に集計してくれます。
ピポットテーブルの集計結果は以下のようになります。

日付 EDY Suica (空白) 総計
6/1 -700 0 -700
6/2 420 0 420
6/3 0 0
・・・・・・
6/30 0 0
総計 1970 -3370 0 -1400

補足

取り急ぎお礼です。
早速やってみます。

回答No.1のkokoroneさんのも読みながらやってますが、
具体的にどうすればいいのか、なかなか難しい。
マクロとかVBAの基礎からやってます。

回答No.1

kokorone

Ｑ１：
おそらく、マクロを使うことになると思います。
Ｑ２：
集計の開始日・終了日の入力セルが必要で、
開始日から終了日までの日別処理を行わないと
いけません。
Ｑ３：
マクロをdatasum()とした場合、隠しセルに
=datasum()としておけば、常時処理が実行
されるため、自動集計も可能です。
Ｑ４：
フォームまたはコントロールのボタンを用意し、
datasum()を割り当てればいいかと。

補足

回答ありがとうございます。
実は私は、マクロはほとんど書いたことないのです。
集計結果シートに
=データシート名.日付=6月1日.sum
=データシート名.日付=6月2日.sum
=データシート名.日付=6月3日.sum
こんな感じで、関数（マクロ？）を並べるというイメージなのでしょうか？