Vbaでピボットテーブルを作成するにはどうすればよいですか?

このガイドをお渡しし、VBA を使用してピボット テーブルを作成し始める前に、お話ししておきたいことがあります。

私が VBA の使い方を学んだのはちょうど 6 年前です。初めてピボット テーブルを作成するマクロ コードを書いたときは失敗でした。

それ以来、私は実際に機能するコードよりも、自分の下手なコーディングから多くのことを学びました。

今日は、マクロ コードを使用してピボット テーブルを自動化する簡単な方法を紹介します。

通常、ピボット テーブルをスプレッドシートに挿入するときは単純なプロセスで行われますが、このプロセス全体が非常に高速であるため、何が起こったのか気付かないほどです。

VBA では、このプロセス全体は同じであり、コードを使用して実行されるだけです。このガイドでは、各ステップを示し、そのコードの記述方法を説明します。

以下の例を見てください。このマクロ コードをボタンで実行すると、新しいワークシートに新しいピボット テーブルが瞬時に返されます。

ピボットテーブルを作成するマクロコード

早速、ピボット テーブルを作成するマクロ コードを書き始めましょう。

VBA でマクロ コードを記述して Excel でピボット テーブルを作成する 8 つの簡単な手順

便宜上、プロセス全体を 8 つの簡単なステップに分けました。これらの手順に従うと、すべてのピボット テーブルを自動化できるようになります。

手順を進めるために、必ずここからこのファイルをダウンロードしてください。

1. 変数を宣言する

最初のステップは、さまざまなものを定義するためにコード内で使用する必要がある変数を宣言することです。

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

上記のコードでは次のように宣言しました。

  1. PSheet: 新しいピボット テーブルのシートを作成します。
  2. DSheet: 技術シートとして使用します。
  3. PChache: ピボット テーブル キャッシュの名前として使用します。
  4. PTable: ピボット テーブルの名前として使用します。
  5. PRange: ソースデータ範囲を設定します。
  6. LastRow と LastCol: データ範囲の最後の行と列を取得します。

2. 新しいスプレッドシートを挿入します

ピボット テーブルを作成する前に、Excel は空のシートを挿入し、そのシート上に新しいピボット テーブルを作成します。

VBAを使用してExcelでピボットテーブルを作成するための新しいワークシートを挿入します

そして、以下のコードは同じことを行います。

「ピボットテーブル」という名前の新しいワークシートがアクティブなワークシートの前に挿入され、同じ名前のワークシートがすでに存在する場合は、最初にそれが削除されます。

新しいワークシートを挿入した後、このコードはピボット テーブル ワークシートに PSheet 変数の値を設定し、ソース データ ワークシートに DSheet 変数の値を設定します。

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

コード内のワークシート名を、データ内の名前に必ず置き換えてください。

3. データ範囲の設定

次のステップは、ソース ワークシートからデータ範囲を設定することです。ここで 1 つ注意する必要があるのは、固定のソース範囲を指定できないことです。

ソース シート内のすべてのデータを識別できるコードが必要です。そして以下がコードです:

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

このコードは、データ テーブルの最初のセルから開始し、最後の行まで、次に最後の列まで選択します。

最後に、この選択した範囲をソースとして設定します。最も良い点は、ピボット テーブルの作成時に毎回データ ソースを変更する必要がないことです。

4. ピボット キャッシュを作成する

Excel 2000 以降では、ピボット テーブルを作成する前に、ピボット キャッシュを作成してデータ ソースを設定する必要があります。

通常、ピボット テーブルを作成すると、Excel はユーザーに尋ねることなく自動的にピボット キャッシュを作成しますが、VBA を使用する必要がある場合は、そのためのコードを記述する必要があります。

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

このコードは 2 つの方法で動作します。1つ目はデータ ソースを使用してピボット キャッシュを設定し、2 つ目は新しく挿入されたワークシートにセル アドレスを設定してピボット テーブルを挿入します。

このコードを変更すると、ピボット テーブルの位置を変更できます。

5. 空のピボット テーブルを挿入します。

ピボット キャッシュの次のステップは、空のピボット テーブルを挿入することです。ピボット テーブルを作成するときは、常に最初に空のピボットを取得し、それからすべての値、列、行を設定することに注意してください。

VBAを使用して空のピボットを挿入し、Excelでピボットテーブルを作成します

このコードは同じことを行います。

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

このコードは空のピボット テーブルを作成し、「SalesPivotTable」という名前を付けます。この名前はコード自体から変更できます。

6. 行フィールドと列フィールドを挿入する

空のピボット テーブルを作成したら、次のステップは通常どおり、行フィールドと列フィールドを挿入します。

行と列のフィールドごとにコードを記述する必要があります。ここでは、行フィールドに年と月を追加し、列フィールドに面積を追加します。

VBAを使用して行フィールドを挿入し、Excelでピボットテーブルを作成します

コードは次のとおりです。

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

このコードでは、年と月を 2 つのフィールドとして指定しています。コードを見ると、位置番号も含まれていることがわかります。この位置番号はフィールドの順序を定義します。

複数のフィールド (行または列) を追加する必要があるたびに、その位置を指定します。また、コードから名前を変更することでフィールドを変更できます。

7. データフィールドの挿入

主なことは、ピボット テーブルの値フィールドを定義することです。

値を定義するコードは行や列の定義とは異なります。ここでは数値、位置、関数の書式設定を定義する必要があるからです。

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

上記のコードを使用して、金額を値フィールドとして追加できます。このコードは、値を区切り文字 (,) を使用した数値としてフォーマットします。

値の追加には xlsum を使用しますが、xlcount やその他の関数を使用することもできます。

8. ピボットテーブルのフォーマットを設定する

最終的には、コードを使用してピボット テーブルをフォーマットする必要があります。通常、ピボット テーブルにはデフォルトの書式設定がありますが、この書式設定は変更できます。

VBA を使用すると、コード内で書式スタイルを設定できます。

VBAを使用してExcel形式でピボットテーブルを作成します

コードは次のとおりです。

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

上記のコードはライン ストリップと「Pivot Style Medium 9」スタイルを適用しますが、 このリンクから別のスタイルを使用することもできます。

最後に、コードを使用する準備が整いました。

[完全なコード] VBA を使用して Excel でピボットテーブルを作成する – コピー アンド ペースト マクロ

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

サンプルファイルをダウンロードする

既存のワークシートのピボット テーブル

上記で使用したコードは、新しいワークシートにピボットテーブルを作成しますが、場合によっては、ワークブックに既に存在するワークシートにピボットテーブルを挿入する必要があります。

上記のコード (新しいワークシートのピボット テーブル) で、新しいワークシートを挿入するコードを記述した部分に、名前を付けます。コードにいくつかの変更を加えてください。

心配しないでください;あなたに見せるね。

まず、ピボット テーブルを挿入するワークシート (既にワークブック内にある) を指定する必要があります。

このためには、以下のコードを使用する必要があります。

新しいワークシートを挿入する代わりに、PSheet 変数にワークシートの名前を指定する必要があります。

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

やるべきことはもう少しあります。最初に使用したコードは、ピボットを挿入する前に、同じ名前のワークシート (存在する場合) を削除します。

既存のワークシートにピボットテーブルを挿入すると、そこに同じ名前のピボットテーブルがすでに存在する可能性があります。

私が言いたいのは、まずそのピボットを削除する必要があるということです。

これを行うには、新しいピボットを挿入する前に、ワークシート (存在する場合) から同じ名前のピボットを削除するコードを追加する必要があります。

追加する必要があるコードは次のとおりです。

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

このコードが何をするのか説明しましょう。

まず、ワークブックに既に存在するピボット テーブルを挿入するワークシートとして PSheet を設定し、DSheet としてデータ ワークシートを設定します。

その後、ワークシートをアクティブ化し、ワークシートから「Sales PivotTable」を削除します。

重要:ワークブック内のワークシートの名前が異なる場合は、コードから変更できます。これを行う必要があるコードを強調表示しました。

最後に、

このコードを使用すると、ピボット テーブルを自動化できます。そして最も良い点は、それがユニークなセットアップであるということです。その後、ワンクリックでピボットテーブルを作成するだけで、時間を大幅に節約できます。さて、一つだけ教えてください。

VBA コードを使用してピボット テーブルを作成したことがありますか?

コメントボックスでご意見をお聞かせください。それらをあなたと共有し、このヒントをあなたの友人と共有したいと思います。

類似記事:

コメントを追加する

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です