こんにちは！なるーらぼです！何やら新生活で息子たちが早く帰宅してくるので相手にいそしんでおります…

今回はユーザーインターフェースでもExcelに特化してピボットテーブルとグラフをやってみたいと思います。

ピボットテーブル

まずピボットテーブルですが、「マクロの記録」をつかってサブプロシージャをつくり、そこから改変して駆使されまくっている方もいらっしゃれば、普通にピボットテーブルを使ったことないわーという方まで幅広くいらっしゃるでしょう。

ピボットテーブルは特定のデータ項目を使って集計する便利な機能です。Excelを使えば非常にたやすいことですが、プログラミングするとなるとめんどくさいものの１つです。でも便利なんですよね…

今回はこんなExcelを用意してみました。

そして、ピボットテーブルをE列へつくります。行に日付をもってきて、日ごとの売り上げ合計を集計したものにします。

さらに、このピボットテーブルをもとにした棒グラフもつくりたいと思います。

もちろんExcelではサクサクつくることができますよね。最初にお見せした範囲を選択して、メニューの「挿入」からピボットテーブル、あるいはピボットグラフを選択します。そして、さきほどお話ししたような日付を行に、売り上げを合計というように指定すれば完成します。

in macro

ピボットテーブルを追加するには、ブックオブジェクトのプロパティにある「PivotCaches」が返すコレクションへ「Createメソッド」を利用することで追加することができます。このとき最低限指定が必要なのはどういう形式のものなのか、ということと対象となるデータの範囲です。

ThisWorkbook.PivotCaches.Create(xlDatabase, Sheet1.Range("A1:C16"))

こういった感じですね。これでピボットキャッシュオブジェクトが返されます。この時点では作成の途中で、実際にピボットテーブルを作成するには返されたピボットキャッシュオブジェクトのもつ「CreatePivotTableメソッド」を利用します。ここでもピボットテーブルをつくる範囲の左上になるセル範囲が必要です。マクロから削除することを考えると、オプションになっていますがテーブル名もつけておいたほうがいいでしょう。

.CreatePivotTable(Sheet1.Range(”E1”), Format(Now, "ピボットyyyymmdd"))

省略していますが、先頭のドットの前にはピボットキャッシュオブジェクトがきます。これでピボットテーブルの枠だけができました。集計させるには上記の「CreatePivotTableメソッド」が返すピボットテーブルオブジェクトを利用して、フィールドをつかみます。つかむには「PivotFields」プロパティに名前を指定するかインデックス番号を指定すればフィールドが返されます。これによってピボットテーブルは完成します。

ここまでのものをマクロで書くとこんな感じです。

Sub note_mu()
　Const tablerange As String = "A1:C16"
　Const pivotrange As String = "E1"
　Dim piv As PivotTable
　Dim field As PivotField
　With ThisWorkbook.PivotCaches.Create(xlDatabase, Sheet1.Range(tablerange))
　　Set piv = .CreatePivotTable(Sheet1.Range(pivotrange), Format(Now, "ピボットyyyymmdd"))
　End With
　Set field = piv.PivotFields("日付")
　field.Orientation = xlRowField
　field.Position = 1
　piv.AddDataField piv.PivotFields("売上"), "合計", xlSum
End Sub

これをオートシェイプにでもマクロ登録してクリックすると、自動でピボットテーブルが作成されることでしょう。以下の例ではスマイルマーク？の図形にマクロ登録してあるので、これをクリックするとピボットテーブルが作成されます。

次はグラフもできるようにしてみます。コードに少し追加をしましょう。

Sub note_mu()
　Const tablerange As String = "A1:C16"
　Const pivotrange As String = "E1"
　Dim piv As PivotTable
　Dim field As PivotField
　Dim pivchart As Shape
　With ThisWorkbook.PivotCaches.Create(xlDatabase, Sheet1.Range(tablerange))
　　Set piv = .CreatePivotTable(Sheet1.Range(pivotrange), Format(Now, "ピボットyyyymmdd"))
　End With
　Set pivchart = Sheet1.Shapes.AddChart2(201, xlColumnClustered)
　Set field = piv.PivotFields("日付")
　field.Orientation = xlRowField
　field.Position = 1
　With pivchart.chart
　　.SetSourceData piv.TableRange1
　　.PivotLayout.PivotTable.AddDataField piv.PivotFields("売上"), "合計", xlSum
　End With
End Sub

Excelではグラフも図形のひとつです。ですから追加するのは図形を表すShapesコレクションです。ここへ「AddChart2」でグラフを追加します。あとはグラフの元データとしてピボットテーブルの範囲「TableRange1プロパティ」をチャートオブジェクトの「SetSourceDataメソッド」で指定します。

これでグラフも自動で作成されるようになりました。

ただ、これではまだ問題があります。

毎回同じピボットテーブルとグラフを作成する

上記のマクロを一度実行したら、グラフとピボットテーブルを手動削除していますよね。しないとグラフは同じようなものがもう一つ作成されますし、ピボットテーブルに至っては実行時エラーになります。

これは同じ位置にピボットテーブルを２つ以上つくることはできないためです。よってCreatePivotTableメソッドの実行に失敗しているのです。

対処するには、手動でやっていることをマクロでやればいいのですが…

どうやってピボットテーブルを特定したらいいでしょうか？ここでピボットテーブルをつくるときに指定している名前が役に立ちます。作成されたピボットテーブルは各シートのPivotTablesコレクションに含まれています。ですから、これをFor Each - Nextで列挙していって、名前が指定したものと同じなら削除すればいいわけです。

For Each cache In Sheet1.PivotTables
　If Format(Now, "ピボットyyyymmdd") = cache.Name Then
　　cache.TableRange1.Delete
　End If
Next

上記コード内の変数「cache」は「PivotTable型」です。「Nameプロパティ」でつけた名前を調べることができるので、特定できたら範囲ごと削除します。

では次に、グラフはどうでしょうか？グラフには名前がついていません。でも特定できないことはありません。まず図形をShapesコレクションから探してグラフなのかどうかチェックします。これは「HasChartプロパティ」がTrueを返してくるならグラフありです。あとはグラフの元データのピボットテーブル名が同じく、つけた名前なのであれば削除すればいいのです。

For Each cache In Sheet1.PivotTables
　If Format(Now, "ピボットyyyymmdd") = cache.Name Then
　　For Each delchart In Sheet1.Shapes
　　　If delchart.HasChart Then
　　　　If Format(Now, "ピボットyyyymmdd") =  _
　　　　　delchart.chart.PivotLayout.PivotTable.Name Then
　　　　　　delchart.Delete
　　　　End If
　　　End If
　　Next
　　cache.TableRange1.Delete
　End If
Next

これでも明日になってしまうと名前が一致しなくなるのでエラーになりますが、用途にあわせて工夫してみるといいでしょう。

最後に

今回はピボットテーブルとグラフを何度でもつくることができるようにしてみる方法を見てきました。文字列、配列、テキストファイル（CSV）、Excelそのものの扱い、アドイン、フォームなど見てきましたね。そろそろ一通り見てきたかな？という感じですが、応用編にはいきません！まだまだ基本的なことで遊ぶことができますのでぜひ遊んでいきましょう。

今回は全体のコードを以下へ置いています。埋め込みできればいいんですけどね。

https://gist.github.com/nalulabo/e09515500a9aea3e30221a0fd345acee