excel在日常工作中的应用与技巧.ppt

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+ ↓ 双击选取单元格之下框线 操作：将光标移到欲要求和的列或行，直按 ?瑬尫尽 ，最 后按一下 甥?挱湥整屲 键就可以。这样我们不用输入参数就可以 快速求和。 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 个号码来，而不会显示那令人讨厌 的科学计数方式来了。 在 E