あれば貸方ー借方で2を入れておきます。VLOOKUP関数で、科目を読み取って、それをシート「借方」のピボットテーブルから探して、それぞれの月のものを読み取ります。月の位置を判定するにはCOLUMN関数を使いましょう。この辺の話はこちらの記事にも書いています。EX-IT手間をかけないExcel資料作成。EX-IT借方についてVLOOKUP関数を入れて、そこから貸方についてVLOOKUP関数を入れて引きましょう。N/Aが出てしまいます。借方にはあるけど貸方にはない、貸方にはあるけど借方にはないということはありえるはずです。そのためエラー処理としてIFERROR関数を使いましょう。IFERROR関数は、=IFERROR(〇,△)で、「もし〇がエラーがだったら、△を表示する」というものです。この〇の部分に、VLOOKUP関数のでつくったものをすべて入れます。エラーが出た場合は0にするという意味で、△に0を入れるわけです。これを借方と貸方それぞれにやります。長い数式になってしまうのですが、致し方ありません。バランスをとってこういうやり方にしています。以上の数式を区分によって場合分けします。列が1だったら借方ー貸方、左から右を引くわけです。それをすべてそのまま書いてしまうと、とてつもなく長い数式になってしまいますのでちょっと工夫しました。「借方ー貸方」という数式をつくっておき、そのまま「借方ー貸方」なら、1をかけます。なく「貸方ー借方」にするなら、-1を「借方ー貸方」にかければ、「貸方ー借方」になるわけです。2が1だったら1、そうでなかったらー1という判定をし、それを先ほどのVLOOKUP関数の式をかけるというものです。次のような数式になります(セルD4に入れる場合)。貸借対照表については、前月の残高に足していかなければいけません。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))ただし例外があります。前月の利益剰余金+当月の当期純利益を足していくことを数式にしましょう。事例では経常利益でやっています。これらも仕訳データを集計してできるわけではありません。消費税の課税対象であるなら、それを税抜きにし、仮払消費税等や仮受消費税等が生まれます。これをやるにはもう1つ消費税用の集計が必要です。別シートを「消費税」という名前にして、ピボットテーブルで借方の消費税区分・月別に借方の消費税額を集計します。そのピボットテーブルを同じシートにコピーして、貸方分をつくりましょう。すれば上のピボットテーブルの合計の仮払消費税等、下のピボットテーブルの合計は仮受消費税等になります。これをそれぞれ推移表のピボットテーブルで集計するわけです。これもVLOOKUP関数で連動させます。仮払消費税等と仮受消費税等の厄介なところは、最後の最後で消費税の金額=未払消費税等を導き出すときには、データ(仕訳)で仮払消費税等と仮受消費税等を使うのです。いうことは、他の科目と同様の処理に加えて、上記の処理をしなければいけません。結果的に次のようなめちゃくちゃ長い数式になります。(課税貨物の仕入がある場合はもっと複雑になりますので、私は別科目にして処理しています)。=IF($A8=1,1,-1)*(IFERROR(VLOOKUP($B8,借方!$A:$M,COLUMN(B7),FALSE),0)-IFERROR(VLOOKUP($B8,貸方!$A:$M,COLUMN(B7),FALSE),0))また、この処理の方法をすると、たとえば経費科目が貸方にある場合で仮払消費税という処理される場合、会計ソフトとExcelの消費税処理が異なる場合もありますが、トータルは一緒になるので私は気にしていません。上記の処理をして、資産合計と負債・純資産合計の差額を計算しておき、これがゼロになれば集計がうまくいってるということになります。うまくいかない場合は次のようなところをチェックしましょう。・仕訳データにある科目で推移表にない科目がある・仕訳データの科目名と推移表の科目名が違う。・消費税の処理、課税区分と税額の対応が間違っている。・Excelで金額を修正したときに、借方金額も貸方金額も同様に修正していない。Excelの元帳でチェックこれで集計ができたとしても、たとえば残高をチェックする場合や残高がマイナスになっている場合、残高が合わない場合は該当のデータを探すのは困難です。やるメリットは薄れます。オートフィルターである程度探せますが、残高のチェック、借方・貸方という簿記特有のしくみのためうまくいきません。いわゆる元帳というものをつくる必要があり、これを手作業であるのは大変です。マクロが必要となります。この元帳は、保管しておかなければいけません。次のようなしくみです。「未払費用」の残高をチェックする場合、元帳をつくるという場合は、推移表で「未払費用」を選びます。推移表で「未払費用」の行のいずれかにカーソルがある状態で、マクロをショートカットキーで実行すれば元帳ができるというしくみです。カーソルがあるセルの行を読み取って、その行の1列目に区分があり(借方ー貸方か、貸方ー借方か)、その行の2列目に科目名があり、その行の3列目に期首残高(B/Sの場合)を使います。仕訳データの借方科目を「未払費用」でフィルタリングをかけ表示されたものを転記。次に、貸方科目を「未払費用」でフィルタリングして表示されたものを先に転記したものの下に転記して、日付順に並べ替えます。これで元帳ができ上がるわけです。残高には数式が入っていますのでデータを修正すればその残高がに反映されるされて残高を合わせることもできます。このしくみをマクロでやっています。次の記事を参考にしていただければ。EX-ITただし、会計ソフトと違い、元帳を修正してデータが修正できるというわけではないというところです。そこは会計ソフトにはかないません。上で元帳をチェックして、仕訳データを修正することはできます。仕訳データを修正した後は、同じくマクロでピボットテーブルを更新して、推移表に反映するようにしています。データを修正してもピボットテーブルを更新しない限り、集計結果(推移表)は変わりません。そこをマクロで効率化しているわけです。この残高を合わせる場合、通常の勘定科目と補助科目があり得ます。Excelでやる場合もこれが厄介です。補助科目ごとに集計するという方法もありますし、私は実際にやっています。補助科目をつくる必要があるかどうかというのは考えてみたほうがいいでしょう。多くの場合、補助科目を私はなくしています。普通預金で補助科目がSBI、ゆうちょではなく、「SBI」という科目、「ゆうちょ」という科目にしているわけです。すればExcelの処理も楽になります。補助科目なんてもういらんがなという考えです。元々はパソコンの性能が低い時代、手書きや紙が前提だった時代にできたのが会計ソフトですので、やはり古いところは古いわけです。補助科目を使わず、SBI、ゆうちょと細かくやっておくと、手作業や紙に印刷するときに困るわけです。データ上で見るなら、データがそれなりに多くなっても問題ありません。ゆうちょという科目で処理しておき、決算書ではそれらをまとめて普通預金にするということは十分できます。会計ソフトに戻すExcelでのチェックが終わったら、その修正結果を仕訳データに反映させます。元々は会計ソフトからエクスポートしたものですのでそのままインポートできるのです。会計ソフトによってはインポートとエクスポートの形式が違うものもあり、その場合にはもう1つマクロを入れるしかありません。弥生会計の場合はエクスポートした形でそのままインポートできます。データで保存すれば、それをインポートできるのです。動かし、「データをコピーしてCSVファイルとして保存し、弥生会計を立ち上げて取り込む」というしくみをつくっています。注意しなければいけないのは、会計ソフトの元のデータを削除しなければいけないということです。この場合、伝票番号というデータやデータ入力日というのは変わってしまいます。Excelのこの方法を使うことはできません。マクロ部分を除いてサンプルファイルをつくってみました。.xlsx実際にはマクロを入れつつ、データから資金繰り表をつくるしくみも入れています。資金繰り表も会計ソフトでつくるよりExcelでつくったほうが細かく、わかりやすくかんたんにできるのです。EX-ITExcel会計のパターン以上のしくみを使って、Excelで会計データチェックをするパターンとしては次のようなものがあります。チェックして、上記の流れで再度戻しています。お客様に修正点を伝えて修正していただく、修正データをお送りしてそれを取り込んでいただくというパターンです。クラウド会計ソフトを使っている場合も会計ソフトがメインとなり、データ量や取引の複雑さ、ソフトによってExcelでやる範囲が変わります。会計ソフトがサブ会計ソフトを使わない場合、Excelに入力していただき、それを集計してデータの形にしてから上記のしくみにしています。会計ソフトを導入しないでそのしくみを入れているお客様もあり、最終的に決算書その他の書類のために、私の会計ソフトに取り込むだけです。システムで売上を管理していたり、経費精算システムがあったりする場合は、わざわざそれを会計ソフトに取り込まず、Excelで加工してExcelで集計して行ったほうが楽です。日々の経理はそうやっておき、決算だけ取り込む場合もあります。私の経理の場合もこのしくみです。Excelにデータ形式で蓄積していき、それを上記のしくみで集計してチェックし、判断に使っています。3つに分ける必要があり、それをそれぞれ経理していたら大変です。すべてのデータを同じシートに入れて、そこに符号を入れて分けています。決算のときはその区分ごとにデータを分けてそれぞれ集計しているわけです。最後の最後は、書類や保管用として会計ソフトに入れてはいます。データは、、次のようにいろんなところから集めています。スプレッドシートからExcelにうつしています。データや店頭で払わないカードデータは、月1回連携しているサービス、freeeやマネーフォワードの家計簿(Amazonの履歴はこれが一番取りやすい)からデータをExcelに集めています。その後マクロで整理し、上記のデータに蓄積しています。(MoneyTreeは不具合で使えなくなり......)売上データは、請求書を発行する税務顧問の場合は、毎月1日に発行するとともにデータとして記録しています。マクロでつくり、RPA&メール・WEBゆうびんで送っています)。EX-ITセミナーや個別コンサルティング、動画や音声コンテンツの売上は、売上が立ったときに音声認識入力で入れています。その後Excelにうつしているという形です。Excelで経理をやるしくみというのはつくれなくもありませんが、古くても信頼性という意味では会計ソフトのほうが優れているのも事実です。会計ソフトと並行して使ったり、一部をExcelでやるというのは、やはりおすすめですが、全部をExcelでやるのは万人におすすめはできません。いえ、経理のしくみを知るにもExcelのしくみをするにも役立ちますので、ぜひ挑戦してみるていただけるとうれしいです。アクアスロン(スイム2.0kmラン20km)に向けて、実家宮崎へ。今回は、娘(2歳)と2人です。以前も2人で飛行機に乗ったことはありましたが、そのときは、後から妻が追いかけてくる形でした。今回は、帰りまで2人です。懸念の機内は、羽田空港で買った東京バナナのくまバージョンを気に入っていたので、終始ご機嫌でした。実家に着くと「ママは?」「ママは?」と。家にはママがいるという感覚なのかもしれません。探していて、どうなることやらと思っていましたが、おばあちゃんと風呂に入りご飯を食べたりしてたら、言わなくなりました。夜もいつも通りタブレット(30分で電源が切れる)をして、寝ました。転職・独立に必要な力。よういち)「ひとりしごと」=雇われない雇わない生き方を、時間・お金の両面でサポート。『税理士のためのRPA入門』『やってはいけないExcel』など14冊。日課は1日1新、ブログ。