家計簿作成（Excel関数）

質問

家計簿作成（Excel関数）

Excel素人ながら家計簿を作成しております。

A1セルより、

　「日付」
　「コード（VLOOKUP関数用）」
　「項目（食費・光熱費等）」
　「支出額」

の列を作りました。

別のセル（シート）に、

「日ごとの支出合計額」及び
「日ごとの各項目別支出合計額」

を算出したいのですが、適した関数がわかりません。

大変恐縮ですがアドバイス頂ければ幸いです。

また、EXCELで家計簿を作るにあたり、入力方法（フォーマット）や、
「こんな家計簿にすると便利」、などのアドバイスを併せて頂ければ嬉しいです。

何卒ご教示頂きたくお願い致します。

回答No.2

KURUMITO · ベストアンサー

シート1にお示しのデータがあるとしてＡ1セルに日付、Ｂ1セルにコード、Ｃ1セルに項目、Ｄ1セルに支出額の文字がそれぞれ入力されており、各データは2行目から下方に有るとします。
作業列を作って対応するのが判り易くしかも計算にもそれほどの負担がかかりません。
Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=A2&C2

そこでお求めの表ですがシート2に作るとしてＡ1セルには日付、Ｂ1セルには曜日、Ｃ1セルから右横のセルにはシート1の項目名で使われているのと同じ文字列を入力します。例えばＣ1セルに食費、Ｄ1セルに光熱費などと入力します。
Ａ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(MIN(Sheet1!A:A)+ROW(A1)-1>MAX(Sheet1!A:A),"",MIN(Sheet1!A:A)+ROW(A1)-1)

Ａ列を選択してセルの書式設定から表示形式で日付を選択します。これでＡ列にはシート1で入力されている日付で最も古い日付から新しい日付までが表示されます。

Ｂ2セルには次の式を入力して下方にオートフィルドラッグします。

=TEXT(A2,"aaa")

これでＢ列には曜日が表示されます。
Ｃ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(OR($A2="",C$1=""),"",SUMIF(Sheet1!$E:$E,$A2&C$1,Sheet1!$D:$D))

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.6

imogasi

普通家計簿には計数に関しては、収入・支出・残高欄を設けると思うが。
ーー
支出額素か無いのは小使い帳方式ですね。収入が月１回しかないなら、-200000のようにする手もあるが
姑息な感じ例。
残高が出ないとどうしようもないでしょう
B,C列
C2の式は=-SUMIF($B$2:B2,"<0")-SUMIF($B$2:B2,">0")
支出 残高
-100 100
20 80
30 50
25 25
35 -10
70 -80
-100 20
20 0
やはり収入欄は設けるべきでしょう。
ーー
＞日ごとの支出合計額」及び
はSUMIF関数で考えると良いでしょう。
日付 支出（収入はマイナス） 残高
2010/8/1 -100 100
2010/8/1 20 80
2010/8/1 30 50
2010/8/2 25 25
2010/8/2 35 -10
2010/8/2 70 -80
2010/8/2 -100 20
2010/8/3 20 0
F1、G1以下に
日付 支出合計
2010/8/1 50
2010/8/2 130
以下日付はオートフィル
G2の式は=SUMPRODUCT(($A$2:$A$100=F2)*($B$2:$B$100>0)*($B$2:$B$100))
２００７の場合はSUMIFS関数を使ってください。
ーー
項目別が入ると３条件になります。
収入はマイナス方式の、上記は決してお勧めしません。
ーー
エクセルの勉強以外なら、出来合いの（フリー）ソフトを探すべきです。

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.5

tom04

No.4です！
たびたびごめんなさい。
前回の方法では、質問にある

＞「日ごとの支出合計額」

に関して載せていませんでしたので、一案としてです。
前回の表で、Sheet2のA列とB列の間に1列挿入し、
=SUM（”その行”）　（←行の範囲指定は適宜行ってください。空白セルを範囲指定しておいてもOKです。）
のような数式を入れオートフィルで下へコピーすれば日付ごとの集計ができると思います。

それからこれも余計なお世話になるかも知れませんが、
Sheet2の1行目と2行目の間に1行挿入し、
=SUM（”その列”）　（←列の範囲指定も適当に・・・）
の数式を入れてオートフィルで右へコピーでその月の各項目ごとの合計が表示されます。

列・行挿入しますので、前回の数式を入れるセルは変わりますが
数式そのものは前回そのままでOKだと思います。

どうも何度も失礼しました。m(__)m

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.4

tom04

こんにちは！
一例です。
↓の画像のようにSheet1には、収入の列・残高の列を設けてみました。

そしてSheet1に作業用の列を2列設けています。

まず、F3セルに
=IF(COUNTBLANK(D3:E3)=2,"",F2+D3-E3)
という数式を入れ、オートフィルで下へずぃ～～～！っとコピーします。
これで収入・支出額の欄に数値が入力されるたびに、残高が表示されます。

作業列G2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")
H2セルに
=IF(AND(D2="",COUNTIF($C$2:C2,C2)=1),ROW(),"")
という数式を入れ、G2・H2セルを範囲指定し、H2セルのフィルハンドル下へずぃ～～～！っとコピーしておきます。
元データは空白でもかまいませんのでかなり下までコピーしておきます。

そして、Sheet2の
A2セルは
=IF(COUNT(Sheet1!G:G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!G:G,ROW(A1))))
として、これもかなり下までオートフィルでコピーします。
これでSheet1の日付が重複なしに表示されます。

次にB1セルに
=IF(COUNT(Sheet1!$H:$H)<COLUMN(A1),"",INDEX(Sheet1!$C:$C,SMALL(Sheet1!$H:$H,COLUMN(A1))))
という数式を入れ、列（右）方向にオートフィルでコピー
これで各項目が表示されます。

最後にB2セルに
=IF(OR($A2="",B$1=""),"",SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$C$2:$C$1000=B$1)*(Sheet1!$E$2:$E$1000)))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

以上、余計なお世話かも知れませんが
参考になれば幸いです。m(__)m

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.3

KURUMITO

日ごとの支出合計額は別の式を使っても良いですが回答2で示した方法ですでに表を作成されているのでしたらＣ列を選択して右クリックして「挿入」で新しくＣ列を作成しＣ1セルには支出合計額とでも入力します。
Ｃ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!D:D))

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。

回答No.1

riveron77

直接の回答ではないのですが…

Excelの勉強しながら家計簿を…ということでしたらスルーを(汗)
家計簿をつけたいわけで、Excelはどちらでも、ということでしたら↓こちらもいいのかな、と。

【うきうき家計簿　フリーソフト版】
http://www.eases.jp/product/ukiuki/ukiuki.htm

無料ですし、結構使いやすいみたいです(by ウチの嫁)。
ご参考まで。

お礼

ご回答拝読致しました。大変参考になりました。

貴重なお時間を割いてのご回答、誠に感謝致しております。

また機会がありましたら宜しくお願い致します。