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

第1章Excel基礎(chǔ)主要內(nèi)容1.1認(rèn)識(shí)Excel1.2Excel文件操作1.1認(rèn)識(shí)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)簽快速訪問(wèn)工具欄

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

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

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

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

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

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

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

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

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

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

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

等比數(shù)列無(wú)法像等差數(shù)列一樣以拉曳填充控點(diǎn)的方式來(lái)建立。例如，要建立2,4,8，……這樣的數(shù)列，我們可以：?jiǎn)螕簟伴_(kāi)始”菜單下“編輯”組中的“填充”命令。2.1.2知識(shí)要點(diǎn)分析3.數(shù)據(jù)填充自動(dòng)填充

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

2.1.2知識(shí)要點(diǎn)分析3.數(shù)據(jù)填充自動(dòng)填充

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

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

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

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

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

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

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

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

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

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

函數(shù)的使用

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

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

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

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

只顯示“河南省”考生“入學(xué)測(cè)試成績(jī)”在60分以上的學(xué)生。2.知識(shí)要點(diǎn)字符函數(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提供了強(qiáng)大的“PowerQuery”,即數(shù)據(jù)查詢功能，能幫助我們不用費(fèi)時(shí)費(fèi)人力粘貼、不用多數(shù)人接受不了的VBA、不用專業(yè)的SQL查詢語(yǔ)句，只需要鼠標(biāo)點(diǎn)下去，就能完成多工作簿的數(shù)據(jù)匯總。3.3完善學(xué)生信息表2.分列

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

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

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

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

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

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

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

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

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

RANDBETWEEN(M,N)

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

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

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

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

制作如圖所示的“出庫(kù)單”，要求：“貨號(hào)”字段由下拉框選擇輸入。當(dāng)“貨號(hào)”字段的值輸入完成后，“商品”、“單價(jià)”字段的值自動(dòng)填充?！皵?shù)量”字段應(yīng)該是大于等于1的整數(shù)。“小計(jì)”字段的值自動(dòng)填充，同時(shí)“合計(jì)”的值不能超過(guò)999999.99。將“小計(jì)”字段各個(gè)位的值拆開(kāi)，自動(dòng)填充到“金額”項(xiàng)的相應(yīng)字段。3.5制作貨物出庫(kù)單2.知識(shí)要點(diǎn)1.單元格區(qū)域命名2.名稱的命名規(guī)則3.名稱的使用3.5制作貨物出庫(kù)單3.方案步驟

制作表格

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

“合計(jì)”字段的計(jì)算

“金額”的設(shè)置

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

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

在I3單元格計(jì)算出每個(gè)員工的總銷售額

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

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

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

在P3單元格、P4單元格、P5單元格，求出每月的平均銷售額、每月的優(yōu)秀率、每月的達(dá)標(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.對(duì)每個(gè)部門(mén)的情況進(jìn)行統(tǒng)計(jì)分析制作相應(yīng)的表頭。在P16單元格向下填充計(jì)算出每個(gè)部門(mén)的人數(shù)。在Q16單元格計(jì)算出每月每個(gè)部門(mén)的平均銷售額在W16單元格求出每個(gè)部門(mén)上半年的平均銷售額3.6分析銷售數(shù)據(jù)3.統(tǒng)計(jì)銷售段人數(shù)在Q22單元格統(tǒng)計(jì)出一月份到六月份6萬(wàn)以下的人數(shù)在Q23單元格統(tǒng)計(jì)出一月份到六月份6到7萬(wàn)以下的人數(shù)。在Q24單元格統(tǒng)計(jì)出一月份到六月份7到8萬(wàn)以下的人數(shù)。在Q25單元格統(tǒng)計(jì)出一月份到六月份8到9萬(wàn)以下的人數(shù)。在Q26單元格統(tǒng)計(jì)出一月份到六月份9萬(wàn)以上的人數(shù)。3.7隨機(jī)生成考試座位號(hào)

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

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

八

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

組：一組被視為單個(gè)項(xiàng)的項(xiàng)。

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

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

計(jì)：用于顯示數(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)下，用戶只要按部就班的進(jìn)行操作，就可以輕松的完成數(shù)據(jù)透視表的制作過(guò)程。4.3數(shù)據(jù)透視表指定數(shù)據(jù)指定數(shù)據(jù)透視表的放置位置。指定數(shù)據(jù)透視表布局。指定數(shù)據(jù)透視表布局。選擇或填入“自動(dòng)”選項(xiàng)卡中的“起始”和“終止”日期，4.3數(shù)據(jù)透視表3.更改值匯總方式

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

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

利用切片器可以進(jìn)行快速篩選，還可以指定當(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ù)透視表顯示報(bào)表篩選頁(yè)

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

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

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

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

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

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

模擬分析

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

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

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

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

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

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

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

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

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

功能：用來(lái)在給定的幾組數(shù)組中，將數(shù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和。5.2求解取料問(wèn)題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求解取料問(wèn)題4.B12單元格實(shí)際丁原料的使用量，輸入公式：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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