Google表格完全排除重复项 [英] Google Sheets Completely exclude duplicates

问题描述

此公式:

=FILTER(SORT(UNIQUE(flatten(C3:H14));1;1); SORT(UNIQUE(flatten(C3:H14));1;1)<>"")

将C3:H14中的所有值合并到一个列中，然后删除重复项并按升序对结果进行排序.

Merges all values in C3:H14 into a single column then removes duplicates and sorts the result in ascending order.

可以将结果与第二个列表进行比较，然后仅保留永不重复的值?

It is possible to compare the result with a second list and then leave only the values that never repeat?

例如，第一射程

   A      B       
Orange  Grapes  
Apple   Carrot  

平射范围(A + B)

Flattened Range (A + B)

   C
Apple
Carrot
Grapes
Orange

比较范围

   D  
Apple  
Banana  
Carrot
Grapes
Orange  
Peach

所以最终结果将是(D-C):

So the final result will be (D - C):

   E     
Banana
Peach          

尽管使用UNIQUE函数可以删除重复项，同时保留每个值的一个副本.

Although using UNIQUE function is possible to remove duplicates while keeping one copy of each value.

我们如何处理这种情况，只留下永不重复的值?另外，可以使用命名范围作为比较范围吗?感谢您的关注.

How can we approach this situation to leave only values that never repeat? Also, it would be possible to use a Named Range as comparison range? Thanks for the attention.

推荐答案

您提到的

所以最终结果将是(D-C)

So the final result will be (D - C)

再往下走

我们如何处理这种情况而只留下永不重复的值?

How can we approach this situation to leave only values that never repeat?

请使用以下公式

=FILTER(B2:B,ISERROR(MATCH(B2:B,A2:A,0)))

关于命名范围，请参见更多信息

As for the named range please see more here

编辑(复制 @Mateo的很棒的提供更多有关此功能如何工作的信息.

To provide a bit more of information on how this function works.

• 首先，它会在两列之间寻找完全匹配的匹配项，并返回这些匹配项的相对位置.
• 如果有任何不匹配的值，它们将作为错误#N/A 返回，并由函数 ISERROR 捕获.
最后，为了获得这些不匹配"，需要进行以下操作:您使用这些不匹配的值过滤最大范围列 B ，然后将其返回.

• First it looks for exact matches between your two columns returning the relative position of these matches.
• If there are any values that don't match, they will be returned as an error #N/A which are caught by the function ISERROR.
• Finally, to get these "unmatches" you filter your biggest range column B with these values that did not match and you return them.

使用的功能:

• 过滤器
• ISERROR
• MATCH

这篇关于Google表格完全排除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！