网站搜索

忘记 INDIRECT：在 Excel 中创建级联下拉列表的现代方法

级联下拉列表——比如选择“水果”类别后，下一列表会缩小到“苹果”或“梨”——是 Excel 高级数据输入的常见做法。旧的 INDIRECT 方法可以实现，但笨重且难以维护。相反，可以使用动态数组来构建列表，更顺畅地处理现代工作流程。

为什么使用 INDIRECT 创建级联下拉列表是不好的

对现代 Excel 来说是一大难题

旧教程通常建议使用 INDIRECT 来创建这种级联效果。虽然这种方法在所有版本中都可行，但它可能会引发问题：

  • “命名范围”的噩梦：要使 INDIRECT 正常工作，你必须为列表中的每一个类别创建一个唯一的命名范围。如果你有 50 个类别，你就在名称管理器中管理 50 个名称。这是一个无法扩展的维护灾难。
  • 绩效税： INDIRECT 是一个不稳定的函数，这意味着每当你更改工作簿中的任何单元格时，它都会重新计算。在大型文件中，这可能会导致显著的延迟。
  • “空格”问题：如果你的类别名称包含空格，INDIRECT 函数会出名地出错，因为命名范围不能包含空格。
  • 数据限制：命名区域不能以数字开头或包含特殊字符。因此，如果您的类别是“2026车型”，您需要在前面加一个字母或下划线。

现代工作流程：表格与溢出

处理依赖关系的一种更快、更智能的方式

在我们深入了解现代工作流程之前，重要的是要注意，这种方法依赖于动态数组，而动态数组在 Excel 2021 或更高版本、Microsoft 365 版本的 Excel 以及网页版 Excel 中可用。

设置

工作区分为Source（主表）、Engine（过滤数据的公式）和UI（下拉菜单）。通常，这三个过程会放在不同的工作表中，但我为了清晰起见把它们放在了一起。

要在阅读本指南时跟随操作，请下载免费的 Excel 工作簿副本。点击链接后，您会在右上角找到下载按钮。

源数据被格式化为名为 T_ProductMaster 的 Excel 表格。这是因为表格是有弹性的——当你在底部添加新行时，它们会自动扩展。此外，当我们引用表格列时，Excel 使用结构化引用，这比标准单元格引用更容易理解。

然而，引擎和用户界面都在正常范围内。这是因为我们将使用动态数组公式，而这些公式不能放入 Excel 表格中。如果你尝试在表格内溢出公式，Excel 会抛出 #SPILL! 错误。

第1步：创建类别链接

一旦你设置并命名了源表（在示例文件中已完成），你可以进入引擎部分。首先，我们需要从表中提取一个类别列表，以填充第一个下拉菜单。

在单元格 E4 中，输入此公式以为您的第一个下拉列表创建一个排序列表，然后按 Enter：

=SORT(T_ProductMaster[Category])

这会创建一个排序后的分类列表，数值从单元格 E4 向下溢出。你也可以使用 UNIQUE 函数来删除重复的数值，但没有必要——在创建列表时，数据验证工具会忽略它们。

现在，你在 UI 部分创建第一个下拉菜单：

  1. 选择单元格J4，在数据选项卡中，展开数据验证下拉菜单，然后点击数据验证。
  2. 在允许下，选择列表。
  3. 在 源 框中，输入以下公式，然后点击 确定。
=$E$4#

# 符号（溢出范围运算符）告诉 Excel 获取从 E4 溢出的整个数据范围。这意味着当你点击 J4 中的 下拉箭头 时，SORT 公式的所有结果都会出现。注意，重复项只会出现一次。

步骤2：创建子类别链接

既然第一个链接已经建立，重复相同的工作流程，为子类别创建依赖关系。在单元格 J4 中选择 硬件 以设置要使用的类别，然后在单元格 F4 中输入以下公式并按 Enter。

=SORT(FILTER(T_ProductMaster[Sub-Category],T_ProductMaster[Category]=J4))

对于 J5 中的相应下拉列表，在 数据有效性来源 字段中使用以下内容：

=$F$4#

第3步：创建产品链接

要完成链条，再重复一次工作流程，将最终产品列表连接到之前的选择。在单元格 J5 中选择 笔记本电脑，并在单元格 G4 中输入以下内容：

=SORT(FILTER(T_ProductMaster[Product],T_ProductMaster[Sub-Category]=J5))

现在，对于单元格 J6 的 数据验证规则，输入如下内容：

=$G$4#

级联列表现在已完成，您可以使用相同的步骤添加所需的任意级别。

专业提示：处理“陈旧数据”问题

无需 VBA 标记不匹配的选择

Excel公式会即时更新，但单元格本身是“粘性的”。换句话说，如果你将J4中的类别从硬件更改为软件，J5和J6的单元格仍然显示你之前的硬件选择。要解决此问题，你可以应用简单的条件格式，将这些无效选择标记为红色。

将警报应用于子类别 (J5)

  1. 选择 J5，然后在 首页选项卡中，点击 条件格式 > 新建规则。
  2. 点击 使用公式确定要设置格式的单元格。
  3. 点击格式，选择一个浅红色单元格填充，然后点击确定。
  4. 在 公式字段中，输入以下内容，然后点击 确定：
=ISERROR(MATCH(J5,$F$4#,0))

这使用 MATCH 函数来检查您在 J5 中的当前选择是否仍然存在于 F4 中的筛选列表中。如果不存在，ISERROR 函数会捕捉到该错误并触发红色填充。

将警报应用于产品（J6）

现在，对单元格J6执行相同的操作，但这一次，在公式字段中输入以下内容：

=OR(ISERROR(MATCH(J5,$F$4#,0)),ISERROR(MATCH(J6,$G$4#,0)))

这使用 OR 来检查 J5 与 F4 之间，或者 J6 与 G4 之间是否不匹配。如果链条的任何一层被中断，产品单元格将会发红。

虽然 INDIRECT 函数长期以来一直是级联下拉列表的首选，但它不再是最有效的工具。通过转向基于 Excel 表格和动态数组公式的现代工作流程，你可以消除数百个命名范围的需求，并避免由易变函数带来的性能滞后。

然而，这并不意味着你应该完全从你的 Excel 工具包中移除 INDIRECT。虽然许多 Excel 专家会告诉你避免使用它，但它仍然是一个用于可扩展任务的强大工具，比如将多个表格拼接成一个仪表板。

相关文章