Excelマクロ超入門 会計データを1つのシートにまとめるマクロ

Excelマクロ入門

会計データを1つのシートにまとめるに何度もコピペする?

会計データは年ごとあるいは年度ごとに分けられています。

弥生会計だと、1つのデータで3年分(3期分)のデータしか持てませんし、クラウド会計だとそれ以上にデータを残せますが、データ環境が違いますので、画面の切り替えは必要になります。

過去のデータも含めて、推移がわかる資料などをExcelでつくりたい場合には、まずは年(期)ごとにデータをダウンロードする必要があります。(これがけっこう大変…)

CSVファイルも年ごとにファイル1つなので、5年分ならファイルは5つ、6年あれば6つです。

スクリーンショット 2020 04 15 16 01 41

それぞれのファイルを開いて、1つのシートにコピペするわけですが、これを手でやっていると、なかなか大変です。

  1. CSVファイルを開く
  2. 元データをコピー
  3. 新しいシートに貼り付ける。

この処理を年数の分だけ、繰り返す必要があります。 ミスする可能性もありますし。当然ながら時間もかかります。

こういった処理も Excel マクロを使うことで、速く確実に処理することができます。

マクロを書く前に手で動かした場合の進め方を考える

それぞれのシートのデータを何度もコピペすることなく1枚のシートにまとめるにはマクロを使うと便利です。

今回の例は、年別のデータを1つのブックにドラッグしてまとめてあるという前提です。

スクリーンショット 2020 04 15 19 20 59

マクロを書くときには、最初に手でやるならどういう動きをするか、流れをまとめてみるとわかりやすくなります。

今回の流れは次のような感じにしています。

  1. 新しいシートを追加して、そこに各年分のデータをコピペしていきます。
  2. 2015年のシートを選択して会計データをコピーし、新しいシートに貼り付けます。
  3. 以後、各年のデータをコピーして連続して貼り付けます。

例えば、2016年のデータは、新しいシートにすでに貼り付けた2015年のデータの最終行を見つけて、その次の行に貼り付けます。

以下、2017年、2018年、2019年…と同じ処理を繰り返していきます。

この処理をマクロで書いていくことになります。

その前に。マクロを使うには、まずは初期設定が必要です。詳しくはこちらの記事で。

Excel VBA超入門 マクロを書いてみたい人集合! 仕事ときどきアウトプットがおすすめ | GO for IT 〜 税理士 植村 豪 Official Blog

会計データを1つにまとめるマクロなら4秒で完了

今回はこういったマクロを書いてみました。

集計シートの追加

各シートをまとめるシートを1枚準備します。

この年別のシートの先頭に新しいシートを追加します。

スクリーンショット 2020 04 15 19 20 43

「Worksheets.Add before:=Worksheets(1)」で1枚目のシートの前にワークシートを追加すると表現しています。

これは、add(追加)、before(前)という英語からもなんとなくわかるところですが、beforeの後ろに「:」をつけないとエラーになります。

新たにシートを追加したことで、1枚目のシートは新しいシートに変わっています。その追加したシートの名前を「Worksheets(1).Name = “data”」で「data」に変えています。

まぁ、名前は何でもいいのですが。

 

コピペ処理の繰り返し

For〜Nextで繰り返しを意味し、この間に書かれている処理を繰り返します。

変数は「i」。どこまで繰り返すかというと、2枚目の2015年のシートから2020年のシートまでです。

「Worksheets(i)」の「i」には「i = 2 To Sheets.Count」が入ることになります。

「max_row」という変数では、それぞれのワークシート1のA列で、データが入っている行数をカウントしています。

で「Rows(“1:” & max_row).Copy」で1行目からデータの入っている行までをコピーしているわけです。

その下の「mrg_row = Range(“A” & Rows.Count).End(xlUp).Row + 1」、この「+1」で一番下のデータの、その次の行を表現していて、

「Range(“A” & mrg_row).PasteSpecial」でA列一番下のデータのその次の行に連続してデータを貼り付けます。

これを各シートについて同じ処理を繰り返すという流れです。

今回の事例、dataシートに集計されたデータは3533行で処理にかかった時間は14秒。(4秒で処理できた方法もあるのですが、諸事情でこちらを。これでも充分です。)

会計データは、個人なら年ごとに確定申告、法人なら年度ごとに確定申告するので、会計データは12ヶ月単位で区切られるのですが、本来であれば廃業しない限り、ずっと続いているものなので、1つにまとまっているというのが本来。

1つのデータで持っていると、推移表やお金の動きなどの資料をつくるとき、データの加工もしやすくなります。その反面、データとして使おうとすると、簿記の借方貸方で金額が発生することがややこしく、決算整理仕訳いらないかなとも。

税理士としてはいかがなものか?と思いますが。興味ある方はマクロ試してみてはいかがでしょうか。

マクロを使うための設定は必要です。

Excel VBA超入門 マクロを書いてみたい人集合! 仕事ときどきアウトプットがおすすめ | GO for IT 〜 税理士 植村 豪 Official Blog


【編集後記】
昨日はお客様とZoomで個別コンサルティングでした。戻ってからはブログなどを。

【昨日の1日1新】
※「1日1新」→詳細はコチラ
散歩で初めての道
とある仕事