事務職のためのExcelVBA入門講座 > 集計関係サンプルマクロ > 項目ごとに集計する 項目が複数ある場合のクロス集計

集計関係サンプルマクロ

項目ごとに集計する 項目が複数ある場合のクロス集計

項目が1つ、値が1つだけの単純なものならDictionaryオブジェクトで十分ですが、
大分類の中に中分類があって、さらに小分類があって・・・・・・といったように
項目が複数の場合、クロス集計するにはADODBを使うと便利です。

ADODBとは?


ADODBとは、データベースにアクセスする方法で、これを使うと
ExcelでもAccessみたいにデータベースとして集計できます。

多少データベースの知識が必要ですが、Dictionaryオブジェクトだと
難しい高度な集計をしたいときに重宝します。

ピボットテーブルのデータ元にレコードセットを指定できるため、
特にExcelでクロス集計したいときに便利です。

レコードセットを元にピボットテーブルを作成する


まず、ADODBを使うには「Microsoft ActiveX Data Objects 2.x Library」参照設定が必要です。
xはバージョンが入ります。理由がなければ最新のバージョンにしておきます。

レコードセットを元にピボットテーブルを使う場合は、以下の流れになります。

  1. レコードセットにデータを取得

  2. PivotCachesコレクションに、PivotCacheオブジェクトを作成

  3. PivotCacheオブジェクトのRecordsetプロパティにレコードセットを指定

  4. CreatePivotTableメソッドでピボットテーブルを作成

  5. ピボットテーブルの行フィールド・列フィールド・合計フィールドを指定



サンプルコード



Sub レコードセットからピボットテーブルを作成する()

Dim objRS As New ADODB.Recordset
Dim objPivotCache As PivotCache
Dim strPivotName As String
Dim i As Long

With objRS
  '◆フィールド定義
  .Fields.Append "ID", adInteger
  .Fields.Append "カテゴリ", adVarChar, 1
  .Fields.Append "項目", adVarChar, 255
  .Fields.Append "日付", adDate
  .Fields.Append "値", adDouble
  .Open
  
  '◆レコード追加
  For i = 1 To 5
    .AddNew
    .Fields(0).Value = i
    .Fields(1).Value = Choose(i, "A", "B", "C", "D", "E")
    .Fields(2).Value = "Test" & i
    .Fields(3).Value = DateSerial(Year(Date), Month(Date), i)
    .Fields(4).Value = i * 1.5
  Next i
  
  .MoveFirst
End With

'◆ピボットテーブルの設定&作成
strPivotName = "集計結果"
Set objPivotCache = ThisWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = objRS
objPivotCache.CreatePivotTable ThisWorkbook.Worksheets(1).Range("A1"), strPivotName

'◆ピボットテーブルのフィールドの設定
With ThisWorkbook.Worksheets(1).PivotTables(strPivotName)
  '◆行フィールドの設定
  For i = 1 To 3
    .PivotFields(i).Orientation = xlRowField
    .PivotFields(i).Position = i
    .PivotFields(i).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
  Next i
  
  '◆列フィールド・合計フィールドの設定
  .PivotFields("日付").Orientation = xlColumnField
  .PivotFields("日付").Position = 1
  
  .PivotFields("値").Orientation = xlDataField
  
End With

'◆オブジェクト変数の解放
objRS.Close
Set objRS = Nothing
Set objPivotCache = Nothing


End Sub


ポイントは、Recordsetプロパティを設定するときにSetステートメントを使うことでしょうか。
Setステートメントを使わないとエラーが出ます。
ピボットテーブルの設定はマクロ記録でコードを取得して改変すると多少楽です。

あと、行フィールドはそのままだと小計行が出てきてしまうので、
SubtotalsプロパティはFalseにしておくとスッキリします。

<<項目ごとに集計する Dictionaryオブジェクトでクロス集計 | 事務職のためのExcelVBA入門講座トップへ | クラスとは>>

この記事へのコメント

コメントを書く

お名前
メールアドレス
URL
コメント
[必須入力]
認証コード
[必須入力]


※画像の中の文字を半角で入力してください。

この記事へのトラックバック


Powered by Seesaa
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。