Excel函数图表与数据分析

第1部分 函 数 应 用

1 Excel基础知识 2

1.1 Excel基础操作 2

1.1.1 创建工作簿 2

1.1.2 工作表的操作 3

1.1.3 单元格的操作 4

1.1.4 数据的输入 6

1.1.5 密码保护 8

1.2 Excel函数基础 9

1.2.1 创建公式 10

1.2.2 使用名称 10

1.2.3 使用引用 11

1.2.4 使用函数 13

2 逻辑函数 16

2.1 函数应用实战 16

2.1.1 FALSE和TRUE函数：返回

1.1.1 逻辑值 16

2.1.2 AND函数：进行交集运算 16

2.1.3 IF函数：判断真假 17

2.1.4 NOT函数：取反 18

2.1.5 OR函数：进行并集运算 18

2.2 综合实例 19

2.2.1 函数求值 19

2.2.2 判断闰年 20

3 数学、三角和数组函数 21

3.1 数学函数 21

3.1.1 ABS函数：计算绝对值 21

3.1.2 CEILING函数：按条件向上

1.1.1 舍入 22

3.1.3 COMBIN函数：计算组合数 23

3.1.4 FACT函数：计算阶乘 24

3.1.5 FLOOR函数：按条件向下

1.1.1 舍入 25

3.1.6 INT函数：向下取整 26

3.1.7 LN、LOG和LOG10函数：

1.1.1 计算对数 26

3.1.8 MOD函数：返回相除的余数 27

3.1.9 PRODUCT函数：计算乘积 28

3.1.10 RAND函数：返回随机数 28

3.1.11 ROMAN函数：返回罗马

3.1.11 数字 29

3.1.12 ROUND、ROUNDDOWN

3.1.11 和ROUNDUP函数：按位数

3.1.11 舍入 29

3.1.13 SUM函数：计算数字之和 30

3.1.14 SUMIF函数：条件求和 31

3.1.15 TRUNC函数：按位数舍入 31

3.2 三角函数 32

3.2.1 正三角函数SIN、COS和

1.1.1 TAN 32

3.2.2 反三角函数ASIN、ACOS

1.1.1 和ATAN 32

3.2.3 DEGREES和RADIANS函数：

1.1.1 转换度和弧度 33

3.3 数组函数 33

3.3.1 MDETERM函数：计算行

1.1.1 列式 33

3.3.2 MINVERSE函数：返回

1.1.1 逆矩阵 34

3.3.3 MMULT函数：计算矩阵

1.1.1 乘积 35

3.3.4 SUMPRODUCT函数：计算数

1.1.1 组求和 35

3.4 综合实例 36

3.4.1 计算原材料的用量 36

3.4.2 随机分配 37

4 统计函数 41

4.1 函数应用实战 41

4.1.1 AVERAGE和AVERAGEA

1.1.1 函数：计算平均值 41

4.1.2 COUNT和COUNTA函数：

1.1.1 统计单元格个数 42

4.1.3 COUNTBLANK函数：统计

1.1.1 空白单元格 43

4.1.4 COUNTIF函数：按条件

1.1.1 统计 43

4.1.5 FREQUENCY函数：计算

1.1.1 频率分布 44

4.1.6 LARGE和SMALL函数：

1.1.1 按条件返回最值 45

4.1.7 MAX和MAXA函数：返回

1.1.1 最大值 46

4.1.8 MEDIAN函数：返回中值 46

4.1.9 MIN和MINA函数：返回

1.1.1 最小值 47

4.1.10 MODE函数：返回频率最多

3.1.11 的数值 47

4.1.11 RANK函数：返回排位 48

4.1.12 CONFIDENCE函数：计算

3.1.11 置信区间 49

4.1.13 CORREL函数：计算相关

3.1.11 系数 50

4.1.14 BINOMDIST函数：返回

3.1.11 二项分布概率 51

4.1.15 CRITBINOM函数：返回

3.1.11 累积二项分布式值 52

4.1.16 NORMDIST和NORMINV

3.1.11 函数：返回正态分布 52

4.1.17 POISSON函数：返回泊松

3.1.11 分布 53

4.1.18 STDEV和STDEVA函数：

3.1.11 返回标准偏差 54

4.1.19 VAR和VARA函数：返回

3.1.11 样本的方差 54

4.2 综合实例 55

4.2.1 成绩单统计 55

4.2.2 成绩单的标准化 57

5 查询和引用函数 61

5.1 函数应用实战 62

5.1.1 ADDRESS函数：返回引用

1.1.1 地址 62

5.1.2 AREAS函数：返回区域个数 63

5.1.3 CHOOSE函数：从列值中选择

1.1.1 数值 63

5.1.4 COLUMN和ROW函数：返回

1.1.1 列标和行号 64

5.1.5 COLUMNS和ROWS函数：

1.1.1 返回引用的行列数 65

5.1.6 HLOOKUP函数：实现水平

1.1.1 查找 65

5.1.7 HYPERLINK函数：创建

1.1.1 跳转 66

5.1.8 INDEX函数：返回指定内容 67

5.1.9 INDIRECT函数：返回指定

1.1.1 的引用 68

5.1.10 LOOKUP函数：查找数据 68

5.1.11 MATCH函数：在数组中

3.1.11 查找 69

5.1.12 OFFSET函数：调整新的

3.1.11 引用 70

5.1.13 VLOOKUP函数：实行竖直

3.1.11 查找 71

5.2 综合实例 72

5.2.1 元件配比 72

5.2.2 欠款人员统计 75

6 数据库函数 78

6.1 函数应用实战 79

6.1.1 DAVERAGE函数：返回条

1.1.1 目的平均值 79

6.1.2 DCOUNT函数：返回单元格

1.1.1 个数 80

6.1.3 DGET函数：返回符合条件的

1.1.1 数值 81

6.1.4 DMAX函数：返回符合条件的

1.1.1 最大值 82

6.1.5 DMIN函数：返回符合条件的

1.1.1 最小值 83

6.1.6 DPRODUCT函数：返回指定

1.1.1 数值的乘积 83

6.1.7 DSTDEV函数：返回样本的

1.1.1 标准偏差 84

6.1.8 DSTDEVP函数：返回总体的

1.1.1 标准偏差 85

6.1.9 DSUM函数：返回指定条件的

1.1.1 数值之和 85

6.1.10 DVARP函数：计算总体

3.1.11 的方差 86

6.2 综合实例 87

7 文本函数 90

7.1 函数应用实战 91

7.1.1 CHAR函数：返回代码

1.1.1 的字符 91

7.1.2 CODE函数：返回字符对应

1.1.1 的代码 91

7.1.3 CLEAN函数：返回不能打印

1.1.1 的字符 92

7.1.4 CONCATENATE函数：合并

1.1.1 字符串 92

7.1.5 EXACT函数：比较字符串

1.1.1 是否相同 93

7.1.6 FIND和FINDB函数：查找

1.1.1 文本字符串 94

7.1.7 LEFT和LEFTB函数：返回

1.1.1 左边字符 94

7.1.8 LEN和LENB函数：返回字符

1.1.1 串长度 95

7.1.9 LOWER函数：将文本转换为

1.1.1 小写 96

7.1.10 MID和MIDB函数：返回

3.1.11 特定字符 97

7.1.11 REPLACE和REPLACEB

3.1.11 函数：替代文本 98

7.1.12 REPT函数：复制文本 99

7.1.13 RIGHT和RIGHTB函数：

3.1.11 返回右边字符 100

7.1.14 SEARCH和SEARCHB

3.1.11 函数：查找文本字符串 101

7.1.15 SUBSTITUTE函数：替换

3.1.11 文本 102

7.1.16 TEXT函数：将数值转换为

3.1.11 文本 102

7.1.17 TRIM函数：清除文本中的

3.1.11 空格 103

7.1.18 UPPER函数：将文本转换为

3.1.11 大写 103

7.1.19 VALUE函数：将文本转换为

3.1.11 数字 104

7.2 综合实例 104

7.2.1 处理数据格式 104

7.2.2 处理收款格式 106

8 日期和时间函数 108

8.1 日期系统介绍 108

8.2 函数应用实战 109

8.2.1 DATE函数：返回日期

1.1.1 编号 109

8.2.2 DATEVALUE函数：返回

1.1.1 日期编号 110

8.2.3 DAY函数：返回日期天数 111

8.2.4 DAYS360函数：返回相差

1.1.1 天数 112

8.2.5 HOUR函数：返回小时数 113

8.2.6 MINUTE函数：返回

1.1.1 分钟数 114

8.2.7 MONTH函数：返回月份 115

8.2.8 NOW函数：返回当前时间 115

8.2.9 SECOND函数：返回秒数 116

8.2.10 TIME函数：返回时间 116

8.2.11 TIMEVALUE函数：返回

3.1.11 时间 117

8.2.12 TODAY函数：返回当前

3.1.11 日期 117

8.2.13 WEEKDAY函数：返回

3.1.11 星期数 118

8.2.14 YEAR函数：返回年份 120

8.3 综合实例 120

8.3.1 计算节假日 120

8.3.2 网吧收费系统 124

8.3.3 计算员工的工龄 128

9 信息函数 131

9.1 函数应用实战 131

9.1.1 CELL函数：返回单元格

1.1.1 信息 131

9.1.2 ERROR.TYPE函数：判断

1.1.1 错误类型 133

9.1.3 INFO函数：返回当前操作

1.1.1 环境的信息 134

9.1.4 ISBLANK函数：判断单元格

1.1.1 是否为空 135

9.1.5 ISERR和ISERROR函数：

1.1.1 判断参数是否为错误值 135

9.1.6 ISLOGICAL函数：判断参数

1.1.1 是否是逻辑值 136

9.1.7 ISNA函数：判断错误值

1.1.1 是否是#N/A 136

9.1.8 ISNOTEXT函数：判断参数

1.1.1 是否是非字符串 138

9.1.9 ISNUMBER函数：判断参数

1.1.1 是否是数字 138

9.1.10 ISREF函数：判断参数

3.1.11 是否是引用 138

9.1.11 ISTEXT函数：判断参数

3.1.11 是否是文本 139

9.1.12 TYPE函数：返回数值的

3.1.11 类型 139

9.2 综合实例 139

10 财务函数 142

10.1 投资函数应用实战 142

10.1.1 FV函数：返回未来值 142

10.1.2 PV函数：返回现值 143

10.1.3 NPV函数：返回净现值 145

10.1.4 XNPV函数：返回净现值 146

10.2 本金和利息函数应用实战 147

10.2.1 PMT函数：返回贷款的

10.2.1 每期付款额 147

10.2.2 IPMT函数：返回付款的

10.2.1 利息额 148

10.2.3 PPMT函数：返回付款的

10.2.1 本金额 149

10.2.4 CUMIPMT和CUMPRINC

10.2.1 函数：返回阶段利息和本金 150

10.3 报酬率函数应用实战 150

10.3.1 IRR函数：返回内部

10.2.1 收益率 151

10.3.2 MIRR函数：返回修正内部

10.2.1 收益率 151

10.3.3 XIRR函数：返回内部

10.2.1 收益率 152

10.4 折旧函数应用实战 153

10.4.1 DB函数：固定余额递减

10.2.1 折旧 153

10.4.2 DDB函数：双倍余额递减

10.2.1 折旧 154

10.4.3 SLN函数：平均折旧 155

10.4.4 SYD函数：年限总和折旧 155

10.4.5 AMORDEGRC函数：返回

10.2.1 折旧 156

10.5 有价证券函数应用实战 157

10.5.1 ACCRINT函数：返回应计

10.2.1 利息 157

10.5.2 ACCRINTM函数：返回

10.2.1 应计利息 157

10.5.3 INTRATE函数：返回利率 158

10.5.4 PRICE函数：返回价格 158

10.5.5 YIELD函数：返回收益率 159

10.6 综合实例 159

第2部分 数 据 分 析

11 图表基础 166

11.1 创建图表 166

11.1.1 制作基础数据 166

11.1.2 选择图表类型 166

11.1.3 设置图表源数据 168

11.1.4 设置图表选项 168

11.1.5 设置图表位置 169

11.2 图表的基本编辑 170

11.2.1 调整图表大小 170

11.2.2 设置图表区字体 170

11.2.3 添加或删除数据 171

11.2.4 改变图表类型 171

11.3 Excel提供图表类型 172

11.3.1 柱形图 172

11.3.2 条形图 172

11.3.3 折线图 173

11.3.4 饼图 173

11.3.5 XY散点图 175

11.3.6 面积图 175

11.3.7 圆环图 176

11.3.8 雷达图 176

11.3.9 圆柱图、圆锥图和棱锥图 177

11.3.10 自定义图表 177

11.4 编辑图表 178

11.4.1 设置图表区 178

11.4.2 编辑数值轴 179

11.4.3 编辑分类轴 180

11.4.4 编辑三维图表 181

12 柱形图和条形图 182

12.1 柱形图 182

12.1.1 业绩达成图 182

12.1.2 双坐标双柱形图 186

12.1.3 多系列的堆积柱形图 189

12.2 条形图 194

12.2.1 对称条形图 194

12.2.2 进度表 199

13 折线图和XY散点图 202

13.1 折线图 202

13.1.1 四分位点图 202

13.1.2 动态月份销售曲线 206

13.1.3 价格走势图 209

13.2 XY散点图 214

13.2.1 函数拟合 214

13.2.2 阶梯图 216

13.2.3 模拟三维散点图 219

14 饼图和圆环图 226

14.1 饼图 226

14.1.1 分类饼图 226

14.1.2 复合饼图 229

14.2 圆环图 232

14.2.1 数量金额环形图 232

14.2.2 试件温度计 236

15 股价图和气泡图 240

15.1 股价图 240

15.1.1 温度信息表 240

15.1.2 双轴股价图 243

15.2 气泡图 248

16 其他图表 252

16.1 面积图 252

16.2 雷达图 256

16.3 曲面图 258

17 数据透视表 262

17.1 数据透视表简介 262

17.2 数据透视表的结构 263

17.3 数据透视表的数据 265

17.4 创建数据透视表 266

17.5 数据透视表实例 271

17.5.1 创建按产品分类的数据

10.2.1 透视表 272

17.5.2 查看销售量前三名的

10.2.1 产品信息 273

17.5.3 查看各部门各类产品的

10.2.1 销售情况 274

17.5.4 添加销售量的数据字段 275

17.6 数据透视表综合应用 277

17.6.1 创建基础数据透视表 278

17.6.2 添加新的行字段 281

17.6.3 添加计算字段和计算项 285

17.6.4 设置数据透视表的格式 286

17.6.5 关于数据透视表的总结 290

17.7 数据透视图 291

17.7.1 创建数据透视图 292

17.7.2 数据透视图实例 296

17.7.3 数据透视图的注意事项 310

18 基本数据分析 315

18.1 单变量求解 315

18.1.1 求解非线性方程 315

18.1.2 调整分数线 316

18.2 使用方案 319

18.3 模拟运算表 322

18.3.1 单变量模拟运算表 323

18.3.2 双变量模拟运算表 325

19 规划求解 327

19.1 关于规划求解 328

19.1.1 加载“规划求解” 329

19.1.2 “规划求解”选项 329

19.1.3 进行“规划求解” 331

19.1.4 分析规划求解结果 334

19.1.5 关于规划求解的常见

10.2.1 问题 339

19.2 规划求解实例 340

19.2.1 公司雇佣计划 340

19.2.2 签订合同计划 347

19.2.3 汇款接收处计划 356

19.2.4 配料计划 364

19.2.5 运输计划 373

第3部分 综 合 应 用

20 综合案例 384

20.1 数组求和 384

20.1.1 问题描述 384

20.1.2 普通公式求解 385

20.1.3 数组公式求解 386

20.1.4 通用的解答方法 389

20.1.5 问题总结 390

20.2 投资决策比较 392

20.2.1 问题描述 392

20.2.2 比较项目的净现值 392

20.2.3 计算动态贴现率的净现值 394

20.2.4 制作净现值-贴现率图表 397

20.2.5 问题总结 404

20.3 筛选模式下的分类汇总 404

20.3.1 问题描述 404

20.3.2 普通公式求解一 405

20.3.3 普通公式求解二 408

20.3.4 数组公式求解 410

20.3.5 三维计算函数SUBTOTAL

10.2.1 介绍 413

20.4 本量利分析 415

20.4.1 基准模型 415

20.4.2 改进的模型 420

20.4.3 单位边际模型 428

20.4.4 迭代递归求解盈亏平衡点 435

附录A Excel函数功能速查 4382100433B

本书主要面向希望深入学习Excel的公式、函数、图表和基本数据分析方法的用户，帮助读者全面掌握Excel的函数和图表、数据分析方法。本书一方面由浅入深、循序渐进地阐述使用Excel的函数和数据分析的方法；另一方面则通过实例来展现其功能。全书以图例引导为主，向读者传达更多的信息。

本书由浅入深、循序渐进地介绍了Excel的函数、图表及数据分析的内容。本书共有20章，分为3个部分：函数应用、数据分析和综合应用。其中第1-10章为第1部分，内容涵盖了Excel基础知识，以及逻辑、数学、统计、查询、数据库、文本、日期、信息、财务等9大类函数，详细介绍了每个函数的表达式、参数、功能，并举出相应的实例；第11-19章为第2部分，内容涵盖了创建图表，以及一些常用图表，如柱形图、条形图、折线图等的使用技巧、应用实例和特点分析，同时包括了模拟运算表、数据透视表、规划求解等基本数据分析的内容；第20章为第3部分，结合前面的基本内容，介绍了多个综合实例。本书最大的特色在于每一节的例子都是经过了精挑细选的，具有很强的针对性，力求让读者掌握基本的参数及使用技巧，能更灵活地解决实际问题。

本书适合Excel各版本软件的使用者、办公室计算机用户、中高级计算机爱好者，也可以作为相关专业的教材使用。

第1篇 基础部分

第2篇 Excel函数应用

第3篇 Excel图表操作

第4篇 Excel数据统计 2100433B

本书由浅入深、循序渐进地介绍了Excel的函数、图表及数据分析的内容。全书共有18章，分为4篇：函数篇、图表篇、数据分析篇和综合实例篇。其中第1~11章为函数篇，内容涵盖Excel的公式、日期、时间、数学、统计、查询、引用、数据库、工程、文本、逻辑、信息和财务等工作簿函数；第12~15章为图表篇，内容涵盖柱形图、条形图、折线图、饼图、XY散点图、面积图、圆环图、雷达图及股价图等Excel基本图表的使用技巧、应用实例和特点分析；第16~17章为数据分析篇，内容涵盖了单变量求解、模拟运算表、数据透视表和规划求解等基本数据分析的内容；第18章为第4篇，结合前面的基本内容，介绍综合实例。本书最大的特色在于每一个例子都经过精挑细选，具有很强的针对性，力求让读者掌握基本的参数及使用技巧，学习尽可能多的知识。

本书适合Excel各版本软件的使用者、办公室计算机用户、中高级计算机爱好者，也可以作为相关专业的教材使用。

本书讲解了Excel2007的300多个函数、19类图表及多种统计与分析数据的方法。全书由5篇，共21章组成，循序渐进地讲述了Excel2007的界面和基本操作、公式和函数，各种不同类别的函数的功能和应用，以及各种图表的实际选择和操作，常用的数据处理和分析工具，最后模拟实际情景，统计和分析了三个综合实例。