Excel、マクロで試算表を作る方法ーピボットテーブル、VLOOKUPを活用ー

「Excelやマクロで試算表を作ってみたい」というご要望をよくききます。
結論としては作れないこともありませんが、それなりに大変です。

 

Excelで試算表を作りたい理由

Excelやマクロで試算表を作りたいという理由は、会計ソフトのせいです。

会計ソフトは使いやすいようで使いにくく、コストもかかります。
Excelで自由に、かつコストをかけないで試算表を作りたくなるのは私も同じです。

規模や業種にもよりますが、Excelだけで集計し、数字をチェックすることはできます。
ただし、それなりにExcelスキルも必要ですし、チェックも欠かせません。

私が現時点で考えているExcel試算表の流れを解説します。

 

 

Excelで試算表を作る流れ

1　仕訳の入力

全体的な流れとしては、
仕訳を入力
↓
集計
となるのですが、仕訳には借方、貸方があり、それぞれで集計する必要があります。
さらに消費税の処理が入ってくると、税抜の金額で集計し、消費税も別途集計しなければいけないのです。

となると、仕訳は、日付、借方科目、貸方科目、金額、内容（摘要）だけでは不十分で、

これくらいのデータが必要となります。
※データは［テーブル］という機能を使って表示させています。
※実際は、科目ごとの消費税コードをVLOOKUP関数で連動させ、自動的に表示させています）

仕訳だけだと1枚のシートにすべて入力が可能です。

私自身の会計（家計、税理士事務所、会社）は、仕訳だけで入力していますが、これは仕訳がわかっているからです。
お客様には、現金ごと、預金ごとの入力をしていただいています。

伝票形式の入力はちょっと厳しいです。「諸口」を使って、1本の仕訳で表現しなければいけません。

 

2　借方、貸方の集計

仕訳データから、借方科目、借方税抜金額を集計します。
使うのはピボットテーブルです。
実際は、ピボットテーブルの列に年月をいれて、月ごとに出せるようにしています。

同様にして、別シートで貸方を集計します。

 

3　試算表のフォーマットを作成

試算表のフォーマットを作っていきます。

貸借、借方と貸方が一致しているかどうかをチェックするため、黄色いセルに数式を入れておきます。
ここが0だったら一致しているということです。

 

4　VLOOKUP関数で数字を連動させる

前月残高をいれ、借方に次のようなVLOOKUP関数を入れて、借方を集計したピボットテーブルから数値を連動させるしくみです。

もし、該当科目がない場合は、エラーになります。

［現金］がピボットテーブルにはない、つまり借方に発生していないからです。

このエラーを防ぐために、IFERROR関数を使います。

同様に、貸方にも数式を入れましょう。

1,382と入りました。

ピボットテーブルを見ると、確かに現金が貸方に1,382あります。

 

5　場合分けをして、残高を計算

次に当月残高を計算するのですが、1つ問題点があります。
［現金］なら、前月残高＋借方ー貸方＝当月残高です。
［未払費用］、つまり負債の勘定科目なら、前月残高-借方+貸方＝当月残高となります。
科目によって、数式を変えなければいけないのです。

これをうまく使い分けるために、A列に、数字を入れていきます。
前者のケースなら、1、そうでなければ2を入れて、こういった数式を入れれば楽です。

6　利益欄の数式を修正

次に営業利益、経常利益の数式をチェックします。
他の部分と同じように数式をいれていると、こう表示され、当月の業績がわかりにくいです。

数式を変えて、

利益が表示できるようにしましょう。
プラスとマイナスで表示し、常に貸方（右側）に表示されるようになります。

利益がマイナスの時はこうなるので、これで問題ありません。
数式を変えれば、借方へ表示することもできます。

 

7　利益剰余金の式を修正

当月残高の貸借差額が、90万円ほどでています。

この原因の1つは利益剰余金です。
P/L（損益計算書）の利益は、B/S（貸借対照表）の利益剰余金に加算されていきます。
P/Lの利益の積み重ねがB/Sの利益剰余金なのです。
利益剰余金の金額は、設立以来現在までの利益の蓄積を意味しますので、設立10年で利益剰余金がプラス100万なら、10年間の利益がプラス100万ということになります。
（配当をしている場合を除く）

そこで、こういった数式を入れて、

連動させる必要があるのです。
税込の試算表、消費税免税の試算表の場合はこれで終わりですが、今回は税抜の試算表ですので、さらに差額が出ています。
これは消費税の差額です。

 

8　仮払消費税、仮受消費税を計算

消費税8％で、売上1,080,000円の場合、税抜の売上は1,000,000円となり、P/Lへ1,000,000円が入ります。
差額の80,000円は、仮受消費税（仮に預かった消費税）として、B/Sへ入るのです。
Excelでやる場合もこの処理をしなければいけません。

ピボットテーブルで、課税区分ごとに消費税額を集計します。
借方、貸方それぞれを集計し、ここで、仮払消費税、仮受消費税を出しておきましょう。

試算表には、こういったVLOOKUP関数を入れて、連動させます。

仮払消費税、仮受消費税が入ると、貸借差額が0となり、完成です。
差額を未払消費税として入れてもいいでしょう。

あとは、好みで体裁を整えます。
条件付き書式で、0を白字にすると、

すっきりします。

B/Sをわかりやすくするために左と右にバランスさせることも可能です。

 

9　マクロを使うなら

ここまでは、Excelの機能を使って試算表を作っています。
マクロは使いません。
この後に、使います。

今回のしくみでは、ピボットテーブルを使っていますので、［更新］が必要です。

仕訳を入力するたびに、更新しなければいけないので、手間もかかります。
次のようなマクロが便利です。
（ピボットテーブルに「pivot」という名前をつけています）

Sub tb()

    Worksheets("借方税抜").PivotTables("pivot").PivotCache.Refresh
    Worksheets("試算表").Select

End Sub

仕訳を入力して、ショートカットキーでマクロを動かすと、

シートが試算表に切り替わってチェックできます。
毎回、更新やシートの切り替えをしなくていいのです。

実際に使う場合は、科目の明細、元帳をつくる部分にマクロを使っています。

すべてをマクロでやろうとするともっと大変です。
労力を考えると私もできません。

なお、「SUMIFでやればいいのでは・・」と思う方もいらっしゃるかもしれません。
SUMIF、SUMIFS関数を使って同じようにできますが、ファイルがかなり重くなります。
昔は、自分の会計をSUMIFS関数でやっていましたが、重くなるので、さらにメンテナンスしやすい今回のしくみに切り替えました。

 

 

現実的には、会計ソフトをうまくつかった方が楽

実際、私は、Excelで試算表を作っていません。
業績を見るのなら、推移表の方が見やすく使いやすいからです。
同じしくみ（多少数式は変わりますが）、推移表を作っています。

そもそも試算表は、その名の通り、入力や転記があっているかどうかを試算するものなのです。
業績チェックに適しているわけではありません。

「Excelで試算表」というのは、可能なのですが、現実的には会計ソフトをうまく使うべきでしょう。

仕訳データを会計ソフトに取り込む、
［Excelコンサル事例］Excelデータを弥生会計へインポートするためのマクロ | EX-IT

 

 

 

会計ソフトから、データをExcelに変換してExcelで加工するといった方法があります。
ExcelのVLOOKUP関数で、わかりにくい会計ソフトの試算表をかんたんにきれいにする方法 | EX-IT

 

 

 

どの会計ソフトでも、一長一短があり、会計ソフトだけでは完結しません。
試算表が必要なのか？どんな数字が必要なのか？を考え、会計ソフト、Excel、をうまく使い分けていきましょう。
Excelでも、Excelの機能、マクロを使い分けることが大事です。

ただ、Excelや会計スキルの勉強になりますので、Excelだけでの集計も一度は試してみていただければ。

---

---

---

---

【編集後記】
昨日は、午後にマクロセミナーを開催。台風の影響で、夕方に1回追加開催し、予定通り夜も1回開催しました。
11/1（土）にも開催します。
※昨日、申込開始しましたが、設定ミスですでに定員と表示されていました・・・。
もちろん、まだ空きはあります。

11/1（土）午前　経理＆会計のためのExcelマクロ入門セミナー

https://www.ex-it-blog.com/macroseminar/　

11/1（土）午後　経理を12倍速くする！Excel入門セミナー

https://www.ex-it-blog.com/excelseminar/　

［10/17までに200kmチャレンジ］
昨日　0km　累計101.5km

【昨日の1日1新】
※詳細は→「1日1新」

1日に同じセミナーを2回開催
紅茶キャラメル