Excelの複式事業簿～第5章-4：各補助簿を作成する～売上管理台帳編～

この記事は、あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』内で紹介されている「本気の家計簿」を参考に、Excelを使った複式家計簿を事業用に応用する帳簿「Excelの複式事業簿」を作るまでを撰述したものである。

前の記事<<第5章-3：各補助簿を作成する・預金出納帳編

• 第0章：Excelを使った複式家計簿を事業用に応用する～導入編～
• 第1章：「本気の家計簿」を事業用にわかりやすい設定へ変更する
• 第2章：勘定科目と仕訳帳のシートに必要項目を追加する
• 第3章：万能表シートを追加する
• 第4章：インボイスに対応したバージョンを作る
• 第5章：各補助簿を作成する～一覧～
• 第6章：総勘定元帳を作成する

あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』で紹介されている「本気の家計簿」を応用して個人事業用の帳簿をつけたい、と思い立ったことから、完全無料で「Excelの複式事業簿」を作っていく連載。

今章では補助簿シートの作り方を撰述している。

今回は売上管理台帳。いわゆる売上台帳（売上帳）である。

売上を日付と請求書単位で管理し、税額や源泉徴収額の表示、また売掛金や未収金になっている場合は入金金額の管理やステータス表示がされる仕組みになっている。

なお「本気の家計簿」の作り方・記帳の仕方はここでは解説しないので、ぜひあがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』をご参照いただきたい。

あがぺいブログ｜ミールキットとExcel家計簿でラクする30代主夫

仕事や育児に忙しい共働き夫婦に向けて、おすすめのミールキットを提案します。食材宅配サービスを食生活アドバイザーの視点からレビューします。

agapei-blog.com

本記事は Windows版 Excel 2021 を基に解説しています。
Excel 365 や Mac版をご利用の場合、一部表示や機能に違いがある場合があります。

売上台帳の役割とは

「Excelの複式事業簿」で「売上管理台帳シート」を作成する前に、売上台帳の基本的な役割をおさらい。

名前の通り事業者が得た売上の記録と管理のために記帳する補助簿の一つ。

請求日・入金日・入金額の対応関係を記録し、入金済／未入金のステータスを管理する役割を持っている。

税務署への提出が必須なものではないが、所得税法施行規則により売上に関する帳簿の作成が義務付けられている。

売上管理台帳を作成する前の記帳方法と勘定科目の確認

「Excelの複式事業簿」の「売上管理台帳シート」を作成する前に、今後「売上管理台帳シート」を活用していくために、いくつかの確認しておくべき事項がある。

源泉徴収された金額をどう台帳に記帳するか

個人事業主として開業した場合、原稿料や講演料などの特定報酬には基本的に源泉徴収が発生する。

会計上は「未収源泉所得税」を使い、確定申告後の還付や税額充当で帳簿処理を行うのが一般的とされる。

ただしこの処理は帳簿が煩雑になりやすいため、ここでは暫定的に「事業主貸」で源泉徴収分を記帳する方法を採用する。

「事業主貸」で処理すれば、源泉分を事業主が立て替えた形となり、仕訳もシンプルに済む。

実務上でもよく使われている方法であり、記帳を効率化したい場合に有効な選択肢といえる。

まだ入金されていない売上は「売掛金」で統一

商品を納品して、まだすぐに売上が入金されない場合、会計上でも基本的には勘定科目を「売掛金」で仕訳をするはずなので、基本的には問題ないかと思われる。

もし「売掛金」以外での勘定科目、たとえば「未収金」などを利用している人は、この後解説するExcelの数式の「売掛金」の部分を該当名称に変更すると対応できる。

売上関係の勘定科目に「売上」の文字を入力しておく

「Excelの複式事業簿」で仕訳を入力する際、このシートの自由度の高さを活かし、勘定科目を「原稿料」や「講演料」など具体的な名称にしているケースもあるだろう。

しかし「売上管理台帳シート」では、売上科目を抽出するために、科目名に「売上」の文字が含まれている必要がある。

そのため、科目名は「原稿料（売上）」「講演料（売上）」のように必ず「売上」を明記しておくこと。

「Excelの複式事業簿」で売上管理台帳シートを作成する

それではこのセクションから「Excelの複式事業簿」における売上管理台帳シートを作成していく手順を撰述していく。

仕訳帳シートに「クライアント」と「請求書番号」の列を追加

売上管理台帳のシートを作成する前に、仕訳帳シートに新しく「クライアント」と「請求書番号」の見出しを追加する。

なお、インボイス対応バージョンの「Excelの複式事業簿」を利用している人は、それぞれW列とX列に、インボイス非対応バージョンの人はR列とS列に「クライアント」と「請求書番号」の見出しを追加することになるはずである。

売上管理台帳シートの作成（インボイス対応版）

こちらで撰述する売上管理台帳シートの作成手順は、インボイス対応版で解説。

インボイス非対応版については次のセクションにて対応版との相違点に絞って解説している。

• 画面下の［＋］をクリックして新しいシートを開く
• シート名を「売上管理台帳」にし、一行目の各列に以下の見出しを入力
  • A列：年
  • B列：日付
  • C列：取引No
  • D列：クライアント
  • E列：請求書番号
  • F列：勘定科目
  • G列：金額
  • H列：税率
  • I列：税額
  • J列：税込金額
  • K列：摘要
  • L列：入金日
  • M列：入金金額
  • N列：入金方法
  • O列：源泉徴収額
  • P列：未収残高
  • Q列：入金ステータス
• L列の入金日からQ列の入金ステータスを範囲指定してテーブル化
• 各セルに以下の数式を入力
  • A2セル：仕訳帳シートの売上に関する取引を抽出

    • =LET(データ,shiwake,年列,1,日付列,2,取引No列,9,クライアント列,23,請求列,24,科目列,5,税抜列,19,税率列,18,税額列,20,金額列,6,摘要列,7,フィルタ済み,FILTER(データ,ISNUMBER(SEARCH(“売上”,INDEX(データ,,科目列)))<em>(INDEX(データ,,8)=”貸方”)</em>(IF(Z1=””,TRUE,INDEX(データ,,年列)=Z1))*(IF(Z2=””,TRUE,INDEX(データ,,クライアント列)=Z2))),クライアント整形,IF(INDEX(フィルタ済み,,クライアント列)=””,””,INDEX(フィルタ済み,,クライアント列)),請求整形,IF(INDEX(フィルタ済み,,請求列)=””,””,INDEX(フィルタ済み,,請求列)),摘要整形,IF(INDEX(フィルタ済み,,摘要列)=””,””,INDEX(フィルタ済み,,摘要列)),CHOOSE({1,2,3,4,5,6,7,8,9,10,11},INDEX(フィルタ済み,,年列),INDEX(フィルタ済み,,日付列),INDEX(フィルタ済み,,取引No列),クライアント整形,請求整形,INDEX(フィルタ済み,,科目列),INDEX(フィルタ済み,,税抜列),INDEX(フィルタ済み,,税率列),INDEX(フィルタ済み,,税額列),INDEX(フィルタ済み,,金額列),摘要整形))

  • L2セル：売上や売掛金が入金された最終日を表示

    • =IF(A2=””,””,IF(E2=””,B2,LET(入金日,MINIFS(shiwake[日付],shiwake[請求書番号],E2,shiwake[勘定科目],”売掛金”,shiwake[貸借区分],”貸方”),IF(入金日=0,””,入金日))))

  • M2セル：クライアントからの入金済みの売上金額を表示

    • =LET(請求,E2,売上,J2,IF(AND(請求=””,売上=””),””,IF(請求=””,売上,LET(売掛行,FILTER(shiwake,(INDEX(shiwake,,24)=請求)<em>(INDEX(shiwake,,5)=”売掛金”)</em>(INDEX(shiwake,,8)=”貸方”)),売掛件数,IFERROR(ROWS(売掛行),0),IF(売掛件数=0,””,LET(取引Nos,INDEX(売掛行,,9),相手行,FILTER(shiwake,ISNUMBER(MATCH(INDEX(shiwake,,9),取引Nos,0))<em>(INDEX(shiwake,,8)=”借方”)</em>(INDEX(shiwake,,4)=”1_資産”)*(INDEX(shiwake,,5)<>”事業主貸”)),金額列,IFERROR(INDEX(相手行,,6),””),IF(COUNTA(金額列)=0,””,SUM(金額列))))))))

  • N2セル：クライアント先から売上が入金された口座などを表示

    • =LET(請求,E2,売掛行,FILTER(shiwake,(INDEX(shiwake,,24)=請求)<em>(INDEX(shiwake,,5)=”売掛金”)</em>(INDEX(shiwake,,8)=”貸方”)),取引Nos,IF(COUNTA(売掛行)=0,{“”},INDEX(売掛行,,9)),相手行,FILTER(shiwake,ISNUMBER(MATCH(INDEX(shiwake,,9),取引Nos,0))<em>(INDEX(shiwake,,8)=”借方”)</em>(INDEX(shiwake,,5)<>”事業主貸”)),科目列,IFERROR(INDEX(相手行,,5),””),IF(COUNTA(科目列)=0,””,TEXTJOIN(” / “,TRUE,UNIQUE(科目列))))

  • O2セル：クライアント先が源泉徴収をした場合の金額を表示

    • =LET(請求,E2,売掛行,FILTER(shiwake,(INDEX(shiwake,,24)=請求)<em>(INDEX(shiwake,,5)=”売掛金”)</em>(INDEX(shiwake,,8)=”貸方”)),取引Nos,IF(COUNTA(売掛行)=0,{“”},INDEX(売掛行,,9)),源泉候補,FILTER(shiwake,ISNUMBER(MATCH(INDEX(shiwake,,9),取引Nos,0))<em>(INDEX(shiwake,,5)=”事業主貸”)</em>(INDEX(shiwake,,8)=”借方”)),IFERROR(SUM(INDEX(源泉候補,,6)),””))

  • P2セル：クライアント先からの未収金がある場合、その額を表示

    • =LET(売上,J2,入金,IFERROR(VALUE(M2),0),源泉,IFERROR(VALUE(O2),0),請求,E2,売掛行,FILTER(shiwake,INDEX(shiwake,,24)=請求),借方合計,SUM(FILTER(INDEX(売掛行,,6),INDEX(売掛行,,5)=”売掛金”,INDEX(売掛行,,8)=”借方”)),貸方合計,SUM(FILTER(INDEX(売掛行,,6),INDEX(売掛行,,5)=”売掛金”,INDEX(売掛行,,8)=”貸方”)),IF(売上=””,””,IF(請求=””,0,IF(ABS(借方合計+貸方合計)=0,0,MAX(0,売上-入金-源泉)))))

  • Q2セル：「完了／一部入金／未入金」で入金ステータス表示

    • =LET(売上,J2,入金,IFERROR(M2,””),源泉,IFERROR(O2,””),残高,P2,請求,E2,IF(売上=””,””,IF(AND(請求<>””,入金=””,源泉=””),”未入金”,IF(残高=0,”完了”,”一部入金”))))

• Y2セルにクライアント一覧を表示する数式をいれ、Y列の名前ボックスを「クライアント一覧」に変更
  • Y2セル：同シートのD列クライアントから自動で一覧をまとめる

    • =SORT(UNIQUE(FILTER(shiwake[クライアント], shiwake[クライアント]<>””)))

• Z1には評価対象年を手入力し、Z2にはクライアント一覧のリストボックスを設定
  • ［データ］タブ＞［データの入力規則］＞［データの入力規則］
  • ［入力値の種類：］を［リスト］＞［元の値：］に「=クライアント一覧」と入力して［OK］をクリック
• B列やL列の日付表記が正しくない場合は設定変更
  • 列を右クリックして［セルの書式設定を開く］
  • ［表示形式］タブの［分類：］を［日付］にして、［種類：］で［〇月〇日］表示パターンを選択して［OK］をクリック
• 売上管理台帳シートの大枠が完成

売上管理台帳シートの作成（インボイス非対応版）

インボイス非対応版「Excelの複式事業簿」に関しては、列名や数式を除き、インボイス対応版と作り方は同じである。

手順が違う箇所のみ以下の折り畳みブロックに撰述しているので、前セクションの「売上管理台帳シートの作成（インボイス対応版）」と合わせて参照し、作成していただきたい。

「Excelの複式事業簿」の売上管理台帳シートの使い方

「Excelの複式事業簿」の売上台帳の大枠が完成したので、ここからはその使い方を撰述していく。

なお、使い方に関してはインボイス対応／非対応ともに同じのため、画像はインボイス対応版のほうで解説。

仕訳帳シートの「クライアント」と「請求書番号」の入力方法

文字通り、「クライアント」には取引先となるクライアント名を、「請求書番号」には売上を請求する際に付与した識別番号を仕訳の際に入力していく。

この時の注意点が、勘定科目が「売上」に関する行にだけ入力するのではなく、関連する取引すべてに「クライアント」と「請求書番号」を入力しておくことである。

そうすることによって、たとえば売上がまだ入金されていない「売掛金」になっている場合などに、その入金金額や残高が自動表示されるようになっている。

また仕訳帳シートにて「クライアント」や「請求書番号」で表示を絞り込んだときに、関連するすべての取引が表示されて、管理がしやすくなる。

入金日や入金ステータスを都度反映させる

この売上管理台帳も現金出納帳や預金出納帳と同じく、テーブル化した箇所に関しては自動的に取引内容が表示される仕組みではないのが仕様となっている。

そのため、取引が新しく追加されている場合は、テーブル化している部分だけを拡張させて表示させる必要がある。

各見出しの解説

入金日

「入金日」は、一部や全額を問わずクライアントから売上の入金があった最終日付が表示されるようになっている。

あくまで「売上」の管理台帳のため、入金があった日付をすべて表示するわけではない。

入金額

クライアントから入金があった実際の金額が表示される。

クライアント側で源泉徴収があった場合や、例えばクラウドワークスなどのシステム利用手数料や振込手数料が引かれている場合は、その金額を引いた、実際に入金があった金額を表示。

源泉徴収額

クライアント側で源泉徴収された上で売上が入金された場合、その仕訳を仕訳帳シートで記帳していれば、源泉徴収額がこちらに反映される。

なおこの「Excelの複式事業簿」では源泉徴収額を「事業主貸」で仕訳するルールとしているので、「未収源泉所得税」で仕訳をする人はO2セルの数式変更が必要となるため注意。

Y列のクライアント一覧とZ2セルのリストについて

Y2セルに数式を入力したことによって表示されるY列とZ2のリスト内のクライアント一覧は、新しくクライアントが追加された場合、自動的にこの一覧に追加される仕様になっている。

Y列に関しては普段は表示しておく必要は特にないため、Y列を右クリックして［非表示］をクリックし、普段は非表示状態にしておくのがおススメ。