EXCEL实用技巧.ppt

EXCELtrainingmanual PracticalSkillManual EXCEL培训手册 实用技巧篇 教材存放位置 Dmnnt008 H HR Admin Training TrainingMaterials MSOffice 前言 Excel是一个功能非常强大的数据处理系统 不只是一个拿来画个表格的表格绘制工具 如能真正掌握Excel的所有功能 你会发现 很多重复的 烦闷的数据处理过程 会变得非常美妙 Excel主要功能 用数据说话 制作数据分析报表 教材存放位置 Dmnnt008 H HR Admin Training TrainingMaterials MSOffice 课程大纲 工作表数据的录入不同格式数据的录入智能填充编辑工作表条件格式与数据有效性查找与替换筛选排序保护工作表其他技巧 工作表的数据输入 单击目标单元格 使之成为当前单元格 此时名称框中出现当前单元格的地址 输入的字符串将在单元格中左对齐 输入完毕后按光标移动键或Enter键 输入文本 Text 输入数值 Num 输入日期和时间 Date Time 快速输入当前日期与时间 输入的数值将在单元格中右对齐 分数的输入 巧用复制与粘贴 功能 使用智能填充可提高重复数据或按规律递增或递减的内容的录入速度 运用 智能填充 填充相同数据 填充已定义的序列数据 用户自定义序列 Tools Options CustomsLists 工作表的数据输入 功能 条件格式使用我们能够根据单元格的内容有选择地和自动地应用单元格格式 作用 快速辨别错误单元格输入项 快速辨别特殊类型单元格 菜单栏 Format ConditionalFormatting 条件格式ConditionalFormat 编辑工作表 条件格式与数据有效性 条件格式应用 定义条件格式可以使用的格式 字体 边框 背景 阴影等 定义条件单元格数值公式多项条件删除条件格式定位条件格式 Edit GoToorF5 Special ConditionalFormats 选择All或Same Same指与当前活动单元格相同设置的 提醒 使用条件格式后要小心使用粘贴与格式刷 编辑工作表 条件格式与数据有效性 条件格式ConditionalFormat 条件格式应用 公式 当把一个公式直接输入到 ConditionalFormat 对话框时 条件格式的真正功能才会体现 简单公式 例 建立条件格式将B2 B10范围中的空白单元格添加阴影 公式 B2 使用函数 以下所有示例公式假设所选择的范围的左上单元格是A1 例1 辨别非数字数据公式 ISTEXT A1 例2 辨别均值以上的单元格公式 A1 AVERAGE A 1 A 15 例3 辨别定月份中的日期公式 MONTH A1 6 单元格包含6月份的日期 例4 辨别当前日期公式 A1 TODAY 例5 辨别周末日期公式 OR WEEKDAY A1 7 WEEKDAY A1 1 如果应用范围中包含空单元格应使用的公式 IF ISBLANK A1 OR WEEKDAY A1 7 WEEKDAY A1 1 条件格式ConditionalFormat 编辑工作表 条件格式与数据有效性 条件格式应用 公式 使用函数 例6 隐藏错误值公式 ISERROR A1 将错误值单元格字体设为背景色公式 IF ISERR B1 C1 B1 C1 例7 辨别范围中的最大值公式 A1 MAX A A A 30 例8 辨别范围中三个最大值公式 A1 LARGE A 1 A 30 3 例9 显示交替行阴影公式 MOD ROW 2 0四行加阴影 接下来的四行不加阴影 MOD INT ROW 1 4 2 例10 辨别范围中重复数值对重复数值应用格式 公式 COUNTIF A 1 D 12 A1 1对非重复数值应用格式 公式 COUNTIF A 1 D 12 A1 1 条件格式ConditionalFormat 编辑工作表 条件格式与数据有效性 功能 数据有效性可允许建立特定的规则 用来规定可以向单元格中输入什么 步骤 示范 菜单栏 Data Validation 数据有效性DataValidation 编辑工作表 条件格式与数据有效性 数据有效性应用使用数据有效性公式接受特定输入项 下列例子都使用直接输入到 DataValidation 对话框的公式 以下所有示例公式假设所选择的范围的左上单元格是A1 例1 只接受文本公式 ISTEST A1 例2 接受比前一个单元格大的数值公式 A1 A1例3 只接受非重复输入项公式 COUNTIF A 1 C 20 A1 1例4 接受以 A 开头的文本公式 LEFT A1 A 数据有效性DataValidation 编辑工作表 条件格式与数据有效性 编辑工作表 查找与替换 功能 当需要重新查看或修改工作表中的某一部分内容时 可以查找和替换指定的任何数值 包括文本 数字 日期 或者查找一个公式 一个附注 运用 示例 1 执行 编辑 菜单中的 查找 命令 屏幕显示对话框 2 在 查找内容 框中输入要查找的字符串 然后指定 搜索方式 和 搜索范围 最后按 查找下一个 按钮即可开始查找工作 3 当Excel找到一个匹配的内容后 单元格指针就会指向该单元格 之后我们可以决定下一步的操作 如果还需要进一步查找 可以选择并按下 查找下一个 按钮 也可选择 关闭 按钮 退出查找对话框 提示 查找目标中最多可输入255个英文字母 一个汉字相当于两个英文字母 由于查找目标框所能显示的字数不多 当我们输入的字符串超过其显示范围时 Excel会自动卷动字符串 来显示超过范围的资料 我们也可通过移动光标卷动字符串 Excel还提供了公式 附注的查找 提示 如果要对工作表执行查找或替换工作 可以先将它们选定为工作表组 再执行相应的操作 替换命令替换命令与查找命令类似 前者将查找到的字符串转换成一个新的字符串 以便于对工作表进行编辑 执行替换操作的步骤如下 执行 编辑 菜单中的 替换 命令 屏幕显示对话框 菜单栏 Edit Find or Replace 查找与替换Find Replace 通配符以下通配符可作为筛选以及查找和替换内容时的比较条件 编辑工作表 查找与替换 编辑工作表 筛选与排序 功能 排序是根据某一指定的数据的顺序重新对行与列的位置进行调整 运用 简单的排序按笔划进行排序按行排序菜单栏 Data Sort 排序Sort 功能 筛选符合某种条件的数据 运用 用于筛选的条件比简单的数据 利用 自动筛选 功能 需同时筛选出符合多组条件的记录 利用高级筛选 示例 利用高级筛选删除重复记录 1 打开那篇包含着重复记录的Excel文件2 选中所有记录 点击 数据 菜单 筛选 高级筛选 命令3 勾选 选择不重复的记录 复选框 然后将筛选方式更改为 将筛选结果复制到其他位置 并设置好目标区域 小提示 默认的 在原有位置显示筛选结果 一项虽然也能得到不重复的记录 但这毕竟只是筛选 Excel只是将重复的记录隐藏罢了 并不能完全达到我们的目的菜单栏 Data Filter 筛选Filter 编辑工作表 筛选与排序 高级筛选的使用第一步 建立条件区域 第一行条件区域输入列标签 说明 请确保在条件区域与数据清单之间至少留了一个空白行 不能连接 其他行条件区域输入筛选条件 说明 1 单列上具有多个条件 如果对于某一列具有两个或多个筛选条件 那么可直接在各行中从上到下依次键入各个条件 2 多列上具有单个条件若要在两列或多列中查找满足单个条件的数据 请在条件区域的同一行中输入所有条件 3 某一列或另一列上具有单个条件若要找到满足一列条件或另一列条件的数据 请在条件区域的不同行中输入条件 未完下页续 编辑工作表 筛选与排序 输入筛选条件 4 两列上具有两组条件之一若要找到满足两组条件 每一组条件都包含针对多列的条件 之一的数据行 请在各行中键入条件5 一列有两组以上条件若要找到满足两组以上条件的行 请用相同的列标包括多列 条件 所指定的限制查询或筛选的结果集中包含哪些记录的条件 编辑工作表 筛选与排序 第二步 选定需要筛选的数据清单中任一单元格 第三步 选择菜单 Data Filter AdvancedFilter 第四步 在 条件区域 编辑框中 输入条件区域的引用 并包括条件标志 第五步 若要更改筛选数据的方式 可更改条件区域中的值 并再次筛选数据 注 如果需要将筛选结果放到另一位置 请选择 CopytoanotherLocation 编辑工作表 筛选与排序 1 隐藏工作表菜单栏 Format Sheet Hide 2 显示已隐藏的工作表菜单栏 Format Sheet Unhide 编辑工作表 隐藏或显示工作表 功能 允许指定用户访问受保护的区域禁止工作簿文件编辑禁止工作簿文件被查看运用 设置工作薄和工作表保护1 Tools Protect2 输入密码 撤消工作薄和工作表保护 只允许用户修改