エクセル/複式簿記の会計ツール

1.概要

 エクセルにて複式簿記での会計ツールを自作してみました。
関数の組み合わせで一応作成はできます。
本当に基本的な機能しかありませんので、高機能をお求めの場合は会計ソフトを使った方が良いと思います。

 「管理」シートにて科目と年度を設定し、「会計」シートにて仕訳入力を行う形になっています。
複式での仕訳を入力すると、自動で貸借対照表と損益計算書が作られ、また費用一覧や元帳の取得も可能になっています。

 資産・負債と純資産の元帳については繰越残高表示が面倒なので、期中の増減額のみ表示する形にしました。
決算書で三期分の表示が可能なので、そちらで繰越残高は確認できます。

 事業主貸と借についても、それぞれ別科目にすると計算がやや面倒なので、事業主貸借という一つの科目を用いています。

※ご注意:
本ツール使用による損害・トラブルについて私は一切責任を負いかねますので、ご了承ください。

2.機能

 行政書士の独占業務では無いので公開することにしました。
記帳を正確に行い青色申告で確定申告を行えば、青色申告による控除額が最大65万円になります。

 シートの関数表示も伏せる必要は無いと思ったので外します。
もっと良い方法があるとは思いますが、関数を読めばどれだけエクセルが便利かが分かるかと思います。

 作成にあたり、考えた機能は以下の通り。
1.行詰め
2.[名前]を用いての、空白でないセル範囲自動選択
3.複数列の一意性チェック
4.セル値に対応する列に対しての別の列値取得
5. 〃         別の列の集計値取得
6.[条件付き書式]による、空白でないセルの枠付け

 いくつかの参考HPで載せられていた関数を組み合わましたが、ここでの紹介は割愛させて頂きます。

 少々大袈裟な表現かもしれませんが。
個人的に、複式簿記は人類の特に大きな英知の一つと思っています。
そのうち、減価償却費の自動計算表も追加するかもしれません。

修正日修正内容
2022/07/03初回版を公開。
2022/12/29・費用一覧部分のバグ修正
・税込記帳の場合の消費税に係る記載列と集計追加
・減価償却費計算のシート追加
・電子帳簿保存用の索引表追加
2023/01/21・OneDrive上でも動作するように修正
・元帳の月毎集計欄を追加
2023/03/14・フィルターやロックの掛け直し
・費用集計をやめ、単なる費用一覧へ修正
202311/22・消費税の端数処理の条件分岐を修正
※端数処理が四捨五入のみになってしまっていた。
・インボイスの有無を入力できる欄を設け、仮受消費税のうちインボイス部分のみ集計可能とした
・税込と税抜方式での記帳例をアップ
2024/02/07・前受収益や前払費用の計算用シートを追加
余白に備考を入れれば、長期分も把握可能。
2024/02/24・「会計」シートの費用一覧における[伝票no]列の書式が一部おかしかったので修正
・全シートにおいて[セル書式の設定]はロック対象から除外
2024/06/14
ver4-3
・vlookup関数は動作が重くなるので全て別関数で置き換え
・その他、重複計算部分の排除
※ファイルサイズは大きくなったが、動作は幾分早くなったはず。

3.リンク

(1)サンプル

-西丸のone drive
「k_会計ツール」
「作成例」
※ダウンロードしてMS EXCELで開くか、ご自身のonedrive上にアップロードしてご使用下さい。

(2)アップロード動画

youtubeマイチャンネル/「参考8」エクセル/会計ツールの自作例

4.更新履歴

・2022/07/10:ページ公開
・2023/07/30:クラウドドライブのリンク等修正
・2023/11/22:会計ツール修正の記載追加
・2024/06/14:「2」追記

スマホ等の会計アプリ活用法

1.会計アプリについて

事業収支を手書きで記録している方向けの参考情報です。
スマホをお持ちであれば基本無料の家計簿アプリが使えるため、個人事業であれば若干の工夫で流用可能です。

既に会計ソフトをお使いの方にとっては不要かもしれません。

– 使えそうなアプリ例
①家計簿カケイ ※費用毎に集計されるので限定的な複式?
https://play.google.com/store/apps/details?id=com.sakura_apps.kk&hl=ja&gl=US
②複式家計簿 ※複式
https://play.google.com/store/apps/details?id=com.gomao.kakeibo&hl=ja&gl=US
※自分の目についたものなので、他にも優良アプリがあるかもしれません。

これらの最大のメリットは自動集計にあると思います。記帳ミスをしても再集計する必要がありません。
正確な入力を普段から欠かさず行えば、会計に取られる時間が大幅に節約できるはずです。
万一のスマホ故障に備えてのバックアップも可能。念の為、定期的に手書きノートへの転記やスクリーンショット保存をしておけば安心では無いでしょうか?

2.単式と複式簿記について

ザックリ簡単に言えば。

単式簿記では基本的に現金残高の増減のみで表す形になり、その出入りの用途はあくまで備考的です。
ですので費用や収益の集計が困難です。
また現金や預金を分けての管理や、それら以外の資産や負債の管理も想定されていません。

一方複式簿記では、借方(左列)と貸方(右列)の二列を用いて何が何によって増減したかの記録ができます。つまり常にカテゴリ分けがされた状態で記録されます。
最悪仕訳帳さえ残していれば、資産・負債・純資産や収益・費用の残高と増減が全て保存できます。
貸借対照表や損益計算書、元帳については、カテゴリ毎の集計や要約の話になります。

複式簿記で最初に悩むのが、仕訳において何を借方(左列)・貸方(右列)に入れるべきかという点かと思います。
個人的には、「資産の増加が左側(借方)」これだけ覚えて負債や用途は逆側に入れることを考えれば楽かと。

■複式簿記のおすすめ勉強サイト
StudyPro/簿記3級 テキスト 問題 予想模試
https://studyboki3.com/
※自分が最初に複式簿記に触れたサイトですが、イラスト付きで楽しいですし非常に分かりやすい優良なページかと思います。
アカウントを登録しログインした状態で勉強すれば、中断しても進捗が保存され、勉強が途中から再開できて便利です。

ただし、もし日商簿記3級受験を目指すのであれば、このサイトだけでは若干不足かもしれません。
個人事業で複式簿記に切り替えたい場合は、この内容で十分かと思います。
仕訳例は、ネット上を探せばすぐ見つけられます。

※貸「し」が右向きなので「右欄」、借「り」が左向きで「左欄」の件は、一瞬で覚えられるので感激しました。

3.使い方の流れ

(1)インストール

スマホの「google play」を立ち上げてアプリ名で検索するか、下記サイトに繋ぎ「インストール」をタップし、しばらく待てばインストールが完了します。

「複式家計簿」は複数ユーザー作成可能ですので、事業用と家計簿用など使い分けが可能で便利です。
青色申告に必要な元帳・台帳を揃え電子的な確定申告「e-Tax」を行えば、控除額が65万円になりますし、正確な記録付けの点からも複式簿記をお勧めしたいところです。

①家計簿カケイ ※費用毎に集計されるので限定的な複式?
https://play.google.com/store/apps/details?id=com.sakura_apps.kk&hl=ja&gl=US
②複式家計簿 ※複式
https://play.google.com/store/apps/details?id=com.gomao.kakeibo&hl=ja&gl=US

(2)初期設定(科目や開始値・残高の設定、入力の登録など)

ア.科目設定
家計簿用アプリでは事業収支に係る科目が無いため、売上や仕入などの科目を作成する必要があります
①カケイ
・売上と仕入:左上「三」の「カテゴリ設定」から、売上(収入)と仕入(支出)を新規作成
・売掛と買掛: 〃    「その他設定」-「銀行口座」で売掛と買掛を作成
※仮の科目として銀行口座を使う方法です。他にもっと良いやり方があるかもしれません。
②複式家計簿
・[科目設定]から科目を追加するのみ

イ.開始値・残高の設定
①カケイ
・[残高調整]から現金や預金の開始残高を設定
②複式家計簿
・仕訳から科目[開始残高]を用いて記帳
※科目名を[元入金]に変更しても良い。

ウ.入力の登録
両アプリとも、よく使う入力・仕訳を登録して呼び出すことで記帳を早く行うことが出来ます
①カケイ
・登録:入力の際に右上の「⋮」から「クイック入力として登録」
・呼び出し:入力タブ(上段左端のエンピツマーク)下部の「クイック入力」から選択
②複式家計簿
・登録:仕訳後に仕訳明細(下段左端)から登録したい仕訳をタップ→「定型仕訳に登録」
※金額固定で無ければ「金額は毎回入力する」にチェック。
・呼び出し:仕訳(下段左から2番目の「新規」)の下段「定型仕訳」から登録済み仕訳を選択

(3)記帳

・仕訳や計上に関する情報はネット上にすぐ見つけられるかと思います
・いくつかの記帳例やアプリ利用方法は「4.個人事業での記帳例」「5.スクリーンショット」に載せてあります

4.個人事業での記帳例

仕訳例では簡単のため金額の桁を小さくし、諸口(1対複数行の仕訳で使う記述)も使いません
金額も左右(借・貸方)同額となるため、金額記載も一列のみとします。
その点若干不正確ですが、逆に分かりやすい部分もあるでしょうしエクセルを用いる方にとってはメリットとなります。

※仕訳例は架空であり、現実味もありません。

(1)開業費と償却

・1/1:開業前に事業用の備品(減価償却対象外)を10円で購入している。開業日は2/1

日付借方科目貸方科目金額
2/1開業費元入金10

・9/1:開業費分10円をさっそく償却した

日付借方科目貸方科目金額
9/1繰延資産償却開業費10

(2)元入金

・2/1:個人事業開業。現金500円、スイス銀行預貯金1,000円を元入金とした

日付借方科目貸方科目金額
2/1現金元入金500
預貯金(スイス銀行)元入金1000

(3)仕入、掛仕入

仕入や売上時の消費税率や非課税については、会計ソフトであれば補助的な設定が可能ですが、今回紹介したアプリでは税率毎の科目を作成して区別する必要がありそうです。

・3/1:事業用の商品を10円分現金で仕入れた

日付借方科目貸方科目金額
3/1仕入現金10

・4/1:事業用の商品を20円分掛けで仕入れた

日付借方科目貸方科目金額
4/1仕入買掛金20

・4/30:掛仕入分20円が銀行口座から引き落とされた

日付借方科目貸方科目金額
4/30買掛金預貯金(スイス銀行)20

(4)売上、掛売上

・3/5:事業用の商品を売却し、現金20円を得た

日付借方科目貸方科目金額
3/5現金売上20

・4/5:事業用の商品を掛けで30円分売却した

日付借方科目貸方科目金額
4/5売掛金売上30

・4/15:掛売上分30円が銀行口座に入金された

日付借方科目貸方科目金額
4/15預貯金(スイス銀行)売掛金30

(5)事業主貸・借

・5/1:事業用の商品を10円分、自分の財布で仕入れた

日付借方科目貸方科目金額
5/1仕入事業主借10
※個人用クレジットカードや預貯金の場合なども同じ。

・5/5:事業用の商品を20円分売却し、そのまま生活費とした

日付借方科目貸方科目金額
5/5事業主貸売上20
※入金先を個人用口座にした場合なども同じ。

※因みに、開業費を元入金に入れず事業借とすることもできます。

(6)減価償却資産

・4/1:事業用の車を現金61円で購入した。車の耐用年数は5年、定額法で償却する

日付借方科目貸方科目金額
4/1車両運搬具現金61
※保険料や税の支払いについては省略。

※定額法は(取得価額 – 残存価額1円)を耐用年数で均等に償却。
一方、定率法では未償却残高に対して一定の割合で償却し、未償却残高がある保証額以下になってからは定額償却に変える。
つまり最初に大きく償却したいときは定率。毎年一定にしたければ定額。

・12/31:事業用車の減価償却費9円を計上した

日付借方科目貸方科目金額
12/31減価償却費車両運搬具9
※4~12月の9か月分:「(61 – 1)円/5年」× 9/12か月 = 9円。

※上は資産価値を直接目減りさせる「直接法」での表記。
資産価値は取得時のままで、減価償却累計額として計上する「間接法」もある。
間接法の方が資産と減価償却とを区別しやすいが、青色申告決算書で毎回取得価額を記録するので直接法でも大して困らない。

(7)前払費用・前受収益と繰延

いくつか仕訳方法がありますが、最初に費用・収益計上し期末や期初に振り替えることとします。
サブスクの更新は無しと仮定。前受収益については、費用の逆なので割愛。

①3/1:事業用の通信系サブスク3年分を36円で契約し、代金を事業用口座から振り込んだ

日付借方科目貸方科目金額
3/1通信費預貯金(スイス銀行)36

②12/31:サブスク代うち来期分12円(12か月分)を前払費用、残り14円(14か月分)を長期前払費用として繰延

日付借方科目貸方科目金額
12/31前払費用通信費12
長期前払費用通信費14
※今期分の通信費は3~12月の10か月分として、10円が残る。


③2年目1/1:前払費用12円を当期の費用に振り替えた

日付借方科目貸方科目金額
翌年1/1通信費前払費用12

2年目12/31:長期前払費用14円うち次期分12円(12か月)を前払費用に振り替えた

日付借方科目貸方科目金額
翌年12/31前払費用長期前払費用12

以下の仕訳は省略。

3年目1/1:前払費用12円を当期の費用に振り替えた
3年目12/31:残りの長期前払費用2円(2か月分)を前払費用に振り替えた
4年目12/31:前払費用2円を当期の費用に振り替えた

(8)前払・前受金、立替・未払金、預け金・預り金(受託販売)

前払金は代金一部の先払い。立替は取引先が負担する手数料や実費など。預け金は代行者に預けて後で返してもらう場合など。逆は割愛します。

6/1:事業用商品20円分のうち10円を先に現金で払い、翌日に残りを現金で支払った

日付借方科目貸方科目金額
6/1前払金現金10
6/2仕入前払金10
仕入現金10

・6/2:上の商品を取り寄せる際の配送料5円は現金で立て替えて払った

日付借方科目貸方科目金額
6/2立替金現金5
※立替でない場合、商品に係る送料の借方科目は「仕入」とし、それ以外は「通信費」。

6/5:商品を30円で売却し、立替分5円を加え35円を相手に請求した

日付借方科目貸方科目金額
6/5売掛金売上30
売掛金立替金5

(9)棚卸と売上原価

「売上原価」は「前期繰越分(期首棚卸高) + 仕入分 ー 期末棚卸高」。

※いくつか仕訳方法がありますが、売上・仕入・繰越商品の「三分法」を取り、科目として売上原価を設けずに期末仕訳後の仕入額を売上原価とします。
簡単のため、棚卸減耗損(在庫一部の消失)・商品評価損(劣化など)には触れません。

・12/31:商品の前期繰越は10円。今期仕入は60円。期末棚卸高は15円だった
売上原価 = 10 + 60 – 15 = 55円。
期末仕訳では繰越商品(前期繰越分)を仕入へ、期末棚卸高の分は仕入から繰越商品へと振り替えます。

日付借方科目貸方科目金額
期末仕入繰越商品10
繰越商品仕入15
※今期仕入に上の-5円分が加味される形になります。

※売上原価 + 期末棚卸高の今期扱った商品総量で見ると、間違えにくいかもしれません。
科目として売上原価を用いる場合(売上原価対立法)は、売上計上時に原価の計上仕訳もするため期末処理は不要となります。売上原価を別に設けるので見やすいですが、その分仕訳が増えます。

(10)損益、振替と繰越

元入金の次期繰越 = 前期繰越 + 事業主個人からの出入れ( 事業主借 ー 事業主貸 ) + 損益( 収益 – 費用 )
事業主から入れたお金と余った収益を加えて次期に回す感じでしょうか。

・1/1:前期の元入金に、事業主個人の出入分50円(事業主借100 ー 事業主貸50)と利益500円が加わった ※一部記載誤りのため修正。

日付借方科目貸方科目金額
期末事業主借事業主貸借の繰越
※期末で元入金と区別する場合
100
事業主貸借の繰越事業主貸50
損益(※収益>費用の場合)繰越利益500
翌年期初事業主貸借の繰越
(※借り>貸しの場合)
元入金50
繰越利益元入金500
※損失の場合は借方/貸方:元入金/損益と逆になります。

※おそらくここは、最初に勉強する際に?となるはず。
それまで複式の2列で記載していたのに、いきなり「損益の振り替え」という謎の方法が登場するからです。

資産・負債を次期に持ち越し、収益・費用をリセットしたうえ損益を純資産の増減に組み込む為の処理、といった認識で良いかと思います。

会社の場合は利益剰余のうち準備金や配当、積立金を除いたものを繰越利益としますが、個人事業の場合は[当期利益]→[繰越利益]→[元入金の増加]なので、損益分を元入金に入れるだけになります。上の3、5行目は以下の仕訳を端折った感じですね。

日付借方科目貸方科目金額
期末<収益科目>損益<収益額>
損益<費用科目><費用額>
損益(※収益>費用の場合)利益剰余<利益額(収益 ー 費用)>
利益剰余繰越利益
翌年期初繰越利益元入金

会計ソフトでは締めや繰越を自動で行ってくれるので意識する必要はありませんが、エクセルで自作する場合は通常仕訳と区別するため、日付とは別に「期初・期末」などの表記を用いた方が良さそうです。

5.スクリーンショットと解説