【「EXCEL」一个公式实现 表格内容根据下拉菜单变化,制表so easy】

office应用 徐 自远 956℃

【「EXCEL」一个公式实现 表格内容根据下拉菜单变化,制表so easy】

一个公式做出媲美ERP下拉拖拽功能的信息表

职场老手总说用上ERP就像用了机械键盘,回不来了,因为ERP通过简单的拖拽就能快速定制信息表。毕竟ERP是块老奶酪,需要点学习成本。小编表示不服,今天咱们就试试用一个公式证明excel中的下拉拖拽也能想用就用。先看效果:

  1. 自动检测是否填入信息,启用标题栏样式
  2. 标题栏信息可根据基础信息表下拉选择
  3. 填入姓名关键字,自动生成对应序号及人员信息关键字,并填充表格样式
  4. 行信息可以任意排序,拖动后对应行序号按照排序后位置重新更新,人员信息跟随关键字变化
  5. 列信息可以自由删减,增加,移动 ,编辑,编辑后的列信息不会出现错误
  6. 关键字删除后,其他信息和表格样式,列标签删除后,自动删除颜色填充样式,重新填入信息后,以上功能依然有效

功能虽多,操作却很简单

上述功能虽然说得天花乱坠,其实的重点在于信息的自动生成,至于表格自动填充边框和样式,简单的一个条件格式就完成了。信息自动生成仅仅依赖一个index复合match函数就能实现,而且这个公式仅需要在第一个单元格内编写一次就完成了,剩下的就是放纵的双击或者拖拽了

index+match 函数

要理解这个公式的终极奥义,必须先知道这个公式讲的是啥,先来看看它的构成

index,插入函数,将某个范围内某行某列的数据插入到当前单元格内

书写规范:

index(被选插入数据所属于范围,数据所在范围内的行,数据所在范围内的列)

动画演示:

match,匹配函数,将被选关键字 在 关键字所属范围内的序值 填写到当前位置

书写规范:

match(关键字,关键字所在范围,匹配度)

动画演示:

行匹配(计算 肖赛在姓名列中的第几行)

列匹配(计算 毕业院校 在标题标签中的第几列)

这两个函数看起来都很简单,但是如果将这两个函复合到一起,将产生能够完全取代并超越vlookuop的强大函数,能同时在行和列间以多条件匹配的形式精确找到你要的数据信息(下图演示了利用index+match 函数 根据姓名肖赛找到他的毕业院校)

书写规范

index(被选插入数据所属于范围,match(匹配行),match(匹配列)

动画演示:

函数输入完成。剩下的就是双击或者拖拽了,左右拖,上下拖,就能完成所有信息的查找。但在你放肆拖拽之前,必须要是正确的设置地址锁定,才能让生成的数据准确无误

关于地址锁定

地址指的是代表单元格位置或者范围的字符,如B1,D5:D8 这样的,A B C….Z 表示列地址,1 2 3 4….99表示行地址,在没有设置地址锁定($表示锁定)的情况下在我们拖动句柄的时候,地址符会根据拖动的方向变化,

横向拖动变化列地址(A1→Z1),纵向拖动的时候变化行地址(A1→A100):

在设置锁定符$后(快捷键F4),会让锁定的行列地址不随句柄的拖动而变化,$符号在谁前面表示锁定谁

  • $A$1,表示行列均锁定,拖动行列地址均不变化
  • A$1,表示列行锁定,仅在横向拖动时,列地址会按照A$1→Z$1变化,纵向拖动时,A$1不会发生变化
  • $A1,表示列锁定,仅在纵向拖动时,行地址会按照$A1→$A100变化,横向拖动时,$A1不会发生变化

说了那么多,关于行列地址锁定不知你看懂没,要是没看懂我也不知道咋描述了,可能度娘那有更好的答案。

接着我们看我们的公式:

index(被选插入数据所属于范围,match(匹配行),match(匹配列)

首先,在本次演示内容中,所有的范围一定不能变化,所有数据范围范围均为行列锁定;(如 $C$2:$P$24,表示C2到P24这个查找范围行列锁定,不随句柄拖动而变化)

对于行匹配,关键字姓名 一直存在于B列,不能变化;向下拖动,需要纵向向下检索关键字,所以锁定列,不锁定行;

对于列匹配,标题标签一直存在于第一行,不能变化,而需要根据所选关键字生成关键字所在范围内行的序数,所以锁定行,不锁定列;完整的公式是这样的:

=INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B6,Sheet2!$B$2:$B$24,0),MATCH(Sheet1!E$1,Sheet2!$C$1:$P$1,0))

演示如下:

完成上面的地址锁定设置后,放肆的自由拖动吧。最终效果请上翻第1-2图

关于无数据区显示 #N/A

这个可以使用IFNA函数解决,用IFNA函数包围上面的公式即可,当上述公式结果值为空时,公式所在的单元格不输入任何数据,看起来就像空白单元格一毛一样:=IFNA( 上述公式,””)

=IFNA(INDEX(Sheet2!$C$2:$P$24,MATCH(Sheet1!$B30,Sheet2!$B$2:$B$25,0),MATCH(Sheet1!E$1,Sheet2!$A$1:$P$1,0)),””)

关于标题栏的下拉菜单

下拉菜单依然是使用 数据验证 的形式完成的,在我前面的文章中已经用到过多次,再演示一遍:

关于填充边框及底色的表格样式

填充表格样式是利用条件各式完成的,excel2013版本以上的数据验证使用说明写的十分清楚易懂,一目了然,设置一个单元格的条件格式后,直接拖拽公式句柄,即可应用到其他单元格,配合公式和数据验证使用,十分简单粗暴。演示如下:

关于功能4中的行序号自动更新

其实这个很简单,利用row()函数即可实现,在序号列输入 =row()-1 后向下拖动,即为在当前单元格填写单元格所在行号减去1的数值,至于减去多少,就看你标题占了多少行就行,如果想要实现在没有行数据时不显示序号,利用if函数即可,公式书写如下(通过B列判断行信息是否为空,若空,则不填写任何数值,若不空,填写序号):

=if(B2=””,””,row()-1)

如果图片太小看不清,文中所有gif图均可单击放大查看,想边看边练习的话推荐使用电脑端查看!

aoskill 专注于分享Adobe office系列软件的使用技巧,让你的办公更高效,更轻松,如果你觉得有用,点波关注吧!!!

http://m.toutiaocdn.net/group/6504303338681532941/?iid=18821494449&app=news_article×tamp=1514415009&tt_from=android_share&utm_medium=toutiao_android&utm_campaign=client_share

 

转载请注明:徐自远的乱七八糟小站 » 【「EXCEL」一个公式实现 表格内容根据下拉菜单变化,制表so easy】

喜欢 (0)

苏ICP备18041234号-1 bei_an 苏公网安备 32021402001397号