Excelの仕訳データから推移表を作るアイデア ーVLOOKUP・ピボットー

Excelで仕訳データから、試算表（推移表）を集計する方法はいくつかありますが、私は今回ご紹介する方法を使っています。

 

会計ソフトはよくできてる

・Excelで会計ソフトを作れるか？
・会計ソフトなしで経理ができないか？
と思われる方も多いでしょう。
会計ソフトだとお金もさらにかかりますし、それでいって使い勝手がいいものではないからです。
かんたん！といいつつかえって難しくしているケースもあります。

ただ、それでも「会計ソフトはよくできている」と思わざるを得ません。
会計のしくみをソフトで表現するのは思ったより大変なのです。
（それとこれとは別で、使いやすいものがもっとでて欲しいのですが）

 

 

借方・貸方、消費税の問題

会計、簿記のしくみでExcel上問題となる点があります。

1つは、借方・貸方。
複式簿記という性質上、借方を集計、貸方を集計しなければいけません。
現金なら、借方を集計した金額から貸方を集計した金額を引き、借入金ならその逆です。
これを表現しなければいけません。

もう1つは、消費税。
消費税の処理をして、集計するのは思ったより大変です。
たとえば、会議費/現金　324円という仕訳があったとき、消費税を考えると、

会議費　300　/現金　324
仮払消費税　24
という取引になります。
そして、会議費の税抜300円を集計し、仮払消費税を集計するという処理が必要です。
消費税の対象かどうかの処理もしなければいけませんし、税率の違いもあります。

さらに最大の問題は、総勘定元帳。
税務上保存しておかなければならず、税務調査時にもチェックされる資料です。
これをExcelで作るのは、非常に難しく、現実的にExcelだけで会計ができない理由でもあります。
マクロを使わなければ厳しいです。

私の場合、総勘定元帳はマクロでやっていますので、Excelだけで会計を完結できるようにはしていますが、業種、業務量によって、お客様ごとに
・Excelで入力、集計、業績把握→会計ソフトに取り込む
・Excelで入力、集計、業績把握、総勘定元帳も作成
といった2つのパターンでやっています。

 

 

Excelで仕訳データから推移表を作る方法

総勘定元帳を作らなくてもExcelで仕訳データから集計ができると便利です。
今日はその方法をご紹介します。
SUMIF、SUMIFSを使う方法、試算表を作る方法もありますが、今回は、VLOOKUP、ピボットテーブルを使って推移表を作る方法です。
私もすべてこの方法でやっています。
試算表よりも推移表の方が使いやすく、SUMIF・SUMIFSよりVLOOKUP+ピボットの方が処理が軽いからです。

消費税処理は複雑ですので、今回は消費税なし（税込経理）でやります。

①仕訳データを準備

シンプルに仕訳を入力します。
複合仕訳、預金出納帳形式で入れたいならこの形式に変換しましょう。
実際はそうやっているケースも多いです。
最終的にこの形式（複合仕訳は、諸口を使ってつなげる）を準備することになります。

②借方、貸方ごとにピボットテーブルで集計

借方科目、金額でピボットテーブルを作ります。
実際は日付（月）のデータも組み合わせますが今回は省略しました。

貸方も同様に作ります。

③推移表のフォーマットを準備

推移表のフォーマットを準備します。
上にB/S、下にP/Lを作るといいでしょう。
残高チェックのために、資産合計から負債・純資産合計を引く数式を入れておきます。
ここが0以外のときは間違っているということです。

④VLOOKUP関数を入力

まずはわかりやすいP/LからVLOOKUP関数を入力します。
VLOOKUPでピボットテーブルから数値を連動させるのです。

まずは借方のVLOOKUP関数を入れてみましょう。

入れてみるとエラーが出てしまいました。
「売上高」のセルを選択して、

ピボットテーブルのA列からB列を選択して、2列目を表示させます。
（「FALSE」は完全一致のものを探します）

この結果、エラーになってしまいました。

借方に「売上高」がないからです。

借方、貸方に必ず科目があるとは限りませんので、「エラーの場合は0にする」という処理をIFERROR関数でやります。

「売上高」は、貸方金額から借方金額を引くので、最終的にはこういった式をいれると、無事売上高の金額を集計できます。

⑤科目によって処理を分ける

この数式をコピーしていけば、完成するはずです。
「会議費」にコピーしてみましょう。
結果は−15,000になってしまいました。
「会議費」は費用科目ですので、借方から貸方を引きます。
数式を変えなければいけないのです。
書き換えてもいいのですが、それでは美しくありませんし手間もかかります。

A列に、借方ー貸方の場合は、1を入れるようにしてIFで条件を分ける方法がおすすめです。
こういった式になります。
=IF(A19=1,
IFERROR(VLOOKUP(B19,借方!A:B,2,FALSE),0)-IFERROR(VLOOKUP(B19,貸方!A:B,2,FALSE),0),
IFERROR(VLOOKUP(B19,貸方!A:B,2,FALSE),0)-IFERROR(VLOOKUP(B19,借方!A:B,2,FALSE),0))
（事例では、見やすいようにAltキー+enterで数式を改行しています。）

式をコピーすれば、P/Lが完成します。
（利益、合計部分は別途数式を入れています）

⑥B/Sの作成

数式をB/Sへコピーしてみると、マイナスになってしまいます。

B/Sは積み上げですので、前月の残高を加えなければいけません。
こういった数式になります。
=IF(A4=1,
C4+IFERROR(VLOOKUP(B4,借方!A:B,2,FALSE),0)-IFERROR(VLOOKUP(B4,貸方!A:B,2,FALSE),0),
C4+IFERROR(VLOOKUP(B4,貸方!A:B,2,FALSE),0)-IFERROR(VLOOKUP(B4,借方!A:B,2,FALSE),0))

すべての項目に数式を入れると、貸借の差額が出てしまいました。
・・・どこかで見たことがある数字です。
この数字は、利益（事例では営業利益、実際には当期純利益）ですので、利益剰余金は別途数式を入れなければいけません。

シンプルにこう入れれば大丈夫です。

差額がゼロになり、完成です。
仕訳で新しい科目を使ったときは推移表へ追加しておかないと貸借差額が出ます。

体裁を整えるとこんな感じです。

・仕訳を入力したらピボットテーブルを更新する
・貸借差額に注意する
・仕訳で新しい科目を追加したら、推移表にも追加する
といった点に注意しましょう。

他の月へコピーする場合は、ピボットテーブルを列に月を表示させ、数式をちょっと工夫しなければいけません。
=IF($A19=1, IFERROR(VLOOKUP($B19,借方!$A:$Z,COLUMN(B2),FALSE),0)-IFERROR(VLOOKUP($B19,貸方!$A:$Z,COLUMN(B2),FALSE),0),IFERROR(VLOOKUP($B19,貸方!$A:$Z,COLUMN(B2),FALSE),0)-IFERROR(VLOOKUP($B19,借方!$A:$Z,COLUMN(B2),FALSE),0))

ピボットテーブルの更新については、こういったマクロを書いて（ピボットテーブルはどれか1つを設定すれば大丈夫です。同じデータから作ったピボットテーブルがすべて更新されます）、ショートカットキーを設定しています。

Sub pivot()

    Worksheets("借方").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ1").PivotCache.Refresh
    Worksheets("推移表").Select
  

End Sub

B/Sは積み上げであること、利益剰余金の考え方を知っていないと、このしくみはできませんので、会計のしくみを学ぶのにもいい事例かと思います。

---

---

---

---

【編集後記】
昨日は、新規プロジェクトの本格的なスタート。
ここ数週間準備してきましたが、なんとか初日終了しました。
夜は個別コンサルティング。
京都からお越しいただきました。

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

恵比寿Palermo
新規プロジェクトで新しい体験