《excel常用函数及数据整理技巧相当全面.ppt》由会员分享，可在线阅读，更多相关《excel常用函数及数据整理技巧相当全面.ppt（49页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、EXCEL常用函数及数据整理技巧常用函数及数据整理技巧1内容概要内容概要1、EXCEL函数应用函数应用2、EXCEL数据整理方法与技巧数据整理方法与技巧3、快捷键应用、快捷键应用2第一部分第一部分EXCEL常用函数常用函数3一、一、EXCEL函数类型函数类型p 文本函数文本函数p 日期和时间函数日期和时间函数p 数学和三角函数数学和三角函数p 统计函数统计函数p 查询和引用函数查询和引用函数p 逻辑函数逻辑函数p 信息函数信息函数p 财务函数财务函数p 工程函数工程函数p 数据库函数数据库函数p 自定议函数自定议函数4二、文本函数二、文本函数通过文本函数，可以在公式中处理字符串。例如可以转换文
2、本通过文本函数，可以在公式中处理字符串。例如可以转换文本格式、截取特定文本字符、查找替代特定文本等。格式、截取特定文本字符、查找替代特定文本等。1、转换类、转换类大小写转换类 upper();lower();proper()数字、文本等格式间转换 dollar();rmb();text()；value()2、截取、截取/合并类合并类截取类 left()/leftb()；right()/rightb()；mid()/midb();len()/lenb()合并类 concatenate()3、文本整理类、文本整理类 trim()/clean()4、字符查找、比较、替代类、字符查找、比较、替代类查找
3、类 find();findb();search();searchb()比较类 exact()替代类 replace();substitute()5三、日期与时间函数三、日期与时间函数数据表处理过程中，日期与时间函数是相当重要的处理依据。数据表处理过程中，日期与时间函数是相当重要的处理依据。1、取当前系统时间、日期信息函数、取当前系统时间、日期信息函数 now();today()2、取得日期、时间的部分字段值函数、取得日期、时间的部分字段值函数 year();month();day();hour();minute();second()3、日期计算函数、日期计算函数 datedif();days3
4、60()6四、数学和三角函数四、数学和三角函数1、与求和有关的函数与求和有关的函数SUM(number1,number2,.)行列求和、区域求和（参数最多30个）SUMIF(range,criteria,sum_range)根据指定条件对若干单元格求和SUBTOTAL(function_num,ref1,ref2,.)返回数据清单分类汇总7四、数学和三角函数四、数学和三角函数2、数字取舍类函数数字取舍类函数ROUND(number,num_digits)：返回某个数字按指定位数舍入后的数字INT(number)：返回小数舍入后的整数，即向下取整用于四舍五入时需要技巧即：加0.5后取整ABS(n
5、umber)：返回数字的绝对值。TRUNC(number,num_digits):将数字的小数部分截去，返回整数。8五、统计函数五、统计函数作为作为EXCEL函数中数量最为庞大的一类，统计类函数提供了丰函数中数量最为庞大的一类，统计类函数提供了丰富的概率与统计函数功能。：富的概率与统计函数功能。：1、用于求平均值的函数、用于求平均值的函数AVERAGE(number1,number2,.)：返回参数的平均值（算术平均值）AVERAGEA(value1,value2,.)：计算参数列表中数值的平均值（算数平均值）TRIMMEAN(array,percent)：返回数据集的内部平均值。2、求单元格
6、个数函数、求单元格个数函数COUNT(value1,value2,.)：返回包含数字以及包含参数列表中的数字的单元格的个数。COUNTA(value1,value2,.)：返回参数列表中非空值的单元格个数。9五、统计函数五、统计函数3、求区域中数据分布频率函数、求区域中数据分布频率函数FREQUENCY(data_array,bins_array)：以一列垂直数组返回某个区域中数据的频率分布。4、线性回归分析函数、线性回归分析函数CORREL(array1,array2)：返回单元格区域 array1 和 array2 之间的相关系数。FORECAST(x,known_ys,known_xs)
7、：根据已有的数值计算或预测未来值。5 5、其他特定需求统计函数、其他特定需求统计函数最大最小值 MAX(number1,number2,.)/MIN(number1,number2,.)第K大与第K小 LARGE(array,k)/SMALL(array,k)中位数 MEDIAN(number1,number2,.)排名 RANK(number,ref,order)10六、查询与引用函数六、查询与引用函数1、绝对引用与相对引用、绝对引用与相对引用绝对引用在复制公式时不希望EXCEL调整引用，须使用绝对引用相对引用在创建公式时，单元格或单元格区域的引用通常是包含公式的单元格的相对位置。2、引用的
8、表示方法、引用的表示方法: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)在表格或数值数组的首列查找指定
9、的数值，并由此返回表格或数组当前行中指定列处的数值11七、逻辑函数七、逻辑函数用于判断真假值，或者进行复合检验的用于判断真假值，或者进行复合检验的EXCEL函数函数,称之为逻称之为逻辑函数。辑函数。1、表达、表达“且且”、“或或”、“不不”关系的逻辑函数关系的逻辑函数AND(logical1,logical2,.)所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。OR(logical1,logical2,.)：在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。NOT(logical)：对参
10、数值求反。当要确保不等于某一特定值时，可以使用此函数。2、if()条件函数条件函数IF(logical_test,value_if_true,value_if_false)执行真假值判断，根据逻辑计算的真假值，返回不同结果。最多嵌套7层条件。12八、信息函数八、信息函数专门用来返回某些指定单元格或区域等的信息，比如单元格的专门用来返回某些指定单元格或区域等的信息，比如单元格的内容、格式、个数等，这类函数统称为信息类函数。内容、格式、个数等，这类函数统称为信息类函数。主要介绍主要介绍IS类函数，常与类函数，常与IF函数配合使用函数配合使用。ISBLANK(value)值为空白单元格时,返回tru
11、e。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。13九、财
12、务函数九、财务函数在此部分结合常用财务管理理论，在此部分结合常用财务管理理论，EXCEL提供了提供了16个具体财务个具体财务函数，主要分为投资理财类函数与折旧类函数。函数，主要分为投资理财类函数与折旧类函数。折旧类函数折旧类函数SLN(cost,salvage,life)返回某项资产在一个期间中的线性折旧值。SYD(cost,salvage,life,per)返回某项资产按年限总和折旧法计算的指定期间的折旧值。DDB(cost,salvage,life,period,factor)使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。14第二部分第二部分EXCEL数据整理技巧数据
13、整理技巧15一、一、EXCEL常用数据整理方法与技巧常用数据整理方法与技巧1、排序、排序2、筛选、筛选3、分列、分列4、分类汇总、分类汇总5、合并计算、合并计算6、数据透视表、数据透视表数据清单：指包含一组相关数据的一系列工作表数据行数据清单应遵循一般准则：每张工作表使用一个数据清单使清单独立：数据清单与其他数据间至少留一个空行或空列数据清单内避免空行与空列16二、数据清单的排序二、数据清单的排序1、排序方式、排序方式简单排序与多重排序2、排序方法、排序方法升序、降序（默认以拼音排序）17三、数据清单的筛选三、数据清单的筛选1、自动筛选、自动筛选适用于简单条件，通常是在一个数据清单的一列中查找
14、相同的值。也可以用自定义筛选方式快速查找出符合多重条件的记录。18三、数据清单的筛选三、数据清单的筛选2、高级筛选、高级筛选如果数据清单字段较多，筛选条件也较多，则可以使用高级筛选注意：A、要首先建立条件区域B、条件区域第一行是作为筛选条件的字段名，必须与数据清单中字段名完全相同C、条件区域其他行用于输入筛选条件D、条件区域与数据清单不能连接，须有一个空行或空列分离19三、数据清单的筛选三、数据清单的筛选2、高级筛选、高级筛选20四、分列四、分列如果某单元格或列包含数个可用信息，希望把其拆分在数个单元格内列示如果某单元格或列包含数个可用信息，希望把其拆分在数个单元格内列示时，则用到时，则用到“
15、分列分列”工具。工具。如下图中：开票期间项如下图中：开票期间项销售订单销售订单物料物料描述描述售达方售达方国家国家订单类型订单类型开票数开票数量量 主营业务收入主营业务收入 开票期间开票期间 30763A055ZK6129H-2成品105271叙利亚ZOR22 1,940,539.86 2006.001 一月 200632719B063ZK6118HA-3成品201267阿联酋ZOR11 721,994.98 2006.004 四月 200632720B063ZK6118HA-3成品201267阿联酋ZOR11 721,994.98 2006.006 六月 200631939E055ZK683
16、1HE成品104594瑞士ZOR11 482,243.18 2006.002 二月 200631941E055ZK6831HE成品104594瑞士ZOR11 485,914.99 2006.004 四月 200631958H039ZK6896HG成品104594瑞士ZOR11 490,271.69 2006.006 六月 200633459E055ZK6831HE成品111658毛里求斯ZOR11 347,204.44 2006.006 六月 200630469A055ZK6129H-2成品104834埃及ZOR12 1,378,882.65 2006.001 一月 200630470C081
17、ZK6100HB成品104834埃及ZOR14 2,167,909.94 2006.001 一月 200631612C081ZK6100HB成品104834埃及ZOR16 3,739,029.35 2006.002 二月 200632025E054ZK6860HA成品104834埃及ZOR16 2,742,313.28 2006.001 一月 200631702F133ZK6780D成品106648美国ZOR24 1,168,198.43 2006.002 二月 200621四、分列四、分列22四、分列四、分列步骤步骤1分列的两种类型：1、按分隔符号分列2、按固定列宽分列操作时应具体按被分列项
18、特征，选取不同分列方法。在本例中，两种方法均可：可以以空格为分列符号分列；也可以用固定列宽人为添加分列线分列23四、分列四、分列步骤步骤2上步中，假设选分隔符号，则弹出步骤2，选取具体分隔符号（打勾）后，会在预览中显出分列线，如无误，点击“下一步”24四、分列四、分列步骤步骤3选取拟分列后各字段数据格式。可选类型有：常规、文本、日期，也可以选择其中一列或几列不导入25四、分列四、分列完成后完成后原先第 I 列被拆分为I、J、K三列为后续数据整理作好准备。26五、分类汇总五、分类汇总分类汇总功能可以自动对所选数据进行分类汇总功能可以自动对所选数据进行汇总，并插入汇总行。汇总方式灵活多汇总，并插入
19、汇总行。汇总方式灵活多样：求和、平均数、最大数、标准方差样：求和、平均数、最大数、标准方差等。等。具体步骤为：1、选定工作表，单击“数据”|“分类汇总”命令，将弹出“分类汇总”对话框（如右图）：选择分类字段（本例国家）及汇总方式（本例求和），及选定汇总项（本例开票数量、主营业务收入）。2、单击“确定”按钮，结果为：27五、分类汇总五、分类汇总28五、分类汇总五、分类汇总 对数据分类汇总后，还可以恢复工作表的原始数据，方法对数据分类汇总后，还可以恢复工作表的原始数据，方法为：再次选定工作表，单击为：再次选定工作表，单击“数据数据”|“分类汇总分类汇总”命令，在弹命令，在弹出出“分类汇总分类汇总”
20、对话框中，单击对话框中，单击“全部删除全部删除”按钮，即可将工按钮，即可将工作表恢复到原始数据状态。作表恢复到原始数据状态。在对数据进行分类汇总后，仍可以对已分类字段继续按其在对数据进行分类汇总后，仍可以对已分类字段继续按其他方式汇总，称之为分类汇总嵌套。以下演示为对单价取平均他方式汇总，称之为分类汇总嵌套。以下演示为对单价取平均的嵌套。的嵌套。29五、分类汇总五、分类汇总30六、合并计算六、合并计算1、合并计算方式：、合并计算方式：A、使用三维公式进行合并计算B、按位置进行合并计算 所有源数据具有同样的位置顺序，可以按位置进行合并计算。C、按分类进行合并计算 如果要汇总计算一组具有相同行、列
21、标志但以不同的方式组织数据的工作表，可以按分类进行合并计算。D、通过生成数据透视表进行合并计算31六、合并计算六、合并计算2、不同合并计算方式的使用范围、不同合并计算方式的使用范围32六、合并计算六、合并计算3、使用三维公式合并计算、使用三维公式合并计算即通过输入合并计算公式，公式中的引用应指向每张工作表中待合并数据所在单元格，即可进行合并计算。如下图，B4单元格即为一合并公式。33六、合并计算六、合并计算4、按位置进行合并计算、按位置进行合并计算34六、合并计算六、合并计算5、按分类进行合并计算、按分类进行合并计算按分类汇总通常按首行和最左端为数据标志，创建合并计算表。35七、数据透视表七、
22、数据透视表1、作用：、作用：数据透视表可以全面的对数据清单进行重新组织和统计数据。2、定义：、定义：数据透视表是一种对大量数据进行快速汇总和建立交叉列表的交互式表格，其不仅可以转换行列以显示源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据用户的需要显示区域中的细节数据。36七、数据透视表七、数据透视表3、特点：、特点：EXCEL提供了向导功能，易于建立数据透视表。真正的按用户设计的格式来完成数据透视表的建立。当原始数据更新后,只需单击“更新数据”按钮，数据透视表就会自动更新数据当用户认为已有的数据透视表不理想时，可以方便的修改数据透视表。37七、数据透视表七、数据透视表4、用法：
23、步骤一、用法：步骤一38七、数据透视表七、数据透视表4、用法：、用法：步骤二步骤二：步骤三步骤三：39七、数据透视表七、数据透视表4、用法：、用法：布局布局拖动40七、数据透视表七、数据透视表4、用法：、用法：布局布局41七、数据透视表七、数据透视表4、用法：、用法：布局确定后返回布局确定后返回步骤步骤3：选择新建工作表，选择新建工作表，并点击完成并点击完成42七、数据透视表七、数据透视表原始原始43七、数据透视表七、数据透视表格式化后（报表格式化后（报表7格式）：格式）：44第三部分第三部分EXCEL快捷键快捷键45EXCEL快捷键示例快捷键示例按下按下完成完成SHIFT+F11 或 ALT
24、+SHIFT+F1插入新工作表CTRL+PAGE DOWN移动到工作簿中的下一个工作表CTRL+PAGE UP移动到工作簿中的上一个工作表SHIFT+CTRL+PAGE DOWN选择工作簿中当前和下一个工作表SHIFT+CTRL+PAGE UP选择工作簿中的当前工作簿或上一个工作簿CTRL+F6 或 CTRL+TAB移动到下一工作簿或窗口CTRL+SHIFT+F6 或 CTRL+SHIFT+TAB移动到前一工作簿或窗口F6移动到已拆分工作簿中的下一个窗格SHIFT+F6移动到被拆分的工作簿中的上一个窗格箭头键向上、下、左或右移动单元格CTRL+箭头键移动到当前数据区域的边缘SHIFT+CTRL
25、+箭头键选择到当前数据区域的边缘SHIFT+箭头键+END+箭头键选择到当前数据区域的边缘46按下按下完成完成HOME移动到行首CTRL+HOME移动到工作表的开头CTRL+END移动到工作表的最后一个单元格，这个单元格位于数据区的最右列和最底行的交叉处（右下角）；或是与首单元格（一般为 A1）相对的单元格。PAGE DOWN向下移动一屏PAGE UP向上移动一屏ALT+PAGE DOWN向右移动一屏ALT+PAGE UP向左移动一屏F5显示“定位”对话框SHIFT+F5显示“查找”对话框SHIFT+F4重复上一次“查找”操作（等同于“查找下一个”）EXCEL快捷键示例快捷键示例47更多更多EXCEL快捷键参考快捷键参考48谢谢大家！谢谢大家！49