条件格式与部分匹配 [英] Conditional Formatting with partial match

问题描述

https://drive.google.com/file/d/ 0Bx4AlUFuxYrQaHZYUklFSENLRlk / view？usp = sharing

我有一个使用INDEX和AGGREGATE公式的电子表格来填充搜索的订单项的结果。我想为结果添加条件格式，以便更容易找到。

I have a spreadsheet that uses an INDEX and AGGREGATE formula to populate results from a searched line item. I would like to add Conditional Formatting to the results to make it easier to find.

例如，如果我键入 tax 而不是滑行，具有部分匹配的结果将填充为模块号，第1课，第2课等。现在我想要列B中的项目强调部分匹配税。

For example, if I type tax instead of taxiing, results that have a partial match will populate as module numbers, Lesson 1, Lesson 2, etc. Now I would like the items in Column B to be highlighted that partially match tax.

我该怎么做？

推荐答案

使用税，说D1，请选择ColumnB和HOME >样式 - 条件格式化，新规则...，使用公式确定要格式化的单元格和格式化此公式为真的值：：

With tax in, say, D1 please select ColumnB and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=SEARCH(D$1,B1)

格式化，选择格式设置，OK，OK。

Format..., select choice of formatting, OK, OK.

SEARCH 在B1中的字符串中查找D1中的字符串。如果发现它返回一个数字，如果不是错误（CF将忽略）。它不区分大小写。 CF认为一个数字结果是一个命中来触发选择的格式。通过选择整个列，适用范围从B1（首先应用上述公式）下降到列的末尾，CF自动跨越 B 单元格（因为行号不被锚定），而不是 D1 ，因为引用被锚定（使用 $ ）。

SEARCH looks for the string in D1 within the string in B1. If found it returns a number, if not an error (which CF will ignore). It is not case sensitive. CF considers a numeric result to be a 'hit' to trigger the chosen formatting. By selecting the entire column the Applies to range is set from B1 (where the above formula is first applied) down to the end of the column, with CF automatically stepping through the B cells (because the row number is not anchored) but not D1 because that reference is anchored (with the $).

这篇关于条件格式与部分匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！