Excel财务数据处理与分析实战技巧精粹电子书

内容提要

写在前面

一、管理好数据的前提是规范数据

二、财务数据分析的原则

1．避免数据分析误区

2．数据分析的具体内容

3．数据分析的方向

三、本书给你的帮助

通用技巧篇

第1章 数据的获取

技巧001 批量导入文本数据

1．常规文本数据的导入

2．15位以上长数字的文本数据导入

3．选择性地导入文本数据

技巧002 批量导入网页数据

技巧003 批量填充导出数据

技巧004 批量修改数据

1．将表格中的“应收帐款”替换为“应收账款”

2．将“借方金额”列和“贷方金额”列中的0值全部清除

技巧005 剔除重复记录

技巧006 批量处理不规范的日期数据

技巧007 行数据和列数据的转换

技巧008 快速拆分英文代码和汉字名称的混合数据

技巧009 快速拆分日期

技巧010 两种方法解决导出的数据不能求和的问题

1．处理包含单引号的普通数据

2．处理包含单引号的日期数据

1．处理包含单引号的数据

2．处理包含单引号的日期数据

技巧011 批量剔除导出数据首部或尾部的不可见字符

1．判断不可见字符是否存在

2．剔除不可见字符

方法1：使用函数剔除不可见字符。

方法2：使用文本分列向导剔除不可见字符。

技巧012 解决导出的文本内容不匹配问题

1．用TRIM函数去除空格字符

2．用N函数和NUMBERVALUE函数转换数据格式

第2章 数据的自定义格式

技巧013 分段显示长数据

技巧014 带有计量单位的数据求和

技巧015 自动填写表头的3种方法

1．月份在前的表头填写方法

2．月份在中间的表头填写方法

3．仅显示年月的自定义表头填写方法

技巧016 批量编辑多个单元格区域

1．在多个空白单元格区域中批量录入相同内容

2．在指定的单元格区域的多个空白单元格中批量录入相同内容

技巧017 隐藏敏感数据

技巧018 根据日期自动获取对应的星期

技巧019 高效录入编码

第3章 数据的规范录入

技巧020 使用单元格下拉列表规范录入内容

技巧021 使用跨表单元格下拉列表规范录入内容

技巧022 限制日期录入范围

技巧023 限制数字录入范围

技巧024 限制单元格只能录入特定编码

技巧025 设置数据录入范围提醒

技巧026 非法录入数据时显示出错警告

技巧027 保持引用位置不变的单元格公式复制（绝对引用的作用）

技巧028 限制输入重复数据

第4章 高效筛选数据

技巧029 单条件筛选

技巧030 双条件交叉关系筛选

技巧031 多条件筛选

技巧032 区间条件筛选

技巧033 多字段条件筛选

技巧034 组合条件筛选

技巧035 数据对比筛选

技巧036 公式条件筛选

技巧037 公式筛选法之从外部获取筛选条件

技巧038 利用高级筛选功能拆分数据汇总表

技巧039 利用高级筛选功能快速对比数据

技巧040 利用高级筛选功能快速对重复项目求和

第5章 数据的汇总与统计

技巧041 快速生成汇总表——数据透视表法

1．数据源范围的选择

2．选择横向字段和纵向字段

3．添加/取消分项汇总

4．按照表格设置字段

5．切换统计方式，选择求和或计数

6．插入表格

技巧042 快速拆分汇总表为明细表

技巧043 利用切片器自由选择筛选数据

技巧044 更新追加数据后的汇总数据

技巧045 快速转换汇总布局

技巧046 批量合并相同内容的单元格

技巧047 对合并单元格数据进行排序

第6章 利用条件格式标识重点数据

技巧048 凸显重复数据

技巧049 自动标记排名前三和后三的数据

技巧050 标记高于或低于平均值的数据

技巧051 用色阶标识数据的大小差异

技巧052 用数据条展示数据大小

技巧053 用饼图展示数据的百分比关系

技巧054 用圆环图展示百分比指标

技巧055 用折线图展示数据的趋势变化

技巧056 用条形图展示指标完成情况

技巧057 用瀑布图展示数据增减幅度

第7章 IF函数的嵌套应用

技巧058 双结果单条件判断取值

技巧059 三结果单条件IF函数嵌套判断取值

技巧060 双结果双条件（或）判断取值

技巧061 双结果双条件（并且关系）不等判断取值

技巧062 双结果双条件（并且关系）相等判断取值

技巧063 双结果多条件（或关系）判断取值

技巧064 多条件的三类结果嵌套判断取值

第8章 数据的查找

技巧065 用工号本表查找姓名

技巧066 用商品编号跨表查找商品信息

技巧067 工龄级别归档查询

技巧068 销售业绩考核归档查询

1．设置单元格格式

2．编辑提成公式

技巧069 查找编码所在的位置数

技巧070 快速比对两组数据中的相同内容

技巧071 用公式查找数据表中的最后一条记录

第9章 数据的保护

技巧072 设置工作簿打开权限

技巧073 保护和取消保护工作簿

1．保护工作簿

2．取消保护工作簿

技巧074 保护工作表之正向操作法

技巧075 保护工作表之反向操作法

技巧076 保护工作表中允许操作的项目

技巧077 设置表格内报表区域的编辑权限

第10章 巧用定位功能加工数据

技巧078 批量添加求和公式

技巧079 批量填充资产类别列中的空白单元格

技巧080 批量填充日期、会计期间、凭证字号列的空白单元格

技巧081 一键清除报表中的非公式区数据

技巧082 标记公式单元格

技巧083 批量制作工资条

技巧084 批量删除数据间的不连续空白单元格

业务技巧篇

第11章 二维数据图表分析法

技巧085 二维柱形图数据分析法

1．数据准备

2．插入控件并编写公式

3．设置条件格式

4．绘制柱形图

技巧086 二维条形图数据分析法

1．数据准备

2．插入控件并编写公式

3．设置条件格式

4．绘制图表

1．纵向双年份数据对比模型

2．横向双年份数据对比模型

技巧087 二维数据图表分析法在多行业中的应用

1．业务员分月销售业绩分析

2．车间产量数据分析

3．餐饮行业数据分析

第12章 三维数据图表分析法

技巧088 搭建日报表分析系统的架构

技巧089 编制一张普通的数据汇总表

1．汇总前的数据梳理

2．收入类别的合计数对比分析

3．影城收入合计数对比分析

4．横向与纵向数据分析

技巧090 编制影城在时间轴上的数据统计分析表

1．分析方案

2．公式解析

3．绘制柱形图

技巧091 编制在一个时间点上的横向数据统计分析表

1．分析方案

2．公式解析

3．绘制条形图

第13章 月度营业收入图表分析法

技巧092 设计月度数据分析结构

技巧093 编制数据分析模板

1．数据与图表之间的联动关系及数据准备

2．编辑选择类别的公式

3．绘制柱形图

4．分析结论

1．家用电器类目营业收入月度分析

2．男装女装类目营业收入月度分析

3．拓展分析

第14章 多方案完成进度图表分析法

技巧094 用饼图展示百分数指标

1．收集数据

2．创建表格并输入基础信息

3．插入控件

4．编制调取月份数据的公式

5．编制完成比率公式

6．编制辅助数据公式

7．绘制图表

技巧095 用单元格图展示百分数指标

1．制作数据源与单元格

2．填充数据

3．设置边框和背景色

4．设置条件格式

5．隐藏数据

6．设置图表标题

技巧096 用形似蓄水池的柱形图展示百分数指标

1．数据准备

2．插入柱形图

3．调整数据的行列关系

4．设置柱形图分类间隔

5．设置完成比率柱形图的次坐标轴

6．调整坐标轴界限值

7．删除坐标值

8．绘制辅助图形

9．填充辅助数据柱形图

10．填充完成比例数据柱形图

11．设置层叠并缩放

12．设置自制的图表标题

综合案例篇

第15章 数量与金额转换图表分析法

1．分析方法

2．应用场景

15.1 分析方案设计

15.2 分析数据准备

15.3 编制明细数据表

15.4 编制分月汇总表

15.4.1 编制分月汇总表的内容

15.4.2 插入数值调节钮控件

15.4.3 设置辅助功能

15.4.4 编制主数据区公式

15.4.5 编制活动数据区公式

15.4.6 编制及应用图表

1．利用P2:P12单元格区域的数据编制图表

2．应用图表进行数据分析

第16章 订单拆解图表分析法

16.1 案例背景

16.1.1 数据来源

16.1.2 日报表的结构和作用

16.1.3 部门统计表

16.1.4 业务员统计表

16.1.5 关于日报表持续报送的思考

16.2 日报表的编制

16.2.1 数据准备

16.2.2 设计日报表布局及内容

16.2.3 插入数据切换的控件

16.2.4 编辑数据区公式

16.3 部门统计表的编制

16.3.1 设计部门统计表布局及内容

16.3.2 添加控件

16.3.3 编辑数据区公式

16.3.4 绘制图表

16.3.5 设置条件格式

16.4 业务员统计表

16.4.1 设计表格布局并填写基础数据

16.4.2 添加控件

16.4.3 编辑数据区公式

16.4.4 绘制图表

第17章 业绩拆解图表分析法

17.1 案例背景

17.1.1 数据来源表

17.1.2 数据统计分析的需求

17.2 案例的实现过程

17.2.1 数据源表的制作

17.2.2 汇总表基础数据准备

17.2.3 插入实现数据切换功能的控件

17.2.4 设置公司名称下拉列表

17.2.5 编辑调取分公司数据的公式

17.2.6 编制月份合计数数据公式

17.2.7 编制业务员数据公式

17.2.8 编制自动图表标题公式

17.2.9 绘制分公司全年销售数据汇总图表

17.2.10 绘制拆分到业务员名下的数据图表

17.3 应用场景拓展

1．业务员的销售明细统计

2．部门费用统计

第18章 收入与回款对比图表分析法

18.1 案例背景

18.1.1 数据源表

18.1.2 数据筛选过渡表

18.1.3 以图表方式输出合同数据

18.2 案例实现过程

18.2.1 在数据源表中做筛选准备

18.2.2 筛选满足条件的合同记录

18.2.3 绘制图表前的数据准备

1．编制绘图数据表

2．插入数值调节钮控件

3．插入复选框控件

4．编写公式调用数据

5．编写图表标题公式和柱形图数据公式

18.2.4 绘制图表

1．绘制查询进度图

2．绘制条形图

3．关联图表标题

4．添加数据标签，设置数据标签和坐标轴数值的万元格式

5．美化图表区

18.3 应用场景拓展

18.3.1 工程施工行业的项目进度管理分析

18.3.2 预算完成情况分析

18.3.3 订单执行情况分析

第19章 收入与回款汇总图表分析法

19.1 案例背景

1．数据准备的规则

2．汇总功能介绍

3．各月数据表功能介绍

19.2 汇总数据表及数据图的制作

19.2.1 数据表存储结构设计和数据规范化处理

19.2.2 汇总表格式设计与插入控件

19.2.3 编辑数据汇总公式

19.2.4 设置明细表条件格式

19.2.5 使用汇总数据编制图表

19.3 应用场景拓展

1．销售发货与收款金额分析

2．费用类计划指标与实际完成情况分析

第20章 收入与销售费用对比图表分析法

20.1 3种常用的对比指标组合

1．生产量与制造费用

2．销售数量与销售费用

3．生产产量与电费

20.2 财务分析图表化的操作流程

20.2.1 案例背景

20.2.2 数据准备

20.2.3 插入控件

20.2.4 编制控件关联业务员数据的公式

20.2.5 编制控件关联主营业务收入数据的公式

20.2.6 编制控件关联销售费用数据的公式

20.2.7 设置条件格式

20.2.8 绘制柱形图

20.3 图表功能的拓展应用

20.3.1 绘制折线图

20.3.2 绘制温度计图

第21章 制作电商客服绩效考核表

21.1 根据业务量考核绩效

21.1.1 案例背景和数据准备

1．制作副本

2．删除【下单时间】字段中的时间信息

3．替换下单时间数据

4．设置【下单时间】列的数据格式

21.1.2 根据考核方案设计表格中各项数据的布局

21.1.3 使用SUMPRODUCT函数编辑绩效考核公式

21.2 根据时间段业务量考核绩效

21.2.1 案例背景

21.2.2 数据准备

1．提取时间数据

2．设置辅助列

21.2.3 编制统计不同时间段、不同金额档次的公式

1．设计汇总表结构

2．编写汇总公式

21.2.4 计算奖励

21.3 根据各店铺交易额均值考核绩效

21.3.1 案例背景

21.3.2 计算每日收入与收入达成率

21.3.3 店铺排名并计算奖励