第6章數據分析工具的應用Excel數據處理與統計初步本章學習目標1、了解加載宏的功能和用法2、掌握數據審核的方法3、掌握模擬運算表的建立方法4、掌握單變量求解的方法5、掌握方案建立和應用方法6、掌握規劃求解的方法7、掌握分析工具庫中的常用工具6.1分析工具的安裝加載宏的概念加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶可根據需要決定是否安裝。其作用是為Excel添加命令和函數，擴充Excel的功能。Excel加載宏的擴展名是.xla或.xll。在默認情況下，Excel將表6-1列出的加載宏程序安裝在如下某一磁盤位置：“MicrosoftOffice\Office”文件夾下的“Library”文件夾或其子文件夾，或Windows所在文件夾下的“Profiles\用戶名\ApplicationData\Microsoft\AddIns”文件夾下。網絡管理員也可將加載宏程序安裝到其他位置。6.1分析工具的安裝2、Excel內置加載宏加載宏描述分析工具庫添加財務、統計和工程分析工具和函數條件求和向導對于數據清單中滿足指定條件的數據進行求和計算歐元工具將數值的格式設置為歐元的格式，并提供EUROCONVERT函數以用于轉換貨幣查閱向導創建一個公式，通過數據清單中的已知值查找所需數據ODBC加載宏利用安裝的ODBC驅動程序，通過開放式數據庫互連（ODBC）功能與外部數據源相連報告管理器為工作簿創建含有不同打印區域、自定義視面以及方案的報告規劃求解對基于可變單元格和條件單元格的假設分析方案進行求解計算模板工具提供Excel的內置模板所使用的工具。使用內置模板時就可自動訪問這些工具InternetAssistantVBA通過使用Excel97InternetAssistant語法，開發者可將Excel數據發布到Web上6.1分析工具的安裝3、安裝分析工具選擇“工具”|“加載宏”菜單6.2數據審核及跟蹤分析1、概念數據審核是一種查找單元格數據錯誤來源的工具，通過它可以快速地找出具有引用關系的單元格，借此分析造成錯誤的單元格。數據審核使用追蹤箭頭，通過圖形的方式顯示或追蹤單元格與公式之間的關系。2、數據審核的方式追蹤引用單元格追蹤從屬單元格6.2數據審核及跟蹤分析3、追蹤引用單元格示例【例】某商場的銷售記錄和銷售匯總表如圖所示。查看銷售匯總表中各數據的來源是否正確。6.2數據審核及跟蹤分析4、數據有效性限制數據的范圍案例某班要建立一個成績登記表，為了減少成績輸入錯誤，可對成績表中數據的輸入類型及范圍進行限制。限制學號為8位字符，不能小于8位，也不能多于8位。限制所有學科成績為0~100之間的整數。限制科目列標題的取值范圍，如“高數”不能輸入為“高等數學”。6.2數據審核及跟蹤分析成績表示例6.2數據審核及跟蹤分析限制方法選擇“數據”|“有效性”限定數據類型限定方式設置數據大小范圍或長度6.2數據審核及跟蹤分析5、圈釋無效數據

1、首先設置數據的有效范圍；2、然后再圈釋無效數據；6.3模擬運算表1、概念模擬運算表是對工作表中一個單元格區域內的數據進行模擬運算，測試使用一個或兩個變量的公式中變量對運算結果的影響。2、模擬運算表的類型①基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；——單模擬運算表②基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響——雙模擬運算表6.3模擬運算表3、單變量模擬運算表概念在單變量模擬運算表中，輸入數據的值被安排在一行或一列中。同時，單變量模擬表中使用的公式必須引用“輸入單元格”。所謂輸入單元格，就是被替換的含有輸入數據的單元格案例假設某人正考慮購買一套住房，要承擔一筆250000元的貸款，分15年還清。現想查看每月的還貸金額，并想查看在不同的利率下，每月的應還貸金額。6.3模擬運算表1、建立模擬運算表3、C4的公式中引用了B4單元格，在實際計算時，將用B列B5:B11的值逐一代替公式中的B4。2、選擇“數據”|“模擬運算表”6.3模擬運算表案例假設某人想貸款45萬元購買一部車，要查看在不同的利率和不同的償還年限下，每個月應還的貸款金額。假設要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期限為10年、15年、20年、30年、35年時，每月應歸還的貸款金額是多少6.3模擬運算表模擬運算表的設置=PMT(B1/12,B2*12,D1)6.4單變量求解1、概念所謂單變量求解，就是求解具有一個變量的方程，Excel通過調整可變單元格中的數值，使之按照給定的公式來滿足目標單元格中的目標值.案例某公司想向銀行貸款900萬元人民幣，貸款利率是8.7%，貸款限期為8年，每年應償還多少金額？如果公司每年可償還120萬元，該公司最多可貸款多少金額？6.4單變量求解2、單變量求解方法（1）建立求解公式：（2）設置求解公式（3）求解結果6.5方案分析1、概念方案是已命名的一組輸入值，是Excel保存在工作表中并可用來自動替換某個計算模型的輸入值，用來預測模型的輸出結果。2、案例已知某茶葉公司2001年的總銷售額及各種茶葉的銷售成本，現要在此基礎上制訂一個五年計劃。由于市場竟爭的不斷變化，所以只能對總銷售額及各種茶葉銷售成本的增長率做一些估計。最好的方案當然是總銷售額增長率高，各茶葉的銷售成本增長率低。最好的估計是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長10%、6%、10%、7%。

6.5方案分析建立方案解決工作表建立方法如下，輸入下表A列、B列及第3行的所有數據；在C4單元格中輸入公式“=B4*(1+$B$16)”，然后將其復制到D4~F4；在C7中輸入公式“=B7*(1+$B$17)”,并將其復制到D7~F7；在C8中輸入公式“=B8*(1+$B$18)”，并將其復制到D8和F8；在C9中輸入公式“=B9*(1+$B$19)”，并將其復制到D9~F9；在C10中輸入公式“=B9*(1+$B$20)”，并將其復制到D10~F10；第11行數據是第7，8，9，10行數據對應列之和；凈收入是相應的總銷售額和銷售成本之差，E19的總凈收入是第13行數據之和。6.5方案分析建立方案（1）選擇“工具”|“方案”菜單單擊添加按鈕，然后在添加方案對話框中輸入方案名6.5方案分析輸入方案變量6.5方案分析建立方案報告6.5方案分析建立方案透視圖6.6線性規劃求解1、規劃求解問題的特點：問題有單一的目標，如求運輸的最佳路線、求生產的最低成本、求產品的最大盈利，求產品周期的最短時間等。問題有明確的不等式約束條件，例如生產材料不能超過庫存，生產周期不能超過一個星期等。問題有直接或間接影響約束條件的一組輸入值。6.6線性規劃求解2、Excel規劃求解問題由以下3部分組成（1）可變單元格（2）目標函數（3）約束條件6.6線性規劃求解3、案例某肥料廠專門收集有機物垃圾，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質來生產高質量的植物肥料，生產的肥料分為底層肥料、中層肥料、上層肥料、劣質肥料4種。為使問題簡單，假設收集廢物的勞動力是自愿的，除了收集成本之外，材料成本是低廉的。該廠目前的原材料、生產各種肥料需要的原材料比例，各種肥料的單價等如下各表所示。問題：求出在現有的情況下，即利用原材料的現有庫存，應生產各種類型的肥料各多少數量才能獲得最大利潤，最大利潤是多少？6.6線性規劃求解表2生產肥料的庫存原材料庫存情況現有庫存泥土4100有機垃圾3200礦物質3500修剪物1600表1各肥料成品用料及其價格表產品泥土有機垃圾礦物質修剪物單價底層肥料55547623105.00中層肥料6432452084.00上層肥料43329844105.00劣質肥料1845231857.00表3單位原材料成本單價項目單位成本泥土0.20有機垃圾0.15礦物質0.10修剪物0.236.6線性規劃求解規劃求解第一步——建立求解工作表6.6線性規劃求解規劃求解第二步——設置求解參數選擇“工具”|“規劃求解”菜單，設置求解的各項參數，如下圖所示。6.6線性規劃求解規劃求解第3步——求解6.6線性規劃求解4、修改資源肥料廠接到一個電話：只要公司肯花10元的運費就能得到150個單位的礦物。這筆交易稍稍降低了礦物質的平均價格，但這些礦物質值10元嗎？解決該問題的方法是，將庫存礦物3500改為3650，用規劃求解重新計算最大盈余。看除去￥10的成本后，盈余是否增加6.6線性規劃求解5、修改約束條件肥料廠接到一個電話，一個老顧客急需25個單位的上層肥料，公司經理在檢查打印結果后，發現沒有安排生產上層肥料。決定增加約束條件，為他生產25個單位的上層肥料。增加的約束條件6.6線性規劃求解6、規劃求解的結果報告運算結果報告6.6線性規劃求解敏感性報告6.6線性規劃求解極限報告6.6線性規劃求解7、規劃求解案例2求解不等式某工廠生產甲、乙兩種產品，假設生產甲產品1噸，要消耗9噸煤，4千瓦電力，3噸鋼材，獲利0.7萬元；生產乙產品