Excel 条件格式 - 包含多个匹配项
尝试避免 VBA 并使用一种条件格式规则。
我想对包含一个或多个可能短语的单元格应用一种格式。
| A |
---+--------+---
1 | foo |
2 | bar |
3 | foobar |
4 | baz |
5 | foobaz |
6 | qux |
7 | barqux |
目标：将条件格式应用于“foo”和“baz”（第 1、3、4、5 行）
类似以下内容应该有效，但我认为问题在于识别要比较的当前单元格，在本例中为 A1:A7 需要是当前单元格引用：
=OR( IFERROR(FIND("foo",A1:A7),0) > 0, IFERROR(FIND("baz",A1:A7),0) > 0 )
有什么想法吗？
Trying to avoid VBA and use one Conditional Formatting rule.
I want to apply a format to cells that contain one or more possible phrases.
| A |
---+--------+---
1 | foo |
2 | bar |
3 | foobar |
4 | baz |
5 | foobaz |
6 | qux |
7 | barqux |
Goal: apply conditional format to 'foo' and 'baz' (rows 1,3,4,5)
Something like the following should work, however I think the problem is in identifying the current cell to compare, in this case the A1:A7 needs to be the current cell reference:
=OR( IFERROR(FIND("foo",A1:A7),0) > 0, IFERROR(FIND("baz",A1:A7),0) > 0 )
Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我找到了答案。
即使条件格式应用于 $A$1:$A$7 范围，您所需要做的就是引用函数中的第一个单元格。
只要您不使用锁定运算符 (
$)，它就会自动递增。 - 抱歉，如果这是常识。如果没有人遇到问题，我会将其保留在这里用于教育目的。I found the answer.
Even though the conditional format was being applied to the range $A$1:$A$7, all you need to do is refer to the first cell in the function.
It'll automatically increment so long as you don't use the lock operator (
$). - Sorry if this is common knowledge. I'm going to keep this here for educational purpose, if no one has a problem.你是完全正确的。您必须输入对“选定”单元格的引用。例如，如果您单击单元格 A1，然后拖动并选择单元格 A1..A7，您将看到 A1 具有与其他单元格不同的背景。这是条件公式的当前单元格。 在这种情况下即
是正确的。对于其他单元格，公式将相应调整。
You're perfectly right. You have to enter the reference to the "selected" cell. E.g. if you click into cell A1 and then drag and select cells A1..A7 you will see that A1 has different background than the other cells. This is your current cell for the conditional formula. I.e.
would be correct in this case. For the other cells the formula will then be adjusted accordingly.