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

シェアする

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

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

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

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

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

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

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

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

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

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

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

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

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

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→統合元は2番目のシートから続きます。シートが増えても対応できるように、数字ではなく
Countプロパティイを指定します。ワークシートの数が増減しても対応できます。
Worksheets(i).Range(“A1”).CurrentRegion→各シートデータを集計する起点はセルA1。
Worksheets(“総計”).Range(“A1”).Consolidate→集計したデータは、総計シートのA1以降に表示します。

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


blog comments powered by Disqus