基于另一个单元格的值进行条件格式设置

Question

基于另一个单元格的值进行条件格式设置

google-sheetsgoogle-sheets-formulaconditional-formatting

540

我正在使用Google Sheets制作日常仪表板。我需要根据另一个单元格C5的值来更改单元格B5的背景颜色。如果C5大于80％，则背景颜色为绿色，但如果低于该值，则为琥珀/红色。

这是否可以通过Google Sheets函数实现，还是需要插入脚本？

- Mark Magalona

5

在您切换到新的 Sheets 前，请使用此答案中的一个脚本并使用 onEdit 触发器适应，或者使用此答案中的不需要触发器的脚本。 - Mogsdad

7个回答

216

再来一个例子：

如果你有从A到D的列，并且需要在B为“完成”时高亮整行（例如从A到D），则可以按照以下方式操作：

"Custom formula is":  =$B:$B="Completed" 
Background Color:     red 
Range:                A:D

当然，如果你有更多的列，可以将范围更改为A:T。

如果B包含“完成”，则使用以下搜索：

"Custom formula is":  =search("Completed",$B:$B) 
Background Color:     red 
Range:                A:D

- lucky1928

9

除非您在“自定义公式为”字段中提供“$”，否则似乎无法正常工作。例如，使用“$B$2：$B”，而不是“B2:B”。 - tdk

2

只是为了补充lucky1928的答案--如果你将范围从A:D改为A:T，你将添加更多的列，而不是行。如果您只想让条件格式适用于有限数量的行，则指定行可能会有所帮助，例如如果您有标题行，则为A2:D13。否则，如果您要匹配某些内容为空白或不等于特定值，则可能会向工作表中存在的所有行添加颜色，这可能不是您想要的。 - Tom Bush

5

请注意公式开头的"="符号非常重要。 - vy32

20

我最近在某个文件中使用了有趣的条件格式，认为它对其他人也很有用。因此，这个答案是为了完善先前的回答而存在。

它应该展示了这个惊人功能的能力，特别是$符号的工作方式。

示例表格

D到G列的颜色取决于A、B和C列中的值。但是公式需要检查水平固定的值（用户，开始，结束）和垂直固定的值（第1行的日期）。这就是$符号变得有用的地方。

解决方案

表中有2个用户，每个用户都有一个定义的颜色，分别是foo（蓝色）和bar（黄色）。
我们必须使用以下条件格式规则，并将它们都应用于相同的范围（D2:G3）：

=AND($A2="foo", D$1>=$B2, D$1<=$C2)
=AND($A2="bar", D$1>=$B2, D$1<=$C2)

用英语来表达，条件意味着：
用户为name，当前单元格的日期在start和end之间。

请注意，这两个公式之间唯一变化的是用户的名称。这使得它非常容易与许多其他用户重复使用！

解释

重要提示：变量行和列相对于范围的开始是相对的。但固定值不受影响。

相对位置很容易混淆。在这个例子中，如果我们使用区域D1:G3而不是D2:G3，颜色格式将会向上移动一行。
为避免这种情况，请记住变量行和列的值应与包含区域的起始位置对应。

在本例中，包含颜色的区域是D2:G3，因此起始位置是D2。 User、start和end随行数变化
-> 固定列A B C，变量行从2开始：$A2、$B2、$C2 Dates随列变化
-> 变量列从D开始，固定行1：D$1

- Romain Vincent

这可能需要一个单独的帖子，但是是否有可能将条件格式本身基于另一个单元格？例如，如果您有一个用于查找表中的“foo”和“bar”的单独查找表，可以配置条件格式以应用该单元格中的fg-color和bg-color规则吗？这是我经常需要的东西，一行的“状态”指示器，其中颜色和状态选项可以轻松配置，而无需手动重新编程条件格式规则。 - Memetican

你可以尝试使用脚本来实现。我想我从未见过通过正常使用动态选择特定颜色或现有格式的方法。编辑：要开始编写脚本，请转到工具>脚本编辑器并开始编码。但这是在使用表格时的一个全新层次的挑战。;-) - Romain Vincent

14

基本上，您只需要在列字母和行数前加上$前缀即可。请参见下面的图片。

- Meirza

5

根据另一个单元格C5的值，更改B5单元格的背景颜色。如果C5大于80％，则背景颜色为绿色，但如果小于80％，则为琥珀色/红色。假设B5不包含任何值，因此假定格式为无小数的百分比“80％”为“.8”，空白视为“低于”：

选择B5单元格，使用标准填充将其着色为“琥珀色/红色”，然后选择“格式”-“条件格式...”，自定义公式是：and:

=C5>0.8

使用绿色填充和 完成。

- pnuts

4

我对解决这个问题的时间感到失望。

我想看看范围内哪些值超出了标准差。

在某个单元格中添加标准差计算公式 =STDEV(L3:L32)*2
选择要突出显示的范围，右键单击，选择条件格式
选择 如果大于则设置单元格格式
在 数值或公式 框中输入 =$L$32（无论您的标准差在哪个单元格中）

我不知道如何将STDEV嵌入其中。我尝试了很多事情，但结果出乎意料。

- Nick.McDermaid

0

我只是想用另一种方式来解释它。在“自定义公式”条件格式中，您有两个重要字段：

自定义公式
应用于

假设您有一个简单的表格，其中包含学生的测试百分比，您希望对得分（列C）> 80％的学生ID（列B）进行着色：

行	B(学生ID)	C(分数)
1	48189	98%
2	9823	6%
3	17570	40%
4	60968	23%
5	69936	7%
6	8276	59%
7	15682	96%
8	95977	31%

要设计一个自定义公式，您只需要为您想要着色的范围的左上角设计一个公式，即B1。

这个公式应该返回：

TRUE，如果它应该被着色
FALSE，如果它不应该被着色

对于B1单元格，公式将会是：

=C1>80%

现在想象一下，你把这个公式放在B1（或者使用另一个范围进行测试）。就像这样：

行	B	C
1	TRUE
2	FALSE
3	FALSE
4	FALSE
5	FALSE
6	FALSE
7	FALSE
8	FALSE

现在想象一下，将公式（或自动填充）从B1拖到B8。它会是这个样子：

行	B	C
1	TRUE
2	FALSE
3	FALSE
4	FALSE
5	FALSE
6	FALSE
7	TRUE
8	FALSE

这直接转换为颜色B1和B7。有趣的是，所有这些都是使用给定的公式和“应用于”范围自动计算的。如果您填写：

自定义公式：=C1>80% 并且
应用于：B1:B8

你的意思是

在提供的范围 B1:B8 的左上角单元格中填写自定义公式 =C1>80%
即 B1，然后
拖动/自动填充公式到整个范围 B1:B8，并且
将公式输出为 TRUE 的单元格着色

如果你想同时对学生ID和分数进行着色，可以使用以下方法：

自定义公式：
```
=$C1>80%
```
应用于：
```
B1:C8
```

$C1 中的 $ 表示在自动填充范围时不更改 C。在虚拟表格中（建议您在某个地方输出表格），它看起来像这样：

行	B	C
1	TRUE	TRUE
2	FALSE	FALSE
3	FALSE	FALSE
4	FALSE	FALSE
5	FALSE	FALSE
6	FALSE	FALSE
7	TRUE	TRUE
8	FALSE	FALSE

通过这种方式，您可以根据任何其他单元格在任何地方着色任何单元格。

- TheMaster

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Zig Mandel · Accepted Answer

注意：在下面的说明中，当它说“B5”时，实际上是指“B {current_row}”，因此对于C5，它是B5，对于C6，它是B6等等。除非您指定$ B $ 5-然后您才引用一个特定的单元格。

自2015年以来，谷歌表格支持此功能：https://support.google.com/drive/answer/78413#formulas

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

使用“自定义公式为”选项，并将其设置为=B5>0.8*C5。
将“范围”选项设置为B5。
设置所需的颜色

您可以重复此过程以添加更多背景、文本或渐变色。

更好的方法是通过在“范围”中使用范围来使单个规则适用于所有行。以下是一个示例，假设第一行是标题：

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

这将类似于先前的示例，但适用于所有行，而不仅仅是第5行。

“范围”也可以在“自定义公式为”中使用，因此您可以基于其列值着色整个行。