ピボットテーブル×エクセル家計簿に7年ベタ惚れの僕がその魅力と作り方を解説

ブログタイトルにもしちゃうほど家計簿が大好きです。

結婚してからの7年間ずっと、自作したエクセル家計簿で家計管理してきました。その甲斐あって、病気とか転職とか紆余曲折あった我家ですが、少しずつ貯蓄も増えてきました。

3日坊主の代名詞的な存在でもある「家計簿」ですが、もし我家もエクセル家計簿に出会えてなかったら、確実に家計簿難民になっていたでしょう。確実に。

7年間も継続してこれたのは、エクセルに標準で備わっている「ピボットテーブル機能」を使って、テンプレートを自作できたことが大きな勝因です。

 

TORACHAN
ピボットテーブルってなに?なんでピボットテーブルが家計簿に役立つの?

そう思う方も多いと思います。

ということで今日は、ピボットテーブル機能を使ったエクセル家計簿の魅力とその作り方についてご紹介してみようと思います。

エクセルのピボットテーブル機能とは?

我家はMicrosoft Excel 2007を使っています。もしかすると他Verと仕様や操作方法が異なることがあるかもしれませんがご了承くださいませ。

ある程度エクセル操作に慣れている方でも、ピボットテーブルと聞いてピンとこない方も多いかもしれません。

すっごく便利な機能なのですが、敷居の高いイメージも手伝って、あまり認知されていないのかもしれません。

ピボットテーブル機能というのは、エクセルに標準装備されている機能で、かんたんにいうと「数字データの合計値や平均値などを自動集計して一覧表を作成してくれる機能」です。

表作成のための参照データ範囲を設定するなど、最初にテンプレートを作ってしまえば、、、

あとはどんなに数字データがたくさんあっても、「更新」をクリックするだけで一瞬で計算&表更新してくれるという、素晴らしい機能なのです・・・(○ˊᵕˋ○)ウットリ

 

例をあげてみましょう!

たとえばくだもの屋さんが、日々の売上記録をエクセルでこんなふうにまとめていたとします↓

 

んでもって、ピボットテーブル機能を使えば、こんな売上一覧表が1分もあればできてしまいます↓

(※我家のエクセル家計簿を作るにはもう少し時間がかかります)

 

小難しい関数や数式は一切使っていません。

それでいて金額や数量などのデータを一瞬で自動集計してくれちゃってます。

データ参照範囲やテーブルデザインなどを最初に決めてしまえば、こんな一覧表を一瞬で作れてしまうのがピボットテーブルの凄いトコロといえます。

また日々の売上記録が追加されていったとしても、更新をクリックするだけで、その追加分が一瞬で一覧表に反映されちゃうのも特筆できる点です。

 

ピボットテーブル機能を使ってエクセル家計簿自作をおすすめしたい理由

それではもっと具体的に書いていきましょう!

なぜピボットテーブル機能がエクセル家計簿とこんな相性がいいのか、ぼくの実体験をもとにその理由を書いてみますね。

膨大な家計データを一瞬で一覧表に!更新もワンクリック

エクセル家計簿にピボットテーブルを使うメリットは、なんといってもこれでしょう。

このおかげで家計管理作業が格段にラクになったのは言うまでもありません。

ピボットテーブルを使うと、こんな家計一覧表がかんたんに作れます(ウチでは年間収支表って呼んでます)↓

 

テンプレート作成に少し時間はかかりますが、一度つくってしまえば、あとは自動でデータ集計して表作成してくれます。

ちなみにこの年間収支表をつくるための元データ(参照データ)がこちらになります↓

(我家では記録シートと呼んでいます)

 

この記録シートに日々の家計データを入力していきます。

ちなみに追加したデータは、年間収支表上で「更新」をクリックするだけで、自動的に反映させることができます。

 我家のエクセル家計簿で使うのは、基本的にこの2枚のシートだけです。あとは必要に応じて、資産管理シートや予算管理シートを使います。

 

とにかくシンプルな入力作業

先ほどご紹介した記録シートをみての通り、家計データの入力は非常にシンプルです。

収入や支出などのすべての家計データを、1行に1データずつ、下方向にひたすら追加し続けるだけです。

月や年をまたいだとしても、ずうーーーっと下方向にデータを追加していってOKです。(年をまたいだ時の年間収支表への反映方法についてはこちらをどうぞ)

【便利ワザ】費目や店名が同じデータは、行ごとコピペすれば劇的に入力作業が楽になります。あとは金額や日付など、必要な部分だけ変更すればOK。これはエクセル家計簿の強みですね。

 

ちなみに、エクセルシート1枚あたり約100万行あるので(2007の場合)、年間の家計データが2000だとしても(←我家の2018年分のデータ数がこのくらいだった)、単純計算で、、、

100万 ÷ 2000 = 5000年分

と、十分すぎるほどの家計データを入力できるので、一般的な家庭なら記録シートを何枚も作る必要はまずないでしょう。

もちろん複数シートに分けても構いませんが、そのたびに年間収支表(ピボットテーブル)の参照データ範囲をいちいち設定しなくちゃならないのは正直面倒くさいです。また記録シート上で過去の家計データをキーワード検索したい時(←我家がよくやります)、1枚のシートにデータまとめておいた方が都合が良いです。

ということで我家は、一生分の家計データを、1枚の記録シートに入力し続けていくつもりです。

 

マクロや関数は一切不要

我家がこのテンプレートを設計しはじめた当初、他の人が作成したテンプレートも、いくつかダウンロードして使ってみたことがあります。

しかし以下の理由により、自分の性分には合わないなと感じ、参考にするのをやめました↓

  1. マクロや関数を組んであるテンプレートが多く、エクセル初心者の自分にはハードルが高すぎる
  2. 入力方法やデザインが凝りすぎていて、自作・習得・修正が大変

 

特に①は、エクセル家計簿をはじめてみたいと思っているこのブログの読者さんにとっても敷居を高めてしまうことになるので、なんとか解消したい部分でした。

マクロを仕込んだエクセルファイルの場合、サポートが終了してしまったエクセルバージョンで使うと、ウイルス感染リスクが高まってしまうという懸念もあります

 

で、その解決策としてピボットテーブル機能を使うことにしました。

ピボットテーブルは「膨大なデータを自動集計して瞬時に一覧表化する」という機能に特化パッケージングしており、エクセル家計簿とはすこぶる相性がいいです。

マクロや関数は必要なし、入力作業は単純で、表デザインの骨格は自動で作成されます。あとは色付けやフォントサイズなど細かい部分を自分好みに変更すればOKです。

マクロや関数は使っていませんが、記録シートの貯蓄行にかんたんな引き算の数式を入れてます(=収入合計-支出合計)

もちろん、ピボットテーブルもその仕組みや作成方法を理解するのに少し時間はかかります。

人によって合う合わないがあると思いますが、個人的には、それ以上の恩恵を感じてるので、ピボットテーブルを使わない理由がないというのが正直な気持ちです。

 

費目(項目)をいくらでも設定できる

費目というのはこの部分ですね↓

家計簿の費目・項目の分類はこうしてます。我家の場合。

2016.04.05

我家の場合、費目は「収入」「支出」「貯蓄」と大きく3つに分けており(大カテゴリ)、それをさらにそれぞれ細分化しています(中⇒小カテゴリ)。

ピボットテーブル×エクセル家計簿は、費目名を自分で自由に設定できる点もメリットです(追加・修正・削除が自由自在)。

また階層についても我家は3階層にしていますが、これを自由に変更することだってできます。極端なハナシ、10階層も100階層だって可能です。

そこまで多階層にする必要はなくとも、たとえば、、、

TORACHAN
ペット代という費目を追加して、さらにその下にエサ代・おもちゃ代・さんぽ用のお洋服代を追加するにゃっ!!

というようなことが簡単にできてしまうわけです。

 

デザインやレイアウトを自分好みに変更できる

ピボットテーブル機能を使った年間収支表は、そのデザインやレイアウトを自由に設定することができます。

通常のエクセルで行う変更と同じように、たとえばフォントやそのサイズや色、セル背景色などはもちろんのこと、費目の位置変更も簡単できます↓

(光熱水費をいちばん上にもっていきたい・・・!)

 

↓位置変更

 

年間収支表の年(年度)をかんたんに切り替えられる

この機能の設定方法は「やさしいエクセル家計簿のはじめ方」で解説しています
TORACHAN
年(年度)が変わったら、新たにテンプレートを作りなおさないといけないの??

そう思う方もいるかもしれませんがその心配は無用です。

テンプレートを作る作業は最初の1回だけ。以降は何年たっても同じテンプレートを使い続けることができます。

年が変わっても、記録シートはずうーーっと下方向にデータを追加していけばOKですし、年間収支表は年のフィルターチェックを入れたり外したりすることで、年を切り替えることができます。

 

予算管理表もピボットテーブルで作成できる

この機能の設定方法は「やさしいエクセル家計簿のはじめ方」で解説しています

その月ごとに予算を設定して、細かく家計管理したい方もいると思います。

そんな予算管理表も、ピボットテーブルを使えば実現できちゃいます。たとえばこんなの↓

 

各費目ごとに予算設定して「予算-実績=残額」を一目で把握することができます。

もちろん年間収支表と同じように、テンプレートを作るのは最初の1回だけでOKですし、更新もワンクリックで可能です。

さらに細かく月ごとに予算管理したい人向けに、こんなレイアウトも実現可能です↓(※スペースの関係上、1~3月のみの表示になっていますが12か月分の表示が可能です)

 

ピボットテーブルを使ったエクセル家計簿の作り方・自作方法

ピボットテーブルによるエクセル家計簿テンプレートの自作方法については、こちらの記事に詳しくまとめていますので参考にしてみてくださいませ(。ᵕᴗᵕ。)”

エクセル家計簿は簡単に自作できる!その作り方を丁寧に解説します

2016.06.04

 

また有料にはなりますが、完成済みテンプレートやトラブル対処マニュアルなど、我家の7年分のノウハウをすべてまとめたコンテンツもご用意しています↓

徹底的に習得してみたい方向けのコンテンツです(‘ω’)ノ

『やさしいエクセル家計簿のはじめ方』好評販売中!テンプレート【完全ver】や音声動画マニュアルなどが付いてます

2017.06.20

 

まとめ。ピボットテーブル×エクセル家計簿はこんな人におすすめ!

エクセル家計簿はこんな人に向いている
  • エクセルを仕事やプライベートで使ったことがある(基本操作が分かっている)
  • デザインや費目などをカスタムして自分だけのオリジナルの家計簿を作りたい
  • 無料アプリや家計簿ソフトなどは便利だがサービス終了リスクがあって心配だ
  • エクセルを使って正確に&ラクに&効率的に&省力的に家計管理をしたい
  • 難しい関数や数式は使わずにテンプレートを作りたい

今は便利な時代でして、マネーフォワードなどの無料アプリを使えば、クレジットカード利用データを自動吸い上げできるなど、ひと昔までは考えられないようなことができちゃいます。

実は我家もマネーフォワードを使っています。ただその使い方は限定的で、資産管理シートをつくるときの口座管理ツールとして補助的に使っています。

マネーフォワードが家計管理ツールとして素晴らしいのは間違いないのですが、たとえば現金払いした支出などは結局手打ちしなければならないし、なにより自分好みの仕様・デザインにできないので、ぼくにはやはり向いてないかな、と。

 

エクセル家計簿自体は目新しいものではなくて、昔からたくさんの人たちが自分だけのオリジナルテンプレートを自作して使ってきました。

僕も以前働いていた職場でデータ集計していた時にピボットテーブルにはじめて触ったことがキッカケで、エクセル家計簿自作という着想を得ました。

エクセル家計簿をこれから自作してみたい方は、ピボットテーブルをぜひ一活用してみてください。

きっとその操作性・快適性に感動してもらえる、と言っても言い過ぎじゃないと思っているんです。

 

ピボットテーブル、この機能をエクセル家計簿で使わないなんてもったいない!



 

SOHTARO
散財・病気・転職と、人生コスパ最悪夫の資産形成術をフォローしてみませんか?失敗だらけのポンコツだからこそ伝えられる何かがあるはず!


『やさしいエクセル家計簿のはじめ方』好評販売中!

 

 

「最も使いやすい家計簿をつくろう!」というコンセプトのもとに自作した

エクセル家計簿の完全マニュアルです。

 

エクセル家計簿歴7年分の我家オリジナルノウハウをすべて詰め込みました!

 

無料アプリや有料ソフトはイマイチしっくりこなかった方も、是非お試しくださいませ(○ˊᵕˋ○)♪


コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です