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

相關學習推薦：

今天聊一下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語句內也就不再需要特別處理。

但更多的情況是，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 SQL查詢中"表"的使用的詳細內容，更多請關注TW511.COM其它相關文章！