実践ワークシート協会の通常業務では、なるべく「元データ」は Office 365 の SharePoint か、SharePoint Access アプリを経由して SQL Azure に入力して、クラウド上でまとめるようにしています。
生のデータがクラウド上にあれば、あとは Excel のデータ接続を使って生データをワークシートに取り込み、自分の欲しい形に加工すればよいわけです。おおよそオートフィルターかピボットテーブルで目的は達成でき、ちょっと複雑（複数テーブルを使う、列を入れ替える、削除するなど）になれば、Power Query や Power Pivot を使います。
(Office 365 SharePoint Online のリストアイテムの取り込みは OData フィードを使うと幸せになれることが多い。くわしくはこちらを参照)
定型の業務であれば、一度つくったピボットテーブルや表を Excel Services の Excel Web Access を使って SharePoint Online のポータルに貼り付けておけば、ローカル(PC)で Excel を開くことなく状況の確認が可能で、協会のメンバーと情報を共有できます。
Excel ユーザーのための Excel Services – Office 365
強いて言えば、協会のメンバーは全員が Excel を使いこなすことができるので、準備したデータを「あ～したい」、「こ～したい」、と思ったら自分で加工できるのが大きな強みでしょう。
このように協会ではピボットテーブルやオートフィルターを活用・多用するので、最新データは「データ更新」ボタンを押してクラウド上のデータを Excel で確認するか、条件が変われば自分でピボットテーブルやオートフィルターを変更するわけですが、一般的にはそうではなく、日次や月次などで「決まった形」でワークシートを用意する、印刷して提出しなければならないケースを聞くことが多いです。（繰り返しになりますが、協会内部の業務ではこのようなケースはありません。）
例えば、支店別の売り上げとか、製品別の売り上げとか、年月での売り上げでまとめたワークシート。[東京支店][大阪支店][名古屋支店] といったワークシートが並んでいたり、[2015年4月][2015年5月][2015年6月] というようなワークシートが並んでいるブックです。
比較・確認する数字などは定例の会議で確認する項目などはほとんど決まっているのでピボットテーブルを駆使した方が最新データへの更新も考えると楽なのに、、、と思うこともしばしばありますが、もうそうやって何年も十何年もその資料を使っていると、なかなか変更するのも大変です。（変更したほうがいいんですけどね）
だったら、その作り方で楽をしよう、というのが今回のテーマです。Excel VBA と ピボットテーブル の話になります。
グループ化と明細の表示を使う
Excel のピボットテーブルには「明細の表示」という機能があります。通常はピボットテーブルによる集計済みの項目の集計元データを、別のワークシートに作成し内容を確認するためのものです。
たとえば、日付項目を「年・月」でグループ化したピボットテーブルを作り、年月の集計値をダブルクリックすると、その年のその月のデータだけが「明細の表示」としてワークシートが追加されます。
クラウドやサーバーからデータを Excel に取り込み、年・月のシートに分割する、というような定型の業務がある場合は、オートフィルターを使ってコピーする方法より、このピボットテーブルと明細の表示を使うほうがあるかに手間がかかりません。そして、この手作業を VBA で自動化すれば、一瞬で年月ワークシートを作成することができます。元データは SharePoint や SQL Azure といったサーバーから取ってきますので、その時点での最新データから年月ワークシートを作成することが可能です。
グループ化は日付だけではなく、支店名や、製品グループなどの文字列データでも有効です。ピボットテーブルのグループ化の機能が強力なのは、あらかじめどんなユニークデータがあるかを知っている必要がないことです。この性質を利用して「重複データの削除」のような使い方も可能です。
マクロ / VBA でピボットテーブルを作る
実践ワークシート協会代表理事/Office TANAKA の中の人 田中亨曰く、 マクロ記録で恐ろしい(難解な)結果が出る代表格の１つであるピボットテーブルの作成ですが、実は、非常にシンプルなコードでまとめることができます。このあたりは田中亨著の「Excel VBA 逆引き辞典」にシンプルなサンプルコードが紹介されています。
1回かぎりであれば手作業で十分ですが、毎回毎回同じことを繰り返す、作成するシートの数が多いようであれば、VBA を用意してそれを実行するだけで、ある条件でワークシートを作成することを自動化可能になります。
手順としては大きく4つです。
- 元の表(テーブル)から条件を設定してピボットキャッシュ、ピボットテーブルを作成する
- ピボットテーブルから詳細の表示を使って詳細ワークシートを作成
- 作成したワークシートにあるテーブルを並べ替え
- 作成したシートの名前を変更
それぞれの手順を見てみましょう。
例として以下のようなテーブルから年月別のワークシートを複数作成します。(サンプルは 10,000件）
1. 元のテーブルから条件を設定してピボットテーブルを作成する
Sub CreatePivotTable()
'エラー処理なし。元のデータしかない、状態からのマクロ
Dim cWS As Worksheet
Dim nWS As Worksheet
Set cWS = ActiveSheet '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
Set nWS = Sheets.Add 'シートを追加して、nWS とする ActiveSheet は nWS
nWS.Name = "tmp" '追加シート nWS の名前を tmp に変更
Dim D As Range
Set D = cWS.Range("A1").CurrentRegion '元のシートの A1 を含むデータの固まりを D とする
ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1") 'Dからピボットキャッシュとテーブルの作成
With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作を以下で行う
.PivotFields("日付").Orientation = xlRowField '日付を行に設定
.PivotFields("金額").Orientation = xlDataField '金額を値に設定
.ColumnGrand = False '集計行の総計を非表示
End With
Range("A3").Group Periods:=Array(False, False, False, False, True, False, True) '日付行を年・月でグループ化する。マクロ記録で調べるとよい。
End Sub
実行結果は以下のようになります。
ピボットテーブルから年月シートを VBA/マクロで作成する
ピボットテーブルができてしまえば、あとは [詳細の表示] に相当するコードでワークシートを追加していきます。大まかな手順は以下になります。
2. ピボットテーブルから[詳細の表示]を使って、各月のワークシートを作成する
3. 作成したワークシートを日付でソートする
4. シートの名前を年月にする
5. 最後にピボットテーブルのシートを消す
ここは一気にいきます。
上記ピボットテーブルを作成したソースに 2, 3, 4, 5 を実現するコードを加えたものが以下です。
Sub CreateMonthlySheets()
'エラー処理なし。元のデータしかない、状態からのマクロ
Dim cWS As Worksheet
Dim nWS As Worksheet
Set cWS = ActiveSheet '元のデータをアクティブシートにしてマクロを実行、そのアクティブシートを cWS とする
Set nWS = Sheets.Add 'シートを追加して、nWS とする ActiveSheet は nWS
nWS.Name = "tmp" '追加シート nWS の名前を tmp に変更
Dim D As Range
Set D = cWS.Range("A1").CurrentRegion '元のシートの A1 を含むデータの固まりを D とする
ActiveWorkbook.PivotCaches.Add(xlDatabase, D).CreatePivotTable nWS.Range("A1") 'ピボットキャッシュとnWSシートにピボットテーブルを作成
With nWS.PivotTables(1) 'ピボットテーブルのフィールドリスト操作
.PivotFields("日付").Orientation = xlRowField '日付を行に設定
.PivotFields("金額").Orientation = xlDataField '金額を値に設定。各年月シート作成のためのフィールドで「金額」そのものには意味はない。
.ColumnGrand = False '集計行の総計を非表示
End With
Range("A3").Group Periods:=Array(False, False, False, False, True, False, True) '日付行を年・月でグループ化する。設定はマクロ記録で調べるとよい。
Set D = nWS.PivotTables(1).DataBodyRange 'ピボットテーブルの値の範囲（金額集計）を D とする
Dim Item As Range
For Each Item In D '金額集計の範囲の数だけ For Each を回す。個々のセル(Range)は Item に入る
Item.ShowDetail = True '詳細の表示を行う。このとき追加されたシートがアクティブシートになる
With ActiveSheet.Sort.SortFields '追加されたシート(アクティブシート）で日付(A2)のソート条件を設定する
.Clear 'おまじない
'A2の日付の値で、昇順(xlAscending)、標準(xlSortNormal) でソートする条件を設定する
.Add Key:=Range("A2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
End With
With ActiveSheet.Sort 'アクティブシートの Sort オブジェクトでソートを実行する
.SetRange Range("A1").CurrentRegion 'A1を含むデータのかたまり（範囲）を対象とし
.Header = xlYes '一番上の行はヘッダーとして
.Orientation = xlTopToBottom '上から下に向かって
.Apply 'ソートを実行
End With
'セル A2 の日付から年月の文字列を作成し、シートの名前にする
ActiveSheet.Name = Format(ActiveSheet.Range("A2"), "yyyy") & "年" & Format(ActiveSheet.Range("A2"), "m") & "月"
Next Item
Application.DisplayAlerts = False 'ピボットテーブルのシートを消す際のアラート表示をオフにする
nWS.Delete 'ピボットテーブルのシートを消す
Application.DisplayAlerts = True 'アラート表示を元に戻す
End Sub
10,000件程度であれば、数秒で年月別シートの作成が可能です。
最近は Power Query や Power Pivot といった機能の追加・拡張が Excel では多いですが、ワークシートを消す、新規に作る、追加するといった作業は VBA でしかできません。
そういった作業の自動化の要望がある限りは VBA がなくなることはないでしょうね。
ただ、ちょっとだけ「手間」を入れると Power Query の機能だけでも上記のことが可能になります。
機会があれば、Power Query で「年月別のシートを作る」手順をご紹介したいと思います。
0 コメント:
コメントを投稿