Excel财务达人精进之路 从小白到高手PDF电子书下载

CHAPTER 01 你真会用Excel吗 1

1.1 十年茫茫不思量，Excel让我癫又狂 1

1.1.1 一张烂表，让我痛不欲生 1

1.1.2 没有技能，让我事倍功半 3

1.1.3 没有思路，让我前功尽弃 5

1.1.4 没说服力，让我丧失很多机会 5

1.2 痛定思痛：从今天重新开始 8

CHAPTER 02 打好基础，从设计表格开始 9

2.1 正确区分Excel两个表格 9

2.1.1 基础表单 9

2.1.1 分析报告 10

2.1.3 关于分析底稿 10

2.1.4 基础表单的设计原则 11

2.2 乱用Excel十三大罪 12

2.2.1 设计的表单无逻辑 12

2.2.2 大而全的表格 13

2.2.3 用二维表格管理数据 15

2.2.4 行标题和列标题使用合并单元格 15

2.2.5 表单中插入小计行 16

2.2.6 表单中插入大量的空行空列 16

2.2.7 在表单数据区域外输入其他无关的数据 16

2.2.8 不同类型的数据保存在一列 17

2.2.9 数字和单位写在一起 17

2.2.10 名称文字中强制添加空格 17

2.2.11 输入非法日期 17

2.2.12 不区分文本型数字和纯数字 18

2.2.13 在基础表单添加不必要的计算列 18

2.3 设计科学规范财务表单实战练习：资金管理 18

2.3.1 绘制逻辑架构图 19

2.3.2 设计银行账号资料表 19

2.3.3 设计项目资料管理表 20

2.3.4 设计期初余额表 20

2.3.5 设计资金管理台账 20

2.4 集团下属子公司表单的格式统一化 22

2.4.1 结构完全固化的表单模板 23

2.4.2 一维数据表单模板 23

2.4.3 建立共享工作簿 24

CHAPTER 03 谈笑间，整理好财务表单 25

3.1 清理数据中的垃圾 25

3.1.1 清除数据中的空格 25

3.1.2 清除数据中的回车符 25

3.1.3 清除眼睛看不见的特殊字符 26

3.1.4 清除数据中的星号 27

3.1.5 清除表格中的图形对象 28

3.2 修改非法日期 28

3.2.1 使用分列工具快速修改非法日期 29

3.2.2 使用函数处理特殊格式的非法日期 30

3.2.3 输入日期的几个小技巧 31

3.3.4 设置日期格式技巧 31

3.2.5 过去、现在和将来：常用日期函数及其应用 33

3.3 转换数字格式 36

3.3.1 将数字转换为原位数的文本型数字 37

3.3.2 将数字转换为固定位数的文本型数字，位数不够就补0 37

3.3.3 将文本型数字转换为纯数字的5大技法 37

3.3.4 容易混淆的概念：单元格和数据 39

3.4 快速填充空单元格 39

3.4.1 快速填充上一行数据 39

3.4.2 快速填充下一行数据 41

3.4.3 快速往空单元格填充数字0 41

3.4.4 快速取消合并单元格并填充数据 42

3.5 批量修改数据 43

3.5.1 批量修改全部单元格数据 43

3.5.2 批量修改部分单元格数据 43

3.5.3 复杂条件下的批量修改数据 44

3.6 数据重新排列 44

3.6.1 将二维表格转换为一维表格：数据透视表方法 44

3.6.2 将二维表格转换为一维表格：Power Query方法 46

3.6.3 行列互转 48

3.6.4 将多列名单重新排列成一列 48

3.6.5 依数据个数重新排列成一列 48

3.7 数据快速分列 49

3.7.1 分列科目编码和科目名称：根据特殊符号 49

3.7.2 分列科目编码和科目名称：根据常规符号 50

3.7.3 分列科目编码和中文科目名称：利用函数 51

3.7.4 分列科目编码和英文科目名称：利用函数 52

3.7.5 提取内部支票号：利用函数 52

3.7.6 分列规格、数量和单位：利用函数 53

3.7.7 含有换行符的数据分列 55

3.7.8 指纹考勤数据分列 55

3.7.9 文本文件的导入与分列 55

3.8 数据快速合并 57

3.8.1 使用简单的连接符（＆）合并数据 57

3.8.2 使用CONCATENATE函数合并数据 57

3.8.3 使用CONCAT函数合并数据 58

3.8.4 使用TEXTJOIN函数合并数据 58

3.8.5 使用快速填充工具合并数据 58

3.9 处理表格中的垃圾行 58

3.9.1 快速删除表单中的空行 59

3.9.2 快速删除小计行 59

3.10 处理重复数据 59

3.10.1 在一列中查找重复数据 60

3.10.2 在一列中跟踪计算每个数的重复次数 60

3.10.3 从多列数据中查找重复行数据 61

3.10.4 删除数据清单中的重复数据 62

3.10.5 处理重复的考勤刷卡数据 62

3.10.6 从一列数据中获取不重复数据列表 63

3.10.7 从多个表格中获取不重复数据列表 63

3.10.8 从两个表格中查找都存在的数据 67

3.10.9 只查找某个表格存在的数据 67

CHAPTER 04 只需要一分钟，轻松搞定数据核对 68

4.1 在一个工作表中查找非法数据 68

4.1.1 使用数据验证圈释无效数据 68

4.1.2 使用条件格式标识非法数据 69

4.2 从两个表格中查找不一样的数据 69

4.2.1 单条件核对：使用函数 70

4.2.2 单条件核对：使用透视表 70

4.2.3 多条件核对：使用函数 72

4.2.4 多条件核对：使用SQL＋透视表 73

4.2.5 多条件核对：使用Power Query＋Power Pivot 77

4.3 核对数据实际应用案例精选 80

4.3.1 实际案例1：核对社保 80

4.3.2 实际案例2：核对公积金和缴费基数 82

4.3.3 实际案例3：核对个税 82

4.3.4 实际案例4：核对增值税 83

4.3.5 实际案例5：核对内部往来账 84

4.3.6 实际案例6：银行对账 85

4.3.7 实际案例7：税金核销 88

4.3.8 核对总公司与分公司开票和未开票 90

4.3.9 核对应付余额 93

4.3.10 核对网银日期和财务日期 94

4.3.11 核对总表和明细表 95

4.3.12 核对收入和发票 96

4.3.13 核对产量和工时 97

4.3.14 核对物料数量 99

4.3.15 核对Excel凭证设置与系统凭证设置 99

4.3.16 核对报关资料 100

4.3.17 核对发货和收货 102

4.4 核对数据总结 102

4.4.1 核对数据的核心及主要技能 103

4.4.2 使用条件格式自动标注不一样的数据 103

CHAPTER 05 练好基本技能，高效处理表单数据 104

5.1 筛选，把满足条件的数据快速找出来 104

5.1.1 筛选的本质 104

5.1.2 筛选不重复记录 105

5.1.3 删除重复记录 105

5.1.4 将筛选结果复制到其他工作表 105

5.1.5 指定任意复杂条件下的高级筛选 105

5.1.6 对数字数据进行特殊筛选 107

5.1.7 对日期数据进行特殊筛选 107

5.1.8 对文本数据进行特殊筛选 107

5.1.9 按颜色进行筛选 108

5.1.10 只修改筛选出来的数据 108

5.1.11 只修改筛选出来单元格的公式 108

5.1.12 在筛选状态下自动显示连续序号 109

5.2 排序很简单，但也很奇妙 110

5.2.1 排序的规则 110

5.2.2 排序的正确方法 110

5.2.3 按照笔划排序 111

5.2.4 对多个关键字排序 111

5.2.5 自定义排序 111

5.2.6 根据单元格颜色进行排序 113

5.2.7 利用排序函数进行排序 113

5.2.8 先排序再恢复原始状态 114

5.3 创建分类汇总和分级显示，让大型表格结构更加清晰 115

5.3.1 对分类汇总工具的看法 115

5.3.2 自动创建地区和月份的分级显示 115

5.3.3 手动组合创建总账科目和明细科目的分级显示 116

5.3.4 复制分类汇总和分级显示数据 117

5.3.5 取消分类汇总和分级显示 118

5.4 很多人忽略的智能表格：快速管理表格和分析数据 118

5.4.1 创建智能表格 118

5.4.2 智能表格的格式化 119

5.4.3 手工调整表格大小 119

5.4.4 为表格添加列、插入行 120

5.4.5 删除表格的列或行 121

5.4.6 为表格添加汇总行 121

5.4.7 使用切片器快速筛选数据 122

5.4.8 使用快速分析工具箱来分析表格 123

5.4.9 将表格作为数据模型使用 123

5.5 把错误消灭在萌芽：利用数据验证控制规范数据输入 124

5.5.1 数据验证的设置方法 124

5.5.2 控制规范日期输入 125

5.5.3 控制规范输入数字 126

5.5.4 只能输入不超过两位小数的数字 126

5.5.5 在单元格设计下拉菜单，快速选择输入数据 126

5.5.6 在单元格设计二级下拉菜单，快速选择指定项目下的数据 128

5.5.7 在一列里限制不能输入重复数据 129

5.5.8 在多列里不允许输入重复数据 130

5.5.9 不能输入另外一个工作表已有的数据 131

5.5.10 只有在上一行数据全部输入后，才能输入下一行数据 132

5.6 熟练运用分列工具，让数据整理不再消耗大好时光 132

5.6.1 根据一个符号分列 133

5.6.2 根据多个符号分列 134

5.6.3 根据指定的特殊符号分列 134

5.6.4 根据固定宽度对数据进行分列：手工插入分列线 135

5.6.5 根据固定宽度对数据进行分列：自动插入分列线 136

5.6.6 把一个单元格的科目名称和部门名称分成两列 137

5.6.7 把一列发生额分列成借方和贷方 139

5.6.8 利用分列工具转换列数据格式 139

5.7 数据查询中的“汽车”：Microsoft Query 140

5.7.1 从当前工作簿中查询数据 140

5.7.2 从其他工作簿中查询数据 143

5.7.3 从文本文件中查询数据 144

5.7.4 从数据库中查询数据 146

5.7.5 查找两个表都存在的数据 146

5.8 数据查询中的“高铁”：Power Query 148

5.8.1 Power Query在哪里 148

5.8.2 从当前工作簿的一个工作表中查询数据 148

5.8.3 从当前工作簿的多个工作表中查询数据 151

5.8.4 从其他一个工作簿中查询数据 153

5.8.5 从其他多个工作簿中查询数据：每个工作簿只有一个工作表 154

5.8.6 从其他多个工作簿中查询数据：每个工作簿有多个工作表 160

5.8.7 从两个表格中查找都存在的数据 162

5.8.8 只查找存在于某个表的数据 166

5.8.9 从文本文件中查询数据 167

5.8.10 从一个工作表中查询并完成汇总计算 168

5.8.11 从多个工作表中查询并完成汇总计算 172

5.8.12 从多个工作簿中查询并完成汇总计算 172

5.8.13 综合练习：两年客户流动分析 174

CHAPTER 06 不会函数公式，怎么做财务管理和财务分析 183

6.1 必须了解和掌握的公式基础知识 184

6.1.1 什么是公式 184

6.1.2 公式的元素 184

6.1.3 公式的运算符 185

6.1.4 公式运算符的优先顺序 185

6.1.5 单元格的引用：相对引用与绝对引用 186

6.1.6 公式返回的错误信息 188

6.1.7 复制和移动公式 188

6.1.8 用F9键查看公式的计算结果 190

6.1.9 用公式求值来检查公式 190

6.1.10 将公式转换为值 190

6.1.11 隐藏、显示和保护公式 190

6.1.12 将公式分行输入，以便使公式更加容易理解和查看 192

6.1.13 在公式表达式中插入空格，编辑起来更加方便 192

6.1.14 公式和函数中的字母不区分大小写 192

6.1.15 公式和函数中的标点符号都必须是半角字符 192

6.2 必须了解和掌握的函数基础知识 193

6.2.1 什么是函数 193

6.2.2 函数的基本语法 193

6.2.3 函数参数的类型 193

6.2.4 养成输入函数的好习惯 194

6.2.5 函数的种类 195

6.2.6 在单元格快速输入函数的技巧 196

6.3 创建嵌套函数公式必须掌握的技能 196

6.3.1 逻辑思路，是函数公式的核心，也是Excel的核心 196

6.3.2 学会绘制逻辑思路图 197

6.3.3 输入嵌套函数技能之一：分解综合法 199

6.3.4 输入嵌套函数技能之二：函数对话框＋名称框法，单流程嵌套IF公式 201

6.3.5 输入嵌套函数技能之三：函数对话框＋名称框法，多流程嵌套IF公式 202

6.3.6 输入嵌套函数技能之四：函数对话框＋名称框法，不同函数的嵌套公式 205

6.4 使用名称提高公式效率，解决复杂的计算问题 206

6.4.1 什么是名称 206

6.4.2 定义名称的规则 207

6.4.3 定义名称的四种方法 207

6.4.4 编辑、修改和删除名称 208

6.4.5 应用案例1：在单元格制作二级下拉菜单 208

6.4.6 应用案例2：制作动态数据源的数据透视表 208

6.4.7 应用案例3：根据数据增减自动调整的图表 210

6.4.8 应用案例4：从不同区域获取数据绘制动态图表 211

6.4.9 名称总结 212

6.5 数组公式尽管不好理解，但了解下是必要的 212

6.5.1 从一个简单的例子说起：数组公式的简单应用 213

6.5.2 数组的基本概念 214

6.5.3 如何输入数组公式 215

6.5.4 数组公式应用举例之一：查找每个人的最新职称及其所取得的日期 216

6.5.5 数组公式应用举例之二：获取每个类别中销售额最大的前5个产品 217

6.5.6 数组公式应用举例之三：计算每个材料的最新单价和平均单价 218

6.5.7 数组公式应用举例之四：查找办理事宜的最新状况 219

6.5.8 数组公式应用举例之五：多条件数据查找 219

6.6 了解和使用条件表达式 220

6.6.1 条件表达式的基本概念 220

6.6.2 使用条件表达式创建数组 220

6.6.3 条件表达式的几个注意事项 222

6.7 千难万难，无非就是逻辑判断 222

6.7.1 IF函数基本应用 222

6.7.2 IF函数嵌套应用 223

6.7.3 使用AND函数组合判断 223

6.7.4 使用OR函数联合判断 223

6.7.5 使用数组简化OR 224

6.7.6 联合使用IF、AND、OR函数判断 224

6.7.7 神奇的IF（｛1，0｝） 224

6.8 分类汇总，其实5个函数就足够了 225

6.8.1 最简单的无条件计数 226

6.8.2 COUNTIF：单条件计数 226

6.8.3 COUNTIFS：多条件计数 228

6.8.4 SUMIF：单条件求和 230

6.8.5 SUMIFS：多条件求和 234

6.8.6 SUMPRODUCT：功能强大的汇总函数 237

6.9 寻寻觅觅，离不开五虎上将沃迈欧VMIIO 242

6.9.1 VLOOKUP函数：打仗最多的一员大将 243

6.9.2 MATCH和INDEX函数：上阵亲兄弟 250

6.9.3 INDIRECT函数：借力打力，千里奔袭 254

6.9.4 OFFSET函数：迂回作战，无处藏身 259

6.9.5 简单聊一聊LOOKUP：不常用但又很有气势 262

6.10 犯错不要紧，纠正就可以 266

6.10.1 IFERROR函数：屏蔽错误值 266

6.10.2 ISERROR函数：仅仅判断是否错误 266

6.10.3 NA函数：专为犯错而来 267

6.11 分分合合，寻寻觅觅，好好使用几个文本函数 268

6.11.1 从文本字符串中截取字符 268

6.11.2 把几个字符串连接成一个新字符串 269

6.11.3 从文本字符串中查找指定字符的位置 269

6.11.4 替换字符串中指定的字符 270

6.11.5 把数字和日期转换为指定格式的文本 271

6.11.6 文本函数综合练习 272

6.12 过日子离不开的日期函数 272

6.12.1 获取当天日期 272

6.12.2 计算指定期限之前或之后的日期 272

6.12.3 计算两个日期之间的期限 273

6.12.4 将年月日三个数字组合成日期 273

6.12.5 将日期拆分成年月日三个数字 273

6.12.6 判断日期是星期几 273

6.12.7 判断日期是本年度第几周 273

6.13 排名分析 274

6.13.1 LARGE函数：降序排序 274

6.13.2 SMALL函数：升序排序 274

6.13.3 排序后如何匹配对应名称 274

6.13.4 出现相同数据排序时匹配名称的处理技巧 275

6.13.5 综合应用：客户排名分析 276

6.14 别忘了其他几个小兄弟 278

6.14.1 CELL函数：获取工作簿信息 279

6.14.2 CELL函数：获取当前工作表名称 279

6.14.3 CELL函数：快速从资金流水账中制作每个账户的明细表 279

6.14.4 CELL函数：制作动态累计汇总 281

6.14.5 CHAR函数：让分析图表信息更加丰富 282

6.14.6 N函数：让不能计算的单元格能够计算 282

6.14.7 ABS函数：有时候是要计算绝对值的 283

CHAPTER 07 数据透视表：海量财务经营数据分析的神器 284

7.1 不是什么表格都可以制作透视表的：先做好准备 285

7.1.1 规范表单结构 285

7.1.2 规范表单数据 285

7.2 创建数据透视表的常用方法：场合不同，方法不同 286

7.2.1 以当前工作簿的一个一维工作表创建：最简单 286

小知识：数据透视表结构及布局方法 287

7.2.2 以当前工作簿的多个一维工作表创建：SQL还是PP 288

小知识：SQL简单知识——SELECT语句 291

7.2.3 以当前工作簿的多个二维工作表创建：很简单，也要留心 297

7.2.4 以其他工作簿的一个一维工作表创建 301

7.2.5 以其他工作簿的多个一维工作表创建 302

7.2.6 以多个工作簿的二维工作表创建 302

7.2.7 以多个有关联的工作表创建：Microsoft Query方法 308

7.2.8 以多个有关联的工作表创建：Power Query＋Power Pivot方法 311

7.2.9 以工作表的部分数据创建 311

7.3 数据透视表的美化：丑小鸭变成小天鹅 312

7.3.1 设置数据透视表样式 313

7.3.2 设置报表布局 314

7.3.3 修改字段名称 315

7.3.4 显示／取消分类汇总 315

7.3.5 显示／取消报表总计 316

7.3.6 合并标签单元格 316

7.3.7 调整字段下的项目次序 317

7.3.8 设置数字格式 318

7.4 利用透视表分析数据：这才是最终目的 318

7.4.1 改变值字段的汇总依据，制作多种计算结果的报告 318

7.4.2 改变值字段的显示方式，制作结构分析报告 319

7.4.3 组合日期，制作年报季报月报 320

7.4.4 排序筛选，找出前十大客户 321

7.4.5 绘制透视图，让分析结果可视化 321

7.4.6 插入切片器，建立自己的BI模板 322

7.4.7 别再笨笨地在原始表里插入计算列了，创建计算字段和计算项吧 324

7.4.8 用日程表筛选报表 326

7.5 一个简单的命令，快速制作各个项目的明细表 326

7.5.1 每次制作一个项目的明细表 326

7.5.2 批量制作所有项目的明细表 327

CHAPTER 08 就这么高效：几个步骤完成大量表格合并汇总 329

8.1 结构完全相同工作表的合并：最简单的工具 329

8.1.1 SUM函数：直接将结构相同的工作表合并 329

8.1.2 合并计算：得到具有分级显示的合并表 330

8.2 数据透视表：更加灵活的合并汇总 332

8.2.1 二维表格合并：多重合并计算数据区域透视表 332

8.2.2 一维表格合并：现有连接＋SQL语句 332

8.3 Power Query：大量工作表或工作簿合并汇总的最高效方法 333

8.3.1 合并当前工作簿里的多个工作表 333

8.3.2 一个文件夹里的多个工作簿，每个工作簿仅有一个工作表 334

8.3.3 一个文件夹里的多个工作簿，每个工作簿有多个工作表 339

8.4 建立滚动汇总模板 339

CHAPTER 09 一目了然的报表，令人信赖的颜值 340

9.1 报表的整体布局 340

9.1.1 显示位置：左侧和顶部留空 340

9.1.2 边框设置和不显示网格线 341

9.1.3 行高与列宽 342

9.1.4 字体和字号设置 342

9.1.5 数字格式 342

9.1.6 文字往右缩进 343

9.1.7 背景颜色 343

9.1.8 别忘了在顶部写单位 343

9.2 自动标注需要关注的数字：自定义数字格式 344

9.2.1 设置自定义数字格式的基本方法 344

9.2.2 数字自定义格式代码结构 344

9.2.3 缩小位数显示数字 345

9.2.4 将数字显示为指定的颜色，并添加标识符号 345

9.2.5 使用条件判断设置数字自定义格式 347

9.3 自动标注重要数据：条件格式 347

9.3.1 进度条：标注呈现不同变化的数据 347

9.3.2 图标集：标准上升／下降和红绿灯效果 348

9.3.3 建立提前提醒模型，让数据追踪自动化 349

9.3.4 自动美化表格，不用手工来调整格式 351

9.3.5 使用公式来做条件格式的重要注意事项 351

CHAPTER 10 一图抵万言：数据分析结果可视化 353

10.1 图表到底是什么 353

10.1.1 对图表的困惑 353

10.1.2 实际案例剖析 354

10.1.3 数据充满思想，图表充满思考 356

10.2 从阅读表格入手 356

10.2.1 阅读表格是基本功 356

10.2.2 案例剖析之一 356

10.2.3 案例剖析之二 356

10.2.4 案例剖析之三 357

10.3 绘制图表的基本方法 358

10.3.1 利用固定数据区域绘图 358

10.3.2 利用定义的名称绘图 359

10.3.3 为图表添加新数据系列 360

10.3.4 修改数据系列 361

10.3.5 删除数据系列 361

10.3.6 设置数据系列的坐标轴 361

10.3.7 设置数据系列的图表类型 362

10.3.8 图表的保存位置 363

10.3.9 复制图表 363

10.3.10 删除图表 363

10.4 格式化图表 363

10.4.1 图表结构及主要元素 364

10.4.2 为图表添加元素 366

10.4.3 选择图表元素的方法 366

10.4.4 设置图表元素格式的工具 367

10.4.5 格式化图表区 367

10.4.6 格式化绘图区 368

10.4.7 格式化坐标轴 368

10.4.8 格式化图表标题、坐标轴标题、图例 368

10.4.9 格式化网格线 368

10.4.10 格式化数据系列 369

10.4.11 格式化数据标签 369

10.4.12 突出标识图表的重点信息 370

10.4.13 让图表元素显示为单元格数据 370

10.4.14 让数据点显示为形状 370

10.4.15 简单是美 370

10.5 常见财务分析图表及其基本应用案例 370

10.5.1 柱形图及其应用 370

10.5.2 条形图及其应用 371

10.5.3 折线图及其应用 373

10.5.4 XY散点图及其应用 374

10.5.5 饼图及其应用 375

10.5.6 圆环图及其应用 376

10..5.7 面积图及其应用 378

10.5.8 雷达图及其应用 379

10.5.9 气泡图及其应用 380

10.5.10 瀑布图（步行图）及其应用 382

10.5.11 目标完成仪表盘及其应用 383

10.5.12 预算差异跟踪分析图 386

10.6 用动态图表灵活分析数据 387

10.6.1 绘制动态图表的基本原理 387

10.6.2 绘制动态图表的辅助区域方法 388

10.6.3 绘制动态图表的动态名称方法 389

10.6.4 常用表单控件的使用方法 392

CHAPTER 11 构建高效财务分析模板：综合练习 396

11.1 实际案例1：两年销售同比分析 396

11.1.1 示例数据 396

11.1.2 制作分析底稿 396

11.1.3 两年销售额、销售成本和毛利整体分析 397

11.1.4 两年销售额、销售成本和毛利的月度同比分析 398

11.1.5 分析企业销售额同比增长原因：产品影响 399

11.1.6 分析指定产品销售额同比增长原因：量价分析 399

11.1.7 分析企业毛利同比增长原因：产品影响 401

11.1.8 分析指定产品毛利同比增长原因：销量-单价-成本分析 402

11.1.9 客户排名分析 404

11.1.10 分析客户流动：新增客户 405

11.1.11 分析客户流动：流失客户 405

11.1.12 分析客户流动：存量客户 406

11.2 实际案例2：利润表跟踪分析 406

11.2.1 获取各月利润表数据 406

11.2.2 滚动汇总分析各月的利润表数据 406

11.2.3 利润表各个项目的趋势数据 407

11.2.4 某个月净利润影响因素分析 408

CHAPTER 12 效率，来自于日常养成的好习惯 410

12.1 关于基础表单设计及数据维护 410

12.2 关于分析报表 410

12.3 关于日常数据整理与加工 411

12.4 关于输入函数和创建公式 412