エクセルマクロVBAでピボットテーブル作成｜値範囲を可変しデータ取得

エクセルマクロVBAでピボットテーブルを新しく作成する方法を紹介します。

今回はピボットテーブルとして取得する値の範囲を可変させて取得します。

具体的には、以下の2つを行っていきます。

・VBAでピボットテーブルを新規作成する

・ピボットテーブルとして取得する値範囲を可変とする

・ピボットテーブルの小計を非表示にする

以下で詳細を説明していきます。

1 エクセルマクロVBAでピボットテーブルの範囲可変させて値を取得
2 ピボットテーブルの集計方法や総計小計表示を変更
3 VBAプログラムの解説
4 Excel VBAについて詳しく理解したいなら
- 4.1 VBAの勉強方法

エクセルマクロVBAでピボットテーブルの範囲可変させて値を取得

ここでは、以下のエクセルのデータ一覧の値を取得して、別シートにピボットテーブルを作成します。

今回のデータは以下のような列が含まれています。

1. entrydate
2. userid
3. name
4. seibetsu
5. age
6. totalmoney
7. birthday

この中で、「1.entrydate」を行、「4.seibetsu」を列、「6.totalmoney」を値としてピボットテーブルを作成します。

ピボットテーブルの集計方法や総計小計表示を変更

この事例では単にピボットテーブルを作成するのではなく、集計方法と表示形式を変更して見た目にもこだわっていきます。

実は、VBAを使わずに手動でピボットテーブルを作成すると以下のようなものが作成されます。

これだと「年しか集計値が見えない」、「総計までは不要」という要望に対して、不十分です。

そこで以下の2点を処理できるようなプログラムを作ります。

1. 集計方法を月と年のみに変更
2. 各小計結果を非表示に変更

上記のように集計をA列に年、B列に月を出力し、かつ総計を非表示にしています。

これで見た目がすっきりした表となります。

表としてこのまま別のデータ分析にも活用できる形にしています。

それでは以下でVBAプログラムの解説をしていきます。

VBAプログラムの解説

'プログラム0｜変数設定の指定
Option Explicit

'プログラム1｜プログラム開始
Sub CreatePivotTable()
    
    'プログラム2｜シート設定
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    
    'プログラム3｜ピボットテーブルを生成
    Dim pc As PivotCache
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("A1").CurrentRegion.Address)
    
    'プログラム4｜ピボットテーブルを作成
    Dim pt As PivotTable
    Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range("A1"), TableName:="pivot1")
    
    'プログラム5｜ピボットテーブルのフィールド設定
    With pt
        
        'プログラム6｜行フィールドを設定
        With .PivotFields("entrydate")
            .Orientation = xlRowField
            .DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)
        End With
        
        'プログラム7｜列フィールドを設定
        .PivotFields("seibetsu").Orientation = xlColumnField
        
        'プログラム8｜値フィールドを設定
        .PivotFields("totalmoney").Orientation = xlDataField
    End With
        
    'プログラム9｜ピボットテーブルの表示形式を変更
    With pt
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False     
        .RowGrand = False        
        .HasAutoFormat = False   
        .RepeatAllLabels xlRepeatLabels 
        .NullString = 0
    End With
    
    'プログラム10｜ピボットテーブルの小計を非表示
    Dim pv_fld As PivotField
    For Each pv_fld In pt.PivotFields
        pv_fld.Subtotals(1) = False
    Next

'プログラム11｜プログラム終了
End Sub

'プログラム0｜変数設定の指定

Option Explicit

'プログラム1｜プログラム開始

Sub CreatePivotTable()

'プログラム2｜シート設定

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

'プログラム3｜ピボットテーブルを生成

Dim pc As PivotCache

Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("A1").CurrentRegion.Address)

'プログラム4｜ピボットテーブルを作成

Dim pt As PivotTable

Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range("A1"), TableName:="pivot1")

'プログラム5｜ピボットテーブルのフィールド設定

With pt

'プログラム6｜行フィールドを設定

With .PivotFields("entrydate")

.Orientation = xlRowField

.DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)

End With

'プログラム7｜列フィールドを設定

.PivotFields("seibetsu").Orientation = xlColumnField

'プログラム8｜値フィールドを設定

.PivotFields("totalmoney").Orientation = xlDataField

End With

'プログラム9｜ピボットテーブルの表示形式を変更

With pt

.RowAxisLayout xlTabularRow

.ColumnGrand = False

.RowGrand = False

.HasAutoFormat = False

.RepeatAllLabels xlRepeatLabels

.NullString = 0

End With

'プログラム10｜ピボットテーブルの小計を非表示

Dim pv_fld As PivotField

For Each pv_fld In pt.PivotFields

pv_fld.Subtotals(1) = False

'プログラム11｜プログラム終了

End Sub

以下で詳しく説明します。

プログラム0｜変数宣言の指定

Option Explicit

1	Option Explicit

「Option Explicit」とは、変数宣言を強制するためのものです。

予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。

詳しい説明は以下のページで紹介しています。

エクセルマクロVBAのOption Explicitとは？設定方法と宣言する理由

エクセルマクロVBAではOption Explicitを設定すると変数宣言を強制させることができます。 Option Explicitは入力しておくと、変数宣言が必須になります。そのせいで変数宣言していないとエラー表示が […]

プログラム1｜プログラム開始

Sub CreatePivotTable()

1	Sub CreatePivotTable()

VBAではプロシージャという単位でプログラムを呼び出します。

プロシージャの構文は下記となっています。

Sub プロシージャ名()
    '実行プログラム
End Sub

Sub プロシージャ名()

'実行プログラム

End Sub

「Sub」で始まり「End Sub」で終わります。

プロシージャに関連するmoduleの話については以下で説明しています。

エクセルマクロVBAの標準モジュールを解説｜名前変更/削除/呼び出し/実行/コピー/複数作成/表示など

エクセルマクロVBAでは、標準モジュールを使ってプログラムを作成します。しかしマクロを使い始めたばかりだと、標準モジュールの使い方が分からず困ってしまうことがあります。そこで、エクセルマクロVBA初心者が標準モジュー […]

プログラム2｜シート設定

    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")

1 2	Dim ws As Worksheet Set ws = Worksheets("Sheet1")

Sheet1をwsとして扱います。

プログラム3｜ピボットテーブルを生成

    Dim pc As PivotCache
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("A1").CurrentRegion.Address)

1 2	Dim pc As PivotCache Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range("A1").CurrentRegion.Address)

ピボットテーブルのオブジェクトを生成します。

データタイプを「xlDatabase」、データ範囲を「ws.Range(“A1”).CurrentRegion.Address」としています。

PivotCachesのCreateメソッドでpc(PivotCache)を生成

PivotCachesのCreateメソッドについてMicrosoftのリファレンスを記載しておきます。

名前	必須 / オプション	データ型	説明
SourceType	必須	XlPivotTableSourceType	SourceType には、xlPivotTableSourceType クラスの定数 (xlConsolidation、xlDatabase、または xlExternal) のいずれかを指定できます。
SourceData	省略可能	Variant	新しいピボットテーブルキャッシュのデータを指定します。
Version	省略可能	Variant	ピボットテーブルのバージョンを指定します。バージョンには、XlPivotTableVersionList 定数のいずれかを指定できます。

この事例では、以下のように記述しています。

対象ブック.PivotCaches.Create(SourceType:=データタイプ, SourceData:=セル範囲)

– 引数1. SourceType:=xlDatabase
– 引数2. SourceData:=ws.Range(“A1”).CurrentRegion.Address
– 引数3. Versionは省略

以下で引数について説明します。

引数1. SourceType

Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range(“A1”).CurrentRegion.Address)

SourceTypeは、Sheet1のセル範囲を指定しているため、xlDatabaseとしています。

もしODBC接続、OLEDB接続、Webクエリなどの外部ソースに接続する場合は、xlExternalと入れ込みます。

引数2. SourceData

Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ws.Range(“A1”).CurrentRegion.Address)

SourceType:=xlDatabaseとする場合、SourceDataの引数は必須となります

ここではRangeオブジェクトで指定したセル範囲として「ws.Range(“A1”).CurrentRegion.Address」としています。

この場合、セル範囲の指定が必要になるため、Addressまで記述しています。

ここではAddressまで入力しないと、エラーが発生する可能性があります。

ちなみにエクセルに名前付き範囲を設定して名前を文字列として渡すことも可能です。

なおDebug.Printで検証してみます。

Debug.Print ws.Range("A1").CurrentRegion.Address

>>>A1:G1000

Debug.Print ws.Range("A1").CurrentRegion.Address

>>>A1:G1000

引数3. Versionは省略

バージョンを指定しなかった場合、ピボットテーブルのバージョンは xlPivotTableVersion12 になります。

補足「ws.Range(“A1”).CurrentRegion.Address」でセル範囲を可変で取得

ここでは「ws.Range(“A1”).CurrentRegion.Address」としており、これでセル範囲を可変で取得できるはずです。

ピボットテーブルでセル範囲を取得する場合、セルA1を含んだデータ一覧を対象するのが基本と考えるからです。

もしかすると、セルの開始地点がA1ではない場合あるかもしれません。

その場合は、この事例の「A1」の部分をデータ開始のセルに変更することで対応可能です。

注意点. ピボットテーブル生成はブックに紐づく

ピボットテーブルは「PivotCaches.Create」で生成しますが、ブック(ここではThisWorkbook)のメソッドです。

シートではなくブックに紐づいているのが注意点です。

プログラム4｜ピボットテーブルを作成

    Dim pt As PivotTable
    Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range("A1"), TableName:="pivot1")

1 2	Dim pt As PivotTable Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range("A1"), TableName:="pivot1")

新しいシートを追加して、セルA1を起点にしてプログラム3で生成したピボットテーブルを作成します。

新しく生成したピボットテーブル名をpivot1としています。

PivotCacheのCreatePivotTableメソッドでpt(PivotTable)を生成

PivotCacheのCreatePivotTableメソッドについてMicrosoftのリファレンスを記載しておきます。

名前	必須 / オプション	データ型	説明
TableDestination	必須	Variant	ピボットテーブルレポートの移動先範囲の左上隅にあるセル (結果のピボットテーブルレポートが配置されるワークシートの範囲)。変換先の範囲は、式で指定された PivotCache オブジェクトを含むブック内のワークシート上にある必要があります。
TableName	省略可能	Variant	作成するピボットテーブルレポートの名前を指定します。
ReadData	省略可能	Variant	True を指定すると、外部データベースのすべてのレコードを含むピボットテーブルキャッシュが作成されます。このキャッシュは非常に大きい場合があります。 False を指定すると、データが実際に読み取る前に、一部のフィールドをサーバーベースのページフィールドとして設定できます。
DefaultVersion	省略可能	Variant	ピボットテーブルレポートの既定のバージョンを指定します。

以下、この事例の値を記載します。

ピボットテーブルオブジェクト.CreatePivotTable(tabledestination:=セルの値, TableName:=名前)

– 引数1. tabledestination
– 引数2. TableName
– 引数3. ReadDataは省略
– 引数4. DefaultVersionは省略

引数1. tabledestination

Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range(“A1”), TableName:=”pivot1″)

ピボットテーブルを作成するセルを指定します。

ここでは、Sheets.Addで新しく生成したシートのセルA1にピボットテーブルのデータを作成します。

引数2. TableName

Set pt = pc.CreatePivotTable(tabledestination:=Sheets.Add.Range(“A1”), TableName:=”pivot1″)

作成したピボットテーブルに名前を付けます。

ここでは「pivot1」としています。任意の名前を付けることが可能です。

プログラム5｜ピボットテーブルのフィールド設定

    With pt
        
        'プログラム6｜行フィールドを設定
        'With .PivotFields("entrydate")
        '    .Orientation = xlRowField
        '    .DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)
        'End With
        
        'プログラム7｜列フィールドを設定
        '.PivotFields("seibetsu").Orientation = xlColumnField
        
        'プログラム8｜値フィールドを設定
        '.PivotFields("totalmoney").Orientation = xlDataField
    End With

With pt

'プログラム6｜行フィールドを設定

'With .PivotFields("entrydate")

' .Orientation = xlRowField

' .DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)

'End With

'プログラム7｜列フィールドを設定

'.PivotFields("seibetsu").Orientation = xlColumnField

'プログラム8｜値フィールドを設定

'.PivotFields("totalmoney").Orientation = xlDataField

End With

プログラム4で作成したピボットテーブル(pt)について、Withステートメントを使ってプログラム6~8の記載を省略します。

プログラム6｜行フィールドを設定

        With .PivotFields("entrydate")
            .Orientation = xlRowField
            .DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)
        End With

With .PivotFields("entrydate")

.Orientation = xlRowField

.DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)

End With

Sheet1の「entrydate」列のデータを行フィールドとして設定します。

Orientationプロパティ

.Orientation = xlRowField

PivotFields(データの各列)の内、entrydateをxlRowFieldで行フィールドに設定できます。

日付のデータの内、月と年を出力

.DataRange.item(1).Group Periods:=Array(False, False, False, False, True, False, True)

entrydateは日付データのため、秒、分、時、日、月、四半期、年の7つで集計することができます。

実際、プログラムを見てみると、FalseとTrueが合わせて7つ存在することが分かるはずです。

実はPeriodsのパラメータには、上記の7つの要素を設定することが可能なのです

このPeriodsのパラメータは左から順に以下を意味しています。

1：秒 False
2：分 False
3：時 False
4：日 False
5：月 True
6：四半期 False
7：年 True

各要素の内、Trueのパラメータで集計をさせることが可能です。

この事例では月と年を対象としたいので、5番目と7番目をTrueにして、それ以外をFalseとしています。

プログラム7｜列フィールドを設定

        .PivotFields("seibetsu").Orientation = xlColumnField

1	.PivotFields("seibetsu").Orientation = xlColumnField

データ範囲の「seibetsu」を列フィールドとして設定します。

プログラム8｜値フィールドを設定

        .PivotFields("totalmoney").Orientation = xlDataField

1	.PivotFields("totalmoney").Orientation = xlDataField

データ範囲の「totalmoney」を値フィールドとして設定します。

プログラム9｜ピボットテーブルの表示形式を変更

    With pt
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False       
        .RowGrand = False          
        .HasAutoFormat = False     
        .RepeatAllLabels xlRepeatLabels
        .NullString = 0
    End With

With pt

.RowAxisLayout xlTabularRow

.ColumnGrand = False

.RowGrand = False

.HasAutoFormat = False

.RepeatAllLabels xlRepeatLabels

.NullString = 0

End With

ピボットテーブルの表示形式を変更します。

ピボットテーブルの見た目を、表形式に近づけるように変更を行っています。

変更は具体的には以下のとおりです。

.RowAxisLayout xlTabularRow ‘表形式にする
.ColumnGrand = False ‘列の総計を削除
.RowGrand = False ‘行の総計を削除
.HasAutoFormat = False ‘「更新時に列幅を自動調整する」をFalseで「オフ」にする
.RepeatAllLabels xlRepeatLabels ‘「アイテムのラベルをすべて繰り返す」をTrueで「オン」にする
.NullString = 0 ‘値がないとき、0を出力

以下で小計を非表示にします。

プログラム10｜ピボットテーブルの小計を非表示

    Dim pv_fld As PivotField
    For Each pv_fld In pt.PivotFields
        pv_fld.Subtotals(1) = False
    Next

Dim pv_fld As PivotField

For Each pv_fld In pt.PivotFields

pv_fld.Subtotals(1) = False

ピボットテーブルの小計表示は役立つこともありますが、今回の事例では非表示にします。

小計の表示と非表示は、ピボットテーブルのPivotFiledsのコレクションであるPivotFieldのSubtotals(1)をFalseにすることで実行可能です。

プログラム11｜プログラム終了

End Sub

End Sub

プログラム1と対になるプログラムで、プログラム終了させるための記述です。

「End Sub」を読み込むと、プログラムが終了します。

Excel VBAについて詳しく理解したいなら

VBAを活用すると、仕事を効率化できる幅を広げることができます。

たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。

興味がある人は以下の記事もご覧ください。

エクセルマクロVBA「できること」は何？100事例で自動化を解説

エクセルマクロVBAを使えば面倒な仕事を自動化して、作業をエクセルに処理させることができます。しかし、エクセルマクロと聞いてもそもそも何か分からない人もいたり、どのようなことができるのか知らなかったりする人は多いです。 […]

動画でも解説しています。

エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！

(音声が小さいので、ボリュームを上げてご覧いただければと思います)

VBAの勉強方法

私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。

少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。

エクセルマクロVBAの練習問題・動画90｜初心者や再学習者向けの演習をサポート

エクセルマクロ入門者・初心者の人向けにVBA学習用の練習問題を紹介します。このページの概要は以下です。・VBAの練習問題や動画を紹介・学習者のレベルに応じた勉強の方針を紹介この動画では以下のことを目指して動画を作 […]

上記のリンクでは、VBA勉強に役立つ内容を紹介しています。

興味がある人はご覧ください。

エクセルマクロVBAでピボットテーブル作成｜値範囲を可変しデータ取得

エクセルマクロVBAでピボットテーブルの範囲可変させて値を取得

ピボットテーブルの集計方法や総計小計表示を変更

VBAプログラムの解説

プログラム0｜変数宣言の指定

プログラム1｜プログラム開始

プログラム2｜シート設定

プログラム3｜ピボットテーブルを生成

PivotCachesのCreateメソッドでpc(PivotCache)を生成

引数1. SourceType

引数2. SourceData

引数3. Versionは省略

補足「ws.Range(“A1”).CurrentRegion.Address」でセル範囲を可変で取得

注意点. ピボットテーブル生成はブックに紐づく

プログラム4｜ピボットテーブルを作成

PivotCacheのCreatePivotTableメソッドでpt(PivotTable)を生成

引数1. tabledestination

引数2. TableName

プログラム5｜ピボットテーブルのフィールド設定

プログラム6｜行フィールドを設定

Orientationプロパティ

日付のデータの内、月と年を出力

プログラム7｜列フィールドを設定

プログラム8｜値フィールドを設定

プログラム9｜ピボットテーブルの表示形式を変更

プログラム10｜ピボットテーブルの小計を非表示

プログラム11｜プログラム終了

Excel VBAについて詳しく理解したいなら

VBAの勉強方法

人気記事

サイト運営情報

パソコンスキル

仕事術

エクセルマクロVBAでピボットテーブルの範囲可変させて値を取得

ピボットテーブルの集計方法や総計小計表示を変更

VBAプログラムの解説

プログラム0｜変数宣言の指定

プログラム1｜プログラム開始

プログラム2｜シート設定

プログラム3｜ピボットテーブルを生成

PivotCachesのCreateメソッドでpc(PivotCache)を生成

引数1. SourceType

引数2. SourceData

引数3. Versionは省略

補足「ws.Range(“A1”).CurrentRegion.Address」でセル範囲を可変で取得

注意点. ピボットテーブル生成はブックに紐づく

プログラム4｜ピボットテーブルを作成

PivotCacheのCreatePivotTableメソッドでpt(PivotTable)を生成

引数1. tabledestination

引数2. TableName

プログラム5｜ピボットテーブルのフィールド設定

プログラム6｜行フィールドを設定

Orientationプロパティ

日付のデータの内、月と年を出力

プログラム7｜列フィールドを設定

プログラム8｜値フィールドを設定

プログラム9｜ピボットテーブルの表示形式を変更

プログラム10｜ピボットテーブルの小計を非表示

プログラム11｜プログラム終了

Excel VBAについて詳しく理解したいなら

VBAの勉強方法

Footer

人気記事

サイト運営情報

パソコンスキル

仕事術