用EXCEL创造价值课程学习

个人介绍工作经历曾经就职于摩托罗拉，搜狐，世界银行所获荣誉OFFICE大师级认证，2012&2013年连续两年最佳EXCEL应用讲师首位香港大学中国商业学院特聘讲师，搜狐集团财务数据分析顾问部分服务过的客户第1页/共62页第一页，共63页。基础功能函数数据透视表培训内容数据透视表函数基础功能排序分类汇总保护工作表选择性粘贴分列查找引用类函数截取数据类函数条件判断类函数数据透视表的基础数据透视表的美化数据透视表的逻辑数据透视表的技巧AIM第2页/共62页第二页，共63页。排序分类汇总选择性粘贴分列高效办公与发散思维第3页/共62页第三页，共63页。一般排序多条件排序按照特定字段排序排序的延伸排序第4页/共62页第四页，共63页。扩展/当前的区别一般排序第5页/共62页第五页，共63页。注意问题：

数据包含标题多条件排序第6页/共62页第六页，共63页。自定义序列按特定字段培训第7页/共62页第七页，共63页。Office按钮---

Excel选项---常用---

编辑自定义列表2007自定义序列第8页/共62页第八页，共63页。文件---选项---高级---

编辑自定义列表2010自定义序列第9页/共62页第九页，共63页。还原排序将无规律的文本进行排序排序的延伸第10页/共62页第十页，共63页。多用于筛选后，将数据粘回时使用还原排序第11页/共62页第十一页，共63页。保护工作表第12页/共62页第十二页，共63页。一般分类汇总多重分类汇总分类汇总常见问题分类汇总第13页/共62页第十三页，共63页。请分类汇总出：

每位销售人员销售额是多少,一共卖出多少件商品，每种商品卖出多少件

练习第14页/共62页第十四页，共63页。粘贴具有公式的数据选择性粘贴的运算转置选择性粘贴第15页/共62页第十五页，共63页。只想复制原始区域的数值而不是公式,或者只想复制原始区域的格式而不需要内容时选择性粘贴第16页/共62页第十六页，共63页。粘贴具有公式的数据第17页/共62页第十七页，共63页。超链接的处理方法选择性粘贴的运算

第18页/共62页第十八页，共63页。转置选择性粘贴第19页/共62页第十九页，共63页。斜线单元格第20页/共62页第二十页，共63页。分列的两大作用

分栏

强制转换格式分列第21页/共62页第二十一页，共63页份证的含义第22页/共62页第二十二页，共63页。分列与选择性粘贴第23页/共62页第二十三页，共63页。VlookupLookup_value要查找的值KEY两个表共同的点在查询表中选择Table_array查找的范围一般情况下全选即可在被查询表中选择第24页/共62页第二十四页，共63页。VlookupCol_index_num查询的数据所在列号相对于第一列，查询的值在第几列在被查询表中数Range_lookup精确/模糊查询

选择False即可第25页/共62页第二十五页，共63页。VLOOKUP第26页/共62页第二十六页，共63页。=substitute(text,””,””)去除中间空格的Substitute对哪个单元格处理双引号之间有一个空格双引号无空格第27页/共62页第二十七页，共63页。MID

=MID（,,）截取类函数

对哪个单元格截取TEXTA1从第几位开始截取Start_Num截取的长度，而不是截取到第几位3Num_Chars4第28页/共62页第二十八页，共63页。数据透视表

数据透视表的基础

数据透视表常见问题

数据透视表的分页

数据透视表的组合数据透视表与函数数据处理与数据分析第29页/共62页第二十九页，共63页。数据透视表

何时使用数据透视表数据透视表的作用

第30页/共62页第三十页，共63页。包括大量错综复杂数据的表格，希望快速整理出一份具有实际意义的报表希望找出同类数据在不同时期的某种特定关系希望对数据进行合理有效地分组需要经常查询分析数据的变化趋势数据源经常变化，然而又需要经常分析和处理最新的数据源什么时候使用数据透视表第31页/共62页第三十一页，共63页。数据透视表的基础行标签/列标签/数值/报表筛选值的汇总方式刷新数据透视表建立总分关系合并标签插入空行空单元格填充第32页/共62页第三十二页，共63页。数据透视表的常见问题第33页/共62页第三十三页，共63页。数据透视表基础练习1第34页/共62页第三十四页，共63页。1.请统计出每个部门工资是多少2.请统计出每个职位在各个部门的工资是多少3.请统计出每个职位都有哪些学历，每个学历都有多少人4.请统计出每个部门的工资占整体工资多少比例数据透视表的基础练习2第35页/共62页第三十五页，共63页。数据透视表的分页第36页/共62页第三十六页，共63页。数据透视表的分页下拉箭头第37页/共62页第三十七页，共63页。请按照面试部门进行拆分拆分条件如下拆分后的各个面试部门数据仅展示参加初试者，忽略掉不参加者样式与下图一样：面试岗位合并且居中，每个项目各独自在一列展示，无各个汇总项数据透视表的分页练习第38页/共62页第三十八页，共63页。数据透视的组合数据透视表的组合数据透视表的组合与分列数据透视表的组合与函数第39页/共62页第三十九页，共63页。请按照要求展示出公司的人数分布1.组合出公司25岁以下，45岁以上，中间为每五岁的人数分布

2.并在上述基础上再组合出10000元以下，40000元以上，中间位每5000元的人数分布数据透视表的组合练习第40页/共62页第四十页，共63页。

=IF（,,）IF函数

Logical_testB2=1Value_TrueValue_False逻辑判断第41页/共62页第四十一页，共63页。不包含的写法A.不等于B.≠C.><D.=!第42页/共62页第四十二页，共63页。请算出住房公积金：如果工资超过了17379，请按照17379*12%进行计算如果没有超过17379，请按照实际工资*12%进行计算IF基础练习-IF练习3第43页/共62页第四十三页，共63页。IF基础练习-IF练习4请算出评估值如果（成本系数-衡量系数）的值大于（成本系数*评定参数）的值，则显示为评估值为XX（XX为（成本系数*评定参数）的值）如果（成本系数-衡量系数）的值小于（成本系数*评定参数）的值，则显示为请重新评估第44页/共62页第四十四页，共63页。=IF(B2-$H$2>B2*C2,"评估值为"&B2*C2,“请重新评估")第45页/共62页第四十五页，共63页。IF的数字比较IF的文本比较IF的计算IF的单元格比较与计算IF的基础使用方式第46页/共62页第四十六页，共63页。IF高级进阶-IF进阶1销售额在800万（含800万）以上的奖金系数为销售额的20%销售额在500万（含500万）至800万之间的奖金系数为销售额的10%销售额在500万以下为销售额的5%第47页/共62页第四十七页，共63页。IF的嵌套IValue_True，=IF(Logical_Test,如果是足球裤且如果是黑色的Value_False）IF(Logical_test,Value_True,Value_Fales),第48页/共62页第四十八页，共63页。IF的嵌套IIValue_True，=IF(Logical_Test,如果不是足球裤，则Value_False）IF(Logical_test,Value_True,Value_Fales))第49页/共62页第四十九页，共63页。=IF(F2>=8000000,F2*20%,IF(F2>=5000000,F2*10%,F2*5%))第50页/共62页第五十页，共63页。销售额在800万（含800万）以上的奖金系数为销售额的20%销售额在500万（含500万）至800万之间的奖金系数为销售额的10%销售额在300万（含300万）至500万之间的奖金系数为销售额的8%销售额在300万以下的奖金系数为销售额的5%第51页/共62页第五十一页，共63页。第52页/共62页第五十二页，共63页。请判断所在区域如果是以“BJ”起始的，那么则为北京地区如果是以“SH”起始的，那么则为上海地区如果是以“HK”起始的，那么则为香港地区IF高级进阶-IF进阶3第53页/共62页第五十三页，共63页。北京天地在线广告有限公司协议规定销售额大于800万时，返点为销售额的20%销售额500-800万时，返点为销售额的10%销售额500以下时，返点为销售额的8%鞍山天鼎文化广告公司协议规定销售额大于500万时，返点为销售额的8%销售额大于300-500万时，返点为销售额的6%销售额300万以下时，返点为销售额的5%杭州开创网络广告有限公司协议规定销售额大于700万时，返点为销售额的15%销售额大于400-700万时，返点为销售额的8%销售额400万以下时，返点为销售额的6%IF高级进阶-IF进阶4根据协议只有以下3家有返点费，而其他公司没有返点费北京天地在线广告有限公司鞍山天鼎文化广告公司杭州开创网络广告有限公司第54页/共62页第五十四页，共63页。=IF(A2="北京天地在线广告有限公司",IF(B2>=8000000,B2*20%,IF(B2>=5000000,B2*10%,B2*8%)),IF(A2="鞍山天鼎文化广告公司",IF(B2>=5000000,B2*8%,IF(B2>=3000000,B2*6%,B2*5%)),IF(A2="杭州开创网络广告有限公司",IF(B2>=7000000,B2*15%,IF(B2>=4000000,B2*15%,B2*6%)),0)))第55页/共62页第五十五页，共63页。SUMIFS、CountifsDATEDIF、TODAY第56页/共62页第五十六页，共63页。2012年非洲5岁以下儿童饥饿死亡人数第57页/共62页第五十七页，共63页。第58页/共62页第五十八页，共63页。线柱组合图II第59页/共62页第五十九页，共63页。条形图6第60页/共62页第六十页，共6