シートごとの売上データ集計|Excelマクロ→ピボットテーブル

毎月の売上データを集計する場合、月ごとにシートが分かれているときは一手間かかります。
Excelマクロで、シートを1つのシートにする方法を解説しました。
EX IT 1
※スクリーンショットをKeynoteで加工

複数のシートをピボットテーブルで集計?

売上データをExcelで集計するなら、ピボットテーブルが便利です。
【関連記事】売上を細かく分析するならExcelのピボットテーブル、データバー、グラフがおすすめ
リンク

売上データは、月ごとにシートに分かれている場合もあります。
1月のデータ
EX IT 2

2月のデータ
EX IT 3

その場合も、ピボットテーブルが使えるのでしょうか。
複数のシートからピボットテーブルを作れないこともありませんが、おすすめしません。
日付と金額という単純なデータならなんとかなりますが、今回の事例のように、日付、商品、金額とあると、うまくいかないからです。

また、複数のシートにデータが分散することは本来好ましいことではありません。

Excelでピボットテーブルを使って集計するなら、1つのシートにデータを集めることを考えましょう。
そのクセをつけておくと、Excelが上達します。

複数のシートにあるデータを1つにするにはマクロを使わざるを得ません。
(逆にいえば、1つのシートにデータがあればマクロを使わなくてすむのです。)

シートごとの売上を1つにするExcelマクロ

複数シートにあるデータ、シートごとの売上を集計するには次のようなマクロを使います。

Sub sheetmerge()
    
'シート[merge]を削除
    On Error Resume Next
    Application.DisplayAlerts = False
       Worksheets("merge").Delete
    Application.DisplayAlerts = True
    
'シート[merge]を一番右に追加
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "merge"
    
    
'すべてのシートで実行
    Dim w As Worksheet
    For Each w In Worksheets
        
        'シート「merge」以外
        If w.Name <> "merge" Then
            
            '各シートのデータをカウント
            Dim From_Max_Row
            From_Max_Row = w.Range("a" & Rows.Count).End(xlUp).Row
            
            'シート[merge]の貼り付け位置をカウント
            Dim To_Max_Row
            To_Max_Row = Worksheets("merge").Range("a" & Rows.Count).End(xlUp).Row
           
            '各シートからシート[merge]へコピーして貼り付け
            w.Rows("1:" & From_Max_Row).Copy Worksheets("merge").Range("a" & To_Max_Row + 1)
           
        End If
    Next

End Sub

マクロの使い方はこちらの記事を参考にしてください。
【関連記事】ネット上のマクロを自分のファイルに流用する方法
リンク


■スポンサードリンク


このマクロは、汎用性があるように作っています。
どんな種類のファイルでも、[merge]というシートを作り、そこにすべてのシートのデータを集めてくれるものです。

①シートを追加し、名前を[merge]に変更

すでにシート[merge]があると、シートを追加して名前を[merge]にするときにエラーがでます。
そこで、「すでにシート[merge]があれば、削除する」という処理を冒頭に入れています。

'シート[merge]を削除
    On Error Resume Next
    Application.DisplayAlerts = False
       Worksheets("merge").Delete
    Application.DisplayAlerts = True
    
'シート[merge]を一番右に追加
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "merge"

②各シートでデータをカウント

シート[merge]以外の各シートでデータを選択します。
「シート[merge]以外」という処理をやっているのはIFの部分です。

        If w.Name <> "merge" Then
            
		○○○
           
        End If

その場合、データがある行だけ選択するようにしています。
各シートのデータ数が違っていてもこれで自動的に対応できるのです。
EX IT 4

            '各シートのデータをカウント
            Dim From_Max_Row
            From_Max_Row = w.Range("a" & Rows.Count).End(xlUp).Row

③シート[merge]へ貼付

1月のデータをコピーして貼り付け、2月のデータをコピーして貼り付けるとき、すでに貼り付けた1月のデータに上に貼り付けては意味がありません。
すでに貼り付けたデータの1つ下に貼り付けます。

EX IT 9

「各シートのデータをすべてコピーし、シート[merge]の一番下+1の場所に貼り付ける」という処理です。

            '各シートからシート[merge]へコピーして貼り付け
            w.Rows("1:" & From_Max_Row).Copy Worksheets("merge").Range("a" & To_Max_Row + 1)
    

以上の汎用性があるマクロだと、見出し部分(日付、商品、金額)も貼り付けられてしまいますが、このままピボットテーブルで集計することもできますし、フィルターをかけて削除することもできます。
今回の事例でアレンジするなら、次のようにやってみましょう。

Sub sheetmerge2()
    
'シート[merge]を削除
    On Error Resume Next
    Application.DisplayAlerts = False
       Worksheets("merge").Delete
    Application.DisplayAlerts = True
    
'シート[merge]を一番右に追加
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "merge"
    
'◇追加 シート[merge]に見出しをつける
    Worksheets("merge").Range("a1").Value = "日付"
    Worksheets("merge").Range("b1").Value = "商品"
    Worksheets("merge").Range("c1").Value = "金額"

    
    
'すべてのシートで実行
    Dim w As Worksheet
    For Each w In Worksheets
        
        'シート「merge」以外
        If w.Name <> "merge" Then
            
            '各シートのデータをカウント
            Dim From_Max_Row
            From_Max_Row = w.Range("a" & Rows.Count).End(xlUp).Row
            
            'シート[merge]の貼り付け位置をカウント
            Dim To_Max_Row
            To_Max_Row = Worksheets("merge").Range("a" & Rows.Count).End(xlUp).Row
           
            '各シートからシート[merge]へコピーして貼り付け
            '◇変更 各シートの2行目からコピー
            w.Rows("2:" & From_Max_Row).Copy Worksheets("merge").Range("a" & To_Max_Row + 1)
           
        End If
    Next

End Sub

こういったデータができあがります。
EX IT 10

1枚のシート→ピボットテーブル

1枚のシートになればこっちのものです。
(「+」は同時、「→」は1つずつ押します)
①データのいずれかにカーソルを置き、Ctrl+T→Enterでテーブル作成
②Alt→N→V→Enterでピボットテーブル作成
③ピボットテーブルフィールド(右)で、商品、金額にチェック
という流れで、こういった集計ができます。

商品別集計です。
EX IT 11

月別の表にしたり、
EX IT 15

商品・月別の表にしたり、
EX IT 12

[条件付き書式]→[データバー]で見やすくしたり、
EX IT 13

[金額]と[個数]を表示したり、自由自在です。
EX IT 14

マクロで集計までやることもできますが、ピボットテーブルのほうがかんたんで応用がききます。

データを集計するにはピボットテーブルを使う

データは1つのシートに入力する、まとめる

複数のシートの場合はマクロで1つにする
という流れで考えておきましょう。

■サンプル
EX-ITサンプル 月別シート集計マクロ.xlsm

※マクロはショートカットキーCtrl+Shift+mで実行できます。
または、Alt+F8で、[sheetmerge2]を実行してください。

【関連記事】データ→ピボットテーブルの活用。Excel表計算ソフトだけど、表を作ってはいけない。
リンク

【関連記事】かっこいいSUMIF・SUMIFS・COUNTIF・COUNTIFSよりも、かんたんなピボットテーブル
リンク





【編集後記】

執筆環境を変えてみようかと、新しいソフトを導入してみました。
ソフトのせいにしてはいけないのですが・・・。
遅れている書籍原稿、なんとか取り戻します。

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

Scrivener製品版
Parallels Desktop12

■スポンサードリンク

■スポンサードリンク

■著書
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』