Excelで会計データ(仕訳)を集計(推移表)・チェック(元帳)するしくみ

  • URLをコピーしました!

会計データをチェックするときには会計ソフトでやらずに Excel でやることが多いです。
そのしくみをまとめてみました。

Screenshot_4

 

会計データのチェックを会計ソフトではなくExcelでやる理由

経理の結果、蓄積された会計データのチェックは、もちろん会計ソフトでもできます。
そのほうが一般的ですし間違いがありません。

ただ、私は税理士という仕事しながら、会計ソフトが使いやすく、見やすいとは思えません。
だからこそ Excel と会計を組み合わせています。

会計ソフトではなく、Excelで会計データのチェックをやる理由は、速くてやりやすくて、落ち着くからです。
会計ソフトのあのフォント、色(何故肌色……)、操作性は、落ち着きません。

お客様はもちろん自分のデータもExcelでチェックしています。

ただ万人向けではないのは事実であり、

普通であれば一部分だけExcelを使い、会計ソフトと併用するほうがおすすめです。

たとえば

・Excelに入力したデータを会計ソフトに取り込んで(インポートして)会計ソフト上でチェックする。

・会計ソフトからデータをExcelに変換して (エクスポートして)、Excel 上でチェックし、修正は会計ソフトでやる、またExcelで資料をつくる

・会計ソフトからデータをExcelに変換してExcelでチェック・修正して、再度会計ソフトに取り込む(会計ソフトのデータは一旦削除します)。
いったものです。

Excelと経理、会計の連携はこの本に書きました。

これらの方法を、私はもちろんやっていますし、やってきましたが、 Excel から会計ソフトにインポートすること、会計ソフトからExcelにエクスポートすることが、結構面倒くさく感じます。
データ量が多くなるとエクスポートやインポートの時間もかかりますし、Excelでチェック・修正してインポートして確認してみると、再度修正が必要ということもあったりもします。

だからこそ、会計ソフトのデータをExcelに出してそこで数字を最終的に固めてから、最後は会計ソフトに戻すということをやっています。
そうするとチェック・修正・追加・削除などをすべてExcelの操作性でできますし、Excelのデザインでできるわけです。

Excel 上で会計データを集計して推移表をつくりチェックしつつ、元帳で残高をチェックすることもできます。

 

ただ、会計ソフトによってはちょっと工夫が必要です。
ネットバンクやクレジットカードのデータを連携している場合は、データのやり取り時に気をつけなければいけません。
どうしてもの場合は、連携を解除したほうが早いでしょう。
クラウド会計ソフトだと、連携したデータの修正が必要な場合は、めちゃくちゃ大変です。
(チェック、修正の必要がないくらい自動化しているのが理想ですが。あくまで理想です)

万人向けではないのですが、このしくみをつくるには Excelの重要なスキルを使えますので、よいトレーニングもなります。
(セミナーのテーマとしてやろうかなと思ってるところではあります。)

なお、必要なExcelスキルは
・テーブル
・関数(SUM、INT、VLOOKUP、IFERROR、COLUMN)
・ピボットテーブル
そして、
・Excelマクロ(あったほうが便利です)。
です。

 

会計データのチェックをExcelでやるしくみ

次のようにして、会計データのチェックをExcelでやるしくみをつくっています。
下準備も必要です。
初回は大変ですが、しくみをつくれば、あとは、新しい会計データをエクスポートするだけでよくなります。

1 会計データをエクスポート

今回の事例では、会計データを弥生会計からエクスポートしています。
(クラウド会計ソフトでも、弥生形式でエクスポートできるので、 弥生会計のパターンでしくみをつくっておけば、クラウド会計ソフトでもつかえます。)

エクスポートするのは仕訳データ(仕訳日記帳)です。

image
仕訳日記帳を表示し(Alt→C→Enter)、[ファイル]→[エクスポート](Alt→F→E)でエクスポートしましょう。
Excelボタンでエクスポートしたデータは使いにくいのでおすすめしていません。

会計ソフトのデータをExcelで使うことを考えると、1つ気をつけなければいけないことがあります。
それはデータ1行で1つの意味を持つように仕訳を入れることです。
具体的にいえば複合仕訳を使わないということになります。

給与の仕訳であれば[諸口]を使って1行ずつデータを入力または処理するようにしましょう。

給与手当 xxx 預り金 xxx

普通預金 XXX

ではなく、
給与手当 xxx 諸口 xxx

諸口   xxx 預り金 xxx

諸口   xxx 普通預金 xxxx

といった形です。

複合仕訳であっても、Excelで扱えないことはないのですが、手間がかかります。
どっちを主に置くかということを考えると、Excelでデータを加工できたほうが楽ですので、仕訳データ形式で入れましょう。
そもそも入力するわけではなく、私の場合システムからデータを連携したり、 Excel から取り込んだりしています。

出納長形式で入力する場合も、会計ソフトにインポートするときは、データ形式にしなければいけません。

エクスポートしたデータは、データの見出し(1行目のタイトル)がついていないのでつけておきましょう。
必要な部分だけで構いません。

image

使わないところは適当に数字でも入れておきます。

見出しをつけたら、Excelのテーブルにしておきましょう。
Ctrl+ T→ Enter です。

image

 

こういったエクスポートしたデータは、列を削除しないのがポイントです。
ここで削除してしまうと次にエクスポートしたときも削除しなければいけなくなりますし、データを会計ソフトに戻す(インポート)ときも困ります。

弥生会計からエクスポートしたデータの日付データの形式はExcelで加工できないできません。

image
これをExcelで加工できるように一工夫しておきましょう。
なぜこれをこんな形式でエクスポートできるようにしてるのかわかりませんが。

元号(H)の後に、ピリオドが入ってるのが邪魔ですので、このピリオドを置換で取り除きます。
日付のD列を選択して、Ctrl+Hで置換のボックスを出し、[置換前の文字列]にピリオド、[置換後の文字列]は空欄にしておくと、置換前の文字列を消すことができます。

image
これでピリオドを消しておけばExcelでも扱えますし、なおかつこのデータを弥生会計に戻すこともできます。

さらに準備が必要です。
仕訳データにあるのは、消費税込の金額と消費税額ですので、税抜金額を準備しておかなければいけません。

image
もちろん消費税免税の場合や、税込で経理している場合は必要ないのですが、税抜きで準備しておけばどのケースでも使えるようになります。

Excelデータで消費税区分を修正した場合に、たとえば、対象外のものを課税と変更したら消費税額も修正しなければいけません。

弥生会計の場合は、消費税区分が対象外の場合、消費税額に何か数字が入っていても無視して0で取り込んでくれます。
ただExcelで修正してチェックする方法をやるのであれば、[対象外]だと消費税額を0にしないといけません。
毎回それで気を使うのも面倒ですので、数式をあらかじめ入れておき、消費税区分によって消費税額が変わるようにしておきましょう。

IF関数で条件を細かく設定してもよいのですが、私の場合は消費税区分の特徴を利用しています。
消費税がかかるものというのは、弥生会計の場合、「課対仕入内8%」や「課税売上8%」と、一番右が「%」です。
「もし、消費税区分の一番右が%だったら、消費税を計算する(端数切捨て)。そうでなかったら0という数式を入れます。
消費税8%だと、こういった数式です。
image

その数式を借方・貸方それぞれに入れておきましょう。

さらには税抜金額も計算しておきます。
前述したように、会計ソフトからエクスポートしたデータの列は削除しないようにしますので、データの一番端に入れましょう。
弥生会計のデータは、Y列までデータが入っていますので、Z列から追加するデータを入れていきます。

Z 列に入れるのは、借方の税込金額から消費税額を引いたもの、AA列に入れるのは、貸方の同様のものです。

image
消費税の対象外のもの、たとえば現金や普通預金、租税公課などは消費税額が0になりますので、税込金額でも税抜き金額でも同じ金額になるわけです。
(なお会計ソフトの設定によっては消費税の処理で端数が出る可能性もあります)。

データをテーブルにしておけば2行目(見出しの下)数式を入れれば、それですべての行に数式が入りますので、その便利さをいかしましょう。

 

2 ピボットテーブルで会計データを集計する

会計データの集計は一工夫必要です。
借方つまり左側と、貸方つまり右側でそれぞれ集計しなければいけません。
そのしくみにピボットテーブルを使っています。

関数ですべて計算したり、マクロでやる方法もあったりするのですが、ピボットテーブルがシンプルでわかりやすいかなと

まずは借方の科目と借方の税抜金額で集計し、それを月別に並べてみましょう。
ピボットテーブルでは次のように集計します。

image

このピボットテーブルのシート名を「借方」にして付け、そのシートをコピーして、もう1つピボットテーブルをつくります。
シート名は「貸方」にして、集計する項目を貸方科目、貸方税抜金額にしましょう。

これで集計の準備は整いました。

image

3 推移表のフォーマットを準備する

ピボットテーブルで集計した、数値を使って推移表として集計していきます。

推移表とはこのような月別の推移を示したものです。
(サンプルは4から6月の3か月です)

image

image

データのチェックするにはこれがおすすめです。
私はこれでしかしか使っていません。

推移表のフォーマットは、会計ソフトで使っている、つまり経理で使っている科目をすべて入れなければいけません。
後でチェックもできますのである程度入れといて数式を入れていくという形で大丈夫です。

BS と PL それぞれに入れていきます。
月についても会社の決算に合わせて入れておきましょう。

チェックのために売上総利益や販管費計、営業利益、経常利益といったところは連動させずに、足したり引いたりする数式をそれぞれ入れておくのがオススメです。
image
そうしたほうがVLOOKUP関数による連動ミスを見つけやすくなります。

BS の方も、資産合計と負債純資産合計のをチェックして、連動や集計がうまくいってるかを確認しましょう。

image

さらに BS は、前期残高があるので(設立1期でない限り)、これも入れておきましょう。

この推移表をピボットテーブルの借方、貸方からつくるには、たとえば売上高だと、貸方ー借方、経費だと借方ー貸方となります。

科目によってそのパターンを分けつつ集計していかなければいけません。
集計にはVLOOKUP関数を使っています。
集計というよりデータを連動するというイメージです。
(SUMIF関数でもできるのですが、デメリットもあるのでVLOOKUPにしています。)。

借方ー貸方か、貸方ー借方かというパターンを、推移表の一番左 A 列に入れておきます。
資産、費用であれば借方ー貸方なので、1を入れ、負債、純資産、収益であれば貸方ー借方で2を入れておきます。
(入れるのは何でもよく区別がつけば大丈夫です)

image

これをIF で判定するわけです。

まず、VLOOKUP関数で、科目を読み取って、それをシート「借方」のピボットテーブルから探して、それぞれの月のものを読み取ります。
月の位置を判定するにはCOLUMN関数を使いましょう。
image
この辺の話はこちらの記事にも書いています。

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

手間をかけないExcel資料作成。会計ソフト推移表→決算予測・売上グラフ・経費グラフ・移動年計グラフ・予算実績比較表・目標達成度グラフ | EX-IT

借方についてVLOOKUP関数を入れて、そこから貸方についてVLOOKUP関数を入れて引きましょう。
image
ただ、これだとVLOOKUP関数で探したものが見つからないときに、エラー# N/Aが出てしまいます。

借方にはあるけど貸方にはない、貸方にはあるけど借方にはないということはありえるはずです。
そのためエラー処理としてIFERROR関数を使いましょう。

IFERROR関数は、=IFERROR(〇,△)で、「もし〇がエラーがだったら、△を表示する」というものです。
この〇の部分に、VLOOKUP関数のでつくったものをすべて入れます。
そして、もしエラーが出た場合は0にするという意味で、△に0を入れるわけです。
これを借方と貸方それぞれにやります。

image

めちゃくちゃ長い数式になってしまうのですが、致し方ありません。
ここを短くしようとすることもできるのですが、そうすると他の部分が複雑になってしまうので、 バランスをとってこういうやり方にしています。

 

以上の数式を区分によって場合分けします。

IF 関数を使って、もし A 列が1だったら借方ー貸方、左から右を引くわけです。
それをすべてそのまま書いてしまうと、とてつもなく長い数式になってしまいますのでちょっと工夫しました。
「借方ー貸方」という数式をつくっておき、そのまま「借方ー貸方」なら、1をかけます。
そうではなく「貸方ー借方」にするなら、-1を「借方ー貸方」にかければ、「貸方ー借方」になるわけです。
これを利用して、IFで、「もし A 2が1だったら1、そうでなかったらー1という判定をし、それを先ほどのVLOOKUP関数の式をかけるというものです。
次のような数式になります(セルD4に入れる場合)。

 

=IF($A4=1,1,-1)*(IFERROR(VLOOKUP($B4,借方!$A:$M,COLUMN(B3),FALSE),0)-IFERROR(VLOOKUP($B4,貸方!$A:$M,COLUMN(B3),FALSE),0))

image

 

さらに B/S= 貸借対照表については、前月の残高に足していかなければいけません。
B/Sは積み重ねですので。

たとえば5月の残高は4月残高+5月の増減になります。
そのため B/Sは、前月のものを足すように次のような数式にしておきましょう。

=C19+IF($A19=1,1,-1)*(IFERROR(VLOOKUP($B19,借方!$A:$M,COLUMN(B18),FALSE),0)-IFERROR(VLOOKUP($B19,貸方!$A:$M,COLUMN(B18),FALSE),0))

image

 

ただし例外があります。
1つは利益剰余金。
利益剰余金は、VLOOKUP関数で導き出せるもの ではありません。
前月の利益剰余金+当月の当期純利益を足していくことを数式にしましょう。
事例では経常利益でやっています。
image

もう1つの例外は仮払消費税等、仮受消費税等です。
これらも仕訳データを集計してできるわけではありません。
消費税の課税区分に応じて、もし取引の借方金額、貸方金額が消費税の課税対象であるなら、それを税抜きにし、仮払消費税等や仮受消費税等が生まれます。
これをやるにはもう1つ消費税用の集計が必要です。

別シートを「消費税」という名前にして、ピボットテーブルで借方の消費税区分・月別に借方の消費税額を集計します。

image

そのピボットテーブルを同じシートにコピーして、貸方分をつくりましょう。

こうすれば上のピボットテーブルの合計の仮払消費税等、下のピボットテーブルの合計は仮受消費税等になります。
これをそれぞれ推移表のピボットテーブルで集計するわけです。

image

これもVLOOKUP関数で連動させます。
ただし、仮払消費税等と仮受消費税等の厄介なところは、最後の最後で消費税の金額=未払消費税等を導き出すときには、データ(仕訳)で仮払消費税等と仮受消費税等を使うのです。

ということは、他の科目と同様の処理に加えて、上記の処理をしなければいけません。

結果的に次のようなめちゃくちゃ長い数式になります。
(課税貨物の仕入がある場合はもっと複雑になりますので、私は別科目にして処理しています)。

 

=IF($A8=1,1,-1)*(IFERROR(VLOOKUP($B8,借方!$A:$M,COLUMN(B7),FALSE),0)-IFERROR(VLOOKUP($B8,貸方!$A:$M,COLUMN(B7),FALSE),0))

 

image

また、この処理の方法をすると、たとえば経費科目が貸方にある場合で仮払消費税という処理される場合、会計ソフトとExcelの消費税処理が異なる場合もありますが、トータルは一緒になるので私は気にしていません。

上記の処理をして、資産合計と負債・純資産合計の差額を計算しておき、これがゼロになれば集計がうまくいってるということになります。
うまくいかない場合は次のようなところをチェックしましょう。

・仕訳データにある科目で推移表にない科目がある
・仕訳データの科目名と推移表の科目名が違う。空白があっても駄目
・消費税の処理、課税区分と税額の対応が間違っている。
・Excelで金額を修正したときに、借方金額も貸方金額も同様に修正していない。貸借が一致を確認。
・ピボットテーブルをつくる前にテーブルをつくっていない

 

4 Excelの元帳でチェック

これで集計ができたとしても、たとえば残高をチェックする場合や残高がマイナスになっている場合、残高が合わない場合は該当のデータを探すのは困難です。
やはり科目別に集計してみないと Excel でやるメリットは薄れます。
オートフィルターである程度探せますが、残高のチェック、借方・貸方という簿記特有のしくみのためうまくいきません。

いわゆる元帳というものをつくる必要があり、これを手作業であるのは大変です。
マクロが必要となります。

そしてこの元帳は、保管しておかなければいけません。

次のようなしくみです。

たとえば「未払費用」の残高をチェックする場合、元帳をつくるという場合は、推移表で「未払費用」を選びます。
推移表で「未払費用」の行のいずれかにカーソルがある状態で、マクロをショートカットキーで実行すれば元帳ができるというしくみです。

image
カーソルがあるセルの行を読み取って、その行の1列目に区分があり(借方ー貸方か、貸方ー借方か)、その行の2列目に科目名があり、その行の3列目に期首残高(B/Sの場合)を使います。

まずは、仕訳データの借方科目を「未払費用」でフィルタリングをかけ表示されたものを転記。
image

次に、貸方科目を「未払費用」でフィルタリングして表示されたものを先に転記したものの下に転記して、日付順に並べ替えます。
これで元帳ができ上がるわけです。

 

image

 

残高には数式が入っていますのでデータを修正すればその残高がに反映されるされて残高を合わせることもできます。

image

このしくみをマクロでやっています。

次の記事を参考にしていただければ。

総勘定元帳(元帳)をExcelマクロで作るアイデア | EX-IT

 

ただし、会計ソフトと違い、元帳を修正してデータが修正できるというわけではないというところです。
そこは会計ソフトにはかないません。
それでも Excel 上で元帳をチェックして、仕訳データを修正することはできます。

仕訳データを修正した後は、同じくマクロでピボットテーブルを更新して、推移表に反映するようにしています。
データを修正してもピボットテーブルを更新しない限り、集計結果(推移表)は変わりません。
そこをマクロで効率化しているわけです。

 

なお、この残高を合わせる場合、通常の勘定科目と補助科目があり得ます。
Excelでやる場合もこれが厄介です。

補助科目ごとに集計するという方法もありますし、私は実際にやっています。
ただ補助科目をつくる必要があるかどうかというのは考えてみたほうがいいでしょう。

多くの場合、補助科目を私はなくしています。
自分の経理も含めて。
たとえば、普通預金で補助科目がSBI、ゆうちょではなく、「SBI」という科目、「ゆうちょ」という科目にしているわけです。
そうすればExcelの処理も楽になります。

補助科目なんてもういらんがなという考えです。
元々はパソコンの性能が低い時代、手書きや紙が前提だった時代にできたのが会計ソフトですので、やはり古いところは古いわけです。
補助科目を使わず、SBI、ゆうちょと細かくやっておくと、手作業や紙に印刷するときに困るわけです。

しかし、
データ上で見るなら、データがそれなりに多くなっても問題ありません。

 

補助科目を使わず、SBI、 ゆうちょという科目で処理しておき、決算書ではそれらをまとめて普通預金にするということは十分できます。

5 会計ソフトに戻す

Excelでのチェックが終わったら、その修正結果を仕訳データに反映させます。
そして、元々は会計ソフトからエクスポートしたものですのでそのままインポートできるのです。
会計ソフトによってはインポートとエクスポートの形式が違うものもあり、その場合にはもう1つマクロを入れるしかありません。
弥生会計の場合はエクスポートした形でそのままインポートできます。

A列から Y 列のデータ(見出しは除く)を新しいシートにコピーして CSV データで保存すれば、それをインポートできるのです。

私はここにもマクロを入れそれを保存しており、その保存するマクロを RPA で動かし、「データをコピーしてCSVファイルとして保存し、弥生会計を立ち上げて取り込む」というしくみをつくっています。
注意しなければいけないのは、会計ソフトの元のデータを削除しなければいけないということです。
また、この場合、伝票番号というデータやデータ入力日というのは変わってしまいます。
そこを重視する場合は Excelのこの方法を使うことはできません。

マクロ部分を除いてサンプルファイルをつくってみました。

EX-ITサンプル 仕訳→推移表・元帳.xlsx

 

実際にはマクロを入れつつ、データから資金繰り表をつくるしくみも入れています。
資金繰り表も会計ソフトでつくるよりExcelでつくったほうが細かく、わかりやすくかんたんにできるのです。

Excelで会計ソフトのデータから資金繰り表を作る方法[入門編] | EX-IT

 

Excel会計のパターン

以上のしくみを使って、Excelで会計データチェックをするパターンとしては次のようなものがあります。

会計ソフトがメイン

会計ソフトでメインの場合、そのデータを
Excelで チェックして、上記の流れで再度戻しています。
または、お客様に修正点を伝えて修正していただく、修正データをお送りしてそれを取り込んでいただくというパターンです。
クラウド会計ソフトを使っている場合も会計ソフトがメインとなり、データ量や取引の複雑さ、ソフトによってExcelでやる範囲が変わります。

会計ソフトがサブ

会計ソフトを使わない場合、Excelに入力していただき、それを集計してデータの形にしてから上記のしくみにしています。
会計ソフトを導入しないでそのしくみを入れているお客様もあり、最終的に決算書その他の書類のために、私の会計ソフトに取り込むだけです。

システムで売上を管理していたり、経費精算システムがあったりする場合は、わざわざそれを会計ソフトに取り込まず、Excelで加工してExcelで集計して行ったほうが楽です。
日々の経理はそうやっておき、決算だけ取り込む場合もあります。

そして私の経理の場合もこのしくみです。
Excelにデータ形式で蓄積していき、それを上記のしくみで集計してチェックし、判断に使っています。
私の場合は、家計簿・税理士・会社の3つに分ける必要があり、それをそれぞれ経理していたら大変です。
すべてのデータを同じシートに入れて、そこに符号を入れて分けています。
決算のときはその区分ごとにデータを分けてそれぞれ集計しているわけです。
最後の最後は、書類や保管用として会計ソフトに入れてはいます。

 

データは、、次のようにいろんなところから集めています。
〇毎日経理するもの
現金や自分が立て替えたカードやSuica、QR コード決済などは、翌朝に音声認識入力で記録し Google スプレッドシートからExcelにうつしています。
音声認識入力で経理。GoogleAssistant×Googleスプレッドシート | EX-IT

〇月1回経理するもの
預金データや Amazon のデータや店頭で払わないカードデータは、月1回連携しているサービス、freeeやマネーフォワードの家計簿(Amazonの履歴はこれが一番取りやすい)からデータをExcelに集めています。
その後マクロで整理し、上記のデータに蓄積しています。
(MoneyTreeは不具合で使えなくなり……)

売上データは、請求書を発行する税務顧問の場合は、毎月1日に発行するとともにデータとして記録しています。
( Excel マクロでつくり、RPA&メール・WEBゆうびんで送っています)。
RPAで郵送を自動化 |UiPath&Webゆうびん | EX-IT
セミナーや個別コンサルティング、動画や音声コンテンツの売上は、売上が立ったときに音声認識入力で入れています。
その後Excelにうつしているという形です。

 

Excelで経理をやるしくみというのはつくれなくもありませんが、古くても信頼性という意味では会計ソフトのほうが優れているのも事実です。
ただ会計ソフトと並行して使ったり、一部をExcelでやるというのは、やはりおすすめですが、全部をExcelでやるのは万人におすすめはできません。
とはいえ、経理のしくみを知るにもExcelのしくみをするにも役立ちますので、ぜひ挑戦してみるていただけるとうれしいです。

 

 

 



■編集後記

日曜日のアクアスロン(スイム2.0㎞ラン20㎞)に向けて、実家宮崎へ。
今回は、娘(2歳)と2人です。
以前も2人で飛行機に乗ったことはありましたが、そのときは、後から妻が追いかけてくる形でした。
今回は、帰りまで2人です。

懸念の機内は、羽田空港で買った東京バナナのくまバージョンを気に入っていたので、終始ご機嫌でした。

■昨日の「1日1新」

娘と宮崎の生協
実家でChomeCast

 

■昨日の娘日記

実家に着くと「ママは?」「ママは?」と。

家にはママがいるという感覚なのかもしれません。
ずっと探していて、どうなることやらと思っていましたが、おばあちゃんと風呂に入りご飯を食べたりしてたら、言わなくなりました。
夜もいつも通りタブレット(30分で電源が切れる)をして、寝ました。

  • URLをコピーしました!