如何使用Excel公式过滤列中的唯一值 [英] How to filter unique values in a column using excel formula

问题描述

我需要针对我的excel工作簿的解决方案.我在sheet1中有一列具有重复值，并且想要在sheet2中显示该列的唯一值. 可能看起来很简单，但是只要sheet1中的值发生变化，这些值就应该反映在sheet2中. 我需要一个Excel公式，但不需要VBA.

I'm in need of a solution for my excel workbook. I have a column with duplicate values in sheet1 and want to display the unique values of that column in sheet2. May be it looks simple but whenever there is a change in values in sheet1 those values should reflect in sheet2. I need an excel formula but not the VBA.

我尝试了几种不同的选择，例如数组和填充物，但是无法获得正确的解决方案.感谢您的及时帮助.非常感谢.

I tried few different options like array and stuff but could not able to get the proper solution. Appreciate your timely help. Many Thanks.

推荐答案

说 Sheet1 表来自 A1 至 A100

在 Sheet2 单元格 A1 中输入:

=Sheet1!A1

在 Sheet2 单元格 A2 中输入数组公式:

In Sheet2 cell A2 enter the array formula:

=IFERROR(INDEX(Sheet1!$A$1:$A$100,INT(SMALL(IF(COUNTIF(A$1:A1,Sheet1!$A$1:$A$100)=0,ROW(Sheet1!$A$1:$A$100)+(COLUMN(Sheet1!$A$1:$A$100)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(A$1:A1,Sheet1!$A$1:$A$100)=0,ROW(Sheet1!$A$1:$A$100)+(COLUMN(Sheet1!$A$1:$A$100)*0.01)),1),1)),"")

然后抄下来.

数组公式必须使用 Ctrl + Shift + Enter 输入，而不仅仅是 Enter 键.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

调整$ A $ 1:$ A $ 100以匹配 Sheet1 中的确切大小.

Adjust the $A$1:$A$100 to match the exact size in Sheet1.

如果您可以在 Sheet1中应用"helper" 列，则可以大大简化公式.

If you can apply a "helper" column in Sheet1, the formula can be tremendously simplified.

这篇关于如何使用Excel公式过滤列中的唯一值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！