用EXCEL创造价值课件.pptx

1、个人介绍 工作经历曾经就职于摩托罗拉，搜狐，世界银行 所获荣誉OFFICE 大师级认证，2019&2019年连续两年最佳EXCEL应用讲师首位香港大学中国商业学院特聘讲师，搜狐集团财务数据分析顾问 部分服务过的客户培训内容 排序 分类汇总 选择性粘贴 分列高效办公与发散思维 一般排序 多条件排序 按照特定字段排序 排序的延伸排序 扩展/当前的区别一般排序注意问题：数据包含标题多条件排序自定义序列按特定字段培训Office按钮-Excel选项-常用-编辑自定义列表2019自定义序列文件-选项-高级-编辑自定义列表2019自定义序列 还原排序 将无规律的文本进行排序排序的延伸多用于筛选后，将数据粘

2、回时使用还原排序保护工作表 一般分类汇总 多重分类汇总 分类汇总常见问题分类汇总 请分类汇总出：每位销售人员销售额是多少,一共卖出多少件商品，每种商品卖出多少件 练习 粘贴具有公式的数据 选择性粘贴的运算 转置选择性粘贴只想复制原始区域的数值而不是公式,或者只想复制原始区域的格式而不需要内容时选择性粘贴粘贴具有公式的数据超链接的处理方法选择性粘贴的运算 转置选择性粘贴 斜线单元格 分列的两大作用 分栏 强制转换格式分列11 01 24 19850816 228 1身份证的含义分列与选择性粘贴VlookupLookup_value 要查找的值KEY 两个表共同的点在查询表中选择Table_arr

3、ay 查找的范围一般情况下全选即可在被查询表中选择VlookupCol_index_num 查询的数据所在列号相对于第一列，查询的值在第几列在被查询表中数Range_lookup 精确/模糊 查询 选择False即可VLOOKUP=substitute(text,”,”)去除中间空格的Substitute对哪个单元格处理双引号之间有一个空格双引号无空格 MID =MID（,）截取类函数对哪个单元格截取TEXT A1从第几位开始截取Start_Num截取的长度，而不是而不是截取到第几位截取到第几位3Num_Chars4 数据透视表 数据透视表的基础 数据透视表常见问题 数据透视表的分页 数据透视

4、表的组合 数据透视表与函数数据处理与数据分析数据透视表 何时使用数据透视表 数据透视表的作用 包括大量错综复杂数据的表格，希望快速整理出一份具有实际意义的报表 希望找出同类数据在不同时期的某种特定关系 希望对数据进行合理有效地分组 需要经常查询分析数据的变化趋势 数据源经常变化，然而又需要经常分析和处理最新的数据源什么时候使用数据透视表数据透视表的基础 行标签/列标签/数值/报表筛选 值的汇总方式 刷新数据透视表 建立总分关系 合并标签 插入空行 空单元格填充数据透视表的常见问题数据透视表基础练习1 1.请统计出每个部门工资是多少 2.请统计出每个职位在各个部门的工资是多少 3.请统计出每个职

5、位都有哪些学历，每个学历都有多少人 4.请统计出每个部门的工资占整体工资多少比例数据透视表的基础练习2数据透视表的分页数据透视表的分页下拉箭头 请按照面试部门进行拆分拆分条件如下A.拆分后的各个面试部门数据仅展示参加初试者，忽略掉不参加者B.样式与下图一样：面试岗位合并且居中，每个项目各独自在一列展示，无各个汇总项数据透视表的分页练习数据透视的组合 数据透视表的组合 数据透视表的组合与分列 数据透视表的组合与函数请按照要求展示出公司的人数分布1.组合出公司25岁以下，45岁以上，中间为每五岁的人数分布 2.并在上述基础上再组合出10000元以下，40000元以上，中间位每5000元的人数分布数

6、据透视表的组合练习 =IF（,）IF函数Logical_test B2=1Value_True Value_False 逻辑判断不包含的写法A.不等于不等于B.C.B2*C2,评估值为&B2*C2,“请重新评估)IF的数字比较 IF的文本比较 IF的计算 IF的单元格比较与计算IF的基础使用方式IF高级进阶-IF进阶1销售额在800万（含800万）以上的奖金系数为销售额的20%销售额在500万（含500万）至800万之间的奖金系数为销售额的10%销售额在500万以下为销售额的5%IF的嵌套IValue_True，=IF(Logical_Test,如果是足球裤如果是足球裤且且如果是黑色的如果是黑

7、色的Value_False）IF(Logical_test,Value_True,Value_Fales),IF的嵌套IIValue_True，=IF(Logical_Test,如果不是足球裤，则如果不是足球裤，则Value_False）IF(Logical_test,Value_True,Value_Fales)=IF(F2=8000000,F2*20%,IF(F2=5000000,F2*10%,F2*5%)销售额在800万（含800万）以上的奖金系数为销售额的20%销售额在500万（含500万）至800万之间的奖金系数为销售额的10%销售额在300万（含300万）至500万之间的奖金系数为

8、销售额的8%销售额在300万以下的奖金系数为销售额的5%请判断所在区域如果是以“BJ”起始的，那么则为北京地区如果是以“SH”起始的，那么则为上海地区如果是以“HK”起始的，那么则为香港地区北京天地在线广告有限公司协议规定销售额大于800万时，返点为销售额的20%销售额500-800万时，返点为销售额的10%销售额500以下时，返点为销售额的8%鞍山天鼎文化广告公司协议规定销售额大于500万时，返点为销售额的8%销售额大于300-500万时，返点为销售额的6%销售额300万以下时，返点为销售额的5%杭州开创网络广告有限公司协议规定销售额大于700万时，返点为销售额的15%销售额大于400-70

9、0万时，返点为销售额的8%销售额400万以下时，返点为销售额的6%根据协议只有以下3家有返点费，而其他公司没有返点费北京天地在线广告有限公司鞍山天鼎文化广告公司杭州开创网络广告有限公司=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)SUMIFS、CountifsDATEDIF、TODAY2019年非洲5岁以下儿童饥饿死亡人数线柱组合图II条形图6