數(shù)據(jù)透視表與SQL典型應用

1、數(shù)據(jù)透視表與SQL典型應用數(shù)據(jù)分析人士必殺技ETwise工作中常收集到的數(shù)據(jù)：同一文件夾下不同工作簿表頭相同的工作表這些數(shù)據(jù)放在D盤的“匯總數(shù)據(jù)”文件夾包含“財務部”、“車間部”、“銷售部”三個工作簿“銷售部”工作簿包含“銷售1部”、“銷售2部”2個工作表注：所有工作簿的工作表的表頭都是相同的1表頭相同如何把這些工作表的所有數(shù)據(jù)匯總在一起進行統(tǒng)計計算常規(guī)做法：把各個工作簿的工作表的數(shù)據(jù)一個一個地復制到同一個工作表中，然后使用數(shù)據(jù)透視表進行匯總操作過程結果l 如果這些工作表更新了數(shù)據(jù)或刪除部分數(shù)據(jù)，此時的結果是怎么樣？l 答案或許是這樣：重新復制數(shù)據(jù)，重新操作一次，心里很痛苦l(xiāng) 此時你會想到為什

2、么Excel里面沒有一種方法“只需要刷新一下即可得到更新后數(shù)據(jù)的統(tǒng)計結果”呢？后期面臨的問題期待的方法2通過SQL與數(shù)據(jù)透視表結合使用，可快速匯總同一文件夾不同工作簿不同工作表相同字段的數(shù)據(jù)，并且可以隨時刷新數(shù)據(jù)透視表SQL語句+=數(shù)據(jù)處理利器報表人士必殺技Excel必修課數(shù)據(jù)分析人士工具箱3請看如何操作：如何使用SQL與數(shù)據(jù)透視表把同一文件夾下不同工作簿表頭相同的工作表進行匯總統(tǒng)計（1）l 第一步：新建一個名字為“匯總”的工作簿。新建一個“匯總”工作簿4操作步驟2l 第二步：打開“匯總”工作簿，在“Sheet1”工作表中任意單擊一個單元格，選擇“數(shù)據(jù)”選項卡，單擊“獲取外部數(shù)據(jù)”組中的“現(xiàn)有

3、連接”按鈕，彈出“現(xiàn)有連接”對話框，單擊“瀏覽更多”按鈕?！艾F(xiàn)有連接”按鈕注：該界面是Excel2010的，對于Excel2007也適用“瀏覽更多”按鈕5操作步驟3l 第三步：在彈出的“選取數(shù)據(jù)源”對話框中找到D盤的數(shù)據(jù)源中任何一個工作簿（如：銷售部）下的工作表（如：銷售1部），單擊“確定”按鈕。數(shù)據(jù)源在D盤的“匯總數(shù)據(jù)”文件中“銷售1部”工作表“銷售部”工作簿6操作步驟4l 第四步：在彈出的“導入數(shù)據(jù)”對話框中選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，將數(shù)據(jù)放置在“現(xiàn)有工作表”的A1單元格，并單擊“屬性”按鈕，在彈出的“連接屬性”對話框下的“使用狀況”下勾選“打開文件時刷新數(shù)據(jù)”，清除在“定義”下“命

4、令文本”的文本，輸入SQL語句。選擇數(shù)據(jù)透視表和數(shù)據(jù)透視圖勾選“打開文件時刷新數(shù)據(jù)”最關鍵：單擊“屬性”按鈕在此處輸入SQL語句7操作步驟5l 第五步：在“命令文本”中輸入以下SQL語句，然后單擊“確定”按鈕。SELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售2部$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)車間部.xlsx.一車間$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)車間部.xlsx.二車間$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)財務部.x

5、lsx.財務部$輸入SQL語句8操作步驟6l 第六步：直接返回“導入數(shù)據(jù)”對話框，單擊“確定”按鈕，即可創(chuàng)建數(shù)據(jù)透視表，然后把“月”、“科目劃分”拖至“報表篩選”，“部門”拖至“行標簽”，“發(fā)生額”拖至“數(shù)值”，最終效果如下：各部門數(shù)據(jù)已經(jīng)進行匯總統(tǒng)計9附表：SQL語句原理講解（1/3）SELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$Excel使用SQL的數(shù)據(jù)源放置的路徑寫法：文件路徑.工作表$l 文件夾：D盤的“匯總數(shù)據(jù)”文件夾，其SQL寫法為“D:匯總數(shù)據(jù)”l 工作簿：而“銷售部”的Excel文件（2007以上的后綴為：xlsx)放置在該文件夾中，最后弄在一起的寫法為：

6、D:匯總數(shù)據(jù)銷售部.xlsxl 工作表：在“銷售部.xlsx”有2個工作表，其中一個為“銷售1部”工作表，最后連接在一起的寫法：D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$詳細解釋路徑寫法文件夾工作簿工作表一步一步地對路徑進行分解10附表： SQL語句原理講解（2/3）SELECT語句講解：SELECT * FROM 工作表$l 表示從工作表中提取所有的數(shù)據(jù)，其中”*”代表所有數(shù)據(jù)l SELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$：該語句的意思是從D盤的“匯總數(shù)據(jù)”文件夾中的“銷售部.xlsx”工作簿中的“銷售1部”工作表提取所有的數(shù)據(jù)UNION ALL語句講解：l 表示把兩

7、個工作表的數(shù)據(jù)連接在一起，其演示如下表1表2SELECT * FROM 表1$UNION ALLSELECT * FROM 表2$UNION ALL連接結果表11附表： SQL語句原理講解（3/3）溫馨提示：1、所有的輸入的字符及標點都是在英文狀態(tài)輸入法輸入的，中文除外；2、Excel里面使用的SQL語句與SQL Server、Oracle等大型數(shù)據(jù)庫的語法有所差異；3、Excel里面通過SQL語句結合數(shù)據(jù)透視表，可以處理300萬左右的數(shù)據(jù)，特別是數(shù)據(jù)透視表的功能，可以很方面地進行匯總統(tǒng)計分析；4、本內容來自于Excel 2010 SQL完全應用第9章；5、通過對Excel 2010 SQL完

8、全應用的學習，也可以直接應用到Access等小型數(shù)據(jù)庫;6、操作源文件及操作演示請看附件。12附表：啟用經(jīng)典數(shù)據(jù)透視表布局（應用網(wǎng)格中的字段拖放）操作13附表：若工作表的數(shù)據(jù)已經(jīng)更新（如：添加、刪除數(shù)據(jù)等），那么數(shù)據(jù)透視表中的匯總數(shù)據(jù)是否也可以同時更新呢？l 操作：選中數(shù)據(jù)透視表中任一單元格，在“數(shù)據(jù)”選項卡的“連接”組中選擇“全部刷新”或“刷新”即可。14刷新Excel 2010 SQL完全應用一書簡介l 第一本專為辦公人士、報表人士、數(shù)據(jù)分析與數(shù)據(jù)挖掘人士、經(jīng)營分析人士、Excel愛好者撰寫的有關在Excel中如何使用SQL的書籍；l 寫書初衷：全面介紹在Excel中如何使用SQL，其實SQL語句真的很簡單；l 溫馨提示：使用SQL結合數(shù)據(jù)透視