税理士は自分のExcel家計簿をどう運用しているか

by

長年、Excelで家計簿をつけています。

フリーランス税理士として、いくら利益をあげればいいのかは、結局のところ、生活費がいくらかによるからです。

また、収入としては事業以外のものもあり、トータルで財政状況を把握する目的でつけています。

いまExcel家計簿をどう運用しているか書いてみました。

家計簿については、いくつか記事を書いています。たとえば：freee会計＋Excel家計簿に入門する

経費にしないカフェ代の集計（これは交通系ICで支払）

決済手段別にシートを入力

現金、SUICA、信金、銀行、クレジットカードなどの決済手段別にワークシートを作成します。

日付, 金額, 現金残高, 取引先, 内容, 借方, 貸方　の列を作成します。（以下、共通フォーマットといいます）

現金のシート（共通フォーマット）

現金の場合は、日付順にはこだわりません。最終的にピボットテーブルで集計するので。
これは、会計ソフトへのインポート用のExcel入力と同じ考えかたです。

そのため、一時的に現金マイナス残高が出ることもありますが、時系列に入力していないことが原因なので、気にしていません。

ただ、その日の残高と、現金を数えた金額が一致していることは確認しています。
プライベートの現金から払った経理にもれ・金額誤りがないことをチェックすることにもつながります。

立替経費を役員借入金で処理。「後からレシートが出てきた」を防ぐには – 税理士 木村将秀のブログ

また、金額列は1列にし、入金は負の金額で、出金は正の金額で入力しています。
出金のほうが件数が多いので、入力しやすいようにするためです。

入金・出金の列を分けてしまうと、データとして処理が面倒にもなります。
会計ソフト用のインポートCSV・Excelを使うときも、この考え方を応用できます。

インポートの記事もいくつか書いています：経理の事務が進まないときの対策（弥生会計CSVインポートの例も）

経費がある場合は、取引先を入力し、借方勘定科目を経費とします。
それ以外のプライベートな支出は、適切な勘定科目を設定しています。

勘定科目名の設定のコツとしては、1文字目をすべて異なる音にすることです。
Excel入力の際、2タイプでオートコンプリートが働き、入力が楽になるからです。

SUICA、銀行、カードなど、CSVデータが取れるものは、それを活用しています。

計算式を使って、最終的に、各シートに、先ほどの列を持つ共通フォーマットのデータを作成します。

事業用の預金口座は、freee会計の試算表から、その月の入金合計・出金合計だけを入れたシートを作ります。
明細は、freeeを見ればいいので。
また、事業用カードは、引落し額だけ事業用口座から分かればいいので、家計簿には入れません。

家計簿は、ほぼ、現金主義で作っているということです。（プライベートのカードだけは発生主義）

税理士事務所メニュー
小冊子『横浜ではたらくフリーランスの守りの種』無料送付
単発相談／単発代行
確定申告
税務顧問
NPO法人等支援
記事執筆等のご依頼

マクロですべてのシートを1シートに集約

このように、すべての決済手段で共通フォーマットを作れば、あとは、すべてのシートを1枚のシートに集めるだけです。

Microsoft Accessをやっていた方なら、ユニオン・クエリを作るイメージです。

VBAコードはChatGPTに書いてもらいました。こんな感じです。

Sub ProcessSheets()
    Dim wsLeftMost As Worksheet
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim pasteRow As Long
    Dim i As Integer

    ' 左端のシートを取得
    Set wsLeftMost = ThisWorkbook.Sheets(1)

    ' 左端のシートの2行目以降を削除
    With wsLeftMost
        If .Rows.Count > 1 Then
            .Rows("2:" & .Rows.Count).Delete
        End If
    End With

    ' 貼り付け開始行
    pasteRow = 2

    ' 左から2番目のシートから8番目のシートまでループ
    For i = 2 To 8
        If i <= ThisWorkbook.Sheets.Count Then
            Set ws = ThisWorkbook.Sheets(i)

            ' A:G列の最終行を取得
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            ' データをコピーして左端のシートに貼付け (2行目以降)
            If lastRow >= 2 Then
                ws.Range("A2:G" & lastRow).Copy
                wsLeftMost.Cells(pasteRow, 1).PasteSpecial Paste:=xlPasteValues
                ' 貼り付け位置を更新
                pasteRow = wsLeftMost.Cells(wsLeftMost.Rows.Count, 1).End(xlUp).Row + 1
            End If
        End If
    Next i

    ' クリップボードをクリア
    Application.CutCopyMode = False

End Sub

いま、決済手段が7つあるので、7つのシートからデータを持ってきています。

ピボットテーブルで月別・勘定科目別に集計

共通フォーマットのデータがあれば、ピボットテーブルで集計することが可能になります。

マクロで1つのシートにまとめたら、それをもとに、ピボットテーブルでつくった月別推移表を更新すると、入力した月までの家計簿の集計結果がまとまります。

売上入金、売上以外の入金（児童手当など）、経費の出金と、家計簿科目を集約したことになります。

この際、入金はすべてマイナスの金額で入れているので、「総計」がマイナスだと、黒字ということになります。
直感的ではないですが、自分が分かればいいので、ステップを減らすことを優先して、こうしています。

推移表には、スパークライン※1で月々の増減傾向を把握し、月ごとには条件付き書式→データバー※2を表示して、金額の多い勘定科目がわかるようにしています。

※1 Alt→N→S→O
※2 Alt→H→L→D→Enter

お見せできる範囲で……（金額は白色にして見えなくしています）

いつもの金額と違う！（大きすぎる、少なすぎる、ダブっている）と感じたら、ピボットテーブル内のセルをクリックすれば、詳細が新しいシートに開かれるので、確認ができます。
だいたい、勘定科目の設定ミスが多いのですが。
確認したら、Ctrl+Zで詳細シートを閉じてしまいます。

この金額のチェックの見方は、会社の会計ソフトでの月次試算表（推移表）の見方と同じです！

合わせて、各月末の、現預金の残高も入れておき、年始からの純資産の増減推移を入力しています。

ここで、理論上は、収支の総計の累計額と、純資産の増減額がほぼ一致するはずなのですが、カードや途中で処理方法を変えた関係で、数万円は差額が出ます。
まあ、家計簿ですし、許容範囲かなと。

この推移や、予算との対比を見て、収支の改善をしています（最近はお酒を買わなくなりました）。
家計も事業も、お金の考え方は同じです。

小冊子『横浜ではたらくフリーランスの守りの種』無料送付
YouTubeチャンネル：守備力を上げる税理士 視聴覚委員会 放送班
仕事のご依頼メニューの一覧

近況報告

お客様や、お仕事をお願いしている先とのメールやりとり。ゆうパックでお預かりしていた資料を返却するなど。

1日1新：Flexible Tripod TOUGH