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

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

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

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

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

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

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

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

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

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

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

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

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

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

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