选择性地显示Excel表格中的隐私数据

2021-09-09 03:48俞木发
电脑爱好者 2021年24期
关键词:加班费单元格姓名

俞木发

实例:在下面的这张截图中,上半部分是某公司的加班原始记录数据(含每个员工的信息),现在需要在给某员工(如李四)的截图中实现如图片下半部分所示的效果(图1)。要求如下:

·同名的员工要进行区分,比如生产1部和生产2部均有名为“李四”的员工。

·选择相应的员工后只高亮显示该员工的加班记录,其他员工的数据自动隐藏。

·选择员工后自动统计出其加班时长和加班费。

·选择员工后通过Excel生成截图,以便发送给员工核对。根据上述的要求,我们可以在Excel中依次执行下述操作:

1. 对员工进行唯一性的处理

从图1中可以看到,同名员工所在的部门是不同的,因此对员工唯一性的标注可以通过“部门+姓名”的方式来实现。在原始数据表的D列后插入一个新列,接着定位到E2单元格并输入公式“=C2&D2”,下拉后就可以将同名员工进行区分了。同上,在I、M列执行相同的操作,完成员工的标注(图2)。

这里为了方便对员工进行选择,可以使用数据验证添加下拉列表的方法实现快捷选择。因为员工的唯一标识数据位于E、I、M列,先定位到O2单元格并输入公式“=E2”,下拉公式直到显示为“0”,然后在显示为“0”的单元格中继续输入公式“=I2”,同上在下一个为“0”的单元格中输入公式“=M2”,下拉后在O列中就可以列出所有加班员工的数据了(图3)。

接下来就是对O列的员工数据去重。选中O2:O22数据区域并复制,定位到Q2单元格,依次点击“开始→粘贴→粘贴数值→值”,然后再选中Q2:Q22区域中的数据,依次点击“数据→删除重复值→当前选定区域排序→删除重复项”,删除重复项后再将空值单元格删除,这样在Q2:Q12区域得到的就是不重复的加班员工的数据。最后定位到P2单元格,依次点击“数据→数据验证→设置→序列”,将“来源”选择为“=$Q$2:$Q$12”,在P2单元格的下拉列表中就可以选择加班员工了(图4)。

2. 高亮顯示员工的数据

要实现在选择了指定的员工后,其数据的高亮显示,可通过条件格式来完成。选中C1: N 8数据区域,依次点击“开始→ 条件格式→新建规则→ 使用公式确定要设置格式的单元格”,输入公式“=$E2=$P$2”(注意E2使用相对引用,P 2使用绝对引用),符合条件的单元格设置为绿色填充,应用的区域是C2:F8。操作同上,再输入公式“=$I2=$P$2”和“= $ M 2 = $ P $ 2”,应用的区域是“= $ G $ 2 : $ J $ 8”和“=$K$2:$N$8”(图5)。

公式解释:

这里使用三个公式依次对加班数据中的“部门:加班时间”区域进行条件应用,当我们在P2单元格中选择了员工姓名后,从E2单元格开始,在上述的数据区域中所有包含该员工的数据就应用上述设置的条件,即将员工所在的单元格填充为高亮绿色显示。

为了便于查看数据,现在可以将E、M、I和Q列设置为隐藏,接着选中C2:N8数据区域,将其字体颜色设置为白色。这样当我们在P2单元格的下拉列表中选择相应的员工后,C2:N8区域中符合条件格式的单元格就会自动以高亮绿色显示,而其他员工的数据则会自动“消失”(实际字体为白色)(图6)。

3. 自动统计加班数据

定位到A10单元格并输入公式“= P2&"加班费小计:"”,即在A10单元格中显示P2单元格新选择员工的数据,并将其和“加班费小计:”字样连接起来。再定位到D10单元格并输入公式“=SUMIF($E$2:$N$8,P2,$F$2:$N$8)”,表示使用SUMIF函数,以P2单元格显示的姓名作为条件,对指定区域F2:N8的加班时间进行统计。定位到D11单元格并输入公式“=D10*14”,完成加班费的统计。这样,我们只要在P2单元格中选择了员工姓名后,就可以自动完成加班时间和加班费的统计了(图7)。

4. 完成截图 发送以供核对

选中A1:N11数据区域并复制,定位到A17单元格,接着依次点击“开始→粘贴→其他粘贴选项→链接的图片”,这样在下方就会出现加班费的截图(选择不同的员工姓名后图片内容会同步发生变化)。最后,我们只需将这张图片使用QQ、微信等发送给员工进行核对即可(图8)。

猜你喜欢
加班费单元格姓名
公司支付了加班费就可以随意延长加班时间吗?
梁潮印笺·姓名章戢孴
梁潮篆痕·姓名类集
玩转方格
玩转方格
自愿加班,有没有加班费?
浅谈Excel中常见统计个数函数的用法
姓名的『姓』字为什么是『女』旁?
英国医生最高获得37.5万英镑的高额加班费
加班费