excel常用函数及数据整理技巧,相当全面.ppt

Roland Berger Strategy Consultants Document number EXCEL常用函数及数据整理技巧 内容概要 1、EXCEL函数应用 2、EXCEL数据整理方法与技巧 3、快捷键应用 第一部分 EXCEL常用函数 一、EXCEL函数类型 文本函数 日期和时间函数 数学和三角函数 统计函数 查询和引用函数 逻辑函数 信息函数 财务函数 工程函数 数据库函数 自定议函数 二、文本函数 通过文本函数，可以在公式中处理字符串。例如可以转换文本格式、截取特定文本字符、查找替代特定文本等。 1、转换类 大小写转换类 upper(); lower(); proper() 数字、文本等格式间转换 dollar(); rmb(); text()；value() 2、截取/合并类 截取类 left()/leftb() ； right()/rightb() ； mid()/midb() ; len()/lenb() 合并类 concatenate() 3、文本整理类 trim() /clean() 4、字符查找、比较、替代类 查找类 find(); findb(); search(); searchb() 比较类 exact() 替代类 replace(); substitute() 三、日期与时间函数 数据表处理过程中，日期与时间函数是相当重要的处理依据。 1、取当前系统时间、日期信息函数 now() ; today() 2、取得日期、时间的部分字段值函数 year() ; month() ; day() ; hour() ; minute(); second() 3、日期计算函数 datedif() ; days360() 四、数学和三角函数 1、与求和有关的函数 SUM(number1,number2, ...) 行列求和、区域求和 （参数最多30个） SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 SUBTOTAL(function_num, ref1, ref2, ...) 返回数据清单分类汇总 四、数学和三角函数 2、数字取舍类函数 ROUND(number,num_digits) ：返回某个数字按指定位数舍入后的数字 INT(number) ：返回小数舍入后的整数，即向下取整 用于四舍五入时需要技巧即：加0.5后取整 ABS(number)：返回数字的绝对值。 TRUNC(number,num_digits): 将数字的小数部分截去，返回整数。 五、统计函数 作为EXCEL函数中数量最为庞大的一类，统计类函数提供了丰富的概率与统计函数功能。： 1、用于求平均值的函数 AVERAGE(number1,number2,...)：返回参数的平均值（算术平均值） AVERAGEA(value1,value2,...)：计算参数列表中数值的平均值（算数平均值） TRIMMEAN(array,percent)：返回数据集的内部平均值。 2、求单元格个数函数 COUNT(value1,value2,...)：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(value1,value2,...)：返回参数列表中非空值的单元格个数。 五、统计函数 3、求区域中数据分布频率函数 FREQUENCY(data_array,bins_array)： 以一列垂直数组返回某个区域中数据的频率分布。 4、线性回归分析函数 CORREL(array1,array2)：返回单元格区域 array1 和 array2 之间的相关系数。 FORECAST(x,known_y‘s,known_x’s)：根据已有的数值计算或预测未来值。 5、其他特定需求统计函数 最大最小值 MAX(number1,number2,...)/ MIN(number1,number2,...) 第K大与第K小 LARGE(array,k) / SMALL(array,k) 中位数 MEDIAN(number1,number2,...) 排名 RANK(number,ref,order) 六、查询与引用函数 1、绝对引用与相对引用 绝对引用—在复制公式时不希望EXCEL调整引用，须使用绝对引用 相对引用—在创建公式时，单元格或单元格区域的引用通常是包含公式的单元格的相对位置。 2、引用的表示方法 :A1与R1C1两种类型引用