如何在Excel中比较两个元素数量不等的数组

In an Excel array formula, I would like to test each element of one array against each element of a second array, when the 2 arrays do NOT have the same number of elements. Simplified right down, this scenario could be represented by:

在Excel数组公式中，我希望针对第二个数组的每个元素测试一个数组的每个元素，而这两个数组的元素数量并不相同。简化后，这个场景可以表示为:

=SUMPRODUCT({1,2,3,4,5}={1,2})

= SUMPRODUCT({ 1,2,3,4,5 } = { 1,2 })

NB - in my real world scenario these arrays are calculated from various prior steps.

NB——在我的真实场景中，这些数组是从前面的不同步骤计算出来的。

Using the above example, I would want a result of {TRUE,TRUE,FALSE,FALSE,FALSE}. What I get is {TRUE,TRUE,#N/A,#N/A,#N/A}.

使用上面的示例，我希望得到{TRUE、TRUE、FALSE、FALSE、FALSE}的结果。我得到的是{TRUE,TRUE，#N/A，#N/A，#N/A}。

It's clear that, when there's more than 1 value being tested for, Excel wants equal numbers of elements in the 2 arrays; when there isn't, the #N/A error fills in the blanks.

很明显，当测试超过一个值时，Excel希望两个数组中的元素数目相等;当没有错误时，#N/A错误将填充空格。

I've considered writing a UDF to achieve what I want, and I'm pretty sure my coding skills are up to creating something like:

我曾考虑过编写一个UDF来实现我想要的，我很确定我的编程技能是用来创建以下内容的:

=ArrayCompare({1,2,3,4,5},"=",{1,2})

= ArrayCompare({ 1,2,3,4,5 },“=”,{ 1,2 })

But I'd much rather do this using native functionality if it's not too cumbersome...

但如果不是太麻烦的话，我宁愿使用本机功能。

So, simple question; can an array formula be constructed to do what I'm after?

所以,简单的问题;可以构造一个数组公式来做我要做的事情吗?

Thanks peeps!

谢谢人!

3 个解决方案

#1

If the second array is a subset of the first array, same order, and starting at position 1 then you can use this array formula for equivalence testing:

如果第二个数组是第一个数组的子集，顺序相同，从位置1开始，那么您可以使用这个数组公式进行等价测试:

=IFERROR(IF({1,2,3,4,5}={1,2},TRUE),FALSE)

For non equivalence just swap the FALSE and TRUE

对于非等价性，只需交换假和真

=IFERROR(IF({1,2,3,4,5}={1,2},FALSE),TRUE)

You can then use this in other formulas just as an array:

你可以在其他公式中使用它作为一个数组:

However if the arrays are not in order, as in this example:

但是，如果数组不按顺序排列，如本例所示:

{1,2,3,4,5},{1,4,5}

Then you have to use MATCH. However all you need is to surround the match with an ISNUMBER like so:

然后你必须使用MATCH。但是，您所需要的只是使用ISNUMBER来包围匹配:

Equivalence test:

等效性测试:

=ISNUMBER(MATCH({1,2,3,4,5},{1,4,5},0))

Non Equivalence test:

非等价测试:

=NOT(ISNUMBER(MATCH({1,2,3,4,5},{1,4,5},0)))

Remember all array formulas are entered with ctrl + shift + enter

记住，所有的数组公式都是按ctrl + shift + enter输入的

#2

Using MATCH function is probably the best way.....but if you actually want to compare every element in one array with another array in a direct comparison then one should be a "column" and one a "row", e.g.

使用匹配功能可能是最好的方式……但是，如果您想要将一个数组中的每个元素与另一个数组进行直接比较，那么一个元素应该是“列”，一个元素应该是“行”，例如。

=SUMPRODUCT(({1,2,3,4,5}={1;4})+0)

= SUMPRODUCT(({ 1,2,3,4,5 } = { 1;4 })+ 0)

Note the semi-colon separator in the second array

注意第二个数组中的分号分隔符

If you can't actually change the column/row designation then TRANSPOSE can be used, i.e.

如果你不能改变列/行指定，那么就可以使用转置，也就是。

=SUMPRODUCT(({1,2,3,4,5}=TRANSPOSE({1,4}))+0)

= SUMPRODUCT(({ 1,2,3,4,5 } = '({ 1 4 }))+ 0)

You may not get the required results if the arrays contain duplicates because then you will get some double-counting, e.g. with this formula

如果数组包含重复项，您可能不会得到所需的结果，因为这样您将得到一些重复计数，例如使用这个公式

=SUMPRODUCT(({1,1,1,1,1}={1;1})+0)

= SUMPRODUCT(({ 1,1,1,1,1 } = { 1;1 })+ 0)

the result is 10 because there are 5x2 comparisons and they are all TRUE

结果是10，因为有5x2比较，它们都是正确的

#3

Maybe:

可能:

{=IF(ISERROR(MATCH({1,2,3,4,5},{1,2},0)),FALSE,TRUE)}