Excel常用函数及数据透视表的应用--课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,PPT课件,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,PPT课件,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,Excel,常用函数,及,透视表的应用,1,PPT课件,Excel常用函数及透视表的应用1PPT课件,EXCEL,几个常用函数,重要概念：绝对引用与相对引用,求和有关的函数的应用,排,位,函数RANK,文本函数（取出字符串中的部分字符）,文本函数,TRIM,文本函数,(CONCATENATE),及连缀字符“,&,”,查找和引用函数,VLOOKUP,2,PPT课件,EXCEL几个常用函数重要概念：绝对引用与相对引用2PPT课,重要概念：绝对引用与相对引用,Excel公式中一个很重要的概念就是绝对引用与相对引用,下面通过几幅图来讲解一下,相对引用,：,如图，在C6单元格输入“=A1”,，,引用了A1单元格的内容,；,向下向,右,拖动填充,，随着单元格的变化，拖动填充的单元格内容也会变化，这就是相对引用。,3,PPT课件,重要概念：绝对引用与相对引用Excel公式中一个很重要的概念,重要概念：绝对引用与相对引用,绝对,引用,：,还是这个表格，在C6单元格中输入“=A1”后按,F4,会出现绝对引用的符号,“=$A$1”,，无论向下向右拖动填充单元格，填充的内容都与A1单元格相同，这就是绝对引用。,4,PPT课件,重要概念：绝对引用与相对引用绝对引用：4PPT课件,重要概念：绝对引用与相对引用,混合,引用,（,绝对引用与相对引用结合使用,）,：,绝对引用中$相当于一把锁，我们都知道单元格地址是由行和列组成的，在绝对引用中，当在C6单元格中输入“=A1”按F4后，会变成=$A$1,即锁定行与列，如果我们只需要绝对引用行或者绝对引用列的时候，我们需要怎么办呢？很简单，只需要再次按F4，就会出现=A$1或者=$A1。,应用技巧：,神奇的,F4,5,PPT课件,重要概念：绝对引用与相对引用混合引用（绝对引用与相对引用结合,重要概念：绝对引用与相对引用,【混合引用】,相对引用A列，绝对引用第1行:,=A$1,6,PPT课件,重要概念：绝对引用与相对引用【混合引用】相对引用A列，绝对引,重要概念：绝对引用与相对引用,【混合引用】绝,对引用A列，相对引用,第,1行:,=$A1,7,PPT课件,重要概念：绝对引用与相对引用【混合引用】绝对引用A列，相对引,01-,求和有关的函数的应用,1,、,Alt+=：快速批量求和,（一键求和）,方法：,1、选中数据源。,2、快捷键：Alt+=。其实Alt+=就是Sum函数的快捷键而已。,.,8,PPT课件,01-求和有关的函数的应用1、Alt+=：快速批量求和（一键,01-,求和有关的函数的应用,1,、,SUM,函数,1,）行或列求和：,=SUM(H3:H12),2,）区域求和：,=SUM(D3:D12,F3:F12),注意：,1,、,SUM,函数中的参数，即被求和的单元格或单元格区域不能超过,30,个。否则,Excel,就会提示参数太多。,2,、对需要参与求和的某个常数，可用“,=SUM,（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。,9,PPT课件,01-求和有关的函数的应用1、SUM函数9PPT课件,2,、,SUMIF,函数,SUMIF,函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式。,常规用法：,=SUMIF(,条件区域,指定的条件,求和区域）,要计算某企业某贷款余额情况。公式输入为,=SUMIF,（,A2:A16,企业代码,D2:D16),其中“,A2:A16”,为提供逻辑判断依据的单元格区域，“企业代码”为判断条件即只统计,A2:A16,区域中编号为该“企业代码”的单元格，,D2:D16,为实际求和的单元格区域。,02-,求和有关的函数的应用,10,PPT课件,2、SUMIF函数 02-求和有关的函数的应用10PP,Sumif：1,、,单条件求和。,举例,：计算男、女生的成绩之和。,方法：在目标单元格中输入公式：=SUMIF(C3:C9,N3,K3:K9)。,解读：单条件求和函数为Sumif，其语法结构为：=Sumif（条件范围，求和条件，求和范围）。当条件范围和求和范围相同时，求和范围课省略。,02-,求和有关的函数的应用,11,PPT课件,Sumif：1、单条件求和。举例：计算男、女生的成绩之和。0,Sumif：,2：隔列求和。举例：计算全年计划数与实际数,方法：在目标单元格中输入公式：=SUMIF($D$3:$K$3,L$3,$D4:$K4)。,解读：1、条件范围为D3:K3，而且不会变化，所以采用绝对引用的形式。,条件为“计划”和“实际”，要根据不同的情况进行变化，而“计划”和“实际”在不同列，同一行，故列采用相对引用的形式，而行采用绝对引用的形式。求和范围为数据区D4：K4，列的范围不会发生变化，而行要变化，所以采用绝对和相对引用相结合的形式。,02-,求和有关的函数的应用,12,PPT课件,Sumif：2：隔列求和。举例：计算全年计划数与实际数02-,Sumif：3,：不同列求和。,举例：计算出“地区”总的销售额,方法：在目标单元格中输入公式：=SUMIF(D:L,O3,C:K)。,解读：从公式中我们可以看出，使用的函数为单条件求和Sumif函数。,02-,求和有关的函数的应用,13,PPT课件,Sumif：3：不同列求和。02-求和有关的函数的应用13P,2,、,SUMIF,函数,提示：,1,、指定条件时，可以使用通配符。,2,、求和区域和条件区域要大小一致，并且要注意两者的起始位置需保持一致。,02-,求和有关的函数的应用,14,PPT课件,2、SUMIF函数02-求和有关的函数的应用14PPT课件,03-,排,位,函数RANK,3,、,RANK,(number,ref,order),Number,：表示需要排位的,数,值,ref,：可以是,数组,或也可以是单元格的引用，表示排,名次,的,范围,。,Order,：其值是可选的，即可以填也可以为空。,如果,order,为,0,或省略,Excel,对数字的排名是基于,ref,降序排列而来。如果,order,为,1,，,Excel,对数字的,排位,是基于,ref,升序排列而来。,简单来说：,RANK,(,用来排序的数值，整个数值序列，,0(,降序,)/1(,升序,),15,PPT课件,03-排位函数RANK3、RANK(number,ref,03-,排,位,函数RANK,案例,1,：对成绩进行排序（从大到小，降序）。,因为要下拉进行公式的复制，所以要添加绝对引用。输入公式=RANK(D2,$D$2:$D$8,0)。,16,PPT课件,03-排位函数RANK案例1：对成绩进行排序（从大到小，降序,03-,排,位,函数RANK,案例,2,：rank函数对不连续单元格排名：不连续的单元格，第二个参数需要用括号和逗号形式连接起来。,输入公式=RANK(B5,(B5,B9,B13,B17),0),17,PPT课件,03-排位函数RANK案例2：rank函数对不连续单元格排名,04-,文本函数（取出字符串中的部分字符）,取字符串函数,MID,：,=MID(text,start_num,num_chars),text,是包含要提取字符的文本串，,start_num,是文本中要提取的第一个字符的位置，,num_chars,指定要提取的字符数,LEFT,：,=LEFT(text,num_chars),text,是包含要提取字符的文本串，,num_chars,指定要由,LEFT,所提取的字符数,RIGHT,：,=RIGHT(text,num_chars),text,是包含要提取字符的文本串，,num_chars,指定希望要,RIGHT,提取的字符数,从字符串,”This is an apple”,分别取出字符“,This,”，“,apple,”，“,is,”,18,PPT课件,04-文本函数（取出字符串中的部分字符）取字符串函数从字符串,05-,文本函数,TRIM,清洗函数：,TRIM,/应用情景/,在处理某系统中导出的数据时，你是否因为数据首尾存在空格、又或者存在换行符，而心生烦恼？而手动清除这些特殊格式真的是太麻烦了！TRIM函数可以很轻松的将单元格中数据首尾的空格去除；结合CLEAN函数，还可以消除换行符。,函数语法：,=TRIM(text),函数功能：,1、可以去除引用目标的首尾空格，但对存在于字符间的空格无效；（注：要去除字符之间的空格可用,SUBSTITUTE,函数）,2、使用该函数后，数据类型会变为“文本”。,19,PPT课件,05-文本函数TRIM清洗函数：TRIM19PPT课件,05-,文本函数,TRIM,/函数应用举例/,1、仅消除数据首尾的空白,C4,=TRIM,（,B4,）,原始数据及修正（删除空格）后数据展示：,20,PPT课件,05-文本函数TRIM/函数应用举例/20PPT课件,05-,文本函数,TRIM,/函数应用举例/,2、删除空格的同时删除换行符。这个操作的完成光靠TRIM函数是不行的，需要CLEAN函数的助攻；,实现该功能的公式如下：,=CLEAN(TRIM(text),原始数据及修正（删除空格）后数据展示：,21,PPT课件,05-文本函数TRIM/函数应用举例/21PPT课件,0,6,-,文本函数,CONCATENATE,及连缀字符“,&,”,连接函数：,CONCATENATE(text1,text2 ),将若干个字符合并至一个字符项中,1,）,2001,12,21,合并写成,2001,年,12,月,21,日,=CONCATENATE(B3,年,C3,月,D3,日,),2,）合并写成,2000year,=CONCATENATE(A2,B2),22,PPT课件,06-文本函数CONCATENATE及连缀字符“&”连接函数,07-,查找和引用函数,VLOOKUP,大众情人Vlookup函数语法：,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),简单来说：,VLOOKUP(,要找谁,在哪儿找,返回第几列的内容,精确找还是近似找,),23,PPT课件,07-查找和引用函数VLOOKUP大众情人Vlookup函数,07-,查找和引用函数,VLOOKUP,应用实例,方法：在目标单元格中输入公式：=VLOOKUP(H3,B3:D9,3,0)。,解读：1、查找值为H3单元格的值，查找范围为B3:D9，返回B3:D9范围中第3列的值，即D列的值，匹配模式为精准匹配。,24,PPT课件,07-查找和引用函数VLOOKUP应用实例24PPT课件,数据透视表应用,25,PPT课件,数据透视表应用25PPT课件,在学习excel数据透视表之前，我们有必要了解它的概念。,什么是数据透视表呢？,通俗地讲：,数据透视表是一种可以把明细表分类汇总，而且可以随意改变汇总模式的一种工具。,什么是,数据透视表,26,PPT课件,什么是数据透视表26PPT课件,1、待统计数据条数很多，成千上万条记录，甚至几十万条记录需要统计，这时用函数公式计算的速度缓慢，导致表格卡顿。,2、待统计的数据包含很多类别，需要分类统计，合并同类项等操作时。,3、待统计的数据需要按照不同的维度整理成多种格式的报表，还可能要根据领导的指示变换行列布局，增加新的统计维度。,4、原始数据中的字段分裂过于繁杂，需要把属性相近的一些数据归类为大类别数据再进行统计和分析，以便用户更清洗直观的查看数据趋势。,5、当数据不但要形成报表，还需要配套的动态图表辅助演示时。,什么时候需要用数据透视表,27,PPT课件,1、待统计数据条数很多，成千上万条记录，甚至,6、当需要数据报表中的某个数字进一步的分析，将其拆分为更详细的数据记录时。,7、当原始数据经常更新，而统计报表也需要同步更新时。,8、当查看数据报表时，需要多组条件筛选器，要求根据不同的筛选条件动态更新表格中的数据时。,9、当需要把数据按照某个维度拆分为多个分表，放置到多个工作表中展示时。,10、当需要统计同比、环比增长额和百分比时。,什么时候需要用数据透视表,28,PPT课件,什么时候需要用数据透视表28PPT课件,1、计算速度快，计算上万条记录也仅需1秒搞定。,2、交互式动态演示，集数据透视表和数据透视图浑然一体，动态图表展示。,3、计算维度丰富，求和项、计数项、平均值、百分比、标准差等全部支持。,4、报表布局变换灵活，仅需拖曳字段到对应位置即可同步更新报表布局。,5、数据透视能力强，支持指定数据向下钻取。,6、最后一个重要的优势就是简单易学。,数据透视表的优势,29,PPT课件,1、计算速度快，计算上万条记录也仅需1秒搞定。,制作数据透视表,原材料：以一张销售明细表为例,创建,数据透视表,30,PPT课件,制作数据透视表创建数据透视表30PPT课件,第1步 选取明细表，鼠标点在明细表内任何地方，以Excel2010版为例：插入选项卡-数据透视表,创建数据透视表,31,PPT课件,第1步 选取明细表，鼠标点在明细表内任何地方，以Excel2,第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。在向导的第一步一般是默认选项，不需要设置，直接点确定即可。,创建数据透视表,32,PPT课件,第2步 执行“数据透视表”命令后，会进入数据透视表创建向导。,第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有一个数据透视表空白区域，就象盖楼房地基已打好，需要我们安排具体的房间位置。下面是添加数据透视表项目,把地区拖动到行标签,把销售量和销售金额拖动到数值区域。,创建数据透视表,33,PPT课件,第3步 进行前两步后，会自动新建一个工作表，且在工作表中会有,拖动完成后，一个数据透视表的雏形展现在我们的面前，如下图所示。,创建数据透视表,34,PPT课件,拖动完成后，一个数据透视表的雏形展现在我们的面前，如下图所示,在制作数据透视表时，需要注意以下几点：,1 空字段名包括空列，无法制作数据透视表,2 相同的字段名，会自动添加序号，以示区别,3 字段所在行有合并单元格，等同于空字段，也无法创建数据透视表,4 如果有空行，会当成空值处理,5,是否存在非法日期和文本型数值,什么样的格式才能做出数据透视表,35,PPT课件,什么样的格式才能做出数据透视表35PPT课件,制作成数据透视表之前，需要看看明细表是否存在下面的问题：,1、存在空列或没有行标题字段。,影响：明细表如果有标题为空，将无法制作数据透视表。,整理:把标题补充完整。,源表的整理,36,PPT课件,制作成数据透视表之前，需要看看明细表是否存在下面的问题：源表,2、存在相同的标题。,影响：数据透视表会自动添加序号以区分,整理：尽量不要存在相同的列标题。,源表的整理,37,PPT课件,2、存在相同的标题。源表的整理37PPT课件,3、存在合并单元格,影响：合并单元格除第1个格外，其他均作为空值处理。,整理：取消合并单元格，填充完整。,源表的整理,38,PPT课件,3、存在合并单元格源表的整理38PPT课件,4、存在非法日期。,影响：生成的数据透视表中，无法按日期格式进行年、月、日格式的筛选和组合。,整理：转换成excel认可的日期格式。方法：选取列-分列-第3步选日期,源表的整理,39,PPT课件,4、存在非法日期。源表的整理39PPT课件,5、存在文本型数字,影响：文本型数字将无法在数据透视表中正确求和。,整理：转换成数值型数字。方法:选取列-分列-完成,源表的整理,40,PPT课件,5、存在文本型数字源表的整理40PPT课件,综上所述：,一个无空标题行、无重复行标题、无合并单元格、无非法日期和数字格式的数据表，才是一个标准的数据透视表的数据源。,源表的整理,41,PPT课件,源表的整理41PPT课件,光看不练假把式,Thanks,42,PPT课件,42PPT课件,