Office TANAKA - Excel VBA Tips[ピボットテーブルを活用する]

VBAでピボットテーブルを活用する方法を解説します。まず、重要なポイントを認識してください。

Excelの正しい使い方

Excelには正しい使い方があります。

Excelは「入力」→「計算」→「出力」という流れで使うことで、最大のパフォーマンスを発揮します。言うまでもありません。これはExcelに限ったことではなく、そもそも業務というのは、この流れです。「入力」とは、いわゆる"元データ"です。「計算」は単なる足し算・引き算ではなく、編集・分類・抽出・加工などなど、元データに対して行う何らかの処理です。「出力」は"アウトプット"です。何らかの成果物を作ります。さて、この「入力」→「計算」→「出力」という流れの中で、ピボットテーブルは「計算」で使うものです。決して、ピボットテーブルが"最終成果物"ではありません。

ピボットテーブルは、何らかの成果物を得るときの"途中経過"として作るものです。ですから、作成したピボットテーブルのデザインやレイアウトを何とかしようと考えないでください。カラフルな表や、見栄えの良いグラフなどは、成果物として自由に作ってください。そして、ピボットテーブルで計算した結果を、その成果物の値として使います。それが、ピボットテーブルの正しい使い方です。

余談ですが、多くのExcelユーザーが、ピボットテーブルを「出力」で使うものと誤解しているのは、Excelの"教え方"が悪いと感じています。多くのExcel教材では、なんかピボットテーブルを"最終奥義"みたいな扱いで取り上げます。ほら、こうするだけで、こんな集計が簡単にできちゃうんですよぉ～すごいですねぇ～便利ですねぇ～それではみなさん今日はここまで…みたいな。ピボットテーブルは「作って終わり」みたいな教え方がほとんどです。違います。そうじゃないです。それじゃ実務で活用できないんです。だいたい、そういう間違った教え方だと、続いてピボットグラフを紹介したりします。あんな、誰も使わないようなものを。ピボットテーブル作成の次に教えるべきは「詳細の表示」とGETPIVOTDATA関数です。そして、ピボットテーブルの本質を正しく伝えるべきです。ピボットテーブルってのは、要するにSUMIFとCOUNTIFなんだということを。

項目を取得する

ここでは、次のような元データから

次のようなピボットテーブルを作成するとします。

ピボットテーブルを作成するところは「ピボットテーブルを作る」をご覧ください。

まずは、話を簡単にするため、次のようなレイアウトで考えてみましょう。

この"行"エリアに表示されている項目を取得します。これは要するに、"行"エリアに配置した[担当]のユニークデータです。これを取得することで重複しないリストを作成できます。その件は「重複しないリストを作る(4)」をご覧ください。

まず、ピボットテーブルを特定します。ここでは、ピボットテーブルがアクティブシートにあるとしましょう。

ActiveSheet.PivotTables(1)

[担当]や[地域]や[商品]などは、PivotFieldオブジェクトで表されます。特定するときは、PivotFields("担当")のように書きます。そして、このPivotFields("担当")の中に、"吉岡"や"橋本"や"広瀬"などの項目があります。この項目は、PivotItemsコレクションで表されます。したがって、たとえば次のような感じです。

Sub Macro1()
    With ActiveSheet.PivotTables(1)
        MsgBox .PivotFields("担当").PivotItems.Count
    End With
End Sub

ひとつずつ項目を取り出すのなら、たとえば次のようにします。

Sub Macro2()
    Dim i As Long
    With ActiveSheet.PivotTables(1)
        For i = 1 To .PivotFields("担当").PivotItems.Count
            Debug.Print .PivotFields("担当").PivotItems(i)
        Next i
    End With
End Sub

もちろん、For Eachでもいけます。

Sub Macro2()
    Dim pi As PivotItem
    With ActiveSheet.PivotTables(1)
        For Each pi In .PivotFields("担当").PivotItems
            Debug.Print pi
        Next pi
    End With
End Sub

最後の「Debug.Print pi」は、pi.Name または pi.Value とした方が分かりやすいかもしれませんね。

PivotFields("担当")が表示されているセル範囲を特定するには、DataRangeプロパティを使います。こちらはセル(Rangeオブジェクト)を返しますので、これさえ分かれば、あとは"力わざ"で、どうとでもなりますね。

Sub Macro3()
    With ActiveSheet.PivotTables(1)
        MsgBox .PivotFields("担当").DataRange.Address
    End With
End Sub

"列"に配置したフィールドも、考え方は同じです。

Sub Macro4()
    With ActiveSheet.PivotTables(1)
        MsgBox .PivotFields("商品").PivotItems.Count & vbCrLf & _
               .PivotFields("商品").DataRange.Address & vbCrLf & _
               .PivotFields("商品").PivotItems(3)
    End With
End Sub

値(集計結果)を取得する

VBAでピボットテーブルを活用するときは、ピボットテーブルによる集計結果を取得しなければなりません。どうすれば、ピボットテーブル内の集計結果を取得できるのでしょうか。実に簡単です。それはGetDataメソッドです。

Sub Macro5()
    MsgBox ActiveSheet.PivotTables(1).GetData("広瀬 D")
End Sub

項目を1つしか指定しないと、総計を返します。

Sub Macro5()
    MsgBox ActiveSheet.PivotTables(1).GetData("橋本")
End Sub

Sub Macro5()
    MsgBox ActiveSheet.PivotTables(1).GetData("D")
End Sub

空欄を指定すると、右下の総計を返します。

Sub Macro5()
    MsgBox ActiveSheet.PivotTables(1).GetData("")
End Sub

項目の間は、半角のスペースで区切ります。フィールドが増えても、考え方は同じです。なお、引数に指定する順番は問いません。

Sub Macro5()
    MsgBox ActiveSheet.PivotTables(1).GetData("吉岡 神奈川 D")
End Sub

存在しない(表示されていない)ところを取得しようとするとエラーになります。上図のピボットテーブルでは、"吉岡"の"東京"はありません。

さて、このようにGetDataメソッドの引数には、取得したい項目を半角スペースで区切って指定します。もちろん、項目と半角スペースを＆演算子などで文字列結合してもいいんですけど、これ、項目数が多くなると煩雑です。やってみましょうか。上記 GetData("吉岡神奈川 D") の"吉岡"と"神奈川"と"D"が、それぞれセルに代入されていたとします。

"吉岡"と"神奈川"と"D"が入力されているのはアクティブシートです。ピボットテーブルはSheet2に存在するとします。

Sub Macro6()
    MsgBox Sheets("Sheet2").PivotTables(1).GetData(Range("A1") & " " & Range("B1") & " " & Range("C1"))
End Sub

まぁ、できるっちゃ、できるんですけど、面倒くさくないですか？Range("A1") & " " & Range("B1") & " " & Range("C1") のところ。これ、項目数が可変になったりしたら、けっこう煩雑ですし、何よりビギナーは、こうした＆演算子による文字列結合をイメージするのが苦手です。つまり、可読性が悪いです。こんなときは、Join関数を使いましょう。

Sub Macro7()
    Dim A(2) As String
    A(0) = Range("A1")
    A(1) = Range("B1")
    A(2) = Range("C1")
    MsgBox Sheets("Sheet2").PivotTables(1).GetData(Join(A, " "))
End Sub

Join関数については、VBAの関数一覧「Join関数」をご覧ください。

これなら項目数が可変になったら、動的配列を使えばいいですね。動的配列に関しては、変数の使い方「部屋数を変えられる動的配列」をご覧ください。

本当は、一度配列に入れるなんてことをしないで、Join関数に直接セル範囲A1:C1を指定できればいいのですけど、Join関数の引数には配列しか指定できません。セル範囲は指定できないんです。でも、近い将来には、配列なんか使わなくてもできるようになります。Excel 2016のProPlusで追加されたTEXTJOIN関数だったら、直接セル範囲を指定した区切り文字で区切れるんです。

Sub Macro8()
    MsgBox WorksheetFunction.TextJoin(" ", True, Range("A1:C1"))
End Sub

上に書いた「近い将来」とか「ProPlus」などの意味が分からない方は、Excel 2016レビュー「Office 2016は3種類ある」をご覧ください。

VBAからピボットテーブルを操作できるようになると、データの分析や集計が便利になります。流れは、VBAでピボットテーブルを作成する → ピボットテーブルで集計した値を取得して別の表などに代入する → 作成したピボットテーブルをシートごと削除するです。