ピボットテーブルの操作をマクロの記録で自動化する方法
- ピボットテーブルの操作をマクロの記録を使って自動化する方法について説明します。
- マクロの記録を使用して作られた構文をVBEのF8キーで実行することで、ピボットテーブルの操作を一行ずつ確認することができます。
- しかし、一部の操作については画面上の変化が分からず、どのような操作を行うと構文が作成されるのか分かりません。具体的には、ピボットテーブルウィザードからテーブルを作成する際の操作についての情報が欲しいです。
- ベストアンサー
ピボットテーブルの操作をマクロの記録で
退職者が残したエクセルファイルがあります。 ピボットテーブル生成の操作をマクロの記録を使って自動化したようです。 私はピボットテーブルを一度も操作した事が有りません。 ですがマクロの記録で作られた構文を VBEのF8キーで1行づつ動作させると画面が変化していくので どういう操作をしてマクロの構文が出来たのかは分かりました。 ですが以下の部分だけ画面上の変化が無く何をどうすると この構文が作成されるのかまったく分かりません。 ピボットテーブルに関する件を検索しようにも マクロを使わない手操作でどういう操作、どういう機能を使ったのかが わからないので検索する事も出来ません。 ピボットテーブルウィザードから ワークシートが挿入されてテーブルを作成する際に どういう操作を行うとこのような構文が出来るのか 教えてください。お願いします。 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("仕入先") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品名") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("番号") .PivotItems("(空白)").Visible = False End With
- gx9wx
- お礼率95% (440/460)
- オフィス系ソフト
- 回答数1
- ありがとう数1
- みんなの回答 （1）
- 専門家の回答
質問者が選んだベストアンサー
エクセルのバージョンが記載されていませんので一般論になりますが、「(空白）」と書かれたデータを表示しない設定にすれば、表示されたコードになります。 具体的には、系列名の右の▼をクリックして「（空白）」のチェックを外すか、「（空白）」のセルで右クリックして「表示しない」を選択してください。
関連するQ&A
- ピボットテーブルのマクロに関して
ピボットテーブルのマクロを組む際に、必要な項目だけを表示させたいのですが、うまく行きません。 私は、マクロを学び始めて１カ月で、まだよくわかっていません。 下の例の場合 --------------------------------------------------------------- Sub Macro1() ' ' Macro1 Macro ' ' Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "データ!R1C1:R255C6", Version:=xlPivotTableVersion10).CreatePivotTable _ TableDestination:="Sheet2!R3C1", TableName:="ピボットテーブル2", DefaultVersion _ :=xlPivotTableVersion10 Sheets("Sheet2").Select Cells(3, 1).Select With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("課名") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("地区名") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _ "ピボットテーブル2").PivotFields("売上額"), "データの個数 / 売上額", xlCount With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("課名") .PivotItems("営業第1課").Visible = False .PivotItems("営業第4課").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("地区名") .PivotItems("いなべ町").Visible = False .PivotItems("海南町").Visible = False .PivotItems("吉野川町").Visible = False .PivotItems("魚津町").Visible = False .PivotItems("篠山町").Visible = False .PivotItems("小諸町").Visible = False .PivotItems("小野田町").Visible = False .PivotItems("西予町").Visible = False .PivotItems("石垣町").Visible = False .PivotItems("滝川町").Visible = False .PivotItems("東根町").Visible = False .PivotItems("南足柄町").Visible = False .PivotItems("日南町").Visible = False .PivotItems("萩町").Visible = False .PivotItems("八日町町").Visible = False .PivotItems("浜田町").Visible = False .PivotItems("府中町").Visible = False .PivotItems("本荘町").Visible = False End With End Sub --------------------------------------------------------------- 「営業第2課・営業第3課」を表示させるために、一度、課名を全部 表示させてから「営業第1課・営業第4課」を非表示にしています。 そして、町に関しても、いったん全部を表示させてから、不必要な町を 非表示にしています。 これを、表示したい項目だけ選択できるようなマクロに修正は できないでしょうか。 実際のデータは会社から持ち出せないようになっていて、 わかりづらかったら済みません。
- 締切済み
- その他MS Office製品
- Withステートメントの使い方
プログラムの中で単一のシートやセルなど一つの処理内容 に対して一連の処理をする時「With」ステートメントで ひとくくりで記述できます。 と参考書やwebサイトで見ました。 とりあえず自力で作成した構文は完璧では有りませんが 「With」ステートメントを使えるようになりました。 さて以下の2種類の構文ですがマクロの記録で作成されています。 これを簡素化しようと思ったのですが すでにWith～End Withが散乱しています。 これはもう纏めるのは無理なのでしょうか？ 私が思うに同じ単語が何回も出てくるので もう少し簡素化できるのではと挑戦しましたが 誤動作になってしまいます。 すでにWith～End Withでくくられていますので そのくくりはやはり崩すと駄目なのでしょうか？ くくり内の2行→1行にする程度が限度でしょうか？ これはどのようにすれば簡素化できるのか コツを教えてください。 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("順番") .Orientation = xlColumnField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("区分") .Orientation = xlColumnField .Position = 2 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("区分名") .Orientation = xlColumnField .Position = 3 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("仕入") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品名") .Orientation = xlRowField .Position = 3 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番") .Orientation = xlRowField .Position = 4 End With （省略） With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("仕入") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品名") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("順番") .PivotItems("(空白)").Visible = False End With ActiveWindow.SmallScroll ToRight:=3 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("親品番") .PivotItems("(空白)").Visible = False End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("親品名") .PivotItems("(空白)").Visible = False End With
- ベストアンサー
- オフィス系ソフト
- マクロでピボットテーブルを作成したい
こんばんは。マクロの自動記録を使って、ピボットテーブルを作成したのですが、記録した時のシート名とデータ範囲で記録されてしまいます。アクティブシートのデータがある範囲をピボットテーブルにしたい時は、どうすれば良いでしょうか？ 以下のコードが自動記録で作成したコードです。 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "シート名!R1C1:R1000C30").CreatePivotTable TableDestination:= _ "", TableName:="ピボットテーブル2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品番号") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品名 ") .Orientation = xlRowField .Position = 2 End With Range("A9").Select ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("商品番号").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _ "ピボットテーブル2").PivotFields("数量 "), "データの個数 / 数量 ", xlCount With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("発送日 ") .Orientation = xlPageField .Position = 1 End With Range("A1").Select ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("発送日 ").Orientation = _ xlHidden With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("希望時期") .Orientation = xlColumnField .Position = 1 End With
- ベストアンサー
- オフィス系ソフト
- ピボットテーブルをVBAで管理
ご教授下さい。 ・車両管理をしており、データの集計をとるため、ピボットテーブルを使用しています。 ・データは(日付)(車番)(品番)(品目)(数量)となっていまして、入力する際はフォームで記入しています。 ・品目は10個あり、シート上に品番表を作り、LOOKUP関数で参照しています。 (品番を入力すれば品目が自動転記される形です。) ・品目(品番)は使われない場合もあります。(ここでつまづいてます。) 品目毎にシートを分けて、10個ピボットテーブルを作っていて、 全品目を使用している場合問題はないのですが、 9品目しか使用していない場合でピボットアイテムエラーが出てしまいます。この部分を回避する策はありませんでしょうか？ また、テーブルを10個に分けているため、コード自体もかなり長くなっています。もっと簡素化できる方法があれば併せて教えていただければと思います。 コードも載せておきます。(これと同様のものが10回ループしてます。) '軽油 Dim strDataRng1 As String strDataRng = ActiveWorkbook.Worksheets("燃料").Range("A1").CurrentRegion.Address(, , xlR1C1) ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'燃料'!" & strDataRng).CreatePivotTable TableDestination:="", _ TableName:="軽油", DefaultVersion:=xlPivotTableVersion10 With ActiveSheet.PivotTables("軽油").PivotFields("車番") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("軽油").AddDataField ActiveSheet.PivotTables( _ "軽油").PivotFields("数量"), "合計 / 数量", xlSum With ActiveSheet.PivotTables("軽油").PivotFields("日付") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("軽油").PivotFields("品目") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("軽油").PivotFields("品目") .PivotItems("軽油").Visible = True .PivotItems("灯油").Visible = False .PivotItems("ガソリン").Visible = False .PivotItems("ハイオク").Visible = False .PivotItems("オイル").Visible = False .PivotItems("エレメント").Visible = False .PivotItems("クーラント").Visible = False .PivotItems("バッテリー液").Visible = False .PivotItems("洗車").Visible = False .PivotItems("電球").Visible = False End With
- 締切済み
- オフィス系ソフト
- エクセルマクロの条件コード
お世話になります。自社便だけのデータ抽出のため下のようなマクロにしたところ、データ上にどれか１便（例えば「西濃便」）がないとエラーとなります。 全ての便が揃っていなくともエラーにならないようにするためには、どうしたら良いでしょうか？ With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("総数") .Orientation = xlDataField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("便 名") .PivotItems("ヤマト便").Visible = False .PivotItems("佐川便").Visible = False .PivotItems("日通便").Visible = False .PivotItems("西濃便").Visible = False .PivotItems("チャーター便").Visible = False End With Application.CommandBars("PivotTable").Visible = False End Sub
- ベストアンサー
- オフィス系ソフト
- ピボットテーブルの範囲を可変にしたい
ピボットテーブルのマクロを作っています。 「発注データ」というシートの隣に新しいシートを追加し そこにピボットテーブルを作成するという内容です。 シート名の「発注データ～」と参照範囲「R1C1:R477C8」の部分が毎回変わるため （列は固定で行数が増減）それに対応した記述にしたいのですが どのように変えたらいいのでしょうか？ アドバイスよろしくお願いします。 以下マクロ記録で作成してみました。 Sub pivot() ' pivot Macro Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "発注データ20170202!R1C1:R477C8", Version:=xlPivotTableVersion15). _ CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="ピボットテーブル1" _ , DefaultVersion:=xlPivotTableVersion15 Sheets("Sheet1").Select Cells(3, 1).Select With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("課") .Orientation = xlColumnField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品コード") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品名１") .Orientation = xlRowField .Position = 3 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品名２") .Orientation = xlRowField .Position = 4 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("棚番号") .Orientation = xlRowField .Position = 5 End With With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("単価") .Orientation = xlRowField .Position = 6 End With ActiveSheet.PivotTables("ピボットテーブル1").AddDataField ActiveSheet.PivotTables( _ "ピボットテーブル1").PivotFields("数量"), "合計 / 数量", xlSum ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("品番").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品コード").Subtotals = Array _ (False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品名１").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("製品名２").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("箱番").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("単価").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("数量").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("課").Subtotals = Array( _ False, False, False, False, False, False, False, False, False, False, False, False) ActiveSheet.PivotTables("ピボットテーブル1").RowAxisLayout xlTabularRow End Sub
- ベストアンサー
- Excel（エクセル）
- ピボットテーブルをマクロで行なうには
以下3行目が項目でデータレコードが4～588行目迄入っています。（行数は毎回変わります） これをピボットテーブルで 縦列に"分類" 横行に"月" フィールドに"受注額（合計）" としたく以下マクロ記録しましたが次に実行しようとするとうまくいきません。 参考書読むとActiveSheetとEnd(xlUp)を使うのかなと思えたのですが 具体的にどのようしたら良いのか宜しく御願いします。 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Aシート'!R3C1:R588C12").CreatePivotTable _ TableDestination:="", TableName:="ピボットテーブル1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select With ActiveSheet.PivotTables("ピボットテーブル1") .DisplayNullString = False .RowGrand = False .SmallGrid = False End With ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:="分類", _ ColumnFields:="月" With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("受注額(合計)") .Orientation = xlDataField .NumberFormat = "#,##0_ ;[赤]-#,##0 " End With End Sub
- 締切済み
- その他MS Office製品
- ピボットテーブルのマクロについて
ピボットテーブルのマクロについて教えてください。 NO 場所 ランク 面積 1 東京 上 10 2 大阪 上 20 3 福岡 中 20 4 広島 中 30 5 東京 中 15 6 東京 中 25 7 大阪 下 35 8 福岡 下 45 9 大阪 下 10 10 大阪 下 10 という表があり、 場所ごとランクごとの面積集計を ピボットテーブルのマクロを組んで実施したい のですが（同じ作業を繰り返すので）、 マクロを記録して、内容を実行しようとすると、 「実行時エラー」が出てしまいます。 記録したマクロは以下の通りです。 （ → ←のところでエラーが出ます） Sub Macro1() ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet1!C2:C4").CreatePivotTable TableDestination:="", TableName:= _ "ピボットテーブル2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select → With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("場所") ← .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("ランク") .Orientation = xlRowField .Position = 2 End With ActiveSheet.PivotTables("ピボットテーブル2").AddDataField ActiveSheet.PivotTables( _ "ピボットテーブル2").PivotFields("面積"), "データの個数 / 面積", xlCount Range("A3").Select ActiveSheet.PivotTables("ピボットテーブル2").PivotFields("データの個数 / 面積 ").Function = _ xlSum End Sub ピボットテーブルのフィールドリストがうまく挙がってこないようなのですが、 どのようにマクロを組み直せば、よいのでしょうか？ 作りたい集計表は、以下のようなものです。 場所 ランク 合計 広島 中 30 集計 30 大阪 下 55 上 20 集計 75 東京 上 10 中 40 集計 50 福岡 下 45 中 20 集計 65 総計 220 長々とまた見づらくてすみません。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルでのマクロでピボットテーブル作成
毎月以下のような表の作業をしますが、 あ い う 1 A B 100 2 C D 100 3 D E 200 4 W R 200 5 T G 500 この表で「う」の列の200を抽出し100と500は抽出しない つまり以下の表になる あ い う 3 D E 200 4 W R 200 というマクロを使用したピボットテーブルを作成したいが、とりあえずマクロの自動記録でやると 以下のようになります。 With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("う") .PivotItems("100").Visible = False .PivotItems("500").Visible = False End With ここで問題なのはいつも「う」の列は100と200と500だけとは限らないことです。 時には600があり、また時には500がなかったりします。 そうすると100とか200とか500に何か変数をあてはめて実行すればいいでしょうか？ よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- マクロ実行時にエラーが出てしまいます
マクロを記録し、それを実行しようとしたのですが、途中で下記のようなエラーが出てしまいます。 実行時エラー '1004': PivotTableクラスのPivotFieldsプロパティを取得できません。 黄色い矢印がとまっているのは下記の箇所です。 →With ActiveSheet.PivotTables("ピボットテーブル").PivotFields("送付先")← 自分なりに調べてみたのですが、選択範囲("E2:E1288"等の箇所)が違うデータでマクロを実行するとエラーになるのだと思っているのですが、選択範囲が毎回変わるのに対応できるコードの変更の仕方が分かりません。 もしよろしければ、アドバイスを頂けませんでしょうか。 コードの全体は下記になります。 Sheets("本体").Select Range("E1").Select ActiveCell.FormulaR1C1 = "品目分け" Range("E2").Select ActiveCell.FormulaR1C1 = "=LEFT(RC[-2],1)" Range("E2").Select Selection.AutoFill Destination:=Range("E2:E1288") Range("E2:E1288").Select Sheets("計算結果").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,SourceData:= _ "本体!C1:C5").CreatePivotTable TableDestination:="[計算用.xls]計算結果!R3C1", _ TableName:="ピボットテーブル", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("ピボットテーブル").ColumnGrand = False With ActiveSheet.PivotTables("ピボットテーブル").PivotFields("送付先") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("ピボットテーブル").PivotFields("品目分け") .Orientation = xlColumnField .Position = 1 End With Range("E4").Select ActiveSheet.PivotTables("ピボットテーブル").AddDataField ActiveSheet.PivotTables( _"ピボットテーブル").PivotFields("数量"), "データの個数 / 数量", xlCount ActiveSheet.PivotTables("ピボットテーブル").PivotFields("データの個数/ 数量").Function = _xlSum With ActiveSheet.PivotTables("ピボットテーブル").PivotFields("品目分け") .PivotItems("(空白)").Visible = False End With Range("A1").Select End Sub
- 締切済み
- その他MS Office製品
お礼
エクセルは２００３－ＳＰ３です。 マクロの記録で 教えていただいたように操作したら 同じコードが出来上がりました。 どうもありがとうございました。