比较两个数组 - 水平与垂直 [英] Comparing two arrays - Horizontal vs Vertical

问题描述

这是什么情况

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

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

苹果柠檬梨

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

问题

所以要知道我们的搜索值在 lookuparray 中出现的频率，我们可以应用如下公式:

{=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的答案.

问题

谁能给我解释一下:

• 无法将水平阵列与垂直阵列进行比较的原因.
• 为什么类型化数组会被自动处理为水平数组
• 为什么在我对假设的测试中，第二个类型化的数组被处理为垂直的.

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

这可能很容易回答，但我似乎无法理解逻辑.

解决方案

谁能给我解释一下:

• 无法将水平阵列与垂直阵列进行比较的原因.

这其实是可以的，你也可以将水平数组与其他水平数组进行比较.

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

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

当您比较两个数组时，一个是垂直的，一个是水平的，excel 实际上是扩展"了最终的数组.考虑以下(1row x 3col 和 2row x 1col):

做 =SUMPRODUCT(--(B3:D3=F3:F4)) 和 =SUMPRODUCT(--({"Apple","Lemon","Pear"}={"Apple";"Pear"})) 并导致 =SUMPRODUCT(--(Apple=Apple, Lemon=Apple, Pear=Apple; Apple=Pear, Lemon=Pear,梨=梨)).基本上感觉 Excel 像这样扩展了两个数组(3col x 2row):

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

所以本质上 excel 得到了一些类似于此的东西，其中第一个数组与第二个数组相乘，得到结果数组:

但由于最后一行和最后一列涉及空格，因此您会在那里得到 N/A.

<块引用>

• 为什么类型化数组会被自动处理为水平数组

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

如评论中所述，在英文版的 excel 中，, 分隔列，; 分隔行，正如在我提供的这个简单示例中所观察到的2行3列数组，excel显示{0,0,0;0,0,0}:

<块引用>

• 为什么在我对假设的测试中，第二个类型化的数组被处理为垂直的.

TRANSPOSE 只是将数组从垂直切换到水平(反之亦然)，但根据您尝试执行的操作，根据我的答案的第一部分，您会得到不同的结果，因此，当 excel 无法将数组的一个项目与另一个数组的另一个项目匹配时，您将有 N/A，或者两个数组的扩展"导致更大的数组.>

What is the case

I'm trying to compare two arrays. For simplicity sake let's assume we want to know how often the values of one array exist in the other array.

My referenced/lookup array data sits in A1:A3

Apple
Lemon
Pear

My search array is NOT in the worksheet, but written {"Apple","Pear"}

Problem

So to know how often our search values exists in the lookuparray we can apply a formula like:

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

However, {=SUMPRODUCT(--({"Apple","Pear"}=A1:A3))} produces an error. In other words the lookup array wasn't working as expected.

What did work was using TRANSPOSE() function to create a horizontal array from my data first using {=SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE(A1:A3)))} resulting in the correct answer of 2!

It seems as though my typed array is automatically handled as an horizontal array, and my data obviously was originally vertical.

To test my hypotheses I tried another formula:

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

Both are typed arrays, so with above logic it would both be horizontal arrays, perfectly able to work without using TRANSPOSE(), however this returns an error! #N/A

Again {=SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE({"Apple","Lemon","Pear"})))} gave a correct answer of 2.

Question

Can someone please explain to me:

• The reasoning why horizontal can't be compared to vertical arrays.
• Why a typed array would automatically be handled as horizontal
• Why in my test of the hypotheses the second typed array was handled as vertical.

I'm really curious, and would also be happy to be linked to appropriate documentation as so far I have not been able to find any.

This might be an easy one to answer, though I can't seem to get my head around the logic.

解决方案

Can someone please explain to me:

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

This is actually possible, and you can also compare horizontal arrays with other horizontal arrays.

The reason you have been getting the error is because of the mismatch in the length of the array. Consider the following arrays:

Doing =SUMPRODUCT(--(B3:D3=F3:G3)) is the same (on excel's english version, I'm not 100% sure on the delimiters on other versions) as =SUMPRODUCT(--({"Apple","Lemon","Pear"}={"Apple","Pear"})) and results in =SUMPRODUCT(--(Apple=Apple, Lemon=Pear, Pear=???)), that is the nth element of the first array is compared to the nth element of the second array, and if there is nothing to match --the 3rd element in the 1st array is Pear but there is no 3rd element for the 2nd array-- then you get N/A.

When you compare two arrays, one vertical and one horizontal, excel actually 'expands' the final array. Consider the following (1row x 3col and 2row x 1col):

Doing =SUMPRODUCT(--(B3:D3=F3:F4)) is the same as =SUMPRODUCT(--({"Apple","Lemon","Pear"}={"Apple";"Pear"})) and results in =SUMPRODUCT(--(Apple=Apple, Lemon=Apple, Pear=Apple; Apple=Pear, Lemon=Pear, Pear=Pear)). Basically it feels like Excel expanded the two arrays like this (3col x 2row):

This 'expansion' only happens when one array is 1 row high and the other is 1 column wide I believe, so if you take arrays that have something different, then excel will go back to trying to compare an element with 'nothing' to give N/A (you can use the Evaluate Formula feature under Formula tab to help):

So essentially excel is getting something a bit similar to this, where the first array is multiplied to the second array, giving the result array:

But since the last row and last column involve blanks, you get N/A there.

• Why a typed array would automatically be handled as horizontal

In your question, it would seem that , delimit rows, so with =SUMPRODUCT(--({"Apple","Pear"}=A1:A3)) you are observing similar to the comparison of two rows in my first example, while with =SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE(A1:A3))), you are getting the 'expansion' occurring.

As stated in the comments, on the English version of excel, , delimits columns and ; delimits rows, as can be observed in this simple example where I supply an array with 2 rows and 3 columns, excel shows {0,0,0;0,0,0}:

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

TRANSPOSE simply switches an array from vertical to horizontal (and vice versa), but depending on what you are trying to do, you'll get different results as per the first part of my answer, so you'll either have N/A when excel cannot match an item of an array with another item of the other array, or 'expansion' of the two arrays that results in a bigger array.

这篇关于比较两个数组 - 水平与垂直的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！