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

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

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

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

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