list 忽略数据验证列表中的重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21575456/
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
Ignore Duplicate Entries in Data Validation list
提问by user2426634
Suppose we have three columns as below:
假设我们有如下三列：
ID Sys Code
1 Roma A4
2 Roma A5
2 Roma A5
2 Roma A7
2 Lima A7
3 Lima B1
4 Lima C1
4 Lima D3
4 Lima D5
5 Alpha E9
6 Alpha E2
As observed from the above, all columns may contain duplicates, the goal is to have three cells in a different sheet where data validation lists would be used. A typical scenario would be:
从上面可以看出，所有列都可能包含重复项，目标是在不同的工作表中包含三个单元格，其中将使用数据验证列表。一个典型的场景是：
User selects the ID (e.g. 2), then on the next cell he selects the Sys (e.g. Roma), this list would be filtered according to the ID selected on the previous cell (only "Roma" and "Lima" shown), lastly, he selects the Code, this would be filtered according to the Sys selected (only "A5" and "A7" shown).
用户选择 ID（例如 2），然后在下一个单元格上选择 Sys（例如 Roma），该列表将根据前一个单元格上选择的 ID 进行过滤（仅显示“Roma”和“Lima”），最后，他选择代码，这将根据所选的系统进行过滤（仅显示“A5”和“A7”）。
The approach which I am currently using is to have a separate list of only unique IDs, this is being used on the first validation list, then on the second validation list I am using the below formula to select the respective Sys:
我目前使用的方法是有一个单独的唯一 ID 列表，这是在第一个验证列表中使用的，然后在第二个验证列表中我使用以下公式来选择相应的系统：
=OFFSET(IDS_Start,MATCH(A1,IDS,0)-2,1,COUNTIF(IDS,A1),1)
Where IDS_Start is a reference to the first ID in my list, IDS a reference to the entire column ID and A1 is the cell in which the user selects the ID.
其中 IDS_Start 是对列表中第一个 ID 的引用，IDS 是对整个列 ID 的引用，A1 是用户在其中选择 ID 的单元格。
The problem with the above is that it will result in duplicates to be displayed on the second validation list, is there a way around this?
上面的问题是它会导致重复显示在第二个验证列表中，有没有办法解决这个问题？
采纳答案by mshthn
This is called cascaded lists and this post will help you to solve it:
这称为级联列表，这篇文章将帮助您解决它：
http://www.bluepecantraining.com/portfolio/cascading-drop-down-lists-in-excel/
http://www.bluepecantraining.com/portfolio/cascading-drop-down-lists-in-excel/
