1、Excel與數據處置與數據處置本章教學目的與要求本章教學目的與要求1 1、掌握宏的加載方法、掌握宏的加載方法2 2、掌握追蹤從屬或援用單元格的方法、掌握追蹤從屬或援用單元格的方法3 3、掌握限定單元格數據的范圍及圈釋無效數據的、掌握限定單元格數據的范圍及圈釋無效數據的 運用方法運用方法4 4、掌握模擬運算表及變量求解的運用、掌握模擬運算表及變量求解的運用5 5、掌握方案的建立和運用、掌握方案的建立和運用6 6、掌握規劃求解工具的運用、掌握規劃求解工具的運用7 7、了解假設檢驗和回歸分析等工具的運用、了解假設檢驗和回歸分析等工具的運用本章重點、難點及學時數本章重點、難點及學時數n重點：重點：n掌

2、握數據審核的方法掌握數據審核的方法n掌握模擬運算表的運用掌握模擬運算表的運用n掌握單變量求解的運用掌握單變量求解的運用n掌握方案的運用掌握方案的運用n掌握規劃求解的運用掌握規劃求解的運用n難點：難點：n掌握規劃求解的運用掌握規劃求解的運用n學時數：學時數：n1212學時上機學時上機6 6學時學時本章目錄本章目錄7.1 分析工具的安裝分析工具的安裝7.2 數據審核及跟蹤分析數據審核及跟蹤分析7.3 模擬運算表模擬運算表7.4 單變量求解單變量求解7.5 方案分析方案分析7.6 線性規劃求解線性規劃求解7.7 數據分析工具庫數據分析工具庫小結小結思索與練習思索與練習7.1 分析工具的安裝分析工具的

3、安裝1、加載宏的概念、加載宏的概念加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶加載宏是一種可選擇性地安裝到計算機中的軟件組件，用戶可根據需求決議能否安裝。其作用是為可根據需求決議能否安裝。其作用是為 Excel 添加命添加命令和函數，擴展令和函數，擴展Excel的功能。的功能。Excel加載宏的擴展名是加載宏的擴展名是.xla或或.xll。在默許情況下，在默許情況下，Excel將下表列出的加載宏程序安裝在如下將下表列出的加載宏程序安裝在如下某一磁盤位置：某一磁盤位置：“Microsoft OfficeOffice文件夾下文件夾下的的“Library文件夾或其子文件夾，或文件夾或其子文件

4、夾，或 Windows 所所在文件夾下的在文件夾下的“Profiles用戶名用戶名Application DataMicrosoftAddIns文件夾下。網絡管理員也文件夾下。網絡管理員也可將加載宏程序安裝到其他位置。可將加載宏程序安裝到其他位置。 7.1 分析工具的安裝分析工具的安裝2、Excel內置加載宏內置加載宏加加 載載 宏宏描描 述述分析工具分析工具庫庫添加財務、統計和工程分析工具和函數添加財務、統計和工程分析工具和函數條件求和條件求和向導向導對于數據清單中滿足指定條件的數據進行求和對于數據清單中滿足指定條件的數據進行求和計算計算歐元工具歐元工具將數值的格式設置為歐元的格式，并提供將

5、數值的格式設置為歐元的格式，并提供EUROCONVERT函數以用于轉換貨幣函數以用于轉換貨幣查閱向導查閱向導創建一個公式，通過數據清單中的已知值查找創建一個公式，通過數據清單中的已知值查找所需數據所需數據ODBC 加加載宏載宏利用安裝的利用安裝的 ODBC 驅動程序，通過開放式數據驅動程序，通過開放式數據庫互連（庫互連（ODBC）功能與外部數據源相連）功能與外部數據源相連7.1 分析工具的安裝分析工具的安裝報告管理報告管理器器為工作簿創建含有不同打印區域、自定義視面為工作簿創建含有不同打印區域、自定義視面以及方案的報告以及方案的報告規劃求解規劃求解對基于可變單元格和條件單元格的假設分析方對基于

6、可變單元格和條件單元格的假設分析方案進行求解計算案進行求解計算模板工具模板工具提供提供 Excel 的內置模板所使用的工具。使用內置的內置模板所使用的工具。使用內置模板時就可自動訪問這些工具模板時就可自動訪問這些工具Internet Assistant VBA通過使用通過使用 Excel 97 Internet Assistant 語法，開語法，開發者可將發者可將 Excel 數據發布到數據發布到 Web 上上7.1 分析工具的安裝分析工具的安裝3、安裝分析工具、安裝分析工具選擇選擇“工具工具|“加載宏菜單加載宏菜單在對話框中選擇所需工具，在對話框中選擇所需工具，按確定按確定 注：假設在安裝注

7、：假設在安裝EXCEL系統時沒有安裝加載宏，那么必需重系統時沒有安裝加載宏，那么必需重新啟動新啟動EXCEL的安裝程序，選擇其中的的安裝程序，選擇其中的“添加添加/刪除命刪除命令，安裝令，安裝EXCEL的加載宏。的加載宏。7.2 數據審核及跟蹤分析數據審核及跟蹤分析1、概念、概念數據審核是一種查找單元格數據錯誤來源的工具，快速地找出數據審核是一種查找單元格數據錯誤來源的工具，快速地找出具有援用關系的單元格，借此分析呵斥錯誤的單元格。具有援用關系的單元格，借此分析呵斥錯誤的單元格。數據審核運用追蹤箭頭，經過圖形的方式顯示或追蹤單元格與數據審核運用追蹤箭頭，經過圖形的方式顯示或追蹤單元格與公式之間

8、的關系。公式之間的關系。 2、數據審核的方式、數據審核的方式追蹤援用單元格追蹤援用單元格見見ch7.xls追蹤援用單元格追蹤援用單元格 操作方法：選定菜單操作方法：選定菜單“工具工具“審核審核 顯示顯示審核審核工具欄工具欄選擇要追蹤援用的含公式單元格選擇要追蹤援用的含公式單元格“審核工具欄中審核工具欄中“追蹤援追蹤援用單元格按鈕用單元格按鈕再次單擊再次單擊“追蹤援用單元格按鈕提供數追蹤援用單元格按鈕提供數據的下一級單元格據的下一級單元格移去援用單元格追蹤箭頭：移去援用單元格追蹤箭頭： 操作方法：選擇操作方法：選擇“審核工具欄中審核工具欄中“移去援用單元格中追蹤箭移去援用單元格中追蹤箭頭頭7.2

9、 數據審核及跟蹤分析數據審核及跟蹤分析追蹤從屬單元格追蹤從屬單元格見見ch7.xls追蹤從屬單元格追蹤從屬單元格某單元格公式援用了其它單元格，那么該單元格為從屬單元格。某單元格公式援用了其它單元格，那么該單元格為從屬單元格。 操作方法：選定菜單操作方法：選定菜單“工具工具“審核審核 顯示顯示審核審核工具欄工具欄選擇要追蹤從屬單元格的單元格選擇要追蹤從屬單元格的單元格“審核工具欄中審核工具欄中“追蹤從追蹤從屬單元格按鈕屬單元格按鈕再次單擊再次單擊“追蹤從屬單元格按鈕提供從屬追蹤從屬單元格按鈕提供從屬的的單元格的的單元格移去援用單元格追蹤箭頭：移去援用單元格追蹤箭頭： 操作方法：選擇操作方法：選擇

10、“審核工具欄中審核工具欄中“移去從屬單元格中追蹤箭移去從屬單元格中追蹤箭頭頭7.2 數據審核及跟蹤分析數據審核及跟蹤分析3、 數據有效性數據有效性數據有效性：對數據進展檢驗和檢查的有效方法，把錯誤數據有效性：對數據進展檢驗和檢查的有效方法，把錯誤限制在數據輸入階段。限制在數據輸入階段。 限定數據類型和有效范圍：限定數據類型和有效范圍： 如：限定數據大小范圍、日期的范圍、輸入字符的個數、如：限定數據大小范圍、日期的范圍、輸入字符的個數、單元格的公式單元格的公式7.2 數據審核及跟蹤分析數據審核及跟蹤分析數據限制的操作方法：選擇數據限制的操作方法：選擇“數據數據 “有效性有效性在對話在對話 框中操

11、作：框中操作：限定文本長度：限定文本長度： “設置選項卡中設置選項卡中“允許允許 下拉列表中選下拉列表中選擇文本長度。擇文本長度。限定數據的有效范圍：限定數據的有效范圍：“設置選項卡中設置選項卡中“允許下拉列表允許下拉列表中選擇整數中選擇整數/小數小數- 確定最大確定最大/小值小值設置單元格有效范圍：設置單元格有效范圍：“設置選項卡中設置選項卡中“允許下拉列表允許下拉列表中選擇序列中選擇序列輸入序列值輸入序列值設置輸入提示信息：設置輸入提示信息： “輸入信息選項卡中輸入要顯示的輸入信息選項卡中輸入要顯示的信息信息7.2 數據審核及跟蹤分析數據審核及跟蹤分析n例：例：見見ch7.xls限定數據范

12、圍限定數據范圍n 某班要建立一個成果登記表，為了減少成果輸入錯某班要建立一個成果登記表，為了減少成果輸入錯誤，可對成果表中數據的輸入類型及范圍進展限制。誤，可對成果表中數據的輸入類型及范圍進展限制。n限制學號為限制學號為8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制一切學科成果為限制一切學科成果為0100之間的整數。之間的整數。n限制科目列標題的取值范圍，如限制科目列標題的取值范圍，如“高數不能輸入為高數不能輸入為“高等數學。高等數學。7.2 數據審核及跟蹤分析數據審核及跟蹤分析4、圈釋無效數據、圈釋無效數據 運用數據有效性規那么可限制單元格可接納的數據，但對

13、運用數據有效性規那么可限制單元格可接納的數據，但對已輸入數據的區域，不能顯示出有誤的數據。采用圈釋無已輸入數據的區域，不能顯示出有誤的數據。采用圈釋無效數據的方法，可以顯示不滿足有效性規那么的錯誤單元效數據的方法，可以顯示不滿足有效性規那么的錯誤單元格。格。操作方法：選擇數據區域操作方法：選擇數據區域設置數據有效性規那么設置數據有效性規那么選選擇擇“工具菜單工具菜單“審核審核選擇選擇“顯示審核工具欄顯示審核工具欄選選中有效性檢測的數據區域中有效性檢測的數據區域單擊單擊“審核工具欄的審核工具欄的“圈釋無圈釋無效數據按鈕效數據按鈕 注：要先設置數據的有效范圍，然后再圈釋無效數據注：要先設置數據的有

14、效范圍，然后再圈釋無效數據例：例：見見ch7.xls圈釋無效數據圈釋無效數據 某班要建立一個成果登記表，曾經對成果表中數據的輸入某班要建立一個成果登記表，曾經對成果表中數據的輸入類型及范圍進展限制，找出其中不符合規定的數據。類型及范圍進展限制，找出其中不符合規定的數據。7.3 模擬運算表模擬運算表1、概念、概念模擬運算表是對任務表中一個單元格區域內的數據進展模擬模擬運算表是對任務表中一個單元格區域內的數據進展模擬運算，測試運用一個或兩個變量的公式中變量對運算結果運算，測試運用一個或兩個變量的公式中變量對運算結果的影響。的影響。2、模擬運算表的類型、模擬運算表的類型基于一個輸入變量的表，用這個輸

15、入變量測試它對多個公基于一個輸入變量的表，用這個輸入變量測試它對多個公式的影響；式的影響；單模擬運算表單模擬運算表基于兩個輸入變量的表，用這兩個變量測試它們對于單個基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響公式的影響雙模擬運算表雙模擬運算表7.3 模擬運算表模擬運算表3、單變量模擬運算表、單變量模擬運算表概念概念在單變量模擬運算表中，輸入數據的值被安排在一行或一列中。在單變量模擬運算表中，輸入數據的值被安排在一行或一列中。同時，單變量模擬表中運用的公式必需援用同時，單變量模擬表中運用的公式必需援用“輸入單元格。輸入單元格。輸入單元格，就是被交換的含有輸入數據的單元格輸入單元格

16、，就是被交換的含有輸入數據的單元格 操作步驟：操作步驟：1、在任務表中建立模擬運算表的構造；、在任務表中建立模擬運算表的構造；2、輸入模擬運算表要用到的公式；、輸入模擬運算表要用到的公式；3、選擇包括公式、援用單元格和運算結果單元格區域、選擇包括公式、援用單元格和運算結果單元格區域3部部分；分；4、選擇、選擇“數據菜單數據菜單“模擬運算表選項；模擬運算表選項；5、在、在“模擬運算表對話框中輸入援用單用格行或列一模擬運算表對話框中輸入援用單用格行或列一種種 確定確定7.3 模擬運算表模擬運算表n例：例：見見ch7.xls單變量模擬運算表單變量模擬運算表n 假設某人正思索購買一套住房，要承當一筆假

17、設某人正思索購買一套住房，要承當一筆250 000元的貸款，分元的貸款，分15年還清。現想查看每月的還貸金額，年還清。現想查看每月的還貸金額，并想查看在不同的利率下，每月的應還貸金額。并想查看在不同的利率下，每月的應還貸金額。 n 假設貸款額分別為假設貸款額分別為400 000，550 000，800 000元，每月的應還貸金額又是多少？元，每月的應還貸金額又是多少？ 7.3 模擬運算表模擬運算表4、雙變量模擬運算表、雙變量模擬運算表概念：概念： 單變量模擬運算表只能處理一個輸入變量對一個或多個公式計單變量模擬運算表只能處理一個輸入變量對一個或多個公式計算結果的影響，要查看兩個變量對公式計算結

18、果的影響，就要算結果的影響，要查看兩個變量對公式計算結果的影響，就要用到雙變量模擬運算表。所謂雙模擬變量，就是指公式中有兩用到雙變量模擬運算表。所謂雙模擬變量，就是指公式中有兩個變量。公式中兩個變量所在的單元格是任取的。可以是任務個變量。公式中兩個變量所在的單元格是任取的。可以是任務表中恣意空白單元格。表中恣意空白單元格。7.3 模擬運算表模擬運算表n操作步驟：操作步驟：n1、在任務表中建立模擬運算表的構造；、在任務表中建立模擬運算表的構造；n2、在行列交叉處輸入模擬運算表要用到的公式；、在行列交叉處輸入模擬運算表要用到的公式；n3、選擇包括公式、選擇包括公式,援用單元格和運算結果單元格區域援

19、用單元格和運算結果單元格區域3部分；部分；n4、選擇、選擇“數據菜單數據菜單“模擬運算表選項；模擬運算表選項；n5、在、在“模擬運算表對話框中輸入公式中行和列援用模擬運算表對話框中輸入公式中行和列援用的單用格的單用格確定確定n例：例：見見ch7.xls雙變量模擬運算表雙變量模擬運算表n 假設某人想貸款假設某人想貸款45萬元購買一部車，要查看在不同萬元購買一部車，要查看在不同的利率和不同的歸還年限下，每個月應還的貸款金額。的利率和不同的歸還年限下，每個月應還的貸款金額。假設要查看貸款利率為假設要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，歸還期限為，歸還期限為10年、年、15年

20、、年、20年、年、30年、年、35年時，每月應歸還的貸款金額是多少年時，每月應歸還的貸款金額是多少 ？7.4 單變量求解單變量求解1、概念、概念所謂單變量求解，就是求解具有一個變量的方程，所謂單變量求解，就是求解具有一個變量的方程，Excel經經過調整可變單元格中的數值，使之按照給定的公式來滿足過調整可變單元格中的數值，使之按照給定的公式來滿足目的單元格中的目的值目的單元格中的目的值.2、單變量求解方法、單變量求解方法在任務表中輸入原始數據；在任務表中輸入原始數據；建立可變數公式；建立可變數公式；設置求解公式：菜單設置求解公式：菜單“工具工具單變量求解單變量求解對話框中輸入：對話框中輸入：目的

21、單元格、目的值、可變單元格目的單元格、目的值、可變單元格例：例： 見見ch7.xls單變量求解單變量求解 某公司想向銀行貸款某公司想向銀行貸款900萬元人民幣，貸款利率是萬元人民幣，貸款利率是8.7%，貸款限期為貸款限期為8年，每年應歸還多少金額？年，每年應歸還多少金額？ 假設公司每年可歸還假設公司每年可歸還120萬元，該公司最多可貸款多少金萬元，該公司最多可貸款多少金額？額？ 前一問題可用前一問題可用PMT函數函數, 后一問題可用單變量求解。后一問題可用單變量求解。7.5 方案分析方案分析1、概念、概念方案是已命名的一組輸入值，是方案是已命名的一組輸入值，是 Excel 保管在任務表中并可用

22、保管在任務表中并可用來自動交換某個計算模型的輸入值，用來預測模型的輸出結果。來自動交換某個計算模型的輸入值，用來預測模型的輸出結果。 例例:知某茶葉公司知某茶葉公司2019年的總銷售額及各種茶葉的銷售本錢，現要年的總銷售額及各種茶葉的銷售本錢，現要在此根底上制定一個五年方案。由于市場競爭的不斷變化，所在此根底上制定一個五年方案。由于市場競爭的不斷變化，所以只能對總銷售額及各種茶葉銷售本錢的增長率做一些估計。以只能對總銷售額及各種茶葉銷售本錢的增長率做一些估計。最好的方案當然是總銷售額增長率高，各茶葉的銷售本錢增長最好的方案當然是總銷售額增長率高，各茶葉的銷售本錢增長率低。率低。 最好的估計是總

23、銷售額增長最好的估計是總銷售額增長13%，花茶、綠茶、烏龍茶、紅茶，花茶、綠茶、烏龍茶、紅茶的銷售本錢分別增長的銷售本錢分別增長10%、6%、10%、7%。 見見ch7.xls方案方案7.5 方案分析方案分析n建立方案處理任務表建立方案處理任務表建立方法如下，輸入下表建立方法如下，輸入下表A列、列、B列及第列及第3行的一切數行的一切數據；在據；在C4單元格中輸入公單元格中輸入公式式“=B4*(1+$B$16)，然后將其復制到然后將其復制到D4F4；在在C7中輸入公式中輸入公式“=B7*(1+$B$17),并并將其復制到將其復制到D7F7；在在C8中輸入公式中輸入公式“=B8*(1+$B$18)

24、，并將其復制到并將其復制到D8和和F8；在在C9中輸入公式中輸入公式“=B9*(1+$B$19)，并將其復制到并將其復制到D9F9；在在C10中輸入公式中輸入公式“=B10*(1+$B$20)，并將其復制到并將其復制到D10F10；第第11行數據是第行數據是第7，8，9，10行數據對應列之和；凈行數據對應列之和；凈收入是相應的總銷售額和收入是相應的總銷售額和銷售本錢之差，銷售本錢之差，E19的總的總凈收入是第凈收入是第13行數據之和。行數據之和。 7.5 方案分析方案分析輸入方案變量值如以下圖所示：輸入方案變量值如以下圖所示：7.5 方案分析方案分析2、顯示方案、顯示方案 選擇選擇“工具工具

25、“方案菜單方案菜單選擇選擇“方案管理器對話框方案管理器對話框中的某一方案中的某一方案單擊單擊 “顯示按鈕顯示按鈕3、建立方案報告、建立方案報告見見ch7.xls方案摘要方案摘要 選擇選擇“工具工具 “方案菜單方案菜單選擇選擇“方案管理器對話框方案管理器對話框中的某一方案中的某一方案單擊單擊 “總結按鈕總結按鈕在在“方案總結對話方案總結對話框中結果類型中選擇框中結果類型中選擇“方案總結方案總結4、建立方案透視圖、建立方案透視圖見見ch7.xls方案數據透視圖方案數據透視圖 選擇選擇“工具工具 “方案菜單方案菜單選擇選擇“方案管理器對話框方案管理器對話框中的某一方案中的某一方案單擊單擊 “總結按鈕

26、總結按鈕在在“方案總結對話方案總結對話框中結果類型中選擇框中結果類型中選擇“方案數據透視表方案數據透視表7.6 線性規劃求解線性規劃求解1、概述、概述 EXCEL提供的規劃求解工具，可求解出線性與非線性兩提供的規劃求解工具，可求解出線性與非線性兩種規劃求解問題，規劃求解問題常用于處理產品比例、人種規劃求解問題，規劃求解問題常用于處理產品比例、人員調度、優化道路、調配資料等方面問題。員調度、優化道路、調配資料等方面問題。2、規劃求解問題的特點：、規劃求解問題的特點：問題有單一的目的，如求運輸的最正確道路、求消費的最低問題有單一的目的，如求運輸的最正確道路、求消費的最低本錢、求產品的最大盈利，求產

27、品周期的最短時間等。本錢、求產品的最大盈利，求產品周期的最短時間等。問題有明確的不等式約束條件，例如消費資料不能超越庫存，問題有明確的不等式約束條件，例如消費資料不能超越庫存，消費周期不能超越一個星期等。消費周期不能超越一個星期等。問題有直接或間接影響約束條件的一組輸入值。問題有直接或間接影響約束條件的一組輸入值。 7.6 線性規劃求解線性規劃求解3、Excel規劃求解問題的組成部分規劃求解問題的組成部分 1一個或一組可變單元格一個或一組可變單元格 可變單元格稱為決策變量，一組決策變量代表一個規劃求可變單元格稱為決策變量，一組決策變量代表一個規劃求解的方案解的方案2目的函數目的函數目的函數表示

28、規劃求解要到達的最終目的，是規劃求解的關目的函數表示規劃求解要到達的最終目的，是規劃求解的關鍵。它是規劃求解中可變量的函數鍵。它是規劃求解中可變量的函數 3約束條件約束條件約束條件是實現目的的限制條件。約束條件是實現目的的限制條件。 意義：經過規劃求解，用戶可為任務表的目的單元格中意義：經過規劃求解，用戶可為任務表的目的單元格中的公式找到一個優化值，規劃求解將直接或間接與目的單的公式找到一個優化值，規劃求解將直接或間接與目的單元格公式相聯絡的一組單元格數值進展調整，最終在目的元格公式相聯絡的一組單元格數值進展調整，最終在目的單元格公式中求得期望的結果。單元格公式中求得期望的結果。7.6 線性規

29、劃求解線性規劃求解例：例： 見見ch7.xls規劃求解規劃求解某肥料廠專門搜集有機物渣滓，如青草、樹枝、凋謝的花朵某肥料廠專門搜集有機物渣滓，如青草、樹枝、凋謝的花朵等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質等。該廠利用這些廢物，并摻進不同比例的泥土和礦物質來消費高質量的植物肥料，消費的肥料分為底層肥料、中來消費高質量的植物肥料，消費的肥料分為底層肥料、中層肥料、上層肥料、劣質肥料層肥料、上層肥料、劣質肥料4種。為使問題簡單，假設種。為使問題簡單，假設搜集廢物的勞動力是自愿的，除了搜集本錢之外，資料本搜集廢物的勞動力是自愿的，除了搜集本錢之外，資料本錢是低廉的。錢是低廉的。該廠目前的原

30、資料、消費各種肥料需求的原資料比例，各種該廠目前的原資料、消費各種肥料需求的原資料比例，各種肥料的單價等如下頁各表所示。肥料的單價等如下頁各表所示。問題：求出在現有的情況下，即利用原資料的現有庫存，應問題：求出在現有的情況下，即利用原資料的現有庫存，應消費各種類型的肥料各多少數量才干獲得最大利潤，最大消費各種類型的肥料各多少數量才干獲得最大利潤，最大利潤是多少？利潤是多少？ 分析分析:所求是在現有的原資料情況下所求是在現有的原資料情況下,應如何合理搭配應如何合理搭配,才干獲才干獲取消費產品的最大利潤取消費產品的最大利潤.7.6 線性規劃求解線性規劃求解表表2 消費肥料的庫存原資料消費肥料的庫存

31、原資料庫存庫存情況情況現有現有庫存庫存泥土泥土4100有機有機垃圾垃圾3200礦物礦物質質3500修剪修剪物物表1 各肥料廢品用料及其價錢表表的意思是消費一個單位的肥料需求多少各種原資料多少單位產品產品泥土泥土有機垃圾有機垃圾礦物質礦物質修剪物修剪物單價單價底層肥料底層肥料55547623105.00中層肥料中層肥料6432452084.00上層肥料上層肥料43329844105.00劣質肥料劣質肥料1845231857.00表表3單位原資料本錢單價單位原資料本錢單價項項 目目單位成本單位成本泥土泥土0.20有機垃圾有機垃圾0.15礦物質礦物質0.10修剪物修剪物0.237.6 線性規劃求解線

32、性規劃求解n建立規劃求解模型步驟：建立規劃求解模型步驟：n規劃求解第一步規劃求解第一步建立求解任務表建立求解任務表(輸入原始數據及相應輸入原始數據及相應的各公式的各公式)7.6 線性規劃求解線性規劃求解規劃求解第二步規劃求解第二步設置求解參數設置求解參數選擇選擇“工具工具 “規劃求解菜單，設置以下求解的各項參規劃求解菜單，設置以下求解的各項參數：數：設置目的單元格：輸入目的函數所在單元格設置目的單元格：輸入目的函數所在單元格(為總余額單元為總余額單元格格)設置目的：最大值、最小值或值的數值設置目的：最大值、最小值或值的數值(最大利潤最大利潤,即最大值即最大值)設置可變單元格：它確實定決議結果為

33、消費數量設置可變單元格：它確實定決議結果為消費數量設置約束條件：單擊設置約束條件：單擊“添加按鈕添加按鈕輸入約束條件輸入約束條件按添按添加加依次輸入一切約束條件依次輸入一切約束條件確定確定7.6 線性規劃求解線性規劃求解n規劃求解第規劃求解第3步步保管求解結果保管求解結果n在規劃求解對話框中按在規劃求解對話框中按“求解求解在規劃求解結果對話在規劃求解結果對話框中按框中按“保管規劃求解結果保管規劃求解結果7.6 線性規劃求解線性規劃求解4、修正資源、修正資源 例例1：見見ch7.xls規劃求解規劃求解肥料廠接到一個：只需公司肯花肥料廠接到一個：只需公司肯花10元的運費就能得到元的運費就能得到15

34、0個個單位的礦物。這筆買賣稍稍降低了礦物質的平均價錢，但單位的礦物。這筆買賣稍稍降低了礦物質的平均價錢，但這些礦物質值這些礦物質值10元嗎？元嗎？處理該問題的方法是，將庫存礦物處理該問題的方法是，將庫存礦物3500改為改為3650，用規劃，用規劃求解重新計算最大盈余。看除去￥求解重新計算最大盈余。看除去￥10的本錢后，盈余能的本錢后，盈余能否添加否添加 操作操作: 將庫存礦物將庫存礦物3500改為改為3650,其它一切公式不變其它一切公式不變,再次進展再次進展求解求解,求得盈余額為求得盈余額為4483.41,原盈余額為原盈余額為4425.89. 可知盈利為可知盈利為57.52.扣除扣除10元本

35、錢后仍有元本錢后仍有47.52.因此該礦物因此該礦物還是要的還是要的.7.6 線性規劃求解線性規劃求解5、修正約束條件、修正約束條件 見見ch7.xls規劃求解規劃求解肥料廠接到一個，一個老顧客急需肥料廠接到一個，一個老顧客急需25個單位的上層肥料，個單位的上層肥料，公司經理在檢查打印結果后，發現沒有安排消費上層肥料。公司經理在檢查打印結果后，發現沒有安排消費上層肥料。數量為數量為0。決議添加約束條件，為他消費。決議添加約束條件，為他消費25個單位的上層個單位的上層肥料。肥料。結果可發現結果可發現:盈余額僅盈余額僅3246.51,比原來比原來4483.41少了少了1236.9。顯然不值得。但如

36、該顧客為長期顧客，那么短。顯然不值得。但如該顧客為長期顧客，那么短期內將損失一些錢，但得到了顧客的信任。期內將損失一些錢，但得到了顧客的信任。添加的添加的約束條約束條件件7.6 線性規劃求解線性規劃求解6、 規劃求解的結果報告規劃求解的結果報告運算結果報告：列出目的單元格、可變單元格及它們的初始值、運算結果報告：列出目的單元格、可變單元格及它們的初始值、最終結果、約束條件和有關約束條件的信息。最終結果、約束條件和有關約束條件的信息。 見見ch7.xls運算結果報告運算結果報告7.6 線性規劃求解線性規劃求解n敏感性報告：敏感性報告： 見見ch7.xls敏感性報告敏感性報告7.6 線性規劃求解線

37、性規劃求解n極限報告：列出目的單元格、可變單元格及它們的數極限報告：列出目的單元格、可變單元格及它們的數值、上下限和目的值。下限為在滿足約束條件和堅持值、上下限和目的值。下限為在滿足約束條件和堅持其它可變單元格數值不變的情況下，某個可變單元格其它可變單元格數值不變的情況下，某個可變單元格可以獲得的最小值，上限那么為在這種情況下可以取可以獲得的最小值，上限那么為在這種情況下可以取到的最大值。到的最大值。 見見ch7.xls極限值報告極限值報告7.6 線性規劃求解線性規劃求解7、求解精度及求解模型設置、求解精度及求解模型設置 Excel采用迭代的方式進展規劃求解，當求解到一定精度采用迭代的方式進展

38、規劃求解，當求解到一定精度時就終了求解，但有時要修正求解的精度、計算時間、規時就終了求解，但有時要修正求解的精度、計算時間、規劃模型和迭代次數。修正上述設置的方法如下：劃模型和迭代次數。修正上述設置的方法如下： 在在“規劃求解參數對話框中設置好各項求解參數；規劃求解參數對話框中設置好各項求解參數； 單擊單擊“選項按鈕，在選項按鈕，在“規劃求解選項對話框中設置各規劃求解選項對話框中設置各項求解參數。項求解參數。 7.6 線性規劃求解線性規劃求解例例2：求解不等式：求解不等式：見見ch7.xls規劃求解不等式規劃求解不等式某工廠消費甲、乙兩種產品，假設消費甲產品某工廠消費甲、乙兩種產品，假設消費甲

39、產品1噸，要耗費噸，要耗費9噸噸煤，煤，4千瓦電力，千瓦電力，3噸鋼材，獲利噸鋼材，獲利0.7萬元；消費乙產品萬元；消費乙產品1噸，要噸，要耗費耗費4噸煤，噸煤，5千瓦電力，千瓦電力，10噸鋼材，獲利噸鋼材，獲利1.2萬元。按方案國萬元。按方案國家能提供應該廠的煤為家能提供應該廠的煤為360噸，電力噸，電力200千瓦，鋼材千瓦，鋼材300噸，問噸，問應該消費多少噸甲種產品和乙種產品，才干獲得最大利潤？應該消費多少噸甲種產品和乙種產品，才干獲得最大利潤？ 假設消費甲種產品假設消費甲種產品X1噸，消費乙種產品噸，消費乙種產品x2噸，噸， 其最大利潤是求其最大利潤是求=0.7x1+1.2x2的最大值

40、。這個問題可用數的最大值。這個問題可用數學建模如下：學建模如下：.0,0.300103.20054.3604921212121xxxxxxxx7.6 線性規劃求解線性規劃求解規劃求解如下：規劃求解如下：B3和和C3分別用于保管甲和乙產品的消費量。分別用于保管甲和乙產品的消費量。目的單元格為目的單元格為B8；可變單元格為；可變單元格為$B$3:$C$3；約束條件為：；約束條件為：$B$3=0$C$3=0$B$4=360$B$5=200$B$6=3007.6 線性規劃求解線性規劃求解n例例3： 見見ch7.xls線形規劃求解線形規劃求解n某公司在某公司在A地有一個消費基地地有一個消費基地,其消費才

41、干為其消費才干為400,隨著市隨著市場需求的增長及該公司業務量的增大場需求的增長及該公司業務量的增大,現有現有3個配送中心的個配送中心的需求都在增長需求都在增長,估計分別為估計分別為200,400,300。公司正思索再。公司正思索再建立一個消費才干為建立一個消費才干為500的工廠，預備建在的工廠，預備建在B地。從地。從A地地的工廠向的工廠向3個配送中心的單位運輸本錢分別為個配送中心的單位運輸本錢分別為5.0元，元，6.0元，元，5.4元，從元，從B地的工廠向地的工廠向3個配送中心的單位運輸本錢個配送中心的單位運輸本錢是是7.0元，元，4.6元，元，6.6元。應怎樣分配元。應怎樣分配A、B兩地到

42、兩地到3個配個配送中心的產品量，才干使運輸本錢最小？送中心的產品量，才干使運輸本錢最小？n規劃模型解釋如下：規劃模型解釋如下：7.6 線性規劃求解線性規劃求解D5、E5、F5表示從A廠將1個單位產品分別送到配送中心1，2，3的費用；D7，E7，F7表示從B廠將1個單位產品分別送到配送中心1，2，3的費用；D6，E6，F6為可變單元格，保管從A廠運到3個配送中心的最正確產品量；D8，E8，F8為可變單元格，保管從B廠運到3個配送中心的最正確產品量；D10，E10，F10表示配送中心1，2，3的最大負荷才干；H5，H7分別是A廠、B廠的消費才干， H9是A，B兩廠的總消費才干7.6 線性規劃求解線

43、性規劃求解G6G6，G8G8分別為分別為A A，B B兩廠各自的產品總量。兩廠各自的產品總量。G6G6公式：公式：=D6+E6+F6=D6+E6+F6；G8G8公式：公式：=G8+E8+F8=G8+E8+F8；D9D9，E9E9，F9F9分別為兩廠送到各配送中心的產品總量。分別為兩廠送到各配送中心的產品總量。D9D9公式：公式：=D6+D8=D6+D8；E9E9公式：公式：=E6+E8=E6+E8；F9F9公式：公式：=F6+F8=F6+F8；本模型的約束條件分析如下：本模型的約束條件分析如下：A A，B B兩廠送到各配送中心的產品總量不能超越各配送中心的負兩廠送到各配送中心的產品總量不能超越

44、各配送中心的負荷才干：荷才干：D9D9：F9=D10F9=0F6=0，D8:F8=0D8:F8=0本模型的目的函數，求以下公式本模型的目的函數，求以下公式的最大值：的最大值：S=D5S=D5* *D6+E5D6+E5* *E6+F5E6+F5* *F6+D7F6+D7* *D8+E7D8+E7* *E8+F7E8+F7* *F8F87.7 數據分析工具庫數據分析工具庫1、概述、概述Excel提供了一組數據分析工具，稱為分析工具庫。其中提提供了一組數據分析工具，稱為分析工具庫。其中提供的分析工具在工程分析、數理統計、經濟計量分析等學供的分析工具在工程分析、數理統計、經濟計量分析等學科中有較強的適

45、用價值。科中有較強的適用價值。分析工具庫由分析工具庫由Excel自帶的加載宏提供。自帶的加載宏提供。 假設啟動假設啟動Excel后，在后，在Excel的的“工具菜單中沒有工具菜單中沒有“數據分數據分析菜單項，就需啟動析菜單項，就需啟動“工具中的工具中的“加載宏菜單項，將加載宏菜單項，將“分析工具庫加載到分析工具庫加載到Excel系統中。系統中。假設加載宏對話框中沒有分析工具庫，那么單擊加載宏對話假設加載宏對話框中沒有分析工具庫，那么單擊加載宏對話框中框中“閱讀按鈕，定位到分析工具庫加載宏文件閱讀按鈕，定位到分析工具庫加載宏文件“Analy32.dll所在的驅動器和文件夾，通常位于所在的驅動器和

46、文件夾，通常位于“Microsoft OfficeOfficeLibraryAnalysis中中,否那否那么需運轉么需運轉Office系統的安裝程序。系統的安裝程序。Excel的的“分析工具庫加載宏提供的一些統計函數、財務函分析工具庫加載宏提供的一些統計函數、財務函數和工程函數。這些函數只需在安裝了數和工程函數。這些函數只需在安裝了“分析工具庫后分析工具庫后才干運用才干運用 。7.7 數據分析工具庫數據分析工具庫2、Excel分析工具庫中的工具分析工具庫中的工具分析工具分析工具名稱名稱說說 明明方差分析方差分析 包括包括3種類型的分析，它們是單因素方差分析、可種類型的分析，它們是單因素方差分析

47、、可重復雙因素分析、無重復雙因素分析重復雙因素分析、無重復雙因素分析相關系數相關系數分析分析用于判斷兩組數據集（可以使用不同的度量單位）用于判斷兩組數據集（可以使用不同的度量單位）之間的關系。之間的關系。協方差分協方差分析析用于返回各數據點的一對均值偏差之間的乘積的用于返回各數據點的一對均值偏差之間的乘積的平均值。平均值。描述統計描述統計分析分析用于生成對輸入區域中數據的單變值分析，提供用于生成對輸入區域中數據的單變值分析，提供有關數據趨中性和易變性的信息有關數據趨中性和易變性的信息指數平滑指數平滑分析分析基于前期預測值導出相應的新預測值，并修正前基于前期預測值導出相應的新預測值，并修正前期預

48、測值的誤差。期預測值的誤差。7.7 數據分析工具庫數據分析工具庫傅里葉分傅里葉分析析解決線性系統問題，并能通過快速傅里葉變換解決線性系統問題，并能通過快速傅里葉變換（FFT）分析周期性的數據。）分析周期性的數據。F-檢驗檢驗用來比較兩個樣本總體的方差用來比較兩個樣本總體的方差直方圖分直方圖分析析在給定工作表中數據單元格區域和接收區間的情在給定工作表中數據單元格區域和接收區間的情況下，計算數據的個別和累計頻率況下，計算數據的個別和累計頻率移動平均移動平均分析分析基于特定的過去某段時期中變量的均值，對未來基于特定的過去某段時期中變量的均值，對未來值進行預測值進行預測t-檢驗分檢驗分析析提供提供3種

49、不同檢驗：雙樣本等方差假設種不同檢驗：雙樣本等方差假設t-檢驗，雙檢驗，雙樣本異方差假設樣本異方差假設t-檢驗，平均值的成對雙樣本檢驗，平均值的成對雙樣本t-檢驗檢驗回歸分析回歸分析通過對一組觀察值使用通過對一組觀察值使用“最小二乘法最小二乘法”直線擬合，直線擬合，進行線形回歸分析。進行線形回歸分析。抽樣分析抽樣分析以輸入區域為總體構造總體的一個樣本以輸入區域為總體構造總體的一個樣本z-檢驗檢驗雙樣本平均差檢驗雙樣本平均差檢驗7.7 數據分析工具庫數據分析工具庫3、 統計分析統計分析 Excel的分析工具庫提供了的分析工具庫提供了3種統計觀測分析工具：指數平種統計觀測分析工具：指數平滑分析、挪

50、動平均分析和回歸分析滑分析、挪動平均分析和回歸分析 三種工具用法一樣，下面以指數平滑分析為例。三種工具用法一樣，下面以指數平滑分析為例。見見ch7.xls 指數平滑分析指數平滑分析1在任務表的一列上輸入各時間點上的察看值，如以下在任務表的一列上輸入各時間點上的察看值，如以下圖圖A列所示。列所示。 7.7 數據分析工具庫數據分析工具庫2 2選擇選擇“工具菜單工具菜單“數據分析數據分析 選項，對話框中選擇選項，對話框中選擇“指指數平滑按確定。數平滑按確定。 3 3在在“指數平滑對話框中設置指數平滑對話框中設置“輸入區域、輸入區域、“阻尼系數阻尼系數、“輸出區域選項。輸出區域選項。4 4選定對話框中

51、選定對話框中“圖表輸出和圖表輸出和“規范誤差復選框標志。規范誤差復選框標志。分析結果：分析結果：B B列為分析之后輸出的預測數據；列為分析之后輸出的預測數據；C C列是分析工具列是分析工具輸出的規范誤差。輸出的規范誤差。7.7 數據分析工具庫數據分析工具庫4、假設檢驗、假設檢驗 假設檢驗是根據對事物進展抽樣所得的少量樣本信息，判別假設檢驗是根據對事物進展抽樣所得的少量樣本信息，判別總體分布的某個假設能否成立的一種數理統計方法。總體分布的某個假設能否成立的一種數理統計方法。假設分析工具有三種：假設分析工具有三種：t-檢驗、檢驗、z-檢驗、檢驗、F-檢驗。檢驗。運用這些檢驗工具可以完成均值、方差的

52、假設檢驗。運用這些檢驗工具可以完成均值、方差的假設檢驗。 方法方法見下例見下例7.7 數據分析工具庫數據分析工具庫n例：例： 見見ch7.xlst檢驗檢驗n 雙樣本等方差雙樣本等方差t-檢驗，以確定兩個樣本均值實踐上檢驗，以確定兩個樣本均值實踐上能否相等能否相等n 某種子公司為比較兩個稻種的產量，選擇了某種子公司為比較兩個稻種的產量，選擇了25塊條件塊條件類似的實驗田，采用一樣的耕種方法進展耕種實驗，類似的實驗田，采用一樣的耕種方法進展耕種實驗，結果播種甲稻種的結果播種甲稻種的13塊田的畝產量單位：市斤分塊田的畝產量單位：市斤分別是：別是：880、1 120、980、885、828、927、9

53、24、942、766、1 180、780、1 068、650；播種乙稻；播種乙稻種的種的12塊實驗田的畝產量分別是：塊實驗田的畝產量分別是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。問這兩個稻種的產量有沒有明顯的高低。問這兩個稻種的產量有沒有明顯的高低之分。之分。 n闡明：要判別兩稻種有無顯著差別，用闡明：要判別兩稻種有無顯著差別，用t-檢驗方法，檢驗方法，需先計算各樣本的平均值和方差，才干作進一步的檢需先計算各樣本的平均值和方差，才干作進一步的檢驗分析。驗分析。7.7 數據分析工具庫數據分析工具庫nt-t-檢驗操作過程：檢驗操

54、作過程：n(1)(1)輸入輸入A A、B B兩列樣本數據下表中右邊數據全為產生的兩列樣本數據下表中右邊數據全為產生的分析結果分析結果7.7 數據分析工具庫數據分析工具庫n(2)(2)選擇選擇“工具菜單中工具菜單中“數據分析選項，對話框中選擇數據分析選項，對話框中選擇t t檢驗雙樣本等方差假設；檢驗雙樣本等方差假設；n3 3在在“雙樣本等方差假設分析對話框中設置雙樣本等方差假設分析對話框中設置t t檢驗的檢驗的各項參數各項參數n按確定按確定7.7 數據分析工具庫數據分析工具庫5、回歸分析、回歸分析回歸分析主要用于分析單個因變量是如何受一個或幾個自變量影回歸分析主要用于分析單個因變量是如何受一個或

55、幾個自變量影響的。如察看某個運發動的運動成果與一系列統計要素的關系。響的。如察看某個運發動的運動成果與一系列統計要素的關系。如年齡、體重、身高等。如年齡、體重、身高等。回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數學模型回歸分析分為線性回歸和非線性回歸兩種。線性回歸的數學模型為：為：Excel經過對一組察看值運用經過對一組察看值運用“最小二乘法直線擬合，進展線性最小二乘法直線擬合，進展線性回歸分析，該回歸分析可同時處理一元回歸與多元回歸問題。回歸分析，該回歸分析可同時處理一元回歸與多元回歸問題。iiiyxu7.7 數據分析工具庫數據分析工具庫n例：用一個多元回歸線性分析例子來闡明回歸分析工

56、具的運用方法nch7.xls回歸分析數據表中，列出了美國19561970年間歷年的人均可支配收入xi和人均可消費支出yi的數據。試用圖中的數據擬合模型。n模型中的趨勢變量t，用于反映除人均收入之外的一切其他要素對人均消費的影響 tttutxy2107.7 數據分析工具庫數據分析工具庫n利用回歸分析工具求解此模型的方法利用回歸分析工具求解此模型的方法n輸入原始數據表；輸入原始數據表；n選擇選擇“工具菜單中工具菜單中“數據分析選項，在數據分析選項，在“數據分析對數據分析對話框中選擇話框中選擇“回歸列表。系統彈出如下對話框；回歸列表。系統彈出如下對話框；n在在“回歸對話框中輸入因變量回歸對話框中輸入

57、因變量y和自變量和自變量x的數據區域；的數據區域；n假設需求線性擬合的假設需求線性擬合的“殘差圖和殘差圖和“線性擬合圖等，那么線性擬合圖等，那么需選擇相應復選框需選擇相應復選框n此題結果見此題結果見見見ch7.xls回歸分析的輸出結果回歸分析的輸出結果綜合實例綜合實例1.單變量模擬運算表實例單變量模擬運算表實例超市要進展一些改革，如對某些產品采取分期付款的方式進超市要進展一些改革，如對某些產品采取分期付款的方式進展銷售。假設有一液晶電視，每臺售價為展銷售。假設有一液晶電視，每臺售價為98,000，采用，采用分期付款的方式進展銷售，初步確定分期付款的方式為零分期付款的方式進展銷售，初步確定分期付

58、款的方式為零首付，月分期手續費率為首付，月分期手續費率為0.7，求在不同的分期付款期，求在不同的分期付款期數月下，每期月消費者需求付款的金額數月下，每期月消費者需求付款的金額 綜合實例綜合實例n【實例操作步驟】【實例操作步驟】n詳細操作步驟如下：詳細操作步驟如下：n步驟步驟1：創建任務表，建立根本的模擬運算任務表，輸入：創建任務表，建立根本的模擬運算任務表，輸入必要的數據及要測試的任務表中的數據，即不同的分期付必要的數據及要測試的任務表中的數據，即不同的分期付款期數，如下圖。款期數，如下圖。 綜合實例綜合實例n步驟步驟2：創建運算公式：創建運算公式n在單元格在單元格D3中輸入公式中輸入公式“=

59、PMTB5,C3,B2，得出，得出當分期付款期數為當分期付款期數為C3單元格中的數值時，消費者每月的單元格中的數值時，消費者每月的付款額。如圖付款額。如圖7-2所示。公式中所示。公式中B5為月分期手續費率，為月分期手續費率，C3為分期付款期數，以月為單位，為分期付款期數，以月為單位，B2為付款本金。為付款本金。綜合實例綜合實例n步驟步驟3：建立單變量模擬運算表：建立單變量模擬運算表n首先要選定公式、數值序列和模擬運算結果所在的單元格首先要選定公式、數值序列和模擬運算結果所在的單元格區域，即區域，即C3:D12，以定義這個模擬運算表，然后選擇，以定義這個模擬運算表，然后選擇“數據菜單中的數據菜單

60、中的“模擬運算表命令，翻開模擬運算表命令，翻開“模擬運算表模擬運算表對話框，在對話框，在“模擬運算表對話框的模擬運算表對話框的“輸入援用列的單元輸入援用列的單元格文本框中輸入第一個變量所在的單元格地址格文本框中輸入第一個變量所在的單元格地址“$C$3，如下圖。如下圖。綜合實例綜合實例n【實例操作結果】【實例操作結果】n最終的計算結果如下圖。最終的計算結果如下圖。 綜合實例綜合實例n雙變量模擬運算表實例雙變量模擬運算表實例n在前面的例子中，僅僅把分期付款期數作為單變量進展模在前面的例子中，僅僅把分期付款期數作為單變量進展模擬運算，但在現實生活中，月分期付款手續費率也是經常擬運算，但在現實生活中，