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

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

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

二、雙變量模擬運(yùn)算表

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

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

圖7-13單變量求解的錯(cuò)誤提示

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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