会計データを1つのシートにまとめるに何度もコピペする?
会計データは年ごとあるいは年度ごとに分けられています。
弥生会計だと、1つのデータで3年分(3期分)のデータしか持てませんし、クラウド会計だとそれ以上にデータを残せますが、データ環境が違いますので、画面の切り替えは必要になります。
過去のデータも含めて、推移がわかる資料などをExcelでつくりたい場合には、まずは年(期)ごとにデータをダウンロードする必要があります。(これがけっこう大変…)
CSVファイルも年ごとにファイル1つなので、5年分ならファイルは5つ、6年あれば6つです。
それぞれのファイルを開いて、1つのシートにコピペするわけですが、これを手でやっていると、なかなか大変です。
- CSVファイルを開く
- 元データをコピー
- 新しいシートに貼り付ける。
この処理を年数の分だけ、繰り返す必要があります。 ミスする可能性もありますし。当然ながら時間もかかります。
こういった処理も Excel マクロを使うことで、速く確実に処理することができます。
マクロを書く前に手で動かした場合の進め方を考える
それぞれのシートのデータを何度もコピペすることなく1枚のシートにまとめるにはマクロを使うと便利です。
今回の例は、年別のデータを1つのブックにドラッグしてまとめてあるという前提です。
マクロを書くときには、最初に手でやるならどういう動きをするか、流れをまとめてみるとわかりやすくなります。
今回の流れは次のような感じにしています。
- 新しいシートを追加して、そこに各年分のデータをコピペしていきます。
- 2015年のシートを選択して会計データをコピーし、新しいシートに貼り付けます。
- 以後、各年のデータをコピーして連続して貼り付けます。
例えば、2016年のデータは、新しいシートにすでに貼り付けた2015年のデータの最終行を見つけて、その次の行に貼り付けます。
以下、2017年、2018年、2019年…と同じ処理を繰り返していきます。
この処理をマクロで書いていくことになります。
その前に。マクロを使うには、まずは初期設定が必要です。詳しくはこちらの記事で。
Excel VBA超入門 マクロを書いてみたい人集合! 仕事ときどきアウトプットがおすすめ | GO for IT 〜 税理士 植村 豪 Official Blog
会計データを1つにまとめるマクロなら4秒で完了
今回はこういったマクロを書いてみました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Sub data_marge() '■変数の宣言 Dim max_row As Long Dim mrg_row As Long Dim i As Long '■集計シートの追加 Worksheets.Add before:=Worksheets(1) Worksheets(1).Name = "data" '■各データシートのコピー For i = 2 To Sheets.Count max_row = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row Worksheets(i).Rows("1:" & max_row).Copy mrg_row = Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row + 1 Worksheets("data").Range("A" & mrg_row).PasteSpecial Next End Sub |
集計シートの追加
各シートをまとめるシートを1枚準備します。
この年別のシートの先頭に新しいシートを追加します。
「Worksheets.Add before:=Worksheets(1)」で1枚目のシートの前にワークシートを追加すると表現しています。
1 2 3 4 |
'■集計シートの追加 Worksheets.Add before:=Worksheets(1) Worksheets(1).Name = "data" |
これは、add(追加)、before(前)という英語からもなんとなくわかるところですが、beforeの後ろに「:」をつけないとエラーになります。
新たにシートを追加したことで、1枚目のシートは新しいシートに変わっています。その追加したシートの名前を「Worksheets(1).Name = “data”」で「data」に変えています。
まぁ、名前は何でもいいのですが。
コピペ処理の繰り返し
For〜Nextで繰り返しを意味し、この間に書かれている処理を繰り返します。
1 2 3 4 5 6 7 8 9 10 11 |
'■各データシートのコピー For i = 2 To Sheets.Count max_row = Worksheets(i).Range("A" & Rows.Count).End(xlUp).Row Worksheets(i).Rows("1:" & max_row).Copy mrg_row = Worksheets("data").Range("A" & Rows.Count).End(xlUp).Row + 1 Worksheets("data").Range("A" & mrg_row).PasteSpecial 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新」→詳細はコチラ
散歩で初めての道
とある仕事