如果其内容存在于单独的单元格区域中，如何有条件地格式化一个单元格(在Google表格中)? [英] How to conditionally format a cell (in Google Sheets) if its content is present in a separate range of cells?

问题描述

我在Google表格的标签上的范围内有一个数字代码列表.这些值是非连续的，未排序的.

I have a list of numeric codes in a range on a tab of a Google sheet. These values are non-consecutive, unsorted.

在单独的选项卡上，如果其内容与上述范围内的任何单个单元格匹配，我想对单元格进行条件格式化.

On a separate tab, I would like to conditional format a cell if its content matches any of the individual cells in the aforementioned range.

在如果...设置单元格格式"下拉列表中，我能做的最好是选择文本正好"并键入一个数字代码.目前，我对每个数字代码都有规定.我想用一个规则来代替单个规则的集合.

The best I can do in the "Format cells if..." dropdown is selecting "Text is exactly" and typing a single numeric code. Currently, I have a rule for each of the numeric codes. I would like to replace this collection of single rules with one rule.

推荐答案

=FILTER(B1, COUNTIF(INDIRECT("A$1:A"), B1))

这是一个相反的例子，因此要使其适应您的目的，您需要删除 NOT() 并使用 INDIRECT() (如果您要引用其他工作表/标签(并且使用文本包含"或文本准确")):

here is an example of the opposite, so to adapt it for your purpose you need to drop NOT() and use INDIRECT() if you want to reference another sheet/tab (and you either use "text contains" or "text is exactly"):

• 应用于范围 A1:A 自定义fx: = FILTER(A1，NOT(COUNTIF(B1:E，A1)))

• apply to range A1:A custom fx: =FILTER(A1, NOT(COUNTIF(B1:E, A1)))

• 应用于范围 B1:B 自定义fx: = FILTER(B1，NOT(COUNTIF(A $ 1:A，B1)))
• 应用于范围 C1:C 自定义fx: = FILTER(C1，NOT(COUNTIF(A $ 1:A，C1)))
• 应用于范围 D1:D 自定义fx: = FILTER(D1，NOT(COUNTIF(A $ 1:A，D1)))
• 等

• apply to range B1:B custom fx: =FILTER(B1, NOT(COUNTIF(A$1:A, B1)))
• apply to range C1:C custom fx: =FILTER(C1, NOT(COUNTIF(A$1:A, C1)))
• apply to range D1:D custom fx: =FILTER(D1, NOT(COUNTIF(A$1:A, D1)))
• etc.

这篇关于如果其内容存在于单独的单元格区域中，如何有条件地格式化一个单元格(在Google表格中)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！