計(jì)算機(jī)excel第六章數(shù)據(jù)管理

1、第六章 數(shù)據(jù)管理李花： 主要內(nèi)容：數(shù)據(jù)列表排序篩選數(shù)據(jù)的匯總合并計(jì)算分列 概述Microsoft Excel 提供了一系列數(shù)據(jù)管理功能，可以很方便地管理和分析數(shù)據(jù)列表中的數(shù)據(jù)。如可以對(duì)數(shù)據(jù)列表進(jìn)行排序、篩選、分類匯總等操作。1. 數(shù)據(jù)列表數(shù)據(jù)列表（數(shù)據(jù)清單）的基本概念由包含相關(guān)數(shù)據(jù)的一系列數(shù)據(jù)行組成，是Excel中的數(shù)據(jù)庫(kù)表格；第一行為字段名（或稱列標(biāo)題）；行為記錄，列為字段；數(shù)據(jù)列表數(shù)據(jù)列表一般滿足以下條件：每張Sheet中僅使用一個(gè)數(shù)據(jù)列表；使數(shù)據(jù)列表獨(dú)立：在數(shù)據(jù)列表與其它數(shù)據(jù)之間至少隔開一個(gè)空行或空列；避免在表內(nèi)有空行或空列2. 數(shù)據(jù)的排序 普通排序原則：“升序”-從小到大數(shù)字：從負(fù)到正

2、文本：0-9,空格，! 等符號(hào)，字母邏輯值：FALSE TRUE錯(cuò)誤值：優(yōu)先級(jí)相同空白始終排在最后排序注意有無“標(biāo)題行”排序結(jié)果不同。排序過程中選中了一個(gè)區(qū)域，則區(qū)域之外的數(shù)據(jù)不排序；隱藏的數(shù)據(jù)也不參加排序。排序類型按單列排序 鼠標(biāo)單擊選擇要排序的列或列中的某一單元格，單擊“數(shù)據(jù)”|“排序和篩選”，選擇“升序”或“降序”;多列排序“數(shù)據(jù)”|“排序和篩選” ，選擇“排序”；關(guān)鍵字段可以是多個(gè)，主要關(guān)鍵字段不能省略，Excel 2007中排序字段最多有64個(gè);排序類型特殊排序（1）行排序: “排序選項(xiàng)”對(duì)話框中選“按行排序”；（2）自定義序列排序：“排序”對(duì)話框中單擊“次序”下拉列表，選“自定義序

3、列”； (3) 按單元格顏色、字體顏色、單元格圖標(biāo)排序 “排序”對(duì)話框中單擊“排序依據(jù)”下拉列表，選擇“單元格顏色”等實(shí)例：排序?qū)Ω?jìng)賽結(jié)果清單，完成如下排序。先按系別升序排序，當(dāng)系別相等時(shí)再按學(xué)號(hào)升序排序，注意保留原始順序，以便恢復(fù)原順序；改變列的順序，要求順序?yàn)閷W(xué)號(hào)、姓名、系別、筆試、上機(jī)測(cè)試、總分、名次等；按系別排序，要求是電子系、信息系、數(shù)學(xué)系、生物系、材料系；按照總分求出名次；操作步驟：排序時(shí)，主關(guān)鍵字為“系別”，次關(guān)鍵字為“學(xué)號(hào)”；選擇清單最后一條記錄的下一行，輸入排序之后的列序號(hào)3、1、2、4、5、6、7、8，在“排序”對(duì)話框的“選項(xiàng)”中選擇排序方向?yàn)椤鞍葱信判颉?，選擇列序號(hào)所在行

4、“行11”為主要關(guān)鍵字；操作步驟： 單擊Excel按鈕，創(chuàng)建自定義序列，序列項(xiàng)為電子系、信息系、數(shù)學(xué)系、生物系、材料系，主要關(guān)鍵字選擇為“系別”，在“排序”對(duì)話框的“次序”中 選“自定義序列”，并選擇剛創(chuàng)建的序列； 選擇“名次”字段的第一個(gè)單元格G3，輸入公式=RANK(F3,$F$3:$F$10)，向下拖動(dòng)填充柄，則可以得出總成績(jī)名次；3. 數(shù)據(jù)篩選數(shù)據(jù)篩選的概念數(shù)據(jù)篩選指按指定條件對(duì)數(shù)據(jù)列表中的記錄進(jìn)行選取，只顯示滿足條件的記錄，而隱藏其它記錄。篩選方式有兩種：自動(dòng)篩選、高級(jí)篩選自動(dòng)篩選操作步驟：鼠標(biāo)單擊數(shù)據(jù)列表中的任何一個(gè)單元格，單擊“數(shù)據(jù)”|“排序和篩選”，選擇 “篩選”，單擊與篩選條

5、件有關(guān)的字段右側(cè)的下拉箭頭。其中可選：按顏色篩選；根據(jù)所選字段的數(shù)據(jù)類型，可以按“文本”、“數(shù)字”、“日期”篩選；數(shù)值：只顯示與指定值匹配的記錄取消對(duì)某字段的篩選在自動(dòng)篩選箭頭下選 “全選”；恢復(fù)顯示所有記錄“數(shù)據(jù)”|“排序和篩選”，選擇“清除”注：篩選條件是累加的，每一個(gè)追加的篩選條件都基于當(dāng)前的篩選結(jié)果，同時(shí)對(duì)每一列只應(yīng)用一個(gè)篩選條件； 比較復(fù)雜的篩選條件，有時(shí)利用自動(dòng)篩選難以完成；實(shí)例：自動(dòng)篩選對(duì)學(xué)生成績(jī)表完成如下操作:查找未輸入數(shù)學(xué)成績(jī)的學(xué)生記錄 ； 篩選外語系男生的記錄； 篩選數(shù)學(xué)成績(jī)最好的五個(gè)人的記錄； 篩選數(shù)學(xué)成績(jī)低于數(shù)學(xué)平均分的記錄； 系別為外語系或性別為男生的記錄； 任何一門

6、課不及格的學(xué)生的記錄； 高級(jí)篩選高級(jí)篩選：使用復(fù)雜條件來尋找滿足條件的記錄操作步驟：創(chuàng)建條件區(qū)域，條件區(qū)域與數(shù)據(jù)列表之間至少應(yīng)隔一個(gè)空行或空列；“數(shù)據(jù)”|“排序和篩選”|“高級(jí)”；高級(jí)篩選的結(jié)果可顯示在原來的數(shù)據(jù)區(qū)域，也可以復(fù)制到其他的區(qū)域，復(fù)制到其他區(qū)域時(shí)指定目標(biāo)區(qū)域的左上角即可；條件區(qū)域“高級(jí)篩選”的條件區(qū)域的建立方法與庫(kù)函數(shù)的條件區(qū)域建立方法相同。條件區(qū)域至少兩行，第一行必須是字段名，下面的各行是相應(yīng)的條件。條件區(qū)域的列與列之間：and 與條件區(qū)域的行與行之間：or 或?qū)嵗焊呒?jí)篩選對(duì)學(xué)生成績(jī)表用高級(jí)篩選完成如下操作：篩選出系別為“外語”或性別為“男”的記錄; 篩選出任何一門課不及格的記

7、錄；4. 數(shù)據(jù)的匯總分類匯總建立分級(jí)顯示(1) 分類匯總目的：在數(shù)據(jù)列表中快速匯總同類數(shù)據(jù)，可分類進(jìn)行求和、計(jì)數(shù)、求均值、求最值等計(jì)算。前提：先要將數(shù)據(jù)按分類字段進(jìn)行排序，再進(jìn)行分類匯總。操作步驟：將數(shù)據(jù)按分類字段進(jìn)行排序；單擊數(shù)據(jù)列表中的任一單元格“數(shù)據(jù)”選項(xiàng)卡|“分級(jí)顯示”組|“分類匯總” 在“分類匯總”對(duì)話框中選擇： 分類字段：選排序所依據(jù)的字段； 匯總方式：選用于分類匯總的函數(shù)方式； 選定匯總項(xiàng)：選要進(jìn)行匯總計(jì)算的字段； 取消分類匯總結(jié)果：在“分類匯總”對(duì)話框中選擇“全部刪除”命令按扭。分類匯總的層次顯示匯總表的左上角有按鈕，分別標(biāo)明“1”，“2”，“3”等，說明層次。匯總表中的“+”

8、按鈕，每個(gè)按鈕對(duì)應(yīng)一個(gè)匯總行，單擊“+”按鈕，顯示出匯總行的明細(xì)數(shù)據(jù)。多極分類匯總在原有分類匯總的基礎(chǔ)上，可以進(jìn)一步做匯總。操作注意：在多級(jí)分類匯總時(shí)在“分類匯總”對(duì)話框中取消選“替換當(dāng)前分類匯總”。實(shí)例：分類匯總對(duì)學(xué)生成績(jī)表，完成如下操作：求每個(gè)系的各科平均成績(jī)；求每個(gè)系的學(xué)生人數(shù)；對(duì)上一步驟得出的匯總結(jié)果，復(fù)制粘貼到其他位置；求每個(gè)系、不同性別的人數(shù)；第1、2題操作步驟提示：先按系別進(jìn)性排序，在“分類匯總”對(duì)話框中分類字段選為“系別”，匯總方式選“平均值”，匯總項(xiàng)選“數(shù)學(xué)”、“英語”、“計(jì)算機(jī)”；嵌套分類匯總，在原有分類匯總的基礎(chǔ)上，再次做分類匯總，分類字段為“系別”、匯總方式選“計(jì)數(shù)”、

9、匯總項(xiàng)選任一字段，一般選分類字段右側(cè)的字段，取消選“替換當(dāng)前分類匯總”；第4題操作步驟提示：以系別為主關(guān)鍵字，性別為次關(guān)鍵字進(jìn)行排序；首先對(duì)“系別”進(jìn)行一級(jí)分類匯總,匯總方式為“求計(jì)數(shù)”，選擇任何一個(gè)沒有空白值的字段（最好選排序字段右側(cè)的字段）；接著再按“性別”進(jìn)行分類匯總，匯總方式和匯總字段同上，在對(duì)話框中一定要選清除“替換現(xiàn)有的分類匯總”的默認(rèn)選項(xiàng)；思考題：如何使用分類匯總的結(jié)果？分類匯總的結(jié)果不能直接用復(fù)制粘貼的方式使用，需要用Ctrl鍵選擇所需要的不連續(xù)的內(nèi)容再?gòu)?fù)制粘貼或用“定位條件”中選擇“可見單元格”，進(jìn)行復(fù)制粘貼。(2) 組及分級(jí)顯示自動(dòng)建立分級(jí)和人工建立分級(jí)操作“數(shù)據(jù)”| “分

10、級(jí)顯示”|“組合”,“自動(dòng)建立分級(jí)顯示”分類匯總和人工分級(jí)的區(qū)別:分類匯總的分級(jí)顯示必須按照某一列的相同的數(shù)據(jù)進(jìn)行匯總，而手動(dòng)建立分級(jí)沒有這個(gè)限制。5. 合并計(jì)算當(dāng)源數(shù)據(jù)具有相同排列順序和位置時(shí)，可以按位置進(jìn)行合并計(jì)算；當(dāng)源數(shù)據(jù)沒有按相同的順序排列，但使用相同標(biāo)志時(shí)，可以按分類合并計(jì)算。操作步驟：“數(shù)據(jù)”|“數(shù)據(jù)工具”|“合并計(jì)算”合并計(jì)算注意：選擇匯總函數(shù)，指定源區(qū)域，明確目標(biāo)區(qū)域是否與源區(qū)域建立鏈接關(guān)系，以及數(shù)據(jù)源區(qū)域中標(biāo)題的位置。當(dāng)合并計(jì)算數(shù)據(jù)時(shí)，需要在三維公式中或“合并計(jì)算”對(duì)話框的“引用位置”下拉列表框中指定數(shù)據(jù)的源區(qū)域。源區(qū)域和目標(biāo)區(qū)域可在同一個(gè)工作簿的同一個(gè)工作表或不同的工作表，

11、也可在不同的工作簿中。合并計(jì)算注意：創(chuàng)建目標(biāo)合并區(qū)域時(shí)，最好單擊目標(biāo)區(qū)域的左上角單元格，避免由于目標(biāo)區(qū)域的形狀產(chǎn)生問題。合并計(jì)算中“鏈接”與“不鏈接”的區(qū)別在于數(shù)據(jù)源的改變能否自動(dòng)反映到結(jié)果區(qū)域以及能否顯示明細(xì)數(shù)據(jù)。實(shí)例1：按位置進(jìn)行合并要求：每個(gè)源區(qū)域中的數(shù)據(jù)以相同的排列順序和位置時(shí)，可以按位置進(jìn)行合并計(jì)算，合并計(jì)算時(shí)不需要選擇標(biāo)志位置。例1：在合并計(jì)算.xlsx實(shí)例1！中計(jì)算公司年度銷售額，一季度、二季度、三季度、四季度數(shù)據(jù)分別在Sheet1:Sheet4中。注：若選擇“創(chuàng)建連至源數(shù)據(jù)的鏈接”，合并計(jì)算將隨著源數(shù)據(jù)的變化而自動(dòng)更新且分級(jí)顯示。 實(shí)例2： 按類進(jìn)行合并計(jì)算前提：在源區(qū)域數(shù)據(jù)可

12、以沒有按相同順序排列但包含了行或列標(biāo)志，則可以按照行或列標(biāo)志進(jìn)行合并計(jì)算。在“合并計(jì)算”對(duì)話框“標(biāo)志位置”選項(xiàng)框中，選定標(biāo)志的位置在“首行”或“最左列”或兩者都選。選擇合并區(qū)域時(shí)，不需要輸入行或列標(biāo)志。要求：對(duì)各個(gè)班級(jí)的收支進(jìn)行合并計(jì)算，求全年級(jí)的各項(xiàng)平均。實(shí)例3：按類進(jìn)行合并計(jì)算對(duì)一公司和二公司的收支進(jìn)行合并計(jì)算，求總公司的收入、支出、金額。6. 分列分列用于將一列數(shù)據(jù)按照分隔符或指定的寬度分為多列。操作：“數(shù)據(jù)”|“數(shù)據(jù)工具”|“分列”實(shí)例：分列要求：對(duì)姓名字段進(jìn)行分列，分為姓和名；對(duì)出生年月日進(jìn)行分列，分為年份、月份、日期；對(duì)一字符串進(jìn)行分列，寬度分別為5個(gè)字符、6個(gè)字符、4個(gè)字符、7個(gè)

13、字符；操作步驟：第1題：選擇數(shù)據(jù)區(qū)域，“數(shù)據(jù)”|“數(shù)據(jù)工具”|“分列”，分隔符號(hào)選擇為“空格”，目標(biāo)區(qū)域選擇為B2；第2題：分隔符號(hào)選擇為“其他”，并輸入“-”，目標(biāo)區(qū)域選擇為B9；第3題：分隔符號(hào)選擇為“固定寬度”，在“數(shù)據(jù)預(yù)覽”中用鼠標(biāo)單擊第5個(gè)字符、第11字符、第18個(gè)字符位置等建立分列線；第七章 數(shù)據(jù)透視表數(shù)據(jù)透視表的概念數(shù)據(jù)透視表的修飾數(shù)據(jù)透視表的操作數(shù)據(jù)透視圖1. 數(shù)據(jù)透視表的概念作用：用于分析數(shù)據(jù)列表或數(shù)據(jù)庫(kù)中的數(shù)據(jù), 快速匯總大量數(shù)據(jù)，可交互式地進(jìn)行數(shù)據(jù)的分析。利用數(shù)據(jù)透視表可：旋轉(zhuǎn)數(shù)據(jù)透視表的行列以查看對(duì)源數(shù)據(jù)的不同匯總；篩選數(shù)據(jù)；顯示部分?jǐn)?shù)據(jù)的明細(xì)數(shù)據(jù)；數(shù)據(jù)透視表的建立 操

14、作步驟：?jiǎn)螕魯?shù)據(jù)列表中的任一單元格,“插入”| “數(shù)據(jù)透視表”,在對(duì)話框中選擇“數(shù)據(jù)源”和數(shù)據(jù)透視表的位置；在“數(shù)據(jù)透視表字段列表”中選擇報(bào)表篩選字段、列標(biāo)簽字段、行標(biāo)簽字段、數(shù)值字段；（選擇字段名，直接拖動(dòng)到各自區(qū)域內(nèi)）數(shù)據(jù)透視表的結(jié)構(gòu)報(bào)表篩選字段（頁(yè)字段）：用來篩選數(shù)據(jù)；行標(biāo)簽字段（行字段）：在報(bào)表中被指定為行方向的字段，也就是分類字段；列標(biāo)簽字段（列字段）：在報(bào)表中被指定為列方向的字段，也就是系列字段；數(shù)值字段（數(shù)據(jù)字段）：匯總數(shù)據(jù)字段；默認(rèn)情況下：COUNT函數(shù)匯總文本數(shù)據(jù)； SUM函數(shù)來匯總數(shù)字?jǐn)?shù)據(jù)；實(shí)例：數(shù)據(jù)透視表1根據(jù)“家庭開支流水賬”，進(jìn)行匯總。要求：用分類匯總和數(shù)據(jù)透視表兩種

15、方法，求每個(gè)支出人的支出總額；用分類匯總和數(shù)據(jù)透視表兩種方法，求出每個(gè)支出人在每個(gè)項(xiàng)目中的支出總額；用數(shù)據(jù)透視表求出每個(gè)支出人在每一年的總支出；操作步驟：第1題：分類匯總：根據(jù)支出人字段排序，再以支出人作為分類字段、匯總方式為求和、匯總項(xiàng)為金額；數(shù)據(jù)透視表：插入點(diǎn)放在數(shù)據(jù)列表內(nèi)，單擊“插入”| “數(shù)據(jù)透視表”，以支出人為行標(biāo)簽，金額為數(shù)據(jù)字段，匯總方式為求和；操作步驟：第2題：分類匯總：以支出人作為主關(guān)鍵字段、以項(xiàng)目作為次關(guān)鍵字段進(jìn)行排序，先以支出人作為分類字段分類匯總的基礎(chǔ)上再以項(xiàng)目作為分類字段進(jìn)行分類匯總；數(shù)據(jù)透視表：以支出人作為行標(biāo)簽、項(xiàng)目作為列標(biāo)簽，金額作為數(shù)值字段；操作步驟：第3題：

16、以支出人作為行標(biāo)簽、日期作為列標(biāo)簽，創(chuàng)建數(shù)據(jù)透視表；單擊日期標(biāo)簽，選擇“數(shù)據(jù)透視表工具”| “分組”|“將所選內(nèi)容分組”，在對(duì)話框中選擇“步長(zhǎng)”為“年”；2. 數(shù)據(jù)透視表的修飾數(shù)據(jù)透視表的布局設(shè)置數(shù)據(jù)透視表樣式設(shè)置字段設(shè)置(1) 數(shù)據(jù)透視表的布局設(shè)置操作方法：“數(shù)據(jù)透視表工具”“設(shè)計(jì)”選項(xiàng)卡“布局”組，選擇相應(yīng)的命令； 數(shù)據(jù)透視表的布局設(shè)置“總計(jì)”：可設(shè)置是否顯示列總計(jì)、行總計(jì)等；“報(bào)表布局”：可設(shè)置以壓縮形式、大綱形式或以表格形式顯示；“分類匯總”：是否顯示分類匯總(2) 數(shù)據(jù)透視表樣式樣式的應(yīng)用“數(shù)據(jù)透視表工具”|“設(shè)計(jì)”選項(xiàng)卡|“數(shù)據(jù)透視表樣式”；數(shù)據(jù)透視表樣式選項(xiàng)可選“行標(biāo)題”、“列標(biāo)

17、題”、“鑲邊行”、“鑲邊列”(3) 字段設(shè)置重命名字段改變字段列的排序順序值字段設(shè)置顯示明細(xì)數(shù)據(jù)計(jì)算字段1. 重命名字段當(dāng)向數(shù)據(jù)透視表添加數(shù)值字段后，它們都將被重命名，為了不影響表格的美觀，需要用戶重命名字段。數(shù)據(jù)透視表數(shù)據(jù)區(qū)域的名稱與數(shù)據(jù)源表頭標(biāo)題行的名稱不能相同。重命名字段操作方法：在編輯欄中直接修改字段名稱；要保持原有的字段名不變，可以使用替換法，如“求和項(xiàng)：”替換為“空格”；2. 改變字段列的排列順序操作方法：右鍵單擊字段名，在快捷菜單中選擇“排序”或選擇數(shù)據(jù)項(xiàng)，單擊鼠標(biāo)右鍵，在快捷菜單中選擇“移動(dòng)”； 3. 值字段設(shè)置操作方法：選擇數(shù)值字段名，單擊鼠標(biāo)右鍵，在快捷菜單中選擇“值字段設(shè)

18、置”，可以設(shè)置匯總方式和值顯示方式； 4. 顯示明細(xì)數(shù)據(jù)操作步驟：對(duì)組合的數(shù)據(jù)，選擇字段名，單擊“活動(dòng)字段”|“展開整個(gè)字段”； 對(duì)數(shù)值單元格，雙擊該單元格，會(huì)新建顯示明細(xì)數(shù)據(jù)的工作表；實(shí)例：數(shù)據(jù)透視表2根據(jù)“超市業(yè)績(jī)表”數(shù)據(jù)，創(chuàng)建不同地區(qū)、不同店面的總銷售額數(shù)據(jù)透視表，并進(jìn)行修飾。要求：以地區(qū)、店面為行標(biāo)簽字段，銷售額為匯總字段；數(shù)據(jù)透視表修飾，達(dá)到圖片效果；操作步驟提示：“分類匯總”中選“在組的底部顯示所有的分類匯總”；“報(bào)表布局”中選“以表格形式顯示”；“空行”中選“在每個(gè)項(xiàng)目后插入空行”；“銷售額”字段重命名為“總銷售額”，并設(shè)置數(shù)字格式為“貨幣格式”；“地區(qū)”字段按升序排序；5. 計(jì)

19、算字段計(jì)算字段是通過對(duì)數(shù)據(jù)透視表中現(xiàn)有的字段進(jìn)行計(jì)算后得到的新字段。操作步驟：?jiǎn)螕魯?shù)據(jù)透視表的任意一個(gè)單元格，“數(shù)據(jù)透視表工具”|“選項(xiàng)”|“工具”|“公式”，選擇“計(jì)算字段”；在對(duì)話框內(nèi)輸入字段名稱以及公式；實(shí)例：數(shù)據(jù)透視表3以產(chǎn)品明細(xì)表為數(shù)據(jù)源，創(chuàng)建數(shù)據(jù)透視表，要求在數(shù)據(jù)透視表中添加計(jì)算字段“銷售單價(jià)”。3. 數(shù)據(jù)透視表的操作數(shù)據(jù)透視表的復(fù)制數(shù)據(jù)透視表的移動(dòng)更新數(shù)據(jù)復(fù)制數(shù)據(jù)透視表操作方法：“數(shù)據(jù)透視表工具”|“選項(xiàng)”| “操作”|“選擇”，下拉列表中選“整個(gè)數(shù)據(jù)透視表”，進(jìn)行復(fù)制粘貼；作用：數(shù)據(jù)透視表創(chuàng)建完成后，若需要對(duì)同一個(gè)數(shù)據(jù)源再創(chuàng)建數(shù)據(jù)透視表，只需對(duì)原有的數(shù)據(jù)透視表進(jìn)行復(fù)制即可，這樣

20、可以提高工作效率。數(shù)據(jù)透視表的移動(dòng)操作方法： “數(shù)據(jù)透視表工具”|“選項(xiàng)”|“操作”|“移動(dòng)數(shù)據(jù)透視表”，選擇移動(dòng)的位置，選擇放在獨(dú)立工作表內(nèi)還是已有的工作表內(nèi)；更新數(shù)據(jù)數(shù)據(jù)源改變，數(shù)據(jù)透視表不能自動(dòng)更新。操作方法：“數(shù)據(jù)透視表工具”|“選項(xiàng)”| “數(shù)據(jù)”|“刷新”4. 數(shù)據(jù)透視圖數(shù)據(jù)透視圖是數(shù)據(jù)透視表的可視化表示，是一種依賴于數(shù)據(jù)透視表的交互式圖表。操作方法： “插入”|“表”|“數(shù)據(jù)透視表”，選擇“數(shù)據(jù)透視圖”，創(chuàng)建數(shù)據(jù)透視表的同時(shí)創(chuàng)建數(shù)據(jù)透視圖或鼠標(biāo)單擊數(shù)據(jù)透視表的任一單元格，“選項(xiàng)”|“工具”|“數(shù)據(jù)透視圖”；實(shí)例：數(shù)據(jù)透視表4根據(jù)銷售單明細(xì)表，完成數(shù)據(jù)透視表。求出每個(gè)銷售員每一天的銷

21、售額總額；對(duì)數(shù)據(jù)透視表進(jìn)行組合，求出每個(gè)銷售員每一個(gè)月的銷售總額和業(yè)務(wù)量；修飾數(shù)據(jù)透視表，銷售員按升序排序，數(shù)據(jù)項(xiàng)為貨幣格式，不顯示列總計(jì)；注意：數(shù)據(jù)透視表的數(shù)據(jù)可來源于外部數(shù)據(jù)；根據(jù)同一張工作表，可創(chuàng)建多個(gè)數(shù)據(jù)透視表；數(shù)據(jù)透視表的數(shù)據(jù)字段可來自于數(shù)據(jù)列表的字段，也可以根據(jù)已有的字段插入“計(jì)算字段”；數(shù)據(jù)透視表可建立在與原有數(shù)據(jù)同一個(gè)工作表上，也可建立在新工作表上。思考題：數(shù)據(jù)透視表能否自動(dòng)更新?數(shù)據(jù)透視圖能否自動(dòng)更新？數(shù)據(jù)透視表如何移動(dòng)或復(fù)制？一張數(shù)據(jù)列表可否建多個(gè)數(shù)據(jù)透視表?數(shù)據(jù)透視表的字段名能否更改？如何依據(jù)外部數(shù)據(jù)源創(chuàng)建數(shù)據(jù)列表？數(shù)據(jù)透視表能否有一個(gè)以上的數(shù)值字段？第八章 數(shù)據(jù)圖表主要

22、內(nèi)容：創(chuàng)建數(shù)據(jù)圖表設(shè)置圖表格式圖表的選擇、移動(dòng)、縮放圖表的趨勢(shì)線特殊圖表數(shù)據(jù)圖表創(chuàng)建數(shù)據(jù)圖表有助于進(jìn)行數(shù)據(jù)分析，看清數(shù)據(jù)間彼此的關(guān)聯(lián)及差異，有助于預(yù)測(cè)和決策。1. 建立圖表兩種圖表嵌入圖表：圖表作為一個(gè)圖形對(duì)象存放在已有的工作表內(nèi)。獨(dú)立圖表：圖表獨(dú)立存放在新的工作表內(nèi)，默認(rèn)名稱為Chart1，Chart2等。嵌入圖表和獨(dú)立圖表都鏈接到數(shù)據(jù)源。創(chuàng)建圖表一般操作步驟：選擇數(shù)據(jù)區(qū)域，“插入”|“圖表”，選擇圖表類型；單擊“設(shè)計(jì)”選項(xiàng)卡，選擇“圖表樣式”和“圖表布局”以及圖表位置；單擊“布局”選項(xiàng)卡，選擇“圖表標(biāo)題”等標(biāo)簽是否顯示以及顯示的位置；單擊“格式”選項(xiàng)卡，選擇“形狀樣式”等快速創(chuàng)建圖表的方法

23、：選擇數(shù)據(jù)源，按F11鍵創(chuàng)建獨(dú)立圖表；選擇數(shù)據(jù)源，按Alt+F1鍵創(chuàng)建嵌入圖表；圖表類型Excel提供了11類圖表，每一類又分別有多種子類。柱形圖適用于顯示多組數(shù)據(jù)在一段時(shí)期內(nèi)的變化；條形圖適用于比較在特定時(shí)間內(nèi)的個(gè)別數(shù)據(jù)或多組數(shù)據(jù)；折線圖適用于顯示某段時(shí)期內(nèi)數(shù)據(jù)連續(xù)變動(dòng)情況及趨勢(shì)，主要用于強(qiáng)調(diào)時(shí)間性及數(shù)據(jù)變動(dòng)率；圓環(huán)圖與餅圖適合于顯示要突出比例關(guān)系的數(shù)據(jù)。實(shí)例1：數(shù)據(jù)圖表根據(jù)銷售表，創(chuàng)建內(nèi)嵌圖表，圖表類型三維柱形圖。要求：添加圖表標(biāo)題、坐標(biāo)軸標(biāo)題；應(yīng)用圖表樣式、填充顏色；設(shè)置數(shù)值軸刻度；設(shè)置為獨(dú)立圖表；操作步驟：“布局”選項(xiàng)卡|“標(biāo)簽”|“圖表標(biāo)題”，單擊“圖表上方”；“布局”|“標(biāo)簽”|“

24、坐標(biāo)軸標(biāo)題”；數(shù)值軸格式：“布局”|“坐標(biāo)軸”|“主要縱坐標(biāo)軸”；圖表樣式、填充顏色：“設(shè)計(jì)”|“圖表樣式”，“格式”|“形狀樣式”；位置：“設(shè)計(jì)”|“位置”|“移動(dòng)圖表”；2. 設(shè)置圖表的格式圖表工具圖表元素(1) 圖表工具激活圖表，“圖表工具”中顯示“設(shè)計(jì)”、“布局”、“格式”選項(xiàng)卡?！霸O(shè)計(jì)”選項(xiàng)卡數(shù)據(jù)組“切換行/列”：切換X軸和Y軸“選擇數(shù)據(jù)”：重新選擇數(shù)據(jù)源圖表布局組 各種預(yù)制的圖表布局，圖表布局決定了是否顯示以及如何顯示圖表的標(biāo)題、標(biāo)簽、網(wǎng)絡(luò)線、圖例、數(shù)據(jù)表等?！皥D表樣式”組各種預(yù)制的圖表樣式，包括數(shù)據(jù)系列的樣式等?！拔恢谩苯M 決定以嵌入圖表方式或獨(dú)立圖表方式存放?！安季帧边x項(xiàng)卡“標(biāo)

25、簽”組和“坐標(biāo)軸”組 設(shè)置是否顯示圖表的標(biāo)題、坐標(biāo)軸的標(biāo)題、圖例、數(shù)據(jù)標(biāo)簽、數(shù)據(jù)表、網(wǎng)絡(luò)線、坐標(biāo)軸等“背景”組 設(shè)置是否顯示圖表背景墻、基底等“格式”選項(xiàng)卡“當(dāng)前所選內(nèi)容”組 可以選擇相應(yīng)的圖表元素；“形狀樣式”組和“藝術(shù)字樣式”組“排列和大小”組(2) 圖表元素圖表是由一系列圖表元素組成的，如圖表區(qū)、繪圖區(qū)、標(biāo)題、系列、坐標(biāo)軸、數(shù)值軸、背景墻、圖例、網(wǎng)絡(luò)線等。圖表元素的選取在數(shù)據(jù)圖表內(nèi)用鼠標(biāo)選取可見的圖表元素或在“布局”或“格式”選項(xiàng)卡中“當(dāng)前所選內(nèi)容”組中“圖表元素”下拉框中選擇元素名稱；設(shè)置圖表元素的格式操作方法：選擇圖表元素，“布局”選項(xiàng)卡中單擊“設(shè)置所選內(nèi)容格式”命令，打開相應(yīng)的對(duì)話

26、框設(shè)置格式或選擇圖表元素，在快捷菜單中選擇相應(yīng)的格式設(shè)置命令；實(shí)例2：數(shù)據(jù)圖表根據(jù)一月份的銷售數(shù)據(jù)，創(chuàng)建餅圖。3 . 圖表的選定、移動(dòng)和縮放激活圖表嵌入圖表：?jiǎn)螕魣D表;獨(dú)立圖表：?jiǎn)螕魣D表工作表的名稱;圖表的移動(dòng)和縮放嵌入式圖表的移動(dòng)、縮放、復(fù)制和刪除：和圖形對(duì)象的操作相同。獨(dú)立圖表的縮放、復(fù)制和刪除獨(dú)立圖表的縮放：調(diào)整“顯示比例”;獨(dú)立圖表的刪除：刪除工作表，若選擇圖表，按DEL鍵，將刪除圖表區(qū)域的部分內(nèi)容;思考題:若隱藏或刪除了數(shù)據(jù)源中的某些數(shù)據(jù)，數(shù)據(jù)圖表會(huì)有什么變化？若刪除了數(shù)據(jù)圖表中的數(shù)據(jù)系列，數(shù)據(jù)源會(huì)有什么變化？思考題：如何增加和刪除數(shù)據(jù)系列？增加數(shù)據(jù)系列： 選擇要增加的數(shù)據(jù)區(qū)域，復(fù)制

27、，單擊圖表，粘貼到圖表區(qū)域或單擊“選擇數(shù)據(jù)”命令；刪除數(shù)據(jù)系列：在圖表內(nèi)選擇要?jiǎng)h除的數(shù)據(jù)系列，單擊Delete按鈕；4. 圖表的趨勢(shì)線操作步驟：創(chuàng)建二維柱形圖，“布局”選項(xiàng)卡|“趨勢(shì)線”，選擇趨勢(shì)線類型；在“設(shè)置趨勢(shì)線格式”對(duì)話框中選擇“顯示公式”、“顯示R平方值”，選擇R平方值最高的類型；5. 特殊圖表組合圖表根據(jù)2008數(shù)碼產(chǎn)品銷售表，創(chuàng)建組合圖表，其中一圖表類型為柱形圖，另一圖表類型為折線圖。2008數(shù)碼產(chǎn)品銷售量U盤MP31月3201502月2901353月3051404月330123操作提示：選擇整個(gè)數(shù)據(jù)，創(chuàng)建二維柱形圖;選擇MP3數(shù)據(jù)系列，“設(shè)計(jì)”|“類型”|“更改圖表類型”，選擇

28、折線圖;注意：不能組合二維和三維圖表類型。特殊圖表雙軸圖表企業(yè)的銷售統(tǒng)計(jì)中產(chǎn)品的銷售量和銷售額兩類數(shù)據(jù)是相互關(guān)聯(lián)的。制作圖表，對(duì)銷售量和銷售額同時(shí)進(jìn)行分析。操作步驟:選擇數(shù)據(jù)區(qū)域，創(chuàng)建二維柱形圖;選擇“銷售量”數(shù)據(jù)系列，“布局”|“當(dāng)前所選內(nèi)容”|“設(shè)置所選內(nèi)容格式”，在“設(shè)置數(shù)據(jù)系列格式”對(duì)話框中選擇“次坐標(biāo)軸”，更改圖標(biāo)類型為折線圖;特殊圖表：動(dòng)態(tài)圖表1要求：制作隨銷售數(shù)據(jù)變化而變化的圖表，當(dāng)源數(shù)據(jù)增加或減少時(shí)，自動(dòng)更新圖表。補(bǔ)充函數(shù)：OFFSET函數(shù)格式：OFFSET(reference,rows,cols,height,width)功能：以指定的引用為參照系，通過給定偏移量得到新的引用

29、。如：=OFFSET(C3,2,3,1,1)Reference 作為偏移量參照系的引用區(qū)域Rows 相對(duì)于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。Cols 相對(duì)于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。Height 高度，即所要返回的引用區(qū)域的行數(shù)。Width 寬度，即所要返回的引用區(qū)域的列數(shù)。操作步驟:定義名稱：時(shí)間和銷售量名稱：時(shí)間 在引用位置上輸入公式：=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1)名稱：銷售量在引用位置上輸入公式：=OFFSET(Sheet2!$B$2,0,0,COUNTA(Sheet2!$B:$B)-1)

30、其中COUNTA(value1,value2,) 計(jì)算非空單元格的數(shù)目操作步驟:選擇數(shù)據(jù)列表，創(chuàng)建圖表，單擊“設(shè)計(jì)”|“數(shù)據(jù)”|“選擇數(shù)據(jù)”，在“選擇數(shù)據(jù)源”對(duì)話框中選取 “銷售量”系列，在“值”一欄中輸入“=Sheet2!銷售量”；在“分類軸標(biāo)簽”設(shè)置中輸入“=Sheet2!時(shí)間”;（請(qǐng)不要忽略“銷售量”名稱前的工作表名稱）特殊圖表：動(dòng)態(tài)圖表2數(shù)據(jù)表中的銷售數(shù)據(jù)越多，圖表上的數(shù)據(jù)點(diǎn)也越來越擁擠。要求：制作始終反應(yīng)最新幾個(gè)數(shù)據(jù)的動(dòng)態(tài)圖表；為了更好地控制數(shù)據(jù)點(diǎn)，在圖表中增加窗體控件控制每次顯示的數(shù)據(jù)點(diǎn)數(shù)目。操作步驟：定義“日期”和“銷售量”名稱定義“日期”名稱：引用位置上輸入公式：=OFFSET

31、(Sheet4!$A$1,COUNTA(Sheet4!$A:$A)-Sheet4!$D$1,0,Sheet4!$D$1,1)；定義“銷售量”名稱：引用位置上輸入公式： =OFFSET(Sheet4!$B$1,COUNTA(Sheet4!$B:$B)-Sheet4!$D$1,0,Sheet4!$D$1,1);(注意：其中D1單元格中存放了要顯示的記錄個(gè)數(shù)）； 在“選擇數(shù)據(jù)源”對(duì)話框中選擇銷售量系列，在“值”欄中輸入“=Sheet4!銷售量”，選擇分類軸標(biāo)簽，輸入:=Sheet4!日期，這樣圖表每次顯示出最新的幾個(gè)數(shù)據(jù)；在“圖表窗口”中添加兩個(gè)標(biāo)簽和微調(diào)控件，標(biāo)簽1標(biāo)題文字為“每次顯示最近數(shù)據(jù)個(gè)數(shù)

32、”，選擇標(biāo)簽2，在編輯欄中輸入“=Sheet4!$D$1”,在微調(diào)控件的“單元格鏈接”屬性中設(shè)置為“Sheet4!$D$1”；補(bǔ)充函數(shù)：PMT函數(shù)格式：PMT(利率，期限，PV，F(xiàn)V，類型)功能：基于固定利率和等額分期付款方式，返回投資或貸款的每期付款額其中 利率和期限值的單位一致，或?yàn)槟昀驶驗(yàn)樵吕?；類型?或省略，表示為期末；若為1，表示期初；PV為現(xiàn)值，F(xiàn)V為未來值實(shí)例：例1：貸款700000元，分20年還清，月利率為0.5%，用PMT函數(shù)計(jì)算每月月初償還金額的公式是？到期共支付了多少利息？每月還款額=PMT(0.5%,20*12,700000,0,1)=-4990.07共支付利息：=

33、4990.07*12*20-700000=497616.6實(shí)例：例2：若某人為將來得到一筆退休金每月做定期儲(chǔ)蓄，他希望在20年以后能拿到40萬退休金，預(yù)期年利率為6%，則每月的儲(chǔ)蓄額為多少? =PMT(6%/12,20*12,0,400000)補(bǔ)充函數(shù)：FV函數(shù)基于固定利率及等額分期付款方式返回某項(xiàng)投資的未來預(yù)期值（復(fù)利計(jì)算)，通常用來求定期存款以及零存整取到期之后本金和利息之和。FV（rate，nper，PMT，PV，type）FV函數(shù)實(shí)例： 若某人每年年初存入儲(chǔ)蓄的金額是5萬，設(shè)目前10年期定期存款的年利率為3.25%，以復(fù)利計(jì)算，求10年后連本帶利息能拿到多少？（零存整取，PV一般取0）

34、=FV(3.25%,10,-50000)PV函數(shù)PV函數(shù)返回投資的現(xiàn)值，現(xiàn)值為一系列未來付款的當(dāng)前值的累積和 。函數(shù)格式：PV(rate，nper，PMT，F(xiàn)V，type)每期給付或所得之金額必須是固定，一筆貸款的未來值為0PV函數(shù)實(shí)例1：每月底支付一項(xiàng)保險(xiǎn)年金500元，設(shè)每年的投資收益率為8%，投資年限為20年，則這項(xiàng)年金的現(xiàn)值為多少？=PV(8%/12,20*12,500,0,0)=59777.15實(shí)例2：例如，假設(shè)要購(gòu)買一項(xiàng)保險(xiǎn)年金，該保險(xiǎn)可以在今后二十年內(nèi)每月末回報(bào)￥600。此項(xiàng)年金的購(gòu)買成本為80,000，假定投資回報(bào)率為8%。那么該項(xiàng)年金的現(xiàn)值？PV(0.08/12, 12*20,

35、600,0) 計(jì)算結(jié)果為：￥-71,732.58。第九章 數(shù)據(jù)分析數(shù)據(jù)表（模擬運(yùn)算表）單變量求解規(guī)劃求解與分析工具庫(kù)方案分析1. 數(shù)據(jù)表數(shù)據(jù)表是一個(gè)單元格區(qū)域，顯示公式中的某些參數(shù)值的變化將如何影響公式結(jié)果。數(shù)據(jù)表有兩種，單變量數(shù)據(jù)表和雙變量數(shù)據(jù)表。雙變量數(shù)據(jù)表反映兩個(gè)變量的改變對(duì)一個(gè)公式結(jié)果的影響。(1) 單變量數(shù)據(jù)表單變量數(shù)據(jù)表查看一個(gè)變量的不同值對(duì)一個(gè)或多個(gè)公式結(jié)果的影響。輸入值需排在一行或一列?？勺儐卧瘢汗街休斎胫狄鎿Q的單元格。行引用和列引用數(shù)據(jù)表中，輸入值要排在一行或一列。如果輸入值排在同一列，則在“輸入引用列的單元格”框中，鍵入可變單元格。 如果輸入值排在同一行上，則在“輸入

36、引用行的單元格”框中，鍵入可變單元格?！皢巫兞繑?shù)據(jù)表”中公式位置當(dāng)輸入值排成一列時(shí)，在第一個(gè)數(shù)值的上一行且處于數(shù)值列右側(cè)的單元格中鍵入所需公式。（可以在公式右邊的單元格中鍵入其它公式）當(dāng)輸入值排成一行時(shí)，在第一個(gè)數(shù)值左邊一列且數(shù)值行下方的單元格內(nèi)鍵入所需公式。（可以為一個(gè)以上的公式）數(shù)據(jù)表計(jì)算結(jié)果數(shù)據(jù)表的計(jì)算結(jié)果存放在數(shù)組中，不能單獨(dú)修改其中的某一個(gè)值。要清除計(jì)算結(jié)果，選中數(shù)據(jù)表中所有結(jié)果，按Delete鍵即可。單變量數(shù)據(jù)表操作步驟： 根據(jù)輸入值的方向，選擇公式的所在位置，輸入公式；“數(shù)據(jù)”|“數(shù)據(jù)工具”|“假設(shè)分析”|“數(shù)據(jù)表”，在“數(shù)據(jù)表”對(duì)話框中鍵入引用的單元格；實(shí)例1：?jiǎn)巫兞繑?shù)據(jù)表例：

37、計(jì)算商場(chǎng)出售商品的獲利情況。公式為毛利=進(jìn)貨成本*加價(jià)百分比*銷售數(shù)量-銷售費(fèi)用操作步驟：參數(shù)寫在同一列A10:A14上，因此選擇B9單元格輸入公式：=B1*B2*B3-B4；選擇A9:B14區(qū)域，在“數(shù)據(jù)表”對(duì)話框中，選擇 “輸入引用列的單元格”，單擊B2單元格，則產(chǎn)生結(jié)果，此結(jié)果區(qū)域?yàn)閿?shù)組；實(shí)例2：?jiǎn)巫兞繑?shù)據(jù)表“考勤應(yīng)扣款計(jì)算表”中用數(shù)據(jù)表計(jì)算每個(gè)部門的基本工資、扣款合計(jì)的總額。操作步驟：設(shè)A19單元格為可變單元格，輸入任何一個(gè)部門的值；所有參數(shù)值寫在C20:C23區(qū)域，在D19、E19單元格輸入公式：SUMIF(C3:C16,A19,E3:E16)和SUMIF(C3:C16,A19,L3

38、:L16)操作步驟：選擇C19:E23區(qū)域，在“數(shù)據(jù)表”對(duì)話框的“輸入引用列的單元格”中輸入“A19”，單擊“確定”按鈕；(2) 雙變量數(shù)據(jù)表創(chuàng)建雙變量數(shù)據(jù)表的過程與創(chuàng)建單變量數(shù)據(jù)表類似。雙變量中的兩組輸入值使用同一個(gè)公式，一組輸入值在公式下方的同一列上，另外一組輸入值在公式右側(cè)的同一行上。操作步驟：在某個(gè)單元格內(nèi)輸入所需的引用兩個(gè)輸入單元格的公式。在公式下面同一列中鍵入一組輸入值，在公式右邊同一行中鍵入第二組輸入數(shù)值；選定公式及輸入行和列的單元格區(qū)域；“數(shù)據(jù)”|“數(shù)據(jù)工具”|“假設(shè)分析”|“數(shù)據(jù)表”，輸入行引用的單元格以及輸入列引用的單元格；實(shí)例3：雙變量數(shù)據(jù)表有人要申請(qǐng)等額本息貸款購(gòu)買房子

39、，其中利率固定，考慮幾種可能的貸款年限和貸款金額下求每月還貸額，選擇最佳方案。操作步驟：B5單元格先輸入公式，然后在B6：B10區(qū)域和C5:G5區(qū)域輸入輸入值；選擇B5:G10區(qū)域，在“數(shù)據(jù)表”對(duì)話框中“引用行的單元格”里選擇F2，“引用列的單元格”里選擇C3；實(shí)例4：雙變量數(shù)據(jù)表在“考勤應(yīng)扣款計(jì)算表”中用數(shù)據(jù)表計(jì)算不同部門、不同職位的人數(shù)。操作步驟：在A19:B20區(qū)域，創(chuàng)建條件區(qū)域；在D20:D23區(qū)域輸入部門的所有參數(shù)值，在E19:H19區(qū)域輸入職位的所有參數(shù)值；在D19單元格輸入公式：=DCOUNT(A2:L16, “基本工資,A19:B20);操作步驟：選擇D19:H23區(qū)域，在“數(shù)

40、據(jù)表”對(duì)話框的“輸入引用列的單元格”中輸入 “B20”，“輸入引用行的單元格”中輸入“A20”，單擊“確定”按鈕；2. 單變量求解單變量求解是在已知結(jié)果的情況下推測(cè)出形成這個(gè)結(jié)果的參數(shù)。在Excel中根據(jù)所提供的目標(biāo)值，不斷改變公式中涉及的引用單元格的輸入值，直到達(dá)到所需要求的公式的目標(biāo)值。公式所在的單元格為目標(biāo)單元格，在求解過程中可修改其值的單元格為可變單元格，“單變量求解”中可變單元格為一個(gè)。操作步驟：選擇目標(biāo)單元格，“數(shù)據(jù)”|“數(shù)據(jù)工具”|“假設(shè)分析”|“單變量求解”，在“單變量求解”對(duì)話框中輸入目標(biāo)單元格、目標(biāo)值以及可變單元格；實(shí)例5：?jiǎn)巫兞壳蠼饽橙藴?zhǔn)備向銀行貸款，貸款期限為30年，貸

41、款利率為6%，每月能承受的還款額為5000元，那么能貸多少。操作步驟選擇B5單元格，輸入公式：=PMT(B4/12,B3,B2)；“數(shù)據(jù)”| “數(shù)據(jù)工具”|“假設(shè)分析”|“單變量求解”，目標(biāo)單元格中選擇B5，目標(biāo)值-5000，可變單元格選擇B2；實(shí)例6：?jiǎn)巫兞壳蠼饫汉镒映蕴?，有x個(gè)桃，每天吃剩下桃的一半又多吃一個(gè)，第十天剩了一個(gè)桃，求原來一共有多少個(gè)桃？操作步驟：設(shè)原來的桃個(gè)數(shù)放在B15單元格里，在B16單元格里輸入公式：=B15-B15/2-1,拖動(dòng)填充柄到B25；“單變量求解”對(duì)話框中，選擇目標(biāo)單元格為B25，目標(biāo)值為1，可變單元格為B15；3. 規(guī)劃求解 “規(guī)劃求解”與“分析工具庫(kù)”是

42、Excel提供的兩組數(shù)據(jù)分析加載宏。加載宏的安裝：?jiǎn)螕鬙ffice按鈕，選擇“Excel選項(xiàng)”；選擇“加載宏”類別，選擇“Excel加載宏”，單擊“轉(zhuǎn)到”；在“加載宏”對(duì)話框中選中“分析工具庫(kù)”和“規(guī)劃求解加載項(xiàng)”；規(guī)劃求解規(guī)劃求解可求多個(gè)變量在一定約束條件下的最佳解?！皵?shù)據(jù)”|“分析”|“規(guī)劃求解”規(guī)劃求解的選項(xiàng)：目標(biāo)單元格，可選最大值、最小值或某一數(shù)值；可變單元格，用逗號(hào)分隔不相鄰單元格的引用；約束條件；規(guī)劃求解的選項(xiàng)采用線性模型：如果求解模型是線性模型，需選“采用線性模型”復(fù)選框，規(guī)劃求解一定能找到求解模型的最優(yōu)解。如果一個(gè)求解模型是非線性的，并不一定都能得到滿意的結(jié)果，它受初值以及規(guī)劃

43、求解選項(xiàng)（如迭代次數(shù)、迭代時(shí)間等）的影響，在求解過程中按Esc鍵可中止求解過程。假定非負(fù)實(shí)例7：規(guī)劃求解若有如下約束條件，y-x=1; 3x+y=0；y=0，求滿足上述四個(gè)條件的x、y使得x+y取最大值。操作步驟：在A4單元格里輸入公式：=A3-A2，在A5單元格里輸入公式：=3*A2+A3；在B7單元格里輸入公式：=A2+A3；在 “規(guī)劃求解參數(shù)”對(duì)話框中如圖設(shè)置。實(shí)例8：規(guī)劃求解設(shè)某食品公司生產(chǎn)A、B兩種產(chǎn)品，需要甲乙丙丁4種原料。每種產(chǎn)品所需的原料和產(chǎn)品的利潤(rùn)值以及每種原料的儲(chǔ)備量具體數(shù)據(jù)如圖所示：步驟1：建立數(shù)學(xué)模型設(shè)A、B兩種產(chǎn)品分別生產(chǎn)x1、x2噸，則按要求建立條件： 2x1+2x

44、2=12 x1+2x2=8 4x1+0 x2=16 0 x1+4x2=0,x2=0 求2x1+3x2的最大值補(bǔ)充：SUMPRODUCT函數(shù)格式：SUMPRODUCT(array1, array2, array3, .)功能：在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。要求：數(shù)組參數(shù)必須具有相同的維數(shù)。步驟2：規(guī)劃求解設(shè)置在B5單元格里輸入公式：=SUMPRODUCT(B2:B3,$G$2:$G$3)，并把公式填充到C5:E5單元格；在B6單元格里輸入公式：=SUMPRODUCT(F2:F3,G2:G3)在“規(guī)劃求解參數(shù)”對(duì)話框設(shè)置參數(shù)，參考圖片，“選項(xiàng)”中選擇“采用線性模型”和

45、“假定非負(fù)”；4. 方案分析方案就是保存在工作表中并可進(jìn)行自動(dòng)替換的一組值，可以比較一組變量的不同的取值對(duì)結(jié)果的影響，也可以建立報(bào)表對(duì)這些方案進(jìn)行對(duì)比或匯總?！皵?shù)據(jù)”|“數(shù)據(jù)工具”|“假設(shè)分析”|“方案管理器”創(chuàng)建、顯示、編輯、刪除方案合并方案：可以把其它工作表的所有方案都復(fù)制到當(dāng)前工作表中。方案分析生成方案總結(jié)生成方案摘要方案數(shù)據(jù)透視表保護(hù)方案在方案管理器中，選定方案名稱，“編輯”里設(shè)為“防止更改”和“隱藏”。當(dāng)工作表受保護(hù)時(shí)能添加方案，但不能編輯或刪除已有方案。思考題:方案管理器中顯示的是當(dāng)前工作表的方案還是當(dāng)前工作簿的方案？若對(duì)某一個(gè)方案設(shè)置保護(hù)，不允許別人修改，如何操作？第十章 宏及其

46、應(yīng)用宏的概念錄制宏編輯宏運(yùn)行宏加載宏指定宏VBA1. 宏的概念宏是存儲(chǔ)在Visual Basic模塊中的一系列命令和函數(shù)，是一段可執(zhí)行的VBA(Visual Basic for Application)程序。創(chuàng)建宏的兩種方法：錄制宏：錄制用戶所做的一系列操作，Excel會(huì)存儲(chǔ)操作時(shí)的每個(gè)步驟信息；用VB程序設(shè)計(jì)語言編寫宏代碼；宏的安全性Excel 2007，默認(rèn)情況下禁用了宏。要運(yùn)行宏，應(yīng)更改安全性設(shè)置。 更改安全性設(shè)置單擊Office按鈕，選擇“Excel選項(xiàng)”|“信任中心”類別|“信任中心設(shè)置”|“宏設(shè)置”，啟用宏或可以通過“開發(fā)工具”|“代碼”|“宏安全性”，更改安全性設(shè)置；包含宏的文件

47、保存保存宏時(shí)，選擇文件類型為啟用宏的工作簿（*.xlsm），而非默認(rèn)的工作簿類型（*.xlsx）。錄制宏的優(yōu)點(diǎn)：宏錄制簡(jiǎn)單，不涉及較深的VBA代碼；幫助用戶快速建立某自定義功能的VBA代碼；建立帶有快捷鍵的自定義過程；錄制宏的缺點(diǎn)：通過錄制宏獲取的宏代碼具有不可彌補(bǔ)的缺點(diǎn)。代碼生硬，欠缺靈活性；代碼冗余嚴(yán)重；宏代碼無法處理需要循環(huán)的事物；錄制宏獲取的代碼效率低下，直接使用這些宏過程無形中會(huì)增加VBA應(yīng)用的開銷；2. 錄制宏錄制宏操作步驟：“開發(fā)工具”|“代碼”|“錄制宏”；（或“視圖”|“宏”，選擇“錄制宏”或單擊狀態(tài)欄的“錄制宏”按鈕） 在對(duì)話框中輸入宏名，并為其設(shè)置選項(xiàng)； 開始進(jìn)行操作，錄

48、制結(jié)束時(shí)按“開發(fā)工具”|“代碼”|“停止錄制”（或單擊狀態(tài)欄上的“停止錄制”按鈕）宏選項(xiàng)宏名和快捷鍵宏名稱的首字必須是字母或漢字，其它字符可以是字母、數(shù)字或下滑線字符。宏名稱不允許有空格。宏快捷鍵使任何Excel默認(rèn)鍵無效。因此設(shè)置快捷鍵時(shí)，輸入Shift+字母，若輸入Shift+a，則快捷鍵為Ctrl+Shift+A。（注意：需按Ctrl+Shift+a/A）宏選項(xiàng)保存位置當(dāng)宏保存的位置為個(gè)人宏工作簿時(shí)，宏將隨Excel的使用而激活；當(dāng)保存在當(dāng)前工作簿時(shí)，只有打開當(dāng)前工作簿時(shí)宏才能激活。個(gè)人宏工作簿，是為宏而設(shè)計(jì)的一種特殊的具有自動(dòng)隱藏特性的工作簿。第一次將宏創(chuàng)建到個(gè)人宏工作簿時(shí)，會(huì)創(chuàng)建名為

49、“PERSONAL.XLSB的新文件。 實(shí)例：宏建立一個(gè)宏，名稱為“單元格格式”要求：字體、字號(hào)、數(shù)字格式等；利用絕對(duì)引用和相對(duì)引用兩種方式創(chuàng)建宏，并比較一下效果以及代碼注：在記錄或編寫宏之前，應(yīng)先制定計(jì)劃，確定宏要執(zhí)行的步驟和命令。因?yàn)槿绻谟涗浐陼r(shí)出現(xiàn)失誤，對(duì)失誤的糾正也記錄在宏中。 宏的絕對(duì)引用和相對(duì)引用宏的絕對(duì)引用：指錄制宏時(shí)每一個(gè)步驟都用絕對(duì)地址記錄下來的叫絕對(duì)引用，默認(rèn)情況下為絕對(duì)引用。宏的相對(duì)引用：指錄制宏時(shí)每一步都用相對(duì)地址來記錄下來的叫相對(duì)引用。相對(duì)引用時(shí)，將記錄錄制宏前最后選定的活動(dòng)單元格和錄制宏時(shí)的活動(dòng)單元格的相對(duì)位置。因此宏運(yùn)行時(shí)，執(zhí)行的單元格將和運(yùn)行宏時(shí)的活動(dòng)單元格以

50、及錄制宏時(shí)的相對(duì)位置有關(guān)。實(shí)例：宏的相對(duì)引用要求：定義宏，完成刪除當(dāng)前行;在當(dāng)前單元格前插入空行；總結(jié)：絕對(duì)引用：錄制宏的第一個(gè)操作是選定單元格時(shí)，運(yùn)行宏時(shí)也從絕對(duì)地址開始；若第一個(gè)操作不是選定單元格時(shí)，運(yùn)行宏時(shí)的第一步是從當(dāng)前活動(dòng)單元格開始。相對(duì)引用：錄制宏的第一個(gè)操作是選定單元格時(shí)，運(yùn)行宏的結(jié)果與錄制宏前的活動(dòng)單元格與第一個(gè)操作選定的單元格的相對(duì)位置以及運(yùn)行宏時(shí)的活動(dòng)單元格有關(guān)；若一個(gè)操作不是選定單元格時(shí)，運(yùn)行宏的結(jié)果與運(yùn)行宏時(shí)的活動(dòng)單元格有關(guān)，即所謂的指哪兒打哪兒。思考題：一個(gè)存放于個(gè)人宏工作簿的宏，如何刪除？在“宏”對(duì)話框中選擇personal.xlsb中的宏名，刪除此宏。3. 編輯宏

51、操作步驟： 在“宏”對(duì)話框中選擇宏名，單擊“編輯”按鈕，進(jìn)入宏代碼窗口； 在宏代碼窗口中，可以對(duì)代碼直接進(jìn)行修改；注意：錄制宏產(chǎn)生的代碼往往比用戶直接編輯的代碼復(fù)雜。4. 運(yùn)行宏運(yùn)行宏，可執(zhí)行宏當(dāng)中的命令和函數(shù)，也就是重復(fù)用戶錄制新宏時(shí)實(shí)際所做的操作。 運(yùn)行宏的方法“宏”對(duì)話框中選擇宏名，單擊“執(zhí)行”或按快捷鍵或通過圖形、控件按鈕或通過快速訪問工具欄上的宏按鈕；提前中止宏的運(yùn)行，可按Esc鍵5. 指定宏為對(duì)象、圖形或控件指定宏操作步驟：右擊要向其指定宏的對(duì)象、圖形或控件，在快捷菜單中單擊“指定宏”；在對(duì)話框中選擇宏名；指定宏在快速訪問工具欄上添加宏按鈕并為其指定宏 操作步驟：?jiǎn)螕鬙ffice按

52、鈕，在“Excel選項(xiàng)”對(duì)話框中選擇“自定義”，“從下列位置選擇命令”下拉列表框中選擇“宏”，選擇“宏名”，單擊“添加”；在“自定義快速訪問工具欄”中選擇“宏名”，單擊“修改”按鈕，選擇按鈕；實(shí)例：指定宏定義宏，在 “銷項(xiàng)查詢表”中完成高級(jí)篩選，并把此宏指定在命令按鈕上。6. 加載宏 加載宏是一類程序，通過加載宏，Excel可以增加新的可選命令或新的功能。這些命令或功能是Excel沒有的或者暫時(shí)不能使用的。 加載宏的類型Excel內(nèi)置的加載宏，如規(guī)劃求解等；來自于其他資源的用于Excel的加載宏；組件對(duì)象模型COM加載宏等；思考題：如果要在本地計(jì)算機(jī)的其他工作簿中使用用戶錄制的宏，應(yīng)該怎么辦？

53、如果要在其他的計(jì)算機(jī)中也要使用用戶錄制的宏應(yīng)該怎么辦？答案1： 宏保存在“個(gè)人宏工作簿”答案2：宏保存為“加載宏”如何添加其他來源的加載宏 在“Excel選項(xiàng)”對(duì)話框中，選擇“加載項(xiàng)”，單擊“轉(zhuǎn)到”，在“加載宏”對(duì)話框中單擊“瀏覽”，選擇加載宏的名稱，如*.xlam文件。7. VBAVBA全稱為Visual Basic for Application，可以認(rèn)為是Visual Basic的一個(gè)用于特定應(yīng)用程序Office的子集。VBA開發(fā)環(huán)境就是VBE。VBE是一個(gè)分離出來的、與Excel無縫結(jié)合的應(yīng)用程序，它不能單獨(dú)運(yùn)行。(1) VBA開發(fā)環(huán)境Excel切換到VBE的方法“開發(fā)工具”|“代碼”|“Visual Basic”或“開發(fā)工具”|“控件”|“查看代碼”VBE窗口VBE窗口是高度可定制的窗口，一般包括工程資源管理器、代碼窗口、監(jiān)視窗口、屬性窗口、立即窗口、本地窗口及對(duì)象瀏覽器。VBA簡(jiǎn)介 對(duì)象：VBA是一種面向?qū)ο蟮恼Z言，如工作表、單元格、圖表