エクセルの各シートにある異なる並び順の表を１つのシートに集計する方法

2016年9月29日 10:13 PM | カテゴリー：IT仕事術 | コメント（0）

　先日のエントリーで「CSVデータを会計情報に利用する」というエントリーを書きました。
そこでは、ひとつのシートに１か月分の売上明細をピボットテーブルで作成するという内容でした。しかし、もうひとつ先に進んで考えてみると、各シートにある月分の集計データを、別シートに１２か月分の集計としてまとめることも必要です。結果、１２ヶ月分のお客様ごとの売上明細を作成できることになるからです。
しかし、ここでひとつの問題が生じます。年の途中でお客様が増えた場合などはエクセルのセル
に記載されているデータ（会社名）の順序が変わることになります（下図）。 

　このように各シートで異なる順序の表を別シートで集計するには、串刺し機能では集計できません。しかし、エクセルの結合という機能を
使うと各シートに存在する異なる形の表を集計することができます。しかも、後からシートを追加しても対応できます。

本日のエントリーでは、
「各シートに存在する表が異なる形（データの並び順が異なる）」
「後からシートを追加」
という２つの条件下において、その表のデータを別シートに集計する方法を考えてみたいと思います。

１シート、１ヶ月で表を管理する

１シートに１２ヶ月分のデータを表示させるのではなく、１ヶ月で１シートということにしました。例えば、sheet2には１月分を、sheet3には２月分をという並び順で各シートに各月の明細を貼り付けました。１年間では１２個のシートができることになります。それに加えて、それら各シート合計を集計する「総計」という名称のシートを作りました。合計で１３個のシートのブックが出来上がりました。

並び順の異なる表と並び順が同じ表とは？

　ここで問題なのが各シートのデータを集計する方法です。各顧問先の名称の並び順が、それぞれのシートで全く同じであれば各シートの合計は非常に簡単です（下図A参照）。串刺し集計を使ったり、関数を使ったりすることで対応できます。しかし、期中で新たに顧問先様が増えたりすることにより、並び順が変わることがあります（下図B参照）。各シートにある並び順が異なる表のデータを集計することは、串刺し集計や関数ではできません。 そこで「各シートにある、異なる並び順の表のデータを１つのシートに集計する」方法は何がベストなのかを考えました。

A社からE社までの各シートの並び順が同じ【図A】

A社からE社までの各シートの並び順が異なっている。→串刺し集計や関数では集計できない。【図B】

並び順が異なる各シートの表を集計する「結合」

　すぐに思い浮かぶのがエクセルの「結合」です。この機能を使えば、「各シートにある、異なる並び順の表のデータを１つのシートに集計する」ことができます。
しかし、ここで問題が生じます。毎月、シートがひとつずつ増えます。例えば、６月になると新たにシートをひとつ追加して、６月のデータを貼り付けます。毎月、
結合の処理をするのは面倒です。できれば、マクロを使って、ワンクリックで集計したいと思いました。

結合をマクロで実行してみる

　以下のマクロを使うことによって、異なる並び順の表のデータを１つのシートに集計することができました。

Sub nss()
Dim i As Long
Dim 売上明細() As String

ReDim 売上明細(Worksheets.Count – 2) As String

For i = 2 To Worksheets.Count
売上明細(i – 2) = Worksheets(i).Name & “!” & _
Worksheets(i).Range(“A1”).CurrentRegion _
.Address(ReferenceStyle:=xlR1C1)
Next

Worksheets(“総計”).Range(“A1”).Consolidate _
Sources:=売上明細, Function:=xlSum _
, TopRow:=True, LeftColumn:=True
End Sub

＊マクロの説明
Dim i As Long→繰り返し処理のための変数「i」
Dim 売上明細() As String→統合元のセル範囲を格納するための変数「売上明細」
For i = 2 To Worksheets.Count→統合元は２番目のシートから続きます。シートが増えても対応できるように、数字ではなく
Countプロパティイを指定します。ワークシートの数が増減しても対応できます。
Worksheets(i).Range(“A1”).CurrentRegion→各シートデータを集計する起点はセルA1。
Worksheets(“総計”).Range(“A1”).Consolidate→集計したデータは、総計シートのA1以降に表示します。

各シートにある異なる並び順の表が、ひとつのシートに集計されました。