Excel实战技巧与提高

2页 序言 个层次 刚刚开始接触 基本掌握常用功能 熟练使用常用功能 +数据透视表 +公式 +数组公式 + 专业知识 +行业经验 新手 初级用户 中级用户 高级用户 专家 3页 知识结构 据分析 4页 提纲 一、认识 二、数据处理 三、函数与公式 四、高级图表 5页 打开文件 专业修复的第三方软件： 6页 认识界面 标题栏 菜单栏 工具栏 行号 列号 标签栏 状态栏 ? ? 7页 自定义工具栏 1、“视图” 具栏” 定义” 2、“工具” 定义” ？ 8页 认识选项 9页 认识选项（续） 10页 认识选项（续） 11页 认识选项（续） 12页 提纲 一、认识 二、数据处理 三、函数与公式 四、高级图表 13页 数据处理 1、自动填充 2、定义名称 3、数据有效性 4、自定义格式 5、条件格式 6、分类汇总 7、合并计算 8、数据透视表 14页 1、自动填充 等差填充 日期填充 文字填充 特定填充 为什么无法填充？ 15页 2、定义名称 1、插入名称。 “插入” -“名称” -“定义” 2、使用名称框命名 3、指定名称。 “插入” -“名称” -“指定” 16页 2、定义名称（续） 全局定义 与 局部定义： 1、默认情况下，为全局定义 2、局部定义命名方法：工作表名称 +半角感叹号 +名称 命名规则： 1、可用数字与字母组合，但不能以数字开头； 2、不能以 R、 C、 r、 R、 1； 3、名称中不能包含空格，可用下划线或点号代替； 4、不能使用除了下换线、点号和反斜杠（ ）意外的其他符号。允许使用问号，但不能作为名称的开头； 5、名称字符不能超过 255个； 6、字母不区分大小写。 17页 3、数据有效性 18页 3、数据有效性（续） 停止 不能输入不符合条件的数据，点击“重试”可重新输入，点击“取消”则取消输入。 警告 可以选择是否继续输入数据，点击“是”可以强行输入不符合条件数据，点击“否”可以重新输入数据，点击“取消”则取消输入。 信息 可选择是否输入数据，点击“确定”可以直接输入数据，点击“取消”可以取消输入。 19页 3、数据有效性（续） 菜单“数据” “有效性” 20页 4、自定义格式 可为 4种类型的数值指定不同的格式：正数、负数、零值和文本。 “大于条件值”格式；“小于条件值”格式；“等于条件值”格式；文本格式 格式代码组成结构： 注：没有特别指定条件值的时候，默认条件值为 0 21页 4、自定义格式（续） #,#红色 -#,#绿色 G/通用格式；“ * ” 显示为 原始数值 说明 1,数，显示为带千分号、两位小数 数，显示为带千分号、两位小数、红色 0 0 零值，显示为绿色 * 等于 文本，显示为 * 100#,#0,1:” ”) 函数的结构 嵌套函数 函数名称 以逗号分隔的 3个参数 思考：哪些函数不带参数？ 31页 查看公式计算结果 使用公式审核工具分布求值 以 =10,1:” ”)为例，执行“公式求值” 1 2 32页 查看公式计算结果 （续） 3 4 5 33页 错误类型 常见的错误值及含义 错误值类型 含义 # 当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误 #当使用的参数或操作数类型错误时，出现错误 #! 当数字被零（ 0）除时，出现错误 #当 未加载宏或定义名称，出现错误 #N/A 当数值对函数或公式不可用时，出现错误 #当单元格引用无效时，出现错误 #公式或函数中使用无效数字值时，出现错误 #当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点 时，出现错误。 34页 函数与公式 1、文本 /数字 /日期 /财务类函数讲解 2、实战案例综合应用 35页 文本处理函数 用途：连接“ &”前后的字符、字段 E =1 E 列 思考：如何把零件号后面的 1、连接符 & 36页 文本处理函数 2、大小写字母转换 有大写字母转为小写字母 所有小写字母转为大写字母 字符串首字母转为大写字母 =“I =“i =“i =“I =“I 爱 =“I 爱 37页 文本处理函数 3、全角半角函数 半角字母转为全角字母 全角字母转为半角字母 = =“” =“我爱 ” ) =“我爱 38页 文本处理函数 4、生成 5)=A 6)=B 7)=C 思考：如何快速填充 A”)=65 7)=a 8)=b 9)=c 39页 文本处理函数 5、提取字符 注意： 个参数 ，而 个参数 身份证号 地区 出生日期 性别 =) =,6) =) 320102781001492 320102 781001 2 员工代码 社保号 姓名 =0) =1,3) 0002337084李虎 0002337084 李虎 0000656246丁世明 0000656246 丁世明 为什么是 3而不是 2？ 为什么不用 40页 数字计算函数 1、计算余数 ,2)=1 5,4)=3 1,7)=0 应用：身份证号判断性别？如何判断数字的奇偶性？ 思考：除 2的余数有多少个？除 3呢？ 41页 数字计算函数 2、取整函数 返回不大于 = 返回不大于 4 = 返回截去小数部分后的整数 3 = 返回截去小数部分后的整数 -3 42页 数字计算函数 3、四舍五入 值 公式 显示结果 2,0) 123 3,2) 4,1200 区别 43页 数字计算函数 3、向上取整 下取整 值 式 显示结果 公式 显示结果 3,0) 123 =3,0) 124 3,2) 3,2) 3,1200 =3,1300 44页 日期与时间函数 1、 4个基础日期函数 特殊函数： ，读取计算机设置的今日日期，无参数 日期 年 月 日 =2009009 6 16 年 月 日 日期 =1,1) 2009 6 16 2009 45页 日期与时间函数 实战练习： 快速计算退休日期（假设：男 60退休、女 55退休） 姓名 性别 出生日期 部门 退休日期 艾笑贻 女 1955合部 安文博 男 1961合部 白露 女 1979合部 谢子坚 男 1973务部 卜娟芳 女 1978务部 蔡星媛 女 1959务部 仓晟义 男 1978息部 曹昱廷 男 1965息部 46页 日期与时间函数 2、 用途：计算两个日期之间的天数、月数或年数。 注： 此函数在 结构： 函数返回值 y 时间段中的整年数 m 时间段中的整月数 d 时间段中的天数 忽略日期中的年和月，计算天数差 忽略日期中的年和日，计算月数差 忽略日期中的年，计算天数差 47页 日期与时间函数 2、 续 ) 开始日期 结束日期 公式 显示结果 20000092,y) 9 20000093,y) 8 20080094,m) 12 20080095,m) 11 20080096,d) 366 20080097,d) 364 20080098, 1 20080099, 30 200800910, 3 200800911, 11 200800912, 1 200800913, 364 48页 财务函数 概念解释： 函数 含义 公式 率 固定值 期 值 FV( 值 PV( 付金额 初 期初为 1，期末为 0。如省略，则为 0 49页 财务函数 案例 1： 分 10个月付清年利率为 8%的 10000元贷款，计算月支付金额。 = =8%/12， 10， 10000） = 例 2： 需以年利率 5%存款 15年，达到存款总额 1,500,000，计算每月存款金额。 = =5%/12， 15， 0， 1500000） = 50页 财务函数 案例 3： 某人将 10000元投入一项事业，年回报率 6%，计算 3年后的累积金额。 = =6%， 3， 0， = 例 4： 两年后需要大笔支出，计划从现在起，每月初存入 2000元，年利率 按月计息（ 12），计算两年后帐户金额。 = =12， 24， 0， 1） = 51页 财务函数 案例 5： 某人拟在 5年后获得 10000元，投资报酬率 10%，计算现在应该投入的金额。 = =10%， 5， 0， 10000） = 例 6： 某人余购买一项养老保险，购买成本为 60000元，可在 20年内每月回报 500元，投资报酬率 8%，计算是否合适。 = =8%/12， 20*12， 500） = 不合适 52页 函数与公式 1、文本 /数字 /日期 /财务类函数讲解 2、案例综合应用 53页 1、 相对单元格 与 绝对单元格 公式所在单元格与公式所引用单元格的 位置关系 称为单元格引用的相对性。 特征 $A$1 对引用，向右向下复制公式不改变引用关系 A$1 行绝对列相对混合引用，向下复制公式不改变引用关系 $*相对列绝对混合引用，向右复制公式不改变引用关系 *C* 相对引用，向右向下复制公式均改变引用关系 练习： 制作九九乘法表 54页 2、工作表的引用 1、引用其他工作表的数据 =工作表名称 ！ 目标单元格 例： =1 2、引用已打开的工作簿的数据 =工作簿名称 工作表名称 ！ 目标单元格 例： =A$1 3、引用未打开的工作簿的数据 =文件路径 工作簿名称 工作表名称 ！ 目标单元格 例： =D:$A$1 练习： 将多张工作表的数据汇总到一张工作表 55页 3、 用途：按指定的条件计算满足条件选项 =22,全勤 , 序号 姓名 出勤 备注 1 张三 20 20 2 李四 23 全勤 3 王五 21 21 4 贾六 22 全勤 =22, 全勤 , 57页 4、 1. 用途：按列 /行的方式查询目标值。 语法格式： =查找值，范围，相对列，假） 用途：模糊及区间查询 语法格式： =查找值，对应值区域，目标值区域） ！ 58页 5、 按照一定的条件进行统计计算。 语法： =件区域，条件） =件区域，条件，值区域） 59页 6、 返回相应的数组或乘积之和。 语法： =(* ( * ） 60页 7、初级数组公式 数组公式 是用于建立可以产生多个结果或对可以存放在行或列中的一组参数进行运算的单个公式。 特点： 可以执行多重运算，返回一组数据结果。 输入公式后，同时按 【 ，公式就会成为数组公式。 注：数组公式的外面会自动加 予以区分。 61页 综合应用 案例分析 62页 提纲 一、认识 二、数据处理 三、函数与公式 四、高级图表 63页 面积图 点）图 条形图 曲面图 柱形图 气泡图 折线图 股价图 饼图 圆柱图 圆环图 圆锥图 雷达图 棱锥图 共有 73个子类型的图表类型。 64页 1、柱形图 020406080部门一 部门二 部门三 部门四* * 公司一、二季度销售情况一季度 二季度 65页 1形图的变形 01020304050607080部门一 部门二 部门三 部门四一季度 二季度 平均020406080100120140部门一 部门二 部门三 部门四一季度 二季度 66页 高级图表 薪酬云梯图 67页 2、条形图 0 20 40 60 80部门一部门二部门三部门四二季度一季度 68页 高级图表 人口金字塔 2 0 0 8 年* * 公司人员年龄结构分析800 700 600 500 400 300 200 100 0 100 200 300 400 500 600 700 80025岁以下25 69页 高级图表 进程甘特图 2 0 0 8 - 1 0 - 3 1 2 0 0 8 - 1 1 - 9 2 0 0 8 - 1 1 - 1 8 2 0 0 8 - 1 1 - 2 7 2 0 0 8 - 1 2 - 6 2 0 0 8 - 1 2 - 1 5 2 0 0 8 - 1 2 - 2 4第一阶段第二阶段第三阶段第四阶段第五阶段第六阶段第七阶段第八阶段 70页 3、饼图 初中及以下高中及技校专科本科及以上合计 71页 3图的变形 东北, 2 0 0 0西北, 1 8 0 0华北, 3 2 0 0华南, 3 6 0 0西南, 2 6 0 0江苏, 1 7 0 0浙江, 1 4 0 0安徽, 8 0 0华东, 3 9 0 0 72页 高级图表 18%30%7%45%39%39%7%15%双饼图 73页 4、折线图 最大, 5 6 0最小, 4 001002003004005006001月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月数量