Excel數(shù)據(jù)處理與分析實(shí)例教程(微課版第3版)-電子教案第11章分析數(shù)據(jù)

第11章分析數(shù)據(jù)教學(xué)內(nèi)容.模擬運(yùn)算表.單變量求解.方案分析.規(guī)劃求解教學(xué)要求【知識(shí)目標(biāo)】了解模擬運(yùn)算表的功能、特點(diǎn)和適應(yīng)范圍了解單變量求解的功能、特點(diǎn)和適應(yīng)范圍了解方案分析的功能、特點(diǎn)和適應(yīng)范圍了解規(guī)劃求解的功能、特點(diǎn)和適應(yīng)范圍【技能目標(biāo)】.掌握模擬運(yùn)算表數(shù)據(jù)分析工具的使用方法.單變量求解數(shù)據(jù)分析工具的使用方法.方案分析數(shù)據(jù)分析工具的使用方法.規(guī)劃求解數(shù)據(jù)分析工具的使用方法教學(xué)重點(diǎn).模擬運(yùn)算表.單變量求解.方案分析.規(guī)劃求解教學(xué)難點(diǎn).模擬運(yùn)算表.方案分析教學(xué)方法講授法、演示法―課時(shí)數(shù)-5課時(shí)（講授3課時(shí)，實(shí)驗(yàn)2課時(shí)）課程導(dǎo)入【思路】首先提出問題，然后組織學(xué)生進(jìn)行討論，最后總結(jié)同學(xué)們對(duì)數(shù)據(jù)分析工具的了解情況，引出數(shù)據(jù)分析的概念，說明數(shù)據(jù)分析工具的作用。【問題】.在Excel中，有哪些數(shù)據(jù)分析工具？.數(shù)據(jù)分析工具的作用是什么？.單變量模擬運(yùn)算和雙變量模擬運(yùn)算的主要區(qū)別是什么？在實(shí)際應(yīng)用中應(yīng)如何選擇？模擬運(yùn)算表模擬運(yùn)算表是對(duì)一個(gè)單元格區(qū)域中的數(shù)據(jù)進(jìn)行模擬運(yùn)算，測(cè)試在公式中使用變量時(shí)，變量值的變化對(duì)公式運(yùn)算結(jié)果的影響。在Excel中可以構(gòu)造2種類型的模擬運(yùn)算表：單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表。前者可以測(cè)試1個(gè)輸入變量的變化對(duì)公式的影響，后者可以測(cè)試2個(gè)輸入變量的變化對(duì)公式的影響。單變量模擬運(yùn)算表在單變量模擬運(yùn)算表中，輸入的數(shù)據(jù)值需要放在同一行或同一列中，并且單變量模擬運(yùn)算表中使用的公式必須要引用“輸入單元格”。輸入單元格是指模擬運(yùn)算表中其值不確定而需要用輸入行或輸入列的單元格中的值替換的單元格。雙變量模擬運(yùn)算表單變量模擬運(yùn)算表只能解決1個(gè)輸入變量對(duì)一個(gè)或多個(gè)公式計(jì)算結(jié)果的影響，如果想查看2個(gè)輸入變量變化對(duì)公式計(jì)算結(jié)果的影響就需要用到雙變量模擬運(yùn)算表。單變量求解模擬運(yùn)算表可以實(shí)現(xiàn)模擬不同變量的變化，計(jì)算可能達(dá)到的最終目標(biāo)。有些應(yīng)用需求恰恰相反，需要根據(jù)預(yù)先設(shè)定的目標(biāo)來分析要達(dá)到該目標(biāo)所需要實(shí)現(xiàn)的具體指標(biāo)。在進(jìn)行這樣的分析時(shí)，往往由于計(jì)算方法較為復(fù)雜或許多因素交織在一起而很難進(jìn)行，這時(shí)可以利用Excel提供的單變量求解命令來完成計(jì)算。以下通過具體示例說明單變量求解命令的應(yīng)用方法。方案分析單變量求解只能解決具有1個(gè)未知變量的問題，模擬運(yùn)算表最多只能解決2個(gè)變量變化引起的問題。如果要解決包括更多可變因素的問題，或是要在多種假設(shè)分析中找出最佳執(zhí)行方案，上述2種工具就無法實(shí)現(xiàn)了，這時(shí)可以使用EXcel的方案分析工具來完成。Excel的方案分析主要用于多方案求解問題，利用方案管理器，模擬不同方案的大致結(jié)果，根據(jù)多個(gè)方案的對(duì)比分析，考查不同方案的優(yōu)劣，從中尋求最佳的解決方案。命名單元格在應(yīng)用方案分析工具之前，首先應(yīng)做些準(zhǔn)備工作。為了使創(chuàng)建的方案能夠明確地顯示有關(guān)變量，以及將來在進(jìn)行方案總結(jié)時(shí)便于閱讀方案摘要報(bào)告，需要先給有關(guān)變量所在的單元格命名。創(chuàng)建方案創(chuàng)建方案是方案分析的關(guān)鍵，應(yīng)根據(jù)實(shí)際問題的需要和可行性來創(chuàng)建各個(gè)方案。創(chuàng)建方案報(bào)告應(yīng)用“方案管理器”對(duì)話框中的“顯示”按鈕只能1一個(gè)方案、1一個(gè)方案地查看某個(gè)方案對(duì)利潤總額的影響，如果能將所有方案匯總到1一個(gè)工作表中，形成1一個(gè)方案報(bào)講授+演示【舉例】某公司計(jì)劃貸款1000萬元，年限為10年，目前的年利率為3%,需要計(jì)算出分月償還時(shí)每個(gè)月的償還額。講授+演示講授+演示告，然后再對(duì)不同方案的影響進(jìn)行比較分析，將更有助于決策人員綜合考查各種方案的效果。11.4規(guī)劃求解以上介紹的一些方法可以解決信息管理工作中的許多問題，但也存在一定的局限性。模擬運(yùn)算表只能分析1~2個(gè)決策變量對(duì)最終目標(biāo)的影響，方案分析在可行方案過多時(shí)操作會(huì)比較煩瑣，單變量求解只能通過一個(gè)變量的變化求得一個(gè)目標(biāo)值。但在實(shí)際工作中往往會(huì)有眾多因素、無數(shù)方案需要計(jì)算、比較和分析，并從中選擇出最優(yōu)方案。Excel的規(guī)劃求解工具可以避開難懂的細(xì)節(jié)，方便快捷地計(jì)算出各種規(guī)劃問題的最佳解。規(guī)劃問題種類繁多，歸結(jié)起來可以分成2類：一類是確定了某個(gè)任務(wù)，研究如何使用最少的人力、物力和財(cái)力去完成它；另一類是已經(jīng)有了一定數(shù)量的人力、物力和財(cái)力，研究如何使用它們以獲得最大的收益。從數(shù)學(xué)角度來看，規(guī)劃問題都有下述3個(gè)共同特征。（1）決策變量：每個(gè)規(guī)劃問題都有一組需要求解的未知數(shù)（x1,x2,…,xn），稱為“決策變量”。這組決策變量的1一組確定值就代表1一個(gè)具體的規(guī)劃方案。（2）約束條件:對(duì)于規(guī)劃問題的決策變量通常都有一定的限制條件，稱為“約束條件”。約束條件通常用包含決策變量的不等式或等式來表示。（3）目標(biāo)函數(shù)：每個(gè)問題都有一個(gè)明確的目標(biāo)，如利潤最大或成本最小。目標(biāo)通常是用與決策變量有關(guān)的表達(dá)式表示，稱為“目標(biāo)函數(shù)”。如果約束條件和目標(biāo)函數(shù)都是線性函數(shù)，則稱為線性規(guī)劃；否則稱為非線性規(guī)劃。如果要求決策變量的值為整數(shù)，則稱為整數(shù)規(guī)劃。講授+演示安裝規(guī)劃求解工具規(guī)劃求解是Excel的1一個(gè)加載項(xiàng)，一般安裝時(shí)默認(rèn)不加載規(guī)劃求解工具。如果需要使用規(guī)劃求解工具，必須手工先進(jìn)行加載。建立規(guī)劃模型在進(jìn)行規(guī)劃求解時(shí)首先要將實(shí)際問題數(shù)學(xué)化、模型化，即將實(shí)際問題用一組決策變量、一組用不等式或等式表示的約束條件以及目標(biāo)函數(shù)來表示，這是求解規(guī)劃問題的關(guān)鍵。然后才可以應(yīng)用Excel的規(guī)劃求解工具求解。輸入規(guī)劃模型建立好規(guī)劃模型后，下一步工作是將規(guī)劃模型的有關(guān)數(shù)據(jù)和公式輸入到工作表中。求解規(guī)劃模型分析求解結(jié)果國」本章小結(jié) E本章主要介紹了Excel在數(shù)據(jù)分析方面的應(yīng)用，通過不同的應(yīng)用實(shí)例分別說明了EXcel的模擬運(yùn)算表、單變量求解、方案分析、規(guī)劃求解等數(shù)據(jù)分析工具的功能、特點(diǎn)、適應(yīng)范圍和使用方法。Excel的數(shù)據(jù)分析工具功能強(qiáng)大、使用方便，掌握和利用好數(shù)據(jù)分析工具，能夠?yàn)楣芾頉Q策提供更為有效的信息。邂課后練習(xí)一、選擇題(見教材)二、填空題(見教材)三、問答題?Excel提供了哪些數(shù)據(jù)分析工具？.模擬運(yùn)算表有哪幾種類型？.單變量求解方法用于解決哪方面的問題？.與模擬運(yùn)算表相比，方案分析的優(yōu)勢(shì)體現(xiàn)在哪里？.在使用Excel進(jìn)行規(guī)劃求解時(shí)，若發(fā)現(xiàn)Excel功能區(qū)中沒有“規(guī)劃求解”命令，應(yīng)怎樣安裝它？實(shí)訓(xùn)任務(wù).某開發(fā)商想貸款100萬元建一個(gè)山林果園，貸款利率5%,期限為25年，月償還額是多少？如果有多種不同的貸款利率(3%、4%、5%、6%、7%)和不同貸款年限(10年、15年、20年、30年)可供選擇，各種情況下的月償還額各是多少？.對(duì)于第1題中的100萬元貸款，若想每月還貸2萬元，在貸款利率為6%的情況下，需要多少年才能還清？.根據(jù)第2題中的有關(guān)數(shù)據(jù)，分別建立貸款利率4%、貸款年限30年，利率5%、貸款年限20年和利率6%、貸款年限10年3種方案，并建立相應(yīng)的方案報(bào)告。.某公司需要采購一批贈(zèng)品用于促銷活動(dòng)，采購的目標(biāo)品種有A、B、C