エクセルで月毎の集計

質問

エクセルで銀行口座の管理をしようとしています。
A列に日付、B列に出金、C列に入金、E列に残高があります。（基本的に通帳の並びと同じです。）

で、月ごとの出金、入金の管理をしたいと考えております。
各列ともにどんどん下の行に出金/入金を入力していくつもりなので、A列のとある月だけの出金、入金を抽出合計できたらいいなぁと思っています。何かよい方法（数式？）をご教示頂きたくよろしくお願いします。

回答No.12

hallo-2007 · ベストアンサー

NO6,11です。
失礼しました。
＞=MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1　と　
＞=MATCH(DATE(YEAR(A),MONTH(A2),0)+0.5,A:A)　　をいれます。
は
＞=MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1　仮に式１
＞=MATCH(DATE(YEAR(A2),MONTH(A2)+1,0)+0.5,データ!A:A)　仮に式２
では如何でしょう。
=DATE(YEAR(A2),MONTH(A2)+1,0)+0.5　で今月の最終の日にちの12時が必要ですね。
仮にデータが
１　日付
２　4/10
３　4/20
４　5/1
５　5/10
６　6/10
・・・となっていて　A2が2009/5/1の場合は　式１は　4行目の４
式2は　5行目の　５　がでると思います。
ちなみにA2に4月の日付の場合は　式１はエラーになると思います。
2行目に仮のデータ　2000/1/1 とかデータの中で突拍子もない小さい日付を入れて置いてください。
ここまでうまく出来ましたら
=SUM(INDEX(データ!B:B,式１のセル):INDEX(データ!B:B,式２のセル))
で試してください。
ここまでできたら式１と式2をコピィして組み入れれば、それらの式をいれているセルは不要です。
>シートが別にでるのでNo.6さんのやり方にはかなり興味があるのですが
ですが、他の方の式も範囲を　データ!と別シートを使えば別シートでも大丈夫ですよ。
ただ、今後データが多くなったり、式をコピィして沢山使用した場合に
パソコンへの負担が少ないほう方法を紹介しています。
条件として、データが日付順に並んでいることが条件でそれを利用しています。
せっかくここまで来たので式を理解して応用してください。

お礼

お礼おそくなり、申し訳ありません、また、重なるコメントありがとうございます。
当初の目的は達成することができましたので、質問を締め切ることにします、ありがとうございました。

回答No.11

hallo-2007

Ｎo6です。
>=DATE(YEAR(A2),MONTH(A2),0)+0.5　のセルには、「＃N/A」
>=DATE(YEAR(A2),MONTH(A2)+1,0)+0.5　のセルには、「＃NAME？」
ちなみに新しいブックで
A2セルに　2009/8/14 と日付を入れます。
B2セルに　>=DATE(YEAR(A2),MONTH(A2),0)+0.5　と入れると2009/7/31 と表示
表示形式を　時刻で　2001/3/14 13:30 を選ぶと　2009/7/31 12:00
となります。
日付のシリアル値について、この辺りが理解できると大変便利ですよ。
YEAR(A2)　はA2セルの年を出しますので　2009
MONTH(A2) は月を出しますので　8
DATE関数の日の部分は　1なら　1日ですが、0なので前月の最終日になります。
=DATE(YEAR(A2),MONTH(A2),0)は、2009年8月の1日前なので 2009年7月31日
その値に　0.5を加えるので　2009/7/31　の正午12:00になるはずです。
この値をMATCH関数でデータで、その月の最初の行と最後の行を指定することが出来るということです。

補足

シートが別にでるのでNo.6さんのやり方にはかなり興味があるのですが、いまだにうまくいきません。なのですみませんが、おしえてください。

＞前月と今月の最終日の12時がでます。
ここまではうまくいきます。

＞=MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1　と　
＞=MATCH(DATE(YEAR(A),MONTH(A2),0)+0.5,A:A)　　をいれます。

上で、２行目のAはA2、さらに２行目のA:Aはデータ!A:Aの間違いと思い、修正したのですが、２行目の方の「最後にあるデータ行番号」がでません。

さらに、最後の式、
=SUM(INDEX(データ!B:B,MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1):INDEX(データ!B:B,MATCH(DATE(YEAR(A2),MONTH(A2)+1,0)+0.5,データ!A:A)))
がどうしてもエラーがでます。

というか、そもそも最後の式には、「前月と今月の最終日の12時のセル」も「最初と最後の行のセル」も参照されていないようなのですが、必要なのでしょうか？

回答No.10

某HN クロメート(Chromate)（@CoalTar）

作業列なしでもよかったです
G1セルに 4 と入力して
=SUMIF($A$2:$A$6,">="&DATE(2009,G1,1),B$2:B$6)
-SUMIF($A$2:$A$6,">="&DATE(2009,G1+1,1),B$2:B$6)

回答No.9

某HN クロメート(Chromate)（@CoalTar）

作業列を使っていいならF列に
=A2-DAY(A2)+1
として表示形式を日付にすると見やすくなります
SUMIFで集計すればSUMPRODUCT関数よりは軽いです
例 2009年4月の集計
=SUMIF($F$2:$F$100,"2009/4/1",B$2:B$100)

回答No.8

tom04

No.5・7です
ごめんなさい！

画像が上手くアップできませんでしたので
もう一度トライしてみます。
何度も失礼しました。m(__)m

回答No.7

tom04

こんにちは！
No.5です。

肝心のセルが「0」になってしまいましたか？

考えられる原因としては、
EOMONTH関数の関係で
メニュー　→　ツール　→　アドイン　→　「分析ツール」にチェックが入っていないと
「0」となる可能性があります。

他に考えられる問題としては、
DSUM関数は検索範囲や検索条件に「タイトル行」（見出し行）も必要ですので
タイトル項目が一致しないと正確な集計は出来ません。

ん～～～～
考えられる原因としてはパッ！と思いつくといえばこの程度です。

尚、前回の回答の補足になりますが、

もう一度画像をアップさせてもらって

↓の表の作業列
I2セルに
=IF(G1="","",">="&DATE(2009,G1,1))
J2セルに
=IF(G1="","","<="&EOMONTH(DATE(2009,G1,1),0))

G3セルに
=IF(G1="","",DSUM(A1:E9,2,I1:J2))

H3セルに
=IF(G1="","",DSUM(A1:E9,3,I1:J2))

としてみました。
結局前回の回答とあまり変化はありませんが、
作業列を簡素化してみました。

以上、お役に立てれば幸いです。m(__)m

回答No.6

hallo-2007

SUMPRODUCT関数や配列関数で集計しても良いのですが、多用するとパソコンの負担になります。
>各列ともにどんどん下の行に出金/入金を入力していくつもりなので、
つまりデータが日付順に上から並んでいくのであれば、以下参考にしてください
集計シート
年月　出金　入金
09/1
09/2
・・・　年月は、2009/1/1　とか日付を入れて、表示形式　ユーザー定義で　yy年mm月　とかして年月の表示にしておきます。
説明のために、空いている列に　
=DATE(YEAR(A2),MONTH(A2),0)+0.5 と　=DATE(YEAR(A2),MONTH(A2)+1,0)+0.5
入れて下へコピィしてください。
前月と今月の最終日の12時がでます。
日々のデータがデータというシート名、A列に日付をいれているとして
=MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1　と　=MATCH(DATE(YEAR(A),MONTH(A2),0)+0.5,A:A)
にするとデータで集計したい月の最初にある日付の行番号と最後にあるデータ行番号がでます。
この値を利用して合計する範囲を指定します。
=SUM(INDEX(データ!B:B,MATCH(DATE(YEAR(A2),MONTH(A2),0)+0.5,データ!A:A)+1):INDEX(データ!データ!B:B,MATCH(DATE(YEAR(A2),MONTH(A2)+1,0)+0.5,データ!A:A)))
式は長くなりますが、データが増えても、式を下へコピィしてもパソコンへの負担は変わりません。
INDEX関数ですが、INDEX(合計したい列を指定,合計したい最初の行番号指定):INDEX(合計したい列を指定,合計したい最後の行番号指定)
といったように範囲を指定するのに使えます。

補足

ありがとうございます。
ご教示どおり、エクセルを実施してみましたが、
=DATE(YEAR(A2),MONTH(A2),0)+0.5　のセルには、「＃N/A」
=DATE(YEAR(A2),MONTH(A2)+1,0)+0.5　のセルには、「＃NAME？」
の表示になってしまいました。
大変お手数おかけしますが、原因等推測できないでしょうか？

回答No.5

tom04

こんばんは！
参考になるかどうか分かりませんが・・・

↓の画像のように作業列を使用して
G1セルに月の数値を入力するだけで、出金・入金の月集計が出来るようにしています。

DSUM関数を使っていますので、
I・J列を参照するようにしています。
（I・J列が目障りであれば、非表示にしてみてください）

まずI2セルに
=IF(G1="","",DATE(2009,G1,1))
J2セルに
=IF(G1="","",EOMONTH(DATE(2009,G1,1),0))
（データの日付は今年を前提にしています）

I4セルに
=">="&I2
J4セルに
="<="&J2
という数式をそれぞれ入れています。

そして出金合計のG3セルに
=IF(G1="","",DSUM($A$1:$E$100,2,I3:J4))
入金合計のH3セルに
=IF(G1="","",DSUM($A$1:$E$100,3,I3:J4))
という数式が入っています。

尚、元データは100行目まで対応できるようにしていますが、
データ量が多い場合は範囲指定の領域をアレンジしてみてください。

そして、EOMONTH関数を使っていますので
メニュー　→　データ　→　アドイン　→　「分析ツール」にチェックを入れておいてください。

これでG1セルに
月の数値を入力するだけでその月の出入金の集計が表示されるはずです。

当方使用のExcel2003での回答でした。

以上、参考になれば幸いですが、
他に良い方法があれば軽く読み流してくださいね。m(__)m

補足

詳しい説明ありがとうございます。
図とまったく同じブックを作ってみたのですが、肝心のG3、H3が“0”になったままです。
他のところは全く同じ数値がでているのですが・・・。当方のエクセルも2003ですが、私の間違いだとしたら、どのようなことが考えられるのでしょうか？

回答No.4

imogasi

>A列のとある月だけの
合計したい月を指定してくれるのですね例えば、F1セルとかに。
それならありがたい。
例データA-D列
日付 出金 入金 残高
2009/6/30 320000
2009/7/23 10000 310000
2009/7/30 5000 305000
2009/8/5 100000 405000
2009/8/8 20000 385000
2009/8/11 10000 375000
2009/8/13 6000 369000
D1に2009、E1に8　（年と月数字）
F1に出金合計　=SUMPRODUCT((YEAR(A3:A100)=2009)*(MONTH(A3:A100)=8)*(B3:B100))
２００７ならSUMIFSを使ってください。
だから、エクセルのバージョンを質問に書くこと。
ーー
これではダメなのだろうな。自動で月が変わると出金合計欄に金額が現れるとかを希望なのかな。

補足

ありがとうございます。
よくわからない点をさらに質問させてください、F1に算出される合計は、E1に入力した値と関連してるようには思えないのですが。
つまりはF1の数式に、E1がはいっていないので。
ですが、やってみるとたしかに8月だけの合計が算出されました、なぜ？？？でも、E1に7を入力しても７月の合計ではありませんでした。
すみませんが、お時間ありましたら、補足お願いします。

回答No.3

KURUMITO

Ｆ2セルから下方には求めたい月を表示させるのですが、例えば4月の月を求めたいのでしたら4/1と入力して数式バー上では2009/4/1のようにします。そのうえでＦ列を選択して右クリックし、「セルの書式設定」から「表示形式」の「ユーザー設定」で m"月"　と入力します。これで4月と表示されるようになります。
Ｇ1セルには出金、Ｈ1セルには入金と入力します。
Ｇ2セルには次の式を入力したのちにＨ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。
=IF($F2="","",SUMPRODUCT((YEAR($A$2:$A$100)=YEAR($F2))*(MONTH($A$2:$A$100)=MONTH($F2))*(B$2:B$100)))
これでＦ2セルに4/1と入力すればＦ2セルには4月と表示され、出金および入金額がＧ２とＨ2セルに表示されます。
Ｆ3セルに5/1と入力すれば5月と表示され出金額および入金額がＧ3およびＨ3セルに表示されます。

補足

試してみました。たしかに、私のやりたいことができておりました。
他にも回答して頂いているので、一端ためしてみて、どれにするか決めたいと思います。本当にありがとうございました。

回答No.2

Cupper

面倒なんでピボットテーブルを使うことをお奨めします

　Excel豆知識 ピボットテーブル
　http://www11.plala.or.jp/koma_Excel/pivot_menu.html
参考にしてください

補足

参考URLを拝見しました。
下にも書きましたが、便利そうではあるんですが、できればリアルタイムにあるセルに表示できれば、と思っています。
お手数でなければ、計算式をお教え願えませんでしょうか？