こんばんは。id:Sixeight です。
(Adventar では Twitter でログインしたので @tomohi_ro になっていた。)
これは Spreadsheets/Excel Advent Calendar 2018 の16日目の記事です。 昨日は id:hogehoge0919 さんの「関数初心者がIF関数をいっぱい使ってTwitter依存度を可視化してみた 」でした。 多くの関数の使い所が示されていてとても参考になりました。
まえがき
最近仕事でもちょくちょくGoogleスプレッドシートを使っているので、なんらかのべんりテクニックを紹介しようと思っていたのですが、他の方の使い方紹介記事を読んでいて、非常に興味深かったので、僕も家庭での使い方を紹介しようと思い直しました。我が家でのスプレッドシートの使いかたを紹介しながら、なぜそうしたかを書ければと思っています。
お金の管理
我が家でのお金の管理は、共通の口座を作り、そこに毎月決まった額をお互いが振り込むことにしています。必要なものはその口座から引き落とされるクレジットカードとその家族カードを作って、基本的にはカードを使うような運用をしています。しかし、日本ではまだ完全にクレジットカードだけでは買い物ができず、どうしても現金を使う必要があります。また、自分のカードと間違って家族カードを使ってしまった場合や、特別に必要なお金があった場合にどうするかという話題もあり、そういったお金の精算をGoogleフォームとGoogleスプレッドシートで管理するということをやっています。
基本的な考え方としては、毎月振り込む額を決めて、そこから使った現金の額を引いた額を毎月振り込むというものです。
投稿フォームを用意する
まず最初に作ったのは現金を使ったときに申請するためのフォームでした。
質問の内容はいろいろ考えた末に以下のようになっています。
- 「誰ですか」
- どちらが使ったのかをラジオボタンで選択するようにしました。
- 「いくら使いましたか」
- 使った額を記入します。運用が面倒になると嫌なのでどんぶりで入力していることもあります。
- 「何に使いましたか」
- 用途を記入します。あとで思い出す用なのでここも雑めに入力します。
- 「いつ使いましたか」
- 当日に入力できないこともあるので、日付も入力するようにしています。
- 「どこで使いましたか」
- お店の名前などを入れるようにしています。
- 「コメント」
- 最初は備考を書くつもりで作ったのですが、いまや感想や日記を入力していたりします。
集計シートを作る
Googleフォームとスプレッドシートに紐付けることができ、回答をシートに自動で追記できます。 この機能を使って、フォームで入力した内容をシートで集計して精算できるようにしていきます。
上記のように右上のシートのロゴっぽいアイコンをクリックして新規にシートを作成します。
このように一回答につき一行追記されます。生々しすぎて隠した結果意味がわからない図に。
月ごとの集計シートを作る
このままでは月ごとの集計が難しいので、回答一覧を一次データとして集計専用のシートを作成します。
こういう感じになりました。これはもちろんダミーです。毎月100万円ずつ振り込めて10万単位でお金を使える家庭になれるといいですね。
今月使った一覧ですが以下のようにして一次データからフィルタして取得します。
=IFERROR(FILTER('フォームの回答 1'!A2:M, 'フォームの回答 1'!A2:A>=$H$4, 'フォームの回答 1'!A2:A<$H$5), "")
非常に単純で、回答のシートから開始日と終了日の間に投稿されたものを取得して表示しているだけです。 FILTER 関数は結果が1件もないとエラーになるので IFERROR 関数で囲っています。
開始日と終了日が手入力になっているのは、正確に月初、月末で精算するのを諦めたからです。ずれ込む前提で作ることで精算が面倒になるのを避ける作戦です。
実はこの集計範囲はフォームから入力した「現金を使った日」ではなくて、フォームから投稿した日付であるタイムスタンプを使っています。 これは精算を終えてから、過去の日付を入力してしまうと精算に漏れてしまうので、ただしく精算するために入力した日付を使っているためです。
使った額を集計する
次にこの集計を作っていきます。こちらも非常に雑に作っていて簡単です。
最初に今月使った合計金額はこのように計算しています。
=SUMIF($B$8:$B, "花子", $C$8:$C)
第一引数が「使った人」の列、第三引数が「使った金額」の列を指定して、第二引数に指定した人名にマッチする場合のみ加算することで今月その人が使った合計金額を求めています。 (ちなみに実際は第二引数にもセルを指定していますが、わかりやすさのため固定値で記載しています。)
本当は、第一引数と第三引数に名前付き範囲を使って、 =SUMIF(使った人, "名前", 金額) のように書きたいのですが、終了行を指定せずに名前付き範囲を使えないので断念しているのですが、いい方法があったらこっそり教えてもらえると嬉しいです。
調整の理由を残したい
「今月の振込額」はここで計算した「合計」に「調整」を加えた額を、基本の振込額から引いて求めています。
上の例では、太郎の「調整」に ¥327,500 が入っていますが、一体何に30万円も使い込んだのでしょうか。
その時は覚えているかもしれませんが、半年後には確実に忘れています。
忘れるという問題を解決するために、非常に単純ですが効果的な方法に「メモ」があります。
セルを右クリックして メモを挿入 ですね。
なるほど、家族カードで30万も使ってしまったようです。
メモは単純ですが非常に有効で、仕事でも、例えばバーンアップチャートを書いていて全体の工数が変動したときに、「なぜ変動したのか」ということをメモで書いておくことであとから辿れるようにしています。
支払い済みか分かるようにする
さて、よく見ると花子の「支払状況」に 済 が入力されていて、全体が緑色になっています。
この 済 のところはなんだかプルダウンメニューになっていそうですがどうなっているのでしょうか。
これには「データの入力規則」を使っています。セルを右クリックして「データの入力規則」を選択すると以下のようなポップアップが開いて詳細を設定できます。
条件はいろいろ選べるのですが、今回は「リストを範囲で指定」を選び、「凡例」という別シートを作ってそこに選択できる内容を一列に列挙して、そこを指定しています。
そして 済 になっていると背景が緑になっていますが、こちらは「条件付き書式」を使って実現しています。
背景色をつけたい範囲を選択して、右クリックして「条件付き書式」を選択すると、右ペーンに設定画面が表示されます。 ここから、書式を反映させる範囲や、書式の設定を入力するのですが、肝は「セルの書式設定の条件」です。
今回はこのように「カスタム数式」を選び、=$E4="済" と入力しています。こうすることでE列が 済 である４行目以降の指定範囲内の行の書式を指定することができます。
以上で、今月の集計が完成しました。これを精算が完了するごとに新しいシートをコピーで作成して、毎月の集計をしています。
太郎はまだ振り込んでいなくて、今月は ¥1,198,000 振り込む必要があることが分かってべんりですね。
月に100万円も振り込めるような人間になりたかった人生でした。
おわりに
この記事では、我が家でのGoogleスプレッドシートを使った家計の管理について紹介しました。
僕は、データを入力する手間を軽減したり、あとでふりかえられるようになっていたり、ひと目見てどんなデータなのか分かるようになっているのが理想だと思っており、データとして正しいことに気を取られて、ふつうの人間にとって扱いにくいものになっているのは本末転倒なんじゃないかと思います。
出来るだけ機械に扱いやすいようにというのは理解できますが、うまくバランスを取ったシートを作っていきたいものですね。
明日は id:AirReader さんの記事です。尊敬するGoogleスプレッドシート使いの一人であるAirさんの記事、非常に楽しみですね。
