Excelの複式事業簿～第７章：証憑管理台帳を作成する～

この記事は、あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』内で紹介されている「本気の家計簿」を参考に、Excelを使った複式家計簿を事業用に応用する帳簿「Excelの複式事業簿」を作るまでを撰述したものである。

前の記事<第6章：総勘定元帳を作成する

• 第0章：Excelを使った複式家計簿を事業用に応用する～導入編～
• 第1章：「本気の家計簿」を事業用にわかりやすい設定へ変更する
• 第2章：勘定科目と仕訳帳のシートに必要項目を追加する
• 第3章：万能表シートを追加する
• 第4章：インボイスに対応したバージョンを作る
• 第5章：各補助簿を作成する～一覧～
• 第6章：総勘定元帳を作成する
• 第7章：証憑管理台帳を作成する（※この記事）

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

あがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』で紹介されている「本気の家計簿」を応用して個人事業用の帳簿をつけたい、と思い立ったことから、完全無料で「Excelの複式事業簿」を作っていく今回の連載。

主要簿である仕訳帳と総勘定元帳が完成し、補助簿も最低限必要な帳簿の作成方法を撰述してきたが、今回は少し例外的な証憑管理台帳の作成方法を紹介。

勘定科目リストと仕訳帳シートから、証憑が必要と思われる取引を自動抽出し、証憑の保管や確認ができるようなシートを作成する。

なお「本気の家計簿」の作り方・記帳の仕方はここでは解説しないので、ぜひあがぺいさんのブログ『ミールキットとExcel家計簿のあがぺいブログ』をご参照いただきたい。

あがぺいブログ｜ミールキットとExcel家計簿でラクする30代主夫

仕事や育児に忙しい共働き夫婦に向けて、おすすめのミールキットを提案します。食材宅配サービスを食生活アドバイザーの視点からレビューします。

agapei-blog.com

本連載記事の内容は、当サイトにて独自に作成・発展させたものであり、あがぺい様の「本気の家計簿」をベースとしつつも、事業帳簿としての活用を前提に再構成しているため、内容に関するご質問・お問い合わせは当サイト側にて承っています。

恐れ入りますが、あがぺい様のサイトへの直接のお問い合わせはご遠慮いただけますようお願いいたします。

証憑の役割とは

証憑とは、取引の事実を証明するための書類のこと。税務調査などで帳簿の内容を確認されるときに提出を求められることがある。

個人事業主に限らず雇用されている会社員にとっても、領収書や請求書など、証憑と言われるものが大事なのは言わずもがな。

しかし証憑管理はつい後回しになりがちであり、結果あとでまとめて管理や仕訳をしようとしても、データでの保存なのか紙なのか、はたまた保存場所や詳細などがわからなくなることがしばしば。

そこでここでは、証憑類を一括して管理できる実用的なシートの構築法を撰述していく。

「Excelの複式事業簿」証憑管理台帳の作成手順

ここから「Excelの複式事業簿」での証憑管理台帳を作成していく。

まず勘定科目シートに必要な見出し列を追加し、その後に証憑管理台帳シートを作成する流れ。

勘定科目リストシートに、必要な見出し列を追加

証憑管理台帳シートにて自動的に証憑が必要な取引仕訳を抽出するように設定するため、勘定科目リストシートにて自動抽出に必要な見出しを追加しなければいけない。

• 勘定科目シートのI列に「証憑区分」を追加
• 証憑が必要になる勘定科目（「売上」や「飲食代」など）には「TRUE」を、不要な科目には「FALSE」を入力していく

証憑管理台帳シートを作成する

勘定科目シートに「証憑区分」列を追加したら、次にいよいよ証憑管理台帳シートを作成していく。

• 「Excelの複式事業簿」を開き［＋］をクリックして新シートを開く
• 必要な見出しを各列に追加していく
  • A列：取引No
  • B列：日付
  • C列：勘定科目
  • D列：摘要
  • E列：金額
  • F列：インボイス番号
  • G列：請求書番号
  • H列：その他番号
  • I列：証憑種別
  • J列：保存場所
  • K列：チェック
  • L列：備考
• H列からL列をテーブル化する
  • H1～L1までを範囲選択
  • ［挿入］タブ
  • ［テーブル］をクリック
  • ［先頭行をテーブルの見出しとして使用する］にチェックをつける
  • ［OK］をクリック
• A2セルに数式を入力する
  • 仕訳帳シートから証憑が必要になる取引仕訳だけを自動抽出する数式

    • =IF(Z1=””,””,LET(年,INDEX(shiwake,,1),日付,INDEX(shiwake,,2),科目,INDEX(shiwake,,5),摘要元,INDEX(shiwake,,7),金額,INDEX(shiwake,,10),No,INDEX(shiwake,,9),インボイス,INDEX(shiwake,,22),請求元,INDEX(shiwake,,24),区分,XLOOKUP(科目,kamoku[勘定科目],kamoku[証憑区分],FALSE),対象年,Z1,対象行,FILTER(shiwake,(年=対象年)*(区分=TRUE)),摘要,IF(INDEX(対象行,,7)=””,””,INDEX(対象行,,7)),インボイス出,IF(INDEX(対象行,,22)=””,””,INDEX(対象行,,22)),請求書,IF(INDEX(対象行,,24)=””,””,INDEX(対象行,,24)),CHOOSE({1,2,3,4,5,6,7},INDEX(対象行,,9),INDEX(対象行,,2),INDEX(対象行,,5),摘要,INDEX(対象行,,10),インボイス出,請求書)))

• K列の［チェック］項目にプルダウンメニューを設定する
  • K2セルを選択した状態に
  • ［データ］タブ
  • ［データの入力規則］をクリック
  • ［データの入力規則］をクリック
  • ［入力値の種類：］を［リスト］に設定
  • ［元の値：］に「=$Y$1:$Y$2」と入力
  • ［OK］をクリック
• 日付列の表示がおかしい場合は正しく設定する
  • B列を右クリックしてB列全体を範囲指定
  • ［セルの書式設定］を開く
  • ［表示形式］タブの［分類：］を「日付」に
  • ［種類：］を「〇月〇日」表示に
  • ［OK］をクリック
• チェック項目を「確認」にした場合の表示設定をする
  • 二行目からのA列～G列を全範囲選択
    • もし仕方がわからなければA列～G列を適当に範囲選択でもOK
  • ［ホーム］タブから［条件付き書式］をクリックして［新しいルール］を開く
  • ルールの種類を「数式を使用して、書式設定するセルを決定」を選択
  • ［次の数式を満たす場合に値を書式設定：］に以下の数式を入力

    • =OR($K2=”確認”)

  • ［書式…］をクリック
  • ［塗りつぶし］タブの［背景色：］から任意の色を選択して［OK］をクリック
  • プレビューに書式が反映されていれば［OK］をクリック
  • A列からG列の範囲選択がわからなかった人は改めて範囲選択設定
    • ［ホーム］タブの［条件付き書式］をクリックして［ルールの管理］を開く
    • ［書式ルールの表示：］を「このワークシート」に
    • 今回設定したルールの［適用先：］に以下の数式を入力

      • =$A$2:$G$1000

    • ［適用］をクリックして［OK］をクリック
• チェック項目を「不要」にした場合の表示設定をする
  • 二行目からのA列～G列を全範囲選択
    • もし仕方がわからなければA列～G列を適当に範囲選択でもOK
  • ［ホーム］タブから［条件付き書式］をクリックして［新しいルール］を開く
  • ルールの種類を「数式を使用して、書式設定するセルを決定」を選択
  • ［次の数式を満たす場合に値を書式設定：］に以下の数式を入力

    • =OR($K2=”不要”)

  • ［書式…］をクリック
  • ［フォント］タブの［文字飾り］の［取り消し線］にチェックをつけ［OK］をクリック
  • プレビューに書式が反映されていれば［OK］をクリック
  • A列からG列の範囲選択がわからなかった人は改めて範囲選択設定
    • ［ホーム］タブの［条件付き書式］をクリックして［ルールの管理］を開く
    • ［書式ルールの表示：］を「このワークシート」に
    • 今回設定したルールの［適用先：］に以下の数式を入力

      • =$A$2:$G$1000

    • ［適用］をクリックして［OK］をクリック
• 証憑管理台帳シートの大枠が完成

「Excelの複式事業簿」証憑管理台帳シートの使い方

ここからは完成した「Excelの複式事業簿」の証憑管理台帳シートの使い方を撰述していく。

おさらいをすると、証憑管理台帳シートのチェック項目を「確認」か「不要」にすると、対応した表示が成されるように設定されている。

証憑を管理する際にこのチェック項目を使って「証憑を既に確認済みなのか」「確認不要なのか」「未確認なのか」を判別していく。

テーブル化の部分は自動表示されないため、都度延長する

この「Excelの複式事業簿」の証憑管理シートも他の補助簿と同じく、一部のテーブル化している項目に関しては自動表示されない仕組みとなっている。

そのため、自動抽出された取引仕訳が追加されたら、その都度テーブルを自身で延長しなければいけない。

テーブル化した見出し項目は自由に使用する

A列からG列の見出しに関しては、数式から取引仕訳が自動抽出される仕組みになっているが、H列からL列のテーブル化している見出しに関しては、特に決まった使い方はない。

H列の「その他番号」にはインボイスや請求書番号以外で控えておきたい番号を、証憑種別には「レシート」や「領収書」「PDFデータ」などの種類を、保存場所には自宅で証憑を保管している場所やPCのフォルダ名を入れてもいい。

入力情報をクリアする場合も、都度自身の操作にて

この「Excelの複式事業簿」の証憑管理台帳シートは、Z1セルに入れた評価対象年の取引仕訳を仕訳帳シートから自動抽出される仕様となっている。

しかしテーブル化部分に関しては自動抽出の対象外のため、評価対象年を変更したあとも入力がそのまま残ってしまう。

PDFなどで台帳データを保存したあとは、不要部分を範囲選択してキーボードの［Delete］キーを押せばゼロクリアできるため、都度自身の操作にて行っていただきたい。