excel數(shù)據(jù)分析與處理第8章 數(shù)據(jù)分析工具的應(yīng)用

1、本章本章學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)1 1、了解、了解加載宏加載宏的功能和用法的功能和用法2 2、掌握、掌握數(shù)據(jù)審核數(shù)據(jù)審核的方法的方法3 3、掌握、掌握模擬運(yùn)算表模擬運(yùn)算表的建立方法的建立方法4 4、掌握、掌握單變量求解單變量求解的方法的方法5 5、掌握、掌握方案方案建立和應(yīng)用方法建立和應(yīng)用方法6 6、掌握、掌握規(guī)劃求解規(guī)劃求解的方法的方法7 7、掌握、掌握分析工具庫(kù)分析工具庫(kù)中的常用工具中的常用工具8.1 數(shù)據(jù)審核數(shù)據(jù)審核及跟蹤分析及跟蹤分析1、概念、概念n數(shù)據(jù)審核數(shù)據(jù)審核是一種查找單元格數(shù)據(jù)錯(cuò)誤來(lái)源的工是一種查找單元格數(shù)據(jù)錯(cuò)誤來(lái)源的工具，通過(guò)它可以快速地找出具有引用關(guān)系的單具，通過(guò)它可以快速地找出具

2、有引用關(guān)系的單元格，借此分析造成錯(cuò)誤的單元格。元格，借此分析造成錯(cuò)誤的單元格。n數(shù)據(jù)審核使用數(shù)據(jù)審核使用追蹤箭頭追蹤箭頭，通過(guò)圖形的方式顯示，通過(guò)圖形的方式顯示或追蹤單元格與公式之間的關(guān)系?；蜃粉檰卧衽c公式之間的關(guān)系。 2、數(shù)據(jù)審核的方式、數(shù)據(jù)審核的方式n追蹤引用單元格追蹤引用單元格 n追蹤從屬單元格追蹤從屬單元格8.1.1追蹤引用追蹤引用單元格單元格n引用單元格引用單元格n是指被其他單元格中的公式引用的單元格，即是指被其他單元格中的公式引用的單元格，即為指定公式提供數(shù)據(jù)的單元格。為指定公式提供數(shù)據(jù)的單元格。n當(dāng)使用當(dāng)使用“追蹤引用單元格追蹤引用單元格”工具時(shí)，工具時(shí)，Excel會(huì)會(huì)用用藍(lán)色

3、追蹤箭頭藍(lán)色追蹤箭頭指明活動(dòng)工作表中為公式提供指明活動(dòng)工作表中為公式提供數(shù)據(jù)的單元格。如果選定單元格引用了其他工數(shù)據(jù)的單元格。如果選定單元格引用了其他工作表或工作簿中的單元格，作表或工作簿中的單元格，Excel會(huì)用會(huì)用黑色追黑色追蹤箭頭蹤箭頭從工作表圖標(biāo)指向選定的單元格。從工作表圖標(biāo)指向選定的單元格。【例例8.1】某商場(chǎng)的銷售記錄和銷售匯總表如圖所示。查某商場(chǎng)的銷售記錄和銷售匯總表如圖所示。查看銷售匯總表中各數(shù)據(jù)的來(lái)源是否正確?？翠N售匯總表中各數(shù)據(jù)的來(lái)源是否正確。8.1.1追蹤引用追蹤引用單元格單元格單擊單擊公式公式選項(xiàng)卡選項(xiàng)卡單擊包括有公式的單元格單擊追蹤引用單元格單擊追蹤引用單元格8.1.

4、2追蹤從屬追蹤從屬單元格單元格n從屬單元格從屬單元格n如果某個(gè)單元格中的公式引用了其它單元格，如果某個(gè)單元格中的公式引用了其它單元格，那么此單元格就稱為從屬單元格。那么此單元格就稱為從屬單元格。n當(dāng)公式中所引用單元格的值發(fā)生變化時(shí)，公式當(dāng)公式中所引用單元格的值發(fā)生變化時(shí)，公式所在單元格（從屬單元格）中的值也會(huì)隨之變所在單元格（從屬單元格）中的值也會(huì)隨之變化。例如，若化。例如，若B3單元格中包含公式單元格中包含公式“=A3+A4”，則，則B3就是就是A3和和A4的從屬單元的從屬單元格。當(dāng)格。當(dāng)A3或或A4單元格的值發(fā)生變化時(shí)，單元格的值發(fā)生變化時(shí)，B3單單元格的值就會(huì)發(fā)生變化。元格的值就會(huì)發(fā)生變

5、化。 8.1.1追蹤引用追蹤引用單元格單元格單擊單擊公式公式選項(xiàng)卡選項(xiàng)卡單擊包括要追蹤的單元格單擊追蹤從屬單元格單擊追蹤從屬單元格8.1.2 數(shù)據(jù)數(shù)據(jù)有效性檢驗(yàn)有效性檢驗(yàn)數(shù)據(jù)有效性數(shù)據(jù)有效性 n數(shù)據(jù)有效性檢驗(yàn)是數(shù)據(jù)有效性檢驗(yàn)是Excel為減少錯(cuò)誤、核查數(shù)據(jù)正確性為減少錯(cuò)誤、核查數(shù)據(jù)正確性而提供的一種數(shù)據(jù)檢驗(yàn)工具。利用該工具可以設(shè)置單元而提供的一種數(shù)據(jù)檢驗(yàn)工具。利用該工具可以設(shè)置單元格數(shù)據(jù)輸入的類型和范圍，對(duì)錯(cuò)誤的輸入數(shù)據(jù)進(jìn)行告警，格數(shù)據(jù)輸入的類型和范圍，對(duì)錯(cuò)誤的輸入數(shù)據(jù)進(jìn)行告警，并拒不接受，把錯(cuò)誤限制在輸入階段。此外，它還能對(duì)并拒不接受，把錯(cuò)誤限制在輸入階段。此外，它還能對(duì)已經(jīng)完成的數(shù)據(jù)表設(shè)置

6、有效性檢驗(yàn)規(guī)則，并據(jù)此標(biāo)識(shí)出已經(jīng)完成的數(shù)據(jù)表設(shè)置有效性檢驗(yàn)規(guī)則，并據(jù)此標(biāo)識(shí)出其中的錯(cuò)誤數(shù)據(jù)。其中的錯(cuò)誤數(shù)據(jù)。 n案例案例【例例8.2】某班要建立一個(gè)成績(jī)登記表，為了減少成績(jī)輸入錯(cuò)誤，某班要建立一個(gè)成績(jī)登記表，為了減少成績(jī)輸入錯(cuò)誤，可對(duì)成績(jī)表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制。可對(duì)成績(jī)表中數(shù)據(jù)的輸入類型及范圍進(jìn)行限制。n限制學(xué)號(hào)為限制學(xué)號(hào)為8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制所有學(xué)科成績(jī)?yōu)橄拗扑袑W(xué)科成績(jī)?yōu)?100之間的整數(shù)。之間的整數(shù)。n限制科目列標(biāo)題的取值范圍，如限制科目列標(biāo)題的取值范圍，如“高數(shù)高數(shù)”不能輸入為不能輸入為“高等數(shù)高等數(shù)學(xué)學(xué)”。 8.1

7、.3 數(shù)據(jù)數(shù)據(jù)有效性檢驗(yàn)有效性檢驗(yàn)n成績(jī)表示例成績(jī)表示例8.1. 2 數(shù)據(jù)數(shù)據(jù)有效性檢驗(yàn)有效性檢驗(yàn)n限制方法限制方法n單擊單擊“數(shù)據(jù)數(shù)據(jù)”選項(xiàng)卡中的選項(xiàng)卡中的“數(shù)據(jù)有效性數(shù)據(jù)有效性”按鈕按鈕限定數(shù)據(jù)類型限定數(shù)據(jù)類型限定方式限定方式設(shè)置數(shù)據(jù)大小范圍或長(zhǎng)度設(shè)置數(shù)據(jù)大小范圍或長(zhǎng)度8.1.2 數(shù)據(jù)數(shù)據(jù)有效性檢驗(yàn)有效性檢驗(yàn)n圈釋無(wú)效數(shù)據(jù)圈釋無(wú)效數(shù)據(jù) 1、按前面介紹的方、按前面介紹的方法首先設(shè)置數(shù)據(jù)的法首先設(shè)置數(shù)據(jù)的有效值范圍有效值范圍:0-100；2、單擊“數(shù)據(jù)有效性”右邊的下箭頭，選擇“圈釋無(wú)效數(shù)據(jù)圈釋無(wú)效數(shù)據(jù)”Excel就會(huì)將不就會(huì)將不符合有效性規(guī)則符合有效性規(guī)則的數(shù)據(jù)圈釋出來(lái)的數(shù)據(jù)圈釋出來(lái)8.1.

8、2 數(shù)據(jù)數(shù)據(jù)有效性檢驗(yàn)有效性檢驗(yàn)n設(shè)置輸入提示信息和錯(cuò)誤警告信息設(shè)置輸入提示信息和錯(cuò)誤警告信息n這些信息都需要通過(guò)這些信息都需要通過(guò)“數(shù)據(jù)有效性數(shù)據(jù)有效性”對(duì)話框進(jìn)對(duì)話框進(jìn)行設(shè)置。行設(shè)置。8.2 模擬模擬運(yùn)算表運(yùn)算表1、概念、概念n模擬運(yùn)算表模擬運(yùn)算表是對(duì)工作表中一個(gè)單元格區(qū)域內(nèi)的是對(duì)工作表中一個(gè)單元格區(qū)域內(nèi)的數(shù)據(jù)進(jìn)行數(shù)據(jù)進(jìn)行模擬運(yùn)算模擬運(yùn)算，測(cè)試使用一個(gè)或兩個(gè)變量，測(cè)試使用一個(gè)或兩個(gè)變量的公式中變量對(duì)運(yùn)算結(jié)果的影響。的公式中變量對(duì)運(yùn)算結(jié)果的影響。2、模擬運(yùn)算表的類型、模擬運(yùn)算表的類型n基于一個(gè)輸入變量的表，用這個(gè)輸入變量測(cè)基于一個(gè)輸入變量的表，用這個(gè)輸入變量測(cè)試它對(duì)多個(gè)公式的影響；試它對(duì)多個(gè)

9、公式的影響；單模擬運(yùn)算表單模擬運(yùn)算表n基于兩個(gè)輸入變量的表，用這兩個(gè)變量測(cè)試基于兩個(gè)輸入變量的表，用這兩個(gè)變量測(cè)試它們對(duì)于單個(gè)公式的影響它們對(duì)于單個(gè)公式的影響雙模擬運(yùn)算表雙模擬運(yùn)算表8.2.1 單變量模擬單變量模擬運(yùn)算表運(yùn)算表n單變量模擬運(yùn)算表概念單變量模擬運(yùn)算表概念n在在單變量單變量模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一模擬運(yùn)算表中，輸入數(shù)據(jù)的值被安排在一行或一列中。同時(shí)，單變量模擬表中使用的公式必行或一列中。同時(shí)，單變量模擬表中使用的公式必須引用須引用“輸入單元格輸入單元格”。所謂輸入單元格，就是被。所謂輸入單元格，就是被替換的含有輸入數(shù)據(jù)的單元格替換的含有輸入數(shù)據(jù)的單元格 n案例案例n【例

10、例8.3】假設(shè)某人正考慮購(gòu)買(mǎi)一套住房，要承擔(dān)假設(shè)某人正考慮購(gòu)買(mǎi)一套住房，要承擔(dān)一筆一筆250 000元的貸款，分元的貸款，分15年還清?，F(xiàn)想查看每年還清?，F(xiàn)想查看每月的還貸金額，并想查看在不同的利率下，每月的月的還貸金額，并想查看在不同的利率下，每月的應(yīng)還貸金額。應(yīng)還貸金額。8.2.1 單變量模擬單變量模擬運(yùn)算表運(yùn)算表1、建立模擬運(yùn)算表、建立模擬運(yùn)算表3、C4的公式中引用了的公式中引用了B4單元格，在實(shí)際計(jì)算單元格，在實(shí)際計(jì)算時(shí)，將用時(shí)，將用B列列B5:B11的值逐一代替公式中的值逐一代替公式中的的B4。2、選擇、選擇“數(shù)據(jù)數(shù)據(jù)”“假設(shè)分析假設(shè)分析” “數(shù)據(jù)表數(shù)據(jù)表”8.2.1 單變量模擬單變

11、量模擬運(yùn)算表運(yùn)算表【例例8.4】對(duì)于例對(duì)于例8.3而言，如果要查看在同等利息情況下，而言，如果要查看在同等利息情況下，分別貸款￥分別貸款￥250 000，￥，￥400 000，￥，￥550 000，￥800 000的每月還貸金額，則可建立如圖的每月還貸金額，則可建立如圖8.5所示的模所示的模擬運(yùn)算表。擬運(yùn)算表。1、在、在B4輸入模擬計(jì)算公式：輸入模擬計(jì)算公式：=PMT($A$4/12,12*15,B1)2、將將B4中的公式向右復(fù)制中的公式向右復(fù)制3、選中選中A4:E12，建立模擬運(yùn)算表，建立模擬運(yùn)算表其中，引用列為其中，引用列為$A$48.2.2 雙變量模擬雙變量模擬運(yùn)算表運(yùn)算表n單變量模擬運(yùn)

12、算表只能解決一個(gè)輸入變量單變量模擬運(yùn)算表只能解決一個(gè)輸入變量對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響，如果對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響，如果想查看兩個(gè)變量對(duì)公式計(jì)算的影響就需要想查看兩個(gè)變量對(duì)公式計(jì)算的影響就需要使用使用雙變量模擬運(yùn)算表雙變量模擬運(yùn)算表。n案例案例n假設(shè)某人想貸款假設(shè)某人想貸款45萬(wàn)元購(gòu)買(mǎi)一部車，要查看在萬(wàn)元購(gòu)買(mǎi)一部車，要查看在不同的利率和不同的償還年限下，每個(gè)月應(yīng)還不同的利率和不同的償還年限下，每個(gè)月應(yīng)還的貸款金額。假設(shè)要查看貸款利率為的貸款金額。假設(shè)要查看貸款利率為5%、5.5%、6.5%、7%、7.5%、8%，償還期，償還期限為限為10年、年、15年、年、20年、年、30年、年、

13、35年時(shí)，每年時(shí)，每月應(yīng)歸還的貸款金額是多少月應(yīng)歸還的貸款金額是多少 8.2.2 雙變量模擬雙變量模擬運(yùn)算表運(yùn)算表單擊單擊“數(shù)數(shù)據(jù)據(jù)”“假設(shè)假設(shè)分析分析”“數(shù)數(shù)據(jù)表?yè)?jù)表”=PMT(B1/12,B2*12,D1)8.3 單變量單變量求解求解1、概念、概念n所謂單變量求解，就是求解具有一個(gè)變量的方所謂單變量求解，就是求解具有一個(gè)變量的方程，程，Excel通過(guò)調(diào)整可變單元格中的數(shù)值，使通過(guò)調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來(lái)滿足目標(biāo)單元格中的目標(biāo)之按照給定的公式來(lái)滿足目標(biāo)單元格中的目標(biāo)值值.n案例案例n【例例8.6】某公司想向銀行貸款某公司想向銀行貸款900萬(wàn)元人民萬(wàn)元人民幣，貸款利率是幣，

14、貸款利率是8.7%，貸款限期為，貸款限期為8年，每年，每年應(yīng)償還多少金額？如果公司每年可償還年應(yīng)償還多少金額？如果公司每年可償還120萬(wàn)元，該公司最多可貸款多少金額？萬(wàn)元，該公司最多可貸款多少金額？ 8.3 單變量單變量求解求解2、單變量求解方法、單變量求解方法（1）建立求解公式：）建立求解公式：（2）設(shè)置求解公式（3）求解結(jié)果單擊單擊“數(shù)據(jù)數(shù)據(jù)”“假假設(shè)分析設(shè)分析”“單變量單變量求解求解”8.4 方案方案分析分析1、概念、概念n方案是已命名的一組輸入值，是方案是已命名的一組輸入值，是 Excel 保存在工作表中并保存在工作表中并可用來(lái)自動(dòng)替換某個(gè)計(jì)算模型的輸入值，用來(lái)預(yù)測(cè)模型的可用來(lái)自動(dòng)替換

15、某個(gè)計(jì)算模型的輸入值，用來(lái)預(yù)測(cè)模型的輸出結(jié)果。輸出結(jié)果。 2、案例、案例n【例例8.7】已知某茶葉公司已知某茶葉公司2001年的總銷售額及各種茶年的總銷售額及各種茶葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個(gè)五年計(jì)劃。由于葉的銷售成本，現(xiàn)要在此基礎(chǔ)上制訂一個(gè)五年計(jì)劃。由于市場(chǎng)竟?fàn)幍牟粩嘧兓?，所以只能?duì)總銷售額及各種茶葉銷市場(chǎng)竟?fàn)幍牟粩嘧兓?，所以只能?duì)總銷售額及各種茶葉銷售成本的增長(zhǎng)率做一些估計(jì)。最好的方案當(dāng)然是總銷售額售成本的增長(zhǎng)率做一些估計(jì)。最好的方案當(dāng)然是總銷售額增長(zhǎng)率高，各茶葉的銷售成本增長(zhǎng)率低。增長(zhǎng)率高，各茶葉的銷售成本增長(zhǎng)率低。 n最好的估計(jì)是總銷售額增長(zhǎng)最好的估計(jì)是總銷售額增長(zhǎng)13%，花茶

16、、綠茶、烏龍茶、，花茶、綠茶、烏龍茶、紅茶的銷售成本分別增長(zhǎng)紅茶的銷售成本分別增長(zhǎng)10%、6%、10%、7%。 8.4 方案方案分析分析n建立方案解決工作表建立方案解決工作表建立方法如下建立方法如下1、輸入下表、輸入下表A列、列、B列及第列及第3行的所有數(shù)據(jù)；行的所有數(shù)據(jù)；2、在在C4單元格中輸入公式單元格中輸入公式“=B4*(1+$B$16)”，然后將其復(fù)制到，然后將其復(fù)制到D4F4；3、在在C7中輸入公式中輸入公式“=B7*(1+$B$17)”,并將其復(fù)制到并將其復(fù)制到D7F7；4、在在C8中輸入公式中輸入公式“=B8*(1+$B$18)”，并將其復(fù)制到并將其復(fù)制到D8F8；5、在在C9中

17、輸入公式中輸入公式“=B9*(1+$B$19)”，并將其復(fù)制到并將其復(fù)制到D9F9；6、在在C10中輸入公式中輸入公式“=B9*(1+$B$20)”，并將其復(fù)制到，并將其復(fù)制到D10F10；7、第第11行數(shù)據(jù)是第行數(shù)據(jù)是第7，8，9，10行數(shù)據(jù)對(duì)行數(shù)據(jù)對(duì)應(yīng)列之和；凈收入是相應(yīng)的總銷售額和銷售應(yīng)列之和；凈收入是相應(yīng)的總銷售額和銷售成本之差，成本之差，E19的總凈收入是第的總凈收入是第13行數(shù)據(jù)之行數(shù)據(jù)之和。和。 8.4 方案方案分析分析1、單擊“數(shù)據(jù)數(shù)據(jù)”2、單擊“模擬分析模擬分析”|”方案管理器方案管理器“3、單擊“添加添加“4、輸入“方案名稱方案名稱“5、指定“方案可變單元格方案可變單元格“

18、6、確定后見(jiàn)到下頁(yè)、確定后見(jiàn)到下頁(yè)“8.4 方案方案分析分析n輸入方案變量輸入方案變量8.4 方案方案分析分析n建立方案報(bào)告建立方案報(bào)告8.4 方案方案分析分析n建立方案透視圖建立方案透視圖8.5 加載宏工具加載宏工具的安裝的安裝1.加載宏的概念加載宏的概念n加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組加載宏是一種可選擇性地安裝到計(jì)算機(jī)中的軟件組件，用戶可根據(jù)需要決定是否安裝。其作用是為件，用戶可根據(jù)需要決定是否安裝。其作用是為 Excel 添加命令和函數(shù)，擴(kuò)充添加命令和函數(shù)，擴(kuò)充Excel的功能。的功能。nExcel2010加載宏的擴(kuò)展名是加載宏的擴(kuò)展名是.xlam。n在默認(rèn)情況下，在默認(rèn)情

19、況下，Excel將表將表9-1列出的加載宏程序安列出的加載宏程序安裝在如下某一磁盤(pán)位置：裝在如下某一磁盤(pán)位置：“Microsoft OfficeOffice”文件夾下的文件夾下的“Library”文件夾或其文件夾或其子文件夾，或子文件夾，或 Windows 所在文件夾下的所在文件夾下的“Profiles用戶名用戶名Application DataMicrosoftAddIns”文件夾下。網(wǎng)絡(luò)管理員文件夾下。網(wǎng)絡(luò)管理員也可將加載宏程序安裝到其他位置。也可將加載宏程序安裝到其他位置。 8.5 加載宏工具加載宏工具的安裝的安裝加 載 宏描 述分析工具庫(kù)添加財(cái)務(wù)、統(tǒng)計(jì)和工程分析工具和函數(shù)歐元工具將數(shù)值

20、的格式設(shè)置為歐元的格式，并提供EUROCONVERT函數(shù)以用于轉(zhuǎn)換貨幣查閱向?qū)?chuàng)建一個(gè)公式，通過(guò)數(shù)據(jù)清單中的已知值查找所需數(shù)據(jù)ODBC 加載宏利用安裝的 ODBC 驅(qū)動(dòng)程序，通過(guò)開(kāi)放式數(shù)據(jù)庫(kù)互連（ODBC）功能與外部數(shù)據(jù)源相連報(bào)告管理器為工作簿創(chuàng)建含有不同打印區(qū)域、自定義視面以及方案的報(bào)告規(guī)劃求解對(duì)基于可變單元格和條件單元格的假設(shè)分析方案進(jìn)行求解計(jì)算模板工具提供 Excel 的內(nèi)置模板所使用的工具。使用內(nèi)置模板時(shí)就可自動(dòng)訪問(wèn)這些工具Internet Assistant VBA通過(guò)使用 Excel 97 Internet Assistant 語(yǔ)法，開(kāi)發(fā)者可將 Excel 數(shù)據(jù)發(fā)布到 Web 上8

21、.5 加載宏工具加載宏工具的安裝的安裝n3、安裝分析工具、安裝分析工具n（1）單擊）單擊 “文件文件”“選項(xiàng)選項(xiàng)”。 n（2）在彈出的）在彈出的“Excel選項(xiàng)選項(xiàng)”對(duì)話框中對(duì)話框中，單擊，單擊“選項(xiàng)選項(xiàng)”，然后在然后在“管理管理”框中，選擇框中，選擇“Excel 加載項(xiàng)加載項(xiàng)”，然后單擊，然后單擊“轉(zhuǎn)到轉(zhuǎn)到”。Excel會(huì)彈出圖示的會(huì)彈出圖示的“加載宏加載宏”對(duì)話框。對(duì)話框。n（3）在）在“加載宏加載宏”框?qū)υ捴校x中要安裝的加載宏?？?qū)υ捴?，選中要安裝的加載宏。 8.6 規(guī)劃規(guī)劃求解求解1、規(guī)劃求解問(wèn)題的特點(diǎn)：、規(guī)劃求解問(wèn)題的特點(diǎn)：n問(wèn)題有單一的問(wèn)題有單一的目標(biāo)目標(biāo)，如求運(yùn)輸?shù)淖罴崖肪€、求

22、，如求運(yùn)輸?shù)淖罴崖肪€、求生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品生產(chǎn)的最低成本、求產(chǎn)品的最大盈利，求產(chǎn)品周期的最短時(shí)間等。周期的最短時(shí)間等。n問(wèn)題有明確的問(wèn)題有明確的不等式約束條件不等式約束條件，例如生產(chǎn)材料，例如生產(chǎn)材料不能超過(guò)庫(kù)存，生產(chǎn)周期不能超過(guò)一個(gè)星期等。不能超過(guò)庫(kù)存，生產(chǎn)周期不能超過(guò)一個(gè)星期等。n問(wèn)題有直接或間接影響約束條件的一組問(wèn)題有直接或間接影響約束條件的一組輸入值。輸入值。 8.6 規(guī)劃規(guī)劃求解求解2、Excel規(guī)劃求解問(wèn)題由以下規(guī)劃求解問(wèn)題由以下3部分組成部分組成 n（1）可變單元格）可變單元格n（2）目標(biāo)函數(shù)）目標(biāo)函數(shù)n（3）約束條件）約束條件8.6.2 建立規(guī)劃建立規(guī)劃求

23、解模型求解模型3、案例、案例n【例例8.8】某肥料廠專門(mén)收集有機(jī)物垃圾，如某肥料廠專門(mén)收集有機(jī)物垃圾，如青草、樹(shù)枝、凋謝的花朵等。該廠利用這些廢青草、樹(shù)枝、凋謝的花朵等。該廠利用這些廢物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來(lái)生產(chǎn)高物，并摻進(jìn)不同比例的泥土和礦物質(zhì)來(lái)生產(chǎn)高質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、質(zhì)量的植物肥料，生產(chǎn)的肥料分為底層肥料、中層肥料、上層肥料、劣質(zhì)肥料中層肥料、上層肥料、劣質(zhì)肥料4種。為使問(wèn)種。為使問(wèn)題簡(jiǎn)單，假設(shè)收集廢物的勞動(dòng)力是自愿的，除題簡(jiǎn)單，假設(shè)收集廢物的勞動(dòng)力是自愿的，除了收集成本之外，材料成本是低廉的。了收集成本之外，材料成本是低廉的。n該廠目前的原材料、生產(chǎn)各種肥

24、料需要的原材該廠目前的原材料、生產(chǎn)各種肥料需要的原材料比例，各種肥料的單價(jià)等如下各表所示。料比例，各種肥料的單價(jià)等如下各表所示。n問(wèn)題：求出在現(xiàn)有的情況下，即利用原材料的問(wèn)題：求出在現(xiàn)有的情況下，即利用原材料的現(xiàn)有庫(kù)存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量現(xiàn)有庫(kù)存，應(yīng)生產(chǎn)各種類型的肥料各多少數(shù)量才能獲得最大利潤(rùn)，最大利潤(rùn)是多少？才能獲得最大利潤(rùn)，最大利潤(rùn)是多少？ 8.6.2 建立規(guī)劃建立規(guī)劃求解模型求解模型表2 生產(chǎn)肥料的庫(kù)存原材料庫(kù)存情況現(xiàn)有庫(kù)存泥土4100有機(jī)垃圾3200礦物質(zhì)3500修剪物1600表1 各肥料成品用料及其價(jià)格表產(chǎn)品泥土有機(jī)垃圾礦物質(zhì)修剪物單價(jià)底層肥料55547623105.00

25、中層肥料6432452084.00上層肥料43329844105.00劣質(zhì)肥料1845231857.00表表3單位原材料成本單價(jià)單位原材料成本單價(jià)項(xiàng)項(xiàng) 目目單位成本單位成本泥土泥土0.20有機(jī)垃圾有機(jī)垃圾0.15礦物質(zhì)礦物質(zhì)0.10修剪物修剪物0.238.6.2 建立規(guī)劃建立規(guī)劃求解模型求解模型n規(guī)劃求解第一步規(guī)劃求解第一步建立求解工作表建立求解工作表n規(guī)劃求解第二步規(guī)劃求解第二步設(shè)置求解參數(shù)設(shè)置求解參數(shù)8.6.2 建立規(guī)劃建立規(guī)劃求解模型求解模型2、單擊、單擊“規(guī)則求解規(guī)則求解”若此處無(wú)若此處無(wú)“規(guī)則求解規(guī)則求解”命令，命令，須按須按8.5節(jié)的介紹安裝節(jié)的介紹安裝“規(guī)則規(guī)則求解求解”加載宏加

26、載宏單擊“數(shù)據(jù)數(shù)據(jù)”選項(xiàng)卡2、設(shè)置、設(shè)置“規(guī)則求解規(guī)則求解參數(shù)參數(shù)”8.6.3 規(guī)劃規(guī)劃求解求解n規(guī)劃求解第規(guī)劃求解第3步步求解求解8.6.4 修改資源修改資源和約束條件和約束條件n肥料廠接到一個(gè)電話：只要公司肯花肥料廠接到一個(gè)電話：只要公司肯花10元元的運(yùn)費(fèi)就能得到的運(yùn)費(fèi)就能得到150個(gè)單位的礦物。這筆個(gè)單位的礦物。這筆交易稍稍降低了礦物質(zhì)的平均價(jià)格，但這交易稍稍降低了礦物質(zhì)的平均價(jià)格，但這些礦物質(zhì)值些礦物質(zhì)值10元嗎？元嗎？n解決該問(wèn)題的方法是，將庫(kù)存礦物解決該問(wèn)題的方法是，將庫(kù)存礦物3 500改為改為3 650，用規(guī)劃求解重新計(jì)算最大盈余?？闯?，用規(guī)劃求解重新計(jì)算最大盈余。看除去￥去￥1

27、0的成本后，盈余是否增加的成本后，盈余是否增加 8.6.4 修改資源修改資源和約束條件和約束條件n修改約束條件修改約束條件 n肥料廠接到一個(gè)電話，一個(gè)老顧客急需肥料廠接到一個(gè)電話，一個(gè)老顧客急需25個(gè)單個(gè)單位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，位的上層肥料，公司經(jīng)理在檢查打印結(jié)果后，發(fā)現(xiàn)沒(méi)有安排生產(chǎn)上層肥料。決定增加約束條發(fā)現(xiàn)沒(méi)有安排生產(chǎn)上層肥料。決定增加約束條件，為他生產(chǎn)件，為他生產(chǎn)25個(gè)單位的上層肥料。個(gè)單位的上層肥料。增加的增加的約束條約束條件件8.6.5 規(guī)則規(guī)則求解報(bào)告求解報(bào)告n規(guī)劃求解的結(jié)果報(bào)告規(guī)劃求解的結(jié)果報(bào)告n運(yùn)算結(jié)果報(bào)告8.6.5 規(guī)則規(guī)則求解報(bào)告求解報(bào)告n敏感性報(bào)告敏感性

28、報(bào)告8.6.5 規(guī)則規(guī)則求解報(bào)告求解報(bào)告n極限報(bào)告8.6.6 求解不等式求解不等式n求解不等式求解不等式n 【例【例8.9】某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設(shè)生產(chǎn)某工廠生產(chǎn)甲、乙兩種產(chǎn)品，假設(shè)生產(chǎn)甲產(chǎn)品甲產(chǎn)品1噸，要消耗噸，要消耗9噸煤，噸煤，4千瓦電力，千瓦電力，3噸鋼材，噸鋼材，獲利獲利0.7萬(wàn)元；生產(chǎn)乙產(chǎn)品萬(wàn)元；生產(chǎn)乙產(chǎn)品1噸，要消耗噸，要消耗4噸煤，噸煤，5千瓦電力，千瓦電力，10噸鋼材，獲利噸鋼材，獲利1.2萬(wàn)元。按計(jì)劃國(guó)家萬(wàn)元。按計(jì)劃國(guó)家能提供給該廠的煤為能提供給該廠的煤為360噸，電力噸，電力200千瓦，鋼材千瓦，鋼材300噸，問(wèn)應(yīng)該生產(chǎn)多少噸甲種產(chǎn)品和乙種產(chǎn)品，噸，問(wèn)應(yīng)該生產(chǎn)多少噸

29、甲種產(chǎn)品和乙種產(chǎn)品，才能獲得最大利潤(rùn)？才能獲得最大利潤(rùn)？ 8.6.6 求解不等式求解不等式n假設(shè)生產(chǎn)甲種產(chǎn)品假設(shè)生產(chǎn)甲種產(chǎn)品x1噸，生產(chǎn)乙種產(chǎn)品噸，生產(chǎn)乙種產(chǎn)品x2噸，這個(gè)問(wèn)題的數(shù)學(xué)建模如下。最大利潤(rùn)噸，這個(gè)問(wèn)題的數(shù)學(xué)建模如下。最大利潤(rùn)是求是求S=0.7x1+1.2x2的最大值。的最大值。.0, 0.300103.20054.3604921212121xxxxxxxx用用EXCEL規(guī)劃求解如下：規(guī)劃求解如下：8.6.6 求解不等式求解不等式n不等式工作表設(shè)計(jì)不等式工作表設(shè)計(jì)8.6.6 求解不等式求解不等式8.7 數(shù)據(jù)分析數(shù)據(jù)分析工具庫(kù)工具庫(kù)1、概述、概述n分析工具庫(kù)分析工具庫(kù)由由Excel自帶

30、的自帶的加載宏加載宏提供，如果提供，如果啟動(dòng)啟動(dòng)Excel后，在后，在Excel的的“數(shù)據(jù)數(shù)據(jù)”選項(xiàng)卡中沒(méi)選項(xiàng)卡中沒(méi)有有“分析分析”邏輯組，邏輯組，就需通過(guò)就需通過(guò)“文件文件”“選項(xiàng)選項(xiàng)”打開(kāi)打開(kāi)“Excel選項(xiàng)選項(xiàng)”對(duì)話框?qū)υ捒?，再通過(guò)其中的，再通過(guò)其中的“加載加載項(xiàng)項(xiàng)”“管理管理”，將，將“分析工具庫(kù)分析工具庫(kù)”加載到加載到Excel系統(tǒng)中。系統(tǒng)中。nExcel的的“分析工具庫(kù)分析工具庫(kù)”加載宏還提供了一些加載宏還提供了一些統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)和工程函數(shù)。這些函數(shù)只有在安裝了有在安裝了“分析工具庫(kù)分析工具庫(kù)”后才能使用后才能使用 8.7 數(shù)據(jù)分析數(shù)據(jù)分

31、析工具庫(kù)工具庫(kù)分析工具名稱說(shuō) 明方差分析 包括3種類型的分析，它們是單因素方差分析、可重復(fù)雙因素分析、無(wú)重復(fù)雙因素分析相關(guān)系數(shù)分析用于判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。協(xié)方差分析用于返回各數(shù)據(jù)點(diǎn)的一對(duì)均值偏差之間的乘積的平均值。描述統(tǒng)計(jì)分析用于生成對(duì)輸入?yún)^(qū)域中數(shù)據(jù)的單變值分析，提供有關(guān)數(shù)據(jù)趨中性和易變性的信息指數(shù)平滑分析基于前期預(yù)測(cè)值導(dǎo)出相應(yīng)的新預(yù)測(cè)值，并修正前期預(yù)測(cè)值的誤差。傅里葉分析解決線性系統(tǒng)問(wèn)題，并能通過(guò)快速傅里葉變換（FFT）分析周期性的數(shù)據(jù)。F-檢驗(yàn)用來(lái)比較兩個(gè)樣本總體的方差直方圖分析在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計(jì)算數(shù)據(jù)的個(gè)別和累計(jì)頻率移動(dòng)平均

32、分析基于特定的過(guò)去某段時(shí)期中變量的均值，對(duì)未來(lái)值進(jìn)行預(yù)測(cè)t-檢驗(yàn)分析雙樣本等方差假設(shè)t-檢驗(yàn)，雙樣本異方差假設(shè)t-檢驗(yàn)，平均值的成對(duì)雙樣本t-檢驗(yàn)回歸分析通過(guò)對(duì)一組觀察值使用“最小二乘法”直線擬合，進(jìn)行線形回歸分析。抽樣分析以輸入?yún)^(qū)域?yàn)榭傮w構(gòu)造總體的一個(gè)樣本z-檢驗(yàn)雙樣本平均差檢驗(yàn)2、Excel分析工具庫(kù)中的工具分析工具庫(kù)中的工具8.7.1 統(tǒng)計(jì)統(tǒng)計(jì)分析分析n 統(tǒng)計(jì)分析工具庫(kù)統(tǒng)計(jì)分析工具庫(kù) nExcel的分析工具庫(kù)提供了的分析工具庫(kù)提供了3種統(tǒng)計(jì)觀測(cè)分析工具：指種統(tǒng)計(jì)觀測(cè)分析工具：指數(shù)平滑分析、移動(dòng)平均分析和回歸分析數(shù)平滑分析、移動(dòng)平均分析和回歸分析 n三種工具用法相同，以指數(shù)平滑分析為例說(shuō)明

33、其用法。三種工具用法相同，以指數(shù)平滑分析為例說(shuō)明其用法。（1）在工作表的一列上輸入各時(shí)間點(diǎn)上的觀察值，如下）在工作表的一列上輸入各時(shí)間點(diǎn)上的觀察值，如下圖圖A列所示。列所示。 8.7.1 統(tǒng)計(jì)統(tǒng)計(jì)分析分析（2）選擇）選擇“數(shù)據(jù)數(shù)據(jù)”|“數(shù)據(jù)分析數(shù)據(jù)分析”命令，從彈出的對(duì)命令，從彈出的對(duì)話框中選擇話框中選擇“指數(shù)平滑指數(shù)平滑”。 （3）設(shè)置）設(shè)置“輸入輸入”、“輸出輸出”選項(xiàng)，如下圖所示。選項(xiàng)，如下圖所示。8.7.2 假設(shè)假設(shè)分析分析n假設(shè)檢驗(yàn)假設(shè)檢驗(yàn) nExcel的分析工具庫(kù)中也提供了一些假設(shè)分析工具，如：的分析工具庫(kù)中也提供了一些假設(shè)分析工具，如：t-檢驗(yàn)、檢驗(yàn)、z-檢驗(yàn)、檢驗(yàn)、F-檢驗(yàn)。檢驗(yàn)。n運(yùn)用檢驗(yàn)工具可以完成均值、方差的假設(shè)檢驗(yàn)。運(yùn)用檢驗(yàn)工具可以完成均值、方差的假設(shè)檢驗(yàn)。 n案例案例【例例8.10】某種子公司為比較兩個(gè)稻種的產(chǎn)量，選擇了某種子公司為比較兩個(gè)稻種的產(chǎn)量，選擇了25塊條件相似的試驗(yàn)田，采用相同的耕種方法進(jìn)行耕種塊條件相似的試驗(yàn)田，采用相同的耕種方法進(jìn)行耕種試驗(yàn)，結(jié)果播種甲稻種的試驗(yàn)，結(jié)果播種甲稻種的13塊田的畝產(chǎn)量（單位：市斤）塊田的畝產(chǎn)量（單位：市斤）分別是：分別是：880、1 120、980、885、828、927、924、942、766、1 180、780、1 068、650；播種乙稻種；播種乙稻種的的12塊試驗(yàn)田的畝