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

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

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

今回の事例は、こういった明細書が月ごとに12枚あるケースです。
スクリーンショット 2015 01 17 10 14 38

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

シート名がこのように規則正しいものであれば、INDIRECT関数を使います。
スクリーンショット 2015 01 17 10 13 49

まずは、項目を作りましょう。
「1月」と入力して、オートフィルでコピーする(セルの右下をドラッグ)と速いです。
スクリーンショット 2015 01 17 10 16 06

次に数式を入れます。
通常、セルB3には、シート「1月」のセルF10の金額が入るはずです。
スクリーンショット 2015 01 17 10 17 52

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

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

セルA3にある「1月」を数式に使おうとして普通に、=A3&”!F10″という数式を入れると、
スクリーンショット 2015 01 17 10 21 13


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


エラーが出ます。
Excelは悪くありません。指示通り、「1月」という文字と、「!F10」をつなげて表示しただけです。
スクリーンショット 2015 01 17 10 22 00

INDIRECT関数を使うと、「文字を数式と考えて」という指示を出せます。
=INDIRECT(A3&”!F10″)でこのように、A3を数式と捉えて、シート「1月」のセルF10を表示してくれます。
スクリーンショット 2015 01 17 10 23 48

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

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

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

スクリーンショット 2015 01 17 10 25 58

規則正しく半角数字と月で「○月」となっているわけではなく、ばらばらなのです・・・。
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へ・・・という繰り返しをやってもらいます。

■スポンサードリンク

まあ、ひとまず目的は達成できた感じです。
スクリーンショット 2015 01 17 10 34 59

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

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

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

まず、データを入力して、
スクリーンショット 2015 01 17 10 39 02

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

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

具体的には、ひな形(マスター)を作っておき、これをコピーしてデータを入れていきます。
スクリーンショット 2015 01 17 10 42 38

一括で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

スクリーンショット 2015 01 17 10 47 17

実際は月々、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)))
という長ーい関数の組み合わせです。

スクリーンショット 2015 01 17 10 57 28

表示させた上で、VLOOKUP関数で、「5月」を探し、該当するデータを表示させます。
スクリーンショット 2015 01 17 10 57 59

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

まとめ

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

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

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

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





■スポンサードリンク
【編集後記】
積ん読(読んでいない本)がたまりすぎてきて、この土日でまとめ読みをして、ちょっと減らす予定です。
大きい、分厚い本から読んでいきます。

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

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




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』
###