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

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

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

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

今回はその補助簿の中から、領収書などを管理するための証憑管理台帳の作成方法を撰述していく。

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

◆

◆

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

「証憑（しょうひょう）」とは、取引や契約の事実・内容を証明する証拠書類のことで、領収書や請求書などがそれにあたる。

証憑管理台帳は、法定の補助簿ではないが、領収書や請求書などの証憑は、税務上きわめて重要な根拠資料であり、適切な保存と管理が求められている。

保存場所をすぐに参照できるよう、証憑管理台帳としてシート化しておこう。

◆

零式・Excelの複式事業簿――証憑管理台帳の作成

「零式・Excelの複式事業簿」の証憑管理台帳では、二枚のシートを使用して一つの台帳とする『データベース連動型』システムとなっている。

すべての年のデータをただひたすら蓄積していく“手入力専用のマスターデータ”となる「証憑入力」シートと、指定した評価年のデータだけを抽出し、状況をチェックする“閲覧・出力専用のダッシュボード”である「証憑管理」シートに分ける。

• 「証憑入力」シートの作成
  • シートを作成して数式とプルダウンを設定する
  • 見やすいように表示形式を変更する
  • [確認区分]を選択したときの表示パターンを設定する
• 「証憑管理」シートの作成

◆

「証憑入力」シートの作成

「証憑入力」シートでは、勘定科目リストにおいて[証憑区分]を[TRUE]に設定した勘定科目が取引帳で使用されたとき、本シートにその取引を自動抽出する仕組みとする。

ここに表示された取引の証憑種別や保存場所を入力しておくことで、「証憑管理」シートからデータを自由に閲覧できるようにする。

• シートを作成して数式とプルダウンを設定する
• 見やすいように表示形式を変更する
• [確認区分]を選択したときの表示パターンを設定する

◆

シートを作成して数式とプルダウンを設定する

• 新しいシートを作成して、名前を「証憑入力」に変更する
• A1セルからK1セルまで、以下のように見出しとなる名称を入力
  • A1：日付　B1：仕訳番号　C1：勘定科目　D1：金額
  • E1：摘要　F1：インボイス　G1：請求書番号
  • H1：確認区分　I1：証憑種別　J1：保存場所　K1：備考
• A2セルをクリックして、[表示]タブから[ウィンドウ枠の固定]を開き、[ウィンドウ枠の固定]をクリック
• A2セルに以下の数式を入力する

  • =LET(need,XLOOKUP(shiwake[勘定科目],kamoku[勘定科目],kamoku[証憑区分],FALSE),mask,(need=TRUE),idx,FILTER(SEQUENCE(ROWS(shiwake[仕訳番号])),mask,""),IF(idx="","",LET(no_raw,INDEX(shiwake[仕訳番号],idx),d_raw,INDEX(shiwake[日付],idx),acc_raw,INDEX(shiwake[勘定科目],idx),amt_raw,IFERROR(ABS(--INDEX(shiwake[金額],idx)),0),te_raw,INDEX(shiwake[摘要],idx),inv_raw,INDEX(shiwake[インボイス],idx),bill_raw,INDEX(shiwake[請求書番号],idx),no,UNIQUE(no_raw),dt,XLOOKUP(no,no_raw,d_raw,"",0,1),ac,XLOOKUP(no,no_raw,acc_raw,"",0,1),am,MMULT(--(no=TRANSPOSE(no_raw)),amt_raw),tx,LET(v,XLOOKUP(no,no_raw,te_raw,"",0,1),IF(v=0,"",v)),iv,LET(v,XLOOKUP(no,no_raw,inv_raw,"",0,1),IF(v=0,"",v)),bl,LET(v,XLOOKUP(no,no_raw,bill_raw,"",0,1),IF(v=0,"",v)),CHOOSE({1,2,3,4,5,6,7},dt,no,ac,am,tx,iv,bl))))

• H列をクリックしてH列全体を範囲選択する
• Ctrlキーを押しながらH1セルをクリックし、H1セル以外のH列を範囲選択した状態で、右クリックして[セルの書式設定]を開く
• [入力値の種類]を「リスト」にして、[元の値]に以下のように入力して[OK]をクリック

  • 確認,不要

• [確認区分]で「確認」「不要」のプルダウンが選択できるように

◆

見やすいように表示形式を変更する

• A列をクリックして、A列全体を範囲選択した状態にする
• Ctrlキーを押しながらA1セルをクリックし、A1セル以外のA列を範囲選択した状態で右クリックして、[セルの書式設定]を開く
• [分類]を「日付」にし、[種類]を「2012年3月14日」にして[OK]をクリック
• 同様の手順でD1セル以外のD列を範囲選択し、右クリックから[セルの書式設定]を開く
• [種類]を「通貨」にし、[記号]を「なし」に、[負の数の表示形式]を「1,234」にして[OK]をクリック
• A1セルからK1セルの見出しを、[ホーム]タブの[フォント]や[配置]を使って自由に装飾する

◆

[確認区分]を選択したときの表示パターンを設定する

• A列をクリックし、Shiftキーを押しながらG列をクリック
• Ctrlキーを押しながらA1セルからG1セルまでを順番にクリック
• [ホーム]タブを開き、[条件付き書式]を開いて[新しいルール]をクリック
• [ルールの種類]を「数式を使用して、書式設定するセルを決定」にして、数式入力欄に以下の数式を入力し、[書式]をクリック

  • =$H2="確認"

• [塗りつぶし]から、証憑の確認が済んだときに表示する色を任意で選択して、[OK]をクリック
• プレビューに設定が反映されていることを確認して[OK]をクリック
• 手順①～④と同様の手順をもう一度行い、手順④の数式を以下にして、[確認区分]を「不要」にしたときの設定も行う

  • =$H2="不要"

• [塗りつぶし]から、証憑の確認が不要のときに表示する色を任意で選択して、[OK]をクリック
• プレビューに設定が反映されていることを確認して[OK]をクリック

◆

「証憑管理」シートの作成

「証憑管理」シートは、「証憑入力」シートをそのまま抽出しているただのコピーのようなシートだが、「評価年」「評価月」というフィルター機能を設定しておくことによって、西暦や月で表示したい証憑を絞って表示させることができる。

「証憑入力」で入力を終えた証憑データを確認したいときの、出力用シートである。

• 新しいシートを作成して、名前を「証憑管理」に変更する
• 各セルに、以下の通りに名称を入力する
  • A1：評価年：　C1：評価月：
  • A2：日付　B2：仕訳番号　C2：勘定科目　D2：金額
  • E2：摘要　F2：インボイス　G2：請求書番号　H2：証憑種別
  • I2：保存場所　J2：備考
• A3セルをクリックしてから[表示]タブを開き、[ウィンドウ枠の固定]を開いて[ウィンドウ枠の固定]をクリック
• A2セルに以下の数式を入力する

  • =LET(base,証憑入力!A2#,topA,INDEX(base,1,1),IF(topA="","対象の『確認』証憑データはありません",LET(n,ROWS(base),cA,INDEX(base,,1),cB,INDEX(base,,2),cC,INDEX(base,,3),cD,INDEX(base,,4),cE,INDEX(base,,5)&"",cF,INDEX(base,,6)&"",cG,INDEX(base,,7)&"",cH,証憑入力!H2:INDEX(証憑入力!H:H,n+1),cI,証憑入力!I2:INDEX(証憑入力!I:I,n+1)&"",cJ,証憑入力!J2:INDEX(証憑入力!J:J,n+1)&"",cK,証憑入力!K2:INDEX(証憑入力!K:K,n+1)&"",mask,IF(cH="確認",IF(cB<>"",IFERROR(IF(YEAR(cA)=$B$1,IF($D$1="",TRUE,MONTH(cA)=$D$1),FALSE),FALSE),FALSE),FALSE),FILTER(CHOOSE({1,2,3,4,5,6,7,8,9,10},cA,cB,cC,cD,cE,cF,cG,cI,cJ,cK),mask,"対象の『確認』証憑データはありません"))))

• [日付]列の表示形式を変更する
  • A列をクリックしてA列全体を範囲選択
  • Ctrlキーを押しながらA1セルとA2セルをクリックし、A1・A2セル以外のA列全体を範囲選択してから、右クリックで[セルの書式設定]を開く
  • [分類]を「日付」に、[種類]を「3月14日」にして[OK]をクリック
• 同様の手順で、[金額]列の表示形式を変更する
  • D1・D2セル以外のD列を範囲選択してから、右クリックで[セルの書式設定]を開く
  • [分類]を「通貨」に、[記号]を「なし」に、[負の数の表示形式]を「1,234」にして[OK]をクリック
• 見出しとなるA2セルからJ2セルまでを、[ホーム]タブの[フォント]や[配置]を使って自由に装飾する

◆

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

証憑管理台帳への記録は、取引帳に入力した都度、「証憑入力」シート側も同時に終わらせておくのが理想だ。

遅くとも毎月末の締め作業（一ヶ月分の帳簿入力を完了させ、PDFとして保存する処理）を行う際には、必ず一緒に済ませておこう。

[保存場所]については『零式・Excelの複式事業簿～Chapter2：帳簿と証憑の保存方法～』でも解説しているため、合わせて参照いただきたい。

• 証憑管理台帳の基本的な使い方
• 一か月分の証憑をPDF化して保存する

◆

証憑管理台帳の基本的な使い方

零式・Excelの複式事業簿における証憑管理台帳（「証憑入力」シートと「証憑管理」シート）は、「勘定科目リスト」シートにおいて[証憑区分]が「TRUE」の科目が、取引帳にて使用された際にそのデータだけをまず「証憑入力」シートに自動表示する。

「証憑入力」シートにデータが反映されていれば、「その証憑を確認したか」や保存場所などの情報を自身で追記していく。

• [確認区分]のプルダウンから「確認」「不要」を選択する
• [証憑種別]に「領収書」や「請求書」など証憑の種類を入力
• [保存場所]に証憑を保管している場所を入力
  • スキャンした領収書や電子データなどをPCで保存している場合は保存先フォルダ名を、紙など現物を保管している場合はその場所やファイル名などを入力
• 「証憑入力」に表示されているデータすべてを入力していく
• 「証憑入力」シートにて[証憑区分]を「確認」にしたデータのみが「証憑管理」シートに表示される
  • 評価年や評価月を入力することで、対象の年月のみ表示も可能

◆

一か月分の証憑をPDF化して保存する

• 「証憑入力」シートに、証憑データをしっかりと入力していく
• ひと月分の入力が完了したら「証憑管理」シートでチェック
• [ファイル]を開き、[印刷]をクリック
• デフォルトでは縦型の形式になっているため、[設定]の「縦方向」を「横方向」に変更する
• 一枚のページに収めるために、[設定]の「拡大縮小なし」を「シートを1ページに印刷」に変更する
• 印刷はせず、左側の[エクスポート]を開いて、[PDF/XPSの作成]をクリック
• [ファイル名]を入力し、[ファイルの種類]を「PDF」にして[発行]をクリック
• PDFファイルが生成されていればOK