excel - 检查具有多个数字的单元格是否包含特定值

Question

I have a column A with the values 

可乐

A1 1:2:3:3
A2 2:3:4:5
A3 1:04:05

在 ColumnA 的 excel 中，我必须检查单元格 A1、A2、A3 中是否有任何单元格包含数字 5 或 05。如果为 true，则必须返回 true。如何在excel中获得上述条件的公式？我尝试使用 Search("5", A1) 但它不起作用？

可以使用什么公式请指导。

Answer 1

如果您使用 Excel 2013 或更高版本，您可以使用以下公式:

=SUMPRODUCT(--(FILTERXML("<b><a>["&SUBSTITUTE(A1,":","]</a><a>[")&"]</a></b>","//a")={"[5]","[05]"}))>0

向下拖动以应用。

The logic is to use SUBSTITUTE function to convert each string into a valid xml script, such as "<b><a>[1]</a><a>[2]</a><a>[3]</a><a>[3]</a></b>" for 1:2:3:3,

then use FILTERXML function to convert the string into a range of values separated by the : colon sign, such as {"[1]";"[2]";"[3]";"[3]"}. I have added special symbols [ and ] for each value within the range for exact match purpose,

lastly compare the the range with {"[5]","[05]"} and use SUMPRODUCT to find out if there is at least one match or TRUE from the comparison. If >0 i.e. at least one match is found, returns TRUE, otherwise FALSE.

编辑#2
如果您无权访问 过滤器XML 函数，你也可以试试下面的公式:

=SUMPRODUCT(IFERROR(FIND({"[5]","[05]"},"["&SUBSTITUTE(A1,":","][")&"]"),0))>0

The logic is similar, firstly use SUBSTITUTE function to convert the string 1:2:3:3 into "[1][2][3][3]", then use FIND function to find out if the string contains any of the following {"[5]","[05]"}, and lastly use SUMPRODUCT function to return the result, if >0 then TRUE else FALSE.

如果您有任何问题，请告诉我。这是我测试过的字符串:

| Strings            | Result |
|--------------------|--------|
| 1:2:3:3            | FALSE  |
| 2:3:4:5            | TRUE   |
| 1:04:05            | TRUE   |
| 005:50:15:25:55    | FALSE  |
| 505:550:500:05a:O5 | FALSE  |