在 Excel 中忽略重复项并创建新的唯一值列表 [英] Ignore Duplicates and Create New List of Unique Values in Excel

查看:34
本文介绍了在 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 中忽略重复项并创建新的唯一值列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆