1、Excel工具在汽车4S店中的高效数据汇总分析应用 王小伟Ex cel在汽车4 S店财务管理中的高效应用系列课程 课程目标 掌握日常数据的整理技巧及常用函数的使用方法 熟练掌握数据透视表在数据分析中的灵活应用 掌握快速汇总大量工作表的有效方法 掌握常用数据分析工具的灵活使用方法和技巧 让您从烦锁的数据处理分析工作中解脱出来 2Ex cel在汽车4 S店财务管理中的高效应用系列课程 目录 第1部分：汽车4S店运营数据汇总分析前的准备 第2部分：常用函数及其在汽车4S店数据管理与分析中的高效应用 第3部分：利用数据透视表快速编制各种汽车4S店统计分析报表 第4部分：快速汇总分析汽车4S店大量工作表
2、数据的实用技能和技巧 第5部分：利用图表展示分析结果 3Ex cel在汽车4 S店财务管理中的高效应用系列课程 第1部分 运营数据汇总分析前的准备 从管理的角度审视两类Ex c el表格 正确处理三种Ex c el数据 培养良好的Ex c el使用习惯 表单数据的快速整理和规范 快速转换非法日期 文本数字与纯数字间的相互转换 快速填充数据 数据的分列 4Ex cel在汽车4 S店财务管理中的高效应用系列课程 从管理的角度审视两类Ex c el表格 清单型表格 按照数据的功能来分别保存在不同的工 作表 结构越简单越好 报告型表格 分析结果的呈现，展示给别人看 结构越清楚越好 在设计任何一个表格时
3、，必须从管理角度， 考虑表格结构应该如何设计、如何布局。 两类表格的区分 5 科学的表 结构 科学的数 据结构 科学规范 的Excel表Ex cel在汽车4 S店财务管理中的高效应用系列课程 正确处理三种Ex c el数据 Excel的3类数据 文本 日期和时间 数字（纯数字和文本型数字） 如何正确输入这3类数据？ 输入文本应注意的问题 正确输入数字型文本 输入正确的日期和时间 6Ex cel在汽车4 S店财务管理中的高效应用系列课程 表单数据的快速整理与规范 整理表格数据的实用技巧 快速修改非法日期 文本数字与纯数字间的相互转换 快速填充数据 数据快速分列 案例练习：案例01 表单数据的快速
4、整理与规范 7Ex cel在汽车4 S店财务管理中的高效应用系列课程 第2部分 常用函数的使用技巧和实际应用 公式的基本知识 函数的基本语法与注意事项 引用方式在公式中的重要性 创建复杂公式的实用方法 利用名称简化计算公式 自定义数字格式使数据易读 数据的逻辑判断 数据的分类汇总计算 灵活的数据查找与引用 8Ex cel在汽车4 S店财务管理中的高效应用系列课程 公式的基本知识 公式 由运算符号把数字、文本、日期、单元格引用、名称、函数等连接在一 起，完成特定任务 公式不是函数 公式要根据具体的表格结构、具体问题，动脑筋创建 特别注意 复制公式要注意单元格的绝对引用和相对引用 复制单元格公式和
5、复制公式本身是不同的 标点符号要是英文的 创建公式要有一个正确的思路 9Ex cel在汽车4 S店财务管理中的高效应用系列课程 函数的基本语法与注意事项 函数语法 函数名(参数1，参数2，参数3，) 函数可以有参数，也可以没参数 参数可以是数值、引用、名称、表达式、另外一个函数 所有的标点符号都必须是半角字符 学习函数的三要素 函数是干什么的 函数怎么用 函数用在什么地方 10Ex cel在汽车4 S店财务管理中的高效应用系列课程 引用方式在公式中的重要性 相对引用：复制公式时，单元格引用发生相对位移 绝对引用：复制公式时，单元格引用不发生位移 混合引用： 合理设置相对引用和绝对引用，可以快速
6、复制公式 案例练习：案例02 公式的引用方式 11Ex cel在汽车4 S店财务管理中的高效应用系列课程 创建复杂公式的实用方法 当计算公式要用到很多函数，需要创建复杂嵌套函 数公式时，可以采用分解综合法 基本步骤 S t ep1：先分解公式，保证每步都准确无误 S t ep2：再综合成一个公式 S t ep3：最后完善公式 案例练习：案例03 分解法创建复杂公式 12Ex cel在汽车4 S店财务管理中的高效应用系列课程 利用名称简化计算公式 合理使用名称，可以使数据处理和分析更加快捷和高效 定义名称的规则 名称的长度不能超过255个字符 名称中不能含有空格 名称中不能使用除下画线和句点以外
7、的其他符号 名称中的第一个字符必须是字母、汉字，不能使用单元格地址、 阿拉伯数字。 名称中的字母不区分大小写。 13Ex cel在汽车4 S店财务管理中的高效应用系列课程 自定义数字格式使数据易读 当表格的金额数字很大时，既不便于查看数据，又影响表格的美观。 缩小位数显示数字 注：数字的自定义格式代码最多为4部分： 整数；负数；零；文本 案例练习：案例04 自定义数字格式 14Ex cel在汽车4 S店财务管理中的高效应用系列课程 数据的逻辑判断 逻辑函数四兄弟 i f：基本逻辑判断 用法：=if(条件表达式,条件成立时的结果,条件不成立时的结果) a nd：把几个条件组合成一个与条件 用法：
8、=and(条件1,条件2,条件3,) o r：把几个条件组合成一个或条件 用法：=or(条件1,条件2,条件3,) i ferr or：判断一个表达式的值是否为错误值 用法：=iferr or(表达式,出错后给予的值) 案例练习：案例05 复杂数据的逻辑判断 15Ex cel在汽车4 S店财务管理中的高效应用系列课程 数据的分类汇总计算 SUMIF：单条件求和 用法：=SUMIF(判断区域, 条件值, 求和区域) SUMIF S ：多条件求和 用法：=SUMIF S (求和区域, 判断区域1,条件值1,判断区域2,条件值 2,) SUMPR O DUCT：单条件求和、多条件求和、单条件计数、多
9、条件计数 用法：=SUMPR ODUCT(数组1,数组2,数组3,) 案例练习：案例06 分类汇总函数基本应用 案例07 多条件汇总SUMIF S函数应用 16Ex cel在汽车4 S店财务管理中的高效应用系列课程 多条件下的分类汇总计算应用 sumpr o duct函数是一个数组函数，用于计算几组数组间对应元素乘积之 和。 用法：=SUMPR ODUCT(数组1, 数组2,数组3 , ) 应注意几个问题： 各个数组参数必须具有相同的维数； 对于非数值型的数组元素将作为0处理； 不允许将各个参数设置为整列或者整行数据； 如果是表达式构建数组，那么该数组不允许是逻辑值。 案例练习：案例08 SU
10、MPR ODUCT函数高效应用 17Ex cel在汽车4 S店财务管理中的高效应用系列课程 灵活的数据查找与引用 V L OOKUP：根据指定条件从指定区域的指定列取数 用法：=VL OOKUP(查找依据,查找区域,取数的列位置,精确查询or模糊 查询) M A T CH：查找指定数据在指定区域的位置 用法：=MA T C H(查找值,查找区域,查找模式) I NDEX：从指定区域的指定行和指定列取数 用法：=INDEX(指定区域,指定行,指定列) I NDIRECT：把字符串表示的单元格地址转换为真正的引用 用法：=INDIRECT(字符串表示的单元格地址) 18Ex cel在汽车4 S店财
11、务管理中的高效应用系列课程 VL OOKUP函数基本应用 VL OOKUP函数只能用于满足下列条件的场合： 查询表是列结构 查询条件是单条件 查询方向是从左往右 查询条件不允许有重复 查询条件是不区分大小写 VL OOKUP函数的两种基本用法 精确查询 模糊查询 案例练习：案例09 VL OOKUP函数基本应用 19Ex cel在汽车4 S店财务管理中的高效应用系列课程 MA T C H函数和INDEX函数 INDEX( )函数常与MATCH( )函数一起使用： 先用MA T CH()函数确定数据所在的行和列，然后利用INDE X ()函数将 该行和列交叉处的数据取出。 注：查找文本值时，MA
12、 T CH( )函数不区分大小写字母 MA T C H()函数查找不成功，则返回错误值#N/A 案例练习：案例10 INDE X MA T CH函数基本应用 20Ex cel在汽车4 S店财务管理中的高效应用系列课程 INDIRECT函数基本应用 把字符串表示的单元格地址转换为真正的引用 比如，单元格C1中保存文本“A1”，而此文本恰好是单元格A1的名 字，那么公式=INDIRECT(C1)就得到单元格A1的数据 比如，公式=INDIRECT(“ s heet1!B1”)就间接得到工作表Sheet1单 元格B1的数据。 案例练习：案例11 Indir ect函数基本应用 21Ex cel在汽车
13、4 S店财务管理中的高效应用系列课程 第3部分：利用数据透视表编制各种分析报表 数据透视表的基本应用 创建数据透视表的基本方法和注意事项 数据透视表的布局美化 建立多维度统计分析报表 建立深层次统计分析报表 建立多层统计分析报表 建立多指标计分析报表 综合应用 22Ex cel在汽车4 S店财务管理中的高效应用系列课程 数据透视表的基本应用 什么是数据透视表？ 动态的、数据统计分析报表 数据透视表的作用？ 分类汇总 数据分析 核对数据 汇总大量工作表 23Ex cel在汽车4 S店财务管理中的高效应用系列课程 认识数据透视表结构 24 报表筛选区域 行区域 列区域 数值区域Ex cel在汽车4
14、 S店财务管理中的高效应用系列课程 创建数据透视表的注意事项 数据区域的第一行为字段名称（标题） 避免在数据清单中存在空行和空列 各列只包含一种类型数据 不能出现非法日期 要汇总的数据不能是文本（文本型数字） 要汇总的数据列内最好不要有空单元格 避免在数据清单中出现合并单元格 避免在单元格的开头和末尾输入空格 25Ex cel在汽车4 S店财务管理中的高效应用系列课程 创建数据透视表的基本方法 利用数据透视表向导 按照向导步骤进行操作 缺点：无法实现数据源的动态更新， 除非使用动态数据区域名称 利用导入数据工具制作数据透视表 利用Excel 的导入数据工具制作 优点：这种数据透视表是一种动态的
15、数据透视表，如果工作表的 数据的增加或减少了，数据透视表的数据源也随之发生改变 案例练习：案例12 数据透视表基本练习 26Ex cel在汽车4 S店财务管理中的高效应用系列课程 利用数据透视表分析数据 布局透视表 设置字段计算方式 设置字段显示方式 组合字段 自定义计算字段 制作明细表 案例练习：案例13 数据透视分析应用 27Ex cel在汽车4 S店财务管理中的高效应用系列课程 数据透视表综合应用 透视表综合应用1：高效核对数据，快速找出两个表的差异数据 透视表综合应用2：快速进行银行与企业账单核对 28Ex cel在汽车4 S店财务管理中的高效应用系列课程 第4部分：快速汇总大量工作簿
16、和工作表数据 快速汇总结构完全相同的多个工作表 快速汇总结构不同的多个工作表 快速汇总多个有关联的工作表 导入数据+SQL语句快速汇总多表 综合应用案例 多年运营费用汇总分析 利润表动态汇总与统计分析 29Ex cel在汽车4 S店财务管理中的高效应用系列课程 快速汇总结构完全相同的多个工作表 快速汇总结构完全相同的表注意事项 每个工作表的结构完全相同 行、列也一样多 行顺序和列顺序一模一样 案例练习：案例14 快速汇总结构相同的多个工作表 30Ex cel在汽车4 S店财务管理中的高效应用系列课程 快速汇总结构不同的多个工作表 多重合并计算数据区域： 汇总的每个工作表数据区或只有一列文本，
17、从第2列开始是要汇总的数字 行数、列数可以不同 项目的行顺序和列顺序也可不同 方法： 使用“数据透视表和数据透视图向导”命令 案例练习：案例15 快速汇总结构不同的多个工作表 31Ex cel在汽车4 S店财务管理中的高效应用系列课程 快速汇总多个有关联的工作表 使用MrcrosofetQuery 工具 汇总多个有关联工作表 有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关 键列数据（比如工号）联系起来。 要将这样几个相关联的工作表数据进行汇总，可使用Microsoft Query 。 案例练习：案例16 快速汇总多个有关联的工作表 32Ex cel在汽车4 S店财务管理中的高效应
18、用系列课程 导入数据+SQL语句快速汇总多个工作表 问题： 要汇总的每个工作表有多列文本，但是这些工作表的列数据结构 完全相同，也就是列数相同，列数据项目的先后顺序也相同，但是行 数不同，如何快速汇总分析？ 常规解决方法：把各个工作表的数据复制粘贴到一张工作表上。 该方法缺点：工作量大而烦琐、汇总数据是静态的，不能随时反映源工 作表的数据变化情况。 科学解决方法：使用导入数据+SQL数据查询的方法 案例练习：案例17 导入数据+SQL语句快速汇总多个工作表 33Ex cel在汽车4 S店财务管理中的高效应用系列课程 综合应用案例 多年运营费用汇总分析 应用练习 1 利润表动态汇总与统计分析 应
19、用练习 2 34Ex cel在汽车4 S店财务管理中的高效应用系列课程 第5部分：利用图表展示分析结果 制作分析图表的基本思路和原则 制作图表的的基本方法 使用动态图表灵活分析数据 35Ex cel在汽车4 S店财务管理中的高效应用系列课程 收集原始数据 规范数据 统计分析数据 用图表展示 分析结果 制作分析图表的基本思路和原则 绘制图表的目的 提出问题 分析问题 解决问题 36Ex cel在汽车4 S店财务管理中的高效应用系列课程 制作图表的的基本方法 绘制图表有2种基本方法： 自动绘制图表 手工绘制图表 37Ex cel在汽车4 S店财务管理中的高效应用系列课程 使用动态图表灵活分析数据 38 案例练习：案例18 库存车盘点分析图 案例练习：案例19 利润表资金流动分析图 案例练习：案例20 预算差异对比分析图联系我们： Tel：010-68455114、68464457 Fax：010-68455214 会员学习交流群：190453952 Net：http:/ Mail：
