数据验证-在Excel中没有重复项的下拉列表 [英] Data validation - Drop down list with no duplicates in excel

问题描述

您能帮助我在下拉列表中删除重复项吗?该列表不是静态的.示例:

Could you please assist me with removing duplicates in a drop down list. The list is not static. Example:

之前

James
Peter
James
Nick
Peter

之后

James
Peter
Nick

我不确定要使用哪个公式来验证数据.

I am not sure what formula to use data validation formula box.

我尝试了以下操作，但没有成功:

I had attempted the below, but no success:

=OFFSET($C$13,0,0,COUNTIF(C:C,"?*")-1)

推荐答案

步骤1-将原始名称放在A列中，将此数组公式¹放在第二行的右侧.我将使用Z2.

Step 1 - With the original names in column A, put this array formula¹ somewhere off to the right in the second row. I'll use Z2.

=IFERROR(INDEX(A$2:INDEX(A:A, MATCH("zzz",A:A )), MATCH(0, COUNTIF(Z$1:Z1, A$2:INDEX(A:A, MATCH("zzz",A:A ))&""), 0)), "")

填充直到名称用完，然后再添加几行以供将来扩展.

Fill down until you run out of names and then a few more rows to allow for future expansion.

第2步-转到公式"►定义的名称"►名称管理器"并创建一个新名称.

Step 2 - Go to Formulas ► Defined Names ► Name Manager and create a new name.

Name: listNames
Scope: Workbook
Refers to: =Sheet2!$Z$2:INDEX(Sheet2!$Z:$Z, MATCH("zzz", IF(LEN(Sheet2!X:X), Sheet2!$Z1:$Z98) ))

第3步-转到要进行数据验证的单元格，然后使用数据"►数据工具"►数据验证".

Step 3 - Go to the cell you want you data validation and use Data ► Data Tools ► Data Validation.

Allow: List
Source: listNames

¹_{数组公式需要使用 Ctrl + Shift + Enter↵完成.如果输入正确，Excel会将公式用大括号括起来(例如 {和} ).您不要在自己中键入大括号.一旦正确输入第一个单元格，就可以像填充其他任何公式一样向下或向右填充或复制它们.尝试将全列引用减少到更接近代表实际数据范围的范围.数组公式对数地缩短了计算周期，因此，最好将参考范围缩小到最小.请参阅有关数组公式的准则和示例以获取更多信息.}

¹ _{Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.}

这篇关于数据验证-在Excel中没有重复项的下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！