>> 今月のおすすめ記事はこちら <<

【家計管理】エクセルで複式簿記の家計簿を作る方法|第二回:仕訳帳

エクセルを使った複式簿記の家計簿(エクセル複式家計簿)を作る方法を紹介します。

この記事では、第二回として仕訳帳を作っていきます。

なるべく入力の手間を減らせるように数式や入力規則を使うようにしています。
エクセル初心者でも分かりやすいように、画像たっぷりで紹介していきますよ!

あがぺい
あがぺい

仕訳は毎日の作業だから効率よく行いたいですね

★導入の記事です。まだ読んでない方はこちらからどうぞ。
>>>【簿記保有者必見】貯まる!複式簿記で家計簿を付けるメリット

エクセル複式家計簿シリーズ

第一回:勘定科目リストをつくる
第二回:仕訳帳をつくる ←この記事
第三回:集計表をつくる
第四回:締め切りと繰り越し
第五回:損益計算書と貸借対照表をつくる

「家計簿を自作するのは大変そう……」

と思ったあなたには最新の家計簿アプリがオススメ!

銀行口座や証券口座と連携できるので、かんたんに家計管理ができますよ。

※基本無料で使えます

\ 人気No.1 家計簿アプリ/

タップするとアプリストアへ移ります

エクセル複式家計簿の仕訳の全体像

まずは今回ご紹介するエクセル複式家計簿の仕訳帳の全体像をお見せします。

仕訳帳の完成品

こちらが今回の記事で作っていく仕訳帳です。

完成した仕訳帳

記入する項目は左から順に、

  • 日付
  • 科目CD(コード)
  • グループ
  • 科目
  • 金額
  • 摘要(内容)

としました。

この記事で紹介する仕訳帳には2つの特徴があります。

  • キーボードを使った入力は3箇所だけ
  • 借方と貸方はプラスとマイナスで表現する

それぞれ簡潔にご説明します。

特徴① キーボードを使った入力は3箇所だけ

毎回毎回、こんなに入力するの面倒だわ・・・

家計簿にかける時間はなるべく減らしたいですよね。
そこで! 7つある項目のうち、

  • 年・科目CD・グループは数式を使った自動入力
  • 科目は入力規則を使ったプルダウンリスト

で完結するように設定します。
つまり、キーボードを使って入力するのは日付・金額・摘要の3項目だけにしてあります。

ラクチンです!

特徴② 借方と貸方はプラスとマイナスで表現する

簿記3級では「左が借方、右が貸方」となるように仕訳をすると習いました。

食費3,200現金3,200
旅費10,000未払金10,000
現金200,000給与200,000
通常の仕訳の例

しかし、エクセルにとってこの形は集計がしにくい・・・
そこでエクセル複式家計簿では

  • 借方=プラス
  • 貸方=マイナス

で仕訳を表現します。

ひとつの仕訳に対して借方(プラス)の行、貸方(マイナス)の行の2行を使って記入していきます。
例として上の仕訳を書き換えるとこのようになります。

食費3,200
現金-3,200
旅費10,000
未払金-10,000
現金200,000
給与-200,000
エクセルの仕訳の例

こんな感じで金額を1列にまとめるとエクセルは大喜び!
かんたんに集計できるようになります。

最初は慣れないかもしれませんが、徐々にこっちのほうが楽だと気づきますよ

エクセル複式家計簿の仕訳帳の作り方

それでは、仕訳帳の作り方を解説します。
ポイントは以下の5点です。

仕訳帳のポイント
  1. プルダウンリスト
  2. YEAR関数
  3. INDEX・MATCH関数
  4. 表示形式
  5. テーブル化

それぞれ詳しく説明します!

先頭行に見出しを入力する

はじめに仕訳帳の見出しを作っていきましょう。
まずは”仕訳帳”のワークシートを選択します。

仕訳帳タブ

A1セルから順に

  • 日付
  • 科目CD
  • グループ
  • 科目
  • 金額
  • 摘要

と入力します。

仕訳帳の見出し

これで表の見出しが完成しました。

順番を入れ替えても問題ありませんが、以降に示す数式などがズレてしまうことにご注意ください。

①プルダウンリストを使う

次に、仕訳の入力を楽にする「プルダウンリスト」を作っていきます。

E列に入力する”科目”は、第一回で作った”勘定科目リスト”にまとめてあります。
そこを参照したプルダウンリストを作成しましょう。

プルダウンリストをつくることでタイピングする手間が省ける他、決まった単語を正確に入力できます。

表記ゆれがなくなるので、集計時にピボットテーブルがバグってしまうリスクを減らす効果があります。

勘定科目リストの科目の範囲に名前を付ける

“勘定科目リスト”のワークシートを開き、科目が書かれている範囲(下の画像の場合C2:C34)を選択します。

科目範囲の選択

赤線で囲んである箇所に「科目」と入力し、エンターを押します。

範囲に名称を付ける

これでこの範囲に「科目」という名前が付きました。

“仕訳帳”のE列を選択する
科目列の選択

”仕訳帳”のワークシートに戻り、E列(科目の列)を選択します。
Eって書いてあるところをクリックすれば列を選択できますよ。

[データ]タブから「データの入力規則」を選択する
データの入力規則

上のメニューからデータ→データの入力規則をクリック

データの入力規則を選択



設定タブで勘定科目リストを参照する
入力規則の完成

入力値の種類は「リスト」を選びます。
元の値の欄は「=科目」と入力します。さきほど入力した範囲が参照できるようになります。

上の画像のように設定できたらOKを押します。

プルダウンリストの完成です
プルダウンの完成

これでE列はプルダウンリストで入力できるようになりました。
▼をクリックして、科目を選ぶだけでOKです。

参照元の勘定科目リストを変更した場合にも中身は自動で更新されるので、入力規則リストの修正は不要です。

②YEAR関数

A列ではYEAR関数を使って、B列に入力された日付の年を計算します。

A2セルには「=YEAR(B2)」と入力してください。
B2に日付を入力すると、A2セルに年が表示されます。

このとき、B2に入力する日付は半角数字とスラッシュを使って「9/12」という形にしてください。

日付の入力

エンターを押すと、自動的に今年の日付に変換されます(特に書式を設定していない場合)。
A2列にはB2の年がYEAR関数によって抽出されます。

YEAR関数

わたしは「9月12日」の表示が煩わしいので、「9/12」と表示されるように設定しています。

B列を選択し、セルの書式設定からユーザー定義の「m/d」を選択。

日付をm/dに
日付の書式設定

横幅が小さくなるのでスッキリしますよ。

③INDEX・MATCH関数

次はプルダウンリストから選んだ「科目」に従って、C列の「科目CD」とD列の「グループ」が自動で入力されるような関数を設定します。

ここではINDEX関数とMATCH関数を使います。

C2セルには「=INDEX(kamoku,MATCH(E2,kamoku[科目],0),1)」
D2セルには「=INDEX(kamoku,MATCH(E2,kamoku[科目],0),2)」

と入力してください。

INEDX/MATCH関数

上の画像のように、E2に入っている「現金」の科目CDとグループを算出してくれます。

関数について補足します。
INDEX関数
INDEX関数は
=INDEX(あ,い,う)
としたときに、「あ」の中で「い行」「う列」にある値を教えてくれます。
今回の
=INDEX(kamoku,MATCH(E2,kamoku[科目],0),1)
は、「kamokuテーブル」の中で、「MATCH(E2,kamoku[科目],0)行」「1列」にある値を教えてくれます。
MATCH関数
MATCH関数は
=MATCH(あ,い,0)
としたときに、「あ」が「い」の中で何行目にあるかを教えてくれます。
今回の
=MATCH(E2,kamoku[科目],0)
は、「E2セルに入っている科目」が「kamokuテーブルの科目の列」の中で何行目にあるかを教えてくれます。
その値がINDEX関数の「い行」に当てはまります。

2020年以降のエクセルをご利用されている方はXLOOKUP関数でもOK!

★エクセルに強くなりたい方はこちらの書籍がオススメです

④表示形式を決める

次にF列の”金額”の表示形式を設定します。
借方/貸方の見分けがつくようにマイナスの数値を赤字で表示する設定にします。

F列を選択して、セルの書式設定を開きます。
一番下の「その他の表示形式」を選択して、分類の通貨を選び、

  • 記号=なし
  • 負の数の表示形式 -1,234

とします。

表示形式
書式設定
マイナスを赤字で表示

これで金額がマイナスのときは赤字で表示されるようになりました。

⑤テーブル化する

最後の締めに、仕訳帳をテーブル化しましょう。

関数や表示形式を設定した表を選択します。

テーブル範囲の選択
[挿入]タブからテーブルを選択

挿入、テーブル

テーブル名は「shiwake」としておきます。

テーブル名の設定

テーブル化するメリット

テーブル化すると、関数や書式が自動的にコピーされます。
例えば、テーブルの下の行で科目を入力すると、自動的にA,C,D列の関数が引き継がれます。

テーブル化のメリット

あとはB列の日付、F列の金額をキーボードから入力するだけで仕訳作業が完了します。

関数の自動入力

これにて仕訳帳の作り方は終わりです。お疲れさまでした!

エクセル複式家計簿の始め方

テンプレートができたので、さっそく家計簿を付けていきましょう。

まずは、今後の資産と負債を管理するために現在の資産と負債を記入します。

現在の資産と負債を整理する

お手持ちの財布や銀行口座に入っている金額などを確認していきましょう。

資産負債
現金 100,000クレカ未払金 -300,000
みずは銀行 500,000奨学金 -1,000,000
薬天銀行 3,000,000カーローン -2,000,000
つみたてNISA 400,000
iDeCo 200,000
ゴールド 10,000

カーローンは負債にカウントしていますが、車は資産にカウントしていません。車の資産価値が減っていくのを計算するのが面倒だからです(通常の簿記であれば減価償却が必要になります)。

例のごとく値はダミーですよ

現在の資産と負債を入力する

現状が把握できたらエクセルに記入していきましょう。

『資産+負債+純資産=0』となるように純資産も入力します。

エクセル複式家計簿の始まり

あとは日々の取引を記録していくだけです!

エクセル複式家計簿の仕訳帳の書き方

最後に具体例を使って仕訳帳の書き方を説明します。

5つのパターンを紹介します

1,500円の商品を現金で購入した

「9月30日にBスーパーで1,500円の商品を現金で購入した」
という取引を記帳してみましょう。

仕訳は簿記3級にならって、

  • 借方(プラス)に費用の発生
  • 貸方(マイナス)に資産の減少

を入力します。

テーブルの最下段の1行下に新しい仕訳を追加していきます。

このとき、日付、科目、金額、摘要、どれを最初に記入しても構いません。
なにかしら入力すると自動でテーブルが拡張し、関数や入力規則がコピーされていることがわかります。

なぜかプルダウンリストはキャプチャに映りませんでした

似たような取引が増えてきたら、コピー&ペーストでも記入できます。

旅費をクレジットカードで払った

こちらは

  • 借方(プラス)に費用の発生
  • 貸方(マイナス)に負債の増加

のパターンです。
クレジットカード払いは負債ですので、このように記帳します。

銀行預金からつみたてNISAの投資信託を購入した

  • 借方(プラス)に資産の増加
  • 貸方(マイナス)に資産の減少

毎月発生する取引はコピー&ペーストも活用しましょう。

給料が薬天銀行に振り込まれた

  • 借方(プラス)に資産の増加、費用の発生
  • 貸方(マイナス)に収益の発生

ですよね。
また、このようにまとめることもできます。

クレジットカードの引き落としがあった

  • 借方(プラス)に負債の減少
  • 貸方(マイナス)に資産の減少

以上のように、簿記の知識を思い出しながら記帳します。

締め切りと繰り越しは第四回で

純資産、損益の勘定科目を使う仕訳はこの記事で取り上げません。

第四回の締め切りと繰り越しにて説明します。


以上で、エクセルで複式簿記の家計簿を作る方法の第二回:「仕訳帳の作り方」を終わります。

あがぺい
あがぺい

お疲れさまでした!

>>>第三回:集計表の作り方へ

8 COMMENTS

通りすがりのK

大変参考になりました。今月から複式簿記に切り替えていけないかなと考えています。ところで、すべての記事リンクがhttps://agapei-blog.com/excel_fukushiki_kakeibo02/ になっていて、自分でURLをいじらないと飛べない箇所が何箇所かありましたのでご報告です。

もう一つ、これは質問なのですが、私のようにすでに家計簿をつけて結構立つ人が、開始月にもともとあった資産や負債などを記入するにはどうすればよいのでしょうか。

いつも、「あと5万くらいあるからオッケー」とやっていたら、マネーフォワードの資産がドンドコ減っていて焦っていて、複式簿記を使って自分の家系の状況をしっかり見たいと思っております。そういう意味では、過去の資産は一旦おいておいて作っていっても良いのですが、どうせならと欲が出てしまっています。

初心者丸出しで、笑ってしまうようなご相談なのですが、もしよければご回答いただけますととても嬉しいです。

返信する
あがぺい

通りすがりのKさん

コメントありがとうございます!!
こちらのミスでお手数をおかけしました。申し訳ございません。
リンクが間違っていることを教えてくださり、たいへん助かりました。

>私のようにすでに家計簿をつけて結構立つ人が、開始月にもともとあった資産や負債などを記入するにはどうすればよいのでしょうか。
https://agapei-blog.com/excel_fukushiki_kakeibo04/
の「エクセル家計簿の繰り越し」の項目が参考になるかと思います。
例では9月の資産等を、ピボットテーブルを使って10月に振り替えていますが、10月からスタートする場合は資産・負債・純資産をご自身で入力してください。
例)現金100万、銀行200万、ローン50万の状態で10月から家計簿を開始する場合、
10/1 現金  1,000,000
10/1 銀行  2,000,000
10/1 ローン  -500,000
10/1 純資産 -2,500,000
と仕訳帳に入力します。資産+負債+純資産=0となるように、純資産を調整します。

おかげさまで複式家計簿の始め方を示していなかったことに気づくことができました。
近日中に追記します!

資産が減っていくのを見ると焦ってしまう気持ち、よく分かります。
エクセル家計簿が通りすがりのKさんのお役に立てれば幸いです。
ご不明点ございましたらお気軽にコメントください!

返信する
通りすがりのK

あがぺい様

通りすがりのKです。早速お返事頂戴でき嬉しいです!
リンクに関しては、良質な記事が些細なことでPVが減るというのはもったいないと思いまして、こちらこそ余計なお世話にならなくてよかったです。

さて、「エクセル家計簿の繰り越し」部分ですが、アドバイスいただきありがとうございます!私も、おそらく月初の繰越を全手動でやることで再現できるとまでは考えていたのですが、なれない作業でなにか例がないかなと思っていた次第です。

借方・貸方というととっつきにくいのですが、それを符号で表現し、見た目は単式のようであって、実は複式簿記なんです。というのが、あがぺい様方式のウリかと思っております。(本業はSIerなのですがとても理にかなっていると感じています)早速真似してやってみます。これからも記事の更新を楽しみに拝見させていただきます。それでは。

返信する
あがぺい

リンクミスに気づかなかったらと思うとゾッとします。
お声がけ、ありがとうございました。

そういっていただけてとても嬉しいです!
まだまだ発展途上の家計簿なので、これからも改善を積み重ねていきます。

返信する
通りすがりのK

あがぺい様
先程投稿させていただいた内容を踏まえまして、あがぺい様のアドバイス通り、純資産を計算することができました。ありがとうございます。記事に関して、追加で疑問があり追加で投稿します(ですので、お返事がもし頂けるようであればどちらか一方で大丈夫です)

「第一回:勘定科目リスト」では、科目にNISAやiDECOなどの証券資産が登場したかと思います。今回の純資産の計算では、11/1日現在の評価額を記載することとしました。この証券資産のメンテナンスについてお伺いしたいです。

今後NISA枠で投信などを買った場合、口座から現金が減り、NISAが増えるといった書き方となると思います。その後、購入からしばらく経ち、評価額が増減した際はどういった記載方法になるのでしょうか。例えば、月初と月末の評価額を比較して、プラス・マイナスの処理をすると良いのでしょうか。

例えば、11/1のNISA評価額が100円だったとして、11/30日は101円とします。この場合、借方にNISAを1円、貸方に投信評価益(科目は例)1円といった書き方を行っていくものなのでしょうか…..

もし追記記事を出されるのであれば、どこかで触れていただけますと初心者としてとても心強いです。

あがぺい様の記事を見て、実際に純資産を計算すると、まだ奨学金があり当然マイナスではあるものの、自分が思った以上にお金は溜まっており、あと一息でプラスマイナスゼロ、だから心配ないと分析できとても元気が出ました。これからはしっかり家計と向き合って、前向きにお金と付き合いたいと思います。本当にありがとうございます。

返信する
あがぺい

貴重なご意見ありがとうございます!
有価証券の評価額の件、回答します。

なかなか難しい議題ですが、論点は【確定していない利益/損失を計上していいのか?】です。
通りすがりのKさんのおっしゃる通り、「あるタイミングで『評価損益』の勘定に振り替える」のも一つの手ですね。
しかし、確定していない利益が毎月の損益計算書に反映されてしまうと、
・株の成績が良かっただけなのに、月収が多いと見誤る
・株が暴落しただけなのに、今月は赤字だと見誤る
といった弊害が発生しうると思います。

個人的には「確定していない損益は計上しない」方がいいのではないかと考えています。
気になったら証券口座のマイページなどで確認するだけにして。

ただ、これはあくまでも私見なので、改めて勉強して「有価証券の評価額の取扱」を記事に追加します。
おかげさまで独りよがりの記事になっていることに気づくことができました。

実際に家計簿を作って分析されたんですね!
元気が出たというお言葉を聞いて、私も元気が出てきました(笑)
一緒に家計改善の道を歩んで行きましょう!!

返信する
通りすがりのK

あがぺい様

こんにちは。通りすがりのKです。早速のお返事いただきありがとうございます!
確かに、まだ未確定の損益を計上したとしても、あまり有益ではないですね。しかし、どこかで「今の評価額ってどうだっけ?積み立てて購入してるインデックス株は順調かな?」ということもウォッチしていきたいという気持ちもあって、難しさを感じます。勢い余って簿記3級のテキストを買い、流し見したのですが有価証券の扱いはなかったように思え、この辺が一つのラダーなのかなと感じている次第です。

お返事にありましたように、記事のアップデートを検討されているとのこと!ありがとうございます。あがぺい様方式だとどういう判断になるのか私も楽しみです。これからも楽しみにお待ちしていますね。

秋も深まりし今日このごろ、ご自愛下さいませ。

返信する
あがぺい

個人の価値観が反映できることが自作家計簿のメリットなので、
・評価額を知りたい人は記帳すればいいし
・未確定な情報は入れたくない人は記帳しなければいい
という結論になりそうです。

ご丁寧にありがとうございました!

返信する

通りすがりのK へ返信する コメントをキャンセル

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

CAPTCHA