勘定科目 シート を作成していない方は先に作成してください。
この自作Excelでは
- 消費税申告
- 家事按分
- 減価償却
については触れませんので、ご了承ください。
仕訳帳自作Excelの全体像です。
このうち、今回は 仕訳帳 シート を作成します。
なお、作成するExcelはすべてシート保護します。
仕訳帳 シート は入力の中心となるシートで、出力される 総勘定元帳 シート や 残高試算表 シート の参照元となります。
自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。
- 仕訳帳は複合仕訳禁止
- 仕訳帳 シート のイメージ
- 「仕訳番号」列
- 「取引日」列
- 「摘要」列
- （借方）（貸方）「勘定科目」列
- （借方）（貸方）「補助科目」列
- 「（借方）金額」列
- 「（貸方）金額」列
- 仕訳は何行用意するか？
- 運用上の禁止事項
仕訳帳は複合仕訳禁止
この仕訳帳の制約として、複合仕訳は記載できませんので注意してください。
複合仕訳って何？という方もいると思いますが、私もこの記事を書くまで知らない用語だったので、具体的に説明します。
「仕入済の商品代5,000円に対し、支払済手付金1,000円を差し引いた4,000円を普通預金から振り込み、振込手数料100円を当方で負担した。」
上記の取引は以下のように仕訳することができます。
| 借 方 | 貸 方 | ||
|---|---|---|---|
| 買掛金 支払手数料 |
5,000円 100円 |
前払金 普通預金 |
1,000円 4,100円 |
このように借方もしくは貸方が複数行になる取引を複合取引とよび、会計ソフトでは借方金額合計と貸方金額合計がバランスしているかエラーチェックします。
この自作Excelでは、行単位で借方と貸方がバランスする必要があるため、上記のような複合仕訳の形式では登録しません。
上記取引を分解して各行でバランスさせる仕訳にします。
前払金を買掛金に充当すると
| 借 方 | 貸 方 | ||
|---|---|---|---|
| 買掛金 | 1,000円 | 前払金 | 1,000円 |
| 買掛金 | 4,000円 | 普通預金 | 4,000円 |
| 支払手数料 | 100円 | 普通預金 | 100円 |
となります。
このように仕訳すると、この３行が同一取引であることが分からないので困る、という方もいると思います。
対策としては
- 仕訳番号を自動採番せずに、同一取引には同一仕訳番号を自分でふる。
- 摘要欄に同じコメントを記載することで、同一取引であることを表現する。
といったことが考えられます。
単一仕訳にするなら "諸口" を使用するのが正しいと思う方もいらっしゃるかもしれません。
単一仕訳と複合仕訳の説明をしているページを見つけました。
私のような分解した仕訳は邪道かもしれませんが、面倒なので "諸口" を使用した仕訳にしていません。
"諸口" を使用するなら、 "諸口" を 勘定科目 シートに追加してください。
仕訳帳 シート のイメージ
私が作成した 仕訳帳 シート です。
本当はセルに色付けしていないのですが、説明のために色付けしました。
グレーのセルは計算式を設定し入力不可にしているセル、白のセルは入力するセルです。
入力対象となる白いセルがかたまっていることが、お分かりいただけると思います。
こうすることで Copy & Paste 運用をやりやすくしています。
「仕訳番号」列
私は自動採番にして入力不可にしています。
= ROW() - [ヘッダ部の行番号]
ここから記載する計算式内では、 [ ] で記述する内容を説明します。
実際は [ ] には該当する数値やセルなどを記載してください。
私は２行目までをヘッダ部に使用していますので、 = ROW()-2 と記述しています。
「取引日」列
入力可能にしてください。
私は "yyyy/mm/dd" 形式で表示していますが、仕訳帳には年を表示しないのが一般的です。
ここでは入力規則を設定しなくてもいいと思います。
参考に私の入力規則設定をご紹介します。 勘定科目 シート の A1 セルに年を指定しています。
年のチェックは不要でも、Excelでは数値を入力すると日時に変換してしまうので、1980/1/1から2100/12/31までなどと指定してもいいかもしれません。
「摘要」列
入力可能にしてください。
入力規則は設定していません。
（借方）（貸方）「勘定科目」列
入力可能にして、必ず入力規則を設定してください。
入力規則は、リボンのデータタブにあります。
入力値の種類にはリストを、元の値には [勘定科目シート作成時に指定した勘定科目セル範囲の名前] を指定してください。私は「勘定科目」という範囲名を指定しています。
元の値には、同一シートの場合セルを指定できますが、別シートの範囲を指定する場合は名前でないといけないので、注意してください。
なお、このリストの表示順は、元の値のセル順になります。コードを頭につけても並び変えてくれません。
入力規則はさぼらずに設定することをおすすめします。
Excelは自由度は高いのですが、その分集計数値の信頼度を保つことは難しいです。
逆に自由度を制約して、集計数値の信頼度を高めましょう。
（借方）（貸方）「補助科目」列
入力可能にしてください。
私は入力規則は設定していません。補助科目を管理している方は、勘定科目と同様の設定をしてください。
勘定科目と補助科目の組み合わせで管理したい方はこちらの記事をご覧ください。
account-it-dentist.hatenablog.com
「（借方）金額」列
入力可能にして、必ず入力規則を設定してください。
プラス整数のみを許可します。
入力規則はさぼらずに設定することをおすすめします。
Excelは自由度は高いのですが、その分集計数値の信頼度を保つことは難しいです。
逆に自由度を制約して、集計数値の信頼度を高めましょう。
自作Excelの運用を始めてからしばらくは、同時入力していた会計システムと数字が合わないことがよくありました。
勘定科目に誤字があり、勘定科目シートは修正したのに記帳済みの仕訳帳シートを修正しなかったことが原因だったこともあります。
不動産用の勘定科目には「（不動産）」とつけているのですが、かっこに半角と全角が混じっていたため、統一しようとしたときにかえっておかしくなってしまいました。
それでも、ずれている金額からだいたい不一致原因を突き止めることにはさほど苦労しなかったのですが、一番苦労したのが残高が１円だけずれたときでした。
最初は会計システムのバグかとも思ったのですが、電卓片手に明細を読み上げてチェックしたところ、間違っていたのはExcelでした。
何が原因なのか見当がつかず頭を抱えて、自作Excelの運用もあきらめようと思っていたのですが、ようやく原因がわかりました。
シート上は整数で表示されていたものの、セルの中身は小数点以下の数字を含んでいたのです。
私は消耗品費を家事按分するときは、年末決算処理時ではなく仕訳帳記帳時に行っていました。
この按分計算をExcel上で行いコピペした結果残高が１円ずれるということになりました。
私と同じ苦労をしないためにも、入力規則の設定はさぼらずに行ってください。
「（貸方）金額」列
（借方）金額と同額を設定し、入力不可にしています。
単純に借方金額セルを指定してもいいのですが、未入力行は "0" 表示されるので
= IF ( ISBLANK( [借方金額] ), "", [借方金額] )
と設定します。
仕訳は何行用意するか？
当然人によりけりですが、開業初年でどれだけ用意すればいいか分からないという方もいると思います。
めいっぱい設定すればいいと思っている方もいるかもしれませんが、あまりおすすめしません。
関数を仕込む行が増えれば、ファイルもそれだけ重くなります。
そもそも、総勘定元帳シートにはおおよそ仕訳帳の倍の行が必要になるので、めいっぱい設定しても無駄です。
あまり頻繁に取引が発生しないなら、一月あたり40行使うとして年500行用意すれば足りるでしょう。
心配でしたら、倍の1,000行用意して運用してみてください。
運用上の禁止事項
仕訳帳シートには絶対にやってはいけないことがあります。
- 行挿入は禁止
- 並び変えは禁止
これらを行うと総勘定元帳シートなどに仕込んだ式が壊れてしまいます。
とはいえ、仕訳帳は日付順に並んでいないといけませんし、あとから古い日付の仕訳を追加したいということは実際ままあります。
そのため、作業用の仕訳帳シートを別に用意してください。
私は「仕訳帳（繰越処理済）」というシートを用意してます。
本来前期繰越と次期繰越の仕訳を記載するために用意したシートですが、ここにコピペしてから行挿入や並び替えをしたのち仕訳帳シートにコピペで戻すようにしていました。
ちなみに次期繰越まで仕訳帳に記載すると残高がゼロになるので用意したシートです。
むしろ普段は別シートに入力し、残高計算が必要になった時点で仕訳帳シートにコピペする運用にしています。
また、私はシート名も変更すると設定した式がくずれるのでやらないことにしてます。
以上で 仕訳帳 シート はシート保護をして終了です。
相対参照と絶対参照との切り替えについては記事を作成していますので、興味があればご覧ください。
account-it-dentist.hatenablog.com
次回は 残高試算表 シート を作成します。
自作するのはめんどうなので完成品がほしい、という方はこちらからどうぞ。
ご質問は下の コメントを書く からお願いします。
