Excel条件格式化多行 [英] Excel Conditional Formatting over multiple rows

问题描述

我正在尝试突出超出预期值范围（仅较高或更低）的单元格。每行对应一个不同的行，它具有两个具有最大值和最小值的单元格。有没有办法让Excel弄出来？

例如，第7行的单元格只能在B31或最大B32的最小值之外突出显示。第18行应由第42行管理。

我已经使用条件格式化。但是我想对所有单元格和行应用相同的逻辑，而不是一个接一个地执行它们。

解决方案

Scott Craner的建议是现在。可以通过以下方式在条件格式中使用公式以下说明：

1. 选择单元格B4（钠基准测试结果）


2. 单击条件格式在功能区中，突出显示单元规则，然后小于


3. 在弹出的对话框中输入以下公式：

<






= VLOOKUP（A4，$ A $ 27：$ C $ 48,2，FALSE）





对于大于规则重复上述操作，但是修改查询公式如下，以便查找MAX（第3列）：

= VLOOKUP（A4，$ A $ 27：$ C $ 48,3，FALSE）

如果这个答案有用或有帮助，请标记或指示，谢谢！

I am trying to highlight cells that fall outside the expected value range (higher or lower only). Each row corresponds to a different row that has two cells with a max and min. Is there a way to make Excel figure that out?

For example Row 7's cells should only highlight if they value is outside the min of B31 or max B32. And Row 18 should be governed by Row 42.

I have used conditional formatting. But I want to apply the same logic to all the cells and rows instead of doing them one by one.

解决方案

Scott Craner's suggestion is spot on. You can use a formula inside of your conditional formatting by following the instructions below:

1. Select Cell B4 (Sodium baseline test results)
2. Click "Conditional Formatting" in the Ribbon, "Highlight Cells Rules", and then "Less Than"
3. In the dialog that pops up, enter the following formula:

=VLOOKUP(A4,$A$27:$C$48,2,FALSE)

4. Click OK to save and apply the conditional format.
5. Click the B4 Cell again, and select the bottom right corner (you should see a little box sort of that you can click and drag).

6. RIGHT CLICK AND DRAG (not left click/drag) that bottom corner so that all the cells get selected. Then when the dialog box pops up, select "Fill Formatting Only". This will apply the formula to all the cells, and the lookup conditions automatically update based on the test name in the same row.

7. Repeat the above for the Greater Than rules, but modify the lookup formula as follows so that it looks up the MAX (3rd) Column:

=VLOOKUP(A4,$A$27:$C$48,3,FALSE)

If this answer was useful or helpful, please mark or indicate as such, thank you!

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