【家計管理簿記】やり方（エクセル編）手順4_仕訳帳から自動で精算表を作成する

っと、いう記事のエクセル編です。

では本題です。

精算表、こんなものを作成します。

では、淡々と進めてまいりましょう。

シートの作成と交互色付け

まずは「勘定科目リスト」と「仕訳帳」を作った同じブックに、新しいシート「精算表」を名付けて作成します。
そして、表左上をクリックして全体選択しフォントを「游ゴシック（Medium）」に変更、その後B2～R78まで選択して交互色付けします。

それから参考程度でいいのですが、A4縦印刷で６ｹ月分が収まるように高さ幅を調整します。
78行目（任意）までセルの高さは15
列幅について、ABC列は1.5、Ｄ列は10、E列は17、F～R列は12

そして先で便利になるので、F4にカーソルを合わせ⇒表示⇒ウインドウを固定⇒凍結行３列　を設定。
こうして、編集中に行列のタイトルがわかるようにしておきます。

セルの書式設定

E2、E3にはタイトルをつけて右寄せ

G列をコピーして使っていくのですが、とりあえずFG列を選択し右クリック⇒セルの書式設定⇒数値⇒数値⇒桁☑⇒負の数は赤を選択

それから、２行目は締日を入力するので２行目全体を選択して、右クリック、セルの書式設定⇒数値⇒日付で西暦/月/日を選択、ついでに２行目は入力行なのでフォントを青太字にするなどします。
（ワタシは手打ち入力するデータを青太字にしています）

プルダウンリスト設定

ちょっと狭いのですがC4に1段目プルダウンリストを作成します。
C4⇒データ⇒入力規則⇒設定⇒リスト⇒ソースには勘定科目シートの資産～損益まで選択しＯK

２段目の作成はE4を選択し⇒データ⇒入力規則⇒設定⇒リスト⇒ソースには
「=INDIRECT(C4)」　と入力しOK。

これで１段目（C4）に紐づく2段目プルダウン（E4）が作成されました。
ん？　っと、思われた方はこちらに戻って確認ください↓

最後にC4とE4を選択しドラグ＆ドロップで７８行までコピー、
これでプルダウンリストの作成完了です。

勘定科目（プルダウンリスト）入力

C4には大分類、E4には勘定科目をプルダウン選択で、勘定科目設定した項目すべてを順序通り入力します。
（面倒ですけど最初だけです）

中分類の設定

勘定科目で設定した分類項目は、中分類の意味合いです。
後々のグラフ化で見やすくなるのでおすすめです。

行を挿入して新たに設け、その行のD列に中分類の内容を手入力（ここは手入力）し、小計の意味で太字にしておきます。
「iDeco」「不動産」など、内訳が１項目しかないものは、行を挿入せずにD列に手入力します。

ごちゃごちゃしてくるので、C列を選択しフォント文字を白色に変更
（見やすさの問題です、消してはいけません）

大分類の設定

大分類項目用に行を新たに挿入、B列に大分類項目を手入力し、見やすさのために文字を大きく太字にします。
さらに上に空行を挿入して見やすくし、貸方側を赤字にします。

さらに、下のように行挿入して資産と損益の区分の見やすさのためにB列に貸借対照表と損益計算書の大大分類の設定をします。

開始貸借対照表の入力

前置きが長くなってしまいました、ここからが仕訳データを引数として集計していく本題です。

まずは仕訳帳シートの24年12月の集計データを精算表に引いてきます。

精算表のF3にセルを合わせ以下をコピペ

=TEXT(EDATE(F2,0),”yy/mm”)

これはF2の　”年/月/日”　データを　年/月　データに変換する意味です。

それから精算表シート、F列の「現金」行、F9にセルを合わせ、以下をコピペして関数を入力

=SUMIFS(仕訳帳!$F:$F,仕訳帳!$E:$E,$E9,仕訳帳!$C:$C,F$3)+SUMIFS(仕訳帳!$K:$K,仕訳帳!$J:$J,$E9,仕訳帳!$H:$H,F$3)

この関数の意味は=SUMIFS(合計範囲,範囲,検索条件,,,）
つまり、=SUMIFS(計算対象数値の範囲,ヒットされる範囲,ヒットする条件,ヒットされる範囲2,ヒットする条件2）っと、いう意味
もう少し、掘り下げて口語訳すると、
（借方の金額範囲のうち,勘定科目範囲の中から,現金と同じ行の数値を合計,するんだけど年月の範囲,から24年12月のものだけ）+（貸方の・・・）　っと、いう意味

これが基本となってコピーしていくので、絶対参照、相対参照をうまく利用しています（範囲設定の$の使い方）

そして、今設定したF9をグッっとつかんで、ダーっと損益の行まで下げれば、バッーと数値が引っ張てこれます。

中分類の空欄には小計をSUM関数で表示するようにして、中分類を太字にするなど見やすくします。

これで２４/１２データ（開始貸借対照表データ）を引っ張ってくることができました。

２５年/１月の集計

次に２５年１月の集計、
F２～F35を選択して、右へ１列コピー、ザっと数値が現れます。
その後G2に「1/31」と月末日付を上書き入力。

G9にセルを合わせ、関数BOX（fｘ）に表示される関数の最後に、「+F9」を入力して前月の集計値を加算するようにします。

これが　”資産の部”　の基本となる関数です。

Ｇ９にセルを合わせ、負債の最後の行、G76までググーっと、コピー、
ババ―っと、集計されます。
そのあともう一度中分類の小計や太字の修正を行います。
（小計を要しない１項目のみの中分類項目（例：年金資産や架空資産等）は太字修正のみ）

”損益の部”については、先月のデータ加算の必要はないので修正します。

先頭行のG42（給与）にセルを合わせ関数BOXの末尾「+F42」を削除します。

そして、G42にセルを合わせG76までググーっとコピー、ババ―っと表示されます。

それからデータのないところも含めて、中分類の小計と太字の設定をやっておきます。

次、純資産と損益を除いて、各中分類の中計を大分類に表示させ太字にします。

さて、次は損益の計算、
損益は、「 収入 － 費用 」ですので、その計算式を入力します。
G76にセルを合わせ、一旦以下をコピペ、上書き

=ABS(G41)-G52

収入の表記は赤字なので数値上はマイナスになっています、なので絶対値ABS関数を使っています。
大分類（G75）に「=G76」で同じ数字を表記させます。

今度は、純資産の計算、
純資産は、「 資産 － 負債 」ですが、純資産も赤字（マイナス）表記ですので、計算式としては、
「 負債 － 資産 」の計算で、赤字で純資産を表記させます。
G35に以下をコピペ、上書き

=ABS(G22)-G7

それから、大分類（G34）に「=G35」で同じ数字を表記させます。
これで、ほぼ形になってきました。

チェック機能

最後にもうひと手間、
集計結果が正しいかどうかのチェック機能を追加しておきます。
「損益の部☑」　という行を作ってG78に以下をコピペ

=IF(SUM(G42,G47,G53,G64,G69,G73,G76)=0,”OK！”,SUM(G42,G47,G53,G64,G69,G73,G76))

これは、損益の部の中分類の計がゼロならば”OK”、それ以外ならばその計算値を表示
っと、いう意味です。

続いて、資産の部
以下の様に、「資産の部☑」　行を作成し、以下をコピペ

=IF(G35-F35+G76=0,”OK！”,G35-F35+G76)

これは、先月と今月の資産の差額が損益と一致していれば”OK”、それ以外ならばその計算値を表示
っと、いう意味。

こうしておくと、仕訳のミスや勘定科目の追加などのときにミスを発見しやすくなります。

さて、最後です。

G2～G78を選択し、右にコピー、日付を月末に上書きすれば一瞬で２月精算が行えます。

こうして、月締め仕訳作業を終えた都度、コピーを繰り返して精算表を作成します。

まとめ

精算表の作成については、ピボットテーブルという自動集計のやり方もあるのですが、ワタシはこのやり方がシンプルで一番応用が利くと思っています。

ポイントはSUMIFS関数、
これを使えば、借方貸方の勘定科目を抽出して計算してくれるので超楽です。

あとはお好みで中分類作成などアレンジしていけばいいのです。

いずれにしても、仕訳帳のデータを抽出する関連性がポイントですので、仕訳帳作成時に勘定科目を追加したときなどは、精算表においても追加の処置が必要です。

次回説明予定の「グラフを作成する」で、最終的にどのような資料ができるのを確認しながら、自身オリジナルの家計管理を作成していきましょう！

「自由になるお金」の管理のために！

では、今回はこのへんで。

★　★　★