您好,欢迎访问三七文档
当前位置:首页 > 商业/管理/HR > 咨询培训 > 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)))公式IFIf是逻辑函数,执行真假值判断,指定要执行的逻辑检测,根据逻辑测试的真假值返回不同的结果。语法IF(logical_test,value_if_true,value_if_false)函数IF可以嵌套七层如果要计算单元格区域中某个文本串或数字出现的次数,则可使用COUNTIF工作表函数。910IF(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
本文标题:Excel常用技能炫酷培训
链接地址:https://www.777doc.com/doc-958037 .html