EXCEL在日常工作中的应用与技巧

EXCEL在日常工作中的

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

VLOOKUP的运用4、函数公式出错信息1、如何在EXCEL中快速定位最后一行数据如果“定位”就是选中的意思，可按CTRL+END键实现。CTRL+↓

双击选取单元格之下框线操作：将光标移到欲要求和的列或行，直按"Alt+"="，最后按一下“enter"键就可以。这样我们不用输入参数就可以快速求和。2、如何用SUN函数快速求和一、EXCEL技巧及应用如果需要要将Excel按行（列）排列的数据，转换为按列（行）排列，可以通过“选择性粘贴”来实现。选中需要转换的数据区域，执行一下“复制”操作；选中保存数据的第一个单元格，执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“转置”选项，确定返回即可。

3、在Excel中行列快速转换在Excel默认情况下，零值将显示为0，这个值是一个比较特殊的数值。如果工作表中包含了大量的零值，会使整个工作表显得十分凌乱。如果要隐藏工作表中所有的零值，可以这样操作：选择“工具”→“选项”，打开“选项”对话框，单击“视图”标签，在“窗口选项”里把“零值”复选框前面的对号去掉，单击“确定”按钮。此时，可以看到原来显示有0的单元格全部变成了空白单元格。4、在工作表中隐藏所有零值小提示：若要在单元格里重新显示0，用上述方法把“零值”复选框前面的打上对号即可。5、数据有效性功能（限制录入数据范围、重复值、创建下拉列表、快捷输入数据等）6、条件格式功能（生日提醒、标记重复值、填充不同色等）7、数据透视表应用数据透视表是一种交互式的表，可以进行某些计算，如求和与计数等。所进行的计算与数据在数据透视表中的排列有关。操作步骤

8.保护工作簿和工作表保护工作簿

选定整个工作簿，右击选择“单元格设置”中“保护”，清空“锁定”前的“√”。

选定要保护的单元格列，进行锁定。选择“审阅”选项卡“更改”组的“保护工作簿”项选择“保护结构和窗口”。勾选“结构”或“窗口”复选框保护结构:不能插入、删除工作表、更改工作表名称等保护窗口:保留窗口的大小及位置等保护工作表:

保护工作表中数据不被任意修改锁定+保护工作表：保护所有被锁定的单元格。选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框里输入密码在“保护工作表”对话框勾选所需的保护内容保护工作表命令只对本工作表起作用只允许用户编辑指定单元格区域取消锁定+保护工作表选择指定单元格，取消单元格的保护锁定此时只有可以被编辑，其它区域都被锁住了9.把多个表格汇总求和成一个汇总表假设汇总的工作表叫Sheet1，而存放数据的工作表叫Sheet2,Sheet3,...,Sheet27，你可以设置公式，把各工作表A1的数值加起来:=SUM(Sheet2:Sheet27!A1)要注意的是，Sheet1不能在Sheet2和Sheet27中间，Excel不会管工作表的名字，只会把Sheet2和Sheet27中间的＊所有＊工作表中相关的储存格（哪怕中间有一个工作表叫Sheet99）加起来。操作步骤：1、在汇总表单元格内选择“Σ”2、选择需要叠加的第一个工作表3、按住shift同时用鼠标选择需要叠加的最后一个工作表4、选择需要叠加的单元格5、Enter插入例表10.身份证号码输入的几种解决方法方法1：在A1单元输入号码时，在前面先输入’号，是：’123456789012345,这样单元格会默认为该单元为文本方式，会完整显示出15个号码来，而不会显示那令人讨厌的科学计数方式来了。在EXCEL中，输入超过11位数字时会自动转为科学计数的方式，比如身份证号是：123456789012345，输入后就变成了：1.23457E+14,现在为大家说几种比较简单快速的方法：方法2：如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的按照上面的方法一个一个的重新输入的话，也有这个好方法哦，比如已在单元A1：A100输入了号码，按以下步骤做：》选择单元A1：A100》单击鼠标右键，设置单元格式》选择自定义，在‘类型’中输入‘0’即可，轻松搞定！

方法3：还有一种用函数来解决的方法：在A1：A100已经输入大量的号码时，在B1单元中输入：=trim(''a1),注意两个’之间是空格，这个公式的意思:先在A1单元15位号码前加个空格符号，然后用trim这个函数把空格符号去掉。输完后，B1单元也是显示出完整的15位数字了。10.身份证号码输入的几种解决方法将“1968年6月12日”转换为“1968-6-12”格式方法1：=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)

显示：1968-6-12方法2：=TEXT(A1,"yyyy-mm-dd")

显示：1968-06-1211.日期格式的转换12.去空格函数如何删去单元格中的空格，如姓名前，中，后的空格，即单元格中是两个字的人名中间有一个空格，想删去有何方法。如：中

国，改为：中国。1、用公式：=SUBSTITUTE(A2,"","")

注：第一对双引号中有一空格。而第二个“”中是无空格的。2、利用查找－替换，一次性全部解决。“编辑”－“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替换”什么也不输入（空白）。然后“全部替换”即可。编辑-定位-定位条件-空值,可选中所有空单元格,再删除。13.怎样快速去除表中不同行和列的空格示例：统计销售人员月度出库时，希望输入10000元以下时，显示为“不达标”;输入10000元以上的分数时，显示为“达标”。这样的效果，不妨称之为“条件显示”，用IF函数可以实现。假设分数在B3单元格中，要将等级显示在C3单元格中。那么在C3单元格中输入以下公式实现不同的等级:=if(b3<=10000,“不达标”，“达标”)

分“不达标”和“达标”2个等级

=if(b3<=10000,“不达标”，if(b3<=35000,“达标”，“优秀”))

注意:符号为半角，IF与括弧之间不能有空格，而且最多嵌套7层。1、假设条件函数IF含义及运用二、常用函数学习与应用IF函数含义：判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。RANK含义：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。将影响后续数值的排位。例如，在一列按升序排列的整数中，如果整数10出现两次，其排位为5，则11的排位为7（没有排位为6的数值）。2、RANK函数的应用排名公式：=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_array不必进行排序。⑵Table_array的第一列中的数值可以为文本、数字或逻辑值。若为文本时，不区分文本的大小写。

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