☰

基于矩阵的 Excel 动态下拉列表

tag-icon • tag-icon microsoft-excel data-validation
基于矩阵的 Excel 动态下拉列表

我希望根据控制矩阵来控制一系列下拉列表（使用数据验证创建）中可用的选项。

在下面的示例屏幕截图中，第一个下拉列表允许选择特征 1 或特征 2。如果选择了特征 1，则矩阵中的 X 表示特征 4 和 5 不允许出现在下拉列表 2 中，并且下拉列表 2 应仅显示特征 3 和 6 作为可能的选择。如果在第二个下拉列表中选择了特征 3，则下拉列表 3 将仅显示特征 8 作为选项。如果选择了特征 3，并且由于在开始时选择了特征 1 而排除了特征 9，则特征 7 不可用。

我希望这能说得通。我对人们关于实现这种控制水平的最佳方法/选项的想法很感兴趣。我使用命名范围和间接函数来实现类似的结果，但在使用矩阵指定可用选项时却不行。我想避免下拉列表中出现空白。

控制矩阵

答案1

下面的屏幕截图显示了如何使用下拉菜单的动态值列表来执行此操作。

第一个值列表，List Values 1是使用前两个下拉列表中的选项通过公式定义的。此列表实现了矩阵中显示的逻辑。如果我没有将您的文本正确地翻译成公式，请根据需要进行调整。

在此处输入图片描述

第二个值列表List Values 2创建一个动态、有序的值列表List Values 1，将任何空白值放在末尾。E14和中的公式E18为：

=IFERROR(INDEX(E$5:E$8,AGGREGATE(15,6,(ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>""),ROWS($E$14:$E14)),1),"")
=IFERROR(INDEX(E$9:E$11,AGGREGATE(15,6,(ROW(E$9:E$11)-ROW(E$8))/(E$9:E$11<>""),ROWS($E$18:$E18)),1),"")

最后，两个公式创建中非空值的数组List Values 2，并分配给名称List_2和List_3，用于指定下拉菜单 2 和 3 的列表值。这些公式显示在屏幕截图中。

我希望这个帮助能祝你好运。 ______________________________________________________________________________

怎么运行的：上面的内部AGGREGATE()函数正在执行与 (function=15) 相同的操作SMALL()，只是它有一个忽略错误的选项 (option=6)，并且它可以在不需要的情况下执行数组计算CTRLShiftEnter。

它所操作的数组是(ROW(E$5:E$8)-ROW(E$4))/(E$5:E$8<>"")，即数组除以范围不为空的值{1;2;3;4}数组： 。True/FalseE5:E8{False;True;True;False}

在涉及逻辑值的算术运算中，True和False被转换为 1 和 0。因此除法给出数组{#DIV/0!;2;3;#DIV/0!}，并AGGREGATE()忽略错误，给出非空白的行号：{2;3}，在上面显示的例子中。

然后将此数组用作 中的 row_nums INDEX()，返回 中的非空白行E5:E8。最后，当返回IFERROR()时返回空白，因为它的填充数量超过了 row_nums 列表中的值的数量。INDEX()#NUM!

命名范围的公式用于INDEX()返回每个列表中的最后一个非空值。 SUM()计算范围内非空单元格的数量，并将其用作的 row_num INDEX()。

相关内容