EXCEL関数を用いた財務状況のリストの作成

質問

皆様、EXCELについてのご質問です。

EXCEL初心者のものです。

当たり前のような質問でしたら、すみませんm(__)m。

現在、財務状況をしめすリストを作成中です。

予算を使用した、年月日（例：2012/04/01）と、横に金額（例：￥5000）が同じ列（2列）に一年分だっーと並んでいます。

そこで、月ごとに自動的にピックアップして、（例えば、4月分10件だけ）、
その月の予算の利用額合計（￥5000+￥3500+￥6300+～）
を自動的に計算して、セルに結果をだしてくれるような関数ないでしょうか。

month関数を利用するとできるのでしょうか。

真摯なアドバイス、お待ちしております。

どうぞ宜しく御願いいたします。

回答No.6

KURUMITO

難しい関数を考えるよりも出来るだけ簡単な関数を使って解決するようにすることが計算にも負担のかからない方法としてお勧めです。そのためには作業列を使うことです。
例えばA２セルから下方に日付が有り、B２セルから下方に金額が入力されているとします。
A列とB列の間に１列挿入して金額をC列にします。
B２セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",YEAR(A2)&"/"&MONTH(A2))

これでA２セルが2012/12/9 でしたら2012/12のように表示されますね。
作業列が目障りでしたらB列を選択して右クリックし「非表示」を選択すればよいでしょう。

その上で例えばD１セルに西暦年の2012などと入力し、D２セルから下方に月である１から１２までを入力します。
E２セルから下方にその月の金額の合計を表示させるとしたらE２セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(D$1="",D2=""),"",SUMIF(B:B,D$1&"/"&D2,C:C))

その月の件数をF列に表示させるのでしたらF２セルに次の式を入力して下方にドラッグコピーします。

=IF(OR(D$1="",D2=""),"",COUNTIF(B:B,D$1&"/"&D2))

お礼

こんにちは。お返事遅れて大変すみませんでしたm(__)m
今回はご回答ありがとうございました。大変参考になりました！

回答No.5

kagakusuki

SUMPRODUCT関数には、計算処理に要するコンピュータの負荷が大きくなりやすいという短所があります。
　御質問の内容程度でしたら、SUMPRODUCT関数を使うまでも御座いません。
　例えば、4月分のみの合計を求める際には、5月1日よりも前の金額の合計から、4月1日よりも前の金額の合計を差し引いた値を求めれば良い訳です。（但し、似た方法ではありますが、3月31日よりも後の合計額から4月30日よりも後の合計額を差し引く方法は、計算ミスが生じる恐れがあるため避けた方が良いと思います）

　今仮に、A列に年月日が入力されていて、B列に金額が入力されているものとします。
　そして、D1セルに入力される整数値によって年を、F1セルに入力される整数値によって月を、指定するものとします。

　その場合、年と月を指定した月における合計金額を求める関数は、次の様なものとなります。

=IF(ISNUMBER(($D$1&"/"&$F$1&"/1")+0),SUMIF($A:$A,"<"&DATE($D$1,$F$1+1,1),$B:$B)-SUMIF($A:$A,"<"&DATE($D$1,$F$1,1),$B:$B),"")

お礼

こんにちは。お返事遅れて大変すみませんでしたm(__)m
今回はご回答ありがとうございました。大変参考になりました。
まだまだ苦闘しておりますが、頑張って関数をあてはめてみますね！！

回答No.4

MarcoRossiItaly

データがどのように配置されているのか確信が持てないのですが、「同じ行内の 1 列が年月日、他の 1 列が金額」という意味ですか？そうだということにして、話を進めます。

これは一般的には、 SUMIF または SUMIFS 関数ですね。また、年、月、日を表す整数から年月日のシリアル値を作るには、 DATE 関数というのが使えます。

添付図では、次式を入力しています。なお「¥」マークは、セルの書式を「通貨」に設定することで表示させてみました。

E4　=sumif(A$4:A$9,">="&date(E$1,d4,1),B$4:B$9)-sumif(A$4:A$9,">="&date(E$1,d4+1,1),B$4:B$9)　…… Excel2003 以前
　　あるいは
　　=sumifs(B$4:B$9,A$4:A$9,">="&date(E$1,d4,1),A$4:A$9,"<"&date(E$1,d4+1,1))　…… Excel2007 以後（No.3さんの数式）

お礼

こんにちは。お返事遅れて大変すみませんでしたm(__)m
今回はご回答ありがとうございました。大変参考になりました！
頑張って、当てはめていますね！

回答No.3

tom04

No.2です！
たびたびごめんなさい。

前回の数式で
SUMIFS関数を使用して方が間違っていました。

=IF(D1="","",SUMIFS(B:B,A:A,">="&DATE(2012,D1,1),A:A,"<"&DATE(2012,D1+1,1)))

に訂正してください。m(_ _)m

お礼

こんにちは。お返事遅れて大変すみませんでしたm(__)m
今回はご回答ありがとうございました。大変参考になりました！

回答No.2

tom04

こんばんは！
一例です。

↓の画像でD1セルに「月」の数値を入力するとします。

表示したいセル（D2）に
=IF(D1="","",SUMPRODUCT((MONTH(A2:A1000)=D1)*(B2:B1000)))
という数式を入れています。

上記数式はExcel2003以前のバージョンでも大丈夫です。
ただ、データ数がもっと多い場合で、Excel2007以降のバージョンをお使いであれば

=IF(D1="","",SUMIFS(B:B,A:A,">="&DATE(2012,D1,1),A:A,"<"&DATE(2012,D1+1,1)-1))
という数式にすればOKだと思います。

別案として、
オートフィルタを使用すればSUBTOTAL関数が使用できます。

F1セルに　←　オートフィルタをかけても非表示にならない行にします。
=SUBTOTAL(9,B:B)

という数式を入れ、A列に好みの月でオートフィルタをかけてみてください。
表示されたセルだけの合計が表示されます。

Excel2007以降であれば「月」単位でオートフィルタが可能です。
Excel2003以前の場合はオートフィルタのオプションから2012/4/1以上　AND　2012/4/30以下
という感じで指定してやる必要があります。

参考になりますかね？m(_ _)m

回答No.1

shintaro-2

一番簡単なのは、
集計機能で集計行を月単位で入れること

応用を効かすのであれば、
ピボットテーブルで別シートに月単位でまとめた表を作成する。

お礼

こんにちは。お返事遅れて大変すみませんでしたm(__)m
今回はご回答ありがとうございました。参考になりました！