Google SpreadSheetで高機能家計簿を作る！
よろしければこちらもご覧ください。
精算もラクラク！Google Driveのフォームとスプレッドシートを使って自動精算システムを作ろう！ - まだ中学生のブログ
上記記事で家計簿にはラベル機能が必須であると主張してみました。
それでは、それをどうやって実現するか。
Google Driveのspreadsheetを使うと簡単に実現できるので紹介したいと思います。
まず、spreadsheetで使う関数を紹介します。
エクセルでも同様の機能なので覚えておいて損なしです。
・SUBTOTAL(function_code, range_1, range_2, ... range_30)
range_1, range_2, ... range_30で指定した範囲をfunction_codeの方法で集計します。
function_codeはこちらを参考にしてください。
今回は合計(sum関数相当)の9を使います。
=SUBTOTAL(9, A1:A10)
のように使います。
共有設定をしているので、よろしければお使いください。
メニューの「ファイル」→「コピーを作成」
で自分のGoogle Driveにコピーが作成され、編集できるようになります。
シートは2枚だけ。
メインとなる家計簿が「項目シート」ラベルを管理する「ラベルシート」です。
このようにラベルを作成してみました。
(画像は一部なので、表にまとめました。)
| 備考 | ||
|
使用者
|
太郎 | |
| 花子 | ||
| 二人 | ||
|
支払い方法
|
現金 | |
| クレジット | ||
| 電子マネー | ||
|
購入したもののジャンル
|
食費 | |
| 交通費 | ||
| 生活費 | ||
| 遊び | ||
| 化粧品 | ||
| 美容 | 散髪含む | |
| 衣類 | ||
| 本 | ||
| 雑貨 | ||
| お酒 | 飲み会etc. | |
| 医療費 | ||
| 光熱費 | ||
| 通信費 | ||
| 給料 | ||
| その他 | ||
|
目的
|
交際費 | |
| 必需 | ||
| 友達 | ||
| デート | ||
| プレゼント |
使用者を作ることで、家族で使用し、家計を分析することもできます。
個人で使用する場合は不要かと思います。
給料が入ったら、マイナスの値で金額を入力すると良いでしょう。
出費がプラス、収支がプラスとすると、合計がマイナス値になれば黒字です。
家計簿のシートはこのようにしてみました。
ラベルを5つ用意しました。本当はラベルの数は可変にしたかったのですが、
このような仕組みすることで、とてもシンプルに高機能家計簿を実現することができます。
続いて、列ごとに入力すべき値を制限していきます。
例えば日付の列は、日付しか入力できないようになっていると集計のミスが減らせます。
また、セルをダブルクリックすると日付が選択できるようになっていると、キーボード操作が減らせて楽になります。
日付の列を選択し、メニューの「データ」→「確認」を選択します。すると↑のようなダイアログが出てきます。
条件を日付にすることで、セルをダブルクリックしただけでカレンダーが出現し、日付を入力できるようになります。
続いてラベルもマウスで選べるようにしましょう。
ラベルのセルを選択し、先ほどと同様に「データ」→「確認」を選択します。
条件を「リスト範囲で指定」にし、「ラベルシート」のラベルを全て選択します。
これで、ラベルが選択できるようになりました。
「月」という列も用意しました。
この列は「=MONTH(日付の列)」となっていて、日付から"月"のみを抽出できるようにしています。
2013/11/02なら"11"という値が抽出できます。
これを用意しておくことで、月単位の集計、2ヶ月単位の集計が簡単にできるようになります。
合計を求めるセルにSUBTOTAL関数を使います。
=SUBTOTAL(109,$E$2:$E$10120)
金額の列を指定して、合計を求めます。
なぜSUM関数ではダメなのか。
それは、集計したいラベルだけを選択した際に、SUM関数ではそれ以外のラベルも集計されてしまうのです。
続いて、集計のための準備をします。
メニューの「データ」→「フィルタ」を選択します。
すると、ラベル1やラベル2の横に▼が出現します。
これは、表示させるデータを選択することができる機能です。
これで、集計したい「食費」と「デート代」などに絞って集計することができます。
これで家計簿作りが完了しました。
集計をしてみましょう。
各ラベルの横の▼をクリックすると、その列の特定のラベルのみに限定することができます。
例えば、「交際費」の「飲み代」を集計してみましょう。
先輩と飲み、新人歓迎会でお付き合いの飲み会をして、疲れたので同期の田中と愚痴を言い合う飲み会をした設定にしてみました。
ラベルを太郎(自分)と食費、交際費、お酒に絞ります。
すると合計値が変わります。
いかがでしょう。
自由度高く家計を分析して、賢い貯金をしてみては！？
よろしければこちらもご覧ください。
精算もラクラク！Google Driveのフォームとスプレッドシートを使って自動精算システムを作ろう！ - まだ中学生のブログ
