Перебор элементов сводки, которые были отфильтрованы

ProgramBox

Перебор элементов сводки, которые были отфильтрованы

Post author:admin
Запись опубликована:16 мая, 2023
Post category:Вопросы по программированию

#excel #vba

Вопрос:

Я пытаюсь скопировать значения данных сводной таблицы на другой рабочий лист, но поскольку я выполняю фильтрацию в сводной таблице, которая «скрывает» несколько меток строк, когда я перебираю элементы pivot поля pivot в метке строки, выполняется цикл на несколько раз больше, чем на самом деле должно. Чтобы проиллюстрировать мою проблему, вот код, который я использую.

 Sub Prob()
Dim Ptable1 as PivotTable
Dim PField as PivotField
Dim PField2 as PivotField
Dim NumOfPItem as Long
Dim PItem as PivotItem
Dim aCell as Range

Set Ptable1 = ActiveSheet.PivotTables("PivotTable1")

With Ptable1.PivotFields("DataCol5")
    .CurrentPage = "12/2/2018"
End With

PField = Ptable1.PivotFields("DataCol1")
NumOfPItem = PField.PivotItems.Count

For each PItem in PField.PivotItems
   'Some code to get a cell reference in another worksheet

   'and then some code to copy the value from the pivot table to cells.
   aCell.Offset(0,1).Value = PField2.PivotItems("XX01").DataRange.Cells(PItem.Position,1)
Next PItem
End Sub

Я только что подготовил кое-что простое, чтобы оно отражало основную проблему, с которой я сталкиваюсь.

Нефильтрованная сводная таблица:

Расположение полей:

Это сводная таблица после ее фильтрации.

Я попытался выполнить перебор элементов pivot, которые были видны с помощью PivotItems.Visible свойства, но все элементы видны, следовательно, выполняется перебор всех имеющихся элементов pivot. Переходим к моему следующему скриншоту.

Как видно, все элементы выбраны, поэтому при переборе всех «видимых» элементов сводки из поля сводки будет выполняться цикл по нему 5 раз, потому что есть 5 разных элементов. Однако в этом примере после некоторой фильтрации у меня получается всего 1 метка строки, поэтому для того, что присутствует в сводной таблице, цикл должен выполняться только один раз. Что я здесь делаю не так?

Ответ №1:

Вам нужно посмотреть, видно ли pivotitem . Кроме того, я заметил, что вы используете PI, это может сбивать с толку, так как PI , поэтому, возможно, стоит это изменить.

Вот пример

 Sub x()

Dim p As PivotTable
Dim pf As PivotField
Dim pit As PivotItem

Set p = ActiveSheet.PivotTables(1)
Set pf = p.PivotFields("Name")

For Each pit In pf.PivotItems
    Debug.Print pit.Visible
Next pit

End Sub

1. Я пытался использовать PivotItems.Visible свойство, однако все они возвращают true, поэтому он перебирает каждый элемент сводки вместо того, который отображается после фильтрации. Это делается потому, что, если вы посмотрите на мой последний снимок экрана, все элементы сводки выбраны.

2. Хорошо, как вы выполняете фильтрацию, с помощью фильтров вверху или в возвращаемых данных? Я попробую еще раз через мгновение.

3. Да, точно. Я фильтрую вверху.

4. Чтобы скопировать данные, вы можете использовать databodyrange для копирования в aCell.Offset(0,1).

5. Если бы у меня было 6-7 строк данных, как бы я узнал, сколько раз мне пришлось бы перебирать их?

Ответ №2:

 Sub Solution()
Dim Ptable1 as PivotTable
Dim PField as PivotField
Dim PField2 as PivotField
Dim NumOfRows as Long
Dim PItem as PivotItem
Dim aCell as Range
Dim PFRng as Range
Dim i as long

Set Ptable1 = ActiveSheet.PivotTables("PivotTable1")

With Ptable1.PivotFields("DataCol5")
    .CurrentPage = "12/2/2018"
End With

PField = Ptable1.PivotFields("DataCol1")
PFRng = PField.DataRange
NumOfRows = PFRng.Rows.Count

For i = 1 to NumOfRows
   'Some code to get a cell reference in another worksheet

   'and then some code to copy the value from the pivot table to cells.
   aCell.Offset(0,1).Value = PField2.PivotItems("XX01").DataRange.Cells(i,1)
Next i
End Sub

Я нашел решение. Вместо того, чтобы перебирать каждый элемент сводки, найденный в поле сводки, я бы просто получил диапазон данных поля сводки, посмотрел, сколько строк находится в диапазоне данных, а затем перебрал его, начиная с 1 до последней строки. Таким образом, мне не пришлось бы беспокоиться о том, что видно. Я могу использовать «i» вместо позиции элемента сводки, чтобы получать значения в одной строке, но в разных полях.