基于另一个单元格值的条件格式 [英] Conditional formatting based on another cell's value

问题描述

我使用 Google 表格作为每日信息中心.我需要的是根据另一个单元格 C5 的值更改单元格 B5 的背景颜色.如果 C5 大于 80%，则背景颜色为绿色，但如果低于 80%，则为琥珀色/红色.

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green but if it's below, it will be amber/red.

这是否适用于 Google 表格功能，还是我需要插入脚本?

Is this available with a Google Sheets function or do I need to insert a script?

推荐答案

注意: 当在下面的解释中说B5"时，它实际上是B{current_row}"，所以对于 C5它是 B5，对于 C6，它是 B6，依此类推.除非您指定 $B$5 - 那么您指的是一个特定的单元格.

Note: when it says "B5" in the explanation below, it actually means "B{current_row}", so for C5 it's B5, for C6 it's B6 and so on. Unless you specify $B$5 - then you refer to one specific cell.

自 2015 年起，Google 表格支持此功能:https://support.google.com/drive/answer/78413#formulas

This is supported in Google Sheets as of 2015: https://support.google.com/drive/answer/78413#formulas

在您的情况下，您需要在 B5 上设置条件格式.

In your case, you will need to set conditional formatting on B5.

• 使用自定义公式是"选项并将其设置为=B5>0.8*C5.
• 将范围"选项设置为B5.
• 设置所需的颜色

• Use the "Custom formula is" option and set it to =B5>0.8*C5.
• set the "Range" option to B5.
• set the desired color

您可以重复此过程为背景或文本或色阶添加更多颜色.

You can repeat this process to add more colors for the background or text or a color scale.

更好的是，通过使用Range"中的范围将单个规则应用于所有行.假设第一行是标题的示例:

Even better, make a single rule apply to all rows by using ranges in "Range". Example assuming the first row is a header:

• 在 B2 条件格式中，将自定义公式为"设置为 =B2>0.8*C2.
• 将范围"选项设置为B2:B.
• 设置所需的颜色

• On B2 conditional formatting, set the "Custom formula is" to =B2>0.8*C2.
• set the "Range" option to B2:B.
• set the desired color

与前面的示例类似，但适用于所有行，而不仅仅是第 5 行.

也可以在自定义公式是"中使用范围，以便您可以根据列值为整行着色.

Will be like the previous example but works on all rows, not just row 5.

Ranges can also be used in the "Custom formula is" so you can color an entire row based on their column values.

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