![Excel 中数据条的自定义公式](https://linux22.com/image/1490873/Excel%20%E4%B8%AD%E6%95%B0%E6%8D%AE%E6%9D%A1%E7%9A%84%E8%87%AA%E5%AE%9A%E4%B9%89%E5%85%AC%E5%BC%8F.png)
答案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