Блог

Главная - Вопросы по программированию - Excel VBA для выбора сводного поля и связанных значений данных в этой строке сводного поля

Excel VBA для выбора сводного поля и связанных значений данных в этой строке сводного поля

#excel #vba #pivot-table

#excel #vba #сводная таблица

Вопрос:

Я хочу выбрать все ячейки данных, связанные с одним конкретным сводным полем, из элементов строки, как мне это сделать?

Мои данные выглядят примерно так:

           Sum of x  Sum of y  Sum of z
Class1       2.5        1         2
   *Name1    *1        *0        *0
   *Name2    *1        *1        *1
   *Name3    *.5       *0        *1
Class2       3.8       2.6        2
   *NameA    *1        *1        *0
   *NameB   *0.8       *0        *1
   *NameC    *1       *0.6       *0 
   *NameD    *1        *1        *1

Теперь я хочу только выбрать данные с * впереди и выполнить условное форматирование — если значение ячейки меньше 1, выделите ячейку. Если оно больше 1, выделите его другим цветом. У меня возникли проблемы с выбором диапазона данных, который я хочу, как описано выше.

Вот попытка ввода кода: (ошибка: объект не поддерживает это свойство или метод)

 Sub formatPivotTable()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables("test")

Set pf = pt.PivotFields("Name").PivotItems.DataRange.Select (error: object doesnt support this property or method)

With pf.DataRange
    .Interior.ColorIndex = 6
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1"
    With .FormatConditions(1)
        .Interior.ColorIndex = 3

    End With
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"
    With .FormatConditions(2)
        .Interior.ColorIndex = 4
    End With
End With

End Sub

Будем признательны за любую помощь.

Комментарии:

1. Я бы не подумал, что вам Select что-нибудь понадобится… непроверено, но на основе примера в документации что, если изменить эту строку на Set pf = pt.PivotFields("Name") ?

2. @BigBen, который выбрал только мои строки, а не поля данных. Он выбрал все элементы строки от Class1 до NameD. я хочу выбрать только строку имени и связанное поле данных.

3. Я всегда находил полезным объяснение Джона Пелтье о том, как выбирать диапазоны сводной таблицы. Если у меня будет время позже, я мог бы еще раз взглянуть на вашу проблему.

4. Спасибо @BigBen, это бы очень помогло. Я взглянул на объяснение Джона Пелтье, но я не смог найти то, что я хочу сделать.

Ответ №1:

Надеюсь, это то, что вы ищете.

Приведенный ниже код:

  1. Перебирает каждое из них pivotItem в «Имени» pivotField и создает namesRange using Union . namesRange Соответствует $B$5:$B$7,$B$9:$B$12 изображению на скриншоте.
  2. Затем использует Intersect , namesRange.EntireRow и DataBodyRange всей сводной таблицы для получения condFormRange . condFormRange Соответствует $B$5:$D$7,$B$9:$D$12 изображению на скриншоте.

Оттуда условное форматирование выполняется так, как оно у вас уже есть.

 Sub FormatPivotTable()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("test")

    Dim pf As PivotField
    Set pf = pt.PivotFields("Name")

    Dim pi As PivotItem
    Dim namesRange As Range

    For Each pi In pf.PivotItems
        If namesRange Is Nothing Then
            Set namesRange = pi.DataRange
        Else
            Set namesRange = Union(namesRange, pi.DataRange)
        End If
    Next pi

    Debug.Print namesRange.Address ' returns $B$5:$B$7,$B$9:$B$12

    If Not namesRange Is Nothing Then
        Dim condFormRange As Range
        Set condFormRange = Intersect(namesRange.EntireRow, pt.DataBodyRange)
        Debug.Print condFormRange.Address ' returns $B$5:$D$7,$B$9:$D$12

        With condFormRange
            .Interior.ColorIndex = 6
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1"

            With .FormatConditions(1)
                .Interior.ColorIndex = 3
            End With

            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1"

            With .FormatConditions(2)
                .Interior.ColorIndex = 4
            End With
        End With
    End If
End Sub

введите описание изображения здесь