私は仕事で非常に長いマクロを作成中ですが、もうすぐ完成するところです。しかし、これが最後の部分で、何をやっても修正方法がわかりません。このピボットテーブルでは、「マネージャー」を列として、その他すべて (つまり、すべての月) を値セクションに、「値」を行として設定する必要があります。いろいろいじってみると、すべてのフィールドがピボットフィールドではなくキューブフィールドとして登録されているようです。これを実行すると、.Orientation = xlDataField に到達するとすぐに、「実行時エラー 5... プロシージャ呼び出しまたは引数が無効です」というメッセージが表示されます。また、これらのデータフィールドが平均化され、特定の数値形式になっていることも確認する必要があります。何をやってもうまくいきません。ガイダンス、修正、回避策があれば、ぜひ教えてください。以下に、いじってみるマクロと、ファイルのダウンロード リンクを示します。
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
With pvtTable.CubeFields(i)
If .Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
.Orientation = xlDataField
'has to be averaged
'has to have number format of ##0.00
End If
End With
Next
Next
これはマクロレコーダーに基づいて試した編集です。フィールドを値フィールドに配置しますが、平均に変更する方法はありません。すべての値が「1」としてリストされるだけです。(画像で確認してください).Function = xlAverage に到達すると、「エラー 1004: PivotField クラスの Function プロパティを設定できません」と表示されます。
If Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
With ActiveSheet.PivotTables(1)
.AddDataField ActiveSheet.PivotTables(1) _
CubeFields(cubName), _
"Average of " & cubName
End With
With ActiveSheet.PivotTables(1).PivotFields(cubName)
.Caption = "Average of " & cubName
.Function = xlAverage
End With
マクロ レコーダーを使用してデータフィールドに何かを追加すると、次のようになります。
ActiveSheet.PivotTables("effRent_perBed_Pivot").CubeFields.GetMeasure _
"[effRent_perBed].[Jan-16]", xlSum, "Sum of Jan-16"
ActiveSheet.PivotTables("effRent_perBed_Pivot").AddDataField ActiveSheet. _
PivotTables("effRent_perBed_Pivot").CubeFields("[Measures].[Sum of Jan-16]"), _
"Sum of Jan-16"
With ActiveSheet.PivotTables("effRent_perBed_Pivot").PivotFields( _
"[Measures].[Sum of Jan-16]")
.Caption = "Average of Jan-16"
.Function = xlAverage
End With
これは私が作業しているファイルのコピーです。生のピボット テーブルと、完成品が表示されています。これを 3 つのシートに対して実行する必要があるため、各シートを順に処理する必要があります。 https://drive.google.com/uc?export=download&id=1NLGg8DVEMHnB2Ad7NAKWySevq8naqFjr
これは他のフォーラムにも投稿されています (できるだけ早く多くの人に知ってもらう必要があります) https://www.excelforum.com/excel-programming-vba-macros/1222588-unable-to-add-cubefield-pivotfield-as-a-data-field-in-vba.html#post4856940
答え1
質問をさらに詳しく述べる必要があります。たとえば、ピボットテーブルを最初から作成してフィールドを追加するのか、それとも既にフィールドが含まれている可能性のある既存のピボットテーブルでこれを実行するのかがわかりません。
誰かが私に教えてくれたラバーダックの問題解決/ブログ投稿、そして私はあなたにもそれを紹介します。なぜなら、人々が関連するすべてのことを知っていれば答えるのに役立つからです。
でhttps://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivottable-adddatafield-method-excel.adddatafield の構文は AddDataField( Field , Caption , Function ) であり、関数はオプションであると書かれています。
それで、これを試してみるとどうなるでしょうか?
If Name = "[effRent_perBed].[Manager]" Then
.Orientation = xlColumnField
Else:
With ActiveSheet.PivotTables(1)
.AddDataField ActiveSheet.PivotTables(1) _
CubeFields(cubName), _
"Average of " & cubName, _
xlAverage
End With
End if
元のコード ブロックにはいくつか問題があります。CubeFields コレクションを反復処理し、さらにキューブフィールドの数を反復処理する理由がわかりません。つまり、次の部分です。
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
それは、Cubefields.count の 2 乗ですべてを反復処理するだけではないでしょうか? For i = 1 To pvtTable.CubeFields.Count の部分を捨てて、cubField 参照を直接使用できるはずです。
コードが要約を XLAverage に設定できなかった理由は、データ領域にフィールドを追加すると、名前が実質的に変更されるためだと思います。DataField をそのままにして、次のコードを実行すると、このことがわかります。
Sub Macro1()
'
' Macro1 Macro
'
Dim pvtTable As PivotTable
Dim cubField As CubeField
Dim i As Long
Dim cubName As String
Set pvtTable = ActiveSheet.PivotTables(1)
For Each cubField In pvtTable.CubeFields
Debug.Print cubField.Name
Next
End Sub
これにより、すべてのフィールドの名前が即時ウィンドウに出力されます (開いていると仮定)。この時点で、[effRent_perBed].[Manager] の結果が表示されますが、これはデータフィールドではありません。データフィールドは、[Measures].[effRent_perBed の平均] のようになります。
そして、それがあなたのコードが失敗した理由です。あなたはまだ、関心のあるフィールドをCubeFieldであるかのように参照していました。しかし、それをDataFields領域に追加しようとするとすぐに、新しいDataFieldが作成され、それは集計を変更する必要のあったもの。
ただし、上で述べたように、DataField を作成するときにこれを実行できます。