#BitchVogue JAPAN

For all my bitches...

ゆずひめ式！複式簿記で作る家計簿のエクセル作成手順

経理 エクセル VBA

　家計簿といえば、収入と支出の金額を記録していく単式簿記が主流です。しかし、複式簿記で記録することで、自分の懐事情を更に多面的に見える化することができます。今回は、それを記録していく用の家計簿エクセルの作り方を紹介します。

　なお、色分けやフォントはご自身のお好みでカスタマイズしてください！

マスタシートの作成

　まず、なんといっても大元となるマスタがなければ意味がありません。この段落ではマスタシートを作成していきます。

①仕訳入力部分の作成

  1. A1セルに「yyyy年m月度」と入力します。
  2. E1セルとH1セルに「合計」と入力します。
  3. D2セルに関数「=SUBTOTAL(9,E:E)」を入力します。
  4. G2セルに関数「=SUBTOTAL(9,H:H)」を入力します。
  5. A3～J4セルを下図の通りに入力します。適宜セルを結合してください。
  6. A5セルに関数「=IF(J5="✔",A4,A4+1)」を入力し、任意にオートフィルします。
  7. H5セルに関数「=E5」を入力し、任意にオートフィルします。

②集計部分の作成

  1. N1セル・R1セルに「合計」と入力します。
  2. V1セルに「差額」と入力します。
  3. M2セルに関数「=SUBTOTAL(9,N:N)」を入力します。
  4. Q2セルに関数「=SUBTOTAL(9,R:R)」を入力します。
  5. V2セルに関数「=M2-Q2」を入力します。
  6. K3～Z4セルを下図の通りに入力します。適宜セルを結合してください。
  7. K列・O列に勘定科目の名称を入力していきます。都度増えていきます。
  8. S列に取引先の名称を入力していきます。都度増えていきます。なお、後から楽になるように「負債＋純資産＋収益」に該当する取引先を上部にまとめ、「資産＋費用」に該当する取引先を下部にまとめておきます。
  9. L5セルに関数「=SUMIF($C:$C,K5,$E:$E)」を入力します。
  10. M5セルに関数「=SUMIF($F:$F,K5,$H:$H)」を入力します。
  11. N5セルに関数「=L5-M5」を入力します。
  12. P5セルに関数「=SUMIF($F:$F,O5,$H:$H)」を入力します。
  13. Q5セルに関数「=SUMIF($C:$C,O5,$E:$E)」を入力します。
  14. R5セルに関数「=P5-Q5」を入力します。
  15. T5セルに関数「=SUMIF($G:$G,S5,$H:$H)」を入力します。
  16. U5セルに関数「=SUMIF($D:$D,S5,$E:$E)」を入力します。
  17. V5セルに関数「=T5-U5」を入力します。
  18. L5～V5セルを選択し、任意にオートフィルします。
  19. T～U列については、「資産＋費用」に該当する取引先のゾーンに入ったところから、関数を入れ替えます。T列が「=SUMIF($G:$G,S●,$H:$H)」でU列が「=SUMIF($D:$D,S●,$E:$E)」となります。
  20. W列に勘定科目をすべて入れ、X列にその勘定科目の注意事項を入力します。
  21. Y5セルに関数「=S5」を入力してオートフィル後、Z列にその取引先の注意事項を入力します。これで仕訳入力の際に迷わないようになります。

③固定とフィルター

  1. C5セルを選択し、上部の「表示」タブ→「ウィンドウ枠の固定」を押します。
  2. 4行目を行選択し、Ctrl + Shift + Lを押してフィルターを表示させます。

コピー用シートの作成

　マスタシートができたら、そのマスタを反映させたシートを作成し、そのシートをコピーして毎月のシートを作成していきます。

  1. マスタシートの上で右クリックし「移動またはコピー」を選択し、(末尾へ移動)をクリックして「コピーを作成する」にチェックを入れOKを押します。
  2. シート名を「コピー用」などわかりやすくしておきます。
  3. K5セルに関数「=マスタ!K5」を入力し、オートフィルします。O列・S列・W列～Z列も同様にマスタシートの同じセルをイコールで結んでオートフィルします。

月度シートの作成と入力

  1. コピー用シートを作成した手順と同様に、コピー用シートをコピーして月度シートにします。シート名は「yyyymm」とし、4月始まりとします。よって、2023年度の場合は「202304」となります。それを「202403」シートまで1年分作成してください。
  2. A1セルが「yyyy年m月度」となっているので「2023年4月度」等に変更します。
  3. 資産・負債・純資産の科目(BS科目)について、前年度からの繰越仕訳を4月1日付で入力していきます。相手科目は「繰越金」を使用し、繰越金は収益の科目とします。繰越仕訳をすべて同じ伝票番号で処理するために、J列の2行目以降は「✔」を入れます。これによってA列の伝票番号がすべて「1」になり、後からフィルターで抽出する際に便利です。なお、「前期純利益」は初めて家計簿を作成する場合は「資産科目の合計金額－負債科目の合計金額」でOKです。

  4. 日々の仕訳を入力していきます。

年度シートの作成

  1. 月度シートの作成と同様の手順で「yyyy年度」というシートを作成します。
  2. A1セルは「yyyy年度」と入力します。
  3. 毎月の仕訳入力が完了したあと、その月度の仕訳をすべてこちらの年度シートにコピペして累積させていきます。なお、繰越仕訳については4月度のみ貼り付けて、5月度以降は繰越仕訳を除いた伝票番号2以降の仕訳を貼り付けます。しかしながら、期の途中から（例えば8月から）家計簿をつけはじめた場合は、8月度のみ繰越仕訳ごと貼り付け、9月以降は繰越仕訳以外を貼り付けとなります。
  4. 3月度の仕訳まで貼り付け終わり、yyyy03シートとyyyy年度シートの資産・負債・純資産科目の残高がすべて一致していたら、抜け漏れがないこととなります。

勘定科目推移表シートの作成

  1. 「勘定科目推移表」というシートを新規作成します。
  2. A1セルは「勘定科目推移表」と入力します。
  3. C1～N1セルに月度シート名とまったく同じ名前を入力します。
  4. O1セルに「合計」と入力し、P1セルに「セル」と入力します。
  5. B2セル以降に資産・費用・負債・純資産・収益の順で勘定科目名をコピペしていきます。また、A2セル以降でそれぞれのグループにまとめます。
  6. P列には、B列にコピペした科目の残高が月度シートのどのセルにあるかを指定します。下図で言うと、受取利息の残高はR16セルに表示されているので「R16」としています。ただし、頭に必ず「!」をつけて「!R16」という表記にしておきます。
  7. C2セルに関数「=INDIRECT(C$1&$P2)」を入力し、N2セルまでオートフィル後、勘定科目がある最後の行までオートフィルします。すると、その月度のその科目の残高が反映されます。これで異常値がないかを確認できます。
  8. お好みで、下図の62～67行目の部分のように、それぞれの合計金額をSUM関数で算出し、差額を確認することもできます。

取引先推移表シートの作成

  1. 「取引先推移表」というシートを作成します。
  2. A1セルは「取引先推移表」と入力します。
  3. 「勘定科目推移表」シートの3～4と同様の手順を行います。
  4. B2セル以降にすべての取引先をコピペしていきます。マスタシート作成の段階で「負債・純資産・収益」の次に「資産・費用」と分けていたので、A列にはその取引先のグループを入力します。
  5. 「勘定科目推移表」シートの手順6～7と同様の手順で関数を展開します。これで異常値がないかを確認できます。

貸借対照表の作成

  1. 「貸借対照表」というシートを作成します。
  2. A1セルに関数「=E1&"貸借対照表"」を入力します。
  3. E1セルに貸借対照表を作りたいシート名を入力します。月ごとに見たい場合は「202304」など、年全体を見たい場合は「2023年度」といった感じです。
  4. A2セルに「資産の部」、C2セルに「負債の部」と入力します。
  5. A3セル・C3セル以降に資産・負債の勘定科目をコピペします。
  6. 負債科目をコピペしたら、その直下に「純資産の部」と入力します。純資産の科目は「前期純利益」「当期純利益」の2つとします。
  7. B3セルに関数「=VLOOKUP(A3,INDIRECT($E$1&"!$K:$N"),4,0)」を入力し、オートフィルします。
  8. D3セルに関数「=VLOOKUP(C3,INDIRECT($E$1&"!$O:$R"),4,0)」を入力し、「前期純利益」までオートフィルします。
  9. 最下部に合計欄を設け、SUM関数で合計金額を表示させます。
  10. 「当期純利益」の金額には「資産の部の合計金額から負債の部と前期純利益の合計金額を引いた金額」が表示されるように関数を組みます。例えば合計欄が18行目にあり、前期純利益が8行目にある場合は「=B18-SUM(D3:D8)」となります。当期純利益がプラスの金額だったら黒字、マイナス金額だったら赤字を意味します。
  11. C列の合計の下に「差額」と入力し、D列の合計金額の下に「資産の合計ー負債・純資産の合計」の関数を入れます。合計欄が18行目にある場合は「=B18-D18」となります。ここの計算結果は必ず「0円」になります。ならない場合、何かがおかしいということになります。

損益計算書の作成

  1. 「損益計算書」というシートを作成します。
  2. A1セルに関数「=E1&"損益計算書"」を入力します。
  3. E1セルに損益計算書を作りたいシート名を入力します。月ごとに見たい場合は「202304」など、年全体を見たい場合は「2023年度」といった感じです。
  4. A2セルに「収益の部」、C2セルに「費用の部」と入力します。
  5. A3セル・C3セル以降に収益・費用の勘定科目をコピペします。
  6. B3セルに関数「=VLOOKUP(A3,INDIRECT($E$1&"!$O:$R"),4,0)」を入力し、オートフィルします。
  7. D3セルに関数「=VLOOKUP(C3,INDIRECT($E$1&"!$K:$N"),4,0)」を入力し、オートフィルします。
  8. 最下部に合計欄を設け、SUM関数で合計金額を表示させます。
  9. C列の合計の下に「当期純利益」と入力し、D列の合計金額の下に「収益の合計ー費用の合計」の関数を入れます。合計欄が29行目にある場合は「=B29-D29」となります。ここの計算結果は必ず貸借対照表の「当期純利益」の金額と一致します。ならない場合、何かがおかしいということになります。

取引先別勘定科目表の作成（VBAを使用します）

　ここまで大変お疲れさまでした。このシートについては、やってみたい方だけで大丈夫です。勘定科目ごとに取引先の残高をVBAを用いて表示させることを目的としています。勘定科目推移表と取引先推移表をクロスでチェックする際に使用します。

　例えば娯楽費を複数の取引先に支払っている場合、取引先別に娯楽費の支払額がわかるシートです。娯楽費をA社とB社に支払っているとして、A社に計上されているべき娯楽費がC社に計上されている、B社に支払っている金額と計上されている金額が相違しているなど、さまざまなチェックが可能となります。

  1. 「取引先別勘定科目表」というシートを作成します。
  2. A1セルに「取引先別勘定科目表」と入力します。
  3. 「マクロ」というシートを作成します。
  4. 「yyyy年度」シートが、左のシートから数えて15番目に存在することを確認します。マスタ→コピー用→yyyy04～yyyy03→yyyy年度の順番が正しい順番です。
  5. ファイル→その他→オプション→リボンのユーザー設定から、下図の通り「開発」にチェックを入れてOKを押下します。

  6. タブに「開発」が出現していることを確認します。

  7. 挿入を押下し、左上の□を選択します。

  8. カーソルが十字になるので、ボタンを置きたい場所をドラッグして選択します。おそらく「ボタン1_Click」のようなポップアップが出るので、OKを押します。出来上がったボタンが「ボタン1」となっているので、任意の名前に変更します。私は「取引先別勘定科目表作成」にしています。
  9. ボタンの上で右クリックし、マクロの登録を押下します。

  10. ポップアップでマクロ名の最後が「ボタン1_Click」となっていることを確認し、新規作成を押下します。
  11. VBA編集画面が開いたら、全選択ですべてを消した上で、下記コードをそのまま貼り付けてVBA編集画面を閉じます。

    Sub ボタン1_Click()

    Set a = Worksheets("取引先別勘定科目表")
    Set b = Worksheets("マスタ")
    Set c = Worksheets("勘定科目推移表")
    Set d = Worksheets(15)

    a.Select
    a.UsedRange.ClearContents
    Range("A1").Value = a.Name

    b.Select
    lastRowTo = Cells(Rows.Count, 25).End(xlUp).Row
    Range(Cells(5, 25), Cells(lastRowTo, 25)).Copy
    a.Select
    Cells(1, 2).PasteSpecial _
    Paste:=xlPasteValues, _
    Transpose:=True
    Cells(1, lastRowTo - 1).Value = "合計"

    c.Select
    lastRowKa = Cells(Rows.Count, 2).End(xlUp).Row
    Range(Cells(2, 2), Cells(lastRowKa, 2)).Copy
    a.Select
    Cells(2, 1).PasteSpecial _
    Paste:=xlPasteValues
    Cells(lastRowKa + 1, 1).Value = "合計"

    d.Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Cells(5, 27).Formula = "=C5&D5"
    Cells(5, 28).Formula = "=E5"
    Cells(5, 29).Formula = "=F5&G5"
    Cells(5, 30).Formula = "=H5"
    Range(Cells(5, 27), Cells(5, 30)).Copy
    Range(Cells(5, 27), Cells(lastRow, 30)).PasteSpecial

    a.Select
    Cells(2, 2).Formula = "=SUMIF('マクロ'!$AA:$AA,$A2&B$1,'マクロ'!$AB:$AB)-SUMIF('マクロ'!$AC:$AC,$A2&B$1,'マクロ'!$AD:$AD)"
    Cells(2, 2).Copy
    Range(Cells(2, 2), Cells(lastRowKa, lastRowTo - 2)).PasteSpecial
    置換 = Range(Cells(2, 2), Cells(lastRowKa, lastRowTo)).Replace("マクロ", d.Name)

    For x = 2 To lastRowKa
    Cells(x, lastRowTo - 1) = WorksheetFunction.Sum(Range(Cells(x, 2), Cells(x, lastRowTo - 2)))
    Next

    For x = 2 To lastRowTo - 1
    Cells(lastRowKa + 1, x) = WorksheetFunction.Sum(Range(Cells(2, x), Cells(lastRow - 2, x)))
    Next

    Range(Cells(2, 2), Cells(lastRowKa + 1, lastRowTo - 1)).Copy
    Cells(2, 2).PasteSpecial Paste:=xlPasteValues

    d.Select
    Range(Cells(5, 27), Cells(lastRow, 30)).ClearContents

    a.Select
    Cells(lastRowKa + 1, lastRowTo - 1).Select

    End Sub

  12. ボタンを押下すると、VBAが実行されます。仕訳が増えれば増えるほど、VBA完了までに時間がかかります。取引先（横）の合計と勘定科目（縦）の合計がクロスするセルが表示されれば完了です。ここは必ず0円になります。ならない場合、何かがおかしいということになります。

  13. ファイルを保存する際、通常は「Excel ブック (*xlsx)」ですが、VBAを使用した場合は必ず「Excel マクロ有効ブック (*xlsm)」を選択します。ファイル名は任意ですが、どの年度の家計簿なのかわかるような名前が望ましいです。

終わりに

　ここまで家計簿エクセルの作成方法を紹介してきました。あとは日々の仕訳を記録していただくだけとなります。仕訳については私の家計簿アカウント @keiri_queen で少しずつ紹介しています。他に知りたい仕訳があれば、アカウントのリンクにあるマシュマロから質問も受け付けています。これを機に、経理を身近に感じていただけたら嬉しいです。

 

 

2023/07/22