比较两个阵列-水平与垂直

当您比较两个数组(一个垂直和一个水平)时，excel实际上会扩展"最终数组.考虑以下内容(1行x 3col和2行x 1col):执行=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将返回尝试将元素与"nothing"进行比较以提供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 caseI'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:A3AppleLemonPearMy search array is NOT in the worksheet, but written {"Apple","Pear"}ProblemSo 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/AAgain {=SUMPRODUCT(--({"Apple","Pear"}=TRANSPOSE({"Apple","Lemon","Pear"})))} gave a correct answer of 2.QuestionCan 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 horizontalWhy 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. 解决方案 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.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}: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. 这篇关于比较两个阵列-水平与垂直的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！