用Power Query合并Excel工作簿中的所有工作表

在Excel中，常需整合多张数据表，如部门工资或商场柜台业绩。旧版依赖数据透视表或VBA处理，前者逐表添加繁琐，工作簿增页则需重置源数据；后者则需要深入学习。新版本的Power Query工具显著简化了此类操作。

1、 数据源，如图，数据源有三个结构相同的工作表。

2、 新建工作簿，并从数据菜单——获取或转换数据（版本不同，菜单显示位置可能有所不同，本经验截图根据2019版本），启动Power Query（以下简称PQ）导入数据。PQ可以从工作表中的一个区域或定义的表格，或一个工作表，或一个工作簿，甚至整个文件夹导入Excel工作表的数据，当然还可以导入文本文件、网页数据等。这里是从工作簿。选择“获取数据”——“从工作簿”后，打开导入文件对话框，点浏览，找到要合并工作表的工作簿后，双击或点下面的“导入”，将工作簿数据专篇PQ，这时会启动PQ导航器。

3、 在PQ导航器中，左侧显示导入的文件，如图，工作簿“数据源.xlsx”以文件夹图标显示，并在后面显示数字3，表示工作簿中有3个工作表。下面展开显示3个工作表的名称，点击任何一个，在右侧中显示预览。如果只要导入某个工作表进行数据处理，就选择该工作表。上面有个“选项多项”选项，勾选后，可在下面选择多个工作表。如果工作簿中的工作表张数是固定的，或只要对某些工作表进行数据处理，就选择多项，并选择要进行处理的工作表；如果是要对工作簿的所有工作表都进行处理，且以后工作表的数量可能有增加或减少时，就不勾选“选择多项”，而是直接点工作簿名称图标选择工作簿。这里我们选择工作簿（右侧预览是空的）。再点右下方的“编辑”，进入PQ编辑界面。也可以随便选择一个工作表，在后续的编辑中删除除“源”以外的步骤，结果一样。

4、 PQ编辑界面有点类似于Excel，在PQ编辑界面完成对数据的各种处理操作，每一个步骤都会在右侧记录下来，并把步骤作为得到结果的名称被引用到下一个步骤中，这有点类似于Excel中的名称，当然也可以对步骤进行重命名，如果你喜欢的话。如导入的数据被命名为“源”，接着“删除的列”就对是对“源”进行操作。现在3个工作表都导入了，先来看一下导入的数据，第1列是工作表名称，第2列是Data，下面的数据都是Table，实际上每个工作表的数据都在这个Table中，上面标题Data右边有一个左右双向的箭头，表示该数据可以展开，展开后就显示出每个Table中的具体内容。现在随便点选一个Sheet对应的Talbe（不是双击展开），下面就会出现该工作表数据的预览。再右边几列是工作表的一些属性。从下面预览中显示的内容看，原有的标题变成了数据，而PQ自动添加数据标题：Column1、Column2等，这显然不是我们所需要的。可以通过修改一下“源”这个步骤的M语句，来实现原有标题仍然是标题。PQ中每个步骤都是有相对应的M语句，也就是M语句决定了这个步骤完成的操作。熟练掌握M语句后，可以直接编写出M语句，完成需要的所有操作。如图，菜单栏下类似于Excel公式编辑栏位置就是M语句显示和编辑的地方，如果没有显示出来，就在视图菜单中勾选“编辑栏”后即可显示。现在只要把“源”这个步骤中的如图所示的“null”改成：“true”，原标题就还原到标题位置了。5而导入的多余一些属性并不是我们需要的，单击不需要的列中左边的列，按住Shift键后，再单击最右边的列，就全部选择了不需要列，在上面的工具条中选择“删除列”将其删除。现在只保留工作表名称和Data列。

6、 既然工作表数据都在Table中，那么现在对Data进行展开，Data右边有一个双向箭头图标，表示可以展开。点这个图标就将展开下面各个工作表中数据。展开时，先弹出一个对话框，供选择要选择工作表中的哪些列进行展开，这里选择全部。下面的“使用原始列名做前缀”的对勾可以去掉，否则会在原每个标题前加上一个Data的前缀。点确定后，得到了3个工作表展开的数据。7如果只是要把所有工作表的数据放到一个工作表中，那么现在就达到要求了。这相当于把要合并工作簿中的各个工作表的数据复制后粘贴到一个工作表中。前面一列是工作表名称，即可以知道各数据来自哪个工作表，也可以根据自己的需要，决定是否删除这一列。这时选择文件菜单——关闭并上载至，还有一个关闭并上载，就是直接上载到一个新的工作表中。

8、 这时返回到Excel工作表中，上载有多个选择，表：就是把PQ得到数据导入到Excel工作表中，数据透视表：就是把PQ中的数据作为数据源，在Excel中生成数据透视表，类似的有数据透视图。最后一项是仅连接，就是只生成一个连接，数据暂时不导入到Excel工作表中，一般用于多个PQ查询合并或追加查询。如图，这里选择“表”，并选择在现有工作表中，选择A1为数据导入位置。确定后，即把PQ中合并了数据导入到Excel工作表中，且是与原有工作簿相关联的。