Excel 2010 で VBA を使用してピボットテーブル レポートとグラフを作成する

概要:  Microsoft Excel 2010 で Visual Basic for Applications (VBA) を使用して、ピボットテーブル レポートとグラフを作成および操作する方法を説明します。コードを使用してピボットテーブル レポートおよびグラフを作成すると、これらの作業をより効率的に、かつ繰り返し行うことができます。

適用対象: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

この記事の内容
ピボットテーブル レポートおよびグラフでの VBA 使用の概要
VBA を使用してピボットテーブル レポートを作成する
VBA を使用してグラフを作成する
埋め込まれたグラフを変更する
ピボットテーブル レポートに基づいてグラフを作成する
まとめ
その他の技術情報

公開: 2011 年 6 月

提供元:   Gerard M. Verschuuren 博士、Mr. Excel (英語) | Mark Roberts、Microsoft Corporation

目次

• ピボットテーブル レポートおよびグラフでの VBA 使用の概要

• VBA を使用してピボットテーブル レポートを作成する

• VBA を使用してグラフを作成する

• 埋め込まれたグラフを変更する

• ピボットテーブル レポートに基づいてグラフを作成する

• まとめ

• その他の技術情報

  サンプル ブックのダウンロード: Creating PivotTable Reports and Charts with VBA (PivotTablesAndCharts.xlsm) (英語)

ピボットテーブル レポートおよびグラフでの VBA 使用の概要

この記事を読み、サンプル ブックをダウンロードすると、Visual Basic for Applications (VBA) コードを使用して、ピボットテーブル レポートおよびグラフを作成する方法がわかります。この記事で説明するコードをサンプル データで実行するには、PivotTablesAndCharts (英語) サンプル ブックをダウンロードしてください。

VBA を使用してピボットテーブル レポートを作成する

PivotTablesAndCharts サンプル ブックの CreatePivot マクロで作成されたピボットテーブル レポートを図 1 に示します。

図 1. ピボットテーブル レポート

このピボットテーブル レポートは、サンプル ブックの Employees ワークシートのデータ テーブルに基づいています。

次のコード行に示すように、CreatePivot マクロはワークシートをアクティブ化し、PivotTableWizard メソッドを使用して、ピボットテーブル レポートの作成プロセスを開始します。

    ActiveWorkbook.Sheets("Employees").Select
    Range("A1").Select
    Set objTable = Sheet1.PivotTableWizard

PivotTableWizard メソッドは PivotTable オブジェクトを返します。このオブジェクトには、PivotFields コレクションが関連付けられています。CreatePivot マクロは、PivotField オブジェクトを PivotFields コレクションに追加し、次の PivotTable レポート フィールドを指定することで続行されます。

• DEPT という名前の行フィールド

• LOCATION という名前の列フィールド

• SUM 関数を使用する SALARY フィールドに基づいたデータ フィールド

    Set objField = objTable.PivotFields("DEPT")
    objField.Orientation = xlRowField
    
    Set objField = objTable.PivotFields("LOCATION")
    objField.Orientation = xlColumnField
    
    Set objField = objTable.PivotFields("SALARY")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"

CreatePivot マクロでページ フィールドを追加して、次のコード行に示すように、GENDER フィールドでレポートをフィルター処理することもできます。

    Set objField = objTable.PivotFields("GENDER")
    objField.Orientation = xlPageField

次に、CreatePivot マクロは、新しいピボットテーブル レポートを印刷プレビューで表示します。

    ActiveSheet.PrintPreview

次のコード行に示すように、CreatePivot マクロは、ユーザーが印刷プレビューを閉じた後、新しいピボットテーブル レポートを削除するかどうかを確認します。DisplayAlerts プロパティを False に設定する行により、"選択したシートに、データが存在する可能性があります" という組み込みの警告が Microsoft Excel に表示されなくなります。

    Application.DisplayAlerts = False
    If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True

次のリストは、CreatePivot マクロのコード全体を示しています。

    Sub CreatePivot()
        ' Creates a PivotTable report from the table on Sheet1
        ' by using the PivotTableWizard method with the PivotFields
        ' method to specify the fields in the PivotTable.
        Dim objTable As PivotTable, objField As PivotField
        
        ' Select the sheet and first cell of the table that contains the data.
        ActiveWorkbook.Sheets("Employees").Select
        Range("A1").Select
        
        ' Create the PivotTable object based on the Employee data on Sheet1.
        Set objTable = Sheet1.PivotTableWizard
        
        ' Specify row and column fields.
        Set objField = objTable.PivotFields("DEPT")
        objField.Orientation = xlRowField
        Set objField = objTable.PivotFields("LOCATION")
        objField.Orientation = xlColumnField
        
        ' Specify a data field with its summary
        ' function and format.
        Set objField = objTable.PivotFields("SALARY")
        objField.Orientation = xlDataField
        objField.Function = xlSum
        objField.NumberFormat = "$ #,##0"
        
        ' Specify a page field.
        Set objField = objTable.PivotFields("GENDER")
        objField.Orientation = xlPageField
        
        ' Preview the new PivotTable report.
        ActiveSheet.PrintPreview
        
        ' Prompt the user whether to delete the PivotTable.
        Application.DisplayAlerts = False
        If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
            ActiveSheet.Delete
        End If
        Application.DisplayAlerts = True
     End Sub

VBA を使用してグラフを作成する

サンプル ブックの CreateChart マクロで作成されたグラフを図 2 に示します。

図 2. 3-D 縦棒グラフ

このグラフは、サンプル ブックの Table ワークシートのデータに基づいています。

CreateChart マクロの最初の行は Table ワークシートを選択し、グラフのデータ範囲を選択するようにユーザーに要求します。InputBox メソッドの Default パラメーターを Selection.Address に、Type パラメーターを 8 に設定すると、Range オブジェクトが返されます。このオブジェクトの Address プロパティは、ユーザーが選択した範囲の範囲参照に設定されています。

    ActiveWorkbook.Sheets("Table").Select
    
    Set objSelection = _
        Application.InputBox(Prompt:="Select the columns and rows to chart", _
        Default:=Selection.Address, _
        Type:=8)

次のコード行は、複数のセルが選択されているかどうかを確認し、選択されていない場合は、正しく選択するようにユーザーに要求します。

    If objSelection.Cells.Count = 1 Then
        MsgBox "You must select at least one row or column for the chart range."
        Exit Sub
    End If

次のコード行は、グラフの作成に必要な作業を行います。Charts コレクションの Add メソッドは新しいグラフ シートを作成し、objChart 変数に割り当てられている Chart オブジェクトを返します。次に、With ステートメントは、その変数を使用して、グラフの外観を指定します。まず、SetSourceData メソッドを使用して、ユーザーが選択したデータ範囲を指定します。.Legend.Delete 行を使用して凡例が削除されていることに注意してください。これは、この凡例が、3-D 縦棒グラフの 3 つ目の軸に表示される情報と重複するからです。

    Set objChart = Charts.Add
    With objChart
        .SetSourceData objSelection
        .ChartType = xl3DColumn
        .Location xlLocationAsNewSheet
        .Legend.Delete
        .PlotBy = xlColumns
    End With

CreateChart マクロの残りの行は、グラフの外観に影響を及ぼす追加のプロパティを指定するようにユーザーに要求する例、およびグラフを削除するかどうかを確認する例をいくつか提供します。次のリストは、CreateChart マクロのコード全体を示しています。

    Sub CreateChart()
        ' Create a new chart sheet from the table on Sheet2
        ' by using the Add method of the Charts collection.
        Dim objSelection As Range, objChart As Chart
        
        ' Select the sheet that contains the data.
        ActiveWorkbook.Sheets("Table").Select
        
        ' Prompt the user to select the range to chart
        ' and set the Range object to the specified range.
        Set objSelection = _
            Application.InputBox(Prompt:="Select the columns and rows to chart", _
            Default:=Selection.Address, _
            Type:=8)
        
        ' Verify whether a selection was made.
        If objSelection.Cells.Count = 1 Then
            MsgBox "You must select at least one row or column for the chart range."
            Exit Sub
        End If
        
        ' Create a new chart sheet and specify its source data
        ' and appearance.
        Set objChart = Charts.Add
        With objChart
            .ChartType = xl3DColumn
            .SetSourceData objSelect
            .Location xlLocationAsNewSheet
            .Legend.Delete
            .PlotBy = xlColumns
        End With
        
        ' Ask the user whether to plot by rows instead.
        If MsgBox("Or plot by rows?", vbYesNo) = vbYes Then
            objChart.PlotBy = xlRows
        End If
        
        ' Prompt the user for a title.
        objChart.HasTitle = True
        objChart.ChartTitle.Text = InputBox("Title?")
        
        ' Ask the user whether to delete the chart.
        Application.DisplayAlerts = False
        If MsgBox("Delete chart?", vbYesNo) = vbYes Then
            ActiveSheet.Delete
        End If
        Application.DisplayAlerts = True
    End Sub

埋め込まれたグラフを変更する

サンプル ブックの DynamicChart マクロは、埋め込まれたグラフの外観を、ユーザーが選択した行および列に基づいて変更する方法を示します。図 3 は、マクロを実行し、[北] 列と [東] 列のデータを選択した後のグラフの外観を示しています。

図 3. 動的なグラフ

埋め込まれたグラフにアクセスし、このグラフを操作するには、この記事の前のセクションの例で使用した Workbook オブジェクトの Charts コレクションではなく、Worksheet オブジェクトの ChartObjects コレクションを使用する必要があります。DynamicChart マクロのコードの最初の行は、これを行う方法を示しています。つまり、埋め込まれたグラフが含まれるワークシートをアクティブにして、Chart オブジェクト変数を、そのシートの ChartObjects コレクションの最初のアイテムに設定します。

    ActiveWorkbook.Sheets("Table+Chart").Activate
    Set objChart = ActiveSheet.ChartObjects(1).Chart

コードの次の行は、この記事の「VBA を使用してグラフを作成する」セクションで説明した方法を使用して、グラフにする行または列を選択し、その選択内容を Range オブジェクト変数に割り当てるようにユーザーに要求します。

グラフのカテゴリに先頭行を使用するか先頭列を使用するかは、ユーザーが選択した内容に基づいて決まります。これを行うために、選択した行数と選択した列数が比較されます。行数の方が多い場合は、先頭列の値がカテゴリとして選択されます。列数の方が多い場合は、先頭行の値がカテゴリとして選択されます。

    r = objSelection.Rows.Count
    c = objSelection.Columns.Count
    If r > c Then
        Set objCategories = Range(Cells(1, 1), Cells(r, 1))
    Else
        Set objCategories = Range(Cells(1, 1), Cells(1, c))
    End If

DynamicChart マクロのコードの最後の行は、Union メソッドを使用して、ユーザーの選択内容から 1 つの範囲を作成します。また、コードがカテゴリに使用するように決定した行または列も作成します。最後に、その範囲を SetSourceData メソッドに渡し、グラフの表示を更新します。

    Set objSrcData = Union(objCategories, objSelection)
    objChart.SetSourceData objSrcData

次のコード例は、DynamicChart マクロのリスト全体を示しています。

    Sub DynamicChart()
        ' Adjusts the embedded chart on Sheet3.
        Dim objChart As Chart, objChObject As ChartObject
        Dim objSelection As Range, objSrcData As Range, objCategories As Range
        Dim r As Long, c As Long
        
        ' Activate the sheet that contains the chart.
        ActiveWorkbook.Sheets("Table+Chart").Activate
        
        ' Access the chart from the ChartObject collection
        ' of the active sheet.
        Set objChart = ActiveSheet.ChartObjects(1).Chart
        
        ' Prompt user to select the rows or columns to chart
        ' and set the Range object to the specified range.
        Set objSelection = _
            Application.InputBox(Prompt:="Select entire rows or columns to chart", _
            Default:=Selection.Address, _
            Type:=8)
        
        ' Determine whether the user selected rows or columns,
        ' and then use either the first row or first column
        ' as the range for categories.
        r = objSelection.Rows.Count
        c = objSelection.Columns.Count
        If r > c Then
            Set objCategories = Range(Cells(1, 1), Cells(r, 1))
        Else
            Set objCategories = Range(Cells(1, 1), Cells(1, c))
        End If
        
        ' Create a single range from the union of
        ' categories and selected data, and then
        ' update the chart.
        Set objSrcData = Union(objCategories, objSelection)
        objChart.SetSourceData objSrcData
    End Sub

ピボットテーブル レポートに基づいてグラフを作成する

サンプル ブックの CreateChartForPivot マクロが示すように、ピボットテーブル レポートに基づいてグラフを作成することもできます。このマクロは、この記事の「VBA を使用してピボットテーブル レポートを作成する」セクションで説明した CreatePivot マクロを呼び出すことで、このプロセスを開始します。

新しく作成されたピボットテーブル レポートに、新しいワークシートの PivotTables コレクションからアクセスするコード行は、重要なメイン アイテムの 1 つです。

    Set objPivot = ActiveSheet.PivotTables(1)

また、PivotTable プロパティを使用して、ピボットテーブル レポート全体が含まれる範囲 (ページ フィールドを除く) を作成するコード行も、重要なアイテムです。

    Set objPivRange = objPivot.TableRange1

CreateChartForPivot マクロの最後のコード行は、この範囲を使用してソース データを指定してから、グラフの外観を指定します。ここでも凡例が削除されていることに注意してください。これは、この凡例が、3-D 縦棒グラフの 3 つ目の軸の情報と重複するからです。

    With objChart
        .SetSourceData objPivRange
        .ChartType = xl3DColumn
        .Legend.Delete
    End With

次のコード例は、CreateChartForPivot マクロのリスト全体を示しています。

    Sub CreateChartForPivot()
        ' Creates a chart based on a PivotTable report.
        Dim objPivot As PivotTable, objPivotRange As Range, objChart As Chart
        
        ' Call the CreatePivot macro to create a new PivotTable report.
        CreatePivot
       
        ' Determine whether the user deleted the PivotTable report,
        ' and if so, exit the macro.
        If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
        
        ' Access the new PivotTable from the sheet's PivotTables collection.
        Set objPivot = ActiveSheet.PivotTables(1)
        
        ' Add a new chart sheet.
        Set objChart = Charts.Add
        
        ' Create a Range object that contains
        ' all of the PivotTable data, except the page fields.
        Set objPivotRange = objPivot.TableRange1
        
        ' Specify the PivotTable data as the chart's source data.
        With objChart
            .SetSourceData objPivotRange
            .ChartType = xl3DColumn
            .Legend.Delete
        End With
    End Sub

まとめ

この記事と PivotTablesAndCharts サンプル ブックは、Excel の VBA コードを使用して、ピボットテーブル レポートおよびグラフを作成する方法を示しています。

その他の技術情報

• Excel 2010 VBA の基礎知識

• Excel デベロッパー センター

• Office 開発者向け技術情報