EXCEL在日常工作中的应用与技巧课件.ppt

1、EXCEL在日常工作中的在日常工作中的应用与技巧应用与技巧 苏春莉 二0一0年九月 上 海一、一、EXCELEXCEL技巧及应用技巧及应用1、如何在EXCEL中快速定位最后一行数据2、如何用SUN函数快速求和3、在Excel中行列快速转换4、在工作表中隐藏所有零值5、数据有效性的应用6、如何用条件格式找出重复项7、数据透视表应用8、保护工作簿和工作表9、把多个表格汇总求和成一个汇总表10、身份证号码输入的几种解决方法11、日期格式的转换12、去空格函数13、怎样快速去除表中不同行和列的空格二、常用函数学习与应用二、常用函数学习与应用1、假设条件函数IF含义及运用2、RANK函数的应用3、VLO

2、OKUP的运用4、函数公式出错信息1 1、如何在、如何在EXCEL中快速定位最后一行数据中快速定位最后一行数据如果“定位”就是选中的意思，可按CTRL+END键实现。CTRL+双击选取单元格之下框线操作：将光标移到欲要求和的列或行，直按Alt+=，最后按一下“enter键就可以。这样我们不用输入参数就可以快速求和。2 2、如何用、如何用SUN函数快速求和函数快速求和一、一、EXCELEXCEL技巧及应用技巧及应用 如果需要要将Excel按行（列）排列的数据，转换为按列（行）排列，可以通过“选择性粘贴”来实现。选中需要转换的数据区域，执行一下“复制”操作；选中保存数据的第一个单元格，执行“编辑选

3、择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“转置”选项，确定返回即可。3 3、在、在Excel中行列快速转换中行列快速转换 在Excel默认情况下，零值将显示为0，这个值是一个比较特殊的数值。如果工作表中包含了大量的零值，会使整个工作表显得十分凌乱。如果要隐藏工作表中所有的零值，可以这样操作：选择“工具”“选项”，打开“选项”对话框，单击“视图”标签，在“窗口选项”里把“零值”复选框前面的对号去掉，单击“确定”按钮。此时，可以看到原来显示有0的单元格全部变成了空白单元格。4 4、在工作表中隐藏所有零值、在工作表中隐藏所有零值小提示：若要在单元格里重新显示小提示：若要在单元格里重新显示

4、0，用上述方法把，用上述方法把“零值零值”复选框前面的打上对号即可。复选框前面的打上对号即可。5 5、数据有效性功能、数据有效性功能（限制录入数据范围、重复值、创建下（限制录入数据范围、重复值、创建下拉列表、快捷输入数据等）拉列表、快捷输入数据等）6 6、条件格式功能、条件格式功能（生日提醒、标记重复值、填充不同色等）（生日提醒、标记重复值、填充不同色等）7 7、数据透视表应用、数据透视表应用 数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据在数据透视表中的排列有关。操作步骤 8.8.保护工作簿和工作表保护工作簿和工作表保护工作簿 选定整个工作簿，右击选择“单元

5、格设置”中“保护”，清空“锁定”前的“”。选定要保护的单元格列，进行锁定。选择“审阅”选项卡“更改”组的“保护工作簿”项选择“保护结构和窗口”。勾选“结构”或“窗口”复选框 保护结构:不能插入、删除工作表、更改工作表名称等 保护窗口:保留窗口的大小及位置等保护工作表:保护工作表中数据不被任意修改 锁定锁定+保护工作表保护工作表：保护所有被锁定的单元格。选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框里输入密码 在“保护工作表”对话框勾选所需的保护内容 保护工作表命令只对本工作表起作用只允许用户编辑指定单元格区域 取消锁定取消锁定+保护工作表保护工作表 选择指定单元格，取消单元格的保护

6、锁定 此时只有可以被编辑，其它区域都被锁住了9.把多个表格汇总求和成一个汇总表把多个表格汇总求和成一个汇总表 假设汇总的工作表叫Sheet1，而存放数据的工作表叫Sheet2,Sheet3,.,Sheet27，你可以设置公式，把各工作表A1的数值加起来:=SUM(Sheet2:Sheet27!A1)要注意的是，Sheet1不能在Sheet2和Sheet27中间，Excel不会管工作表的名字，只会把Sheet2和Sheet27中间的所有工作表中相关的储存格（哪怕中间有一个工作表叫Sheet99）加起来。操作步骤：操作步骤：1、在汇总表单元格内选择“”2、选择需要叠加的第一个工作表 3、按住shi

7、ft同时用鼠标选择需要叠加的最后一个工作表 4、选择需要叠加的单元格 5、Enter插入例表 10.身份证号码输入的几种解决方法身份证号码输入的几种解决方法 方法1：在A1单元输入号码时，在前面先输入号，是：123456789012345,这样单元格会默认为该单元为文本方式，会完整显示出15个号码来，而不会显示那令人讨厌的科学计数方式来了。在EXCEL中，输入超过11位数字时会自动转为科学计数的方式，比如身份证号是：123456789012345，输入后就变成了：1.23457E+14,现在为大家说几种比较简单快速的方法：方法方法 2：如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的

8、按照上面的方法一个一个的重新输入的话，也有这个好方法哦，比如已在单元A1：A100输入了号码，按以下步骤做：选择单元A1：A100单击鼠标右键，设置单元格式选择自定义，在类型中输入0即可，轻松搞定！方法方法3：还有一种用函数来解决的方法：在A1：A100已经输入大量的号码时，在B1单元中输入：=trim(a1),注意两个之间是空格，这个公式的意思:先在A1单元15位号码前加个空格符号，然后用trim这个函数把空格符号去掉。输完后，B1单元也是显示出完整的15位数字了。10.身份证号码输入的几种解决方法身份证号码输入的几种解决方法将“1968年6月12日”转换为“1968-6-12”格式方法1：

9、=YEAR(A1)&-&MONTH(A1)&-&DAY(A1)显示：1968-6-12方法2：=TEXT(A1,yyyy-mm-dd)显示：1968-06-1211.11.日期格式的转换日期格式的转换12.12.去空格函数去空格函数 如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法。如：中国，改为：中国。1、用公式：=SUBSTITUTE(A2,)注：第一对双引号中有一空格。而第二个“”中是无空格的。2、利用查找替换，一次性全部解决。“编辑”“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替换”什么也不输入（空白）。然后“全部替换”

10、即可。编辑-定位-定位条件-空值,可选中所有空单元格,再删除。13.13.怎样快速去除表中不同行和列的空格怎样快速去除表中不同行和列的空格 示例：示例：统计销售人员月度出库时，希望输入10000元以下时，显示为“不达标”;输入10000元以上的分数时，显示为“达标”。这样的效果，不妨称之为“条件显示”，用IF函数可以实现。假设分数在B3单元格中，要将等级显示在C3单元格中。那么在C3单元格中输入以下公式实现不同的等级:=if(b3=10000,“不达标”，“达标”)分“不达标”和“达标”2个等级=if(b3=10000,“不达标”，if(b3=35000,“达标”，“优秀”)注意:符号为半角，

11、IF与括弧之间不能有空格，而且最多嵌套7层。1 1、假设条件函数、假设条件函数IFIF含义及运用含义及运用二、常用函数学习与应用二、常用函数学习与应用 IF IF函数含义：函数含义：判断，根据逻辑计算的真假值，返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。RANK含义：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没有排位为 6 的数值）。2 2、RANKRANK函数的应用函数的应用排名公

12、式：=RANK(B2,$B$2:$B$21)；示例：3、VLOOKUP的运用的运用VLOOKUP(需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False)1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。2.Table_array为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。如果 range_lookup为 TRUE或省略，则 table_array的第一列中的数值必须按升序排列，否则，函数 VLOOKUP不能返回正确的数值。如果range_lookup为 FALSE，table_arr

13、ay不必进行排序。Table_array的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。3 3.Col_index_num为table_array中待返回的匹配值的列序号。Col_index_num为 1 时，返回 table_array第一列中的数值；Col_index_num为 2 时，返回 table_array第二列中的数值，以此类推。如果Col_index_num小于 1，函数 VLOOKUP返回错误值#VALUE!；如果Col_index_num大于 table_array的列数，函数 VLOOKUP返回错误值#REF!。4.Range_lookup为一逻

14、辑值，指明函数 VLOOKUP返回时是精确匹配还是近似匹配。如果为 TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果 range_value为 FALSE，函数 VLOOKUP将返回精确匹配值。如果找不到，则返回错误值#N/A。示例示例4 4、函数公式出错信息、函数公式出错信息 （7 7个）个）#DIV/0零作除数#NAME?在公式中使用了不能识别的名称删除了公式中使用的名称，或者使用了不存在的名称。函数名的拼写错误#VALUE！使用了不正确的参数或运算符在需要数字或逻辑值时输入了文本#REF！引用了无效的单元格地址删除了由其

15、它公式引用的单元格将移动单元格粘贴到由其它公式引用的单元格中。#NULL！指定了两个并不相交的区域，故无效使用了不正确的区域运算符或不正确的单元格引用。#N/A 当在函数或公式中引用了无法使用的数值内部函数或自定义工作表函数中缺少一个或多个参数。使用的自定义工作表函数不存在。V LO O KU P（）函数中的查找值 l o o ku p _ v a l u e、FALSE/TRUE参数指定了不正确的值域。#NUM！数字类型不正确在需要数字参数的函数中使用了不能接受的参数。由公式产生的数字太大或太小：在 -10307和10307之间#！输入到单元格中的数值太长，在单元格中显示不下；单元格公式所产生的结果太长，单元格容纳不下；日期和时间产生了负值时将产生。