Excel與數(shù)據(jù)處理(第5版)電子教案-8

第8章數(shù)據(jù)分析工具的應用Excel與數(shù)據(jù)處理第5版本章學習目標1、了解加載宏的功能和用法2、掌握數(shù)據(jù)審核的方法3、掌握模擬運算表的建立方法4、掌握單變量求解的方法5、掌握方案建立和應用方法6、掌握規(guī)劃求解的方法7、掌握分析工具庫中的常用工具8.1數(shù)據(jù)審核及跟蹤分析1、概念數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯誤來源的工具，通過它可以快速地找出具有引用關系的單元格，借此分析造成錯誤的單元格。數(shù)據(jù)審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關系。2、數(shù)據(jù)審核的方式追蹤引用單元格追蹤從屬單元格8.1.1追蹤引用單元格引用單元格是指被其他單元格中的公式引用的單元格，即為指定公式提供數(shù)據(jù)的單元格。當使用“追蹤引用單元格”工具時，Excel會用藍色追蹤箭頭指明活動工作表中為公式提供數(shù)據(jù)的單元格。如果選定單元格引用了其他工作表或工作簿中的單元格，Excel會用黑色追蹤箭頭從工作表圖標指向選定的單元格?！纠?.1】某商場的銷售記錄和銷售匯總表如圖所示。查看銷售匯總表中各數(shù)據(jù)的來源是否正確。8.1.1追蹤引用單元格單擊公式選項卡單擊包括有公式的單元格單擊追蹤引用單元格8.1.2追蹤從屬單元格從屬單元格如果某個單元格中的公式引用了其它單元格，那么此單元格就稱為從屬單元格。當公式中所引用單元格的值發(fā)生變化時，公式所在單元格（從屬單元格）中的值也會隨之變化。例如，若B3單元格中包含公式“=A3+A4”，則B3就是A3和A4的從屬單元格。當A3或A4單元格的值發(fā)生變化時，B3單元格的值就會發(fā)生變化。8.1.1追蹤引用單元格單擊公式選項卡單擊包括要追蹤的單元格單擊追蹤從屬單元格8.1.2數(shù)據(jù)有效性檢驗數(shù)據(jù)有效性數(shù)據(jù)有效性檢驗是Excel為減少錯誤、核查數(shù)據(jù)正確性而提供的一種數(shù)據(jù)檢驗工具。利用該工具可以設置單元格數(shù)據(jù)輸入的類型和范圍，對錯誤的輸入數(shù)據(jù)進行告警，并拒不接受，把錯誤限制在輸入階段。此外，它還能對已經(jīng)完成的數(shù)據(jù)表設置有效性檢驗規(guī)則，并據(jù)此標識出其中的錯誤數(shù)據(jù)。案例【例8.2】某班要建立一個成績登記表，為了減少成績輸入錯誤，可對成績表中數(shù)據(jù)的輸入類型及范圍進行限制。限制學號為8位字符，不能小于8位，也不能多于8位。限制所有學科成績?yōu)?~100之間的整數(shù)。限制科目列標題的取值范圍，如“高數(shù)”不能輸入為“高等數(shù)學”。8.1.3數(shù)據(jù)有效性檢驗成績表示例8.1.2數(shù)據(jù)有效性檢驗限制方法單擊“數(shù)據(jù)”選項卡中的“數(shù)據(jù)有效性”按鈕限定數(shù)據(jù)類型限定方式設置數(shù)據(jù)大小范圍或長度8.1.2數(shù)據(jù)有效性檢驗圈釋無效數(shù)據(jù)

1、按前面介紹的方法首先設置數(shù)據(jù)的有效值范圍:0-100；2、單擊“數(shù)據(jù)有效性”右邊的下箭頭，選擇“圈釋無效數(shù)據(jù)”Excel就會將不符合有效性規(guī)則的數(shù)據(jù)圈釋出來8.1.2數(shù)據(jù)有效性檢驗設置輸入提示信息和錯誤警告信息這些信息都需要通過“數(shù)據(jù)有效性”對話框進行設置。8.2模擬運算表1、概念模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，測試使用一個或兩個變量的公式中變量對運算結果的影響。2、模擬運算表的類型①基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；——單模擬運算表②基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響——雙模擬運算表8.2.1單變量模擬運算表單變量模擬運算表概念在單變量模擬運算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時，單變量模擬表中使用的公式必須引用“輸入單元格”。所謂輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格案例【例8.3】假設某人正考慮購買一套住房，要承擔一筆250000元的貸款，分15年還清。現(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的應還貸金額。8.2.1單變量模擬運算表1、建立模擬運算表3、C4的公式中引用了B4單元格，在實際計算時，將用B列B5:B11的值逐一代替公式中的B4。2、選擇“數(shù)據(jù)”→“假設分析”

→“數(shù)據(jù)表”8.2.1單變量模擬運算表【例8.4】對于例8.3而言，如果要查看在同等利息情況下，分別貸款￥250000，￥400000，￥550000，￥800000的每月還貸金額，則可建立如圖8.5所示的模擬運算表。1、在B4輸入模擬計算公式：=PMT($A$4/12,12*15,B1)2、將B4中的公式向右復制3、選中A4:E12，建立模擬運算表其中，引用列為$A$48.2.2雙變量模擬運算表單變量模擬運算表只能解決一個輸入變量對一個或多個公式計算結果的影響，如果想查看兩個變量對公式計算的影響就需要使用雙變量模擬運算表。案例假設某人想貸款45萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個月應還的貸款金額。假設要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時，每月應歸還的貸款金額是多少8.2.2雙變量模擬運算表單擊“數(shù)據(jù)”→“假設分析”→“數(shù)據(jù)表”=PMT(B1/12,B2*12,D1)8.3單變量求解1、概念所謂單變量求解，就是求解具有一個變量的方程，Excel通過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目標單元格中的目標值.案例【例8.6】某公司想向銀行貸款900萬元人民幣，貸款利率是8.7%，貸款限期為8年，每年應償還多少金額？如果公司每年可償還120萬元，該公司最多可貸款多少金額？8.3單變量求解2、單變量求解方法（1）建立求解公式：（2）設置求解公式（3）求解結果單擊“數(shù)據(jù)”→“假設分析”→“單變量求解”8.4方案分析1、概念方案是已命名的一組輸入值，是Excel保存在工作表中并可用來自動替換某個計算模型的輸入值，用來預測模型的輸出結果。2、案例【例8.7】已知某茶葉公司2001年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎上制訂一個五年計劃。由于市場竟爭的不斷變化，所以只能對總銷售額及各種茶葉銷售成本的增長率做一些估計。最好的方案當然是總銷售額增長率高，各茶葉的銷售成本增長率低。最好的估計是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長10%、6%、10%、7%。

8.4方案分析建立方案解決工作表建立方法如下1、輸入下表A列、B列及第3行的所有數(shù)據(jù)；2、在C4單元格中輸入公式“=B4*(1+$B$16)”，然后將其復制到D4~F4；3、在C7中輸入公式“=B7*(1+$B$17)”,并將其復制到D7~F7；4、在C8中輸入公式“=B8*(1+$B$18)”，并將其復制到D8~F8；5、在C9中輸入公式“=B9*(1+$B$19)”，并將其復制到D9~F9；6、在C10中輸入公式“=B9*(1+$B$20)”，并將其復制到D10~F10；7、第11行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對應列之和；凈收入是相應的總銷售額和銷售成本之差，E19的總凈收入是第13行數(shù)據(jù)之和。8.4方案分析1、單擊“數(shù)據(jù)”2、單擊“模擬分析”|”方案管理器“3、單擊“添加“4、輸入“方案名稱“5、指定“方案可變單元格“6、確定后見到下頁“8.4方案分析輸入方案變量8.4方案分析建立方案報告8.4方案分析建立方案透視圖8.5加載宏工具的安裝加載宏的概念加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為Excel添加命令和函數(shù)，擴充Excel的功能。Excel2010加載宏的擴展名是.xlam。在默認情況下，Excel將表9-1列出的加載宏程序安裝在如下某一磁盤位置：“MicrosoftOffice\Office”文件夾下的“Library”文件夾或其子文件夾，或Windows所在文件夾下的“Profiles\用戶名\ApplicationData\Microsoft\AddIns”文件夾下。網(wǎng)絡管理員也可將加載宏程序安裝到其他位置。8.5加載宏工具的安裝2、Excel內(nèi)置加載宏加載宏描述分析工具庫添加財務、統(tǒng)計和工程分析工具和函數(shù)歐元工具將數(shù)值的格式設置為歐元的格式，并提供EUROCONVERT函數(shù)以用于轉換貨幣查閱向?qū)?chuàng)建一個公式，通過數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)ODBC加載宏利用安裝的ODBC驅(qū)動程序，通過開放式數(shù)據(jù)庫互連（ODBC）功能與外部數(shù)據(jù)源相連報告管理器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面以及方案的報告規(guī)劃求解對基于可變單元格和條件單元格的假設分析方案進行求解計算模板工具提供Excel的內(nèi)置模板所使用的工具。使用內(nèi)置模板時就可自動訪問這些工具InternetAssistantVBA通過使用Excel97InternetAssistant語法，開發(fā)者可將Excel數(shù)據(jù)發(fā)布到Web上8.5加載宏工具的安裝3、安裝分析工具（1）單擊“文件”→“選項”。（2）在彈出的“Excel選項”對話框中，單擊“選項”，然后在“管理”框中，選擇“Excel加載項”，然后單擊“轉到”。Excel會彈出圖示的“加載宏”對話框。（3）在“加載宏”框?qū)υ捴?，選中要安裝的加載宏。8.6規(guī)劃求解1、規(guī)劃求解問題的特點：問題有單一的目標，如求運輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時間等。問題有明確的不等式約束條件，例如生產(chǎn)材料不能超過庫存，生產(chǎn)周期不能超過一個星期等。問題有直接或間接影響約束條件的一組輸入值。8.6規(guī)劃求解2、Excel規(guī)劃求解問題由以下3部分組成（1）可變單元格（2）目標函數(shù)（3）約束條件8.6.2建立規(guī)劃求解模型3、案例【例8.8】某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質(zhì)來生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料4種。為使問題簡單，假設收集廢物的勞動力是自愿的，除了收集成本之外，材料成本是低廉的。該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價等如下各表所示。問題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫存，應生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤，最大利潤是多少？8.6.2建立規(guī)劃求解模型表2生產(chǎn)肥料的庫存原材料庫存情況現(xiàn)有庫存泥土4100有機垃圾3200礦物質(zhì)3500修剪物1600表1各肥料成品用料及其價格表產(chǎn)品泥土有機垃圾礦物質(zhì)修剪物單價底層肥料55547623105.00中層肥料6432452084.00上層肥料43329844105.00劣質(zhì)肥料1845231857.00表3單位原材料成本單價項目單位成本泥土0.20有機垃圾0.15礦物質(zhì)0.10修剪物0.238.6.2建立規(guī)劃求解模型規(guī)劃求解第一步——建立求解工作表規(guī)劃求解第二步——設置求解參數(shù)8.6.2建立規(guī)劃求解模型2、單擊“規(guī)則求解”若此處無“規(guī)則求解”命令，須按8.5節(jié)的介紹安裝“規(guī)則求解”加載宏單擊“數(shù)據(jù)”選項卡2、設置“規(guī)則求解參數(shù)”8.6.3規(guī)劃求解規(guī)劃求解第3步——求解8.6.4修改資源和約束條件肥料廠接到一個電話：只要公司肯花10元的運費就能得到150個單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價格，但這些礦物質(zhì)值10元嗎？解決該問題的方法是，將庫存礦物3500改為3650，用規(guī)劃求解重新計算最大盈余?？闯ィ?0的成本后，盈余是否增加8.6.4修改資源和約束條件修改約束條件肥料廠接到一個電話，一個老顧客急需25個單位的上層肥料，公司經(jīng)理在檢查打印結果后，發(fā)現(xiàn)沒有安排生產(chǎn)上層肥料。決定增加約束條件，為他生產(chǎn)25個單位的上層肥料。增加的約束條件8.6.5規(guī)則求解報告規(guī)劃求解的結果報告運算結果報告8.6.5規(guī)則求解報告敏感性報告8.6.5規(guī)則求解報告極限報告8.6.6求解不等式求解不等式

【例8.9】某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設生產(chǎn)甲產(chǎn)品1噸，要消耗9噸煤，4千瓦電力，3噸鋼材，獲利0.7萬元；生產(chǎn)乙產(chǎn)品1噸，要消耗4噸煤，5千瓦電力，10噸鋼材，獲利1.2萬元。按計劃國家能提供給該廠的煤為360噸，電力200千瓦，鋼材300噸，問應該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤？8.6.6求解不等式假設生產(chǎn)甲種產(chǎn)品x1噸，生產(chǎn)乙種產(chǎn)品x2噸，這個問題的數(shù)學建模如下。最大利潤是求S=0.7x1+1.2x2的最大值。用EXCEL規(guī)劃求解如下：8.6.6求解不等式不等式工作表設計8.6.6求解不等式8.7數(shù)據(jù)分析工具庫1、概述分析工具庫由Excel自帶的加載宏提供，如果啟動Excel后，在Excel的“數(shù)據(jù)”選項卡中沒有“分析”邏輯組，就需通過“文件”→“選項”打開“Excel選項”對話框，再通過其中的“加載項”→“管理”，將“分析工具庫”加載到Excel系統(tǒng)中。Excel的“分析工具庫”加載宏還提供了一些統(tǒng)計函數(shù)、財務函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了“分析工具庫”后才能使用8.7數(shù)據(jù)分析工具庫分析工具名稱說明方差分析包括3種類型的分析，它們是單因素方差分析、可重復雙因素分析、無重復雙因素分析相關系數(shù)分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關系。協(xié)方差分析用于返回各數(shù)據(jù)點的一對均值偏差之間的乘積的平均值。描述統(tǒng)計分析用于生成對輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有關數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑分析基于前期預測值導出相應的新預測值，并修正前期預測值的誤差。傅里葉分析解決線性系統(tǒng)問題，并能通過快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。F-檢驗用來比較兩個樣本總體的方差直方圖分析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計算數(shù)據(jù)的個別和累計頻率移動平均分析基于特定的過去某段時期中變量的均值，對未來值進行預測t-檢驗分析雙樣本等方差假設t-檢驗，雙樣本異方差假設t-檢驗，平均值的成對雙樣本t-檢驗回歸分析通過對一組觀察值使用“最小二乘法”直線擬合，進行線形回歸分析。抽樣分析以輸入?yún)^(qū)域為總體構造總體的一個樣本z-檢驗雙樣本平均差檢驗2、Excel分析工具庫中的工具8.7.1統(tǒng)計分析統(tǒng)計分析工具庫Excel的分析工具庫提供了3種統(tǒng)計觀測分析工具：指數(shù)平滑分析、移動平均分析和回歸分析三種工具用法相同，以指數(shù)平滑分析為例說明其用法。（1）在工作表的一列上輸入各時間點上的觀察值，如下圖A列所示。

8.7.1統(tǒng)計分析（2）選擇“數(shù)據(jù)”|“數(shù)據(jù)分析”命令，從彈出的對話框中選擇“指數(shù)平滑”。（3）設置“輸入”、“輸出”選項，如下圖所示。8.7.2假設分析假設檢驗Excel的分析工具庫中也提供了一些假設分析工具，如：t-檢驗、z-檢驗、F-檢驗。運用檢驗工具可以完成均值、方差的假設檢驗。案例【例8.10】某種子公司為比較兩個稻種的產(chǎn)量，選擇了25塊條件相似的試驗田，采用相同的耕種方法進行耕種試驗，結果播種甲稻種的13塊田的畝產(chǎn)量（單位：市斤）分別是：880、1120、980、885、828、927、924、942、766、1180、780、1068、650；播種乙稻種的12塊試驗田的畝產(chǎn)量分別是：940