用Excel实现高效统计.ppt

1、用用Excel实现高效统计实现高效统计在运营数据处理、财务及人事领域的应用2011年9月说明：本幻灯片中的部分内容来自于对外经济贸易大学佟强老师的课程讲义，特此致谢内容大纲Excel的菜单功能介绍常用菜单功能用于数据整理：排序、查找、筛选用于数据计算：自动求和用于图表制作：插入图表高级菜单功能用于格式优化：条件格式、数据有效性用于数据整理：分列用于数据统计：分类汇总、数据透视表内容大纲Excel的基本函数介绍统计函数COUNT系列、MAX、MIN数学函数MOD、ROUND系列、SUM系列、INT日期函数TODAY、WORKDAY查找或引用函数LOOKUP系列、MATCH文本函数LEN、MID系

2、列、FIND、SUBSTITUTE、TRIM逻辑函数IF、AND系列数据处理与管理Excel提供了较强的数据处理和管理的功能，可以对数据表进行筛选、排序、分类汇总、统计和重新组织表格等。由于Excel允许将数据存放在工作表的任意位置，这为数据的处理和管理带来不便。因此，在对数据做某些处理之前，要求数据必须按“数据清单”存放。数据清单数据清单由标题行和数据部分组成。第一行是表的列标题（字段名），用不同的名字加以区分。从第二行起是数据部分（不允许出现空白行和空白列）。每一行数据称为一个记录，每一列称为一个字段。在工作表中，最好只有一个数据清单，且放置在工作表的左上角。数据清单与其他数据之间应该留出

3、至少一个空白行和一个空白列。排序工具栏排序按钮3个数据列/行的排序：主关键字、次关键字、第三关键字4个数据列/行的排序先对“最次要的关键字”排序然后再用对其他关键字排序自定义序列排序筛选如果希望工作表中只显示数据清单中满足条件的记录，可以用筛选或高级筛选来实现。数据清单经过筛选后，不满足条件的记录只是被暂时隐藏了，并没有被删除。通过筛选，可以实现对筛选后的数据做编辑操作、设置特殊格式、制作图表和打印等。分类汇总分类汇总是指在数据清单中按某一列数据的值对数据清单进行分类后，按不同的类对数据进行统计。分类汇总时要：确定数据清单中的一列为分类字段；分类字段中的同类别的数据在相邻的单元格（预排序）；确

4、定分类汇总的方式（总和、个数、平均值）；确定要统计哪些数据列。数据透视表数据透视表是对原有的数据清单重组并建立一个统计报表。数据透视表是一种交互式的、交叉制表的Excel报表，用户对数据进行汇总和分析。函数Excel函数一共11类，分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。函数的构成：函数名：不区分大小写。参数表：用逗号分隔的参数1，参数2，参数N（N30）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。返回值：根据参数表给出的参数计算得到的结果。函数嵌套函数嵌套是指一

5、个函数可以作为另一个函数的参数使用。例如：ROUND(AVERAGE(A2:C2),0)AVERAGE的返回值作为ROUND的参数；Excel函数嵌套最多可嵌套七级。取整函数INTINT(number)将数字向下舍入到最接近的整数（Integer）number需要进行向下舍入取整的实数例子：INT(8.9)将8.9向下舍入到最接近的整数(8)INT(-8.9)将-8.9向下舍入到最接近的整数(-9)A2-INT(A2)返回单元格A2中正实数的小数部分求余函数MODMOD(number,divisor)返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。number被除数divisor

6、除数例子：MOD(7,4)=3MOD(-7,-4)=3MOD(7,-4)=-1MOD(-7,4)=1如果divisor为零，函数MOD返回错误值#DIV/0!逻辑函数系列AND(logical1,logical2,.)所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE。OR(logical1,logical2,.)所有参数中只要有一个为真，返回TRUE；否则返回FALSE。NOT(logical)返回逻辑值的相反值。按列查找函数VLOOKUPVLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lo

7、okup_value：查找值table_array：数据区col_index_num：为数据区中数据区中待返回的匹配值的列序号range_lookup：如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。文本函数字符串长度函数LEN(S)截取子串函数MID(S,m,n)截取左子串函数LEFT(S,n)截取右子串函数RIGHT(S,n)删除首尾空格的函数TRIM(S)替换函数SUBSTITUTE(S1,m,n,S2)查找函数FIND(sub,S,n)