《物流数据分析》课件-任务2.2 高级函数应用

任务1.4高级函数应用【任务目的】1.熟练掌握VLOOKUP等查找和引用函数的使用；2.熟练掌握SUMIFS、COUNTIFS等多条件统计函数的使用。【工作任务】一个Excel工作簿中有如图所示的“销售订单明细表”和“图书编号对照表”两个工作表，请对“销售订单明细表”每个订单补齐图书名称和单价信息，并完成以下统计计算：1.《MSOffice高级应用》图书在2021年的总销售额；2.隆华书店在2021年第3季度（7月1日~9月30日）的订单个数；3.隆华书店在2021年的每月平均销售额（保留2位小数）。【知识链接】1.VLOOKUP函数

【格式】=VLOOKUP（查找值，查找区域，返回值的列序号，查找类型）。【作用】在指定区域中查找给定值，最终返回找到值所在的行号和指定列号交叉位置的值。【说明】查找值必须属于查找区域的第一列的值；查找类型有True和False两种，当取True时表示近似查找，如果找不到精确匹配值，则匹配小于查找值的最大值，要求查找区域的值必须是经过排序的。当取值为False时，表示进行精确查找，大多数情况下均用此类型。在函数使用中，True和False可以分别用1和0表示。2.SUMIFS函数【格式】=SUMIFS(求和区域，条件判断区域1，条件1，条件判断区域2，条件2，条件判断区域3，条件3，……)。【作用】对满足特定条件（条件数量大于等于2个）的区域求和，相对于SUMIF是单条件求和，SUMIFS是多条件求和，功能更为强大。3.COUNIFS函数【格式】=COUNTIFS(条件判断区域1，条件1，条件判断区域2，条件2，条件判断区域3，条件3，……)。【作用】统计满足指定条件的单元格个数。相对于COUNTIF是单条件计数，COUNTIFS是多条件计数，功能更为强大。4.AVERAGEIFS【格式】=

AVERAGEIFS(求平均值区域，条件判断区域1，条件1，条件判断区域2，条件2，条件判断区域3，条件3，……)。【作用】对满足特定条件（条件数量大于等于2个）的区域求平均值。相对于AVERAGEIF是单条件平均，AVERAGEIFS是多条件平均，功能更为强大。4.WEEKDAY函数【格式】=WEEKDAY（日期，返回值类型）【作用】返回日期在一周中的天序数(日期是本周的第几天）【实施步骤】1.填充图书名称将光标定位到“销售订单明细表”的E3单元格，输入公式“=VLOOKUP(D3,编号对照!$A$2:$C$19,2,0)”，回车即可返回第一个订单的图书名称，双击E3单元格右下角填充句柄，实现公式的引用，完成所有图书名称的填充。2.填充图书单价将光标定位到“销售订单明细表”中的F3单元格，输入公式“=VLOOKUP(D3,编号对照!A$2:C$19,3,0)”，回车即可返回第一个订单的图书单价，双击F3单元格右下角填充句柄，实现公式的引用，完成所有图书单价的填充。思考与点拨：VLOOKUP函数的计算原理是怎样的？VLOOKUP函数的基本语法结构为“VLOOKUP（查找值，查找区域，返回值的列序号，查找类型）”。执行公式计算时，Excel首先会在查找区域（即第二个参数）的第一列中寻找查找值（即第一个参数），找到之后确定行号，再根据第三个参数指定列标，行号和列标的交叉位置即最终返回的值。3.计算小计将光标定位到“销售订单明细表”中的H3单元格，输入公式“=F3*G3”，回车完成第一个订单的小计计算，双击H3单元格右下角填充句柄，实现公式的引用，完成所有订单的小计计算，如图所示。4.编制统计报告新建一个名为“统计报告”的工作表，编制如图所示的统计报告表格。选中B3单元格，输入公式“=SUMIFS(订单明细表!H3:H636,订单明细表!E3:E636,订单明细表!E7,订单明细表!B3:B636,">=2021/1/1",订单明细表!B3:B636,"<=2021/12/31")”，回车完成第一个统计项目的计算。选中B4单元格，输入公式“=COUNTIFS(订单明细表!C3:C636,订单明细表!C12,订单明细表!B3:B636,">=2021/7/1",订单明细表!B3:B636,"<=2021/9/30")”，回车完成第二个统计项目的计算。选中B5单元格，输入公式“=SUMIFS(订单明细表!H3:H636,订单明细表!C3:C636,订单明细表!C12,订单明细表!B3:B636,">=2021/1/1",订单明细表!B3:B636,"<=2021/12/31")/12”，回车即可完成第三个统计项目的计算，如图所示。思考与点拨：1.

如何让公式更为简短？这里所写的几个公式由于反复引用了工作表名字和单元格地址，使公式变得复杂冗长，其实可以通过对销售订单明细表中的列进行命名，命名后在公式中引用名字即可，可使公式大幅度缩短，提高公式可读性。2.SUMIF和SUMIFS函数有什么区别？SUMIF是单条件求和，SUMIFS是多条件求和，后者可替代前者，且须注意SUMIF和SUMIFS的参数顺序是不一样的，SUMIF的求和区域是最后一个参数，SUMIFS的求和区域是第一个参数。【任务小结】本任务涉及日常工作中常见的计算和统计问题，这类问题可以用函数来实现，也可以使用后续内容中的数据透视表或者SQL查询来完成。任务首先需要通过图书编号来查找图书名称，这类查找匹配问题一般通过VLOOKUP来完成，该函数是Excel中极其有用的函数，需要重点掌握。在统计计算时，SUMIF和COUNTIF分别是单条件下的求和与计数，而SUMIFS和COUNTIFS则可以分别实现多条件下的求和及计数，但是参数顺序的区别需要注意。为简化公式中的引用，可以提前对单元格区域命名，这样可以大大提高公式的可读性。【实践训练】素材文件“1-4高级函数应用练习素材”包含捷安物流公司2021年员工出差记录和费用类别编号，请补充“费用报销管理”工作表中的“费用类别”、“地区”和“是否