第1章 公式与函数基础 1
1.1 用公式计算数据 2
1.1.1 公式的构成 2
1.1.2 输入公式的方法 3
1．在单元格中输入公式 3
2．在编辑栏中输入公式 3
3．结合键盘与鼠标输入公式 4
1.1.3 编辑公式 5
1．修改公式 5
2．复制公式 6
3． 显示与隐藏公式 7
4．将公式结果转化成数值 8
1.2 用函数计算数据 10
1.2.1 函数的作用 10
1．简化公式 10
1.2.3 函数的类型 11
1.2.2 函数的结构 11
3．进行智能判断 11
2．实现特殊运算 11
1.2.4 插入函数 12
1.3 认识运算符 14
1.3.1 运算符的类型 14
1.3.2 运算符的优先级 15
1.3.3 嵌套括号 15
1.4.1 定义单元格名称 16
1.4 定义与使用名称 16
1.4.2 使用定义的名称 18
1．查找名称所对应的单元格 18
2．引用名称 20
1.5 单元格的引用类型 21
1.5.1 相对引用 21
1.5.2 绝对引用 21
1.5.3 混合引用 21
2．#DIV/0！错误 22
1．####错误 22
1.6.1 Excel中的常见错误值 22
1.6 正确处理公式与函数中的错误 22
3．#VALUE！错误 23
4．#N/A错误 23
5．#NAME？错误 24
6．#REF！错误 24
7．#NUM！错误 25
8．#NULL！错误 25
1.6.2 设置检测规则 25
1.6.3 追踪单元格 26
1．在同一工作表中追踪单元格 27
2．在不同的工作表中追踪单元格 27
第2章 逻辑与信息函数的应用 29
2.1 逻辑函数的应用 30
2.1.1 用FALSE和TRUE函数返回逻辑值 30
2.1.2 用NOT函数对逻辑值求反 31
2.1.3 用IF函数返回条件值 31
2.1.4 用AND函数实现交集运算 34
2.1.5 用OR函数实现并集运算 35
2.2 信息函数的应用 37
2.2.1 用CELL函数返回单元格信息 37
2.2.2 用ISBLANK函数判断单元格是否为空 38
2.2.3 用ISERR函数判断单元格中是否为错误值 40
2.2.4 用ERROR.TYPE函数判断错误类型 41
2.2.5 用ISLOGICAL函数判断参数是否为逻辑值 41
2.2.6 用ISREF函数判断单元格中是否为引用 41
2.2.9 用ISNONTEXT函数判断参数是否为非字符串 42
2.2.7 用ISTEXT函数判断参数是否为文本 42
2.2.8 用ISNUMBER函数判断参数是否为数字 42
2.2.10 用TYPE函数判断数值类型 43
第3章 文本函数的应用 45
3.1 转换字符串 46
3.1.1 用VALUE函数将文本转换为数值 46
3.1.2 用TEXT函数将数值转换为文本 48
3.1.5 用PROPER函数将字母转换为适当的大小写 49
3.1.4 用UPPER函数将字母转换为大写 49
3.1.3 用LOWER函数将字母转换为小写 49
3.2 合并与比较字符串 50
3.2.1 用CONCATENATE函数合并字符串 50
3.2.2 用EXACT函数比较字符串 51
3.3 查找与替换字符串 52
3.3.1 用FIND函数查找文本 52
3.3.2 用SEARCH函数查找文本 53
3.3.3 用REPLACE函数替换文本 54
3.3.4 用SUBSTITUTE函数替换文本 55
3.4 复制与返回字符串 56
3.4.1 用REPT函数复制文本 56
3.4.2 用LEN函数返回字符串长度 57
3.4.3 用LEFT函数返回左边字符 59
3.4.4 用MID函数返回指定位置的字符 59
3.4.5 用RIGHT函数返回右边字符 61
3.4.6 用TRIM函数清除多余的空格 65
第4章 日期和时间函数的应用 67
4.1 日期函数的应用 68
4.1.1 了解日期系统 68
4.1.2 用DATE函数返回日期 69
1．年份溢出 70
2．月份溢出 70
3．日期溢出 70
4.1.3 用DATEVALUE函数显示日期编号 71
4.1.4 用TODATE函数显示当前日期 71
4.1.5 用YEAR函数返回日期的年份 73
4.1.6 用MONTH函数返回月份数 74
4.1.7 用DAY函数返回日期天数 75
4.1.8 用WEEKDAY函数显示日期的星期数 76
4.2 时间函数的应用 78
4.2.1 用TIME函数返回时间 78
3．秒溢出 79
4.2.2 用TIMEVALUE函数转换时间 79
2．分钟溢出 79
1．小时溢出 79
4.2.3 用NOW函数显示当前日期和时间 80
4.2.4 用HOUR函数返回小时数 80
4.2.5 用MINUTE函数返回分钟数 80
4.2.6 用SECOND函数返回秒数 83
第5章 数学与三角函数的应用 85
5.1.1 用SUM函数求和 86
5.1 数学函数的应用 86
5.1.2 用SUMIF函数按条件求和 89
5.1.3 用PRODUCT函数求乘积 90
5.1.4 用MOD函数返回相除两数的余数 91
5.1.5 用POWER函数求幂 92
5.1.6 用FACT函数计算阶乘 92
5.1.7 用ABS函数求绝对值 92
5.1.8 用LN、LOG10和LOG函数计算对数 94
5.1.10 用COMBIN函数计算组合数 95
5.1.11 用ROUND函数按位四舍五入 95
5.1.9 用RAND函数返回随机数 95
5.1.12 用CEILING函数按条件向上舍入 96
5.1.13 用FLOOR函数按条件向下舍入 98
5.1.14 用INT函数向下取整 98
5.1.15 用ODD函数将数值舍入为奇数 102
5.1.16 用EVEN函数将数值舍入为偶数 102
5.1.17 用ROMAN函数将阿拉伯数字转换为罗马数字 103
5.2 三角函数的应用 103
5.2.1 正三角函数的应用 103
5.2.3 用DEGREES和RADIANS函数转换角度和弧度 104
5.2.2 反三角函数的应用 104
5.3 数组函数的应用 105
5.3.1 用MDETERM函数计算矩阵行列式值 105
5.3.2 用MMULT函数计算矩阵乘积 105
5.3.3 用SUMPRODUCT函数计算数据乘积之和 106
5.3.4 用SUMXMY2函数计算差的平方和 107
第6章 统计函数的应用 109
6.1 常规统计 110
6.1.1 用MAX函数返回最大值 110
6.1.2 用MIN函数返回最小值 112
6.1.3 用MEDIAN函数返回中值 113
6.1.4 用LARGE函数按条件返回最大值 115
6.1.5 用SMALL函数按条件返回最小值 116
6.1.6 用RANK函数排序数字 117
6.1.7 用AVERAGE函数计算平均值 118
6.1.8 用COUNT函数统计数值单元格数量 119
6.1.10 用COUNTA函数统计非空白单元格数量 120
6.1.11 用COUNTIF函数按条件进行统计 120
6.1.9 用COUNTBLANK函数统计空白单元格数量 120
6.1.12 用MODE函数统计出现频率最高的数值 121
6.1.13 用FREQUENCY函数统计分布频率 123
6.2 数理统计 124
6.2.1 用POISSON函数返回泊松分布 124
6.2.2 用CORREL函数分析相关系数 125
6.2.3 用STDEV函数求样本的标准偏差 127
6.2.4 用VAR函数计算样本方差 127
第7章 数据库函数的应用 129
7.1.1 了解数据库 130
7.1 认识数据库 130
7.1.2 用记录单创建数据库 131
7.2 管理数据 133
7.2.1 用DGET函数返回指定条件的值 133
7.2.2 用DSUM函数计算指定数字的和 135
7.2.3 用DPRODUCT函数计算指定数字的乘积 135
7.2.4 用DAVERAGE函数返回条目的平均值 136
7.2.5 用DMAX函数返回符合条件的最大值 137
7.2.6 用DMIN函数返回符合条件的最小值 138
7.2.7 用DSTDEV函数计算样本的标准偏差 139
7.2.8 用DSTDEVP函数计算总体的标准偏差 140
7.2.9 用DVARP函数计算总体的方差 141
7.2.10 用DCOUNT函数统计数字单元格 142
第8章 查询和引用函数的应用 145
8.1 查询函数的应用 146
8.1.1 用CHOOSE函数在列表中选择值 146
1．向量型LOOKUP函数 147
8.1.2 用LOOKUP函数查找数据 147
2．数组型LOOKUP函数 149
8.1.3 用HLOOKUP函数进行水平查找 151
8.1.4 用VLOOKUP函数进行竖直查找 152
8.1.5 用MATCH函数返回数值在数组中的位置 156
8.2 引用函数的应用 158
8.2.1 用ADDRESS函数显示引用地址 158
8.2.2 用COLUMN数返回引用的列标 159
8.2.3 用ROW函数返回引用的行号 159
8.2.4 用COLUMNS函数返回引用的列数 160
8.2.5 用ROWS函数返回引用的行数 160
8.2.6 用AREAS函数返回区域数量 160
8.2.7 用INDEX函数返回指定内容 160
1．数组型INDEX函数 161
2．引用型INDEX函数 162
8.2.8 用INDIRECT函数返回指定的引用及结果 162
8.2.10 用HYPERLINK函数实现快速跳转 163
8.2.9 用OFFSET函数偏移引用位置 163
第9章 财务函数的应用 167
9.1 投资预算函数的应用 168
9.1.1 用FV函数预测投资效果 168
9.1.2 用PV函数计算投资现值 169
9.1.3 用NPV函数计算非固定回报投资 170
9.2 折旧值函数的应用 171
9.2.1 用DB函数计算折旧值 172
9.2.2 用DDB函数计算折旧值 173
9.2.3 用SYD函数计算折旧值 175
9.2.4 用SLN函数计算折旧值 176
9.3 本金和利息函数的应用 176
9.3.1 用PMT函数计算每期还贷额 176
9.3.2 用PPMT函数计算付款中的本金 178
9.3.3 用IPMT函数计算付款中的利息 179
9.4 报酬率函数的应用 181
9.4.1 用IRR函数计算现金流的内部收益率 181
9.4.2 用MIRR函数计算现金流的修正内部收益率 182
第10章 创建与编辑图表 185
10.1 创建图表 186
10.2 编辑图表 189
10.2.1 改变图表位置和大小 189
1．改变图表位置 189
2．改变图表大小 189
10.2.2 设置图表文字格式 192
1．设置标题文字格式 193
2．设置图例的字体格式 193
3．设置坐标轴及坐标轴标题的字体格式 194
10.2.3 设置边框与填充效果 197
1．设置图表区的边框与填充效果 197
2．设置绘图区的边框与填充效果 201
3．设置图例的边框与填充效果 203
4．设置数据系列的边框与填充效果 204
5．设置坐标轴的线条效果 205
6．设置网格线的线条效果 206
10.2.4 设置图表选项 208
10.3 在图表中添加或删除系列 210
10.4 更改图表类型 212
第11章 各种图表的应用场合 215
11.1 柱形图的应用 216
11.1.1 比较不同项目的数据 216
11.1.2 比较同类别数据 217
11.2 条形图的应用 217
11.3 饼图的应用 220
11.4 圆环图的应用 222
11.5 折线图的应用 224
11.6 XY散点图的应用 227
11.7 其他类型图表的应用 230
11.7.1 气泡图的应用 230
11.7.2 面积图的应用 231
11.7.3 曲面图的应用 231
11.7.4 雷达图的应用 232
11.7.5 股价图的应用 232
11.7.6 圆柱图、圆锥图和棱锥图的应用 233
11.8 制作组合图表 234
第12章 图表制作技巧 239
12.1 在图表中自动显示最值 240
12.2 用图片表示数据点 244
12.3 用下拉列表框切换系列 248
12.4 用滚动条控制显示数量 252
12.5 用柱形宽度表示系列比例 258
12.6 为图表创建快照 262
第13章 制作数据透视图表 265
13.1 数据透视表的应用 266
13.1.1 创建数据透视表 266
13.1.2 使用数据透视表 270
1．按类查看数据 270
2．更改数据的汇总方式 272
3．更改数据透视表布局 274
13.1.3 设置数字透视表的格式 275
13.2 数据透视图的应用 276
13.2.1 创建数据透视图 277
13.2.2 使用数据透视图 279
第14章 制作工资表及销量图 283
14.1 案例目标 284
14.2 案例分析 285
14.3 案例操作 286
14.3.1 计算员工工资 286
1．计算销售总额 286
2．计算总工资 287
3．计算税后工资 288
14.3.2 计算各种面钞的数量 290
14.3.3 制作销售额柱形图 294
1．创建销售额柱形图 294
2．美化销售额柱形图 296
14.3.4 制作产品销量饼图 304
1．创建产品销量饼图 304
2．美化产品销量饼图 307
14.4 案例总结 309