excel高级应用技巧.pptVIP

数据处理方法与技巧 EXCEL高级应用 内容 1 EXCEL基本概念及数据输入技巧2 清除值为0的单元格3 在空单元格中输入相同的值4 数据的同步变化 跨工作簿计算5 各种函数 rankmatchindexvlookupoffset 6 确定年级班名次7 将不及格的成绩用红色表示8 定位查找9 等级考试发证问题10 盘库打印问题11 数据透视表12 高级筛选问题13 双轴图表的绘制14 邮件合并 图片的使用 15 宏与VBA 2 一 基本概念及数据输入技巧 一 基本概念1 工作簿 一个文件 等价于一本活页夹 2 工作表 等价于活页夹中的活页纸 一个工作簿中最多可包含255个工作表 3 单元格 等价于活页纸上的小方格 一个工作表中有65536行 1 65536 256列 A IV 4 活动工作簿5 活动工作表6 活动单元格7 单元格地址 相对地址 绝对地址 混合地址 例 B2 B 2B 2或 B2打开 投资额 工作簿 计算投资比例 3 一 基本概念及数据输入技巧 二 数据输入及技巧1 输入文本 例如 姓名 职称 电话号码 身份证号码 2 输入数值 例如 整数 实数 科学记数 分数 3 输入日期 例如 2003 7 23 CTRL 键可将计算机中的日期调入单元格 4 输入时间 例如 14 30 20 CTRL SHIFT 键可将计算机中的时间调入单元格 4 一 基本概念及数据输入技巧 二 数据输入及技巧5 等差或等比数列的输入方法 1 先输入二个数据 2 选定这二个单元格 3 鼠标靠向填充柄 等鼠标指针变为 4 按住鼠标右键拖动至目的地 松开鼠标标右键 从弹出的快捷菜单中选 等差序列 或 等比序列 6 日期输入方法 1 先输入一个日期 2 选定这个单元格 3 鼠标靠向填充柄 等变为 4 按住鼠标右键拖动至目的地 松开鼠标 从弹出的快捷菜单中选 以年填充 或 以月填充 等 5 一 基本概念及数据输入技巧 7 在一个区域内输入相同的数据CTRL ENTER键8 自定义序列单击 文件 菜单下的 选项 高级 标签中的 编辑自定义列表 进行相应的操作即可 例 教授 副教授 讲师 助教 6 一 基本概念及数据输入技巧 在 考试报名表 工作簿中进行操作 数据格式的设置 操作步骤 1 选定区域 单击 开始 菜单下 数字 下的 其他数字格式 或 选定区域 单击右键选 设置单元格格式 2 单击 数字 标签 再单击 自定义 进行自定义格式的设置 2006152 000 2006级数控技术 0 班 思考题 能否设定输入 江苏大学11级2班 江苏大学12级3班 7 一 基本概念及数据输入技巧 数据有效性的设置操作步骤 3 选定性别下的区域 单击 数据 菜单下的 数据有效性 允许选 序列 来源框中输入 男 女 或单击 数据 菜单下的 数据有效性 下的 数据有效性 4 选定身份证号下的区域 从E3开始 单击 数据 菜单下的 数据有效性 进行设置 8 一 基本概念及数据输入技巧 9 一 基本概念及数据输入技巧 10 一 基本概念及数据输入技巧 数据有效性的清除 例如 清除 性别 标题下的数据有效性 选定性别下的区域 单击 数据 菜单下的 数据有效性 从弹出的对话框中单击左下角的 全部清除 按钮即可 11 操作方法 在 基础操作表 工作簿中进行 1 选定需要清除值为0的数据区域 E2 F204 2 单击 开始 菜单下的 查找和替换 中的替换命令 3 在弹出的 查找和替换 对话框中的查找内容处输入0 在替换为处不输入任何内容 选中 单元格匹配 单击 全部替换 命令按钮 即可将 E2 F204 区域中所有值为0的单元格清除 也可以用 查找 命令去做 二 清除值为0的单元格 12 例 如何将借方金额和贷方金额中为空的单元格输入0操作方法 在基础表中进行操作 1 选定数据区域 E2 F204 2 单击 开始 菜单下的 查找和替换 下的 定位条件 3 在弹出的 定位 对话框中 单击 定位条件 按钮 4 在弹出 定位条件 对话框中选 空值 单击 确定 5 原区域中所有空值的单元格均被选中 6 输入0值 然后按CTRL ENTER也可以用 查找 命令去做 三 在空单元格中输入相同的值 13 打开 成绩表 工作簿 将成绩表中的语文 数学 英语成绩分别复制相应的三张工作表中 要求数据同步变化 使用 选择性粘贴 中的 粘贴链接 即可 将计算结果复制到其它单元格中 用 选择性粘贴 中的 数值 数据的跨工作簿计算打开 跨工作簿计算用数据表 文件夹中的四个工作簿可实现数据的跨工作薄计算 打开 工资单 工作簿 用合并计算的方法求出男女同志的平均工资 平均奖金 四 数据的同步变化 数据跨工作簿计算 合并计算 14 打开 工资单 工作簿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时 找大于等于要找值的最小值 数据必须从大到小排序 五 各种函数的应用 15 打开 工资单 工作簿4 VLOOKUP函数 定位查找相应的值VLOOKUP函数的有数 搜索表区域首列满足条件的元素 确定待检索单元格在区域中的行序号 再进一步返回单元格的值 缺省表以升序排序 参数1 需要在数据表首列进行搜索的值参数2 需要在其中搜索数据的信息表 可以是一个区域参数3 满足条件的单元格在数据区域中的列序号 首列为1参数4 指定在查找时要求大致匹配还是精确匹配 FALSE为大致匹配上 TRUE为精确匹配 缺省为精确匹配例如 F46单元格中 VLOOKUP E46 B2 E40 4 0 公式中4表示区域中的第四列 即基本工资这一列 五 各种函数的应用 16 打开 工资单 工作簿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 第一参数为作为参照系区域在原始表中的偏移量 第二个参数是行相对于参照系的偏移量 第三个参数是列相对参照系的偏移量 第四个参数是新区域的行数 第五个参数是新区域的列数 五 各种函数的应用 17 六 确定年级 班级名次 打开 考试成绩表 工作簿操作步骤 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 排三班的班级排名 18 七 将不及格的成绩用红色表示 打开 考试成绩表 工作簿操作步骤 1 选定C3 G122区域 2 单击 开始 菜单下的 样式 菜单下的 条件格式 菜单下的 新建规则 从弹出的对话框中选 只为包含以下内容设置单元格格式 3 在弹出的对话框中进行相应的设置如果有多个条件 则重复以上操作 注意2003中 多个条件需一次完成 19 八 定位查找 打开 定位查找 工作簿中的定位查找工作表 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 颜色设置为 黄色 20 八 定位查找 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 插入 菜单下的 表格 菜单下的 数据透视表 25 十二 数据透视表 26 十三 数据的高级筛选 操作步骤 打开高级筛选综述表 高级筛选 1 先配好筛选的条件 将要用到的标题复制到数据区域的旁边 空一列即可 在复制出来的标题下输入筛选条件 同一行上为 并且 条件 不同行上为 或者 条件 2 选定数据区域 3 数据 排序和筛选 筛选 高级 4 从弹出的对话框中选定 方式 数据区域 条件区域 以及数据将要复制的位置 5 单击 确定 按钮注意 1 在条件区域中 条件单元格内包含单元格引用 如 D5 800 条件区域标题虽然可以不填 但在选择筛选条件时却不能不选 即如果只选择 D5 800 所在的单元格作为筛选条件 结果是错误的 必须把筛选条件单元格的上一个单元格一并选中 2 在条件区域中 条件单元格内不包含单元格引用 如 800 必须写上与数据区标题相同名称 其他任何名称或不填都会产生错误结果 建议使用复制粘贴的方法 避免输入失误造成筛选结果出错 27 十四 特殊排序 操作步骤 打开教师通信录表 按表中的职务进行排序 1 开始 下的 选项 下的 高级 标签的 编辑自定义列表 导入相应的序列 2 选定数据区域 3 单击 数据 菜单下的 排序 单击对话框中的 次序 按钮 确定自定义的排序序列 4 选定主关键字 单击 确定 28 十五 双轴图表的绘制 操作步骤 打开双轴图表 xls 1 选择数据区域中的某个单元格 单击菜单 插入 图表 中的 折线图 选 数据点折线图 29 十五 双轴图表的绘制 2 这时可以看到插入的两个数据系列中 由于销售增长率数值太小而无法显示其变化趋势 右击 销售增长率 系列 在弹出的快捷菜单中选择 设置数据系列格式 在弹出的 数据系列格式 对话框中选择 系列选项 选项卡 选择 次坐标轴 单击 确定 30 十五 双轴图表的绘制 3 右击 销售额 系列 选择 更改系列图表类型 将 销售额 系列的图表类型更改为 柱形图 4 最后 修改绘图区图案背景 修改图例位置并完成双轴图 31 十六 去掉出错单元格的错误信息 打开 投资额 工作簿 1 选定有错误信息的区域 例如 C2 C62 单击 开始 菜单下的 样式 菜单下的 条件格式 菜单下的 新建规则 从弹出的对话框中选 使用公式确定要设置单元格格式 3 在弹出的对话框中进行相应的设置公式中输入 ISERROR C2 字体颜色设为白色 32 十七 邮件合并 带图片的邮件合并 一 准备工作 准备一份Excel的学生信息数据 需要注意的是 在使用Excel工作簿时 必须保证数据文件是数据库格式 即第一行必须是字段名 数据行中间不能有空行等 比如第一行里面包含了学号 姓名 性别 照片格式 照片名等信息的字段 照片名必须与学生姓名一致 照片 的输入如下图所示 单击G2 在G2的编辑栏中输入 A2 F2 后敲回车键 再双击单元格进行自动填充即可 数据准备工作的正确与否 关系到以后打印出来的证件正确与否 所以必须要仔细校对检查 33 十七 邮件合并 带图片的邮件合并 三 照片的拍摄采集与编辑照片格式 jpg 大小 10 15KB为宜 名称 要与学生姓名一致 不能有同音字和形近字 然后将刚才编辑的Excel数据 word模板格式 编辑好的照片放在同一个文件夹中 如果照片放在子文件夹 则路径中分隔符需用二个反斜杠 四 利用Word邮件合并功能将Excel数据 照片合并到word模板 步骤如下 1 启动word程序 从菜单栏中选择 视图 菜单下 工具栏 下的 邮件合并 显示 邮件合并 工具栏2 在邮件合并工具栏上单击打开 数据源 工具 打开相应的数据源 EXCEL工作簿文件 34 十七 邮件合并 带图片的邮件合并 3 将光标定位到相应的位置 单击插入 插入域 工具按钮 插入相应的域4 下面是实现