事務職のためのExcelVBA入門講座 > ExcelVBAサンプルマクロ > ピボットテーブルの内容をセルに転記

ExcelVBAサンプルマクロ

ピボットテーブルの内容をセルに転記

Excelでクロス集計を行うには、ピボットテーブルを使うのが一番簡単なのですが、
ピボットテーブルって結構使いにくかったりしませんか?

データを集計したあと、うっかり関係ないところをドラッグしてしまって
表の形式が崩れたり・・・・・・。

クロス集計したデータさえ見れればOK、というときはピボットテーブルを作成した後、
セルに転記して元のピボットテーブルは削除してしまう、というのも手です。

以下は、ピボットテーブルのデータをセル範囲に転記するサンプルです。

サンプルコード



Sub ピボットテーブルの内容をセルに転記()

Dim strPivotName As String 'ピボットテーブル名
Dim DataArea As Range    'ピボットテーブルのセル範囲
Dim aryData As Variant   'データ範囲から値を取り込む2次元配列

strPivotName = "ピボット1"

With ThisWorkbook.Worksheets(1)
  '◆ピボットテーブルのデータ範囲取得
  Set DataArea = .PivotTables(strPivotName).TableRange1
  
  '◆見出し行の分セル範囲を縮める(データ範囲を1行下へずらし、1行分縮める)
  Set DataArea = DataArea.Offset(1, 0)
  Set DataArea = DataArea.Resize(DataArea.Rows.Count - 1)
  
  '◆セル範囲から2次元配列作成
  aryData = DataArea
  
  '◆セル範囲に転記&罫線設定
  DataArea.Offset(10, 0) = aryData
  DataArea.Offset(10, 0).Borders.LineStyle = True
  
  '◆元のピボットテーブルを削除
  .PivotTables(strPivotName).TableRange1.Clear
End With

End Sub


使用例


先にピボットテーブルを作っておきます。名前は「ピボット1」としました。
ピボットテーブルの内容をセルに転記01

実行後はこんな感じ。
ピボットテーブルの内容をセルに転記02

サンプルということで、転記する表の位置はやっつけですが、
新規シートにピボットテーブルを一時作成し配列に取り込んだあと、
ピボットテーブルのシートを削除としてもいいと思います。
レコードセットからピボットテーブルを作成するなどと組み合わせてもいいですね。

マクロ解説


ピボットテーブルのデータ範囲は、PivotTableオブジェクトの
TableRange1プロパティを参照します。
ちなみに、TableRange2プロパティもあります。
違いは、
・TabelRange1プロパティ→ページフィールドは含まれない。
・TabelRange2プロパティ→ページフィールドも含む。
らしい。(ヘルプより)

Variant型の変数にセル範囲を指定すると、セル範囲から2次元配列を作成します。

TableRange1プロパティはRangeオブジェクトを返すので、そのまま
「Variant型変数 = TableRange1プロパティ」として2次元配列を作成してもいいのですが、
ピボットテーブルの最初の行にある「合計:値」「日付」のプルダウンが邪魔なので
少しセル範囲を変えています。

Offsetプロパティは、指定した行または列分だけセル範囲をずらすプロパティです。
戻り値は変更したあとのセル範囲(Rangeオブジェクト)です。

◆Offsetプロパティ
Offset(ずらす行数、ずらす列数)
正の数を指定すれば行番号・列番号は大きくなります(下/右にずれます)
負の数を指定すれば行番号・列番号は小さくなります(上/左にずれます)

最初の状態ではデータ範囲はE1:I6ですが、Offsetで1行ずらしてE2:I7にします。
◇最初のデータ範囲(E1:I6)
ピボットテーブルの内容をセルに転記03

◇Offset後のデータ範囲(E2:I7)
ピボットテーブルの内容をセルに転記04

ただ、1行ずらすと7行目は必要ないので、1行分セル範囲を縮めないといけません。
そこで、Resizeプロパティを使います。

◆Resizeプロパティ
Resize(変更後の行数、変更後の列数)
引数を省略すると、元の行数と同じ値になります。
元データの行数はデータ範囲.Rows.Countで取得できますので、
DataArea.Resize(DataArea.Rows.Count - 1)
とすれば、元のデータから1行分縮めた範囲がセル範囲となります。(E2:I6)


<<VBA用語集 | 事務職のためのExcelVBA入門講座トップへ | ランダムな順で並べ替える>>

この記事へのコメント

コメントを書く

お名前
メールアドレス
URL
コメント
[必須入力]
認証コード
[必須入力]


※画像の中の文字を半角で入力してください。

この記事へのトラックバック


Powered by Seesaa
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。