如果单元格在彼此的范围内，则会进行条件格式化 [英] Conditional formatting if cells are within a range of each other

问题描述

使用Excel 2013。我在列A中有一长串数字。我想有条件地突出显示任何相对于它上面的数字+/- 5的单元格，或者低于它的数字。 / p>

例如，如果我有1，1，10，20，21，22，50（按此顺序），突出显示的单元格应该是仅包含1，1 ，20，21，22。

解决方案

请尝试：

  = OR（ABS（A1-A2）< 6，ABS（A2-A3）< 6）
  

允许的最大差异为+/- 5，这在公式中表达的数量要比+/- 6少一点，因此< 6 与 ABS 一起，因为 +/- ，即差异的迹象不相关有两种可能性（因此 OR ），在示例A2中需要与A1和A3进行比较。

条件格式的性质是它自动执行相关的适用于范围，并依次将公式应用于每个单元格。就像单元格中显示的公式一样，复制到一行将自动调整为 = OR（ABS（A2-A3）< 6，ABS（A3-A4）< 6），所以对于CF。

如果单元格中的公式是 = OR（ABS（$ A $ 1- $ A $ 2）< ; 6，ABS（$ A $ 2- $ A $ 3）< 6）在将结果复制到另一个单元格之前不会有所不同（因为引用已被修复 - $ S）。所以CF - 与 $ ，整个适用于范围将根据A1中的值进行格式化： A3。

与CF一样，在CF中可以使用与 $ 相同的选项，即使参考绝对（$）可以与亲属混合，在公式的不同部分和单个单元格引用（即独立的列和行）中。

Using Excel 2013. I have a long list of numbers in column A. I would like to conditionally highlight any cell that is in a range of +/- 5 with respect to the number above it OR the number below it.

For example, if I have 1, 1, 10, 20, 21, 22, 50 (in this order) the cells highlighted should be the ones containing only 1, 1, 20, 21, 22.

解决方案

Please try:

=OR(ABS(A1-A2)<6,ABS(A2-A3)<6)  

The maximum difference allowed is +/-5 which is a little simpler to express in a formula as not as much as +/-6, hence <6 in conjunction with ABS because of the +/-, ie the sign of the difference is not relevant. There are two possibilities (hence the OR), in the example A2 needs to be compared with both A1 and A3.

It is the nature of Conditional Formatting that it automatically steps through the relevant Applies to range, and applies the formula to each cell in turn. Just like the formula shown in a cell and copied down one row would automatically adjust to =OR(ABS(A2-A3)<6,ABS(A3-A4)<6), so for CF.

If the formula in the cell were =OR(ABS($A$1-$A$2)<6,ABS($A$2-$A$3)<6) before copying the result in another cell would not differ (because the references have been fixed – with $s). So with CF – with $ throughout then the entire Applies to range would be formatted according only to the values in A1:A3.

The same options for use of $ are available in CF as in a cell – ie making references absolute ($) can be mixed with relative, both in different parts of a formula and within a single cell reference (ie Column and Row independently).

这篇关于如果单元格在彼此的范围内，则会进行条件格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！