ピボットテーブルをマクロ化

質問

はじめまして
業務でピボットテーブルを使うことが多々あるのですが、マクロ化したいものの、なかなかできない状況です。
お助けいただけませんか？

以下のような２列のデータがあります。（行数は毎回異なります,
また１列目の数字も無数にあります）
1列目は数字と文字によるもの
２列目は正負の数値

100 t 40
200 t 60
100 t -50
300 t -20
300 t -10
200 t 10
100 t -10
200 t -90
300 t 50
100 t 20
300 t 30
200 t -10

これらのデータを２列目の正負によって場合わけして、１列目の文字列別に集計したいです。
つまり今回の場合は
100 t 60
200 t 70
300 t 80
100 t -60
200 t -100
300 t -30
のようになります。
手作業で行うと正負の場合わけをした後に、ピボットテーブルを２回使用しなければなりません。
よろしくお願いいたします。

回答No.8

OtenkiAme

こんにちは。ANo.1、ANo.3、ANo.5、ANo.7です。

　MackyNo1 さんに提示されたマクロコードを見ていましたら、このマクロコードは、旧バージョンのファイルをXL2007で操作し、記録されていますね。
　XL2007で記録・作成されたピボットテーブルのマクロコードには、ピボットテーブルを作成する処理は、もちろん、機能が追加されたことで下位バージョンでは動作しないコードが生成されますので注意してください。
　また、XL2007では、ウィザードを使うことはできるのですが、「レイアウト」機能を使うことができませんので、ANo.7で提示した記録作業は、XL2000～XL2003で行ってください。

回答No.7

OtenkiAme

こんにちは。ANo.1、ANo.3、ANo.5です。

　ANo.5ですでにサンプルコードは提示していますが、大体の処理内容がわかったので、「一般機能だけで提示のあったデータから結果のデータを作成するなら…」と一つ一つの作業手順・操作方法を考えてみました。これらの手順は、マクロの自動記録で生成されたコードも編集し易いようにコードの前後につながりを持った処理手順にしています。
　なお、記録した直後のマクロコードでは、２箇所だけ処理を修正する必要がありますが、そこを修正すれば、とりあえず望みの結果を得るマクロコードができると思います。（実際の処理に不要な処理は、多々ありますが…）
　興味があったら操作を記録して記録したコードを提示してみてください。責任を持って添削しますから…。

01. 　データのないシートからデータのある［シート見出し］を
　　　クリック
02. 　行番号の［１］を右クリックし、［挿入］をクリック
03. 　A1セルをクリック
04. 　A1セルに“code”とタイプして［Tab］を押す
05. 　B1セルに“share”とタイプして［Tab］を押す
06. 　C1セルに“judge”とタイプして数式バー左の［入力（レ）］
　　　をクリック
07. 　［Ctrl］と［Shift］を押しながら［＊］を押す
08. 　［（A1と表示されている）名前ボックス］をクリックし、　
　　　Database　と入力して［Enter］を押す
09. 　［Ctrl］を押しながら［G］を押す
10. 　ジャンプダイアログの［範囲選択］をクリックし、“空白セル”
　　　を選択して［ＯＫ］をクリック
11. 　アクティブセル（C2）に =IF(B2>=0,"+","-")　という数式を
　　　作成して［Ctrl］を押しながら［Enter］を押す
12. 　［Shift］を押しながら［F11］を押す
13. 　ワークシートメニューから「データ」＞「ピボットテーブルと…」
　　　をクリック
14. 　ウィザード1/3で、［次へ］をクリック
15. 　ウィザード2/3で、範囲に　“Database”　が表示されている
　　　ことを確認して［次へ］をクリック
16. 　ウィザード3/3で、［オプション］をクリック
17. 　“列の総計”のチェックを外して［ＯＫ］をクリック
18. 　ウィザード3/3で［レイアウト］をクリック
19. 　“judge”フィールドを行エリアにドラッグ＆ドロップして
　　　ダブルクリック
20. 　「集計」で“なし”を選択して［詳細］をクリック
21. 　「自動並べ替えオプション」で“降順”を選択して［ＯＫ］
　　　をクリック
22. 　ピボットテーブルフィールドダイアログの［ＯＫ］をクリック
23. 　「code」フィールドを行エリアにドラッグ＆ドロップ
24. 　「Share」フィールドをデータエリアにドラッグ＆ドロップ
25. 　レイアウトダイアログの［ＯＫ］をクリック
26. 　ウィザード3/3で“既存のワークシート”を選択
27. 　［（=$A/$3と表示された）テキストボックス］をクリックし、
　　　3を1に変更して　=$A/$1　に修正
28. 　ウィザード3/3の［完了］をクリック
29. 　ピボットテーブルツールバーの［レポートの書式設定］
　　　をクリック
30. 　オートフォーマットの右下にある［なし］を選択して［ＯＫ］
　　　をクリック
31. 　［Ctrl］と［Shift］を押しながら［＊］を押す
32. 　［Ctrl］を押しながら［C］を押す（又はコピーボタンでも可）
33. 　標準ツールバーの［貼り付け］ボタンの［▼］をクリックし、
　　　［値］をクリック
34. 　列番号の［Ａ］を右クリックし、［削除］をクリック
35. 　行番号の［１］を右クリックし、［削除］をクリック
36. 　［名前ボックス（A1と表示されている所）］をクリックして
　　　［Enter］を押す
37. 　［Ctrl］を押しながら［Page Down］を押す
38. 　列番号の［Ｃ］を右クリックし、［削除］をクリック
39. 　行番号の［１］を右クリックし、［削除］をクリック
40. 　名前ボックス（A1と表示されている所）］をクリックして
　　　［Enter］を押す
41. 　［Ctrl］を押しながら［Ｆ３］を押す
42. 　名前定義のリストから“Database”を選択して［削除］を
　　　クリックして［閉じる］をクリック
43. 　［Ctrl］を押しながら［Page Up］を押す

お礼

ありがとうございます。
明日作ってみようと思います。
よろしくお願いします。

回答No.6

MackyNo1

>ただ、ピボットテーブルまではできるのですが、
100 t 60
200 t 70
300 t 80
100 t -60
200 t -100
300 t -30
のように並び替えるのにはどうすればよろしいでしょうか？

上記の質問に回答するだけなら、ピボットテーブルでcodeの上にカーソルを置いて「A↓Z」の昇順のアイコンをクリックする操作を加えればよいと思います。

しかし、例示されたコードをみると、B列の一番下のセルの右のセルを取得するところとピボットテーブルの作成範囲が固定されていますのでデータ数が増減すると問題が発生します。

前者については、マクロの記録で対応するなら、B列の一番下のセルを選択後に相対参照に切り替え、１つ右のセルを選択するか、Selection.Offset(0,1)のように選択した範囲の１つ右のセルを指定するコードにする必要があります。

後者については、簡便に対応するならデータ範囲を大きめに指定してピボットテーブルを作成し、ピボットテーブルに表示される「（空白）」という欄を右クリックして「表示しない」を選択するのが簡単な対応です。

あるいは「挿入」「名前」「定義」で参照範囲に「=OFFSET($A$1,0,0,COUNTA($A:$A),1)」のような数式を入力しておいてピボットテーブルのデータ範囲を自動取得できるようにしておき、この範囲の名前をコード内に使用することもできます。

しかし、最も確実な対応策は、すでに回答したように、いったんピボットテーブルを作成しておき（この場合ピボットテーブルのデータ範囲に上記の名前定義を利用する）、新たに作成したいデータを元データの上にコピー貼り付けして（必要に応じて元データの削除をしてから）、ピボットテーブルの「データの更新」を行うのが簡便でよいと思うのですが・・・

回答No.5

OtenkiAme

こんにちは。

ANo1、ANo3です。

Sheet1にデータがあるとして、ピボットテーブルを使って結果を出すサンプルコードです。
ANo3で、記録する際の作業手順を書いた処理＋αを整理したコードなので、MackyNo1 さんに提示されたマクロコードとは処理が異なります。

提示されたマクロコードでは、他のマクロを実行されているようですね。
こういう回答者が分からないマクロがあるとその後の処理に影響が出ることがありますので修正は、質問者さんが行ってくださいね。
あと、"judge"を列エリアに入れたのはどうしてでしょうか？

Sub Macro2()
　　Dim DataSht As Worksheet
　　Dim PvtSht As Worksheet
　　Set DataSht = Worksheets("Sheet1")
　　With DataSht
　　　　.Rows("1:1").Insert
　　　　With .Range("A1:C1")
　　　　　　.Value = Array("code", "share", "judge")
　　　　　　.HorizontalAlignment = xlCenter
　　　　End With
　　　　With .Range("A1").CurrentRegion
　　　　　　.Resize(.Rows.Count - 1, 1).Offset(1, _
　　　　　　　　.Columns.Count - 1).Value = "=IF(RC[-1]>=0,""+"",""-"")"
　　　　　　.Name = "Database"
　　　　End With
　　End With
　　Set PvtSht = Worksheets.Add
　　With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
　　　　SourceData:="Database").CreatePivotTable _
　　　　　　(TableDestination:=PvtSht.Name & "!R1C1")
　　　　.ColumnGrand = False
　　　　.Format xlPTNone
　　　　.PivotFields("judge").Subtotals(1) = False
　　　　.PivotFields("judge").AutoSort xlDescending, "judge"
　　　　.AddFields RowFields:=Array("judge", "code")
　　　　.PivotFields("share").Orientation = xlDataField
　　　　With .TableRange1
　　　　　　.Interior.ColorIndex = xlNone
　　　　　　.Copy
　　　　　　.PasteSpecial Paste:=xlPasteValues
　　　　　　.Range("A1").EntireColumn.Delete
　　　　End With
　　End With
　　With DataSht
　　　　With .Range("A1").CurrentRegion
　　　　　　.Resize(, 1).Offset(, .Columns.Count - 1).Clear
　　　　End With
　　　　.Rows("1:1").Delete
　　　　.Range("A1").CurrentRegion.Resize(1).Copy
　　End With
　　With PvtSht
　　　　.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
　　　　.Rows("1:1").Delete
　　　　Application.Goto .Range("A1")
　　End With
　　Set PvtSht = Nothing
　　Set DataSht = Nothing
End Sub

お礼

返信遅れまして申し訳ございません。
ありがとうございます。
参考にさせていただき、改良してみます。

回答No.4

MackyNo1

>実際に「マクロの記録」でない方法で、マクロはどのように記述すればよいのでしょうか？

どうしてマクロにする必要があるのでしょうか？

普通なら、私の回答したリスト範囲を可変にする方法で作成したピボットテーブルが作ってあれば、「データの更新」をするだけで、データの変更に対応した集計結果が得られると思うのですが・・・

ちなみに、マクロの記録でデータ範囲を可変にするのは、データベース上のセルを選択してＣｔｒｌ＋Ｓｈｉｆｔ＋＊キーのショートカット操作をすれば、リスト範囲を自動的に取得することができます。

お礼

お返事遅くなりました。
マクロの記録で作ったのは以下となります。

Sub Macro1()
'
' Macro1 Macro
'

'
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("A1").Select
ActiveCell.FormulaR1C1 = "code"
Range("B1").Select
ActiveCell.FormulaR1C1 = "share"
Range("C1").Select
ActiveCell.FormulaR1C1 = "judge"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=+IF(RC[-1]>0,""+"",""-"")"
Range("C2").Select
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
Range("C13").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Selection.CurrentRegion.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R13C3", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet4!R3C1", TableName:="ピボットテーブル1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet4").Select
Cells(3, 1).Select
Application.Run "BLPLinkReset"
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("code")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _
"ピボットテーブル1").PivotFields("share"), "合計 / share", xlSum
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("judge")
.Orientation = xlColumnField
.Position = 1
End With
Range("A1").Select
End Sub

ただ、ピボットテーブルまではできるのですが、
100 t 60
200 t 70
300 t 80
100 t -60
200 t -100
300 t -30
のように並び替えるのにはどうすればよろしいでしょうか？

回答No.3

OtenkiAme

こんにちは。

可変する範囲であっても記録したコードを編集して
望みの結果を作成することはできますので、
記録したコードの編集方法をアドバイスすることはできます。

ここは、マクロの作成依頼場所ではないようですので
現在の状態で構いませんから、操作を記録して提示して下さい。
（こちらは分からないことだらけなので…）

記録する際の作業手順を書きます。（XL2000～XL2003と想定しています）

ピボットテーブル作成用のワークシートを挿入する
データのあるシートを選択する
データ右端のセル範囲に数値の正/負を判定する数式を作成する
数式の上にフィールド名を入力する
Ctrl+Shift+*でアクティブセル領域を選択する
名前ボックスに“Database”と名前定義する
挿入したワークシートを選択する
ピボットテーブルウィザードを表示させる
ウィザード2/3で“Database”を確認して［次へ］をクリック
［オプション］ボタンをクリック
列の総計のチェックを外してOKをクリック
［レイアウト］ボタンをクリック
数式の上に付けたフィールド名を行エリアにドラッグしてダブルクリック
集計なしを選択
詳細ボタンをクリック
降順を選択してOK、OK
“100 t”アイテムのあるフィールドを行エリアにドラッグ
“40”アイテムのあるフィールドをデータエリアにドラッグ
レイアウトのOKをクリック
既存のワークシートを選択して、［セル選択］ボタンをクリックし、A1セルを選択
もう一度、［セル選択ボタン］をクリックして、［完了］ボタンをクリック

一応ここまで。

回答No.2

MackyNo1

同じ項目名の値に対して、ピボットテーブルを利用して正と負のデータで別々に集計するなら、当然のことですが正負の区別をする補助列を作成する必要があります。

＞実際にはマクロの記録でやってみたのですが、毎回行数が異なるのでできませんでした。

データ数が追加されても、同じピボットテーブルで簡便に集計できるようにするには、ピボットテーブルを作成するときにウィザードの最初でデータ範囲を大きめに設定して、作成されたテーブルの「(空白）」のセルを右クリックして「表示しない」とするのが簡単です。

実際のデータ範囲に応じて自動的にピボットテーブルの範囲を変動させるなら、以下のような名前定義を利用します。

「挿入」「名前」「定義」でたとえば「リスト」と名前をつけておき。参照範囲に「OFFSET($A1,0,0,COUNTA($A:$A),列の数）」のような関数を設定しておき、ピボットテーブルの範囲を指定するとき「=リスト」のように設定します。

お礼

MackyNo1さん
ありがとうございます。
ご指摘の内容は非常にわかりました。実際に「マクロの記録」でない方法で、マクロはどのように記述すればよいのでしょうか？

回答No.1

OtenkiAme

こんにちは。

> マクロ化したいものの、なかなかできない状況です。
うまくいかなったマクロを提示されては如何ですか？

> ピボットテーブルを２回使用しなければなりません。
正負の作業列を設ければ１回で済むのではないでしょうか？
マクロで作成するなら正負の作業列を追加したり、
作成後に削除することもできると思います。

お礼

>OtenkiAmeさん
お返事ありがとうございます。
実際にはマクロの記録でやってみたのですが、毎回行数が異なるのでできませんでした。
確かに１回でできそうです。
ただデータが毎回1000件を超えるので、できればマクロ化したいものです。