Excel进阶课程(函数).ppt

Excel函数在商业中的应用,公式函数必备知识,案例：计算订单销售额及利润,建立公式,相对引用 公式走哪我跟哪，跟公式的相对位置不变 如： 绝对引用 给钱我就不走了，位置绝对不移动 如：,案例：一个公式完成折扣计算表,混合引用与数组,混合引用： =C$4*$B5 行列都不锁定:A1 行列都锁定:$A$1 列活动行锁定:A$1 列锁定行活动:$A1 数组的使用 =C4:E4*B5:B7 Shift+ctrl+enter,公式中常见错误判断,公式中常见错误判断,商业函数的使用技能,函数语法规则,= 函数名（参数） 某些函数的参数可以省略 参数与参数之间用逗号分开“，”,常用的基本函数,sum (范围) 求和 average (范围) 平均数 max (范围) 最大值 min (范围) 最小值 count (范围) 计数 计数字 counta (范围) 计数 非空单元格,案例：sum另类用法,一键填充求和区域（蓝色区域）,案例：销售订单表,计算订单个数,逻辑函数,if （逻辑判断式，真值，假值）,案例：成绩表,判断考试等级： =210 A 级 210 B级 是否补考：三门成绩必须全部大于分才不需补考,的嵌套,=20 A 级 210220 B级 210 C级 If的嵌套不能超过7层,查找函数,vlookup （查询对象，数据源，显示序列，匹配参数） 匹配参数：false精确查找=0(推荐) true模糊查找=1 显示序列：需要显示的数据位于数据源中第几列,必须是数字,Vlookup数据源要求,数据源： 标准的二维数据 查询对象必须位于数据源第一列 显示对象必须位于数据源当中 必须是一个连续区域,案例：同一张工作表中的查询,自动显示所选人员的总成绩,案例：不同工作表之间的查询,案例：数据对比,查找两张表中ID号相同的数据,日期函数,year (日期) month (日期) day (日期) date （年,月,日） Today() 当前日期 now()当前时间 Hour、minute、second、time datedif (起始日期，结束日期，返回类型),案例：将产品成交日期进行处理,成交日期的年月日分开 计算成交年数，并可每年自动更新,文本函数,left (文本对象，截取位数) right (文本对象，截取位数) mid(文本对象，开始位数，截取位数),案例：整理产品型号,将品牌和型号分开两列显示 使用分列功能,Excel进阶课程之图表,常见图表的制作技巧,认识图表商务模型,创建图表的方法 一键生成法 按部就班法 用图表传递信息的类型 常用图表类型及选择技巧,图表类型的选择,柱形图：强调的是数据区中所有的数据 条形图：与柱形图强调要点相同，建议使用在横轴为时间线时 折线图：强调的是数据趋势 饼图：强调的是部分对整体的贡献 XY散点图：强调X轴与Y轴之间的相对关系,图表的基本修改方式,移动和调整图表 更改图表类型 移动图表元素 删除、格式化图表元素,修改原则： 看得见的部分双击 看不见的单击右键,图表工具栏的使用,图表对象：用于选择需要在图表中选定修改的图表对象 图表区格式：调出在图表中选定的图表对象的格式属性 图表类型：用于修改整个图表或者图表中某一数据系列的图表类型,图表工具栏的使用,图例：是否显示图例 数据表：属否显示原始数据 按行/按列：图表的数据显示以行或列标题为分类 顺时针/逆时针排列：文字的排列方式,制作柱形图和折线图的混合图表,例题：根据下列数据，制作“产品销售展示图”,案例：产品销售展示图,更改图表类型,设置次坐标轴（双坐标轴）,双击整条折线，打开“数据系列格式”对话框,修改坐标轴单位,双击次坐标轴数据，打开“坐标轴格式”对话框,在饼图中显示类别名称和百分比,练习：展示三月份的三种产品的市场份额，并明确标示份额百分比。,图表的美化技巧,根据下表数据，制作“奥运金牌对比图”,案例：奥运金牌对比图,选择图表类型,更改绘图区格式,双击绘图区，将边框和填充色都设置为“无”,更改图表区格式,双击图表区，边框设为“无”，区域进行“填充效果”设置,填充效果,删除坐标轴、网格线,单击网格线、横坐标轴，删除Delete 双击纵坐标轴，如图所示，选择“无”,修改数据系列格式,双击数据系列，在“图案”中选择橙色，边框选“无”,增加数据系列的宽度,双击数据系列，在“选项”中减少“分类间距”,加宽坐标轴文字与图形间距,双击坐标轴，在“对齐”选项卡中增加“偏移量”数值,后期处理,相应位置添加图片和文本框，修改格式,Excel商业数据处理与分析,数据清单的应用与筛选,预备知识,条件格式 在设定单元格的格式时，可以使用条件判断的方式，使单元格自动进行格式更改。 菜单：格式条件格式 有效性 用于限定可以在单元格中录入的对象的范围 菜单：数据有效性,案例：支出证明单的条件格式,金额出填写不同范围的数据，字体颜色不同,案例：支出证明单有效性限定,金额必须填写01000元之间的数字 鼠标放在填写金额的单元格内，自动出现提示信息 填写错误将弹出“出错警告”,案例：企业员工档案数据清单,为简化录入，请在“部门”列中制作下拉菜单 Alt+ 数据有效性,案例：突出显示高工资人员,工资金额在5500元以上的人员名称自动变红色,案例：限制销售日期的有效性,销售业绩表销售日期，只能填写2008-4-1到2008-6-1之间的数据,数据的排序,案例：拼音排序,员工档案表按姓名的汉语拼音顺序排序,案例：笔划排序,员工档案表按姓名的笔划排序（中文版）,案例：自定义排序（一）,企业员工档案数据清单表按照学位高低排序,案例：自定义排序（二）,工具选项自定义序列 数据排序选项自定义序列,利用筛选快速查找数据,自动筛选,自定义:等于、不等于、包含 “与”“或”的关系 前10个 全部显示,Subtotal函数介绍,subtotal(运算类型，范围),数据透视分析,数据源的要求,不能合并单元格 第一行必须是字段名，并且不能为空。 数据源数据可以为空，但不能空整行整列。 同一列的数据必须是同种类型的数据。 删除所有汇总数据，删除分类汇总。,创建员工档案数据透视,建立数据透视表 设置数据透视表格式 多角度查看数据透视表 报表分成两大类： 行字段、列字段、页字段：放置分类项 数据项：放置汇总数据,企业销售业绩的多指标分析,设置排序 分类汇总数据 添加新字段 隐藏部分字段 更新数据区域 组合分析数据,分类汇总数据,每个城市，各产品的销售总额，二维表查看,隐藏部分字段,刷新数据,更新数据区域,当数据源范围发生变化后，可进入向导第二步重新选择数据源,数据透视图的编辑,查看每种材料，各型号的采购数量汇总图 （1）先制作透视表 （2）单击工具栏中的,部门采购情况的透视图分析,数据透视图的编辑 透视图类型的切换 饼图块的拆分、修改记录 透视图的静态转化,数据透视图的编辑,查看每种材料，各型号的采购数量汇总图 （1）再制作透视图：,透视图类型的切换,右键-图表类型-饼图,饼图块的拆分、修改记录,双击-添加数据标志-类别名称和百分比,透视图的静态转化,删除数据透视表 隐藏字段按钮,多人合作文档,专业报告制作美化