Excel在销售与财务管理中的应用PDF电子书下载

第1章 获取销售数据和财务数据 1

1.1 从Access数据库中获取数据 2

1.1.1 在Excel中导入Access数据库数据 2

1.1.2 在Excel中导入Access数据库数据并创建数据透视表 4

1.1.3 通过新建数据库查询将Access数据库数据导入到Excel工作表 5

1.1.4 通过复制粘贴方法将Access数据库数据复制到Excel工作表 8

1.1.5 通过Access的导出工具将Access数据库数据复制到Excel工作表 9

1.2 从SQL Server数据库中获取数据 11

1.2.1 利用复制粘贴方法获取SQL Server数据库数据 11

1.2.2 通过新建数据库查询法将SQL Server数据导入到Excel工作表 13

1.3 从文本文件中获取数据 14

1.3.1 直接将文本文件打开为Excel工作簿 15

1.3.2 使用导入数据工具将文本文件数据导入到Excel工作表 17

1.4 从其他工作簿中获取数据 17

1.4.1 利用复制粘贴法从其他工作簿中获取数据 17

1.4.2 利用导入数据法从其他工作簿中获取数据 17

1.5 汇总同一工作簿中各个工作表的数据 18

1.6 汇总不同工作簿中的数据 21

1.7 从互联网获取数据 21

1.7.1 建立Web查询 21

1.7.2 保存Web查询 23

1.7.3 运行Web查询 24

1.7.4 复制和粘贴静态数据 24

第2章 使用图表分析数据 25

2.1 创建图表的方法 26

2.1.1 快速创建销售图表 26

2.1.2 利用图表向导创建销售图表 26

2.2 图表的编辑与美化 28

2.2.1 设置坐标、标题、图例等的格式 28

2.2.2 改变图表大小 29

2.2.3 移动或复制图表 29

2.2.4 添加数据标志 29

2.3 为图表添加／删除系列 30

2.3.1 利用复制粘贴法为图表添加系列 30

2.3.2 利用添加系列法为图表添加系列 31

2.3.3 删除系列 31

2.4 在图表上显示特殊数据 32

2.4.1 在图表上显示最大销售量（额）和最小销售量（额） 32

2.4.2 在图表上显示平均销售量（额） 33

2.5 将销售额和销售量绘制在一张图表上 35

2.6 绘制动态图表 36

2.6.1 制作随销售数据变化而变化的图表 36

2.6.2 制作随部门变化而变化的图表 39

2.6.3 制作始终能反映最新几个数据的动态图表 40

2.6.4 制作可动态滚动和缩放的销售图表 43

2.7 对图表进行个性化处理 46

2.7.1 在图表上显示产品图片 46

2.7.2 制作半透明图表 48

2.7.3 制作颜色渐变图表 50

2.8 利用雷达图进行财务分析 50

2.8.1 雷达图的构成 51

2.8.2 财务指标的选取 51

2.8.3 绘制雷达图 52

2.9 打印图表 55

2.9.1 打印单张图表 56

2.9.2 打印多张图表 56

第3章 使用数据清单分析数据 58

3.1 使用数据清单应注意的事项 59

3.2 使用记录单输入数据 59

3.3 使用记录单编辑数据 60

3.3.1 使用记录单修改数据 60

3.3.2 使用记录单删除数据 60

3.3.3 使用记录单查找数据 60

3.4 在工作表中有大标题时如何使用记录单 60

第4章 对数据进行排序分析 62

4.1 数据排序的规则 63

4.2 数据排序的操作步骤 63

4.3 自定义排序 63

4.4 按姓氏笔画排序 65

4.5 先进行排序然后再恢复排序前的状态 65

4.6 按照单元格颜色或字体颜色进行排序 66

4.7 按照字符的长度进行排序 67

4.8 在不改变数据顺序情况下进行排序 67

4.9 筛选出最大销售额的前几个并进行排序 68

4.10 筛选出最小销售额的前几个并进行排序 69

第5章 对数据进行查找筛选和分类汇总 70

5.1 查找定位数据 71

5.1.1 使用通配符查找定位数据 71

5.1.2 使用条件格式快速查找定位数据 72

5.2 数据筛选 74

5.2.1 自动筛选 74

5.2.2 取消筛选 75

5.2.3 自定义筛选 75

5.2.4 高级筛选 76

5.2.5 计算条件下的高级筛选 77

5.2.6 将含有数据的行筛选出来 78

5.2.7 保存筛选结果 79

5.2.8 对筛选的结果进行汇总 80

5.3 数据分类汇总 82

5.3.1 创建分类汇总 82

5.3.2 撤销分类汇总 83

5.4 利用Excel 2003的列表工具分析数据 83

5.4.1 创建列表 83

5.4.2 利用列表分析数据 85

5.4.3 删除列表 85

5.5 数据的特殊处理 86

5.5.1 找出特殊数据，并用不同的颜色表示出来 86

5.5.2 找出两张工作表中都存在的行数据 88

5.5.3 找出仅存在一张工作表中的行数据 89

5.5.4 利用排序方法删除数据区域内的所有空行 90

5.5.5 利用自动筛选方法删除数据区域内的所有空行 92

5.5.6 利用高级筛选方法删除数据区域内的所有空行 93

5.5.7 利用VBA编程方法删除数据区域内的所有空行 94

5.5.8 利用VBA编程方法删除数据区域内的所有空列 94

5.5.9 利用自动筛选方法删除数据区域内的所有重复行数据 95

第6章 利用数据透视表和数据透视图分析数据 97

6.1 制作数据透视表和数据透视图的基本方法 98

6.1.1 制作数据透视表 98

6.1.2 数据的透视分析 101

6.1.3 数据更新 101

6.1.4 显示数据项的明细数据 101

6.1.5 制作数据透视图 102

6.2 从其他数据源制作数据透视表 103

6.2.1 利用其他工作簿数据制作数据透视表 103

6.2.2 利用数据库数据制作数据透视表 107

6.3 利用数据透视表对同一工作簿中各个工作表的销售数据进行汇总分析 107

6.3.1 创建一个带有公司页选择器的汇总表 108

6.3.2 创建一个带有公司页选择器及商品选择列表框的销售柱形图 112

6.4 利用数据透视表生成销售数据的时间序列 113

6.4.1 生成日销售量的时间序列 113

6.4.2 生成月销售量的时间序列 114

6.4.3 生成季度销售量的时间序列 115

6.4.4 生成年-季-月-日组合的销售量时间序列 115

6.4.5 生成销售总额时间序列数据 116

6.5 统计不同规模销售量的发生次数和频率分布 117

6.5.1 统计不同规模销售量的发生次数 117

6.5.2 统计不同规模销售量的频率分布 119

6.6 统计不同地区不同规模销售量的发生次数和频率分布 120

6.7 制作不同规模销售量频率分布图 121

6.8 利用数据透视表分析财务会计数据 122

6.8.1 利用数据透视表制作试算平衡表和科目汇总表 123

6.8.2 利用数据透视表制作明细分类账 127

6.8.3 利用数据透视表制作会计凭证查询系统 128

第7章 利用Excel的多维分析工具（OLAP）进行数据分析 130

7.1 创建基于OLAP多维数据集的数据透视表 131

7.2 使用基于OLAP多维数据集的数据透视表 137

第8章 利用函数进行数据管理分析 140

8.1 求和函数与应用 141

8.1.1 无条件求和——SUM函数 141

8.1.2 单条件求和——SUMIF函数 142

8.1.3 基于多个条件进行求和——SUM函数和IF函数联合使用 142

8.1.4 计算几组数组间对应元素乘积之和——SUMPRODUCT函数 145

8.1.5 对数据区域中最大的n个数据进行求和 146

8.1.6 对数据区域中最小的n个数据进行求和 147

8.1.7 根据商品和月份名称加总销售数据 148

8.1.8 对不同颜色的单元格或不同颜色字体的单元格进行求和 150

8.2 条件和逻辑函数 151

8.2.1 IF函数 152

8.2.2 IF函数和AND函数及OR函数联合使用 153

8.3 查找与引用函数 153

8.3.1 VLOOKUP函数和HLOOKUP函数 154

8.3.2 MATCH函数 156

8.3.3 INDEX函数 157

8.3.4 ADDRESS函数 161

8.3.5 INDIRECT函数 161

8.3.6 OFFSET函数 164

8.4 数据库函数 165

8.4.1 DSUM函数 165

8.4.2 DAVERAGE函数、DMAX函数和DMIN函数 167

8.4.3 DGET函数 168

8.5 日期和时间函数 168

8.5.1 确定具体日期 169

8.5.2 确定两个日期之间的天数 170

8.5.3 设置日期显示格式 170

8.6 财务函数 171

8.6.1 年金现值的计算——PV函数 171

8.6.2 不等额收付款项现值的计算——NPV函数 172

8.6.3 年金终值的计算——FV函数 173

8.6.4 不等额收付款项终值的计算 174

8.6.5 已知年金时计算利率——RATE函数 175

8.6.6 已知不等额收付款项时计算利率——IRR函数 175

8.6.7 期数和投资回收期的计算——NPER函数 176

8.6.8 等额分期付款的有关计算——PMT函数、IPMT函数和PPMT函数 177

8.6.9 计算累计偿还利息额和本金额——CUMIPMT函数和CUMPRINC函数 178

8.6.10 债券投资的有关计算 179

8.6.11 固定资产折旧的计算 183

8.7 字符串函数 186

8.7.1 从身份证号码中提取生日、性别等信息 186

8.7.2 将金额的各个数字依次填入到连续的单元格 187

8.7.3 设置单元格内数字的显示格式 190

第9章 销售信用政策分析与决策 191

9.1 信用标准决策 192

9.1.1 信用标准决策方法 192

9.1.2 信用标准决策实例 192

9.2 信用条件决策 193

9.2.1 信用条件决策方法 193

9.2.2 信用条件决策实例 194

9.3 收账政策决策 195

9.3.1 收账政策决策方法 195

9.3.2 收账政策决策实例 195

9.4 现金折扣决策 196

9.4.1 现金折扣决策方法 196

9.4.2 现金折扣决策实例 197

9.5 信用政策的综合决策 198

9.5.1 信用政策综合决策方法 198

9.5.2 信用政策综合决策实例 198

9.6 信用决策的动态分析方法 199

9.6.1 单期信用政策方案决策 199

9.6.2 永续信用政策方案决策 200

第10章 应收账款管理 202

10.1 应收账款信息管理系统 203

10.1.1 建立应收账款基本信息表 203

10.1.2 自动计算到期日、欠款额、开票天数和是否到期 204

10.1.3 利用条件格式突出显示逾期发票 205

10.1.4 保护计算公式 207

10.1.5 输入应收账款基本信息 210

10.1.6 删除应收账款的基本信息 211

10.1.7 修改应收账款基本信息 212

10.2 制作应收账款对账单 212

10.2.1 制作应收账款数据透视表 212

10.2.2 制作应收账款对账单 214

10.2.3 打印应收账款对账单 214

10.2.4 发送应收账款对账单邮件给客户 215

10.3 应收账款账龄分析 216

10.3.1 编制应收账款的账龄分析表 216

10.3.2 绘制显示不同客户的应收账款账龄分析图 219

10.3.3 绘制显示不同账龄区间的应收账款总额账龄分析图 222

10.4 利用数据透视表和数据透视图进行账龄分析 223

第11章 存货管理 228

11.1 基本的经济订货批量模型 229

11.1.1 经济订货批量方程 229

11.1.2 经济订货批量计算 229

11.2 扩展的经济订货批量模型 231

11.2.1 存货陆续供应和耗用的经济订货批量模型 231

11.2.2 存货瞬时到货且允许缺货的经济订货批量模型 231

11.2.3 存货陆续供应和耗用且允许缺货的经济订货批量模型 231

11.3 有数量折扣但不允许缺货情况下的经济订货批量 233

11.3.1 非连续价格形式的折扣优惠 233

11.3.2 连续价格形式的折扣优惠 234

11.4 确定最佳存货期 236

11.4.1 存货保本期和存货保利期的计算 237

11.4.2 建立存货保本期和存货保利期计算模型 237

11.5 存货的ABC分类管理 238

11.5.1 存货的ABC分类管理概述 238

11.5.2 利用Excel对存货进行ABC分类管理 239

11.6 存货计价 241

11.6.1 个别认定法 241

11.6.2 先进先出法（利用函数） 243

11.6.3 制作先进先出法模版 244

11.6.4 先进先出法（利用VBA） 245

11.6.5 后进先出法（利用VBA） 248

11.6.6 月末一次加权平均法 249

11.6.7 移动加权平均法 250

11.6.8 成本与市价孰低法 251

11.6.9 零售价格法 252

11.6.10 毛利率法 253

第12章 销售预测 254

12.1 利用Excel预测函数进行预测 255

12.1.1 利用LINEST函数进行一元线性回归分析 256

12.1.2 利用LINEST函数进行多元线性回归分析 257

12.1.3 利用LOGEST函数进行一元指数回归分析 257

12.1.4 利用LOGEST函数进行多元指数回归分析 258

12.1.5 利用FORECAST函数直接进行一元线性预测 259

12.1.6 利用GROWTH函数直接进行一元指数预测 260

12.1.7 利用TREND函数直接进行一元线性预测 260

12.1.8 利用SLOPE函数获取一元线性回归方程的斜率 261

12.1.9 利用INTERCEPT函数获取一元线性回归方程的截距 261

12.2 利用数据分析工具进行预测 262

12.2.1 移动平均法 262

12.2.2 指数平滑法 263

12.2.3 回归分析法 264

12.3 利用回归工具进行预测分析 265

12.3.1 一元线性回归分析 265

12.3.2 多元线性回归分析 266

12.3.3 一元非线性回归分析 267

12.3.4 多元非线性回归分析 268

12.3.5 利用图表法进行回归分析 270

12.4 利用规划求解工具解决预测问题 273

12.4.1 基本预测模型 273

12.4.2 应用实例 274

12.5 利用概率预测方法进行预测 275

12.5.1 条件概率预测方法 275

12.5.2 利用马尔科夫方法预测市场销售状态 276

12.5.3 利用马尔科夫方法预测市场占有率 277

12.5.4 蒙特卡罗预测方法 278

第13章 成本预测与管理 282

13.1 成本预测 283

13.1.1 高低点法 283

13.1.2 回归分析法 284

13.1.3 目标利润推算法 284

13.1.4 比例预测法 285

13.2 成本分解 286

13.2.1 利用图表分解成本 286

13.2.2 利用函数分解成本 286

13.2.3 利用回归工具分解成本 287

13.3 销售收入和成本费用分析 287

13.3.1 整理数据并计算有关的指标 288

13.3.2 绘制趋势分析动态图表 289

13.3.3 绘制对比分析动态图表 291

第14章 利润预测及规划 297

14.1 单品种产品的本量利分析 298

14.1.1 盈亏平衡点的计算 298

14.1.2 绘制基本的本量利分析图 298

14.1.3 绘制带有数值调节按钮和盈亏平衡线的本量利分析图 299

14.1.4 在本量利分析图上添加数值调节按钮并显示数据 305

14.1.5 绘制基本的盈亏面积图 307

14.1.6 绘制带有数值调节按钮的盈亏面积图 311

14.1.7 利润的敏感性分析模型 313

14.1.8 目标利润分析模型 315

14.1.9 产销不平衡时的本量利分析 315

14.2 多品种产品的本量利分析 319

14.2.1 品种结构确定情况下多品种产品的盈亏平衡点计算 319

14.2.2 品种结构不确定情况下多品种产品的盈亏平衡点计算 320

14.2.3 多品种产品的利润敏感性分析模型 321

14.3 本量利最优化分析模型 323

14.3.1 利润最大化模型 323

14.3.2 保利模型 325

14.3.3 保本模型 326

14.3.4 成本控制模型 326

14.4 非线性本量利分析 327

14.4.1 利用图表进行非线性本量利分析 327

14.4.2 利用插值法确定非线性情况下的保本销售量 329

14.4.3 非线性本量利分析模型 330

14.5 不确定性本量利分析 331

14.5.1 利用联合概率法确定保本销售量及其概率分布 332

14.5.2 利用联合概率法确定利润概率分布 333

14.5.3 蒙特卡罗模拟法 335

14.6 制定利润计划 339

14.6.1 编制利润计划表 339

14.6.2 分析利润计划完成情况 340

第15章 产品生产决策 343

15.1 新产品开发的决策分析 344

15.2 亏损产品是否应停产的决策分析 344

15.3 最优生产批量的决策分析 346

15.3.1 成批生产单一品种产品的情况 346

15.3.2 同一生产设备分批轮换生产几种产品的情况 347

15.4 根据订单确定最优产品生产计划 348

15.5 不确定情况下的生产决策 349

15.5.1 利用小中取大法进行不确定性情况下的生产决策 349

15.5.2 利用大中取小法进行不确定性情况下的生产决策 350

15.5.3 利用概率法进行不确定性情况下的生产决策 351

第16章 产品定价决策 354

16.1 成本加成定价决策 355

16.1.1 完全成本加成定价决策 355

16.1.2 变动成本加成定价决策 356

16.1.3 全部成本加成定价决策 356

16.2 以市场需求为基础的定价决策 357

16.2.1 利用规划求解工具确定产品价格 357

16.2.2 利用图表确定产品价格 358

16.2.3 利用函数确定产品价格 359

16.3 本量利定价决策 360

16.3.1 保本定价法 360

16.3.2 保利定价法 360

16.4 反向定价决策 361

16.4.1 确定批发价 361

16.4.2 确定出厂价 361

16.5 特殊订货定价决策 362

16.5.1 不增加专属成本也不影响正常销售的特殊订货定价决策 362

16.5.2 需要增加专属成本但不影响正常销售的特殊订货定价决策 362

16.5.3 接受订货会影响正常生产销售的特殊订货定价决策 363

第17章 销售及服务决策 364

17.1 销售方式决策 365

17.1.1 打折销售决策的一般方法 365

17.1.2 建立打折销售决策模型 367

17.1.3 从资金周转的角度确定商品折扣的盈亏平衡点 368

17.1.4 折扣销售／返还现金／附赠商品的销售方式决策 370

17.2 销售渠道决策 372

17.2.1 自销／代销方式的决策方法 372

17.2.2 建立自销／代销方式决策模型 374

17.3 分期付款销售方式决策 376

17.4 售后服务方式决策 379

附录A 范例索引 381

附录B Excel工作表函数 388

附录C Excel常用键盘快捷键 396