EXCEL考勤表批量查找名字 40
传统的做法是:
图二的sheet的名称就是每个部门
新建一个表,把所有部门和人员信息整合,作为vlookup的索引信息
源数据中新增一列,用vlookup函数得到部门信息
最后直接筛选部门就行了
但是Excel早就有了全新的方式来处理数据
以这个问题为基础,介绍一个excel的功能-新建查询,其核心是Power Qwery
如果你懂一点编程,Power Qwery 会非常强大,如果不懂,也没关系,基本的操作即可实现大部分功能
这种任务完全可以仅仅用鼠标解决,不需要记忆复杂的公式
首先,有一个源数据
现在需要按部门拆分这个源数据,但是源数据中没有部门信息
不过我们不对源数据做任何修改
打开部门人员信息工作簿,大概是这个样子
每个部门一个表,分别登记了 姓名-ID(考勤机)的对应关系
从数据分析的角度讲,这种表不完整,我们需要补全列-部门
现在提取一个总表
选项卡-数据-新建查询-从文件-从工作簿,选择这个工作簿
出现 导航器,勾选 选择多项,然后 勾 选需要整合的部门对应的sheet
点击右下角的加载-下拉箭头,选择 加载到...
弹出以下信息,选中 仅创建连接,然后点击 加载 按钮
然后我们会发现excel右侧出现了 工作簿查询,并且有三个查询项
现在把这三个数据整合起来
选项卡-数据-新建查询-合并查询-追加
选择两个部门
正式进入 power query 界面,现在显示的是两个部门的信息
接下来在此界面中继续进行追加操作,将其他部门也追加进来
选项卡-开始-追加查询,选择尚未追加进来的部门
继续,直到所有部门都追加完成
为每个部门查询添加部门信息列
选项卡-添加列-添加自定义列
现在已经有了部门信息
power query 操作的关键之处在于下面的 应用的步骤,步骤后面可以插入步骤,也可以撤销步骤,每个步骤都有自己的查询结果,而整个查询的结果就是最后一个步骤的查询结果
关闭并上载,系统自动新建了一个关联的sheet,显示这个查询的结果
每次刷新查询结果,都会刷新这个sheet的数据
现在试一下修改财务部人员信息,增加一个新员工 alex
刷新查询,现在sheet已经更新为最新数据
同时,系统自动创建了表,默认名称与追加的查询名称相同,都是 append1
关闭这个工作簿新建一个空白的工作簿,例如 源表+部门信息.xlsx
根据前面的方法,新建一个对 表-Append1 的查询
注意带有蓝色标题的是 表,而另一种是 Sheet
同样新建一个对源数据的查询
现在这个工作簿有两个查询(已经对查询进行了重命名)
打卡记录 上需要的 部门信息 在 姓名-ID部门 上
使用查询编辑器的 合并 功能
合并功能类似于公式函数 Vlookup,这正是我们所需要的,步骤非常简单
双击 工作簿查询 - 打卡记录,进入查询编辑器
选项卡-开始-合并-合并查询
合并查询:姓名-ID-部门
上下两个查询都选择 ID 列,然后确定
结果中新增了一列 table 数据,表头带左右箭头表示可以展开
点击那个箭头,然后选择 扩展,勾选 部门,去掉勾选 使用原始列名作为前缀
现在已经得到 包含 部门信息 的源数据,关闭并上载
至此基本完成
如果需要为每个部门建立分表
工作簿界面,右键 工作簿查询-打卡记录-引用
现在快速得到一个新的查询,而且是基于 前面的查询结果.可以对部门进行筛选,比如
关闭并上载
如果直接使用总表,可以添加切片器,方便查询
看上去步骤很多,其实当熟悉查询功能以后,很快就能完成
学习过程非常平滑,没有记忆难点
学会这个操作,类似的操作很方便就能完成,直接在查询编辑器中增加删除步骤即可
如果你愿意,以上操作可以直接在 源数据 工作簿中完成
一键出来结果
