Excelの月別シートのデータを集計するマクロ

2023年1月24日

URLをコピーしました！

Excelで月別のシートがあったとき、それを手で集計するのは大変です。
こういったときにマクロが役立ちます。

※Excelとマクロ　by　Sony α1+16-35mmF4

1 月別シートからデータを集めるExcelマクロ
2 INDERECT関数を使って月別シートからデータを集める
3 データを月別シートに分けるマクロ

月別シートからデータを集めるExcelマクロ

Excelで月別にシートがあり、必要なデータを集計したいとき、1年分であればコピペ（コピーして貼り付け）を12回繰り返す必要があります。

たかが12回ですがされど12回。
もしなくせるならなくしたいものです。
（人に任せるというのはナシで）

こういったときにマクロが使えます。

Excelマクロの基本は、セミナーの冒頭をYouTubeにアップしています。
（セミナーは今のところご要望または個別コンサルティングで承っております）

Sub macro()


    Dim w As Worksheet
    Dim n
    n = 1
    For Each w In Worksheets
            Range("a" & n).Value = w.Name
            Range("b" & n).Value = w.Range("b7").Value
            Range("c" & n).Value = w.Range("b8").Value
            
            n = n + 1
    
    Next
     


End Sub

For Each w In Worksheetsは、「すべてのシートで処理をする」というもので、ForからNextで囲まれる部分をすべてのシートでやってくれます。

このように、A列に日付、B列に金額、Ç列に源泉所得税を集計していくわけです。
繰り返しの1回目は、A1、B1、Ç1、2回目は、A2、B2、C2に転記しています。

この1つずつ転記するのが、この部分です。

Range(“a” & n).Value = w.Name
Range(“b” & n).Value = w.Range(“b7”).Value
Range(“c” & n).Value = w.Range(“b8”).Value

n = n + 1

まずnに1が入って、
Range(“a” & 1).Value = w.Name
Range(“b” & 1).Value = w.Range(“b7”).Value
Range(“c” & 1).Value = w.Range(“b8”).Value

A1にシートの名前（w.Name）、B1にセルb7のデータ、Ç1にセルb8のデータを入れていきます。
そして、
n = n + 1
で、「1」に1を足し、「2」にして、

Range(“a” & 2).Value = w.Name
Range(“b” & 2).Value = w.Range(“b7”).Value
Range(“c” & 2).Value = w.Range(“b8”).Value
と処理していきます。

最後は、「集計」というシートまで集計してしまいますが、気にしないようにしましょう。
（厳密につくるなら、「集計」以外のすべてのシートで、という処理にします）

INDERECT関数を使って月別シートからデータを集める

INDIRECT関数を使えば、マクロを使わずに集めることもできます。

【INDIRECT関数・マクロ】それぞれのシートにある月々のデータをExcelで集計する方法

ただ、これは、シート名の一覧が必要で、そのシート名が不規則な場合は使えません。
（そしてそういうことはよくあります）

また、各シートの決まった場所（この場合は、セルÇ7、C8）にデータがあることが大前提です。
マクロがスムーズに動くよう、人がルールを守る必要があります。

今後のスキルの発展性も考えると、マクロを身につけておくほうがおすすめです。
次のような場合にもマクロは使えます。

データを月別シートに分けるマクロ

月別にシートをつくる
↓
それらを集計する

という方法の他に、

データをつくる
↓
月別にシートをつくる
という方法もあります。

それぞれのメリットは次のとおりです。
・前者は、直感的にシートをつくることができる
・後者は、データをつくるのが楽、間違いがない

こういったデータをつくり、

テンプレートに転記していくというしくみです。

こういったマクロを使います。

Sub macro2()


    Dim i
    For i = 2 To 13
        
        Worksheets("meisai").Copy after:=Worksheets(Worksheets.Count)
        
        ActiveSheet.Name = Format(Worksheets("data").Range("a" & i).Value, "yyyy年m月")
        Range("a2").Value = Worksheets("data").Range("a" & i).Value
        Range("c7").Value = Worksheets("data").Range("b" & i).Value
        Range("c8").Value = Worksheets("data").Range("c" & i).Value
        
    
    Next
    


End Sub

テンプレートのシートをコピーし、このデータの2行目から13行目まで繰り返します。

繰り返す処理は、
・日付（A2）をテンプレートのシート名へ
・金額（B2）をテンプレートのセルC7へ
・源泉所得税（C2）をテンプレートのセルC8へ
というものです。
これを2行目から13行目まで繰り返します。
指定すれば、10000行でも、ミスなく処理できるのがマクロの魅力です。

このデータ→複数のシートは、マクロでなければ効率化できません。
私は請求書もこのしくみでつくっています。

事例としてはおすすめですので、ぜひ使ってみていただければ。
人の力、コピペに頼らないようにしましょう。