使用数据透视表直接汇总多个文件的数据

使用数据透视表直接汇总多个文件的数据有很多情况下，我们需要汇总多个文件中的数据，这些文件中的工作表可能有相同的格式，也可能有不同的格式但是包含同种类型的数据。今天给大家介绍一个直接用SQL查询来汇总多个文件的数据并生成数据透视表的方法。假设有三个分公司的销售数据如下，存放在目录“C:\销售数据”中。这三个文件格式一样，文件名分别为“济南.xlsx”、“南京.xlsx”、“青岛.xlsx”，数据都存放在“Sheet1”工作表中，标题都在第二行，数据都是从第一列开始。这种格式一致的数据汇总起来就比较省事儿了。操作步骤：1、点击【插入】选项卡中的“数据透视表”，打开“创建数据透视表”对话框，选择其中的“使用外部数据”，然后点击“选择连接”按钮。2、在弹出的对话框中点击“浏览更多”按钮，然后选择三个销售数据表中的任意一个。这时会弹出“选择表格”对话框，这里只有一个表格，我们直接点击“确定”即可，这时就生成了一个空白的数据透视表。其实这一步选择哪个表都无所谓，因为我们后面要更改查询语句。3、选中数据透视表，点击【数据】选项卡中的“属性”，打开“连接属性”对话框，我们转到“定义”选项卡，这里有“连接字符串”和“命令文本”等信息。我们下一步就是要直接更改这个“命令文本”4、在命令文本中直接输入以下SQL语句。select*from[C:\销售数据\济南.xlsx].[Sheet1$A2:D]unionallselect*from[C:\销售数据\南京.xlsx].[Sheet1$A2:D]unionallselect*from[C:\销售数据\青岛.xlsx].[Sheet1$A2:D]点击“确定”按钮后，我们可以看到数据透视表中出现了相应的字段。我们就可以将“产品”添加到行字段区域，将“一月”、“二月”、“三月”添加到值字段区域了。最终得到汇总数据的数据透视表如下。5、总结一下，关键点在SQL语句。数据文件路径“C:\销售数据\济南.xlsx”前后加了方括号[]，可以替换成“·”，这个符号在Tab键上方，跟波浪号~在一起。替换后如下：select*from`C:\销售数据\济南.xlsx`.[Sheet1$A2:D]unionallselect*from`C:\销售数据\南京.xlsx`.[Sheet1$A2:D]unionallselect*from`C:\销售数据\青岛.xlsx`.[Sheet1$A2:D]工作表名称前后也需要前后加[]，工作表和文件路径之间需要加上英文的句号；工作表名称后面跟美元符号$，再后面可以指明数据所在的区域，可以指明数据从哪一行开始，否则就认为从第一行开始，也可以指明到哪一行结束，否则就会读取所有记录；如果要添加字段，比如记录数据来源，我们在select后面添加语句，比如“'济南'as城市”，完整的语句如下：select'济南'as城市,*from[C:\销售数据\济南.xlsx].[Sheet1$A2:D]unionallselect'南京'as城市,*from[C:\销售数据\南京.xlsx].[Sheet1$A2:D]unionallselect'青岛'as城市,*from[C:\销售数据\青岛.xlsx].[Sheet1$A2:D]如果数据源格式不一致，但是都包含“产品”、“一月”、“二月”、“三月”这几个字段，那我们也可以指明需要读取的字段名称，假设我们任意增加了几个字段，数据放在“C:\销售数据\格式不一致示例”目录中，SQL语句如下，这里注意数据区域扩展了，所以每个工作表后面跟着的数据区域范围也要相应地调整。select'济南'as城市,产品,一月,二月,三月from[C:\销售数据\格式不一致示例\济南.xlsx].[Sheet1$A2:E]unionallselect'南京'as城市,产品,一月,二月,三月from[C:\销售数据\格式不一致示例\南京.xlsx].[Sheet1$A2:F]unionallselect'青岛'as城市,产品,一月,二月,三月from[C:\销售数据\格式不一致示例\青岛.xlsx].[Sheet1$A2:F]通过以上讲解，我相信应该可以应付大部分多个文件汇总的情况。小伙伴们，你学会了吗？--End--