excel - 比较两个数组 - 水平与垂直

Question

情况如何

我正在尝试比较两个数组。为了简单起见，我们假设我们想知道一个数组的值在另一个数组中存在的频率。

我的引用/查找数组数据位于 A1:A3

Apple
Lemon
Pear

我的搜索数组不在工作表中，而是写成{"Apple","Pear"}

问题

因此，要了解搜索值在查找数组中存在的频率，我们可以应用如下公式:

{=SUMPRODUCT(--(range1=range2))}

但是，{=SUMPRODUCT(--({"Apple","Pear"}=A1:A3))} 会产生错误。换句话说，查找数组没有按预期工作。

有效的是使用 TRANSPOSE() 函数首先使用 {=SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE 从我的数据创建水平数组(A1:A3)))} 得出正确答案 2!

似乎我的类型化数组被自动处理为水平数组，而我的数据显然最初是垂直的。

为了检验我的假设，我尝试了另一个公式:

{=SUMPRODUCT(--({"Apple","Pear"}={"Apple","Lemon","Pear"}))}

两者都是类型化数组，因此根据上述逻辑，它们都是水平数组，无需使用 TRANSPOSE() 即可完美工作，但是这会返回错误! #N/A

再次 {=SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE({"Apple","Lemon","Pear"})))} 给出了正确的结果答案2。

问题

有人可以向我解释一下吗:

• 水平数组不能与垂直数组进行比较的原因。
• 为什么类型化数组会自动被处理为水平数组
• 为什么在我的假设测试中，第二个类型化数组被处理为垂直。

我真的很好奇，也很高兴能链接到适当的文档，因为到目前为止我还没有找到任何文档。

这可能是一个很容易回答的问题，尽管我似乎无法理解其中的逻辑。

Answer 1

Can someone please explain to me:

• The reasoning why horizontal can't be compared to vertical arrays.

这实际上是可能的，并且您还可以将水平数组与其他水平数组进行比较。

您收到错误的原因是数组长度不匹配。考虑以下数组:

正在做=SUMPRODUCT(--(B3:D3=F3:G3))与 =SUMPRODUCT(--({"Apple","Lemon","Pear"}={"Apple","Pear"})) 相同(在 Excel 的英文版本上，我不能 100% 确定其他版本上的分隔符)结果为=SUMPRODUCT(--(Apple=Apple, Lemon=Pear, Pear=???)) ，即将第一个数组的第 n 个元素与第二个数组的第 n 个元素进行比较，如果没有任何匹配项——第一个数组中的第 3 个元素是 Pear，但第二个数组中没有第 3 个元素—— - 然后你得到N/A .

当您比较两个数组(一个垂直数组和一个水平数组)时，Excel 实际上“扩展”了最终数组。考虑以下情况(1 行 x 3 列和 2 行 x 1 列):

正在做=SUMPRODUCT(--(B3:D3=F3:F4))与 =SUMPRODUCT(--({"Apple","Lemon","Pear"}={"Apple";"Pear"})) 相同结果为=SUMPRODUCT(--(Apple=Apple, Lemon=Apple, Pear=Apple; Apple=Pear, Lemon=Pear, Pear=Pear)) 。基本上感觉就像 Excel 扩展了两个数组，如下所示 (3col x 2row):

我相信，这种“扩展”仅在一个数组为 1 行高而另一个数组为 1 列宽时才会发生，因此，如果您采用具有不同内容的数组，那么 Excel 将返回尝试将元素与“无”进行比较' 给出 N/A(您可以使用“公式”选项卡下的“评估公式”功能来提供帮助):

所以本质上 Excel 得到的东西与此有点类似，其中第一个数组与第二个数组相乘，给出结果数组:

但是由于最后一行和最后一列包含空白，因此您会得到 N/A 。

• Why a typed array would automatically be handled as horizontal

在你的问题中，似乎 ,分隔行，因此使用 =SUMPRODUCT(--({"Apple","Pear"}=A1:A3))您正在观察类似于我的第一个示例中两行的比较，而使用 =SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE(A1:A3))) ，您正在发生“扩展”。

正如评论中所说，在英文版的excel上，,分隔列和 ;分隔行，正如在这个简单示例中可以观察到的，其中我提供了一个包含 2 行和 3 列的数组，excel 显示 {0,0,0;0,0,0} :

• Why in my test of the hypotheses the second typed array was handled as vertical.

TRANSPOSE只需将数组从垂直切换为水平(反之亦然)，但根据您想要执行的操作，您将根据我的答案的第一部分得到不同的结果，因此您将获得 N/A当 Excel 无法将一个数组的一项与另一个数组的另一项匹配时，或者两个数组的“扩展”导致更大的数组时。