1。地区表
Excel工作表和数据库数据表有很多区别。最有意义的是,数据库数据表可以理解为由行和列组成,而Excel工作表是由一个个单元格组成的,而这些单元格有一种独特的地址表达方式,即A1或R1C1。它们还可以形成数据连接的单元格范围,例如 A2:H8。
那么问题来了,如果我们只需要计算Excel工作表的一部分,那么用SQL应该如何表达呢?
这种问题很常见。
比如很多人的Excel标题行不在表格的第一行,而是在第二行...
下图
这个时候我们要计算A2:F列的单元格范围,这样我们就可以更方便的使用字段名而不是整个Excel工作表来处理数据...
再比如,一个表中有两个或多个“表”……这句话是什么意思?
见下图
如图所示的桌子中,既有“老师桌”,也有“学生桌”;如果我们只想让SQL引用并计算A2:D8的教师表数据...
...Excel中的SQL实际上支持将工作表的单元格区域用作“表格”。
对于上图所示的问题,SQL可以写成:
SELECT name, subject FROM [数据表$A2:D8]
查询结果如下:
在第一种情况下,我们知道数据从单元格 A2 开始,但我们不知道 F 列中的哪个单元格结束。 SQL可以写成:
SELECT Name, Hobbies FROM [学生表$A2:F]
另外,如果我们需要SQL引用计算表D:G整列的数据,那么SQL可以写成:
SELECT * FROM [学生表$D:G]
总结以上Excel工作表区域的表达方式,即工作表名称+符号$+相对引用状态的单元格地址,最后使用方括号。
只是紫色。
本节提示:
[学生表$A2:F],我们说这条语句可以引用最后一个数据存在的A2列到F列的单元格范围,但这有一个限制性前提,即它不是self -加入状态。所谓自连接是指应该使用SQL来连接工作簿本身。在自链接状态下,A2:F的最大表达量为A2:F65536行;如果此时需要的参考行数超过65536行,请使用全表模式。
2。跨工作簿的表格
一个众所周知的问题是Excel函数在处理跨工作簿数据时非常累。除少数查找参考功能(如VLOOKUP等)外,大部分功能都需要打开相关工作簿。计算用量。
是的,VLOOKUP函数不需要打开相关工作簿,可以跨工作簿使用。而且,VLOOKUP公式写完后,即使你删除了它所引用的工作簿,也不会妨碍它的计算。这是因为它已经在公式所在的工作簿中缓存了相关数据,但是VLOOKUP模式不支持复杂的函数嵌套……打个响指,有兴趣的话改天再单独聊这个。
…咳咳,回到SQL~~
…我们在当前工作簿中所有处理表之前共享的SQL语句。如果我们需要处理的数据位于其他工作簿中,那么SQL应该如何表达呢?
比如获取“学生表”中“成绩表”的所有数据,我深深佩服你。
如果是OLE DB方式(该方式请参考本系列教程第一章),SQL语句如下
SELECT * FROM [D:\EH小学\学生表.xlsx].[成绩表$]
FROM后指定的表字符串由两部分组成。第一个方括号是指定的工作簿存储路径+带后缀的完整工作簿名称,最后一个方括号是工作表名称,两个方括号之间用句点(.)连接。
如果通过VBA+ADO使用SQL语句...
敲前面的书柜警告:VBA基础不好的孩子请跳过以下内容...
与OLE DB方法相比,VBA+ADO方法灵活得多。它可以使用ADO直接创建并打开指定工作簿的链接,因此SQL语句不需要指定工作簿的完整名称等。
代码参考如下
Sub ADO_SQL()' 适用于除 2003 版本之外的更高版本的 Excel。 As Object, rst As ObjectDim strPath As String, strCnn As String, strSQL As StringDim i As LongSet cnn = CreateObject("adodb.connection")strPath = "D:\EH小学\学生表.xlsx" '指定工作簿 strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;扩展属性=Excel 12.0;数据源=" & www.sychzs.cn strCnn '创建并打开指定工作簿的链接 strSQL = "SELECT * FROM [score table$]" 'strSQL语句查询score表中所有数据 Set rst = cnn.Execute(strSQL) '执行 strSQLCells.ClearContentsFor i = 0 To rst.Fields .Count - 1Cells(1, i + 1) = rst.Fields(i).NameNextRange("a2").CopyFromRecordset rstcnn.CloseSet cnn = NothingEnd Su
上述代码第 7 行直接指定了全名需要连接的工作簿,SQL语句中无需特殊处理。
但更多时候,ADO创建的链接是一个工作簿,需要在另一个或多个工作簿中获取数据,比如两个工作簿之间的数据查询统计。这时通常使用的代码如下
Sub ADO_SQL2()' 适用于除版本 2003 之外的更高版本的 Excel。 As Object, rst As ObjectDim strPath As String, strCnn As String, strSQL As StringDim i As LongSet cnn = CreateObject("adodb.connection")strPath = ThisWorkbook.FullName '代码所在工作簿的全名 strCnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & www.sychzs.cn strCnn '创建指向代码所在的工作簿 strSQL = "SELECT * FROM [Excel 12.0;DATABASE=D:\EH Elementary School\Student Table.xlsm].[Grade Table$]"Set rst = cnn.Execute(strSQL) '执行 SQLCells。 ClearContentsFor i = 0 To rst.Fields .Count - 1Cells(1, i + 1) = rst.Fields(i).NameNextRange("a2").CopyFromRecordset rstcnn.CloseSet cnn = NothingEnd Sub
代码中的第 7 行创建到当前工作簿的链接,到另一个工作簿的链接在 SQL 语句中指定。 SQL语句如下
SELECT * FROM [Excel 12.0;DATABASE=D:\EH Elementary School\Student Table.xlsx].[Grade Table$]
FROM 指定表的字符串由两部分组成。第一个方括号中,Excel 12.0 是目标工作簿的版本号。正如我们在第2章中所说,Excel 12.0适用于除2003之外的所有Excel版本。DATABASE指定数据源工作簿的路径和名称。第二个括号是工作表名称。在两个方括号之间使用英文点。
貌似VBA+ADO方式的SQL语句比OLE DB方式复杂?确实如此,但前者也更强大。例如,它可以通过VBA对象的属性、方法、循环和判断语句来有条件地过滤工作簿和工作表……相比之下,OLE DB中的SQL语句是纯手工常量模式。当然,更重要的是,前者不仅可以检查数据,还可以添加、修改和删除数据,而后者则仅限于检查。