Excel複数シート集計ー複数シートの合計値を1枚に集計するマクロ【サンプルあり】ー

  • URLをコピーしました!

昨日の記事Excel複数シート集計ー各シート最下部の合計値をINDIRECT関数・COUNTA関数で瞬時に集計する方法ー[サンプルあり]をマクロでやる方法を解説します。

Macro

 

マクロの基礎

今回の事例は、「月別の12枚のシートにある合計値を1枚のシートにまとめる」というものです。

スクリーンショット 2013 09 13 8 03 25
前回の記事では、関数で計算しましたが、マクロでは、「それぞれのシートから合計値を持ってくる(コピーしてくる)」という操作をExcelにやってもらいます。
シートが12枚あるから12回です。
マクロだと、シートが100枚でも1,000枚でも、疲れず文句言わずミスなくやってくれます。

「マクロって何?」という方は、こちらの記事を参考にしてみてください。

Excelマクロ入門ー実際にかんたんなマクロを書いてみましょうー | EX-IT

 

 

Excelでマクロを書く下準備ーExcelを持っていれば無料で使えますー | EX-IT

 

 

 

 

複数のシートから集計するマクロの解説

次のようなマクロを書きます。
【貼り付け用】ーーーここからーーー
Sub shuukei()

Dim Shuukei_cell
Shuukei_cell = 1
Dim w As Worksheet
For Each w In Worksheets

If w.Name < > “集計” Then

Dim Last_row
Last_row = w.Range(“d” & Rows.Count).End(xlUp).Row
Range(“b” & Shuukei_cell).Value = w.Range(“d” & Last_row)
Shuukei_cell = Shuukei_cell + 1
End If
Next

End Sub
ーーーここまでーーー
【解説用】
Sub shuukei()

Dim Shuukei_cell
Shuukei_cell = 1・・・①
Dim w As Worksheet
For Each w In Worksheets・・・②

If w.Name < > “集計” Then・・・③

Dim Last_row
Last_row = w.Range(“d” & Rows.Count).End(xlUp).Row・・・④
Range(“b” & Shuukei_cell).Value = w.Range(“d” & Last_row)・・・⑤
Shuukei_cell = Shuukei_cell + 1・・・⑥
End If
Next

End Sub

スクリーンショット 2013 09 13 8 07 46

①Shuukei_cellは、シート「集計」のB列のセルです。1行目(セルB1)からスタートするので、
Shuukei_cell=1
と初期値が1と指定します。何も指定しないと0が入りエラーが出ます。セルB0というのがないからです。

②For Each w in Worksheets〜Nextは、「すべてのシートで実行する」という構文です。
wは変数で、それぞれが好きに決めることができます。

③ここでIF〜End Ifという構文を使っています。「すべてのシート」だとシート「集計」も含まれてしまいます。集計したいのは、「4月」から「3月」の12枚のシートだけですので、「もし、シートの名前が集計じゃなかったら」という条件を入れているのです。

④Last_rowは、それぞれのシートにあるデータの最下部を意味します。合計値は、D列にあるのでD列で探すように指示してください。
これができるのは、データの最下部に合計値があるというルールがあるからです。

■参考記事
Excelだけでは難しい効率化ー最大行・最終行・データ数をカウントするマクロー | EX-IT

 

 

⑤シート「集計」のB列のセルに、各シートのD列のデータ最下部の数値を入れていきます。
まず、シート「4月」のセルB38をシート「集計」のセルD1へ入れます。

⑥シート「4月」の処理が終わったら、次はシート「5月」の処理に移ります。これは、②で「すべてのシートで実行」とすでに指示していることです。ただし、このままだと、「5月」のデータも、シート「集計」のセルD1に入れてしまいます。
「5月」のデータは、セルD2に入れたいので、D1からD2へ1つ下に行くように指示するのです。
Shuukei_cell = Shuukei_cell + 1
という構文で指示できます。
「Shuukei_cellに1を追加する」という意味です。

結果、シート「5月」のセルB32(データ最下部)をシート「集計」のD2へ入れます。

サンプルを置いておきます。
EX-ITサンプル 複数シート集計マクロ.xlsm
(ファイルを開くときにマクロを有効にしてください)

シート「集計」にあるボタン「集計」をクリックすると集計できます。
セルをいったん消して実行させたり、各月のシートの数字や合計値の位置を変えたりしてみてください。
スクリーンショット 2013 09 13 8 08 25

 

 

マクロは「書く」もの

「マクロって記録させるものじゃないの?」と思われる方もいらっしゃるかもしれませんが、記録だと今回のようなプログラムは書けません。
実際に12回繰り返して記録しなければいけないのです。
しかも、「最下部のデータをとってくる」というのは記録できず、毎回プログラムを変える必要があります。

「記録マクロ」ではなく、「書くマクロ」を身につけよう | EX-IT

 

 

 

 

前回の記事の方法と比べると結果は同じですが、方法はまったく違いますし、覚えるべきことも変わってきます。
Excel複数シート集計ー各シート最下部の合計値をINDIRECT関数・COUNTA関数で瞬時に集計する方法ー[サンプルあり]

 

 

 





【編集後記】
今週はたまたま講演の仕事が重なりました。どちらも経理業務効率化です。
今日は新橋にて。
満員電車を避けるために、6時半ごろ現地近くのカフェに着き、仕事してます。

  • URLをコピーしました!