この記事には広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
我が家で使っている、エクセルを使った複式簿記の家計簿（エクセル複式家計簿）を作る方法を紹介します。
第三回は集計表を作っていきます。
エクセル複式家計簿では仕訳の集計にピボットテーブルを利用します。
「ピボットテーブル？」という方にも伝わるよう、スクリーンショットをたくさん用意しました。
ピボットテーブルって意外と使う機会がないんですよね。私は家計簿を機に使い方を学びました。
第一回：勘定科目リストをつくる
第二回：仕訳帳をつくる
第三回：集計表をつくる ←この記事
第四回：締め切りと繰り越し
第五回：損益計算書と貸借対照表をつくる
ミールキットは共働き世帯が使うべき令和の便利アイテム。食材・調味料・レシピがセットになって自宅に届くから、献立決めや買い物のストレスから一気に開放されます。
お得に試せるサービスもあるので、気になったらすぐにはじめてみてくださいね！
運営者イチオシのミールキット
スポンサーリンク
エクセル複式家計簿の集計表の全体像
まずはどんな集計表を作るのかについて、全体像を確認します。
この記事では下の画像のような集計表を作っていきます。
特徴を一言でいうと「ピボットテーブルを利用して月ごとに各科目の合計金額をまとめる」です。
だいたいの家計簿は月で締めていると思います。
この集計表を使えば、月ごとの損益計算書・貸借対照をつくることができるようになりますよ。
それでは具体的な話に移っていきましょう！
ピボットテーブルとは
ピボットテーブルってなに？
ここで一度、ピボットテーブルについて簡潔にご紹介します。
ピボットテーブルは集計ツール
ピボットテーブルはデータの集計を楽にしてくれる機能です。
膨大なデータを集計して様々な側面からデータを見せてくれます。
家計簿であれば、
- 月次の支出、年次の支出
- 項目ごとの支出、グループごとの支出
- 支出の合計、支出の平均
などなど。どんな情報を得たいかによって集計の切り口を変えることができる便利なツールです。
データをインフォメーションに昇華させるツールですね！
ピボットテーブルの注意点
ピボットテーブルを扱う上で、絶対に守らなければならないルールがあります。
そのルールとは「元になるデータがリスト形式であること」です。
リスト形式といってもよくわからないので、ダメな例をいくつか挙げました。
ピボットテーブルの元になる仕訳帳では下記のこと、厳禁ですよ！
- セルの結合
- 空白セル
- 表記ゆれ
- 誤入力
これらが元のデータにあるとうまく集計してくれません。
ピボットテーブルにとって集計しやすいデータリストを作ってあげることが大切です。
第二回で解説した仕訳帳は、誤入力を未然に防ぐ仕組みを取り入れています。
・テーブル化しているのでセルの結合が禁止されている
・入力規則により、表記ゆれの可能性がない
★ピボットテーブルの基本をより詳しく知りたい方はこちらの書籍がオススメ！
ポチップ
スポンサーリンク
エクセル複式家計簿の集計表を作る
ピボットテーブルの概要はご理解いただけましたか？
それでは具体的な集計表の作り方を見ていきましょう。
集計表の作り方
集計表にピボットテーブルを作成したいので、”集計表”ワークシートを選びます。
上のタブからピボットテーブルを選択します。
分析するデータは”仕訳帳”ワークシートの「shiwake」と名付けたテーブルになります。
別の名前をつけている場合はそれに合わせてくださいね。
ピボットテーブルの作成場所は「集計表!$A$1」でいいと思います。（”集計表”ワークシートのA1セルってことです）
できたらOKを押します。
OKを押すと、ピボットテーブルの素ができます。
右のフィールドリストを使って行や列の設定をおこないます。
行（縦軸）には科目類を入れていきます。
ドラッグ&ドロップで動かせます。
このとき、上から[グループ＞科目CD＞科目]の順に並べるとキレイにまとまります。
列（横軸）には時系列を入れます。
上から順に[年＞日付]の順に並べましょう。
表の中には合計金額を表示させたいので、金額を入れます。
デフォルトで合計になるはずです。
これにて集計表の素が完成しました！
・・・なんか見づらくない？
今のままでは表を眺めても「なんのこっちゃ？」なので、体裁を整えていきましょう。
ピボットテーブルの見た目を整える
ピボットテーブルの設定をいじって、見た目をシンプルにします。
小計が多すぎて分かりにくいので、科目CDと年の小計は消してしまいましょう。
ピボットテーブルのフィールドから「科目CD」のフィールドの設定を開きます。
小計を「なし」にします。
これで科目CDにおける小計が表示されなくなりました。
同様に「年」の小計もいらないと思うので、消しましょう。
行の総計（一番右の列）も特に必要ないので消しておきます。
[ピボットテーブル分析]タブからオプションを開き、[集計とフィルター]の「行の総計を表示する」のチェックを外します。
列の総計（一番下の行）は残しておきましょう。
仕訳が正しく記入できているか確認する目印になります。
横軸が9月12日・9月13日・・・と一日単位で分かれています。
細かくしすぎても解釈が難しくなるだけなので、月単位でまとめちゃいましょう。
どこか任意の日付のセルをクリックして（アクティブなフィールドを日付にして）、[ピボットテーブル分析]タブから「グループの選択」をクリックします。
単位を「月」にします。
これで9月がひとまとまりになりました。
行（縦軸）が複数行にわたって表示されると見にくいので、1行にきゅっとまとめます。
[デザイン]タブからレポートのレイアウトを開き、「表形式で表示」を選択します。
これで科目が隙間なく並ぶようになりました。
金額のプラスマイナスを認識しやすくするため、マイナスは赤字で表示するようにします。
ピボットテーブルのフィールドから「合計/金額」の右の▼をクリックし、「値フィールドの設定」を開きます。
左下の「表示形式」を開きます。
通貨を選択し、記号＝なし、負の数の表示形式-1,234を選んで、OKを押します。
これでプラスマイナスの見分けがつきやすくなりました。
仕訳が増えてくると、科目CDの順番がごちゃごちゃになることがあります。
数字の小さい順に並ぶように設定しておきましょう。
ピボットテーブルの「科目CD」の▼をクリックし、「昇順」を選びます。
これでバッチリ並ぶようになりました。
ピボットテーブルは自由に形を変えることができます。まずは我が家の家計簿を参考にしていただき、その後、自分好みの形にアレンジしてみてくださいね。
ピボットテーブルの使い方
最後にピボットテーブルの使い方をお伝えします。
やることは以下の2つだけです。
体裁を整えてしまえば、ピボットテーブルを操作することはほとんどありません。
- 仕訳を入力したら更新する
- 列の総計がゼロになっていることを確認する
仕訳を入力したら更新する
ひとつは仕訳を入力したらピボットテーブルを更新すること。
日々、”仕訳帳”ワークシートに取引を入力することになりますが、入力後、都度ピボットテーブルを更新しなければなりません。
- ピボットテーブル上で右クリックする
- [ピボットテーブル分析]タブから更新する
の2通りのやり方があります。
お忘れなく！
列の総計がゼロになっていることを確認する
ピボットテーブルの更新後、必ず一番下の総計をチェックしましょう。
ここがゼロになっていない場合、仕訳帳に入力ミスがあると分かります。
この例では、仕訳帳で入力する数値に誤りがあったようです（-1500と入力すべきところが-150になっていた）。
複式簿記は借方と貸方でプラマイゼロになるのが原則ですので、常にチェックするようにしてくださいね。
以上で、エクセルで複式簿記の家計簿を作る方法の第三回：「集計表の作り方」を終わります。
お疲れさまでした！
第一回：勘定科目リストをつくる
第二回：仕訳帳をつくる
第三回：集計表をつくる ←この記事
第四回：締め切りと繰り越し
第五回：損益計算書と貸借対照表をつくる
スポンサーリンク
