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

複数のシートにある1年間のデータを、1枚のデータにまとめるにはマクロが便利です。

 

シート名が規則正しければ、INDIRECT関数が便利

今回の事例は、こういった明細書が月ごとに12枚あるケースです。

去年1年間の金額を集計するのを目的とします。
1つずつコピーして貼り付ければできないことはありませんが、手間がかかってめんどくさいです。

シート名がこのように規則正しいものであれば、INDIRECT関数を使います。

まずは、項目を作りましょう。
「1月」と入力して、オートフィルでコピーする（セルの右下をドラッグ）と速いです。

次に数式を入れます。
通常、セルB3には、シート「1月」のセルF10の金額が入るはずです。

集計シートのセルB3に「＝」を入れて、シート「1月」のセルF10をクリックして入れることもできますが、これを繰り返すのはめんどくさく、手間がかかります。

最終的に「=’1月’!F10」という数式が入ればいいので、これをINDIRECT関数で表示します。
INDIRECT関数は、文字を数式に変えることができる関数です。

セルA3にある「1月」を数式に使おうとして普通に、＝A3&”!F10″という数式を入れると、

エラーが出ます。
Excelは悪くありません。指示通り、「1月」という文字と、「!F10」をつなげて表示しただけです。

INDIRECT関数を使うと、「文字を数式と考えて」という指示を出せます。
=INDIRECT（A3&”!F10″)でこのように、A3を数式と捉えて、シート「1月」のセルF10を表示してくれます。

あとは、同じように数式を入れれば完成です。

 

 

シート名が不規則なら、マクロ

以前コンサルで拝見した事例では、このようなファイルでした。

規則正しく半角数字と月で「○月」となっているわけではなく、ばらばらなのです・・・。
Excelではこの「規則正しく」が非常に大事となります。

この場合、上記の方法だとちょっと難しく（シート名通りに項目を入れればいいのですが）、困ります。

ただ、救いは、それぞれのF10からF13にデータが入っていて、12枚のシートが順番通りにあるということです。

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

Sub sheetmerge()


    Dim w As Worksheet
    Dim n
    n = 2
    
    For Each w In Worksheets
        If w.Name <> "集計" Then
    
            Range("a" & n).Value = w.Name
            Range("b" & n).Value = w.Range("f10").Value
            Range("c" & n).Value = w.Range("f11").Value
            Range("d" & n).Value = w.Range("f12").Value
            Range("e" & n).Value = w.Range("f13").Value
            
            n = n + 1
        End If
    Next

End Sub

for　eachというのは、「すべてのシートで〜という処理をする」という構文です。
1枚目のシート名をセルA2へ、セルF10をセルB3へ、セルF11をセルC3へ・・・という繰り返しをやってもらいます。

まあ、ひとまず目的は達成できた感じです。

もっと簡単に書くこともできますが、マクロを使えばこれくらいの行数で、どれだけシートがあっても簡単に集計ができます。

 

 

Excelでの処理は、「データ→アウトプット」が基本

今回のような事例だと、明細（アウトプット）を入力して作って、データとして集計するというパターンですが、Excelでの処理は、その逆、つまり、「データ→アウトプット」という流れにした方が速く、メンテナンスしやすくなります。

まず、データを入力して、

それを明細にわけていくマクロを使うと便利です。

データの状態だと、一覧で見ることもできますし、集計も楽ですし、会計ソフトへの取り込みもできます。
明細は明細で作って、プリントアウトして、それを見ながら会計データを入力するというのは時間の無駄です・・・。

具体的には、ひな形（マスター）を作っておき、これをコピーしてデータを入れていきます。

一括で12ヶ月分を作るなら、こういったマクロです。

Sub sheetdivide()

    Dim i
    For i = 2 To 13
        Worksheets("master").Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("集計").Range("a" & i).Value
        ActiveSheet.Range("f10").Value = Worksheets("集計").Range("b" & i).Value
        ActiveSheet.Range("f11").Value = Worksheets("集計").Range("c" & i).Value
        ActiveSheet.Range("f12").Value = Worksheets("集計").Range("d" & i).Value
        ActiveSheet.Range("f13").Value = Worksheets("集計").Range("e" & i).Value
        
    Next
    
End Sub

実際は月々、1枚ずつ出すと思いますので、選択した行の明細を出すというマクロを使います。

変えたのは、「for  i =」の後の部分です。

Sub sheetdivide()

    Dim i
    For i = Selection(1).Row To Selection(Selection.Count).Row
        Worksheets("master").Copy after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = Worksheets("集計").Range("a" & i).Value
        ActiveSheet.Range("f10").Value = Worksheets("集計").Range("b" & i).Value
        ActiveSheet.Range("f11").Value = Worksheets("集計").Range("c" & i).Value
        ActiveSheet.Range("f12").Value = Worksheets("集計").Range("d" & i).Value
        ActiveSheet.Range("f13").Value = Worksheets("集計").Range("e" & i).Value
        
    Next
    
End Sub

ボックスを表示させて入力する、マクロのデータを毎月変更する（1月なら、iを2にする）こともできますが、選択した部分を出すマクロを選んでいます。
これも、Excelの機能だけでもできないことはありません。

まず、シート名をセルに表示させます。
（印刷範囲外に表示しています）
=RIGHT(CELL(“filename”,A1),LEN(CELL(“filename”,A1))-FIND(“]”,CELL(“filename”,A1)))
という長ーい関数の組み合わせです。

表示させた上で、VLOOKUP関数で、「5月」を探し、該当するデータを表示させます。

毎月は、シートをコピーして、シート名を変えるという処理が必要となり、マクロよりはちょっと手間です。

まとめ

Excelで手間がかかるものの1つとして、異なるシートでの処理があります。

これを効率化するには、
・データの作り方を工夫する
・データ→アウトプットの流れを意識する
・Excelの機能（関数）とマクロを両方学んで応用が利くようにする
ということが大事です。

Excelを学んでからマクロというわけではなく、同時期に学ぶとより効果があります。

今月のセミナー、Excel入門の方は埋まってしまいましたが、 マクロ入門セミナーは残席2名です。
1/24（土）の14:00から開催します。
Excelマクロ入門セミナー

 

---

---

---

---

【編集後記】
積ん読（読んでいない本）がたまりすぎてきて、この土日でまとめ読みをして、ちょっと減らす予定です。
大きい、分厚い本から読んでいきます。

【昨日の1日1新】
※詳細は→「1日1新」

ランで東京税理士会神田支部へ