《实用技巧与提高崔旺PPT课件.ppt》由会员分享，可在线阅读，更多相关《实用技巧与提高崔旺PPT课件.ppt（73页珍藏版）》请在金锄头文库上搜索。
1、 1 Excel技巧与提高 讲师 崔旺课时 2 5h 2 序言 Excel用户水平的5个层次 你属于哪一层次 3 目前EXCEL使用状况 原因 根本不知道还有其他功能知道功能但不知道如何使用暂时使用不上 不去了解 4 知识结构 Excel数据处理与操作 Excel函数与公式 Excel图表与图形 ExcelVBA与宏 Excel数据分析 5 目录 一 认识Excel二 数据处理三 函数与公式四 高级图表 6 工具栏 一 认识Excel2003 窗口标题 显示程序名及文件名 列号 状态栏 行号 标签栏 编辑栏 显示当前单元格中的数据或公式 单击左边 fx 可输入公式 工作区 菜单栏 左右视窗分隔
2、条 上下视窗分隔条 全选按钮 储存格名称栏由列标和行标组成当前活动储存格地址 7 1 自定义工具栏 1 视图 工具栏 自定义 2 工具 自定义 8 2 认识选项 1 工具 选项 9 认识选项 续1 10 认识选项 续2 11 认识选项 续3 12 二 认识Excel2007 工作区 13 1 Excel2007与2003比较 14 2 Excel2007质的突破 1 灵巧变化的状态栏1 1状态栏缩放控制 增加了一个不需弹出窗口的控制滑块来调整文件的缩放比例 当调整控制滑块时 文件同时改变显示比例 也可以使用 和 按钮来放大或缩小显示比例 每点击一次调节10 15 1 2多样化的计算状态栏确在之
3、前的Excel版本中 当你选中了数值数据时 可以在状态栏看到这些数据的小计 求和 计数 平均值等等 可以选择6种不同的小计方式 但一次只能看到一种 Excel2007中可以把几个或者全部的小计方式显示在状态栏 求和 最大值 最小值 计数 计数值 平均值的全部显示或者显示其任意组合 1 3插入工作表按钮只要单击这个按钮就会在工作簿中新增一个工作表 这一点比较快捷 16 2 图表的可视化更加突出 17 EXCEL2007完全兼容2003通过 Microsoftoffice 另存为 命令 转换为Excel2003等低级版本 EXCEl2003不能兼容2007 若要打开2007版本需安装兼容包 Fil
4、eFormatConverters 三 EXCEl2003与2007的转换与兼容 18 目录 一 认识Excel二 数据处理三 函数与公式四 高级图表 19 二 数据处理 1 自动填充2 快速输入与选择粘贴3 数据有效性4 自定义格式5 条件格式6 分类汇总7 合并计算8 数据透视表 20 1 自动填充 使用菜单命令使用鼠标左键配合智能标记配合键根据范本填充使用鼠标右键 你知道几种 21 2 快速输入与选择粘贴 1 同时在多个单元格中输入相同数据 快捷鍵Ctrl Enter 2 选择性粘贴 很实用的功能 22 3 数据有效性 23 3 数据有效性 续 停止 不能输入不符合条件的数据 点击 重试
5、 可重新输入 点击 取消 则取消输入 警告 可以选择是否继续输入数据 点击 是 可以强行输入不符合条件数据 点击 否 可以重新输入数据 点击 取消 则取消输入 信息 可选择是否输入数据 点击 确定 可以直接输入数据 点击 取消 可以取消输入 24 4 自定义格式 可为4种类型的数值指定不同的格式 正数 负数 零值和文本 节代码与节代码之间用英文状态下的分号 隔开 条件格式代码组成结构 快捷键Ctrl 1 25 4 自定义格式 续 00 红色 00 绿色 0 00 蓝色 0 数字占位符 如果单元格的内容大于占位符 则显示实际数字 如果小于点位符的数量 则用0补足 数字占位符 只显示有意义的数字而
6、不显示无意义的零 小数点后数字如大于 的数量 则按 的位数四舍五入 1 第一节 即 00 单元格输入的数值为 正数 时 应用该格式 2 第二节 即 红色 00 单元格输入的数值为 负数 时 应用该格式 3 第三节 即 绿色 0 00 单元格输入的数值为 0 时 应用该格式 4 第四节 即 蓝色 单元格输入的数值为 文本 时 应用该格式 26 4 自定义格式 续 实战练习 编制设备编号 不足10位 编号前补0 并在10位编号前统一加 No 27 5 条件格式 格式 条件格式 28 6 合并计算 例 年销售产品的统计计算 29 7 分类汇总 可手动创建分级显示和自动生成分级显示 注意 1 一个工作
7、表内在同行方向上或列方向上只能创建一个分级显示 2 在同一个分级显示中 最多允许有8个层次关系 30 8 数据透视表 初步的数据统计与分析数据透视表是一个功能非常强大的数据分析工具 它是集排序 筛选 分类汇总及合并计算为一体的综合性数据分析工具 31 提纲 一 认识Excel二 数据处理三 函数与公式四 高级图表 32 3 1什么是函数 什么是公式 公式是以 为引导 通过运算符按照一定的顺序组合进行数据运算处理的等式 函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式 33 3 2解读函数的参数 IF A1 0 SUM B1 G1 函数的结构 嵌套函数 函数名称 以逗号分隔的3
8、个参数 函数的帮助 34 3 3查看公式计算结果 使用公式审核工具分布求值以 IF A1 0 SUM B1 G1 为例 执行 公式求值 1 2 35 查看公式计算结果 续 3 4 5 36 3 4相对单元格与绝对单元格 公式所在单元格与公式所引用单元格的位置关系称为单元格引用的相对性 37 Excel公式相对与绝对地址操作 在Excel中输入公式时 相对地址与绝对地址可以进行转换操作 凡行或列地址前出现有 标志的 该行或列的地址即为绝对地址引用 否则为相对地址引用 在地址输入时 相对地址和绝对地址间可以用F4键进行循环转换 G3 F4 G 3 F4 G 3 F4 G3 F4 38 3 5 工作
9、表的引用 1 引用其他工作表的数据 工作表名称 目标单元格例 sheet2 A12 引用已打开的工作簿的数据 工作簿名称 工作表名称 目标单元格例 Book2 xls sheet1 A 13 引用未打开的工作簿的数据 文件路径 工作簿名称 工作表名称 目标单元格例 D Excel案例 Book2 xls sheet1 A 1 39 函数与公式 1 文本 数字 日期 财务类函数讲解2 实战案例综合应用 40 3 6文本处理函数 用途 连接 前后的字符 字段 1 连接符 41 文本处理函数 2 大小写字母转换Lower Upper Proper 所有大写字母转为小写字母 所有小写字母转为大写字母
10、字符串首字母转为大写字母 LOWER ILOVEEXCEL iloveexcel UPPER iloveexcel ILOVEEXCEL PROPER i爱excel I爱Excel 42 文本处理函数 3 全角半角函数Widechar Asc 半角字母转为全角字母 全角字母转为半角字母 WIDECHAR Excel ASC 我爱 我爱Excel 43 文本处理函数 4 生成A ZCharChar 65 AChar 66 BChar 67 C Char 90 Z 思考 如何快速填充A Z序号 Code A 65 Char 97 aChar 98 bChar 99 c Char 122 z 44
11、 1 如何取出单元格中的部分字符 1 Left 从左边开始截取指定数目的字符 语法 left text num chars 2 Right 从右边开始截取指定数目的字符 语法 right text num chars 3 Mid 从文本指定位置开始截取指定数目的字符语法 mid text start num num chars 文本处理函数 45 文本处理函数 2 Left Right Mid实例练习 注意 MID函数有3个参数 而LEFT RIGHT只有2个参数 为什么是3而不是2 为什么不用Right函数 46 3 7数字计算函数 1 计算余数 Mod 3 2 1Mod 15 4 3Mod
12、 21 7 0 应用 身份证号判断性别 如何判断数字的奇偶性 思考 除2的余数有多少个 除3呢 47 数字计算函数 2 取整函数IntTrunc Int 3 2 返回不大于3 2的最大整数3 Int 3 2 返回不大于 3 2的最大整数 4 Trunc 3 2 返回截去小数部分后的整数3 Trunc 3 2 返回截去小数部分后的整数 3 48 数字计算函数 3 四舍五入Round 区别 49 3 8日期与时间函数 1 4个基础日期函数 特殊函数 Today 读取计算机设置的今日日期 无参数NOW 读取计算机设置的当时时间 无参数 50 日期与时间函数 2 Datedif函数用途 计算两个日期之
13、间的天数 月数或年数 注 此函数在Excel帮助文件中没有 结构 Datedif start date end date unit 51 日期与时间函数 2 Datedif函数 续 52 1 IF 如果您指定的情況结果为TRUE 則传回一个值 若结果为FALSE 则传回另一个值 a 格式 IF logical test value if true value if false 是用来计算TRUE或FALSE的任何值或运算式 如果logical test为TRUE 则会传回该值 如果logical test为FALSE 则会传回该值 3 9 逻辑函数 53 b 范例 某教师依据各学生的考试成绩
14、判定其是否合格过关 如下 IF B4 60 合格 不及格 54 文本 数字 日期类函数案例综合应用 2 MID 从文本字符串中指定的起始位置起 返回指定长度的字符 1 自动从身份证号码中提取出生年月 性别信息 2 自动从参加工作时间中提取工龄信息 1 IF函数 根据逻辑表达式测试的结果 返回相应的值 IF函数允许嵌套 6 连接符 将若干个文字项合并至一个文字项中 5 VALUE 将代表数字的文字串转换成数字 4 DATEDIF 计算两个日期之间的天数 月数或年数 3 TODAY 返回计算机系统内部的当前日期 55 3 10财务函数 概念解释 56 财务函数 案例1 分10个月付清年利率为8 的
15、10000元贷款 计算月支付金额 PTM rate nper pv fv type PTM 8 12 10 10000 1037 03 案例2 需以年利率5 存款15年 达到存款总额1 500 000 计算每月存款金额 PTM rate nper pv fv type PTM 5 12 15 0 1500000 5611 9 57 财务函数 案例3 某人将10000元投入一项事业 年回报率6 计算3年后的累积金额 FV rate nper pmt pv type PTM 6 3 0 10000 11910 16 案例4 两年后需要大笔支出 计划从现在起 每月初存入2000元 年利率2 25 按
16、月计息 2 25 12 计算两年后帐户金额 FV rate nper pmt pv type FV 2 25 12 24 2000 0 1 49141 34 58 财务函数 案例5 某人拟在5年后获得10000元 投资报酬率10 计算现在应该投入的金额 PV rate nper pmt fv type PV 10 5 0 10000 6209 21 案例6 某人余购买一项养老保险 购买成本为60000元 可在20年内每月回报500元 投资报酬率8 计算是否合适 PV rate nper pmt fv type PV 8 12 20 12 500 59777 15不合适 59 3 11查找统计函数 1 VLOOKUP HLOOKUP函数用途 按列 行的方式查询目标值 语法格式 VLOOKUP 需查找值 范围 相对列 假 2 LOOKUP函数用途 模糊及区间查询语法格式 LOOKUP 需查找值 对应值区域 目标值区域 LOOKUP函数要求分级区间是升序排列 60 LOOKUP类查找函数的比较 续 Lookup函数使用方法类同于Vlookup 但两者的语法稍有不同 Lookup lookup
