用Excel实现高效统计

1、在运营数据处理、财务及人事领域的应用2011年9月 说明：本幻灯片中的部分内容来自于对外经济贸易大学佟强老师的课程讲义，特此致谢内容大纲 excel的菜单功能介绍 常用菜单功能 用于数据整理：排序、查找、筛选 用于数据计算：自动求和 用于图表制作：插入图表 高级菜单功能 用于格式优化：条件格式、数据有效性 用于数据整理：分列 用于数据统计：分类汇总、数据透视表内容大纲 excel的基本函数介绍 统计函数 count系列、max、min 数学函数 mod、round系列、sum系列、int 日期函数 today、workday 查找或引用函数 lookup系列、match 文本函数 len、mi

2、d系列、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函数嵌套最多可嵌套七级。取整函数int int(number) 将数字向下舍入到最接近的整数（integer） number 需要进行向下舍入取整的实数 例子：int(8.9) 将 8.9 向下舍入到最接近的整数 (8) int(-8.9) 将 -8.9 向下舍入到最接近的整数 (-9) a2-int(a2) 返回单元格 a2 中正实数的小数部分 求余函数mod mod(numbe

6、r,divisor) 返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。 number 被除数 divisor 除数 例子：mod(7, 4)=3 mod(-7, -4)=3mod(7, -4) = -1mod(-7, 4) = 1 如果 divisor 为零，函数 mod 返回错误值 #div/0!逻辑函数系列 and(logical1, logical2, .) 所有参数的逻辑值为真时，返回 true；只要一个参数的逻辑值为假，即返回 false。 or(logical1, logical2, .) 所有参数中只要有一个为真，返回true；否则返回false。 not(logi

7、cal) 返回逻辑值的相反值。按列查找函数vlookup vlookup(lookup_value,table_array,col_index_num,range_lookup) lookup_value：查找值 table_array：数据区 col_index_num：为 数据区中数据区中待返回的匹配值的列序号 range_lookup：如果为 true 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 false，函数 vlookup 将返回精确匹配值。如果找不到，则返回错误值 #n/a。文本函数 字符串长度函数len(s) 截取子串函数mi