Excel高级技巧培训.pptx

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,Excel,培训,高级篇，杰出效率,版权所属,1,培训提纲,Excel,高级使用技巧,Excel,函数使用技巧,Excel,数据分析技巧,Excel,图表,技巧,2,Excel,高级使用技巧,构思和设置表结构,建立和输入数据,编辑和修饰表格,简单使用和分析,保存表格数据,打印输出数据,增加,3,Excel,高级使用技巧,-,构思及表结构设置,制作过程,重点,技巧,构思和设置结构,控制表格结构的合理性,数据项目拆（独立列）与逻辑关系,建立和输入数据,把握表格数据的准确性,输入技巧与建立和复制公式关系,编辑和修饰表格,增强表格数据的可读性,编辑对象、数字、对齐条件格式等,简单使用和分析,有效运用数据的分析性,组织数据、创建、编辑和修饰图表,保存表格数据,正确进行文件归档保存,保存、另存为及安全性,打印输出数据,掌握表格数据打印输出,小表格、大表格及超表格的打印技巧,4,构思及表结构设置,表结构设置指北针,凡,须独立管理的项目，必须单独设置项目名称，独占一列，避免混合设置,管理项目的独立性必须与扩展性结合考虑，以方便统计分析,表格结构设计必须考虑后期统计分析的应用状态,。,控制,原始数据输入准确性的解决方法,用调用法控制原始数据的准确性,用计算法控制加数据的准确性,使用,校对工具,5,其他技巧,-,录入技巧下拉菜单,一、直接输入：,1.,选择要设置的单元格，譬如,A1,单元格；,2.,选择菜单栏的“数据”“有效性”出现“数据有效性”弹出窗口；,3.,在“设置”选项中“有效性条件”“允许”中选择“序列”右边的“忽略空值”和“提供下拉菜单”全部打勾在“来源”下面输入数据，譬如“,1,2,3,4,5,6,7,8,9”(,不包括双引号，分割符号“,”,必须为半角模式,),按“确定”就,OK,了，再次选择该,A1,单元格，就出现了下拉菜单。,6,其他技巧,-,录入技巧下拉菜单,如果同一工作表的某列就是下拉菜单想要的数据，譬如引用工作表,Sheet1,的,B2,：,B5,，,B2,：,B5,分别有以下数据：,1,、,2,、,3,、,4,，操作如下：,1.,选择要设置的单元格，譬如,A1,单元格；,2.,选择菜单栏的“数据”“有效性”出现“数据有效性”弹出窗口；,3.,在“设置”选项中“有效性条件”“允许”中选择“序列”右边的“忽略空值”和“提供下拉菜单”全部打勾在“来源”下面输入数据“,=$B$2:$B$5”,，也可以按右边带红色箭头的直接选择,B2,：,B5,区域按“确定”就,OK,了，再次选择该,A1,单元格，就出现了下拉菜单。,7,其他技巧,-,定义工作表名称,1.CTRL+F3,2.,=OFFSET,(,费用明细,!$,A$1,COUNTA,(,费用明细,!$,A:$A),COUNTA,(,费用明细,!$,1:$1),8,其他,高级,技巧,功能,命令,选择整个单元格范围,Ctrl+Shift+*,快速关闭多个文件,按住,Shift,键，打开“文件”菜单，单击“全部关闭”,选定多个工作表,一组相邻的工作表，可先选第一个表，按住,Shift,键，再单击最后一个表的标签；选不相邻的工作表，要按住,Ctrl,键,对多个工作表快速编辑,选定多个工作表，可在相同位置设置,1.,页面设置,2.,输入相同的数据,3.,快速设置,4.,输入公式,快速浏览长工作簿,按下“,Ctri+Home”,键可以回到当前工作表的左上角,(,即,A1,单元格,),，按下“,Ctrl+End”,键可以跳到工作表含有数据部分的右下角。,同一个单元格换行,Alt+,回车键,巧变文本为数字,在空白的单元格中填人数字,1,，然后选中这个单元格，执行“复制”命令，然后再选中所要转换的范围，选择“选择性粘贴”中的“乘”,在单元格中输入,0,值,设置单元格格式”，在接着出现的界面中选“数字”标签页，在列表框中选择“文本”,分散对齐,在,Excel,表格中输入人名时为了美观，我们一般要在两个字的人名中间空出一个字的间距,选中该列，点击“格式单元格对齐”，在“水平对齐”中选择“分散对齐,文件加密,另存新档,/,工具,/,一般选项,部分加密,选取整篇文档,/,右键储存格格式,/,保护,/,解锁,/,选区需要加密区域,/,右键储存格格式,/,保护,/,加锁,/,菜单工具,/,保护文档,9,Excel,函数使用技巧,Excel,数学函数,Excel,逻辑函数,Excel,文本函数,Excel,日期时间函数,Excel,查询与引用,10,函数类,函数,函数名称,常用否,Excel,的数学函数,ABS,返回参数的绝对值,N,COUNTIF,计算给定区域内满足特定条件的单元格的数目,Y,INT,返回实数舍入后的整数值,N,ROUND,返回某个数字按指定位数舍入后的数字,Y,SUBTOTAL,返回数据清单或数据库中的分类汇总,N,SUM,返回某一单元格区域中所有数字之和,N,SUMIF,根据指定条件对若干单元格求和,Y,Excel,函数使用技巧,-,数学函数,函数,函数,函数名称,显示,常用,Excel,逻辑函数,COUNTIF,根据指定条件对若干单元格次数,COUNTIF(B4:B10,90),Y,AND,其中,Logical1,logical2,.,表示待检测的,1,到,30,个条件值，各条件值可能为,TRUE,，可能为,FALSE,。参数必须是逻辑值，或者包含逻辑值的数组或引用,AND(logical1,logical2,.),，,Y,OR,OR,函数指在其参数组中，任何一个参数逻辑值为,TRUE,，即返回,TRUE,OR(B1:B3),Y,IF,IF,函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此,If,函数也称之为条件函数。它的应用很广泛，可以使用函数,IF,对数值和公式进行条件检测。,IF(logical_test,value_if_true,value_if_false),。,Y,Excel,函数使用技巧,-,逻辑,函数,函数类,函数,函数名称,显示,常用,Excel,函数,引,用之文本函数,CONCATENATE,将若干文字串合并到一个文字串中。,CONCATENATE(text1,text2,.),Y,DOLLAR,依照货币格式将小数四舍五入到指定的位数并转换成文字。,DOLLAR,或,RMB(number,decimals),EXACT,该函数测试两个字符串是否完全相同。如果它们完全相同，则返回,TRUE,；否则，返回,FALSE,。函数,EXACT,能区分大小写，但忽略格式上的差异。利用函数,EXACT,可以测试输入文档内的文字。,EXACT(text1,text2),LEFT,LEFT,基于所指定的字符数返回文本串中的第一个或前几个字符。,LEFT(text,num_chars),Y,MID,MID,返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。,MID(text,start_num,num_chars),Y,RIGHT,RIGHT,根据所指定的字符数返回文本串中最后一个或多个字符。,RIGHT(text,num_chars),Y,TRIM,除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数,TRIM,。,TRIM(text),Y,VALUE,将代表数字的文字串转换成数字。,VALUE(text),Excel,函数使用技巧,-,引用及文本,函数,函数类,函数,函数名称,显示,常用否,Excel,时间函数,MONTH,返回以系列数表示的日期中的月份。月份是介于,1,（一月）和,12,（十二月）之间的整数。,MONTH(serial_number),Y,TODAY,返回当前日期的系列数，系列数是,Microsoft Excel,用于日期和时间计算的日期,-,时间代码。,TODAY(),YEAR,返回某日期的年份。返回值为,1900,到,9999,之间的整数。,YEAR(serial_number),Y,Excel,函数使用技巧,-,时间,函数,函数类,函数,函数名称,显示,常用否,Excel,查询与引用函数,VLOOKUP,用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),Y,HLOOKUP,用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。,HLOOKUP(lookup_value,table_array,row_index_num,range_lookup),Y,TRANSPOSE,TRANSPOSE,用于返回区域的转置。函数,TRANSPOSE,必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与,array,的列数和行数相同。使用函数,TRANSPOSE,可以改变工作表或宏表中数组的垂直或水平走向。由于此处是以数组公式输入，因此需要按,CRTL+SHIFT+ENTER,组合键来确定为数组公式，此时会在公式中显示,。随即转置成功,TRANSPOSE(array),Excel,函数使用技巧,-,查询及引用,函数,Excel,函数使用技巧,-,函数案例讲解,1,案例详见插入,EXCEL,文档,函数类,函数,应用,常用否,语法格式,隐藏,函数,DATEDIF,返回两个日期之间的年,月,日,Y,DATEDIF(,起始日期,终止日期,Y/M/D),NUMBERSTRING,返回数字的大小写,N,NUMBERSTRING(,数字,1/2),DATESTRING,返回日期的中文,N,DATESTRING(,日期,),数学,函数,ABS,返回参数的绝对值,N,ABA(,数字,),COUNTIF,计算给定区域内满足特定条件的单元格的数目,Y,COUNTIF(,选择列,筛选规则,),ROUND,返回某个数字按指定位数舍入后的数字,Y,ROUND(,数字,保留位数,),SUMIF,根据指定条件对若干单元格求和,Y,SUMIF(,选择列,筛选规则,汇总计算列,),逻辑,引用,AND,同时设定条件，需满足其中所有条件,Y,AND(logical1,logical2,.)，,OR,同时设定条件，只满足其中一项条件即可,Y,IF,条件函数,Y,IF(,条件,符合条件返回值,不符合条件返回值,),。,VLOOKUP,用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,Y,VLOOKUP(,参照列,选择区域,返回第几列的值，,0/FALSE),16,Excel,函数使用技巧,-,函数案例讲解,2,案例详见插入,EXCEL,文档,函数类,函数,应用,常用否,语法格式,逻辑,引用,HLOOKUP,用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。,Y,HLOOKUP(,参照行,选择区域,返回第几行的值，,1/FALSE),RANK,返回某一数值在一列数值中的相对于其他数值的排位,Y,RANK（Number,ref,order）(ORDER0,升序，,order1,降序）,文本,函数,CONCATENATE,将若干文字串合并到一个文字串中。,N,CONCATENATE(text1,text2,.),LEFT,LEFT,基于所指定的字符数返回文本串中的第一个或前几个字符。,Y,LEFT(text,num_chars),MID,MID,返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。,Y,MID(text,start_num,num_chars),RIGHT,RIGHT,根据所指定的字符数返回文本串中最后一个或多个字符。,Y,RIGHT(text,num_chars),TRIM,除了单词之间的单个空格外，清除文本中所有的空格。在从其他应用程序中获取带有不规则空格的文本时，可以使用函数,TRIM,。,Y,TRIM(text),时间,函数,MONTH,返回以系列数表示的日期中的月份。月份是介于,1,（一月）和,12,（十二月）之间的整数。,Y,MONTH(serial_number),TODAY,返回当前日期的系列数，系列数是,Microsoft Excel,用于日期和时间计算的日期,-,时间代码。,TODAY(),YEAR,返回某日期的年份。返回值为,1900,到,9999,之间的整数。,Y,YEAR(serial_number),17,Excel,数据分析技巧,Excel,分级显示及分类汇总,Excel,数据透析表,18,Excel,数据分析技巧,-,分级显示及分类汇总,部门,工号,身份证号码,姓名,应纳税所得额,税率,速算,个人所得税,DSA0,0011,AAA,10,000.00,0.20,325,1,675.00,DSA0,0012,BBB,500.00,0.05,0,25.00,DSA0,0013,CCC,1,500.00,0.10,25,125.00,DSA0,0014,DDD,3,800.00,0.15,125,445.00,DSA0,汇总,15,800.00,475,2,270.00,GSA0,0015,EEE,4,000.00,0.15,125,475.00,GSA0,0016,FFF,800.00,0.10,25,55.00,GSA0,0017,GGG,1,200.00,0.10,25,95.00,GSA0,0018,HHH,650.00,0.10,25,40.00,GSA0,汇总,6,650.00,200,665.00,总计,22,450.00,675,2,935.00,19,数据透视表是,Excel,中最具技术性的复杂组件之一。,数据透视表的本质是将表格数据库变为动态数据汇总报告。,数据透视表的优势：,人性化：方便的操作和界面,灵活性：可以方便的得到任何类别的数据,Excel,数据分析,-,数据,透析,表,-,使用,数据透视表分析数据,20,如何生成数据透视表,下面我们以此数据来进行数据透视表的分析。,21,为数据透视表选择合适的数据,虽然,Excel,可以对任何数据生成数据透视表，但并不是所有的数据资料都可以从中受益。,一般而言，数据资料应为数据库格式，包含下面两种类型的字段：,数据（包括数字或文本）,类别：对数据的描述,数据的第一行要求为项目标题。,当数据列不包含数字时数据透视表默认为以计数的方式使用数据；当数据列为数字时默认为使用求和方式。,22,如何生成数据透视表,Step 1:,选择工具栏中的：数据,数据透视表和数据透视图。,23,如何生成数据透视表,Step 2:,一般情况按照默认的选项即可，单击下一步。,上部分选项选择数据来自本,Excel,文件，或其它数据类型，或多个,Excel,数据文件。,24,如何生成数据透视表,Step 3:,弹出对话框，选择将要生成数据分析表的数据源。,当活动单元格的位置在数据区域时，透视表会自动选择数据区域。,25,如何生成数据透视表,Step 4:,选择新建工作表，使数据透视表生成在一张新的工作表中，点击“完成”。,选择“布局”和“选项”可以设置一些数据透视表的模式，但不是必需的，所以如果是新手入门就当作没有这些键好了，但是我们也分别解释一下。,26,布局,在“布局”对话框里，可以将透视表的各字段安排在不同的位置。,但是这个步骤也可以在数据透视表建立后进行。,27,选项,数据透视表提供了丰富的“选项”。,列总计：以列汇总；,行总计：以行汇总；,打开时刷新：打开文件时自动进行数据刷新；,显示明细数据：可以显示每个数字对应的数据库中的数据；,28,如何生成数据透视表,Step 5:,生成新的一页工作表，并会弹出包含所有项目标题的对话框。,29,如何生成数据透视表,Step 6:,将项目标题拖到左侧表格的对应位置，一张数据透视表就完成了。,30,如何读取数据透视表,项目选项：选择不同组合下的数据。,点击右键，对交叉表进行字段分析，如求和，计数，最大值等,31,数据透视表的组成部分,列字段。如除主详细特征；,行字段。如,time,；,数据区域。中间的数字；,总数求和。如合计；,元素。如个人；,条件。如,city;,字段列表。,32,移动,在数据透视表中无法进行普通的操作，例如插入或删除一行或一列，也不能对数字进行硬性修改。,如何移动数据透视表中的项目？,将鼠标移到想要移动的项目，点右键，选择“顺序”，然后选择想要移动方向即可。,注意只能移动项目，而不能仅仅移动数据。,33,手工组合,组合项目是数据透视表的一个有用特性。,如何将选项进行组合？,选择要组合的元素，点击右键，选择“组合显示明细数据”,“组合”，出现新的项目名称“,city2”,，将生成的“数据组,1”,改为“重点城市”。,这样就可以得到分“重点城市”和“其他城市”的数据。,34,手工组合,35,自动组合,当被组合项为数字时，可以进行自动组合。,例如日期可以自动选择按照月、季度、年等组合；,收入，年龄等都可以按照自己指定的区间段进行组合；,当进行手工组合时会生成新的元素，如对“收入”进行组合会生成“收入,2”,的元素；在进行自动组合时没有新的元素生成，组合数据替代了详细数据；因此建议在原数据库中复制一列数据。,36,插入计算字段,数据透视表有自我进行计算的功能，这种功能叫做“插入计算字段”。,例如在数据透视表中有销售额和销量的数据，则可以通过计算来得到单价的数据。,步骤：“数据透视表工具栏”,”公式”计算字段”,在“名称”写入新建数据的名称；,在“公式”内键入计算公式；,确定。,37,数据透视表的其他,阅读详细数据,对于数据透视表产生的数据可以查看详细的数据产生来源，具体的方式是双击此数。,例如数据透视表计算出库中有,3,个样本符合某条件，那么通过双击此数可以得到具体是哪,3,个样本符合这个条件，并在新的工作表中显示。,在双击前请保证“表格选项”中的“显示明细数据”已被选择。,设置报告格式,如果需要直接把数据透视表作为报告提交，那么一个正规的格式是必须的，请在“数据透视表工作栏”中选择“设置报告格式”，选择合适的个是显示数据透视表,选择显示元素,点击数据透视表中的任意列字段和行字段的右下方的小三角，弹出这个字段的所有元素，可以把你不需要的元素进行隐藏。,例如您只对,2005,年的数据感兴趣，则点击数据透视表中年份的一栏，将其他年份去掉，则表中仅剩下,2005,年的数据。,38,数据透视图,除制作数据透视表外，我们还可以制作数据透视图,.,数据透视图兼有数据透视表和图表的功能。既可以随意选择数据组合，又可以随意改变图表类型。,39,应注意的方面,仅当活动单元格处在数据透视表中的位置时，才能激活数据透视表。,数据透视表有一个小缺点，当用户更改数据源时，他不能自动更新。但只需单击工具栏中“刷新”按钮或右键的“更新数据”，强制数据透视表使用最新的数据。当然你也可以在表格选项中选择每隔固定时间刷新数据。,若在操作中将“字段列表”关掉，有两种方法可以再显示：,点右键选择“数据透视表向导”,在“数据透视表工作栏”种选择“显示字段列表”,40,Excel,图表,技巧,高级图表应用,41,Excel,图表,技巧,-,高级图表应用双轴曲线,42,Excel,图表,技巧,-,高级图表应用,-,断层图,43,Excel,图表,技巧,-,高级图表应用,-,双色柱形图,44,Excel,图表,技巧,-,高级图表应用,-,瀑布图,45,Excel,图表,技巧,-,高级图表应用,-,绘图区分割,46,Excel,图表,技巧,-,高级图表应用,-,添加水平线,47,Excel,图表,技巧,-,高级图表应用,-,标注最大小值,48,Excel,图表,技巧,-,高级图表应用,-,涨跌图,49,Excel,图表,技巧,-,高级图表应用,-,步进图,50,Excel,图表,技巧,-,高级图表应用,-,甘特图,51,Excel,图表,技巧,-,高级图表应用雷达图,52,Excel,图表,技巧,-,高级图表应用金字塔,53,Excel,图表,技巧,-,高级图表应用量本利,54,Excel,图表,技巧,-,高级图表应用云梯图,55,Excel,图表,技巧,-,高级图表应用复合饼图,56,Excel,图表,技巧,-,高级图表应用双层饼图,57,Excel,图表,技巧,-,高级图表应用盈亏饼图,58,