【ExcelVBA】ピポットテーブル作成方法・データ範囲の変更方法を解説!

こんにちは、フリーランスエンジニア兼ライターのワキザカ サンシロウです。

皆さんは、VBAでピポットテーブルを作ったことがありますか?

ピポットテーブルは大量のデータを効率的に集計・分析する時によく使います。

そこで今回は、

・ピポットテーブルとは?

・ピポットテーブルを作る時の考え方

・ピポットテーブルを作る方法

といった基礎的なことから、

・ピポットテーブルを作る具体的なサンプルコード

・ピポットテーブル作成後にデータ追加する方法

・マクロの記録を使った実践的なテクニック

といった応用的な方法まで、徹底的に解説します!

ピポットテーブルとは

ピポットテーブルとは、データ集計・分析をしやすくするためのExcelの機能です。

次のような一覧データがあったとします。

データ一覧:

ピポットテーブルを使えば、言語別の学習時間を確認しやすい形式に変換することができます。

ピポットテーブル化したデータ:

このように一覧データから見やすい形式に変えることができるのが、ピポットテーブルです。

この例では数十行でしたが、数千行・数万行でもすぐに集計結果を確認できるので、データ分析にとても便利な機能ですね。

ピポットテーブルを作るときの考え方

次に、ピポットテーブルを作るときの考え方について簡単に解説します。

ピポットテーブルは以下のような流れでデータを作ります。


セル範囲を指定してピポットテーブル用のキャッシュデータを作成

キャッシュデータをもとにピポットテーブル作成

元のデータを変更しないためキャッシュデータを作ってから、キャッシュデータを使ってピポットテーブルを作るわけですね。

その後、作ったピポットテーブルに集計方法(行・列・値)を指定して、絞り込んだデータを表示します。

そのため、VBAで処理を作る場合も以下のような流れで処理を書いていきます。


1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定

ピポットテーブルの作り方

次に、VBAでピポットテーブルを作る方法について解説します。

先ほどお伝えした通り、以下の手順で作っていくためそれぞれ詳しく解説しますね。


1. データ範囲を指定してキャッシュデータを作る
2. キャッシュデータをもとにピポットテーブル作成
3. 集計したい方法に行・列・値を指定

データ範囲を指定したキャッシュデータ作成方法

まず、ピポットテーブルに使うキャッシュデータを作る方法について解説します。

キャッシュデータの作成方法は次のとおりです。

キャッシュデータを格納する専用の型「PivotCache」を使って変数を作り、変数に「PivotCaches.Create」で作成したキャッシュを格納してます。

このように、まずはキャッシュデータを作成します。

ピポットテーブル作成方法

次は、ピポットテーブルの作成方法について解説します。

さきほど作ったキャッシュデータを使って、次のように書くことでピポットテーブルを作ります。

ピポットテーブル作成方法:

新規シートにピポットテーブルを作成する場合は、次のようになります。

新規シートにピポットを作成する方法:

ピポットテーブル作成直後:

行・列の指定方法

次に、行・列の追加方法について解説します。

ピポットテーブルの行・列の追加方法は次のとおりです。

行・列の追加_コード:

行・列設定後イメージ:

値の指定方法

次に、値の追加方法について解説します。

ピポットテーブルで値を追加するときは、以下のように値フィールドの集計方法を選択します。

VBAでは以下のように書くことで、値フィールドを指定して値を追加することができます。

Functionに指定できる値は次のとおりです。

Functionに指定する集計方法一覧:

No集計方法VBAで指定する値
1合計xlSum
2データの個数xlCount
3平均xlAverage
4最大値xlMax
5最小値xlMin
6xlProduct
7数値の個数xlCountNums
8標本標準偏差xlStDev
9標準偏差xlStDevP
10標本分散xlVar
11分散xlVarP

サンプルコード

次に、サンプルコードをもとに具体的な使い方を解説します。

ピポットテーブル作成に使うデータ:

サンプルコード:

実行後:

新規シートを作成して「A1~D21」までのセル範囲でピポットキャッシュを作成し、行に「学習日」、列に「言語」、値に「時間 (h)」の合計をして指定して、ピポットテーブルを作成しています。

このように、簡単にピポットテーブルを追加することができます。

データ追加時の対処方法

ただ、既存のピポットテーブルにデータを追加したいケースもありますよね。

そのため、データの範囲を変更する方法を合わせて覚えておくのがおすすめです!

変更方法は次のとおりです。

ワークシート.PivotTables(ピポットテーブル名).ChangePivotCache」でデータソースの変更を指定した後、新しくデータソースを設定することで変更できます。

注意点として、「SourceData:=データ元のシート名!データ範囲」のように「データ元のシート名」 + 「!」 + 「データ範囲」になることに注意してください。

先ほど作成したピポットテーブルのデータソースを変更するサンプルを用意しました。

実行前:

サンプルコード:

実行後:

データの範囲を「A1:D21」から「A1:D10」に変更しています。

このように、データ範囲も簡単に変えることができるので、覚えておくと便利です。

マクロの記録を使った実践的なテクニック

ただ、ピポットテーブルは今回解説した方法以外にも様々な機能が備わっていますよね。

それらすべての書き方をVBAで覚えるのは、正直大変です。

そのため、マクロの記録機能を覚えておくのがおすすめです!

マクロの記録を使えば、Excelの操作を自動でVBAのコードに変換することができます。

そのため、「作りたいピポットテーブルを自分で作って、作り方をマクロの記録で保存する」といった使い方ができます。

マクロの記録の詳しい使い方については以下で解説しているので、気になる方はぜひ見てみてくださいね!

【ExcelVBA入門】マクロの記録を使って簡単にマクロを作る方法とは
更新日 : 2018年8月17日

まとめ

今回は、VBAでピポットテーブルを作る方法・データ範囲の変更方法について解説しました。

ピポットテーブルはデータ分析でよく使うので、覚えておくと便利です!

使い方も簡単なので、ぜひ使ってみてくださいね!

LINEで送る
Pocket

最短でエンジニアを目指すなら侍エンジニア塾

cta_under_bnr

侍エンジニア塾は業界で初めてマンツーマンレッスンを始めたプログラミングスクールです。これまでの指導実績は16,000名を超え、未経験から数多くのエンジニアを輩出しています。

あなたの目的に合わせてカリキュラムを作成し、現役エンジニア講師が専属であなたの学習をサポートするため効率よく学習を進めることができますよ。

無理な勧誘などは一切ありません。まずは無料体験レッスンを受講ください。

無料体験レッスンの詳細はこちら

書いた人

Sanshiro Wakizaka

Sanshiro Wakizaka

フリーランスエンジニア兼ライターのワキザカ サンシロウです。
ExcelVBAの自動化ツール開発、WEB開発をメインにエンジニア業務をこなしつつ、サムライエンジニアにてライター業務をしております。

プログラミングをこれからやってみたい方に向けて、ためになる記事を全力で書いていきますので宜しくお願い致します!

おすすめコンテンツ

あなたにぴったりなプログラミング学習プランを無料で診断!

プログラミング学習の効率を劇的に上げる学習メソッドを解説