1、一级下拉菜单
3、终极下拉菜单——联想式输入
●●●
小窍门：将屏幕横放或点击图片可放大图片！
▎1 一级下拉菜单
工作中经常有使用率很高的资料，譬如公司的多个银行账号、多个部门名称、多个分店名称等，这时可以将这些常用资料设置为Excel表格的下拉菜单，通过点击下拉菜单选择需要的资料，即提高效率，也能避免错误。本小节介绍如何设置一级下拉菜单。
案例
华东区有6家分店，分店名称如下图01所示，要求：将分店名称添加为下拉菜单。
图01 华东区分店明细
解决方法：
■ 设置Excel数据有效性中的“序列”，实现下拉菜单功能。
（1）将分店名称录入表格。将华东区6家分店名称录入至“Sheet2”中的A列，如下图02所示。
图02 分店名称录入至Sheet2工作表中
（2）数据有效性设置“序列”。切换至“Sheet1”工作表，选定A列，单击工具栏上的“数据”标签，点击“数据有效性”右侧的下拉箭头，从下拉菜单中选择“数据有效性”，如下图03所示。
图03 调出数据有效性
（3）Excel将弹出“数据有效性”对话框窗口，设置数据有效性序列的取数“来源”。在“数据有效性”对话框中，选择“设置”标签，选择“设置”标签，点击“允许”下方的下拉箭头，从下拉菜单中选择“序列”，点击“来源”下方右侧的图标，如下图04所示。
图04 数据有效性设置序列
数据有效性对话窗口将缩小为仅剩“来源”下方的输入框，如下图05所示。
图05 数据有效性“来源”输入框
将表格切换至 “Sheet2” 工作表，选定A列，可以看到“来源”下方的输入框内为：=Sheet2！$A:$A，点击“来源”下方右侧的图标，如下图06所示。
图06 数据有效性“来源”取数
（4）Excel回到“数据有效性”对话框界面，“来源”下方输入框内已完成取数，点击“确定”。至此，数据有效性设置“序列”设置完成，已实现下拉菜单功能。如下图07所示。
图07 数据有效性“来源”完成取数
（5）分店名称已添加至下拉菜单。将表格切换至工作表“Sheet1”，点击单元格A1右侧的下拉箭头，从下拉菜单可以看到华东区6家分店的名称列表，选择需录入的店铺名称即可，如下图08所示。
由于设置数据有效性时，选定的是工作表“Sheet1”的A列，因此A列的所有单元格都已设置为下拉菜单。
▎2 二级下拉菜单
工作中录入数据时遇到分类的情况，譬如按大区划分店铺，简单的一级下拉菜单则不能满足工作要求，这时需要设置二级下拉菜单。
案例
华东区有6家分店，华北区有5家分店，分店名称如下表09所示，要求：设置一级下拉菜单为：华东区、华北区，根据选择的一级下拉菜单中的分区，点击二级下拉菜单时则显示该分区的下属分店名称的列表。
华东区
华北区
上海徐汇店
北京东城店
南京鼓楼店
天津和平店
杭州西湖店
石家庄长安店
苏州虎丘店
北京朝阳店
上海静安店
天津滨海店
无锡崇安店
表09 华东区、华北区分店明细
解决方法：
■ 将华东区及华北区店铺离别分别设置为自定义名称“华东区”和“华北区”。
■ 设置一级菜单时，将“自定义名称”设置为Excel数据有效性中的序列的来源。
■ 设置二级菜单时，使用INDIRECT函数的特点引用“自定义名称”的值，设置为数据有效性序列的来源。
（1）将分店名称按大区分别录入表格。将华东区6家分店名称录入至“Sheet2”中的A列，华北区5家分店名称录入至“Sheet2”中的B列，如下图10所示。
图10 华东区、华北区店铺名称录入Sheet2工作表中
Excel自定义名称是将编写好的Excel公式、工作表中单元格或单元格区域定义为一个名称，并用此名称代替编写好的Excel公式、单元格或单元格区域。合理使用自定义名称，可以更加快速准确地创建公式，简化Excel公式，拖拽复制时参数不变，而且自定义名称可以在工作簿中的任意工作表调用，使数据处理和分析更加快捷和高效。
（3）自定义华东区分店，调出“名称管理器”界面。
选定华东区店铺名称所在的A列，单击工具栏上的“公式”标签，点击“名称管理器”，如下图11所示。
图11 自定义华东区分店，调出“名称管理器”
（4）将华东区分店自定义名称：华东区。
Excel弹出“新建名称”对话框窗口，由于上一步操作中选定A列，因此在“引用位置”右侧输入框内可以看到“=Sheet2!$A:$A”，“名称”右侧输入框内输入：华东区，点击“确定”，如下图12所示。
图12 将华东区分店自定义名称：华东区
（5）自定义华东区分店，调出“名称管理器”界面。
选定华北区店铺名称所在的B列，单击工具栏上的“公式”标签，点击“名称管理器”，如下图13所示。
图13 自定义华北区分店，调出“名称管理器”
（6）将华北区分店自定义名称：华北区。
Excel弹出“新建名称”对话框窗口，由于上一步操作中选定B列，因此在“引用位置”右侧输入框内可以看到“=Sheet2!$B:$B”，“名称”右侧输入框内输入：华北区，点击“确定”，如下图14所示。
图14 将华北区分店自定义名称：华北区
（7）自定义名称完成。
华东区、华北区分店自定义名称完成，从“名称管理器”中可以看到自定义名称列表，如下图15所示，点击：关闭。
（8）验证及运用“自定义名称”。
自定义名称“华东区”、“华北区”设置完毕后，在该工作簿单元格中输入“=华东区”或“=华北区”，将会引用“Sheet2!$A:$A”或“Sheet2!$B:$B”的数据。
譬如在Sheet1工作表中，选定单元格A2:A7区域，然后编辑栏输入：=华东区，输入完毕后，同时按Shift Ctrl Enter完成，如下图16所示。Shift Ctrl Enter是将整个区域设为一个数组公式，各单元格不能再单独修改公式。
图16 引用自定义名称：华东区
公式的前后{}代表的是数组，结果如下图17所示，单元格A2:A7引用了“Sheet2!$A:$A”的数据。
图17 “华东区”引用华东区店铺名称数据
（9）数据有效性设置一级下拉菜单。将“Sheet1”工作表的A列设置为选择大区的一级下拉菜单。从工作表Sheet2切换到Sheet1，如下图18所示。
图18 切换至“Sheet1”工作表
（10）调出“数据有效性”对话界面。选定A列，单击工具栏上的“数据”标签，点击“数据有效性”右侧的下拉箭头，从下拉菜单中选择“数据有效性”，如下图19所示。
图19 调出数据有效性
（11）为“Sheet1”工作表A列设置“来源”。
Excel将弹出“数据有效性”对话框窗口，选择“设置”标签，点击“允许”下方的下拉箭头，从下拉菜单中选择“序列”，“来源”下方输入框内输入：华东区,华北区，“华东区,华北区”中间的“,”为英文键盘逗号，如下图20所示。
图20 为“Sheet1”工作表A列大区设置“来源”
值得注意的是，由于“华东区”、“华北区”是自定义名称，此处的来源设置为“华东区,华北区”是为设置二级菜单做准备。
（12）数据有效性设置二级下拉菜单。将“Sheet1”工作表的B列设置为选择大区下属分店名称的二级下拉菜单。
调出“数据有效性”对话界面。选定B列，单击工具栏上的“数据”标签，点击“数据有效性”右侧 的下拉箭头，从下拉菜单中选择“数据有效性”，如下图21所示。
图21 调出数据有效性
（13）为“Sheet2”工作表B列设置“来源”。
Excel将弹出“数据有效性”对话框窗口，选择“设置”标签，点击“允许”下方的下拉箭头，从下拉菜单中选择“序列”，“来源”下方输入框内输入：=INDIRECT（A1），点击“确定”，如下图22所示。
图22 为“Sheet1”工作表B列大区下属分店名称设置“来源”
（14）Excel会弹出提示窗口“源当前包含错误。示范继续？”，点击“是”，如下图23所示。
图23 提示窗口点击：是
由于单元格中没有数据，因此会跳出这个提示。
至此，Sheet1工作表B列的大区所属分店名称二级下拉菜单设置完成。
关于INDIRECT函数的详细介绍点击下方蓝字：
↓↓↓
INDIRECT函数详解丨返回并显示指定的内容（增加举例）
本例中B列数据有效性的来源“=INDIRECT（A1）”会随着B列单元格位置的变化而变化，单元格B1的来源为“=INDIRECT（A1）”，单元格B2的来源则变成“=INDIRECT（A2）”，以此类推。“=INDIRECT（A1）”的第1参数引用的单元格为“A1”，省略第2参数，即返回单元格A1的引用。由于单元格A1是下拉菜单，单元格B1的值将随着单元格A1的值而动态引用。
譬如点击单元格A1的下拉菜单，选择“华东区”，单元格B1根据数据有效性来源“=INDIRECT（A1）”，返回“华东区”的引用内容“Sheet2!$A:$A”（自定义名称“华东区”引用的位置）所组成的序列，生成华东区下属分店名称所组成的二级下拉菜单。
（15）测试一级、二级下拉菜单。
Sheet1工作表A列和B列的数据有效性设置完成后，我们对A列和B列的数据有效性运行情况进行测试。点击单元格A1，单元格右侧出现下拉箭头，点击下拉箭头，将出现下拉菜单：华东区、华北区，如下图24所示。
图24 测试A列的一级菜单
从单元格A1下拉菜单中，选择“华东区”，点击单元格B1，单元格右侧出现下拉箭头，点击下拉箭头，将出现下拉菜单列表（Sheet2工作表A列数据）：上海徐汇店、南京鼓楼店、杭州西湖店、苏州虎丘店、上海静安店、无锡崇安店，如下图25所示。
图25 测试B列的二级菜单-华东区
同样，如单元格A1选择“华北区”，单元格B1下拉菜单则显示Sheet2工作表B列数据。如下图26所示。
图26 测试B列的二级菜单-华北区
（16）设置三级、四级下拉菜单。
设置三级、四级下拉菜单的方式跟设置二级下拉菜单的方法一样，掌握了二级下拉菜单的设置方法，同样的套路可以设置三级、四级甚至五级下拉菜单。
▎3 终极下拉菜单——联想式输入
工作中录入数据时如果下拉菜单中的数据太多，选择起来会很麻烦，譬如上例中各分区的店铺有几十家，甚至上百家的时候，这时下拉菜单就失去了方便的作用。这时需要设置联想式的下拉菜单。联想式输入是指：当输入第一个字后，只有以这个字开头的数据显示出来可供选择。
效果如下
▼
（1） 对店铺名称进行升序排列。
选定A列，单击工具栏上的“数据”标签，点击“升序”按钮，如下图27所示。
图27 店铺名称升序排列
（2） 调出“名称管理器”界面。
单击工具栏上的“公式”标签，点击“名称管理器”按钮，如下图28所示。
图28 调出“名称管理器”
（3） 新建自定义名称“店铺”。
Excel弹出“名称管理器”对话框窗口，点击“新建”按钮。如下图29所示。
图29 名称管理器点击“新建”
Excel弹出“新建名称”对话框窗口，“名称”右侧输入框内输入：店铺，“引用位置”右侧输入框内输入公式:
=OFFSET(Sheet1!$A$1,MATCH(Sheet1!$C3&""*"",Sheet1!$A:$A,0)-1,,COUNTIF(Sheet1!$A:$A,Sheet1!$C3&""*""),1)
点击“确定”，如下图30所示。
图30 自定义名称“店铺”
这里用到通配符星号*，起到了模糊运算的作用。
关于Offset、Match、Countif函数的详细介绍点击下方蓝字：
↓↓↓
Match函数详解丨返回需查找值在指定查找区域内的位置（举例）
OFFSET函数丨返回指定位置的单元格或者区域（举例）
Countif函数详解丨在指定区域内对满足指定条件的单元格进行计数（举例）
自定义名称完成。从“名称管理器”中可以看到自定义名称“店铺”，如下图31所示，点击：关闭。
图31 自定义名称列表
（4） 调出“数据有效性”对话界面。
选定需联想输入的单元格区域C3：C10，单击工具栏上的“数据”标签，点击“数据有效性”右侧的下拉箭头，从下拉菜单中选择“数据有效性”，如下图32所示。
图32 调出“数据有效性”界面
（5） 为联想输入的单元格区域设置数据“来源”。
Excel将弹出“数据有效性”对话框窗口，选择“设置”标签，点击“允许”下方的下拉箭头，从下拉菜单中选择“序列”，“来源”下方输入框内输入“=店铺”，此处的“店铺”为上文中的自定义名称，如下图33所示。
图33 为联想输入区域设置数据“来源”
（6） 取消勾选出错警告。
选择“出错警告”标签，找到下方的“输入无效数据时显示出错警告”选项，取消勾选该选项，点击“确定”，如下图34所示。
图34 取消勾选出错警告
至此，单元格区域C3：C10联想式输入已经设置完成。
（7） 测试“联想式下拉菜单”。
Sheet1工作表单元格C3：C10的数据有效性设置完成后，我们对单元格C3：C10的数据有效性的联想式输入运行情况进行测试。点击单元格C3，单元格右侧出现下拉箭头，点击下拉箭头，将出现下拉菜单：北京朝阳店、北京东城店、杭州西湖店。。。，为A列的全部数据，如下图35所示。
图35 测试联想式输入
现在在单元格C3中，输入：上，然后点击单元格右侧的下拉箭头，此时出现下拉菜单：上海静安店、上海徐汇店，该下拉菜单显示的店铺列表为A列数据中第一个字为“上”的所有店铺名称，如下图36所示。
图36 测试联想式输入“上”
联系式输入的最终效果如下图动图：
●●●
