Excel未使用宏列和助手列的其他表的筛选数据验证

Question

如何在Excel中创建预先筛选的数据验证下拉列表，同时：

• 不使用VBA/宏
• 没有或尽可能少的助手列
• 不要手动创建单独的命名范围，因为基础数据将随着每次刷新而更改(源表是OData-feed)。
• 不要在下拉列表中显示空值
• 还不能使用新的Excel数组函数，如FILTER()或UNIQUE()
• 按字母顺序排列下拉列表

这个问题近在咫尺，但我无法让the answer在我的情况下工作。这个answer uses an array，但是当我使用如图所示的公式时，结果是空的：

{=IFERROR(INDEX(Table1[Item];SMALL(IF(Table1[Type]="PT";ROW(1:5);"");ROW(1:5)));"")}

OData提要可能可以使用PowerQuery (DAX)进行预过滤，但我想知道如何在常规的Excel公式中实现这一点。

这张桌子是这样的：

​

​

所需的下拉列表显示类型= "PT“的每一项，因此值为: DEF、KLM和RST。

​

​

Answer 1

今天早上又做了一次，我找到了一个包含两个助手列的解决方案。

如下所示，第一个助手列查找具有相应类型"PT“的项目名称，第二个助手列按字母顺序对结果进行排序。

以下是该解决方案的两个限制：

( 1)项目名称必须是而不是纯粹是数字(如123或456)；

2)对于给定类型，不存在重复匹配项名(例如。不能有两个名为ABC的项目，其类型为PT )。

​

​

在展示公式之前，我需要定义几个名称，如下所示：

项目-它是我的解决方案中A列的名称；

类型-它是我的解决方案中B列的名称；

Filter -我在名称管理器中将其硬编码为"PT“。如果是首选的话，它可以存储在一个单元中；

-它是一个动态值，用于计算满足给定类型的项目数。在我的解决方案中，它作为height引用在下面的偏移量公式中使用。

筛选--它是列C (Helper 1)中的一个动态范围，捕获除空白之外的所有值。公式为=OFFSET(Sheet1!$C$1,1,,Height,)；

List --它是D列中的另一个动态范围(Helper 2)，它捕获除空白之外的所有值。公式是=OFFSET(Sheet1!$D$1,1,,Height,)。

​

​

C2中的公式如下所示。因为它是一个数组公式，所以您需要按Ctrl+Shift+Enter aka CSE才能使它工作。只要把它拖下来就行了。

{=IFERROR(INDEX(Item,SMALL(IF(Type=Filter,ROW(Item)-1,""),ROW()-1),),"")}

单元格D2中的公式也是数组公式，您也可以在输入CSE后向下拖动它。

{=IFERROR(INDEX(Filtered,MATCH(ROWS($1:1),COUNTIF(Filtered,"<="&Filtered),0)),"")}

最后，使用List (预定义的名称)作为数据验证的源，如下所示。

​

​

结论 我认为上述解决方案可能不太实用，特别是在数据量很大的情况下，但是我喜欢使用SMALL有条件地定位一系列数据，并使用COUNTIF按字母顺序排序文本值。 我还注意到偏移量的局限性，即它不能用于存储在名称中的动态范围，因为没有有效的起始单元格或单元格范围。否则，可能不需要任何辅助列。 最后，--我认为解决这个问题的最佳方法是Power ，它可以很容易地过滤和排序所需的数据范围，即使是对大量的数据也是如此。还有一种方法是使用数据透视表来筛选和排序所需的列表，如果添加帮助表是一种选择。干杯:)