excel跨表查询:vlookup+indirect函数组合,你都不知道有多强大
8月底都在说身在excel查找界颇有名气的vlookup函数即将退休,微软官方也公布将迎来的是xlookup函数,据说功能也是强大的一批!目测感觉有些用法就是vlookup函数和lookup函数的结合体啊,但在目前形势来看普及该函数可能还需要一段时间,因为还需要考虑各个excel版本版的兼容性!否则发给客户或者老板的报表因为版本不一致而导致乱套了啊
所以今天还是来学习vlookup函数的固定套路吧!巧用函数公式制作工资条,尽管现在的OA智能办公给我们人事工作带来了很多便利,但是不可否认的是:技多不压身。今天用不到这个函数,说不定哪天就会遇到其他的问题需要这些函数公式解决!
职场大忌:临时抱佛脚,工作中遇到的问题不能再去翻书本,查网页……
一、准备数据
1、动态月份在A2中输入公式=DATE(2019,$N$1,1)
按住快捷键CTRL+1设置单元格格式——自定义——e年m月
2、月份来源
点击开发工具——插入数值调节框——输入最小值和最大值以及链接单元格(也可以通过设置数据的有效性实现)
二、制作第一个人的工资条
1、比如我要制作12月的工资条,在A2中输入公式=INDEX(12月!D:D,ROW(A12)/3)
index函数语法=index(工号区域,行号,列号)
第一参数返回区域:工号是位于每个表格的d列
第二参数行号:工资明细是位于第4行开始,因为每个人的工资条需要3行数据,row(a12)就是第12行,ROW(A12)/3结果就是第4行,下拉公式就是ROW(A15)/3的结果是第5行
第三参数列号:就返回1列,可以省略
当你更改月份的时候,工资表名就要变化,如果将N1单元格是无法直接带入的,需要使用一个indirect函数引用文本。
所以公式就变为=INDEX(INDIRECT($N$1&”月!d:d”),ROW(A12)/3)
2、根据工号查找他的相关信息
vlookup函数语法=vlookup(查找值,查找区域,返回列数,精确查找)
姓名:根据工号查找员工姓名,在C2中输入公式
=VLOOKUP(B2,员工信息!$C:$D,2,0)最基础的用法
出勤天数:需要跨表引用各个月份的动态天数,在D2中输入公式
=VLOOKUP($B2,INDIRECT($N$1&”月!d:af”),COLUMN(D1),0)
公式解释:
查找值是$B2中的工号查找区域:INDIRECT($N$1&”月!d:af”),即是每个月份的d:af列,这里的查找区域是变化的,使用一个indirect函数引用,这样就实现跨表查询了!返回列数:出勤天数是在查找区域的第4列,就是coulunm(d1)。如果你的原表中都是按照顺序的就可以向右复制公式即可!否则就自己手动更改返回列数或者时候match函数!精确查找:省略或者为0
三、准备裁剪线,批量填充公式
复制第一个人的工资记录即A1:M3区域——在名称框中选中a4:k51区域粘贴即可
按住ctrl+g定位空值——合并单元格——设置对齐方式为填充——输入以减号(-)后按CTRL+ENTER批量添加裁剪线了!
小编提醒:今天的分享主要是一些查找引用的函数组合,想要快速学会excel中小技巧,还是需要在实际工作当中去实践才能掌握的更牢固!更多简单实用的办公教程,欢迎关注我。
excel中可以直接套用的查找引用函数的公式
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 2305938578@qq.com 举报,一经查实,本站将立刻删除。本文网址:https://www.excelwordppt.com/word-excel-ppt/4219.html