仕訳帳 シート を作成していない方は先に作成してください。
この自作Excelでは
- 消費税申告
- 家事按分
- 減価償却
については触れませんので、ご了承ください。
仕訳帳自作Excelの全体像です。
このうち、今回は 残高試算表 シート を作成します。
なお、作成するExcelはすべてシート保護します。
残高試算表 は 総勘定元帳 とは異なり、必須帳票ではありません。
しかし、 残高試算表 は 総勘定元帳 よりかなり簡単に作成できるので、先に説明します。
説明をきかなくても自分で 残高試算表 を作成できるという方や 残高試算表 は不要という方は、この記事は飛ばしてください。
自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。
残高試算表 シート のイメージ
私が作成した 残高試算表 シート です。
簿記の教科書にある残高試算表をイメージした方は違和感があると思います。
そちらは後程取り上げます。
この説明では、前期繰越を仕訳帳に仕訳せず、勘定科目シートに記載したことを前提とします。
「勘定科目」列
勘定科目 シート からコピーします。
使っていない（予約のみの）科目は、行自体を非表示にするといいでしょう。
= [勘定科目シートの勘定科目] & ""
ここから記載する計算式内では、 [ ] で記述する内容を説明します。
実際は [ ] には該当する数値やセルなどを記載してください。
例えば、 = 勘定科目!B10 & "" 。
「前期繰越」列
= IF ( LEN ( [勘定科目] ) = 0, "", VLOOKUP ( [勘定科目] , [勘定科目シートの該当範囲], [勘定科目シートの該当範囲の前期繰越の列番号], FALSE ) )
例えば、 = IF ( LEN ( A10 ) = 0, "", VLOOKUP ( A10 , 勘定科目!B4:D100, 2, FALSE ) ) 。
前期繰越を仕訳帳に記帳し勘定科目シートに記載しないなら、この列は不要です。
仕訳帳から抜き出すのは不可能ではないと思いますが、手間のわりにメリットがないように思います。
ここも「勘定科目」列 と同様に、単純に 勘定科目 シート からコピーでも問題ありませんが、念のため VLOOKUP関数を使っています。
また、VLOOKUP関数がうまく値を拾わないときはこちらをご覧になって原因を調べてください。
account-it-dentist.hatenablog.com
また、VLOOKUP関数の列番号を動的に取得したい場合はこちらをご覧になってください。
account-it-dentist.hatenablog.com
「借方金額」列
=IF ( LEN ( [勘定科目] ) = 0, "", SUMIF ( [仕訳帳の（借方）勘定科目列], [勘定科目], [仕訳帳の（借方）金額列] ) )
仕訳帳から借方金額を集計します。
このセルを、残高計算の説明では [借方金額総計] と表現します。
「貸方金額」列
=IF ( LEN ( [勘定科目] ) = 0, "", SUMIF ( [仕訳帳の（貸方）勘定科目列], [勘定科目], [仕訳帳の（貸方）金額列] ) )
仕訳帳から貸方金額を集計します。
このセルを、残高計算の説明では [貸方金額総計] と表現します。
「期末残高」列
残高を計算する式を説明する前に、 勘定科目 シート に設定した '残高計算係数' を取得します。
'残高計算係数' は私の造語で、残高を計算する上で
借方金額 － 貸方金額 とするべきか 貸方金額 － 借方金額 とするべきか
を判断します。
= VLOOKUP ( [勘定科目] , [勘定科目シートの該当範囲], [勘定科目シートの該当範囲の残高計算係数の列番号], FALSE )
ここも「勘定科目」列 と同様に、単純に 勘定科目 シート からコピーでも問題ありませんが、念のため VLOOKUP関数を使っています。
また、VLOOKUP関数がうまく値を拾わないときはこちらをご覧になって原因を調べてください。
account-it-dentist.hatenablog.com
また、VLOOKUP関数の列番号を動的に取得したい場合はこちらをご覧になってください。
account-it-dentist.hatenablog.com
それでは残高を計算します。
= IF ( LEN ( [勘定科目] ) =0, "", [前期繰越] + [残高計算係数] * ( [貸方金額総計] - [借方金額総計] ) )
参考記事です。
account-it-dentist.hatenablog.com
以上で 残高試算表 シート はシート保護をして終了です。
このフォーマットで問題ない方は以上です。
簿記教科書の残高試算表フォーマット
簿記の教科書にある 残高試算表 はこのようなイメージではないでしょうか。
この説明では、前期繰越は勘定科目シートに記載せず、仕訳帳に仕訳することを前提とします。
前期繰越を勘定科目シートに記載した場合は、下記の計算結果に [前期繰越] を加算してください。
[前期繰越] については上記で説明しています。
残高計算
前期繰越を勘定科目シートに記載した場合は、下記の計算結果に [前期繰越] を加算してください。
[残高計算係数] については上記で説明しています。
借方金額は、
= IF ( [残高計算係数] = 1, SUMIF ( [仕訳帳の（借方）勘定科目列], [勘定科目], [仕訳帳の（借方）金額列] - SUMIF ( [仕訳帳の（貸方）勘定科目列], [勘定科目], [仕訳帳の（貸方）金額列], 0 )
貸方金額は、
= IF ( [残高計算係数] = -1, SUMIF ( [仕訳帳の（貸方）勘定科目列], [勘定科目], [仕訳帳の（貸方）金額列] - SUMIF ( [仕訳帳の（借方）勘定科目列], [勘定科目], [仕訳帳の（借方）金額列], 0 )
以上で 残高試算表 シート はシート保護をして終了です。
次回から 総勘定元帳 シート を作成します。 総勘定元帳 シート は難しいため、複数回に分けて説明します。
自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。
ご質問は下の コメントを書く からお願いします。
