3第三章-数据统计-课件

第三章第三章 数据统计数据统计1.单条件求和1.1 每隔一行求和【案例案例3-1】单元格区域A1:A10中的数据，要将偶数行的数字进行求和，则可以在单元格E2中输入公式“=sum(if(MOD(ROW(B1:B10),2)=0,B1:B10)”;而如果要将奇数行的数字进行求和，则可以在单元格E4中输入公式“=sum(if(MOD(ROW(B1:B10),2)0,B1:B10)”。1.2 每隔两行求和【案例3-2】如果要从第1行开始每隔两行求和，在单元格E2中输入数组公式“=SUM(IF(MOD(ROW(INDIRECT(”1:“&COUNTA(B1:B11)-1,3)=0,B1:B11)”即可。如果要从第2行开始每隔两行求和，在单元格E4中输入数组公式“=SUM(IF(MOD(ROW(INDIRECT(”1:“&COUNTA(B1:B11)+1,3)=0,B1:B11)”即可。如果要从第2行开始每隔两行求和，在单元格E4中输入数组公式“=SUM(IF(MOD(ROW(B1:B11),3)=0,B1:B11)”即可。1.3 每隔1列求和方法和每隔1行求和基本一样，只是方向的转换。【案例3-3】1.4 每隔两列求和【案例3-4】1.5 对含有文本型数字的单元格进行求和如果单元格区域内含有文本型数字，那么要计算这些纯数字以及文本型数字，需要先使用VALUE函数将文本型数字转换为纯数字，然后计算。【案例3-5】计算公式为“=sum(VALUE(B1:B6)”(数组公式)1.6 对单元格区域内指定的数字进行求和【案例3-6】=SUM(A1:D8=3)*A1:D8)=SUM(IF(A1:D8=3,A1:D8)=SUMPRODUCT(A1:D8=3)*(A1:D8)=COUNTIF(A1:D8,3)*3补：SUMPRODUCT函数功能：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法：=SUMPRODUCT（array1,array2,array3,.）Array1,array2,array3,.为 2 到 30 个数组，其相应元素需要进行相乘并求和。注意：引用的数据区域大小不一致会导致计算错误如：=sumproduct（A2:A6，B2:B5）数据区域中有错误值时，计算会出现错误值数据区域应用不能整列引用=SUMPRODUCT(A:A,B:B)则返回错误值#NUM！。数据区域有文本，计算中系统默认文本值为01.7 对单元格区域内所有的正数或负数进行求和【案例3-7】1.8 动态汇总当日数据和以前累计数据【案例3-8】对于一些流水账数据，如果希望在某列单独显示当日累计数据和以前累计数据，可以使用sumif和today函数来解决。2 多条件求和2.1 对指定时间区间内的数据进行求和【案例3-9】联合利用sum和if函数，或者sumproduct函数。本例中用到的datevalue函数将日期字符串转换为日期序列号2.2 不同单位的不同商品型号的销售额累加案例【3-10】:（1）在G3单元格中输入公式（2）利用数据透视表实现多条件求和对于案例3-10这样的问题，利用数据透视表来解决是最方便的，具体步骤如下：单击数据区域的任一单元格“数据”“数据透视表和数据透视图”，打开向导对话框根据向导提示完成数据透视表2.3 按年月进行汇总【案例3-11】假若有一个每日的销售流水账，现要求按年、月进行汇总计算。2.4 统计一个月内周一到周五的加班小时数【案例3-12】超过8小时的算加班，要用weekday函数判断某天是周几，只有周一至周五的加班时间才进行计算。在单元格AH4中输入公式：=SUMPRODUCT(WEEKDAY($C$3:$AG$3,2)6)*(WEEKDAY($C$3:$AG$3,2)7),C4:AG4-8)2.5 存在合并单元格情况下的多条件加总计算【案例3-13】很多情况下，为了使表格美观，常常在数据区域内设置很多合并单元格。如下所示：2.6 利用数据库函数进行多条件加总计算DSUM函数：用于计算数据清单中满足指定条件的数字之和；DSUM（database，field，criteria）；其中，参数database为构成列表或数据库的单元格区域。Field指定函数所使用的数据列，数据列必须在列表中的第1行具有标志项。Field可以是文本，也可以是表示列位置的数字，如1（表示第1列）,2（表示第2列）。Criteria为一组包含给定条件的单元格区域。【案例3-14】如图所示，计算两个蓝色单元格3 求平均值、最大值和最小值对数据求平均值、最大值和最小值，也是实际工作中经常遇到的问题。一般情况下，利用AVERAGE函数、MAX函数和MIN函数就可以求平均值，最大值和最小值。但在有些情况下，需要满足多个条件的平均值，最大值和最小值，就需要联合使用If函数和AVERAGE函数、MAX函数和MIN函数了。3.1 每隔一行求平均值、最大值和最小值将1.1小节中介绍的函数SUM替换成AVERAGE、MAX和MIN，就可以每隔一行求平均值、最大值和最小值。3.2 每隔两行求平均值、最大值和最小值方法同1.2小节中介绍的每隔两行求和。3.3 对含有零值的单元格区域求平均值如果单元格有零值，当利用AVERAGE函数引用计算包含零值的单元格区域的数据平均值时，会把零值也计算在内，这样造成统计计算失真。此时，可以利用IF函数和AVERAGE函数计算不包括零值的平均值。公式为：=AVERAGE(IF(A1:C50,A1:C5)【案例3-15】3.4 除去一个最大值和一个最小值后对单元格区域求平均值【案例3-16】计算所有单元格的数字和找出最大、最小值总和减去最大、最小值统计单元格个数，然后减2计算平均值：(SUM(A1:C5)-MAX(A1:C5)-MIN(A1:C5)/(COUNT(A1:C5)-2)3.5 单条件求平均值、最大值和最小值3.6多条件求平均值、最大值和最小值3.7 利用数据库函数进行多条件求平均值、最大值和最小值【详见实验内容】3.8 计算加权平均值所谓加权平均值，就是将各个数字分别乘以各自的权重，再将这些结果加总后求平均值。【案例3-17】如图所示为学生平时考试成绩和期末考试成绩，每次考试成绩分别占不同的权重，现在要求计算每个学生的平均成绩。在单元格H6中输入公式：=SUMPRODUCT(B6:G6,$B$2:$G$2)，并将其向下填充复制到单元格H14，就得到各个学生的加权平均成绩3.9 计算移动平均值移动平均值，就是指定时间段，对时间序列数据进行移动计算平均值。移动平均值常常用在计算股票的移动平均线，存货成本等方面。下面介绍如何按移动加权平均法计算存货成本。移动加权平均法，是指每次收入存货后，将本次收货的成本加原有库存的成本，除以本次收货数量加原有收货数量，据此计算加权单价，并对发出存货进行计价的一种方法。【案例3-18】某公司的存货示例数据。利用移动加权平均法对存货进行计价的有关计算步骤如下：在单元格D3中输入公式=IF(B4=“”,“”,B4*C4)，然后向下填充复制，计算各日收入存货的金额。在单元格F4中输入公式=IF(E4=“”,“”,I3)，然后向下填充复制，计算每次发出存货的平均价格。在单元格G4中输入公式=IF(E4=“”,“”,E4*F4)，然后向下填充复制，计算每次发出存货的金额。在单元格H4中输入公式=N(H3)+N(B4)-N(E4)，然后向下填充复制，计算各日的结存存货的数量。在单元格I4中输入公式=IF(B4=0,I3,(N(J3)+N(D4)/(N(H3)+N(B4)，计算各日的移动加权平均单位成本。在单元格J4中输入公式=H4*I4，然后将其向下填充复制到J20，计算各日的结余存货金额。