excel高级应用技巧.ppt

数据处理方法与技巧 EXCEL高级应用 江苏大学教师教育学院陶明华电话 13505280548E Mail mhtao 2 内容 1 EXCEL基本概念及数据输入技巧2 清除值为0的单元格3 在空单元格中输入相同的值4 数据的同步变化 跨工作簿计算5 各种函数 rankmatchindexvlookupoffset 6 确定年级班名次7 将不及格的成绩用红色表示8 定位查找9 等级考试发证问题10 盘库打印问题11 数据透视表12 高级筛选问题13 双轴图表的绘制14 邮件合并 图片的使用 15 宏与VBA 3 一 基本概念1 工作簿 一个文件 等价于一本活页夹 2 工作表 等价于活页夹中的活页纸 一个工作簿中最多可包含255个工作表 3 单元格 等价于活页纸上的小方格 一个工作表中有65536行 1 65536 256列 A IV 4 活动工作簿5 活动工作表6 活动单元格7 单元格地址 相对地址 绝对地址 混合地址 例 B2 B 2B 2或 B2打开 投资额 工作簿 计算投资比例 一 基本概念及数据输入技巧 4 二 数据输入及技巧1 输入文本 例如 姓名 职称 电话号码 身份证号码 2 输入数值 例如 整数 实数 科学记数 分数 3 输入日期 例如 2003 7 23 CTRL 键可将计算机中的日期调入单元格 4 输入时间 例如 14 30 20 CTRL SHIFT 键可将计算机中的时间调入单元格 一 基本概念及数据输入技巧 5 二 数据输入及技巧5 等差或等比数列的输入方法 1 先输入二个数据 2 选定这二个单元格 3 鼠标靠向填充柄 等鼠标指针变为 4 按住鼠标右键拖动至目的地 松开鼠标标右键 从弹出的快捷菜单中选 等差序列 或 等比序列 6 日期输入方法 1 先输入一个日期 2 选定这个单元格 3 鼠标靠向填充柄 等变为 4 按住鼠标右键拖动至目的地 松开鼠标 从弹出的快捷菜单中选 以年填充 或 以月填充 等 一 基本概念及数据输入技巧 6 一 基本概念及数据输入技巧 7 在一个区域内输入相同的数据CTRL ENTER键8 自定义序列单击 工具 菜单下的 选项 再单击 自定义序列 office按钮 下的 EXCEL选项 下的 常用 标签的 编辑自定义列表 进行相应的操作即可9 学号录入后三位即可显示为2006152XXX10 班级录入编号 如1 即可显示为全称 2006级数控技术1班 11 性别可通过下拉框选择12 身份证号具有提示功能及位数较验功能 7 一 基本概念及数据输入技巧 在 考试报名表 工作簿中进行操作 数据格式的设置 操作步骤 1 单击 格式 菜单下的 单元格 或 开始 菜单下 单元格 菜单下的 设置单元格格式 2 进行自定义格式的设置 2006152 000 2006级数控技术 0 班 3 选定学号下的区域 设定为自定义格式 2006152 000 4 选定班级下的区域 设定为自定义格式 2006级数控技术 0 班 思考题 能否设定输入 江苏大学10级2班 江苏大学11级3班 8 一 基本概念及数据输入技巧 数据有效性的设置操作步骤 4 选定性别下的区域 单击 数据 菜单下的 数据有效性 允许选 序列 来源框中输入 男 女 或单击 数据 菜单下的 数据有效性 下的 数据有效性 5 选定身份证号下的区域 从E3开始 单击 数据 菜单下的 数据有效性 进行设置 9 一 基本概念及数据输入技巧 10 一 基本概念及数据输入技巧 11 一 基本概念及数据输入技巧 数据有效性的清除 例如 清除 性别 标题下的数据有效性 选定性别下的区域 单击 数据 菜单下的 数据有效性 从弹出的对话框中单击左下角的 全部清除 按钮即可 12 操作方法 在 基础操作表 工作簿中进行 1 选定需要清除值为0的数据区域 E2 F204 2 单击 编辑 菜单下的 替换 命令或按快捷键Ctrl H 或 开始 菜单下的 编辑 菜单下的 查找和选择 菜单下的 替换 3 在弹出的 查找和替换 对话框中的查找内容处输入0 在替换为处不输入任何内容 选中 单元格匹配 单击 全部替换 命令按钮 即可将 E2 F204 区域中所有值为0的单元格清除 也可以用 查找 命令去做 二 清除值为0的单元格 13 例 如何将借方金额和贷方金额中为空的单元格输入0操作方法 在基础表中进行操作 1 选定数据区域 E2 F204 2 单击 编辑 菜单下的 定位 命令 或 开始 菜单下的 编辑 菜单下的 查找和选择 菜单下的 定位条件 3 在弹出的 定位 对话框中 单击 定位条件 按钮 4 在弹出 定位条件 对话框中选 空值 单击 确定 5 原区域中所有空值的单元格均被选中 6 输入0值 然后按CTRL ENTER也可以用 查找 命令去做 三 在空单元格中输入相同的值 14 打开 成绩表 工作簿 将成绩表中的语文 数学 英语成绩分别复制相应的三张工作表中 要求数据同步变化 使用 选择性粘贴 中的 粘贴链接 即可 将计算结果复制到其它单元格中 用 选择性粘贴 中的 数值 数据的跨工作簿计算打开 跨工作簿计算用数据表 文件夹中的四个工作簿可实现数据的跨工作薄计算 打开 工资单 工作簿 用合并计算的方法求出男女同志的平均工资 平均奖金 四 数据的同步变化 数据跨工作簿计算 合并计算 15 打开 工资单 工作簿1 RANK函数 用于乱序数据的排序号 例 在J2中输入 RANK I2 I 2 I 40 0 0表示从大到小2 IF函数 根据给定的条件确定相应的值 例 实发工资大于等于600 评价为 高 实发工资大于等于500小于600评价为 中 实发工资小于500评价为 低 在K2单元格中输入 IF I2 600 高 IF I2 500 中 低 3 MATCH函数 匹配函数 如工作表中的C46位置中的 MATCH B46 B2 B40 0 得出B46中名字与B2 B40中的第几个名字匹配 成功给出第几个的值 匹配类型为1时 找小于等于要找值的最大值 数据必须从小到大排序 匹配类型为0时 查找等于要找值的第一个值 数据无需排序 匹配类型为 1时 找大于等于要找值的最小值 数据必须从大到小排序 五 各种函数的应用 16 打开 工资单 工作簿4 VLOOKUP函数 定位查找相应的值VLOOKUP函数的有数 搜索表区域首列满足条件的元素 确定待检索单元格在区域中的行序号 再进一步返回单元格的值 缺省表以升序排序 参数1 需要在数据表首列进行搜索的值参数2 需要在其中搜索数据的信息表 可以是一个区域参数3 满足条件的单元格在数据区域中的列序号 首列为1参数4 指定在查找时要求大致匹配还是精确匹配 FALSE为大致匹配上 TRUE为精确匹配 缺省为精确匹配例如 F46单元格中 VLOOKUP E46 B2 E40 4 0 公式中4表示区域中的第四列 即基本工资这一列 五 各种函数的应用 17 打开 工资单 工作簿5 ISERROR函数 判断是否错误 判断单元格是否错误 6 INDEX函数 返回指定行列交叉处引用的单元格参数1 为指定的要搜索的范围参数2 为指定返回的行序号参数3 为指定返回的列序号参数4 返回该区域中行和列交叉域 一般为省略例 返回A1 C10区域 第五行 第二列的值 INDEX A1 C10 5 2 例 在C50的单元格中输入 INDEX A2 I40 3 2 返值为 常镇 7 OFFSET函数 偏移定位 OFFSET 数据库 B 3 20 8 第一参数为作为参照系区域在原始表中的偏移量 第二个参数是行相对于参照系的偏移量 第三个参数是列相对参照系的偏移量 第四个参数是新区域的行数 第五个参数是新区域的列数 五 各种函数的应用 18 六 确定年级 班级名次 打开 考试成绩表 工作簿操作步骤 1 在I3单元格中输入 RANK H3 H 3 H 122 0 注意区域需用绝对地址 0表示从大到小 2 在J3单元格中输入 RANK H3 H 3 H 42 0 排一班的班级排名 3 在J43单元格中输入 RANK H43 H 43 H 82 0 排二班的班级排名 4 在J83单元格中输入 RANK H83 H 83 H 122 0 排三班的班级排名 19 七 将不及格的成绩用红色表示 打开 考试成绩表 工作簿操作步骤 1 选定C3 G122区域 2 单击 格式 菜单下的 条件格式 或 开始 菜单下的 样式 菜单下的 条件格式 菜单下的 新建规则 从弹出的对话框中选 只为以下内容设置单元格格式 3 在弹出的对话框中进行相应的设置 20 八 定位查找 打开 定位查找 工作簿中的定位查找工作表 MATCH函数的使用 操作步骤 1 在B21单元格中输入 输入条件 合并B21C21单元格 2 在B22中输入 行 C22中输入 列 3 将光标定位到B23 在 数据 菜单下选 有效性 从弹出的对话框中选 设置 标签 允许选 序列 来源设为 A 2 A 17 同理将C22单元格进行设置 来源设为 B 1 O 1 4 在F22中输入 结果 F23中输入 INDEX B2 O17 MATCH B23 A2 A17 0 MATCH C23 B1 O1 0 match函数中的0为匹配方式 5 选中B2 O17区域 设置 条件格式 选 公式 B 23 A2 C 23 B 1 颜色设置为 黄色 21 九 等级考试发证问题 打开 技能等级办证登记单 工作簿 IF函数的使用 操作步骤 选办证登记单 成绩高于70分通过标记为 P 且巳交费者可办证 1 选定C3单元格 输入 IF B3 70 P 2 选定E3 输入 IF C3 P IF D3 30 是 否 22 十 会计科目问题 打开 科目表 工作簿 VLOOKUP函数的使用 操作步骤 在C2单元格中输入 IF LEN A2 4 B2 VLOOKUP LEFT A2 4 A B 2 0 在D2单元格中输入 IF LEN A2 7 B2 IF LEN A2 7 VLOOKUP LEFT A2 7 A B 2 0 VLOOKUP函数的有数 搜索表区域首列满足条件的元素 确定待检索单元格在区域中的行序号 再进一步返回单元格的值 缺省表以升序排序 参数1 需要在数据表首列进行搜索的值参数2 需要在其中搜索数据的信息表 可以是一个区域参数3 满足条件的单元格在数据区域中的列序号 首列为1 注意 要搜索的值必须是区域的第一列 且此列巳按升序排序 参数4 指定在查找时要求大致匹配还是精确匹配 FALSE为大致匹配上 TRUE为精确匹配 缺省为精确匹配 23 十一 盘库打印问题 打开 盘库打印条 工作簿 INDEX函数的使用 操作步骤 1 单击 插入 菜单下的 名称 下的 定义 在对话框中输入 DATA 引用位置输入 OFFSET 数据库 B 3 COUNTA 数据库 B 3 B 999 8 第一参数为作为参照系区域在原始表中的偏移量 第二个参数是行相对于参照系的偏移量 第三个参数是列相对参照系的偏移量 第四个参数是新区域的行数 第五个参数是新区域的列数 2 在 打印表 的J1单元格中输入 请输入页数 J2单元格中输入数值1 3 在 打印表 工作表中 输入所示的内容 4 在A4单元格中输入 INEDX DATA 4 J 2 1 1 COLUMN 横向拖动公式 5 在A12单元格中输入 INEDX DATA 4 J 2 1 2 COLUMN 横向拖动公式 6 在A21A30中进行类似的输入 INEDX DATA 4 J 2 1 3 COLUMN INEDX DATA 4 J 2 1 4 COLUMN 横向拖动公式 24 十二 数据透视表 操作步骤 打开材料库存 数据透视表 1 选定数据区域 2 数据 数据透视表 或 插入 菜单下的 表 菜单下的 数据透视表 3 根据提示向导进行操作将原始表中的数据通过数据透视表做作答案表中的形式 25 十三 数据的高级筛选 操作步骤 打开高级筛选综述表 高级筛选 1 先配好筛选的条件 将要用到的标题复制到数据区域的旁边 空一列即可 在复制出来的标题下输入筛选条件 同一行上为 并且 条件 不同行上为 或者 条件 2 选定数据区域 3 数据 筛选 高级筛选 4 从弹出的对话框中选定 方式 数据区域 条件区域 以及数据将要复制的位置 5 单击 确定 按钮注意 1 在条件区域中 条件单元格内包含单元格引用 如 D5 800 条件区域标题虽然可以不填 但在选择筛选条件时却不能不选 即如果只选择 D5 800 所在的单元格作为筛选条件 结果是错误的 必须把筛选条件单元格的上一个单元格一并选中 2 在条件区域中 条件单元格内不包含单元格引用 如 800 必须写上与数据区标题相同名称 其他任何名称或不填都会产生错误结果 建议使用复制粘贴的方法 避免输入失误造成筛选结果出错 26 十四 特殊排序 操作步骤 打开教师通信录表 按表中的职务进行排序 1 工具 选项 自定义序列 在弹出的对话框中导入 职务 的序列 或 office按钮 下的 EXCEL选项 下的 常用 标签的 编辑自定义列表 导入相应的序列 2 选定数据区域 3 数据 排序 单击对话框中的 选项 按钮 确定自定义的排序序列 4 选定主关键字 单击 确定 27 十五 双轴图表的绘制 操作步骤 打开双轴图表 xls 1 选择数据区域中的某个单元格 单击菜单 插入 图表 在 图表向导 对话框中选择图表类型为 折线图 单击 完成 按钮 28 2 这时可以看到插入的两个数据系列中 由于销售增长率数值太小而无法显示其变化趋势 右击 销售增长率 系列 在弹出的快捷菜单中选择 数据系列格式 在弹出的 数据系列格式 对话框中选择 坐标轴 选项卡 选择 次坐标轴 单击 确定 十五 双轴图表的绘制 29 3 右击 销售额 系列 选择 图表类型 将 销售额 系列的图表类型更改为 柱形图 4 最后 修改绘图区图案背景 修改图例位置并完成双轴图 十五 双轴图表的绘制 30 1 选定有错误信息的区域 例如 B2 B62 格式 菜单下的 条件格式 公式中输入 ISERROR B2 字体颜色设为白色 十六 去掉出错单元格的错误信息 31 一 准备工作 准备一份Excel的学生信息数据 需要注意的是 在使用Excel工作簿时 必须保证数据文件是数据库格式 即第一行必须是字段名 数据行中间不能有空行等 比如第一行里面包含了学号 姓名 性别 照片格式 照片名等信息的字段 照片名必须与学生姓名一致 照片 的输入如下图所示 单击G2 在G2的编辑栏中输入 A2 F2 后敲回车键 再双击单元格进行自动填充即可 数据准备工作的正确与否 关系到以后打印出来的证件正确与否 所以必须要仔细校对检查 十七 邮件合并 带图片的邮件合并 32 三 照片的拍摄采集与编辑照片格式 jpg 大小 10 15KB为宜 名称 要与学生姓名一致 不能有同音字和形近字 然后将刚才编辑的Excel数据 word模板格式 编辑好的照片放在同一个文件夹中 四 利用Word邮件合并功能将Excel数据 照片合并到word模板 步骤如下 1 启动word程序 打开word模板 从菜单栏中选择 工具 函与邮件 显示邮件合并工具栏 命令2 在邮件合并工具栏上单击打开 数据源 工具 打开相应的数据源 十七 邮件合并 带图片的邮件合并 33 3 将光标定位到相应的位置 单击插入 插入域 工具按钮 插入相应的域4 下面是实现批量打印照片的关键一步 就是照片域的实现 先把光标定位在要插入照片的地方 单击 插入 域 在 域名 处选择 Includepicture 在 域属性 文件名中 为了方便起见填入任意字符 比如 1 单击确定 如图 单击刚才插入的 域 shift 键 F9 键 选择 1 点击 插入域 插入 照片名 点击 合并到新文档 确定 单击 保存 在主文档同一目录下 按 ctrl A 键全选 按 F9 在同一页纸中出现了多个准考证 十七 邮件合并 带图片的邮件合并 34 宏是在应用程序中可以自动运行的一连串功能命令 能够完成大量的重复操作 VBA是一种编程语言 专门用于OFFICE中打开 客户资料 工作簿 1 单击 工具 菜单下的 宏 下的 安全性 将安全级设置为 中 或 office按钮 下的 EXCEL选项 下的 信任中心 标签的 信任中心设置 的 宏设置 将安全级设置为 中 2 将活动单元格定位在B2的位置 2 单击 工具 菜单下的 宏 下的 录制新宏 单击选 相对引用 一定要使 相对引用 处于选定的状态 或选择 视图 下的 宏 标签 录制宏 3 将第一个人的资料复制粘贴到指定的位置 复制 粘贴用快捷键进行 将活动单元格定位到第二个人的姓名中 B6 单元格完成一个周期操作 4 单击 停止录制 5 单击 工具 菜单下的 宏 下的 宏 单击宏的名称 再单击 执行 按钮即可 如果单击 选项 按钮 可为宏设置快捷键 如果按ALT F11可进入VBA编程 如果在过程中加入语句 dimjasintegerforj 1to500nextj循环体为原来的宏过程 即可自动完成相应的操作 十八 宏与VBA 35 谢谢大家