我有一列数值,经常出现重复。我需要创建一个新的列,其中包含基于第一列的唯一值,如下所示:
Column A Column B
a a
a b
b c
c
c
这个B列需要出现在同一工作簿的另一个工作表上,因此我认为它需要使用sheet2!A1
格式。
我尝试使用数据/筛选菜单选项,但没有成功,因为这只适用于命令。每当将新值输入A列时,我需要B列自动更新。
我有一列数值,经常出现重复。我需要创建一个新的列,其中包含基于第一列的唯一值,如下所示:
Column A Column B
a a
a b
b c
c
c
这个B列需要出现在同一工作簿的另一个工作表上,因此我认为它需要使用sheet2!A1
格式。
我尝试使用数据/筛选菜单选项,但没有成功,因为这只适用于命令。每当将新值输入A列时,我需要B列自动更新。
Totero的回答是正确的。链接也非常有帮助。
基本上你需要的公式是:
B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
然后按下ctrl+shift+enter(否则它将无法使用数组公式)。
这里需要记住两个重要的事情:完整列表在单元格A2:A20
中,而这个公式必须粘贴在单元格B2
中(不是B1
,因为那样会导致循环引用)。其次,这是一个数组公式,所以您需要按下ctrl+shift+enter,否则它将无法正确地工作。
这里有一份很好的指南,介绍如何从一个列中提取唯一列表和重复项的方法(链接).
基本上类似于:
=INDEX(Sheet1!$A$1:$A$20, MATCH(0, COUNTIF($B$1:B1,Sheet!$A$1:$A$20), 0))
Select all the rows, including the column headers, in the list
您想要筛选。
单击范围的左上角单元格,然后拖动到右下角单元格。
On the Data menu, point to Filter, and then click Advanced Filter.
In the Advanced Filter dialog box, click Filter the list, in place.
Select the Unique records only check box, and then click OK.
显示筛选列表并隐藏重复行。
On the Edit menu, click Office Clipboard.
显示“剪贴板”任务窗格。
Make sure the filtered list is still selected, and then click Copy Copy button.
经过筛选的列表会使用边框进行标记,并且所选项将出现在剪贴板顶部作为一个 > > 项目。
On the Data menu, point to Filter, and then click Show All.
重新显示原始列表。
Press the DELETE key.
原始列表已删除。
In the Clipboard, click on the filtered list item.
答案: 筛选后的列表会出现在与原始列表相同的位置。
B2=A2
B3=IFERROR(INDEX(A:A,MATCH(B2,A:A,1)+1),"")
B3可以向下粘贴。如果最后一个唯一匹配结果为0,这是不希望的,请在周围放置一些IF语句来排除这种情况。
编辑:
对于文本值,比IF语句更容易:
B3=IFERROR(T(INDEX(A:A,MATCH(B2,A:A,1)+1)),"")
从列中删除重复项
While column B is still selected, in the formula input box, enter
=IF(TRIM(A1)=TRIM(A2),"",TRIM(A1))
While Column B is still selected, select Edit -> Fill -> Down (in newer versions, simply select cell B1 and pull down the outer box to expand all the way down in the column)
注意: 如果B列在另一个工作表中,您可以使用Sheet1!A1和Sheet1!A2。
=SORT(UNIQUE(A:A))
如果您想在一列中列出唯一值,那么上述公式最为适用。
UNIQUE()
仅适用于 Office 365,而不适用于旧版的 Excel。 - Ross PattersonPrivate Sub Worksheet_Change(ByVal Target As Range)
Dim rngDest As Range
If Not Intersect(Target, Me.Columns(1)) Is Nothing Then
Set rngDest = ThisWorkbook.Sheets("Sheet2").Range("A1")
Me.Range(Me.Range("A2"), Me.Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=rngDest, Unique:=True
End If
End Sub
我有一个颜色名称列表,范围为A2:A8,在B列中,我想提取一个不同的颜色名称列表。
按照以下步骤操作:
=IF(COUNTIF(A$2:A2,A2)=1,A2,””)
因此,对于这个任务,首先按照从A到Z或从Z到A的顺序对数据进行排序,然后您可以使用下面所述的一个简单公式:
=IF(A2=A3, "Duplicate", "Not Duplicate")
上述公式说明,如果A2列数据(A是列,2是行号)与A3(A是列,3是行号)相似,则打印“重复”,否则打印“不重复”。
让我们举个例子,A列包含电子邮件地址,其中一些是重复的,因此在第2列中,我使用了上述公式,结果显示了两个重复单元格,一个是第2行,另一个是第6行。
一旦您获得了重复数据,只需在工作表上放置筛选器,并仅使重复数据可见并删除所有不必要的数据即可。
在上述公式中查找带有错误控制的此处提到
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")
其中:(B2:B9 是您想提取唯一值的列数据,D1 是您的公式所在单元格上方的单元格)
IFERROR(...,"")
消除#N/A
,通过正确指定范围,即在示例中使用$A$2:$A$20
来消除0
,如果需要动态,则使用OFFSET
。 - Dan=A1
,B2单元格中输入=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))
,B3单元格中输入=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B2, $A$2:$A$20), 0))
,以此类推。请注意,唯一变化的是:B1,这就是为什么它被标记为相对引用。 - shlgug