EXCEL高級(jí)教案1

1、第一講 制作電子表格的操作技巧一、選取的特殊技巧：1、 選定不相鄰的文本矩形區(qū)域：按著ctrl”鍵，然后單擊所選定的單元格（可拖拉）2、 選定相鄰的多張工作表：?jiǎn)螕粢x擇的第一工作表標(biāo)簽，然后按著shift鍵，再單擊最后一張工作表，（不拖拉）3、 選定不相鄰的多張工作表：先單擊想要選定的第一張工作表的標(biāo)簽，按住ctrl鍵分別單擊我們所要選定的工作表標(biāo)簽二、工作表的插入、刪除、移動(dòng)、復(fù)制及其它：1. 插入工作表：選擇插入菜單中的工作表；2. 刪除工作表：選擇編輯菜單中的刪除工作表3. 移動(dòng)（在工作簿中）鼠標(biāo)法: 單擊標(biāo)簽，然后拖拉到相應(yīng)位置.4. 復(fù)制（在工作簿中）鼠標(biāo)法：按下 ctrl+拖拉到

2、相應(yīng)位置移動(dòng)和復(fù)制到另外一本工作簿菜單法“:編輯”菜單的“移動(dòng)和復(fù)制”（如下圖）5. 重新命名工作表：雙擊選中的工作表的標(biāo)簽或“格式”菜單下的“工作表”中的“重命名”.6. 分割工作表：利用橫向和豎向滾動(dòng)條上的分割符號(hào)進(jìn)行拖動(dòng)；或窗口（菜單） 分割窗口7. 取消分隔：雙擊分隔符注：分割后的工作表還是一張工作表，對(duì)任一窗格內(nèi)容的修改都會(huì)反映到另一窗格8. 隱藏表格線：工具（菜單） 選項(xiàng) “視窗中的“網(wǎng)格線”（批注）9. 在工作表中增加注釋：利用插入菜單中的批注10. 工具欄的顯示/隱藏：“視圖”菜單下的“工具欄”中的“各選項(xiàng)”三、輸入特殊數(shù)據(jù)的技巧：1. 輸入文字：(1) 默認(rèn)的單元格寬度是8個(gè)

3、字符寬,如果超過(guò)8個(gè)字符時(shí),我們可以通過(guò)格式中的列中的列寬改變寬度，或者使用鼠標(biāo)移動(dòng)，但一個(gè)單元格最多可以輸入122漢字(2) 改變單元格內(nèi)的漢字排列格式：格式中的單元格中的對(duì)齊,標(biāo)題應(yīng)采用"合并單元格"的方法。(3) 對(duì)于以”0”開(kāi)頭的數(shù)字串, 實(shí)際屬于字符串, 應(yīng)按 0592方法輸入（即先輸入） 或者對(duì)選定的單元格設(shè)定為文字格式(通過(guò)”格式”菜單中的”單元格”中的“數(shù)字”下的“文本”2. 輸入數(shù)字：我們可以通過(guò)格式中的單元格中的數(shù)字來(lái)設(shè)置數(shù)字輸入的各種格式，其中注意小數(shù)（7.89）科學(xué)記數(shù)（1.23E+0.8）12356789分?jǐn)?shù)（0/）3. 輸入日期和時(shí)間：（1） 我

4、們?cè)谝?2小時(shí)計(jì)時(shí)時(shí)，數(shù)字與字母之間必須有一空格，分別代表上午，下午，例：5:00（2） 在既輸入日期,又輸入時(shí)間,日期與時(shí)間之間也必須有空格,鍵入日期時(shí),我們可以使用斜杠()或連字符(-)，但我們可以選定不同的輸出格式二、相同數(shù)據(jù)的輸入：1、 同時(shí)對(duì)多個(gè)單元格輸入相同的數(shù)據(jù) 選定要輸入相同數(shù)據(jù)的單元格區(qū)域 輸入數(shù)據(jù)（此數(shù)據(jù)會(huì)出現(xiàn)在選定單元格的左上角的第一單元格） 同時(shí)按下Ctrl+回車2、 同時(shí)選定工作表組 選定工作表組 在工作表組中的一張工作表內(nèi)輸入數(shù)據(jù)，那么該數(shù)據(jù)也會(huì)反映到這組工作表中三. 輸入的自動(dòng)化：對(duì)一些有規(guī)則的數(shù)據(jù)（等差，等比，星期KK星期日等）利用編輯菜單中的填充中的序列其基本

5、步驟：（） 在序列中的第一個(gè)單元格中輸入初值，例如：1（） 用拖拉選定區(qū)域（） 選擇編輯中的填充”中的 ”序列”中的自動(dòng)填充、等差、等比（步長(zhǎng)值）如圖3-1圖3-1(5)、自定義序列：工具（菜單） 選項(xiàng) 自定義序列四、編輯工作表1、編輯（修改插入，刪除）單元格內(nèi)容，先選定所要編輯的單元格，然后雙擊2、編輯欄中的數(shù)據(jù)：先選定所要編輯的單元格，然后單擊上面的編輯欄3、變單元格中的字體、大小、顏色和對(duì)齊方式（注意分散對(duì)齊）執(zhí)行格式中的單元格中的各選項(xiàng)Þ此項(xiàng)內(nèi)容非常豐富如下圖：第二講 公式的輸入及函數(shù)應(yīng)用一、在公式中使用運(yùn)算符號(hào)：（加法）（減法）*（乘法）（除法） （乘方）&(文字運(yùn)

6、算符號(hào))，例本月銷售本月銷售、 （不等于）、（小于等于）Þ其運(yùn)算的優(yōu)先級(jí)跟數(shù)學(xué)上差不多，若要改變，請(qǐng)加括符Þ其中要注意輸入負(fù)數(shù)時(shí)，不能加括符，例：5*1050三、公式位置的引用1. 單元格地址的輸入：例：1+B2+C32. 位置引用符號(hào)及說(shuō)明符號(hào)說(shuō)明區(qū)域(：冒號(hào)）引用位于兩個(gè)引用位置之間的所有單元格，并包括這兩個(gè)引用位置a1:b3合集（，；）引用兩個(gè)指定的引用位置,例:3,6.；逗號(hào)和分號(hào)交集（空格）引用兩個(gè)引用位置的公共單元,例:a2:c2ob1:b3 實(shí)際為b23. 相對(duì)地址引用：也就是當(dāng)我們把一單元格里的公式拷貝到另外的單元格時(shí)，其格式會(huì)發(fā)生相對(duì)變化，也就是說(shuō)公式的各

7、個(gè)單元格地址會(huì)根據(jù)(2),相對(duì)于(1)的相對(duì)位置變化.公式單元格拷貝后單元格地址(1)Þb2=a1+a2+C6Þ(1)2(2)ÞC2=b1+b2+d6Þ(2)2E2=d1+d2+F64. 絕對(duì)地址引用：就是在我們要把公式拷貝或填入到新位置，并且使那些固定單元格地址保持不變時(shí)使用，通常我們是在行號(hào)和列號(hào)前面添加美無(wú)$5. 混合地址引用：混合地址是指只有行或者列變?yōu)榻^對(duì)地址6. 三維地址引用：是指在一本工作簿中不同的工作表引用單元格，三維引用的一般格式為：工作名！單元格地址，例：sheet1！1+2(注意:其中的a2是指當(dāng)前工作區(qū)的a2)7. 數(shù)組：就是單元

8、的集合或是一組處理的值集合數(shù)組的輸入：（1）輸入公式 如：=B2：B4*C2：C4結(jié)果放于三格。=SUM（B2：B4*C2：C4） 結(jié)果放于一格=B2:B4*120；140；160結(jié)果放于三格，逗號(hào)表示水平，分號(hào)表垂直。(2) Ctrl+ shift+ Enter數(shù)組的擴(kuò)充：在使用數(shù)組時(shí)，其它運(yùn)算對(duì)象應(yīng)該和第一個(gè)數(shù)組具有相同的維數(shù)。必要是Excel會(huì)將運(yùn)算對(duì)象擴(kuò)展，以符合操作需要的維數(shù)。如：=SUM（1，2，3+4）會(huì)自動(dòng)將數(shù)值擴(kuò)充成=SUM（1，2，3+4，4，4）五、控制重算的方式:主要是通過(guò)工具菜單中的選項(xiàng)中的重新計(jì)算里面的各種設(shè)置來(lái)改變它，在公式中之所以其結(jié)果能自動(dòng)改變，是因?yàn)樯鲜鰧?duì)話

9、框中設(shè)為“自動(dòng)重算”，若設(shè)為“人工重算”，其單元格的值被改變時(shí)，公式的值不會(huì)自動(dòng)改變，還得設(shè)置“重算所有文檔”六使用自動(dòng)求和按鈕：å其中分四種情況：1. 把多個(gè)單元格的數(shù)值之和放在一個(gè)單元格中：其步驟：(1) 先選定放置求和結(jié)果的單元格 (2) 按下自動(dòng)求和按鈕 (3) 用鼠標(biāo)改變虛框的范圍(4) 按下確認(rèn)或回車鍵2. 在Excel中,我們還能夠利用自動(dòng)求和按鈕一次輸入多個(gè)求和公式例如對(duì)圖5-1表中的部門和產(chǎn)品分別求總計(jì),我們只需先選定總計(jì)欄中的B5:D5”單元格區(qū)域,然后按下求和按扭，或選E2：E5”單元格區(qū)域,按下確認(rèn)按鈕即可è對(duì)列或行求和3. 此外，我們還可以利用選定

10、操作對(duì)不相鄰的單元格自動(dòng)求和4. 對(duì)行、列一起求和:全部選定第二講之二：函數(shù)一、基本概念：（函數(shù)）參數(shù)：我們傳給函數(shù)用來(lái)執(zhí)行運(yùn)算的數(shù)值各個(gè)參數(shù)需要用括號(hào)（）括起來(lái)結(jié)果：函數(shù)返回的數(shù)值稱為結(jié)果，è工作表函數(shù)能自動(dòng)實(shí)現(xiàn)決策，執(zhí)行和數(shù)值返回語(yǔ)法：在公式中使用的字符次序稱為語(yǔ)法，所有函數(shù)都有相同的基本語(yǔ)法如果一個(gè)公式以函數(shù)開(kāi)頭，也要象輸入其它公式一樣，輸入一個(gè)等號(hào)二、有關(guān)函數(shù)的約定：1. 當(dāng)我們手工輸入函數(shù)時(shí)，需要一個(gè)等號(hào),例：A1-A3（Sum(B2:C3)*100)+100和sqrt(B1);2. 當(dāng)我們直接使用函數(shù)指南f(x)或插入函數(shù)中的函數(shù)進(jìn)行運(yùn)算，不需要人為輸入等號(hào)，系統(tǒng)在編輯欄

11、自動(dòng)產(chǎn)生一個(gè)等號(hào)在使用參數(shù)時(shí)，我們一定要注意必選項(xiàng)（粗體）和任選項(xiàng)（非粗體）對(duì)于參數(shù)，我們可以用區(qū)域代表一個(gè)參數(shù)（如：Sum(A1：A5)因?yàn)楹瘮?shù)中僅能帶30個(gè)參數(shù)三、其它規(guī)定：1. 參數(shù)名和參數(shù)類型：參數(shù)名：我們可以根據(jù)參數(shù)的名稱來(lái)判斷參數(shù)的類型，以免輸入的數(shù)據(jù)類型錯(cuò)誤參數(shù)類型：數(shù)，文字（需要用雙引號(hào)），邏輯值（True, False）.錯(cuò)誤值(#NAME? #Null!)；引用（$A$10）數(shù)組Sum(B2:D2*B3:D3).2. 參數(shù)表中使用逗號(hào)：（高級(jí)班才講）用戶必須用逗號(hào)分隔單個(gè)參數(shù)，但應(yīng)注意不要額外鍵入逗號(hào)，因?yàn)橐粋€(gè)逗號(hào)將代表一個(gè)參數(shù)，例：Aver(1,2,3,4,5)返回值3,

12、 而Aver(, , l,2,3,4,5)卻返回2.14.注意: 如果將引用作為一個(gè)參數(shù), 而且這一引用使用逗號(hào)做合并運(yùn)算,則用括弧將引用括起來(lái).例：Areas(A1,C1).注意：在公式中輸入函數(shù)：例：A1-A3Sum(B2:C3)*100)+1003. 錯(cuò)誤值的意義：錯(cuò)誤值#DIN/01公式被零除#N/A沒(méi)有可用的數(shù)值通常情況是，您將數(shù)值直接輸入某些工作表單元格雖然這些單元格中會(huì)包含數(shù)據(jù)，但目前尚無(wú)數(shù)據(jù)引用這些單元格的公式將返回#N/A,而不會(huì)計(jì)算數(shù)值#NANE?Microsoft Excel不識(shí)別公式中使用的名字.#NULL!指定的兩個(gè)區(qū)域不相交.#NUM!數(shù)字有問(wèn)題.#REF!公式引用

13、了無(wú)效的單元格#VALUE!參數(shù)或操作數(shù)的類型有錯(cuò)具體函數(shù)實(shí)例：1， SUM（） 求和2， AVERAGE（） 求平均值3， COUNT（） 統(tǒng)計(jì)參數(shù)的個(gè)數(shù)4， FACT（） 求階乘 例：FACT（5）=1205， POWER（） 冪函數(shù) 210=POWER（2，10）6， LOG（） 求對(duì)數(shù) LOG21024=LOG（1024，2）7， MAX（）8， MIN（）9， STDEV（） 估算樣本的標(biāo)準(zhǔn)偏差財(cái)務(wù)函數(shù)1， RATE（）求利率語(yǔ)法：RATE（NPER，PMT，PV，F(xiàn)V，TYPE）其參數(shù)的含義：NPER：期數(shù) PMT：付款數(shù) PV：現(xiàn)值 FV：未來(lái)值 TYPE：類型例：金額為8000

14、的4年期貸款，月支付款為200，則該筆貸款的月利率為多少？ =RATE（4*12，-200，8000，0，0）2， PMT（RATE，NPER，PV，F(xiàn)V，TYPE）例：若需要10個(gè)月付清年利率為8%的10000貸款的月付款為多少？ =PMT（8%/12，10，10000，0，0）又例：對(duì)于同一筆貸款，如果支付期在每期初，則： =PMT（8%/12，10，10000，0，1）（注：TYPE=0 則為期未 TYPE=1 則為期初）又例：如果需要以按月定期存款方式在20年內(nèi)存款50000，假設(shè)年利率為6%，則每月應(yīng)向銀行支付多少？ =PMT（6%/12，20*12，0，50000，1）練習(xí)：若每月

15、向銀行定期存款800，年利率為7%，那么30年后銀行存款應(yīng)為多少？（用FV（）邏輯函數(shù)：AND（）判斷參數(shù)的邏輯值，參數(shù)只要一個(gè)為假，則輸出為假；參數(shù)全為真，則輸出為真。OR（） 只要一個(gè)為真，則輸出為真，全假則輸出為假。IF（） 執(zhí)行真假值判斷，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果。例：A1=55，A2=60，A3=30AND(A1>0,A2>0,A3>0):輸出的結(jié)果是”真” AND(A1>0,A2>0,A3<0): 輸出的結(jié)果是”假”O(jiān)R(A1>0,A2>0,A3>0) :輸出的結(jié)果是”真” OR(A1<0,A2>0,A3&

16、gt;0) :輸出的結(jié)果是”真”O(jiān)R(A1<0,A2<0,A3<0) :輸出的結(jié)果是”假”IF(A1>0,A1*2,A1/2): 輸出的結(jié)果是”110” IF(A2<0,A1*2,A1/5): 輸出的結(jié)果是”11”SUMIF(A1:B7,“>50”,A1:B7)其含義是:求A1:B7大于50的單元格的和函數(shù)嵌套：例：IF(AVERAGE(A1:A3)>50,MAX(A1:A3),SUM(A1:A3)含義是:如果A1,A2,A3三個(gè)數(shù)的均值>50,則求A1,A2,A3三個(gè)數(shù)的最在值,否則, 求A1,A2,A3三個(gè)數(shù)的和. 如下圖中函數(shù):IF(IF(

17、C4= "男",65-D4,60-D4)<0,"退職",IF(C4="男",65-D4,60-D4)=If(c3<1200,0,if(c3<1700,(c3-1200)*5%,if(c3<3000,(c3-1200)*10%-25,if(c3<8000,(c3-1200)*15%-125) 結(jié)果如下圖：備注：所有的函數(shù)都可利用函數(shù)指南來(lái)操作，但一定要注意數(shù)值類型與函數(shù)類型要配比.第三講：對(duì)數(shù)據(jù)的排序、篩選、分類匯總一， 記錄單的創(chuàng)建1， 輸入記錄單的字段名2， 選定并執(zhí)行數(shù)據(jù)/記錄單 確定3， 在各字段中

18、鍵入記錄單的值4， 按“回車”即加入一條記錄記錄的插入：執(zhí)行插入/單元格 整行 確定記錄的查找：1，執(zhí)行數(shù)據(jù)/記錄單 鍵入欲查找記錄的值 按“回車”1、 排序：執(zhí)行數(shù)據(jù)菜單中的”排序，其中可以按照主關(guān)鍵字，次關(guān)鍵字，第三關(guān)鍵字，依次排列(也是說(shuō),首先按照主關(guān)鍵字排序,當(dāng)主關(guān)鍵字中有相同字段時(shí),再根據(jù)次關(guān)鍵字排序,依次類排).（圖4-4）圖4-42、 篩選：執(zhí)行數(shù)據(jù)菜單中的“篩選”中的“自動(dòng)篩選或高級(jí)篩選”自動(dòng)篩選：是指在分類匯總的基礎(chǔ)上，選擇某一部份（類別）的數(shù)據(jù)步驟：（1）、選定要篩選的單元格。（2）、數(shù)據(jù)（菜單） 篩選 自動(dòng)篩選自定義篩選：（1）、在建立篩選的基礎(chǔ)上，利用向下的箭頭，選擇自

19、定義選項(xiàng)（2）、在自定義自動(dòng)篩選方式對(duì)話框中進(jìn)行條件設(shè)定（可同時(shí)設(shè)定兩個(gè)條件）高級(jí)篩選：一般用在于條件比較復(fù)雜的尋找。1） 設(shè)定條件區(qū)域在數(shù)據(jù)清單的前方插入幾個(gè)空行輸入要設(shè)定條件的字段名稱及條件若要對(duì)不同的列指定多重條件，請(qǐng)?jiān)跅l件區(qū)域的同一行輸入所有的條件（條件標(biāo)記必須和我們想評(píng)價(jià)的列標(biāo)記相同）若要相同的列指定不同的條件，請(qǐng)把條件輸入不同的行上2）數(shù)據(jù)（菜單） 篩選 高級(jí)篩選2） 根據(jù)對(duì)話框進(jìn)行設(shè)置三、分類匯總1、 對(duì)欲分類的字段進(jìn)行排序2、 執(zhí)行數(shù)據(jù)/分類匯總3、 選擇分類字段及匯總方式和匯總項(xiàng) 確定多級(jí)分類匯總：1、 進(jìn)行兩列以上排序2、 給主分類段分類匯總3、 給次分類段分類匯總并使“替

20、換當(dāng)前分類匯總”無(wú)效 確定四、模擬運(yùn)算表 單模擬運(yùn)算表：?jiǎn)巫兞磕M運(yùn)算表的結(jié)構(gòu)特點(diǎn)是，其輸入數(shù)值被排列在一列中（列引用）或一行中（行引用）。單變量模擬運(yùn)算表中使用的公式必須引用輸入單元格。 1. 在一列或一行中，鍵入要替換工作表上的輸入單元格的數(shù)值序列。2. 如果輸入數(shù)值被排成一列，請(qǐng)?jiān)诘谝粋€(gè)數(shù)值的上一行且處于數(shù)值列右側(cè)的單元格中，鍵入所需的公式。在同一行中，在第一個(gè)公式的右邊，分別鍵入其它公式。 如果輸入數(shù)值被排成一行，請(qǐng)?jiān)诘谝粋€(gè)數(shù)值左邊一列且處于數(shù)值行下方的單元格內(nèi)，鍵入所需的公式。在同一列中，在第一個(gè)公式的下方，分別鍵入其它公式。 3. 選定包含公式和需要被替換的數(shù)值的單元格區(qū)域。4.

21、在“數(shù)據(jù)”菜單中，單擊“模擬運(yùn)算表”命令。 5. 如果模擬運(yùn)算表是列方向的，請(qǐng)?jiān)凇拜斎胍昧械膯卧瘛本庉嬁蛑?，為輸入單元格鍵入引用。 如果模擬運(yùn)算表是行方向的，請(qǐng)?jiān)凇拜斎胍眯械膯卧瘛本庉嬁蛑?，為輸入單元格鍵入引用。 雙模擬運(yùn)算表：雙變量模擬運(yùn)算表中的兩組輸入數(shù)值使用同一個(gè)公式。這個(gè)公式必須引用兩個(gè)不同的輸入單元格。 1. 在工作表的某個(gè)單元格內(nèi)，輸入所需的引用兩個(gè)輸入單元格的公式。2. 在公式下面同一列中鍵入一組輸入數(shù)值，在公式右邊同一行中鍵入第二組輸入數(shù)值。3. 選定包含公式以及數(shù)值行和列的單元格區(qū)域。4. 在“數(shù)據(jù)”菜單中，單擊“模擬運(yùn)算表”命令。 5. 在“輸入引用行的單元格”編輯

22、框中，輸入要由行數(shù)值替換的輸入單元格的引用。6. 在“輸入引用列的單元格”編輯框中，輸入要由列數(shù)值替換的輸入單元格的引用。第四講：數(shù)據(jù)透視表、及圖表的創(chuàng)建 數(shù)據(jù)透視表是一種對(duì)大量數(shù)據(jù)快速匯總和建立交叉表的交互式格式表格，它是一種組織數(shù)據(jù)的軟設(shè)備。用戶可以在透視表中指定想顯示的字段和數(shù)據(jù)項(xiàng)，以確定如何組織數(shù)據(jù)。一、 透視表的創(chuàng)建1、 單擊工作表中的任意單元格2、 單擊數(shù)據(jù)/數(shù)據(jù)透視表達(dá)式 下一步 下一步3、 拖動(dòng)右邊的字段按鈕到圖中來(lái)做成透視表的行列數(shù)據(jù) 例如把日期作為頁(yè)字段，產(chǎn)品作為行字段，地區(qū)作為列字段，銷售額作為數(shù)據(jù)項(xiàng) 如下圖：最后結(jié)果是：二、 修改透視表1、 行列互換（1），在列字段名單

23、元格A4按下左鍵®拖曳到行字段中即可。（行同上）2、 添加和刪除字段單擊工具欄上的“數(shù)據(jù)透視表”/向?qū)?#174;直接將欲添加的字段拖曳到透視表中將欲刪除的字段拖曳出來(lái)。三、 刷新透視表（有以上兩種方法）1， 執(zhí)行數(shù)據(jù)/更新數(shù)據(jù)2， 單擊“數(shù)據(jù)透視表”工具欄上的“！”四、 對(duì)數(shù)據(jù)透視表排序 （1），右單擊字段名（如：地區(qū)）®字段®高級(jí)®選擇排序項(xiàng)®確定®確定 如下圖：五、 改變透視表的匯總方式 單擊工具欄上的“數(shù)據(jù)透視表”/字段 (如下圖)六、顯示和隱藏明細(xì)數(shù)據(jù) 數(shù)據(jù)透視表能夠?qū)?shù)據(jù)進(jìn)行分類匯總，也可顯示或隱藏明細(xì)數(shù)據(jù)。 （1）、右單

24、擊透視表中的列字段名（如“地區(qū)”）（2）、執(zhí)行“組及分級(jí)顯示”®顯示明細(xì)數(shù)據(jù)（3）、在對(duì)話框中選擇明細(xì)數(shù)據(jù)所在的字段，（如“產(chǎn)品”）®確定 如下圖）六、 創(chuàng)建計(jì)算字段 （1），右單擊透視表®公式®計(jì)算字段®彈出對(duì)話框 （2），在“名稱”欄中輸入計(jì)算字段的名稱，在“公式”欄中輸入計(jì)算字段的公式，如下圖：（3）、單擊“添加”®確定最后的結(jié)果是：圖表的創(chuàng)建：圖表是將數(shù)據(jù)用圖形的形式表示出來(lái)一、 使用向?qū)?chuàng)建圖表將光標(biāo)定在表格中®單擊工具欄上的“圖表向?qū)А卑粹o®選擇圖表的類型（如下圖）下一步®按向?qū)瓿捎嘞碌牟僮?/p>

25、圖表類型的更改：右單擊圖表®圖表類型®重新選擇圖表的類型其它項(xiàng)的更改：最快的方法是：需改什么就雙什么 如改文字則雙擊文字使用趨勢(shì)線（1）、激活圖表®執(zhí)行“圖表”菜單中“添加趨勢(shì)線”®選擇類型®選擇數(shù)據(jù)系列®確定使用誤差線：1、 單擊圖表中要添加誤差線的數(shù)據(jù)系列 如“漁類”®執(zhí)行格式/數(shù)據(jù)系列®選擇“誤差線Y”2、 選擇“正負(fù)偏差”®在“誤差量”欄中選擇百分比®確定地圖數(shù)據(jù)將在電腦上演示第五講：數(shù)據(jù)高級(jí)分析與決策一、 規(guī)劃求解 簡(jiǎn)介：規(guī)劃求解是數(shù)學(xué)中的優(yōu)化問(wèn)題，它通過(guò)改變多個(gè)輸入單元格求出最優(yōu)解，

26、同時(shí)保證工作表中的其他公式保持在設(shè)置的極限之內(nèi)。 下一實(shí)例說(shuō)明規(guī)劃求解的應(yīng)用的操作步驟。某個(gè)廠家，在一段時(shí)間內(nèi)生產(chǎn)兩種產(chǎn)品，分別是產(chǎn)品A和產(chǎn)品B。受原材料的限制，產(chǎn)品A的產(chǎn)量不得超過(guò)4000，產(chǎn)品B的產(chǎn)量不超過(guò)4000；受機(jī)器和人員以及時(shí)間的限制，兩種產(chǎn)量之和不得超過(guò)7000。產(chǎn)品A的單件利潤(rùn)為200元，每多生產(chǎn)100件，由于成本的降低，單件利潤(rùn)增加2元。產(chǎn)品B的單件利潤(rùn)為190元，每多生產(chǎn)100件單件利潤(rùn)增加3元?，F(xiàn)在進(jìn)行規(guī)劃求解，求出產(chǎn)品A和產(chǎn)品B產(chǎn)量的最佳方案，使總利潤(rùn)最大。（1）、新建一張表，其中B4單元格的公式是“=B2+B3”，B6中的公式是=（200+B2/100*2）*B2+（

27、190+B3/100*3）*B3（2）、執(zhí)行工具/規(guī)劃求解®如圖（3）、選擇“最大值”，設(shè)置目標(biāo)單元格為$B$6,可變單元格為$B$2:$B$3(4)、單擊添加®彈出“添加約束”對(duì)話框 ®輸入約束值®添加（5）、重復(fù)第（4）步，把所有約束條件都添加到“規(guī)劃求解參數(shù)”對(duì)話框的“約束”列表框中，如下圖（6）、單擊“求解”®確定最后的結(jié)果是：產(chǎn)品A為3000 產(chǎn)品B為4000 總利潤(rùn)率為：2020000練習(xí)題：用一塊長(zhǎng)20米，寬1米的鉛皮靠墻圍成一個(gè)矩形，若要矩形的體積最大，則此矩形的長(zhǎng)與寬各應(yīng)是多少米最合適。方案管理器方案是對(duì)問(wèn)題不同的設(shè)想，以便觀

28、察對(duì)問(wèn)題的影響，結(jié)論出最佳的投資方案。實(shí)例：新建如下表：其中C4單元格中的公式是“=B4*（1+$B$16）”,將這個(gè)公式復(fù)制到D4和E4;C7中的公式是=B7*(1+$B$17),將其復(fù)制到D7和E7;C8中的公式是=B8*(1+$B$18),將其復(fù)制到D8和E8;C9中的公式是=B9*(1+$B$19),將其復(fù)制到D9和E9;總計(jì)一行的單元格中的公式都是三種產(chǎn)品成本的和;凈收入一行的單元格中的公式都是每一年的銷售額減去總計(jì)銷售成本;E18單元格中顯示幾年的總凈收入,它是各年凈收入的和;B16到B19單元格顯示的是銷售額和各種產(chǎn)品銷售成本的年增長(zhǎng)率.這個(gè)工作表根據(jù)銷售額和各種產(chǎn)品銷售成本的年

29、增長(zhǎng)率來(lái)估計(jì)未來(lái)幾年公司的銷售額情況和凈收入情況,并計(jì)算這幾年的總凈收入.對(duì)此可以建立最好情況估計(jì)和最壞情況估計(jì)兩個(gè)方案.創(chuàng)建步驟如下:(1),執(zhí)行工具/方案®添加®在方案名框中鍵入”最好情況估計(jì)”,(因?yàn)檫@里是用年增長(zhǎng)率來(lái)估計(jì)銷售情況) 在可變單元格選為$B$16:$B$19,如下一圖:®確定(2),依次鍵入變量的值 .如下二圖®添加®重復(fù)上步編輯”最壞情況估計(jì)”方案®確定®關(guān)閉顯示方案:執(zhí)行工具/方案®選擇要顯示的方案名®單擊”顯示”創(chuàng)建方案總結(jié)報(bào)告:執(zhí)行工具/方案®單擊”方案總結(jié)”

30、4;選擇類型®在”結(jié)果單元格”文本框中輸入方案有效結(jié)果的單元格引用(如:$E$18)®確定最后結(jié)果是有效數(shù)據(jù)的定義(例:學(xué)生成績(jī)分是介于0至100之間)方法:(1),選擇要定義有效數(shù)據(jù)的式區(qū)域(2),執(zhí)行數(shù)據(jù)/有效數(shù)據(jù)(3),在設(shè)置選卡的許可下拉列表中選擇小數(shù),在數(shù)據(jù)列表中選擇介于,在最小值文本框中輸入0,最大值為100 如下圖®確定標(biāo)識(shí)單元格中的錯(cuò)誤數(shù)值：(1),選中欲標(biāo)識(shí)的數(shù)據(jù)區(qū)域(2),執(zhí)行工具/審核/顯示審核工具欄(3)單擊此工具欄上的圈釋無(wú)效數(shù)據(jù)如下圖:宏的創(chuàng)建(1),打開(kāi)工具/宏/錄制新宏(2),在宏名中鍵入宏的名字(可以定義快捷鍵 如:CTRL+E)®確定(3),在工作表中,執(zhí)行一系列的操作(4),操作完成后單擊停止例:創(chuàng)建一個(gè)使表