(Excel) 基于相邻单元格值的条件格式 [英] (Excel) Conditional Formatting based on Adjacent Cell Value

问题描述

我正在尝试根据相邻单元格的值在 Excel 中对一系列单元格应用条件格式，以实现如下目的:

I'm trying to apply conditional formatting in Excel on a range of cells, based on the adjacent cell's value, to achieve something like this:

目标是如果 B 列(实际费用)中的值大于其在 C 列(预期费用)中的相邻值，则将其突出显示为红色.我遵循了许多教程，这些教程说通过选择 Conditional Formatting>New Rules>Use a Formula to determine which Cells to Format 然后应用一个规则 =$B4>$C4 来应用条件格式如果实际"单元格大于预期"单元格，则将其格式化为红色.这对一个单元格很有用，但当我尝试将其应用于一系列单元格时，效果就不那么好了.我使用了格式刷并编辑了规则以应用于一系列单元格(即 $B$2:$B$5)，如下所示，但它仅基于初始公式的格式(如果 B4 大于C4) 并且一切都变红了.

The goal is to highlight values in Column B (Actual Expense) red if the value is greater than it's adjacent value in column C (Expected Expense). I've followed a number of tutorials that said to apply conditional formatting by selecting Conditional Formatting>New Rules>Use a Formula to Determine Which Cells to Format then applying a rule =$B4>$C4 which would format the 'Actual' cell red if it were greater than the 'Expected' cell. This works great for one cell, but not so well when I attempt to apply this to a range of cells. I've used the format painter as well as editing the rule to be applied over a range of cells (i.e. $B$2:$B$5) as shown below but it only bases the formatting on the initial formula (if B4 is greater than C4) and everything undesirably turns red.

我不想为每一行制定单独的规则.有没有办法将其合并为一条规则?

I'd rather not make individual rules for every row. Is there a way to consolidate this into one rule?

推荐答案

公式中行号前的$符号需要去掉....公式中使用的行号要对应第一行数据，因此如果您将其应用于(适用于")范围 $B$2:$B$5 它必须是这个公式

You need to take out the $ signs before the row numbers in the formula....and the row number used in the formula should correspond to the first row of data, so if you are applying this to the ("applies to") range $B$2:$B$5 it must be this formula

=$B2>$C2

通过使用该相对"版本而不是绝对"版本，Excel(隐式)调整范围内每一行的公式，就好像您正在复制公式一样

by using that "relative" version rather than your "absolute" one Excel (implicitly) adjusts the formula for each row in the range, as if you were copying the formula down

这篇关于(Excel) 基于相邻单元格值的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！