零式・Excelの複式事業簿～補助簿編：売上管理台帳～

Excelマネジメント

自営業の第一歩を踏み出したとき、まずぶつかる壁の一つが「帳簿をどうするか」という問題。

簿記の知識なんてないし、自分で帳簿をつけるのは難しそう。
でも会計ソフトってちょっと手が出ないし……

そんな問題を解決するため、簿記の知識が無くとも、日々の取引を見たまま、ありのままに1ずつ行入力していくだけで、システムが裏側で正規の「複式簿記」を自動生成してくれる「零式・Excelの複式事業簿」を構築していく。

補助簿とは、仕訳帳や総勘定元帳といったメインの帳簿（主要簿）では追い切れない特定の科目（売掛金など）の詳細な内訳を記録しておくための「専用のサブノート」。

今回はその補助簿の中から、売上の入金額や入金状況を確認するための売上管理台帳を作成していく。

～零式・Excelの複式事業簿～
ガイドライン

――作成マニュアル――

――利用マニュアル――

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

本連載で提供する「Excelの複式事業簿（以下、本システム）」および関連する解説記事は、個人事業主の帳簿作成業務をサポートするためのツールです。ご利用にあたっては、以下の事項にご同意いただいた上で、ご自身の責任において運用をお願いいたします。

1. 本システムは補助ツールであり、税務上の保証を行うものではありません
本システムは、入力されたデータに基づき機械的に複式簿記の計算および集計を行う「計算補助ツール」です。出力された数値の正確性や、それが税法上正当なものであるか（青色申告の要件を満たすか等）を完全に保証するものではございません。

2. 最終的な申告責任は事業主様に帰属します
確定申告における最終的な内容確認および納税の責任は、すべて事業主様ご自身に帰属いたします。本システムを利用して作成した帳簿や決算書によって、万が一税務調査で指摘を受けたり、追徴課税等の不利益が生じた場合でも、当サイト（撰述オートマタ）および筆者は一切の責任を負いかねますので、あらかじめご了承ください。

3. 事業ごとの個別事情には対応できない場合がございます
本連載の解説は、一般的な個人事業主を想定した「基本ルール」に基づいています。事業の形態や規模、適用される特例などによっては、記事で触れていない特殊な処理や追加の仕訳が必要になるケースがございます。自身の事業における特有の税務処理について判断に迷われる場合は、必ず管轄の税務署や顧問税理士などの専門家にご確認ください。

4. 独自のカスタマイズに関するサポートについて
本システムは、関数や数式が精密に連携して動くよう設計されています。ユーザー様ご自身でシートの保護を解除したり、列を挿入したり、数式を書き換える等のカスタマイズを行った場合、思わぬ計算エラーやデータの消失を招くリスクがございます。万が一システムが崩壊した場合、原因の特定や復旧は各自でご対応いただくことになりますので、確かな知識をお持ちでない段階での独自の改変は控え、可能な限りデフォルトの構造のまま大切に運用していただくようお願いいたします。

◆

◆

零式・Excelの複式事業簿――売上管理台帳の役割

補助簿には売上帳というものがあり、売上の発生や売掛金（入金予定の売上）の回収状況を管理するために用いられる。

「いつ・誰に・いくら請求したか」を整理し、入金漏れを防ぐための帳簿だ。

しかし実務では、請求書番号や源泉徴収税額、分割入金など、管理すべき情報はさらに多い。

「零式・Excelの複式事業簿」では、こうした実務まで踏まえた「売上管理台帳」として設計していく。

◆

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

売上管理台帳の仕組みは、取引帳（＝仕訳帳）にて選択された「売上高」の取引を抽出し、未入金や入金完了の入金ステータスから、入金額・源泉徴収税額などを自動表示するものになっている。

◆

評価年や見出しを入力していく

  • 新しいシートを作成して、名前を「売上管理台帳」に変更する
  • 各セルに見出しとなる名称を入力
    • A1：評価年：
    • A2：取引先：
    • A3：日付　B3：仕訳番号　C3：取引先　D3：請求書番号
    • E3：摘要　F3：勘定科目　G3：金額　H3：税率
    • I3：税額　J3：税込金額　K3：源泉徴収税額　L3：手数料
    • M3：未収残高　N3：入金日　O3：入金額　P3：入金方法
    • Q3：入金ステータス
  • A4セルを選択した状態で、[表示]タブの[ウィンドウ枠の固定]から1行目のウィンドウ枠を固定しておく
  • K3セルからQ3セルまでを範囲選択して、[挿入]タブから[テーブル]をクリック
    • [先頭行をテーブルの見出しとして使用する]にチェックをつけて[OK]をクリック
  • [テーブルデザイン]タブを開き、[テーブル名:]に「tbl_receivable」と入力
評価年や見出しを入力
  • ①
    新しいシートを作成して、名前を「売上管理台帳」に変更する
  • ②
    各セルに見出しとなる名称を入力
  • ③
    A4セルを選択した状態で、[表示]タブの[ウィンドウ枠の固定]から1行目のウィンドウ枠を固定しておく
  • ④
    K3からQ3セルまでを範囲選択して、[挿入]タブから[テーブル]をクリック

    [先頭行をテーブルの見出しとして使用する]にチェックをつけて[OK]をクリック

  • ⑤
    [テーブルデザイン]タブを開き、[テーブル名:]に「tbl_receivable」と入力

◆

数式を設定していく

数式を設定
  • ①
    以下の各セルにそれぞれ数式を入力
    • A4：「仕訳帳」シートから売上に関する取引データを抽出 
      • =LET(n,ROWS(shiwake[日付]),mask,IF(shiwake[貸借区分]="貸方",IF(shiwake[分類]="4_収益",IF(ISNUMBER(SEARCH("売上",shiwake[勘定科目])),IF(IF($B$1="",TRUE,shiwake[年]=$B$1),IF($B$2="",TRUE,shiwake[取引先]=$B$2),FALSE),FALSE),FALSE),FALSE),idx,FILTER(SEQUENCE(n),mask,""),IF(idx="","",LET(v_tori,INDEX(shiwake[取引先],idx),v_inv,INDEX(shiwake[請求書番号],idx),v_tekiyo,INDEX(shiwake[摘要],idx),CHOOSE({1,2,3,4,5,6,7,8,9,10},INDEX(shiwake[日付],idx),INDEX(shiwake[仕訳番号],idx),IF(v_tori="","",v_tori),IF(v_inv="","",v_inv),IF(v_tekiyo="","",v_tekiyo),INDEX(shiwake[勘定科目],idx),INDEX(shiwake[税抜金額],idx),INDEX(shiwake[税率],idx),INDEX(shiwake[税額],idx),INDEX(shiwake[金額],idx)))))
    • K4：「仕訳帳」シートから売上取引の源泉徴収税額に該当する金額を抽出 
      • =IF($A4="","",LET(inv,$D4,s_no,$B4,amt,IF(inv<>"",SUM(SUMIFS(shiwake[絶対金額],shiwake[請求書番号],inv,shiwake[勘定科目],{"未収源泉徴収税額","事業主貸"},shiwake[貸借区分],"借方")),SUM(SUMIFS(shiwake[絶対金額],shiwake[仕訳番号],s_no,shiwake[勘定科目],{"未収源泉徴収税額","事業主貸"},shiwake[貸借区分],"借方"))),IF(amt=0,"",amt)))
    • L4：「仕訳帳」シートから売上取引の手数料に該当する金額を抽出 
      • =IF($A4="","",LET(inv,$D4,s_no,$B4,amt,IF(inv<>"",SUMIFS(shiwake[絶対金額],shiwake[請求書番号],inv,shiwake[勘定科目],CHAR(42)&"手数料"&CHAR(42),shiwake[貸借区分],"借方"),SUMIFS(shiwake[絶対金額],shiwake[仕訳番号],s_no,shiwake[勘定科目],CHAR(42)&"手数料"&CHAR(42),shiwake[貸借区分],"借方")),IF(amt=0,"",amt)))
    • M4：売上の未収分の金額を自動計算表示 
      • =IF($J4="","",LET(due,MAX(0,IFERROR(--$J4,0)-IFERROR(--[@源泉徴収税額],0)-IFERROR(--[@手数料],0)),paid,IF($D4="",IFERROR(--[@入金額],0),SUMPRODUCT(($D$4:$D$5000=$D4)*IFERROR(--$O$4:$O$5000,0))),MAX(0,due-paid)))
    • N4：「仕訳帳」シートから売上が入金された日付を抽出 
      • =IF($A4="","",IF($D4="",$A4,LET(dt,MINIFS(shiwake[日付],shiwake[請求書番号],$D4,shiwake[勘定科目],"売掛金",shiwake[貸借区分],"貸方"),IF(dt=0,"",dt))))
    • O4：「仕訳帳」シートから入金された売上金額を抽出 
      • =IF($A4="","",LET(inv,$D4,s_no,$B4,amt,IF(inv<>"",SUMIFS(shiwake[絶対金額],shiwake[請求書番号],inv,shiwake[分類],"1_資産",shiwake[勘定科目],"<>未収源泉徴収税額",shiwake[勘定科目],"<>事業主貸",shiwake[勘定科目],"<>売掛金",shiwake[貸借区分],"借方"),SUMIFS(shiwake[絶対金額],shiwake[仕訳番号],s_no,shiwake[分類],"1_資産",shiwake[勘定科目],"<>未収源泉徴収税額",shiwake[勘定科目],"<>事業主貸",shiwake[勘定科目],"<>売掛金",shiwake[貸借区分],"借方")),IF(amt=0,"",amt)))
    • P4：「仕訳帳」シートから売上の入金先に該当する勘定科目を抽出 
      • =IF($J4="","",LET(n,ROWS(shiwake[仕訳番号]),mode,IF($D4="","cash","ar"),idxPay_cash,FILTER(SEQUENCE(n),IF(shiwake[仕訳番号]=$B4,IF(shiwake[貸借区分]="借方",IF(shiwake[分類]="1_資産",IF(shiwake[勘定科目]<>"未収源泉徴収税額",IF(shiwake[勘定科目]<>"事業主貸",TRUE,FALSE),FALSE),FALSE),FALSE),FALSE),""),idxAR,FILTER(SEQUENCE(n),IF(shiwake[請求書番号]=$D4,IF(shiwake[勘定科目]="売掛金",IF(shiwake[貸借区分]="貸方",TRUE,FALSE),FALSE),FALSE),""),nos,IF(mode="ar",UNIQUE(INDEX(shiwake[仕訳番号],idxAR)),""),idxPay_ar,IF(mode="ar",FILTER(SEQUENCE(n),IF(ISNUMBER(MATCH(shiwake[仕訳番号],nos,0)),IF(shiwake[貸借区分]="借方",IF(shiwake[分類]="1_資産",IF(shiwake[勘定科目]<>"未収源泉徴収税額",IF(shiwake[勘定科目]<>"事業主貸",TRUE,FALSE),FALSE),FALSE),FALSE),FALSE),""),""),lst,IF(mode="cash",INDEX(shiwake[勘定科目],idxPay_cash),INDEX(shiwake[勘定科目],idxPay_ar)),IF(IFERROR(ROWS(lst),0)=0,"",TEXTJOIN(" / ",TRUE,UNIQUE(lst)))))
    • Q4：未入金・一部入金・過入金・入金完了・即時入金の表示切替 
      • =IF($J4="","",LET(due,MAX(0,IFERROR(--$J4,0)-IFERROR(--[@源泉徴収税額],0)-IFERROR(--[@手数料],0)),paid,IF($D4="",IFERROR(--[@入金額],0),SUMIFS($O$4:$O$5000,$D$4:$D$5000,$D4)),IF(paid>due,"⚠ 過入金",IF(paid=0,"未入金",IF(paid<due,"一部入金",IF($D4="","即時入金","完了"))))))

◆

表示形式を変更してシートを見やすいように装飾する

  • [日付]列の表示形式を変更する
    • A列全体を範囲選択してから、Ctrlキーを押しながらA1・A2・A3セルをクリック
    • 右クリックから[セルの書式設定]を開く
    • [分類]から「日付」を選択し、[種類]を「2012年3月14日」にして[OK]をクリック
  • 同様の手順で、[入金日]の表示形式も同じものに変更する
  • [取得価額]の表示形式を変更する
    • 手順①と同じ手順で、G1・G2・G3セル以外のG列を範囲選択し、右クリックから[セルの書式設定]を開く
    • [分類]から「通貨」を選択し、[記号]を「なし」に、[負の数の表示形式]を「1,234」にして[OK]をクリック
  • 同様の手順で、他の金額表示列も表示形式を変更していく
    • 以下の列は金額表示列となるため、手順②と同じ手順で表示形式を通貨に変更する
      • I列：税額
      • J列：税込金額
      • K列：源泉徴収税額
      • L列：手数料
      • M列：未収残高
      • O列：入金額
  • A3セルからJ3セルの見出しを装飾する
    • [ホーム]タブの[フォント]や[配置]を使って装飾
タイムラインのタイトル
  • ①
    [日付]列の表示形式を変更する

    A列全体を範囲選択してから、Ctrlキーを押しながらA1・A2・A3セルをクリック

    右クリックから[セルの書式設定]を開く

    [分類]から「日付」を選択し、[種類]を「2012年3月14日」にして[OK]をクリック

  • ②
    同様の手順で、[入金日]の表示形式も同じものに変更する
  • ③
    [金額]の表示形式を変更する

    手順①と同じ手順で、G1・G2・G3セル以外のG列を範囲選択し、右クリックから[セルの書式設定]を開く

    [分類]から「通貨」を選択し、[記号]を「なし」に、[負の数の表示形式]を「1,234」にして[OK]をクリック

  • ④
    同様の手順で、他の金額表示列も表示形式を変更していく
  • ⑤
    A3セルからJ3セルの見出しを装飾する

◆

零式・Excelの複式事業簿――売上管理台帳の使い方

売上管理台帳では、勘定科目リストにて[分類]が「4_収益」の中で「売上」の文字が入る勘定科目を取引帳（仕訳帳）にて使用した際、その取引を売上管理台帳に自動抽出する仕組みとなっている。

「零式・Excelの複式事業簿」の場合、初期の勘定科目リストでは「売上高」のみを用意しているため、この「売上高」の取引が発生した際に抽出するということになる。

もし売上の詳細を分けたいために勘定科目リストに自身で別の売上勘定科目を追加している場合、勘定科目名に必ず「売上」の文字を入力しておこう。

◆

取引帳に売上が入力されると、自動的に売上管理台帳に反映

日々の取引で売上が発生した場合、通常通りまずは取引帳にその内容を入力していく。

売上は通常、商品を納品したり成果物を相手に納めたその時点で帳簿に記帳しなければならないため、即時入金ではない場合でも 「売掛金」 商品やサービスを納品して、その代金を後日受け取る権利（売上債権）のこと。現金を回収するまでの間、一時的に「資産」として計上される勘定科目。 ※という勘定科目を使って取引帳に入力をしておく。

売上に関する取引が取引帳（仕訳帳）に入力されると、自動的に売上管理台帳にもその取引データが自動抽出される。

後日無事に売上分の金額が銀行などに入金されると、その取引も入力する。この時「以前売掛金にしていた分の金額が銀行へ入金された」という取引のため、[取引区分]は「収入」ではなく「移動」になるので注意。

また、入金された際に源泉徴収税額が引かれていたり、手数料などが引かれていたりする場合は、その取引も忘れずに入力しておかなければならない。

取引帳に入力をしたら、忘れずに仕訳帳でも入力が反映されているか確認。

軽減税率や要判定チェックなど、税区分の調整が必要ないかや、取引先・請求書番号の入力も忘れずに行っておこう。

すべての取引を間違えずに入力し終えると、売上管理台帳での入金ステータスが「入金完了」になる。

なお、売上が後日入金の売掛金にならず、その場で即日で現金受け取りができた場合などは、その取引を入力すれば、売上管理台帳では[入金ステータス]が「即時入金」できちんと反映される。

◆

テーブル部分は自動拡張されないため、都度自身で拡張が必要

売上管理台帳の売上データ自体は、取引帳（仕訳帳）に入力が完了すると自動的に反映される仕組みとなっているが、テーブル部分であるK列からQ列は自動でテーブルを拡張することができない。

そのため、新しい売上データが反映されたら、都度自身でテーブル部分を拡張させておこう。

<<前のページ

次のページ>>

タイトルとURLをコピーしました