Excelで会計データを効率よく修正するしくみ

会計ソフトは便利な一方、使いにくい部分もあります。
会計データの確認、修正には、Excelを使うことも多いです。

image

会計ソフトの操作性・Excelの操作性

会計ソフトでデータをチェック・修正するときは、こういった集計表でチェックしたい金額をダブルクリックし、

image

データを修正すれば反映されます。
これは、これで便利なのですが、
一括して修正したり、置換したり、コピーしたり、削除したり、追加したりするのは少々手間です。
(弥生会計では置換がようやくできるようになりましたが、使いにくく…)
この画面もあまり長く見たくありません。

image

クラウド会計ソフトだと、こういった修正はさらに困難です。
あれは、ルールがきちんと決め、設定をしっかりやってこそ「自動的に」できあがります。
修正しようとすると、結構大変です。

もちろん、修正がないのが理想ではあるのですが、現実と理想はまた違うもの。
特に、ひとりしごとや、中小企業では、そうはいきません。
決算前にご依頼いただき、その初年度1年分をまとめて確認、修正することもあります。
(それまで税理士事務所・法人が処理していたとしても、チェックは必須です。)

そんなとき、Excelで仕訳データを修正、追加、削除できれば、便利です。

会計ソフトからデータを取り出し(エクスポート)、

image

Excel上で処理します。

データの追加、削除も楽です。

image

Excelで修正した後は、会計ソフトのデータをいったん削除し、修正後のExcelデータを戻せば(インポート)すみます。

image

ただ、Excelでこのデータを修正するとしても、修正後の残高が確認できなければ、ちょっと不便です。
このExcelデータを会計ソフトに戻して、残高を確認して、またExcelにして修正して・・・と繰り返すのは手間がかかります。

Excelでデータ修正の結果を確認できれば・・・と昔思ってやりだしたのが今回の方法です。

image

Excelで仕訳を集計しこのように数字を確認できます。
(マクロを使えば、元帳をみてチェックすることもでき、私は実際にやっています)

image

Excelで、仕訳修正の下準備

では、Excelで修正するしくみをつくってみましょう。

会計ソフトからエクスポートするデータ

会計ソフトからエクスポートするデータは、次の3つです。
・仕訳データ
・推移表データ
・試算表データ(期首)

推移表データは、Excelで推移表をつくる際に、科目の一覧をつくるために、試算表データ(期首)は、期首の残高の数字を連動させるために必要です。

仕訳データの加工

弥生会計の仕訳データは、[仕訳日記帳]で、Alt→F→E(ALT、F、Eと1つずつ押す。エクスポート)とやれば、エクスポートできます。
ファイル名は、〇〇.csvとCSVデータにしたほうが便利です。
(標準設定だと.txtです)

エクスポートしたデータは見出しがないので(見出しがあるようにエクスポートすると余計に使いにくいのでやっていません)、1行挿入して、

image

適当に「1」で入れておきましょう。

image

そのうえで、必要な項目(日付、借方科目、貸方科目、摘要)の見出しを入れます。
ここで見出しを入れるのは、のちほどピボットテーブルで必須だからです。
摘要の次のR列、S列には、借方金額(税抜)、貸方金額(税抜)の欄をつくります。

image

エクスポートしたデータで、不要な列を削除しないのは、このしくみを再利用するためです。
削除してしまうと、エクスポートした都度、削除しなければいけませんが、この方法なら、再度2行目以降に貼り付ければそのまま使えます。

データをCtrl+Tでテーブルにしておきましょう。

image

税抜経理の場合、[借方税抜]欄には、借方の金額ー借方消費税という数式を入れます。
[貸方税抜]も同様です。

※なお、税抜経理の場合、仮払消費税等、仮受消費税等の処理をするなら今回の事例プラスアルファの処理が必要です。
ただ、仕訳のチェック、修正であれば、そこまでしなくても十分でしょう。
固定資産科目の消費税処理は注意が必要です。

ex-it_06

さらに、弥生会計の場合、日付の形式が、「H.29/04/01」と、無駄な「.」が入っているのでExcel上日付として処理できません。
置換(Ctrl+H)で、.を空欄に置換しておきましょう。

image

これで日付として扱えます。

image

推移表の準備

次は、推移表の準備です。

会計ソフトからエクスポートしたデータはこのように、余分なところもあります。
Excelでは科目一覧が欲しいので、これを加工してつくりましょう。

image

弥生会計の場合、オートフィルター(Ctrl+Shift+L)で、[   を選べば、

image

余分な部分がフィルタリングされ、選択して、Ctrl+-(マイナス)で、削除できます。

image

次に、「計」で検索して、同様に削除すれば、

image

こういった科目一覧をつくれます。
(B列につくってください)
これをベースに集計していくのです。

image

のちほどの計算に使うので、A列には、
資産、費用の場合→1
負債、純資産、収益の場合→2

を入れます。

これらの区分によって、借方ー貸方にするか、貸方ー借方にするかが変わってくるからです。

ex-it_19

売上や原価のところは特に注意しましょう。

ex-it_20

期首残高をVLOOKUP関数で連動

推移表には、期首残高(前期末残高)が必要なので、試算表の期首残高を連動させます。

(推移表データには、期首残高がないので)
会計ソフトの試算表データはこのようなものです。

Excelファイルに、シート「期首残高」をつくり、貼り付けます。

image

推移表には、次のようなVLOOKUP関数を入れて、コピー(セルの右下をダブルクリック)すれば、

image

自動的にコピーできます。
ここで、年月も入れておきましょう。

image

借方、貸方をピボットテーブルで集計

次に仕訳データを集計していきます。
流れは、このようなものです。
仕訳データを借方、貸方別にピボットテーブルで集計し、それぞれからVLOOKUP関数で推移表に連動します。

image

仕訳データを選択して、ピボットテーブルをつくります。

image

借方、月、借方税抜を次のように設置し、推移表のかたちに整えましょう。

シート名は、「借方」とします。

image

ピボットテーブルが正しく集計されないのは、テーブルを使わないから | EX-IT

シート「借方」をコピーし、

image

貸方、月、貸方税抜へ変更し、シート名を「貸方」にしておきましょう。

image

借方、貸方のピボットテーブルからVLOOKUP関数で連動し、推移表

推移表に、VLOOKUP関数を入れていきます。
まずは、このように、「B2(科目名)をシート「借方」のA列からM列の一番左から探して、もし一致したら2列目を表示する」というVLOOKUP関数を入れましょう。

こういった場合、B2のBを固定したいので、Bの前に、$ をつけ(F4キーを押してもできます)、A:Mには、両方を固定したので、両方に$ をつけます(F4キー)。

image

シート「借方」を見て、間違いなく連動できているかを確認しましょう。

image

次に、貸方を連動するVLOOKUP関数を入れます。
このようにコピーして、

image

借方を貸方へ変更すれば、シート「貸方」の同じ列のデータを連動できるので楽です。

image

ただし、4月の残高を正確に出すには、前月の金額に足さなければいけません。
C列の期首残高を足しましょう。

image

これで、推移表の数字と一致します。

image

この数式をコピーすると、エラーが出ているところが。

image

これは、シート「借方」「貸方」に探している科目がないときのエラーです。
「データがないとき=エラーのときは、0にして」という意味で、IFERROR関数を使いましょう。
借方、貸方、それぞれにつけます。

=C4+ IFERROR(VLOOKUP($B4,借方!$A:$M,COLUMN(B3),FALSE),0)-IFERROR(VLOOKUP($B4,貸方!$A:$M,COLUMN(B3),FALSE),0)

image

 

IFを使わずにVLOOKUP関数の ”#N/A”エラーを処理する方法ーIFERRORー | EX-IT

これで正しく表示されました。

image

ただ、負債や収益(売上)の数字がなんだか変です。
これは、

資産・費用なら、
= IFERROR(VLOOKUP($B2,借方!$A:$M,2,FALSE),0)-IFERROR(VLOOKUP($B2,貸方!$A:$M,2,FALSE),0)

と借方ー貸方にし、

負債・純資産・収益なら

= IFERROR(VLOOKUP($B2,貸方!$A:$M,2,FALSE),0)-IFERROR(VLOOKUP($B2,借方!$A:$M,2,FALSE),0)

と貸方ー借方にしなければいけません。

※純資産の当期純利益は、特殊な数式が入ります。

image

ただ、IFで、これらの組み合わせをつくると、数式がかなり長くなるので、ちょっと工夫しています。

「もし、A列が1だったら、1、そうじゃなかったらー1」として、さきほどの
= IFERROR(VLOOKUP($B2,借方!$A:$M,2,FALSE),0)-IFERROR(VLOOKUP($B2,貸方!$A:$M,2,FALSE),0)

にかけています。

借方ー貸方に、-1をかければ、-借方+貸方、つまり、貸方ー借方になりますので。

image

これを他の月にコピーすると、やはりおかしいです。
これは、常に、ピボットテーブルの2列目から探しているからであり、その〇列目というのを、2、3、4・・・と変更する必要があります。
1つずつ修正していたら日が暮れるので、

image

列の指定を、COLUMN(B1)とし、コピーします。

COLUMN(B1)は、セルB1の列数を表示するもので、B1だと2、コピーして、C1だと3、D1だと4・・と変化するのです。

image

この数式をコピーすると、正しく計算されます。

最終的な数式はこういったものです。

=C2+IF($A2=1,1,-1)*( IFERROR(VLOOKUP($B2,借方!$A:$M,COLUMN(B1),FALSE),0)-IFERROR(VLOOKUP($B2,貸方!$A:$M,COLUMN(B1),FALSE),0))

さらにもう1つ必要なことがあります。

P/Lを見てみると、データがおかしくなっているのです。

これは、B/Sで前月残高を足すという処理をしているからですので、売上以降は、C列を足す数式を削除しましょう。

=C2+IF($A2=1,1,-1)*( IFERROR(VLOOKUP($B2,借方!$A:$M,COLUMN(B1),FALSE),0)-IFERROR(VLOOKUP($B2,貸方!$A:$M,COLUMN(B1),FALSE),0))

=IF($A2=1,1,-1)*( IFERROR(VLOOKUP($B2,借方!$A:$M,COLUMN(B1),FALSE),0)-IFERROR(VLOOKUP($B2,貸方!$A:$M,COLUMN(B1),FALSE),0))

にします。

※2となっている行数は適宜変更

ex-it_57

P/Lの利益や販管費計は、通常の数式を入れたほうがミスを防げます。

image

最終的な利益が、会計ソフトと合っていればOKです。

image

※今回の事例では、B/Sの貸借を合わせていません。データチェックだけですので。
月次の資料に使う場合は、B\Sへ流動資産計、資産の部合計などを入れ、当期純利益も反映させましょう。

Excelでの修正・確認、会計ソフトへの反映

ピボットテーブルの更新

仕訳を修正したら、ピボットテーブルを更新しなければいけません。
そうしなければ、ピボットテーブルから連動している推移表も変わらないからです。

image

シート「借方」「貸方」のいずれかで、右クリックし[更新](またはAlt+F5)で、更新できます。
(どちらかでやれば、一方も更新されます)

image

Excelマクロを入れておくとさらに便利

毎回更新するのがめんどうなので、Excelマクロを入れておくと便利です。

ピボットテーブルをクリックし、[ピボットテーブルツール]→[分析]の左側にある[ピボットテーブル名]を確認しましょう。
通常は「ピボットテーブル1」とついています。
(Excelのアップデートで、ここが全角カナになったのがうれしいです)

image

マクロは、次のように書きます。
ピボットテーブル1を更新し、シート「推移表」を表示するというマクロです。

image

 

 

さらには、ExcelでAlt+F8を押し、[オプション]でショートカットキーを設定すれば、

image

仕訳修正


ショートカットキー(ここではCtrl+m)

推移表が更新され、表示される
というのが瞬時にできます。

image

Excelマクロを実行する4つの方法。ショートカットキー・ボタン | EX-IT

会計ソフトへデータを戻す

修正が終わったら、会計ソフトへデータを戻しましょう。

弥生会計だと、仕訳の最後をクリックし、スクロールバー(右側のバー)で仕訳の最初を表示して、

image

Shiftキーを押しながらクリックすれば、すべてのデータを選択できるので、削除(Ctrl+Delete)すれば一括で消せます。
(これ、Ctrl+Aで選択できればいいのですが)
クラウド会計ソフトも同様です。

image

このあと、修正したExcelデータをインポート(Alt→F→I)します。
削除するのを忘れないようにしましょう。

なお、こういったExcelとの連携をやるなら、会計ソフトに振替伝票形式で入れてはいけません。
Excelで加工しにくくなります。
複合や諸口を使って、1行の仕訳にしましょう。
そもそもデータとは、1行のものですので。

このしくみをつくるのにも時間がかかるので、「会計ソフトでやったほうが早い」という場合もあるかもしれませんが、Excelのスキルアップのいい勉強にもなるので、ぜひ挑戦してみていただければ。

このしくみなら、会計ソフトなしで経理もできそうですが、必須の総勘定元帳をつくるには、さらに複雑なマクロが必要ですので、データを会計ソフトに入れて、元帳・決算書は会計ソフトで出すことをおすすめします。


■編集後記

昨日は、両国国技館のアンパンマンコンサートへ。
両国国技館へ行くのは、相撲、セミナーに続き3回目です。
升席で見るコンサートは意外と見やすいなと。
アンパンマンが空を飛んだり、巨大なばいきんまんロボが天井を動いたり、演出も結構派手でした。

■昨日の1日1新
※詳細は→「1日1新」
アンパンマンコンサート
上野駅エキュート イートイン

■昨日の娘日記

アンパンマンコンサート、最初は、きょとんとしていた娘。
アンパンマンたちが出てくると大喜びでした。
アンパンマンも好きですが、ばいきんまんはもっと好きなようで。
反応がまた違いました。
次回も行ってみようかと。

その後、上野動物園へ行きましたが、暑くて、娘の好きなゴリラとゾウを見て早々に帰りました。