excel - 下拉列表的动态命名范围 : Removing Duplicates without VBA

Question

我正在创建一个我希望尽可能“放手”的 Excel 仪表板。

用户粘贴导出的 .csv 报告，如下所示，最多可以有数万行:

Company     Property    Segment Type    Date        Forecast
Company 1   Property 1  Transient       01/01/2016  395.6599731
Company 1   Property 1  Corporate       01/01/2016  5066.720295
Company 1   Property 2  Transient       01/01/2016  4671.060322
Company 1   Property 2  Corporate       01/01/2016  0
Company 1   Property 1  Transient       02/01/2016  3513.280067
Company 1   Property 1  Corporate       02/01/2016  3513.280067
Company 1   Property 2  Transient       02/01/2016  0
Company 1   Property 2  Corporate       02/01/2016  3180.830109
Company 2   Property 1  Transient       01/01/2016  3180.830109
Company 2   Property 1  Corporate       01/01/2016  749.5098877
Company 2   Property 2  Transient       01/01/2016  3104.579819
Company 2   Property 2  Corporate       01/01/2016  2355.069931
Company 2   Property 1  Transient       02/01/2016  602.5899658
Company 2   Property 1  Corporate       02/01/2016  3327.980011
Company 2   Property 2  Transient       02/01/2016  2725.390045
Company 2   Property 2  Corporate       02/01/2016  0

将有一个单独的选项卡，其中包含公司下拉列表、属性下拉列表和平均预测显示。

我有多个动态命名范围如下(示例用于公司):

=OFFSET(数据!$A$2, 0, 0, COUNTA(数据!$A:$A)-1, 1)

我想要不包含重复的下拉菜单，所以只有公司 1、公司 2。

当我使用上面的动态命名范围时，下拉列表有 8 个公司 1 和 8 个公司 2。

有什么方法可以在不使用 VBA 的情况下做到这一点？数组函数可以嵌套在动态命名范围内吗？

Answer 1

您可以创建数据透视表来为每个下拉列表制作唯一值列表。使用数据表作为源，使用相应的下拉字段作为数据透视表行字段。每个数据透视表都是该字段的唯一值列表。

然后在数据透视表范围而不是数据范围上使用您的偏移公式来创建您的命名范围以进行数据验证。唯一的缺点是在粘贴新数据时需要刷新数据透视表。