Excelのファイル(ブック)を1つのシートに効率よくまとめるには、マクロを使うしかありません。
ファイルをまとめるマクロを紹介します。
ファイルが分かれていると集計が大変
たとえば、支店ごとにファイルがわかれていると、その集計が大変です。
新宿支店
渋谷支店
池袋支店
この集計を毎月やっていてはいくら時間があっても足りなくなるでしょう。
ファイルを開いて、コピーして、貼り付けて・・・という作業を、ファイルの数だけ繰り返さなければいけません。
こういった場合、マクロを使わなければ効率化できません。
ファイル(ブック)を1つにするマクロ
マクロはこういったものです。
Sub bookmerge() Dim b As Workbook '集計するブック Dim b1 As Workbook '集計先のブック Dim d '集計するブック内のシートのデータ数 Dim d1 '集計先のシートのデータ数 Workbooks.Add Set b1 = ActiveWorkbook For Each b In Workbooks If b.Name <> b1.Name Then Dim i As Long For i = 1 To b.Worksheets.Count d = b.Worksheets(i).Range("a" & Rows.Count).End(xlUp).Row d1 = b1.Worksheets(1).Range("a" & Rows.Count).End(xlUp).Row b.Worksheets(i).Rows("1:" & d).Copy b1.Worksheets(1).Range("a" & d1 + 1) Next End If Next End Sub
それぞれの支店のファイルを、1つのシートにまとめます。
まとめたいファイルはあらかじめ開いておいてください。
実際には、
・特定のフォルダにあるExcelファイルを1つにまとめる
・特定のファイルにあるExcelファイルのうち、特定の名前が含まれるファイルをまとめる
・ファイルのシートすべてをまとめる
・ファイルのシートのうち、指定したもの(月、支店など)のみまとめる
といったことができます。
dという変数に、このマクロが入っているファイルのワークシート1のA列で、行をカウントします。
その後、新宿支店のワークシート1の2行目から最後の行をコピーし、このブックのワークシート1のA列で、データがある行の下へ貼り付けます
貼り付け位置は、
d1 = b1.Worksheets(1).Range("a" & Rows.Count).End(xlUp).Row
と表現しています。
最後の行に1を足した場所、つまり、一番下の行のその下に貼り付けるのです。
マクロを実行後、新しいブックが作られ、そのブックに、3つのファイル(新宿支店、渋谷支店、池袋支店)のデータを集めます。
サンプルはこちらです。
EX-ITサンプル ブック集計マクロ
マクロを有効し、【集計】ボタンを押すと、そのときに開いているブックのデータを、新しいブックに集計します。
理想は、1つのシートにデータを入れること
支店別だけではなく、月別、年別、銀行別など様々なデータで上記のマクロは使えます。
1つのシートにまとめれば、
テーブルにしたり
ピボットテーブルで集計したり、
グラフにしたり、
仕訳データを作って会計ソフトに取り込んだりすることができます。
ただ、こういったマクロを使わなくて済むように、理想は、1枚のシートにデータを入力するのが理想です。
そうはいっても、支店別や部署別、人別に入力する場合もあるかと思いますので、そういったときには今回のようなマクロを使ってみましょう。
マクロの基礎については、こちらの記事を参考にしてください。
フォルダにあるファイルを1つにするなら、こちらのマクロがおすすめです。
シートを集計するには、こちらのマクロが使えます。
プライベートスペースが散らかりまくっています。
冬物との入れ替えもあるので、今日片付ける予定です。
ブログに書いておけば、やると思いますので(^_^;)
【昨日の1日1新】
※詳細は→「1日1新」
全3回セミナーの2回目を開催
iPhone「tasukuma」研究
■著書
『税理士のためのプログラミング -ChatGPTで知識ゼロから始める本-』
『すべてをがんばりすぎなくてもいい!顧問先の満足度を高める税理士業務の見極め方』
ひとり税理士のギモンに答える128問128答
【インボイス対応版】ひとり社長の経理の基本
「繁忙期」でもやりたいことを諦めない! 税理士のための業務効率化マニュアル
ひとり税理士の自宅仕事術
リモート経理完全マニュアル――小さな会社にお金を残す87のノウハウ
ひとり税理士のセーフティネットとリスクマネジメント
税理士のためのRPA入門~一歩踏み出せば変えられる!業務効率化の方法~
やってはいけないExcel――「やってはいけない」がわかると「Excelの正解」がわかる
AI時代のひとり税理士
新版 そのまま使える 経理&会計のためのExcel入門
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
【監修】十人十色の「ひとり税理士」という生き方