年間ごとの集計を求めたいのですが

質問

単純な入出金表があり、数年に渡っての取引が記されています。支出と入金を年毎で算出したいのですがExcel関数で出来ますでしょうか。
表は以下の様になっています。

　　　A　　　　　B　　　　　C　　　　　 D
1　　日付　　　名前　　　支払金額　　入金金額
2　2000/1/3　　〇〇　　　　　　　　　30,000
3　2000/2/10　 〇〇　　　15,000
4　2001/3/5　　〇〇　　　　　　　　　10,000
5　2001/12/3　 〇〇　　　10,000
6　2002/6/4　　〇〇　　　　　　　　　 5,000

元表には手は加えず(集計・並べ替え等)参照するのみにしたいと考えています。

SUMIF関数やVLOOKUP等考えたのですが上手くいきません。皆様御教授御願い致します。

回答No.5

matsu_jun · ベストアンサー

分かりやすくSUMIFを利用するとすれば

=SUMIF(A2:A6,">=2000/1/1",C2:C6)-SUMIF(A2:A6,">=2001/1/1",C2:C6)
2000年1月1日以降のデータの合計 - 2001年1月1日以降のデータの合計

これで2000年1月1日～12月31日までのデータの合計が出ます。
これだと年度毎集計でも応用が効きます。2000年度なら
=SUMIF(A2:A6,">=2000/4/1",C2:C6)-SUMIF(A2:A6,">=2001/4/1",C2:C6)

お礼

ご回答下さいまして有難うございます。
私の使用環境に一番あった方法でした。御教授下さいまして有難うございました。

回答No.6

SAKURAMYLOVE

配列数式を使う手もあります。
データが100行まであるとして、Ｆ列からＩ列までに出力するものとします。
【手順】
(1)以下のフィールド名を設定します。
Ｆ、Ｇ列は、集計する年頭と年末日付をセットしておきます。
　　　Ｆ　　　　　　　Ｇ　　　　　　　　　　Ｈ　　　　　　 Ｉ
1　　ＦＲＯＭ　　　　ＴＯ　　　　　　支払金額　　入金金額
2　2000/1/1　　2000/12/31　　　　　　　　　
3　2001/1/1　　2001/12/31
4　2002/1/1　　2002/12/31

(2)支払い金額および入金金額のしたにそれぞれ以下の式を入力します。
入力終了時、Crtrl+Shiftを押しながらEnterキーを押します。
（式全体が{=・・・　}となります）
H2=SUM(IF((F2<=$A$2:$A$100)*(G2>=$A$2:$A$100),$C$2:$C$100,0))
I2=SUM(IF((F2<=$A$2:$A$100)*(G2>=$A$2:$A$100),$D$2:$D$100,0))
(3)H2,I2を下の行にコピーします。

お礼

ご回答下さいまして有難うございます。
悩みは無事解決いたしました。御教授下さいました方法も次の機会に生かしたいと思います。ありがとうございました。

回答No.4

KenKen_SP

No.2 mshr1962さんのSUMPRODUCT関数がお勧めです。

それで、半期とか任意の期間を指定して集計するなら、
=SUMPRODUCT((A2:A6>=DATEVALUE("2000/1/1"))*(A2:A6<=DATEVALUE("2000/6/30"))*(C2:C6))

ですね。

また、別解としてオートフィルターとSUBTOTAL関数の組み合わせでもできます。

1. オートフィルターで例えば2000/1/1から2000/12/31のデータを表示
2. ＝SUBTOTAL(9,C2:C65536)

ご参考までに。

お礼

ご回答下さいまして有難うございます。
SUMPRODUCT関数良いな、と思ったのですが私の使用環境ではもうすこし考えないとダメなようです。
オートフィルターでSUBTOTALも便利ですが今の使用環境に適してはいないのです…すみません。
御教授下さいまして有難うございました。

回答No.3

jasmine002

Excelは最近余り使っていないので自信無いですが、、。
E列に年度を書き出すところまではNO1さんと同じです。
あと、
「データ」－－＞「集計」で、
「グループの基準」－－－－－年度
「集計の方法」－－－－－－－合計
「集計するフィールド」－－－収入、支出
そして
「現在の集計表と置き換える」のチェックをはずして
完了、でどうでしょうか。

　元に戻す時は「データ」－－＞「集計」で、
「すべて削除」ボタンをクリックしますと元の表に。

　なお「集計行をデータの下に挿入する」のチェックを
はずしますと、集計はデータの上に表示されます。
私の感覚では見苦しい感じがしますけれども、、、。

お礼

ご回答有難うございます。
やはりJasmine002 さんがおっしゃっているとうりビジュアル的に少し気がかりです。会社全体で使うものなので次の機会に生かしたいと思います。御教授下さいまして有難うございました。

回答No.2

mshr1962

SUMIFだと
=SUM(IF((YEAR(A2:A6)=2000)*1,C2:C6))
でShft+Ctrl+Enrerで配列数式にするか

=SUMPRODUCT((YEAR(A2:A6)=2000)*(C2:C6))

入金はC2:C6をD2:D6に変えるだけ．．．

お礼

ご回答下さいまして有難うございます。
SUMIFでの方法がうまく出来なかったのでSUMPRODUCTを試したところ出来ました。ただ、表の中に空白や文字列があると答えが返ってこないので色々工夫したいと思います。
御教授くださいまして有難うございました。

回答No.1

fukkyse

E列に年数を抽出。「E2=YEAR(A2)…」として、SUMIF関数でOKではないでしょうか。

お礼

ご回答有難うございます。御教授頂いた方法でうまく出来ました。
ただ、既存の表にこの作業（「E2=YEAR(A2)…」)をしていくには少し時間がかかってしまいます。(実はブックが300近くあるものですから…。すみません。)
知識として蓄えて次に生かしたいと思います。