商务数据计算与统计.pptx

第2章商务数据计算与统计第2章主要内容2.1 最基本的商务数据计算与统计 2.2 带条件的数据统计计算案例 2.3 日期型与文本型数据的统计计算 2.4 分组统计计算 2.5 合并计算应用案例第2章目录2.1 最基本的商务数据计算与统计 l2.1.1 描述数据集中趋势的统计量计算 l2.1.2 描述数据离散程度的统计量计算第2章1.求和格式：SUM(参数1,参数2,)功能：求一系列数据的累加和。2.求平均值格式：AVERAGE(参数1,参数2,)功能：求一系列数据的算术平均值。例例例例=SUM(A1:D1)=SUM(A1:D1)或或或或=SUM(A1,B1,C1,D1)SUM(A1,B1,C1,D1)或或或或=A1+B1+C1+D1=A1+B1+C1+D1例例例例 =AVERAGE(A1:D1)=AVERAGE(A1:D1)或或或或 =AVERAGE(A1,B1,C1,D1)=AVERAGE(A1,B1,C1,D1)或或或或 =(A1+B1+C1+D1)/4=(A1+B1+C1+D1)/42.1.1 描述数据集中趋势的统计量计算第2章3中位数函数MEDIAN格式：MEDIAN(参数1,参数2,)功能：求一系列数据的中值。中值是一系列数据按从小到大排序后，取排在中间的一个值。如果参加统计数据的个数为偶数，取位于中间的两个数的平均值。例如：l=MEDIAN(4,3,6)，结果是4（取3，4，6中间的4）。l=MEDIAN(6,2,4,3)，结果是3.5（取2，3，4，6中间两个数的平均值，(3+4)/2=3.5）。第2章4众数函数MODE格式：MODE(参数1，参数2，)功能：返回一系列数据中出现频率最高的数。如果数据中没有重复出现的数，返回错误值N/A。例如：l=MODE(3,5,5,2,3,5)，结果是5（只有5出现的次数最多，3次）。l=MODE(2,3,3,3,5,5,5)，结果是3（3和5出现次数最多，都是3次。返回先出现的3）。第2章【例2-1】对“北京新发地批发市场”完成以下任务（1）计算每种蔬菜的平均价格。（2）计算最低价格的平均值。（3）计算最低价格的中位数。（4）计算最低价格的众数。第2章2.1.2 描述数据离散程度的统计量计算1最大值函数MAX格式：MAX(参数1,参数2,)功能：求一系列数据的最大值。2最小值函数MIN格式：MIN(参数1,参数2,)功能：求一系列数据的最小值。第2章【例2-2】计算图2.1“北京新发地批发市场”蔬菜最低价格中的最高价格和最低价格。（1）计算最低价格中的最高价格。（2）计算最低价格中的最低价格。第2章3标准差函数格式：STDEV(参数1,参数2,)功能：把每个参数看作x，若有x1,x2,xn共n个数，则 标准差反映一组数据与其平均值的离散程度。第2章A公司与B公司职工工资表编号公司A公司B1200035002200039003200050004200041005200035006200039007200041008200006000平均工资42504250标准差6363.96848.53中位数20004000众数20003500最低工资20003500最高工资200006000第2章目录2.2 带条件的数据统计计算案例 l2.2.1 IF函数与逻辑函数分组计算案例 l2.2.2 简单条件快速统计计算案例 l2.2.3 用数据库函数实现任意条件统计计算案例第2章2.2.1 IF函数与逻辑函数分组计算案例1分支函数IF 格式：IF（逻辑表达式，表达式1，表达式2）功能：若“逻辑表达式”值为真，函数值为“表达式1”的值；否则为“表达式2”的值。l方括号部分表示可以省略。当“逻辑表达式”的值为真时，函数值为“表达式1”的值。如果省略“表达式1”（注意逗号不能省略），结果是“0”；否则，当“逻辑表达式”的值为假时，函数值为“表达式2”的值。如果省略“表达式2”，结果是“0”。第2章例 在C2单元格输入：=IF(A2B2,100,200)=IF(A2=60,true,false)=IF(A2=60,及格,不及格)=IF(A260,=IF(A260,不及格不及格,IF(A270,IF(A270,及格及格,良好良好/优秀优秀)=IF(A260,=IF(A260,不及格不及格,IF(A270,IF(A270,及格及格,IF(A2=84,IF(A2=20，C3=20）第2章4”逻辑非”函数NOT格式：NOT(参数)功能：函数值取参数的反值。参数值为真则函数值为假。否则，参数值为假，则函数值为真。l例如，F3存放的是科室信息，判断是否是“科室1”的逻辑表达式为：NOT(F3=科室1)等价于 F3科室1 如果F3不等于科室1，则结果是真值，否则为假值。第2章【例2-4】对职工情况表，完成以下任务。（1）增加一列标记，如果年龄在2030，则标记“是”，否则不标记（2）用“1”标识出学历是本科、大专的职工，并统计出人数。第2章【例2-5】完成以下任务。（1）计算“补助1”。标准是：经理500，监理300，其余人员150。（2）计算“补助2”。标准是：职务等级为“监理”及以上，且基本工资低于5000的补助200。（3）计算“工会会费扣款”。假设：工会会费扣款为基本工资的3%。计算结果进行四舍五入保留2位小数。（4）计算“应发工资”。第2章2.2.2 简单条件快速统计计算案例1条件计数函数COUNTIF 格式：COUNTIF(条件数据区,条件)功能：统计“条件数据区”中满足给定条件的单元格的个数。l条件数据区：该区域的每个单元格都必须是数字或名称、数组或包含数字的引用。空值和文本值将被忽略。l条件：可以是数字、文本、表达式、函数或单元格地址引用。其中文本必须用双引号括起来，数字可以不用双引号括起来。l在“条件”中可以使用通配符“?”匹配任意一个单个字符，“*”匹配任一个字符串。如果要查找实际的问号或星号，在字符前键入波形符“”。第2章2条件求和函数SUMIF格式：SUMIF(条件数据区,条件,求和数据区)功能：在“条件数据区”查找满足条件的单元格，统计满足条件的单元格对应于“求和数据区”中数据的累加和。l其中，方括号部分表示可以省略。如果“求和数据区”省略，统计“条件数据区”满足条件的数据累加和，这时的“条件数据区”也是“求和数据区”。lSUMIF函数中的前两个参数与COUNTIF中的前两个参数的含义相同。第2章3条件求平均值函数AVERAGEIF格式：AVERAGEIF(条件数据区,条件,求平均值数据区)功能：在“条件数据区”查找满足条件的单元格，统计满足条件的单元格对应于“求平均值数据区”中数据的平均值。如果“求平均值数据区”省略，统计“条件数据区”满足条件的单元格中数据的平均值。l系统执行该函数的过程和规定与SUMIF基本相同，不同的是AVERAGEIF按条件求平均值，SUMIF函数按条件求累加和。第2章【例2-6】根据职工情况表的数据完成以下任务。（1）统计35岁及以下的人数。（2）统计低于平均基本工资的人数。（3）统计“科室2”的基本工资总和。（4）统计“科室2”的平均基本工资。第2章4多条件求个数函数COUNTIFS格式：COUNTIFS(条件数据区1,条件1,条件数据区2,条件2,)功能：对多个条件数据区域，判断是否满足给定的对应的条件，计算符合所有条件的单元格的个数。l说明：格式中的方括号部分可以省略，最多允许 127 个区域和条件对。其余规定与COUNTIF相同。lCOUNTIFS与COUNTIF不同的是：COUNTIFS可以对同时满足多个条件的数据区的单元格实现计数，COUNTIF只能实现对满足一个条件的数据区及单元格计数。第2章5多条件求和函数SUMIFS格式：SUMIFS(求和数据区,条件数据区1,条件1,条件数据区2,条件2,)功能：对多个条件数据区域，判断是否满足给定的对应的条件，对符合所有条件的数据区的单元格求和。l注意：SUMIFS 的求和数据区是第一个参数，SUMIF的求和数据区是第三个参数。第2章6多条件求平均值函数AVERAGEIFS格式：AVERAGEIFS(求平均值数据区,条件数据区1,条件1,条件数据区2,条件2,)功能：对多个条件数据区域，判断是否满足给定的对应的条件，对符合所有条件的数据区的单元格求平均值。l注意：AVERAGEIFS 的求平均值数据区是第一个参数，AVERAGEIF的求平均值数据区是第三个参数。第2章【例2-7】根据职工情况表的数据完成以下任务。（1）统计性别为“男”且年龄在3040（含30，40）的人数。（2）统计性别为“男”且年龄在3040（含30，40）的人的平均基本工资。（3）统计性别为“男”且学历是“博士”和“硕士”的人数。第2章2.2.3 用数据库函数实现任意条件统计计算案例1数据库函数的格式与约定格式：函数名(数据清单区,指定的统计字段,条件区)l数据清单区：“数据清单区”与第1章介绍的数据清单的约定相同。“数据清单区”包含字段名行，可以是整个数据表或其中的一部分，但是一定要包含所有条件字段和统计字段。l指定的统计字段：它可以是以下三种形式之一。字段名所在的单元格地址。带英文双引号的“字段名”。“列”在数据清单中的位置（用数字表示）：“1”表示第1列，“2”表示第2列第2章例如，统计“博士”的平均年龄。第2个参数写为以下3种形式都是等价的。l第2个参数是地址引用：DAVERAGE(A2:G22,C2,)l第2个参数是字段名：DCOUNT(A2:G22,年龄,)l第2个参数是序号：DCOUNT(A2:G22,3,)（当数据清单区从A列开始时）第2章（1）条件区第1行的规定有2种。规定1：第1行输入字段名或字段名所在地址的引用。l说明：如果给出的条件可以直接在数据清单中找到，第1行必须与数据清单的字段名完全一样，或字段名所在地址的引用。例如，条件是统计性别为“男”的人数。“性别”在B2单元格，第1行可以输入“性别”或“=B2”。规定2：第1行为空白（不输入任何内容，但是属于条件区）。l说明：如果给出的条件不能直接在数据清单中找到，这时就需要用公式计算的条件。例如，条件是统计低于平均工资的人数。数据清单中包含数据表数据，需要用公式计算平均工资。公式由逻辑或关系表达式组成，公式的结果是逻辑值。只有公式计算结果为真值时，才认为记录是符合条件的。第2章（2）条件区第2行开始设置条件。条件输入的位置有如下规定。若条件放在不同的行（占用多行），为“或”关系。若条件放在同一行（占用多列），为“与”关系。条件的内容有如下规定。在条件中允许出现“*”代表任意一个字符串，“？”代表任意一个字符。条件区中的文字不需要加双引号，直接输入文字内容即可。条件区中的关系运算符（如“”“”或“=”）、“*”和“？”，必须是英文字符，不能是中文字符。一次只能对工作表中的一个条件区进行统计。当条件区第1行不能为空白时，最好不要重新输入字段名，而是用地址引用。如果输入错误会导致统计结果不正确。第2章2数据库函数（1）条件求和函数DSUM。格式：DSUM(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，对满足条件的记录“指定的统计字段”数据求累加和。（2）条件求平均值函数DAVERAGE。格式：DAVERAGE(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，对满足条件的记录“指定的统计字段”数据求平均值。第2章（3）条件求最大值函数DMAX。格式：DMAX(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，返回满足条件的记录“指定的统计字段”数据的最大值。（4）条件求最小值函数DMIN。格式：DMIN(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，返回满足条件的记录“指定的统计字段”数据的最小值。第2章（5）条件统计数值型数据的个数函数DCOUNT。格式：DCOUNT(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，返回满足条件的记录“指定的统计字段”数值型数据的单元格个数。l如果“指定的统计字段”省略，逗号不能省略。l例如：DCOUNT(数据清单区,条件区)（6）条件统计个数函数DCOUNTA。格式：DCOUNTA(数据清单区,指定的统计字段,条件区)功能：在“数据清单区”找出满足“条件区”条件的记录，返回满足条件的记录“指定的统计字段”数值型数据的单元格个数。l如果指定的统计字段省略，逗号不能省略。例如：lDCOUNTA(数据清单区,条件区)第2章【例2-8】完成以下计算任务。（1）统计性别为男，并且学历是博士和硕士的人的平均年龄和平均基本工资。l设置条件区I4:J6l计算平均年龄，公式：=DAVERAGE(A2:G22,C2,I4:J6)l计算平均基本工资，公式：DAVERAGE(A2:G22,G2,I4:J6)第2章（2）统计科室1或科室2的年龄为3040的人数。l设置条件区I10:K12l输入公式：=DCOUNT(A2:G22,I10:K12)（3）统计博士中的最高工资。l设置条件区I16:I17l输入公式：=DMAX(A2:G22,G2,I16:I17)（4）统计硕士中低于平均工资的人数。l设置条件区I21:J22。在J22输入公式：=G30时，对数据的小数部分从左到右的第n位四舍五入；l当n=0时，对数据的小数部分最高位四舍五入取数据的整数部分；l当n0时，对数据的整数部分从右到左的第n位四舍五入。如果省略n，默认n=2。如果“逻辑值”为FALSE或省略，则返回的文本中包含逗号分隔符。FIXED函数的功能与ROUND基本一样，不同的是FIXED的结果是文本型数据，且可以选择是否带逗号分隔符。例如：=FIXED(1234.56,-1)结果是1,230。第2章8文本转数值函数VALUE格式：VALUE(S)功能：将文本型数据转为数值型数据。l例如：l =VALUE(12.80)结果是12.8。l =VALUE(AB)结果是#VALUE!。9大小写字母转换函数LOWER、UPPER格式：LOWER(S)功能：大写字母转为小写字母。格式：UPPER(S)功能：小写字母转为大写字母。LOWER与UPPER都不改变文本中的非字母的字符。例如：=LOWER(aBCdE)结果是abcde。=UPPER(aBCdE)结果是ABCDE。第2章10替换函数REPLACE、REPLACEB格式：REPLACE(S1,m,n,S2)、REPLACEB(S1,m,n,S2)功能：结果是S1，但是S1中从m开始的n个字符已经被S2替换。例如：=REPLACE(abcdef,2,3,x)结果是axef。=REPLACE(abcd,1,2,xyz)结果是xyzcd。11比较函数EXACT格式：EXACT(S1,S2)功能：比较两个字符串是否完全相等。如果字符串S1等于字符串S2返回TRUE，否则返回FALSE。只有S1与S2的长度相等且按位相等（区分大小写字母）时，结果才是TRUE。例如：EXACT(EXCEL,Excel)=FALSE。=EXACT(abc,ab)结果是FALSE。第2章12查找函数FIND、FINDB格式：FIND(sub,S,n)、FINDB(sub,S,n)功能：从S串的左起第n个位置开始查找sub子串，返回sub子串在S中的起始位置。如果在S中没有找到sub，返回VALUE！。FIND区分大小写，sub中不允许使用通配符。例如：=FIND(ab,xaBcaba,1)结果是5。=FIND(计算机,中国计算机,1)结果是3。=FINDB(计算机,中国计算机,1)结果是5。第2章13搜索函数SEARCH、SEARCHB格式：SEARCH(sub,S,n)、SEARCHB(sub,S,n)功能：与FIND基本一样，但是SEARCH不区分大小写，sub中允许使用通配符。通配符包括问号（“?”可匹配任意的单个字符）和星号（“*”可匹配任意一串字符)。如果要查找真正的问号或星号，在问号或星号的前面键入波形符(“”)。例如：=SEARCH(ab,xaBcaba,1)结果是2。（注意SEARCH不区分大小写）第2章【例2-14】对“北京新发地批发市场”网站下载的数据进行处理。下面的任务是如何将A列的数据拆分到BE列。第2章操作思路与步骤：（1）数据中的人民币符号有两种，“￥”和“￥”。将所有的“￥”替换为“￥”，操作是：选中A列，在“开始”选项卡的“编辑”组中单击“查找和选择”按钮，选择“替换”，在“查找”框输入“￥”，“替换为”框输入“￥”，单击“全部替换”按钮。（2）分离出日期。在B3输入公式：=VALUE(MID(A3,2,9)（3）分离蔬菜品种名称。在C3输入公式：=MID(A3,FIND(,A3,1)+1,FIND(￥,A3,1)-1-FIND(,A3,1)-LEN(北京新发地批发市场)第2章（4）分离蔬菜最低价格。在D3输入公式：=VALUE(MID(A3,FIND(￥,A3,1)+1,FIND(￥,A3,FIND(￥,A3,1)+1)-FIND(￥,A3,1)-1)（5）分离蔬菜最高价格。在E3输入公式：=VALUE(MID(A3,FIND(￥,A3,FIND(￥,A3,1)+1)+1,FIND(￥,A3,FIND(￥,A3,FIND(￥,A3,1)+1)+1)-(FIND(￥,A3,FIND(￥,A3,1)+1)+1)（6）选中B3:E3，单击选定区域右下角的“填充柄”将公式向下复制到其他行。执行以上操作后，可以删除A列。但是要注意，一定要先将BE列的公式转为数值之后，再删除A列。第2章目录2.4 分组统计计算l2.4.1 用分类汇总功能实现分组统计l2.4.2 用数据透视表功能实现分组统计l2.4.3 数据的频数统计计算第2章2.4.1 用分类汇总功能实现分组统计在分类汇总时，要区分哪些是分类字段，哪些是统计字段。在“分类汇总”对话框中需要选择以下内容。l（1）选择分类字段。确定数据清单中的某一列为“分类字段”。l（2）确定分类汇总的方式：总和、个数或平均值等。l（3）确定统计字段。要统计哪些数据列。第2章【例2-15】完成以下任务。1按“科室”分类，求各科室的平均年龄、平均工资和人数第2章2求出每个“科室”不同“性别”的最小年龄第2章2.4.2 用数据透视表功能实现分组统计【例2-16】完成以下任务1用数据透视表统计图2.17中每个“科室”的平均“年龄2统计各学历的人数以及占总人数的百分比第2章【例2-17】完成以下任务1统计不同的学历、不同的职务等级的分布情况，并且能按不同的科室显示分布情况第2章2按“性别”和“科室”分类统计人数和平均工资第2章【例2-18】按“年”“季度”“月”分组统计不同时间段的股票平均开盘价与平均收盘价。第2章2.4.3 数据的频数统计计算频数函数FREQUENCY格式：FREQUENCY（数据区，分段点区）功能：按“分段点区”给出的分段，以数组的形式返回“数据区”中数据在各分段点的频数分布。说明：（1）“分段点区”是一列连续的单元格区域，数值按升序排列。（2）“分段点区”中的每一个数据表示一个分段点，统计的结果是包含该数据以及小于该数据的个数。（3）如果分段区有n个数，则返回数组元素的个数最多为n1。（4）公式结束按【Ctrl+Shift+回车】组合键。第2章【例2-19】完成以下任务。1统计职工情况表中的不同年龄段的人数。l年龄段，分别是：l年龄小于30；l3039；l4049；l以及年龄大于49的人数。第2章2统计职工情况表中的不同工资段的人数。l工资段，分别是：l小于3000（不含3000）；l30004000（不含4000）；l40005000（不含5000）；l大于等于5000。第2章第2章2.5 合并计算应用案例1按位置合并计算【例2-20】2013年、2014年和2015年的三张销售报表。计算这三张报表对应月份的销售数量、销售总价和邮费的总计。第2章2按名称合并计算【例2-21】三张工作表，分别是2014年、2015年和2016年6月中旬的水果价格。求出这三年水果价格的平均价格。也就是对最低价、平均价和最高价求平均值。