Excel 2019數(shù)據(jù)分析技術(shù)與實(shí)踐 課件全套 第00-16講（第1-14章） 課程簡(jiǎn)介- Excel應(yīng)用：二手車數(shù)據(jù)分析

課程簡(jiǎn)介第一章Excel在各行各業(yè)中的應(yīng)用目錄頁CONTENTSPAGE1.學(xué)什么？2.如何考核？4.上機(jī)紀(jì)律3.學(xué)習(xí)建議學(xué)什么？1學(xué)什么——課程任務(wù)與目標(biāo)企業(yè)信息資源管理屬于微觀層次的信息資源管理的范疇，指企業(yè)為達(dá)到預(yù)定的目標(biāo)運(yùn)用現(xiàn)代化管理方法和手段對(duì)與企業(yè)相關(guān)的信息資源和信息活動(dòng)進(jìn)行組織、規(guī)劃、協(xié)調(diào)和控制，以實(shí)現(xiàn)對(duì)企業(yè)信息資源的合理開發(fā)和有效利用。信息資源管理包括數(shù)據(jù)資源管理和信息處理管理。數(shù)據(jù)資源管理強(qiáng)調(diào)對(duì)數(shù)據(jù)的控制，后者關(guān)心管理人員在一條件下如何獲取和處理信息，且強(qiáng)調(diào)企業(yè)信息資源的重要性。

Excel是目前最流行的辦公軟件，利用Excel在企業(yè)管理活動(dòng)中進(jìn)行數(shù)據(jù)處理已經(jīng)相當(dāng)普遍。例如用Excel進(jìn)行考勤管理、工資管理、業(yè)績(jī)管理、銷售管理、成本核算、財(cái)務(wù)分析、籌資決策分析、投資決策分析等工作。學(xué)什么——課程任務(wù)與目標(biāo)在電子商務(wù)、企業(yè)財(cái)務(wù)管理、人力資源管理、辦公自動(dòng)化等諸多領(lǐng)域得到了廣泛應(yīng)用。Excel2019在數(shù)據(jù)分析領(lǐng)域上的常用功能，包括數(shù)據(jù)采集、數(shù)據(jù)加工與處理、數(shù)據(jù)分類與統(tǒng)計(jì)、數(shù)據(jù)可視化以及VBA編程等方面的知識(shí)。階段式：第一階段，主要學(xué)習(xí)Excel的基本使用第二階段，主要學(xué)習(xí)VBA編程，實(shí)現(xiàn)數(shù)據(jù)批量處理與自動(dòng)化辦公第三階段，是綜合實(shí)訓(xùn)階段，包括人力資源管理、財(cái)會(huì)會(huì)計(jì)應(yīng)用、電商數(shù)據(jù)分析學(xué)什么——課程學(xué)時(shí)與內(nèi)容安排授課方式：講解演示+練習(xí)實(shí)踐+問題總結(jié)學(xué)時(shí)安排：每周3學(xué)時(shí)（周三），1-16周，共48學(xué)時(shí)上課地點(diǎn)：機(jī)房上課（明德樓618）教學(xué)內(nèi)容：數(shù)據(jù)采集數(shù)據(jù)處理數(shù)據(jù)統(tǒng)計(jì)與分析數(shù)據(jù)報(bào)表Excel編程綜合應(yīng)用學(xué)什么——教材教材：《Excel2019數(shù)據(jù)分析與實(shí)踐》陳清華施莉莉主編參考教材：《Excel在經(jīng)濟(jì)管理中的應(yīng)用與VBA程序設(shè)計(jì)》徐軍編寫（2013）《Excel在經(jīng)濟(jì)管理中的應(yīng)用》何永琴等編寫（2018）本門課程使用的軟件是Excel2019如何考核？2如何考核——考核方式過程考核占50分：出勤(10)：扣分制（曠課1次-2分，請(qǐng)假1次-1分，遲到/早退1次-1分）課堂表現(xiàn)(10)：基礎(chǔ)分6分（表現(xiàn)好/差1次+/-1分）實(shí)驗(yàn)報(bào)告(30)：加分制（根據(jù)作業(yè)、測(cè)驗(yàn)等完成情況計(jì)算）終結(jié)性考核(考查)占50分：開卷上機(jī)考查綜合評(píng)價(jià)（100分）=過程考核（50分）+期末考核（50分）如何考核——上課要求考勤(10)：曠課或請(qǐng)假次數(shù)達(dá)到1/3取消考試資格課堂表現(xiàn)(10)上課不認(rèn)真1次扣1分（包括吃東西、講話、玩手機(jī)、不服從老師安排等）上機(jī)不認(rèn)真編程一次扣1分過程考核——出勤及課堂表現(xiàn)（20）如何考核——上課要求3.實(shí)訓(xùn)作業(yè)(30)：過程考核——實(shí)訓(xùn)作業(yè)（30%）上交時(shí)間：下次課前上傳FTP逾期不給分填寫要求：完整、整潔、正確課代表：認(rèn)真負(fù)責(zé)學(xué)習(xí)建議3學(xué)習(xí)建議——興趣你們用過EXCEL哪個(gè)版本？現(xiàn)在是哪個(gè)版本？學(xué)習(xí)建議——興趣掌握Excel基本功能的基礎(chǔ)上學(xué)習(xí)建議——興趣關(guān)注Excel報(bào)表的設(shè)計(jì)學(xué)習(xí)建議——樹立學(xué)習(xí)目標(biāo)學(xué)習(xí)建議——樹立學(xué)習(xí)目標(biāo)關(guān)注各版本的新功能學(xué)習(xí)建議——樹立學(xué)習(xí)目標(biāo)1利用Excel設(shè)計(jì)專業(yè)報(bào)告：年度財(cái)務(wù)報(bào)表應(yīng)用2使用VBA完成自動(dòng)化辦公：制作考試座位表4使用Excel制作應(yīng)用軟件：數(shù)據(jù)采集軟件、財(cái)務(wù)會(huì)計(jì)軟件關(guān)注各行業(yè)的應(yīng)用實(shí)例3使用Excel完成數(shù)據(jù)分析應(yīng)用：商務(wù)數(shù)據(jù)分析學(xué)習(xí)建議——如何學(xué)？基礎(chǔ)←→提高探討←→交流總結(jié)←→分享實(shí)驗(yàn)←→實(shí)踐工夫在課外?。?！學(xué)習(xí)建議——課程進(jìn)階先修課程：《大學(xué)計(jì)算機(jī)基礎(chǔ)》、《Office辦公軟件》課程進(jìn)階：浙江省計(jì)算機(jī)等級(jí)考試二級(jí)考試二級(jí)辦公軟件高級(jí)應(yīng)用技術(shù)CEAC國(guó)家信息化計(jì)算機(jī)教育認(rèn)證信息化辦公課程認(rèn)證電子商務(wù)數(shù)據(jù)分析（初級(jí)、中級(jí)）上機(jī)紀(jì)律4上機(jī)紀(jì)律上機(jī)要求上機(jī)前應(yīng)復(fù)習(xí)所學(xué)的實(shí)驗(yàn)內(nèi)容。上機(jī)人員必須愛護(hù)計(jì)算機(jī)設(shè)備，注意安全用電，確保人身安全，杜絕各類安全事故的發(fā)生，嚴(yán)禁插拔微機(jī)房上的任何連線。不得自行拔、插、移動(dòng)機(jī)器及其部件，不得自行增減機(jī)器部件。實(shí)驗(yàn)過程中，若發(fā)生故障或事故，應(yīng)立即關(guān)機(jī)、斷電、中斷實(shí)驗(yàn)，并報(bào)告指導(dǎo)老師或管理員，等候處理。上機(jī)紀(jì)律上機(jī)要求上機(jī)人員應(yīng)在指定機(jī)位上機(jī)，嚴(yán)禁將自帶磁盤、鼠標(biāo)、耳機(jī)或與上機(jī)內(nèi)容無關(guān)的物品進(jìn)入機(jī)房，不帶任何火種和危險(xiǎn)品進(jìn)入機(jī)房，不得把機(jī)房的物品帶出機(jī)房。遵守機(jī)房課堂紀(jì)律，保持機(jī)房的安靜和整潔。不大聲喧嘩，不隨意走動(dòng)，不吃零食，不吸煙，不隨地吐痰。上機(jī)紀(jì)律上機(jī)要求上機(jī)嚴(yán)禁運(yùn)行游戲程序，不得運(yùn)行與所學(xué)課程無關(guān)的軟件，不得改動(dòng)機(jī)器的設(shè)置，特別是不得改動(dòng)機(jī)器中設(shè)置的口令。上機(jī)結(jié)束請(qǐng)將機(jī)器關(guān)閉，鍵盤、凳子放回原處。謝謝聆聽！模塊一：EXCEL技術(shù)基礎(chǔ)第二章Excel的基本使用目錄頁CONTENTSPAGEExcel2019窗口組成Excel2019基本設(shè)置工作簿與工作表基本操作Excel條件格式保護(hù)Excel文檔本章內(nèi)容學(xué)習(xí)目標(biāo)LEARNINGTARGETExcel窗口組成：快速訪問工具欄、名稱框Excel基本設(shè)置：自動(dòng)保存設(shè)置、模版、新建默認(rèn)數(shù)工作簿與工作表基本操作：打開Excel方式、標(biāo)簽顏色、窗口拆分與凍結(jié)Excel條件格式：條件格式保護(hù)Excel文檔：加密與備份、保護(hù)工作表、保護(hù)工作簿等重點(diǎn)掌握Excel2019窗口組成1目錄窗口組成快速訪問工具欄名稱框本節(jié)內(nèi)容Excel窗口組成窗口組成標(biāo)題欄菜單欄功能區(qū)中央編輯區(qū)滾動(dòng)條狀態(tài)欄快速訪問工具欄名稱框快速訪問工具欄標(biāo)題欄名稱框菜單欄功能區(qū)程序關(guān)閉按鈕行號(hào)狀態(tài)欄工作表標(biāo)簽水平滾動(dòng)條垂直滾動(dòng)條列標(biāo)單元格插入工作表工作表導(dǎo)航視圖切換顯示比例滑動(dòng)條插入函數(shù)按鈕編輯欄Excel窗口組成1.快速訪問工具欄“快速訪問工具欄”顧名思義就是將常用的工具擺放于此，幫助快速完成工作。預(yù)設(shè)的“快速訪問工具欄”只有3個(gè)常用的工具，分別是“保存”、“撤銷”及“恢復(fù)”，如果想將自己常用的工具也加此區(qū)，請(qǐng)按下

進(jìn)行設(shè)定。按此圖標(biāo)展開“自定義快速訪問工具欄”列表例如選擇“新建”這里會(huì)列出常用的功能，如果正好是您需要的功能，只要選取功能命令，即可加入快速訪問欄了。已加入“新建”鈕，以后要新建文件就方便多了。Excel窗口組成如果經(jīng)常使用的命令不在清單中，您可執(zhí)行【其他命令】，開啟Excel選項(xiàng)窗設(shè)定：選擇此命令在此列表中選擇命令（例如選擇“插入工作表行”功能）這里會(huì)列出已加入“快速訪問工具欄”的命令剛才選取的“插入工作表行”按下“添加”鈕1.快速訪問工具欄Excel窗口組成2.名稱框作用：Excel名稱框主要可以實(shí)現(xiàn)快速定位到需要的單元格區(qū)域以及簡(jiǎn)化公式寫法。名稱框的快速定位功能具體體現(xiàn)：定位到某個(gè)單元格；要選中某幾列并快速定位這幾列數(shù)據(jù)；要選中某幾行；要選中一方形選區(qū)。Excel窗口組成2.名稱框作用：Excel名稱框主要可以實(shí)現(xiàn)快速定位到需要的單元格區(qū)域以及簡(jiǎn)化公式寫法。名稱框定義可簡(jiǎn)化公式，具體做法：自定義名稱：選中某單元格，在名稱框里輸入自定義名稱；定義區(qū)域，也是同樣做法。在公式中的應(yīng)用：在公式中使用名稱，可以使公式更直觀易讀。比如要對(duì)1-3月進(jìn)行求和，即B2:D121單元格區(qū)域求和，如果將這一區(qū)域定義為一季度，那么在公式欄中可以，直接輸入：=SUM(一季度)。定義多區(qū)域：如果需要在名稱框快速定位多個(gè)區(qū)域，比如選擇A1:A10、E2:E10及F3:F100，只需要選擇這些單元格區(qū)域之后，單擊“公式——定義名稱”，彈出“新建名稱”對(duì)話框，在名稱中輸入：多區(qū)域，引用位置復(fù)制：A1:A10,E2:E10,F3:F100，確定。Excel2019基本設(shè)置2目錄自動(dòng)保存模板使用新建工作簿的工作表默認(rèn)數(shù)本節(jié)內(nèi)容Excel2019基本設(shè)置1.自動(dòng)保存自動(dòng)保存功能設(shè)置“保存自動(dòng)恢復(fù)信息時(shí)間間隔”，就是說在使用Excel2019時(shí)沒有保存表格而遇突發(fā)事件，此項(xiàng)功能可以給予我們非常大的幫助，通過你設(shè)置的“保存自動(dòng)恢復(fù)信息時(shí)間間隔”時(shí)間，可以恢復(fù)你原先的表格。說明：此項(xiàng)功能只能恢復(fù)Excel在異常情況下沒保存關(guān)閉程序。如果你是正常操作關(guān)閉程序的時(shí)候按的是“否”，那么表格將無法恢復(fù)。自動(dòng)保存如何設(shè)置文件－選項(xiàng)－保存如遇突發(fā)事件，導(dǎo)致表格沒有保存，可以打開“自動(dòng)恢復(fù)文件位置”后面路徑中的位置，找到你之前的表格。Excel2019基本設(shè)置2.模板使用創(chuàng)建模版

Excel2019中提供有大量的現(xiàn)有模版，使用模版創(chuàng)建工作簿不僅速度快，而且部分格式已確定，省去了調(diào)整格式的時(shí)間。有時(shí)我們也需要?jiǎng)?chuàng)建自己的工作簿模板。點(diǎn)擊【文件】—【另存為】，選擇【保存類型】為“Excel模板”，注意：不要改變文檔的存放位置！?。。?！使用模版選擇【文件】-》【新建】--》【個(gè)人】》【我的模板】。Excel2019基本設(shè)置3.工作表默認(rèn)數(shù)新建工作表默認(rèn)數(shù)文件－選項(xiàng)－常規(guī)－新建工作表簿時(shí)－包含的工作表數(shù)工作表數(shù)：1－255新建一個(gè)工作簿時(shí)才能體現(xiàn)出來工作簿與工作表基本操作1.3目錄打開Excel工作簿的方式工作表標(biāo)簽顏色設(shè)置窗口拆分與凍結(jié)自動(dòng)套用表格樣式本節(jié)內(nèi)容工作簿與工作表基本操作1.打開Excel工作簿的方式在使用文件菜單下的打開按扭時(shí)，可選擇方式有：普通方式只讀方式副本方式瀏覽器方式保護(hù)的視圖修復(fù)方式工作簿與工作表基本操作2.工作表標(biāo)簽顏色設(shè)置設(shè)定頁次（工作表）標(biāo)簽顏色除了可以更改工作表的名稱，頁次卷標(biāo)的顏色也可以個(gè)別設(shè)定，這樣看起來就更容易辨識(shí)了。例如我們要將“銷售數(shù)量”這個(gè)頁次卷標(biāo)改為紅色：在頁次卷標(biāo)上按下鼠標(biāo)右鍵選擇此命令選擇頁次卷標(biāo)的顏色選擇此項(xiàng)，可恢復(fù)頁次標(biāo)簽的默認(rèn)色彩設(shè)定好之后，“銷售數(shù)量”工作表的頁次卷標(biāo)會(huì)顯示紅色底線，那是因?yàn)椤颁N售數(shù)量”是目前的作用工作表；若選取其他的工作表，就會(huì)看到“銷售數(shù)量”工作表的頁次卷標(biāo)正是剛才設(shè)定的紅色了：表格主題顏色如何設(shè)置？工作簿與工作表基本操作3.窗口拆分與凍結(jié)凍結(jié)拆分窗口當(dāng)凍結(jié)窗格時(shí)，可以選擇在工作表中滾動(dòng)時(shí)仍可見的特定行或列。其方法如下：要鎖定行，請(qǐng)選擇其下方要出現(xiàn)拆分的行。要鎖定列，請(qǐng)選擇其右側(cè)要出現(xiàn)拆分的列。要同時(shí)鎖定行和列，請(qǐng)單擊其下方和右側(cè)要出現(xiàn)拆分的單元格。點(diǎn)擊菜單欄“視圖”-“凍結(jié)拆分窗格”當(dāng)凍結(jié)窗格時(shí)，“凍結(jié)窗格”選項(xiàng)更改為“取消凍結(jié)窗格”，可以取消對(duì)行或列的鎖定。工作表拆分視圖－窗口－拆分將窗口拆分成四個(gè)部分工作簿與工作表基本操作凍結(jié)拆分窗口拆分窗格以鎖定單獨(dú)工作表區(qū)域中的行或列：指向垂直滾動(dòng)條頂端或水平滾動(dòng)條右端的拆分框。當(dāng)指針變?yōu)椴鸱种羔樆驎r(shí)，將拆分框向下或向左拖至所需的位置。要取消拆分，請(qǐng)雙擊分割窗格的拆分條的任何部分。垂直滾動(dòng)條拆分框水平滾動(dòng)條拆分框3.窗口拆分與凍結(jié)工作簿與工作表基本操作4.自動(dòng)套用表格格式自動(dòng)套用表格格式開始-套用表格格式套用現(xiàn)成的格式工作簿與工作表基本操作上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告第1-5題注意思考題20分鐘1.4Excel條件格式目錄突出顯示單元格規(guī)則項(xiàng)目選取規(guī)則 數(shù)據(jù)條色階圖標(biāo)集本節(jié)內(nèi)容Excel條件格式利用“格式化條件”分析數(shù)據(jù)當(dāng)工作表中的數(shù)據(jù)量一多，實(shí)在很難一眼辨識(shí)出數(shù)據(jù)高低或找出所需的項(xiàng)目，若我們想強(qiáng)調(diào)工作表中的某些資料，如：會(huì)計(jì)成績(jī)達(dá)到90分以上者、銷售額未達(dá)標(biāo)準(zhǔn)者，便可利用設(shè)定格式化的條件功能，自動(dòng)將數(shù)據(jù)套上特別的格式以利辨識(shí)。在設(shè)定格式化的條件功能中，具有許多種數(shù)據(jù)設(shè)定規(guī)則與視覺效果，“開始”-“條件格式”：設(shè)定格式化的條件功能的各種規(guī)則每種規(guī)則中可選擇更細(xì)的設(shè)定條件Excel條件格式以下我們將一一利用銷售業(yè)績(jī)表，來練習(xí)套用設(shè)定格式化的條件功能的各種規(guī)則。1.突出顯示單元格規(guī)則 4.色階2.項(xiàng)目選取規(guī)則 5.圖標(biāo)集3.數(shù)據(jù)條設(shè)定格式化的條件應(yīng)用之一：分析全班考試成績(jī)，標(biāo)示為黃色的是90分以上，標(biāo)示為紅色的則是不及格的成績(jī)，一眼就可以分辨出來了。利用“格式化條件”分析數(shù)據(jù)Excel條件格式1.突出顯示單元格規(guī)則突出顯示單元格規(guī)則包括：大于、小于、介于、等于、文本包含、發(fā)生日期、重復(fù)值。突出顯示單元格規(guī)則中的各類規(guī)則Excel條件格式用“重復(fù)值”規(guī)則標(biāo)示暢銷作者重復(fù)值規(guī)則可標(biāo)示出數(shù)據(jù)范圍內(nèi)單元格數(shù)據(jù)有重復(fù)的項(xiàng)目。倘若我們想知道有哪些作者是排行榜的?？?，就可以利用此規(guī)則來查詢，選取查詢的范圍，然后按下設(shè)定格式化的條件鈕，執(zhí)行『突出顯示單元格規(guī)則／重復(fù)值』命令：若在此選擇唯一，則可標(biāo)示只出現(xiàn)過一次的名字選擇重復(fù)設(shè)定標(biāo)示的格式按確定標(biāo)示出重復(fù)出現(xiàn)在榜上的暢銷作家1.突出顯示單元格規(guī)則Excel條件格式2.項(xiàng)目選取規(guī)則用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)以銷售業(yè)績(jī)數(shù)據(jù)為例，我們要從一堆數(shù)據(jù)中找出業(yè)績(jī)優(yōu)秀與不佳的資料：方法一：步驟1--用“值最大的10項(xiàng)”標(biāo)示名列前茅的業(yè)務(wù)成績(jī)項(xiàng)目選取規(guī)則可標(biāo)示出數(shù)據(jù)范圍內(nèi)，排行在最前面或最后面幾項(xiàng)的數(shù)據(jù)。倘若我們想了解每一季的業(yè)績(jī)中，業(yè)績(jī)位于前3名或是業(yè)績(jī)位于倒數(shù)3名的業(yè)務(wù)員，就可以利用此規(guī)則來查詢。請(qǐng)選取要查詢的范圍，然后按下設(shè)定格式化的條件鈕，執(zhí)行『項(xiàng)目選取規(guī)則／值最大的10項(xiàng)』命令，先查詢第1季的前3名：Excel條件格式在此輸入3，表示要查詢前3名的數(shù)字選擇要標(biāo)示的格式列出第1季的前3名按確定用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)2.項(xiàng)目選取規(guī)則Excel條件格式用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)方法一：步驟2--

用“值最小的10項(xiàng)”標(biāo)示名列前茅的業(yè)務(wù)成績(jī)。接續(xù)上列，請(qǐng)選取要查詢的范圍，按下設(shè)定格式化的條件鈕，執(zhí)行『項(xiàng)目選取規(guī)則／值最小的10項(xiàng)』命令，來查詢倒數(shù)3名的業(yè)務(wù)成績(jī)：在此輸入3，表示要查詢后3名的數(shù)字選擇要標(biāo)示的格式列出第1季的倒數(shù)3名按確定2.項(xiàng)目選取規(guī)則Excel條件格式用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)方法二：用“前10%”及"最后10%"規(guī)則標(biāo)示前30%或最后30%的業(yè)績(jī)以業(yè)績(jī)高低百分比率標(biāo)示出業(yè)績(jī)好與業(yè)績(jī)壞的項(xiàng)目。按下設(shè)定格式化的條件鈕，執(zhí)行“項(xiàng)目選取規(guī)則／值最大的10%"，分別查出每一季位居前30%的業(yè)績(jī)：選擇要標(biāo)示的格式在此輸入30，表示要查詢列于前30%的項(xiàng)目2.項(xiàng)目選取規(guī)則Excel條件格式用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)方法二：用“前10%”及"最后10%"規(guī)則標(biāo)示前30%或最后30%的業(yè)績(jī)要再列出業(yè)績(jī)落在最后30%的項(xiàng)目，按下設(shè)定格式化的條件鈕。執(zhí)行『項(xiàng)目選取規(guī)則／值最小的10%』命令進(jìn)行設(shè)定：將前面30%標(biāo)示為紅色，倒數(shù)30%標(biāo)示為綠色2.項(xiàng)目選取規(guī)則Excel條件格式用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)方法三：用高于平均及低于平均規(guī)則找出高于或低于平均的業(yè)績(jī)選取查詢的范圍，按下設(shè)定格式化的條件鈕，執(zhí)行『項(xiàng)目選取規(guī)則／高于平均值』命令。選擇要標(biāo)示的格式2.項(xiàng)目選取規(guī)則Excel條件格式用“項(xiàng)目選取規(guī)則”標(biāo)示銷售業(yè)績(jī)方法三：用高于平均及低于平均規(guī)則找出高于或低于平均的業(yè)績(jī)?cè)賵?zhí)行『項(xiàng)目選取規(guī)則／低于平均值』命令，使用不同的格式來標(biāo)示低于平均值的項(xiàng)目，就可以清楚分辨出每一季大家的業(yè)績(jī)好壞了：2.項(xiàng)目選取規(guī)則Excel條件格式3.數(shù)據(jù)橫條用“數(shù)據(jù)橫條”規(guī)則標(biāo)示業(yè)績(jī)數(shù)據(jù)高低資料橫條會(huì)使用不同長(zhǎng)度的色條，來顯示數(shù)據(jù)數(shù)據(jù)，數(shù)字愈大色條愈長(zhǎng)，反之，數(shù)字愈小則色條愈短。繼續(xù)以銷售業(yè)績(jī)工作表作為示例。選取要查詢的范圍，按下設(shè)定格式化的條件鈕，執(zhí)行『數(shù)據(jù)條』命令：從默認(rèn)的項(xiàng)目中，選擇要使用的顏色Excel條件格式4.色階用“色階”規(guī)則標(biāo)示銷售業(yè)績(jī)色階規(guī)則會(huì)使用不同深淺或不同色系的色彩，來顯示數(shù)據(jù)，例如：數(shù)字較大的用深色表示，數(shù)字較小的用淺色表示。點(diǎn)選要使用的色階項(xiàng)目Excel條件格式5.圖標(biāo)集用“圖標(biāo)集”規(guī)則標(biāo)示學(xué)生成績(jī)圖標(biāo)集規(guī)則有17種圖標(biāo)類型，可在各數(shù)據(jù)旁邊附注旗幟、燈號(hào)或箭頭等圖示。假設(shè)以三旗幟圖示為例：綠色旗幟表示較高值，黃色旗幟表示中間值，紅色旗幟表示較低值。以學(xué)生成績(jī)工作表進(jìn)行示例：選取數(shù)據(jù)范圍，按下設(shè)定格式化的條件鈕，執(zhí)行『圖標(biāo)集』命令。點(diǎn)選要使用的圖示類型，此例選擇五箭號(hào)（彩色）項(xiàng)目Excel條件格式上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告第7-8題15分鐘保護(hù)Excel文檔5目錄加密與備份保護(hù)工作表保護(hù)工作簿保護(hù)共享工作簿隱藏公式隱藏工作簿本節(jié)內(nèi)容保護(hù)Excel文檔1.加密與備份1）選取“文件”-“另存為”，點(diǎn)擊“工具”-“常規(guī)選項(xiàng)”，可設(shè)置文件的打開權(quán)限密碼和修改權(quán)限密碼，以及設(shè)置只讀和生成備份文件。設(shè)置打開密碼設(shè)置修改密碼生成備份文件復(fù)選框設(shè)置只讀工具-常規(guī)選項(xiàng)保護(hù)Excel文檔2）或者通過選取“文件-信息-保護(hù)工作簿”里面的“用密碼進(jìn)行加密”也可以對(duì)文件進(jìn)行加密動(dòng)作。1.加密與備份保護(hù)Excel文檔2.保護(hù)工作表1）對(duì)單元格進(jìn)行保護(hù)設(shè)置鎖定屬性，以保護(hù)存入單元格的內(nèi)容不被改寫。選中單元格，右鍵“設(shè)置單元格格式”，在對(duì)話框中選擇“保護(hù)”標(biāo)簽并選中“鎖定”；選定需要鎖定的單元格，選擇“審閱”-“保護(hù)工作表”；同時(shí)設(shè)置取消保護(hù)密碼，即完成對(duì)單元格的鎖定設(shè)置。保護(hù)Excel文檔3.保護(hù)工作簿打開“審閱”-“保護(hù)工作簿”或者“文件-信息-保護(hù)工作簿”?？杀Ｗo(hù)工作簿結(jié)構(gòu)，以免被刪除、移動(dòng)、隱藏、取消隱藏、重命名工作表，并且不可插入新的工作表。選定“窗口”選項(xiàng)”則可以保護(hù)工作簿窗口不被移動(dòng)、縮放、隱藏、取消隱藏或關(guān)閉。保護(hù)Excel文檔如果要對(duì)工作簿中的修訂進(jìn)行跟蹤，可設(shè)置保護(hù)共享工作簿。打開“審閱”-“保護(hù)并共享工作簿”，對(duì)共享的工作簿進(jìn)行設(shè)置。4.保護(hù)共享工作簿保護(hù)Excel文檔5.隱藏公式如果不想在共享工作簿之后，讓其他用戶看到并編輯已有公式，可在共享之前，將包含公式的單元格設(shè)置為隱藏，并保護(hù)工作表。步驟一：選定要隱藏的公式所在的單元格區(qū)域，右擊選擇“設(shè)置單元格格式”，單擊“保護(hù)”選項(xiàng)，選擇“隱藏”復(fù)選框，單擊“確定”。步驟二：點(diǎn)擊“審閱”-“保護(hù)工作表”，即可隱藏公式。保護(hù)Excel文檔6.隱藏工作簿選擇“視圖”-“隱藏”，可以把當(dāng)前處于活動(dòng)狀態(tài)的工作簿隱藏起來；如果要取消隱藏，可選擇“窗口”-“取消隱藏”命令，然后在“取消隱藏”窗口中選擇相應(yīng)工作簿即可。Excel條件格式上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告第6題15分鐘本章重點(diǎn)與難點(diǎn)EXCEL2019窗口組成和基本設(shè)置工作簿與工作表的基本操作條件格式：條件格式的各種應(yīng)用保護(hù)EXCEL文檔：幾種方法謝謝聆聽！模塊一：EXCEL技術(shù)基礎(chǔ)第三章Excel數(shù)據(jù)采集上節(jié)回顧工作簿與工作表工作窗口：快速訪問工具欄、名稱框基本設(shè)置：自動(dòng)保存設(shè)置、模版、工作表默認(rèn)數(shù)基本操作：打開方式、標(biāo)簽顏色、窗口拆分與凍結(jié)、自動(dòng)套用格式Excel條件格式：條件格式5種規(guī)則保護(hù)Excel文檔：加密與備份、保護(hù)工作表、保護(hù)工作簿上節(jié)回顧復(fù)習(xí)提問名稱框能實(shí)現(xiàn)哪些功能？窗口凍結(jié)可以實(shí)現(xiàn)哪些操作？條件格式有哪些規(guī)則？保護(hù)Excel文檔，上次課講了哪幾個(gè)？目錄頁CONTENTSPAGE外部數(shù)據(jù)導(dǎo)入數(shù)據(jù)填充數(shù)據(jù)驗(yàn)證數(shù)據(jù)采集相關(guān)應(yīng)用本章內(nèi)容學(xué)習(xí)目標(biāo)LEARNINGTARGET外部數(shù)據(jù)導(dǎo)入：自文本、自網(wǎng)站、自數(shù)據(jù)庫數(shù)據(jù)填充：自動(dòng)填充、序列填充、自定義序列數(shù)據(jù)驗(yàn)證應(yīng)用：制作收款憑證、差旅報(bào)銷單、購置稅匯總表重點(diǎn)掌握外部數(shù)據(jù)導(dǎo)入1外部數(shù)據(jù)導(dǎo)入Excel里面的數(shù)據(jù)，除了手工自動(dòng)錄入滿足日常工作學(xué)習(xí)之用外，還可以導(dǎo)入文本文件、word、各種數(shù)據(jù)庫(如Access)等不同來源的數(shù)據(jù)。操作：數(shù)據(jù)－獲取外部數(shù)據(jù)獲取外部數(shù)據(jù)外部數(shù)據(jù)導(dǎo)入例如：我們希望通過單擊一個(gè)按鈕，即可從Web站點(diǎn)上直接獲取數(shù)據(jù)（如最新的股票報(bào)價(jià)信息），將其導(dǎo)入到Excel工作表中進(jìn)行查看、分析，并希望以后更新數(shù)據(jù)以與Web頁上的最新數(shù)據(jù)保持一致，可以使用Web查詢來完成此操作。其操作步驟如下：在“數(shù)據(jù)”菜單上，點(diǎn)擊“獲取外部數(shù)據(jù)”，然后單擊“自網(wǎng)站”，彈出“新建Web查詢”對(duì)話框。在Web地址一欄輸入你要導(dǎo)入的Web地址，選擇要導(dǎo)入的部分及Web頁的格式，按確定。在對(duì)話框中選擇數(shù)據(jù)導(dǎo)入的位置，例如“=$A$7”，那么滿足條件的數(shù)據(jù)便會(huì)從數(shù)據(jù)庫中自動(dòng)提取并從單元格$A$7開始向下填充，如果選擇新表，則自動(dòng)創(chuàng)建一張新工作表用于存放導(dǎo)入的數(shù)據(jù)，按“確定”就可以完成網(wǎng)頁數(shù)據(jù)的導(dǎo)入。此后，要更新數(shù)據(jù)與Web頁上的最新數(shù)據(jù)保持一致，單擊“數(shù)據(jù)”菜單上的“全部刷新”命令，或右鍵“刷新”。1.自網(wǎng)站外部數(shù)據(jù)導(dǎo)入四種web查閱選項(xiàng)，選擇導(dǎo)入Excel的格式：【無】指只導(dǎo)入文字【僅RTF格式】指導(dǎo)入格式但不包含鏈接【完全HTML格式】指導(dǎo)入文字、格式和鏈接。1.自網(wǎng)站外部數(shù)據(jù)導(dǎo)入Excel里面的數(shù)據(jù)，除了手工自動(dòng)錄入滿足工作學(xué)習(xí)之用外，還可以導(dǎo)入文本文件、word、各種數(shù)據(jù)庫(如Access)等不同來源的數(shù)據(jù)。2.自文本外部數(shù)據(jù)導(dǎo)入Excel中有個(gè)專門的工具用于將數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)入Excel中：MicrosoftQuery是用于將數(shù)據(jù)從外部數(shù)據(jù)源檢索到Excel中的一種程序。通過使用Query可以從企業(yè)的數(shù)據(jù)庫和文件中檢索數(shù)據(jù)，而不必重新鍵入需要在Excel中分析的數(shù)據(jù)。也可以在每次更新數(shù)據(jù)庫時(shí)，自動(dòng)通過源數(shù)據(jù)庫中的數(shù)據(jù)來更新Excel報(bào)表和匯總數(shù)據(jù)。

MicrosoftQuery可訪問的數(shù)據(jù)庫類型有：MicrosoftAccess、MicrosoftSQLServer和Oracle等數(shù)據(jù)庫。也可以從Excel清單和文本文件中檢索數(shù)據(jù)。3.自數(shù)據(jù)庫外部數(shù)據(jù)導(dǎo)入當(dāng)從外部導(dǎo)入的外部數(shù)據(jù)源有變時(shí)，Excel需要從不同數(shù)據(jù)源重新讀取數(shù)據(jù)，這就需要用到Excel的功能“全部刷新”。該功能菜單：數(shù)據(jù)-全部刷新-全部刷新。4.刷新數(shù)據(jù)外部數(shù)據(jù)導(dǎo)入上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告：第1-2題操作數(shù)據(jù)填充2數(shù)據(jù)填充在Excel表格中填寫數(shù)據(jù)時(shí)，經(jīng)常會(huì)遇到一些在結(jié)構(gòu)上有規(guī)律的數(shù)據(jù)，例如1997、1998、1999；星期一、星期二、星期三等。對(duì)這些數(shù)據(jù)我們可以采用填充技術(shù)，讓它們自動(dòng)出現(xiàn)在一系列的單元格中。填充功能是通過“填充柄”或“填充序列對(duì)話框”來實(shí)現(xiàn)的。填充柄：用鼠標(biāo)單擊一個(gè)單元格或拖曳鼠標(biāo)選定一個(gè)連續(xù)的單元格區(qū)域時(shí)，框線的右下角會(huì)出現(xiàn)一個(gè)黑點(diǎn)，這個(gè)黑點(diǎn)就是填充柄。填充序列對(duì)話框：?jiǎn)螕簟熬庉嫛辈藛蜗碌摹疤畛洹敝械摹靶蛄小奔纯?。序列填充填充柄?shù)據(jù)填充1.序列填充用鼠標(biāo)單擊一個(gè)單元格時(shí)，框線的右下角會(huì)出現(xiàn)一個(gè)黑點(diǎn)，這個(gè)黑點(diǎn)就是填充柄。拖動(dòng)填充柄完成序列填充。輸入數(shù)據(jù)類型直接拖動(dòng)填充柄按下CTRL同時(shí)拖動(dòng)填充柄文本相同數(shù)值填充相同數(shù)值填充數(shù)字相同數(shù)值填充序列填充，步長(zhǎng)為1日期相同數(shù)值填充以日序列填充，步長(zhǎng)為1時(shí)間相同數(shù)值填充以時(shí)序列填充，步長(zhǎng)為1數(shù)據(jù)填充1）等差等比序列填充在單元格中輸入第一個(gè)數(shù)據(jù)，開始-編輯-填充-序列，打開序列對(duì)話框中根據(jù)輸入數(shù)據(jù)選擇“等差序列”或“等比序列”，再根據(jù)情況設(shè)置“步長(zhǎng)值”（遞增：正數(shù)，遞減：負(fù)數(shù)），最后單擊“確定”退出。1.序列填充數(shù)據(jù)填充2）日期序列填充在單元格中輸入第一個(gè)日期，右鍵拖動(dòng)該單元格填充柄到最后一個(gè)單元格，快捷菜單中選擇“序列”，打開的對(duì)話框中選擇“日期”，繼續(xù)選擇“日期單位：年、月、日、工作日”再根據(jù)情況設(shè)置“步長(zhǎng)值”（遞增：正數(shù)，遞減：負(fù)數(shù)），最后單擊“確定”退出。1.序列填充數(shù)據(jù)填充3）自定義序列填充使用Excel錄入數(shù)據(jù)時(shí)，經(jīng)常會(huì)需要輸入一系列具有相同特征的數(shù)據(jù)。例如周一到周日、一組按一定順序編號(hào)的產(chǎn)品名稱等。如果經(jīng)常用到同樣的數(shù)據(jù)，可以將其添加到填充序列列表中，以方便日后的使用。1.序列填充數(shù)據(jù)填充1）不同單元格輸入相同數(shù)據(jù)選中多個(gè)單元格–輸入數(shù)據(jù)–快捷鍵“Ctrl+Enter”快速填充。2）記憶式輸入選項(xiàng)-高級(jí)-編輯選項(xiàng)-為單元格值啟用記憶式鍵入選中單元格按右鍵–從下拉列表中選擇ALT+向下箭頭2.其他填充方式數(shù)據(jù)填充3）分?jǐn)?shù)輸入如輸入分?jǐn)?shù)1/2，則必須在單元格中輸入“0?”才可以顯示效果。4）填充至同組工作表如果想在不同工作表的相同單元格中輸入相同數(shù)據(jù)時(shí)，則先選中這些工作表，點(diǎn)擊“開始－編輯－填充－成組工作表”。這樣可在這些表中輸入一次數(shù)據(jù)，不同的工作表的對(duì)應(yīng)位置也會(huì)產(chǎn)生相同的數(shù)據(jù)。2.其他填充方式數(shù)據(jù)填充上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告：第3-4題20分鐘數(shù)據(jù)驗(yàn)證（檢驗(yàn)）3數(shù)據(jù)驗(yàn)證EXCEL中數(shù)據(jù)有效性的設(shè)置數(shù)據(jù)制作過程中需要對(duì)某些單元格設(shè)置下拉列表,便于數(shù)據(jù)的選擇和限制其他無效數(shù)據(jù)的輸入，需要對(duì)數(shù)據(jù)的有效性進(jìn)行設(shè)置。數(shù)據(jù)－數(shù)據(jù)驗(yàn)證1.數(shù)據(jù)驗(yàn)證數(shù)據(jù)驗(yàn)證1）直接自定義序列有時(shí)候我們?cè)诟髁懈餍兄卸驾斎胪瑯拥膸讉€(gè)值，比如說，輸入學(xué)生的等級(jí)時(shí)我們只輸入四個(gè)值：優(yōu)秀，良好，合格，不合格。單元格能夠像下拉框一樣，讓輸入者在下拉菜單中選擇就可以實(shí)現(xiàn)輸入。操作步驟：在“允許”下拉菜單中選擇“序列”；在“數(shù)據(jù)來源”中輸入“優(yōu)秀，良好，合格，不合格”（注意用英文狀態(tài)下的逗號(hào)分隔）；選上"忽略空值"和"提供下拉菜單"兩個(gè)復(fù)選框。點(diǎn)擊【輸入信息】選項(xiàng)卡，選上“選定單元格顯示輸入信息”，在“輸入信息”中輸入“請(qǐng)?jiān)谶@里選擇”。

2.下拉菜單的實(shí)現(xiàn)數(shù)據(jù)驗(yàn)證2）利用表內(nèi)數(shù)據(jù)作為序列源有時(shí)候序列值較多，直接在表內(nèi)打印區(qū)域外把序列定義好，然后引用。操作步驟：先在同一工作表內(nèi)的打印區(qū)域外要定義序列填好(假設(shè)在D2:D3），如“男，女”，選擇【設(shè)置】選項(xiàng)卡，在"允許"下拉菜單中選擇"序列"；“來源”欄點(diǎn)擊右側(cè)的展開按鈕（有一個(gè)紅箭頭），用鼠標(biāo)拖動(dòng)滾動(dòng)條，選中序列區(qū)域在D2:D3；選上"忽略空值"和"提供下拉菜單"兩個(gè)復(fù)選框。點(diǎn)擊【輸入信息】選項(xiàng)卡，選上"選定單元格顯示輸入信息"，在"輸入信息"中輸入"請(qǐng)?jiān)谶@里選擇"。2.下拉菜單的實(shí)現(xiàn)數(shù)據(jù)驗(yàn)證3）聯(lián)動(dòng)實(shí)現(xiàn)：思考動(dòng)手。實(shí)驗(yàn)報(bào)告3（2）2.下拉菜單的實(shí)現(xiàn)5分鐘數(shù)據(jù)驗(yàn)證3.數(shù)據(jù)唯一性校驗(yàn)員工的身份證號(hào)碼應(yīng)該是唯一的，為了防止重復(fù)輸入，我們用“數(shù)據(jù)有效性”來提示大家。操作步驟：選中需要建立輸入身份證號(hào)碼的單元格區(qū)域（如C2至C16列），執(zhí)行“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證”命令，在【設(shè)置】標(biāo)簽下，按“允許”右側(cè)的下拉按鈕，選擇“自定義”選項(xiàng)，然后在下面“公式”方框中輸入公式：=COUNTIF(C:C,C2)=1，確定返回。點(diǎn)擊【出錯(cuò)警告】選項(xiàng)卡，在“樣式”列表框選上“警告”，在“錯(cuò)誤信息”中輸入“身份證號(hào)碼有重復(fù)，請(qǐng)重新輸入”。為了將這個(gè)設(shè)置應(yīng)用到整個(gè)C列（除了字段名稱所在的單元格即C2單元格），可用填充柄工具向下拖動(dòng)將公式復(fù)制到C列其他的單元格。注：當(dāng)有多個(gè)條件時(shí)，可用AND/OR：=and(COUNTIF(C:C,C2)=1,len(C2)=18)=true數(shù)據(jù)驗(yàn)證4.自動(dòng)實(shí)現(xiàn)輸入法中英文轉(zhuǎn)換有時(shí)需在不同行或不同列之間要分別輸入中文和英文。我們希望Excel能自動(dòng)實(shí)現(xiàn)輸入法在中英文間轉(zhuǎn)換。操作步驟：假設(shè)在A列輸入學(xué)生的中文名，B列輸入學(xué)生的英文名。先選定B列，點(diǎn)擊進(jìn)入“數(shù)據(jù)→數(shù)據(jù)驗(yàn)證”，進(jìn)入【輸入法模式】，在“模式”下拉菜單中選擇“關(guān)閉（英文模式）”；然后再“確定”。數(shù)據(jù)驗(yàn)證5.圈釋無效數(shù)據(jù)有時(shí)，輸入了錯(cuò)誤的數(shù)據(jù)，需要醒目注釋時(shí)，可以使用該功能。數(shù)據(jù)驗(yàn)證上機(jī)實(shí)踐完成實(shí)驗(yàn)報(bào)告：第5題15分鐘4數(shù)據(jù)采集相關(guān)應(yīng)用實(shí)際應(yīng)用1.制作會(huì)計(jì)憑證背景知識(shí)1：企業(yè)收款憑證收款憑證：是用來記錄現(xiàn)金和銀行存款收款業(yè)務(wù)的會(huì)計(jì)憑證，它是由出納人員根據(jù)審核無誤的原始憑證收款后填制的。在借貸記賬法下，在收款憑證左上方所填列的借方科目，應(yīng)是“現(xiàn)金”或“銀行存款”科目。在憑證內(nèi)所反映的貸方科目，應(yīng)填列與“現(xiàn)金”或“銀行存款”相對(duì)應(yīng)的科目。金額欄填列經(jīng)濟(jì)業(yè)務(wù)實(shí)際發(fā)生的數(shù)額，在憑證的右側(cè)填寫所附原始憑證張數(shù)，并在出納及制單處簽名或蓋章。實(shí)際應(yīng)用1.制作會(huì)計(jì)憑證背景知識(shí)1：企業(yè)收款憑證實(shí)際應(yīng)用1.制作會(huì)計(jì)憑證問題知識(shí)點(diǎn)會(huì)計(jì)憑證表格基本操作單元格合并邊框列寬文字方向格式擴(kuò)充：科目下拉數(shù)據(jù)驗(yàn)證合計(jì)公式實(shí)際應(yīng)用2.差旅費(fèi)報(bào)銷單問題知識(shí)點(diǎn)報(bào)銷單表格基本操作數(shù)據(jù)驗(yàn)證保護(hù)單元格公式單元格格式填充職別：“助教、講師、副教授、教授”所有金額：保留2為小數(shù)的數(shù)字格式合計(jì)公式，調(diào)整合計(jì)金額格式為“會(huì)計(jì)專用”在其他費(fèi)用中批量輸入5筆記錄，金額均為1元實(shí)際應(yīng)用3.汽車購置稅計(jì)算問題知識(shí)點(diǎn)購置稅匯總表購置稅購置稅額是征收汽車售價(jià)（不含稅價(jià)）的10%，公式為：新車購置稅額=計(jì)稅價(jià)格*10%汽車經(jīng)銷商對(duì)外提供的價(jià)格往往是含稅價(jià)格。因此，計(jì)算新車購置稅前需要先去掉增值稅17%部分，即為計(jì)稅價(jià)格。以下案例為計(jì)算購置稅的應(yīng)用情境。1）周：自動(dòng)填充；2）銷售總額、購置稅額：數(shù)據(jù)類型整數(shù)；3）合計(jì)購置稅：會(huì)計(jì)專用格式，累計(jì)本周購置稅；4）保護(hù)策略：不允許修改表格結(jié)構(gòu)；5）清空數(shù)據(jù)，保存為模版。實(shí)際應(yīng)用課后練習(xí)完成實(shí)驗(yàn)報(bào)告：第6-8題，任選1題15分鐘本講小結(jié)外部數(shù)據(jù)導(dǎo)入：自文本、自網(wǎng)站、自數(shù)據(jù)庫、數(shù)據(jù)刷新數(shù)據(jù)填充：自動(dòng)填充、序列填充、自定義序列、其他數(shù)據(jù)驗(yàn)證實(shí)際應(yīng)用：制作收款憑證、差旅費(fèi)報(bào)銷單、汽車購置稅匯總表謝謝聆聽！模塊一：EXCEL技術(shù)基礎(chǔ)第四章數(shù)據(jù)分類與處理（一）上節(jié)回顧知識(shí)回顧數(shù)據(jù)填充：自動(dòng)填充、序列填充、自定義序列數(shù)據(jù)校驗(yàn)外部數(shù)據(jù)導(dǎo)入：自文本、自網(wǎng)站、自數(shù)據(jù)庫應(yīng)用：制作收款憑證、差旅費(fèi)報(bào)銷單、車輛購置稅匯總表上節(jié)回顧復(fù)習(xí)提問數(shù)據(jù)填充：序列填充的三個(gè)功能？如何填充相同的內(nèi)容至同一工作表的不同單元格？如何填充相同的內(nèi)容至不同工作表的相同單元格？數(shù)據(jù)校驗(yàn)：可設(shè)置的四項(xiàng)是？如何設(shè)置下拉選項(xiàng)？上節(jié)回顧復(fù)習(xí)練習(xí)數(shù)據(jù)填充：1.產(chǎn)生自定義序列：個(gè)、十、百、千、萬2.產(chǎn)生日期序列：2021/01/01-2021/01/31共計(jì)31天數(shù)據(jù)驗(yàn)證：下拉制作：選擇年份，可選2010－2021（今年）

校驗(yàn)文字長(zhǎng)度：6－8個(gè)字符長(zhǎng)度10分鐘目錄頁CONTENTSPAGE數(shù)據(jù)篩選排序與分類匯總單元格引用名稱定義與引用函數(shù)本章內(nèi)容第四章數(shù)據(jù)分類與處理學(xué)習(xí)目標(biāo)LEARNINGTARGET數(shù)據(jù)篩選排序與分類匯總單元格引用：相對(duì)地址、絕對(duì)地址、混合地址、跨表引用、跨工作簿引用名稱引用：名稱定義、引用函數(shù)本章重難點(diǎn)數(shù)據(jù)篩選1目錄自動(dòng)篩選自定義篩選高級(jí)篩選本節(jié)內(nèi)容數(shù)據(jù)篩選數(shù)據(jù)篩選分類：自動(dòng)篩選、自定義篩選、高級(jí)篩選。1.自動(dòng)篩選自動(dòng)篩選又稱直接條件篩選。如篩選性別為“男”或“女”。數(shù)據(jù)—排序和篩選—篩選數(shù)據(jù)篩選篩選—數(shù)據(jù)篩選/文本篩選—自定義篩選。2.自定義篩選數(shù)據(jù)篩選篩選—高級(jí)篩選。在“高級(jí)篩選”對(duì)話框中，篩選方式有兩種：在原有區(qū)域顯示篩選結(jié)果；將篩選結(jié)果復(fù)制到其他位置。列表區(qū)域：要篩選的區(qū)域，即所有數(shù)據(jù)區(qū)域；條件區(qū)域：設(shè)置的篩選條件，即設(shè)定篩選條件區(qū)域。與篩選：兩個(gè)條件在同一行；或篩選：兩個(gè)條件在不同行。3.高級(jí)篩選數(shù)據(jù)篩選上機(jī)實(shí)踐使用數(shù)據(jù)的三種篩選方式完成實(shí)驗(yàn)報(bào)告第1題10分鐘2分類匯總分類匯總分類匯總是一種按字段分類的數(shù)據(jù)處理。字段的分類就是先對(duì)字段進(jìn)行排序，使相同的字段排在一起，然后進(jìn)行分類處理。分類處理可以是求和、求平均值、計(jì)數(shù)、最大值、最小值、乘積等。具體操作步驟：對(duì)分類字段進(jìn)行排序，排序的目的是要把同類記錄放在一起。通過使用“數(shù)據(jù)”—“分級(jí)顯示”—“分類匯總”命令，可以自動(dòng)計(jì)算列匯總的分類匯總和總計(jì)。例如：求各個(gè)產(chǎn)品的銷售總額，就可以使用分類匯總求和。分類匯總多重分類匯總：分類匯總上機(jī)實(shí)踐綜合排序、分類匯總完成實(shí)驗(yàn)報(bào)告第2題10分鐘數(shù)據(jù)計(jì)算：?jiǎn)卧褚?目錄相對(duì)與絕對(duì)引用切換混合引用跨表引用跨工作簿引用本節(jié)內(nèi)容單元格引用概述EXCEL公式中會(huì)運(yùn)用到的地址有相對(duì)引用地址與絕對(duì)參照地址2種類型。相對(duì)引用地址的表示法，如：Bl、C4。絕對(duì)參照地址的表示法，則須在單元格地址前面加上“$”符號(hào)，如：$B$l、$C$4。相對(duì)與絕對(duì)參照有什么差異？相對(duì)引用地址會(huì)隨著公式的位置而改變；而絕對(duì)參照位址則不管公式在什么地方，它永遠(yuǎn)指向同一個(gè)單元格。單元格引用1.相對(duì)與絕對(duì)引用切換切換相對(duì)引用與絕對(duì)參照地址的快捷鍵F4。F4鍵可循序切換單元格地址的參照類型，每按一次F4鍵，參照地址的類型就會(huì)改變，其切換結(jié)果如右表：接著輸入“+B2”，再按下F4鍵將B2變成$B$2，最后按下F4鍵，公式就建立完成了：F4存儲(chǔ)格參照位址B3第1次$B$3絕對(duì)參照第2次B$3只有行編號(hào)是絕對(duì)地址第3次$B3只有列編號(hào)是絕對(duì)地址第4次B6還原為相對(duì)參照C3的公式內(nèi)容單元格引用C2及C3的公式分別是由相對(duì)地址與絕對(duì)地址組成，但兩者的計(jì)算結(jié)果卻不一樣。它們差別在哪里呢？選定C2：C3單元格，拉曳填滿控點(diǎn)到下一欄，將公式復(fù)制到D2：D3單元格：計(jì)算結(jié)果不同了1.相對(duì)與絕對(duì)引用切換F4存儲(chǔ)格參照位址B3第1次$B$3絕對(duì)參照第2次B$3只有行編號(hào)是絕對(duì)地址第3次$B3只有列編號(hào)是絕對(duì)地址第4次B6還原為相對(duì)參照單元格引用2.混合引用如果在公式中同時(shí)使用相對(duì)引用與絕對(duì)引用，這種情形稱為混合引用，例如：這種公式在復(fù)制后，絕對(duì)參照的部份(如$Bl的$B)不會(huì)變動(dòng)，而相對(duì)引用的部份則會(huì)隨情況做調(diào)整。請(qǐng)依照下列步驟將D2單元格中的公式改成混合參照公式：=$B2+C2具體步驟：?jiǎn)螕鬌2單元格，將插入點(diǎn)移至”=”之后，接著按兩次F4鍵，讓$B$2變成$B2。絕對(duì)引用相對(duì)引用絕對(duì)引用相對(duì)引用=$A$1+A2

和=$B1+B2單元格引用3.跨表引用EXCEL中同一個(gè)文件中不同工作表之間的數(shù)據(jù)可以互相引用，例如：=Sheet2!B2=工作表名!A1+工作表名!D2單元格引用4.跨工作簿引用EXCEL中不同文件中的數(shù)據(jù)可以互相引用，例如：=[文件名.xlsx]Sheet2!B4打開要引用的兩個(gè)工作簿，在其中一個(gè)工作簿的某個(gè)工作表中激活要引用數(shù)據(jù)的單元格，在“編輯”欄中輸入一個(gè)“＝”。切換到另一個(gè)已經(jīng)打開的工作簿的工作表中。單擊要引用數(shù)據(jù)的單元格，“編輯”欄中將出現(xiàn)工作簿間的引用公式。按下“Enter”鍵或單擊“編輯”欄中的“輸入”按鈕即可。單元格引用上機(jī)實(shí)踐完成存款利息、銷售額的計(jì)算實(shí)驗(yàn)報(bào)告第3－5題15分鐘名稱引用4目錄名稱管理器名稱的定義名稱的修改與刪除名稱的使用本節(jié)內(nèi)容名稱引用名稱管理器名稱可以代表一定的單元格區(qū)域、常量、文本、公式等。選擇“公式”->“名稱管理器”(或按Ctrl+F3鍵）時(shí)，出現(xiàn)“名稱管理器”?！懊Q管理器”功能:顯示有關(guān)工作簿中每個(gè)名稱的信息。允許篩選顯示的名稱。單擊"篩選"按鈕僅顯示符合一定標(biāo)準(zhǔn)的名稱。為"新建名稱"對(duì)話框提供快速訪問。編輯名稱。警告：刪除名稱時(shí)需特別小心。如果名稱用于公式中，刪除名稱會(huì)導(dǎo)致公式變得無效(顯示#NAME?)。然而，刪除名稱操作可撤消，所以如果在刪除名稱后發(fā)現(xiàn)公式返回#NAME?，選擇"撤消"命令(或按Ctrl+Z)恢復(fù)名稱。名稱引用1.名稱的定義具體方法有3種：在“名稱框”中直接輸入名稱后回車。使用“定義名稱”功能。使用“名稱管理器”—“新建”功能。名稱引用2.名稱的修改/刪除對(duì)于已經(jīng)定義好的名稱如何修改和刪除呢，其實(shí)很簡(jiǎn)單，如果在名稱管理器內(nèi)直接修改或刪除即可。單擊刪除即可對(duì)已定義的名稱刪除，單擊編輯后，在彈出的對(duì)話框中修改相應(yīng)選項(xiàng)即可完成修改。名稱引用3.名稱的使用定義完名稱后，就可在EXCEL中像引用單元格一樣引用名稱，以便于我們使用，比如倉庫數(shù)據(jù)有效性時(shí)就可以直接用名稱代替。如圖，當(dāng)然除此之后還有很多其它的用法，大家可以自己再探索一下對(duì)于已經(jīng)定義好的名稱如何修改和刪除呢，其實(shí)很簡(jiǎn)單，如果在名稱管理器內(nèi)直接修改或刪除即可。1）在公式中使用，其中全年是名稱：=sum（全年）2）在數(shù)據(jù)驗(yàn)證中使用，地區(qū)即是名稱。名稱引用上機(jī)實(shí)踐完成銷售總量的計(jì)算即實(shí)驗(yàn)報(bào)告第6題6分鐘常用函數(shù)5常用函數(shù)數(shù)值函數(shù)常用函數(shù)文本函數(shù)常用函數(shù)日期與時(shí)間函數(shù)本章小結(jié)數(shù)據(jù)篩選：三種數(shù)據(jù)篩選方式排序與分類匯總：按字段分類，并統(tǒng)計(jì)匯總單元格引用：相對(duì)地址、絕對(duì)地址、混合地址、跨表引用、跨工作簿引用名稱引用：名稱定義、修改和引用常用函數(shù)：數(shù)值、日期、字符串謝謝聆聽！模塊一：EXCEL技術(shù)基礎(chǔ)第四章數(shù)據(jù)分類與處理（二）上節(jié)回顧知識(shí)點(diǎn)回顧單元格引用：相對(duì)引用、絕對(duì)引用、混合引用、跨表引用、跨工作簿引用名稱引用：名稱定義、修改和引用數(shù)據(jù)篩選：三種數(shù)據(jù)篩選方式（自動(dòng)篩選、自定義篩選、高級(jí)篩選）分類匯總：按字段分類，并統(tǒng)計(jì)匯總問題：例舉每種引用有何不同，寫法用法？問題：例舉名稱的使用？問題：哪三種篩選方式，其中高級(jí)篩選能實(shí)現(xiàn)哪些功能？問題：匯總制作步驟？上節(jié)回顧高級(jí)篩選復(fù)習(xí)提問性別為男，或工資>3000條件區(qū)域：性別為男，且工資>3000條件區(qū)域：性別為男且工資>3000，或性別為女且工資>5000條件區(qū)域：上節(jié)回顧復(fù)習(xí)練習(xí)1.完成人員銷售量的統(tǒng)計(jì)：相對(duì)引用，絕對(duì)引用，混合引用2.完成冠軍銷量統(tǒng)計(jì)：跨表引用，名稱的使用其中1－12月使用跨表引用全年使用名稱框3.完成銷售額的統(tǒng)計(jì)：跨簿引用其中每月單價(jià)在文件“知識(shí)回顧：?jiǎn)蝺r(jià)”中。4.查找篩選1月銷量>=80，或12月銷量>=85的人員名單10分鐘目錄頁CONTENTSPAGE2.時(shí)間函數(shù)3.文本函數(shù)4.其他函數(shù)1.日期函數(shù)本節(jié)知識(shí)點(diǎn)日期函數(shù)：TODAY,YEAR,MONTH,DAY,

NETWORKDAYS,DATE時(shí)間函數(shù)：NOW,HOUR,MINITUE,SECOND文本函數(shù)：UPPER,LOWER,PROPER,MID,LEFT,RIGHT,FIND其他函數(shù)：YEARFARC日期函數(shù)1日期函數(shù)Today（）Year（）Networkdays（）Date（）返回日期格式的當(dāng)前日期返回日期的年份值返回兩個(gè)日期之間的完整工作日返回excel日期時(shí)間代碼中代表日期的數(shù)字日期函數(shù)1.日期函數(shù)：TODAY()返回日期格式的當(dāng)前日期當(dāng)前日期是Excel日期和時(shí)間計(jì)算使用的日期-時(shí)間代碼。如果在輸入函數(shù)前，單元格的格式為“常規(guī)”，Excel會(huì)將單元格格式自動(dòng)更改為“日期”。如果要查看序列號(hào)，則必須將單元格格式更改為“常規(guī)”或“數(shù)值”。TODAY函數(shù)也可以用于計(jì)算時(shí)間間隔。TODAY()參數(shù)引用：公式說明（結(jié)果）=TODAY()返回日期格式的當(dāng)前日期（2020/03/30）例1：在登記表上，設(shè)置自動(dòng)標(biāo)上當(dāng)天的制表日期。日期函數(shù)返回日期的年份值。以此可類推MONTH、DAY函數(shù)。Serial_number為一個(gè)日期值，其中包含要查找年份的日期。應(yīng)使用DATE函數(shù)輸入日期，或者將函數(shù)作為其他公式或函數(shù)的結(jié)果輸入。YEAR()參數(shù)引用：2.年份函數(shù)：YEAR(serial_number)公式說明（結(jié)果）=YEAR(B2)第一個(gè)日期的年份（2016）=YEAR(C2)第一個(gè)日期的年份（2018）=YEAR(C2)-YEAR(B2)2018-2016=2例2：求各工程竣工歷時(shí)多少年，已完工多久。日期函數(shù)實(shí)訓(xùn)任務(wù)一5分鐘知識(shí)點(diǎn)日期時(shí)間函數(shù)todayyear完成實(shí)驗(yàn)報(bào)告，題1工齡計(jì)算；原文件詳見工作簿“01：日期函數(shù)”。日期函數(shù)返回參數(shù)start-date和end-date之間完整的工作日（不包括周末和專門指定的假期）NETWORKDAYS(start_date，end_date，holidays)Start_date代表開始日期。End_date代表終止日。Holidays是表示不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，法定假日以及其他非法定假日。此數(shù)據(jù)清單可以是包含日期的單元格區(qū)域，也可以是由代表日期的序列號(hào)所構(gòu)成的數(shù)組常量。3.工作日函數(shù)：Networkdays（）公式說明（結(jié)果）=NETWORKDAYS(A2,A3)開始日期-終止日期，實(shí)際工作日=NETWORKDAYS(A2,A3,A4)扣除第一個(gè)假日=NETWORKDAYS(A2,A3,A4:A11)扣除所有假日日期函數(shù)例3：求某企業(yè)員工工作天數(shù)。3.工作日函數(shù)：Networkdays（）日期函數(shù)實(shí)訓(xùn)任務(wù)一5分鐘知識(shí)點(diǎn)日期時(shí)間函數(shù)networkdays完成實(shí)驗(yàn)7，題2工作日計(jì)算；原文件詳見工作簿“01：日期函數(shù)”。日期函數(shù)返回excel日期時(shí)間代碼中代表日期的數(shù)字DATE（year,month,day）Year參數(shù)year可以為1到4位數(shù)字。Month代表每年中月份的數(shù)字。如果所輸入的月份大于12，將從指定年份的一月份開始往上加算。例如：DATE(2018,14,2)返回代表2019年2月2日的序列號(hào)。Day代表在該月份中第幾天的數(shù)字。如果day大于該月份的最大天數(shù)，則將從指定月份的第一天開始往上累加。例如，DATE(2018,1,35)返回代表2018年2月4日的序列號(hào)。4.日期函數(shù)：Date（）例4：計(jì)劃撰寫一本西藏游記完成時(shí)間。日期函數(shù)實(shí)訓(xùn)任務(wù)一5分鐘知識(shí)點(diǎn)日期時(shí)間函數(shù)DATE日期減法完成實(shí)驗(yàn)7，題3-5生產(chǎn)天數(shù)計(jì)算等；原文件詳見工作簿“01：日期函數(shù)”。時(shí)間函數(shù)2時(shí)間函數(shù)Second（）Minute（）Hour（）時(shí)間函數(shù)1.時(shí)間函數(shù)：Second函數(shù)返回時(shí)間值的秒數(shù)。SECOND(serial_number)Serial_number必需。表示一個(gè)時(shí)間值，其中包含要查找的秒數(shù)。時(shí)間有多種輸入方式：帶引號(hào)的文本字符串（例如"6:45PM"）、十進(jìn)制數(shù)（例如0.78125表示6:45PM）或其他公式或函數(shù)的結(jié)果（例如TIMEVALUE("6:45PM")）。返回的秒數(shù)為0到59之間的整數(shù)。時(shí)間函數(shù)2.時(shí)間函數(shù)：Minute函數(shù)返回時(shí)間值的分鐘數(shù)。MINUTE(serial_number)Serial_number必需。表示一個(gè)時(shí)間值，其中包含要查找的分鐘數(shù)。返回的分?jǐn)?shù)為0到59

之間的整數(shù)。時(shí)間函數(shù)3.時(shí)間函數(shù)：Hour函數(shù)返回時(shí)間值的小時(shí)數(shù)。HOUR(serial_number)Serial_number必需。表示一個(gè)時(shí)間值，其中包含要查找的小時(shí)數(shù)。返回的小時(shí)數(shù)為0到23

之間的整數(shù)。時(shí)間函數(shù)實(shí)訓(xùn)任務(wù)二5分鐘知識(shí)點(diǎn)日期時(shí)間函數(shù)日期減法secondminutehour完成實(shí)驗(yàn)7，題1運(yùn)動(dòng)會(huì)成績(jī)計(jì)算；原文件詳見工作簿“02：時(shí)間函數(shù)”。時(shí)間函數(shù)實(shí)訓(xùn)任務(wù)二8分鐘知識(shí)點(diǎn)日期時(shí)間函數(shù)日期減法secondminuteHourIf函數(shù)完成實(shí)驗(yàn)7，題2停車費(fèi)計(jì)算；原文件詳見工作簿“02：時(shí)間函數(shù)”。3文本函數(shù)文本函數(shù)Proper（）Upper（）Lower（）Mid（）Mod（）Len（）Find（）Left（）Right（）Reft（）文本函數(shù)1.文本函數(shù)：Proper函數(shù)將文本字符串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫，將其余的字母轉(zhuǎn)換成小寫。PROPER(text)text必需。用引號(hào)括起來的文本、返回文本值的公式或是對(duì)包含文本（要進(jìn)行部分大寫轉(zhuǎn)換）的單元格的引用。=PROPER(B2)文本函數(shù)2.文本函數(shù)：Upper函數(shù)將文本轉(zhuǎn)換為大寫字母。UPPER(text)text必需。用引號(hào)括起來的文本、返回文本值的公式或是對(duì)包含文本（要進(jìn)行部分大寫轉(zhuǎn)換）的單元格的引用。=UPPER(B2)文本函數(shù)3.文本函數(shù)：Lower函數(shù)將文本轉(zhuǎn)換為小寫字母。LOWER(text)text必需。用引號(hào)括起來的文本、返回文本值的公式或是對(duì)包含文本（要進(jìn)行部分大寫轉(zhuǎn)換）的單元格的引用。=LOWER(D2)文本函數(shù)4.文本函數(shù)：Mid函數(shù)返回文本字符串中從指定位置開始的特定數(shù)目的字符。MID(text,start_num,num_chars)text:為包含要提取字符的文本字符串。start_num:為文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的start_num為1，以此類推。num_chars:指定希望MID從文本中返回字符的個(gè)數(shù)。=MID(B2,7,8)文本函數(shù)5.取模函數(shù)：Mod函數(shù)返回兩數(shù)相除的余數(shù)。MOD(number,divisor)Number：為被除數(shù)。Divisor：為除數(shù)。如果divisor為零，函數(shù)MOD返回錯(cuò)誤值#DIV/0!。結(jié)果的正負(fù)號(hào)與除數(shù)相同。=IF(MOD(MID(B2,17,1),2)=0,"女","男")文本函數(shù)實(shí)訓(xùn)任務(wù)三10分鐘知識(shí)點(diǎn)文本函數(shù)properupperlowermidmod完成實(shí)驗(yàn)報(bào)告，1身份證信息處理；原文件詳見工作簿“03：文本函數(shù)”。文本函數(shù)1.文本函數(shù)：Len函數(shù)返回文本字符串中的字符個(gè)數(shù)。LEN(text)text：為要計(jì)算其長(zhǎng)度的文本字符串；包括空格。=LEN(A2)文本函數(shù)2.文本查找函數(shù)：Find函數(shù)用于查找其他文本字符串(within_text)內(nèi)的文本字符串(find_text)，并從within_text的首字符開始返回find_text的起始位置編號(hào)。FIND(find_text,within_text,start_num)Find_text：是要查找的文本。Within_text：是包含要查找文本的文本。Start_num：指定開始進(jìn)行查找的字符。如果忽略start_num，則假設(shè)其為1。FIND函數(shù)區(qū)分大小寫并且不允許使用通配符。=FIND(B1,D2,1)文本函數(shù)3.文本函數(shù)：Left函數(shù)從文本字符串的開頭返回指定數(shù)目的字符。LEFT(text,num_chars)text：包含要提取的字符的文本字符串，或?qū)Π谋镜牧械囊谩um_chars：（可選）您希望LEFT提取的字符的數(shù)目；如果省略，則為1。=LEFT(D2,4)=LEFT(B8,FIND("-",B8)-1)=MID(B8,1,FIND("-",B8)-1)文本函數(shù)4.文本函數(shù)：Right函數(shù)從文本字符串的末尾開始返回指定數(shù)目的字符。RIGHT(text,num_chars)text：包含要提取的字符的文本字符串，或?qū)Π谋镜牧械囊?。num_chars：（可選）您希望RIGHT提取的字符的數(shù)目；如果省略，則為1。=RIGHT(B12,LEN(B12)-FIND("-",B12))=MID(B12,FIND("-",B12)+1,LEN(B12)-FIND("-",B12))文本函數(shù)實(shí)訓(xùn)任務(wù)三10分鐘知識(shí)點(diǎn)文本函數(shù)LenFindLeftRightMid完成實(shí)驗(yàn)報(bào)告，2電話號(hào)碼處理；原文件詳見工作簿“03：文本函數(shù)”。文本函數(shù)1.文本函數(shù)：Rept函數(shù)按照定義的次數(shù)重復(fù)顯示文本。REPT（text，number_times)text：表示需要重復(fù)顯示的文本number_times：表示指定文本重復(fù)顯示的次數(shù)=REPT("|",100*B2/$B$18)文本函數(shù)實(shí)訓(xùn)任務(wù)三6分鐘知識(shí)點(diǎn)文本函數(shù)/查找函數(shù)REPTLOOKUP完成實(shí)驗(yàn)報(bào)告，3全年生產(chǎn)統(tǒng)計(jì)；原文件詳見工作簿“03：文本函數(shù)”。4其他函數(shù)其他函數(shù)1.效益函數(shù)：Yearfrac函數(shù)判別某一特定條件下全年效益或債務(wù)的比例。YEARFRAC（tstart_date,end_date,basis)Start_date：表示開始日期;End_date：代表結(jié)束日期。函數(shù)中的日期有多種輸入方式：帶引號(hào)的文本串(如"1998/01/30")、序列號(hào)(如35829表示1900日期系統(tǒng)中的1998年1月30日)，或其他公式或函數(shù)的結(jié)果(例如DATevalUE("1998/1/30"))。Basis：表示日計(jì)數(shù)基準(zhǔn)類型，其中：0或省略為US(NASD)30/3601實(shí)際天數(shù)/實(shí)際天數(shù)2實(shí)際天數(shù)/3603實(shí)際天數(shù)/3654歐洲30/360。=YEARFRAC(B2,C2,2)其他函數(shù)實(shí)訓(xùn)任務(wù)四5分鐘知識(shí)點(diǎn)其他函數(shù)yearfrac完成實(shí)驗(yàn)報(bào)告報(bào)告，1盈利額預(yù)算；原文件詳見工作簿“04：其它函數(shù)”。其他函數(shù)實(shí)訓(xùn)任務(wù)五5分鐘完成實(shí)驗(yàn)報(bào)告，1評(píng)星練習(xí)；原文件詳見工作簿“05：綜合練習(xí)”。涉及函數(shù)IFVLOOKUP本章小結(jié)日期函數(shù)：TODAY,YEAR,MONTH,DAY,

NETWORKDAYS,DATE時(shí)間函數(shù)：NOW,HOUR,MINITUE,SECOND文本函數(shù)：UPPER,LOWER,PROPER,MID,LEFT,RIGHT,FIND其他函數(shù)：YEARFARC謝謝聆聽！模塊一：EXCEL基礎(chǔ)第五章統(tǒng)計(jì)函數(shù)與數(shù)據(jù)分析知識(shí)回顧數(shù)據(jù)篩選：三種數(shù)據(jù)篩選方式排序與分類匯總：按字段分類，并統(tǒng)計(jì)匯總單元格引用：相對(duì)地址、絕對(duì)地址、混合地址、跨表引用、跨工作簿引用名稱引用：名稱定義、修改和引用常用函數(shù)：數(shù)值、日期、字符串目錄頁CONTENTSPAGE1.條件計(jì)數(shù)2.條件求和3.條件均值4.排名統(tǒng)計(jì)Countif/countifsSum/sumif/sumifsAverageif/averageifsRoundRankMax/MinLarge/SmallEXCEL知識(shí)點(diǎn)：常用統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)函數(shù)與數(shù)據(jù)分析分段統(tǒng)計(jì)四舍五入函數(shù)最值函數(shù)排名統(tǒng)計(jì)條件計(jì)數(shù)1統(tǒng)計(jì)函數(shù)——計(jì)數(shù)指定區(qū)域中符合指定條件的單元格計(jì)數(shù)。countif（range，criteria）參數(shù)：range

要計(jì)算其中非空單元格數(shù)目的區(qū)域；參數(shù)：criteria

以數(shù)字、表達(dá)式或文本形式定義的條件。例1：計(jì)算語數(shù)英成績(jī)?cè)?0分以下的學(xué)生人數(shù)。函數(shù)表達(dá)式：=COUNTIF(D2:D101,"<60")區(qū)域條件表達(dá)式1.條件計(jì)數(shù)函數(shù)：countif函數(shù)統(tǒng)計(jì)函數(shù)——計(jì)數(shù)統(tǒng)計(jì)多個(gè)區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)criteria_range1為第一個(gè)需要計(jì)算其中滿足某個(gè)條件的單元格數(shù)目的單元格區(qū)域（簡(jiǎn)稱條件區(qū)域）criteria1為第一個(gè)區(qū)域中將被計(jì)算在內(nèi)的條件（簡(jiǎn)稱條件），其形式可以為數(shù)字、表達(dá)式或文本同理，criteria_range2為第二個(gè)條件區(qū)域，criteria2為第二個(gè)條件，依次類推。最終結(jié)果為多個(gè)區(qū)域中滿足所有條件的單元格個(gè)數(shù)。例2：計(jì)算語數(shù)英成績(jī)?cè)?0-69分之間的學(xué)生人數(shù)。=COUNTIFS(D2:D101,">=60",D2:D101,"<=69")區(qū)域1條件1條件2區(qū)域22.計(jì)數(shù)函數(shù)：Countifs函數(shù)統(tǒng)計(jì)函數(shù)——計(jì)數(shù)實(shí)訓(xùn)任務(wù)一：條件計(jì)數(shù)函數(shù)打開工作簿“01：統(tǒng)計(jì)分析應(yīng)用.xlsx”；完成工作表“1.1”中的計(jì)算。5-1統(tǒng)計(jì)分析應(yīng)用”8分鐘統(tǒng)計(jì)函數(shù)——最值指定區(qū)域中最小數(shù)：MIN（range）指定區(qū)域中最大數(shù)：MAX（range）參數(shù)：range要計(jì)算最值的區(qū)域。例3：查找語數(shù)英成績(jī)最高分，最低分。函數(shù)表達(dá)式：=MAX(D2:D101)函數(shù)表達(dá)式：=MIN(D2:D101)區(qū)域3.最值函數(shù)：Min/Max函數(shù)統(tǒng)計(jì)函數(shù)——四舍五入返回某個(gè)數(shù)字按指定位數(shù)取整后的數(shù)字。ROUND(number，num_digits)Number：需要進(jìn)行四舍五入的數(shù)字。Num_digits：指定的位數(shù)，按此位數(shù)進(jìn)行四舍五入。例4：計(jì)算語數(shù)英成績(jī)平均分，并保留小數(shù)點(diǎn)1位。函數(shù)表達(dá)式：=ROUND(AVERAGE(D2:D101),1)取舍的數(shù)字位數(shù)4.取舍函數(shù)：Round函數(shù)統(tǒng)計(jì)函數(shù)——實(shí)訓(xùn)任務(wù)實(shí)訓(xùn)任務(wù)二：最值、均值、取舍打開工作簿“01：統(tǒng)計(jì)分析應(yīng)用.xlsx”；完成工作表“1.1”中的計(jì)算。8分鐘條件求和2統(tǒng)計(jì)函數(shù)——匯總根據(jù)指定條件對(duì)若干單元格求和。SUMIF(range,criteria,sum_range)Range：為用于條件判斷的單元格區(qū)域。Criteria：為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。sum_range：是需要求和的實(shí)際單元格。例5：求各班學(xué)生語數(shù)英總和。函數(shù)表達(dá)式：=SUMIF($A$2:$A$101,"信息1“/$I13,E2:E101)=SUMIF($A$2:$A$101,$I13,$E$2:$E$101)判斷區(qū)域條件實(shí)際區(qū)域1.單條件求和函數(shù)：Sumif函數(shù)統(tǒng)計(jì)函數(shù)——匯總根據(jù)多個(gè)指定條件對(duì)若干單元格求和。SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)1）criteria_range1為計(jì)算關(guān)聯(lián)條件的第一個(gè)區(qū)域。2）criteria1為條件1，條件的形式為數(shù)字、表達(dá)式、單元格引用或者文本，可用來定義將對(duì)criteria_range1參數(shù)中的哪些單元格求和。3）criteria_range2為用于條件2判斷的單元格區(qū)域。4）criteria2為條件2和3）均成對(duì)出現(xiàn)。最多允許127個(gè)區(qū)域、條件對(duì)，即參數(shù)總數(shù)不超255個(gè)。5）sum_range是需要求和的實(shí)際單元格。=SUMIFS($E:$E,$A:$A,$I19,$D:$D,"男")例6：求各班男女生語文分?jǐn)?shù)的總和。實(shí)際區(qū)域條件1區(qū)域條件2區(qū)域條件1條件22.多條件求和函數(shù)：Sumifs函數(shù)統(tǒng)計(jì)函數(shù)——匯總實(shí)訓(xùn)任務(wù)三：?jiǎn)螚l件求和函數(shù)打開工作表“01：統(tǒng)計(jì)分析應(yīng)用.xlsx”；完成工作表“2.1-2.2”中的計(jì)算。12分鐘條件平均值3平均值計(jì)算AverageifAverageifs條件平均值函數(shù)平均值計(jì)算1.單條件平均值函數(shù)：Aaverageif函數(shù)根據(jù)條件指定的單元格求平均值。AVERAGEIF(range,criteria,average_range)Range：為用于條件判斷的單元格區(qū)域。Criteria：為確定哪些單元格將求平均值的條件，其形式可以為數(shù)字、表達(dá)式或文本。average_range：是需要求平均值的實(shí)際單元格。例7：求各班語數(shù)英的平均分，并保留1位小數(shù)。=ROUND(AVERAGEIF($A:$A,$I25,E:E),1)條件區(qū)域條件實(shí)際區(qū)域平均值計(jì)算2.多條件平均值函數(shù)：Aaverageifs函數(shù)利用多重標(biāo)準(zhǔn)對(duì)滿足條件的單元格求平均值。AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)同sumifs函數(shù)例8：求各班男女生語文分?jǐn)?shù)的平均分，并保留1位小數(shù)。=AVERAGEIFS($E:$E,$A:$A,$I31,$D:$D,"男")=ROUND(AVERAGEIFS($E:$E,$A:$A,$I31,$D:$D,"男"),1)實(shí)際區(qū)域條件區(qū)域1條件區(qū)域2條件1條件2平均值計(jì)算實(shí)訓(xùn)任務(wù)四：?jiǎn)螚l件平均值函數(shù)打開工作簿“01：統(tǒng)計(jì)分析應(yīng)用.xlsx”；完成工作表“3.1-3.2”中的計(jì)算。15分鐘4排名統(tǒng)計(jì)排名統(tǒng)計(jì)Rank函數(shù)Large函數(shù)Small函數(shù)排名統(tǒng)計(jì)求某一個(gè)數(shù)值在某一區(qū)域內(nèi)的排名。RANK(number，ref，[order])number為需要求排名的那個(gè)數(shù)值或者單元格名稱（單元格內(nèi)必須為數(shù)字）；ref為排名的參照數(shù)值區(qū)域；order的為0和1，默認(rèn)不用輸入，得到的就是從大到小的排名，若是想求倒數(shù)第幾，order的值請(qǐng)使用1。例：求每個(gè)學(xué)生的排名。函數(shù)表達(dá)式：=RANK(D2,D:D)排名的數(shù)字參照數(shù)值區(qū)域1.排名函數(shù)：Rank函數(shù)排名統(tǒng)計(jì)返回?cái)?shù)據(jù)集中的第K個(gè)最大值。LARGE(array,k)array為需要找到第k個(gè)最大值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從大到小)。說明：LARGE函數(shù)計(jì)算最大值時(shí)忽略邏輯值TRUE和FALSE以及文本型數(shù)字例10：求十個(gè)最高分。=LARGE(排名函數(shù)!D:D,最值!A2)找的區(qū)域排名2.第K個(gè)最大值函數(shù)：Large函數(shù)排名統(tǒng)計(jì)返回?cái)?shù)據(jù)集中的第K個(gè)最小值。SMALL(array,k)array為需要找到第k個(gè)最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從小到大)。說明：函數(shù)計(jì)算最小值時(shí)忽略邏輯值TRUE和FALSE以及文本型數(shù)字。例：求十個(gè)最低分。=SMALL(排名函數(shù)!D:D,最值!A2)找的區(qū)域排名3.第K個(gè)最小值函數(shù)：Sma