Excel COUNTIF单元格包含给定的文本(部分匹配) [英] Excel COUNTIF cell contains a given text (partial match)

问题描述

我有一个条件格式，像这样的单元格

I have a conditional formatting with cells like

=COUNTIF(A3:H2663;R5)

如果在其他位置找到输入到R5的值，则该框将变为红色.

If the value entered into R5 is found elsewhere, the box will then turn red.

但是，有时它不是完全匹配，然后它无法识别它.这可能是因为在输入的数字末尾有一个多余的数字.

However, sometimes it's not an exact match, and then it doesn't recognize it. That may be because of an extra figure at the end of the entered number.

所以我的问题是:如果A3:H2663中的单元格仅包含R5中的值，并且不是完全匹配，我可以更改公式以进行匹配吗?

So my question is: can I change the formula to make a match, if the cells from A3:H2663 simply contain the value in R5, and isn't an exact match?

推荐答案

使用COUNTIF()函数，您可以在条件中使用通配符.

With the COUNTIF() function, you can use wildcard characters in your criteria.

如果要查找任何一开始具有搜索/条件值的单元格值:

If you want to find any cell value that has the search/criteria value at the very start:

=COUNTIF(A3:H2663, R5 & "*")

如果要查找其中具有搜索/条件值的任何单元格值:

If you want to find any cell value that has the search/criteria value anywhere in it:

=COUNTIF(A3:H2663, "*" & R5 & "*")

*通配符表示零个或多个字符.

The * wildard character represents zero or more characters.

这篇关于Excel COUNTIF单元格包含给定的文本(部分匹配)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！