Excel公式与函数应用实例解析前言

随着社会信息化的蓬勃发展，如今的企业与个人都面临着前所未有的挑战与压力。过去粗放式的、手工式的各项数据管理和处理已经明显不能适合社会的需要，激烈的竞争要求企业的财务管理、市场分析、生产管理甚至日常办公管理必须逐渐精细和高效。Excel作为一个简单易学、功能强大的数据处理软件已经广泛应用于各类企业日常办公中，是目前应用最广泛的数据处理软件之一。但是很多用户应用Excel仅仅限于建立表格和一些简单的计算，对于Excel在财会、审计、营销、统计、金融、工程、管理等各个领域的应用知之甚少，其实，Excel提供了功能齐全的函数计算和分析工具，如果能熟练用它来进行数据分析，必将能获取更为精确的信息，并大大提高工作效率，从而增强个人以及企业的社会竞争力。

本丛书为读者展现了Excel在企业日常活动中一个个财务管理、营销分析、日常管理等领域的实例、案例，读者可以信手拈来，稍加改造用到工作中。为了能让读者尽快地、轻松地掌握这些内容，本丛书编写与光盘开发时特别突出了如下特点：

一、超大容量教学录像（视频）及软件、素材源文件DVD教学光盘

1．赠33～66讲不等的多媒体教学录像

现场视、听互动教学无疑是最快的学习方法，为了全面提高学习效率，让读者像在课堂上听课一样轻松掌握，我们花费了数月的不眠之夜，全程录制了每一本书的教学录像（视频），也许这些教学录像（视频）还存在这样或那样的不足，我们相信这些基本不会影响学习。只要读者朋友看书前，认真看一看教学录像（视频），然后根据书中的素材和步骤就一定能够以最快的速度制作出各种各样的应用实例和案例。

2．赠送“Office简体中文版（测试版）”软件一套

以往很多读者想学习但找不到相关软件，为了解决这个问题，我们特意与微软（中国）公司取得联系，获得他们的“Office简体中文版（测试版）”的特别授权，将“Office简体中文版（测试版）”附在光盘中，读者可以将其安装后再打开学习。

在此我们要特别感谢微软（中国）公司对我们的大力支持！

3．赠送Office常用模版420余个

我们从各个行业搜集了常用模版420余个，读者需要时可以根据行业找到相应的模版，稍加改造，就可为之所用，不用费时费力另行制作，非常方便。

4．提供所有素材和源文件

本书中所有例子的素材都在光盘中，读者不必自行录入、制作，直接复制过来，按照书中的路径打开即可使用。

二、实用而易学的图书内容

1．尽可能选择行业应用中的例子，让读者学完以后就直接能用

本丛书在写作过程中参考了许多行业应用资料和同类图书，走访了许多行业人员，力求选择的例子结合实际并把握好尺度，并且让读者拿来就能直接用。

2．基础知识与应用相结合，以及图示化的写作方法，让学习更直观、更快速

本丛书以行业应用为主，但并非开篇即介绍专业知识，而是准备了“预备知识”，为的是降低学习门槛，增强易读性。采用图示化的写作方法也是为了让学习更加直观。

本书系统地介绍了利用Excel公式与函数来实现数据处理、数据管理、数据统计、数据分析等方法，具有很强的实用性与可操作性。全书共分13章，分别介绍了Excel函数基础知识、Excel函数在企业人事管理中的应用、Excel函数在工资管理中的应用、Excel函数在销售数据统计中的应用、Excel函数在产品销售本量利分析中的应用、Excel函数在财务计算中的应用、Excel函数在财务预算中的应用、Excel函数在财务分析中的应用、Excel函数在固定资产管理中的应用、Excel函数在数据库管理中的应用、Excel函数在经济预测中的应用，以及Excel工程函数的应用。

本书适合企业行政人员、人事档案管理人员、财务人员、市场分析人员、数据统计分析人员以及Excel应用爱好者作为参考书。

本书DVD光盘：50讲教学录像（视频） Office2007简体中文版（测试版） 模板大全 所有素材、源文件。

第1章Excel函数应用基础

（教学录像：55分钟） 1

1.1函数概述 2

1.1.1什么是函数 2

1.1.2什么是函数参数 2

1.1.3函数的构成 2

1.1.4函数类型 3

1.1.5使用函数应注意的事项 3

1.2公式的输入与编辑 3

1.2.1使用“插入函数”向导输入公式 3

1.2.2手动输入公式 5

1.2.3公式编辑 7

1.3通过“函数帮助”来学习函数 7

1.4引用数据源 8

1.4.1引用相对数据源 8

1.4.2引用绝对数据源 9

1.4.3引用混合数据源 9

1.4.4在公式中引用三维数据源来进行计算 10

1.4.5在公式中引用多张工作表中特定

数据源来进行计算 11

1.4.6在公式中引用多个工作簿数据源

来进行计算 11

1.5名称的定义与使用 12

1.5.1名称的定义 12

1.5.2名称的使用 14

1.5.3名称的管理 14

1.6公式审核工具的应用 17

1.6.1追踪引用单元格 17

1.6.2追踪从属单元格 17

1.6.3错误检查 18

1.6.4显示计算步骤 19

1.6.5移去显示箭头 20

1.7公式错误值与解决方法 20

1.7.1“#####”错误值的处理方法 20

1.7.2“#DIV/0！”错误值的处理方法 21

1.7.3“#N/A”错误值的处理方法 22

1.7.4“#NAME"para" label-module="para">

1.7.5“#NUM！错误值的处理方法 25

1.7.6“#VALUE！”错误值的处理方法 25

1.7.7“#REF！”错误值的处理方法 26

1.7.8“#NULL！”错误值的处理方法 27

第2章Excel函数在企业人事管理中的应用（教学录像：82分钟） 28

2.1企业员工档案管理 29

2.1.1从身份证号码中自动提取出生日期 29

2.1.2从身份证号码中自动提取性别 30

2.1.3自动计算员工工龄 31

2.1.4自动计算员工实际年龄 32

2.2企业员工加班和考勤管理 33

2.2.1计算员工加班所得的加班奖金 33

2.2.2计算员工本月因未出勤而扣除的工资 34

2.2.3计算员工应有年假 35

2.3企业员工培训成绩统计和

查询管理 36

2.3.1计算员工各门考核成绩的总成绩 36

2.3.2计算员工各门考核成绩的平均成绩 36

2.3.3计算员工总成绩排名 37

2.3.4建立“简单员工培训成绩查询”表 38

2.3.5建立员工培训成绩自动查询系统 40

2.4企业员工考评管理 44

2.4.1计算员工业绩考评成绩 44

2.4.2计算员工工作态度考评成绩 47

2.4.3计算员工技能考评成绩 48

2.4.4计算员工年终考评成绩 49

第3章Excel函数在工资管理中的应用

（教学录像：54分钟） 54

3.1企业员工销售业绩奖金管理 55

3.1.1统计每位销售员当月的总销售额 55

3.1.2根据每位销售员的总销售额

判断业绩奖金提成率 55

3.1.3计算每位销售员当月的业绩奖金额 56

3.1.4评选本月最佳销售奖的归属者 57

3.2企业员工福利待遇管理 58

3.2.1计算员工的住房补贴金额 58

3.2.2计算员工的伙食补贴金额 59

3.2.3计算员工的交通补贴金额 59

3.2.4计算员工的医疗补贴金额 60

3.2.5合计员工的总福利金额 60

3.3企业员工社会保险管理 61

3.3.1计算员工的应扣养老保险金额 61

3.3.2计算员工的应扣医疗保险金额 62

3.3.3计算员工的应扣失业保险金额 63

3.3.4计算员工的应扣住房公积金金额 63

3.3.5合计员工应扣的总社会保险金额 64

3.4企业员工考勤扣款与满勤奖管理 65

3.4.1计算员工考勤扣款金额 65

3.4.2评选哪些员工获得本月的满勤奖 65

3.5统计员工当月实发工资 66

3.5.1获取员工相关的应发和扣发金额 66

3.5.2计算应扣个人所得税金额 68

3.5.3计算每位员工当月实发工资金额 69

3.5.4创建每位员工工资发放工资条 70

第4章Excel函数在销售数据统计中的应用（教学录像：28分钟） 73

4.1销售数据汇总统计 74

4.1.1按产品型号统计当月销售数据 74

4.1.2按部门统计当月销售数据 75

4.1.3按销售员统计当月产品销售数量

与销售金额 76

4.2销售数据统计分析 77

4.2.1销售员的销售排名分析 77

4.2.2考评本月销售员是否“达标” 78

4.2.3各产品销量占总销量的百分比分析 78

4.2.4本月与上月销售数据对比分析 80

4.2.5预测下月产品销量与销售金额 82

第5章Excel函数在产品销售本量利分析中的应用（教学录像：87分钟） 84

5.1盈亏平衡销售量（销售额）

预测与分析 85

5.1.1单一产品保本点预测 85

5.1.2多产品保本点预测 87

5.2不同单位售价对利润的影响 92

5.2.1建立基本计算表 92

5.2.2建立本量利分析图 96

5.3分析单位可变成本对利润的影响 101

5.3.1建立基本计算表 101

5.3.2建立盈亏平衡图表 106

5.4利用模拟运算计算不同单位

售价保本点 112

5.4.1求解不同售价下的保本点 112

5.4.2建立图表直观显示盈亏平衡销售量、

盈亏平衡销售额与单位售价 114

5.5单位利润的计算 118

5.5.1求解不同单位可变成本、单位

售价下的单位成本、单位收入 118

5.5.2不同单位可变成本、单位售价

对单位收入的影响 120

第6章Excel函数在财务计算中的应用（教学录像：85分钟） 123

6.1资金投入与回报计算 124

6.1.1资金投入与回报金额 124

6.1.2资金投入的回报率 128

6.2贷款本金与利息计算 131

6.2.1贷款本金与分期偿还额计算 132

6.2.2贷款利息计算 135

6.3证券与债券计算 136

6.3.1证券应付价格、利息与收益率计算 137

6.3.2债券付息时间与次数计算 141

6.4长期借款筹资计算 144

6.4.1利用模拟运算表进行筹资计算 145

6.4.2建立长期借款筹资决策分析表 149

第7章Excel函数在财务预算中的应用（教学录像：90分钟） 155

7.1创建财务预算基本模型 156

7.1.1创建预算销售表模型 156

7.1.2创建预算管理费用表模型 157

7.1.3创建预算制造费用表模型 157

7.1.4创建预算定额成本表模型 158

7.1.5创建预算投资收益和营业外

收入支出表模型 158

7.2企业日常业务预算 158

7.2.1销售预算 159

7.2.2生产预算 162

7.2.3直接材料和采购预算 165

7.2.4直接人工成本预算 169

7.2.5制造费用预算 171

7.2.6产品成本预算 172

7.2.7管理费用预算 174

7.2.8销售费用预算 175

7.3企业现金预算 177

7.3.1现金收入预算 177

7.3.2现金支出预算 180

7.3.3筹集资金计算 183

7.4预算企业利润表与资产负债表 185

7.4.1预算利润表 185

7.4.2预算资产负债表 187

第8章财务报表分析（教学录像：

53分钟） 192

8.1资产负债表分析 193

8.1.1建立比较资产负债表 193

8.1.2资产负债表结构比较分析 199

8.2利润表分析 207

8.2.1建立比较利润表 207

8.2.2利润表结构比较分析 211

8.3财务比率计算与分析 215

8.3.1了解财务比率分析指标 215

8.3.2根据财务报表计算具体财务比率 217

第9章Excel函数在固定资产管理中的应用（教学录像：60分钟） 225

9.1企业固定资产折旧计算 226

9.1.1平均年限法 226

9.1.2双倍余额递减法 228

9.1.3年数总和法 231

9.1.4余额递减法 233

9.2企业固定资产各折旧计算法

比较分析 236

9.2.1创建各种折旧法比较分析模型 236

9.2.2创建折旧法比较分析图表 237

9.3企业固定资产折旧数据自动查询 239

9.3.1创建固定资产折旧数据自动

查询系统 240

9.3.2自动计算固定资产的月、年折旧值 243

第10章Excel函数在数据库管理中的应用（教学录像：30分钟） 248

10.1企业采购数据管理 249

10.1.1计算采购产品的总数量与平均数量 249

10.1.2计算指定采购产品相对应的

采购情况 251

10.1.3计算产品采购数量的偏差与方差 255

10.2企业库存数据管理 257

10.2.1在企业产品库存中统计满足条件

的产品种类 257

10.2.2在企业产品库存中查询指定产品

的库存数量 258

10.2.3创建企业产品库存数据自动

查询系统 258

10.3外部数据库数据的运用 261

10.3.1导入Access数据库中的数据源 261

10.3.2导入网页中的外部数据源 262

10.3.3导入文本文件中的数据源 264

第11章Excel函数在经济预测中的应用（教学录像：36分钟） 266

11.1定性预测 267

11.1.1德尔菲法 267

11.1.2主观概率法 271

11.2回归分析预测 274

11.2.1一元线性回归预测 274

11.2.2多元线性回归预测 278

11.2.3非线性回归预测 280

11.3时间序列平滑预测 282

11.3.1移动平均预测 282

11.3.2指数平滑预测 287

11.4季节变动预测 293

11.4.1平均数趋势法预测 293

11.4.2环比法预测 299

第12章Excel在工程函数中的应用（教学录像：30分钟） 306

12.1贝赛尔函数的应用 307

12.1.1使用BESSELI函数计算修正

Bessel函数值In(X) 307

12.1.2使用BESSELJ函数计算

Bessel函数值Jn(X) 307

12.1.3使用BESSELK函数计算修正

Bessel函数值Kn(X) 308

12.1.4使用BESSELY函数计算

Bessel函数值Yn(X) 308

12.2进制转换函数的应用 309

12.2.1二进制数字系统下的进制转换 310

12.2.2八进制数字系统下的进制转换 311

12.2.3十进制数字系统下的进制转换 312

12.2.4十六进制数字系统下的进制转换 313

12.3度量衡转换函数的应用 315

12.3.1长度与面积单位的转换 316

12.3.2重量和质量单位的转换 318

12.3.3液体度量单位的转换 318

12.4数据筛选函数在工资数据

管理中的应用 319

12.4.1核对工资表中的数据是否正确 319

12.4.2从工资表中筛选出符合要求的数据 319

12.5积分函数在工程计算中的应用 320

12.5.1使用ERF函数计算设计模型

的误差值 320

12.5.2使用ERFC函数计算设计模型

的余误差值 321

12.6复数函数的应用 322

12.6.1使用IMABS函数求复数的模 322

12.6.2使用IMREAL函数求复数

的实系数 323

12.6.3使用IMAGINARY函数求复数

的虚系数 323

12.6.4使用IMCONJUGATE函数求复数

的共轭复数 324

12.6.5使用IMSUM函数求复数的和 325

12.6.6使用IMSUB函数求复数的差 325

12.6.7使用IMDIV函数求两个复数的商 326

12.6.8使用IMPRODUCT函数求复数的积 327

12.6.9使用IMEXP函数求复数的指数 327

12.6.10使用IMSQRT函数求复数

的平方根 328

第13章Excel常用函数的应用（教学录像：51分钟） 329

13.1日期与时间函数 330

13.1.1常用日期函数应用 330

13.1.2常用时间函数应用 334

13.2数学函数 338

13.2.1常用数学函数的应用 338

13.2.2常用数组函数应用 344

13.3统计函数 348

13.3.1常用统计函数应用 348

13.3.2常用排位统计函数应用 355

13.3.3常用分布统计函数的应用 358

13.4查找和引用函数 360

13.5信息函数 372

附录AExcel常用函数列表 378

附录BExcel学习、交流相关网站 389 2100433B

内容简介

《Excel公式与函数实例应用大全》是一本专门针对Excel函数应用的图书，在讲解每一个函数的格式、参数及使用方法后，都以一个实例详细演示该函数的应用，让读者不但知道函数的用途、参数及格式，而且能融会贯通地将这些函数应用到日常工作中，从而提高效率。 《Excel公式与函数实例应用大全》图文并茂、实例丰富，主要内容包括：Excel基本操作、Excel公式使用、函数应用基础、文本函数、时间/日期函数、查询与引用函数、财务函数、数学和三角函数、统计函数、工程函数、数据库函数、逻辑函数。 《Excel公式与函数实例应用大全》适合于对Excel应用有较高要求的用户，也可以作为一本Excel函数备查手册，以备不时之需。2100433B

《Excel公式、函数和图表应用与实例分析》主要内容：Excel公式、函数和图表的基本使用方法，接着介绍了公式、函数和图表在科学计算、个人理财、企业管理、信息管理、统计分析、科学实验、规划决策、市场营销等各方面的应用实例，最后介绍了Excel的自定义函数和图表 。

前言

第1章 Excel公式、函数和图表应用基础

1.1 Excel公式基础知识

1.1.1 公式的组成要素

1.1.2 公式的输入与编辑

1.1.3 公式的运算符

1.1.4 Excel的单元格引用

1.1.5 数组公式

1.2 Excel函数基础知识

1.2.1 函数的输入

1.2.2 函数的种类

1.3 Excel图表基础知识

1.3.1 图表的创建

1.3.2 图表的编辑和修饰

1.3.3 图表的类型

第2章 Excel公式、函数和图表在科学计算中的应用

2.1 Excel科学计算相关函数

2.1.1 Excel的数学函数

2.1.2 Excel的三角函数

2.2 用Excel解方程

2.2.1 使用单变量求解方程

2.2.2 求解线性方程

2.2.3 使用克拉默法则解方程

2.2.4 使用矩阵运算解方程

2.3 用Excel计算数值积分

2.3.1 计算数值积分相关函数说明

2.3.2 用Excel计算数值积分

2.4 Excel工程函数的应用

2.4.1 Excel的工程函数

2.4.2 度量衡转换器

2.4.3 进制转换器

第3章 Excel公式、函数和图表在个人理财中的应用

3.1 个人理财计算器

3.1.1 整存整取计算器

3.1.2 零存整取计算器

3.1.3 分期付款计算器

3.1.4 个人所得税计算器

3.1.5 信用卡免息期计算

3.2 个人记账本

3.2.1 个人记账本

3.2.2 个人收支分析

3.3 基金和证券投资问题

3.3.1 开放式基金净值

3.3.2 个人基金持仓明细表

3.3.3 K线图的绘制

第4章 Excel公式、函数和图表在企业管理中的应用

4.1 企业固定资产管理问题

4.1.1 Excel的日期函数

4.1.2 固定资产折旧问题

4.2 企业人事工资管理问题

4.2.1 身份证查询和转换

4.2.2 人事信息提取

4.2.3 工资管理问题

第5章 Excel公式、函数和图表在信息管理中的应用

5.1 Excel的数据库功能

5.1.1 概述

5.1.2 数据清单的排序、汇总和筛选

5.1.3 Excel的记录单

5.1.4 Excel的聚集函数

5.1.5 Excel的数据库函数

5.2 学生成绩管理问题

5.2.1 学生成绩统计

5.2.2 学生成绩分析

5.2.3 学生成绩查询

5.3 库存管理问题

5.3.1 库存表的建立

5.3.2 库存表的统计

5.3.3 库存表的查询

第6章 Excel公式、函数和图表在统计分析中的应用

6.1 统计数据的数字特征

6.1.1 频数表和直方图

6.1.2 统计量

6.2 随机变量的概率分布和数字特征

6.2.1 正态分布

6.2.2 二项分布

6.2.3 泊松分布

6.3 随机数据的生成

6.3.1 生成随机数据

6.3.2 蒙特卡洛方法计算圆周率

6.4 参数估计与假设检验

6.4.1 参数估计

6.4.2 假设检验

6.5 回归分析

6.5.1 一元线性回归分析

6.5.2 多元线性回归分析

6.5.3 非线性回归分析

第7章 Excel公式、函数和图表在科学实验中的应用

7.1 实验数据分析

7.1.1 单因素实验的方差分析

7.1.2 双因素无重复实验的方差分析

7.1.3 双因素重复实验的方差分析

7.2 实验图表绘制

7.2.1 函数图像绘制——直坐标

7.2.2 函数图像绘制——极坐标

7.2.3 振动合成图像的绘制

第8章 Excel公式、函数和图表在规划决策中的应用

8.1 线性规划问题

8.1.1 生产决策问题

8.1.2 配料问题

8.1.3 整数规划

8.2 网络规划问题

8.2.1 最大流问题

8.2.2 匹配问题

8.2.3 最短路径问题

8.2.4 最小费用流问题

8.3 决策问题

8.3.1 不确定性决策问题

8.3.2 风险性决策问题

第9章 Excel公式、函数和图表在市场营销中的应用

9.1 营销数据分析

9.1.1 营销数据的建立

9.1.2 营销数据的统计

9.1.3 营销数据的查找

9.2 成本与利润分析

9.2.1 成本与利润分析

9.2.2 动态图表利润分析

9.3 市场营销的预测

9.3.1 移动平均预测

9.3.2 指数平滑预测

9.3.3 季节变动预测

第10章 Excel的自定义函数和图表

10.1 Excel的自定义函数

10.1.1 宏与VBA编程

10.1.2 VBA语言基础

10.1.3 Excel的自定义函数

10.2 Excel自定义函数实例

10.2.1 个人所得税计算函数

10.2.2 数词转换函数

10.2.3 身份证转换和提取函数

10.3 Excel的自定义图表

10.3.1 Excel的自定义类型图表

10.3.2 组合图表

10.3.3 双轴图表

10.3.4 构建自定义图表类型