跳到主要内容

在 Excel 中制作动态相关下拉列表(逐步)

在本教程中,我们将逐步介绍如何创建一个依赖下拉列表,该下拉列表根据第一个下拉列表中选择的值显示选项。 换句话说,我们将根据另一个列表的值制作一个 Excel 数据验证列表。

做一个动态依赖的下拉列表
10s用得心应手的工具制作依赖下拉列表
在 Excel 2021 或 Excel 365 中制作动态依赖下拉列表
关于本教程您可能会问的一些问题

文档依赖下拉列表 1 1 1

免费下载示例文件 doc样本


视频:制作依赖于 Excel 的下拉列表

 


做一个动态依赖的下拉列表

 

第 1 步:键入下拉列表的条目

1. 首先,在下拉列表中输入您想要显示的条目, 每个单独列在一列.

通知 第一列(产品)中的项目稍后将作为依赖列表的 Excel 名称。 例如,此处 Fruit 和 Vegetable 将分别作为列 B2:B5 和 C2:C6 的名称。

看截图:

文档依赖下拉列表 1 2

2. 然后为每个数据列表创建表。

选择列范围 A1:A3,单击 插页 > ,然后在“创建表”对话框中,勾选 我的桌子有标题 复选框。 请点击 OK.

文档依赖下拉列表 1 3

然后重复此步骤为其他两个列表创建表。

您可以在名称管理器中查看所有表格和对范围的引用(按 按Ctrl + F3 打开它)。

文档依赖下拉列表 1 4

第 2 步:创建范围名称

在这一步中,您需要创建 名称 对于主列表和每个从属列表。

1. 选择出现在主列表中的项目 (A2:A3).

2.然后转到 姓名框 在旁边 公式栏.

3.输入名称,这里命名为 产品.

4。 按 输入 键完成。

文档依赖下拉列表 1 5

然后重复上述步骤,为每个依赖列表分别创建名称。

这里将第二列 (B2:B5) 命名为 Fruit,将第三列 (C2:C6) 命名为 Vegetable。

文档依赖下拉列表 1 15

文档依赖下拉列表 1 6

您可以在名称管理器中查看所有范围名称(按 按Ctrl + F3 打开它)。

文档依赖下拉列表 1 7

第 3 步:添加主下拉列表

接下来,添加主下拉列表(Product),这是一个普通的数据验证下拉列表,而不是从属下拉列表。

1.首先,创建一个表。

选择一个单元格(E1),然后键入第一列标题 (产品), 并移动到下一列单元格 (F1), 键入第二列标题 (Item). 您将把下拉列表添加到该表中。

然后选择这两个标题(E1 F1),点击 插页 选项卡,然后选择 在表格组中。

在“创建表”对话框中,勾选 我的桌子有标题 框,然后单击 OK.

文档依赖下拉列表 1 8

2. 选择单元格 E2 要向其中插入主下拉列表,请单击 时间 标签并转到 数据工具 组点击 数据验证 > 数据验证.

文档依赖下拉列表 1 9

3. 在数据验证对话框中,

  • 清单 ,在 部分,
  • 输入以下公式 来源 栏,产品是主列表的名称,
  • 点击 OK.
=Product

文档依赖下拉列表 1 10

您可以看到主下拉列表已经创建。

文档依赖下拉列表 1 11

第 4 步:添加依赖下拉列表

1. 选择单元格 F2 要添加依赖的下拉列表,单击 时间 选项卡,然后转到数据工具组单击 数据验证 > 数据验证.

2. 在数据验证对话框中,

  • 清单 ,在 部分,
  • 将下面的公式输入 来源 栏,E2 是包含主下拉列表的单元格。
  • 点击 OK.
=INDIRECT(SUBSTITUTE(E2," ","_"))

文档依赖下拉列表 1 12

如果E2为空(你没有选择主下拉列表中的任何一项),你会看到弹出如下消息,点击 继续。

文档依赖下拉列表 1 13

现在已经创建了依赖下拉列表。

文档依赖下拉列表 1 14

第 5 步:测试从属下拉列表。

1。 选择 水果 在主下拉列表中(E2),然后转到从属下拉列表 (F2) 单击箭头图标,查看水果项是否在列表中,然后从相关下拉列表中选择一项。

2。 按 制表 在数据输入表中开始新行的键,选择 蔬菜, 并移动到右侧的下一个单元格,查看蔬菜项目是否在列表中,然后从依赖下拉列表中选择一个项目。

动图 1

:

10s用得心应手的工具制作依赖下拉列表

 

Kutools for Excel 提供了一个强大的工具,让依赖的下拉列表变得更加简单快捷,让我们看看:

kte gif 1

在执行以下步骤之前,请 点击下载Kutools for Excel 30天免费试用 首先。

第 1 步:键入下拉列表的条目

首先,如下图所示安排您的数据:

doc kutools 动态下拉列表 1

第 2 步:应用 Kutools 工具

1.选择你已经创建的数据,点击 库工具 选项卡,然后单击 下拉列表 要显示子菜单,请单击 动态下拉列表.

doc kutools 动态下拉列表 2

2.在依赖下拉列表中

  • 访问 模式B 匹配您的数据模式,
  • 点击 输出范围,输出范围列必须等于数据范围列,
  • 点击 Ok.

doc kutools 动态下拉列表 3

现在已经创建了依赖下拉列表。

doc kutools 动态下拉列表 4

Tips:
  • 方式B支持创建三级或更多级下拉列表:
    doc kutools 动态下拉列表 5 1
  • 如果你的数据排列如下图所示,你需要使用模式A,模式A只支持创建一个2级依赖的下拉列表。
    doc kutools 动态下拉列表 6
  • 有关如何使用 Kutools 创建依赖下拉列表的更多详细信息,请访问 本教程 .

Kutools for Excel

全功能30天免费试用,无需信用卡。

Excel的300多种强大的高级功能。

不需要任何特殊技能,每天可以节省数小时的时间。

在 Excel 2021 或 Excel 365 中制作动态依赖下拉列表

 

如果您在Excel 2021或Excel 365中,还有另一种方法可以使用新功能快速创建动态依赖下拉列表 独特性FILTER.

假设您的源数据如屏幕截图所示排列,请按照以下步骤创建动态下拉列表。

第 1 步:使用公式获取主下拉列表的项目

选择一个单元格,例如单元格 G3,并使用 UNIQUE 和 FILTER 函数从中提取唯一值 产品 列表,这将是主下拉列表的来源,然后按 输入 键。

=UNIQUE(FILTER(A3:A20, A3:A20<>""))
备注:由于产品在 A3:A12 中,我们向阵列添加 8 个额外的单元格以满足可能的新条目。 此外,我们将 FILTER 函数嵌入到 UNIQUE 中以提取不带空格的唯一值。

第 2 步:创建主下拉列表

1.选择一个要放置主下拉列表的单元格,例如单元格 D3,单击“ 时间 标签,然后转到 数据工具 组点击 数据验证 > 数据验证.

2. 在数据验证对话框中,

  • 清单 ,在 部分,
  • 输入以下公式 来源 酒吧,
  • 点击 OK.
=$G$3#
备注:这称为溢出范围引用,此语法指的是整个范围,无论它扩展或收缩了多少。

现在主下拉列表已创建。

第 3 步:使用公式获取依赖下拉列表的项目

选择一个单元格,例如单元格H3,使用FILTER函数根据单元格中的值过滤项目 D3 (主下拉列表中的选定项目),按 输入 键。

=FILTER(B3:B20, A3:A20=D3)
备注:如果主下拉列表中有空白,则公式将归零。

第四步:创建依赖下拉列表

1. 选择一个放置依赖下拉列表的单元格,例如单元格 E3,单击“ 时间 标签,然后转到 数据工具 组点击 数据验证 > 数据验证.

2. 在数据验证对话框中,

  • 清单 ,在 部分,
  • 输入以下公式 来源 酒吧,
  • 点击 OK.
=$H$3#
备注:这称为溢出范围引用,此语法指的是整个范围,无论它扩展或收缩了多少。

现在依赖下拉列表创建成功。

当您在 A3:A20 中添加新项目或进行一些更改时,下拉列表将自动更新。

Tips:

按字母顺序排序下拉列表

如果你想按字母顺序排列下拉列表中的项目,你可以使用下面的公式来准备表格。

对于主下拉菜单(单元格 G3 中的公式):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

对于依赖下拉列表(单元格 H3 中的公式):

=SORT(FILTER(B3:B20, A3:A20=D3))

现在两个下拉列表都按字母顺序 A 到 Z 排序。

文档依赖下拉 365 8

要按字母顺序从 Z 到 A 排序,请使用以下公式:

对于主下拉菜单(单元格 G3 中的公式):

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

对于依赖下拉列表(单元格 H3 中的公式):

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)

您可能会问的一些问题:

1、为什么要为每个数据列表插入一张表?

为数据列表插入表格将帮助您根据数据列表中的更改自动更新下拉列表。 例如,在第一个数据列表中添加“其他”,则主下拉列表将自动添加“其他”。

文档相关下拉列表更新

2、为什么要用表格来放置下拉列表?

当您按 Tab 键向表格中添加新行时,下拉列表也将自动添加到新行中。

3. INDIRECT函数如何工作?

间接 函数用于将文本字符串转换为有效引用。

4. 公式 INDIRECT(SUBSTITUTE(E2&F2," ","")) 是如何工作的?

首先, 替代 函数用另一个文本替换文本。 这里它用于从组合名称(E2 和 F2)中删除空格。 然后 间接 函数将文本字符串(E2 和 F2 的组合内容)转换为有效引用。

最佳办公生产力工具

🤖 Kutools 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations