vba Excel - 在列中查找不等于值数组的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25256210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - Find Strings in Column That Do Not Equal Array of Values
提问by Ken Prince
I have a column of data. I want to find all the values that do not equal any values from an array, one by one.
我有一列数据。我想从数组中一一找出不等于任何值的所有值。
I tried defining a named value which was an array like this
我尝试定义一个命名值，它是这样的数组
={"value1","value2","value3","value4","value5"}
={"value1","value2","value3","value4","value5"}
..and so on.
..等等。
Which excel functions can I use to find values that are not contained in the array.
我可以使用哪些 excel 函数来查找数组中未包含的值。
And why is the function not iterating through the array? It's only testing the first index.
为什么函数不遍历数组？它只测试第一个索引。
I tried this:
{=NOT(X8=NamedValue)}
我试过这个：
{=NOT(X8=NamedValue)}
And =MATCH(NamedValue, X3,0)
和 =MATCH(NamedValue, X3,0)
Both only test the first value in the array.
两者都只测试数组中的第一个值。
回答by dotNET
You should use Excel's MATCH()function for this purpose. Supply 0to the match_typeparameter. More information can be found on this link.
MATCH()为此，您应该使用 Excel 的函数。提供0给match_type参数。可以在此链接上找到更多信息。
Examples of usage:
用法示例：
Pass hard-coded list of values to the formula:
将硬编码的值列表传递给公式：
=MATCH("dfs", {"erw","sad","dfs"}, 0)
Pass cell range to the formula:
将单元格范围传递给公式：
=MATCH("dfs", C1:C15, 0)
回答by parade
Assume column A is your list of "good values", column B is list of all values.
假设列 A 是您的“好值”列表，列 B 是所有值的列表。
A B value2 value1 value3 value2 value4 value3 value4 value5 Place the following formula in C1, and copy down
AB 值2 值1 值3 值2 值4 值3 值4 值5 将下面的公式放在C1 中，并复制下来
=IF(ISNA(VLOOKUP(B1,$A:$B,2,FALSE)),0,1)
Which will place a "1" in the the rows where there is a match, and false otherwise.
这将在匹配的行中放置一个“1”，否则为 false。
