予算実績対比表の作成
本年度実績と予算、または本年度実績と前年度実績との対比表は、レイアウトの違いはあれ作成していることが多いのではないかと思います。
いったんレイアウトができてしまえば、あとは毎月本年度の実績の数字を更新していくだけで良いのですが、最初にレイアウトを作成することに手間がかかります。
予算実績対比表のレイアウトを作成するExcelのマクロを作成しました。
事前に準備するものは以下の3つです。
- 本年度実績の月次推移表
- 前年度実績の月次推移表
- 予算の月次推移表
上記の三種類の月次推移表は、どれも前回の投稿で作成した月次推移表のレイアウトを使用するものとします。
Excelのファイル
JDLIBEX出納帳のデータを元にしたもの
弥生会計のデータを元にしたもの
変数への値の代入
どちらのExcelのファイルについても、マクロの最初に変数へ値を代入する部分があります。
file_path = "C:¥----¥----¥----" nendo_1 = "2021" file_name_1 = "----.xlsx" nendo_2 = "2020" file_name_2 = "----.xlsx" file_name_3 = "----.xlsx"
指定する内容は以下の通り。
| file_path | 処理の対象となるxlsxファイル（下記のfile_name_1、file_name_2、file_name_3のファイル）が保存されているフォルダを指定。 |
| nendo_1 | 個人事業者であれば”2021″、法人であれば”2021-2022″といった当年度を指定。 |
| file_name_1 | 当年度の実績推移ファイルの名前を指定。ファイル名の先頭に\をつけない。 |
| nendo_2 | 個人事業者であれば”2020″、法人であれば”2020-2021″といった前年度を指定。 |
| file_name_2 | 前年度の実績推移ファイルの名前を指定。ファイル名の先頭に\をつけない。 |
| file_name_3 | 予算ファイルの名前を指定。ファイル名の先頭に\をつけない。 |
nendo_1、nendo_2で指定した年度を名前とするワークシートが新たに作成されます。
それらのワークシートと「yosan」シートから金額を集計した結果が「予算実績対比_単月」、「予算実績対比_累計」シートに表示されます。
「予算実績対比_単月」、「予算実績対比_累計」では、金額を集計する計算式はマクロで書き込んでいますが、「百分比（売上高に対する経費の割合）」「前年比」「予算比」などの計算式については、空欄になっているところはマニュアルで埋めていく必要があります。
集計期間の切替方法
「予算実績対比_単月」シートのD2セルの日付を変更すると集計期間が変更されます。
例えば5月分について集計する場合、JDLIBEX出納帳のデータを元にしたものであれば、D2セルの日付を「2021/5/1」と変更します。（表示形式の設定により、「2021年5月」と表示されます。）これにより、「予算実績対比_単月」「予算実績対比_累計」の両方のシートの集計期間が変更されます。
弥生会計のデータを元にしたものであれば、D2セルの内容を「5月度」と変更します。
sumifs関数で集計の対象となる列を変化させる方法
金額の集計はsumifs関数で行っていますが、4月なら4月のデータが入っている列を、5月なら5月のデータが入っている列を集計しなければなりません。
このように集計する列を変化させる方法としてmatch関数とoffset関数を使用しています。
弥生会計のデータを元にした予算実績対比表を例にとって説明すると、「予算実績対比_単月」シートで、D2セルに入っている「4月度」という文字列が、「2021」シートのD2セルからP2セルの範囲で何番目にあるのかをmatch関数で求めて、その分だけC列を右に異動した列を集計させる、というように計算式を作成しています。
VBAで関数をセルに入力する際に関数の中で変数を使用する
またsumifs関数については、必要なセルにR1C1形式で入力するようにしていますが、関数内で参照するワークシート名が変数で与えられています。
このような場合には、関数全体を文字列で入力すれば良いわけですから、変数の部分とそれ以外の文字列の部分を根気よく「&」でつなげていけば入力することができます。