Excelで数値のプラスマイナスを反転して前期の決算整理仕訳を効率的に洗い替える方法

売上や経費を計上するタイミングは発生主義が原則で、現金主義は簡易的な方法です。

以前その辺りの内容について取り上げたことがあります。

請求書の日付は請求日ではなく請求締日を書くべき！　理由は発生主義で経理できなくなってしまうから

 

現金主義を採用している場合は決算のときに決算整理仕訳として以下のような仕訳を追加することで発生主義に修正します。

売掛金　/　売上　×××

仕入　/　買掛金　×××

 

このとき前期の決算で計上した決算整理仕訳は以下のような仕訳を計上する(「洗い替える」・「逆仕訳をきる」などといいます)必要があります。　

前期と借方・貸方に入る科目が逆になります。

売上　/　売掛金　×××

買掛金　/　仕入　×××

 

前期の洗い替えるべき仕訳の数が多いと手入力だとなかなか大変です。

弥生会計(以下、弥生)だと前期の決算整理仕訳をコピペすることも可能ですが、科目の借方・貸方を逆にするのは手入力なのでやっぱりそれなりに手間がかかります。

だったら科目の借方・貸方は同じなまま、Excelで数値だけ一気にプラスからマイナスに反転できれば手間がかからないのでは？

というのが今回紹介するやり方。

売上　/　売掛金　×××

ではなく

売掛金　/　売上　-×××

と弥生の当期のデータで計上することが目標になります。

 

弥生から前期の決算整理仕訳をエクスポート

まず前期の決算整理仕訳をExcelにエクスポートします。

今回のデータは

前期:1期(平成31年4月1日-令和2年3月31日)

当期:2期(令和2年4月1日-令和3年3月31日)

です。

仕訳日記帳から対象となる仕訳を検索機能などでピックアップ。

ちなみに弥生では通常の仕訳とは別に「決算整理仕訳」を登録することができ、「期間」も他の月と独立して表示することもできますが仕訳をデータとして集計したりグラフにしたりするときにとても扱いづらくなるので私はまったく使っていません。

普通に決算日(今回の場合は「3/31」)で登録するだけで実務上特に困ることはありません。

伝票No.が登録順に自動登録されているので決算整理仕訳入力開始時の伝票No.だけ確認すれば決算整理仕訳だけ抽出するのは簡単です。

 

仕訳日記帳の画面から

ファイル→エクスポート→OK

で抽出した仕訳を出力できます。

「出力先」の最後に「.csv」をつけることにより出力したデータをExcelで開けます。

 

 

Excelで数値をマイナスに反転する方法

Excelで出力したデータが入ったファイルを開き、数値と日付を修正していきます。

まずは数値から。

出力されたデータより下のどのセルでもいいので「-1」と入力。

この「-1」をコピーします。

そしてマイナスにしたい数値が入った、I、J、O、P列の数値が入ったセルをすべて選択します。

I、J列を選んだあと

Shift+F8

を押すと

離れたO、P列を同時に選ぶことができます。

 

選択後、

ホーム→貼り付け→形式を選択して貼り付け

 

乗算→OK

 

で数値をマイナスにできます。

「-1」をコピーした後、ショートカットキーなら

Alt→E→S

で「形式を選択して貼り付け」を呼び出すこともできます。

覚えればこのショートカットキーの方が断然早いですよ。

 

数値が全部マイナスに反転しているのが確認できます。

「-1」を入れたセルは不要なので削除しておきましょう。

 

日付を当期の決算日に置換

次にD列の日付を当期の決算日(令和3年3月31日)に置換します。

「R.02」の部分を「R.03」に置換すればOKですね。

 

日付を選択して

ホーム→検索と選択→置換

から置換機能を選択(ショートカットキーは「Ctrl+H」。こっちの方が断然早いです)。

 

置換前の文字列を上の行に、置換後の文字列を下の行に入力→すべて置換

で置換できます。

 

これで当期に取込める状態になったので上書き保存します。

 

弥生へインポート

弥生のデータを当期分に切り替えて仕訳日記帳を開いたら、

ファイル→インポート

から「インポートファイル名」で先ほど上書き保存したファイルを選び「OK」を押します。

注意書きにあるとおり、インポート前にはバックアップをしておきましょう。

 

仕訳データがインポートできました！

 

ただしこのタイミングでエラーが起きることも。

エラーが発生する原因としては

Excelで入力した「-1」の削除忘れがありがちです。

また、「-1」をDeleteで消しただけだとエラーになることも。

実は今回の記事を作成していく中でも「-1」はDeleteで消しただけではエラーになってしまいました。

おそらく「-1」を入力したセルに色や罫線をつけたりしていたのでその情報が残ったままになってたからなのかな、と。

「-1」を入れていたセルの行全体を削除したらエラーはなくなりました。

 

逆仕訳でなく数値をマイナスにするメリット

仕訳はそのままで数値だけマイナスにするというやり方には経理担当者も税理士事務所にお勤めの方も違和感をもたれるかもしれません。

簿記では通常金額がマイナスの仕訳って起こさないので。

ただ結果は逆仕訳とまったく同じになるので、効率化できるのならこれでいいんじゃないかなーと。

洗い替えが必要な前期の決算整理仕訳が多ければ多いほど便利です。

 

数値をマイナスにするもうひとつのメリットとしてピボットテーブルでの集計がやりやすくなることがあげられます。

また記事にできればと考えていますが弥生からエクスポートした仕訳データはExcelのピボットテーブル機能で集計することができます。

支払先別にいくら支払っているかなどを確認できたり金額の大きい順に並び替えたりできるのでとても便利なのですが、逆仕訳があるとうまいこと集計できません。

自分が知らないだけでなんかやり方はあるのかもしれませんが。

これが数値をマイナスにした状態だとうまいこと集計できるんです。

手入力はしなくていいし、集計はうまくいくしで自分の中ではもはやこの数値をマイナスで計上する方法しか考えられません。

 

数値の反転は他でも役立つかも

数値を反転させたいと思うケースが頻繁にあるかはわかりません。

ただ自分の場合他にも活用できるケースがありました。

それはクレジットカードの取引データを弥生にインポートしようとしたときです。

カード会社によって取引データの形式は千差万別。

弥生にインポートしようとすると一定の形式になっている必要があるのですが、先日取り込もうとしたデータは支払がプラスの値で、値引きがマイナスの値で同じ列に表示されていました(キャッシュレス決済の5%還元があるから何かしら値引きが発生するんですよね…)。

これを取り込もうとすると弥生側で支払と値引きを逆に認識してしまったんです。

認識を逆にできないかいろいろ方法を検討してみたんですがわからずじまい…

結局数値を反転させることで取込できました。

数値を反転させる作業自体は毎月発生してしまいますが、カードの取引をすべて手入力することに比べればたいした作業量ではないのでまぁいいかなと。

 

他にも数値を反転させることで効率化できる事例はあるのかも。

そう考えて今回のやり方を紹介してみました。

Excelの数値を反転させたいけどやり方がわからない！という方の参考になればうれしいです。