Excel數(shù)據(jù)庫管理應用教程課件第七章 數(shù)據(jù)分析工具及應用

Excel數(shù)據(jù)管理應用教程第七章數(shù)據(jù)分析工具及應用第七章數(shù)據(jù)分析工具及應用7.1模擬運算表7.2單變量求解7.3方案分析7.4規(guī)劃求解7.5使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)7.1模擬運算表單變量模擬運算表雙變量模擬運算表模擬運算表的清除一、單變量模擬運算表1.單變量模擬運算表實例7.1利用單變量模擬運算表開展分期付款方式的銷售業(yè)務【實例引入】超市要進行一些改革，如對某些產(chǎn)品采取分期付款的方式進行銷售。假設有一液晶電視，每臺售價為98,000，采用分期付款的方式進行銷售，初步確定分期付款的方式為零首付，月分期手續(xù)費率為0.7％，求在不同的分期付款期數(shù)（月）下，每期（月）消費者需要付款的金額一、單變量模擬運算表【實例操作步驟】具體操作步驟如下：步驟1：創(chuàng)建工作表，建立基本的模擬運算工作表，輸入必要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的分期付款期數(shù)，如圖7-1所示。圖7-1單變量模擬運算表的初始數(shù)據(jù)一、單變量模擬運算表步驟2：創(chuàng)建運算公式在單元格D3中輸入公式“=PMT（B5,C3,B2）”，得出當分期付款期數(shù)為C3單元格中的數(shù)值時，消費者每月的付款額。如圖7-2所示。公式中B5為月分期手續(xù)費率，C3為分期付款期數(shù)，以月為單位，B2為付款本金。7-2單變量模擬運算表的運算公式一、單變量模擬運算表步驟3：建立單變量模擬運算表首先要選定公式、數(shù)值序列和模擬運算結果所在的單元格區(qū)域，即C3:D12，以定義這個模擬運算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運算表”命令，打開“模擬運算表”對話框，在“模擬運算表”對話框的“輸入引用列的單元格”文本框中輸入第一個變量所在的單元格地址“$C$3”，如圖7-3所示。圖7-3單變量模擬運算表的對話框

一、單變量模擬運算表【實例操作結果】最終的計算結果如圖7-4所示。圖7-4單變量模擬運算表的運算結果一、單變量模擬運算表2.單變量模擬運算表實例7.1其實屬于假設檢驗問題，即根據(jù)實際條件作出某種可能的假設，并建立數(shù)據(jù)表。然后通過進一步操作計算出多種情況下的值，并得到樣本數(shù)據(jù)，對假設的正確性做出判斷。從而可以通過查看和比較由工作表中的數(shù)據(jù)的不同變化所引起的各種結果，進一步取得結論。Excel提供了多種方法進行預測分析，模擬運算表是其中比較常用的一個工具。模擬運算表是對工作表中一個單元格區(qū)域內(nèi)的數(shù)據(jù)進行模擬運算，它可以顯示公式中某些值的變化對計算結果的影響。模擬運算表為同時求解某一運算中所有可能的變化值組合提供了方便，并且還可以將所有不同的計算結果同時顯示在工作表中，便于查看和比較。模擬運算表有兩種形式：單變量模擬運算表和雙變量模擬運算表。（1）基于一個輸入變量的表，用這個輸入變量測試它對公式的影響——單模擬運算表。（2）基于兩個輸入變量的表，用這兩個變量測試它們對于單個公式的影響——雙模擬運算表。一、單變量模擬運算表在實例7.1中，采用的是單變量模擬運算，其中“單變量”就是“月分期付款期數(shù)”。模擬運算表的使用一般包含三個步驟：創(chuàng)建工作表、創(chuàng)建運算公式和建立模擬運算表。單變量模擬運算必須包括輸入值和相應的結果值。運算表的輸入值要排在一列或一行中。如果輸入值被排在一行中，則稱為行引用；如果輸入值被排在一列中，則稱為列引用。如果是行引用，則在圖7-3單變量模擬運算表的對話框中的“輸入引用行的單元格”文本框中輸入第一個變量的單元格地址；如果是列引用，則在“輸入引用列的單元格”文本框輸入第一個變量的單元格地址。在實例7.1中所用的是列引用，所以應在模擬運算表對話框的“輸入引用列的單元格”文本框中輸入第一個變量所在的單元格地址“$C$3”。一、單變量模擬運算表使用模擬運算表進行計算后的結果都保存在數(shù)組中，如果選用任意計算結果所在的單元格，如單元格D7，則顯示為“{=表（，C3）}”，如圖7-5所示圖7-5單變量模擬運算表運算結果的顯示方式一、單變量模擬運算表再看模擬運算的工作原理，D3中的公式是“=PMT（B5,C3,B2）”，在這里，C3是一個初始變量，即所謂的單變量，在實際運算中，C3將被其他單元格的值替換（模擬），比如當計算D4中的值時，Excel將把單元格C4中的值替換C3，從而計算出分期付款期數(shù)為C4中的值時，消費者每月的付款額。二、雙變量模擬運算表1.雙變量模擬運算表。實例7.2利用雙變量模擬運算表開展分期付款方式的銷售業(yè)務【實例引入】單變量模擬運算只能模擬一種情況的變化，如果需要模擬更復雜的情況，則可以使用雙變量模擬運算。在雙變量模擬運算中可以模擬兩個值的變化得出不同的結果。在前面的例子中，僅僅把分期付款期數(shù)作為單變量進行模擬運算，但在現(xiàn)實生活中，月分期付款手續(xù)費率也是經(jīng)常要發(fā)生變化的。假設想查看在不同的分期付款期數(shù)和不同的月分期付款手續(xù)費率下消費者每月付款金額的變化情況，就必須建立雙變量模擬運算表了。二、雙變量模擬運算表【實例操作步驟】步驟1：創(chuàng)建工作表建立基本的運算工作表，輸入必要的數(shù)據(jù)及要測試的工作表中的數(shù)據(jù)，即不同的月分期付款手續(xù)費率和分期付款期數(shù)，其中，月分期付款手續(xù)費率屬于行變量，分期付款期數(shù)屬于列變量。如圖7-6所示。圖7-6雙變量模擬運算表的初始數(shù)據(jù)

二、雙變量模擬運算表

步驟2：創(chuàng)建運算公式雙變量模擬運算中首先要在行、列交叉處所在的單元格輸入運算公式。在本例中，在分期付款期數(shù)和月分期付款手續(xù)費率的交叉處，即D3單元格中輸入公式“=PMT(B5,B7,B2)”，運算結果如圖7-7所示。圖7-7雙變量模擬運算表的運算公式二、雙變量模擬運算表步驟3：建立雙變量模擬運算表首先要選定公式、數(shù)值序列和模擬運算結果所在的單元格區(qū)域，即D3:J13，以定義這個模擬運算表，然后選擇“數(shù)據(jù)”菜單中的“模擬運算表”命令，打開“模擬運算表”對話框，在“輸入引用行的單元格”中輸入月分期手續(xù)費率所在的單元格“$B$5”，在“輸入引用列的單元格”中輸入分期付款期數(shù)所在的單元格“$B$7”，如圖7-8所示。最后單擊“模擬運算表”對話框的“確定”按鈕。圖7-8雙變量模擬運算表的對話框

二、雙變量模擬運算表【實例操作結果】最終的計算結果如圖7-9所示。圖7-9雙變量模擬運算表的運算結果二、雙變量模擬運算表2.以上實例利用雙變量模擬運算表來計算在不同的分期付款期數(shù)下，消費者每月的付款金額。它的很多操作跟單變量模擬運算相似。要注意的是，在步驟2的公式“=PMT(B5,B7,B2)”中兩個變量所在的單元格B5和B7是任取的，它可以是工作表中的任意空白單元格。事實上，它只是一種形式，因為它的取值來源于輸入行或輸入列。在本例的公式中，B5和B7分別代表兩個變量，B5表示月分期手續(xù)費率，它代表月分期手續(xù)費率所在行的E3:J3單元格區(qū)域；B7表示分期付款期數(shù)，它代表分期付款期數(shù)所在列的D4:D13單元格區(qū)域。三、模擬運算表的清除模擬運算表的清除由于模擬運算表的數(shù)據(jù)是存放在數(shù)組中的，所以如果要清除模擬運算表中的計算結果，只能對其整個區(qū)域進行完全清除，而無法清除個別結果。具體操作步驟如下。選定計算結果所在的單元格區(qū)域，注意不能選定公式和輸入值，否則Excel將清除包括公式和輸入值在內(nèi)的整個表，選擇“編輯”菜單中的“清除”命令，在其子菜單中選擇“全部”命令，即可將整個計算結果清除。也可選定由模擬運算得到的區(qū)域，然后直接按Del鍵將其刪除。7.2單變量求解1.案例7.3利用單變量求解計算付款期數(shù)【案例引入】在上面模擬運算的例子中，主要是求出消費者在固定的分期手續(xù)費率和分期付款期數(shù)下每月應付的金額。假設現(xiàn)在有一消費者可負擔的每月的付款金額為￥5000，想知道在固定的月分期手續(xù)費率下，該消費者可承受的付款期數(shù)是多少？7.2單變量求解【案例操作步驟】步驟1：在單變量求解工作表中輸入原始數(shù)據(jù)。如圖7-10所示圖7-10單變量求解工作表初始數(shù)據(jù)的建立7.2單變量求解步驟2：在月付款金額對應的單元格B4中輸入公式“=PMT(B3,B5,B2)”，如圖7-11所示。圖7-11單變量求解工作表的運算公式7.2單變量求解步驟3：選擇“工具”菜單的“單變量求解”命令，打開“單變量求解”對話框，在“目標單元格”中輸入“$B$4”，在“目標值”中輸入“-5000”，表示經(jīng)過求解之后，單元格B4的值應是-5000，目標單元格必須包含公式，在“可變單元格”中輸入“$B$5”，即最后分期付款期數(shù)所在的單元格，如圖7-12所示。接著在單擊“確定”按鈕后，屏幕上可能出現(xiàn)出錯提示，表示無法進行求解，如圖7-13所示。圖7-12單變量求解對話框的數(shù)據(jù)輸入

圖7-13單變量求解的錯誤提示

7.2單變量求解在這里可以先給可變單元格設置一個值，比如5，因為其中的數(shù)值也要根據(jù)目標單元格中的結果而發(fā)生變化，所以事先給的值并不會影響到最終的計算結果。具體設置如圖7-14所示。步驟4：再打開“單變量求解”對話框，按上面同樣的設置，可得到“單變量求解狀態(tài)”對話框，表明求得一個解，如圖7-15所示。圖7-14單變量求解工作表中可變單元格的初始設置

圖7-15單變量求解狀態(tài)對話框

7.2單變量求解【案例操作結果】求解結果如圖7-16所示。圖7-16單變量求解的結果

7.2單變量求解2.所謂單變量求解，就是求解具有一個變量的方程，它通過調(diào)整可變單元格中的數(shù)值，使之按照給定的公式來滿足目標單元格中的目標值。要注意的是，在實例7.3的步驟3中，會出現(xiàn)圖7-13所示的出錯提示，表示無法進行求解，這是因為可變單元格是一空白單元格，而空白單元格的值被默認為0，所以目標單元格中通過公式計算的結果就出現(xiàn)了被0除的錯誤，因此先給可變單元格設置一個值，這個值可以是任意的。7.2單變量求解3.單變量求解與投資函數(shù)（NPER）的比較在第六章中，曾經(jīng)提到貸款定額還款期數(shù)計算函數(shù)NPER，其功能是基于固定利率及等額分期付款方式，返回一項投資或貸款的期數(shù)。對于實例7.3，也可以用NPER函數(shù)來解決。同樣地，對于第六章實例6.1提到的問題，也可以用單變量求解來計算。7.3方案分析方案概述顯示方案編輯方案建立方案摘要一、方案概述1.實例7.4超市收入預測方案【案例引入】假設已知超市在2007年的各部門的收入情況，現(xiàn)在想對2008年的收入情況做一個估計，分析在不同增長方案下的收入情況，這些增長方案包括經(jīng)營情況一般、經(jīng)營情況良好和經(jīng)營情況較差三種方案。一、方案概述【案例操作步驟】具體操作步驟如下：步驟1：制作如圖7-17所示的工作表，包括2007年度的收入情況，以及2008年度根據(jù)經(jīng)營情況（一般）設置的收入增長率。其中在單元格D3輸入公式“=C3-B3”，然后將其復制到D4、D5、D6；在單元格D11中輸入公式“=C3*(1+C11)-B3*(1+B11)”，并將其復制到D12、D13、D14；在單元格D15中輸入公式“=SUM(D11:D14)”。從圖中可以看出，在經(jīng)營情況一般這種方案下，2008年企業(yè)的總利潤為5052715。圖7-17超市收入預測數(shù)據(jù)一、方案概述步驟2：選擇“工具”菜單的“方案”命令，彈出“方案管理器”對話框，如圖7-18所示。圖7-18方案管理器對話框

一、方案概述步驟3：在“方案管理器”中單擊“添加”按鈕，彈出“編輯方案”對話框，在“方案名”中輸入方案名稱“經(jīng)營情況一般”；在“可變單元格”中輸入需要更改的單元格的引用“$B$11:$C$14”，選中“防止更改”復選項。如圖7-19所示。7-19編輯方案對話框一、方案概述步驟4：在圖7-19所示的對話框中單擊“確定”按鈕，將打開“方案變量值”對話框。在“方案變量值”對話框中分別輸入可變單元格所對應的單元格，如圖7-20所示。圖7-20方案變量值對話框

一、方案概述步驟5：在圖7-20所示的對話框中單擊“確定”按鈕，則該方案創(chuàng)建完成，返回“方案管理器”對話框，將顯示出所定義方案的名稱。如圖7-21所示。圖7-21添加方案后的方案管理器對話框一、方案概述步驟6：在“方案管理器”對話框中單擊“添加”按鈕，使用類似的步驟再創(chuàng)建兩個方案?！緦嵗僮鹘Y果】最終結果如圖7-22所示圖7-22添加所有方案后的方案管理器對話框一、方案概述2.單變量求解只能解決具有一個未知變量的問題。模擬運算表最多只能解決兩個變量引起的問題，如果要解決3個及以上變量發(fā)生變化時的問題，或要在幾種假設分析中找到最佳解決方案，可以使用方案來解決。在Excel中，方案是一組由Excel保存在工作表中并可進行自動替換的值。通過Excel的方案管理可以很方便地進行假設分析。在方案中可以為任意多的變量存儲輸入值的不同組合，并為每個組合命名?？梢愿鶕?jù)名稱選擇一組值，Excel顯示使用這些值的數(shù)據(jù)表。還可以創(chuàng)建匯總報告，顯示不同的值組合的效果。匯總報告可以是綱要也可以是數(shù)據(jù)透視表。二、顯示方案創(chuàng)建方案時，并不會顯示該方案的結果，如果想查看不同方案的執(zhí)行結果，使用“顯示”方案命令。具體執(zhí)行步驟如下：選擇“工具”菜單的“方案”命令，彈出“方案管理器”對話框，在該對話框中選擇方案名稱，然后單擊“顯示”按鈕即可顯示該方案的結果。三、編輯方案做好方案以后，可以隨時修改方案的名字，修改可變單元格的內(nèi)容，增加新的方案，刪除不再需要的方案。具體操作步驟如下：步驟1：選擇“工具”菜單的“方案”命令，彈出“方案管理器”對話框。步驟2：如果要刪除不需要的方案，可先選中該方案，然后單擊“刪除”按鈕。步驟3：如果要增加方案，單擊“添加”按鈕，然后添加相關的信息。步驟4：如果要修改已有方案，可先選中該方案，然后單擊“編輯”按鈕，出現(xiàn)“編輯方案”對話框，如圖7-19所示，在該對話框中可對方案名稱、可變單元格和備注等進行修改，單擊“確定”按鈕，將打開“方案變量值”對話框，修改變量值后單擊“確定”按鈕即可，如圖7-20所示。四、建立方案摘要使用“顯示”命令，一次只能查看一種方案的結果，當需要將所有的方案結果都顯示出來，以便于比較，尋求最佳方案，則可以通過建立方案摘要來實現(xiàn)。具體操作步驟如下：步驟1：打開包含方案的工作表，選擇“工具”菜單的“方案”命令，彈出“方案管理器”對話框，單擊“摘要”按鈕，Excel將打開“方案摘要”對話框，如圖7-23所示。圖7-23方案摘要對話框四、建立方案摘要步驟2：在“方案摘要”對話框的“結果單元格”中輸入保存方案執(zhí)行結果的單元格或單元格區(qū)域，然后單擊“確定”按鈕即可生成報表，如圖7-24所示。圖7-24方案摘要7.4規(guī)劃求解規(guī)劃求解概述規(guī)劃求解各參數(shù)說明規(guī)劃求解應用創(chuàng)建規(guī)劃求解報告一、規(guī)劃求解概述1.實例7.5利用規(guī)劃求解制定銷售決策【實例引入】假設超市某一部門要銷售兩種商品，其中A商品的采購價為900元，銷售價為2400元，B商品的采購價位800元，銷售價為1800元，而且兩種商品的物流和倉儲成本不同，A商品的物流成本和倉儲成本分別為30元和19.5元，B商品物流成本和倉儲成本分別為25元和6.5元，現(xiàn)在的問題是在每月的物流和倉儲費用固定的情況下（物流和倉儲總費用分別為2800元和1500元）。該如何分配A、B兩種商品的進貨數(shù)量，才能得到最大的銷售利潤？一、規(guī)劃求解概述【實例操作步驟】具體操作步驟如下：步驟1：建立商品數(shù)據(jù)表，如圖7-25所示。圖7-25商品數(shù)據(jù)表一、規(guī)劃求解概述步驟2：輸入公式，在B12單元格中輸入公式“=(B6-B5-B4-B3)*B7+(C6-C5-C4-C3)*C7”，在單元格B13和B14中分別輸入“=B4*B7+C4*C7”和“=B5*B7+C5*C7”。如圖7-26所示。圖7-26公式輸入一、規(guī)劃求解概述步驟3：建立好上面表格后，就可以利用規(guī)劃求解工具對表格的問題進行求解了。選擇“工具”菜單中的“規(guī)劃求解”命令，彈出“規(guī)劃求解參數(shù)”對話框，如圖7-27所示。在“設置目標單元格”文本框中輸入B12單元格，即要求的銷售最大利潤。在“等于”單選項中選擇“最大值”，表示所要求的目標函數(shù)的最大值。在“可變單元格”文本框中輸入B7:C7，就是目標函數(shù)的兩個變量，也就是兩種商品各自的進貨量。這里的引用均為絕對引用。圖7-27規(guī)劃求解參數(shù)對話框一、規(guī)劃求解概述步驟4：設置規(guī)劃求解約束條件。選擇“規(guī)劃求解參數(shù)”對話框中的“添加”按鈕，彈出“添加約束”對話框，在對話框中添加限制條件。對于物流總成本的限制，應該是物流總成本小于每月的物流費用控制，即B13≤B9。添加完該約束后，單擊對話框中的“添加”按鈕，接著添加其它的約束條件。最終添加完約束條件的結果如圖7-29所示。圖7-29規(guī)劃求解參數(shù)對話框

一、規(guī)劃求解概述步驟5：進行規(guī)劃求解。選擇“規(guī)劃求解參數(shù)”對話框中的“求解”按鈕，彈出“規(guī)劃求解結果”對話框。接著單擊“確定”按鈕，就可以看到規(guī)劃求解的求解結果，如圖7-31所示。圖7-31規(guī)劃求解的求解結果

一、規(guī)劃求解概述在圖7-31中求出的兩種商品的進貨數(shù)量都是小數(shù)，而實際的進貨數(shù)量應該都是整數(shù)。通過修改規(guī)劃求解的約束條件可以很快得到解決。具體操作步驟如下：按前面的方法進入“規(guī)劃求解參數(shù)”對話框（見圖7-29），選擇“約束”區(qū)域中要修改的約束條件，本例中是選擇商品A所對應的約束條件（$B$7>=0）；接著選擇“更改”按鈕，彈出“改變約束”對話框。然后再對話框的關系運算符中選擇“int”，如圖7-32所示。圖7-32對商品的進貨數(shù)量更改約束條件一、規(guī)劃求解概述通過同樣的方法對商品B的約束條件也進行修改，通過上面的設置后，再次進行規(guī)劃求解，得到的最終求解結果?！緦嵗僮鹘Y果】最終求解結果如圖7-34所示。圖7-34更改整數(shù)約束后的求解結果一、規(guī)劃求解概述2.“規(guī)劃求解”是Excel中的一個“加載宏”，借助“規(guī)劃求解”，可求得工作表上某個單元格（被稱為目標單元格）中公式的最優(yōu)值?！耙?guī)劃求解”將對直接或間接與目標單元格中公式相關聯(lián)的一組單元格中的數(shù)值進行調(diào)整，最終在目標單元格公式中求得期望的結果?！耙?guī)劃求解”通過調(diào)整所指定的可更改的單元格（可變單元格）中的值，從目標單元格公式中求得所需的結果。在創(chuàng)建模型的過程中，可以對“規(guī)劃求解”模型中的可變單元格數(shù)值應用約束條件?？梢詫⒓s束調(diào)劑應用于可變單元格、目標單元格或其他與目標單元格直接或間接相關的單元格。而且約束條件可以引用其他影響目標單元格公式的單元格。使用“規(guī)劃求解”可通過更改其他單元格來確定某個單元格的最大值或最小值。一、規(guī)劃求解概述在Excel的默認操作環(huán)境下，是沒有“規(guī)劃求解”命令按鈕的。在使用“規(guī)劃求解”之前必須先通過加載宏來加載“規(guī)劃求解”，具體步驟如下：在“工具”菜單上，單擊“加載宏”，在彈出的對話框中“可用加載宏”列表框中選中“規(guī)劃求解”選項，然后單擊“確定”按鈕。如圖7-35所示。圖7-35加載“規(guī)劃求解”對話框

一、規(guī)劃求解概述如果選中的功能是第一次使用，這時系統(tǒng)會提示要通過Excel安裝盤安裝該功能，如圖7-36所示。單擊“確定”按鈕，即可進入安裝過程。安裝完后，在“工具”菜單中就有“規(guī)劃求解”選項了。圖7-36提示對話框二、規(guī)劃求解各參數(shù)說明單擊“規(guī)劃求解”按鈕，將出現(xiàn)如圖7-37所示的規(guī)劃求解參數(shù)的對話框。圖7-37規(guī)劃求解參數(shù)對話框

二、規(guī)劃求解各參數(shù)說明1.設置目標單元格：一些單元格、具體數(shù)據(jù)和運算符號的組合。注意，目標單元格一定要是公式，即一定要以“=”開始。2.最大值、最小值：在此指定是否希望目標單元格為最大值、最小值或某一特定數(shù)值。如果需要指定數(shù)值，請在右側編輯框中鍵入該值。3.可變單元格：在此指定可變單元格。求解時其中的數(shù)值不斷調(diào)整，直到滿足約束條件并且“設置目標單元格”框中指定的單元格達到目標值?？勺儐卧癖仨氈苯踊蜷g接的和目標單元格相關聯(lián)。推測：4.約束：在此列出了規(guī)劃求解的所有約束條件。5.添加：顯示“添加約束”對話框。6.更改：顯示“更改約束”對話框。注意。單擊此按鈕時，要先選中需要更改的約束。二、規(guī)劃求解各參數(shù)說明7.刪除：刪除選定的約束條件。8.選項：顯示“規(guī)劃求解選項”對話框，如圖7-38所示。圖7-38規(guī)劃求解選項對話框二、規(guī)劃求解各參數(shù)說明在這個對話框中可以加載或保存規(guī)劃求解模型，并對求解過程的高級屬性進行設置，比如要設置求解的精度、結算時間、規(guī)劃模型、迭代次數(shù)等。其中“最長運算時間”用來設定求解過程的時間。可輸入的最大值為32767秒，默認值為100秒。一般來說默認的100秒已能夠滿足大多數(shù)小型規(guī)劃求解的要求了。對話框的“迭代次數(shù)”用來設定求解過程中所要進行的迭代次數(shù)，限制求解過程的時間。其最大值和默認值均與“最長運算時間”相同。對話框的“精度”用來控制求解精度的數(shù)字，以確定約束條件單元格中的數(shù)值是否滿足目標值或上下限，精度值必須為小數(shù)（0和1之間），輸入數(shù)字的位數(shù)越多，精度越高。例如，0.0001比0.001的精度高。對話框的“允許誤差”只應用于具有整數(shù)約束條件的問題。輸入的數(shù)值是滿足整數(shù)約束條件的目標單元格求解結果與最佳結果之間的允許百分比誤差。另外在該對話框中還可進行收斂度、迭代次數(shù)、差分等數(shù)學工具的選擇和設置。三、規(guī)劃求解應用在規(guī)劃求解中，比較重要的就是目標單元格的最大最小值設定（目標函數(shù)的設定），可變單元格和約束這三個部分。下面來分析一下實例7.5提出的問題。在規(guī)劃求解中，目標函數(shù)一般表示規(guī)劃求解要達到的最終目標，如最大利潤、最小成本、最短路徑、最佳產(chǎn)品搭配等。實例7.5提出的問題是求解最大的銷售利潤，也即該問題的目標函數(shù)是最后的所得利潤。目標函數(shù)與可變單元格有著直接或間接的聯(lián)系，是規(guī)劃求解的關鍵。可變單元格是實際問題中有待解決的基本因素，一個規(guī)劃求解中可能有一個變量，也可能有多個變量。可變單元格的設置非常關鍵，設置錯誤將導致規(guī)劃求解失敗。在實例7.5中，可變單元格就是A、B兩種商品的進貨數(shù)量。三、規(guī)劃求解應用約束條件實現(xiàn)目標的限制條件，規(guī)劃求解是否有解與約束條件有密切的關系，它對可變單元格中的值起著直接的限制作用。約束條件可以是等式，也可以是不等式。在實例7.5中，由于每月的物流和倉儲費用是固定，因此約束條件是物流和倉儲費用。另外因為要求的是兩種商品的進貨數(shù)量，所以兩個數(shù)量值都要是正數(shù)?，F(xiàn)在我們對實例7.5進行一些擴展，假設超市要做一些活動，需要至少38件B商品，則在同樣的費用情況下應如何安排兩種商品的進貨數(shù)量。對于這個問題，可以通過添加約束條件來解決。具體操作步驟如下：三、規(guī)劃求解應用按前面的方法進入“規(guī)劃求解參數(shù)”對話框（見圖7-29），選擇“添加”按鈕。在彈出的“添加約束”對話框中輸入新增的約束條件“C7>=38”。增加該約束條件后，該對話框中的約束條件如圖7-39所示。圖7-39添加新的約束條件三、規(guī)劃求解應用通過上面的設置后，再次進行規(guī)劃求解，得到的最終求解結果如圖7-40所示。圖7-40最終規(guī)劃求解結果四、創(chuàng)建規(guī)劃求解報告規(guī)劃求解可以創(chuàng)建以下3種類型的報告，每份報告被存放在工作簿中單獨的一張工作表中：（1）運算結果報告——列出目標單元格和可變單元格以及它們的初始值、最終結果、約束條件和有關約束條件的信息。（2）敏感性報告——顯示關于求解結果對于一些微小變化的敏感性信息。對于非線性模型，此報告提供縮減梯度和拉格朗日乘數(shù)；對于線性模型，此報告將含縮減成本、影子價格（機會成本）、目標系數(shù)（允許有小量增減額）以及約束區(qū)域。（3）極限報告——顯示目標單元格和可變單元格以及它們的數(shù)值、上下限和目標值。創(chuàng)建規(guī)劃求解報告的步驟如下：四、創(chuàng)建規(guī)劃求解報告在圖7-30的“規(guī)劃求解結果”對話框中，選擇“報告”列表中的一種報告類型（也可以同時選中多個報告），在本例中，選擇“運算結果報告”，然后單擊“確定”按鈕，Excel就會在工作表簿中新建一個工作表，然后將報告顯示在其中。具體結果如圖7-41所示。要注意的是含有整數(shù)約束條件的模型不能生成敏感性報告和極限報告。圖7-41運算結果報告

7.5使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)利用數(shù)據(jù)分析工具進行銷售預測利用數(shù)據(jù)分析工具進行數(shù)據(jù)統(tǒng)計數(shù)據(jù)分析工具的加載和使用

一、使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)1.實例7.6利用數(shù)據(jù)分析工具進行銷售預測【實例引入】超市有關部門要作如下的銷售預測。根據(jù)超市近7年來的銷售收入數(shù)據(jù)（如圖7-42所示），預測下一年（第8年）的銷售量。圖7-42超市近7年來的銷售數(shù)據(jù)

一、使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)【實例操作步驟】步驟1：選擇“工具”菜單中的“數(shù)據(jù)分析”命令，彈出“數(shù)據(jù)分析”對話框，從“分析工具列表”中選擇“回歸”選項，然后單擊“確定”按鈕，打開“回歸”對話框，如圖7-43所示。圖7-43回歸對話框

一、使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)步驟2：在“Y值輸入框”中輸入“$B$2:$B$8”，在“X值輸入框”中輸入“$A$2:$A$8”，在“輸出選項”中選“輸出區(qū)域”，并填入“$D$1”，然后根據(jù)實際需要，勾選其他需要的選項，如圖7-44所示。單擊“確定”按鈕，回歸分析的摘要就輸出在本工作表上。圖7-44設置回歸分析選項一、使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)【實例操作結果】最終求解結果如圖7-45所示。圖7-45回歸分析結果

一、使用數(shù)據(jù)分析工具庫分析數(shù)據(jù)2.回歸分析工具通過對一組觀察值使用“最小二乘法”直線擬合來執(zhí)行線性回歸分析。本分析工具可用來分析單個因變量是如何受一個或幾個自變量的值影響的。例如，觀察某個運動員的運動成績與一系列統(tǒng)計因素（如年齡、身高和體重等）的關系?？梢曰谝唤M已知的成績統(tǒng)計數(shù)據(jù)，確定這三個因素分別在運動成績測試中所占的比重，然后使用該結果對尚未進行過測試的運動員的表現(xiàn)進行預測。在線性回歸模型中，如果影響因變量的自變量只有一個，則稱為一元線性回歸；如果影響因變量的自變量有多個，則稱為多元線性回歸。回歸分析工具使用工作表函數(shù)LINEST。在Excel中，一元線性回歸和多元線性回歸都采用圖7-45的回歸模型進行求解。其差別在于樣本值所在數(shù)據(jù)區(qū)域的設置，對于一元回歸，輸入的X值區(qū)域只有一列數(shù)據(jù)，而多元回歸則有多列數(shù)據(jù)。二、利用數(shù)據(jù)分析工具進行數(shù)據(jù)統(tǒng)計1.實例7.7利用數(shù)據(jù)分析工具進行數(shù)據(jù)統(tǒng)計【實例引入】超市有關部門要作如下的數(shù)據(jù)統(tǒng)計。根據(jù)當月的員工加班情況（如圖7-46所示），統(tǒng)計出各加班段的人數(shù)。圖7-46超市員工加班情況二、利用數(shù)據(jù)分析工具進行數(shù)據(jù)統(tǒng)計【實例操作步驟】對于實例7.7所提出的問題，可以通過直方圖分析工具來解決。具體操作步驟如下：步驟1：建立如圖7-47所示的統(tǒng)計表圖7-47建立直方圖統(tǒng)計表

二、利用數(shù)據(jù)分析工具進行數(shù)據(jù)統(tǒng)計