新版・Excelの複式事業簿～第3章：仕訳帳～

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

第2章では、簿記の要となる主要簿・仕訳帳のシートを作成していく。

後ほど「消費税集計」シートや「万能表」シートで使うことになる見出しや数式も先に入力をしていく。

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

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

◆

◆

「仕訳帳」シートを作成する

「新・Excelの複式事業簿」でも仕訳帳は要となるため、後の各集計表や補助簿作成の際に必要になる項目をすべて先に用意しておく。

ここで作成した「仕訳帳」シートを参照して、他のシートのデータも自動反映されていくようになる。

• 仕訳をしていくためのテーブルを作成する
• テーブルに数式を入力していく
• リストを設定する
• 金額の表示形式の変更

◆

手順①：仕訳をしていくためのテーブルを作成する

• 新しいシートを作成して、名前を「仕訳帳」に変更する
• 1行目に、A列から順番に見出しとなる単語を入力していく
  • 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:貸方合計
• 入力した見出しを範囲選択し、[挿入タブ]の[テーブル]をクリック
• [先頭行をテーブルの見出しとして使用する]にチェックをつけて[OK]をクリック。テーブル化する
• [テーブルデザイン]タブの[テーブル名:]に「shiwake」を入力

◆

手順②：テーブルに数式を入力していく

作成したテーブルと見出しに、各数式を入力していく。

• 2行目の、各セルに以下の数式を入力していく
  • A2：年の自動入力

    • =YEAR(B2)

  • D2：貸借区分の自動判定

    • =IF(OR([@分類]="",[@金額]=""),"",IF([@分類]="6_その他","⚠",IF(OR(AND([@分類]="1_資産",[@金額]>0),AND([@分類]="2_負債",[@金額]>0),AND([@分類]="3_純資産",[@金額]<0),AND([@分類]="4_収益",[@金額]<0),AND([@分類]="5_費用",[@金額]<0)),"借方","貸方")))

  • E2：科目IDの自動入力

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

  • F2：資産や負債など分類の自動入力

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

  • K2：税区分の自動判定

    • =IF([@勘定科目]="","",LET(baseTax,XLOOKUP([@勘定科目],kamoku[勘定科目],kamoku[税区分],""),IF(baseTax="要判定","",IF([@軽減チェック]="☑","軽減税率",baseTax))))

  • L2：税率の自動入力

    • =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),"⚠"))))))

  • M2：税額の自動計算

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

  • O2：貸借が一致しているかの自動判定

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

  • S2：相手科目の自動抽出

    • =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)))))

  • T2：帳票区分の自動入力

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

  • U2：税抜金額の自動計算

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

  • V2：他の金額計算のための絶対金額

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

  • W2：貸借のどちら側の金額かの自動判定

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

  • X2：貸借のどちら側の金額かの自動判定

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

  • Y2：同じ仕訳番号内での借方合計金額の自動計算

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

  • Z2：同じ仕訳番号内での貸方合計金額の自動計算

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

◆

手順③：リストを設定する

作成したテーブルの中で、リストを利用する見出しにリスト設定をしていく。

◆

手順④：金額の表示形式の変更

仕訳帳のH列[金額]やM列[税額]など金額に関する項目は、数字の表示形式を通貨方式にしておくと、金額としてわかりやすくなる。

◆

手順⑤：不要な列の非表示

S列の見出し[相手科目]からZ列の[貸方合計]までは、自動計算や自動抽出のために必要とする見出しのため、通常時には使用することがない。

そのため普段は非表示設定にしておくほうが、仕訳帳の見た目をスッキリと保つことができるためおススメ。

次のページ>>新版・Excelの複式事業簿～第4章：記帳方法の解説～