从小白到高手：Excel财务达人精进之路

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 将二维表格转换为一维表格：PowerQuery 方法 ............................................... 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 多条件核对：使用PowerQuery 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　数据查询中的汽车：MicrosoftQuery ............................................................. 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

....... 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 应