首页 > 知识库 > 正文

在Excel中制作具有联想能力的下拉列表的方法?

11对于新安装的Excel,一个新建的工作簿默认具有_个工作表。C 312浏览Internet上的网页,需要知道_。C网页的地址13在Excel中,当使用错误的运算对象类型时,将产生错误值B#VALUE!14支持局域网与广域网互连的设备称为()。C路由器15在Word的文档中插入数学公式,在"插入"菜单中应选的命令是D对象16Excel中,一个工作表有 行D6553617Windows 写字板应用程序和记事本应用程序的主要区别是D以上都正确18在Excel中,编辑栏指的是D工具栏和工作表之间的区域19基于通信双方所共享的但是不为别人所知秘密,利用计算机强大的计算能力,以该秘密作为加密和解密的密钥的认证是()A公钥认证20幻灯片集的背景色最好采用C统一的颜色21在Excel中,关于“删除”和“清除”的正确叙述是A删除指定区域是将该区域中的数据连同单元格一起从工作表中删除;清除指定区域仅清除该区域中的数据而单元格本身仍保留22网上共享的资源有()、()和()。B软件 数据 信道23在Excel是一个 应用软件B电子表格24OSI模型的最高层是B应用层25图表是用于A可视化地显示数字www.shufadashi.com防采集。

我现在有两个表格月报表和汇总表,分别如下图,现在我想在月报表中的第二行(用红色字体标出来的)有一个下拉列表分别是1-12月份,如果当我选择2月份的时候,会在下面的每一列的本月数据这列跳出对应的每一个项目2月份的发生额,但是月报表这个表中的数据全部来自于汇总表,而且每一个项目的每一列本年度中的数据会随着汇总表中的本年度发生额的变化而变化,想这种动态如何制作?求大神指导,很着急,谢谢

你这问题就是下了菜单加数据查询匹配!

准备工具/材料:装有windows 10的电脑一台,Microsoft Office 家庭和学生版 2016 excel软件。在EXCEL中将两个表中相同内容所对应的值相匹配的办法如下: 1、首先,用于在表单中输入名称和数据的

我用一篇文章让你完全掌握Excel下拉菜单的制作方法。

Office Excel的功能非常强大,也非常好用,我个人平时使用时非常好,所以我非常喜欢!一般的文字排版、表格、计算、函数的应用等都用EXCEL来解决,现将个人的一些使用体会通过本站于大家共享。

下拉菜单,从制作方法上,可以分为数据有效性法、控件法;从功能上,可以分为一级下拉菜单、多级联动下拉菜单、查询下拉菜单。

01、下拉菜单制作方法

Excel这种计算能力,使得Excel具有灵活的强大的计算能力(但计算能力受到数据量大小的限制)。Access计算功能是由查询和程序来完成的。Access的特点正好发挥计算能力的强大作用。4.数据容量:Excel表处理

下拉菜单有两者制作方法,最常用的是我们熟知的数据有效性,其实Excel中还有一个工具可以制作下拉菜单,它就是控件。

实例:如果A1=wROK1天and学习excel,则公式“=PROPER(A1)” 返回结果:Wrok1天And学习Excel 34.REPT 用途:按照给定的次数重复显示文本。语法:REPT(text,number_times)。参数:Text 是需要重复显示的文本

由于控件灵活性非常强,篇幅有限,本文只做简要介绍,将主要精力放在数据有效性上面。

①数据有效性法

数据有效性在2016版Excel中叫做数据验证。

如图所示,需要为部门列设置一级下拉菜单,设置下拉菜单之后,不仅能够提高录入效率,而且可以有效防止不规范地输入。

Step1: 选择要添加下拉菜单的单元格C2:C7,切换到「数据」选项卡,点击「数据验证」

Step2:验证条件中,「允许」中选择「序列」

Step3:「来源」框内选择已制作好的列表区域(也可手动录入选项,选项之间用英文状态下的逗号隔开)

GIF动图演示

②控件法

控件是Excel中比较高级的一种功能,多用于VBA开发。它被集成在「开发工具」选项卡。控件法创建的下拉菜单,多数用于数值的选择,一般创建的较少,不能批量创建。

如果你的Excel中,没有开发工具这个选项卡,需要先在「自定义功能区」中将「开发工具」添加进来。

勾选如下图中的开发工具即可。

创建方法:

Step1:切换到在「开发工具」选项卡,在「控件」分区,点击「插入」,选择「组合框」控件

Step2:在工作表的任意位置绘制生成控件,选中控件点击「鼠标右键」→「设置控件格式」,在弹出的对话框中设置数据源区域,其他项保持默认即可。

GIF动图演示

控件的使用非常灵活,它和OFFSET函数、CHOOSE函数、MTATCH函数、INDEX函数等结合,能制作出非常高效的动态图表,这里不详细展开。

02、多级联动下拉菜单

首先制作二级联动菜单。

二级联动菜单指的是,当我们选择一级菜单之后,对应的二级菜单会随着一级菜单的不同而选项也不同。二级菜单的创建方法有很多种,这里我们讲最常用的:通过indirect函数创建。

如图所示,我们要创建省份是一级下拉菜单,对应的市名是二级下拉菜单的联动菜单。

①为省市创建“名称”

名称是一个有意义的简略表示法,可以在Excel中方便的代替单元格引用、常量、公式或表。

比如将C20:C30区域定义为名称:MySales,那么公式=SUM(MySales)可以替代=SUM(C20:C30),可见名称比单元格区域更具有实际意义。

Step1:按住Ctrl键,分别用鼠标选取包含省、市名的三列数据,要点是不要选择空单元格。(也可以通过Ctrl+G调出定位条件,设置定位条件为在常量来选取数据区域)

Step2:在菜单栏中切换到【公式】选项卡→选择【定义的名称】分区→点击【根据所选内容创建】,在弹出的菜单中,勾选【首行】选项,如图所示,这样就创建了三个省份的“名称”,“名称”的值为对应着城市名。

②创建联动菜单

Step1:创建一级菜单

为区域中的省份一列创建一级菜单,创建方法通过“引用区域”的方式,直接将第一个图中的B1:D1区域作为数据来源,这里不在赘述。

Step2:为上图中的“市”创建二级菜单

选中【市】列需要设置的单元格区域→在验证条件中选择【序列】→【来源】中输入公式=INDIRECT($C3)→点击【确定】,此时会弹出错误提示,点击【是】继续下一步即可,如图。

提示:这里出错的原因是此时C3单元格中为空,还未选择省份的数据,找不到数据源,不影响二级菜单的设置。

完成之后,就实现了二级联动菜单,如图所示。

多级下拉菜单的制作原理是完全一样的,学会了二级下拉菜单,*菜单甚至四级菜单应该也不成问题,自己动手试一试吧!

03、查询式下拉菜单

下拉菜单的目的之一是提高输入的效率,但是,如果选项过多,那么下拉列表势必会很长,此时要想快速从下拉菜单中找到目标选项就非常困难。

我经常在想,如果能进行搜索下拉菜单该多好啊,这里教给你的方法,虽然没有搜索框,但是能模拟搜索的效果。

我把它称为查询式下拉菜单。

如图,要根据A列的集团列表,在E2单元格创建查询式下拉菜单,更方便地选择集团。该下拉菜单可以根据E2单元格内输入的第一个字来动态显示所有以输入汉字开头的集团,即实现查询作用。

Step1:对A列的集团进行升序排序。

Step2:选中E2单元格,打开「数据验证」对话框。在“允许”中选择“序列”,并在“来源”中输入公式:

=OFFSET($A$1,MATCH($E$2&"*",$A$2:$A$15,0),,COUNTIF($A$2:$A$15,$E$2&"*"),1)

Step3:在「数据验证」对话框,切换到「出错警告」窗口,取消勾选「输入无效数据时显示出错警告」,然后点击确定,完成设置。

最终的效果如下动图所示:

操作步骤同样很简单,难点是来源里面设置的公式。

①为什么要对集团数据列进行升序排序

排序之后,可以将第一个字相同的集团排在一起,这样在后面的输入首字进行查询式,这些集团都能够显示出来。

②OFFSET函数

它的语法形式是 OFFSET(reference,rows,cols,height,width),参数1为参照系,参数2为偏移行数,参数3为偏移列数,参数4为返回几行,参数5为返回几列。

总之,这里主函数OFFSET的作用就是:当E2单元格内输入首字时,找到以输入的汉字开头的集团名称,并引用所有符合条件的集团作为下拉菜单的显示内容。

③MATCH($E$2&"*",$A$2:$A$15,0)

在集团列表中查找以E2单元格字符开头的集团名称,返回找到的对应的第一个集团在列表中的序号;

④COUNTIF($A$2:$A$15,$E$2&"*")

在列表中统计以E2中字符开头的集团的个数

这里,MATCH函数作为OFFSET的第二个参数,即向下移动的行数;COUNTIF函数作为OFFSET的第4个参数,即从集团列表中返回的行数。

举例:当E2中输入“广”时

MATCH($E$2&"*",$A$2:$A$15,0)返回以广开头的集团在$A$2:$A$15中的序号,即2(广发集团排在第二位)。

此时COUNTIF($A$2:$A$15,$E$2&"*")统计出以广开头的集团共有三个,所以返回值为3。

主函数就变为OFFSET($A$1,2,,3,1),即返回「以A1为参照,向下移动移动两行(A3),行数总计为3行(A3:A5)的一个区域」,这个区域正是以广开头的三家集团:广发集团、广汇集团、广汽集团。

⑤为什么不能勾选出错警告

数据验证,要求输入的内容和设置的源中的内容必须一致,否则将提示警告,导致无法正常输入。我们因为是首字匹配,因此要取消警告。

最后,再次强调,函数是重点,理解了函数在本里中充当的含义,才能灵活的设置查询式下拉菜单。

End 祝你好运!!

关注精进Excel,任意点开三篇文章,没有你想要的知识,算我耍流氓!

我是头条号:Excel自学成才,可以尝试使用搜索式的下拉菜单,制作方法:

Excel下拉菜单好用,但是数据源多起来之后,就会出现找数据找不到的烦恼,对于如下所示,朋友们是怎么处理的呢?

这是小福个人思路,也不一定好用,仅供朋友们借鉴参考。

通过在辅助单元格E1输入关键词,然后将下拉菜单的数据源进行缩减,从中选择你需要的。

1辅助查询数据

需实现在E1单元格输入关键字,便从A列提取包含该字符的所有数据

其中:A列有102个数据即A1:A102

然后在F1输入公式:

=INDEX($A$1:$A$103,SMALL(IF(ISNUMBER(FIND($E$1,$A$1:$A$102)),ROW($A$1:$A$102),103),ROW(C1)))

自己制作,根据单元格位置不同可以套用上面的公式,注意102和103的位置即可。

2数据源保存

当查找关键字C时,下拉的数据提供是10个,当查找关键字R时,下拉的数据提供是6个。。那么如何动态的数据源如何保存供下拉数据源引用呢?

在【公式】-【名称管理器】中新建

名称为:数据源

引用位置:=OFFSET(Sheet1!$F$1,0,0,1048576-COUNTBLANK(Sheet1!$F:$F),1)

3下拉菜单数据引用

选中C列数据源

在【数据】-【数据验证】

在来源中输入=数据源 (刚刚定义的名称管理器)

对于数据源太多,你是怎么使用下拉菜单的呢?

用二级下拉菜单可解决此类问题。例如现在我们要制作一张表格,用下拉菜单的方式填写农林牧渔业的行业归类。

⑴、新建一个工作簿,默认有Sheet1、Sheet2、Sheet3三张工作表,在Sheet2工作表中,输入的菜单内容如下表:

⑵、定义一级菜单选项:将A1---A5定义为“行业”;

⑶、定义二级菜单选项(注意:定义的二级菜单名称应与一级菜单选项内容一致):将B2--B5定义为“农业”,B7---B11定义为“林业”,B13---B16定义为“畜牧业”,B18---B19定义为“渔业”。

⑷、选定Sheet1工作表B2单元格,点击命令菜单【数据---有效性】,在“数据有效性”对话框设置选项卡中,点选有效性条件的“允许(A)”值为“序列”,然后在“来源(S)”中输入“=行业”,一级菜单设定完成。

⑸、选定Sheet1工作表C2单元格,点击命令菜单【数据---有效性】,在“数据有效性”对话框设置选项卡中,点选有效性条件的“允许(A)”值为“序列”,然后在“来源(S)”中输入“=INDIRECT(B2)”,二级菜单设定完成。

试试看,做过B2单元格的选择后,才能做C2单元格的选择。但不足之处是选择完C2后又修改B2,C2的内容不会相应变化而形成录入错误。怎样避免此类错误?还需你的研究和挖掘。

用惯了网络搜索的你,一定也熟悉了网络搜索中的关键词推荐。

那如果在Excel中能用上,是不是也很酷啊!?

这里,我就分享下,如何在Excel中实现联想式下拉列表!


Excel中实现联想式下拉列表

实现联想式下拉列表,需要如下几个小技巧:数据有效性验证(下面会讲解)Offset函数动态确定范围

=offset($A$1,MATCH(B3&"*",$A:$A,0)-1,,COUNTIF($A:$A,B3&"*"))
参数1:$A$1,表示下拉框引用的区域起始位置,从A1单元格开始;参数2:MATCH(B3&"*",$A:$A,0)-1,表示将区域偏移到查找的关键字上,如示例中,输入"安"以后,区域就从第一找到"安"的位置开始;参数3:默认不偏移;参数4:COUNTIF($A:$A,B3&"*"),统计下输入内容的个数,如示例中,有3个姓"安"的人。

如上公式,输入"安",Offset则从第一个姓"安"人员位置开始,往下取3个单元格,作为下拉框的内容。

完整动画演示如下:

推荐一种多级菜单的制作方法:

以省份、地市、区县数据为例。

动图演示:

关键步骤提示:

第一步:分级数据整理

红色部分是一级菜单,*部分是二级菜单,无填充部分是*菜单需要的数据。如下图:

第二步:自定义名称

在名称管理器中可以看到已经建立的名称:

第三步:建立各级菜单

一级菜单:

二级菜单:

*菜单:

用数组或编程,见效果图,即使2个表的项目名称顺序不一致,也可以查找到数据.

如果不对,可能与你表格的结构不同,那就需要提供实际的表格,根据实际写.

B5=INDEX(汇总表!$A:$Z,MATC($A5,汇总表!$A:$A,),SMALL(IF(汇总表!$3:$3=B$3,COLUMN($A$1:$Z$1),256),--LEFT($A$2,LEN($A$2)-2)))

数组公式

C5=VLOOKUP($A5,汇总表!$A:$Z,MATCH(月报表!B$3,汇总表!$W$3:$Z$3,)+22,0)

B5,C5下拉,然后复制B5:C10公式,到其他2列,类推。

1、数据记2113录与整理在一个Excel文件中可以存储许多独5261立的表格,我们4102可以把一些不同类1653型但是有关联的数据存储到一个Excel文件中,这样不仅可以方便整理数据,还可以方便我们查找和应用数据。后期还可以对具有相似表格框架,相同性质的数据进行合并汇总工作。2、数据加工与计算在现代办公中对数据的要求不仅仅是存储和查看,很多时候是需要对现有的数据进行加工和计算。例如,每个月我们会核对当月的考勤情况、核算当月的工资、计算销售数据等。在Excel中主要应用公式和函数等功能来对数据进行计算。3、信息传递和共享在Excel中,使用对象连接和嵌入功能可以将其他软件制作的图形插入到Excel的工作表中,其主要途经是通过【超链接】和插入对象功能。其链接对象可以是工作簿、工作表、图表、网页、图片、电子邮件地址或程序的链接、声音文件、视频文件等内容来自www.shufadashi.com请勿采集。

声明:本网内容旨在传播知识仅供参考,不代表本网赞同其观点,文字及图片版权归原网站所有。

你可能还关注
热门推荐
今日推荐 更多