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

#N/A ——当在函数或公式中引用了无法使用的数值 内部函数或自定义工作表函数中缺少一个或多个参数。 使用的自定义工作表函数不存在。 VLOOKUP（ ） 函 数 中 的 查 找 值 lookup_value、 FALSE/TRUE参数指定了不正确的值域。 #NUM！——数字类型不正确 在需要数字参数的函数中使用了不能接受的参数。 由公式产生的数字太大或太小：在 -10307和10307之间 ######！——输入到单元格中的数值太长，在单元格中显 示不下；单元格公式所产生的结果太长，单元格容纳不下； 日期和时间产生了负值时将产生。
9.把多个表格汇总求和成一个汇总表
假设汇总的工作表叫Sheet1，而存放数据的工作表叫 Sheet2, Sheet3, ..., Sheet27，你可以设置公式，把各工作表 A1的数值加起来:=SUM(Sheet2:Sheet27!A1) 要注意的是， Sheet1不能在Sheet2和Sheet27中间，Excel不会管工作表的 名字，只会把Sheet2和Sheet27中间的＊所有＊工作表中相 关的储存格（哪怕中间有一个工作表叫Sheet99）加起来。 操作步骤： 1、在汇总表单元格内选择‚Σ” 2、选择需要叠加的第一个工作表 3、按住shift同时用鼠标选择需要叠加的最后一个工作表 4、选择需要叠加的单元格 5、Enter 插入例表
2、 RANK函数的应用
RANK含义：返回一个数字在数字列表中的排位。数 字的排位是其大小与列表中其他值的比值（如果列表已 排过序，则数字的排位就是它当前的位置）。将影响后 续数值的排位。例如，在一列按升序排列的整数中，如 果整数 10 出现两次，其排位为 5，则 11 的排位为 7（没 有排位为 6 的数值）。
10.身份证号码输入的几种解决方法
在EXCEL中，输入超过11位数字时会自动转为科学计数 的方式，比如身份证号是：123456789012345，输入后就变 成了：1.23457E+14,现在为大家说几种比较简单快速的方法： 方法1：在A1单元输入号码时，在前面先输入’号， 是：’123456789012345,这样单元格会默认为该单元为文 本方式，会完整显示出15个号码来，而不会显示那令人讨厌 的科学计数方式来了。
示例
4、函数公式出错信息 （7个） #DIV/0——零作除数 #NAME?——在公式中使用了不能识别的名称 删除了公式中使用的名称，或者使用了不存在的名称。 函数名的拼写错误 #VALUE！——使用了不正确的参数或运算符 在需要数字或逻辑值时输入了文本 #REF！——引用了无效的单元格地址 删除了由其它公式引用的单元格 将移动单元格粘贴到由其它公式引用的单元格中。 #NULL！——指定了两个并不相交的区域，故无效 使用了不正确的区域运算符或不正确的单元格引用。
将“1968年6月12日”转换为“1968-6-12”格式 方法1：=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) 显示：1968-6-12 方法2：=TEXT(A1,"yyyy-mm-dd") 显示：1968-06-12
12.去空格函数
如何删去单元格中的空格，如姓名前，中，后的空格，即 单元格中是两个字的人名中间有一个空格，想删去有何方法。 如：中 国，改为：中国。 1、用公式：=SUBSTITUTE(A2," ","") 注：第一对双引号中 有一空格。而第二个“”中是无空格的。 2、利用查找－替换，一次性全部解决。 “编辑”－“替换”（或Ctrl+H），在“查找”栏内输入一空格，“替 换”什么也不输入（空白）。然后“全部替换”即可。
4、在工作表中隐藏所有零值 在Excel默认情况下，零值将显示为0，这个值是一个比 较特殊的数值。如果工作表中包含了大量的零值，会使整 个工作表显得十分凌乱。如果要隐藏工作表中所有的零值， 可以这样操作：选择“工具”→“选项”，打开“选项”对话框， 单击“视图”标签，在“窗口选项”里把“零值”复选框前面的对 号去掉，单击“确定”按钮。此时，可以看到原来显示有0的 单元格全部变成了空白单元格。
二、常用函数学习与应用
1、假设条件函数IF含义及运用 2、 RANK函数的应用 3、 VLOOKUP的运用 4、函数公式出错信息
一、EXCEL技巧及应用
1、如何在EXCEL中快速定位最后一行数据
如果“定位”就是选中的意思，可按CTRL+END键实现。 CTRL+↓ 双击选取单元格之下框线
2、如何用SUN函数快速求和 操作：将光标移到欲要求和的列或行，直按"Alt+"="，最 后按一下“enter"键就可以。这样我们不用输入参数就可以 快速求和。
保护工作表: 保护工作表中数据不被任意修改
• 锁定+保护工作表：保护所有被锁定的单元格。
• 选择‚审阅‛选项卡的‚更改‛组的‚保护工作表‛ 项，在对话框里输入密码 • 在‚保护工作表‛对话框勾选所需的保护内容 • 保护工作表命令只对本工作表起作用 只允许用户编辑指定单元格区域
• 取消锁定+保护工作表 • 选择指定单元格，取消单元格的保护锁定 • 此时只有可以被编辑，其它区域都被锁住了
3、在Excel中行列快速转换 如果需要要将Excel按行（列）排列的数据，转换为 按列（行）排列，可以通过“选择性粘贴”来实现。 选中 需要转换的数据区域，执行一下“复制”操作；选中保存数 据的第一个单元格，执行“编辑选择性粘贴”命令，打开“ 选择性粘贴”对话框，选中其中的“转置”选项，确定返回 即可。
EXCEL在日常工作中的 应用与技巧
苏春莉 二0一0年九月 上 海
一、EXCEL技巧及应用
1、如何在EXCEL中快速定位最后一行数据 2、如何用SUN函数快速求和 3、在Excel中行列快速转换 4、在工作表中隐藏所有零值 5、数据有效性的应用 6、如何用条件格式找出重复项 7、数据透视表应用 8、保护工作簿和工作表 9、把多个表格汇总求和成一个汇总表 10、身份证号码输入的几种解决方法 11、日期格式的转换 12、去空格函数 13、怎样快速去除表中不同行和列的空格
方法3：还有一种用函数来解决的方法：在A1：A100已 经输入大量的号码时，在B1单元中输入：=trim(' 'a1),注意 两个’之间是空格，这个公式的意思:先在A1单元15位号码 前加个空格符号，然后用trim这个函数把空格符号去掉。输 完后，B1单元也是显示出完整的15位数字了。
11.日期格式的转换
13.怎样快速去除表中不同行和列的空格
编辑-定位-定位条件-空值,可、假设条件函数IF含义及运用 IF函数含义：判断，根据逻辑计算的真假值，返回不同 结果。可以使用函数 IF 对数值和公式进行条件检测。
示例： 统计销售人员月度出库时，希望输入10000元以下时，显 示为“不达标”;输入10000元以上的分数时，显示为“达标”。这 样的效果，不妨称之为“条件显示”，用IF函数可以实现。 假设分数在B3单元格中，要将等级显示在C3单元格中。 那么在C3单元格中输入以下公式实现不同的等级: =if(b3<=10000,“不达标”，“达标”) 分“不达标”和“达标”2个等级 =if(b3<=10000,“不达标”， if(b3<=35000,“达标”，“优秀”)) 注意:符号为半角，IF与括弧之间不能有空格，而且最多 嵌套7层。
方法 2：如果已经输入了大量的号码，全部以科学计数 显示的话，而又懒的按照上面的方法一个一个的重新输入 的话，也有这个好方法哦，比如已在单元A1：A100输入了 号码，按以下步骤做：》选择单元A1：A100》单击鼠标右 键，设置单元格式》选择自定义，在‘类型’中输入‘0’ 即可，轻松搞定！
10.身份证号码输入的几种解决方法
8. 保护工作簿和工作表
保护工作簿 • 选定整个工作簿，右击选择‚单元格设置‛中‚保 护‛，清空‚锁定‛前的‚√‛。 • 选定要保护的单元格列，进行锁定。 •选择‚审阅‛选项卡‚更改‛组的‚保护工作簿‛项 •选择‚保护结构和窗口‛。 •勾选‚结构‛或‚窗口‛复选框 • 保护结构: 不能插入、删除工作表、更改工作表名称 等 • 保护窗口: 保留窗口的大小及位置等
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。
小提示：若要在单元格里重新显示0，用上述方法把“零值”复选框前面的打上对号即可。
5、数据有效性功能（限制录入数据范围、重复值、创建下 拉列表、快捷输入数据等）
6、条件格式功能（生日提醒、标记重复值、填充不同色等）
7、数据透视表应用 数据透视表是一种交互式的表，可以进行某些计算，如 求和与计数等。所进行的计算与数据在数据透视表中的排列 有关。 操作步骤
排名公式：=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 的第一列中的数值可以为文本、数字或逻辑值。 若为文本时，不区分文本的大小写。