首页 > 知识库 > 正文

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

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

由网友 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列数据源

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

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

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

由网友 Excel技巧精选 提供的答案:

谢邀。

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

那如果在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个单元格,作为下拉框的内容。

完整动画演示如下:

由网友 精进Excel 提供的答案:

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

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

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

01、下拉菜单制作方法

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

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

①数据有效性法

数据有效性在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,任意点开三篇文章,没有你想要的知识,算我耍流氓!

由网友 谈天说地聊工作 提供的答案:

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

⑴、新建一个工作簿,默认有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的内容不会相应变化而形成录入错误。怎样避免此类错误?还需你的研究和挖掘。

由网友 嘉州六弦琴 提供的答案:

用数组或编程,见效果图,即使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列,类推。

由网友 韩老师讲office 提供的答案:

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

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

动图演示:

关键步骤提示:

第一步:分级数据整理

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

第二步:自定义名称

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

第三步:建立各级菜单

一级菜单:

二级菜单:

三级菜单:

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

更多精彩内容推荐:

牙结石该如何去除?

想必我们每个人多多少少都会有点牙结石的,并且因为他而困扰,那么我们平常怎么来预防牙结石,怎么清楚牙结石呢?...

龙配什么属相好?

以属相配,十二属相是从属十二地支固定不变的。子鼠,丑牛,寅虎,辰龙,已蛇,午马,未羊,申猴,酉鸡,戌狗,亥猪。所谓合配不合配是从地支的合冲刑害上说的。合配就是要地支相合,地支相冲,相刑,相害就是不合配。地支有六合三合。六合是:子丑合,寅亥合,卯戌合,辰酉合,已申合,午未合。三合是...

发生过关系的男女朋友,还能做朋友吗?

谢谢邀请!这个事情得分情况,还得看双方是一种什么人和什么朋友?如果你们是向着男女朋友的关系发展的,然后再有一天发生了关系,那就是更进一步了,换句话说,也就是水到渠成的。如果是普通朋友同事或者很好的朋友,但不是男女朋友和情人关系的。这样的发生了关系的话,做朋友问题不大,彼此之间发生...

女人生没生过孩子,身体的差别在哪里?

女人生没生过孩子,还是有差别的,虽然说很多明星做了妈妈依然少女感十足,但有些地方,是藏也藏不住的。比如五年剖四的蒋丽莎,32岁跟四五十岁没什么两样:(图片来自网络)除此之外,还有一些部位也能看出生没生孩子的差别:怀孕的时候由于腹部等部位被拉伸,皮肤弹性纤维断裂,很多女人都会留下不...

微信零钱如何免费转到银行卡?

在2016年3月1日之前,微信是可以免费提现的,但是自2016年3月1日之后,微信开启了敛财之路,所有提现都必须要收取手续费,且收费高达0.1%。在提现收费后,很多用户发现可以通过信用卡还款来避免手续费,当时还信用卡的渠是不收手续费的(甚至可以通过信用卡溢缴款来取现,因为有的银行...

刚和女朋友同居时,你是什么感受?

中国人民大学潘教授一项关于大学生同居调查结果显示:大学生在对婚前同居持宽容态度的同时,特别是男性,对自己法律上配偶的是否有婚前同居行为却看得非常重要。对“假如你的恋人失去贞节,你还与她结婚吗?”这个问题,有53.4%的男大学生选择了“不”。同时,76.6%的未婚同,居女性认为同居对自己身体产生了严重影响,62.4%的女性对自己的同居行为表示后悔。...

我前段时间办了ETC,如果长时间不上高速,ETC还有用吗?

我发现今年ETC的热度非常高,如此看来往后去ETC会越来越普及。不过ETC在使用过程中有一些注意事项也是需要了解的,今天就来说一说关于ETC应该了解的基本知识。1、ETC有效期目前ETC设备有效期基本上是10年,在有效期内只要设备正常、卡里有钱、高速公路不倒闭你就可以一直用,哪怕...

曾经的央视主持人林海现在怎么样了呢?

曾经主持过《正大综艺》的林海现状如何呢?...

精力太过旺盛怎么办?

精力太过旺盛怎么办?有公鸡旺盛吗?公鸡一天可交配三百次。没事别在这吹牛,去扛两千包水泥,再来个二十公里长跑。最后给个美女你,你还行的话证明你是真旺盛,不行的话趁早混开。女人要是精力旺盛怎么办,不可能也要去扛水泥吧。...

和女人握手,她抠你手心是啥意思?

你有被女人抠过手心吗?...

唐朝的生活有多开放?

其实唐朝的开放体现在各个方面,政治、经济、文化和生活。这里主要说唐朝的生活:唐朝生活有多开放?她们的开放程度,恐怕我们现代人都得甘拜下风,今人说唐朝的开放,其实主要是感叹唐朝的性开放。其中最具代表性也是最直观记录最多的也就是皇室,皇室乱伦、婚前性行为、女子不必守贞……等各种性开放...

夫妻之间长期不同房的话,对身体健康有哪些影响呢?你怎么看?

谢邀夫妻长期不同房,就是说双方中有一方不愿意跟另一方进行性生活。会造成有需求一方的伤害,这种伤害会很严重。有需求一方会产生暴躁,控制不住可能产生对另一方的伤害。很多悲剧由此产生。有需求一方如果是弱者,也可能对自己产生心理疾病进而产生生理疾病,出现性功能减退至消失。如果双方长期不同...

猜你喜欢
热门推荐
今日推荐 更多
猜你喜欢