Excel 2010公式·函数·图表与数据分析

　　本书以通俗易懂的语言、精挑细选的实用技巧和翔实的操作案例，全面介绍了Excel2010公式、函数、图表与数据分析的基础知识以及应用案例，主要内容包括Excel2010快速入门与应用、Excel公式与函数基础知识、公式审核与错误处理、文本与逻辑函数、日期与时间函数、数学与三角函数、财务函数、统计函数、查找与引用函数、数据库函数、图表入门与应用、数据处理与分析，以及使用数据透视表和数据透视图等方面的知识、技巧及应用案例。
　　本书配套一张多媒体全景教学光盘，收录了书中全部知识点的视频教学课程，同时还赠送了4套相关视频教学课程，以及多本电子图书和相关行业规范知识，可以帮助读者循序渐进地学习、掌握和提高。
　　本书可作为有一定Excel基础操作知识的读者学习公式、函数、图表与数据分析的参考用书，既可以作为函数速查工具手册，又可以作为丰富的函数应用案例宝典，适合广大电脑爱好者及各行各业人员作为学习Excel的自学手册使用，同时还可以作为初中级电脑培训班的电脑课堂教材。

目    录

第1章  Excel快速入门与应用 1

1.1  Excel的启动与退出 2

1.1.1  启动Excel 2

1.1.2  熟悉Excel 2010工作界面 3

1.1.3  退出Excel 7

1.2  工作簿的基本操作 8

1.2.1  创建新工作簿 8

1.2.2  开始输入数据 10

1.2.3  保存和关闭工作簿 11

1.2.4  打开保存的工作簿 13

1.2.5  保护工作簿 14

1.3  工作表的基本操作 15

1.3.1  选取工作表 15

1.3.2  重命名工作表 16

1.3.3  添加与删除工作表 18

1.3.4  复制与移动工作表 19

1.3.5  保护工作表 21

1.4  单元格的基本操作 23

1.4.1  选取单元格 23

1.4.2  插入单元格 24

1.4.3  清除与删除单元格 27

1.4.4  复制与移动单元格 31

1.5  格式化工作表 32

1.5.1  调整表格行高与列宽 32

1.5.2  设置字体格式 34

1.5.3  设置对齐方式 36

1.5.4  添加表格边框 36

1.5.5  自动套用格式 38

1.6  实践案例与上机指导 39

1.6.1  添加背景图案 39

1.6.2  为工作表标签添加颜色 40

1.7  思考与练习 41

第2章  Excel公式与函数基础知识 43

2.1  认识公式与函数 44

2.1.1  什么是公式 44

2.1.2  什么是函数 44

2.2  单元格引用 45

2.2.1  相对引用 45

2.2.2  绝对引用 46

2.2.3  混合引用 47

2.2.4  改变引用类型 49

2.3  公式中的运算符及其优先级 50

2.3.1  算术运算符 51

2.3.2  比较运算符 51

2.3.3  引用运算符 51

2.3.4  文本运算符 52

2.3.5  运算符的优先级顺序 52

2.4  输入与编辑公式 53

2.4.1  输入公式 53

2.4.2  修改公式 55

2.4.3  公式的复制与移动 56

2.4.4  公式的显示与隐藏 59

2.4.5  删除公式 61

2.5  函数的结构和种类 63

2.5.1  函数的结构 63

2.5.2  函数的种类 63

2.6  输入函数的方法 64

2.6.1  直接输入函数 64

2.6.2  通过【插入函数】对话框

输入 65

2.7  定义和使用名称 67

2.7.1  定义名称 67

2.7.2  根据所选内容一次性创建多个

名称 70

2.7.3  让定义的名称只应用于当前

工作表 71

2.8  实践案例与上机指导 73

2.8.1  将公式结果转换为数值 73

2.8.2  使用公式统计销售数据 74

2.9  思考与练习 75

第3章  公式审核与错误处理 77

3.1  审核公式 78

3.1.1  使用公式错误检查功能 78

3.1.2  添加追踪箭头追踪引用

和从属单元格 79

3.1.3  监视单元格内容 81

3.1.4  定位特定类型的数据 82

3.1.5  使用公式求值 83

3.2  公式返回错误及解决办法 85

3.2.1  #DIV/0!错误及解决方法 85

3.2.2  #N/A错误及解决方法 85

3.2.3  #NAME?错误及解决方法 86

3.2.4  #NULL!错误及解决方法 87

3.2.5  #NUM!错误及解决方法 87

3.2.6  #REF!错误及解决方法 88

3.2.7  #VALUE!错误及解决方法 88

3.2.8  #####错误及解决方法 89

3.3  处理公式中常见的错误 89

3.3.1  括号不匹配 89

3.3.2  循环引用 90

3.3.3  空白但非空的单元格 91

3.3.4  显示值与实际值 91

3.3.5  返回错误值 92

3.4  实践案例与上机指导 92

3.4.1  在工作表中显示公式

而非计算结果 92

3.4.2  取消追踪箭头 94

3.5  思考与练习 95

第4章  文本与逻辑函数 97

4.1  文本函数 98

4.2  文本函数应用举例 99

4.2.1  使用CONCATENATE函数

自动提取序号 99

4.2.2  使用REPLACE函数为电话

号码升级 100

4.2.3  使用MID函数从身份证中

提取出生日期 100

4.2.4  使用SUBSTITUTE函数

缩写名称 101

4.2.5  使用LEN函数检查身份证

位数是否正确 102

4.2.6  使用EXACT函数比较产品

编号是否相同 103

4.2.7  使用FIXED函数解决因四舍

五入造成的显示误差问题 104

4.2.8  使用LEFT函数从E-mail

地址中提取账号 105

4.2.9  使用FIND函数提取英文

名字 105

4.2.10  使用LOWER函数将文本

 转换为小写 106

4.3  逻辑函数 107

4.4  逻辑函数应用举例 108

4.4.1  使用TRUE函数判断两列

数据是否相同 108

4.4.2  使用OR函数判断员工考核

是否达标 108

4.4.3  使用AND函数判断歌手

是否晋级 109

4.4.4  使用NOT函数进行筛选 110

4.4.5  使用IF函数判断员工是否

完成销售任务量 111

4.4.6  使用FALSE函数判断两列

数据是否相等 112

4.4.7  使用IFERROR函数检查

数据的正确性 113

4.5  实践案例与上机指导 113

4.5.1  将汉语拼音的人名转换为

英文全拼人名 114

4.5.2  根据年龄判断职工是否

退休 114

4.5.3  去掉文本中的所有空格 115

4.6  思考与练习 116

第5章  日期与时间函数 117

5.1  日期函数 118

5.1.1  Excel提供的两种日期系统 118

5.1.2  日期序列号和时间序列号 118

5.1.3  常用的日期函数 119

5.2  日期函数应用举例 119

5.2.1  使用DATEVALUE函数计算

请假天数 119

5.2.2  使用DATE函数计算已知

第几天对应的准确日期 120

5.2.3  使用DAYS360函数计算

总借款天数 122

5.2.4  使用WEEKDAY函数查看

某一日期对应的星期数 122

5.2.5  使用EOMONTH函数计算

指定日期到月底的天数 124

5.2.6  使用WORKDAY计算项目

完成日期 125

5.2.7  用NETWORKDAYS计算两个

日期之间的实际工作日 125

5.2.8  使用TODAY函数返回当前

日期与星期数 126

5.2.9  使用NOW函数确定是否已到

加油时间 127

5.2.10  使用YEAR函数计算员工的

 年龄 127

5.2.11  使用EDATE函数计算还款

 日期 129

5.3  时间函数 129

5.4  时间函数应用举例 130

5.4.1  使用TIME函数计算比赛

到达终点的时间 130

5.4.2  使用TIMEVALUE函数计算

口语测试时间 131

5.4.3  使用HOUR函数计算停车的

小时数 132

5.4.4  使用MINUTE函数精确到分钟

计算工作时间 133

5.4.5  使用SECOND函数计算广告

时长 134

5.5  实践案例与上机指导 135

5.5.1  将非日期数据转换为标准的

日期 135

5.5.2  计算工程时间 135

5.6  思考与练习 136

第6章  数学与三角函数 139

6.1  常用的数学与三角函数 140

6.2  常规计算 141

6.2.1  使用SUM函数求出学生的

总成绩 142

6.2.2  使用SUMIF函数计算仓库

进库数量之和 142

6.2.3  使用ABS函数计算两地的

温差 143

6.2.4  使用PRODUCT函数计算

长方形的体积 144

6.3  舍入计算 144

6.3.1  使用CEILING函数计算

通话费用 145

6.3.2  使用FLOOR函数计算员工的

提成奖金 145

6.3.3  使用INT函数计算员工平均

销售金额(以整数进行舍入) 146

6.3.4  使用TRUNC函数计算利润额

(以2位小数形式) 147

6.4  阶乘与随机数 147

6.4.1  使用COMBIN函数确定

组合数目 147

6.4.2  使用RAND函数随机创建

彩票号码 148

6.4.3  使用SUMPRODUCT函数统计

多种产品的总销售金额 149

6.5  指数与对数 149

6.5.1  使用LOG函数计算无噪信道

传输能力 149

6.5.2  使用LOG10函数计算

分贝数 150

6.5.3  使用LN函数计算均衡

修正项 151

6.6  三角函数计算 151

6.6.1  使用ACOS函数利用

反余弦求角 151

6.6.2  使用DEGREES函数计算

角度 152

6.6.3  使用RADIANS函数计算

弧长 153

6.6.4  使用SIN函数计算直角三角形

中锐角的对边长度 153

6.6.5  使用ATAN2函数计算射击

目标的方位角 154

6.7  其他数学和三角函数 155

6.7.1  使用PI函数计算圆周长 155

6.7.2  使用ROMAN函数将阿拉伯

数字转换为罗马数字 156

6.7.3  使用SUBTOTAL函数计算

数据中员工的年薪总和 157

6.8  实践案例与上机指导 158

6.8.1  使用SUMIFS函数统计某日期

区间的销售金额 158

6.8.2  使用MOD函数统计出每次

通话时长的剩余秒数 159

6.9  思考与练习 160

第7章  财务函数 161

7.1  常用财务函数名称及功能 162

7.2  折旧值计算函数 163

7.2.1  使用SLN函数计算固定资产

的折旧值 163

7.2.2  使用SYD函数计算固定资产

每年的折旧额 164

7.2.3  使用DB函数计算每年的

折旧值 165

7.2.4  使用VDB函数计算折旧

期间的折旧值 166

7.3  投资计算函数 167

7.3.1  使用PV函数计算某项投资

的现值 167

7.3.2  使用NPV函数计算某项投资

的净现值 169

7.3.3  使用FV函数计算某项投资

的未来值 169

7.3.4  使用NPER函数计算出贷款

的清还年数 170

7.4  本金与利息函数 171

7.4.1  使用PMT函数计算贷款每期

付款金额 171

7.4.2  使用IPMT函数计算每年的

利息偿还金额 171

7.4.3  使用EFFECT函数将名义

年利率转换为实际年利率 172

7.4.4  使用NOMINAL函数计算某

债券的名义年利率 173

7.4.5  使用RATE函数计算某项借款

的收益率 173

7.5  收益率函数 174

7.5.1  使用IRR函数计算某项投资

的内部收益率 174

7.5.2  使用MIRR函数计算某项投资

的修正内部收益率 175

7.5.3  使用XIRR计算未必定期发生

的现金流的内部收益率 176

7.6  债券与证券函数 177

7.6.1  使用ACCRINT函数计算定期

付息债券应计利息 177

7.6.2  计算当前付息期内截止到

成交日的天数 178

7.6.3  使用COUPNCD函数计算

成交日之后的下一个付息日 178

7.6.4  使用DISC函数计算有价证券

的贴现率 179

7.6.5  使用INTRATE函数计算

一次性付息证券的利率 180

7.6.6  使用YIELD函数计算有价

证券的收益率 180

7.7.7  使用YIELDMAT计算到期付息

的有价证券的年收益率 181

7.7  实践案例与上机指导 182

7.7.1  计算某项投资在可变利率下的

未来值 182

7.7.2  计算贷款指定期间的本金

偿还金额 183

7.8  思考与练习 184

第8章  统计函数 187

8.1  常用的统计函数名称及功能 188

8.2  平均值函数 188

8.2.1  使用AVERAGE函数统计

成绩平均值 189

8.2.2  使用AVERAGEA函数求

包含文本值的平均值 189

8.2.3  使用AVERAGEIF函数求

每季度平均支出金额 190

8.2.4  使用AVERAGEIFS函数计算满足

多重条件数据的平均值 191

8.2.5  使用GEOMEAN函数计算

销售量的几何平均值 192

8.2.6  使用TRIMMEAN函数进行

评分统计 192

8.3  数理统计函数 193

8.3.1  使用MODE函数统计哪种商品

最受欢迎 193

8.3.2  使用GROWTH函数预测全年

的销售金额 194

8.3.3  使用FREQUENCY函数分别统计

每个分数段的人员个数 195

8.4  条目统计函数 196

8.4.1  使用COUNT函数统计生产

车间异常机台个数 196

8.4.2  使用COUNTIF函数统计出

大于指定数值的记录条数 197

8.4.3  使用COUNTIFS函数统计满足

多个条件的记录数目 198

8.4.4  使用COUNTBLANK函数统计

未检验完成的产品数 198

8.5  最大值与最小值函数 199

8.5.1  使用LARGE函数返回数据表

中前三名分数 199

8.5.2  使用SMALL函数计算数据表

中最后3名分数 200

8.5.3  使用MAX函数统计最高

分数 201

8.5.4  使用MIN函数统计最低

分数 201

8.5.5  使用MINA函数计算学生考试

成绩中最低分数(包含文本) 202

8.5.6  使用MAXA函数计算学生考试

成绩中最高分数(包含文本) 203

8.6  实践案例与上机指导 204

8.6.1  统计出大于平均值的记录

条数 204

8.6.2  忽略0值求最小值 204

8.6.3  按条件求取最大值 205

8.7  思考与练习 206

第9章  查找与引用函数 207

9.1  查找和引用函数概述 208

9.2  普通查询 208

9.2.1  使用CHOOSE函数判断成绩

是否合格 208

9.2.2  使用LOOKUP函数查找信息

(向量型) 209

9.2.3  使用LOOKUP函数查找信息

(数组型) 211

9.2.4  使用VLOOKUP函数将得分

转换成等级 212

9.2.5  使用CHOOSE函数判断成绩

是否合格 213

9.3  引用查询 214

9.3.1  使用INDEX函数按指定条件

查找学生某项成绩 214

9.3.2  使用MATCH函数根据编号

查询指定员工的基本工资 215

9.3.3  使用TRANSPOSE函数实现

行列转置 216

9.3.4  使用OFFSET函数实现动态

查询 217

9.4  引用表中数据 218

9.4.1  使用ROWS函数计算出需要

开支的员工人数 218

9.4.2  使用ADDRESS函数查询随机

抽查的产品编号地址 219

9.4.3  使用AREAS函数统计分公司

数量 220

9.4.4  使用COLUMNS函数计算需要

扣款的项目数量 221

9.4.5  使用GETPIVOTDATA函数

从数据透视表中提取数据 221

9.4.6  使用HYPERLINK函数在数据

表中指定公司邮件地址 223

9.5  实践案例与上机指导 223

9.5.1  使用ROW函数建立三级序列

编号 224

9.5.2  使用COLUMN函数建立三级

序列编号 224

9.5.3  使用INDEX与MATCH函数

双条件查询 225

9.6  思考与练习 226

第10章  数据库函数 227

10.1  数据库函数概述 228

10.2  计算数据库中的数据 228

10.2.1  使用DPRODUCT函数计算出

 销售数量的乘积值 228

10.2.2  使用DSUM函数统计去除指定

 条件的数据 229

10.3  数据库常规统计 230

10.3.1  使用DAVERAGE函数统计

 某个职业的平均工资 230

10.3.2  使用DCOUNT函数统计满足

 条件的数据 231

10.3.3  使用DCOUNTA统计基本

 工资小于800元的人数 232

10.3.4  使用DGET统计指定销售日期

 和产品名称对应的销量 233

10.3.5  使用DMAX函数计算商品销售

 数量的最大值 234

10.3.6  使用DMIN函数计算商品销售

 数量的最小值 235

10.4  数据库散布度统计 236

10.4.1  使用DSTDEV函数计算员工的

 年龄标准差 236

10.4.2  使用DSTDEVP函数计算员工

 总体的年龄标准差 237

10.4.3  使用DVAR函数计算男员工

 销售量的方差 238

10.4.4  使用DVARP函数计算男员工

 销售量的总体方差 239

10.5  实践案例与上机指导 239

10.5.1  在DSUM函数中使用

 通配符 240

10.5.2  在DAVERAGE函数参数中

 使用通配符 240

10.6  思考与练习 241

第11章  图表入门与应用 243

11.1  认识图表 244

11.1.1  图表的类型 244

11.1.2  图表的组成 248

11.2  创建图表的方法 249

11.2.1  通过对话框创建图表 249

11.2.2  使用功能区创建图表 250

11.2.3  使用快捷键创建图表 254

11.3  设置图表 255

11.3.1  更改图表类型 255

11.3.2  更改数据源 256

11.3.3  设计图表布局 257

11.3.4  设计图表样式 258

11.4  创建各种类型的图表 259

11.4.1  使用折线图显示产品销量 259

11.4.2  使用饼图显示人口比例 260

11.4.3  使用柱形图显示员工培训

 成绩 261

11.4.4  使用XY散点图创建测试

 对比图 262

11.5  美化图表 263

11.5.1  设置图表标题 263

11.5.2  设置图表区 265

11.5.3  设置绘图区 266

11.5.4  设置图例 267

11.5.5  设置坐标轴标题 267

11.5.6  设置网格线 268

11.6  实践案例与上机指导 270

11.7  思考与练习 271

第12章  数据处理与分析 273

12.1  数据的筛选 274

12.1.1  自动筛选 274

12.1.2  高级筛选 275

12.2  数据的排序 276

12.2.1  单条件排序 277

12.2.2  多条件排序 277

12.2.3  按行排序 278

12.2.4  按列排序 279

12.3  数据的分类汇总 280

12.3.1  简单分类汇总 280

12.3.2  清除分类汇总 281

12.4  合并计算 282

12.4.1  按位置合并计算 283

12.4.2  按分类合并计算 285

12.5  分级显示数据 287

12.5.1  新建分级显示 287

12.5.2  隐藏与显示明细数据 289

12.6  实践案例与上机指导 289

12.6.1  自动筛选日期和时间 289

12.6.2  多种分类汇总 291

12.7  思考与练习 292

第13章  使用数据透视表和数据

  透视图 293

13.1  认识数据透视表与数据透视图 294

13.1.1  认识数据透视表 294

13.1.2  认识数据透视图 294

13.1.3  数据透视表与数据透视图

 的区别 295

13.2  创建与编辑数据透视表 296

13.2.1  创建数据透视表 296

13.2.2  设置数据透视表字段 297

13.2.3  删除数据透视表 298

13.3  操作数据透视表中的数据 299

13.3.1  刷新数据透视表 299

13.3.2  数据透视表的排序 299

13.3.3  更改数据透视表的汇总

 方式 300

13.3.4  筛选数据透视表中的

 数据 301

13.4  美化数据透视表 302

13.4.1  应用数据透视表布局 302

13.4.2  应用数据透视表样式 303

13.5  创建与操作数据透视图 304

13.5.1  使用数据区域创建数据

 透视图 304

13.5.2  更改数据透视图类型 304

13.5.3  筛选数据 305

13.5.4  清除数据图 307

13.6  实践案例与上机指导 307

13.6.1  设置数据透视图布局 307

13.6.2  应用数据透视图样式 308

13.7  思考与练习 309

思考与练习答案 311