Я работаю над очень длинным макросом для своей работы и почти закончил, но это последняя часть, и что бы я ни делал, я не могу придумать, как это исправить. В этой сводной таблице мне нужно, чтобы столбцы были "Менеджеры", а все остальное (т. е. все месяцы) было в разделе "Значения", а строки были "Значения". После некоторых манипуляций с макросом выяснилось, что все поля зарегистрированы как CubeFields, а не как PivotFields. Когда я запускаю его, как только он доходит до .Orientation = xlDataField, выдается "Ошибка времени выполнения 5... Недопустимый вызов процедуры или аргумент". Мне также нужно убедиться, что эти поля DataField усреднены и имеют определенный числовой формат. Ничего из того, что я делаю, не работает, и любые рекомендации/исправления/обходные пути будут высоко оценены! Ниже приведены макрос и ссылка на файл для манипуляций.
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: Невозможно задать свойство Function класса PivotField.
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
Когда я использую Macro Recorder, чтобы добавить что-либо в поле данных, я получаю следующее:
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-невозможно-добавить-поле-куба-сводное-поле-как-поле-данных-в-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, и также итерируете по количеству cubefields. т.е. этот кусок:
For Each cubField In pvtTable.CubeFields
For i = 1 To pvtTable.CubeFields.Count
Разве это не будет просто итерацией по всему квадрату Cubefields.count? Вы должны иметь возможность отказаться от этого 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].[Average of effRent_perBed]
И вот почему ваш код не сработал: вы все еще ссылались на интересующее вас поле, как будто это все еще CubeField. Но как только вы попытаетесь добавить его в область DataFields, будет создан новый DataField, иэтотот, для которого вам нужно было изменить агрегацию.
Но, как я уже сказал выше, это можно сделать во время создания DataField.