子集的Excel命名范围

Question

下面是一个示例表:

Field1 Field2
A       1
A       2
B       3
B       4
C       5
C       6

(注意:在完整的集合中有很多字段)。

需要定义名称范围A, B, C，所以当我做验证时，它显示它的子集。

。命名范围将导致选择1和2。B命名范围将导致选择3和4。等等…

现在我把表分成单独的列表来定义名称范围(即列A有两个值在它下面，如果1和2)。然而，这是一个更新的噩梦，当原始表有60多个名字映射，并且每周由多人更新。

是否有一种方法可以自动从主表创建所有这些命名范围，并有下拉菜单不显示所有的结果空白?

Answer 1

我建议这样做(有3个帮助栏):

1. 在列中使用helper提取字段1中的惟一值，并将此新信息保存为动态命名范围。要提取唯一值，您可以使用unique函数(如果您的excel版本有它)或以下数组公式(不要忘记按Ctrl+Shift+Enter以便正确工作):

   =IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$100),0)),"")
   

现在您可以创建一个数据验证下拉列表，只需调用命名范围作为列表。

2. 使用另一个辅助列提取字段2中的值，以在下拉列表中选择的字段1值作为输入。

3. 使用额外的辅助列来组织步骤2中值的数据并删除空白。之后，您可以创建一个新的动态命名范围及其相应的下拉列表。

注意:如果你的excel版本有FILTER功能，你可以避免第2步和第3步。

你可以在这里找到一个例子。(下载excel文件进行测试)