Excel 2016數(shù)據(jù)處理與分析（微課版）PPT完整全套教學(xué)課件

第1章Excel基礎(chǔ)主要內(nèi)容1.1認(rèn)識Excel1.2Excel文件操作1.1認(rèn)識Excel1.1.1Excel中的基本元素.xlsx1.1.1Excel中的基本元素工作簿1.1.1Excel中的基本元素PPT模板下載：/moban/行業(yè)PPT模板：/hangye/節(jié)日PPT模板：/jieri/PPT素材下載：/sucai/PPT背景圖片：/beijing/PPT圖表下載：/tubiao/優(yōu)秀PPT下載：/xiazai/PPT教程：/powerpoint/Word教程：/word/Excel教程：/excel/資料下載：/ziliao/PPT課件下載：/kejian/范文下載：/fanwen/試卷下載：/shiti/教案下載：/jiaoan/字體下載：/ziti/

工作表1.1.1Excel中的基本元素單元格1.1.2Excel的工作界面1.1.2Excel的工作界面工作表:Sheet1的工作區(qū)域默認(rèn)工作簿名稱:工作簿1工作表標(biāo)簽快速訪問工具欄

Excel中的功能操作分為文件、開始、插入、頁面布局、公式、數(shù)據(jù)、審閱、視圖等等。各選項卡中收錄相關(guān)的功能群組，方便使用者切換、選用。1.1.2Excel的工作界面行號1~1048576,共2^20=1048576行列號A~XFD，共2^14=16384列名稱框地址欄1.1.2Excel的工作界面鼠標(biāo)的正常形狀編輯欄組活動單元格:A1功能區(qū)當(dāng)要使用“功能區(qū)”時，只要將鼠標(biāo)移到任一個選項卡上單擊即可開啟；但當(dāng)鼠標(biāo)移到其他地方再單擊時，“功能區(qū)”又會自動隱藏了。1.1.2Excel的工作界面在功能區(qū)的右下角按下按鈕，可以打開對話框做更詳細(xì)的設(shè)定。例如我們想要改變單元格的某些屬性，就可以單擊“開始”選項卡中“字體”功能區(qū)右下角的“啟動器”按鈕，打開“設(shè)定單元格格式”對話框來調(diào)整。1.1.2Excel的工作界面如果覺得功能區(qū)占用太大的版面位置，可以將“功能區(qū)”隱藏起來。1.1.3工作表操作單擊工作表標(biāo)簽右側(cè)的加號。右鍵單擊工作表標(biāo)簽，通過快捷菜單中選擇。通過菜單選擇插入工作表。新建工作表1.1.3工作表操作單擊工作表標(biāo)簽，拖動其到所需的位置。調(diào)整工作表順序1.1.3工作表操作通過菜單選擇刪除工作表。右鍵單擊工作表標(biāo)簽，在快捷菜單中選擇。刪除工作表1.1.3工作表操作右鍵單擊工作表標(biāo)簽，在快捷菜單中選擇。雙擊工作表標(biāo)簽，對其進行編輯。重命名工作表1.1.3工作表操作右鍵單擊工作表標(biāo)簽，在快捷菜單中選擇。按下Ctrl鍵的同時將其拖動到所需的位置。復(fù)制工作表1.1.3工作表操作右鍵單擊工作表標(biāo)簽，選擇相應(yīng)的操作。更改工作表標(biāo)簽顏色1.1.3工作表操作右鍵單擊工作表標(biāo)簽，選擇相應(yīng)的操作。右鍵單擊任意工作表標(biāo)簽，然后打開“取消隱藏”對話框，選擇相應(yīng)的操作。隱藏和取消隱藏工作表1.1.4數(shù)據(jù)區(qū)域的選擇整行整列連續(xù)區(qū)域整個數(shù)據(jù)區(qū)域整個工作表區(qū)域連續(xù)區(qū)域的選擇1.1.4數(shù)據(jù)區(qū)域的選擇借助Ctrl鍵。在“名稱框”中輸入用逗號分隔的區(qū)域的地址。通過“定位條件”對話框。不連續(xù)區(qū)域的選擇1.1.5單元格的基本操作“數(shù)字”單元格的格式設(shè)置“對齊”“字體”“邊框”“填充”“保護”1.1.5單元格的基本操作“單元格樣式”單元格的樣式設(shè)置“套用表格格式”“條件格式”1.2Excel文件操作1.2Excel文件操作xlsx:不包含宏的工作簿文件（默認(rèn)）xlsm:包含宏的工作簿文件xltx:不包含宏的工作簿模板文件xltm:包含宏的工作簿模板文件xlsa:加載項文件xlsb:與舊的xls格式相似，但具有新功能的二進制文件xlsk:備份文件……Excel文件格式包括1.2Excel文件操作Excel文件的保存1.2Excel文件操作Excel文件的保存1.2Excel文件操作Excel文件的保護1.2Excel文件操作Excel文件的保護再見！第1章

數(shù)據(jù)輸入的格式設(shè)置主要內(nèi)容2.1數(shù)據(jù)驗證2.2多級菜單2.1數(shù)據(jù)驗證2.1.1案例說明制作如圖所示的表格。2.1.2知識要點分析1.數(shù)據(jù)輸入

先選擇單元格，再輸入數(shù)據(jù)。數(shù)據(jù)會在單元格和編輯欄中同時顯示輸入的內(nèi)容。用①Enter鍵（活動單元格向下移動）；②Tab鍵（活動單元格向右移動）；③單擊編輯欄上的“

”按鈕（活動單元格不改變）三種方法確認(rèn)輸入。如果要放棄剛才輸入的內(nèi)容，單擊編輯欄上的“

”按鈕或使用鍵盤上的Esc鍵。2.1.2知識要點分析1.數(shù)據(jù)輸入

文本的輸入文本：由字母、漢字、數(shù)字等符號形成的字符串。輸入文本：直接輸入文本內(nèi)容。輸入數(shù)值文本：在數(shù)值前加單引號。如：‘2006010101。默認(rèn)格式下，Excel將輸入的文本在單元格中左對齊。2.1.2知識要點分析1.數(shù)據(jù)輸入數(shù)值型數(shù)據(jù)可以直接輸入。默認(rèn)格式下，Excel將輸入的數(shù)值在單元格中右對齊。注意：當(dāng)單元格中數(shù)據(jù)以科學(xué)計數(shù)法表示或者填滿了“#”符號時，表示這一列沒有足夠的寬度來正確顯示數(shù)字，在這種情況下，需要改變數(shù)字格式或者改變列寬。3.06×1063.06E+062.1.2知識要點分析1.數(shù)據(jù)輸入日期和時間的輸入輸入日期：用斜杠/或連字符-作為分隔符。如：2008/5/12輸入時間：用冒號:作為分隔符。如：2:28或14:28:04輸入系統(tǒng)的日期：按組合鍵Ctrl+;

輸入系統(tǒng)的時間：按組合鍵Ctrl+Shift+;2.1.2知識要點分析2.數(shù)據(jù)驗證單元格中的數(shù)據(jù)是0~100之間整數(shù)。2.1.2知識要點分析2.數(shù)據(jù)驗證單元格中的數(shù)據(jù)是0~100之間整數(shù)。2.1.2知識要點分析2.數(shù)據(jù)驗證單元格中的數(shù)據(jù)是0~100之間整數(shù)。2.1.2知識要點分析2.數(shù)據(jù)驗證單元格中的數(shù)據(jù)是0~100之間整數(shù)。2.1.2知識要點分析2.數(shù)據(jù)驗證固定序列值的輸入。序列源中的數(shù)據(jù)一定要用英文逗號分隔！2.1.2知識要點分析3.數(shù)據(jù)填充自動填充

根據(jù)已輸入的數(shù)據(jù)，在連續(xù)單元格中自動填充數(shù)據(jù)

自動填充數(shù)值序列：首先在起始的兩個單元格中分別輸入序列的前兩項數(shù)值，然后選中這兩個單元格，將鼠標(biāo)箭頭移至其右下角，當(dāng)指針變成黑色十字形時，按住左鍵向下方或右方拖曳到最后一個單元格。如果選中一個值拖動鼠標(biāo)結(jié)果會怎樣？2.1.2知識要點分析3.數(shù)據(jù)填充自動填充

等比數(shù)列無法像等差數(shù)列一樣以拉曳填充控點的方式來建立。例如，要建立2,4,8，……這樣的數(shù)列，我們可以：

在第一個單元格中輸入2，再選中需要生成序列的一個單元格區(qū)域。2.1.2知識要點分析3.數(shù)據(jù)填充自動填充

等比數(shù)列無法像等差數(shù)列一樣以拉曳填充控點的方式來建立。例如，要建立2,4,8，……這樣的數(shù)列，我們可以：單擊“開始”菜單下“編輯”組中的“填充”命令。2.1.2知識要點分析3.數(shù)據(jù)填充自動填充

自動填充相同的數(shù)據(jù)：選定單元格區(qū)域，輸入值，然后按Ctrl＋Enter鍵，即可實現(xiàn)在選定的單元格區(qū)域中一次性輸入相同的值。

2.1.2知識要點分析3.數(shù)據(jù)填充自動填充

自動填充含數(shù)值的文本序列：輸入第一項數(shù)據(jù)，拖曳后文本不變，但數(shù)值變化。2.1.2知識要點分析3.數(shù)據(jù)填充用戶自定義填充序列

Excel允許用戶自定義填充序列。例如，在填充序列中沒有二十四節(jié)氣，我們可以將其添加到序列中。選擇“文件-選項-高級-編輯自定義列表”命令，在“選項”對話框中選擇“自定義序列”選項卡。2.1.2知識要點分析3.數(shù)據(jù)填充用戶自定義填充序列2.1.2知識要點分析3.數(shù)據(jù)填充用戶自定義填充序列2.1.2知識要點分析3.數(shù)據(jù)填充用戶自定義填充序列2.1.2知識要點分析3.數(shù)據(jù)填充快速填充例如，有一批的數(shù)據(jù)，現(xiàn)在要求將“設(shè)備品名”欄中的中文信息提取出來，放在對應(yīng)行的“設(shè)備名”中。2.1.2知識要點分析3.數(shù)據(jù)填充快速填充

(1)在B2單元格輸入要提取的中文名字，回車后，再在B3單元格中輸入要提取的第一個漢字“路”，這時系統(tǒng)就會彈出感知填充的提示信息，直接按下Enter鍵，即可在相應(yīng)的單元格中提取到所要的中文信息。2.1.2知識要點分析3.數(shù)據(jù)填充快速填充如果“快速填充”未生成預(yù)覽，則可能未啟用。需啟用此功能，然后再使用它。（單擊“文件”|“選項”|“高級”）2.1.2知識要點分析3.數(shù)據(jù)填充快速填充(2)通過單擊“數(shù)據(jù)”|“快速填充”。2.1.3操作步驟1.制作表頭輸入表頭信息合并單元格，調(diào)整表頭信息的格式2.1.3操作步驟2.輸入設(shè)置可以自動產(chǎn)生固定的兩個值：“男，女”可以通過“數(shù)據(jù)驗證”控制！選中需要指定的單元格，打開“數(shù)據(jù)驗證”對話框…2.1.3操作步驟2.輸入設(shè)置2.1.3操作步驟2.輸入設(shè)置固定的一些值，可以通過“數(shù)據(jù)驗證”控制！2.1.3操作步驟2.輸入設(shè)置建立數(shù)據(jù)表的整體框架。選擇“籍貫”數(shù)據(jù)列。打開“數(shù)據(jù)驗證”對話框。2.1.3操作步驟2.輸入設(shè)置2.1.3操作步驟2.輸入設(shè)置固定的長度，可以通過“數(shù)據(jù)驗證”控制！固定的長度，可以通過“數(shù)據(jù)驗證”控制！固定的長度，可以通過“數(shù)據(jù)驗證”控制！2.2多級菜單2.2.1案例說明在前一節(jié)學(xué)生信息表中再添加兩個字段：學(xué)院、專業(yè)。如下圖所示。2.2.2知識要點分析1.單元格區(qū)域命名單元格區(qū)域命名就是給一個單元格區(qū)域起一個“名稱”。使用名稱的好處：使公式含義更容易理解。提高公式編輯的準(zhǔn)確性。快速定位到特定位置。名稱可以作為變量來使用?？梢苑奖愕貞?yīng)用于所有的工作表。2.2.2知識要點分析2.名稱的命名規(guī)則單元格區(qū)域命名就是給一個單元格區(qū)域起一個“名稱”。名稱的命名規(guī)則：名稱的第一個字符必須是字母或下劃線，不能使用單元格地址。名稱中的字符可以是字母、數(shù)字，不能含有空格，可以使用句點。名稱的長度不能超過255個字符。名稱中的字母不區(qū)分大小寫。命名時，不要使用Excel的內(nèi)部名稱。2.2.2知識要點分析2.名稱的命名規(guī)則如果我們希望將一個單元格區(qū)域命名，可以通過“公式”菜單下“定義的名稱”組中的“名稱管理器”命令打開“名稱管理器”對話框。2.2.2知識要點分析2.名稱的命名規(guī)則如果我們希望將一個單元格區(qū)域命名，可以通過“公式”菜單下“定義的名稱”組中的“名稱管理器”命令打開“名稱管理器”對話框。2.2.2知識要點分析2.名稱的命名規(guī)則我們也可以通過“名稱框”快速定義“名稱”。先要選擇“名稱”要引用的區(qū)域，然后在“名稱框”中輸入要定義的“名稱”。選擇“名稱”要引用的區(qū)域輸入名稱2.2.2知識要點分析2.名稱的命名規(guī)則Excel還提供了對帶有行列標(biāo)題的多個單元格區(qū)域定義名稱的方法：選中需要定義名稱的多個單元格區(qū)域，通過“公式”菜單下“定義的名稱”組中的“根據(jù)所選內(nèi)容創(chuàng)建”命令打開“以選定區(qū)域創(chuàng)建名稱”對話框。2.2.2知識要點分析3.名稱的使用我們可以通過輸入的方式在公式中使用定義的名稱，也可以通過<F3>鍵打開“粘貼名稱”對話框來使用定義的名稱。2.2.2知識要點分析4.多級菜單的下拉式輸入現(xiàn)有銷售產(chǎn)品的類別、名稱和型號，要實現(xiàn)多級關(guān)系的下拉式錄入。2.2.2知識要點分析4.多級菜單的下拉式輸入分別選擇不同的區(qū)域進行命名。例如，定義名稱“電器”，其引用的區(qū)域為:B2:B5。2.2.2知識要點分析4.多級菜單的下拉式輸入分別選擇不同的區(qū)域進行命名。定位B2:I17的“常量”區(qū)域。打開“根據(jù)所選內(nèi)容創(chuàng)建”對話框，在彈出的對話框中選擇“最左列”。完成單元格區(qū)域的命名。2.2.2知識要點分析4.多級菜單的下拉式輸入選擇C2:C16區(qū)域。鼠標(biāo)左鍵依次單擊“數(shù)據(jù)”|“數(shù)據(jù)工具”|“數(shù)據(jù)驗證”，在“允許”下選擇“序列”，在“來源”處輸入“=indirect(B2)”，即可實現(xiàn)類別名稱的選擇錄入。隨著B列所選內(nèi)容的不同，C列可選項也會隨之變化。2.2.2知識要點分析4.多級菜單的下拉式輸入我們通過單元格區(qū)域命名，并使用indirect函數(shù)實現(xiàn)了三級菜單的下拉式錄入。以此類推，更多級的下拉菜單可以很方便的實現(xiàn)！2.2.3操作步驟1.完善表格2.2.3操作步驟2.完善新加字段的多級輸入設(shè)置建立一張“學(xué)院及專業(yè)”工作表。2.2.3操作步驟2.完善新加字段的多級輸入設(shè)置按照前面的方法完成“學(xué)院”“專業(yè)”兩個字段的多級菜單輸入設(shè)置。再見！第3章公式與函數(shù)主要內(nèi)容3.1制作九九乘法表3.2學(xué)生成績表的處理3.3完善學(xué)生信息表3.4

100以內(nèi)的加減法測試3.5制作貨物出庫單3.6分析銷售數(shù)據(jù)3.7隨機生成考試座位號3.1制作九九乘法表1.問題描述

制作如圖所示九九乘法表。2.知識要點數(shù)據(jù)運算Excel的數(shù)據(jù)計算是通過公式實現(xiàn)的，它可以對工作表中的數(shù)據(jù)進行算術(shù)運算、比較運算和文本連接運算等。3.1制作九九乘法表算術(shù)運算符運算符意義舉例運算結(jié)果+加法運算2+57-減法運算取負(fù)數(shù)2-7-2-5-2*乘法運算2.1*1.53.15/除法運算13/52.6%百分百運算13%0.13^冪運算2^33^0.581.73205083.1制作九九乘法表比較運算符運算符意義舉例運算結(jié)果<

小于2<5"ab"<"abc"TrueTrue>

大于2>5"123">"99"FalseFalse<=小于等于2<=（5+3）/2"a"<="a"&"b"TrueTrue>=大于等于5-3>=2"abc">="abcd"TrueFalse=等于2=(4\2)"abc"="abc"TrueTrue<>

不等于2<>3^0.5"abc"<>"ABC"TrueTrue3.1制作九九乘法表字符串運算符

字符串的運算只有連接運算。其運算符是“&”。它表示把兩個字符串首尾連接成一個字符串。例如："學(xué)習(xí)"&"Excel"，其運算結(jié)果為“學(xué)習(xí)Excel”。3.1制作九九乘法表3.1制作九九乘法表單元格引用

單元格引用的作用在于標(biāo)識工作表中的單元格或單元格區(qū)域，并通過單元格引用來標(biāo)識公式中所使用的數(shù)據(jù)地址，這樣在創(chuàng)建公式時就可以直接通過單元格引用的方法來快速創(chuàng)建公式并完成計算，提高計算數(shù)據(jù)的效率。3.1制作九九乘法表相對引用。

相對引用是指單元格的公式中直接使用了其他單元格的地址。絕對引用。

絕對引用是指單元格公式中引用其他單元格地址的行或列前面添加了“$”符號。絕對引用無論是被移動還是被復(fù)制時，其引用的地址都不改變。3.1制作九九乘法表3.方案步驟1.制作表頭2.編寫3.使用字符函數(shù)3.2學(xué)生成績表的處理1.問題描述

計算如圖所示的表格中紅色框中的各個數(shù)據(jù)。2.知識要點

函數(shù)的使用

函數(shù)實際上是一些預(yù)定義的公式，運用一些稱為參數(shù)的特定的順序或結(jié)構(gòu)進行計算。Excel2016提供了財務(wù)、統(tǒng)計、邏輯、文本、日期與時間、查找與引用、數(shù)學(xué)和三角、工程等多類函數(shù)，可大大簡化公式的輸入過程，只需設(shè)置函數(shù)相應(yīng)的參數(shù)即可進行計算。3.2學(xué)生成績表的處理基本函數(shù)求和函數(shù)SUM()求平均值函數(shù)AVERAGE()最大值函數(shù)MAX()最小值函數(shù)MIN()計數(shù)函數(shù)COUNT（）條件計數(shù)函數(shù)COUNTIF()條件函數(shù)IF()3.2學(xué)生成績表的處理3.方案步驟完善表頭計算相關(guān)數(shù)據(jù)統(tǒng)計各類人數(shù)3.2學(xué)生成績表的處理1.問題描述

在原“學(xué)生信息表”的基礎(chǔ)上增加“身份證號”、“出生日期”和“計算機入學(xué)測試成績”等三個字段。如圖所示。3.3完善學(xué)生信息表同時還要求：

按“計算機入學(xué)測試成績”降序排列。

按“籍貫”分類統(tǒng)計人數(shù)。

只顯示“河南省”考生“入學(xué)測試成績”在60分以上的學(xué)生。2.知識要點字符函數(shù)LEFT（）函數(shù)。2.RIGHT（）函數(shù)3.MID（）函數(shù)4.AND（）函數(shù)5.OR（）函數(shù)6.VLOOKUP（）函數(shù)3.3完善學(xué)生信息表數(shù)據(jù)引入和分列1.數(shù)據(jù)引入。EXCEL2016提供了強大的“PowerQuery”,即數(shù)據(jù)查詢功能，能幫助我們不用費時費人力粘貼、不用多數(shù)人接受不了的VBA、不用專業(yè)的SQL查詢語句，只需要鼠標(biāo)點下去，就能完成多工作簿的數(shù)據(jù)匯總。3.3完善學(xué)生信息表2.分列

在Excel中有一個功能叫分列，它的功能主要體現(xiàn)在對數(shù)據(jù)的有效拆分。假如需要統(tǒng)計一下公司員工的年齡，人事部門在做員工檔案的時候只記錄了員工的身份證號，沒有記其出生日期，有沒有什么辦法可以快速統(tǒng)計出員工的出生日期？Excel提供了分列功能來快速的從身份證號碼里拆分出他們的出身日期3.3完善學(xué)生信息表數(shù)據(jù)排序1.單條件排序。

對工作表中的一列數(shù)據(jù)進行排序，可使用單條件排序功能。2.多條件排序。

工作表中多列的內(nèi)容進行排序，可使用多條件排序。3.3完善學(xué)生信息表分類匯總

分類匯總是Excel中最常用的功能之一，它能夠快速地以某一個字段為分類項，對數(shù)據(jù)列表中的數(shù)值字段進行各種統(tǒng)計和計算，如求和、計數(shù)、平均值、最大值、最小值、乘積等。

3.3完善學(xué)生信息表3.方案步驟完善表格完善數(shù)據(jù)按“成績”排序按“籍貫”分類統(tǒng)計人數(shù)篩選3.3完善學(xué)生信息表3.4

100以內(nèi)的加減法測試1.問題描述

設(shè)計一個如圖所示100以內(nèi)的加減法運算自動判分表。要求系統(tǒng)自動產(chǎn)生若干道算術(shù)題，由學(xué)生回答。系統(tǒng)要給出未答題數(shù)量并自動顯示統(tǒng)計出的正確與錯誤的成績。3.4

100以內(nèi)的加減法測試2.知識要點隨機函數(shù)

RAND()函數(shù)：返回一個大于等于0,小于1的均勻分布的隨機數(shù)。

RANDBETWEEN(M,N)

函數(shù)：返回一個[M,N]范圍內(nèi)均勻分布的隨機整數(shù)。3.4

100以內(nèi)的加減法測試2.“宏”

宏是一個指令集，是一系列的命令和函數(shù)，用來告訴Excel完成用戶指定的動作。宏類似于計算機程序，但它是完全運行于Excel之中的，可以使用“宏”來完成枯燥的、頻繁的重復(fù)性工作。且“宏”完成動作的速度比用戶自己要快很多。3.4

100以內(nèi)的加減法測試3.方案步驟制作表格出題自動評判重復(fù)出題3.5制作貨物出庫單1.問題描述

制作如圖所示的“出庫單”，要求：“貨號”字段由下拉框選擇輸入。當(dāng)“貨號”字段的值輸入完成后，“商品”、“單價”字段的值自動填充?！皵?shù)量”字段應(yīng)該是大于等于1的整數(shù)?！靶∮嫛弊侄蔚闹底詣犹畛洌瑫r“合計”的值不能超過999999.99。將“小計”字段各個位的值拆開，自動填充到“金額”項的相應(yīng)字段。3.5制作貨物出庫單2.知識要點1.單元格區(qū)域命名2.名稱的命名規(guī)則3.名稱的使用3.5制作貨物出庫單3.方案步驟

制作表格

單元格設(shè)置“貨號”、“商品”和“單價”字段的設(shè)置“數(shù)量”字段的設(shè)置“小計”字段的計算

“合計”字段的計算

“金額”的設(shè)置

3.6分析銷售數(shù)據(jù)1.問題描述

本案例是一個公司的銷售明細(xì)，通過常用統(tǒng)計函數(shù)的使用可對銷售數(shù)據(jù)進行分析，如平均銷售業(yè)績、銷售排名、銷售業(yè)績優(yōu)秀、銷售業(yè)績達標(biāo)、銷售業(yè)績段的人數(shù)等。如圖所示。3.6分析銷售數(shù)據(jù)2.方案步驟1.匯總銷售數(shù)據(jù)總排名

在I3單元格計算出每個員工的總銷售額

在J3單元格對數(shù)據(jù)進行降序排位

在K3單元格對數(shù)據(jù)進行百分比排位

在L3單元格設(shè)置判斷條件，

在P3單元格、P4單元格、P5單元格，求出每月的平均銷售額、每月的優(yōu)秀率、每月的達標(biāo)率。3.6分析銷售數(shù)據(jù)在P6單元格、在P7單元格、在P8單元格，求出每月第一名、第二名、第三名的銷售額。在P9單元格、P10單元格、P11單元格，求出每月倒數(shù)第一名、倒數(shù)第二名、倒數(shù)第三名的銷售額。在P12單元格求出每月的中值數(shù)，即銷售額居中的數(shù)據(jù)。在P13單元格求出銷售額出現(xiàn)頻率最多的數(shù)據(jù)2.對每個部門的情況進行統(tǒng)計分析制作相應(yīng)的表頭。在P16單元格向下填充計算出每個部門的人數(shù)。在Q16單元格計算出每月每個部門的平均銷售額在W16單元格求出每個部門上半年的平均銷售額3.6分析銷售數(shù)據(jù)3.統(tǒng)計銷售段人數(shù)在Q22單元格統(tǒng)計出一月份到六月份6萬以下的人數(shù)在Q23單元格統(tǒng)計出一月份到六月份6到7萬以下的人數(shù)。在Q24單元格統(tǒng)計出一月份到六月份7到8萬以下的人數(shù)。在Q25單元格統(tǒng)計出一月份到六月份8到9萬以下的人數(shù)。在Q26單元格統(tǒng)計出一月份到六月份9萬以上的人數(shù)。3.7隨機生成考試座位號

在各種考試中，考生需要對照自己的準(zhǔn)考證號，對號入座，這時需要為考生分配考試座位號。如圖所示為學(xué)生的原始記錄，共有379條記錄。3.7隨機生成考試座位號1.問題描述本例中，我們先用隨機函數(shù)生成0到1之間的隨機數(shù)，再利用RANK函數(shù)生成隨機名次，然后用公式為每個考場隨機分配50名考生。3.7隨機生成考試座位號2.知識點講解INDEX(

)函數(shù)返回表格或區(qū)域中的值或值的引用。函數(shù)INDEX()有兩種形式，數(shù)組形式和引用形式。數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組；引用形式通常返回引用。3.7隨機生成考試座位號3.方案步驟在C1和D1單元格分別輸入“隨機”和“隨機名次”。在C2單元格輸入公式“=RAND()”并向下填充到C380單元格。在D2單元格輸入公式“=RANK(C2,C$2:C$380)”并向下填充到D380單元格。如圖3-100所示。3.7隨機生成考試座位號4.在F1單元格輸入“考號”，在G1：N1單元格分別輸入第一考場到第

八

考場（共379考生，需8個考場）。5.在F2，F(xiàn)3單元格輸入1和2，選擇這兩個單元格后向下填充到50（假設(shè)

每場50個考生）。6.在G2單元格輸入公式并向下向右填充到N51單元格。完成考生座位號分配。如圖所示。G2單元格輸入的公式含義是在G2單元格同時顯示學(xué)生的“姓名”和“學(xué)號”。3.7隨機生成考試座位號再見！第4章數(shù)據(jù)分析基礎(chǔ)主要內(nèi)容4.1數(shù)據(jù)篩選4.2使用條件格式標(biāo)識數(shù)據(jù)4.3數(shù)據(jù)透視表4.4合并運算4.5模擬分析

篩選數(shù)據(jù)列表的意思就是將不符合用戶特定條件的行隱藏起來，這樣可以更方便的讓用戶對數(shù)據(jù)進行查看。Excel提供了兩種篩選數(shù)據(jù)列表的命令。4.1

數(shù)據(jù)篩選自動篩選“自動篩選”一般用于簡單的條件篩選，篩選時將不滿足條件的數(shù)據(jù)暫時隱藏起來，只顯示符合條件的數(shù)據(jù)。高級篩選

如果條件比較多，可以使用“高級篩選”來進行。使用高級篩選功能可以一次把我們想要看到的數(shù)據(jù)都找出來。4.1

數(shù)據(jù)篩選

條件格式，可以根據(jù)單元格內(nèi)容對單元格應(yīng)用條件格式，從而使單元格的外觀與眾不同。條件格式是用于可視化數(shù)值型數(shù)據(jù)的有用工具。使用Excel中的條件格式功能，可以預(yù)置一種單元格格式，并在指定的某種條件被滿足時自動應(yīng)用于目標(biāo)單元格?？梢灶A(yù)置的單元格格式包括邊框、底紋、字體顏色等。此功能可以根據(jù)用戶的要求，快速對特定單元格進行必要的標(biāo)識，以起到突出顯示的作用。4.2使用條件格式標(biāo)識數(shù)據(jù)1.條件格式簡介

條件格式功能允許以單元格的內(nèi)容為基礎(chǔ)，選擇性地或自動地應(yīng)用單元格格式。

條件格式可以方便地快速識別錯誤的單元格或特定類型的單元格。

可以使用某種格式來輕松地標(biāo)識特定的單元格。要對單元格或區(qū)域應(yīng)用條件格式規(guī)則，可首先選定單元格，然后使用“開始”|“樣式”|“條件格式”下拉列表中的其中一個命令來指定某個規(guī)則。4.2使用條件格式標(biāo)識數(shù)據(jù)2.使用圖形的條件格式Excel中用于顯示圖形的三個條件格式選項：數(shù)據(jù)條，色階和圖標(biāo)集。這些條件格式類型，有助于更好的可視化區(qū)域內(nèi)的數(shù)值。1、數(shù)據(jù)條

數(shù)據(jù)條條件格式可直接在單元格中顯示水平條。水平條的長度取決于單元格中的值與該區(qū)域內(nèi)其他單元格的值的相對比例。4.2使用條件格式標(biāo)識數(shù)據(jù)2、色階

色階可以讓表格數(shù)據(jù)更直觀。色階是指在一個單元格區(qū)域中顯示雙色漸變或三色漸變，顏色的底紋表示單元格中的值，并且漸變顏色能夠隨數(shù)據(jù)值的大小而改變。3、圖標(biāo)集

另一個條件格式選項是在單元格中顯示圖標(biāo)。所顯示的圖標(biāo)取決于單元格的值。4.2使用條件格式標(biāo)識數(shù)據(jù)3.創(chuàng)建基于公式的規(guī)則Excel的條件格式功能非常強大，但有時它可能也無法完成所需操作。幸運的是，可以通過編寫條件格式公式來擴展它的功能。

要指定基于公式的條件格式，首先選擇單元格，然后選擇“開始”|“樣式”|“條件格式”|“新建規(guī)則”。這時將顯示“新建格式規(guī)則”對話框。在該對話框中單擊“使用公式確定要設(shè)置格式的單元格”規(guī)則類型，然后即可指定公式。4.2使用條件格式標(biāo)識數(shù)據(jù)4.使用條件格式管理規(guī)則復(fù)制含有條件格式的單元格刪除條件格式定位含有條件格式的單元格4.2使用條件格式標(biāo)識數(shù)據(jù)

數(shù)據(jù)透視表可能是Excel中技術(shù)最復(fù)雜的組件，然而只需要單擊幾下鼠標(biāo)，就能以數(shù)十種不同的方式切分?jǐn)?shù)據(jù)表，并得出你希望得到的任何匯總類型。1.認(rèn)識數(shù)據(jù)透視表

數(shù)據(jù)透視表在本質(zhì)上是一個從數(shù)據(jù)庫生成的動態(tài)匯總報表。這里所指的數(shù)據(jù)庫既可以位于一個工作表中，也可以位于外部數(shù)據(jù)文件中。在創(chuàng)建數(shù)據(jù)透視表后，既可以按照任何想到的方式重新排列信息，也可以插入特殊的公式以執(zhí)行各種新的計算。4.3數(shù)據(jù)透視表

創(chuàng)建數(shù)據(jù)透視表時，通常需要匯總一個或多個數(shù)據(jù)字段。相反的，類別字段的值將會在數(shù)據(jù)透視表中顯示為行、列或篩選項。

字段：從源列表或數(shù)據(jù)庫中的字段衍生出的數(shù)據(jù)分類。

組：一組被視為單個項的項。

項：字段中的元素，在數(shù)據(jù)透視表中作為行或列的標(biāo)題顯示。

匯總函數(shù)：用來對數(shù)據(jù)字段中的值進行合并的計算類型。4.3數(shù)據(jù)透視表行標(biāo)簽：在數(shù)據(jù)透視表中擁有行方向的字段。列標(biāo)簽：數(shù)據(jù)透視表中具有列方向的字段。表篩選：數(shù)據(jù)透視表中具有分頁方向的字段?！茢?shù)值：數(shù)值字段提供要匯總的數(shù)據(jù)值?？?/p>

計：用于顯示數(shù)據(jù)透視表中一行或一列中所有單元格的總和的

行或列。4.3數(shù)據(jù)透視表2.創(chuàng)建數(shù)據(jù)透視表

創(chuàng)建數(shù)據(jù)透視表的操作比較復(fù)雜，為了便于用戶使用，Excel提供了數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?，在該向?qū)У闹笇?dǎo)下，用戶只要按部就班的進行操作，就可以輕松的完成數(shù)據(jù)透視表的制作過程。4.3數(shù)據(jù)透視表指定數(shù)據(jù)指定數(shù)據(jù)透視表的放置位置。指定數(shù)據(jù)透視表布局。指定數(shù)據(jù)透視表布局。選擇或填入“自動”選項卡中的“起始”和“終止”日期，4.3數(shù)據(jù)透視表3.更改值匯總方式

對數(shù)據(jù)透視表中的數(shù)據(jù)匯總時最常使用求和方法。但是，也可以使用“值字段設(shè)置”對話框中指定的其他許多不同匯總方法來顯示數(shù)據(jù)。要顯示此對話框的方法是，右擊數(shù)據(jù)透視表中的任何值，然后從快捷菜單中選擇“值字段設(shè)置”。4.3數(shù)據(jù)透視表4.更改值顯示方式

在創(chuàng)建數(shù)據(jù)透視表后，用戶可以根據(jù)需要設(shè)置值的不同顯示方式，以方便對數(shù)據(jù)進行分析。要以不同的形式顯示數(shù)值，可使用“值顯示方式”選項卡上的下拉控件。有很多選項可供選擇，其中包括作為統(tǒng)計或分類匯總的百分比。4.3數(shù)據(jù)透視表5.切片器

利用切片器可以進行快速篩選，還可以指定當(dāng)前篩選狀態(tài)，從而輕松、準(zhǔn)確地了解篩選的數(shù)據(jù)。切片器提供可用于單擊篩選表格數(shù)據(jù)或數(shù)據(jù)透視表數(shù)據(jù)的按鈕。除快速篩選外，切片器還可以指示當(dāng)前篩選狀態(tài)，以便了解篩選后的數(shù)據(jù)透視表中顯示哪些內(nèi)容。4.3數(shù)據(jù)透視表6.數(shù)據(jù)透視表顯示報表篩選頁

利用數(shù)據(jù)透視表的“顯示報表篩選頁”功能，就可以創(chuàng)建一系列鏈接在一起的數(shù)據(jù)透視表，每一張工作表顯示報表篩選字段中的一項。4.3數(shù)據(jù)透視表Excel中的“合并計算”功能可以匯總或合并多個數(shù)據(jù)源區(qū)域中的數(shù)據(jù)。合并計算的數(shù)據(jù)源區(qū)域可以是同一個工作表中的不同表格，也可以是同一工作簿中的不同工作表，還可以是不同工作表中的表格。1.按類別合并

如圖所示有兩張結(jié)構(gòu)相同的數(shù)據(jù)表，利用合并計算可以輕松地將這兩張表進行合并匯總。4.4合并運算2.創(chuàng)建分戶報表

合并計算可以按類別進行合并，如果引用區(qū)域的行、列方向均包含了多個類別時，則可以利用合并計算功能將引用區(qū)域中的全部類別匯總到同一表格上并顯示所有明細(xì)。4.4合并運算3.有選擇地合并計算如圖所示列出了南京天水加油站的3個月的信息，如果要匯總0號柴油的“銷售數(shù)量”和“銷售金額”，可以用“合并計算”的功能。盡管在“銷售數(shù)量”和“銷售金額”兩列之間還包含其他文本型數(shù)據(jù)列，“合并計算”仍然可以有選擇的進行計算。4.4合并運算

“模擬運算表”實際上是對工作表中的一個單元格區(qū)域進行模擬運算，它可以反映一個計算公式中某些數(shù)值的變化對計算結(jié)果的影響。模擬運算表為同時求解某一運算中所有可能的變化值提供了捷徑，由于它可以將不同的計算結(jié)果以列表的方式同時顯示出來，因而便于查看、比較和分析數(shù)據(jù)。4.5

模擬分析Excel中模擬運算表有兩種形式，即“單變量模擬運算表”和“雙變量模擬運算表”。

“單變量模擬運算表”：主要用來分析當(dāng)其他因素不變時，一個參數(shù)的變化對目標(biāo)值的影響。例如要計算一筆貸款的分期付款額，可用財務(wù)函數(shù)PMT來計算，而要分析不同利率對每月貸款的影響時，則需要用單變量模擬運算。4.5

模擬分析

“雙變量模擬運算表”：用來分析當(dāng)其他因素不變時，兩個參數(shù)的變化對目標(biāo)值的影響。雙變量模擬運算表中的兩個參數(shù)使用同一個公式，這個公式必須引用兩個不同的單元格。4.5

模擬分析再見！第5章規(guī)劃求解主要內(nèi)容5.1規(guī)劃求解基礎(chǔ)5.2求解取料5.3求解任務(wù)分配5.1規(guī)劃求解基礎(chǔ)規(guī)劃求解是MicrosoftExcel加載項程序，可用于模擬分析。使用“規(guī)劃求解”查找一個單元格（稱為目標(biāo)單元格）中公式的優(yōu)化（最大或最?。┲?，受限或受制于工作表上其他公式單元格的值?！耙?guī)劃求解”與一組用于計算目標(biāo)和約束單元格中公式的單元格（稱為決策變量或變量單元格）一起工作。“規(guī)劃求解”調(diào)整決策變量單元格中的值以滿足約束單元格上的限制，并產(chǎn)生對目標(biāo)單元格期望的結(jié)果。5.1規(guī)劃求解基礎(chǔ)規(guī)劃求解的特點：

有多個可以調(diào)整的單元格?？梢酝ㄟ^更改其他單元格來確定某個單元格的最大值或最小值。可以指定可調(diào)整單元格可能的數(shù)值約束。一個問題可以有多個解。5.1規(guī)劃求解基礎(chǔ)1.規(guī)劃求解問題的特點

規(guī)劃求解是一組命令的組成部分，這些命令有時也稱作假設(shè)分析工具，即該過程通過更改單元格中的值來查看這些更改對工作表中公式結(jié)果的影響，規(guī)劃求解主要是為工作表中的目標(biāo)單元格中的公式找到一個優(yōu)化值，在保證工作表中的其他數(shù)據(jù)保持在設(shè)置的范圍之內(nèi)時通過改變輸入值從而求出最優(yōu)解。5.1規(guī)劃求解基礎(chǔ)規(guī)劃問題的特點一般來講，適合使用規(guī)劃求解的問題具有如下特點：目標(biāo)單元格的解都有單一的目標(biāo)，如求運輸?shù)淖罴崖肪€，求值班人員的最佳安排時間表，求產(chǎn)品的最低成本等等，要求求目標(biāo)函數(shù)的最優(yōu)解。對于目標(biāo)單元格的解存在有明確的可以用不等式表達的約束條件和限制。3.可以把問題的表達描述為：一組約束條件及限制（不等式），一個目標(biāo)方程。4.輸入值直接或間接地影響約束條件和目標(biāo)單元格的解。5.利用Excel可以簡單的求得問題滿足約束條件和限制求得的目標(biāo)最優(yōu)解。5.1規(guī)劃求解基礎(chǔ)2.規(guī)劃求解問題的組成部分

在Excel中，一個規(guī)劃求解問題由以下三個部分組成?？勺儐卧瘢嚎勺儐卧袷菍嶋H問題中有待解決的未知因素。目標(biāo)函數(shù)：目標(biāo)函數(shù)表示規(guī)劃求解要達到的最終目標(biāo)，如求最大利潤、最短路徑、最小成本、最佳產(chǎn)品搭配等。約束條件：約束條件是實現(xiàn)目標(biāo)的限制條件，規(guī)劃求解是否有解與約束條件有密切的關(guān)系，它對可變單元格中的值起著直接的限制作用。5.1規(guī)劃求解基礎(chǔ)3.規(guī)劃求解問題的步驟使用數(shù)值與公式建立工作表。確保單元格格式符合邏輯性，例如，如果不能生產(chǎn)半個產(chǎn)品，則需要將這些單元格格式設(shè)置為不能含有小數(shù)值。選擇“數(shù)據(jù)”|“分析”|“規(guī)劃求解”命令，將顯示“規(guī)劃求解參數(shù)”對話框。5.1規(guī)劃求解基礎(chǔ)4.指定目標(biāo)單元格5.指定含有可變單元格的區(qū)域6.指定約束條件7.根據(jù)需要更改規(guī)劃求解選項8.單擊“求解”按鈕，使用規(guī)劃求解解決問題。5.1規(guī)劃求解基礎(chǔ)4.在Excel中調(diào)用規(guī)劃求解工具

“規(guī)劃求解”工具是Excel的加載宏，在默認(rèn)安裝的MicrosoftExcel2016中需要加載后才能使用，加載該工具的步驟為：

單擊“文件”選項卡下的“選項”，在彈出的“Excel選項”對話框中單擊左側(cè)列表中的“加載項”選項卡，然后在右下方“管理”組合框中選擇“Excel加載項”，并單擊“轉(zhuǎn)到”按鈕，如圖所示。5.1規(guī)劃求解基礎(chǔ)

在彈出的“加載宏”的對話框中勾選“規(guī)劃求解加載項”復(fù)選框，并單擊“確定”按鈕完成操作，如圖所示。在Excel主界面點擊“數(shù)據(jù)”選項卡，在“分析”選項組中，顯示“規(guī)劃求解”，如圖所示。5.2求解取料問題1.問題描述

某公司將4種不同含硫量的液體原料（分別記為甲、乙、丙、?。┗旌仙a(chǎn)兩種產(chǎn)品（分別記為A和B），按生產(chǎn)工藝的要求，原料甲、乙、丁必須首先倒入池中混合（也可一直用到一種原料），混合后的液體再與原料丙混合生成A和B。已知甲、乙、丙、丁的含硫量分別是3%，1%，2%和1%，進貨價格分別為7、16、10、15（千元/噸）；產(chǎn)品A和B的含硫量分別不能超過2.5%和1.5%，售價分別為9和15（千元/噸）。根據(jù)市場信息，甲、乙、丙的供應(yīng)沒有限制，原料丁的供應(yīng)量最多為50噸，產(chǎn)品A和B的市場需求分別為100噸和200噸。應(yīng)如何安排生產(chǎn)產(chǎn)品A和產(chǎn)品B的數(shù)量，使得在最大程度降低總成本同時計算丁原料的實際需求？5.2求解取料問題2.知識要點SUMPRODUCT()函數(shù)

功能：用來在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和。5.2求解取料問題3.制作步驟在D9和D10單元格分別輸入公式：“D9=SUM(D2:D5)”和“D10=SUM(E2:E5)”。在E9和E10單元格分別輸入公式：“E9=SUMPRODUCT(D2:D5,B2:B5)/B9”和“E10=SUMPRODUCT(B2:B5,E2:E5)/B10”。5.2求解取料問題4.B12單元格實際丁原料的使用量，輸入公式：

“B12=SUM(D5:E5)”。5.B13單元格為總成本，設(shè)定為目標(biāo)單元格輸入公式：“B13=SUMPRODUCT(C2:C5,D2:D5+E2:E5)”5.2求解取料問題6.選中B13單元格，打開“規(guī)劃求解參數(shù)”對話框，在“設(shè)置目標(biāo)單元格”文本框中選擇B13單元格，選中“最小值”單選按鈕，在可變單元格文本框中選擇D2:E5單元格區(qū)域。5.2求解取料問題7.單擊“添加”按鈕，添加約束條件：D2:E5>=0，產(chǎn)量不能為負(fù)數(shù)D9:D10=B9:B10，實際產(chǎn)量與需求一直E9:E10<=C9:C10,生產(chǎn)出來的成品的含硫量不能超過上限B12<=50，丁原料的供應(yīng)是有限定的?！斑x擇求解方法”對應(yīng)的文本框選擇單線性規(guī)劃5.2求解取料問題8.單擊“求解”按鈕開始運算，并顯示運算結(jié)果，如圖所示。然后單擊“確定”按鈕保存此結(jié)果，結(jié)果如圖所示。5.3求解任務(wù)分配1.問題描述

某醫(yī)院新建一個病房，需要配備護士，周一到周日分別最少需要16、12、12、15、14、16、14人，按規(guī)定一個護士一周要連續(xù)上班5天，醫(yī)院病房給配備了20名護士，是否夠用？5.3求解任務(wù)分配2.知識要點

護士是每周上班5天，如果周一是第一天上班，則要連續(xù)上到周五，如果周四第一天上班，則上星期四、五、六、日、一，依次類推。例如計算星期一護士上班人數(shù)，除了計算星期一安排分配上班護士人數(shù)，還需要統(tǒng)計從星期四到星期日分配上班護士的人數(shù)。5.3求解任務(wù)分配3.制作步驟

根據(jù)已知條件建立關(guān)系表格，在B1單元格輸入星期一，向右填充到H1單元格，建立一周。護士編號為護士1到護士20，A2單元格輸入護士1，向下填充到A21，建立20格護士的名單，并錄入每天需要的護士數(shù)，如圖所示5.3求解任務(wù)分配1.B2：H21單元格區(qū)域用于記錄護士實際上班情況，用0表示未分配，1表示分配任務(wù)，且是該護士本周第一次上班的時間，此區(qū)域作為規(guī)劃求解的可變單元格。2.J列用于統(tǒng)計護士是否有上班，根據(jù)任務(wù)分配問題的特性，每位護士最多只能上班5天，J2單元格公式填充至J21單元格。J22=SUM(B2:H2)。5.3求解任務(wù)分配3.第22行用于計算每天上班的人數(shù)，B22公式為“=SUM($B$2:B21,E2:$H$21)”向右填充到E22單元格。F22的公式為“=SUM(B2:F21)”。向右填充到H22單元格。J22單元格公式為“=SUM(B2:F21)”。5.3求解任務(wù)分配4.

選中J22單元格，打開“規(guī)劃求解對話框”，在“設(shè)置目標(biāo)單元格”文本框中選擇J22，選中最小值，設(shè)定“可變單元格”為B2：H21。添加約束條件如圖5-16所示。選中“使無約束變量為非負(fù)數(shù)”。選擇求解方法為“單純線性規(guī)劃”。5.3求解任務(wù)分配5.單擊“求解”，顯示如圖所示，單擊“繼續(xù)”按鈕。5.3求解任務(wù)分配6.顯示如圖所示，單擊“確定”按鈕。顯示如圖所示，給出求解結(jié)果。再見！第6章數(shù)據(jù)可視化6.1Excel-圖表基礎(chǔ)Excel-圖表基礎(chǔ)借助圖表的方式進行數(shù)據(jù)的可視化其根本目的是：使得人們對信息的認(rèn)知更加直觀化和圖形化。人類對“形象化”信息的接受能力要遠(yuǎn)高于“原始數(shù)據(jù)”。計算機恰恰相反“原始數(shù)據(jù)”-計算機-“形象化”-人可視化的意義Excel-圖表基礎(chǔ)分類軸表示自變量，一般為X軸；數(shù)據(jù)軸表示因變量，一般設(shè)為Y軸。在一些復(fù)雜情況下，可以使用多個數(shù)據(jù)軸分類軸和數(shù)據(jù)軸Excel-圖表基礎(chǔ)當(dāng)前Excel共提供多種內(nèi)置的圖表類型。常見的圖表類型Excel-圖表基礎(chǔ)在計算機的世界中，幾乎左右的功能性應(yīng)用軟件其基本操作都是類似的：選定要操作的對象-選定單元格區(qū)域。確定要施加在操作對象的操作-選定圖表類型給出上述操作的其他必須信息-指定一些項目。創(chuàng)建圖表的基本步驟6.2Excel-柱形圖Excel-柱形圖可視化問題Excel-柱形圖統(tǒng)計五級成績的人數(shù)，涉及到了判斷-COUNTIF函數(shù)需要根據(jù)結(jié)果數(shù)據(jù)插入柱形圖要點與思路6.3Excel-股價圖Excel-股價圖可視化問題Excel-股價圖股價圖包含四種子類型盤高-盤低-收盤圖開盤-盤高-盤低-收盤圖成交量-盤高-盤低-收盤圖成交量-開盤-盤高-盤低-收盤圖注意工作表中數(shù)據(jù)要根據(jù)上述順序安排要點與思路6.4Excel-條形圖對比選票Excel-條形圖對比選票可視化問題Excel-條形圖對比選票條形圖又稱為橫向柱形圖，他相對于柱形圖有其獨特優(yōu)勢條形圖由于是橫向的，當(dāng)圖的分類較多，而且分類字段名稱又較長時，比較適合分類軸是垂直軸，有更多空間顯示分類名稱。要點與思路6.5Excel-背景餅圖Excel-背景餅圖可視化問題Excel-背景餅圖圓餅圖是用扇形面積，也就是圓心角的度數(shù)表示數(shù)量。圓餅圖主要用來表示整體與局部之間的數(shù)量關(guān)系，即分組數(shù)據(jù)的內(nèi)部構(gòu)成比例一般而言，餅圖僅有一個要繪制的數(shù)據(jù)系列，且要繪制的數(shù)值沒有負(fù)值或零值。要點與思路6.6Excel-自動縮放列表Excel-自動縮放列表可視化問題Excel-自動縮放列表先創(chuàng)建一個標(biāo)準(zhǔn)圖表然后進行一些調(diào)整，使得該圖表在添加新的數(shù)據(jù)時可以自動擴大，刪除數(shù)據(jù)時自動縮小。要點與思路6.7Excel-自動標(biāo)識圖表中的最大值和最小值Excel-自動標(biāo)識圖表中的最大值和最小值可視化問題Excel-自動標(biāo)識圖表中的最大值和最小值動態(tài)的標(biāo)識出最大值和最小值，會使圖表顯得更直觀、更形象。動態(tài)標(biāo)識依賴于對最大、最小值的自動計算要點與思路第7章

VBA應(yīng)用主要內(nèi)容7.1VBA基礎(chǔ)7.2庫存查詢7.3報名結(jié)果統(tǒng)計7.1VBA基礎(chǔ)7.1.1VBA的開發(fā)環(huán)境VisualBasic編輯器VBE（VisualBasicEditor）提供了完整的開發(fā)和調(diào)試VBA代碼的環(huán)境。7.1.1VBA的開發(fā)環(huán)境VBA是VisualBasicforApplications的簡稱。其意思是開發(fā)環(huán)境被整合到了某個應(yīng)用程序的VisualBasic語言。

VBA就是一種工具，一種可以創(chuàng)造工具的工具。

VBA提供了給你充分的自由，做幾乎任何其他編程語言或者環(huán)境能做的事情，避免重復(fù)的手動勞動。

VBA使用的是VB6的語法，采用的是COM組件庫?？梢苑奖隳懿僮鱋ffice的各種功能。7.1.1VBA的開發(fā)環(huán)境VBA能干什么？如果你想避免重復(fù)勞動，或者需要多個文檔協(xié)作，VBA是最好的選擇。理論上VBA可以操作任何WindowsAPI（WindowsApplicationProgrammingInterface，Windows應(yīng)用程序編程接口），和任意用COM封裝的組件。它們可以使自身的功能和辦公緊密結(jié)合。7.1.1VBA的開發(fā)環(huán)境VBA不能干什么？

VBA的功能取決于其庫。如果沒有庫那么VBA也不能幫你。此外，如果你開發(fā)的項目過于龐大，則應(yīng)該換一門更專業(yè)的編程平臺（比如.Net、Python等等），以便更好的組織代碼和查錯。

VBA比較適合短小的，與工作任務(wù)很直接的問題。7.1.1VBA的開發(fā)環(huán)境可以使用3種方法在Excel中打開VBE窗口。方法一：右擊工作表標(biāo)簽，在彈出的快捷菜單中選擇“查看代碼”項。方法二：單擊“開發(fā)工具”選項卡“代碼”選項組中的“VisualBasic”按鈕。方法三：在工作表中使用【Alt+F11】組合鍵。1.打開VBE編輯窗口7.1.1VBA的開發(fā)環(huán)境2.VBE窗口的組成菜單欄工具欄工程資源管理器屬性窗口屬性窗口7.1.1VBA的開發(fā)環(huán)境3.在VBE中編寫代碼在VBE中編寫代碼實際上就是寫一個VBA子過程，子過程名就是宏名。子過程定義格式為：Sub子過程名（[<形參列表>]）

[<語句組>]EndSub7.1.1VBA的開發(fā)環(huán)境3.在VBE中編寫代碼通過“開發(fā)工具”|“代碼”|“VisualBasic”或者打開“宏”的編輯窗口。也可以打開一個新的窗口。7.1.2Sub和Function“Sub”這個關(guān)鍵字標(biāo)志著一個程序過程的開始，“EndSub”則表示其結(jié)束。例如：SubTest()MsgBox"Hello,World"EndSub7.1.2Sub和FunctionFunction被叫做自定義函數(shù)，是我們自己寫的一個函數(shù)程序。它和Sub除了關(guān)鍵字的差別外，F(xiàn)unction與Sub最大的不同就在于Function需要返回一個值，而Sub不需要。例如：FunctionWeekDayName(WAsInteger)AsStringSelectCaseWCase1WeekDayName="Monday"Case2WeekDayName="Tuesday"Case3WeekDayName="Wednesday"Case4WeekDayName="Thursday"Case5WeekDayName="Friday"Case6WeekDayName="Saturday"Case7WeekDayName="Sunday"EndSelectEndFunction7.1.3變量與類型常量①字面常量：實際值數(shù)值常量：1563.141.25E10字符串常量："學(xué)生成績管理系統(tǒng)""Office"布爾常量：TureFalse7.1.3變量與類型常量②符號常量：用標(biāo)識符表示常量ConstPIAsSingle=3.14159程序執(zhí)行語句s=r*r*PI時，自動將PI用3.14159替換。③系統(tǒng)常量：系統(tǒng)預(yù)先定義的常量vbOK代表確定vbCancel代表取消。7.1.3變量與類型變量變量是用來存放程序運行過程中用到的各種中間數(shù)據(jù)。它是內(nèi)存中存儲單元的符號地址，是內(nèi)存中一個命名的存儲單元。在整個程序的執(zhí)行過程中，變量的值是可以變化的，也就是說存儲單元中存放的信息是可以改變的。但在程序執(zhí)行的每個瞬間，變量的值都是明確的、已知的。變量是指在程序運行期間取值可以變化的量。一個變量有3個基本要素：變量名、變量的數(shù)據(jù)類型和變量值。7.1.3變量與類型變量①變量的命名規(guī)則變量名必須以英文字母或漢字為起始字符。變量名可以包含字母、漢字、數(shù)字或下劃線，但不能包含空格和標(biāo)點符號。變量名的長度不能超過255個字符，不區(qū)分大小寫。變量名不能使用VBA的關(guān)鍵字。7.1.3變量與類型變量②變量的聲明顯式變量聲明Dim變量名[As類型名][,變量名[As類型名],……]例如DimscoreAsIntegerDimaverAsSingle,strAsString,flagAsBoolean,w7.1.3變量與類型變量②變量的聲明隱式變量聲明沒有顯式聲明的變量（隱式變量）的數(shù)據(jù)類型是變體型。7.1.3變量與類型數(shù)據(jù)類型2^15-12^31-13.402823*10^387.1.3變量與類型數(shù)組數(shù)組是由一組具有相同數(shù)據(jù)類型的變量組成的集合。顯式聲明一維數(shù)組。Dim數(shù)組名([下標(biāo)下界To]下標(biāo)上界)[As數(shù)據(jù)類型]說明：下標(biāo)下界缺省值為0，如果非0，要使用To選項。Dima(5)AsInteger整型數(shù)組a，包含6個元素，a(0)、a(1)、a(2)、a(3)、a(4)和a(5)，下標(biāo)為0~5Dimb(1To5)AsSingle單精度數(shù)組b，包含5個元素，b(1)、b(2)、b(3)、b(4)和b(5)，下標(biāo)為1~5。7.1.3變量與類型運算符--算術(shù)運算符7.1.3變量與類型運算符--關(guān)系運算符7.1.3變量與類型運算符--邏輯運算符7.1.3變量與類型運算符--字符串運算符

＋：要求兩個運算符的類型一致；&：允許兩個運算符的類型不一致，但其結(jié)果一定是字符型數(shù)據(jù)。7.1.3變量與類型運算符--字符串運算符“&”強制兩個表達式作為字符串連接。

"中國"&"北京" '結(jié)果為"中國北京""123"&"456" '結(jié)果為"123456"123&456 '結(jié)果為"123456““+”運算符用來連接兩個字符串（注意：加法）"123"+"456" '兩個字符串連接，結(jié)果為"123456"123+456 '兩個數(shù)相加，結(jié)果為579例如：7.1.4對象面向?qū)ο蠓椒ㄖ械膶ο笥梢唤M屬性和一組行為構(gòu)成，是系統(tǒng)中用來描述客觀事物的一個實體，它是用來構(gòu)成系統(tǒng)的一個基本單位。對象的幾個特性：方法：表示一種動作。函數(shù)：表示一種具有返回值的功能。屬性：表示對象所帶有的某種信息。7.1.5COM組件COM是ComponentObjectModel的簡稱，在Office中使用的對象模型是COM。你可以把軟件看作是一個機器，而組件就是機器的一個個零件。組件之間相互協(xié)作，共同完成任務(wù)。7.1.6幾個完整的VBA程序例7.1對一個單元格區(qū)域內(nèi)內(nèi)紅色的數(shù)字求和。7.1.6幾個完整的VBA程序例7.1對一個單元格區(qū)域內(nèi)內(nèi)紅色的數(shù)字求和。需明確以下幾個方面的問題：（1）如何表示一個單元格范圍，并以此作為問題的輸入，即到底要對哪些單元格應(yīng)用這個自定義的求和。（2）如何遍歷一個個的單元格。（3）如何獲取一個單元格的值。（4）如何判斷一個單元格的文字是紅色的。（5）如何求和。（6）如何輸出。7.1.6幾個完整的VBA程序例7.1對一個單元格區(qū)域內(nèi)內(nèi)紅色的數(shù)字求和。（2）如何遍歷一個個的單元格。關(guān)于遍歷一個像Selection這樣的集合，VBA提供了一個很方便的語法，叫作ForEach…Next。其語法為：ForEach<obj>In<Collection>'DoSomethingwith<obj>Next7.1.6幾個完整的VBA程序例7.1對一個單元格區(qū)域內(nèi)內(nèi)紅色的數(shù)字求和。（4）如何判斷一個單元格的文字是紅色的。在VBA中，對象r{Range}有一個Font{Font}屬性，表示字體的所有信息，例如邊框、加粗、斜體等，當(dāng)然，還有文字顏色Color{Double}。顏色是由RGB組成的，紅色對應(yīng)的是R=255，G=0，B=0。在VBA中寫作RGB(255,0,0)。也就是說如果r.Font.Color=RGB(255,0,0)為真，那就說明當(dāng)前單元格中的數(shù)據(jù)就是紅色的。當(dāng)然VBA還提供了一些顏色常量來方便書寫，如紅色就是vbRed。所以上面的代碼等價于r.Font.Color=vbRed。7.1.6幾個完整的VBA程序例7.1對一個單元格區(qū)域內(nèi)內(nèi)紅色的數(shù)字求和。Sub計算紅色單元格數(shù)據(jù)和()DimsumAsSingleDimrAsRangesum=0ForEachrInSelectionIfr.Font.Color=vbRedThensum=sum+r.ValueNextMsgBoxsumEndSub7.2庫存查詢7.2.1案例說明有一個庫存文件，現(xiàn)在要求從庫存表中查詢到包含某個關(guān)鍵字的設(shè)備的庫存情況。7.2.2知識要點分析從“設(shè)備名稱”字段中遍歷查詢，尋找包含指定關(guān)鍵字的設(shè)備名稱?？梢允褂肐nStr()函數(shù)解決這個問題。

如果有包含的設(shè)備名稱，那么，我們就將該設(shè)備名稱和其對應(yīng)的“庫存量”的值寫到“查詢結(jié)果”工作表的指定位置，并且統(tǒng)計共找到了多少個包含指定關(guān)鍵字的設(shè)備名稱。7.2.3操作步驟創(chuàng)建一個名稱為“Check_Stock”的宏，將代碼寫在其中。

為了方便使用，我們可以在表格上插入一個按鈕控件，將其指定給該宏。SubCheck_Stock()DimS$,J&,L$,F_Key$,M%DimEP_Name$,Total&Total=3DoTotal=Total+1IfWorksheets("查詢結(jié)果").Range("A"&Total).Value<>""ThenWorksheets("查詢結(jié)果").Range("A"&Total).Value=""Worksheets("查詢結(jié)果").Range("B"&Total).Value=""Worksheets("查詢結(jié)果").Range("C"&Total).Value=""ElseExitDoEndIfLoopF_Key=Range("C1").ValueM=1:S="B":J=2:Total=3DoJ=J+1L=S&JEP_Name=Worksheets("庫存表").Range(L).ValueIfEP_Name=""ThenExitDoIfInStr(EP_Name,F_Key)<>0ThenTotal=Total+1:Worksheets("查詢結(jié)果").Range("A"&Total).Value=Worksheets("庫存表").Range("A"&J).ValueWorksheets("查詢結(jié)果").Range("B"&Total).Value=EP_NameWorksheets("查詢結(jié)果").Range("C"&Total).Value=Worksheets("庫存表").Range("D"&J).ValueEndIfLoopWorksheets("查詢結(jié)果").Range("B2").Value="共找到"&Total-3&"種產(chǎn)品"EndSub7.3報名結(jié)果統(tǒng)計7.3.1案例說明將學(xué)生參賽報名簡表文件，匯總到報名信息匯總表中。同時還要將指導(dǎo)教師和參賽隊聯(lián)系人的姓名和電子郵箱地址統(tǒng)計到“E-mail”工作表中。7.3.2知識要點分析在“匯總表”中添加一個按鈕，具體執(zhí)行時，要先打開一個提取文件對話框，選擇需要提取信息的報名簡表文件，然后將報名簡表中的相關(guān)信息提取出來，寫入?yún)R總表的相應(yīng)位置。同時還要將報名簡表中的聯(lián)系人和指導(dǎo)教師的“姓名”和“郵箱”信息提取出來，寫入“E_Mail”表中。7.3.3操作步驟我們創(chuàng)建一個名稱為“collect”的宏，將以上分析的處理的代碼寫在Subcollect()下面。為了方便使用，我們可以在表格上插入一個按鈕控件，將其指定給Subcollect()事件。7.3.3操作步驟由于比賽允許一個指導(dǎo)教師指導(dǎo)多支參賽隊伍，因此，在我們統(tǒng)計指導(dǎo)教師和參賽隊聯(lián)系人的姓名和電子郵箱地址時可能會出現(xiàn)重復(fù)的情況，請大家自己試著寫一個VBA代碼刪除上面說到的重復(fù)數(shù)據(jù)。同時，也請大家試著看能不能通過Excel提供的系統(tǒng)功能和函數(shù)來解決這個問題。再見！第8章

綜合應(yīng)用主要內(nèi)容8.1銷售數(shù)據(jù)分析8.2采購成本分析8.1銷售數(shù)據(jù)分析8.1.1問題描述1.將“Excel_素材.xlsx”文件另存為“Excel.xlsx”(“.xlsx”為文件擴展名)，后續(xù)操作均基于此文件。2.命名“產(chǎn)品信息”工作表的單元格區(qū)域A1:D78名稱為“產(chǎn)品信息”；命名“客戶信息”工作表的單元格區(qū)域A1:G92名稱為“客戶信息”。8.1銷售數(shù)據(jù)分析3.在“訂單明細(xì)”工作表中，完成下列任務(wù):①根據(jù)B列中的產(chǎn)品代碼，在C列、D列和E列填入相應(yīng)的產(chǎn)品名稱、產(chǎn)品類別和產(chǎn)品單價(對應(yīng)信息可在“產(chǎn)品信息”工作表中查找)。②設(shè)置G列單元格格式，折扣為0的單元格顯示“-”，折扣大于0的單元格顯示為百分比格式，并保留0位小數(shù)(如15%)。③在H列中計算每訂單的銷售金額，公式為“金額=單價×數(shù)量×(1-折扣)”，設(shè)置E列和H列單元格為貨幣格式，保留2位小數(shù)。8.1銷售數(shù)據(jù)分析4.在“訂單信息”工作表中，完成下列任務(wù):①根據(jù)B列中的客戶代碼，在E列和F列填入相應(yīng)的發(fā)貨地區(qū)和發(fā)貨城市(提示:需首先清除B列中的空格和不可見字符)，對應(yīng)信息可在“客戶信息”工作表中查找。②在G列計算每訂單的訂單金額，該信息可在“訂單明細(xì)”工作表中查找(注意:一個訂單可能包含多個產(chǎn)品)，計算結(jié)果設(shè)置為貨幣格式，保留2位小數(shù)。③使用條件格式，將每訂單訂貨日期與發(fā)貨日期間隔大于10天的記錄所在單元格填充顏色設(shè)置為“紅色”，字體顏色設(shè)置為“白色，背景1”。8.1銷售數(shù)據(jù)分析5.在“產(chǎn)品類別分析”工作表中，完成下列任務(wù):①在B2:B9單元格區(qū)域計算每類產(chǎn)品的銷售總額，設(shè)置單元格格式為貨幣格式，保留2位小數(shù);并按照銷售額對表格數(shù)據(jù)降序排序。②在單元格區(qū)域D1:L17中創(chuàng)建復(fù)合餅圖，并根據(jù)樣例文件圖8-1設(shè)置圖表標(biāo)題、繪圖區(qū)、數(shù)據(jù)標(biāo)簽的內(nèi)容及格式。8.1銷售數(shù)據(jù)分析6.在所有工作表的最右側(cè)創(chuàng)建一個名為“地區(qū)和城市分析”的新工作表，并在該工作表A1:C19單元格區(qū)域創(chuàng)建數(shù)據(jù)透視表，以便按照地區(qū)和城市匯總訂單金額。數(shù)據(jù)透視表設(shè)置需與樣例文件圖8-2保持一致。8.1銷售數(shù)據(jù)分析7.在“客戶信息”工作表中，根據(jù)每個客戶的銷售總額計算其所對應(yīng)的客戶等級(不要改變當(dāng)前數(shù)據(jù)的排序)，等級評定標(biāo)準(zhǔn)可參考“客戶等級”工作表;使用條件格式，將客戶等級為1級~5級的記錄所在單元格填充顏色設(shè)置為“紅色”，字體顏色設(shè)置為“白色，背景1”。8.為文檔添加自定義屬性，屬性名稱為“機密”，類型為“是或否”，取值為“是”。8.1.3方案步驟要求1的操作步驟如下:（1）打開“Excel_素材.xl