vba 数据验证 - excel 中没有重复项的下拉列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36778024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Data validation - Drop down list with no duplicates in excel
提问by James
Could you please assist me with removing duplicatesin a drop down list. The list is not static. Example:
拜托你帮我删除重复的下拉列表。该列表不是静态的。例子：
Before
前
James
Peter
James
Nick
Peter
After
后
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,0,0,COUNTIF(C:C,"?*")-1)
采纳答案by James
Step 1 - With the original names in column A, put this array formula1 somewhere off to the right in the second row. I'll use Z2.
步骤 1 - 使用 A 列中的原始名称，将此数组公式 1 放在第二行右侧的某个位置。我会用Z2。
=IFERROR(INDEX(A:INDEX(A:A, MATCH("zzz",A:A )), MATCH(0, COUNTIF(Z:Z1, A: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.
向下填充直到用完名称，然后再填充几行以供将来扩展。
Step 2 - Go to Formulas ? Defined Names ? Name Manager and create a new name.
第 2 步 - 转到公式？定义名称 ? 名称管理器并创建一个新名称。
Name: listNames
Scope: Workbook
Refers to: =Sheet2!$Z:INDEX(Sheet2!$Z:$Z, MATCH("zzz", IF(LEN(Sheet2!X:X), Sheet2!$Z1:$Z98) ))
Step 3 - Go to the cell you want you data validation and use Data ? Data Tools ? Data Validation.
第 3 步 - 转到您要进行数据验证的单元格并使用 Data ? 数据工具 ? 数据验证。
Allow: List
Source: listNames
????????
?????????
1 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 formulasfor more information.
1个数组公式需要用敲定Ctrl+ Shift+ Enter?。如果输入正确，Excel 会将公式括在大括号中（例如{和}）。您没有在自己输入大括号。正确输入第一个单元格后，它们可以像任何其他公式一样填充或向下或向右复制。尝试将全列引用减少到更接近代表实际数据范围的范围。数组公式以对数方式消耗计算周期，因此将引用范围缩小到最小值是一种很好的做法。有关更多信息，请参阅数组公式的准则和示例。
