带硬停止的条件格式颜色渐变 [英] Conditional formatting color gradient with hard stops

问题描述

我在 Excel 工作表中有一列数据，其中包含正值和负值.我想要做的是应用条件格式(颜色渐变)，从深绿色到浅绿色表示正值，浅红色到深红色表示负值.

I have a column of data in an Excel sheet which has positive and negative values. What I want to be able to do is apply conditional formatting (a color gradient) from say dark green to light green for positive values and light red to dark red for negative values.

但是，我似乎无法做到这一点.如果我应用从最大值到零的条件格式，零为浅绿色，那么所有负值最终也会变成浅绿色.有没有办法让条件格式仅适用于到某个值而不是超出?我可以类似地为负值制作条件格式，但它再次将正值着色为浅红色.如果我将两者都放在同一张表中，则优先级最高的获胜.

However, I don't seem to be able to do that. If I apply a conditional format from, say, the largest value to zero, with zero as light green, then all the negative values will end up being light green too. Is there a way to make a conditional format apply only up to a certain value and not beyond? I can similarly make a conditional format for the negative values, but again it will color positive values light red. If I have both in the same sheet, then whichever has the highest priority wins.

更新:虽然这真的很难看，但我决定尝试找出哪些单元格大于 0(或者实际上是中点值，在这种情况下为 ~1.33) 并且哪个更低，并将单元格引用显式设置为这些单元格.所以我尝试了这样定义的条件格式(正绿色刻度):

Update: Although this is really ugly, I decided to try to figure out which cells are greater than 0 (or actually a midpoint value, ~1.33 in this case) and which are lower and set the cell references explicitly to those cells. So I tried defined conditional formatting like this (positive green scale):

<x:conditionalFormatting sqref="$E$5 $E$6 $E$10 $E$13 $E$15 $E$17 $E$18 $E$19 $E$22 $E$24 $E$25..." xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:cfRule type="colorScale" priority="1">
    <x:colorScale>
      <x:cfvo type="num" val="1.13330279612636" />
      <x:cfvo type="num" val="1.91050388235334" />
      <x:color rgb="d6F4d6" />
      <x:color rgb="148621" />
    </x:colorScale>
  </x:cfRule>
</x:conditionalFormatting>

像这样(负红色刻度):

And like this (negative red scale):

<x:conditionalFormatting sqref="$E$4 $E$7 $E$8 $E$9 $E$11 $E$12 $E$14 $E$16 $E$20 $E$21 $E$23 $E$26 $E$28 $E$29 $E$30..." xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <x:cfRule type="colorScale" priority="1">
    <x:colorScale>
      <x:cfvo type="num" val="0.356101709899376" />
      <x:cfvo type="num" val="1.13330279612636" />
      <x:color rgb="985354" />
      <x:color rgb="f4dddd" />
    </x:colorScale>
  </x:cfRule>
</x:conditionalFormatting>

这很好用！直到您尝试排序为止(我在此表上有一个自动过滤器)，并且它搞砸了单元格分配.所以现在我的值大于 1.33 应该(并且确实)应用了绿色渐变规则，但现在被红色渐变引用(因此最终变成淡红色).

And this works great! Right up until the point you try to sort (I have an auto filter on this sheet) and it screws up the cell assignments. So now I have so values greater than 1.33 that should (and did) have the green gradient rules applied but are now referenced by the red gradient (and so end up pale red).

我尝试了相对和绝对单元格引用(即减去 $)，但这似乎也不起作用.

I tried with both relative and absolute cell references (i.e. minus the $), but that doesn't seem to work either.

推荐答案

我无法找到使用默认 Excel 条件格式来完成这项工作的方法.可以在 VBA 中创建自己的条件格式算法来启用此功能，但是:

I haven't been able to find a way to make this work using default Excel conditional formatting. It is possible to create your own conditional formatting algorithm in VBA that will enable this functionality, however:

Sub UpdateConditionalFormatting(rng As Range)
    Dim cell As Range
    Dim colorValue As Integer
    Dim min, max As Integer

    min = WorksheetFunction.min(rng)
    max = WorksheetFunction.max(rng)

    For Each cell In rng.Cells
        If (cell.Value > 0) Then
            colorValue = (cell.Value / max) * 255
            cell.Interior.Color = RGB(255 - colorValue, 255, 255 - colorValue)
        ElseIf (cell.Value < 0) Then
            colorValue = (cell.Value / min) * 255
            cell.Interior.Color = RGB(255, 255 - colorValue, 255 - colorValue)
        End If

        Next cell
    End
End Sub

上面的代码将生成以下配色方案，并且可以轻松修改以适应您心目中的任何调色板:

The code above will generate the following color scheme, and can be easily modified to fit whatever palette you have in mind:

您可以在宏中使用此代码，或将其放入 Worksheet_Change 事件中并自动更新(请注意，当安装在 Worksheet_Change 事件处理程序中时，您将失去撤消功能):

You can use this code in a macro, or drop it into a Worksheet_Change event and have it updated automatically (note that when installed in the Worksheet_Change event handler you will lose undo functionality):

Sub Worksheet_Change(ByVal Target As Range)
    UpdateConditionalFormatting Range("A1:A21")
End Sub

这篇关于带硬停止的条件格式颜色渐变的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！