谷歌表格完全排除重复项 [英] 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)))

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

编辑(复制@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

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