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

2021-09-09俞木发

电脑爱好者 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）。