|
说到多文件汇总、多表汇总，解决方案有很多，可以使用数据透视表、VBA、函数、Power Query等，对于txt、csv文件，我们还可以用批处理来合并。但是我还是钟爱用SQL查询的方式来实现。这是为什么呢？ 理由一：不受数据源格式限制 假如数据源中多个表的格式不一致，比如每列的位置不一致、列数不一致等，使用上面那些方法就不好用了。但是，使用SQL查询的方式，查询结果可以统一列的排列顺序。 理由二：随心所欲地选择数据 数据源中有很多字段，但是相当多的字段其实都是冗余字段，对我们分析数据没有任何帮助。如果我们把全部字段都包含进来，那么汇总文件就会很大，每次打开都会很费时间。用SQL查询的方式就可以做到想要什么就选择什么字段，一个几十MB大小的文件，可以缩减到不到1MB，就是因为去掉了不需要的冗余字段。 理由三：方便的过滤功能 通过添加条件语句，可以方便地过滤掉不需要的数据，使汇总数据干净、简洁、明了。并且在条件语句中支持模糊查找及各种算术运算符，还可以使用子查询等，极大地丰富了条件的设置。 理由四：直接汇总分析，一步到位 可以直接在语句中指明汇总方式及按照什么字段汇总，比如求和、计数、求平均值、最大值、最小值等统计功能，直接一步到位，不需要再单独设置透视表或公式来统计。 理由五：直接创建数据透视表 在创建查询的过程中，可以直接设置创建数据透视表的方式来返回数据，不需要单独生成列表再创建数据透视表，减少操作步骤，方便快捷。 理由六：灵活、开放式的数据区域 就像用Offset指定动态区域一样，使用SQL查询也可以设置开放式的区域，数据源增、减行数，一刷新就可以反应到查询结果中。并且，在查询参数中可以指定每个表中有效的数据区域，该数据区域参数也可以设置成开放式的动态区域。 理由七：可以动态指定查询参数 可以使用单元格内容作为查询参数，动态地反映不同的查询条件所得到的结果，这里需要在MS Query中设置。 理由八：处理大数据时，速度快 如果你的数据源有几万行、十几万甚至几十万行时，甚至在加上各种条件，使用SQL查询的优势就很明显了。 原因九、十、十一……：等待你来发掘... 好了，说了这么多好处，你是否也蠢蠢欲动了呢？下面我们就来介绍一下这种方法。 SQL基础 SQL就是Structured Query Language，结构化查询语言，可以用来查询、更新、管理数据。SQL语句有很多，跟Excel结合起来，我们重点看一下Select、where、group by、order by等。 SQL查询语句示例 1、查询名称为Data的表中的所有字段的数据 Select * from [Data$] 注意：Excel工作表名称后面需要加上美元符号$，并且需要放到方括号[]中；星号*表示查询所有字段的数据。 2、查询名称为Data的表中的“销售员”和“销量”两个字段。 Select [销售员],[销量] from [Data$] 注意：字段名称可以不加方括号，但是当字段名称中间有空格、字段名是SQL语句中的保留名称等特殊情况时就必须要加方括号；在查询时，只要表中有这些字段就可以了，不用管这些字段的前后顺序。 3、查询名称为Data的表中销售员Lily的销售信息 Select * from [Data$] Where 销售员='Lily' 注意：需要指定条件时就用Where语句，多个条件用And、Or连接，分别表示并且、或；条件格式为[字段名]=“具体内容”，操作符可以是=、>、<、>=、<=；如果是模糊查询就用Like，比如：销售员 like '%Lily%' ;如果是同一个字段的多个值可以用关键字In，比如：销售员 in ('Lily','Cherry')。 4、查询名称为Data的表中销售员Lily的销售总额 Select 销售员,Sum(销量) as Lily的总销量 from [一组$] Where 销售员='Lily' group by 销售员 注意：进行汇总的时候，可以直接用Sum、Group by；在select中出现的字段，如果这个字段是汇总字段，则这个字段必须加到Group by里面；Sum函数是求和，也可用其他功能函数，比如：平均值AVG、计数Count、最大值Max、最小值Min；As可以重命名字段。 5、查询名称为Data的表中D5:F200区域的数据中销售员和销量两个字段 Select [销售员],[销量] from [Data$D5:F200] 注意：在Data$后面加上区域的地址，如果不知道最后一行是多少，或者为了让数据区域是动态的，可以省略最后一行的行号，如下。 Select [销售员],[销量] from [Data$D5:F] 6、查询名称为一组、二组、三组三个表中的所有数据 Select '一组' as 数据来源,* from [一组$] union all Select '二组' as 数据来源,* from [二组$] union all Select '三组' as 数据来源,* from [三组$] 注意：有需要合并的表，就用union all连接起来，一般最多不能超过50个。如果超过50个，也可以用子查询的方式来解决。子查询示例如下： Select * from (Select * from [一组$] union all Select * from [二组$] union all Select * from [三组$]) union all select * from [五十一组$]。在输入SQL语句时，各种符号一定要在英文状态下输入，否则你可能半天都检查不出来错在哪儿；如果使用星号*，那几个表中的数据字段顺序要一致、数据区域大小要一致，如果直接指定字段名称，则不受此限制。 7、查询名称为一组、二组、三组三个表中销售员Lily和David的所有数据 select * from (Select * from [一组$] union all Select * from [二组$] union all Select * from [三组$]) where 销售员 in ('Lily','David') 或者 Select * from [一组$] where 销售员 in ('Lily','David') union all Select * from [二组$] where 销售员 in ('Lily','David') union all Select * from [三组$] where 销售员 in ('Lily','David') 注意：可以在一个查询语句的外面在嵌套上另外一个查询语句，表示从查询结果中再次查询。 8、查询名称为Data的表中的所有字段的数据并按照日期排序 Select * from [Data$] order by 日期 注意：排序用order by，默认是升序，加上DESC表示降序，DESC是descending的缩写。降序示例：order by 日期 Desc 以上就是常用的各种代码示例。 应用实例 下面我们以实例讲解一下怎样合并多表的数据。 数据源文件中有三个表，分别是一组、二组、三组，里面有各个销售员的销售数据。 点击【数据】选项卡下面的“现有连接”。 在弹出的对话框中点击左下角的“浏览更多”按钮，找到数据所在的文件，会弹出以下对话框。 任意选择一个表并点击“确定”按钮就可以了，因为后面我们还要更改成查询语句。 在弹出的对话框中我们可以选择显示列表或者直接创建数据透视表，并且可以指定数据显示的位置。 这里我们选择“表”，这样我们就建立了链接并初步导入了数据。 在上一步中得到的数据区域，点击右键，选择“表格”-->“编辑查询”。 在打开的对话框中输入查询语句。 Select '一组' as 数据来源,* from [一组$] union all Select '二组' as 数据来源,* from [二组$] union all Select '三组' as 数据来源,* from [三组$] 这样就完成了多表汇总。 是不是感觉很简单呢？赶快动手试一试吧！ 其他数据汇总方式，请移步。 |
|
|
