即使第一个相邻单元格为空白，有条件的格式基于相邻的单元格值
我想为单元C3红色染色，因为该值小于第3行（E3）上的下一个填充单元格。
I would like to colour cell C3 red, as the value is less than the next filled cell on row 3 (E3).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您需要在范围
c2的格式规则中进行自定义公式：w。您可以将下面的公式用于绿色：
并相应地使用相同范围的另一种条件格式规则，并因此更改
＆gt;。请注意，相等的情况在您的示例中没有格式。
为了了解原因，有一些工作中的组件。如果OP或任何路人想要一个，我会给出一个轮廓。
首先是Google表中有条件格式的工作方式。我会在这里简短。我们在规则中放置的范围是
c2：w。 Google表将使用该范围内的开始单元格作为参考。对于c2：w，第一个单元格是c2。在公式中，细胞是根据与该参考单元相对位置读取的。因此，在评估单元格格式c2时，它会按原样对待公式。但是，例如，在评估单元格格式c3时，Google Sheet会以1行迭代所有未固定的范围。另一个示例：对于d3，所有未固定的范围均由1行和1列迭代。每当公式评估
true时，将应用格式 - 如果您有适用于给定单元格的后续格式规则，则需要进一步格式化。接下来是条件公式的组成部分。
not（isblank（c2））检查空白单元格，并且只有在单元格非空白时才能使整个公式正确。对于
filter（）用法，请咨询。我将解释如何将filter（）应用于您的示例。在我们的使用中，我们正在尝试摆脱与所讨论的单元格在同一行的范围内的空单元格，并从下一列到列W。（我使用W列W列，因为在您的图像。否
$行的符号，因为行索引的意图是在格式规则中迭代我们的范围c2：w。否$d的符号，因为我们的意思是有问题的单元格旁边的1列，并且应迭代确切的列索引。我们固定W列W，因为末端列没有发展，因此并不是要迭代。忽略这将不会特别改变您的示例结果。但是，可以清楚地说明含义。arrayformula（不（isblank（d2：$ w2））））生成一个本地行数组，其单元格为true/false，每个阵列> false 相应的细胞是非空白的。filter（）依次仅使单元格从d2：$ w2arrayformula中的单元格（not（isblank（d2：$ w2））））那是true。我们只关心第一个非蓝本细胞。要从（本地）阵列检索第一个单元格，我们使用 （...，1,1） 。 （也就是说，省略
index（）也尤其适用于第一个单元格，因为将单个单元格与＆gt;或＆lt; 到阵列范围，仅使用数组的第一个单元格。）You need custom formula in the formatting rules for range
C2:W.And you can use the formula below for green:
And make another conditional formatting rule with the same range and change
>accordingly for red.Note that the equal case does not have formatting in your example.
To understand why, there are a few components at work. I'll give an outline in case OP or any passer-by wants one.
First is how conditional formatting in Google Sheet works. I will be brief here. The range we put in the rule is
C2:W. Google Sheet will use the beginning cell in that range as a reference. ForC2:W, the 1st cell isC2. In the formula, the cells are read in terms of relative position to that reference cell. So when evaluating formatting for cellC2, it treats the formula as is. But, for example, when evaluating formatting for cellC3, Google Sheet iterates all non-fixed ranges by 1 row. Another example: forD3, all non-fixed ranges are iterated by 1 row and 1 column.Whenever the formula evaluates to
true, the format will be applied -- although that is subject to further formatting if you have subsequent formatting rules which apply to the given cell.Next are the components of the conditional formula.
not(isblank(C2))checks for blank cells and makes the whole formula only true when the cell is non-blank.For
filter()usage, please consult official documentation. I will explain howfilter()is applied to your example.In our use, we are trying to rid of empty cells in the range that is on the same row as the cell in question and goes from the next column to column W. (I am using column W because there is no known end column in your image. Please adjust to your application accordingly.) Same row as
C2means row 2. Hence the digit2inD2:$W2. No$sign for row because row index is meant to iterate through our rangeC2:Win the formatting rule. No$sign forDbecause we mean 1 column next to the cell in question and the exact column index should iterate. We fix column W because the end column does not evolve and is thus not meant to iterate. Neglecting this will not particularly change the result in your example. However, it is good to be clear with the meanings.arrayformula(not(isblank(D2:$W2)))produces a local row array whose cells aretrue/falsethat each represents whether the corresponding cell is non-blank.filter()in turn only keeps cells fromD2:$W2for cells inarrayformula(not(isblank(D2:$W2)))that aretrue.We only care about the 1st non-blank cell. To retrieve the 1st cell from a (local) array, we use
index(...,1,1). (That said, omittingindex()also happens to work for the 1st cell in particular because when comparing a single cell with>or<to an array range, only the 1st cell of the array is used.)使用此公式将条件格式应用到所需范围（从单元C2开始）的范围：
Apply a conditional formatting to the range desired (starting from cell C2) using this formula: