Excel:动态内容的相关下拉列表

Question

我已按以下格式从 Google Analytics (GA) API 导入了客户及其网站的列表:

Account     Profile
Client 1    www.client1.com
Client 1    sub.client1.com
Client 1    tst.client1.com
Client 2    www.client2.com
Client 3    www.client3.com
Client 3    sub.client3.com

我需要的是一个包含唯一帐户名称的下拉列表，然后是一个包含所选帐户的相关配置文件的第二个下拉列表。看起来像这样的东西:

Account     Profile
Client 1    www.client1.com
            sub.client1.com
            tst.client1.com

我们一直向 GA 添加帐户和个人资料，因此每次运行报告时都需要刷新列表。

我该如何去做呢？

Answer 1

只要您有此列表按帐户排序，您就可以混合使用 Offset CountA为您提供所需的所有范围。

步骤是:

a.从“帐户”列中获取唯一值(我在这一步中使用 VBA，但您也可以使用频率公式来混合它——如果您可以使用 VBA 来实现这一点，我会避免它)。大致思路是这样的:

    Sub FindUniqueValues(SourceRange As Range, TargetCell As Range)
        SourceRange.AdvancedFilter xlFilterCopy, , TargetCell, True
    End Sub

(为了方便起见，我从我的 worksheet_change 事件中调用了它，但如果您使用 vba 来提取 G.A. 数据，只需集成到其中即可)

b.将其提供给具有论坛 AccountsRange 的命名范围(例如 =$D$2:INDEX($D$2:$D$5000,COUNTA($D$2:$D$5000)) ) -- 假设您指定 D1如TargetCell对于上一步，唯一值不会超过 4999(在我的例子中，第一个值是行标题 - 因此从 D2 开始)

c.添加此命名范围 AccountsRange作为数据验证列表的来源——您的帐户组合框此时已完成。我假设您将此组合框放入 E2我将在此基础上派生第二个 ComboBox

d.创建一个新的命名范围，例如 ProfilesRange公式为=INDEX($B$1:$B$5000,MATCH($E$2,$A$1:$A$5000,0),1):INDEX($B$1:$B$5000,COUNTIF($A‌​$1:$A$5000,$E$2)+MATCH($E$2,$A$1:$A$5000,0)-1,1)注意:如果您从此处复制粘贴，则上述公式将在 COUNTIF($A 之后包含换行符不可见字符。 excel 会提示公式无效，只需重新输入 $A$1部分COUNTIF应该没问题

A2是 G.A. 的帐户列的开头导入的数据( A1 是标题，假设此处最多有 4999 个数据行) E2是上一个 AccountsRange 下拉步骤中的组合框单元格。 -1 in Match需要步骤，因为第一个值行的偏移量需要为 0，而不是从 Match 返回的 1。 。第二个 1 是列偏移量(如果需要跳跃几列来获取辅助数据，则可以更多)。 CountIf这是非常不言自明的:)这基本上让我们得到了从起点开始的最终范围内所需的单元格数量。

e.创建使用 ProfilesRange 作为源的新数据验证列表，并且辅助组合框已完成。

可能出现的问题:

1. 当您更改“帐户”组合框时，“配置文件”组合框可能会出错，因为配置文件中的值不太可能出现在新客户端中。您可以忽略该错误(一旦您从新刷新的下拉列表中选择正确的值，它就会自行修复，或者在 E2 中的值发生变化时，在其中添加更多的 VBA 仙尘来清除下拉单元格。

2. 数据验证列表提示在您尝试设置 ProfilesRange 时评估错误。忽略此错误并继续是安全的。它基本上意味着 ProfilesRange 没有返回任何可计算的内容(很可能是因为您没有在 AccountsRange 中选择任何实际计算为 ProfilesRange 的内容 - 当然假设此处使用的公式的实际单元格范围对于您的工作表来说实际上是正确的)

感谢osknows感谢他在论坛上的提示和改进(在下面的评论中):避免使用 OFFSET，它在大范围内非常慢并且不稳定。 =OFFSET($D$2,0,0,COUNTA($D$2:$D$5000) 可以替换为 =$D$2:INDEX($D$2:$D$5000,COUNTA($D$2:$D$5000) )对于命名范围。(此技巧现已合并到此解决方案中，因此除非您检查版本历史记录，否则您将看不到原始的 Offset 论坛)