《Excel數(shù)據(jù)處理與可視化》教案全套 韓春玲 1-32-數(shù)據(jù)錄入與基本設(shè)置 -附錄

《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)錄入與基本設(shè)置授課類型理實(shí)一體教學(xué)目標(biāo)1、熟悉數(shù)據(jù)錄入基本知識2、會使用特殊符號等進(jìn)行數(shù)據(jù)設(shè)置3、掌握常用的單元格設(shè)置方法重點(diǎn)1、數(shù)據(jù)格式特殊符號的使用2、單元格格式的設(shè)置難點(diǎn)1、數(shù)據(jù)特殊的對齊方式2、特殊計(jì)量單位使用教學(xué)過程一使用特殊符號自定義單元格格式1.“0”數(shù)字占位符當(dāng)使用“0”數(shù)字占位符自定義單元格格式時，如果單元格中的數(shù)字位數(shù)大于指定占位符的位數(shù)，則如實(shí)顯示該數(shù)字；如果該數(shù)字位數(shù)小于占位符的位數(shù)，則用0補(bǔ)足該數(shù)字位數(shù)為占位符的位數(shù)。2.“#”數(shù)字占位符當(dāng)使用“#”數(shù)字占位符自定義單元格格式時，只顯示數(shù)字有意義的零，不顯示數(shù)字無意義的零。在小數(shù)點(diǎn)后，數(shù)字位數(shù)如果大于“#”的位數(shù)，則按“#”的位數(shù)四舍五入。3.“?”數(shù)字占位符當(dāng)使用“?”數(shù)字占位符自定義單元格格式時，在小數(shù)點(diǎn)兩邊為無意義的零添加空格，以實(shí)現(xiàn)數(shù)字按小數(shù)點(diǎn)對齊。4.“,”千分位分隔符當(dāng)使用“,”千分位分隔符自定義單元格格式時，在數(shù)字中，每隔3位數(shù)加進(jìn)一個逗號，也就是千位分隔符，以免數(shù)字位數(shù)太多不好讀取。5.“@”文本占位符單個@的作用是引用固定文本。6.“!”原樣顯示后面的符號在單元格格式中，“、”“#”“？”等都是有特殊意義的字符。如果想在單元格中顯示這些字符，則要在符號前加“!”。如圖19所示，如果想在單元格中顯示“666"#”，可以自定義單元格格式為“#!"!#”（第一個“#”為數(shù)字占位符）。7.“*”重復(fù)后面的字符當(dāng)使用“*”重復(fù)后面的字符時，可以重復(fù)顯示“*”后面的字符，直到充滿整個單元格。8.顏色顯示符當(dāng)自定義單元格格式為“[紅色];[藍(lán)色];[黑色];[綠色]”時顯示結(jié)果如圖正數(shù)為紅色，負(fù)數(shù)顯示為藍(lán)色，零顯示為黑色，文本則顯示為綠色。二、設(shè)置數(shù)字以小數(shù)點(diǎn)對齊設(shè)置單元格格式，自定義單元格格式為“#.0?????”，即可得數(shù)字以小數(shù)點(diǎn)對齊的效果。#：保留原有整數(shù)位數(shù)。0：1位數(shù)。?：數(shù)字占位符，1個“？”占1位，5個“？”占5位。本示例數(shù)字中，最多的小數(shù)位數(shù)是6位，如果是整數(shù)，保留1位小數(shù)，所以寫成“#.0?????”；即使沒有6位小數(shù)，也要占6位小數(shù)的位置。三、單元格數(shù)據(jù)換行1）自動換行單擊“開始”→“自動換行”2）Alt+Enter組合鍵將光標(biāo)放在想要換行的地方，按Alt+Enter組合鍵即可實(shí)現(xiàn)換行，四、設(shè)置不能隔行或隔列填寫數(shù)據(jù)（1）單擊“數(shù)據(jù)驗(yàn)證”項(xiàng)。（2）設(shè)置驗(yàn)證條件。在打開的“數(shù)據(jù)驗(yàn)證”對話框的“設(shè)置”選項(xiàng)卡中，將“驗(yàn)證條件”的“允許”設(shè)為“自定義”，“公式”設(shè)為“=COUNTBLANK($A$2:A2)=0”。（3）設(shè)置出錯警告。選中“數(shù)據(jù)驗(yàn)證”對話框的“出錯警告”選項(xiàng)卡，勾選“輸入無效數(shù)據(jù)時顯示出錯警告”項(xiàng)，將“樣式”設(shè)為“停止”，“錯誤信息”設(shè)為“不能隔行或隔列填寫”。五、設(shè)置傾斜的列標(biāo)簽單擊“開始”菜單的“對齊方式”功能區(qū)中的“方向”按鈕，就可以沿對角或垂直方向旋轉(zhuǎn)文字，這是標(biāo)記窄列的好方式。六、給單元格數(shù)據(jù)加滾動條顯示（1）插入文本框控件。單擊“開發(fā)工具”→“插入”→“文本框（Activex控件）”，如圖128所示。（2）設(shè)置文本框控件屬性。右擊文本框控件，在彈出的快捷菜單中選擇“屬性”命令。其中，更改“MultiLine”屬性為“True”，即可實(shí)現(xiàn)自動換行；更改“ScrollBars”屬性為“2-fmScrollBarsVertical”，即可實(shí)現(xiàn)縱向滾動條，如圖129所示。（3）關(guān)閉設(shè)計(jì)模式，輸入文字。在“開發(fā)工具”菜單中，關(guān)閉“設(shè)計(jì)模式”，然后在文本框中輸入文字，當(dāng)文字不能完全顯示在當(dāng)前文本框內(nèi)時，即可自動出現(xiàn)滾動條，實(shí)現(xiàn)滾動顯示，如圖130所示。七、凍結(jié)窗格，輕松查看行、列數(shù)據(jù)1）凍結(jié)行凍結(jié)行的結(jié)果：不管在工作表中怎樣向下翻看數(shù)據(jù)，被凍結(jié)的行永遠(yuǎn)在數(shù)據(jù)的上方。凍結(jié)行的關(guān)鍵步驟：將光標(biāo)放在要被凍結(jié)行的下一行的第一個單元格，單擊“視圖”→“凍結(jié)窗格”→“凍結(jié)窗格”。當(dāng)然，如果凍結(jié)的是第一行，也可以直接選擇“視圖”→“凍結(jié)窗格”→“凍結(jié)首行”。凍結(jié)列的結(jié)果：不管在工作表中怎樣向右翻看數(shù)據(jù)，被凍結(jié)的列永遠(yuǎn)在數(shù)據(jù)的左側(cè)。當(dāng)然，如果凍結(jié)的是第一列，也可以直接選擇“視圖”→“凍結(jié)窗格”→“凍結(jié)首列”。3）凍結(jié)行和列想知道數(shù)據(jù)對應(yīng)的行、列的意義，最好是同時凍結(jié)行和列。凍結(jié)行和列的關(guān)鍵步驟：將光標(biāo)放在要被凍結(jié)行下、列右的第一個單元格，再單擊“視圖”→“凍結(jié)窗格”→“凍結(jié)窗格”八、輕松繪制單斜線、雙斜線表頭1）單斜線表頭在“月份”“部門”之間按Alt+Enter組合鍵，將它們分為兩行。在“月份”“銷量”前加空格以調(diào)整它們的位置。2）雙斜線表頭（1）在“月份”“銷量”“部門”之間按Alt+Enter組合鍵，將它們分成3行。在“月份”“銷量”前加空格以調(diào)整它們的位置。（2）在插入“直線”形狀時，要按住Alt鍵以繪制出與邊框等長的直線形狀，再調(diào)整直線形狀的高度與寬度，九、如何讓數(shù)字以“萬”為計(jì)數(shù)單位來顯示選擇單元格或區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在打開的“設(shè)置單元格格式”對話框的“數(shù)字”選項(xiàng)卡中選擇“自定義”命令，并在“類型”中輸入“0!.0,”，單擊“確定”按鈕，數(shù)字即可按“萬”位顯示。作業(yè)練習(xí)單元格設(shè)置教學(xué)小結(jié)本節(jié)知識：使用特殊符號自定義單元格格式設(shè)置數(shù)字以小數(shù)點(diǎn)對齊單元格數(shù)據(jù)換行設(shè)置不能隔行或隔列填寫數(shù)據(jù)設(shè)置傾斜的列標(biāo)簽給單元格數(shù)據(jù)加滾動條顯示凍結(jié)窗格，輕松查看行、列數(shù)據(jù)輕松繪制單斜線、雙斜線表頭如何讓數(shù)字以“萬”為計(jì)數(shù)單位來顯示設(shè)置僅能修改部分單元格數(shù)據(jù)隱藏工作表《Excel數(shù)據(jù)處理與可視化》課程教案授課題目合并單元格授課類型理實(shí)一體教學(xué)目標(biāo)批量合并、拆分單元格給合并單元格填充序號合并單元格計(jì)算合并單元格篩選合并單元格數(shù)據(jù)查詢重點(diǎn)1、合并單元格的使用2、單元格格式的設(shè)置難點(diǎn)1、合并單元格篩選、查詢教學(xué)過程1批量合并單元格關(guān)鍵操作：巧用分類匯總與定位批量合并單元格。（1）選中所有數(shù)據(jù)，選擇“數(shù)據(jù)”→“分類匯總”，在打開的“分類匯總”對話框中，將“分類字段”設(shè)為“部門”，“匯總方式”設(shè)為“計(jì)數(shù)”，在“選定匯總項(xiàng)”中勾選“部門”項(xiàng)。（2）選中B2:B28，按Ctrl+G組合鍵，打開“定位”對話框，單擊“定位條件”按鈕，在彈出的“定位條件”對話框的“選擇”中選擇“常量”項(xiàng)，如圖156、圖157所示。（3）選擇“開始”→“合并后居中”，彈出“合并單元格時，僅保留左上角的值，而放棄其他值”提示框，多次單擊“確定”按鈕，如圖158所示。（4）將光標(biāo)放在數(shù)據(jù)區(qū)，選擇“數(shù)據(jù)”→“刪除全部分類匯總”，在彈出的“分類匯總”對話框中單擊“全部刪除”按鈕，如圖159所示，然后刪除A列，即可完成相同內(nèi)容單元格的合并。2批量拆分合并單元格（1）選中所有合并單元格，單擊“合并后居中”按鈕，則會取消合并后居中，如圖160所示。（2）按Ctrl+G組合鍵，打開“定位”對話框，單擊“定位條件”按鈕，在“定位條件”對話框的“選擇”中選擇“空值”項(xiàng)，如圖161和圖162所示。（3）在A3單元格中輸入公式“=A2”，按Ctrl+Enter組合鍵執(zhí)行計(jì)算，如圖163所示。3給合并單元格填充序號選中整個合并單元格區(qū)域，輸入公式“=MAX($A$1:A1)+1”，按Ctrl+Enter組合鍵執(zhí)行計(jì)算。4合并單元格計(jì)算合并后的單元格大小不一，用“先計(jì)算出第一個合并單元格結(jié)果，再填充”的常規(guī)函數(shù)方法并不能實(shí)現(xiàn)批量計(jì)算。5合并單元格篩選圖168原數(shù)據(jù)圖169篩選結(jié)果6合并單元格數(shù)據(jù)查詢有合并單元格的數(shù)據(jù)表如圖179所示?！皞}庫”一列已按照倉庫名稱進(jìn)行了合并，要求可以根據(jù)給定的倉庫與商品，查詢出對應(yīng)的銷量。圖179有合并單元格的數(shù)據(jù)表作業(yè)合并單元格練習(xí)教學(xué)小結(jié)本節(jié)知識：批量合并單元格批量拆分合并單元格給合并單元格填充序號合并單元格計(jì)算合并單元格篩選合并單元格數(shù)據(jù)查詢《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)規(guī)范授課類型理實(shí)一體教學(xué)目標(biāo)利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入，包括規(guī)范的日期、時間、單位等利用函數(shù)規(guī)范數(shù)據(jù)重點(diǎn)1、利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入2、利用函數(shù)規(guī)范數(shù)據(jù)難點(diǎn)利用函數(shù)規(guī)范數(shù)據(jù)教學(xué)過程一利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入1規(guī)范性別輸入利用數(shù)據(jù)驗(yàn)證輸入性別，不僅規(guī)范而且快速。單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開的“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，在“來源”中選擇“男,女”。特別注意：（1）（2）只要對一個單元格設(shè)置了數(shù)據(jù)驗(yàn)證，就可通過鼠標(biāo)拖動單元格右下角填充柄，將數(shù)據(jù)驗(yàn)證的設(shè)置填充到其他單元格。2.限定輸入內(nèi)容在很多時候，要求只在某些特定值內(nèi)選擇輸入單元格的內(nèi)容。例如，如圖182所示的“評定等級”只有優(yōu)秀、良好、合格、不合格，所有姓名對應(yīng)的等級必須出自其中之一，而再無其他值，這時就可以利用數(shù)據(jù)驗(yàn)證來規(guī)范等級輸入。在“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，在“來源”中選擇4個等級所在的L2:L5區(qū)域，如圖182所示。設(shè)置了數(shù)據(jù)驗(yàn)證后，輸入等級時只要選擇其中之一就可以了。3.限定數(shù)值范圍可限定輸入數(shù)值范圍。（1）靜態(tài)限制輸入數(shù)值范圍。直接輸入“最大值”“最小值”，如圖183所示。（2）動態(tài)限制輸入數(shù)值范圍。在設(shè)置了數(shù)據(jù)驗(yàn)證以后，可以通過修改“最小值”“最大值”單元格的數(shù)值，動態(tài)調(diào)整數(shù)據(jù)的允許輸入數(shù)值范圍，如圖184所示。4.限定文本長度在“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“文本長度”，“數(shù)據(jù)”設(shè)為“等于”，“長度”設(shè)為“11”，如圖185所示。5.限制輸入重復(fù)值單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在彈出的“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“自定義”，在“公式”中輸入“=COUNTIF(H:H,H1)=1”，如圖1-86所示，單擊“確定”按鈕可以禁止輸入重復(fù)值。其中，公式的含義：H列中H1單元格的內(nèi)容只出現(xiàn)1次。如果H列中H1單元格的內(nèi)容出現(xiàn)次數(shù)超過1，則被禁止輸入。6.限定身份證號碼單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開的“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“自定義”，在“公式”中輸入“=AND(LEN(D1)=18,COUNTIF(D:D,D1&"*")=1)”，如圖187所示。二、設(shè)置只能輸入規(guī)范的日期（1）設(shè)置日期格式。選中要填充日期的單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在打開的“設(shè)置單元格格式”對話框中，將“類型”設(shè)置為要求輸入的格式，這里選擇常用的“*2012/3/14”格式，如圖189所示。（2）利用數(shù)據(jù)驗(yàn)證規(guī)范日期區(qū)間。選中要填充日期的單元格區(qū)域，在“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“日期”，“數(shù)據(jù)”設(shè)為“介于”，“開始日期”設(shè)為“2000/1/1”，“結(jié)束日期”設(shè)為“=today()”，如圖190所示。選中“出錯警告”選項(xiàng)卡，將“樣式”設(shè)為“停止”，“標(biāo)題”設(shè)為“請重新輸入：”，“錯誤信息”設(shè)為“請輸入格式如2012/03/14的日期，并且起始日期介于2000/1/1與今天之間?！?，如圖191所示。通過以上步驟的設(shè)置，單元格中就只允許輸入規(guī)范格式、特定區(qū)域的日期。三巧用數(shù)據(jù)驗(yàn)證規(guī)范時間格式（1）（2）單擊“數(shù)據(jù)”→“數(shù)據(jù)驗(yàn)證”，在打開的“數(shù)據(jù)驗(yàn)證”對話框中選擇“設(shè)置”選項(xiàng)卡，將“允許”設(shè)為“序列”，“來源”設(shè)為“=$E$2”，如圖1-93所示。在“輸入信息”選項(xiàng)卡中，在“輸入信息”中輸入“請輸入當(dāng)前時間”，如圖194所示。四數(shù)量單位“分”屬于文本，不能參與計(jì)算。所以，在寫公式時，首先要把單位去除。去除數(shù)量單位“分”文本要用SUBSTITUTE函數(shù)，即{=SUBSTITUTE(B2:B10,"分",)}。因?yàn)橐M(jìn)行的是數(shù)組計(jì)算，所以按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算。五一鍵添加“能計(jì)算”的數(shù)量單位選中數(shù)據(jù)單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，打開“設(shè)置單元格格式”對話框。在“分類”中選擇“自定義”，直接在“類型”的“G/通用格式”后輸入數(shù)量單位六使用多級聯(lián)動菜單規(guī)范數(shù)據(jù)輸入（1）分級數(shù)據(jù)整理。（2）自定義名稱。（3）建立各級菜單。七處理不能計(jì)算的“數(shù)值”1）選擇性粘貼2）數(shù)據(jù)分列3）VALUE函數(shù)4）SUMPRODUCT函數(shù)八規(guī)范全角、半角數(shù)據(jù)8規(guī)范全角、半角數(shù)據(jù)（1）在E3單元格中輸入公式“=ASC(D3)”，按Enter鍵執(zhí)行計(jì)算，然后將公式向下填充。ASC函數(shù)的作用是將全角（雙字節(jié)）字符轉(zhuǎn)換成半角（單字節(jié)）字符，如圖1-118所示。（2）在F3單元格中輸入公式“=WIDECHAR(D3)”，按Enter鍵執(zhí)行計(jì)算，然后將公式向下填充。WIDECHAR函數(shù)的作用是將半角（單字節(jié)）字符轉(zhuǎn)換成全角（雙字節(jié)）字符，如圖1-119所示。九數(shù)字與文本分離的方法1）函數(shù)法2）分列法3）快速填充法作業(yè)練習(xí)利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入教學(xué)小結(jié)本節(jié)知識：利用數(shù)據(jù)驗(yàn)證（數(shù)據(jù)有效性）規(guī)范數(shù)據(jù)輸入設(shè)置只能輸入規(guī)范的日期巧用數(shù)據(jù)驗(yàn)證規(guī)范時間格式數(shù)據(jù)輸入不規(guī)范，部分?jǐn)?shù)據(jù)帶數(shù)量單位，此時怎么計(jì)算平均值一鍵添加“能計(jì)算”的數(shù)量單位使用多級聯(lián)動菜單規(guī)范數(shù)據(jù)輸入處理不能計(jì)算的“數(shù)值”規(guī)范全角、半角數(shù)據(jù)數(shù)字與文本分離的方法LOOKUP+FIND函數(shù)組合規(guī)范標(biāo)準(zhǔn)名稱給同一單元格的姓名和電話號碼中間加分隔符號《Excel數(shù)據(jù)處理與可視化》課程教案授課題目行列設(shè)置授課類型理實(shí)一體教學(xué)目標(biāo)快速刪除空白行插入行或刪除行后，都可自動填寫序號數(shù)據(jù)轉(zhuǎn)置與跳過單元格復(fù)制最快捷的一列轉(zhuǎn)多列方式聚光燈效果（閱讀模式）改變當(dāng)前行和列的顏色將同部門員工姓名合并到同一單元格或分行顯示重點(diǎn)1、空白行處理2、行列轉(zhuǎn)置難點(diǎn)1、有附加條件的行列轉(zhuǎn)置教學(xué)過程一快速刪除空白行（1）純空白行，無其他空白單元格。當(dāng)數(shù)據(jù)區(qū)除了有整行空白，沒有其他空白單元格時，采用快速刪除空白行的方法為：按Gtrl+G組合鍵，打開“定位”對話框。單擊“定位條件”按鈕，打開“定位條件”對話框，“選擇”欄下選擇“空值”項(xiàng)，單擊“確定”按鈕后，右擊，在彈出的快捷菜單中選擇“刪除”→“整行”，（2）既有空白行，又有空白單元格。如果數(shù)據(jù)區(qū)域除了空白行，還有空白單元格，采用正確的方法是：建立輔助列；在H2單元格中輸入公式“=COUNTA(A2:G2)，按Enter鍵執(zhí)行計(jì)算，然后將公式向下填充，如圖1134所示；篩選出輔助列中為0的行，并選中；按Alt+；組合鍵，顯示可見行，右擊，在彈出的快捷菜單中選擇“刪除行”，如圖1135所示。二、插入行或刪除行后，都可自動填寫序號插入行或刪除行后，都可自動填寫序號1）ROW函數(shù)2）ROW函數(shù)+表格3）SUBTOTAL函數(shù)三、數(shù)據(jù)轉(zhuǎn)置與跳過單元格復(fù)制（1）選擇性粘貼，數(shù)據(jù)轉(zhuǎn)置的使用。（2）跳過單元格復(fù)制。四、最快捷的一列轉(zhuǎn)多列方式（1）將數(shù)據(jù)復(fù)制到C列。（2）在D1單元格中輸入公式“=C6”，按Enter鍵執(zhí)行計(jì)算。因?yàn)閺腃6開始名字將另起一列顯示，如圖1-153所示。五、設(shè)置傾斜的列標(biāo)簽單擊“開始”菜單的“對齊方式”功能區(qū)中的“方向”按鈕，就可以沿對角或垂直方向旋轉(zhuǎn)文字，這是標(biāo)記窄列的好方式。（3）公式向下填充，再向右填充，在C1:G5區(qū)域會囊括原來一整列分布的數(shù)據(jù)。（4）選中D1:G5區(qū)域，復(fù)制，再進(jìn)行選擇性粘貼，用數(shù)值覆蓋原有的公式。（5）刪除C1:G5區(qū)域以外的數(shù)據(jù)。五、聚光燈效果（閱讀模式）改變當(dāng)前行和列的顏色（1）條件格式設(shè)計(jì)顏色。（2）顏色隨單元格改變而移動。（3）工作簿的保存。六、將同部門員工姓名合并到同一單元格（1）先按照部門進(jìn)行排序。（2）在D2單元格中輸入公式“=IF(A2=A3,B2&","&D3,B2)”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，得到的結(jié)果如圖1159所示。（3）將結(jié)果復(fù)制，并進(jìn)行選擇性粘貼，只保留數(shù)值，放到C列相應(yīng)的位置，并刪除D列。（4）在D2單元格中輸入公式“=COUNTIF($A$2:A2,A2）”，再將公式向下填充，得到的結(jié)果是每個部門中的第一行編號都是1，即姓名最全的一行，如圖1160所示。七、將同一單元格的同部門員工姓名分行顯示將同一單元格的同部門員工姓名分行顯示（1）打開查詢編輯器。（2）姓名的分列顯示。單擊“各部門所有人員”列標(biāo)簽，選擇“轉(zhuǎn)換”→“拆分（3）逆透視列。按住Ctrl鍵，選中所有的姓名列，然后選擇“轉(zhuǎn)換”→“逆透視列”，作業(yè)閱讀模式設(shè)置教學(xué)小結(jié)本節(jié)知識：快速刪除空白行插入行或刪除行后，都可自動填寫序號數(shù)據(jù)轉(zhuǎn)置與跳過單元格復(fù)制最快捷的一列轉(zhuǎn)多列方式聚光燈效果（閱讀模式）改變當(dāng)前行和列的顏色將同部門員工姓名合并到同一單元格將同一單元格的同部門員工姓名分行顯示《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)維度轉(zhuǎn)換、數(shù)據(jù)格式轉(zhuǎn)換授課類型理實(shí)一體教學(xué)目標(biāo)1、目錄的建立方法2、鏈接到另一張表的方法3、數(shù)據(jù)舍入4、數(shù)字大小寫轉(zhuǎn)換重點(diǎn)1、數(shù)據(jù)鏈接2、目錄制作難點(diǎn)1、數(shù)據(jù)鏈接的方式教學(xué)過程一使用函數(shù)建立目錄A1單元格上，選擇“公式”“定義名稱”，在打開的“新建名稱”對話框的“名稱”框中輸入“目錄”，在“引用位置”中輸入公式“=INDEX(GET.WORKBOOK(1),ROW(A1))&T(NOW())”在A1單元格中輸入公式“=IFERROR(HYPERLINK(目錄&"!A1",MID(目錄,FIND("]",目錄)+1,99)),"")”在目錄工作表以外的合適位置，輸入公式“=HYPERLINK("#目錄!A1","返回目錄")，即可得到“返回目錄”的鏈接二、不使用函數(shù)建立目錄（1）選定所有工作表。在工作表標(biāo)簽上面，右擊，在彈出的快捷菜單中選擇“選定全部工作表”命令，如圖1178所示（2）輸入公式。此時，所有工作表處于選定狀態(tài)，在A1單元格中輸入“=XFD1”，按Enter鍵執(zhí)行計(jì)算，此函數(shù)返回值為“0”，如圖1179所示。Excel2003及之前的版本文件有256（2的8次方）列，即到IV列；Excel2007及以后的版本有16384（2的14次方）列，即到XFD列。這里引用XFD1單元格的數(shù)值，為0。（3）自動生成“兼容性報(bào)表”。單擊“文件”→“信息”→“檢查問題”→“檢查兼容性”，如圖1180所示。（4）復(fù)制目錄到“目錄”工作表。復(fù)制“兼容性報(bào)表”中的目錄到“目錄”工作表中，如圖1183所示。三、鏈接到另一張表的4種方法1）文字2）形狀3）圖標(biāo)4）ActiveX控件四、單擊訂單名稱，即可跳到訂單詳情工作表公式“=HYPERLINK("#訂單詳情!A"&MATCH(C2,訂單詳情!A:A,0),訂單詳情!A3)”五、數(shù)值取整的9種方法1）INT函數(shù)取整2）TRUNC函數(shù)取整3）ROUND函數(shù)小數(shù)取整4）ROUND函數(shù)整數(shù)取整5）ROUNDUP函數(shù)向上舍入取整6）ROUNDDOWN函數(shù)向下舍入取整7）MROUND函數(shù)按指定基數(shù)向上舍入取整8）CEILING函數(shù)按指定基數(shù)向上舍入取整9）FLOOR函數(shù)按指定基數(shù)向下舍入取整六、數(shù)值的特殊舍入方式1）舍入到偶數(shù)2）舍入到奇數(shù)七、NUMBERSTRING函數(shù)和TEXT函數(shù)1）阿拉伯?dāng)?shù)字轉(zhuǎn)中文數(shù)字2）中文數(shù)字轉(zhuǎn)為阿拉伯?dāng)?shù)字八、怎么計(jì)算長短不一的文本算式結(jié)果（1）選項(xiàng)設(shè)置。單擊“文件”→“選項(xiàng)”→“高級”，勾選“轉(zhuǎn)換Lotus1-2-3公式”項(xiàng)，（2）數(shù)據(jù)分列。復(fù)制B2:B8區(qū)域到C2:C8區(qū)域，如圖1212所示。選中C2:C8區(qū)域，單擊“數(shù)據(jù)”→“分列”按鈕，在打開的“文本分列向?qū)?第1步”對話框中不做特殊修改，直接單擊“完成”按鈕，如圖1213所示。（3）選項(xiàng)設(shè)置。單擊“文件”→“選項(xiàng)”→“高級”→“轉(zhuǎn)換Lotus1-2-3公式”，將其前面的鉤去除，如圖1215所示。作業(yè)練習(xí)大小寫數(shù)值轉(zhuǎn)換教學(xué)小結(jié)本節(jié)知識：1.5數(shù)據(jù)維度轉(zhuǎn)換使用函數(shù)建立目錄不使用函數(shù)建立目錄鏈接到另一張表的4種方法單擊訂單名稱，即可跳到訂單詳情工作表1.6數(shù)據(jù)格式轉(zhuǎn)換數(shù)值取整的9種方法數(shù)值的特殊舍入方式NUMBERSTRING函數(shù)和TEXT函數(shù)怎么計(jì)算長短不一的文本算式結(jié)果阿拉伯?dāng)?shù)字（小寫）轉(zhuǎn)為中文數(shù)字（大寫）來表示人民幣的金額《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)篩選、排序排名授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握數(shù)據(jù)篩選、高級篩選、多條件篩選方法2、學(xué)會篩選后的序號填充、公式篩選等知識3、掌握各種排序與排名的方法重點(diǎn)1、高級篩選2、多種排序與排名難點(diǎn)1、排除特殊值的排序與排名教學(xué)過程一數(shù)據(jù)篩選基礎(chǔ)（1）篩選指定某值。（2）排除指定值篩選。（3）高于指定值篩選。（4）篩選指定范圍數(shù)值。（5）篩選前幾位數(shù)值。（6）高于平均值篩選。二、高級篩選高級篩選的關(guān)鍵點(diǎn)：（1）必須按照篩選要求自己寫一個條件區(qū)域。（2）高級篩選的原數(shù)據(jù)區(qū)域第一行（列標(biāo)簽）一定要包含條件區(qū)域的第一行（列標(biāo)簽）。（3）高級篩選條件區(qū)域，一定要注意各條件之間的關(guān)系。三、篩選符合條件的兩種方法1）自動換行單擊“開始”→“自動換行”2）Alt+Enter組合鍵將光標(biāo)放在想要換行的地方，按Alt+Enter組合鍵即可實(shí)現(xiàn)換行，四、不用公式的跨表查詢（1）寫條件。（2）高級篩選。在要放結(jié)果的“查詢”工作表中，選擇“數(shù)據(jù)”→“排序和篩選”，高級篩選設(shè)置較簡單，可以選擇指定姓名購買記錄，但姓名一旦更改，須要重新進(jìn)行篩選。五、CELL+SUMIF函數(shù)組合使隱藏列不參與匯總（1）建立輔助行。在第11行建立輔助行，B11單元格中輸入公式“=CELL("width",B1)”，然后向右填充，計(jì)算出B列到G列各列的列寬，如圖1244所示。（2）函數(shù)實(shí)現(xiàn)。先在H2單元格中輸入公式“=SUMIF($B$11:$G$11,">0",B2:G2)”，再向下填充到H8，如圖1245所示。六、序號經(jīng)過篩選后仍然不亂可以用SUBTOTAL函數(shù)實(shí)現(xiàn)無論如何篩選，序號總保持連續(xù)。在G2單元格中輸入公式“=SUBTOTAL(3,$H$2:H2)*1”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，得到的新序號代替原來的數(shù)字序號，即可實(shí)現(xiàn)篩選后序號有序，如圖1248和圖1249所示。七、多個排序條件的排序（1）將光標(biāo)放在數(shù)據(jù)區(qū)，單擊“數(shù)據(jù)”→“排序”（2）在打開的“排序”對話框中，單擊“添加條件”按鈕，可以添加很多排序條件，（3）如果想要按指定順序排序，則可以自定義排序序列，就像本示例中按指定部門順八、輕松繪制單斜線、雙斜線表頭九、數(shù)據(jù)按行排序選定除了行標(biāo)題（首列）的所有數(shù)據(jù)，單擊“數(shù)據(jù)”→“排序”，在打開的“排序”對話框中單擊“選項(xiàng)”按鈕，在打開的“排序選項(xiàng)”對話框中選中“按行排序”選項(xiàng)。（2）選擇“主要關(guān)鍵字”為消費(fèi)記錄所在“行2”，“次序”為“降序”；單擊“添加條件”按鈕，添加“次要關(guān)鍵字“選擇消費(fèi)方式所在的“行4”，根據(jù)要求選擇“升序”或者“降序”，如圖1255所示；單擊“確定”按鈕，即實(shí)現(xiàn)按行排序。十、數(shù)據(jù)按行排序（1）將光標(biāo)放在要排序的列時，單擊“數(shù)據(jù)”→“排序”，在打開的“排序”對話框中選擇“自定義序列”，如圖1258所示。（2）在打開的“自定義序列”對話框的“輸入序列”中輸入“見習(xí)、助理、初級、中級、高級、特級”，如圖1259所示。（3）單擊“確定”按鈕，依次返回，即可實(shí)現(xiàn)職務(wù)由低到高排序。十一、剔除0值排名次，升序、降序由你定公式“=IF(B2=0,"",RANK(B2,$B$2:$B$16))十二、RANK.EQ函數(shù)引用合并區(qū)域，實(shí)現(xiàn)多列數(shù)據(jù)排名公式“=RANK.EQ(B3,($B$3:$B$11,$E$3:$E$11,$H$3:$H$11,$K$3:$K$11))十三、SUMPRODUCT函數(shù)實(shí)現(xiàn)中式排名公式“=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))作業(yè)練習(xí)單元格設(shè)置教學(xué)小結(jié)本節(jié)知識：數(shù)據(jù)篩選基礎(chǔ)、高級篩選篩選符合條件的兩種方法不用公式的跨表查詢CELL+SUMIF函數(shù)組合使隱藏列不參與匯總序號經(jīng)過篩選后仍然不亂多個排序條件的排序數(shù)據(jù)按行排序數(shù)據(jù)按自定義序列排序剔除0值排名次，升序、降序由你定只給有銷量的產(chǎn)品添加序號RANK.EQ函數(shù)引用合并區(qū)域，實(shí)現(xiàn)多列數(shù)據(jù)排名SUMPRODUCT函數(shù)實(shí)現(xiàn)中式排名SUMPRODUCT函數(shù)實(shí)現(xiàn)分組排名利用公式自動生成字母序列數(shù)據(jù)透視表實(shí)現(xiàn)排名《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)據(jù)去重復(fù)、工作表工作薄合并授課類型理實(shí)一體教學(xué)目標(biāo)1、熟悉數(shù)據(jù)去重復(fù)的方法2、多個工作簿、工作表合并、匯總與拆分重點(diǎn)1、熟悉數(shù)據(jù)去重復(fù)的方法2、多個工作簿、工作表合并、匯總與拆分難點(diǎn)1、多個工作簿、工作表合并、匯總與拆分教學(xué)過程一刪除重復(fù)項(xiàng)刪除重復(fù)值，不僅可以對單列數(shù)據(jù)進(jìn)行刪除，還可以對多列數(shù)據(jù)同時進(jìn)行操作。在“刪除重復(fù)值”對話框中勾選多列（多列之間都是“AND”的關(guān)系），數(shù)據(jù)同時重復(fù)的行，才能被刪除。二、計(jì)算報(bào)名人數(shù)公式“=SUMPRODUCT(1/COUNTIF($A$2:$A$16,$A$2:$A$16))”，即可統(tǒng)計(jì)出報(bào)名人數(shù)，如圖1295所示。-圖1295計(jì)算共有多少人報(bào)名三、利用COUNT+MATCH函數(shù)組合統(tǒng)計(jì)兩列重復(fù)值公式“=COUNT(MATCH(A2:A11,B2:B11,0))”，按Ctrl+Shift+Enter組合鍵，即可計(jì)算出兩個季度同時進(jìn)入前10名的人數(shù)，如圖1297所示。圖1297兩個季度都進(jìn)入前10名的人數(shù)四、利用EXACT函數(shù)設(shè)置條件格式，標(biāo)記兩組不同的數(shù)據(jù)（1）選中A2:A21區(qū)域，選擇“開始”→“條件格式”→“新建規(guī)則”。（2）在“編輯格式規(guī)則”對話框中，選擇“選擇規(guī)則類型”的“使用公式確定要設(shè)置格式的單元格”項(xiàng)，并在“為符合此公式的值設(shè)置格式”中輸入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，如圖1300所示。（3）單擊“格式”按鈕，在彈出的“設(shè)置單元格格式”對話框中選中“填充”選項(xiàng)卡，選擇一種背景顏色，單擊“確定”按鈕，如圖1301所示。五、利用數(shù)據(jù)查詢功能實(shí)現(xiàn)多個工作表合并（1）打開查詢編輯器。（2）追加查詢。（3）在“查詢編輯器”中，單擊“開始”→“關(guān)閉并上載”，關(guān)閉“查詢編輯器”。六、利用PowerQuery編輯器實(shí)現(xiàn)多個工作簿合并與刷新Excel2016提供了強(qiáng)大的PowerQuery編輯器即數(shù)據(jù)查詢功能，可以不用費(fèi)時費(fèi)力粘貼、不用專業(yè)的SQL查詢語句，只要單擊幾下，就能完成多工作簿的數(shù)據(jù)匯總。當(dāng)然，中間要輸入一個非常簡單的小公式。七、多個工作表數(shù)據(jù)匯總八、對于結(jié)構(gòu)一致的多個工作表，合并計(jì)算是最好的匯總方法九、多個工作表，不用合并，直接查詢作業(yè)練習(xí)單元格設(shè)置教學(xué)小結(jié)本節(jié)知識：刪除重復(fù)項(xiàng)計(jì)算報(bào)名人數(shù)利用COUNT+MATCH函數(shù)組合統(tǒng)計(jì)兩列重復(fù)值利用EXACT函數(shù)設(shè)置條件格式，標(biāo)記兩組不同的數(shù)據(jù)利用數(shù)據(jù)查詢功能實(shí)現(xiàn)多個工作表合并利用PowerQuery編輯器實(shí)現(xiàn)多個工作簿合并與刷新多個工作表數(shù)據(jù)匯總對于結(jié)構(gòu)一致的多個工作表，合并計(jì)算是最好的匯總方法多個工作表，不用合并，直接查詢《Excel數(shù)據(jù)處理與可視化》課程教案授課題目圖片處理授課類型理實(shí)一體教學(xué)目標(biāo)批量導(dǎo)入文件名工作表中批量插入照片在批注中插入圖片圖片放在文件夾里，Excel也能查看一次為上千幅圖片重命名利用Excel照相機(jī)自動匹配圖片重點(diǎn)1、圖片名種處理方式2、文件重命名難點(diǎn)1、插入與讀取圖片2、文件重命名教學(xué)過程一批量導(dǎo)入文件名（1）生成包含圖片名稱的文本文檔。（2）生成Excel文件。二、工作表中批量插入照片公式“="<table><imgsrc=""E:\圖片\"&B2&".jpg""width=""100""height=""100"">"”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，如圖1365所示。三、在批注中插入圖片（1）（2）選中批注邊框，右擊，在彈出的快捷菜單中選擇“設(shè)置批注格式”，如圖1372所示。圖1371插入批注圖1372設(shè)置批注格式（3）在彈出“設(shè)置批注格式”對話框的“顏色與線條”選項(xiàng)卡中，選擇“填充”的“顏色”為“填充效果”命令，單擊“確定”按鈕，如圖1373所示。（4）在彈出“填充效果”對話框的“圖片”選項(xiàng)卡中，單擊“選擇圖片”按鈕，如圖1374所示。（5）單擊“瀏覽”按鈕，如圖1375所示。（6）在彈出的“選擇圖片”對話框中，選中相應(yīng)圖片，單擊“插入”按鈕，即可完成四、圖片放在文件夾里，Excel也能查看（1）（2）復(fù)制文件夾路徑。選擇放置圖片的文件夾，按住Shfit鍵，同時右擊，在彈出的快捷菜單中選擇“復(fù)制為路徑”命令，即可將圖片文件夾路徑復(fù)制，如圖1378所示。圖1378復(fù)制為路徑（3）在C2單元格中輸入公式“=HYPERLINK("E:\圖片素材\"&B2&".jpg",B2)”，其中“E:\圖片素材\”為圖片文件夾路徑，按Enter鍵執(zhí)行計(jì)算，可以得到第一幅圖片鏈接，再將公式向下填充，即可得到所有圖片的鏈接，如圖1379所示。圖1379輸入公式五、一次為上千幅圖片重命名六、利用Excel照相機(jī)自動匹配圖片（1）打開“照相機(jī)”功能。圖片匹配功能，須要用到“照相機(jī)”功能。該功能的調(diào)用方式是：單擊“開始”→“選項(xiàng)”→“自定義功能區(qū)”→“不在功能區(qū)的命令”，將“照相機(jī)”添加到主選項(xiàng)卡中的某菜單中，如圖1387所示，這里將它添加到“開始”菜單的“新建”功能組內(nèi)。（2）給圖片拍照。選中C列任意圖片所在單元格（一定是單元格，而不是圖片本身），單擊“照相機(jī)”按鈕，然后在要匹配圖片的F2單元格處按住鼠標(biāo)左鍵拖動，畫出照片，如圖1388所示。（3）定義名稱。單擊“公式”→“定義名稱”。在打開的“編輯名稱”對話框中“名稱”輸入“tupian”，“引用位置”輸入公式“=INDIRECT("c"&MATCH(苗木生長習(xí)性!$E$2,苗木生長習(xí)性!$B:$B,0))”，如圖1389所示。（4）輸入名稱。選中F2單元格中的圖片（照相機(jī)貼出的照片），在地址欄中輸入公式“=tupian”，即可完成圖片的匹配，在E2單元格中選擇任意名稱，F(xiàn)2單元格中即出現(xiàn)該名稱對應(yīng)的圖片，如圖1390所示。作業(yè)一次為上千幅圖片重命名教學(xué)小結(jié)本節(jié)知識：批量導(dǎo)入文件名工作表中批量插入照片在批注中插入圖片圖片放在文件夾里，Excel也能查看一次為上千幅圖片重命名利用Excel照相機(jī)自動匹配圖片《Excel數(shù)據(jù)處理與可視化》課程教案授課題目公式綜述授課類型理實(shí)一體教學(xué)目標(biāo)必須知道的公式基礎(chǔ)知識公式中常出現(xiàn)的錯誤代碼及修正方法使用“追蹤錯誤”對公式進(jìn)行檢查將公式保護(hù)起來數(shù)組公式重點(diǎn)1、數(shù)組公式2、公式基礎(chǔ)知識難點(diǎn)1、數(shù)組公式教學(xué)過程一必須知道的公式基礎(chǔ)知識1.運(yùn)算符Excel運(yùn)算符有數(shù)學(xué)運(yùn)算符、邏輯運(yùn)算符、引用運(yùn)算符、文本運(yùn)算符。2.運(yùn)算符優(yōu)先級公式中同時用到了多個運(yùn)算符，Excel將按下面的順序進(jìn)行運(yùn)算。（1）如果公式中包含了相同優(yōu)先級的運(yùn)算符，Excel將從左到右進(jìn)行計(jì)算。（2）如果公式中有不同優(yōu)先級的運(yùn)算符，則運(yùn)算符的計(jì)算順序從高到低依次為：引用運(yùn)算符（：）、負(fù)號（-）、百分比（%）、乘方（^）、乘和除（*和/）、加和減（+和-）、連接符（&）、比較運(yùn)算符。如果要修改計(jì)算順序，則應(yīng)把公式中要先計(jì)算的部分括在圓括號內(nèi)。3.相對引用與絕對引用（1）概念?！馎1：相對引用?！?A$1：絕對引用?！?A1：列絕對引用，行相對引用。●A$1：行絕對引用，列相對引用。（2）用途?！裥星疤砑?：在復(fù)制（填充）公式時，行數(shù)不發(fā)生變化。●列前添加$：在復(fù)制（填充）公式時，列數(shù)不發(fā)生變化。（3）F4鍵（用于引用方式之間切換的快捷鍵）。●按1次：絕對引用?！癜?次：對行絕對引用、對列相對引用。●按3次：對行相對引用、對列絕對引用?！癜?次：相對引用。4.公式排查錯誤在公式返回值出現(xiàn)錯誤時，可以單擊“公式”→“公式求值”來查找錯誤5.公式填充將公式由一個單元格拖到另一個單元格稱為公式填充。6.公式批量填充選取要輸入公式的單元格區(qū)域，在編輯欄中輸入公式7.只保留公式單元格的數(shù)值為了防止數(shù)據(jù)隨源數(shù)據(jù)改變，或者復(fù)制數(shù)據(jù)后出現(xiàn)錯誤，可以通過“選擇性粘貼”選項(xiàng)只保留公式單元格的數(shù)值8.公式顯示如果想要看到詳細(xì)的公式，可以單擊“公式”→“顯示公式”9.顯示另一個單元格公式如果要在別的單元格顯示某單元格的公式，可使用FORMULATEXT函數(shù)，如圖41310.隱藏公式如果不想讓別人看到你的公式，可以將公式隱藏。11.保護(hù)公式選中非公式單元格區(qū)域，取消單元格鎖定，不必操作公式單元格區(qū)域，再設(shè)置保護(hù)工作表，這樣操作以后，公式單元格就不會被選中了。12.顯示公式部分計(jì)算結(jié)果選取公式中要顯示的部分表達(dá)式，按F9鍵，即可顯示公式部分計(jì)算結(jié)果。13.中斷公式編輯有時在公式編輯狀態(tài)下，不小心單擊到了其他單元格，出現(xiàn)取消不掉公式編輯的情況。此時，應(yīng)該選擇公式編輯欄左側(cè)的紅色“×”，強(qiáng)行停止公式編輯。二、公式中常出現(xiàn)的錯誤代碼及修正方法錯誤值含義解決辦法＃＃＃＃1.單元格數(shù)據(jù)太長2.公式單元格所產(chǎn)生的結(jié)果太大，在單元格中無法完整顯示3.日期和時間格式的單元格相減，出現(xiàn)了負(fù)值1.增加列寬2.如果是由日期或時間相減產(chǎn)生了負(fù)值引起的，可以改變單元格的格式為文本，但結(jié)果只能是負(fù)時間量＃DIV/0!1.除數(shù)為02.公式中除數(shù)使用了空單元格或包含零值單元格的引用1.修改單元格引用2.在做除數(shù)的單元格中輸入不為零的值＃VALUE!1.數(shù)值運(yùn)算時引用文本型的數(shù)據(jù)2.使用了不正確的參數(shù)或運(yùn)算符3.當(dāng)執(zhí)行自動更正公式功能時，不能更正公式1.更正相關(guān)的數(shù)據(jù)類型或參數(shù)類型2.提供正確的參數(shù)＃REF!刪除了被公式引用的單元格范圍恢復(fù)被引用的單元格范圍，或者重新設(shè)定引用范圍＃N/A查找或引用函數(shù)中找不到匹配的值檢查被查找的值，使其存在于查找的數(shù)據(jù)區(qū)域＃NAME?在公式中使用了Excel所不能識別的文本，例如：1.輸錯了函數(shù)名稱2.使用了已刪除的單元格區(qū)域或名稱3.引用的文本沒有加英文雙引號1.改正函數(shù)名稱2.修改引用的單元格區(qū)域或名稱3.引用的文本加英文雙引號＃NUM!1.函數(shù)參數(shù)無效2.公式的結(jié)果太大或太小，無法在工作表中表示1.確認(rèn)函數(shù)中使用的參數(shù)類型正確2.修改公式，找到錯誤原因并更正＃NULL！1.使用了不正確的區(qū)域運(yùn)算符2.引用的單元格區(qū)域的交集為空1.改正區(qū)域運(yùn)算符2.更改引用的單元格區(qū)域，使之相交三、使用“追蹤錯誤”對公式進(jìn)行檢查（1）將光標(biāo)放在公式單元格中，單擊“公式”→“錯誤檢查”→“追蹤錯誤”之后，可以發(fā)現(xiàn)，Excel自動用箭頭標(biāo)出了錯誤來源。（2）將光標(biāo)放在箭頭的“源頭”，左側(cè)會出現(xiàn)錯誤提示按鈕（），單擊錯誤提示下拉箭頭，可看到錯誤原因與改正方法。四、將公式保護(hù)起來（1）所有單元格去除“鎖定”。（2）保護(hù)和隱藏公式單元格。（3）設(shè)置保護(hù)工作表。五、數(shù)組公式1.數(shù)組概念什么是數(shù)組？數(shù)組就是具有某種聯(lián)系的多個單元格數(shù)據(jù)的組合2.數(shù)組的維度“維度”是數(shù)組的一個重要概念。數(shù)組有一維數(shù)組、二維數(shù)組、三維數(shù)組、四維數(shù)組……在公式里，應(yīng)用更多的是一維數(shù)組和二維數(shù)組。一維數(shù)組可以簡單地被看成一行或一列單元格數(shù)據(jù)集合，如圖4-30中B2:B8單元格區(qū)域的一組數(shù)據(jù)、E2:K2單元格區(qū)域的一組數(shù)據(jù)都是一維數(shù)組。二維數(shù)組可以看成一個多行多列的單元格數(shù)據(jù)集合，也可以看成多個一維數(shù)組的組合，但在公式中，數(shù)組的描述如下。一維數(shù)組：單獨(dú)一行的數(shù)組元素之間用半角逗號隔開；單獨(dú)一列的數(shù)組元素之間用半角分號隔開。二維數(shù)組：同行的數(shù)組元素間用半角逗號分隔，不同行的數(shù)組元素間用半角分號分隔。如果要看一個數(shù)組中的數(shù)組元素，可以將該數(shù)組元素選中，然后按F9鍵，即可在地址欄中顯示該數(shù)組元素3.數(shù)組公式數(shù)組公式與普通公式有以下幾點(diǎn)不同。（1）執(zhí)行計(jì)算方式不同：普通公式按Enter鍵結(jié)束，數(shù)組公式按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算。（2）公式顯示方式不同：整個數(shù)組公式外圍有一對{}。（3）結(jié)果顯示位置不同：普通公式的結(jié)果顯示在一個單元格里；數(shù)組公式的結(jié)果可以顯示在一個單元格里，也可以顯示在多個單元格里。（4）公式修改方式不同：選中普通公式所在單元格即可修改普通公式；數(shù)組公式，尤其是結(jié)果顯示在多個單元格里的數(shù)組公式，必須選中其結(jié)果單元格區(qū)域進(jìn)行修改，并按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算才能被修改，否則會出現(xiàn)“無法更改部分?jǐn)?shù)組?！钡奶崾究?。六、數(shù)組公式——應(yīng)用初步1.行列數(shù)相同的數(shù)組運(yùn)算1）行數(shù)相同的數(shù)組相乘運(yùn)算2）列數(shù)相同的數(shù)組相乘運(yùn)算切記：●輸入公式之前要選中所有填結(jié)果的單元格區(qū)域?！裰惠斎雸?zhí)行計(jì)算的公式部分。2.數(shù)組與單一數(shù)據(jù)運(yùn)算3.單列數(shù)組與單行數(shù)組運(yùn)算單列數(shù)組與單行數(shù)組的計(jì)算規(guī)律：●計(jì)算結(jié)果返回一個多行多列的二維數(shù)組?！穹祷囟S數(shù)組的行數(shù)同單列數(shù)組的行數(shù)相同、列數(shù)同單行數(shù)組的列數(shù)相同。●二維數(shù)組各單元格結(jié)果分別是單行數(shù)組與單列數(shù)組每個數(shù)組元素互相運(yùn)算得出的。單列數(shù)組與單行數(shù)組的計(jì)算規(guī)律如圖444所示。4.單行或單列數(shù)組與多行多列數(shù)組運(yùn)算單行或單列數(shù)組與多行多列數(shù)組運(yùn)算規(guī)律：●結(jié)果返回一個多行多列的二維數(shù)組?！穹祷?cái)?shù)組的行、列數(shù)與多行多列數(shù)組的行、列數(shù)相同?！駟瘟袛?shù)組與多行多列數(shù)組運(yùn)算時，返回單列數(shù)組與二維數(shù)組對應(yīng)行所有列數(shù)據(jù)的運(yùn)算結(jié)果?！駟涡袛?shù)組與多行多列數(shù)組運(yùn)算時，返回單行數(shù)組與二維數(shù)組對應(yīng)列所有行數(shù)據(jù)的運(yùn)算結(jié)果。5.行、列數(shù)相等的二維數(shù)組運(yùn)算七、數(shù)組公式——典型應(yīng)用1.“繞過”乘積直接求和2.計(jì)算連續(xù)數(shù)值和、平均值3.計(jì)算不同產(chǎn)品種類數(shù)4.多條件運(yùn)算5.構(gòu)建新數(shù)組運(yùn)算。作業(yè)練習(xí)數(shù)組公式教學(xué)小結(jié)本節(jié)知識：必須知道的公式基礎(chǔ)知識公式中常出現(xiàn)的錯誤代碼及修正方法使用“追蹤錯誤”對公式進(jìn)行檢查將公式保護(hù)起來數(shù)組公式—基礎(chǔ)知識數(shù)組公式—應(yīng)用初步數(shù)組公式—典型應(yīng)用《Excel數(shù)據(jù)處理與可視化》課程教案授課題目統(tǒng)計(jì)函數(shù)授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握MODE.MULT函數(shù)、COUNTIF函數(shù)、“IFS”結(jié)尾、COUNTIFS函數(shù)、TRIMMEAN函數(shù)等統(tǒng)計(jì)函數(shù)的用法；2、會靈活選用統(tǒng)計(jì)函數(shù)重點(diǎn)1、統(tǒng)計(jì)函數(shù)的選用常識2、多個函數(shù)的組合難點(diǎn)1、多個函數(shù)的組合教學(xué)過程一利用MODE.MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（一）公式“=MODE.MULT($A$2:$G$12)”，按Enter鍵執(zhí)行計(jì)算，即可得出現(xiàn)次數(shù)最多的數(shù)值。二、利用MODE.MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（二）公式“=IFERROR(INDEX(MODE.MULT($A$1:$E$10),ROW(A1)),"")”，按Enter鍵執(zhí)行計(jì)算，即得出現(xiàn)次數(shù)最多的數(shù)值，三、利用COUNTIF函數(shù)給眾多班級中相同班級的學(xué)生編號公式“=COUNTIF($B$2:B2,B2)”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充就得到所有班級的學(xué)生編號。四、5個常用的“IFS”結(jié)尾的多條件統(tǒng)計(jì)函數(shù)1）COUNTIFS函數(shù)：多條件計(jì)數(shù)語法：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：COUNTIFS(條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●條件區(qū)域1：必需，在其中計(jì)算關(guān)聯(lián)條件的第1個區(qū)域。●條件1：必需，條件的形式為數(shù)字、表達(dá)式、單元格引用或文本。它定義了要計(jì)數(shù)的單元格范圍。例如，條件可以表示為32、">32"、B4、"apples"或"32"?！裨诒臼纠校y(tǒng)計(jì)市場1部業(yè)績分高于10的女高級工程師人數(shù)，有以下4個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：業(yè)績分；條件2：高于10?！駰l件區(qū)域3：性別；條件3：女。●條件區(qū)域4：職稱；條件4：高級工程師。所以，公式為“=COUNTIFS(A2:A21,"市場1部",E2:E21,">=10",C2:C21,"女",D2:D21,"高級工程師")”。2）SUMIFS函數(shù)：多條件統(tǒng)計(jì)和語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：SUMIFS(統(tǒng)計(jì)和的數(shù)值區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●統(tǒng)計(jì)和的數(shù)值區(qū)域：必需，要計(jì)算和的一個或多個單元格，其中包含數(shù)字或包含數(shù)字的名稱、數(shù)組、引用。●條件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計(jì)算關(guān)聯(lián)條件的1～127個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達(dá)式、單元格引用或文本的1～127個條件，用來定義要統(tǒng)計(jì)和的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計(jì)市場1部女高級工程師業(yè)績總分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部。●條件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。3）AVERAGEIFS函數(shù)：多條件統(tǒng)計(jì)平均值語法：AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：AVERAGEIFS(統(tǒng)計(jì)平均值區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●統(tǒng)計(jì)平均值區(qū)域：必需，要計(jì)算平均值的一個或多個單元格，其中包含數(shù)字或數(shù)字的名稱、數(shù)組、引用?！駰l件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計(jì)算關(guān)聯(lián)條件的1～127個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達(dá)式、單元格引用或文本的1～127個條件，用來定義要計(jì)算平均值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計(jì)市場1部女高級工程師平均業(yè)績分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：性別；條件2：女。●條件區(qū)域3：職稱；條件3：高級工程師。所以，公式為“=AVERAGEIFS(E2:E21,A2:A21,"市場1部",C2:C21,"女",D2:D21,"高級工程師")”。4）MAXIFS函數(shù)：多條件統(tǒng)計(jì)最大值語法：MAXIFS(max_range，criteria_range1，criteria1，[criteria_range2，criteria2],…)中文語法：MAXIFS(取最大值的單元格區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●取最大值的單元格區(qū)域：必需，要取最大值的一個或多個單元格，其中包含數(shù)字或數(shù)字的名稱、數(shù)組、引用。●條件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計(jì)算關(guān)聯(lián)條件的1～126個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達(dá)式、單元格引用或文本的1～126個條件，用來定義取最大值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計(jì)市場1部女高級工程師最高業(yè)績得分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部?！駰l件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。5）MINIFS函數(shù)：多條件統(tǒng)計(jì)最小值語法：MINIFS(min_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文語法：MINIFS(取最小值的單元格區(qū)域,條件區(qū)域1,條件1,[條件區(qū)域2,條件2],…)●取最小值的單元格區(qū)域：必需，要取最小值的一個或多個單元格，包含數(shù)字或數(shù)字的名稱、數(shù)組、引用。●條件區(qū)域1、條件區(qū)域2等：條件區(qū)域1必需，后續(xù)條件區(qū)域是可選的。在其中計(jì)算關(guān)聯(lián)條件的1～126個區(qū)域?！駰l件1、條件2等：條件1必需，后續(xù)條件是可選的。形式為數(shù)字、表達(dá)式、單元格引用或文本的1～126個條件，用來定義取最小值的單元格。例如，條件可以表示為32、"32"、">32"、"蘋果"或B4。在本示例中，要統(tǒng)計(jì)市場1部女高級工程師最低業(yè)績得分，有以下3個條件對?！駰l件區(qū)域1：市場部；條件1：市場1部。●條件區(qū)域2：性別；條件2：女?！駰l件區(qū)域3：職稱；條件3：高級工程師。五、利用SUM+COUNTIF函數(shù)組合統(tǒng)計(jì)不重復(fù)值的個數(shù)在E2單元格中輸入公式“=SUM(1/COUNTIF(B2:B16,B2:B16))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算，即可統(tǒng)計(jì)出銷售的商品種數(shù)所示。六、利用FREQUENCY函數(shù)分段計(jì)數(shù)輸入公式“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，按Ctrl+Shift+Enter組合鍵執(zhí)行計(jì)算，即可計(jì)算每個分?jǐn)?shù)段的人數(shù)。七、利用COUNTIFS函數(shù)統(tǒng)計(jì)滿足多個條件的單元格數(shù)量在F2單元格中輸入公式“=COUNTIFS($A$2:$A$135,G$1,$C$2:$C$135,">="&$F2)”，按Enter鍵執(zhí)行計(jì)算，再將公式向下、向右填充，即可得到每個班級各分?jǐn)?shù)段的人數(shù)。八、利用TRIMMEAN函數(shù)計(jì)算去掉最高分和最低分后的平均分，左側(cè)公式為“TRIMMEAN（B3:B8,2/9）”，表示在9個裁判員打分中，去掉一個最高分和一個最低分后計(jì)算平均分；右側(cè)公式為“TRIMMEAN(F3:F8,4/9)”，表示在9個裁判員打分中，去掉兩個最高分和兩個最低分后計(jì)算平均分。作業(yè)練習(xí)統(tǒng)計(jì)函數(shù)的使用教學(xué)小結(jié)本節(jié)知識：利用MODE.MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（一）利用MODE.MULT函數(shù)統(tǒng)計(jì)出現(xiàn)最多的數(shù)字（二）利用COUNTIF函數(shù)給眾多班級中相同班級的學(xué)生編號5個常用的“IFS”結(jié)尾的多條件統(tǒng)計(jì)函數(shù)利用SUM+COUNTIF函數(shù)組合統(tǒng)計(jì)不重復(fù)值的個數(shù)利用FREQUENCY函數(shù)分段計(jì)數(shù)利用COUNTIFS函數(shù)統(tǒng)計(jì)滿足多個條件的單元格數(shù)量利用TRIMMEAN函數(shù)計(jì)算去掉最高分和最低分后的平均分《Excel數(shù)據(jù)處理與可視化》課程教案授課題目文本函數(shù)授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握常用的文本函數(shù)的使用方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點(diǎn)1、常用文本函數(shù)的使用2、根據(jù)場景靈活選用函數(shù)難點(diǎn)1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一MID+FIND函數(shù)組合提取括號內(nèi)數(shù)據(jù)公式“=MID(A2,FIND("(",A2)+1,FIND(")",A2)-1-FIND("(",A2))”二、TEXT——超級好用的文本函數(shù)TEXT函數(shù)功能：TEXT函數(shù)主要通過格式代碼對數(shù)字應(yīng)用格式，從而更改數(shù)字的顯示方式。語法：TEXT(Value,Format_text)?！馰alue：數(shù)值，或是計(jì)算結(jié)果為數(shù)值的公式，也可以是對包含數(shù)值單元格的引用?！馞ormat_text：文本形式的數(shù)字格式。注意：●text返回的一律是文本型數(shù)據(jù)。如果要計(jì)算，可以先將文本轉(zhuǎn)換為數(shù)值，然后再計(jì)算?！裎谋拘蛿?shù)據(jù)遇到四則運(yùn)算會被自動轉(zhuǎn)為數(shù)值?！竦谋静粫⑴cSUM之類的函數(shù)運(yùn)算?！镜湫蛻?yīng)用】1）格式日期2）格式時間3）千分位分隔符4）格式數(shù)字、貨幣5）加前導(dǎo)符0補(bǔ)充位數(shù)6）百分比7）特殊格式8）條件區(qū)段判斷9）自定義條件區(qū)段三、&——文本連接符的使用文本連接符（&）是把兩個或多個單元格的內(nèi)容連在一起，寫在同一個單元格里的最簡單的方式?！?”在連接單元格數(shù)據(jù)時，不管單元格格式為文本型還是數(shù)值型，都能實(shí)現(xiàn)單元格數(shù)據(jù)連接，但得到的結(jié)果都是文本型數(shù)字。在使用“&”時，還要注意數(shù)據(jù)格式等問題?！镜湫蛻?yīng)用】1）基本用法2）合并后換行3）合并帶格式的內(nèi)容4）合并列實(shí)現(xiàn)多條件查找四、CONCATENATE文本連接函數(shù)CONCATENATE函數(shù)功能：將兩個或多個文本字符串連接為一個字符串文本。語法：CONCATENATE(text1,[text2],…)?！駎ext1：必需，要連接的第一個項(xiàng)目。項(xiàng)目可以是文本、數(shù)字或單元格引用?！駎ext2,…：可選，要連接的其他文本項(xiàng)目。最多可以有255個項(xiàng)目，總共最多支持8192個字符。【典型應(yīng)用】1）基本用法2）合并后換行3）合并帶格式的內(nèi)容五、CONCAT文本連接函數(shù)CONCAT函數(shù)功能：將單元格區(qū)域文本（或單個文本）連接為一個字符串。中文語法：CONCAT（文本1，[文本2，…])?！?/p>

●[文本2，…]：【典型應(yīng)用】1）合并單元格區(qū)域2）加分隔符合并單元格區(qū)域3）條件篩選單元格區(qū)域合并七、TEXTJOIN文本連接函數(shù)TEXTJOIN函數(shù)功能：將多個單元格區(qū)域的文本字符串結(jié)合在一起，包括每個文本之間的分隔符。中文語法：TEXTJOIN（分隔符,是否忽略空白單元格,文本1,[文本2,…])?！穹指舴罕匦?，可以是文本字符串，或者為空，或者用雙引號引起來的一個或多個字符，或者對有效文本字符串的引用。如果提供一個數(shù)字，則將被視為文本?！袷欠窈雎钥瞻讍卧瘢罕匦?，如果為TRUE（或1），則忽略空白單元格。●文本1：必需，要連接的文本項(xiàng)，如文本字符串或字符串?dāng)?shù)組?！馵文本2,…]：可選，要連接的其他文本項(xiàng)，可以有252個文本項(xiàng)。每個文本項(xiàng)可以是一個文本字符串或字符串?dāng)?shù)組?！镜湫蛻?yīng)用】1）同行單元格區(qū)域合并2）行列單元格區(qū)域合并八、CLEAN函數(shù)清除非打印字符CLEAN函數(shù)功能：刪除文本中所有不能打印的字符。對從其他應(yīng)用程序?qū)氲奈谋?，使用CLEAN函數(shù)將刪除其中含有當(dāng)前操作系統(tǒng)無法打印的字符。例如，可以使用CLEAN函數(shù)刪除某些通常出現(xiàn)在數(shù)據(jù)文件開頭和結(jié)尾處且無法打印的低級計(jì)算機(jī)代碼。語法：CLEAN(text)。中文語法：CLEAN（文本）。參數(shù)：文本必需，要從中刪除非打印字符的任何工作表信息。CLEAN函數(shù)不僅可以清除格式符號，還能清除單元格的設(shè)置。九、SUBSTITUTE文本替換函數(shù)的使用SUBSTITUTE函數(shù)功能：在某個文本字符串中替換指定的文本。語法：SUBSTITUTE(text,old_text,new_text,[instance_num])?！裎谋荆罕匦?，要替換其中字符的文本，或者對含有文本（要替換其中字符）的單元格的引用?！駉ld_text：必需，要替換的文本。●new_text：必需，用于替換old_text?！駃nstance_num：可選，指定要用new_text替換old_text。如果指定了instance_num，則只有滿足要求的old_text被替換。否則，文本中出現(xiàn)的所有old_text都會被更改為new_text。使用SUBSTITUTE函數(shù)的特點(diǎn)如下。●區(qū)分大小寫和全/半角：當(dāng)text中沒有包含old_text指定的字符串時，該函數(shù)結(jié)果與text相同?！癞?dāng)?shù)?個參數(shù)為空文本或被省略，而只保留參數(shù)前的逗號時，相當(dāng)于將text中包含old_text指定的字符串去掉?！癞?dāng)?shù)?個參數(shù)被省略，text中與old_text相同的文本將被替換?！袢绻?個參數(shù)有指定，如“2”，則只有第2次出現(xiàn)的old_text被替換。【典型應(yīng)用】1）統(tǒng)一替換部分字符2）計(jì)算字符串內(nèi)特定字符的個數(shù)3）帶單位的數(shù)值計(jì)算4）同一單元格中的最大值十REPLACE函數(shù)——隱藏身份證號碼的部分?jǐn)?shù)字REPLACE函數(shù)含義：用新字符串替換舊字符串。語法：=Replace(old_text,start_num,num_chars,new_text)。中文語法：=Replace(要替換的字符串,開始位置,替換個數(shù),新的文本)。第4個參數(shù)是文本，要加上引號。作業(yè)練習(xí)單TEXT函數(shù)教學(xué)小結(jié)本節(jié)知識：MID+FIND函數(shù)組合提取括號內(nèi)數(shù)據(jù)TEXT—超級好用的文本函數(shù)&—文本連接符的使用CONCATENATE文本連接函數(shù)CONCAT文本連接函數(shù)TEXTJOIN文本連接函數(shù)CLEAN函數(shù)清除非打印字符SUBSTITUTE文本替換函數(shù)的使用REPLACE函數(shù)—隱藏身份證號碼的部分?jǐn)?shù)字《Excel數(shù)據(jù)處理與可視化》課程教案授課題目時間與日期函數(shù)授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握常用的時間與日期函數(shù)的使用方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點(diǎn)1、常用時間與日期函數(shù)的使用2、根據(jù)場景靈活選用函數(shù)難點(diǎn)1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一根據(jù)身份證號碼計(jì)算退休日期功能：返回表示日期，該日期與指定日期（start_date）相隔（之前或之后）指定的月份數(shù)。語法：EDATE(start_date,months)。中文語法：EDATE（開始日期，月份數(shù)）。●start_date：必需，代表開始日期。應(yīng)使用DATE函數(shù)輸入日期，或者將其他公式或函數(shù)的結(jié)果作為日期。例如，DATE(2008,5,23)輸入的日期是2008年5月23日。如果start_date不是有效日期，則EDATE返回錯誤值“#VALUE!”?！駇onths：必需，start_date之前或之后的月份數(shù)。months為正值將生成未來日期，為負(fù)值將生成過去日期，如果months不是整數(shù)，則將其舍去小數(shù)部分取整。Excel可將日期存儲為可用于計(jì)算的序列號。在默認(rèn)情況下，1900年1月1日的序列號是1，而2008年1月1日的序列號是39448，這是因?yàn)榫?900年1月1日有39448天。二、根據(jù)續(xù)費(fèi)月數(shù)計(jì)算到期日DATE函數(shù)功能：返回表示特定日期的連續(xù)序列號。語法：DATE(year,month,day)。中文語法：DATE(年,月,日)?！?/p>

year：必需，年份，year參數(shù)的值可以包含1～4位數(shù)字?！?/p>

month：必需，1個正整數(shù)或負(fù)整數(shù)，表示1年中從1～12月的各月?！?/p>

day：必需，1個正整數(shù)或負(fù)整數(shù)，表示1月中從1～31日的各天。三、利用DATEDIF函數(shù)計(jì)算精確到年、月、天的賬齡DATEDIF函數(shù)功能：用于計(jì)算兩個日期之間的天數(shù)、月數(shù)和年數(shù)。語法：DATEDIF(start_date,end_date,unit)。中文語法：DATEDIF(起始日期,結(jié)束日期,時間代碼)?！駍tart_date（參數(shù)1）：表示起始日期。●end_date（參數(shù)2）：表示結(jié)束日期?！駏nit（參數(shù)3）：為所需信息返回時間的單位代碼。各單位代碼含義如下。●"y"：返回時間段中的整年數(shù)；●"m"：返回時間段中的整月數(shù)；●"d"：返回時間段中的天數(shù)；●"md"：參數(shù)1和參數(shù)2的天數(shù)之差，忽略年和月?！?ym"：參數(shù)1和參數(shù)2的月數(shù)之差，忽略年和日。●"yd"：參數(shù)1和參數(shù)2的天數(shù)之差，忽略年，按照月、日計(jì)算天數(shù)。四、利用WORKDAY函數(shù)計(jì)算幾個工作日之后的日期WORKDAY函數(shù)返回在某日期（起始日期）之前或之后、與該日期相隔指定工作日數(shù)的某個日期。工作日不包括周末和專門指定的假日。在計(jì)算發(fā)票到期日、預(yù)期交貨時間或工作天數(shù)時，可以使用函數(shù)WORKDAY來扣除周末或假日。語法：WORKDAY(start_date,days,[holidays])。中文語法：WORKDAY(開始日期,天數(shù),[排除的一個或多個日期])?！駍tart_date：必需，開始日期?！馾ays：必需，start_date之前或之后不含周末及節(jié)假日的天數(shù)。days為正值生成未來日期；days為負(fù)值生成過去日期。●holidays：可選，一個可選列表，其中包含從工作日歷中排除的一個或多個日期。五、CONCAT文本連接函數(shù)CONCAT函數(shù)功能：將單元格區(qū)域文本（或單個文本）連接為一個字符串。中文語法：CONCAT（文本1，[文本2，…])。●

●[文本2，…]：【典型應(yīng)用】1）合并單元格區(qū)域2）加分隔符合并單元格區(qū)域3）條件篩選單元格區(qū)域合并七、利用EOMONTH函數(shù)取某月的最后一天EOMONTH函數(shù)功能：返回某個月份的最后一天。語法：EOMONTH(start_date,months)。中文語法：EOMONTH(日期,月份數(shù))?！?/p>

start_date：日期，必需，且一定是日期格式的?！?/p>

months：月份數(shù)，必需，是指日期之前或之后的月份數(shù)。它為正值則生成未來日期；它為負(fù)值則生成過去日期；它為0則是本月。months不同值返回的結(jié)果如圖4146所示?！?/p>

dAY：返回日期所在月的天數(shù)，是從1～31之間的整數(shù)。八、利用NETWORKDAYS.INTL函數(shù)計(jì)算工作日NETWORKDAYS.INTL函數(shù)功能：計(jì)算兩個日期之間的工作日數(shù)。語法：NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays])。中文語法：NETWORKDAYS.INTL(起始日期,結(jié)束日期,[周末數(shù)或字符串],[節(jié)假日])?！駍tart_date和end_date：必需，要計(jì)算其差值的日期。start_date可以早于或晚于end_date，也可以與其相同?！駑eekend：可選，表示介于start_date和end_date之間，但又不包括非工作日數(shù)。weekend是一個用于指定非工作日的周末數(shù)或字符串?！駂olidays：可選，一組可選的日期，表示要從工作日中排除的一個或多個日期。holidays應(yīng)是一個包含相關(guān)日期的單元格區(qū)域，或者是一個由表示這些日期序列值構(gòu)成的數(shù)組常量。作業(yè)時間與日期函數(shù)練習(xí)教學(xué)小結(jié)本節(jié)知識：根據(jù)身份證號碼計(jì)算退休日期根據(jù)續(xù)費(fèi)月數(shù)計(jì)算到期日利用DATEDIF函數(shù)計(jì)算精確到年、月、天的賬齡利用WORKDAY函數(shù)計(jì)算幾個工作日之后的日期利用EOMONTH函數(shù)取某月的最后一天利用NETWORKDAYS.INTL函數(shù)計(jì)算工作日《Excel數(shù)據(jù)處理與可視化》課程教案授課題目數(shù)學(xué)函數(shù)授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握常用的數(shù)學(xué)函數(shù)的使用方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點(diǎn)1、常用數(shù)學(xué)函數(shù)的使用2、根據(jù)場景靈活選用函數(shù)難點(diǎn)1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一SUMIF函數(shù)應(yīng)用——單條件、多條件、模糊條件求和SUMIF條件求和函數(shù)語法：SUMIF(range,criteria,[sum_range])。中文語法：SUMIF(根據(jù)條件進(jìn)行計(jì)算的單元格區(qū)域,單元格求和的條件,[求和的實(shí)際單元格])。其中，前兩個參數(shù)是必需的，第3個參數(shù)可選，如果第3個參數(shù)被省略了，則默認(rèn)的是對第1個參數(shù)單元格區(qū)域求和。二、UMIF函數(shù)應(yīng)用——非空值條件、排除錯誤值、日期區(qū)間求和（1）統(tǒng)計(jì)非空值的種類對應(yīng)的銷量和，可用公式“=SUMIF(B2:B15,"*",C2:C15)”。其中，星號(*)是通配符，可以匹配任意一個串字符。（2）統(tǒng)計(jì)非空值的日期對應(yīng)的銷量和，可用公式“SUMIF(A2:A15,"<>",C2:C15)”。其中，“<>”表示非空值。（3）統(tǒng)計(jì)排除錯誤值的總庫存，可用公式“=SUMIF(D2:D15,"<9e307")”。其中，9E307也可寫為9E+307（以科學(xué)計(jì)數(shù)法表示），是Excel能接受的數(shù)字最大值。在Excel中，經(jīng)常用9E+307代表最大數(shù)，這是約定俗成的。（4）統(tǒng)計(jì)2017年3月20日至2017年3月25日的總銷量和，可以用公式“=SUM(SUMIF(A2:A15,{">=2017/3/20",">2017/3/25"},C2:C15)*{1,-1})”。其中，“SUMIF(A2:A15,{">=2017/3/20",">2017/3/25"},C2:C15)”的結(jié)果是兩個數(shù)：一個是“2017/3/20”以后非空值的日期對應(yīng)的銷量和（權(quán)且用Ａ代表這個數(shù)），另一個是“2017/3/25”以后非空值的日期對應(yīng)的銷量和（權(quán)且用Ｂ代表這個數(shù)）?！?SUM(SUMIF(A2:A15,{">=2017/3/20",">2017/3/25"},C2:C15)*{1,-1})”可以表示為“＝SUM({A,B}*{1,-1})”，即Ａ?Ｂ，也就是“2017/3/20”以后非空值的日期對應(yīng)的銷量和?“2017/3/25”以后非空值的日期對應(yīng)的銷量和”，就是最終所求2017年3月20日至2017年3月25日的總銷量。三、SUMIF函數(shù)應(yīng)用——隔列求和、查找與引用公式“=SUMIF($B$2:$G$2,H$2,$B3:$G3)”四、SUMIFS函數(shù)多字段、多條件求和公式“=SUMIFS(D2:D15,B2:B15,"寧波倉",C2:C15,"鼠標(biāo)",D2:D15,">1000",D2:D15,"<5000")”SUMIFS函數(shù)功能：用于計(jì)算滿足多個條件的全部參數(shù)的總量。語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)中文語法：SUMIFS(求和單元格區(qū)域,條件單元格區(qū)域1,條件1,[條件單元格區(qū)域2,條件2],…)。●

sum_range：必需，要求和的單元格區(qū)域。●

criteria_rangel：必需，用來搜索條件1的單元格區(qū)域。●

criterial：必需，定義條件單元格區(qū)域1中單元格符合的條件?！?/p>

其后的參數(shù)：［條件單元格區(qū)域2,條件2］…是可以省略的。SUMIFS函數(shù)最多可以輸入127個單元格區(qū)域/條件對。五、SUMPRODUCT函數(shù)用法解析語法：SUMPRODUCT(array1,[array2],[array3],…)?！馻rray1：必需，其相應(yīng)元素要進(jìn)行相乘并求和的第一個數(shù)組參數(shù)。●[array2],[array3],…：可選，2～255個數(shù)組參數(shù)，其相應(yīng)元素要進(jìn)行相乘并求和。特別注意：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，SUMPRODUCT函數(shù)將返回錯誤值?！镜湫蛻?yīng)用】1）基本用法2）單條件求和3）多條件求和4）模糊條件求和5）單條件計(jì)數(shù)6）多條件計(jì)數(shù)7）模糊條件計(jì)數(shù)8）按月份統(tǒng)計(jì)數(shù)據(jù)9）跨列統(tǒng)計(jì)10）多權(quán)重統(tǒng)計(jì)11）二維單元格區(qū)域統(tǒng)計(jì)七、SUMPRODUCT函數(shù)的注意事項(xiàng)八、利用SUBTOTAL函數(shù)實(shí)現(xiàn)忽略隱藏行統(tǒng)計(jì)SUBTOTAL函數(shù)語法：SUBTOTAL(function_num,ref1,[ref2],…)?！?/p>

function_num：必需，數(shù)字1～11或101～111，指定分類匯總所使用的函數(shù)。如果使用1～11，將包括手動隱藏的行；如果使用101～111，則排除手動隱藏的行。始終排除已篩選掉的單元格?！?/p>

ref1：必需，要對其進(jìn)行分類匯總計(jì)算的第1個命名區(qū)域或引用?！?/p>

其他參數(shù)：可選，要對其進(jìn)行分類匯總計(jì)算的第2～254個命名區(qū)域或引用。。九、AGGREGATE函數(shù)——忽略錯誤值計(jì)算的萬能函數(shù)功能：返回列表或數(shù)據(jù)庫中的合計(jì)，可忽略隱藏行和錯誤值的選項(xiàng)。引用形式：AGGREGATE(function_num,options,ref1,[ref2],…)。數(shù)組形式：AGGREGATE(function_num,options,array,[k])?！?/p>

function_num：必需，一個1～19的數(shù)字。每個數(shù)字指定要使用的函數(shù)，如圖4179所示。在本示例中，用公式“=AGGREGATE(9,6,B2:B13)”代替求和函數(shù)“=SUM(B2:B13)”，其中第1個參數(shù)“9”代表求和?！?/p>

options：必需，一個數(shù)值，代表在函數(shù)的計(jì)算單元格區(qū)域內(nèi)要忽略的內(nèi)容。Options不同數(shù)值代表在函數(shù)計(jì)算單元格區(qū)域要忽略的內(nèi)容如圖4180所示。在本示例中，所有的AGGREGATE函數(shù)第2個參數(shù)都是“6”，即隱藏錯誤值。●

ref1：必需，要統(tǒng)計(jì)的數(shù)據(jù)單元格區(qū)域。當(dāng)在數(shù)組形式的使用中有k時，k代表要計(jì)算的2～253個數(shù)值參數(shù)。對于“=AGGREGATE(15,6,B2:B13,2)”，function_num是15，k是2，就是統(tǒng)計(jì)第2小的數(shù)值。作業(yè)數(shù)學(xué)函數(shù)練習(xí)教學(xué)小結(jié)本節(jié)知識：SUMIF函數(shù)應(yīng)用—單條件、多條件、模糊條件求和SUMIF函數(shù)應(yīng)用—非空值條件、排除錯誤值、日期區(qū)間求和SUMIF函數(shù)應(yīng)用—隔列求和、查找與引用SUMIFS函數(shù)多字段、多條件求和SUMPRODUCT函數(shù)用法解析SUMPRODUCT函數(shù)的注意事項(xiàng)利用SUBTOTAL函數(shù)實(shí)現(xiàn)忽略隱藏行統(tǒng)計(jì)AGGREGATE函數(shù)—忽略錯誤值計(jì)算的萬能函數(shù)ROUND函數(shù)對數(shù)據(jù)四舍五入QUOTIENT函數(shù)與TRUNC函數(shù)—截去小數(shù)，保留整數(shù)《Excel數(shù)據(jù)處理與可視化》課程教案授課題目查找與引用函數(shù)（一）授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握常用的查找與引用函數(shù)的使用方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點(diǎn)1、常用查找與引用函數(shù)的使用2、根據(jù)場景靈活選用函數(shù)難點(diǎn)1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一VLOOKUP函數(shù)應(yīng)用之基礎(chǔ)—基本查找VLOOKUP函數(shù)功能：依據(jù)給定的查閱值，在一定的數(shù)據(jù)單元格區(qū)域中，返回與查閱值對應(yīng)的想要查找的值。中文語法