歸納總結Excel SQL查詢中"表"的使用

本篇文章給大家帶來了關于excel的相關知識,其中主要介紹了關于sql查詢中表的使用,包括了區域成表、跨工作簿的表等等相關內容,希望對大家有幫助。

歸納總結Excel SQL查詢中"表"的使用

相關學習推薦:excel

今天聊一下SQL語句中的excel表。

1.區域成表

Excel工作表和數據庫的數據表有很多不同之處,最顯著的地方在于,數據庫的數據表可以理解為由行列構成,而Excel工作表則是由一個又一個單元格構成,且這些單元格擁有獨特的地址表述方法,也就是A1或R1C1,它們還可以構成數據相連的單元格區域,例如A2:H8。

那么問題來了,如果我們只需要計算某張Excel工作表的部分區域的話,SQL該怎么表述呢?

這種問題是很常見的。

比如,很多人的Excel標題行并不是處于表格的第一行,而是第2行……

如下圖所示

歸納總結Excel SQL查詢中"表"的使用

此時,我們希望計算A2:F列的單元格區域,這樣我們更容易使用字段名處理數據,而不是整張Excel工作表……

再比如,一張表里存在兩個或更多個“表”……這句話什么意思呢?

見下圖

歸納總結Excel SQL查詢中"表"的使用

圖中所示的表格中,既存在一份“教師表”,又存在一份“學生表”;如果我們只希望SQL引用計算A2:D8的教師表數據……

……Excel中的SQL其實是支持將工作表的單元格區域作為“表”使用的。

上圖所示的問題,SQL可以寫成:

SELECT?姓名,學科?FROM?[數據表$A2:D8]

查詢結果如下:

歸納總結Excel SQL查詢中"表"的使用

而第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語句內也就不再需要特別處理。

但更多的情況是,ADO創建的鏈接是一個工作簿,需要獲取的數據在另一個或多個工作簿,例如兩個工作簿之間的數據查詢統計。此時通常使用的代碼如下

Sub?ADO_SQL2() '適用于除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?=?ThisWorkbook.FullName?'代碼所在工作簿的完整名稱 strCnn?=?"Provider=Microsoft.ACE.OLEDB.12.0;Extended?Properties=Excel?12.0;Data?Source="?&?strPath cnn.Open?strCnn?'創建到代碼所在工作簿的鏈接 strSQL?=?"SELECT?*?FROM?[Excel?12.0;database=D:EH小學學生表.xlsm].[成績表$]" Set?rst?=?cnn.Execute(strSQL)?'執行SQL 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?Sub

代碼中第7行創建了當前工作簿的鏈接,SQL語句中又指定了另外一個工作簿的鏈接。SQL語句如下

SELECT?*?FROM?[Excel?12.0;DATABASE=D:EH小學學生表.xlsx].[成績表$]

FROM指定表的字符串有兩部分組成。第一個中括號中,Excel 12.0是目標工作簿的版本號,第2章時我們講過,Excel 12.0適用于除了2003以外的所有Excel版本。DATABASE指定的是數據源工作簿的路徑和名稱。第2個中括號內是工作表名。兩個中括號之間使用英文點號相連。

看起來似乎VBA+ADO方法的SQL語句比OLE DB法更復雜?確實如此,不過前者的功能也更強大。比如,它可以通過VBA對象的屬性、方法,循環和判斷語句等,有條件的篩選工作簿和工作表……相比之下,OLE DB中的SQL語句就是純手工常量模式了。當然,更重要的是,前者不但可以查數據,還可以增改刪數據,后者卻只限于查。

相關學習推薦:excel

? 版權聲明
THE END
喜歡就支持一下吧
點贊7 分享