杰出的Excel在企业中的应用课件.ppt

1、Microsoft Office Specialist 杰出的杰出的Excel在企业在企业中的应用中的应用 讲师讲师:郑学谦郑学谦EXCEL的数据处理的数据处理&数据分析数据分析繁琐的数据收集繁琐的数据收集将复杂的数据整理成有效数据各种表格数据汇总各种表格数据汇总将数据数据进行各种方式汇总个性需求多的数据分析个性需求多的数据分析根据需求使用分析工具处理数据准确的图表说话准确的图表说话将各种内容的数据展示出来什么是数据统计分析？什么是数据统计分析？p产能比分析、投放比分析、利耗比分析！产能比分析、投放比分析、利耗比分析！p分类汇总统计（不同汇总方式，不同分类方式）分类汇总统计（不同汇总方式，不同

2、分类方式）p统计汇总分析的目的？统计汇总分析的目的？p同比、环比、百分比同比、环比、百分比p周期性分析、时效性分析、因素性分析周期性分析、时效性分析、因素性分析Excel最强大的数据分析工具最强大的数据分析工具数据透视表数据透视表数据透视表的基础功能数据透视表的基础功能p实现高效的数据分类汇总实现高效的数据分类汇总p拖拽字段拖拽字段p需求性组合字段需求性组合字段p需求性显示状态需求性显示状态p其它辅助功能其它辅助功能p排序排序p筛选筛选p显示格式显示格式p汇总方式汇总方式案例案例1 周期性分析周期性分析p数据要求：数据要求：p日期字段、时间字段日期字段、时间字段p销售周期的区间不同（分类字段不

3、同）销售周期的区间不同（分类字段不同）p年、季度、月、日、小时年、季度、月、日、小时p多字段叠加多字段叠加p统计数据的显示状态根据需求变换统计数据的显示状态根据需求变换案例案例2 数值区间分析数值区间分析p数据要求：数据要求：p数值字段数值字段p数值字段的分组区间不同（分类字段的不同）数值字段的分组区间不同（分类字段的不同）p价格区间、销量区间价格区间、销量区间p统计数据的显示状态根据需求变换统计数据的显示状态根据需求变换案例案例3 数据属性分类数据属性分类p数据要求：数据要求：p文字字段组合文字字段组合p根据数据的属性实现分类分组分析（文字分类）根据数据的属性实现分类分组分析（文字分类）p产

4、品分类、内容分类、属性分类产品分类、内容分类、属性分类p统计数据的显示状态根据需求变换统计数据的显示状态根据需求变换总结：数据分析的思路总结：数据分析的思路p按相关因素进行任意字段组合按相关因素进行任意字段组合p按相关因素进行字段数据组合按相关因素进行字段数据组合p数据透视表的拖拽思路数据透视表的拖拽思路p数据属性字段数据属性字段页、行、列区域页、行、列区域p数值属性字段数值属性字段数据区域数据区域分析中的其它需求分析中的其它需求p增加字段增加字段p增加计算字段增加计算字段p组合字段（虚拟字段）组合字段（虚拟字段）p分类排列（表格排列方式）分类排列（表格排列方式）p字段显示格式设置（货币、百分

5、比、自定义）字段显示格式设置（货币、百分比、自定义）p生成数据透视图（生成图表）生成数据透视图（生成图表）数据透视表对数据透视表对源源表格格式的要求表格格式的要求p不能有合并单元格不能有合并单元格p单条记录成行，不能有空行单条记录成行，不能有空行p数值是数值，文本是文本数值是数值，文本是文本p数据有效性问题数据有效性问题p数据差错的工具数据差错的工具-圈释无效数据圈释无效数据p基于数据有效性基于数据有效性数据的有效性的使用数据的有效性的使用p保证数据的有效的规则设置保证数据的有效的规则设置p数据格式要求：数据格式要求：p数值（限制范围）数值（限制范围）p文本（长度）文本（长度）p日期（限制范围

6、）日期（限制范围）p时间（限制范围）时间（限制范围）p序列（下拉菜单）序列（下拉菜单）p多级菜单（名称的使用）多级菜单（名称的使用）表格区域的名称表格区域的名称p定义名称（名称框）定义名称（名称框）p引用数据（插入，名称，粘贴）引用数据（插入，名称，粘贴）p指定指定p公式中应用名称公式中应用名称p定义：定义：Ctrl+F3p粘贴：粘贴：F3p名称的理解：全局公式名称的理解：全局公式用函数管理你的数据用函数管理你的数据数据太多，太复杂，怎么整理？数据太多，太复杂，怎么整理？查询函数查询函数p查询查询p简单查询数据简单查询数据p区间内查询区间内查询p数据的对比数据的对比p使用查询函数（使用查询函数

7、（lookup）查找引用函数查找引用函数pVLookup(查找项，被查找区域，返回列，查找项，被查找区域，返回列，false精确匹配精确匹配/true模糊匹配模糊匹配)p索引项目索引项目p对比对比pp个人所得税计算个人所得税计算pHLookup()案例：案例：制作工资数据表制作工资数据表p使用使用vlookup函数查询姓名和部门函数查询姓名和部门p使用名称提取数据使用名称提取数据p使用使用vlookup函数计算函数计算应扣所得税额应扣所得税额p完成工资条、保护工作表完成工资条、保护工作表员员工工编编号号员员工工姓姓名名所所属属部部门门最最后后一一次次调调薪薪时时间间调调整整后后基基础础工工资资

8、调调整整后后岗岗位位工工资资调调整整后后工工龄龄工工资资调调整整后后总总基基本本工工资资银银行行帐帐号号0001 刘聪销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-0010002 张永红销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-0020003 张华销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-0030004 黄毅销售部2005-6-1￥1,500.00￥

9、800.00￥200.00￥2,500.000000-1234-5678-9123-0040005 姜浪彬销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-0050006 李新销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-0060007 姚新民销售部2005-6-1￥1,500.00￥800.00￥200.00￥2,500.000000-1234-5678-9123-007员员工工基基本本工工资资记记录录表表级数级数税率(%)税率(%)速算

10、扣除数速算扣除数1￥0.00￥500.005%￥0.002￥501.00￥2,000.0010%￥25.003￥2,001.00￥5,000.0015%￥125.004￥5,001.00￥20,000.0020%￥375.005￥20,001.00￥40,000.0025%￥1,375.006￥40,001.00￥60,000.0030%￥3,375.007￥60,001.00￥80,000.0035%￥6,375.008￥80,001.00￥100,000.0040%￥10,375.009￥100,001.00NA45%￥15,375.00个人所得税率表个人所得税率表全月应纳税所得额全月应纳

11、税所得额起征点1600元,低于1600元的不征收个人所得税起征点1600元,低于1600元的不征收个人所得税文本函数文本函数pLeft（从左提取）（从左提取）pRight（从右提取）（从右提取）pMid（从中间提取）（从中间提取）pFind（查找）（查找）p文本函数文本函数pToday()pDay()pMonth()pYear()pWeekday()pWeekNum()pDateDIF()p日期函数日期函数与函数成为朋友与函数成为朋友有事找帮助！有事找帮助！制表工具制表工具制表工具制表工具数据有效性数据有效性p制表的苦恼制表的苦恼p解决方法解决方法p不能填错不能填错数据有效性数据有效性p限制单

12、元格输入限制单元格输入p信息提示信息提示p有效性的警告方式有效性的警告方式条件格式（显示表中数据特点的最好工具）条件格式（显示表中数据特点的最好工具）p显示格式提示显示格式提示p自定义条件格式自定义条件格式p内容提醒内容提醒p日期提醒（合同到期日提醒）日期提醒（合同到期日提醒）p财务数据提示财务数据提示p隔行变色隔行变色汇总工具汇总工具数据汇总工具数据汇总工具p函数汇总函数汇总p工具汇总工具汇总p分类汇总分类汇总根据分类项，汇总项，汇总方式汇总根据分类项，汇总项，汇总方式汇总p合并计算合并计算多表合并数据多表合并数据p条件求和计算条件求和计算根据特殊分类项汇总根据特殊分类项汇总p数据透视数据透

13、视交互式分类汇总交互式分类汇总分类汇总分类汇总p数据分析的基本方法数据分析的基本方法p分类分类p汇总汇总p多级别分类汇总多级别分类汇总p打印打印p数据顺序数据顺序p结果复制结果复制合并计算合并计算p多个表单的数据进行合并汇总多个表单的数据进行合并汇总p数据位置不一样数据位置不一样p表头顺序不一样表头顺序不一样p最左列内容不一样最左列内容不一样用图表说话用图表说话怎样将的你的数据用图表怎样将的你的数据用图表 表现出来表现出来例：图表动画例：图表动画p公司正式员工为公司正式员工为52人人p现人员来源分配现人员来源分配p公司员工平均年龄公司员工平均年龄35岁，博士生岁，博士生7%，硕士生，硕士生60

14、%，海外背景海外背景10%。例：例：08年到年到09年人员增减变化年人员增减变化p0年实际人员年实际人员52人人p09年预计增加人员年预计增加人员18人人p09年预计增加后年预计增加后80人人p人力资源部建议人数人力资源部建议人数 28人人Excel标准图表类型标准图表类型面积图面积图6XY(散点散点)图图5条形图条形图6曲面图曲面图4柱型图柱型图7气泡图气泡图2折线图折线图7股价图股价图4饼图饼图6圆柱图圆柱图7圆环图圆环图2圆锥图圆锥图7雷达图雷达图3棱锥图棱锥图7选择合适图表类型选择合适图表类型Jan7,092Feb7,497Mar7,560Apr5,796May13,984Jun18,

15、743Jul15,434Aug14,545Sep17,434Oct16,984Nov15,983Dec27,822选择图表类型选择图表类型柱状图柱状图对比关系的几种选择对比关系的几种选择020406080100120123456SKU-1SKU-2020406080100120140160123456SKU-2SKU-1123456SKU-1SKU-2020406080100SKU-1SKU-2条型图条型图分类标志易读分类标志易读时间趋向弱时间趋向弱饼图饼图体现数据的百分比对比体现数据的百分比对比建议不多于建议不多于5或或6个数据点个数据点扇区可脱离中心扇区可脱离中心如果数据项太多如果数据项太

16、多常见需求常见需求处理丢失数据处理丢失数据p 忽略丢失数据，有空距忽略丢失数据，有空距p 将丢失数据处理为将丢失数据处理为0p 内插值内插值次坐标轴次坐标轴涨跌柱线涨跌柱线趋势线趋势线p趋势线的的估计值与实际数值的接近程度趋势线的的估计值与实际数值的接近程度图表替数据说话图表替数据说话-根据数据设计图表根据数据设计图表分组对比分组对比坐标轴多级分类坐标轴多级分类不同内容数据对比不同内容数据对比不同内容数据对比不同内容数据对比2美化图表美化图表系列设置系列设置绘图区背景绘图区背景在图表中使用自选图形在图表中使用自选图形坐标轴多级分类坐标轴多级分类可以腾飞的图表可以腾飞的图表温度计图表温度计图表动态图表展示动态图表展示谢谢！谢谢！Q&A咨询电话：咨询电话：010-59790435