《Excel常用技能炫酷培训.ppt》由会员分享，可在线阅读，更多相关《Excel常用技能炫酷培训.ppt（46页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Excel Art图表改变工作生活陕西黄马甲物流配送股份有限公司经营中心：杨莉2011-11.-XX2021/9/261目录EXCEL基本功能演示EXCEL图形图标演示公式的应用键盘的使用技巧表间的链接应用数据透视表/图的制作2021/9/262EXCEL 基本功能演示图形图标演示2021/9/263EXCEL公式的应用常用公式演示2021/9/264COUNT统计函数，可以计算数组或单元格区域中数字项的个数。语法COUNT(value1,value2,.)2021/9/265函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被
2、忽略。所以，COUNT的单纯应用很少，一般都会选择COUNTIF公式，并和IF套用。COUNT例子=Count(data)2021/9/266COUNTIF数字和三角函数，计算给定区域内满足特定条件的单元格的数目。语法COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本2021/9/267上述公式，我们可以看出，COUNTIF是作为IF语句的判断条件出现的。它表示计算C2到F2区域内值为“1”的单元格个数。COUNTIF例子=IF(COUNTIF(C2:F2,1
3、)=0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)公式2021/9/268IFIf是逻辑函数，执行真假值判断，指定要执行的逻辑检测，根据逻辑测试的真假值返回不同的结果。语法IF(logical_test,value_if_true,value_if_false)函数IF可以嵌套七层如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。2021/9/269IF(B2C2,超过预算,OK)判断当B2单元格的值大于C2单元格时，提示超出预算。IF(B260,“及格”,IF(70B280,“中”,IF(80B2=90,“优”,“不及格)三层判断，第二个
4、IF语句是第一个IF语句的“假”值。实例如下：IF例子：一般及嵌套的应用2021/9/2610如上两公式：公式一选择了嵌套SUM公式；公式二选择了嵌套COUNTIF及ROUND两个公式。公式一是通过判断王一在3天里每天是否有消费，消费的总额是多少；而公式二是通过判断在某一天是否有人消费，如果有，算出人均消费额度，并取小数点后两位保留。其中，值“1”表示该人是否消费，表格从A1单元格开始排列。IF例子：以其他公式为参数=IF(C2,B2/SUM(C2:F2),0)+IF(C3,B3/SUM(C3:F3),0)+IF(C4,B4/SUM(C4:F4),0)=IF(COUNTIF(C2:F2,1)=
5、0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)公式一公式二2021/9/2611HLOOKUP在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。语法结构：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)lookup_value：需要在数据表第一行中进行查找的数值。table_array：为需要在其中查找数据的数据表。row_index_num：为table_array中待返回的匹配值的行序号。range_lookup：为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还
6、是近似匹配。如果为TRUE或省略，则返回近似匹配值。2021/9/2612上述公式是指针对B2单元格的数值$21566,在E1：J3区域范围内搜索出近似的值（因为range_lookup 参数被忽略），找到后显示E1：J3区域表中第三行的对应值。HLOOKUP例子=HLOOKUP（B2，E1：J3，3）2021/9/2613VLOOKUP在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。语法结构：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value：需要在数据表第一列中进
7、行查找的数值。table_array：为需要在其中查找数据的数据表。col_index_num：为table_array中待返回的匹配值的列序号。range_lookup：为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。2021/9/2614上述例子是一个收入税率表。可以看到不同的收入等级的收入税率。公式是指针对B2单元格的数值$21566,在D2：F7区域范围内搜索出近似的值（因为range_lookup 参数被忽略），找到后显示D2：F7区域表中第三列的对应值VLOOKUP例子=VLOOKUP（B2，D2：F7，3）2021/9/2
8、615HLOOKUP及VLOOKUP当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数VLOOKUP。如果(row)col_index_num小于1，函数(H)VLOOKUP返回错误值#VALUE!；如果(row)col_index_num大于table_array的列数，函数(H)VLOOKUP返回错误值#REF!。2021/9/2616注意事项及文件间的操作以下案例均直接由EXCEL中复制粘贴，可在PPT中双击调用EXCEL格式编辑，相关公式可由编辑栏直接查看。由于WORD、EXCEL及PPT是OFFICE
9、组件，它们之间的调用很方便，两种方式：同上所说，直接复制粘贴就可以了，还能保持用原格式再次嵌套编辑；选择“插入”-“对象”，在对话框中选择“由文件创建”，不选中“显示为图标”选项即可。方法可适用于上述工具任一种。2021/9/2617公式使用小技巧当同一列（行）所有单元格使用的公式相同，可以使用以下两种快捷方式：使用拖动柄，在完成首行（列）单元格公式后选中右下角的拖动柄，填充所要应用公式的所有单元格。使用EXCEL自带的记忆功能。在第一个单元格完成公式后，填充第二个单元格，再以第二个单元格填充第三个，以第三个填充第四个，到第五个开始就无须做任何操作，公式对应的值一出现，第五个单元格自动填充。这
10、种方式适用于复杂公式及大数据量。以免上种方式由于操作失误造成漏项。2021/9/2618 EXCEL键盘使用技巧快捷键2021/9/2619快捷键箭头键+CTRL-“选择框移动到当前数据区域的边缘”F11或ALT+F1-“使用当前区域的数据创建图表”CTRL+END-“移动到工作表的最后一个单元格，这个单元格位于数据区的最右列和最底行的交叉处（右下角）；或是与首单元格（一般为A1）相对的单元格。”ALT+ENTER-“在单元格中折行”CTRL+ENTER-“用当前输入项填充选定的单元格区域,适用于在选定区域或单元格内需要输入同一值时使用。”SHIFT+ENTER-“完成单元格输入并在选定区域中
11、上移”SHIFT+TAB-“完成单元格输入并在选定区域中左移”CTRL+SHIFT+A-“在公式中键入函数名之后，无须打开函数面板，自动为该函数插入变量名和括号”F4-“在编写公式中使用F4可以为所选单元格添加固定符号$点击单元格右下角的十字可以将当前单元格的内容延续下去2021/9/2620EXCEL表间的链接应用数据源链接等2021/9/2621方法要使多张表同时关联，首先要确定源数据的位置；源数据位置一定，在其他任何需要和该数据值相关的位置上全部使用调用公式。即用“=”来直接调用该源数据或加上函数及参数，使该位置的数据可以针对源数据进行相关处理。2021/9/2622费用总表中“本周已消
12、费”列的值是从费用明细表中“本周消费小计”行中读取的。例如：王一的“本周已消费”公式为：=费用明细表.xls费用明细!$C$5可以把上两表拷贝到不同EXCEL表中，注意都从A1单元格开始。当打开相关联的任何一张表时（可以同时关联很多张表），会自动弹出下列对话框。选择是，表间的链接就建立了。例子费用明细表费用总表A12021/9/2623EXCEL 数据透视表的制作透视2021/9/2624透视表的特点一个数据透视表实际上是一个产生于数据库的动态总结报告。数据透视表的最新概念是它的交互特性。最大的缺点是在修改原数据时，数据透视表不会自动更新。但可以单击数据透视表工具栏上的“刷新”按钮迫使透视表使
13、用最新数据。2021/9/2625注意事项1.不是所有的数据都可以创建数据透视表，所要总结的数据必须以数据库的形式存在。当然，我们可以把数据库存储在工作表中，即常说的列表。2.一个单独的数据库表格一般应包含数据字段和类别字段。数据字段用于总结，类别字段的数值可以作为行、列或页出现在数据透视表上。3.数据透视表的特性对不包含实际值字段的数据库也有效，可通过方法而不是求和来总结数据透视表上的信息。2021/9/2626制作步骤第一步：定义数据位置第二步：定义数据第三步：实现数据透视表使用对话框规划数据透视表使用透视表字段列表工具栏规划数据透视表完成数据透视表的定义2021/9/2627用于创建透视
14、表的数据列表2021/9/2628选择“数据”-“数据透视表和数据透视图”第一步：定义数据位置2021/9/26291.Excel列表或数据库：存储在工作表中的数据库受不超过65535个记录和256个字段的限制，第一行必包含字段名称，数据可以是数值、文本或公式。2.外部数据源：使用Query获得数据。可以使用dBASE文件、SQLServer数据或其他系统能够建立访问的数据。3.多重合并计算数据区域：可以从多表格创建数据透视表，等价于合并表格中的信息。4.另一数据透视表或图：从已经存在的透视表使用过的数据创建新透视表。注：从内存使用方面考虑，如希望从同一组数据中创建多个透视表，最好先建立第一个
15、透视表，然后把该透视表当作后续透视表的数据源。向导之步骤一2021/9/2630第二步：定义数据要转移到向导下一步，单击“下一步”按钮。向导二提示我们需要数据。但注意，对话框依赖于你在向导一上的选择有所变化。从现在开始，我们分别通过三个完整的创建案例来具体了解选择不同类型的效果及特点。2021/9/2631在步骤一选择该类型，步骤二为下面的对话框。注：上图中选定区域已存在。原因是在选择创建数据透视表时，单元格指针指在了数据列表内的任一位置，Excel自动识别了原数据范围。也可以使用“浏览”按钮打开不同工作表并且选择范围。例1：使用Excel列表或数据库2021/9/2632第三步：实现数据透视
16、表选择“现有工作表”选项，可以在下面的选择范围内定义起始单元格位置；选择“新建工作表”选项，自动插入一个新的工作表。注：从步骤2到步骤3时，如果该工作薄已存在数据透视表，会自动弹出下面的提示框。选择“是”，自动弹出透视表列表，选择后确定才会出现步骤3的对话框。2021/9/2633规划数据透视表选择“版式”，出现版式对话框。2021/9/2634关于规划“版式”对话框中右下角的选项是表单中的标题栏名称，可以用鼠标点击选中拖动到左边的图示相应位置，表单中各标题下的数据就会自动统计到该图示对应的单元格中。如果想改动选项位置，用鼠标在图示中选中拖到图示框外即可取消该项设置，然后把需要的选项再次拖入就
17、可以了。如果不想在该处调整版式，可以在确定数据透视表建立位置后直接点“完成”，出现下页中的工具栏。2021/9/2635调整数据透视表设置同版式里方法一样，用鼠标选中工具栏上的各名称，拖动到相应位置就可以了。如果生成表后还想调整，屏幕上又没有显示“数据透视表”的工具栏，可以单击“视图”-“工具栏”-“数据透视表”，即可。2021/9/2636根据需要确定选项后，点击完成，就可以生成下面的数据透视表了。例：使用上页信息的透视表简单数据透视表2021/9/2637在步骤一选择该类型，步骤二为下面的对话框。例2：使用另一数据透视表注：上图对话框中出现了两个数据表项是EXCEL自动记录了当前工作薄中已
18、经存在的所有数据透视表名称，并提供选择。2021/9/2638第三步：实现数据透视表这里我们选择数据透视表1做为参照对象，选择“下一步”。选择“现有工作表”选项，可以在下面的选择范围内定义起始单元格位置；选择“新建工作表”选项，自动插入一个新的工作表。2021/9/2639根据需要确定选项后，点击完成，就可以生成下面的数据透视表了。例：使用上页信息的透视表使用页字段的数据透视表这里我们添加了页字段。使用这种方式对于想对同一表单建立多张数据透视表很有好处，可以节约内存资源，提高数据透视表分析的效率。2021/9/2640q在步骤一选择该类型，步骤二为下面的对话框。例3：多重合并计算数据区域缺省选
19、择，下一步出现右图对话框，每选定一个区域后选择添加，在所以区域里可以看见选择了的区域位置及区域范围。如果选错了，可以选择后删除。2021/9/2641第三步：实现数据透视表这步选择同其他两个例子一样，我们都选择新建工作表，然后完成。这里注意，多重合并最好不要在版式里调整选项，到数据透视表的工具栏中再做调整。2021/9/2642数据透视表工具栏设置2021/9/2643q上图中出现的不再是单表单时的标题栏名称，而是行、列、值、页。行和列指的是两张表单中的所有标题行名称及第一列的所有信息，有重复的内容以一个为代表，不重复显示。q值就是表单中除开标题行及第一列的所有信息；q页很简单，选择下拉菜单会发现出现项一、项二的选项，它们分别表示数据表单一及数据表单二。如果是三张表，就会自动出现项三这个选项。选择其一，你会发现和前面两个例子中用单表作出的数据透视表一样。q需要注意的是，测试中的两张表关联并不大，所以透视表看上去作用不大。在选择“多重合并计算数据区域”这个选项前，要慎重考虑合并的表是否关联，有必要合并吗？说明2021/9/2644结果上表是多重合并后生成的数据透视表，可以在EXCEL中打开。修改设置及选项位置后，可以看看效果。2021/9/2645谢谢参与谢谢参与谢谢参与谢谢参与2021/9/2646