新编Excel 2016应用大全 实战精华版PDF电子书下载

第一篇 基础篇 2

第1章 初识Excel 2016 2

1.1 Excel简介 3

1.2 Excel 2016的启动与退出 4

1.2.1 启动Excel 2016 4

1.2.2 退出Excel 2016 5

1.2.3 创建桌面快捷方式 5

1.3 Excel 2016工作环境简介 5

1.3.1 工作界面概述 5

1.3.2 功能区 6

1.3.3 “文件”按钮 9

1.3.4 快速访问工具栏 10

1.3.5 使用快捷键 10

1.3.6 右键快捷菜单 10

1.3.7 工作簿和工作表 11

1.3.8 使用状态栏 11

1.4 文件转换与兼容性 12

1.4.1 Excel 2016支持的文件格式 12

1.4.2 检查工作簿与Excel早期版本的兼容性 13

1.4.3 以其他文件格式保存工作簿 14

1.4.4 更改默认的文件保存格式 14

1.5 如何学习Excel 2016 15

1.5.1 使用网络查找资源 16

1.5.2 使用微软在线帮助 16

第2章 工作簿和工作表的基本操作 17

2.1 工作簿的基础操作 18

2.1.1 新建空白工作簿 18

2.1.2 依据模板建立新工作簿 19

2.1.3 保存工作簿 20

2.1.4 将建立的工作簿保存为模板 21

2.1.5 设置工作簿的默认保存格式 21

2.1.6 关闭当前工作簿 22

2.2 工作表基础操作 22

2.2.1 重命名工作表 23

2.2.2 添加和删除工作表 23

2.2.3 移动工作表 24

2.2.4 复制工作表 25

2.2.5 拆分工作表 27

2.2.6 保护工作表 28

第3章 使用单元格、行和列 30

3.1 单元格的基本操作和格式设置 31

3.1.1 插入单元格 31

3.1.2 删除单元格 32

3.1.3 合并单元格 32

3.1.4 自定义单元格的数字格式 34

3.1.5 设置单元格的文本对齐方式 35

3.1.6 让单元格中的内容实现自动换行 36

3.1.7 缩小字体以适合单元格大小 36

3.1.8 合并单元格内容的几个小技巧 37

3.1.9 快速取消合并的单元格 39

3.1.10 改变单元格中的文字方向 39

3.1.11 同一单元格中的文本使用不同的格式 40

3.1.12 为工作表绘制斜线表头 41

3.1.13 制作双斜线表头 42

3.1.14 为单元格添加框线与底纹 43

3.1.15 使用条件格式突出显示单元格 45

3.1.16 套用表格格式快速改变表格外观 46

3.1.17 使用单元格样式快速设置单元格格式 47

3.1.18 在特定单元格中插入批注 49

3.1.19 一次性清除单元格中所有格式设置 50

3.1.20 选择性删除单元格中的内容或格式 51

3.2 行和列的基本操作 52

3.2.1 在指定位置插入行或列 52

3.2.2 在工作表中快速插入多个行或列 53

3.2.3 隔行插入行或列的实现方法 53

3.2.4 将工作表中的一列分为多列 54

3.2.5 冻结或锁定工作表的行与列 55

3.2.6 完美冻结第一行与第一列的技巧 56

3.2.7 快速定位特别长的行数据 56

3.2.8 设置工作表的行高与列宽 57

3.2.9 工作表行与列的移动与复制 59

3.2.10 批量删除工作表中的行或列 59

3.2.11 让行与列实现交叉着色 61

3.2.12 隐藏或显示特定的行与列 62

3.2.13 让工作表中的多行与一行并存 63

3.2.14 轻松互换行与列的数据 63

3.2.15 快速删除工作表中所有的空行 64

第4章 表格数据的输入与编辑 66

4.1 表格数据的输入 67

4.1.1 输入数据 67

4.1.2 相同数据的快速填充 67

4.1.3 有规则数据的填充 68

4.1.4 非连续单元格数据的填充 69

4.2 格式化数据 69

4.2.1 快速设置数据格式 69

4.2.2 设置数据格式 70

4.2.3 自定义数据格式 72

4.2.4 固定小数位数 73

4.2.5 设置数据的有效范围 73

4.3 表格数据的移动、复制与粘贴 75

4.3.1 通过“剪贴板”来移动、复制和粘贴数据 75

4.3.2 通过快捷键来移动、复制和粘贴数据 76

4.3.3 “选择性粘贴”功能的应用 78

4.4 数据查找与替换 79

4.4.1 数据查找 79

4.4.2 数据替换 81

第5章 表格的美化 84

5.1 表格字体与对齐方式设置 85

5.1.1 设置表格字体 85

5.1.2 设置表格对齐方式 86

5.2 表格边框与底纹设置 87

5.2.1 设置表格边框效果 87

5.2.2 设置表格底纹效果 89

5.3 套用样式美化单元格与表格 90

5.3.1 套用单元格样式 90

5.3.2 新建单元格样式 91

5.3.3 套用表格样式 93

5.3.4 新建表格样式 95

第6章 工作表的打印、共享与安全设置 97

6.1 工作表的打印 98

6.1.1 快速设置打印页面 98

6.1.2 对工作表进行缩放打印 98

6.1.3 对工作表设置分页符 99

6.1.4 对工作表设置打印区域 100

6.1.5 对工作表设置打印标题行 101

6.2 工作簿的共享 102

6.2.1 创建共享工作簿 102

6.2.2 创建受保护的共享工作簿 103

6.2.3 跟踪工作簿的修订 104

6.2.4 接受或拒绝修订 105

6.3 设置Excel文档的安全性 106

6.3.1 保护工作簿 106

6.3.2 保护工作表 108

6.3.3 设置允许用户编辑的区域 109

6.3.4 保护公式 110

6.4 工作簿的网络应用 112

6.4.1 获取网上数据 113

6.4.2 创建交互式Web页面文件 115

第二篇 函数篇 118

第7章 公式、数组及引用操作 118

7.1 认识公式 119

7.1.1 公式概述 119

7.1.2 查找和更正公式中的错误 119

7.1.3 公式兼容性问题 120

7.1.4 移动或复制公式 121

7.2 认识数组 122

7.2.1 深刻理解数组概念 122

7.2.2 数组与数组公式 122

7.2.3 数组公式的用途 122

7.3 利用数组模拟关系 123

7.3.1 利用数组模拟AND和OR 123

7.3.2 利用数组模拟IF（） 123

7.4 创建单元格引用 124

7.4.1 在同一工作表上创建单元格引用 124

7.4.2 创建对其他工作表的单元格引用 126

7.4.3 通过使用“粘贴链接”命令创建单元格引用 127

7.4.4 断开外部引用的链接 127

7.4.5 将单元格引用更改为其他单元格引用 128

7.4.6 将单元格引用更改为命名区域 128

7.4.7 在相对引用、绝对引用和混合引用间切换 129

7.4.8 删除或允许使用循环引用 129

第8章 名称的使用 130

8.1 认识名称 131

8.1.1 Excel中名称定义的重要作用 131

8.1.2 名称定义的规则何在 131

8.2 定义名称 131

8.2.1 利用“新建名称”对话框快速定义名称 131

8.2.2 利用名称框快速定义名称 132

8.2.3 一次性定义多个名称 133

8.2.4 利用公式定义名称 134

8.2.5 利用常量定义名称 135

8.2.6 利用名称定义不连续的单元格区域 136

8.2.7 将定义的名称只应用于当前工作表 136

8.2.8 查看当前工作簿中的所有名称 136

8.3 编辑名称 137

8.3.1 编辑名称引用 137

8.3.2 创建动态名称 137

8.3.3 快速选择名称对应的单元格区域 138

8.3.4 修改已经定义的名称 139

8.3.5 删除过期名称 140

第9章 函数的基本使用 141

9.1 认识函数 142

9.1.1 认识函数的重要作用 142

9.1.2 函数的构成 142

9.1.3 函数的参数及其说明 142

9.1.4 函数的种类 143

9.2 函数使用技巧 144

9.2.1 巧妙输入和编辑函数公式 144

9.2.2 利用函数工具提示轻松掌握函数 145

9.2.3 妙用函数公式的查错与监视功能 147

9.2.4 保护和隐藏工作表中的函数公式 148

9.2.5 按步查看公式中的计算结果 149

9.2.6 如何引用单元格 150

9.2.7 掌握数据类型区分及转换的技巧 151

9.2.8 判断逻辑关系 152

9.2.9 熟练掌握运算符号 153

9.2.10 浅谈函数公式的限制和突破 154

9.2.11 巧妙处理函数参数 155

9.2.12 妙解函数的易失性 155

第10章 日期与时间计算函数应用 156

10.1 TODAY函数：显示当前系统日期 157

10.2 DAY函数：显示任意日期 157

10.3 DATEVALUE函数：将文本格式的日期转换为序列号 158

10.4 DATE函数：将数值转换为日期格式 159

10.5 DATE函数：将非日期数据转换为标准日期 160

10.6 DAYS360函数：计算两个日期之间的天数 161

第11章 文本与信息处理函数应用 163

11.1 函数使用 164

11.1.1 ISTEXT函数：判断单元格中是否包含文本 164

11.1.2 ASC函数：将全角字符转换为半角字符 164

11.1.3 WIDECHAR函数：将半角字符转换为全角字符 165

11.1.4 EXACT函数：判断两个字符串是否相同 166

11.1.5 LEN函数：计算文本字符串中的字符个数 167

11.1.6 BAHTTEXT函数：使用泰铢货币格式将数字转换为文本 167

11.1.7 DOLLAR函数：使用美元货币格式将数字转换为文本 168

11.1.8 CLEAN函数：删除文本中的所有非打印字符 169

11.1.9 CODE函数：计算文本字符串中第一个字符的数字代码 170

11.1.10 CHAR函数：由代码数字返回指定字符 171

11.1.11 FIND函数：在一个字符串值中查找另一个字符串值 172

11.1.12 TRIM函数：查找并删除文本中的空格符 174

11.1.13 LOWER和UPPER函数：文本的大小写转换 175

11.1.14 SUBSTITUTE函数：替换文本 177

11.1.15 REPLACE函数：字符串中的查找和替换 178

11.2 案例介绍 179

11.2.1 综合案例1——提取公司员工出生日期 179

11.2.2 综合案例2——专家信息统计 182

第12章 逻辑函数应用技巧 184

12.1 判断真假值的逻辑函数应用技巧 185

12.1.1 应用AND函数进行交集运算 185

12.1.2 应用FALSE函数判断逻辑值为假 186

12.1.3 应用NOT函数计算反函数 186

12.1.4 应用OR函数进行并集运算 187

12.1.5 应用TRUE函数判断逻辑值为真 188

12.2 进行复合检验的逻辑函数应用技巧 189

12.2.1 应用IF函数对真假函数进行判断 189

12.2.2 应用IFERROR函数自定义公式错误时的提示函数 190

12.3 综合实战 191

第13章 数学与三角函数应用技巧 193

13.1 数学函数应用技巧 194

13.1.1 应用ABS函数计算绝对值 194

13.1.2 应用CEILING函数按条件向上舍入数值 194

13.1.3 应用COMBIN函数计算给定数目对象的组合数 196

13.1.4 应用EVEN函数计算取整后最接近的偶数 196

13.1.5 应用EXP函数计算e的n次幂 197

13.1.6 应用FACT函数计算某数的阶乘 198

13.1.7 应用FACTDOUBLE函数计算数字的双倍阶乘 199

13.1.8 应用FLOOR函数计算向下舍入最接近的倍数 200

13.1.9 应用GCD函数和LCM函数计算整数的最大公约数和最小公倍数 201

13.1.10 应用INT函数将数字向下舍入到最接近的整数 201

13.1.11 应用LN函数、LOG函数和LOG10函数计算对数 202

13.1.12 应用MDETERM函数计算矩阵行列式的值 203

13.1.13 应用MINVERSE函数和MMULT函数计算逆矩阵和矩阵乘积 204

13.1.14 应用MOD函数计算两数相除的余数 205

13.1.15 应用MROUND函数计算按指定基数舍入后的数值 206

13.1.16 应用MULTINOMIAL函数计算一组数字的多项式 207

13.1.17 应用ODD函数计算对指定数值向上舍入后的奇数 208

13.1.18 应用PI函数和SQRTPI函数计算π值和返回某数与π的乘积的平方根 209

13.1.19 应用POWER函数计算给定数字的乘幂 209

13.1.20 应用PRODUCT函数计算指定数值的乘积 210

13.1.21 应用QUOTIENT函数计算商的整数部分 211

13.1.22 应用RAND函数和RANDBETWEEN函数计算随机实数和随机整数 211

13.1.23 应用ROMAN函数将阿拉伯数字转换为罗马数字 213

13.1.24 应用ROUND函数、ROUNDDOWN函数和ROUNDUP函数按位数进行舍入 213

13.1.25 应用SERIESSUM函数计算基于公式的幂级数之和 215

13.1.26 应用SIGN函数计算数字的符号 215

13.1.27 应用SQRT函数计算正平方根 216

13.1.28 应用SUBTOTAL函数计算列表或数据库中的分类汇总 217

13.1.29 应用SUM函数求和 218

13.1.30 应用SUMIF函数按给定条件对指定单元格求和 219

13.1.31 应用SUMIFS函数对某一区域内满足多重条件的单元格求和 220

13.1.32 应用SUMPRODUCT函数计算数组间元素乘积之和 221

13.1.33 应用SUMSQ函数计算参数的平方和 222

13.1.34 应用SUMXMY2函数计算两数组中对应数值之差的平方和 222

13.1.35 应用SUMX2MY2函数和SUMX2PY2函数计算两数组中对应数值的平方差之和与平方和之和 223

13.1.36 应用TRUNC函数将数字的小数部分截去返回整数 224

13.2 三角函数应用技巧 224

13.2.1 应用ACOS函数计算数字的反余弦值 224

13.2.2 应用ACOSH函数计算数字的反双曲余弦值 225

13.2.3 应用ASIN函数计算数字的反正弦值 226

13.2.4 应用ASINH函数计算数字的反双曲正弦值 227

13.2.5 应用ATAN函数计算数字的反正切值 227

13.2.6 应用ATANH函数计算数字的反双曲正切值 228

13.2.7 应用ATAN2函数计算X及Y坐标值的反正切值 229

13.2.8 应用COS函数计算给定角度的余弦值 229

13.2.9 应用COSH函数计算某数字的双曲余弦值 230

13.2.10 应用DEGREES函数将弧度转换为度 231

13.2.11 应用RADIANS函数将角度转换为弧度 231

13.2.12 应用SIN函数计算给定角度的正弦值 232

13.2.13 应用SINH函数计算某数字的双曲正弦值 233

13.2.14 应用TAN函数计算给定角度的正切值 233

13.2.15 应用TANH函数计算某数字的双曲正切值 234

13.3 综合实战：计算个人所得税 234

第14章 数据库函数应用技巧 237

14.1 数据库函数概述 238

14.1.1 数据库函数的共同特点 238

14.1.2 数据库函数的参数介绍 238

14.1.3 对数据库函数的几点说明 238

14.1.4 对条件区域的几点说明 238

14.2 本章的基础数据清单 239

14.3 数据库函数应用技巧 239

14.3.1 应用DAVERAGE函数计算条目的平均值 239

14.3.2 应用DCOUNT函数计算包含数字的单元格的数量 241

14.3.3 应用DCOUNTA函数计算非空单元格的数量 242

14.3.4 应用DGET函数计算符合条件的记录 243

14.3.5 应用DMAX函数计算符合条件的最大数值 244

14.3.6 应用DMIN函数计算符合条件的最小数值 245

14.3.7 应用DPRODUCT函数计算指定数值的乘积 246

14.3.8 应用DSTDEV函数计算样本的估算标准偏差 247

14.3.9 应用DSTDEVP函数计算总体样本的标准偏差 248

14.3.10 应用DSUM函数计算指定数值的和 249

14.3.11 应用DVAR函数计算样本方差 250

14.3.12 应用DVARP函数计算总体方差 251

14.4 综合实战 252

第15章 查找和引用函数应用技巧 255

15.1 应用实例 256

15.1.1 应用ADDRESS函数以文本形式返回引用值 256

15.1.2 应用AREAS函数计算引用中的区域个数 257

15.1.3 应用CHOOSE函数从列表中选择数值 258

15.1.4 应用COLUMN函数计算给定引用的列标 259

15.1.5 应用COLUMNS函数计算数组或引用的列数 260

15.1.6 应用HLOOKUP函数实现水平查找 261

15.1.7 应用HYPERLIN函数创建快捷方式（跳转） 262

15.1.8 应用INDEX函数计算表或区域中的值或值的引用 263

15.1.9 应用INDIRECT函数计算指定的引用 265

15.1.10 应用LOOKUP函数查找数据 266

15.1.11 应用MATCH函数在数组中进行查找 268

15.1.12 应用OFFSET函数调整新的引用 269

15.1.13 应用ROW函数计算行号 270

15.1.14 应用ROWS函数计算引用的行数 271

15.1.15 应用RTD函数检索实时数据 271

15.1.16 应用TRANSPOSE函数计算转置单元格区域 272

15.1.17 应用VLOOKUP函数实现竖直查找 272

15.2 综合实战：学生成绩查询 273

第16章 统计函数应用技巧 276

16.1 平均值函数应用技巧 277

16.1.1 应用AVEDEV函数计算数据与其均值的绝对偏差平均值 277

16.1.2 应用AVERAGE函数计算参数的平均值 277

16.1.3 应用AVERAGEA函数计算参数列表中数值的平均值 278

16.1.4 应用AVERAGEIF函数计算满足条件的单元格的平均值 279

16.1.5 应用AVERAGEIFS函数计算满足多重条件的平均值 280

16.1.6 应用COVAR函数计算协方差 280

16.1.7 应用CONFIDENCE函数计算总体平均值的置信区间 281

16.1.8 应用GEOMEAN函数计算几何平均值 282

16.1.9 应用HARMEAN函数计算调和平均值 283

16.2 Beta分布函数应用技巧 284

16.2.1 应用BETADIST函数计算Beta累积分布函数 284

16.2.2 应用BETAINV函数计算指定Beta分布的累积分布函数的反函数 285

16.3 概率相关函数应用技巧 285

16.3.1 应用CHIDIST函数计算x2分布的单尾概率 286

16.3.2 应用CHIINV函数计算x2分布的单尾概率的反函数 286

16.3.3 应用CHITEST函数计算独立性检验值 287

16.3.4 应用BINOMDIST函数计算一元二项式分布的概率值 288

16.3.5 应用FDIST函数计算F概率分布 289

16.3.6 应用FINV函数计算F概率分布的反函数值 290

16.3.7 应用FREQUENCY函数计算以垂直数组的形式返回频率分布 291

16.3.8 应用FTEST函数计算F检验的结果 292

16.3.9 应用HYPGEOMDIST函数计算超几何分布 292

16.3.10 应用PROB函数计算区域中的数值落在指定区间内的概率 294

16.3.11 应用TTEST函数计算与学生的t检验相关的概率 294

16.3.12 应用ZTEST函数计算z检验的单尾概率值 295

16.4 单元格数量计算函数应用技巧 297

16.4.1 应用COUNT函数计算参数列表中数字的个数 297

16.4.2 应用COUNTA函数计算参数列表中值的个数 298

16.4.3 应用COUNTBLANK函数计算区域内空白单元格的数量 298

16.4.4 应用COUNTIF函数计算区域中满足给定条件的单元格的数量 299

16.4.5 应用COUNTIFS函数计算区域中满足多个条件的单元格的数量 300

16.5 指数与对数相关函数应用技巧 301

16.5.1 应用EXPONDIST函数计算指数分布 301

16.5.2 应用GAMMALN函数计算γ函数的自然对数 302

16.5.3 应用GROWTH函数计算沿指数趋势的值 302

16.5.4 应用LOGINV函数计算对数分布函数的反函数 304

16.5.5 应用LOGNORMDIST函数计算对数累积分布函数 304

16.6 最大值与最小值函数应用技巧 305

16.6.1 应用CRITBINOM函数计算使累积二项式分布小于或等于临界值的最小值 305

16.6.2 应用LARGE函数计算数据集中第k个最大值 306

16.6.3 应用MAX函数计算参数列表中的最大值 307

16.6.4 应用MAXA函数计算参数列表中的最大值（包括数字、文本和逻辑值） 308

16.6.5 应用MEDIAN函数计算给定数值集合的中值 308

16.6.6 应用MIN函数计算参数列表中的最小值 309

16.6.7 应用MINA函数计算参数列表中的最小值（包括数字、文本和逻辑值） 310

16.6.8 应用MODE函数计算在数据集内出现次数最多的值 311

16.6.9 应用SMALL函数计算数据集中的第k个最小值 312

16.7 标准偏差与方差函数应用技巧 312

16.7.1 应用DEVSQ函数计算偏差的平方和 312

16.7.2 应用STDEV函数计算基于样本的标准偏差 313

16.7.3 应用STDEVA函数计算基于样本（包括数字、文本和逻辑值）的估算标准偏差 314

16.7.4 应用STDEVP函数计算基于样本总体的标准偏差 315

16.7.5 应用STDEVPA函数计算基于总体的标准偏差 316

16.7.6 应用VARPA函数计算基于总体（包括数字、文本和逻辑值）的标准偏差 317

16.7.7 应用VAR函数计算基于样本的估算方差 318

16.7.8 应用VARA函数计算基于样本（包括数字、文本和逻辑值）的估算方差 318

16.7.9 应用VARP函数计算基于样本总体的方差 319

16.8 正态累积分布函数应用技巧 320

16.8.1 应用NORMDIST函数计算正态累积分布 320

16.8.2 应用NORMINV函数计算标准正态累积分布函数的反函数 321

16.8.3 应用NORMSDIST函数计算标准正态累积分布 322

16.8.4 应用NORMSINV函数计算标准正态累积分布函数的反函数 322

16.8.5 应用STANDARDIZE函数计算正态化数值 323

16.9 线性回归相关函数应用技巧 324

16.9.1 应用SLOPE函数计算线性回归线的斜率 324

16.9.2 应用STEYX函数计算通过线性回归法预测每个x的y值时所产生的标准误差 325

16.9.3 应用INTERCEPT函数计算线性回归线的截距 326

16.9.4 应用LINEST函数计算线性趋势的参数 327

16.9.5 应用FORECAST函数计算沿线性趋势的值 330

16.10 数据集相关函数应用技巧 331

16.10.1 应用CORREL函数计算两个数据集之间的相关系数 331

16.10.2 应用KURT函数计算数据集的峰值 332

16.10.3 应用PERCENTRANK函数计算数据集中值的百分比排位 333

16.10.4 应用QUARTILE函数计算数据集的四分位数 334

16.10.5 应用RANK函数计算一个数字在数字列表中的排位 335

16.10.6 应用TRIMMEAN函数计算数据集的内部平均值 336

16.11 Pearson乘积矩函数应用技巧 337

16.11.1 应用PEARSON函数计算Pearson乘积矩相关系数 337

16.11.2 应用RSQ函数计算Pearson乘积矩相关系数的平方 337

16.12 t分布函数应用技巧 338

16.12.1 应用TDIST函数计算学生的t分布 338

16.12.2 应用TINV函数计算学生的t分布的反函数 339

16.13 其他函数应用技巧 340

16.13.1 应用FISHER函数计算Fisher变换值 340

16.13.2 应用FISHERINV函数计算Fisher变换的反函数值 341

16.13.3 应用GAMMADIST函数计算γ分布 342

16.13.4 应用GAMMAINV函数计算γ累积分布函数的反函数 343

16.13.5 应用NEGBINOMDIST函数计算负二项式分布 343

16.13.6 应用PERCENTILE函数返回区域中数值的第k个百分点的值 344

16.13.7 应用PERMUT函数计算给定数目对象的排列数 345

16.13.8 应用POISSON函数计算泊松分布 346

16.13.9 应用SKEW函数计算分布的不对称度 347

16.13.10 应用TREND函数计算沿线性趋势的值 347

16.13.11 应用WEIBULL函数计算韦伯分布 349

16.14 综合实战（1）：产品销售量统计 350

16.15 综合实战（2）：统计奖金发放人数 352

第17章 财务函数使用技巧 354

17.1 利息与利率计算函数应用技巧 355

17.1.1 应用ACCRINT函数计算定期支付利息的证券的应计利息 355

17.1.2 应用ACCRINTM函数计算在到期日支付利息的证券的应计利息 356

17.1.3 应用COUPNUM函数计算结算日和到期日之间的应付利息次数 357

17.1.4 应用CUMIPMT函数计算两个付款期之间累积支付的利息 358

17.1.5 应用EFFECT函数计算年有效利率 358

17.1.6 应用INTRATE函数计算完全投资型证券的利率 359

17.1.7 应用IPMT函数计算一笔投资在给定期数内的利息偿还额 360

17.1.8 应用ISPMT函数计算特定投资期内要支付的利息 361

17.1.9 应用NOMINAL函数计算年度的名义利率 361

17.1.10 应用RATE函数计算年金的各期利率 362

17.2 折旧值计算函数应用技巧 363

17.2.1 应用AMORDEGRC函数计算每个结算期的折旧值 363

17.2.2 应用AMORLINC函数计算每个结算期的折旧值 364

17.2.3 应用DB函数用固定余额递减法计算折旧值 365

17.2.4 应用DDB函数使用双倍余额递减法或其他指定方法计算折旧值 366

17.2.5 应用SLN函数计算固定资产的每期线性折旧费 367

17.2.6 应用SYD函数计算某项固定资产按年限总和折旧法计算的每期折旧金额 368

17.2.7 应用VDB函数使用余额递减法计算给定期间或部分期间内的折旧值 369

17.3 天数与付息日计算函数应用技巧 370

17.3.1 应用COUPDAYBS函数计算从付息期开始到结算日之间的天数 370

17.3.2 应用COUPDAYS函数计算包含结算日的付息期天数 371

17.3.3 应用COUPDAYSNC函数计算从结算日到下一付息日之间的天数 371

17.3.4 应用COUPNCD函数计算结算日之后的下一个付息日 372

17.3.5 应用COUPPCD函数计算结算日之前的上一付息日 373

17.4 收益与收益率计算函数应用技巧 374

17.4.1 应用IRR函数计算一系列现金流的内部收益率 374

17.4.2 应用MIRR函数计算正和负现金流以不同利率进行计算的修正收益率 375

17.4.3 应用ODDFYIELD函数计算首期付息日的收益率 376

17.4.4 应用ODDLYIELD函数计算末期付息日的收益率 377

17.4.5 应用TBILLEQ函数计算国库券的等价证券收益率 377

17.4.6 应用TBILLYIELD函数计算国库券的收益率 378

17.4.7 应用YIELD函数计算定期支付利息的证券的收益率 379

17.4.8 应用YIELDDISC函数计算已贴现证券的年收益率 380

17.4.9 应用YIELDMAT函数计算在到期日支付利息的证券的年收益率 381

17.4.10 应用XIRR函数计算一组现金流的内部收益率 381

17.5 价格转换函数应用技巧 382

17.5.1 应用DOLLARDE函数将以分数表示的价格转换为以小数表示的价格 383

17.5.2 应用DOLLARFR函数将以小数表示的价格转换为以分数表示的价格 383

17.5.3 应用TBILLPRICE函数计算国库券的价格 384

17.6 未来值计算函数应用技巧 385

17.6.1 应用FV函数：计算一笔投资的未来值 385

17.6.2 应用FVSCHEDULE函数计算应用一系列复利率计算的初始本金的未来值 386

17.7 本金计算函数应用技巧 386

17.7.1 应用CUMPRINC函数计算两个付款期之间为贷款累积支付的本金 386

17.7.2 应用PPMT函数计算一笔投资在给定期间内偿还的本金 387

17.8 现价计算函数应用技巧 388

17.8.1 应用ODDFPRICE函数计算每张票面为￥100且第一期为奇数的证券的现价 388

17.8.2 应用ODDLPRICE函数计算每张票面为￥100最后一期为奇数的证券的现价 389

17.8.3 应用PRICE函数计算每张票面为￥100且定期支付利息的证券的现价 390

17.8.4 应用PRICEDISC函数计算票面为￥100的已贴现证券的现价 391

17.8.5 应用PRICEMAT函数计算票面为￥100且在到期日支付利息的证券的现价 392

17.9 净现值与贴现率计算函数应用技巧 392

17.9.1 应用DISC函数计算证券的贴现率 392

17.9.2 应用NPV函数计算基于一系列定期的现金流和贴现率计算的投资的净现值 393

17.9.3 应用XNPV函数计算一组现金流的净现值 394

17.9.4 应用PV函数计算投资的现值 395

17.10 期限与期数计算函数应用技巧 396

17.10.1 应用DURATION函数计算定期支付利息的证券的每年期限 396

17.10.2 应用MDURATION函数计算面值为￥100的有价证券Macauley修正期限 397

17.10.3 应用NPER函数计算投资的期数 398

17.11 其他函数应用技巧 399

17.11.1 应用PMT函数计算年金的定期支付金额 399

17.11.2 应用RECEIVED函数计算完全投资型证券在到期日收回的金额 400

17.12 综合实战：年数总和法计算固定资产折旧 401

第18章 工程函数应用技巧 403

18.1 进制转换函数应用技巧 404

18.1.1 应用BIN2DEC、BIN2HEX或BIN2OCT函数转换二进制数 404

18.1.2 应用DEC2BIN、DEC2HEX或DEC2OCT函数转换十进制数 405

18.1.3 应用HEX2BIN、HEX2DEC或HEX2OCT函数转换十六进制数 406

18.1 4应用OCT2BIN、OCT2DEC或OCT2HEX函数转换八进制数 407

18.2 复数计算函数应用技巧 408

18.2.1 应用COMPLEX函数将实系数和虚系数转换为复数 409

18.2.2 应用IMABS、IMARGUMENT函数计算复数的模和角度 409

18.2.3 应用IMCONUGATE函数求解复数的共轭复数 410

18.2.4 应用IMCOS、IMSIN函数计算复数的余弦和正弦 411

18.2.5 应用IMDIV、IMPRODUCT、IMSUB和IMSUM函数计算复数的商、积、差与和 412

18.2.6 应用IMAGINARY和IMREAL函数计算复数的虚系数和实系数 413

18.2.7 应用IMSQRT函数计算复数的平方根 414

18.3 指数与对数函数应用技巧 415

18.3.1 应用IMEXP和IMPOWER函数计算指数和整数幂 415

18.3.2 应用IMLN、IMLOG10和IMLOG2函数计算对数 416

18.4 贝塞尔相关函数应用技巧 417

18.4.1 应用BESSELI函数计算修正的Bessel函数值L？（x） 417

18.4.2 应用BESSELJ函数计算Bessel函数值J？（x） 418

18.4.3 应用BESSELK函数计算修正Bessel函数值K？（x） 418

18.4.4 应用BESSELY函数计算Bessel函数值Y？（x） 419

18.5 其他函数应用技巧 420

18.5.1 应用CONVERT函数转换数值的度量系统 420

18.5.2 应用DELTA函数检验两个值是否相等 421

18.5.3 应用ERF、ERFC函数计算上下限的积分和补余误差函数 422

18.5.4 应用GESTEP函数检验数值是否大于阈值 423

第三篇 图表篇 426

第19章 日常办公中图表的使用 426

19.1 认识图表 427

19.1.1 创建图表 427

19.1.2 了解图表的构成 428

19.2 常用图表及应用范围 429

19.2.1 柱形图 429

19.2.2 条形图 430

19.2.3 折线图 432

19.2.4 饼图 433

19.2.5 散点图 433

19.2.6 曲面图 434

19.3 修改图表 435

19.3.1 图表大小和位置的调整 435

19.3.2 图表的复制和删除 437

19.3.3 更改图表类型 437

19.4 编辑图表 439

19.4.1 添加图表标题 439

19.4.2 图表中对象边框、填充效果设置 439

19.5 套用图表样式以快速美化图表 443

第20章 图表分析数据应用技巧 444

20.1 趋势线添加应用技巧 445

20.1.1 哪些图表支持趋势线 445

20.1.2 什么样的数据适合使用趋势线 445

20.1.3 如何为图表添加趋势线 446

20.1.4 趋势预测／回归分析类型 447

20.1.5 设置趋势线格式 447

20.1.6 删除趋势线 449

20.2 折线添加应用技巧 449

20.2.1 添加垂直线 449

20.2.2 添加高低点连线 449

20.2.3 删除折线 450

20.3 涨／跌柱线添加应用技巧 450

20.4 误差线添加应用技巧 451

20.4.1 支持误差线的图表类型 451

20.4.2 添加误差线 451

20.4.3 设置误差线的显示选项 453

20.4.4 设置误差线的误差量选项 453

20.4.5 删除误差线 453

第四篇 数据分析篇 456

第21章 排序与筛选 456

21.1 排序的运用 457

21.1.1 3种实用的工作表排序方法 457

21.1.2 对超过3列的数据进行排序 458

21.1.3 按日期和时间进行混合条件排序 459

21.1.4 返回排序前的表格 460

21.1.5 特殊班级排序一例 460

21.1.6 按笔划进行排序 461

21.1.7 按字数进行排序 462

21.1.8 按自定义序列进行排序 462

21.1.9 按单个条件进行排序 463

21.1.10 按多个条件进行排序 463

21.2 筛选的运用 464

21.2.1 自动筛选符合条件的数据 464

21.2.2 按颜色进行排序或筛选 465

21.2.3 筛选高于或低于平均值的记录 466

21.2.4 取消当前数据范围的筛选和排序 467

21.2.5 在当前数据范围内重新应用筛选和排序 468

21.2.6 使用筛选功能简化数据查询 468

21.2.7 利用高级筛选快速删除重复数据 468

第22章 基本数据分析应用技巧 470

22.1 使用数据表进行假设分析应用技巧 471

22.1.1 数据表有哪些类型 471

22.1.2 使用单变量数据表 471

22.1.3 使用双变量数据表 472

22.1.4 清除数据表 473

22.2 假设分析方案应用技巧 473

22.2.1 定义方案 474

22.2.2 创建方案 474

22.2.3 显示方案 476

22.3 分析工具库应用技巧 476

22.3.1 加载分析工具库 476

22.3.2 方差分析 477

22.3.3 相关系数 481

22.3.4 协方差 483

22.3.5 描述统计 483

22.3.6 指数平滑 485

22.3.7 F-检验双样本方差分析 486

22.3.8 傅里叶分析 487

22.3.9 直方图 489

22.3.10 移动平均 490

22.3.11 随机数发生器 491

22.3.12 排位与百分比排位 493

22.3.13 回归分析 494

22.3.14 抽样分析 495

22.3.15 t-检验 497

22.3.16 z-检验：双样本平均差检验 502

22.4 单变量求解实例 503

22.4.1 求解一元方程 503

22.4.2 实例：使用单变量求解计算银行贷款利率 505

第23章 数据组合与分类汇总 507

23.1 数据的分级显示 508

23.1.1 手动创建行的分级显示 508

23.1.2 手动创建列的分级显示 509

23.1.3 自动创建分级显示 510

23.1.4 分级显示的隐藏 512

23.1.5 分级显示的删除 513

23.1.6 自定义分级显示样式 513

23.1.7 复制分级显示的数据 514

23.2 数据的分类汇总 515

23.2.1 创建一个含有图表的汇总报表 515

23.2.2 插入一个分类汇总 517

23.2.3 删除分类汇总 518

23.3 数据的合并计算 518

23.3.1 按位置对数据进行合并计算 518

23.3.2 按类别对数据进行合并计算 519

23.3.3 使用公式对数据进行合并计算 523

23.3.4 使用数据透视表合并数据 524

第24章 数据透视表分析数据应用技巧 527

24.1 创建数据透视表应用技巧 528

24.1.1 数据透视表概述 528

24.1.2 创建数据透视表 529

24.2 自定义数据透视表的字段与布局应用技巧 530

24.2.1 添加数据透视表字段 531

24.2.2 删除数据透视表字段 531

24.2.3 改变数据透视表字段列表的视图方式 531

24.2.4 设置数据透视表选项 534

24.2.5 字段设置 535

24.2.6 值字段设置 535

24.3 数据透视表相关操作应用技巧 536

24.3.1 复制数据透视表 536

24.3.2 移动数据透视表 537

24.3.3 清除与删除数据透视表 537

24.3.4 重命名数据透视表 538

24.3.5 刷新数据透视表 538

24.3.6 显示与隐藏字段列表 539

24.4 数据透视表的分析应用技巧 540

24.4.1 更改数据透视表的排序方式 540

24.4.2 更改数据透视表的汇总方式 541

24.4.3 筛选汇总结果 542

第五篇 宏和VBA篇 546

第25章 宏和VBA的运用 546

25.1 什么是宏和VBA 547

25.2 宏的录制与保存 547

25.2.1 在功能区中添加“开发工具”选项卡 547

25.2.2 录制宏 548

25.2.3 使用相对引用录制宏 549

25.2.4 设置宏的保存方式 549

25.3 宏的启动与运行 550

25.3.1 使用“宏”对话框来启动宏 550

25.3.2 使用快捷键来快速启动宏 551

25.3.3 使用表单控件启动宏 551

25.3.4 使用ActiveX控件来启动宏 552

25.4 更改宏的安全设置 554

25.4.1 宏安全设置及其作用 554

25.4.2 更改宏的安全设置操作步骤 555

25.5 编写简单的VBA程序 556

25.5.1 打开VisualBasic编辑器 556

25.5.2 轻松输入代码 557

25.5.3 使用对象浏览器了解VBA对象 559

25.5.4 代码的调试技巧 560