1、一级下拉菜单 2、二级下拉菜单,三级、四级下拉菜单 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所示。 图08 分店名称已添加至下拉菜单 由于设置数据有效性时,选定的是工作表"Sheet1"的A列,因此A列的所有单元格都已设置为下拉菜单。 ▎2 二级下拉菜单 工作中录入数据时遇到分类的情况,譬如按大区划分店铺,简单的一级下拉菜单则不能满足工作要求,这时需要设置二级下拉菜单。 案例 华东区有6家分店,华北区有5家分店,分店名称如下表09所示,要求:设置一级下拉菜单为:华东区、华北区,根据选择的一级下拉菜单中的分区,点击二级下拉菜单时则显示该分区的下属分店名称的列表。 华东区 华北区 上海徐汇店 北京东城店 南京鼓楼店 天津和平店 杭州西湖店 石家庄长安店 苏州虎丘店 北京朝阳店 上海静安店 天津滨海店 无锡崇安店 表09 华东区、华北区分店明细 解决方法: ■ 将华东区及华北区店铺离别分别设置为自定义名称"华东区"和"华北区"。 ■设置一级菜单时,将"自定义名称"设置为Excel数据有效性中的序列的来源。 ■设置二级菜单时,使用INDIRECT函数的特点引用"自定义名称"的值,设置为数据有效性序列的来源。 (1)将分店名称按大区分别录入表格。将华东区6家分店名称录入至"Sheet2"中的A列,华北区5家分店名称录入至"Sheet2"中的B列,如下图10所示。 图10 华东区、华北区店铺名称录入Sheet2工作表中 (2)使用"名称管理器"自定义名称。 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所示,点击:关闭。 图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测试联想式输入"上" 联系式输入的最终效果如下图动图: ●●●