职场老手说用上ERP就像用了机械键盘,回不来了,因为ERP通过简单的拖拽就能快速定制信息表。毕竟ERP是块老奶酪,需要点学习成本。今天咱们就试试用一个公式证明excel中的下拉拖拽也能想用就用。先看效果:
自动检测是否填入信息,启用标题栏样式
标题栏信息可根据基础信息表下拉选择
填入姓名关键字,自动生成对应序号及人员信息关键字,并填充表格样式
行信息可以自动拖动排序,拖动后对应行序号重新按照排序后位置更新,人员信息跟随关键字变化
列信息支可以自由删减,增加,移动 ,编辑,编辑后的列信息不会出现错误
关键字删除后,其他信息和表格样式,列标签删除后,自动删除颜色填充样式,重新填入信息后,以上功能依然有效
上述功能虽然说得天花乱坠,其实的重点在于信息的自动生成,至于表格自动填充边框和样式,简单的一个条件格式就完成了。信息自动生成仅仅依赖一个index符合match函数就能实现,而且这个公式仅需要在第一个单元格内编写一次就完成了,剩下的就是放纵双击或者拖拽了
要理解这个公式的终极奥义,必须先知道这个公式讲的是啥
index,插入函数,将某个范围内某行某列的值插入到当前位置
书写规范:
index(被选插入数据所属于范围,数据所在范围内的行,数据所在范围内的列)
动画演示:
match,匹配函数,将被选关键字 在 关键字所属范围内的序值 填写到当前位置
书写规范:
match(关键字,关键字所在范围,匹配度)
动画演示:
这两个函数看起来都很简单,但是如果将这两个函复合到一起,将产生能够完全取代并超越vlookuop的强大函数,能同时在行和列间以多条件匹配的形式精确找到你要的数据信息
书写规范
index(被选插入数据所属于范围,match(匹配行),match(匹配列)
动画演示:
函数输入完成。剩下的就是双击或者拖拽了,但在你放肆拖拽之前,必须要是正确的设置地址锁定,才能让生成的数据准确无误
地址指的是代表单元格位置或者范围的字符,如B1,D5:D8 这样的,ABC....Z 表示列地址,1234....100表示行地址,在没有设置地址锁定($表示锁定)的情况下在我们拖动句柄的时候,地址符会根据拖动的方向变化,
横向拖动变化列地址(A1→Z1),纵向拖动的时候变化行地址(A1→A100):
在设置锁定符$后(快捷键F4),会让锁定的行列地址不随句柄的拖动而变化,$符号在谁前面表示锁定谁
$A$1,表示行列均锁定,拖动行列地址均不变化
A$1,表示列行锁定,仅在横向拖动时,列地址会按照A$1→Z$1变化,纵向拖动时,A$1不会发生变化
¥A1,表示列锁定,仅在纵向拖动时,行地址会按照$A1→$A100变化,横向拖动时,$A1不会发生变化
说了那么多,不知你看懂没,要是没看懂我也不知道咋描述了,可能度娘那有更好的答案。
接着我们看我们的公式:
index(被选插入数据所属于范围,match(匹配行),match(匹配列)
首先,范围一定不能变化,设置范围均为行列锁定;
对于行匹配,关键字姓名 一直存在于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图
这个可以使用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版本以上的数据验证使用说明写的十分清楚易懂,一目了然,简单演示如下:
aoskil
联系客服