批量给每个人创建一个 Excel 工作表，再做一个所有人自动汇总表

原创 我是小花椒 Excel学习世界 2022-08-22 19:00 发表于上海

前一篇推文中，我用的案例有很多工作表，每个工作表以人名命名。后台有读者留言问如何大批量创建这种工作表？又如何汇总这些工作表的数据？

案例：

下图 1 是所有员工每个月的业绩表，请设置如下：

• 为每个人建立一个单独的工作表，工作表中以相同的模板展示每个人的单月和总计业绩；

• 请在最前面加一个汇总表，自动提取出每个人的总业绩

效果如下图 2、3 所示。

解决方案：

1. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”

2. 在弹出的对话框中选择“现有工作表”及所需上传至的位置 --> 点击“确定”：

3. 在右侧的“数据透视表字段”区域按以下方式拖动字段：

• 筛选：“姓名”

• 行：“月份”

• 值：“交易笔数”

4. 选中数据透视表的任意单元格 --> 选择菜单栏的“数据透视表分析”-->“选项”-->“显示报表筛选页”

5. 因为只有一个筛选项“姓名”，所以不用再做选择，直接在弹出的对话框中点击“确定”即可。

以人名命名的所有工作表都批量创建好了，每个工作表中都有一个数据透视表，筛选出了该员工的业绩表。

6. 按住 Shift 键 --> 选中所有以姓名命名的工作表

7. 选中整个数据透视表区域 --> 复制并粘贴为值

8. 根据需要设置数据表的格式。

所有工作表中的数据表都批量设置成了同样格式。

现在设置汇总表。

9. 将 Sheet1 拖动到第一个工作表前面，按以下方式重新拖动字段：

• 行：“姓名”

• 值：“交易笔数”

10. 删除原始数据表 --> 复制数据透视表 --> 在 A1 单元格粘贴为值 --> 按需设置数据表的格式

11. 在 B2 单元格中输入以下公式：

=INDIRECT(A2&"!B5")

公式释义：

• indirect 函数的作用是返回由文本字符串指定的引用；

• 参数 A2 不加引号，表示引用的是 A2 单元格的内容，即“陈小娟”；而 & 后面加了引号，则引用的是文本字符串；

• 完整的参数就是 陈小娟!B5，此处的“!"是工作表名称的终结符号；

• 整个公式的作用就是引用工作表“陈小娟”中 B5 单元格的内容

我们去“陈小娟”工作表查看一下，引用结果正确。

12. 向下拖动单元格，复制公式。

随机抽查一下“诸葛钢铁”的结果，也完全正确。