この記事には広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
エクセルを使った複式簿記の家計簿（エクセル複式家計簿）を作る方法を紹介します。
この記事では、第二回として仕訳帳を作っていきます。
なるべく入力の手間を減らせるように数式や入力規則を使うようにしています。
エクセル初心者でも分かりやすいように、画像たっぷりで紹介していきますよ！
仕訳は毎日の作業だから効率よく行いたいですね
★導入の記事です。まだ読んでない方はこちらからどうぞ。
>>>【簿記保有者必見】貯まる！複式簿記で家計簿を付けるメリット
第一回：勘定科目リストをつくる
第二回：仕訳帳をつくる ←この記事
第三回：集計表をつくる
第四回：締め切りと繰り越し
第五回：損益計算書と貸借対照表をつくる
簿記はビジネスでも家庭でも、”一生役立つスキル”です。簿記3級くらいなら通信講座でガッツリ学ぶ必要ありません。学び直しや独学で合格を目指す方はテキストだけでも十分だと思いますよ。
フルカラーで分かりやすい
ポチップ
オススメはパブロフ流！ ゆるい犬のキャラが可愛くて癒やされます笑
スポンサーリンク
エクセル複式家計簿の仕訳の全体像
まずは今回ご紹介するエクセル複式家計簿の仕訳帳の全体像をお見せします。
仕訳帳の完成品
こちらが今回の記事で作っていく仕訳帳です。
記入する項目は左から順に、
- 年
- 日付
- 科目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点です。
- プルダウンリスト
- YEAR関数
- INDEX・MATCH関数
- 表示形式
- テーブル化
それぞれ詳しく説明します！
テーブルを科目などでソート（並び替え）した場合、元の順番に戻せなくなります。
あらかじめ”取引No.”の列を追加しておくことで、この問題を回避することができますよ！
先頭行に見出しを入力する
はじめに仕訳帳の見出しを作っていきましょう。
まずは”仕訳帳”のワークシートを選択します。
A1セルから順に
- 年
- 日付
- 科目CD
- グループ
- 科目
- 金額
- 摘要
と入力します。
これで表の見出しが完成しました。
順番を入れ替えても問題ありませんが、以降に示す数式などがズレてしまうことにご注意ください。
①プルダウンリストを使う
次に、仕訳の入力を楽にする「プルダウンリスト」を作っていきます。
E列に入力する”科目”は、第一回で作った”勘定科目リスト”にまとめてあります。
そこを参照したプルダウンリストを作成しましょう。
プルダウンリストをつくることでタイピングする手間が省ける他、決まった単語を正確に入力できます。
表記ゆれがなくなるので、集計時にピボットテーブルがバグってしまうリスクを減らす効果があります。
“勘定科目リスト”のワークシートを開き、科目が書かれている範囲（下の画像の場合C2:C34）を選択します。
赤線で囲んである箇所に「科目」と入力し、エンターを押します。
これでこの範囲に「科目」という名前が付きました。
”仕訳帳”のワークシートに戻り、E列（科目の列）を選択します。
Eって書いてあるところをクリックすれば列を選択できますよ。
上のメニューからデータ→データの入力規則をクリック
入力値の種類は「リスト」を選びます。
元の値の欄は「=科目」と入力します。さきほど入力した範囲が参照できるようになります。
上の画像のように設定できたらOKを押します。
これでE列はプルダウンリストで入力できるようになりました。
▼をクリックして、科目を選ぶだけでOKです。
参照元の勘定科目リストを変更した場合にも中身は自動で更新されるので、入力規則リストの修正は不要です。
②YEAR関数
A列ではYEAR関数を使って、B列に入力された日付の年を計算します。
A2セルには「=YEAR(B2)」と入力してください。
B2に日付を入力すると、A2セルに年が表示されます。
このとき、B2に入力する日付は半角数字とスラッシュを使って「9/12」という形にしてください。
エンターを押すと、自動的に今年の日付に変換されます（特に書式を設定していない場合）。
A2列にはB2の年がYEAR関数によって抽出されます。
わたしは「9月12日」の表示が煩わしいので、「9/12」と表示されるように設定しています。
B列を選択し、セルの書式設定からユーザー定義の「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)」
と入力してください。
上の画像のように、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の投資信託を購入した
- 借方（プラス）に資産の増加
- 貸方（マイナス）に資産の減少
毎月発生する取引はコピー&ペーストも活用しましょう。
給料が薬天銀行に振り込まれた
- 借方（プラス）に資産の増加、費用の発生
- 貸方（マイナス）に収益の発生
ですよね。
また、このようにまとめることもできます。
クレジットカードの引き落としがあった
- 借方（プラス）に負債の減少
- 貸方（マイナス）に資産の減少
以上のように、簿記の知識を思い出しながら記帳します。
純資産、損益の勘定科目を使う仕訳はこの記事で取り上げません。
第四回の締め切りと繰り越しにて説明します。
以上で、エクセルで複式簿記の家計簿を作る方法の第二回：「仕訳帳の作り方」を終わります。
お疲れさまでした！
>>>第三回：集計表の作り方へ
第一回：勘定科目リストをつくる
第二回：仕訳帳をつくる ←この記事
第三回：集計表をつくる
第四回：締め切りと繰り越し
第五回：損益計算書と貸借対照表をつくる
スポンサーリンク
大変参考になりました。今月から複式簿記に切り替えていけないかなと考えています。ところで、すべての記事リンクが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です。早速お返事頂戴でき嬉しいです！
リンクに関しては、良質な記事が些細なことでPVが減るというのはもったいないと思いまして、こちらこそ余計なお世話にならなくてよかったです。
さて、「エクセル家計簿の繰り越し」部分ですが、アドバイスいただきありがとうございます！私も、おそらく月初の繰越を全手動でやることで再現できるとまでは考えていたのですが、なれない作業でなにか例がないかなと思っていた次第です。
借方・貸方というととっつきにくいのですが、それを符号で表現し、見た目は単式のようであって、実は複式簿記なんです。というのが、あがぺい様方式のウリかと思っております。（本業はSIerなのですがとても理にかなっていると感じています）早速真似してやってみます。これからも記事の更新を楽しみに拝見させていただきます。それでは。
リンクミスに気づかなかったらと思うとゾッとします。
お声がけ、ありがとうございました。
そういっていただけてとても嬉しいです！
まだまだ発展途上の家計簿なので、これからも改善を積み重ねていきます。
あがぺい様
先程投稿させていただいた内容を踏まえまして、あがぺい様のアドバイス通り、純資産を計算することができました。ありがとうございます。記事に関して、追加で疑問があり追加で投稿します（ですので、お返事がもし頂けるようであればどちらか一方で大丈夫です）
「第一回：勘定科目リスト」では、科目にNISAやiDECOなどの証券資産が登場したかと思います。今回の純資産の計算では、11/1日現在の評価額を記載することとしました。この証券資産のメンテナンスについてお伺いしたいです。
今後NISA枠で投信などを買った場合、口座から現金が減り、NISAが増えるといった書き方となると思います。その後、購入からしばらく経ち、評価額が増減した際はどういった記載方法になるのでしょうか。例えば、月初と月末の評価額を比較して、プラス・マイナスの処理をすると良いのでしょうか。
例えば、11/1のNISA評価額が100円だったとして、11/30日は101円とします。この場合、借方にNISAを1円、貸方に投信評価益（科目は例）1円といった書き方を行っていくものなのでしょうか…..
もし追記記事を出されるのであれば、どこかで触れていただけますと初心者としてとても心強いです。
あがぺい様の記事を見て、実際に純資産を計算すると、まだ奨学金があり当然マイナスではあるものの、自分が思った以上にお金は溜まっており、あと一息でプラスマイナスゼロ、だから心配ないと分析できとても元気が出ました。これからはしっかり家計と向き合って、前向きにお金と付き合いたいと思います。本当にありがとうございます。
貴重なご意見ありがとうございます！
有価証券の評価額の件、回答します。
なかなか難しい議題ですが、論点は【確定していない利益/損失を計上していいのか？】です。
通りすがりのKさんのおっしゃる通り、「あるタイミングで『評価損益』の勘定に振り替える」のも一つの手ですね。
しかし、確定していない利益が毎月の損益計算書に反映されてしまうと、
・株の成績が良かっただけなのに、月収が多いと見誤る
・株が暴落しただけなのに、今月は赤字だと見誤る
といった弊害が発生しうると思います。
個人的には「確定していない損益は計上しない」方がいいのではないかと考えています。
気になったら証券口座のマイページなどで確認するだけにして。
ただ、これはあくまでも私見なので、改めて勉強して「有価証券の評価額の取扱」を記事に追加します。
おかげさまで独りよがりの記事になっていることに気づくことができました。
実際に家計簿を作って分析されたんですね！
元気が出たというお言葉を聞いて、私も元気が出てきました（笑）
一緒に家計改善の道を歩んで行きましょう！！
あがぺい様
こんにちは。通りすがりのKです。早速のお返事いただきありがとうございます！
確かに、まだ未確定の損益を計上したとしても、あまり有益ではないですね。しかし、どこかで「今の評価額ってどうだっけ？積み立てて購入してるインデックス株は順調かな？」ということもウォッチしていきたいという気持ちもあって、難しさを感じます。勢い余って簿記3級のテキストを買い、流し見したのですが有価証券の扱いはなかったように思え、この辺が一つのラダーなのかなと感じている次第です。
お返事にありましたように、記事のアップデートを検討されているとのこと！ありがとうございます。あがぺい様方式だとどういう判断になるのか私も楽しみです。これからも楽しみにお待ちしていますね。
秋も深まりし今日このごろ、ご自愛下さいませ。
個人の価値観が反映できることが自作家計簿のメリットなので、
・評価額を知りたい人は記帳すればいいし
・未確定な情報は入れたくない人は記帳しなければいい
という結論になりそうです。
ご丁寧にありがとうございました！
初めまして。
あがぺいさまが作ってくださったこちらのExcel家計簿を愛用しています。
しかしいざ自分がどれだけお金を貯めれているんだろうと確認したいときは、パソコンが手元になく家計簿を確認できないことが多く困っております。あがぺいさまの家計簿を見よう見まねでGoogleスプレッドシートで作成を始めたのですが、INDEX・MATCH関数がうまく入らなかったりテーブルの機能がなく(？)困っております。無理も承知なのですがアドバイスをお願いしてもよろしいでしょうか？？当方Excelもスプレッドシートも分からないため、このようにご相談させていただきました。
今後もあがぺいさまの家計簿は愛用していきます。ありがとうございます。
匿名ねこさん初めまして。
エクセル家計簿を愛用していただきありがとうございます！
たしかに、今のままでは使い勝手悪いですよね。
正直申し上げると、Googleスプレッドシートでの作り方は分かりません。
私も「いつかやらねば！」と思いながらも未だ着手できておらず。。。
代案として、Microsoftのスマホアプリを使ってエクセル家計簿を見ることはできませんか？
試しにやってみましたがiPhoneのMicrosoft365っていうアプリではグラフもきれいに見えました。
いつか、Googleスプレッドシートで複式家計簿を作る方法を編み出して記事にしたいと思っています。
私の宿題とさせてください。よろしくお願いします。
ご返信ありがとうございます！
スマホアプリのご提案やスプレッドシートでの作成を検討してくださるとのこと、心より感謝いいたします！
実はスマホアプリも使ってたのですが同期が反映されずやめてしまったんです。
こまめにPC側で新しいデータのものに更新して使ってみます。
スプレッドシートでの作成は急ぎませんので、記事を楽しみにお待ちしております。
どうもありがとうございました！