売上や経費などの毎月の数字を前年と比較しながら一覧できるエクセル月次推移表 【オンライン事務代行アウトソーシング・サービス 『ジム楽』ブログ】

事務や経理の代行のプロフェッショナル、オンライン事務代行アウトソーシング・サポート「ジム楽」のコッティです。
公認会計士・税理士として20年の経験から、会計税金などの数字とエクセルの組み合わせのスペシャリストを自負しています。


このような売上高や粗利益、経費や営業利益などの毎月の数字の推移を、前年の同月と比べながら知りたい、という社長は多いと思いますが、弥生などの会計ソフトから出る資料ではそのようなものがありません。そこで、弥生会計を使っているケースを例に、前年同月比較の月次推移表をエクセルで簡単に作る際のポイントについてお話します。

1.弥生会計から月次推移のデータをダウンロードする


まずは弥生会計で、「集計」→「残高試算表」→「月次推移」を選択します。


すると、「残高試算表(年間推移)」の画面になりますので、左上の「Excel」マークをクリックします。すると、「Excelへの書き出し」の画面がポップアップされますので、「新規のファイルに書き出す」にチェックが入っていることを確認した上で、「OK」します。


すると、しばらくするとこのようなエクセルが出力されます。


そこで、左下の「損(損益計算書)」シートを選択した上で、左上の「◢」をクリックしてシート全体を反転させます。

そして、月次推移表を作る新しいエクセルのブックに貼り付けします。そして、このシートをコピーしてもう一つ同じシートを作っておいてください。

2.ベースとなる関数を作る

ベースとなる関数をまず作ってから、それをコピー&ペーストすると、驚くほど効率的に正確に月次推移表などを作成することができます。そこで、まずは最初のベースとなる関数を作っていきます。

(1)VLOOKUP関数


売上高などの勘定科目のA列と4月以降の会計数字の間に空白セルの列を数列挿入します。そして、C3セルにカーソルを合わせた状態で、左上の「fx」マークをクリックして、「関数の挿入」ボックスを呼び出し、「関数の検索」欄に「VLOOKUP」を入力して「検索開始」を選択すると、「関数名」ボックスに「VLOOKUP」関数が表示されるので、「OK」を選択します。


すると「関数の引数」ボックスが表示されるので、「検索値」に参照したい数字である「売上高」が入力されているA3セルをクリックします。


「範囲」は、最初に弥生会計からダウンロードしておいた前期の推移データを貼り付けたシートに移動して、左上の「◢」をクリックして全体を反転させます。


「列番号」には検索する元の月次推移データで左から2列目にあることを意味する「2」を、「検索方法」には、「FALSE」を入力します。


すると、このように正しい数字がC3セルに入力されます(F列は、このような確認用に置いておいたデータですので、後ほど消します)。

(2)参照するセルを固定する「$」

次に、これをコピーして使うための加工をしていきます。関数ボックスの「sheet1!1:1048576」を反転させた状態にします。


そのままでキーボードの左上にあるF4キーを押すと、上記のように「$」マークが付されて、セルの範囲が固定されます(他のセルにコピーしても移動しないという意味です)。


ここまで出来た状態で、下のセルにもコピーすると、このように正しい数字が入ります。


次に、左上の「fx」マークをクリックして「関数の引数」を再度呼び出し、「列番号」を2から下記のように変更します。


「列番号」は、「C$1」とします。「$1」は、横軸である行を1行目に固定したといことを意味します。また、C1セルC2セルに2と3を入力します。


その上で、このように変えたセルを他のセルに貼り付けすると、このように正しい数字が導かれます。


ここでシート名などを少し整理します。目的である前期と当期の月次推移表の名前を「推移」、前期の元データを「前期」、また当期のデータも同様に弥生からダウンロードして貼り付けたシートを「当期」とします。また、前期の列の右に、当期の列を新しく挿入して作ります。そして、当期の数字を引っ張てくるために、まずはC4セルをD4セルにコピーします。すると、このように「♯VALUE!」と出ます。


そこで、関数ボックスの「前期」となっているのを「当期」に変更します。また、参照列である2列目を意味する「2」をD1セルに入力します。すると、このように当期のデータを引っ張てくるようなりました。

(3)IFERROR関数


このセルを下に貼り付けると、一部「♯N/A」が出てきます。該当するものがない、という意味です。前期はあったけど、当期は発生していない勘定科目があるとこのように表示されます。


そこで、このように今までの関数にIFERROR関数を追加します。今までの関数を「IFERROR(従来の関数),0」」のように囲みます。すると、「0」で表示されるようになります。


5月分の数字も出すために、このように貼り付けます。すると正しい数字が出ました。

(4)コピー&ペーストの汎用性を高めるための工夫

そして、参照列の入力の仕方を少し変更します。前の列に+1になりようにすることで、関数のコピーと貼り付けが楽になります。

3.ベースの関数がある程度出来たら、他のセルに一気にペーストしていく

この状態で、前期と当期の数字をブロックの塊でコピーして右に貼り付けていきます。ほとんど正しいですが、10月だけ変な数字が出ています。

元データである「前期」シートを見てみます。分かりやすいように7行目に列数字を入れてみたところ、8列目は上半期の合計でした。ですので、10月の参照列の数字は「9」に変更する必要があります。


あとは体裁を整えれば、このような前年の同月と比較が可能な月次推移表が完成します。

こちらも便利ですよ。是非ご参加ください。
エクセル月次推移表で経営数値の年間の着地予測値を簡単に算出

また、今回のエクセル・フォーマットが欲しいというかたは、お問い合わせフォームからご連絡ください。
お問い合わせフォーム

自社で行う振込・支払の処理や請求書発行、会計帳簿の作成などの事務作業やバックオフィス業務を効率化したい、減らしたいなどのご要望がございましたら、

初期費用ゼロにも関わらず、公認会計士事務所のノウハウで効率的で質の高いサービスをご提供するオンライン事務代行アウトソーシング・サポート『ジム楽』までお気軽にお問い合わせください。

Page Top

もう、面倒な作業に悩まないでください!