Office高级操作PPT课件.ppt

Excel部分 电子与信息工程学院徐莉2012 10 1 课程考核 课程考核成绩由2部分构成 行程性考核成绩 60 考勤 40 实验成绩 60 期末考试 40 2 Excel常用函数 3 单元格引用方式相对引用 绝对引用 混合引用单元格引用的分类一维引用 A1 A10 A2 G2二维引用 A1 C5二维引用 Sheet1 Sheet3 A1单元格引用的分类相对引用 A1 B5绝对引用 A 5 C 2混合引用 A2 D 1 基础概念 引用 4 Excel中的常用函数 略 sum average max min count mode If 5 统计函数COUNTIF求满足给定条件的数据个数格式 COUNTIF range criteria COUNTIF B2 B10 A COUNTIF C C 10 6 条件格式 查找不及格的分数 并将其字体设置为加粗 倾斜 红色 选中B14 H21区域 选择 格式 条件格式 命令 打开 条件格式 对话框 做如图设置 7 条件格式 查找员工信息表中不存在的员工信息 并将他们的信息设置为红色 加粗字体 选中A2 D7区域 选择 格式 条件格式 命令 打开 条件格式 对话框 做如图设置 8 条件求和SUMIF求满足条件的单元格求和格式 SUMIF range criteria sum range Range 对满足条件的单元格求和Criteria 在指定范围内检索符合条件的单元格Sum range 要进行计算的单元格区域 9 sumif函数 SUMIF A2 A10 10月 C2 C10 10 数学函数INT求小数数值的整数部分 将数值向下取整为最接近的整数 ROUND求按指定位数舍入后的数字 11 时间函数TODAY 返回日期格式的当前日期 YEAR 返回日期的年份值 MONTH 返回月份值 DAY 返回一个月中的第几天的数值 12 文本函数RIGHT text num chars 从一个文本字符串的最后一个字符开始返回指定个数的字符 VALUE text 将一个代表数值的文本字符串转换成数值 MID text start num num chars 从文本字符串中指定的起始位置起返回指定长度的字符 CONCATENATE text1 text2 将多个文本字符串合并成一个 13 14 15 17位为顺序码 是县 区级政府所辖派出所的分配码 每个派出所分配码为10个连续号码 例如 000 009 或 060 069 其中单数为男性分配码 双数为女性分配码 如遇同年同月同日有两人以上时顺延第二 第三 第四 第五个分配码 如 007的就是个男生而且和他同年月日生的男生至少有两个他们的后四位是001 和003 15 16 查找函数VLOOKUP返回一个数值在一组数值中的排位格式 VLOOKUP lookup value table array col index num range lookup lookup value 用数值或数值所在的单元格指定要查找的数值 table array 指定查找的范围 col index num 为table array中待返回的匹配值的列序号 range lookup 用True或False指定查找方法 如果为True或省略 则返回近似匹配值 如果为False 则返回精确匹配值 17 Vlookup函数 VLOOKUP A3 员工基本信息表 1 65536 2 0 18 合并计算及Vlookup函数 19 问题说明 假设公司每个季度进行考核评比 计算评比分数 在年终评比时作汇总工作 依评比分数作年终考核排名 依评比等级发给年终奖金 礼品并调整工资作为奖惩 我们使用Excel进行考勤核算和奖惩核算 20 工作表介绍 员工资料表 工作表 该工作表存放员工的基本信息 21 工作表介绍 缺勤记录表 工作表 该工作表记录每个员工一季度至四季度中缺勤的天数 以便年终评比参考使用 22 工作表介绍 建立各个季度的考勤核算表 我们预设公司的出勤总天数为70天 若请假或旷工一天就扣1 当缺勤超过5天时将自动显示 警告 两个字 若缺勤天数超过50天时 将自动显示 开除 两个字 否则就显示抵扣后的出勤天数 季度考核成绩由出勤天数 占20 和主管评分 占80 两部分组成 23 工作表介绍 年度考勤核算表 24 工作表介绍 评比奖励 工作表 评比奖励说明 若员工的评比级别为 优 年终奖金 基本工资 1 1 2 年终礼品 香港旅游 调整工资 基本工资 1 5 其余依次类推 25 第一步 输入查询姓名的公式在 一季度核算 工作表中的B3单元格输入公式 VLOOKUP A3 员工工资表 A 3 F 16 2 公式意义 以员工编号字段为关键字在 员工资料表 中查询 返回查找到的 员工资料表 中的该行的第2列的值 即 姓名 字段 26 第二步 输入计算缺勤天数的公式在 一季度核算 的C3单元格输入公式 VLOOKUP A3 缺勤记录表 A 3 F 16 3 公式意义 以员工编号字段为关键字在 缺勤记录表 中查询 返回查找到的 缺勤记录表 中的该行的第3列的值 即 缺勤天数 字段 27 第三步 输入计算出勤天数的公式在 一季度核算 的D3单元格输入公式 IF C3 50 开除 警告 公式意义 当缺勤天数不足5天时 显示扣抵后的出勤天数 当缺勤天数超过5天时 自动显示 警告 当缺勤天数超过50天时 自动显示 开除 28 第四步 复制以上公式到其他员工记录在 一季度核算 中 选中B3 D3单元格区域 使用填充柄向下复制到B16 D16 29 第五步 计算考核成绩在 一季度核算 中 季度考核成绩由出勤天数 占20 和主管评分 占80 两部分组成 1 F3单元格公式 70 C3 0 2 E3 0 8 2 使用填充柄将F3中的公式复制到该列其下单元格 30 第六步 用类似的步骤建立第二 三 四季度的 季度考勤核算表 31 第七步 输入查询姓名的公式在 年度考核表 工作表中的B3单元格输入公式 VLOOKUP A3 员工工资表 A 3 F 16 2 公式意义 以员工编号字段为关键字在 员工资料表 中查询 返回查找到的 员工资料表 中的该行的第2列的值 即 姓名 字段 32 第八步 输入查询基本工资的公式在 年度考核表 工作表中的C3单元格输入公式 VLOOKUP A3 员工工资表 A 3 F 16 5 公式意义 以员工编号字段为关键字在 员工资料表 中查询 返回查找到的 员工资料表 中的该行的第5列的值 即 基本工资 字段 选取B3 C3单元格区域 使用填充柄复制B3和C3中的公式到其下单元格 33 第九步 计算四个季度的平均考核分数在 年度考核表 工作表中 使用Excel的 合并计算 功能 计算四个季度的季度成绩平均值 1 选择D3单元格 选择菜单命令 数据 合并计算 打开的 合并计算 对话框 2 在 函数 下拉列表框中选择 平均值 函数 3 单击 引用位置 右边的折叠对话框按钮 选取 一季度核算 工作表中的F3 F16单元格区域后 单击折叠对话框按钮返回 合并计算 对话框 4 单击 添加 按钮 将 一季度核算 F 3 F 16 单元格区域添加到计算区域中 一季度核算 F 3 F 16 即显示在 所有引用区域 列表框中 5 依步骤 3 和步骤 4 将二 三 四季度的数据加入到计算区域中 34 35 第十步 建立成绩排名在 年度考核表 工作表中 计算每个员工的成绩排名 我们使用RANK 函数 1 E3单元格公式 RANK D3 D 3 D 16 2 使用填充柄将E3中的公式复制到其下单元格 36 第十一步 计算评比等级在 年度考核表 工作表中 假设年终以季平均成绩将员工分为优 良 中 差4个等级 评比的结果将影响年终奖金的多寡 年终礼品的好坏以及工资结构的调整 等级评比标准如下 考勤分数评比等级90分 100分优85分 90分良80分 84分中0分 79分差我们使用条件判断函数IF 计算评比等级 1 F3单元格公式 IF D3 90 优 IF D3 85 良 IF D3 80 中 差 2 使用填充柄将F3中的公式复制到其下单元格 37 第十二步 计算年终奖金评比奖励说明 若员工的评比级别为 优 年终奖金 基本工资 1 1 2 年终礼品 香港旅游 调整工资 基本工资 1 5 其余依次类推 在 年度考核表 工作表中 G3单元格输入公式 C3 1 VLOOKUP F3 评比奖励 A 2 D 5 2 0 38 第十三步 计算年终礼品在 年度考核表 工作表中 H3单元格输入公式 VLOOKUP F3 评比奖励 A 2 D 5 3 0 39 第十四步 计算调整工资在在 年度考核表 工作表中 I3单元格输入公式 C3 1 VLOOKUP F3 评比奖励 A 2 D 5 4 0 使用填充柄将G3 H3 I3中的公式复制到其下单元格 40 调整分数线 41 问题说明 某教师统计了25名学生的考试分数 需要根据总分来判定合格的人数 要求是合格人数达到12 求合格分数线 42 第一步 假定合格分数首先假定合格分数为120来计算学生的等级 使用IF函数 大于120即为合格 否则为不合格 43 第二步 计算合格人数在H3单元格使用countif函数计算合格的人数 44 第三步 使用单变量求解选定H3单元格 选择 工具 单变量求解 设置其目标值为12 可变单元格为H2 单击确定即可求出当合格人数为12时的分数线 45 工厂产量分析 46 问题说明 某工厂需要制定全年的生产计划 假设每个季度保证稳定的15 的增长率 已知一季度可以完成400单位的产量 现在假设你是该工厂的数据分析员 要求你对全年的计划做一个分析 47 第一步 计算原定计划的全年产量根据已有的条件 用公式在B3 B5单元格区域分别生成二 三 四季度的产量 并在B6单元格使用求和公式得出全年的产量 48 第二步 生成图表对A1 B6单元格区域内的数据生成簇状柱形图表 系列产生在列 不显示图例 显示 值 的数据标签 作为其中的对象插入到工作表中 放置在A9 F21单元格区域内 49 第三步 工作表重命名将当前的工作表命名为 改变一季度产量 复制 改变一季度产量 工作表 并重命名为 改变季度增长率 50 第四步 单变量求解1在 改变一季度产量 工作表中 制定全年计划的分析人员希望知道如果要将全年的产量提高到2400 那么在季度增长率保持不变的情况下 相应的一季度必须要完成多少的产量才能达到这个全年的目标 使用单变量求解来完成 直接在图表上提高全年的产量即可弹出 单变量求解 的对话框 设置可变单元格如图所示 51 第四步 单变量求解2在 改变季度增长率 工作表中 制定全年计划的分析人员希望知道如果要将全年的产量提高到2400 那么在一季度产量保持不变的情况下 相应的季度增长率必须提高到多少才能达到这个全年的目标 使用单变量求解来完成 直接在图表上提高全年的产量即可弹出 单变量求解 的对话框 设置可变单元格如图所示 52 身高预测 53 问题描述 某个城市对中小学生中不同年龄段的男性的身高进行了抽样调查统计 得到了下面这个表格的结果 现在想要根据这个表格来预测一下六岁年龄段的平均身高 54 选择合适的趋势线类型 可用于对图表中的数据图像图形进行拟合 并且可以根据趋势线进行数据的预测分析 是提升趋势线的拟合程度 提高预测分析的准确性的关键 55 EXCEL中提供的趋势线类型 线性对数多项式乘幂指数移动平均 56 第一步 创建图表打开 某城市男生年龄身高对照表 xls 选定A1 B14单元格区域 插入 XY散点图 中的 平滑线散点图 设定系列产生在列 设置图表无标题 不显示图例 作为其中的对象插入到工作表的A17 H35单元格区域内 57 第二步 修改图表格式设置图表区的背景为黄色 圆角 绘图区的背景色为天蓝色 Y轴的刻度最小值为100 主要刻度单位为10 X轴的刻度最小值为6 主要刻度单位为2 58 第三步 添加趋势线1在图表中选择曲线 单击鼠标右键 选择 添加趋势线 命令 打开 添加趋势线 对话框 在 类型 选项卡中选择 线性 59 第三步 添加趋势线2在 添加趋势线 对话框 在 选项 选项卡中勾选 显示公式 和 显示R平方值 复选框 单击确定 60 第四步 修改图表将绘图区高度调小一些 将趋势线的公式拖动至图表的正上方 61 说明 R平方值又称为趋势线的决定技术 它反映了趋势线的估计值与对应的实际值之间的拟合程度 它的数值范围在0 1之间 当R的平方值等于1或接近于1的时候 趋势线对于实际数据的拟合程度最高 此时的趋势线最可靠 根据此趋势线拟合所得到的数据也最准确 因此 要得到最准确的结果 就要在趋势线的类型中选择R平方值最接近于1的类型 使得趋势线对实际数据的拟合程度达到最高 62 第五步 选择合适趋势线在B37单元格输入 线性 C37单元格输入 R平方值 先在下方单元格记录线性类型的R平方值 依次在下方的单元格记录各种类型对应的R平方值 从中选择R平方值最接近于1的类型的趋势线 注意 在趋势线上单击右键 即可打开 趋势线格式 对话框 在 类型 选项卡中即可修改 63 第六步 预测身高在A15单元格输入 6 在B15单元格输入公式 54 1 LN A15 14 635 即可预测得到6岁男孩的身高 64 线性趋势线 适合于数据的增长或降低速率比较稳定的数据情况 数据点在图表上表现为近似于一条直线 65 对数趋势线 66 多项式趋势线 数据在图表上表现为包含一个或多个波峰和波谷的曲线 67 乘幂趋势线 如果数据中含有零或负数值 就不能创建乘幂趋势线 68 指数趋势线 如果数据值中含有零或负值 就不能使用指数趋势线 69 移动平均 移动平均趋势线平滑处理了数据中的微小波动 从而更清晰地显示了图案和趋势 移动平均使用特定数目的数据点 由 周期 选项设置 取其平均值 然后将该平均值作为趋势线中的一个点 例如 如果 周期 设置为2 那么 头两个数据点的平均值就是移动平均趋势线中的第一个点 第二个和第三个数据点的平均值就是趋势线的第二个点 依此类推 70 企业贷款 71 单变量模拟运算问题说明 我们准备贷一笔款 比如10000元 贷款期限为10年 年利率为8 如果贷款利率发生了变化 按月偿还时 每月偿还的金额是多少呢 72 计算固定利率下的每月付款额 Excel有专门的计算函数PMT PMT是 基于固定利率及等额分期付款方式 返回投资或贷款的每期付款额 的函数 格式 PMT Rate Nper Pv Fv Type Rate表示贷款利率Nper表示该项贷款的付款总次数Pv表示现值 或一系列未来付款的当前值的累积和 也称为本金Fv表示未来值 或在最后一次付款后希望得到的现金余额 如果省略Fv 则假设其值为零 也就是一笔贷款的未来值为零Type为0或省略 表示各期的付款时间是在期末 为1表示付款时间在期初 73 第一步 输入公式在D2单元格中输入公式 PMT B4 12 B5 B3 74 第二步 选择包含公式和替换值的单元格区域C2 D8 75 第三步 模拟运算单击 数据 模拟运算表 打开 模拟运算表 对话框 设置