深圳笔记本价格交流组

这一篇让你完全掌握Excel下拉菜单:一级二级三级、联想式输入..

EXCEL2019-06-29 11:12:14

     Excel下拉菜单是将指定的内容设置为下拉列表来限定输入的数据,不需要手工输入,只需点击鼠标即可从下拉菜单中选择内容输入至单元格中,这样能显著提高录入的效率,以及能减少发生录入错误的几率。


     1、一级下拉菜单

     2、二级下拉菜单,三级、四级下拉菜单

     3、终极下拉菜单——联想式输入



小窍门:将屏幕横放或点击图片可放大图片!


   一级下拉菜单


        工作中经常有使用率很高的资料,譬如公司的多个银行账号、多个部门名称、多个分店名称等,这时可以将这些常用资料设置为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列的所有单元格都已设置为下拉菜单。



    二级下拉菜单


        工作中录入数据时遇到分类的情况,譬如按大区划分店铺,简单的一级下拉菜单则不能满足工作要求,这时需要设置二级下拉菜单。


案例


华东区有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)设置三级、四级下拉菜单。


           设置三级、四级下拉菜单的方式跟设置二级下拉菜单的方法一样,掌握了二级下拉菜单的设置方法,同样的套路可以设置三级、四级甚至五级下拉菜单。



    终极下拉菜单——联想式输入


       工作中录入数据时如果下拉菜单中的数据太多,选择起来会很麻烦,譬如上例中各分区的店铺有几十家,甚至上百家的时候,这时下拉菜单就失去了方便的作用。这时需要设置联想式的下拉菜单。联想式输入是指:当输入第一个字后,只有以这个字开头的数据显示出来可供选择。

效果如下


 (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  测试联想式输入“上”


    联系式输入的最终效果如下图动图:



图书推荐


即将出版《Excel效率宝典:财务管理高手之路》



-转发是最好的赞赏!-



更多文章

Excel最常用的“七”个打印技巧!

73岁老人居然用Excel表格来创作山水画,令人叹服!

想要公司利润最大化,用Excel的这个功能就够了!

Excel强大的翻译功能,不用太可惜了~



PS:您也可以给我留言,或加微信:ilovexcel,我会安排时间集中回复!


EXCEL
微信订阅号:lovexcel
真实案例最实用
【实战运用 相互交流】

欢迎来撩:lovexcel@qq.com

微信:ilovexcel




长按二维码,选择“识别图中二维码”即可关注



Copyright © 深圳笔记本价格交流组@2017