HI,大家好,我是星光。上期我们聊了SQL常用查询语句中的字段查询,其简化版语法如下:
SELECT 字段名 FROM 表名
当场我就……小声说了,关键字FROM指明了要获取字段信息的表名。倘若数据源是Excel表格,则需要在表名后增加美元符号$,并用中括号包起来,例如[Sheet1$]……
事实上,上述例子是SQL In Excel 对工作表引用最简单的一种情况,也就是整表引用;此外还有单元格区域引用、跨工作簿引用等。
所以咱们今天就再来集中聊一下SQL语句中的Excel表。
1.区域成表Excel工作表和数据库的数据表有很多不同之处,最显著的地方在于,数据库的数据表可以理解为由行列构成,而Excel工作表则是由一个又一个单元格构成,且这些单元格拥有独特的地址表述方法,也就是A1或R1C1,它们还可以构成数据相连的单元格区域,例如A2:H8。
那么问题来了,如果我们只需要计算某张Excel工作表的部分区域的话,SQL该怎么表述呢?
这种问题是很常见的。
比如,很多人的Excel标题行并不是处于表格的第一行,而是第2行……
如下图所示▼
此时,我们希望计算A2:F列的单元格区域,这样我们更容易使用字段名处理数据,而不是整张Excel工作表……
再比如,一张表里存在两个或更多个“表”……这句话什么意思呢?
见下图▼
图中所示的表格中,既存在一份“教师表”,又存在一份“学生表”;如果我们只希望SQL引用计算A2:D8的教师表数据……
……Excel中的SQL其实是支持将工作表的单元格区域作为“表”使用的。
上图所示的问题,SQL可以写成:
SELECT 姓名,学科 FROM [数据表$A2:D8]
查询结果如下:
而第1种情况,我们知道数据开始于A2单元格,但不知道结束于F列的哪个单元格,SQL可以写成:
SELECT 姓名,爱好 FROM [学生表$A2:F]
另外,如果我们需要SQL引用计算表格D:G整列的数据,SQL可以写成:
SELECT * FROM [学生表$D:G]
总结以上几种Excel工作表区域的表述方式,也就是,工作表名称+美金符号$+相对引用状态下的单元格地址,最后使用中括号包起来。
就酱紫。
?本节小贴士:
[学生表$A2:F],我们说该语句可以引用从A2至F列最后存在数据的单元格区域,但这是有一个限制前提条件的,即非自连接状态。所谓自连接是指SQL应用于链接自身的工作簿。自链接状态下,A2:F的表达方式最多是A2:F65536行;倘若此时需要的引用行超过65536行,请使用整表模式。
2.跨工作簿的表一个众所周知的问题是,Excel函数在处理跨工作薄数据时很是疲态,除了个别几个查找引用类函数(例如VLOOKUP等),绝大部分函数都需要打开相关工作簿后才可以计算使用。
是的,VLOOKUP函数并不需要打开相关工作簿也可以跨工作薄使用,而且在VLOOKUP公式书写完成后,即便你把它所引用的工作簿给删了,也不妨碍它计算,这是因为它已经把相关数据缓存到了公式所在的工作簿中,不过VLOOKUP这种模式并不支持函数复杂嵌套……打个响指,关于这一点,如果你感兴趣,我们改天单独聊一下。
……咳,说回SQL~~
……我们之前分享的SQL语句都是处理当前工作簿的表格,如果我们所需要处理的数据位于其它工作簿时,SQL该怎么表述呢?
例如,获取位于计算机D盘的“EH小学”文件夹下的“学生表.xlsx”工作簿中的“成绩表”的所有数据——一口气读完这话的,不得不让在下心生佩服。
如果是OLE DB法(该方法参考本系列教程第1章),SQL语句如下▼
SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]
FROM后指定表字符串有两个部分构成,第一个中括号内是指定工作簿的存放路径+带后缀的完整工作簿名称,后一个中括号内是工作表名称,两个中括号之间使用英文点号(.)相连。
如果是通过VBA+ADO使用SQL语句……
敲书柜前方预警:VBA基础差的童鞋请自行跳过以下内容……
相比于OLE DB法,VBA+ADO的方法要灵活的多,它可以使用ADO直接创建并打开与指定工作簿的链接,因此SQL语句就无需再指定工作簿完整名称等。
代码参考如下▼
Sub ADO_SQL() '适用于除2003版以外的高版本Excel Dim cnn As Object, rst As Object Dim strPath As String, strCnn As String, strSQL As String Dim i As Long Set cnn = CreateObject("adodb.connection") strPath = "D:\EH小学\学生表.xlsx" '指定工作簿 strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & strPath cnn.Open strCnn '创建并打开到指定工作簿的链接 strSQL = "SELECT * FROM [成绩表$]" 'strSQL语句,查询成绩表的所有数据 Set rst = cnn.Execute(strSQL) '执行strSQL Cells.ClearContents For i = 0 To rst.Fields.Count - 1 Cells(1, i + 1) = rst.Fields(i).Name Next Range("a2").CopyFromRecordset rst cnn.Close Set cnn = Nothing End Su
以上代码第7行直接指定了需要连接的工作簿完整名称,SQL语句内也就不再需要特别处理。
上一篇:excel函数教程:有了ChatGPT和Office Copilot以后,我们不用再学习Excel了……吗? 下一篇: 我用Excel分析Excel后,发现了很多意想不到的的秘密
郑重声明:本文版权归原作者所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。