Excel中使用SQL语句汇总表格
上一节我们学习了通过Excel数据透视表对多个工作表进行快速汇总,这仅仅是一种特殊的情况。我们在实际工作中经常遇到的情况是,要汇总的每个工作表中有多列文本。但是这些工作表的列数据结构完全相同,也就是列数相同,列数据项目的先后顺序也相同,但是行数可以不同,今天我们来学习下在Excel中使用SQL语句汇总表格。
使用导入数据+SQL数据查询的方法是目前这类问题最好的解决方法之一。这种方法的实质就是将各个Excel工作表当成数据库的表进行连接查询,得到一个查询数据集,这个数据集中保存有各个工作表的所有数据,然后将这个查询记录集的数据保存到工作表,或者以此为基础制作普通的数据透视表,从而得到需要的报表。
需要注意的是,为了能够利用导入数据+SQL数据查询的方法汇总多列文本情况下的多个工作表数据。每个工作表的第一行最好是数据区域的列标题。如果每个工作表的第一行不是数据区域的列标题。就需要先对数据区域定义名称,或者在SQL语句中进行特殊处理。
图1所示为3个城市的销售数据工作表“北京”、“上海”和“天津”,每个工作表保存有不同销售部门的各个产品的销售量和销售额数据。现在要把这3个工作表的数据合并到一张工作表上,或者直接制作数据透视表,以便于进行各种统计分析。
图1
下面是利用SQL数据查询的方法来汇总具有多列文本的多个工作表数据的具体方法和步骤。
1、在任何一个工作表中单击“数据”选项卡,在“获取外部数据一功能组中单击“现有连接”按钮,如图2所示,打开“现有连接”对话框。如图3所示。
图2
图3
2、单击对话框左下角的“浏览更多”按钮,打开“选取数据源”对话框,如图4所示。
图4
3、从保存该工作簿的文件夹中选择该文件,如图5所示。
图5
4、单击“打开”按钮,打开“选择表格”对话框,从表格列表中任选一个表格,并要选择“数据首行包含列标题”复选框,如图6所示。
图6
5、单击“确定”按钮,打开“导入数据”对话框,如图7所示。
图7
6、单击“属性”按钮,打开“连接属性”对话框,如图8所示。切换到“定义”选项卡,在“命令文本”文本框中输入以下SQL命令(如图9所示):
图8
图9
select '北京'as城市,*from[北京$]union all
select '上海'as城市,*from[上海$]union all
select '天津'as城市,*from[天津$]
7、单击“确定”按钮,返回到“导入数据”对话框。
根据需要,可以选择“表”单选按钮或者“数据透视表”单选按钮,并指定数据的保存位置,最后单击“确定”按钮,印可得到需要的汇总表。
图10所示是选择“表”单选按钮后得到的汇总数据清单。
图10
图11
关于SQL语句的小知识:
本节介绍了利用SQL语句对多个具有多列文本工作表数据进行查询汇总的基本方法,下面简要介绍SQL语句的基本知识。
在众多的SQL语句中,SELECT语句是使用最频繁的。SELECT语句主要用来对数据库进行查询并返回符合用户查询标准的结果数据。
SELECT语句有5个主要的子句,而FROM是唯一必需的子句。每一个子句有大量的选择项和参数。
SELECT语句的语法格式如下:
SELECT字段列表
FROM子句
[WHERE子句]
[GROUP BY子句]
[HAVING子句]
[ORDER BY子句]
SELECT语句的各项组成说明如下:
(1)字段列表
字段列表指定多个字段名称。各个字段之间用半角逗号“。”分隔。用星号“*”表示所有的字段。当包含有多个表的字段时。可用“数据表名。字段名”表示,即在字段名前标明该字段所在的数据表。
例如。“select日期,产品编号,销售量,销售额”就是选择数据表中的“日期”、“产品编号”、“销售量”和“销售额”这4个字段。还可以在字段列表中自定义宇段。例如SQL语句“select日期,产品编号,销售量,销售额。“北京'as城市from[北京$]”中,除了查询工作表“北京”中的宇段“日期”、“产品编号”、“销售量”和“销售额外”。还自定义了一个工作表中没有的字段“城市”。并将“北京”作为该宇段的数据。由于“北京”是一个文本。因此需要用单引号括起来。将某个数据保存在自定义字段的方法是利用AS属性词。即“'北京'as城市”。
(2)FROM子句
FROM子句是一个必需子句,指定要查询的数据表,各个数据表之间用半角逗号“。”分隔。
但要注意。如果是查询工作簿中的数据表,就必须用方括号将工作表名括起来,并且工作表名后要有美元符号($)。
例如。“select日期,产品编号,销售量,销售额from[北京$]".就是查询工作表“北京”中的字段“日期”、“产品编号”、“销售量”和“销售额”。
如果要查询的是Access数据库、SQL Server数据库等关系型数据库的数据表,在FROM后面直接写上数据表名即可。
(3)WHERE子句
WHERE子句是一个可选子句,指定查询的条件。可以使用SQL运算符组成各种条件运算表达式。
例如“WHERE部门=“销售部”就表示要查询的部门是“销售部”的数据。
如果条件值是数值,则直接写上数值,如“WHERE年龄>50";如果条件值是字符串。则必须用单引号“'”括起来。如“WHERE部门='销售部'”;如果条件值是日期,则必须用井号“#”或单引号“'”括起来。如“WHERE日期:=#2007-12-22#”。
(4)GROUP BY子句
GROUP BY子句是一个可选子句,指定分组项目,使具有同样内容的记录(如日期相同、部门相同、性别相同等)归类在一起。
例如,“GROUP BY性别”就表示将查询的数据按性别分组。
(5)HAVING子句
HAVING子句是—个可选子句,功能与WHERE子句类似,只是必须与GROUP BY子句一起使用。
例如,要想只显示平均工资大于5000元的记录并按部门进行分组,则可以使用子句“GROUPBY部门HAVING AVG(工资总额)>5000"。
(6)ORDERBY子句
ORDER BY子句是一个可选子句,指定查询结果以何种方式排序。排序方式有两种:升序(ASC)和降序(DESC)。如果省略ASC和DESC.则表示按升序排序。
例如。“ORDER BY姓名ASC"就表示查询结果按姓氏拼音升序排序;而“ORDER BY工资总额。年龄DESC”则表示查询结果按“工资总额”从小到大升序排序,“年龄”按从大到小降序排序。
此外,在实际工作中可能要查询工作簿中的多个工作表或者数据库中的多个数据表。这就是多表查询问题。
多表查询有很多种方法。例如。利用WHERE子句设置多表之间的连接条件。利用JOIN…ON子句连接多个表。利用UNION或者UNION ALL连接多个SELECT语句等。
Excel中使用SQL语句汇总表格时,如果我们要查询多个工作表或数据表的数据,并将这些表的数据生成一个记录集。那么可以利用UNION ALL将每个表的SELECT语句连接起来。
数据分析咨询请扫描二维码
CDA数据分析师在中国航信高科技产业园进行了面向测试度量的数据分析培训课程,培训人数近2 ...
2024-05-01CDA数据分析师走进深圳迈瑞生物医疗电子股份有限公司,在迈瑞总部展开了为期两天的培训,本次课程参训人员线上及线下近百人, ...
2024-05-01CDA数据分析师在合肥市对合肥阳光新能源科技有限公司开展了为期8天的企业内训。 合肥阳光新能源科技 ...
2024-05-01CDA数据分析师走进海尔大学,进行了《数据治理与数据中台建设的道与术》专题培训,培训现场爆满,近百人参加了此次培训。 ...
2024-05-01在中国银行苏州分行培训中心开始数据分析师培训,此次培训课程共10天内容,包括Excel、MySQL、概率论与数理统计、SPSS等内容, ...
2024-05-01从实际的业务需求出发,结合行业的典型应用特点,围绕实际的商业问题,探讨数据挖掘、机器学习模型在金融领域的应用,包括获客、信用评分、细分画像、交叉销售、反欺诈、违规识别、时序预测、运筹优化、流程挖掘九个方面,形成 ...
2024-05-01本次培训课程为线上+线下的模式,由于学员编程能力不一、部分学员没有编程基础,故提供统计学、python基 ...
2024-05-01华夏银行信用卡中心-机器学习培训 1、课程亮点 取材于业界一流企业和顶级咨询公司的行业实践;已经被证明是人人 ...
2024-05-01主 题:数据中台建设及数据分析应用主题分享 1. 数据中台市场洞察 2. 主流数据中台产品比较 3. 某企业数据中 ...
2024-05-01围绕“数据驱动”战略,全力打造我行 300 人数字化人才梯队,着力培养数字化管理人才、大数据专业团队 ...
2024-05-01在当今数据驱动的商业环境中,数据分析成为了企业决策的重要依据。通过对大量数据的收集、处理和分析,企业能够更好地理解市场 ...
2024-04-29在人工智能(AI)的世界里,提示词(Prompt)是一种强大的工具,它能够引导AI按照用户的需求产生特定的输出。本文将深入探讨AI ...
2024-04-29CDA立足未来职场,拓展前沿视野——对外经贸大学保险学院举办“三全育人大讲堂”分享行业最新动态。 ...
2024-04-294月2日,CDA数据分析师创始发起人兼协会理事长赵坚毅博士受邀在浙江万里学院举办了一场以“数字化能力在职场中的作用” ...
2024-04-29随机森林(Random Forests)现在机器学习中比较火的一个算法,是一种基于Bagging的集成学习方法,能够很好地处理分类和回归的问 ...
2022-12-23方差分析是数据分析中常用的一种统计分析方法,接下来让我们简单了解一下方差分析的基本思想和原理吧。 方差分析(Analysis ...
2022-12-23来源:关于数据分析与可视化 关于streamlit-aggrid 数据排序 表格样式的调整 数据 ...
2022-08-03作者:麦叔 定义 「把上面晦涩的概念汇成一句话就是:」 ❝ 回调函数就是一个被作为参 ...
2022-08-03现今,高学历人群日益增多,物以稀为贵的高学历光环淡去。无论本科生还是研究生,甚至博士生,求职竞争力都大不如前,就业压力越来越大。
2022-06-01某家企业10个人面试,有9个本科生……如何脱颖而出,除得体的举止和良好的沟通力外,证书成重要筹码,这也是很多人考证的关键所在。
2022-04-14