Google Spreadsheet Conditional Formatting Based on another Cell Value [英] Google Spreadsheet Conditional Formatting Based on another Cell Value

问题描述

我正在使用Google电子表格并在每日信息中心上工作。我需要的是根据另一个单元格的值 - C5来更改单元格B5的背景颜色。如果C5大于80％，则背景颜色为绿色，但如果低于此值，则为琥珀色/红色。

这是否可以在Google电子表格函数中使用，或者是否需要插入脚本？非常感谢

解决方案

注意：当它在下面的解释中显示B5意思是B {current_row}，所以对于C5它是B5，对于C6是B6等等。除非您指定$ B $ 5 - 那么您可以引用一个特定的单元格。

---

截至2015年，Google表格支持此功能：
https://support.google.com/drive/answer/78413#公式

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

• 使用自定义公式选项并将其设置为 = B5> 0.8 * C5 。


• 将 Range 选项设置为 B5 。
  
• 设置所需颜色





您可以重复此过程为背景或文字或色阶。





更好的做法是，使用范围中的范围将单个规则应用于所有行。示例假设第一行是一个标题：







• 在B2条件格式设置上，将自定义公式< > code> B2：B 。


• 设置所需颜色
  




将像前面的示例一样，但适用于所有行，而不仅仅适用于第5行。


范围也可用于自定义公式是中，因此您可以对整行进行着色根据他们的列值。




I'm using Google spreadsheet and working on 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.

Is this available in google spreadsheet functions or do I need to insert a script. Thanks a lot

解决方案

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.

---

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

In your case, you will need to set conditional formatting on 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:


• 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

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.

这篇关于Google Spreadsheet Conditional Formatting Based on another Cell Value的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！