Excel高级技能应用.ppt

Excel的高级应用2 2 一数据透视表的制作 数据透视表 PivotTable 快速从繁锁信息中挖掘和整合信息的工具 3 例1 请按学院统计各类不同职称的论文篇数的平均值并内嵌于该工作表中 要求表从上到下依次为各个学院 同一学院内由上到下依次为各类职称 方法一 排序后 分类汇总 方法二 数据透视表 一数据透视表的制作 4 一数据透视表制作三步骤 行字段 若一个字段 要求其有多少个不同的值就要显示在多少行上 则被定义为行字段 列字段 若一个字段 要求其有多少个不同的值就要显示在多少列上 则被定义为行字段 5 例2 一数据透视表的制作 每个销售员销售了多少每种商品销售了多少每个销售员销售每种商品各多少 各地区销售了多少 6 二在数据透视表中添加计算项 数据透视表创建完成后 用户无法更改或者移动数据透视表中的任何区域 也不能在数据透视表中插入单元格或是添加公式进行自定义的计算 如果需要在数据透视表中创建自定义的计算项目 必须使用 添加计算字段 或 添加计算项 功能 计算字段 通过对表中现有的字段执行计算后得到的新的字段 计算项 在已有的字段中插入新的项 通过对该字段现有的其他项执行计算后得到的 7 一图表制作的必经之路 WHAT WHICH HOW 8 二图表制作四步曲 HOW 对需要处理的数据进行选定 选择 插入 图表 弹出图表向导对话框 选择自己所需要的数据类型 一 二 三 四 确认数据后 点击确定完成 9 三制作注意事项 慎重选择图表类型 绘图区比例的设置 数值轴刻度设置 10 一 慎重选择图表类型 饼形图 条形图 柱形图 折线图 散点图 比较类型 比例 排序 时间序列 频率分布 关联性 11 不同类型的图表 用途也不同 DEMO 12 二 绘图区比例的设置 图表大小标题轴图例 绘图区常见纵横比例 影响比例的主要因素 13 三 数值轴刻度 修改刻度表将使图表得到重大改变 科学合理的设置刻度 14 四玩转图表数据系列 转 15 常用函数的应用 16 一快速匹配公式 格式 VLOOKUP lookup value table array col index num range lookup VLOOKUP 快速匹配公式 lookup value需要在数组第一列中查找的数值 table array需要在其中查找数据的数据表 col index numtable array中待返回的匹配值的列序号 range lookup为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 17 二三大条件函数的运用 格式 COUNTIF range criteria COUNTIF 条件计数函数 Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 18 二三大条件函数的运用 格式 SUMIF range criteria sum range SUMIF 条件求和函数 Range为用于条件判断的单元格区域 Criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 Sum range是需要求和的实际单元格 19 格式 IF logical test value if true value if false 即 IF 条件 真 假 IF 条件函数 二三大条件函数的运用 logical test表示计算结果为TRUE或FALSE的任意值或表达式 Value if truelogical test为TRUE时返回的值 Value if falselogical test为FALSE时返回的值 20 三计算加权值 格式 SUMPRODUCT array1 array2 array3 SUMPRODUCT 在给定的几组数组中 将数组间对应的元素相乘 并返回乘积之和 array1 array2 array3 为2到30个数组 其相应元素需要进行相乘并求和 21 四数值的四舍五入 格式 CELING number num digits 格式 FLOOR number num digits 格式 ROUND number num digits CELING 向上舍入 FLOOR 向下舍入 ROUND 四舍五入 22 五提取身份证号码中的信息 一 生日日期的提取 18位身份证号码 第7至10位为出生年份 第11 12位为出生月份 第13 14位为出生日期 15位身份证号码 第7 8位为出生年份 第9 10位为出生月份 第11 12位为出生日期 23 二 性别的提取 18位身份证号码 第15至17位为顺序号 并能够判断性别