在 Excel 中忽略重复项并创建新的唯一值列表

问题描述：

我有一列经常出现重复的值.我需要根据第一列创建一个具有唯一值的新列，如下所示:

I have a column of values that often appear as duplicates. I need to create a new column, of unique values based on the first column, as follows:

Column A   Column B  
a          a
a          b
b          c
c
c

此 B 列实际上需要出现在同一工作簿中的不同工作表上，因此我认为它需要使用 sheet2!A1 样式格式.

This Column B will actually need to appear on a different sheet, within the same workbook, so I assume it will need to work with the sheet2!A1 style format.

我对数据/过滤器菜单选项没有任何运气，因为这似乎只适用于命令.每当在 A 列中输入新值时，我都需要 B 列自动更新.

I have not had any luck with the Data/Filter menu options as this only seems to work on command. I need column B to update automatically whenever a new value is entered into column A.

答

Totero 的回答是正确的.该链接也很有帮助.

Totero's answer is correct. The link is also very helpful.

基本上你需要的公式是:

Basically the formula you need is:

B2=INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

然后按 ctrl+shift+enter(否则使用数组公式将不起作用).

Then press ctrl+shift+enter (or it will not work using a array formula).

这里要记住两件重要的事情:完整列表在单元格 A2:A20 中，然后这个公式必须粘贴在单元格 B2 中(不是 B1 因为这会给你循环引用).其次这是一个数组公式，所以你需要按 ctrl+shift+enter 否则它不会正常工作.

Two important things to keep in mind here: The complete list is in cells A2:A20, then this formula has to be pasted in cell B2 (Not B1 as that will give you circular reference). Secondly this is an array formula, so you need to press ctrl+shift+enter or it will not work correctly.

在 Excel 中忽略重复项并创建新的唯一值列表

相关推荐