Excel – 将同一天休假的人名合并到日期的同一个单元格中

年底将至，公司大部分员工的年假还没休完。

为了鼓励大家积极休假，领导要求每位员工先按月提交自己的休假计划表，原则上公司尽量批准每个人的休假申请，但是如果同一天休假的人数超过 50%，部门负责人会请大家协商后错开休假时间。

那么问题来了，如果部门有很多人，每人一张休假表，谁能帮领导汇总成一个总表，以便 一眼便能知晓同一天有哪些人休假？

案例：

下图 1 至 5 是部门各位员工 11 月的休假计划表，每人有一个以自己名字命名的独立工作表，为了方便部门领导查看及统一规划，请将所有人的休假表汇总成一个总表，要求如下：

• 仅列出有人休假的所有日期；

• 每个日期对应的单元格内列出当天休假的所有员工姓名，以“、”分开。

效果如下图 6 所示。

解决方案：

先将原始数据表改造一下。

1. 打开“王钢蛋”的休假表 --> 选中整个数据表的第三行 --> 按 F5 或 Ctrl+G --> 在弹出的对话框中点击“定位条件”

2. 在弹出的对话框中选择“常量”--> 勾选“文本”--> 点击“确定”

此时就选中了所有填写了“休”的单元格。

3. 输入“王钢蛋”--> 按 Ctrl+Enter 回车

用同样的方式将每个工作表中的休假标识都替换成员工的姓名。

4. 选择菜单栏的“数据”-->“新建查询”-->“从文件”-->“从工作簿”

5. 在弹出的对话框中找到本休假表文件 --> 选择后点击“导入”

数据表就上传至了 Power Query。

6. 在弹出的“导航器”对话框中勾选“选择多项”--> 勾选工作簿中的所有工作表 --> 点击“转换数据”

7. 展开左边的“查询”区域 --> 选择第一个查询，即“龙淑芬”--> 选择菜单栏的“主页”-->“追加查询”

8. 在弹出的对话框中选择“三个或更多表”--> 选中“可用表”区域中除了当前表以外的所有表 --> 点击“添加”按钮，将选中的表格都添加到“要追加的表”区域 --> 点击“确定”按钮

所有查询都追加到了“龙淑芬”查询中。

9. 选择“主页”-->“将第一行用作标题”

10. 点击第一列标题旁边的筛选箭头 --> 取消选择所有数字 --> 点击“确定”

现在就隐藏了重复的日期行，仅保留每个人的休假记录。

11. 按住 Shift 键，选中所有列 --> 选择菜单栏的“转换”-->“逆透视列”

12. 点击第一个列名中的属性标识 --> 在弹出的菜单中选择“整数”，从而将日期列的属性由文本更改为数值

13. 选中第一列 --> 选择菜单栏的“主页”-->“升序排序”

14. 选中第一列 --> 选择菜单栏的“转换”-->“透视列”

15. 在弹出的对话框中按以下方式设置 --> 点击“确定”：

• 值列：选择“值”

• 展开“高级选项”--> 在“聚合值函数”区域选择“不要聚合”

透视结果中，有多人休假的单元格都显示为错误值，所以还要再设置一下。

16. 在代码的最右边括号前添加以下代码，表示将文本合并起来，用“、”分隔开：

each Text.Combine(_,'、')

* 请注意：除了顿号分隔符以外，其他所有符号都必须是英文半角符号。

现在错误值就被正确替换成了多个员工姓名，每个姓名之间以“、”隔开。

17. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至...”

18. 在弹出的对话框中选择“仅创建连接”--> 点击“加载”

现在回到了 Excel。

19. 在右侧的“工作簿查询”区域中选中“龙淑芬”--> 右键单击 --> 在弹出的菜单中选择“加载到...”

20. 在弹出的对话框中选择“表”-->“新建工作表”--> 点击“加载”

工作簿中就会新建一个工作表，Power Query 中的合并结果表就加载到了其中。

将表格中的所有单元格设置为自动换行，适当调整单元格的高度与宽度，就是最终需要的结果。