数据透视表：一个sheet自动生成多个sheet，再自动生成多个Excel文件

Attention: 本分享基于win7系统和office 2013。

由于需要整理信息，某单位M需要各学院补充一些数据。为了保密，单位M希望让每个学院仅补充自己的数据，不影响其他学院的信息补充工作。为此，单位M需要将原有数据表（如图1）的数据分成不同的excel文件分发给各个学院。

图1显示了一张21行8列的数据表。编号列、项目名称列和立项时间列中空白的内容含义如下：比如，5-8行中A、B、C列空白，表示5-8行的A、B、C列分别与第4行的A、B、C列相同。单位M需要各学院补充F至H列缺失的信息。

在数据处理开始之前，务必要将A、B、C列重复的数据补充完整得到图2——具体原因见图29。

选中数据区域，点击“插入”->”数据透视表”。在“创建数据透视表”窗口最好选择将数据透视表放入新工作表，得到图5界面。

将数据透视表字段依次逐个添加至图5右下角“行”区域。得到图7。

显然图7的展示形式并不如图2直接、美观，因此需要将图7调整为类似图2的显示形式。

如图8，选中数据透视表中任意一个单元格，单击鼠标右键，选择“数据透视表选项”，选择“显示”，在“经典数据透视表布局”前的框中打勾，点确定，生成图9的界面。

Tips：注意图9和图10，你就会明白为什么之前我会说“将数据透视表字段依次逐个添加至图5右下角“行”区域。”因为点击的顺序和“行”区域显示的顺序一致，也和图9与图10中各列的位置一致。

我们看图11的界面。在工具栏“数据透视表工具”中选择“设计”。

在“分类汇总”中选择“不显示分类汇总”，得到图12

在“总计”中选择“对行和列禁用”，得到图13

在“报表布局”中选择“重复所有项目标签”，得到图14.

图14中空值的字段显示“空白”，这反倒让我们难以只直观看出哪些地方是需要我们补充的，为此需要稍作处理。

利用“替换”功能——快捷键“Ctrl+H”，将“（空白）”改为“空格”，得到图16

做完以上这些步骤才算完成了准备工作，下面进入正题。

我们先将图16中这一个sheet根据立项单位划分为不同sheet。如图17将“立项单位”字段拖入数据透视表筛选器，在A1立项单位那里的下拉箭头选择“选择多项”，点击确定。

如图18，在“分析”中点击“选项”，选择“显示报表筛选页”，点击确定，生成了不同学院的sheet，如图20.

下面将这一个excel中的多个sheet拆分为不同excel。按“Alt+F11”，弹出如图21的VB窗口。

点击VB窗口的“插入”，选择“模块”，复制如下代码并粘贴至新出现的窗口中（如图23），点击保存，选择“否”。

Sub SaveSeparately0

Dim sht As Worksheet

Application.ScreenUpdating = False

ipath = ThisWorkbook.Path &" '

For Each sht In Sheets

sht.Copy

ActiveWorkbook.SaveAs ipath & sht.Name

ActiveWorkbook.Close

Next

Application.ScreenUpdating = True

End Sub

选择合适的位置保存新生成的excel（注意保存位置的名称会成为新生成sheet的命名前缀，如保存在“新建文件夹”，新生成的sheet名称为“新建文件夹+sheet名称”）。点击运行按钮（如图26）生成多个sheet。

效果如图27。

由于无法在数据透视表中直接编辑数据，所以需要复制“数据透视表”，“选择性粘贴”为“数值”，即可编辑。

填一个上文挖的坑~~~

Tips：若在处理数据之前未将重复数据进行补充——比如图29中显示的事未补全“项目名称”这一列时的情况，则会导致出现较多“空白”项，显示不够清楚且需要后续再进行调整，不够便捷。

END