Excel常用技能炫酷培训

ExcelArt图表改变工作生活陕西黄马甲物流配送股份有限公司经营中心：杨莉2011-11.-XX1目录EXCEL基本功能演示EXCEL图形图标演示公式的应用键盘的使用技巧表间的链接应用数据透视表/图的制作2EXCEL基本功能演示图形图标演示3EXCEL公式的应用常用公式演示4COUNT统计函数，可以计算数组或单元格区域中数字项的个数。语法COUNT(value1,value2,...)56函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数计算进去；但是错误值或其他无法转化成数字的文字则被忽略。所以，COUNT的单纯应用很少，一般都会选择COUNTIF公式，并和IF套用。COUNT例子JanFeb525718355291125098TRUEFALSE#DIV/0!Total#NAME?JanFeb=Count(data)COUNTIF数字和三角函数，计算给定区域内满足特定条件的单元格的数目。语法COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本78上述公式，我们可以看出，COUNTIF是作为IF语句的判断条件出现的。它表示计算C2到F2区域内值为“1”的单元格个数。COUNTIF例子日期总费用王一刘二张三李四日费用2004-8-2936111122004-8-301211142004-8-314511115本周消费小计27161931=IF(COUNTIF(C2:F2,1)=0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)))公式IFIf是逻辑函数，执行真假值判断，指定要执行的逻辑检测，根据逻辑测试的真假值返回不同的结果。语法IF(logical_test,value_if_true,value_if_false)函数IF可以嵌套七层如果要计算单元格区域中某个文本串或数字出现的次数，则可使用COUNTIF工作表函数。910IF(B2C2,超过预算,OK)判断当B2单元格的值大于C2单元格时，提示超出预算。IF(B260,“及格”,IF(70B280,“中”,IF(80B290,“良”,IF(B2=90,“优”,“不及格))))三层判断，第二个IF语句是第一个IF语句的“假”值。实例如下：IF例子：一般及嵌套的应用姓名分数成绩李海60及格苏杨50不及格贺华80良张青75中王芳98优11如上两公式：公式一选择了嵌套SUM公式；公式二选择了嵌套COUNTIF及ROUND两个公式。公式一是通过判断王一在3天里每天是否有消费，消费的总额是多少；而公式二是通过判断在某一天是否有人消费，如果有，算出人均消费额度，并取小数点后两位保留。其中，值“1”表示该人是否消费，表格从A1单元格开始排列。IF例子：以其他公式为参数日期总费用王一刘二张三李四日费用2004-8-2936111122004-8-301211142004-8-314511115本周消费小计27161931=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)=0,0,(ROUND(B2/COUNTIF(C2:F2,1),2)))公式一公式二HLOOKUP在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。语法结构：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)lookup_value：需要在数据表第一行中进行查找的数值。table_array：为需要在其中查找数据的数据表。row_index_num：为table_array中待返回的匹配值的行序号。range_lookup：为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。1213上述公式是指针对B2单元格的数值$21566,在E1：J3区域范围内搜索出近似的值（因为range_lookup参数被忽略），找到后显示E1：J3区域表中第三行的对应值。HLOOKUP例子=HLOOKUP（B2，E1：J3，3）IncomeisGreaterThanorEqualTo…$0$2,651$27,301$58,501$131,801$284,701EnterIncome:$21,566ButLessThan…$2,650$27,300$58,500$131,800$284,700TheTaxRateis:28.00%TaxRate15.00%28.00%31.00%36.00%39.60%45.25%VLOOKUP在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。语法结构：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value：需要在数据表第一列中进行查找的数值。table_array：为需要在其中查找数据的数据表。col_index_num：为table_array中待返回的匹配值的列序号。range_lookup：为一逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为TRUE或省略，则返回近似匹配值。1415上述例子是一个收入税率表。可以看到不同的收入等级的收入税率。公式是指针对B2单元格的数值$21566,在D2：F7区域范围内搜索出近似的值（因为range_lookup参数被忽略），找到后显示D2：F7区域表中第三列的对应值VLOOKUP例子IncomeisGreaterThanorEqualTo…ButLessThanorEqualTo…TaxRateEnterIncome:$21,566$0$2,65015.00%TheTaxRateis:28.00%$2,651$27,30028.00%$27,301$58,50031.00%$58,501$131,80036.00%$131,801$284,70039.60%$284,70145.25%=VLOOKUP（B2，D2：F7，3）HLOOKUP及VLOOKUP当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP。当比较值位于要进行数据查找的左边一列时，请使用函数VLOOKUP。如果(row)col_index_num小于1，函数(H)VLOOKUP返回错误值#VALUE!；如果(row)col_index_num大于table_array的列数，函数(H)VLOOKUP返回错误值#REF!。16注意事项及文件间的操作以下案例均直接由EXCEL中复制粘贴，可在PPT中双击调用EXCEL格式编辑，相关公式可由编辑栏直接查看。由于WORD、EXCEL及PPT是OFFICE组件，它们之间的调用很方便，两种方式：同上所说，直接复制粘贴就可以了，还能保持用原格式再次嵌套编辑；选择“插入”-〉“对象”，在对话框中选择“由文件创建”，不选中“显示为图标”选项即可。方法可适用于上述工具任一种。17公式使用小技巧当同一列（行）所有单元格使用的公式相同，可以使用以下两种快捷方式：使用拖动柄，在完成首行（列）单元格公式后选中右下角的拖动柄，填充所要应用公式的所有单元格。使用EXCEL自带的记忆功能。在第一个单元格完成公式后，填充第二个单元格，再以第二个单元格填充第三个，以第三个填充第四个，到第五个开始就无须做任何操作，公式对应的值一出现，第五个单元格自动填充。这种方式适用于复杂公式及大数据量。以免上种方式由于操作失误造成漏项。18EXCEL键盘使用技巧快捷键19快捷键箭头键+CTRL-“选择框移动到当前数据区域的边缘”F11或ALT+F1-“使用当前区域的数据创建图表”CTRL+END-“移动到工作表的最后一个单元格，这个单元格位于数据区的最右列和最底行的交叉处（右下角）；或是与首单元格（一般为A1）相对的单元格。”ALT+ENTER-“在单元格中折行”CTRL+ENTER-“用当前输入项填充选定的单元格区域,适用于在选定区域或单元格内需要输入同一值时使用。”SHIFT+ENTER-“完成单元格输入并在选定区域中上移”SHIFT+TAB-“完成单元格输入并在选定区域中左移”CTRL+SHIFT+A-“在公式中键入函数名之后，无须打开函数面板，自动为该函数插入变量名和括号”F4-“在编写公式中使用F4可以为所选单元格添加固定符号$点击单元格右下角的十字可以将当前单元格的内容延续下去20EXCEL表间的链接应用数据源链接等21方法要使多张表同时关联，首先要确定源数据的位置；源数据位置一定，在其他任何需要和该数据值相关的位置上全部使用调用公式。即用“=”来直接调用该源数据或加上函数及参数，使该位置的数据可以针对源数据进行相关处理。2223费用总表中“本周已消费”列的值是从费用明细表中“本周消费小计”行中读取的。例如：王一的“本周已消费”公式为：=[费用明细表.xls]费用明细!$C$5可以把上两表拷贝到不同EXCEL表中，注意都从A1单元格开始。当打开相关联的任何一张表时（可以同时关联很多张表），会自动弹出下列对话框。选择是，表间的链接就建立了。例子日期总费用王一刘二张三李四日费用2004-8-2936111122004-8-301211142004-8-314511115本周消费小计27161931姓名上周余额本周缴费本周已消费目前余额王一231002796刘二34501668张三4501926李四56103135费用明细表费用总表A1EXCEL数据透视表的制作透视24透视表的特点一个数据透视表实际上是一个产生于数据库的动态总结报告。数据透视表的最新概念是它的交互特性。最大的缺点是在修改原数据时，数据透视表不会自动更新。但可以单击数据透视表工具栏上的“刷新”按钮迫使透视表使用最新数据。25注意事项1.不是所有的数据都可以创建数据透视表，所要总结的数据必须以数据库的形式存在。当然，我们可以把数据库存储在工作表中，即常说的列表。2.一个单独的数据库表格一般应包含数据字段和类别字段。数据字段用于总结，类别字段的数值可以作为行、列或页出现在数据透视表上。3.数据透视表的特性对不包含实际值字段的数据库也有效，可通过方法而不是求和来总结数据透视表上的信息。26制作步骤第一步：定义数据位置第二步：定义数据第三步：实现数据透视表使用对话框规划数据透视表使用透视表字段列表工具栏规划数据透视表完成数据透视表的定义2728用于创建透视表的数据列表DateAmountAcctTypeOpenedByBranchCustomer09-01-03340CheckingNewAcctsCentralExisting09-01-0315,759CDTellerWestsideExisting09-01-0315,276CDNewAcctsNorthCountyExisting09-01-0312,000CDNewAcctsWestsideExisting09-01-035,000CDNewAcctsNorthCountyExisting09-01-037,000SavingsNewAcctsNorthCountyNew09-01-035,000SavingsNewAcctsWestsideExisting09-01-034,623SavingsNewAcctsNorthCountyExisting09-01-035,879CheckingNewAcctsCentralExisting09-01-033,171CheckingN