excel - 如何创建具有动态验证列表的下拉菜单

Question

我有包含“类别”和“产品”列的数据表。产品名称不是唯一的，类别名称不是唯一的，但类别-产品组合在表中只出现一次。此数据表未以任何方式排序。

Category  Product

========  =======

Chairs      Victorian

Chairs      Beautiful

Chairs      Edwardian

Chairs      Gross

Tables      Victorian

Tables      Edwardian

Tables      Huge

Tables      Kool

Tables      Lambda

Closets     Edwardian

Closets     Excellent

Closets     Major

Closets     Hello

Chairs      Huge

Tables      Picturesque

Closets     Picturesque

Chairs      Incredible

Closets     Minor

Chairs      Just

Chairs      Kool

我已经创建了具有唯一类别名称的临时表，它将用作第一个下拉列表的验证范围。这部分工作正常。

Categories

==========

Chairs

Tables

Closets

在第一个下拉列表旁边，我有另一个下拉列表，它应该动态创建在上一个下拉列表中选择的类别的产品列表。

如果仅针对一对下拉菜单（类别/产品），我可以使用临时列创建令人满意的结果。

Category:   Chairs           Product: Victorian

                                                  Beautiful

                                                  Edwardian

                                                  Gross

                                                  Huge

                                                  . . .

问题是这个下拉对应该是另一个包含列的表的一部分：'Category'、'Product'、'Amount'。因此，当用户在第一列类别下拉值“椅子”中选择时，下一列下拉列表中的列表中应该只有“椅子”类别中的产品可用。在下一行中，当用户选择“表格”类别时，相邻单元格中应该只有“表格”类别中的产品可用。

我试图在没有 VBA 代码的情况下仅使用公式、数组公式、命名函数（不要将它们与 UDF 函数混淆）来实现这一点。

有可能这样做还是我在浪费时间（已经 2 天了）？

Excel文件与我已经做过的：这里

更新（2019-09-30）：

在评论中发现这一点：'但是，Excel 不允许您在数据验证中使用数组公式， ' ... Fernando J. Rivera 2017 年 11 月 4 日在Excel 动态下拉列表中过滤表

因此，这意味着不可能进行动态数据验证。

标签： excelexcel-formulaexcel-2010

Answer 1

解决这个问题的一种方法是IF在数据验证窗口中使用一个函数。

=IF($F$3=$B$2,$B$3:$B$10,$C$3:$C$10)

它检查F3类别列的单元格并查看它是否与单元格中的字符串匹配B2。如果是，它使用列表下的验证列表，如果不是，则使用另一个列表。这可以通过嵌套 IF 语句扩展到壁橱等。

缺点是，据我现在所见，必须分别对每一行进行验证。