基于另一个单元格的值的条件格式 [英] Conditional formatting based on another cell's value
问题描述
我将Google表格用作日常仪表板.我需要基于另一个单元格C5的值更改单元格B5的背景颜色.如果C5大于80％，则背景颜色为绿色，但如果低于C5，则为琥珀色/红色.
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.
更好的是，使用"范围"中的范围，使一条规则适用于所有行.假设第一行是标题的示例:
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屋！
