Пользовательская формула для панелей данных в Excel

Question

Как можно использовать пользовательскую формулу для определения условий форматирования для панелей данных?

Это означает, что в Excel в разделе "Условное форматирование" есть формат "Строки данных". Однако вы не можете указать критерии, для которых вы хотите, чтобы эти столбцы отображались.

Сценарий состоит в том, что у меня есть диапазон данных, и некоторые из этих значений не имеют значения либо из-за их классификации, либо помечаются как выбросы. Они необычно велики, и я бы хотел, чтобы их исключили из визуализации панели данных, так как полосы сильно искажены из-за больших значений.

Answer 1

Решение, которое я нашел, состоит в том, чтобы использовать VBA и сначала условно выбрать диапазон, а затем применить форматирование к этому диапазону. Решение VoG здесь помогло.

Sub formatDataBarRange()

    ' Build range only for cells labeled to be included
    Dim LR As Long, i As Long, r As Range
    LR = Range("G" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Range("B" & i).Value = "include" Then
            If r Is Nothing Then
                Set r = Range("A" & i)
            Else
                Set r = Union(r, Range("A" & i))
            End If
        End If
    Next i

    ' Add data bar formatting
    r.FormatConditions.AddDatabar
    r.FormatConditions(r.FormatConditions.Count).ShowValue = True
    r.FormatConditions(r.FormatConditions.Count).SetFirstPriority
    With r.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With r.FormatConditions(1).BarColor
        .Color = 2668287
        .TintAndShade = 0
    End With
    r.FormatConditions(1).BarFillType = xlDataBarFillSolid
    r.FormatConditions(1).Direction = xlContext
    r.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    r.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    r.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With r.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With r.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With

End Sub

Answer 2

Вы можете установить максимальное значение для панелей данных. Все, что выше максимума, просто займет всю планку, а не искажает ваши результаты, как в вашем примере.

В этом примере я установил максимум на 100:

Вы можете установить это, перейдя в Conditional Formatting... > Manage Rules... > Edit Rules... и измените Максимальный тип на Number .

Установите это на что-то вменяемое, и ваша проблема перекоса должна исчезнуть.