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

Document number,1,EXCEL常用函数及数据整理技巧,Document number,2,内容概要,1、EXCEL函数应用 2、EXCEL数据整理方法与技巧 3、快捷键应用,Document number,3,第一部分 EXCEL常用函数,Document number,4,一、EXCEL函数类型,文本函数 日期和时间函数 数学和三角函数 统计函数 查询和引用函数 逻辑函数 信息函数 财务函数 工程函数 数据库函数 自定议函数,Document number,5,二、文本函数,通过文本函数，可以在公式中处理字符串。例如可以转换文本格式、截取特定文本字符、查找替代特定文本等。 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(),Document number,6,三、日期与时间函数,数据表处理过程中，日期与时间函数是相当重要的处理依据。 1、取当前系统时间、日期信息函数 now() ; today() 2、取得日期、时间的部分字段值函数 year() ; month() ; day() ; hour() ; minute(); second() 3、日期计算函数 datedif() ; days360(),Document number,7,四、数学和三角函数,1、与求和有关的函数 SUM(number1,number2, .) 行列求和、区域求和 （参数最多30个） SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和 SUBTOTAL(function_num, ref1, ref2, .) 返回数据清单分类汇总,Document number,8,四、数学和三角函数,2、数字取舍类函数 ROUND(number,num_digits) ：返回某个数字按指定位数舍入后的数字 INT(number) ：返回小数舍入后的整数，即向下取整 用于四舍五入时需要技巧即：加0.5后取整 ABS(number)：返回数字的绝对值。 TRUNC(number,num_digits): 将数字的小数部分截去，返回整数。,Document number,9,五、统计函数,作为EXCEL函数中数量最为庞大的一类，统计类函数提供了丰富的概率与统计函数功能。： 1、用于求平均值的函数 AVERAGE(number1,number2,.)：返回参数的平均值（算术平均值） AVERAGEA(value1,value2,.)：计算参数列表中数值的平均值（算数平均值） TRIMMEAN(array,percent)：返回数据集的内部平均值。 2、求单元格个数函数 COUNT(value1,value2,.)：返回包含数字以及包含参数列表中的数字的单元格的个数。 COUNTA(value1,value2,.)：返回参数列表中非空值的单元格个数。,Document number,10,五、统计函数,3、求区域中数据分布频率函数 FREQUENCY(data_array,bins_array)： 以一列垂直数组返回某个区域中数据的频率分布。 4、线性回归分析函数 CORREL(array1,array2)：返回单元格区域 array1 和 array2 之间的相关系数。 FORECAST(x,known_ys,known_xs)：根据已有的数值计算或预测未来值。 5、其他特定需求统计函数 最大最小值 MAX(number1,number2,.)/ MIN(number1,number2,.) 第K大与第K小 LARGE(array,k) / SMALL(array,k) 中位数 MEDIAN(number1,number2,.) 排名 RANK(number,ref,order),Document number,11,六、查询与引用函数,1、绝对引用与相对引用 绝对引用在复制公式时不希望EXCEL调整引用，须使用绝对引用 相对引用在创建公式时，单元格或单元格区域的引用通常是包含公式的单元格的相对位置。 2、引用的表示方法 :A1与R1C1两种类型引用 3、主要函数 LOOKUP(lookup_value,lookup_vector,result_vector) 在单行区域或单列区域中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值,Document number,12,七、逻辑函数,用于判断真假值，或者进行复合检验的EXCEL函数,称之为逻辑函数。 1、表达“且”、“或”、“不”关系的逻辑函数 AND(logical1,logical2, .) 所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。 OR(logical1,logical2,.)：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。 NOT(logical)：对参数值求反。当要确保不等于某一特定值时，可以使用此函数。 2、if()条件函数 IF(logical_test,value_if_true,value_if_false) 执行真假值判断，根据逻辑计算的真假值，返回不同结果。最多嵌套7层条件。,Document number,13,八、信息函数,专门用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等，这类函数统称为信息类函数。 主要介绍IS类函数，常与IF函数配合使用。 ISBLANK(value) 值为空白单元格时,返回true。 ISERR(value) 值为任意错误值（除去 #N/A）时,返回true。 ISERROR(value) 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）时,返回true。 ISLOGICAL(value) 值为逻辑值时,返回true。 ISNA(value) 值为错误值 #N/A（值不存在）时,返回true。 ISNONTEXT(value) 值为不是文本的任意项时,返回true。 ISNUMBER(value) 值为数字时,返回true。 ISREF(value) 值为引用时,返回true。 ISTEXT(value) 值为文本时,返回true。,Document number,14,九、财务函数,在此部分结合常用财务管理理论，EXCEL提供了16个具体财务函数，主要分为投资理财类函数与折旧类函数。 折旧类函数 SLN(cost,salvage,life) 返回某项资产在一个期间中的线性折旧值。 SYD(cost,salvage,life,per) 返回某项资产按年限总和折旧法计算的指定期间的折旧值。 DDB(cost,salvage,life,period,factor) 使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。,Document number,15,第二部分 EXCEL数据整理技巧,Document number,16,一、EXCEL常用数据整理方法与技巧,1、排序 2、筛选 3、分列 4、分类汇总 5、合并计算 6、数据透视表,Document number,17,二、数据清单的排序,1、排序方式 简单排序与多重排序 2、排序方法 升序、降序（默认以拼音排序）,Document number,18,三、数据清单的筛选,1、自动筛选 适用于简单条件，通常是在一个数据清单的一列中查找相同的值。也可以用自定义筛选方式快速查找出符合多重条件的记录。,Document number,19,三、数据清单的筛选,2、高级筛选 如果数据清单字段较多，筛选条件也较多，则可以使用高级筛选,注意： A、要首先建立条件区域 B、条件区域第一行是作为筛选条件的字段名，必须与数据清单中字段名完全相同 C、条件区域其他行用于输入筛选条件 D、条件区域与数据清单不能连接，须有一个空行或空列分离,Document number,20,三、数据清单的筛选,2、高级筛选,Document number,21,四、分列,如果某单元格或列包含数个可用信息，希望把其拆分在数个单元格内列示时，则用到“分列”工具。 如下图中：开票期间项,Document number,22,四、分列,Document number,23,四、分列 步骤1,分列的两种类型： 1、按分隔符号分列 2、按固定列宽分列 操作时应具体按被分列项特征，选取不同分列方法。在本例中，两种方法均可：可以以空格为分列符号分列；也可以用固定列宽人为添加分列线分列,Document number,24,四、分列 步骤2,上步中，假设选分隔符号，则弹出步骤2，选取具体分隔符号（打勾）后，会在预览中显出分列线，如无误，点击“下一步”,Document number,25,四、分列 步骤3,选取拟分列后各字段数据格式。 可选类型有：常规、文本、日期，也可以选择其中一列或几列不导入,Document number,26,四、分列 完成后,原先第 I 列被拆分为I 、J、K三列 为后续数据整理作好准备。,Document number,27,五、分类汇总,分类汇总功能可以自动对所选数据进行汇总，并插入汇总行。汇总方式灵活多样：求和、平均数、最大数、标准方差等。,具体步骤为： 1、选定工作表，单击“数据” | “分类汇总”命令，将弹出“分类汇总”对话框（如右图）：选择分类字段（本例国家）及汇总方式（本例求和），及选定汇总项（本例开票数量、主营业务收入）。 2、单击“确定”按钮，结果为：,Document number,28,五、分类汇总,Document number,29,五、分类汇总,对数据分类汇总后，还可以恢复工作表的原始数据，方法为：再次选定工作表，单击“数据” | “分类汇总”命令，在弹出“分类汇总”对话框中，单击“全部删除”按钮，即可将工作表恢复到原始数据状态。 在对数据进行分类汇总后，仍可以对已分类字段继续按其他方式汇总，称之为分类汇总嵌套。以下演示为对单价取平均的嵌套。,Document number,30,五、分类汇总,Document number,31,六、合并计算,1、合并计算方式： A、使用三维公式进行合并计算 B、按位置进行合并计算 所有源数据具有同样的位置顺序，可以按位置进行合并计算。 C、按分类进行合并计算 如果要汇总计算一组具有相同行、列标志但以不同的方式组织数据的工作表，可以按分类进行合并计算。 D、通过生成数据透视表进行合并计算,Document number,32,六、合并计算,2、不同合并计算方式的使用范围,Document number,33,六、合并计算,3、使用三维公式合并计算 即通过输入合并计算公式，公式中的引用应指向每张工作表中待合并数据所在单元格，即可进行合并计算。 如下图，B4单元格即为一合并公式。,Document number,34,六、合并计算,4、按位置进行合并计算,Document number,35,六、合并计算,5、按分类进行合并计算,按分类汇总通常按首行和最左端为数据标志，创建合并计算表。,Document number,36,七、数据透视表,1、作用： 数据透视表可以全面的对数据清单进行重新组织和统计数据。 2、定义： 数据透视表是一种对大量数据进行快速汇总和建立交叉列表的交互式表格，其不仅可以转换行列以显示源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据用户的需要显示区域中的细节数据。,Document number,37,七、数据透视表,3、特点： EXCEL提供了向导功能，易于建立数据透视表。 真正的按用户设计的格式来完成数据透视表的建立。 当原始数据更新后,只需单击“更新数据”按钮，数据透视表就会自动更新数据 当用户认为已有的数据透视表不理想时，可以方便的修改数据透视表。,Document number,38,七、数据透视表 4、用法：步骤一,Document number,39,七、数据透视表,4、用法： 步骤二：,步骤三：,Document number,40,七、数据透视表,4、用法： 布局 拖动,Document number,41,七、数