Excel在數(shù)據(jù)管理與分析中的應(yīng)用課件

1、第8章 數(shù)據(jù)審核與數(shù)據(jù)分析本章學(xué)習(xí)目標(biāo)1、了解加載宏的使用方法2、掌握數(shù)據(jù)審核的方法3、掌握模擬運(yùn)算表的建立方法4、掌握單變量求解的方法5、掌握方案分析的方法6、掌握線性規(guī)劃求解的方法7、掌握數(shù)據(jù)分析工具的應(yīng)用方法8.1 加載宏與分析工具安裝加載宏的概念加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為 Excel 添加命令和函數(shù)，擴(kuò)充Excel的功能。Excel加載宏的擴(kuò)展名是.xla或.xll。 8.1 加載宏與分析工具安裝加 載 宏描 述與 Access 鏈接創(chuàng)建可以使用 Excel 數(shù)據(jù)的 Access 窗體和報(bào)表，并將 Excel 數(shù)據(jù)導(dǎo)入到 Ac

2、cess 中。要使用 AccessLinks 加載宏，必須先安裝 Microsoft Access 分析工具庫添加財(cái)務(wù)、統(tǒng)計(jì)和工程分析工具和函數(shù)“自動(dòng)保存”加載宏以指定間隔自動(dòng)保存工作簿條件求和向?qū)?duì)于數(shù)據(jù)清單中滿足指定條件的數(shù)據(jù)進(jìn)行求和計(jì)算歐元工具將數(shù)值的格式設(shè)置為歐元的格式，并提供EUROCONVERT函數(shù)以用于貨幣轉(zhuǎn)換查閱向?qū)?chuàng)建一個(gè)公式，通過數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)ODBC 加載宏利用安裝的 ODBC 驅(qū)動(dòng)程序，通過開放式數(shù)據(jù)庫互連（ODBC）功能與外部數(shù)據(jù)源相連報(bào)告管理器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視圖以及方案的報(bào)告規(guī)劃求解加載宏對(duì)基于可變單元格和條件單元格的假設(shè)分析方

3、案進(jìn)行求解計(jì)算MS Query 加載宏將采用 Microsoft Excel 97 和 Microsoft Excel 2000 格式的外部數(shù)據(jù)區(qū)域轉(zhuǎn)換為 Microsoft Excel 5.0/95 格式，并允許使用通過以前版本 Excel 中的 Visual Basic for Applications 所創(chuàng)建的宏模板工具提供 Excel 的內(nèi)置模板所使用的工具。使用內(nèi)置模板時(shí)就可自動(dòng)訪問這些工具數(shù)據(jù)追蹤模板向?qū)赡０?，通過與之鏈接的數(shù)據(jù)庫中的記錄進(jìn)行數(shù)據(jù)跟蹤和分析更新加載宏鏈接更新對(duì) Excel 4.0 版加載宏的鏈接，以便能直接訪問 Excel 5.0/7.0、Excel 97 和

4、Excel 2000 的內(nèi)置功能Internet Assistant VBA通過使用 Excel 97 Internet Assistant 語法，開發(fā)者可將 Excel 數(shù)據(jù)發(fā)布到 Web 上2、Excel內(nèi)置加載宏8.1 加載宏與分析工具安裝3、加載宏的安裝選擇“工具”|“加載宏”菜單8.2 數(shù)據(jù)審核及跟蹤分析1、概念數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來源的工具，通過它可以快速地找出具有引用關(guān)系的單元格，借此分析造成錯(cuò)誤的單元格。數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關(guān)系。 2、數(shù)據(jù)審核的方式追蹤引用單元格 追蹤從屬單元格8.2 數(shù)據(jù)審核及跟蹤分析3、追蹤引用單元格

5、示例8.2 數(shù)據(jù)審核及跟蹤分析4、 數(shù)據(jù)有效性 限制數(shù)據(jù)的范圍 案例某班要建立一個(gè)成績登記表，為了減少成績輸入錯(cuò)誤，可以對(duì)成績表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制。限制學(xué)號(hào)為8位字符，不能小于8位，也不能多于8位。限制所有學(xué)科成績?yōu)?100之間的整數(shù)。限制科目列標(biāo)題的取值范圍，如“高數(shù)”不能輸入為“高等數(shù)學(xué)”。 8.2 數(shù)據(jù)審核及跟蹤分析成績表示例8.2 數(shù)據(jù)審核及跟蹤分析限制方法選擇“數(shù)據(jù)”|“有效性”限定數(shù)據(jù)類型限定方式設(shè)置數(shù)據(jù)大小范圍或長度8.2 數(shù)據(jù)審核及跟蹤分析5、圈釋無效數(shù)據(jù) 8.3 模擬運(yùn)算表1、概念模擬運(yùn)算表是對(duì)工作表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)行模擬運(yùn)算，測試使用一個(gè)或兩個(gè)變量的公

6、式中變量對(duì)運(yùn)算結(jié)果的影響。2、模擬運(yùn)算表的類型基于一個(gè)輸入變量的表，用這個(gè)輸入變量測試它對(duì)多個(gè)公式的影響；單模擬運(yùn)算表基于兩個(gè)輸入變量的表，用這兩個(gè)變量測試它們對(duì)于單個(gè)公式的影響雙模擬運(yùn)算表8.3 模擬運(yùn)算表1、單變量模擬運(yùn)算表概念在單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時(shí)，單變量模擬表中使用的公式必須引用“輸入單元格”。所謂輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格 案例假設(shè)某人正考慮購買一套住房，要承擔(dān)一筆250 000元的貸款，分15年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應(yīng)還貸金額。8.3 模擬運(yùn)算表1、建立模擬運(yùn)算表3、C4的公式中引用了B4

7、單元格，在實(shí)際計(jì)算時(shí)，將用B列B5:B11的值逐一代替公式中的B4。2、選擇“數(shù)據(jù)”|“模擬運(yùn)算表”8.3 模擬運(yùn)算表案例假設(shè)某人想貸款45萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個(gè)月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時(shí)，每月應(yīng)歸還的貸款金額是多少 8.3 模擬運(yùn)算表模擬運(yùn)算表的設(shè)置=PMT(B1/12,B2*12,D1)8.4 單變量求解概念所謂單變量求解，就是求解具有一個(gè)變量的方程，Excel通過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目標(biāo)單元格中的目標(biāo)值.案例某公司想

8、向銀行貸款900萬元人民幣，貸款利率是8.7%，貸款限期為8年，每年應(yīng)償還多少金額？如果公司每年可償還120萬元，該公司最多可貸款多少金額？ 8.4 單變量求解單變量求解方法1、建立求解公式：2、設(shè)置求解公式3、求解結(jié)果8.5 方案分析1、概念方案是已命名的一組輸入值，是 Excel 保存在工作表中并可用來自動(dòng)替換某個(gè)計(jì)算模型的輸入值，用來預(yù)測模型的輸出結(jié)果。 2、案例已知某茶葉公司2001年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個(gè)五年計(jì)劃。由于市場竟?fàn)幍牟粩嘧兓?，所以只能?duì)總銷售額及各種茶葉銷售成本的增長率做一些估計(jì)。最好的方案當(dāng)然是總銷售額增長率高，各茶葉的銷售成本增長率低。

9、 最好的估計(jì)是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長10%、6%、10%、7%。 8.5 方案分析建立方案解決工作表建立方法如下，輸入下表A列、B列及第3行的所有數(shù)據(jù)；在C4單元格中輸入公式“=B4*(1+$B$16)”，然后將其復(fù)制到D4F4；在C7中輸入公式“=B7*(1+$B$17)”,并將其復(fù)制到D7F7；在C8中輸入公式“=B8*(1+$B$18)”，并將其復(fù)制到D8和F8；在C9中輸入公式“=B9*(1+$B$19)”，并將其復(fù)制到D9F9；在C10中輸入公式“=B9*(1+$B$20)”，并將其復(fù)制到D10F10；第11行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對(duì)應(yīng)

10、列之和；凈收入是相應(yīng)的總銷售額和銷售成本之差，E19的總凈收入是第13行數(shù)據(jù)之和。 8.5 方案分析建立方案（1）選擇“工具”|“方案”菜單單擊添加按鈕，然后在添加方案對(duì)話框中輸入方案名8.5 方案分析輸入方案變量8.5 方案分析建立方案報(bào)告8.5 方案分析建立方案透視圖8.6 線性規(guī)劃求解1、規(guī)劃求解問題的特點(diǎn)：問題有單一的目標(biāo)，如求運(yùn)輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間等。問題有明確的不等式約束條件，例如生產(chǎn)材料不能超過庫存，生產(chǎn)周期不能超過一個(gè)星期等。問題有直接或間接影響約束條件的一組輸入值。 8.6 線性規(guī)劃求解2、Excel規(guī)劃求解問題由以下3部分組

11、成 （1）可變單元格（2）目標(biāo)函數(shù)（3）約束條件8.6 線性規(guī)劃求解3、案例某肥料廠專門收集有機(jī)物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為使問題簡單，假設(shè)收集廢物的勞動(dòng)力是自愿的，除了收集成本之外，材料成本是低廉的。該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價(jià)等如下各表所示。問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤，最大利潤是多少？ 8.6 線性規(guī)劃求解表2 生產(chǎn)肥料的庫存原材料庫存情況現(xiàn)有庫存泥土

12、4100有機(jī)垃圾3200礦物質(zhì)3500修剪物1600表1 各肥料成品用料及其價(jià)格表產(chǎn)品泥土有機(jī)垃圾礦物質(zhì)修剪物單價(jià)底層肥料55547623105.00中層肥料6432452084.00上層肥料43329844105.00劣質(zhì)肥料1845231857.00表3單位原材料成本單價(jià)項(xiàng) 目單位成本泥土0.20有機(jī)垃圾0.15礦物質(zhì)0.10修剪物0.238.6 線性規(guī)劃求解規(guī)劃求解第一步建立求解工作表8.6 線性規(guī)劃求解規(guī)劃求解第二步設(shè)置求解參數(shù)選擇“工具”|“規(guī)劃求解”菜單，設(shè)置求解的各項(xiàng)參數(shù)，如下圖所示。8.6 線性規(guī)劃求解規(guī)劃求解第3步求解8.6 線性規(guī)劃求解修改資源 肥料廠接到一個(gè)電話：只要公司

13、肯花10元的運(yùn)費(fèi)就能得到150個(gè)單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價(jià)格，但這些礦物質(zhì)值10元嗎？解決該問題的方法是，將庫存礦物3 500改為3 650，用規(guī)劃求解重新計(jì)算最大盈余?？闯ィ?0的成本后，盈余是否增加 8.6 線性規(guī)劃求解 修改約束條件 肥料廠接到一個(gè)電話，一個(gè)老顧客急需25個(gè)單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒有安排生產(chǎn)上層肥料。決定增加約束條件，為他生產(chǎn)25個(gè)單位的上層肥料。增加的約束條件8.6 線性規(guī)劃求解 規(guī)劃求解的結(jié)果報(bào)告運(yùn)算結(jié)果報(bào)告8.6 線性規(guī)劃求解2、敏感性報(bào)告8.6 線性規(guī)劃求解3、極限報(bào)告8.7 數(shù)據(jù)分析工具庫1、概述分析工具庫由Excel

14、自帶的加載宏提供，如果啟動(dòng)Excel后，在Excel的“工具”菜單中沒有“數(shù)據(jù)分析”菜單項(xiàng)，就需啟動(dòng)“工具”中的“加載宏”菜單項(xiàng)，將“分析工具庫”加載到Excel系統(tǒng)中。Excel的“分析工具庫”加載宏還提供了一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析工具庫”后才能使用 8.7 數(shù)據(jù)分析工具庫分析工具名稱說 明方差分析 包括3種類型的分析，它們是單因素方差分析、可重復(fù)雙因素分析、無重復(fù)雙因素分析相關(guān)系數(shù)分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。協(xié)方差分析用于返回各數(shù)據(jù)點(diǎn)的一對(duì)均值偏差之間的乘積的平均值。描述統(tǒng)計(jì)分析用于生成對(duì)輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有

15、關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑分析基于前期預(yù)測值導(dǎo)出相應(yīng)的新預(yù)測值，并修正前期預(yù)測值的誤差。傅里葉分析解決線性系統(tǒng)問題，并能通過快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。F-檢驗(yàn)用來比較兩個(gè)樣本總體的方差直方圖分析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計(jì)算數(shù)據(jù)的個(gè)別和累計(jì)頻率移動(dòng)平均分析基于特定的過去某段時(shí)期中變量的均值，對(duì)未來值進(jìn)行預(yù)測t-檢驗(yàn)分析雙樣本等方差假設(shè)t-檢驗(yàn)，雙樣本異方差假設(shè)t-檢驗(yàn)，平均值的成對(duì)雙樣本t-檢驗(yàn)回歸分析通過對(duì)一組觀察值使用“最小二乘法”直線擬合，進(jìn)行線形回歸分析。抽樣分析以輸入?yún)^(qū)域?yàn)榭傮w構(gòu)造總體的一個(gè)樣本z-檢驗(yàn)雙樣本平均差檢驗(yàn)2、Excel分析工

16、具庫中的工具8.7 數(shù)據(jù)分析工具庫3、 統(tǒng)計(jì)分析 Excel的分析工具庫提供了3種統(tǒng)計(jì)觀測分析的工具：指數(shù)平滑分析、移動(dòng)平均分析和回歸分析 。三種工具的用法相同，以指數(shù)平滑分析為例說明其用法。（1）在工作表的一列上輸入各時(shí)間點(diǎn)上的觀察值，如下圖A列所示。 8.7 數(shù)據(jù)分析工具庫（2）選擇“工具”“數(shù)據(jù)分析”菜單項(xiàng)，從彈出的對(duì)話框中選擇“指數(shù)平滑”。 （3）設(shè)置“輸入”、“輸出”選項(xiàng)，如下圖所示。8.7 數(shù)據(jù)分析工具庫4、假設(shè)檢驗(yàn) Excel的分析工具庫中也提供了一些假設(shè)分析工具，如：t-檢驗(yàn)、z-檢驗(yàn)、F-檢驗(yàn)。運(yùn)用檢驗(yàn)工具可以輕松地完成均值、方差的假設(shè)檢驗(yàn)。 案例某種子公司為比較兩個(gè)稻種的產(chǎn)

17、量，選擇了25塊條件相似的試驗(yàn)田，采用相同的耕種方法進(jìn)行耕種試驗(yàn)，結(jié)果播種甲稻種的13塊田的畝產(chǎn)量（單位：市斤）分別是：880、1 120、980、885、828、927、924、942、766、1 180、780、1 068、650；播種乙稻種的12塊試驗(yàn)田的畝產(chǎn)量分別是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。問這兩個(gè)稻種的產(chǎn)量有沒有明顯的高低之分。 8.7 數(shù)據(jù)分析工具庫輸入A、B兩列的樣本數(shù)據(jù)8.7 數(shù)據(jù)分析工具庫設(shè)置t檢驗(yàn)的各項(xiàng)參數(shù)8.7 數(shù)據(jù)分析工具庫5、回歸分析Excel通過對(duì)一組觀察值使用“最小二乘法”直線擬合

18、，進(jìn)行線性回歸分析，該回歸分析可同時(shí)解決一元回歸與多元回歸問題。案例下圖所示的工作表中，列出了美國19561970年間歷年的人均可支配收入和人均可消費(fèi)支出的數(shù)據(jù)。試用圖中的數(shù)據(jù)擬合模型。模型中的趨勢變量t，用于反映除人均收入之外的所有其他因素對(duì)人均消費(fèi)的影響 8.7 數(shù)據(jù)分析工具庫8.7 數(shù)據(jù)分析工具庫設(shè)置回歸分析模型的參數(shù)8.7 數(shù)據(jù)分析工具庫回歸分析結(jié)果The End第9章 外部數(shù)據(jù)的導(dǎo)入與分析第9章 外部數(shù)據(jù)的導(dǎo)入與分析Excel是一個(gè)極好的數(shù)據(jù)分析和圖表分析工具，也是一個(gè)高效的數(shù)據(jù)輸入工具。將它和專業(yè)數(shù)據(jù)庫應(yīng)用系統(tǒng)相結(jié)合，作為數(shù)據(jù)庫系統(tǒng)的輸入工具或數(shù)據(jù)分析工具，能解決數(shù)據(jù)庫系統(tǒng)應(yīng)用過程

19、中的許多問題，極大地提高工作效率。 本章學(xué)習(xí)目標(biāo)1、了解Excel與外部數(shù)據(jù)交換的方法2、掌握Excel訪問外部數(shù)據(jù)的條件3、了解MsQuery的功能及用法4、掌握MSQuery單表和多表查證方法5、掌握Excel與ACCESS、VFP交換數(shù)據(jù)的方法6、掌握Excel與文本文件交換數(shù)據(jù)的方法9.1 導(dǎo)入Web數(shù)據(jù)概述Excel提供了Web頁數(shù)據(jù)的導(dǎo)入功能 ，能夠?qū)⒁蛱鼐W(wǎng)中的網(wǎng)頁數(shù)據(jù)導(dǎo)入Excel進(jìn)行分析。導(dǎo)入Web數(shù)據(jù)的方法1）保證計(jì)算機(jī)已經(jīng)與網(wǎng)絡(luò)連接。2）選擇Excel的“數(shù)據(jù)”|“導(dǎo)入外部數(shù)據(jù)”|“新建Web查詢”，Excel會(huì)彈出“新建Web查詢”對(duì)話框，如下圖9所示 9.1 導(dǎo)入Web

20、數(shù)據(jù)3）在圖中“地址”文本框中輸入Web頁的網(wǎng)絡(luò)地址，單擊圖中的“轉(zhuǎn)到”按鈕。稍后系統(tǒng)就會(huì)在該對(duì)話框中顯示完整的Web頁。圖中顯示的是新浪網(wǎng)的股票交易Web頁。9.1 導(dǎo)入Web數(shù)據(jù)4）單擊Web頁數(shù)據(jù)區(qū)域中要導(dǎo)入數(shù)據(jù)前面的按鈕。5）單擊圖中的“導(dǎo)入”按鈕后，Excel會(huì)彈出數(shù)據(jù)存放位置的設(shè)置對(duì)話框，如圖（a）所示。圖（b）是導(dǎo)入到Excel中的Web數(shù)據(jù)。9.2 Excel與Word的數(shù)據(jù)交換 概述Miscrosoft Office系統(tǒng)內(nèi)的各種軟件之間的數(shù)據(jù)交換非常方便，通過復(fù)制和粘貼就能夠傳遞數(shù)據(jù)。Excel與Word是Microsoft Office系統(tǒng)中最常用的兩種辦公應(yīng)用軟件，Wor

21、d常用于處理文字，Excel常用于表格計(jì)算。方法（1）選中Excel中的表格數(shù)據(jù)區(qū)域，然后單擊工具欄中的“復(fù)制”按鈕（或按Ctrl+C鍵）。（2）單擊Word文檔中要插入Excel表格的位置，然后單擊Word工具欄中的“粘貼”按鈕（或按Ctrl+V鍵）。9.3 Excel與文本文件的數(shù)據(jù)轉(zhuǎn)換文本文件概述文本文件是計(jì)算機(jī)中的一種通用格式的數(shù)據(jù)文件，它可以在各軟件系統(tǒng)之間傳遞數(shù)據(jù)。幾乎所有的軟件系統(tǒng)都可直接操作文本文件。例如，Word、Excel、Access、FoxPro、Porer Point、Sybase、Oracle、C語言編輯器等，都能建立、修改或讀入文本文件。9.3 Excel與文本文

22、件的數(shù)據(jù)轉(zhuǎn)換2將Excel工作表保存為文本文件9.3 Excel與文本文件的數(shù)據(jù)轉(zhuǎn)換2導(dǎo)入文本文件到Excel工作表中步驟之一：選擇Excel的“文件”|“打開”菜單，并在“打開”對(duì)話框中指定“文本文件（*.prn）”類型。 9.3 Excel與文本文件的數(shù)據(jù)轉(zhuǎn)換2導(dǎo)入文本文件到Excel工作表中步驟之二：文本文件中各列的間隔方式 9.3 Excel與文本文件的數(shù)據(jù)轉(zhuǎn)換2導(dǎo)入文本文件到Excel工作表中步驟之三：指定各數(shù)據(jù)之間的間隔符 9.3 Excel與文本文件的數(shù)據(jù)轉(zhuǎn)換2導(dǎo)入文本文件到Excel工作表中步驟之四：指定各列數(shù)據(jù)的類型及是否導(dǎo)入。 9.4 Excel與外部數(shù)據(jù)庫1、概述將Exc

23、el與數(shù)據(jù)庫系統(tǒng)結(jié)合起來，運(yùn)用數(shù)據(jù)庫系統(tǒng)管理、組織、存儲(chǔ)數(shù)據(jù)，用Excel為數(shù)據(jù)庫輸入數(shù)據(jù)，對(duì)數(shù)據(jù)庫系統(tǒng)處理的結(jié)果數(shù)據(jù)進(jìn)行分析、制圖或作財(cái)務(wù)分析等，將會(huì)極大地提高工作效率，達(dá)到事半功倍的效果。ODBC（Open Database Connetivity）即人們常說的“開放式數(shù)據(jù)庫互連”，它是數(shù)據(jù)庫系統(tǒng)的一種接口技術(shù)，可用于不同的應(yīng)用程序，如Visual Basic、Excel、MISCROSOFT Qurey等。ODBC主要用于數(shù)據(jù)庫系統(tǒng)與其他應(yīng)用程序之間的通信，它提供應(yīng)用程序與數(shù)據(jù)庫系統(tǒng)之間進(jìn)行通信的一些技術(shù)細(xì)節(jié)。 9.4 Excel與外部數(shù)據(jù)庫ODBC驅(qū)動(dòng)程序ODBC 驅(qū)動(dòng)程序是符合“開放

24、數(shù)據(jù)庫互連”標(biāo)準(zhǔn)的數(shù)據(jù)源驅(qū)動(dòng)程序。它提供了多種數(shù)據(jù)庫管理系統(tǒng)的驅(qū)動(dòng)程序，如Access、SQL Sever等，通過這些驅(qū)動(dòng)程序，可以直接訪問相關(guān)數(shù)據(jù)庫系統(tǒng)中的數(shù)據(jù)。 數(shù)據(jù)源就是數(shù)據(jù)的來源，是要訪問的數(shù)據(jù)庫的一組存儲(chǔ)信息，它包含了用于數(shù)據(jù)庫連接所必需的信息。另外，還包括數(shù)據(jù)庫服務(wù)器的名稱和位置，用于連接數(shù)據(jù)庫的驅(qū)動(dòng)程序的名稱，以及在登錄到數(shù)據(jù)庫時(shí)所需的信息。 9.4 Excel與外部數(shù)據(jù)庫2、訪問外部數(shù)據(jù)庫的條件 1）具有訪問外部數(shù)據(jù)的權(quán)限 2）系統(tǒng)中安裝了 Microsoft Query 3）具有訪問外部數(shù)據(jù)庫的ODBC 驅(qū)動(dòng)程序 9.5 Microsoft Query介紹1 Microsof

25、t Query概述 Microsoft Query可作為一個(gè)獨(dú)立的工具單獨(dú)運(yùn)行，用以查詢各種數(shù)據(jù)庫中的數(shù)據(jù)，也可以在Excel的工作表中調(diào)用它，用于將外部數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入到Excel的工作表中。使用 Query 從外部數(shù)據(jù)庫中檢索數(shù)據(jù)到Excel的工作表，可以減少數(shù)據(jù)的重復(fù)輸入。此外，當(dāng)外部數(shù)據(jù)庫更新數(shù)據(jù)時(shí)，Miscrosoft Query還可以刷新Excel工作表中的數(shù)據(jù)，以保持與外部數(shù)據(jù)的一致性。 9.5 Microsoft Query介紹MSquery用于將外部數(shù)據(jù)查詢到Excel中分析9.5 Microsoft Query介紹2 Miscrosoft Query、數(shù)據(jù)源及ODBC9.

26、6 用MS Query查詢外部數(shù)據(jù) 案例某人在Access系統(tǒng)中建立了一個(gè)學(xué)生管理數(shù)據(jù)庫系統(tǒng)，存放在“C:My Documents”目錄下，名字是db4.mdb。該數(shù)據(jù)庫中有學(xué)生、教師、學(xué)生選課及成績等數(shù)據(jù)表。其中，學(xué)生檔案數(shù)據(jù)表如下所示?，F(xiàn)在要建立一個(gè)訪問該數(shù)據(jù)庫的數(shù)據(jù)源，并且將該數(shù)據(jù)庫中的所有數(shù)據(jù)查詢到Excel中進(jìn)行分析處理。注意：將“XCEL資料”目錄中的“學(xué)生教學(xué)管理.mdb”文件復(fù)制到C:My Document目錄中，且改名為db4.mdb。 9.6 用MS Query查詢外部數(shù)據(jù)ACCESS學(xué)生管理數(shù)據(jù)庫中的學(xué)生檔案表9.6 用MS Query查詢外部數(shù)據(jù)查詢外部數(shù)據(jù)步驟之一建立

27、數(shù)據(jù)源選擇“數(shù)據(jù)”|“獲取外部數(shù)據(jù)”|“新建數(shù)據(jù)庫查詢”菜單項(xiàng)，系統(tǒng)會(huì)彈出如圖示對(duì)話框。 選中新數(shù)據(jù)源，然后“確定”9.6 用MS Query查詢外部數(shù)據(jù)設(shè)置數(shù)據(jù)源的各項(xiàng)參數(shù)1、在此輸入數(shù)據(jù)源名字（可任取名字2、在此選擇正確的數(shù)據(jù)庫驅(qū)動(dòng)程序3、完成1、2后單擊“連接”，見下面的對(duì)話框9.6 用MS Query查詢外部數(shù)據(jù)設(shè)置數(shù)據(jù)源對(duì)應(yīng)的數(shù)據(jù)庫單擊“選擇”按鈕然后從彈出的文件選擇對(duì)話框中找到對(duì)應(yīng)的數(shù)據(jù)庫文件，選定文件后，單擊“確定”，會(huì)見到下述對(duì)話框！9.6 用MS Query查詢外部數(shù)據(jù)建立的數(shù)據(jù)源建立的數(shù)據(jù)源9.6 用MS Query查詢外部數(shù)據(jù)2、查詢外部數(shù)據(jù)步驟之二訪問數(shù)據(jù)源在上圖中，選

28、中要訪問的數(shù)據(jù)源，然后單擊“確定”按鈕，就會(huì)見到下述對(duì)話框。1、選中要訪問的數(shù)據(jù)表2、單擊9.6 用MS Query查詢外部數(shù)據(jù)3、查詢到Excel中的數(shù)據(jù)表根據(jù)“查詢向?qū)А碧崾?，不斷單擊向?qū)?duì)話框的“下一步”，會(huì)見到最后一步“向?qū)?duì)話框”選擇其中的“將數(shù)據(jù)返回Miscrosft Office Excel”，會(huì)得到下頁所示的工作表。9.6 用MS Query查詢外部數(shù)據(jù)從外部數(shù)據(jù)庫查詢到的數(shù)據(jù)表9.6 用MS Query查詢外部數(shù)據(jù)4、建立多表查詢數(shù)據(jù)庫中的數(shù)據(jù)表與Excel的工作表有許多差異，數(shù)據(jù)庫中的表要講究一定的數(shù)據(jù)規(guī)范，這種規(guī)范可能導(dǎo)致數(shù)據(jù)庫中有許多不同的數(shù)據(jù)表 。比如學(xué)生庫中的成績、

29、課程和學(xué)生檔案就分散在不同的數(shù)據(jù)表中。9.6 用MS Query查詢外部數(shù)據(jù)多表查詢的方法指定了查詢的數(shù)據(jù)源，在“查詢向?qū)е付小钡膶?duì)話框中，從多個(gè)數(shù)據(jù)表中選中需要的全部數(shù)據(jù)字段名字。在本例中，從學(xué)生表中選中學(xué)號(hào)、姓名、班級(jí)；從課程表中選中課程編號(hào)、課名；從成績表中選中成績。9.6 用MS Query查詢外部數(shù)據(jù)按向?qū)ё鐾昶溆嗖襟E，最后得到如下工作表9.7 操作MS Query1、概述Miscrosoft Query是一個(gè)非常有用的數(shù)據(jù)庫查詢軟件，它可以在Windows系統(tǒng)中獨(dú)立運(yùn)行，也可以通過加載宏的方式在Excel中運(yùn)行。 在Excel中操作MS Query的方法是：在通過查詢向?qū)耐獠肯?/p>

30、導(dǎo)的最后一步驟中，選擇“在Microsoft Query中查看數(shù)據(jù)或編輯查詢（Y）”9.7 操作MS Query2、MS Query的操作界面9.7 操作MS Query3、 添加、刪除查詢結(jié)果中的列字段 用鼠標(biāo)將這時(shí)的列字段拖放到下面的窗口中，即可將對(duì)應(yīng)列數(shù)據(jù)查詢出來！選中按Delete鍵即可刪除對(duì)應(yīng)用列的查詢結(jié)果9.7 操作MS Query4添加或刪除表窗口中的表單擊Query中的 ，可見添加表窗口選中表名，再“添加”9.7 操作MS Query5、多表查詢 多表連接關(guān)系 只有正確地建立了多表之間的連接字段后，多表查詢才能有正確的結(jié)果9.7 操作MS Query建立多表連接關(guān)系的方法方法1

31、：從一個(gè)表中將連接字段直接拖放到另一個(gè)表中的連接字段上。方法2：選擇Miscrosoft Query“表”|“連接”菜單項(xiàng)，Miscrosoft Query會(huì)彈出一個(gè)建立關(guān)系的對(duì)話框， 9.7 操作MS Query.6 條件查詢 2、條件區(qū)域，在此區(qū)域輸入查詢條件1、若不見條件區(qū)域，單擊9.7 操作MS Query3參數(shù)條件查詢?cè)跅l件中輸入，就表示對(duì)參數(shù)查詢，在內(nèi)的文字是提示信息。執(zhí)行此查詢時(shí)將顯示下面的對(duì)話框，輸入一個(gè)名字，將只查詢對(duì)應(yīng)學(xué)生的數(shù)據(jù)9.8 Excel與其他文件相互轉(zhuǎn)換概述Excel可以直接打開多種不同類型的文件，如文本文件、dBASE文件、Web網(wǎng)頁、Excel低版本的文件、模

32、板文件、Miscrosoft Query建立的查詢文件、Lotus 1-2-3文件等，然后將這些文件轉(zhuǎn)換成為Excel工作簿；Excel也可以將工作簿文件保存為文本文件、以Web頁的方式發(fā)布到網(wǎng)上，或保存為Lotus 1-2-3文件等。 9.8 Excel與其他文件相互轉(zhuǎn)換2、將Excel文件轉(zhuǎn)換成其它類型的文件選擇“文件”|“另存為”菜單命令，從保存類型的下拉列表中選擇需要的文件類型9.8 Excel與其他文件相互轉(zhuǎn)換由Excel工作表轉(zhuǎn)換成的WEB網(wǎng)頁9.9 導(dǎo)入外部數(shù)據(jù)應(yīng)用舉例【例1】 某單位有一醫(yī)療管理系統(tǒng)是用FoxPro編寫的，其中有一個(gè)匯總數(shù)據(jù)庫，假設(shè)數(shù)據(jù)庫的文件名為“醫(yī)療.dbf”，其中的數(shù)據(jù)如圖所示。將此數(shù)據(jù)表中的數(shù)據(jù)導(dǎo)入Excel9.9 導(dǎo)入外部數(shù)據(jù)應(yīng)用舉例【例2】 一家擁有7000多職工的合資企業(yè)，其人事檔案管理系統(tǒng)是用FoxPro編寫的，該公司的財(cái)務(wù)部門每個(gè)月要對(duì)公司職工的各項(xiàng)財(cái)務(wù)數(shù)據(jù)進(jìn)行匯總和對(duì)比分析，并作出各種分析報(bào)表和圖表。將此VFP數(shù)據(jù)表導(dǎo)入Excel并作出下頁的分析圖表。姓名職工號(hào)類別加班時(shí)間加班工資房屋補(bǔ)貼其