Fórmula personalizada para tabelas de dados no Excel

Question

Como alguém usaria uma fórmula personalizada para determinar as condições de formato dos dados?

Ou seja, no Excel, em "Formatação condicional", há um formato chamado "Barras de dados". No entanto, você não pode especificar os critérios que deseja que essas barras sejam exibidas.

O cenário é que tenho um intervalo de dados, e alguns desses valores são irrelevantes devido à sua categorização ou sendo rotulados como outliers. Eles são anormalmente grandes e eu gostaria que eles fossem excluídos da visualização da barra de dados, já que as barras são bastante distorcidas devido aos grandes valores.

    

Answer 1

A solução que encontrei é utilizar o VBA e, condicionalmente, selecionar primeiro o intervalo e aplicar a formatação a esse intervalo. A solução do VoG aqui ajudou.

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

Você pode definir um valor máximo para as barras de dados. Qualquer coisa acima do máximo simplesmente ocupará toda a barra em vez de desviar seus resultados como seu exemplo.

Neste exemplo, tenho o máximo definido para 100:

VocêpodedefinirissoindoparaConditionalFormatting...>ManageRules...>EditRules...ealterarotipoMáximoparaNumber.

Defina isso como algo sensato e seu problema de distorção deve desaparecer.