杰出的Excel在企业中的应用

MicrosoftOfficeSpecialist《杰出的Excel在企业中的应用》讲师:郑学谦EXCEL的数据处理&数据分析繁琐的数据收集将复杂的数据整理成有效数据各种表格数据汇总将数据数据进行各种方式汇总个性需求多的数据分析根据需求使用分析工具处理数据准确的图表说话将各种内容的数据展示出来什么是数据统计分析？产能比分析、投放比分析、利耗比分析！分类汇总统计（不同汇总方式，不同分类方式）统计汇总分析的目的？同比、环比、百分比周期性分析、时效性分析、因素性分析Excel最强大的数据分析工具数据透视表数据透视表的基础功能实现高效的数据分类汇总★拖拽字段需求性组合字段需求性显示状态其它辅助功能排序筛选显示格式汇总方式案例1周期性分析数据要求：日期字段、时间字段销售周期的区间不同（分类字段不同）年、季度、月、日、小时多字段叠加统计数据的显示状态根据需求变换案例2数值区间分析数据要求：数值字段数值字段的分组区间不同（分类字段的不同）价格区间、销量区间统计数据的显示状态根据需求变换案例3数据属性分类数据要求：文字字段组合根据数据的属性实现分类分组分析（文字分类）产品分类、内容分类、属性分类统计数据的显示状态根据需求变换总结：数据分析的思路按相关因素进行任意字段组合按相关因素进行字段数据组合数据透视表的拖拽思路数据属性字段——页、行、列区域数值属性字段——数据区域分析中的其它需求增加字段增加计算字段组合字段（虚拟字段）分类排列（表格排列方式）字段显示格式设置（货币、百分比、自定义）生成数据透视图（生成图表）数据透视表对源表格格式的要求★不能有合并单元格单条记录成行，不能有空行数值是数值，文本是文本数据有效性问题数据差错的工具-圈释无效数据基于数据有效性数据的有效性的使用保证数据的有效的规则设置数据格式要求：数值（限制范围）文本（长度）日期（限制范围）时间（限制范围）序列（下拉菜单）多级菜单（名称的使用）表格区域的名称定义名称（名称框）引用数据（插入，名称，粘贴）指定公式中应用名称定义：Ctrl+F3粘贴：F3名称的理解：全局公式用函数管理你的数据数据太多，太复杂，怎么整理？查询函数查询简单查询数据区间内查询数据的对比使用查询函数（lookup）查找引用函数VLookup(查找项，被查找区域，返回列，false精确匹配/true模糊匹配)索引项目对比…个人所得税计算HLookup()案例：制作工资数据表使用vlookup函数查询姓名和部门使用名称提取数据使用vlookup函数计算应扣所得税额完成工资条、保护工作表员工编号员工姓名所属部门最后一次调薪时间调整后基础工资调整后岗位工资调整后工龄工资调整后总基本工资银行帐号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￥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员工基本工资记录表级数税率(%)速算扣除数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个人所得税率表全月应纳税所得额起征点1600元,低于1600元的不征收个人所得税文本函数Left（从左提取）Right（从右提取）Mid（从中间提取）Find（查找）文本函数Today()Day()Month()Year()Weekday()WeekNum()DateDIF()日期函数与函数成为朋友有事找帮助！制表工具制表工具——数据有效性制表的苦恼解决方法不能填错——数据有效性限制单元格输入信息提示有效性的警告方式条件格式（显示表中数据特点的最好工具）显示格式提示自定义条件格式内容提醒日期提醒（合同到期日提醒）财务数据提示隔行变色汇总工具数据汇总工具函数汇总工具汇总分类汇总——根据分类项，汇总项，汇总方式汇总合并计算——多表合并数据条件求和计算——根据特殊分类项汇总数据透视——交互式分类汇总分类汇总数据分析的基本方法分类汇总多级别分类汇总打印数据顺序结果复制合并计算多个表单的数据进行合并汇总数据位置不一样表头顺序不一样最左列内容不一样用图表说话怎样将的你的数据用图表表现出来例：图表动画公司正式员工为52人现人员来源分配公司员工平均年龄35岁，博士生7%，硕士生60%，海外背景10%。各部门人数分布情况10人20人10人12人领导层前台中台后台各部门人数分布情况34%58%3%5%民生银行调入推荐招聘猎头公司外聘例：08年到09年人员增减变化0年实际人员52人09年预计增加人员18人09年预计增加后80人人力资源部建议人数28人08年到09年人员增减变化52182852800102030405060708090100Excel标准图表类型图表类型数量图表类型数量面积图6XY(散点)图5条形图6曲面图4柱型图7气泡图2折线图7股价图4饼图6圆柱图7圆环图2圆锥图7雷达图3棱锥图7选择合适图表类型WebSiteVisitors05,00010,00015,00020,00025,00030,000JanFebMarAprMayJunJulAugSepOctNovDecWebSiteVisitors05,00010,00015,00020,00025,00030,000JanFebMarAprMayJunJulAugSepOctNovDecWebSiteVisitors05,00010,00015,00020,00025,00030,000JanFebMarAprMayJunJulAugSepOctNovDecWebSiteVisitorsJanFebMarAprMayJunJulAugSepOctNovDecWebSiteVisitors010,00020,00030,000JanFebMarAprMayJunJulAugSepOctNovDecWebSiteVisitors05,00010,00015,00020,00025,00030,000JanFebMarAprMayJunJulAugSepOctNovDecJan7,092Feb7,497Mar7,560Apr5,796May13,984Jun18,743Jul15,434Aug14,545Sep17,434Oct16,984Nov15,983Dec27,822选择图表类型柱状图——对比关系的几种选择020406080100120123456SKU-1SKU-2020406080100120140160123456SKU-2SKU-10%10%20%30%40%50%60%70%80%90%100%123456SKU-2SKU-1123456SKU-1SKU-2020406080100SKU-1SKU-2条型图PercentWhoStronglyAgree0%5%10%15%20%25%30%StorelocationsareconvenientStorehoursareconvenientStoresarewell-maintainedIlikeyourwebsiteEmployeesarefriendlyYouhaveagoodselectionofproductsIlikeyourTVadsYousellqualityproductsOverall,IamsatisfiedIwouldrecommendyourcompanySurveyResults0%10%20%30%40%50%60%70%80%90%100%StorelocationsareconvenientStorehoursareconvenientStoresarewell-maintainedIlikeyourwebsiteEmployeesarefriendlyYouhaveagoodselectionofproductsIlikeyourTVadsYousellqualityproductsOverall,IamsatisfiedIwouldrecommendyourcompanyStronglyAgreeAgreeUndecidedDisagreeStronglyDisagree分类标志易读时间趋向弱饼图——体现数据的百分比对比建议不多于5或6个数据点扇区可脱离中心您认为公司的产品价格非常好很好好较差非常差如果数据项太多您认为公司的产品价格非常好很好好较差非常差常见需求处理丢失数据忽略丢失数据，有空距将丢失数据处理为0内插值02040608010012:00AM4:00AM8:00AM12:00PM4:00PM8:00PM02040608010012:00AM4:00AM8:00AM12:00PM4:00PM8:00PM02040608010012:00AM4:00AM8:00AM12:00PM4:00PM8:00PM次坐标轴01,000,0002,000,0003,000,0004,000,0005,000,000一月二月三月四月五月六月销量百分比01,000,0002,000,0003,000,0004,000,0005,000,000一月二月三月四月五月六月0.0%2.0%4.0%6.0%8.0%10.0%12.0%14.0%16.0%销量百分比销量百分比一月4,456,32212.5%二月2,032,23311.3%三月1,889,88310.2%四月2,450,90814.5%五月2,879,34314.3%六月1,183,43415.1%涨跌柱线0100,000200,000300,000400,000500,000600,000700,000800,000一月二月三月四月五月六月收入支出月份收入支出一月160,156288,239二月384,238232,177三月401,845300,484四月648,498528,024五月732,075420,434六月736,350580,190趋势线趋势线的的估计值与实际数值的接近程度TestScoresy=0.6419x+26.154R2=0.66565055606570758085909510050556065707580859095100Test1Test2图表替数据说话-根据数据设计图表FirstHa