Excel數(shù)據(jù)源的多表合并和SQL查詢

1、Excel多表合并和查詢是一個應用很廣泛的問題。下面是一個簡單的例子，我們需要從兩張數(shù)據(jù)表里，得出每個行業(yè)的股票波動率平均值。第一個數(shù)據(jù)表保存了股票和行業(yè)的對應關系，有兩列，第一列為股票名，第二列為每只股票對應的行業(yè)。第二張表保存了各個股票在各個交易日的收盤價和前收盤價，有四列，第一列是股票名，第二列為交易日，第三列和第四列分別為股票在這個交易日的前收盤價和收盤價。上面例子的一個簡單且常用的方法是使用增加輔助列、averageifs、數(shù)據(jù)透視表之類的函數(shù)。但當數(shù)據(jù)比較大或者邏輯結構比較復雜時，這些函數(shù)計算的速度比較慢。楊文博在博客上提到類似的問題，并給出了一個基于Python的解決方案。但這樣

2、需要安裝Python,同時修改起來也比較麻煩。下面以上述案例為例子演示Excel內(nèi)部如何支持基于多個表格數(shù)據(jù)進行查詢。1、單擊菜單數(shù)據(jù)-自其它來源-來自MicrosoftQuery,之后彈出一個窗口2、選擇ExcelFiles*點擊確定后，進入選擇工作簿界面。此處選擇包含數(shù)據(jù)的文件。它可以是你正在編輯的這個文件。再次點擊確定后你將進入到以下界面。在該界面上點擊左下方的選項”，在彈出的表選項勾選上系統(tǒng)表”。確定后可以看到查詢向?qū)ё髠葧鰜硪恍?shù)據(jù)表。每一行都是一個數(shù)據(jù)表其中后綴為$'的表為Excel文件中的工作表，其它為Excel文件中的名稱所定義的數(shù)據(jù)區(qū)域。點擊左側的“號可以展開看這個

3、數(shù)據(jù)表的列名。3、接下來有幾種方式，一種方式是利用查詢向?qū)Э梢暬亟?shù)據(jù)庫查詢語句，另一種方式是直接輸入數(shù)據(jù)庫查詢語句。我一般使用后者。這時候在上面的查詢向?qū)?選擇列"中隨意勾選上一列，不斷下一步，直到點擊完成確定。此時會彈出以下方框：一,導入數(shù)提!S3.!請選曾讀麴據(jù)在工作簿中的顯示方式口。表e竟數(shù)據(jù)透視裹©；2J數(shù)據(jù)透視盤和數(shù)據(jù)透視表晶僅創(chuàng)建連接勒數(shù)闞該貴位置e現(xiàn)有工作表的：,國【確定I曬4、此時可以選擇數(shù)據(jù)顯示為表格形式或者數(shù)據(jù)透視表，并選擇數(shù)據(jù)放置位置。激活剛才選擇的位置，點擊菜單數(shù)據(jù)-屬性”，在彈出的窗口再次選擇連接屬性，進入下圖的界面:在命令文本區(qū)域輸入新的S

4、QL查詢語句，點擊確定，再刷新數(shù)據(jù)區(qū)域即可。SQL查詢語句是整個操作的核心。這里不談具體SQL語句怎么寫，而是介紹下如何引用各種數(shù)據(jù)表。有以下幾種方法：?通過名稱引用。比如如果定義一個數(shù)據(jù)區(qū)域為Industry,那么select*fromindustry這樣是可行的。這種方法最多支持65535行數(shù)據(jù)，當數(shù)據(jù)行數(shù)過多時，Excel會提示找不到該數(shù)據(jù)表。同一張工作表里可以有多個數(shù)據(jù)表，通過定義不同的名稱去引用。?通過工作表名引用。比如一個工作表名為Quotes,那么select*from'Quotes$'這樣是可行的。這里工作表名后面的$號表示這是一個工作表。工作表可以包含高達100萬行數(shù)據(jù)。但同一個工作表內(nèi)只能有一個數(shù)據(jù)表?可以通過數(shù)據(jù)表的地址進行引用。比如select*from'Quotes$A1:B10000'。?上面的引號可以用中括號代替。比如select*fromQuotes$A1:B10000?如果數(shù)據(jù)表不在目前工作的文件內(nèi)，需要在上面的數(shù)