《Excel數(shù)據(jù)處理與可視化》教案13-數(shù)學(xué)函數(shù)

《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í)際單元格])。其中，前兩個(gè)參數(shù)是必需的，第3個(gè)參數(shù)可選，如果第3個(gè)參數(shù)被省略了，則默認(rèn)的是對第1個(gè)參數(shù)單元格區(qū)域求和。二、UMIF函數(shù)應(yīng)用——非空值條件、排除錯(cuò)誤值、日期區(qū)間求和（1）統(tǒng)計(jì)非空值的種類對應(yīng)的銷量和，可用公式“=SUMIF(B2:B15,"*",C2:C15)”。其中，星號(*)是通配符，可以匹配任意一個(gè)串字符。（2）統(tǒng)計(jì)非空值的日期對應(yīng)的銷量和，可用公式“SUMIF(A2:A15,"<>",C2:C15)”。其中，“<>”表示非空值。（3）統(tǒng)計(jì)排除錯(cuò)誤值的總庫存，可用公式“=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é)果是兩個(gè)數(shù)：一個(gè)是“2017/3/20”以后非空值的日期對應(yīng)的銷量和（權(quán)且用Ａ代表這個(gè)數(shù)），另一個(gè)是“2017/3/25”以后非空值的日期對應(yīng)的銷量和（權(quán)且用Ｂ代表這個(gè)數(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ì)算滿足多個(gè)條件的全部參數(shù)的總量。語法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)中文語法：SUMIFS(求和單元格區(qū)域,條件單元格區(qū)域1,條件1,[條件單元格區(qū)域2,條件2],…)?！?/p>

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

criteria_rangel：必需，用來搜索條件1的單元格區(qū)域?！?/p>

criterial：必需，定義條件單元格區(qū)域1中單元格符合的條件。●

其后的參數(shù)：［條件單元格區(qū)域2,條件2］…是可以省略的。SUMIFS函數(shù)最多可以輸入127個(gè)單元格區(qū)域/條件對。五、SUMPRODUCT函數(shù)用法解析語法：SUMPRODUCT(array1,[array2],[array3],…)?！馻rray1：必需，其相應(yīng)元素要進(jìn)行相乘并求和的第一個(gè)數(shù)組參數(shù)。●[array2],[array3],…：可選，2～255個(gè)數(shù)組參數(shù)，其相應(yīng)元素要進(jìn)行相乘并求和。特別注意：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，SUMPRODUCT函數(shù)將返回錯(cuò)誤值?！镜湫蛻?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],…)。●

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

ref1：必需，要對其進(jìn)行分類匯總計(jì)算的第1個(gè)命名區(qū)域或引用。●

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

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

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

ref1：必需，要統(tǒng)計(jì)的數(shù)據(jù)單元格區(qū)域。當(dāng)在數(shù)組形式的使用中有k時(shí)，k代表要計(jì)算的2～253個(gè)數(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)用—非空值條件、排除錯(cuò)誤值、日期區(qū)間求和SUMIF函數(shù)應(yīng)用—隔列求和、查找與引用