零式・Excelの複式事業簿～第4章：仕訳帳～ 

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

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

第4章では、個人事業主にとって文字通りの主要簿となる「仕訳帳」を作成していく。

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

◆

◆

主要簿である仕訳帳について

仕訳帳は「総勘定元帳（そうかんじょうもとちょう）」と並び、会計において最も重要な「主要簿」に位置づけられている。

個人事業主にとって、日々の取引を複式簿記のルールに則って記録することは法律で定められた「義務」であり、この仕訳帳が正しく整備されていなければ、決算書（P/L・B/S）を作成することができない。

しかし「借方・貸方」といった特有の概念など、会計を専門的に学んだ経験や簿記の知識がなければ、仕訳帳を自力で書くことは極めて困難となっている。

その難解さゆえに、多くの事業主が「自分で作るのは無理だ」と判断し、有料の会計ソフトやアプリに頼らざるを得ないのが実情だ。

そこで当サイトでは、日記感覚で取引をExcelに記録していけば仕訳帳が完成していくシステムを目指し、「零式・Excelの複式事業簿」を考案した。

通常、仕訳帳では貸借を左右に分けて記帳していくものなのだが、「零式・Excelの複式事業簿」では上下に分けて自動的に記帳していく。

◆

「零式・Excelの複式事業簿」――「仕訳帳」シートを作成する

「零式・Excelの複式事業簿」の「仕訳帳」シートは、他の補助簿となる様々なシートのデータ源となっている。

「仕入台帳」や「売上管理台帳」など、多くのシートが「仕訳帳」からデータを抽出し、自動表示する。

取引帳が「零式・Excelの複式事業簿」における“心臓”ならば、仕訳帳は“頭脳”といえよう。

• 「shiwake」テーブルの作成
• 「shiwake」テーブルにプルダウンリストと数式を設定
• 「shiwake」テーブルを装飾する

◆

「shiwake」テーブルの作成

• 新しいシートを作成して、名前を「仕訳帳」に変更する
• 各セルに、見出しとなる名称を以下のように入力
  • A1セル：年
    • 取引帳から[年]を自動抽出
  • B1セル：日付
    • 「取引帳」から[日付]を自動抽出
  • C1セル：仕訳番号
    • 「取引帳」の[取引番号]から自動で割り振られる
  • D1セル：貸借区分
    • [分類][勘定科目][金額]の増減から貸借を自動判定
  • E1セル：科目ID
    • [勘定科目]から判定して「勘定科目リスト」から自動抽出
  • F1セル：分類
    • [勘定科目]から判定して「勘定科目リスト」から自動抽出
  • G1セル：勘定科目
    • 「取引帳」から[勘定科目]を自動抽出
  • H1セル：金額
    • 「取引帳」から[金額]を自動抽出
  • I1セル：軽減チェック
    • 軽減税率の判定をプルダウンで行う項目
  • J1セル：申告チェック
    • 申告が必要な非課税の判定をプルダウンで行う項目
  • K1セル：要判定チェック
    • 税区分が要判定で[税率]と[税額]が「⚠」表示になっている際の調整用
  • L1セル：税区分
    • 「勘定科目リスト」から[税区分]を自動抽出
  • M1セル：税率
    • 「税率マスタ」から税率を自動判定
  • N1セル：税額
    • [税率]と[金額]から税額を自動計算
  • O1セル：摘要
    • 「取引帳」から[詳細]を自動抽出
  • P1セル：チェック
    • [仕訳番号]や[貸借区分]から貸借が一致しているか自動チェック
  • Q1セル：取引先
    • 手入力
  • R1セル：インボイス
    • 手入力
  • S1セル：請求書番号
    • 手入力
  • T1セル：相手科目
    • [仕訳番号]で対になっている[勘定科目]を自動表示
  • U1セル：帳票区分
    • 「勘定科目リスト」から自動抽出
  • V1セル：税抜金額
    • [金額]と[税額]から自動計算
  • W1セル：絶対金額
    • [金額]を自動変換
  • X1セル：借方金額
    • [金額]を自動変換
  • Y1セル：貸方金額
    • [金額]を自動変換
  • Z1セル：借方合計
    • 同じ[仕訳番号]内の[借方金額]を自動合算
  • AA1セル：貸方合計
    • 同じ[仕訳番号]内の[貸方金額]を自動合算
• A1からAA1セルを範囲選択して、[挿入]タブから[テーブル]をクリック
  • [先頭行をテーブルの見出しとして使用する]にチェックをつけて[OK]をクリック
• [テーブルデザイン]タブの[テーブル名]に「shiwake」と入力

◆

「shiwake」テーブルにプルダウンリストと数式を設定

• I2セルをクリックして選択し、[データ]タブの[データの入力規則]を開いて、[データの入力規則]をクリック
• [入力値の種類]を「リスト」にして、[元の値]に以下を入力

  • ☑,　

  • ※空白スペースまで含めるので注意
• J2セルをクリックして選択し、[データ]タブの[データの入力規則]を開いて、[データの入力規則]をクリック
• [入力値の種類]を「リスト」にして、[元の値]に以下を入力

  • ☑,　

  • ※空白スペースまで含めるので注意
• K2セルをクリックして選択し、[データ]タブの[データの入力規則]を開いて、[データの入力規則]をクリック
• [入力値の種類]を「リスト」にして、[元の値]に以下を入力

  • 標,軽,非,不

• 各セルに以下の数式を入力していく
  • A2セル（年）

    • =LET(target, ROUNDUP((ROW()-1)/2, 0), no, IFERROR(INDEX(torihiki[取引番号], target), ""), IF(no="", "", INDEX(torihiki[年], target)))

  • B2セル（日付）

    • =LET(target, ROUNDUP((ROW()-1)/2, 0), no, IFERROR(INDEX(torihiki[取引番号], target), ""), IF(no="", "", INDEX(torihiki[日付], target)))

  • C2セル（仕訳番号）

    • =LET(target, ROUNDUP((ROW()-1)/2, 0), no, IFERROR(INDEX(torihiki[取引番号], target), ""), IF(no="", "", no))

  • D2セル（貸借区分）

    • =IF([@仕訳番号]="", "", IF(ISODD(ROW()-1), "借方", "貸方"))

  • E2セル（科目ID）

    • =IF([@勘定科目]="","",XLOOKUP([@勘定科目],kamoku[勘定科目],kamoku[科目ID],""))

  • F2セル（分類）

    • =IF([@勘定科目]="","",XLOOKUP([@勘定科目],kamoku[勘定科目],kamoku[分類],""))

  • G2セル（勘定科目）

    • =IF([@仕訳番号]="","",LET(target,ROUNDUP((ROW()-1)/2,0),type,INDEX(torihiki[取引区分],target),kamokuE,INDEX(torihiki[勘定科目],target),kessaiF,INDEX(torihiki[決済手段],target),isDebit,[@貸借区分]="借方",IF(kamokuE="期首在庫の処理",IF(isDebit,"仕入高","棚卸資産"),IF(kamokuE="期末在庫の計上",IF(isDebit,"棚卸資産","仕入高"),IF(type="支出",IF(isDebit,kamokuE,kessaiF),IF(type="収入",IF(isDebit,kessaiF,kamokuE),IF(type="移動",IF(isDebit,kamokuE,kessaiF),"")))))))

  • H2セル（金額）

    • =IF(OR([@仕訳番号]="",[@分類]=""),"",LET(target,ROUNDUP((ROW()-1)/2,0),absAmt,INDEX(torihiki[金額],target),bunruiHead,LEFT([@分類],1),isDebit,[@貸借区分]="借方",sign,IF(isDebit,IF(OR(bunruiHead="1",bunruiHead="2"),1,-1),IF(OR(bunruiHead="3",bunruiHead="4",bunruiHead="5"),1,-1)),absAmt*sign))

  • L2セル（税区分）

    • =IF([@仕訳番号]="","",LET(detail,[@摘要],IF(OR(ISNUMBER(SEARCH("決算整理",detail)),ISNUMBER(SEARCH("振替",detail))),"不課税",IF([@申告チェック]="☑","非課税",IF([@軽減チェック]="☑","軽減税率",LET(baseTax,XLOOKUP([@勘定科目],kamoku[勘定科目],kamoku[税区分],""),LET(selectTax,SWITCH([@要判定チェック],"標","標準課税","軽","軽減税率","非","非課税","不","不課税",""),IF(selectTax<>"",selectTax,IF(baseTax="要判定","",baseTax)))))))))

  • M2セル（税率）

    • =IF([@勘定科目]="","",LET(baseTax,XLOOKUP([@勘定科目],kamoku[勘定科目],kamoku[税区分],""),inTax,[@税区分],IF(AND(baseTax="要判定",inTax=""),"⚠",IF(inTax="","",LET(k,inTax,d,[@日付],eff,MAXIFS(TaxRate[適用開始日],TaxRate[税区分],k,TaxRate[適用開始日],"<="&d),XLOOKUP(eff,FILTER(TaxRate[適用開始日],TaxRate[税区分]=k),FILTER(TaxRate[税率],TaxRate[税区分]=k),"⚠"))))))

  • N2セル（税額）

    • =IF([@勘定科目]="","",IF(OR([@絶対金額]="",[@税率]=""),"",IF([@税率]="⚠","⚠",IFERROR(IF([@税率]=0,0,ROUNDDOWN([@絶対金額]*[@税率]/(1+[@税率]),0)),""))))

  • O2セル（摘要）

    • =LET(target, ROUNDUP((ROW()-1)/2, 0), no, IFERROR(INDEX(torihiki[取引番号], target), ""), IF(no="", "", INDEX(torihiki[詳細], target)&""))

  • P2セル（チェック）

    • =IF(OR([@借方合計]="",[@貸方合計]=""),"",IF([@借方合計]=[@貸方合計],"OK","⚠️ 不一致"))

  • T2セル（相手科目）

    • = IF(OR([@仕訳番号]="",[@勘定科目]="",[@貸借区分]=""),"",LET(thisNo,[@仕訳番号],thisAccount,[@勘定科目],thisDK,[@貸借区分],matchRow,IFERROR(FILTER([勘定科目],([仕訳番号]=thisNo) * ([勘定科目]<>thisAccount) * ([貸借区分]<>thisDK)),""),c,COUNTA(matchRow),IF(c=0,"",IF(c=1,INDEX(matchRow,1),TEXTJOIN(" / ",TRUE,matchRow)))))

  • U2セル（帳票区分）

    • =IF([@分類]="", "", XLOOKUP([@分類], kamoku[分類], kamoku[帳票区分], ""))

  • V2セル（税抜金額）

    • =IF([@勘定科目]="","",IF(OR([@絶対金額]="",[@税率]="",[@税額]=""),"",IF(OR([@税率]="⚠",[@税額]="⚠"),"⚠",IFERROR([@絶対金額]-[@税額],""))))

  • W2セル（絶対金額）

    • =IF([@金額]="","", ABS([@金額]))

  • X2セル（借方金額）

    • =IF(OR([@貸借区分]="",[@絶対金額]=""),"",IF([@貸借区分]="借方",[@絶対金額],0))

  • Y2セル（貸方金額）

    • =IF(OR([@貸借区分]="",[@絶対金額]=""),"",IF([@貸借区分]="貸方",[@絶対金額],0))

  • Z2セル（借方合計）

    • =IF([@仕訳番号]="","",SUMIFS([借方金額],[仕訳番号],[@仕訳番号]))

  • AA2セル（貸方合計）

    • =IF([@仕訳番号]="","",SUMIFS([貸方金額],[仕訳番号],[@仕訳番号]))

◆

「shiwake」テーブルを装飾する

• [日付]の表示形式を変更する
  • B列をクリックして全体を範囲選択してから、右クリックで[セルの書式設定]を開く
  • [分類]を「日付」に、[種類]を「3月14日」にして[OK]をクリック
• [仕訳番号]を右揃え表示にする
  • C列をクリックして全体を範囲選択してから、Ctrlキーを押しながらC1セルをクリックでC1以外を範囲選択している状態に
  • [ホーム]タブを開いて[配置]内にある右揃えをクリック
• 金額列の表示形式を変更する
  • Ctrlキーを押しながらH列とN列をクリックして、それぞれの列全体を範囲選択
  • Ctrlキーを押しながら、H1セルとN1セルをクリックして、それぞれのセル以外の列全体を範囲選択した状態に
  • 右クリックして[セルの書式設定]を開く
  • [種類]を「通貨」に、[記号]を「なし」に、[負の数の表示形式]を「1,234」にして[OK]をクリック
• 普段使用しない列を非表示にする
  • U列をクリックし、Shiftキーを押しながらAA列をクリックで範囲選択して、右クリックから[非表示]を選択

◆

「零式・Excelの複式事業簿」――「仕訳帳」シートの使い方について

『零式・Excelの複式事業簿～第3章：取引帳～』でも解説したように、「零式・Excelの複式事業簿」の「仕訳帳」シートは「取引帳」シートと連動して使用する場合がある。

そのため、「仕訳帳」シートの使い方については『零式・Excelの複式事業簿～Chapter1：取引帳と仕訳帳の使い方～』で撰述する。