Excel一级下拉菜单、二级下拉菜单的设置方法

【一级下拉菜单】

在Excel中，一级下拉菜单的作用就是让我们点击单元格的时候，会提供下拉箭头，供用户选择要填入的值。

（1）选中要设置的单元格区域。

（2）数据——数据验证——允许序列。

*注意：

“允许”中选择“序列”，勾选“忽略空值”和“提供下拉箭头”。

“来源”中输入下拉菜单的内容，中间用英文状态下的逗号进行分隔，但是这里有文本长度限制。如果我们想输入更多数据（也就是下拉框中提供更多选项）的话，我们可以将数据输入在一个空列中，然后在“来源”中输入数据区域的绝对引用地址，这时候就没有限制。如果想要表格更加美观的话，我们还可以选中整一列后右键，隐藏源数据列。

完成效果图：

【二级下拉菜单】

在这里以二级下拉菜单为样例。源数据工作表步骤中称为源数据表，需要显示的菜单工作表称为菜单表。

（1）输入源数据表。

将源数据的标题输入在第一行，标题下包含的二级标题竖向排列在对应的标题下。

（2）在菜单表中选中需要输入一级菜单的区域，并进行设置。

*这一步设置好之后，我们选择一个一级菜单中的项目，二级菜单中的项目将会自动匹配。

源数据区域注意绝对引用，跨工作表引用需要在前面写工作表的名字，后面用英文状态叹号。

*此样例中进行跨工作表引用源数据，后续可以对源数据工作表进行隐藏，使表格更加简洁美观。

（3）回到源数据表中，选中源数据区域所有单元格（包括空单元格）。

*如果源数据行中间出现了隔行空值，如下图1所示。我们也可以通过同时摁键盘上的Ctrl+G打开“定位”窗口，点击左下角的“定位条件”，选择“空值”，然后摁delete就可以删除了，如果一个区域中有多个空行，也可以通过全部选中这个区域然后通过定位空值的方式删除所有空行。

（4）同时摁键盘上的Ctrl+G打开“定位”窗口，点击左下角的“定位条件”，选择“常量”，点击“确定”，这样我们就定位了源数据区域中的所有非空单元格。

（5）选中源数据区域所有单元格（包括空单元格），点击“公式”——“定义的名称”——“根据所选内容创建”——“首行”。

完成操作后，在“名称管理器”中就会出现刚才创建的名称了。

*后续需要修改数据的话，直接在工作表的源数据区域进行修改，然后打开“名称管理器”将对应名称的“引用位置”进行对应的修改，最后点击左边的勾确认修改即可。

（6）在菜单表中，选中需要定义二级菜单的列，“数据”——“数据工具”——“数据验证”——“序列”。在“来源”中输入=indirect(A1)。

*这里选中整列进行操作后，选中列名也会显示二级菜单项。想解决这个问题，就需要准确选中所有需要定义二级菜单的区域，然后再indirect函数的参数中输入对应的单元格位置。这里位置需要相对引用。

然后，我们选择一个一级菜单的项，二级菜单的项就会进行自动更新了。

（7）关于单价的提取。

*样例中源数据在商品名称后使用中文括号包含了商品价格，我们可以通过find、len、mid、value、iferror等函数的嵌套使用提取对应的价格。

*如果商品有对应的属性，例如薯条分为大、中、小份，可在源数据表输入商品信息时将属性通过英文括号接在商品名称后面，价格前面。这里通过中英文括号的区别来避免函数误提取。

*此样例中商品信息英文括号出现的数量不做限制，中文括号在一个商品信息中仅可出现1次。

*下面以菜单表C2单元格公式为例，解释公式各部分的作用。

函数：=IFERROR(VALUE(MID($B2,FIND("（",$B2,1)+1,LEN($B2)-FIND("（",$B2,1)-1)),0)

解释：

mid函数的作用是在特定单元格提取指定长度的字符串。

这里mid函数第一个参数是需要提取字符串的单元格地址，这里最好对单元格的列地址进行绝对引用。

mid函数第二个参数使用find函数查找左侧单元格（B列，即为商品信息）中第一个中文状态左括号的出现位置+1，偏移量为1（为了防止mid函数提取价格时将中文左括号提取进去，影响后续总价运算，偏移量的设置就是为了使Excel从价格的第一位数字开始提取）。*这个find函数第一个参数是需要查找的值，这个用英文状态双引号进行引用，第二个参数是查找值的单元格地址，这里同样对单元格列地址进行绝对引用，第三个参数是从单元格的哪一个字符开始查找，输入“1”表示从头开始查找。

mid函数第三个参数使用len函数和find函数的组合来确定商品信息中表示价格的字符串数量。首先使用len函数统计出单元格中总字符长度，然后减去商品信息中第一次出现中文状态左括号的字符串位置，通过偏移量-1的设置就可以去除中文右括号对于价格提取的影响。为了防止mid函数提取价格时将中文右括号提取进去。find函数的作用在上一段已进行介绍。

value函数的作用是将文本类型的数据转换为数值类型的数据，因为我们在源数据表中输入的商品信息是以文本形式保存的，所以商品价格字符串的数据类型默认为文本，文本无法参与数学运算，我们需要将文本类型转换为数值类型。

iferror函数是为了让函数运算出错的情况下显示数值型数据“0”，不影响总价的计算。

（8）关于总价的计算。

总价通过sum函数进行计算，这里因为总价在菜单表中仅出现一次，所以可以不进行绝对引用，但为了严谨也可以进行绝对引用，这里不受影响。

（9）关于表格美化。

隐藏源数据表，在工作表名称处右键，单击“隐藏”，即可隐藏源数据表。

隐藏后如果想重新显示被隐藏的工作表，我们可以在另外的工作表名称处右键，选择“取消隐藏”，单击或摁住ctrl多选工作表名称进行取消隐藏。

菜单表也可以做一定的美化，例如设置单元格填充颜色等。