CHAPTER01当管理会计遇上Excel1
1.1管理会计与Excel的不了情...............................................................................2
1.1.1当会计遇上管理所迸发的火花.............................................................................2
1.1.2管理会计？还是财务会计.....................................................................................3
1.1.3与君初相识，蹒跚初学步.....................................................................................3
1.2至快至强，快人一步..........................................................................................7
1.2.1快入——从源头开始的快速输入..........................................................................7
1.2.2快选——单元格的快速选择...............................................................................10
1.2.3快找——单元格的快速定位与查找替换.............................................................12
1.3去粗存精，去伪存真........................................................................................14
1.3.1查与删——显现和成批删除重复项....................................................................14
1.3.2禁与输——重复项目的禁止和批量输入.............................................................16
1.3.3一起找不同——双工作簿比较...........................................................................17
1.3.4快乐修理工——不规则文本的修整与转换.........................................................20
1.3.5快乐修理工——日期和时间的整理与转换.........................................................21
1.3.6快乐修理工——合并单元格的反合并.................................................................21
1.4批量处理，事半功倍........................................................................................22
1.4.1批量单元格——多单元格区域和整行整列.........................................................22
1.4.2批量单元格——Ctrl_F快算月度小计与年度累计...............................................24
1.4.3批量工作表——多表填充、清除、汇总与合并计算...........................................25
1.4.4批量工作簿——开关、打印和透视....................................................................27
1.5不忘初心，学无止境........................................................................................27
1.5.1让你的管理会计在Excel中666........................................................................27
1.5.2玩转Excel，为自己疯狂打CALL吧..................................................................30
CHAPTER02集团营销管理大会的筹办31
2.1办公室来了个小白............................................................................................32
2.1.1小公”举”的芳龄.............................................................................................32
2.1.2办公室的这群人.................................................................................................33
2.2新人到岗的头把火............................................................................................33
2.2.1着急的小吴........................................................................................................33
2.2.2公司的销售新政.................................................................................................34
2.2.3让小白更明白.....................................................................................................35
2.3财务视角的销售管理........................................................................................38
2.3.1营销管理、销售管理和销售业绩管理.................................................................38
2.3.2财务管理推动销售管理......................................................................................38
2.4一行公式搞定业绩提成....................................................................................39
2.4.1实现自动查找的LOOKUP、VLOOKUP和HLOOKUP函数.............................39
2.4.2用数组函数解决累进加成的业绩计算.................................................................42
2.4.3用TEXT函数进行业绩提成计算修正................................................................42
2.5用VLOOKUP等函数实现跨文件取数并取得机械版块全部提成......................45
2.5.1原样复制表格的两种方法...................................................................................45
2.5.2用VLOOKUP函数从关联文件中自动取值........................................................47
2.5.3找回Excel的实心黑十字光标............................................................................50
2.5.4嵌套CHOOSE函数自动分配业绩任务数..........................................................51
2.5.5简单的SUM函数求得完整的机械版块业绩提成................................................52
2.6用函数实现多家二级公司明细汇总..................................................................53
2.6.1INDIRECT函数和CELL函数初接触.................................................................53
2.6.2待汇总文件路径、名称及单元格分析.................................................................55
2.6.3INDIRECT函数和CELL函数在业绩汇总中的综合运用....................................55
2.7数据透视表生成之基于当前工作表指定区域....................................................57
2.7.1基于当前工作表指定区域快速生成数据透视表..................................................57
2.7.2美化和调整数据透视表......................................................................................58
2.7.3弹指间，多维度多角度的业绩分析了然于胸......................................................61
2.7.4数据透视表汇总的那些事儿...............................................................................62
2.7.5从数据透视表中速查明细...................................................................................64
2.7.6批量修改数据透视表求和等多种统计方式.........................................................64
2.8数据透视表生成之基于当前工作表动态区域....................................................65
2.8.1移动的矩形块：OFFSET函数............................................................................65
2.8.2非空单元格统计者：COUNTA函数....................................................................66
2.8.3新建一个动态区域名称......................................................................................66
2.8.4基于当前工作表动态区域名称快速生成数据透视表...........................................67
2.8.5检验新增销售明细后动态数据源数据透视表的变化...........................................67
2.9静享下午茶时光：多重区域拼合行数列数都不同的多表..................................68
2.9.1瞧这一家人一天的开支......................................................................................68
2.9.2将数据透视表向导找出来...................................................................................69
2.9.3利用数据透视表向导生成多重区域餐饮透视表..................................................69
2.10数据透视表生成之基于同一工作簿文件的不同工作表....................................71
2.10.1初识简单的SQL语句......................................................................................71
2.10.2西南区销售明细多表合一的SQL语句.............................................................72
2.10.3多表的各列的个数不同、顺序不同时的SQL语句...........................................73
2.10.4新建数据连接生成基于同一工作簿多工作表的数据透视表..............................73
2.10.5利用现有的数据连接生成基于多工作表的数据透视表.....................................77
2.10.6编辑和删除现有的数据透视表数据连接...........................................................77
2.11数据透视表生成之基于不同工作簿文件不同工作表.......................................78
2.11.1进一步整理的SQL语句...................................................................................78
2.11.2新建数据连接制作来源于多工作簿、多工作表的明细清单..............................80
2.11.3利用现有的数据连接动态生成基于多工作簿的明细清单..................................81
2.11.4新建数据连接生成基于多工作簿、多工作表的数据透视表..............................82
2.12锦上添花：让数据透视表更具创意性.............................................................83
2.12.1无中生有之横向插入新计算字段......................................................................84
2.12.2无中生有之横向插入新计算项.........................................................................85
2.12.3字段组合之用纵向、横向组合分组来分析存货季度性销售..............................86
2.12.4字段组合之用金额分区分析销售大单的频次....................................................88
2.12.5字段组合之插入组合字段分类汇总并显示在顶部或底部..................................89
2.12.6分级显示之那些”父辈”们的事儿..................................................................90
2.12.7数据透视表之三”生”三”示”、十里桃花.....................................................92
CHAPTER03给集团生产总监送去的头疼解药97
3.1开足马力抓生产...............................................................................................98
3.1.1易总布置的新任务.............................................................................................98
3.1.2生产控制和成本预测、决策的财务知识.............................................................98
3.2用SQL语句和函数取得历年有效订单并预测其增长.......................................99
3.2.1新年新布局，获取历年有效订单总数.................................................................99
3.2.2巧妙选中数据透视表不连续的区域..................................................................104
3.2.3利用SLOPE函数和INTERCEPT函数取得订单增长的规律...........................104
3.2.4利用FORECAST函数预测订单增长数并验证................................................106
3.3用直观靓丽的图表进行历年订单趋势预测......................................................106
3.3.1美美的Excel订单预测图形.............................................................................106
3.3.2利用图表工具插入订单趋势图.........................................................................108
3.3.3添加和编辑趋势图的数据系列.........................................................................109
3.3.4让订单趋势图更加赏心悦目的技巧...................................................................111
3.4用扩展数组和条件求和函数搞定货品订单预测和分周序时进度......................113
3.4.1SUMIF函数和SUMIFS函数............................................................................113
3.4.2SUMIFS函数在订单预测中的实际应用............................................................113
3.4.3SUMPRODUCT函数和WEEKNUM函数........................................................115
3.4.4神秘的IF({1}..和IF({1,0}.................................................................................116
3.4.5利用IF({1,0}..和SUMIFS找出订单量最多和最少的城市.................................117
3.4.6函数、SQL语句和数组在序时生产订单进度中的综合运用.............................120
3.5用内嵌的迷你图和条件格式强化分周序时进度和畅销品分析.........................123
3.5.1利用迷你图和数据条的条件格式嵌入炫酷的序时订单图..................................123
3.5.2设置条件格式的管理规则让每个内嵌图表都听话.............................................126
3.5.3利用TRANSPOSE函数转置可更新的单元格区域..........................................127
3.5.4利用条件格式的管理规则找出滞销和畅销的单品.............................................128
3.5.5利用马尔柯夫法预测单品滞销和畅销互转的概率.............................................129
3.6用多种方法预测未来各单品成本发展水平和质量控制....................................131
3.6.1VLOOKUP函数也能玩逆向查找.....................................................................131
3.6.2”两点法”力挺”单”品总成本........................................................................132
3.6.3”二乘法”劲怼”单”品总成本........................................................................133
3.6.4”回归”分析工具也和单品总成本”铆”上了..................................................135
3.6.5”散点图”探索单品维修费与工时的内在逻辑..................................................138
3.6.6”帕累托图”掌控产品品质分析........................................................................140
CHAPTER04钱从哪里来145
4.1用Excel模板预测资金需求量........................................................................146
4.1.1寻求具有操作性的现金流预测方法..................................................................146
4.1.2用销售百分比法预测资金需求量......................................................................147
4.1.3用销售百分比法预测资金需求增量..................................................................148
4.1.4用回归分析法预测资金需求量.........................................................................150
4.2用模拟分析测试不同利率和期限下的企业贷款偿还能力................................151
4.2.1运用PMT函数测算固定利率、固定期限下的月度还款金额和本息之和..........152
4.2.2运用方案管理器比较不同利率、不同期限的多种融资方案..............................153
4.2.3运用模拟运算表的二维模拟运算测算多种利率、多种期限下的偿还能力.........155
4.2.4运用色阶让月度偿还利息负担更具明确的等级性和警示性..............................157
4.2.5运用单变量求解测算固定偿还能力下的可贷额度.............................................158
4.3用表单控件实时计算不同贷款额度和利率区间下的利息支付.........................159
4.3.1表单控件（窗体控件）的工作原理..................................................................160
4.3.2认识活泼的小伙伴们——常见表单控件...........................................................160
4.3.3让表单控件的小伙伴和单元格在贷款测算的舞台上一起”嗨”.........................166
4.4两个等额，哪个更划算..................................................................................169
4.4.1IPMT函数与PPMT函数.................................................................................169
4.4.2等额本息与等额本金与IPMT函数与PPMT函数的结合运用..........................170
4.4.3用数据条凸显对比各期贷款成本和差异分析....................................................172
4.4.4贷款期间遇到利率调整的应对方法..................................................................173
4.4.5等额本息与等额本金对比计算表的通用性扩展................................................173
4.5组合贷款在规划求解下的巧妙设计................................................................174
4.5.1资金的时间价值、贴现与PV函数...................................................................174
4.5.2运用规划求解工具搞定组合贷款的期限设计....................................................175
4.5.3运用规划求解工具测算组合贷款的贷款利率设计.............................................181
4.5.4运用规划求解工具搞定组合贷款的贷款额度分配.............................................183
4.6用Excel模板助力多种模式下的公司债券发行...............................................186
4.6.1企业债券发行的定价策略和IRR、NPV函数...................................................186
4.6.2分期付息发行模式下的溢价发行定价与内在报酬率.........................................188
4.6.3分期付息发行模式下的折价发行定价与内在报酬率.........................................191
4.6.4一次性还本付息发行模式下的溢价发行定价与内在报酬率..............................192
4.6.5一次还本付息发行模式下的折价发行定价与内在报酬率..................................194
4.6.6用智能目录纵览多种发行模式下的发行定价与内在报酬率..............................196
4.7债权转股权（债转股）与股权融资的模型设计..............................................198
4.7.1股权融资与债务重组的点滴知识......................................................................198
4.7.2INDEX函数和MATCH函数............................................................................198
4.7.3债转股的Excel模板的构建.............................................................................201
4.7.4用”演变”工具完成债转股方案的行权时机推演.............................................202
4.7.5股权融资的定价模型设计.................................................................................204
4.8与股权相关Excel文档的美化设计、安全加固和发送....................................206
4.8.1股份结构及组织架构图的设计.........................................................................206
4.8.2股份结构及组织架构图的美化和插图Excel文件的”瘦身”.............................210
4.8.3用Excel玩截图及股东会议会址地图的抓取....................................................212
4.8.4将股权结构表等超过一屏的单元格区域截取为带行号列标的图形...................213
4.8.5对股东会议的Excel表格的五重保护...............................................................215
4.8.6用Excel发送PDF/XPS安全邮件...................................................................220
CHAPTER05花钱也是一门大学问223
5.1非贴现、贴现类投资决策模板的构建.............................................................224
5.1.1ISNUMBER函数与静态回收期投资决策模板构建...........................................224
5.1.2会计收益率投资决策模板的构建......................................................................226
5.1.3动态回收期投资决策模板构建.........................................................................227
5.1.4净现值法投资决策模板的构建.........................................................................228
5.1.5现值指数法投资决策模板的构建......................................................................229
5.1.6IRR法投资决策模板的构建.............................................................................230
5.1.7利用二进制约束搞定资金限量条件下的最优投资组合.....................................231
5.2投资敏感性分析.............................................................................................233
5.2.1对”敏感性”的分析........................................................................................233
5.2.2投资敏感性分析的要素....................................................................................235
5.2.3REPT与REPLACE(B)、SUBSTITUTE函数的悲喜剧...................................236
5.2.4投资敏感性分析模板的构建与ROW(1:r)魔术.................................................237
5.2.5投资敏感三要素的一维模拟运算......................................................................242
5.2.6用”雷达”快速扫描投资三要素的敏感性.......................................................247
5.2.7投资敏感三要素的敏感临界点测算..................................................................250
5.3用VBA代码来拓宽投资渠道.........................................................................252
5.3.1借由”一行代码”闯入VBA的殿堂................................................................253
5.3.2保存一个带有VBA代码的Excel文件.............................................................254
5.3.3VBA语言的三种算法结构、调用Excel公式与录制宏.....................................257
5.3.4设计带帮助的VBA自定义函数和过程.............................................................261
5.3.5让公式无所遁形的自定义追踪函数——WhoseCT函数...................................264
5.3.6使用快捷键让WhoseCT函数追踪公式...........................................................264
5.3.7创建个人加载宏并设置、修改其帮助和快捷键................................................266
5.3.8懒人的Excel公式追踪利器──”Excel公式一键显影软件”..........................268
5.4用自定义VBA函数助力投资要素的敏感分析................................................269
5.4.1多因素敏感对NPV的模板设计.......................................................................270
5.4.2利用NPV4ME函数计算NPV..........................................................................271
5.4.3多因素敏感对NPV的综合影响.......................................................................272
5.4.4单因素敏感对NPV的综合影响.......................................................................273
CHAPTER06资产管理”平衡”术275
6.1管理会计的”边际”........................................................................................276
6.1.1那些关于”边际”的管理”闲话”....................................................................276
6.1.2边际贡献与固定成本的”溢、缺、平”对比....................................................277
6.2零部件采购、产能利用与产品定价决策.........................................................277
6.2.1用量固定时的零部件外购、自制决策...............................................................278
6.2.2用量不确定时的零部件外购、自制决策...........................................................278
6.2.3无追加成本时新品投产的剩余产能利用...........................................................279
6.2.4需追加成本时新品投产的剩余产能利用...........................................................279
6.2.5以成本为导向的产品定价方法.........................................................................280
6.2.6以市场为导向的产品定价方法.........................................................................281
6.2.7量价关系求导的产品定价方法.........................................................................283
6.3存货经济订货量基础模型和扩展模型.............................................................284
6.3.1那些关于”经济”的管理”闲话”....................................................................284
6.3.2存货的取得与储存的成本闲话.........................................................................284
6.3.3经济订货量基础模型及动态验证......................................................................286
6.3.4允许缺货的经济订货量模型及动态验证...........................................................294
6.4资产管理盈亏临界点分析模型........................................................................297
6.4.1资产管理的量、本、利公式推导及其分析.......................................................297
6.4.2盈亏平衡模型的创建及结果解析......................................................................298
CHAPTER07用好家里的大件儿301
7.1固定资产管理的基础信息准备........................................................................302
7.1.1在同一列防止重复输入固定资产类别、状态等................................................302
7.1.2数据校验中的”忽略空值”究竟是个什么鬼....................................................304
7.2用VBA代码确保固定资产数据输入的快而准................................................305
7.2.1将动态数组赋值给数据校验中的公式...............................................................305
7.2.2设定的AciveX组合框引用基础信息................................................................306
7.3Excel内置的折旧函数与自定义累计折旧函数...............................................308
7.3.1折旧政策与SLN、VDB、DDB和SYD等折旧函数........................................308
7.3.2固定资产折旧的明细建档与累计折旧的depSYD和depDB函数.....................311
7.4固定资产自动化管理系统的构建....................................................................313
7.4.1自动创建固定资产管理卡与多折旧方法逐期比对表.........................................313
7.4.2自动生成固定资产折旧与累计折旧汇总报表....................................................315
7.4.3利用隔行汇总、公式转数值和快速空行整理折旧明细.....................................316
7.5固定资产更新决策与模型...............................................................................318
7.5.1新旧固定资产更新决策因素的分析..................................................................318
7.5.2搭建固定资产更新模型决策新资产是否投资....................................................319
CHAPTER08集团财务的报表自动汇总分析难题323
8.1报表公式设置及表间公式校验........................................................................324
8.1.1资产负债表报表原理及公式讲解......................................................................324
8.1.2利润表及利润分配表报表原理及公式讲解.......................................................326
8.1.3现金流量表报表原理及公式讲解......................................................................328
8.2利用代码实现下属机构的空白报表自动化下发..............................................332
8.2.1对下属分支机构整体规划.................................................................................332
8.2.2设计自动下发空白报表的VBA代码................................................................334
8.2.3下属分支机构填报报表与公式、数字混合列的清除.........................................335
8.2.4下属分支机构报表下发后的收回......................................................................337
8.3会计报表自动（实时）汇总系统的打造.........................................................338
8.3.1集团上报报表汇总的要求.................................................................................338
8.3.2多个报表汇总的解决方案分析.........................................................................338
8.3.3动态、可追溯的自动报表汇总系统设计...........................................................339
8.3.4自动报表汇总系统的汇总测试.........................................................................342
8.3.5自动报表汇总系统的修改与汇总数据清除.......................................................344
8.3.6自动报表汇总系统的数据备份.........................................................................345
8.4会计报表财务分析指标及自动化的实施.........................................................346
8.4.1以ROE为核心的财务分析的方法...................................................................346
8.