Excel 下拉列表怎么做（数据验证）

在 Excel 中创建下拉列表：选中单元格，打开 数据 > 数据验证，把 允许 设为 序列，在 来源 中输入用逗号分隔的选项（North,South,East,West），或选择存放选项的区域，然后单击“确定”。现在每个单元格都会显示一个箭头，列出这些选项，其他输入都会被拒绝。

公式重置

E2fx

	A	B	C	D	E	F
1	Rep	Region	Sales		Region	Sales
2	Ana	North▾	120		North▾NorthSouthEastWest	360
3	Ben	South▾	85
4	Cara	North▾	240
5	Dan	East▾	60
6	Eve	South▾	150

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

F2 显示 360，即 North 的合计。在 B3 中选择 North，F2 会加上 Ben 的 85。E2 也有下拉列表：在那里选择 South，F2 就改为显示 South 的合计。用下拉列表做输入、再用公式读取它，是下拉列表最常见的用法。

下拉列表的创建步骤

1. 选中要添加列表的单元格，例如 B2:B6。
2. 打开 数据 > 数据验证（在“数据工具”组中）。在 Windows 上，按键顺序是 Alt、A、V、V。
3. 在 设置 选项卡上，把 允许 设为 序列。
4. 在 来源 中，可以输入用逗号分隔的选项 North,South,East,West，也可以点击该框，再在工作表上选择存放选项的区域，这样会写入 =$F$2:$F$5。
5. 保持勾选 提供下拉箭头（不勾选就没有箭头，只剩下输入检查）。
6. 单击 确定。

要用键盘打开列表，选中单元格后按 Alt+向下键（Windows）或 Option+向下键（Mac）。在 Microsoft 365 版 Excel 中，在单元格里输入开头几个字母，列表就会缩小到匹配的选项。

同一对话框里还有两个可选的选项卡：输入信息 会在选中单元格时显示提示，出错警告 决定有人输入列表外的值时会发生什么。样式为 停止（默认）时，输入会被拒绝；样式为 警告 或 信息 时，弹出提示后仍允许输入。取消勾选 输入无效数据时显示出错警告，就能让人随意输入，同时仍然提供列表。

手动输入的选项要用电脑区域设置中的列表分隔符隔开。在大多数使用小数逗号的区域（法国、德国、西班牙、意大利），分隔符是分号：Nord;Sud;Est;Ouest。

用单元格区域创建下拉列表

在对话框里输入的列表是看不见的，只能回到对话框中修改。放在单元格里的列表更容易维护：改一个单元格，所有用到它的下拉列表都会跟着变。这里的地区放在 E2:E5 中，B2:B6 上的下拉列表以这个区域为来源。

公式重置

B2fx

	A	B	C	D	E
1	Rep	Region	Sales		Regions
2	Ana	North▾NorthSouthEastWest	120		North
3	Ben	South▾	85		South
4	Cara	North▾	240		East
5	Dan	East▾	60		West
6	Eve	South▾	150

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

把 E5 从 West 改成 Central，再打开 B 列中任意一个箭头：列表中出现的是 Central 而不是 West。B 列中已经选好的值不会改变。

要使用另一个工作表上的区域（这是把列表藏起来的常用做法），在“来源”中输入工作表名：=Lists!$A$2:$A$5。如果希望在底部添加选项时列表自动变长，先把这些选项转换成表格（选中它们，插入 > 表格），再选择表格的这一列作为来源：引用会随表格一起扩展。

用 UNIQUE 做动态下拉列表

如果选项应该来自数据本身（某一列中出现过的每个地区，各出现一次），就用公式生成列表，再让下拉列表指向这个结果。在 G2 中输入 =SORT(UNIQUE(B2:B8))，会按字母顺序溢出不重复的地区。

公式重置

E2fx

	A	B	C	D	E	F	G
1	Rep	Region	Sales		Pick	Sales	Regions
2	Ana	North	120		South▾EastNorthSouthWest	235	East
3	Ben	South	85				North
4	Cara	North	240				South
5	Dan	East	60				West
6	Eve	South	150
7	Fay	West	95
8	Gus	East	110

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

G2 溢出 East、North、South、West，E2 中的下拉列表就提供这四项。把 B7 改成 Central，Central 会同时出现在溢出结果和列表中。

在 Excel 中，把下拉列表的来源设为 =$G$2#。单元格后面的 # 表示“这个公式的整个溢出区域”，所以列表的长度始终和结果完全一致，末尾不会有空行。溢出引用需要 Excel 365 或 2021；来源单元格可以在另一个工作表上（=Lists!$A$2#）。如果数据列中有空单元格，UNIQUE 会为它们返回一个 0；用 =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))) 把它们排除掉。UNIQUE 一文详细讲解了这个函数。

二级联动下拉列表

联动列表会随另一个单元格中的选择而变化：在 A2 中选择 Fruit，B2 就只提供水果。在 Excel 365 和 2021 中，用 FILTER 公式生成第二个列表：=FILTER(E2:E8,D2:D8=A2) 返回类别与 A2 相同的选项，B2 中的下拉列表以这个溢出结果为来源。

公式重置

A2fx

	A	B	C	D	E	F	G
1	Category	Item		Category	Item		Items
2	Fruit▾FruitVegetableBakery	Pear▾		Fruit	Apple		Apple
3				Fruit	Pear		Pear
4				Vegetable	Carrot		Kiwi
5				Vegetable	Leek
6				Bakery	Bread
7				Fruit	Kiwi
8				Bakery	Bagel

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

A2 为 Fruit 时，G2 溢出 Apple、Pear 和 Kiwi，这些就是 B2 中的选项。在 A2 中选择 Bakery：G2 变成 Bread 和 Bagel。在你重新选择之前，B2 仍然是 Pear，因为下拉列表从不改变单元格中已有的值。在 Excel 中，B2 的来源是 =$G$2#。

在旧版 Excel 中，经典做法是用 INDIRECT 和名称：

1. 把每个类别的选项各放在一列，以类别名作为标题：Fruit 放一列，Vegetable 放下一列。
2. 选中每一列选项，在名称框（编辑栏左侧）中用类别名为它命名：Fruit、Vegetable、Bakery。
3. 给 A2 添加下拉列表，来源为 Fruit,Vegetable,Bakery。
4. 给 B2 添加下拉列表，来源为 =INDIRECT(A2)。INDIRECT 会把 A2 中的文本变成对同名区域的引用。

名称必须与类别文本完全一致，而且不能包含空格（用 Dairy_Products，或在来源中用 =INDIRECT(SUBSTITUTE(A2," ","_"))）。INDIRECT 的更多内容见 INDIRECT 一文。

查找所选项对应的值

下拉列表常常用作订单表或报价单的输入：用户选择一个产品，查找公式填入它的价格。

公式重置

B2fx

	A	B	C	D	E	F
1		Product	Price		Product	Price
2	Order	Pear▾ApplePearCarrotBreadMilk			Apple	$1.20
3					Pear	$1.50
4					Carrot	$0.80
5					Bread	$2.40
6					Milk	$1.10

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

轮到你了： 在 C2 中，从 E:F 的表格里返回 B2 中所选产品的价格。

检查显示答案

选择 Pear 时，答案是 $1.50。在 B2 中选择另一个产品，价格会跟着变。=XLOOKUP(B2,E2:E6,F2:F6) 也可以；参数说明见 VLOOKUP。

根据所选项给单元格着色

要根据选择的内容给单元格着色（Done 为绿色，Late 为红色），给同一批单元格添加条件格式规则：选中 B2:B6，打开 开始 > 条件格式 > 突出显示单元格规则 > 等于，输入 Late 并选择一种格式。要给整行着色，选中 A2:B6，使用 新建规则 > 使用公式确定要设置格式的单元格，公式为 =$B2="Late"。

公式重置

B3fx

	A	B
1	Task	Status
2	Quote	Done▾
3	Invoice	Late▾OpenDoneLate
4	Order	Open▾
5	Report	Late▾
6	Survey	Done▾

在以下条件下高亮 B2:B6

点击单元格查看它的公式。修改数字或公式，表格会重新计算。

B3 和 B5 被突出显示。在 B4 中选择 Late，它也会被突出显示；在 B3 中选择 Done，突出显示就会消失。条件格式一文详细讲解了规则。

下拉列表不起作用的原因

• “数据 > 数据验证”中没有勾选 提供下拉箭头。列表仍然会限制输入，但没有箭头。
• 箭头只在选中的单元格上显示。表格中没有任何标记能看出其他哪些单元格带有列表；要找到它们，使用 开始 > 查找和选择 > 数据验证。
• 来源区域中有空白单元格，所以列表中出现了空行。只选择有内容的单元格，或者使用没有空白的溢出来源（=$G$2#）。
• 输入选项时用错了分隔符：在使用逗号的 Excel 中输入 North;South，会变成一个名为 North;South 的选项。
• 一个下拉列表只能保存一个值。选择第二个选项会替换第一个；要在一个单元格中选择多个选项，需要 VBA 宏。

要把下拉列表复制到其他单元格而不复制值，先复制单元格，再使用 开始 > 粘贴 > 选择性粘贴 > 验证。要删除下拉列表，选中单元格，选择 数据 > 数据验证 > 全部清除。