數(shù)據(jù)透視表與SQL典型應(yīng)用學(xué)習(xí)教案

1、會(huì)計(jì)學(xué)1數(shù)據(jù)數(shù)據(jù)(shj)透視表與透視表與SQL典型應(yīng)用典型應(yīng)用第一頁(yè)，共17頁(yè)。這些數(shù)據(jù)(shj)放在D盤(pán)的“匯總數(shù)據(jù)(shj)”文件夾包含“財(cái)務(wù)部”、“車間(chjin)部”、“銷售部”三個(gè)工作簿“銷售部”工作簿包含“銷售1部”、“銷售2部”2個(gè)工作表注：所有工作簿的工作表的表頭都是相同的2表頭相同第1頁(yè)/共17頁(yè)第二頁(yè)，共17頁(yè)。常規(guī)做法：把各個(gè)工作簿的工作表的數(shù)據(jù)一個(gè)一個(gè)地復(fù)制到同一個(gè)工作表中，然后使用數(shù)據(jù)透視表進(jìn)行(jnxng)匯總l如果這些工作表更新了數(shù)據(jù)或刪除部分?jǐn)?shù)據(jù)，此時(shí)的結(jié)果是怎么樣？l答案或許是這樣：重新復(fù)制數(shù)據(jù)，重新操作一次，心里很痛苦l(xiāng)此時(shí)你會(huì)想到為什么Excel里面沒(méi)

2、有一種方法“只需要刷新一下即可得到更新后數(shù)據(jù)的統(tǒng)計(jì)結(jié)果”呢？后期面臨的問(wèn)題期待的方法3第2頁(yè)/共17頁(yè)第三頁(yè)，共17頁(yè)。+=4第3頁(yè)/共17頁(yè)第四頁(yè)，共17頁(yè)。l第一步：新建一個(gè)(y )名字為“匯總”的工作簿。新建一個(gè)“匯總(huzng)”工作簿5第4頁(yè)/共17頁(yè)第五頁(yè)，共17頁(yè)。l第二步：打開(kāi)“匯總”工作簿，在“Sheet1”工作表中任意單擊一個(gè)(y )單元格，選擇“數(shù)據(jù)”選項(xiàng)卡，單擊“獲取外部數(shù)據(jù)”組中的“現(xiàn)有連接”按鈕，彈出“現(xiàn)有連接”對(duì)話框，單擊“瀏覽更多”按鈕?！艾F(xiàn)有(xin yu)連接”按鈕注：該界面(jimin)是Excel2010的，對(duì)于Excel2007也適用“瀏覽更多”按鈕

3、6第5頁(yè)/共17頁(yè)第六頁(yè)，共17頁(yè)。l第三步：在彈出的“選取數(shù)據(jù)源”對(duì)話框中找到D盤(pán)的數(shù)據(jù)源中任何一個(gè)(y )工作簿（如：銷售部）下的工作表（如：銷售1部），單擊“確定”按鈕。數(shù)據(jù)(shj)源在D盤(pán)的“匯總數(shù)據(jù)(shj)”文件中“銷售(xioshu)1部”工作表“銷售部”工作簿7第6頁(yè)/共17頁(yè)第七頁(yè)，共17頁(yè)。l 第四步：在彈出的“導(dǎo)入數(shù)據(jù)”對(duì)話框中選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”，將數(shù)據(jù)放置在“現(xiàn)有工作表”的A1單元格，并單擊“屬性”按鈕，在彈出的“連接屬性”對(duì)話框下的“使用狀況”下勾選“打開(kāi)文件(wnjin)時(shí)刷新數(shù)據(jù)”，清除在“定義”下“命令文本”的文本，輸入SQL語(yǔ)句。選擇(xunz)

4、數(shù)據(jù)透視表和數(shù)據(jù)透視圖勾選“打開(kāi)文件(wnjin)時(shí)刷新數(shù)據(jù)”最關(guān)鍵：?jiǎn)螕簟皩傩浴卑粹o在此處輸入SQL語(yǔ)句8第7頁(yè)/共17頁(yè)第八頁(yè)，共17頁(yè)。l第五步：在“命令(mng lng)文本”中輸入以下SQL語(yǔ)句，然后單擊“確定”按鈕。SELECT * FROM D:匯總數(shù)據(jù)(shj)銷售部.xlsx.銷售1部$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)(shj)銷售部.xlsx.銷售2部$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)(shj)車間部.xlsx.一車間$UNION ALLSELECT * FROM D:匯總數(shù)據(jù)(shj)車間部.xlsx.二車間$UNION

5、 ALLSELECT * FROM D:匯總數(shù)據(jù)(shj)財(cái)務(wù)部.xlsx.財(cái)務(wù)部$輸入(shr)SQL語(yǔ)句9第8頁(yè)/共17頁(yè)第九頁(yè)，共17頁(yè)。l第六步：直接返回“導(dǎo)入數(shù)據(jù)”對(duì)話框，單擊“確定”按鈕，即可創(chuàng)建數(shù)據(jù)透視表，然后把“月”、“科目劃分”拖至“報(bào)表(bobio)篩選”，“部門”拖至“行標(biāo)簽”，“發(fā)生額”拖至“數(shù)值”，最終效果如下：各部門數(shù)據(jù)已經(jīng)進(jìn)行(jnxng)匯總統(tǒng)計(jì)10第9頁(yè)/共17頁(yè)第十頁(yè)，共17頁(yè)。SELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$Excel使用SQL的數(shù)據(jù)源放置的路徑寫(xiě)法：文件路徑.工作表$文件夾：D盤(pán)的“匯總數(shù)據(jù)”文件夾，其SQL寫(xiě)法為“D

6、:匯總數(shù)據(jù)”工作簿：而“銷售部”的Excel文件（2007以上的后綴為：xlsx)放置在該文件夾中，最后弄在一起的寫(xiě)法為：D:匯總數(shù)據(jù)銷售部.xlsx工作表：在“銷售部.xlsx”有2個(gè)工作表，其中一個(gè)(y )為“銷售1部”工作表，最后連接在一起的寫(xiě)法：D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$詳細(xì)解釋(jish)路徑寫(xiě)法文件夾工作簿工作表一步一步地對(duì)路徑進(jìn)行分解11第10頁(yè)/共17頁(yè)第十一頁(yè)，共17頁(yè)。SELECT語(yǔ)句講解：SELECT * FROM 工作表$表示從工作表中提取所有的數(shù)據(jù)，其中”*”代表(dibio)所有數(shù)據(jù)SELECT * FROM D:匯總數(shù)據(jù)銷售部.xlsx.銷售1部$：

7、該語(yǔ)句的意思是從D盤(pán)的“匯總數(shù)據(jù)”文件夾中的“銷售部.xlsx”工作簿中的“銷售1部”工作表提取所有的數(shù)據(jù)UNION ALL語(yǔ)句講解(jingji)：表示把兩個(gè)工作表的數(shù)據(jù)連接在一起，其演示如下表1表2SELECT * FROM 表1$UNION ALLSELECT * FROM 表2$UNION ALL連接結(jié)果表12第11頁(yè)/共17頁(yè)第十二頁(yè)，共17頁(yè)。溫馨提示：1、所有的輸入的字符及標(biāo)點(diǎn)都是在英文狀態(tài)輸入法輸入的，中文除外；2、Excel里面使用的SQL語(yǔ)句與SQL Server、Oracle等大型數(shù)據(jù)庫(kù)的語(yǔ)法有所差異；3、Excel里面通過(guò)SQL語(yǔ)句結(jié)合數(shù)據(jù)透視表，可以處理300萬(wàn)左右的

8、數(shù)據(jù)，特別是數(shù)據(jù)透視表的功能，可以很方面地進(jìn)行匯總(huzng)統(tǒng)計(jì)分析；4、本內(nèi)容來(lái)自于Excel 2010 SQL完全應(yīng)用第9章；5、通過(guò)對(duì)Excel 2010 SQL完全應(yīng)用的學(xué)習(xí)，也可以直接應(yīng)用到Access等小型數(shù)據(jù)庫(kù);6、操作源文件及操作演示請(qǐng)看附件。13第12頁(yè)/共17頁(yè)第十三頁(yè)，共17頁(yè)。14第13頁(yè)/共17頁(yè)第十四頁(yè)，共17頁(yè)。l 操作：選中數(shù)據(jù)透視表中任一單元格，在“數(shù)據(jù)”選項(xiàng)卡的“連接(linji)”組中選擇“全部刷新”或“刷新”即可。15刷新(shu xn)第14頁(yè)/共17頁(yè)第十五頁(yè)，共17頁(yè)。l 第一本專為辦公人士、報(bào)表人士、數(shù)據(jù)分析與數(shù)據(jù)挖掘人士、經(jīng)營(yíng)分析人士、Excel愛(ài)好者撰寫(xiě)的有關(guān)在Excel中如何使用SQL的書(shū)籍；l 寫(xiě)書(shū)初衷：全面