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

駆け出しの自営業者（個人事業主）が、自営を始めたはいいものの「帳簿をどう付け、どう保存すればよいのかわからない」という状況に陥ったときの一助となるよう、Excelで管理できる複式簿記――「新版・Excelの複式事業簿」。

今回は補助簿編のひとつ、売上管理台帳を作成していく。

～新版・Excelの複式事業簿～
作成ガイドライン

第0章：ゼロから作るExcelの複式事業簿
第1章：勘定科目リスト
第2章：税率マスタ
第3章：仕訳帳
第4章：記帳方法の解説
第5章：消費税集計
第6章：万能表
第7章：全体集計
第8章：総勘定元帳

補助簿編
- 固定資産台帳
- 出納帳
- 売上管理台帳（※この記事）
- 証憑管理台帳

最終章：決算整理仕訳

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

業種や取引内容、課税方式等によって必要な勘定科目や処理は異なるため、本記事の内容がすべてのケースに当てはまるものではありません。判断に迷う場合は、国税庁の公式情報を確認した上で、必要に応じて税理士などの専門家にご相談いただくなど、ご自身の状況に照らしてご対応ください。

◆

「新版・Excelの複式事業簿」での売上管理台帳の役割
「取引先リスト」シートを作成する
「売上管理台帳」シートを作成する
「売上管理台帳」シートの使い方

「新版・Excelの複式事業簿」での売上管理台帳の役割

補助簿には売上帳というものがあり、売上の発生や売掛金の回収状況を管理するために用いられる。「いつ・誰に・いくら請求したか」を整理し、入金漏れを防ぐための帳簿だ。

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

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

◆

「取引先リスト」シートを作成する

売上管理台帳を作成する前に、取引先を一覧でまとめておく「取引先リスト」シートを作成しておく。

取引先に関しては必ずしも「取引先リスト」シートに入力しないといけないわけではなく、都度仕訳帳の[取引先]列に手入力してもOK。

「取引先リスト」シートに追加した取引先は、このあと作成する「売上管理台帳」シートにてその取引先のみに絞って表示することができるようになる。

新しいシートを作成して、名前を「取引先リスト」に変更する
1行目のA列から順番に、見出しとなる名称を入力していく
- A1:取引先ID　B1:名称　C1:担当者　D1:メールアドレス
  E1:電話番号　F1:住所　G1:契約ステータス　H1:契約開始日
  I1:契約終了日　J1:締め日　K1:支払時期　L1:源泉徴収
  M1:インボイス番号　N1:メモ
入力したA1からN1までの見出しを範囲選択して、[挿入]タブから[テーブル]をクリック
[先頭行をテーブルの見出しとして使用する]にチェックをつけ[OK]をクリック
[テーブルデザイン]タブを開いて、[テーブル名:]に「tbl_client」と入力
L2セルを選択して、[データ]タブを開き[データの入力規則]から[データの入力規則]をクリック
[入力値の種類:]から[リスト]を選択して、[元の値:]に以下のように入力
- 0％,10.21％,20.42％
- [OK]をクリックする
[源泉徴収]列で、プルダウンリストにて源泉所得税率を選択できるようになる
B列の見出し以外を範囲選択して、名前ボックスに「取引先」と入力

画像つきの解説はこちら

「取引先リスト」シートの作成

①
新しいシートを作成して、名前を「取引先リスト」に変更する
②
1行目のA列から順番に、見出しとなる名称を入力していく
③
入力したA1からN1までの見出しを範囲選択して、[挿入]タブから[テーブル]をクリック
④
[先頭行をテーブルの見出しとして使用する]にチェックをつけ[OK]をクリック
⑤
[テーブルデザイン]タブを開いて、[テーブル名:]に「tbl_client」と入力
⑥
L2セルを選択して、[データ]タブを開き[データの入力規則]から[データの入力規則]をクリック
⑦
[入力値の種類:]から[リスト]を選択して、[元の値:]に以下のように入力
```
0％,10.21％,20.42％
```
[OK]をクリックする
⑧
[源泉徴収]列で、プルダウンリストにて源泉所得税率を選択できるようになる
⑨
B列の見出し以外を範囲選択して、名前ボックスに「取引先」と入力

◆

「売上管理台帳」シートを作成する

メインとなる売上管理台帳のシートを作成していく。

仕組みとしては、仕訳帳から勘定科目[売上]を使用した取引を抽出し、取引先や売上金を自動表示。

またその[売上]の貸借が[売掛金]の場合、後日売掛金が入金された日付や金額も自動的に反映されるようになっている。

新しいシートを作成して、名前を「売上管理台帳」に変更する
各セルに、以下のように名称を入力していく
- A1:評価年:　A2:取引先:
- A3:日付　B3:仕訳番号　C3:取引先　D3:請求書番号　E3:摘要
  F3:勘定科目　G3:金額　H3:税率　I3:税額　J3:税込金額
  K3:源泉徴収額　L3:手数料　M3:未収残高　N3:入金日
  O3:入金額　P3:入金方法　Q3:入金ステータス
B2セルを選択して、[データ]タブを開き、[データの入力規則]から[データの入力規則]をクリック
[入力値の種類:]から[リスト]を選択し、[元の値:]に「=取引先」と入力して[OK]をクリック
「取引先リスト」シートに入力している取引先が、プルダウンリストから選べるようになる
K3からQ3の見出しを範囲選択して、[挿入タブ]を開き[テーブル]をクリック
[先頭行をテーブルの見出しとして使用する]にチェックをつけ[OK]をクリック
[テーブルデザイン]タブを開いて、[テーブル名:]に「tbl_receivable」と入力
A3からJ3までを見出し風にデザインする

各セルに以下の数式を入力する

A4:「仕訳帳」シートから[売上]の取引を自動抽出

=LET(n,ROWS(shiwake[日付]),mask,(shiwake[貸借区分]="貸方")*(shiwake[分類]="4_収益")*ISNUMBER(SEARCH("売上",shiwake[勘定科目]))*(IF($B$1="",TRUE,shiwake[年]=$B$1))*(IF($B$2="",TRUE,shiwake[取引先]=$B$2)),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($D4="","",LET(inv,$D4,tgtAcc,{"未収源泉徴収税額","事業主貸"},nos,IFERROR(UNIQUE(FILTER(shiwake[仕訳番号],(shiwake[請求書番号]=inv)*(shiwake[勘定科目]="売掛金")*(shiwake[貸借区分]="貸方"),"")),""),amt,IF(nos="","",SUMPRODUCT(--ISNUMBER(MATCH(shiwake[仕訳番号],nos,0))*--(shiwake[貸借区分]="借方")*--ISNUMBER(MATCH(shiwake[勘定科目],tgtAcc,0))*shiwake[絶対金額])),IF(amt=0,"",amt)))

L4:売上の入金の際に手数料が発生した場合に表示

「勘定科目リスト」シートにて、売上の入金と同じ仕訳番号にある”手数料”の文字が入る勘定科目の金額を反映

=IF($J4="","",LET(isFee,--(shiwake[貸借区分]="借方")*--ISNUMBER(SEARCH("手数料",shiwake[勘定科目])),sCash,SUMPRODUCT(--(shiwake[仕訳番号]=$B4)*isFee*shiwake[絶対金額]),hasAR,IF($D4="",0,COUNTIFS(shiwake[請求書番号],$D4,shiwake[勘定科目],"売掛金",shiwake[貸借区分],"貸方")),nos,IF(hasAR=0,"",UNIQUE(FILTER(shiwake[仕訳番号],(shiwake[請求書番号]=$D4)*(shiwake[勘定科目]="売掛金")*(shiwake[貸借区分]="貸方"),""))),sAR,IF(hasAR=0,0,SUMPRODUCT(--ISNUMBER(MATCH(shiwake[仕訳番号],nos,0))*isFee*shiwake[絶対金額])),s,IF($D4="",sCash,sAR),IF(s=0,"",s)))

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($J4="","",IF($D4="",LET(s,SUMPRODUCT(--(shiwake[仕訳番号]=$B4)*--(shiwake[貸借区分]="借方")*--(shiwake[分類]="1_資産")*--(shiwake[勘定科目]<>"未収源泉徴収税額")*--(shiwake[勘定科目]<>"事業主貸")*ABS(shiwake[金額])),IF(s=0,"",s)),LET(tx,shiwake[仕訳番号],amt,ABS(shiwake[金額]),isPay,--(shiwake[貸借区分]="借方")*--(shiwake[分類]="1_資産")*--(shiwake[勘定科目]<>"未収源泉徴収税額")*--(shiwake[勘定科目]<>"事業主貸"),hasAR,COUNTIFS(shiwake[仕訳番号],tx,shiwake[請求書番号],$D4,shiwake[勘定科目],"売掛金",shiwake[貸借区分],"貸方"),s,SUMPRODUCT(isPay*--(hasAR>0)*amt),IF(s=0,"",s))))

P4:売上の入金方法を自動表示

=IF($J4="","",LET(n,ROWS(shiwake[仕訳番号]),mode,IF($D4="","cash","ar"),idxPay_cash,FILTER(SEQUENCE(n),(shiwake[仕訳番号]=$B4)*(shiwake[貸借区分]="借方")*(shiwake[分類]="1_資産")*(shiwake[勘定科目]<>"未収源泉徴収税額")*(shiwake[勘定科目]<>"事業主貸"),""),idxAR,FILTER(SEQUENCE(n),(shiwake[請求書番号]=$D4)*(shiwake[勘定科目]="売掛金")*(shiwake[貸借区分]="貸方"),""),nos,IF(mode="ar",UNIQUE(INDEX(shiwake[仕訳番号],idxAR)),""),idxPay_ar,IF(mode="ar",FILTER(SEQUENCE(n),ISNUMBER(MATCH(shiwake[仕訳番号],nos,0))*(shiwake[貸借区分]="借方")*(shiwake[分類]="1_資産")*(shiwake[勘定科目]<>"未収源泉徴収税額")*(shiwake[勘定科目]<>"事業主貸"),""),""),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列全体を範囲選択して右クリックし、セルの書式設定から日付表示を正しく表示させる
- [分類:]から[日付]を選択し、[種類:]から[○月×日]表示を選んで[OK]
A4セルをクリックして[表示タブ]から[ウィンドウ枠の固定]をする
「売上管理台帳」シートの完成

画像つきの解説はこちら

「売上管理台帳」シートの作成

①
新しいシートを作成して、名前を「売上管理台帳」に変更する
②
各セルに、画像のように名称を入力していく
③
B2セルを選択して、[データ]タブを開き、[データの入力規則]から[データの入力規則]をクリック
④
[入力値の種類:]から[リスト]を選択し、[元の値:]に「=取引先」と入力して[OK]をクリック
⑤
「取引先リスト」シートに入力している取引先が、プルダウンリストから選べるようになる
⑥
K3からQ3の見出しを範囲選択して、[挿入タブ]を開き[テーブル]をクリック
⑦
[先頭行をテーブルの見出しとして使用する]にチェックをつけ[OK]をクリック
⑧
[テーブルデザイン]タブを開いて、[テーブル名:]に「tbl_receivable」と入力
⑨
A3からJ3までを見出し風にデザインする
⑩
各セルに手順⑩の数式を入力する
⑪
A列全体を範囲選択して右クリックし、セルの書式設定から日付表示を正しく表示させる

[分類:]から[日付]を選択し、[種類:]から[○月×日]表示を選んで[OK]
⑫
A4セルをクリックして[表示タブ]から[ウィンドウ枠の固定]をする
ラベル
「売上管理台帳」シートの完成