《Excel数据处理与可视化》教案13-数学函数

《Excel数据处理与可视化》课程教案授课题目数学函数授课类型理实一体教学目标1、掌握常用的数学函数的使用方法2、会根据应用场景选择合适的函数解决具体问题重点1、常用数学函数的使用2、根据场景灵活选用函数难点1、根据场景灵活选用函数教学过程一SUMIF函数应用——单条件、多条件、模糊条件求和SUMIF条件求和函数语法：SUMIF(range,criteria,[sum_range])。中文语法：SUMIF(根据条件进行计算的单元格区域,单元格求和的条件,[求和的实际单元格])。其中，前两个参数是必需的，第3个参数可选，如果第3个参数被省略了，则默认的是对第1个参数单元格区域求和。二、UMIF函数应用——非空值条件、排除错误值、日期区间求和（1）统计非空值的种类对应的销量和，可用公式“=SUMIF(B2:B15,"*",C2:C15)”。其中，星号(*)是通配符，可以匹配任意一个串字符。（2）统计非空值的日期对应的销量和，可用公式“SUMIF(A2:A15,"<>",C2:C15)”。其中，“<>”表示非空值。（3）统计排除错误值的总库存，可用公式“=SUMIF(D2:D15,"<9e307")”。其中，9E307也可写为9E+307（以科学计数法表示），是Excel能接受的数字最大值。在Excel中，经常用9E+307代表最大数，这是约定俗成的。（4）统计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)”的结果是两个数：一个是“2017/3/20”以后非空值的日期对应的销量和（权且用Ａ代表这个数），另一个是“2017/3/25”以后非空值的日期对应的销量和（权且用Ｂ代表这个数）。“=SUM(SUMIF(A2:A15,{">=2017/3/20",">2017/3/25"},C2:C15)*{1,-1})”可以表示为“＝SUM({A,B}*{1,-1})”，即Ａ−Ｂ，也就是“2017/3/20”以后非空值的日期对应的销量和−“2017/3/25”以后非空值的日期对应的销量和”，就是最终所求2017年3月20日至2017年3月25日的总销量。三、SUMIF函数应用——隔列求和、查找与引用公式“=SUMIF($B$2:$G$2,H$2,$B3:$G3)”四、SUMIFS函数多字段、多条件求和公式“=SUMIFS(D2:D15,B2:B15,"宁波仓",C2:C15,"鼠标",D2:D15,">1000",D2:D15,"<5000")”SUMIFS函数功能：用于计算满足多个条件的全部参数的总量。语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2]…)中文语法：SUMIFS(求和单元格区域,条件单元格区域1,条件1,[条件单元格区域2,条件2],…)。●

sum_range：必需，要求和的单元格区域。●

criteria_rangel：必需，用来搜索条件1的单元格区域。●

criterial：必需，定义条件单元格区域1中单元格符合的条件。●

其后的参数：［条件单元格区域2,条件2］…是可以省略的。SUMIFS函数最多可以输入127个单元格区域/条件对。五、SUMPRODUCT函数用法解析语法：SUMPRODUCT(array1,[array2],[array3],…)。●array1：必需，其相应元素要进行相乘并求和的第一个数组参数。●[array2],[array3],…：可选，2～255个数组参数，其相应元素要进行相乘并求和。特别注意：数组参数必须具有相同的维数，否则，SUMPRODUCT函数将返回错误值。【典型应用】1）基本用法2）单条件求和3）多条件求和4）模糊条件求和5）单条件计数6）多条件计数7）模糊条件计数8）按月份统计数据9）跨列统计10）多权重统计11）二维单元格区域统计七、SUMPRODUCT函数的注意事项八、利用SUBTOTAL函数实现忽略隐藏行统计SUBTOTAL函数语法：SUBTOTAL(function_num,ref1,[ref2],…)。●

function_num：必需，数字1～11或101～111，指定分类汇总所使用的函数。如果使用1～11，将包括手动隐藏的行；如果使用101～111，则排除手动隐藏的行。始终排除已筛选掉的单元格。●

ref1：必需，要对其进行分类汇总计算的第1个命名区域或引用。●

其他参数：可选，要对其进行分类汇总计算的第2～254个命名区域或引用。。九、AGGREGATE函数——忽略错误值计算的万能函数功能：返回列表或数据库中的合计，可忽略隐藏行和错误值的选项。引用形式：AGGREGATE(function_num,options,ref1,[ref2],…)。数组形式：AGGREGATE(function_num,options,array,[k])。●

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

options：必需，一个数值，代表在函数的计算单元格区域内要忽略的内容。Options不同数值代表在函数计算单元格区域要忽略的内容如图4180所示。在本示例中，所有的AGGREGATE函数第2个参数都是“6”，即隐藏错误值。●

ref1：必需，要统计的数据单元格区域。当在数组形式的使用中有k时，k代表要计算的2～253个数值参数。对于“=AGGREGATE(15,6,B2:B13,2)”，function_num是15，k是2，就是统计第2小的数值。作业数学函数练习教学小结本节知识：SUMIF函数应用—单条件、多条件、模糊条件求和SUMIF函数应用—非空值条件、排除错误值、日期区间求和SUMIF函数应用—隔列求和、查找与引用