ピボットテーブルを使ってみる（データ付き）

はじめに
ピボットテーブルとは
ピボットテーブルを使ってみる
1. 日付と商品名の項目を入替えて表示する
必要な項目のみ表示させる
フィルターエリアに項目を配置する
マクロの記録で動作を確認
まとめ
1. 関連記事:

はじめに

　ピボットテーブルが初めてという方のために、初歩の使い方を解説します。
実際に手元で操作できるようにデータも用意しましたので、ダウンロードしてお使いください。

売上データsample ダウンロード

　データは架空のものです。データ項目は、日付、社員ID、氏名、担当エリア、商品、単価、部数、売上金額です。
999件のデータがあります。ピボットテーブルやパワークエリなどの練習に便利です。

ピボットテーブルとは

　ピボットテーブルは、データを可視化しながら集計できるツールです。VBAや数式マクロなどのプログラミング知識が不要なので誰でも手軽に始める事ができます。

　できる事はデータの並替、合計値、平均値、総数などの集計処理です。上に示したサンプルデータのようにたくさんの項目がある場合、色々な集計方法が考えられます。目的にあった表示になるように視覚的な操作で表を作ることができます。

　ただ、使い方にはコツが必要です。上手く使いこなすにはとにかく手を動かして使い方に慣れていく必要があります。できる事を少しずつ増やしながら進めていくと良いでしょう。

　ピボットテーブルは元データを壊す事はないので、途中で形が崩れてしまっても何度でもやり直す事ができますので、とにかくトライしてみましょう。

ピボットテーブルを使ってみる

　ダウンロードしたファイルから操作できます。

１．使いたい表の一部を選択して、「挿入」から「ピボットテーブル」を選択。

２．ダイアログが出てくるのでデータ範囲を確認し、出力先は「新規ワークシート」にしてOKを押す。

３．新しいシートが作られて、ピボットテーブルのツールが現れる。
　右側の「ピボットテーブルのフィールド」を操作する事でピボットテーブルを作成する。

４．ピボットテーブルは上のデータ項目を下の4つの項目にドラッグする事で作成される。
　４－１　「日付」の項目を「行」フィールドにドラッグする。
　　　　　　すると以下のように日付だけの列が出力される。

　４－２　「商品」の項目を「列」フィールドにドラッグする。
　　　　　　すると以下のように「商品名」が列に並ぶ。
　
　４－３　「売上金額」の項目を「値」フィールドにドラッグ。
　　　　　　すると、各商品の年ごとの総計金額が表示されます。

　ここまでで以下のような表が完成されます。

　年の部分の＋マークをクリックすると、四半期、月などの値を展開する事ができます。
この辺の操作はオートフィルターと同じなので分かりやすいと思います。

　日付の項目に関しては、下のように、月、四半期、年などのフィールドが追加されています。（リストの下の方）
もし、四半期を表示させたくない場合はこのチェックを外します。

日付と商品名の項目を入替えて表示する

　ピボットテーブルでは、データにあるフィールドを差し替える事で様々な表を作ることができます。
　ここでは行と列を入替えて表示させてみましょう

　５－１　「日付」項目を右クリックして「列ラベルに追加」を選択。

　５－２　「商品」項目を右クリックして「行ラベルに追加」を選択。
　すると以下のように日付と商品名を入替える事ができます。

　それぞれの項目のチェックを外して最初からやり直しても大丈夫です。

必要な項目のみ表示させる

　必要なフィールドのデータのみを表示させたい時は、オートフィルターと同じような方法で項目選択できます。
商品は列ラベルに配置されていますので、列ラベルの右側の▼マークをクリックします。以下のようにメニューが出ますので表示させたい項目をクリックします。

フィルターエリアに項目を配置する

　これまで4つのエリアのうち「フィルター」エリアに関しては設定していませんでしたが、フィルターエリアにフィールドを設定すると、その項目にフィルターをかけることができます。

　具体例を見ていきましょう。ここでは、フィルターエリアに「担当エリア」フィールドをドラッグしてみます。

　すると赤で囲んだ部分に新たに項目が出現します。▼マークをクリックすると項目に対してフィルターをかけることができるようになります。

　以下のように「関東」を選択すると、関東エリアの金額のみの合計金額が表示されるようになります。

マクロの記録で動作を確認

　ピボットテーブルでは、VBAを使う必要はありませんが、VBAでプログラミングしたい場合は、マクロの記録を使うと便利です。表を作るまでの操作をマクロの記録でコード出力させると以下のようになりました。

　VBAを使わない方はこの項目は読み飛ばしてください。

Sub Macro1()
'
' Macro1 Macro
'

'   'ピボットテーブルのデータとなる部分を選択
    Sheets("data").Select
    Range("A1").Select
    Application.CutCopyMode = False
    
    'ピボットテーブルを新規追加
    Sheets.Add
    'データ元となるPivotCacheオブジェクトを追加
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "data!R1C1:R1000C8", Version:=8).CreatePivotTable TableDestination:= _
        "Sheet4!R3C1", TableName:="ピボットテーブル2", DefaultVersion:=8
    Sheets("Sheet4").Select
    Cells(3, 1).Select
    'PivotCacheオブジェクトのプロパティを設定
    With ActiveSheet.PivotTables("ピボットテーブル2")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("ピボットテーブル2").RepeatAllLabels xlRepeatLabels
    
    '日付を行に指定
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("日付")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("日付").AutoGroup
    
    '商品を列に指定
    With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
    '売上金額を値に指定
    ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _
        "ピボットテーブル2").PivotFields("売上金額"), "合計 / 売上金額", xlSum
End Sub

ピボットテーブルの作成は最初にブックのPivotCachesオブジェクトを Create してデータの参照元を生成します。
続いて、PivotCacheオブジェクトのプロパティを設定しています。

・ブック.PivotCaches.Create (データの参照元を生成)
・シート.PivotCache のプロパティを設定

次に、ピボットテーブルのフィールドに対して行、列、値を設定しています。
プログラミングする場合は、オブジェクト名やプロパティ値の設定などを参考にカスタマイズすることになるでしょう。