Chapter 01 公式的应用技巧 1
1.1 认识公式 2
001 了解公式的组成结构 2
002 使用复制公式与填充的方法快速计算数据 3
003 自定义公式的应用 5
1.2 公式中的运算符 6
004 认识Excel 2010中应用的运算符 6
005 了解运算符的顺序 8
1.3 单元格地址的引用 9
006 使用相对引用选择参与计算的单元格 10
007 使用绝对引用固定某一单元格 10
008 使用混合引用固定引用行或列 11
009 使用跨表引用快速选择参与计算的数据源 13
1.4 公式审核 15
010 使用追踪引用查看值的引用 15
011 使用追踪从属单元格查看数据的应用范围 16
012 使用显示公式查看编辑的公式 17
013 使用公式求值分步计算单元格数据 17
014 对工作表中的公式进行错误检查 19
015 使用监视窗口查看工作表中的变化 20
1.5 公式中计算出错的处理方法 21
016 “＃＃＃＃＃”错误及解决方法 21
017 “＃DIV/0！”错误及解决方法 22
018 “＃N/A”错误及解决方法 23
019 “＃NAME？”错误及解决方法 24
020 “＃NULL！”错误及解决方法 25
021 “＃NUM！”错误及解决方法 26
022 “＃REF！”错误及解决方法 27
023 “＃VALUE！”错误及解决方法 29
Chapter 02 定义与使用名称技巧 31
2.1 定义名称的方法 32
024 使用名称框定义名称 32
025 根据所选内容创建名称 34
2.2 定义名称的对象 35
026 定义工作表名称区分工作表 35
027 定义单元格区域 37
028 在工作表中定义常量 39
029 在工作表中定义常量数组 39
2.3 管理名称 40
030 使用名称管理器新建名称 40
031 使用名称管理器删除不需要的名称 42
032 使用名称管理器编辑已有名称 43
2.4 使用名称工作 45
033 在公式中使用定义的名称 45
034 将定义的数组名称应用于公式 46
035 根据定义的名称计算数据 47
Chapter 03 函数基础与常用函数应用技巧 49
3.1 认识Excel函数 50
036 了解函数的组成结构 50
037 使用函数的优势 51
038 了解参数的可选与必选 52
039 通过帮助学习函数的应用 54
3.2 函数编辑与使用 56
040 使用“插入函数”对话框输入函数 56
041 通过单击“插入函数”按钮输入函数 58
042 使用自动完成功能输入函数 59
043 自定义函数 60
044 使用函数提示工具 61
3.3 常用函数的使用 63
045 使用SUM函数快速求和 63
046 使用AVERAGE函数求平均值 64
047 使用MAX函数查找出最大值 65
048 使用MIN函数查找出最小值 67
049 使用COUNT函数统计单元格个数 68
Chapter 04 财务函数应用技巧 70
4.1 基本财务函数 71
050 使用PV函数计算投资的现值 71
051 使用FV函数计算一笔投资的期值 72
052 使用RATE函数计算年金的各期利率 73
053 使用NPER函数计算总期数 75
4.2 计算本金和利息函数 77
054 用PMT函数计算贷款偿还额 77
055 用IPMT函数计算贷款在给定期间内支付的利息 78
056 用PPMT函数计算贷款在给定期间内偿还的本金 79
057 用ISPMT函数计算特定投资期内支付的利息 80
058 用CUMIPMT函数计算两个付款期之间累计支付的利息 81
059 使用CUMPRINC函数计算两个付款期之间累计支付的本金 83
060 使用EFFECT函数计算有效的年利率 84
061 使用NOMINAL函数计算名义年利率 85
4.3 计算投资预算函数 87
062 用FVSCHEDULE函数基于一系列复利计算未来值 87
063 使用NPV函数计算投资的净现值 88
064 使用XNPV函数计算现金流的净现值 89
4.4 计算收益函数 90
065 使用IRR函数计算一系列现金流的内部收益率 90
066 使用MIRR函数计算修正内部收益率 92
067 使用XIRR函数计算现金流的内部收益率 93
4.5 计算折旧值函数 94
068 使用AMORDEGRC函数计算每个结算期间的折旧值 94
069 使用AMORLINC函数计算每个结算期间的折旧值 95
070 使用DB函数计算一笔资产在给定期间内的折旧值 96
071 使用DDB函数双倍余额递减计算折旧值 98
072 使用VDB函数返回折旧值 99
073 使用SLN函数计算某项资产在一个期间内的线性折旧值 101
074 使用SYD函数按年限总和折旧法计算折旧值 102
4.6 计算债券函数 103
075 使用ACCRINT函数计算证券的利息 103
076 使用COUPDAYBS函数计算付息期与成交日的天数 104
077 使用COUPDAYS函数计算付息期的天数 105
078 使用COUPDAYSNC函数计算固定付息日间的天数 106
079 使用COUPNCD函数计算结算日之后的付息日 107
080 使用COUPNUM函数计算付息次数 109
081 使用COUPPCD函数计算结算日之前的付息日 110
082 使用DISC函数计算证券的贴现率 111
083 使用DURATION函数计算证券的修正期限 112
084 使用INTRATE函数计算一次性付息证券的利率 114
085 使用ODDFPRICE函数计算有价证券的价格 115
086 使用ODDFYIELD函数计算有价证券的收益率 116
087 使用ODDLPRICE函数计算有价证券的价格 118
088 使用ODDLYIELD函数计算有价证券的收益率 119
089 使用PRICE函数计算定期付息的有价证券价格 121
090 使用PRICEDISC函数计算折价发行的有价证券价格 122
091 使用PRICEMAT函数计算到期付息的有价证券价格 123
092 使用RECEIVED函数计算有价证券的金额 124
093 使用TBILLPRICE函数计算国库券的价格 125
094 使用TBILLYIELD函数计算国库券的收益率 126
095 使用YIELD函数计算有价证券的收益率 127
096 使用YIELDDISC函数计算证券的年收益率 128
097 使用YIELDMAT函数计算到期付息的有价证券年收益率 129
098 使用MDURATION函数计算假设面值的修正Macauley 131
099 使用TBILLEQ函数计算国库券的等效收益率 132
Chapter 05 查找与引用函数应用技巧 134
5.1 查找表中数据函数 135
100 使用CHOOSE函数根据序号从列表中选择对应的内容 135
101 使用HLOOKUP函数在区域或数组的行中查找数据 136
102 使用VLOOKUP函数在区域或数组的列中查找数据 138
103 使用LOOKUP函数以向量形式仅在单行单列中查找 139
104 使用INDEX函数以引用形式返回指定位置中的内容 140
5.2 引用表中数据函数 142
105 使用MATCH函数返回指定内容所在的位置 142
106 使用ADDRESS函数返回指定行号和列标对应的单元格地址 144
107 使用COLUMN函数返回单元格或单元格区域首列的列标 145
108 使用ROW函数返回单元格或单元格区域首行的行号 146
109 使用OFFSET函数根据给定的偏移量返回新的引用区域 148
110 使用TRANSPOSE函数转置数据区域的行列位置 149
111 使用INDIRECT函数返回由文本值指定的引用 151
5.3 显示数量函数 152
112 使用AREAS函数返回引用中包含的区域数量 152
113 使用COLUMNS函数返回数据区域包含的列数 153
114 使用ROWS函数返回数据区域包含的行数 154
Chapter 06 统计与求和函数应用技巧 156
6.1 基本计数函数 157
115 使用COUNTA函数返回指定内容所在的位置 157
116 使用COUNTBLANK函数返回单元格个数 158
6.2 条件统计函数 159
117 使用COUNTIF函数根据条件进行计数 160
118 使用COUNTIFS函数根据多条件进行计数 161
6.3 条件求和类函数 162
119 使用SUMIF函数根据条件求和 162
120 使用SUMIFS函数计算多重条件的和 163
6.4 条件均值类函数 164
121 使用AVERAGEA函数计算参数中非空值的平均值 165
122 使用AVERAGEIF函数计算指定条件的平均值 166
123 使用AVERAGEIFS函数计算多条件平均值 167
124 使用TRIMMEAN函数计算去除最高最低分的平均值 169
6.5 方差函数 170
125 使用VAR函数计算方差 170
126 使用VARA函数计算样本方差 171
127 使用VARP函数计算给定样本总体方差 172
128 使用VARPA函数计算整个样本总体方差 173
6.6 频率统计函数 174
129 使用FREQUENCY函数分段统计学生成绩 174
130 使用MODE函数计算出现频率最多的数值 175
131 使用MODE.SNGL函数返回数组或区域出现频率最多的数值 176
132 使用MODE.MULT函数返回阵列中频率最多的数值 177
6.7 极值应用函数 178
133 使用MAXA函数返回列表中的最大值 179
134 使用MINA函数返回列表中的最小值 180
135 使用LARGE函数返回指定的第N大的值 181
136 使用SMALL函数返回指定的第N小的值 182
6.8 概率分布函数 183
137 使用BETADIST函数计算概率密度 183
138 使用BETAINV函数计算概率密度的反函数值 184
139 使用BINOMDIST函数计算概率值 185
140 使用CHIDIST函数计算单尾概率 186
141 使用CHIINV函数计算单尾概率的反函数值 188
142 使用EXPONDIST函数计算指数分布 189
143 使用FDIST函数计算F概率分布 190
144 使用FINV函数计算F概率分布的反函数值 192
145 使用GAMMADIST函数计算伽马累积分布 193
146 使用GAMMAINV函数计算伽马累积分布函数的反函数值 194
147 使用HYPGEOMDIST函数计算超几何分布 196
148 使用LOGNORMDIST函数计算累积分布函数 197
149 使用LOGINV函数计算累积分布函数的反函数值 198
150 使用NEGBINOMDIST函数计算负二项式分布 199
151 使用NORMDIST函数计算正态分布函数 200
152 使用NORMINV函数计算正态累积分布函数的反函数值 202
153 使用NORMSDIST函数计算正态累积分布函数 203
154 使用NORMSINV函数计算正态累积分布函数的反函数值 205
155 使用STANDARDIZE函数计算正态化数值 206
156 使用POISSON函数计算泊松分布 208
157 使用TDIST函数计算t分布的百分点 210
158 使用TINV函数计算t分布的t值 211
159 使用WEIBULL函数计算韦伯分布 213
6.9 检验函数 214
160 使用CHITEST函数计算独立性检验值 215
161 使用FTEST函数计算F检验结果 216
162 使用TTEST函数计算t检验概率 217
163 使用ZTEST函数计算z检验的单尾概率值 218
6.10 排名应用类函数 220
164 使用RANK函数对数据进行排名 220
165 使用RANK.EQ函数返回一个数字在一组数字中的排位 221
166 使用RANK.AVG函数返回单元格地址 223
Chapter 07 日期和时间函数应用技巧 225
7.1 年月日函数 226
167 使用YEAR函数返回年份数 226
168 使用MONTH函数返回指定月数 228
169 使用DAY函数返回指定日期的具体天数 229
170 使用DATE数返回指定日期的序列号 231
171 使用EDATE函数返回指定日期 232
172 使DAYS360数计算两日期之间的天数 234
173 使用EOMONTH函数返回指定月份的最后一天 236
174 使用YEARFRAC函数返回两日期天数占全年天数的百分比 237
7.2 星期相关函数 239
175 使用WEEKDAY函数返回一周中的第几天的数值 239
176 使用WEEKNUM函数返回一年中的周数 241
7.3 工作日相关函数 242
177 使用NETWORKDAYS函数返回两日期之间的完整工作日数 243
178 使用NETWORKDAYS.INTL函数按周末参数返回工作日数 244
179 使用TODAY函数返回日期格式的当前日期 246
180 使用WORKDAY函数返回指定日期之前或之后的日期值 248
181 使用WORKDAY.INTL函数返回指定日期之前或之后的日期 249
7.4 返回时间段函数 250
182 使用HOUR函数返回小时数值 251
183 使用MINUTE函数返回分钟数值 252
184 使用NOW函数返回当前日期和时间 253
185 使用SECOND函数返回秒值 254
186 使用TIME函数返回时间的小数值 255
7.5 文本与日期和时间格式转换函数 257
187 使用DATEVALUE函数将文本格式的日期转换为序列号 257
188 使用TIMEVALUE函数将文本格式的时间转换为序列号 259
Chapter 08 数学和三角函数应用技巧 261
8.1 常规数学计算函数 262
189 使用ABS函数计算数字的绝对值 262
190 使用SIGN函数获取数值的符号 263
191 使用PRODUCT函数计算乘积 265
192 使用PI函数返回pi值 266
193 使用SQRT函数计算正平方根 268
194 使用SQRTPI函数计算pi乘积的平方根 269
195 使用MOD函数计算两数相除的余数 270
196 使用QUOTIENT函数返回商的整数部分 271
197 使用GCD函数计算最大公约数 271
198 使用LCM函数计算最小公倍数 273
8.2 舍入与取整函数 274
199 使用TRUNC函数返回数字的整数部分 274
200 使用INT函数返回永远小于原数字的最接近的整数 275
201 使用CEILING函数按条件向上舍入 276
202 使用EVEN函数沿绝对值增大的方向舍入到最接近的偶数 277
203 使用FLOOR函数按条件向下舍入 278
204 使用ROUND函数按指定位数对数字进行四舍五入 279
205 使用ODD函数沿绝对值增大的方向舍入到最接近的奇数 280
8.3 指数与对数函数 281
206 使用POWER函数计算数字的乘幂 281
207 使用EXP函数计算e的n次幂 282
208 使用LN函数计算自然对数 283
209 使用LOG函数计算以指定数字为底数的对数 284
8.4 三角函数 285
210 使用DEGREES函数将弧度转换为角度 285
211 使用RADIANS函数将角度转换为弧度 286
212 使用COS函数计算给定角度的余弦值 287
213 使用ACOS函数计算反余弦值 288
214 使用ACOSH函数计算数字反双曲余弦值 288
215 使用SIN函数计算角度的正弦值 289
216 使用ASIN函数计算数字的反正弦值 290
217 使用ASINH函数计算数字反双曲正弦值 291
218 使用ATAN函数计算以弧度表示的反正切值 292
219 使用ATAN2函数计算以坐标值表示的反正切值 293
220 使用ATANH函数计算反双曲正切值 294
221 使用COSH函数计算双曲余弦值 295
222 使用SINH函数计算双曲正弦值 296
223 使用TAN函数计算正切值 297
224 使用TANH函数计算双曲正切值 298
Chapter 09 文本函数应用技巧 299
9.1 字符串编辑函数 300
225 使用CONCATENATE函数将多个字符串合并到一处 300
226 使用EXACT函数比较两个字符串是否相同 301
227 使用LEN函数计算文本中的字符个数 302
228 使用LENB函数计算文本中代表字符的字节数 303
229 使用REPT函数按给定次数重复文本 305
230 使用FIXED函数将数字按指定的小数位数取整 305
9.2 返回文本内容函数 306
231 使用LEFT函数从文本左侧起提取指定个数的字符 307
232 使用LEFTB函数从文本左侧起提取指定字节数的字符 307
233 使用MID函数从文本指定位置起提取指定个数的字符 308
234 使用MIDB函数从文本指定位置起提取指定字节数的字符 310
235 使用RIGHT函数从文本右侧起提取指定个数的字符 311
236 使用RIGHTB函数从文本右侧起提取指定字节数字符 312
9.3 转换文本格式函数 313
237 使用ASC函数将全角字符转换为半角字符 313
238 使用WIDECHAR函数将半角字符转换为全角字符 314
239 使用CODE函数将文本字符串中第一个字符转换为数字代码 315
240 使用CHAR函数将数字代码转换为对应的字符 316
241 使用RMB函数将数字转换为带人民币符号￥的文本 317
242 使用DOLLAR函数将数字转换为带美元符号？的文本 318
243 使用TEXT函数将数字转换为文本 319
244 使用BAHTTEXT函数将数字转换为泰语文本 323
245 使用T函数将参数转换为文本 323
246 使用LOWER函数将文本转换为小写 325
247 使用UPPER函数将文本转换为大写 326
248 使用PROPER函数将每个单词的首字母转换为大写 326
249 使用VALUE函数将文本格式的数字转换为普通数字 327
9.4 查找与替换文本函数 328
250 使用FIND函数查找指定字符的位置 329
251 使用FINDB函数查找指定字符的位置 330
252 使用SEARCH函数以字符为单位查找指定字符的位置 331
253 使用SEARCHB函数以字节为单位查找指定字符的位置 333
254 使用REPLACE函数以字符为单位根据指定位置进行替换 334
255 使用REPLACEB函数按指定位置以字节为单位进行替换 335
256 使用SUBSTITUTE函数以指定文本进行替换 336
9.5 删除文本中的字符函数 338
257 使用CLEAN函数删除无法打印的字符 338
258 使用TRIM函数删除多余的空格 339
Chapter 10 逻辑函数应用技巧 340
10.1 逻辑值函数 341
259 使用TRUE函数返回逻辑值TRUE 341
260 使用FALSE函数返回逻辑值FALSE 343
10.2 交集、并集和求反函数 344
261 使用AND函数判断指定的多个条件是否同时成立 344
262 使用OR函数判断指定的任一条件为真就返回真 345
263 使用NOT函数对逻辑值求反 347
264 使用IF函数计算应缴税金 348
10.3 用IFERROR函数判断公式结果 349
Chapter 11 工程函数应用技巧 351
11.1 贝塞尔函数 352
265 使用BESSELI函数返回修正的贝塞尔函数 352
266 使用BESSELJ函数返回贝塞尔函数 353
267 使用BESSELK函数返回修正的贝塞尔函数 354
268 使用BESSELY函数返回贝塞尔函数 355
11.2 转换函数 356
269 使用BIN2DEC函数将二进制数转换为十进制数 356
270 使用BIN2HEX函数将二进制数转换为十六进制数 357
271 使用BIN2OCT函数将二进制数转换为八进制数 358
272 使用COMPLEX函数将实系数及虚系数转换为复数 359
273 使用CONVERT函数对数字进行量度转换 359
274 使用DEC2BIN函数将十进制数转换为二进制数 362
275 使用DEC2HEX函数将十进制数转换为十六进制数 363
276 使用DEC2OCT函数将十进制数转换为八进制数 364
277 使用HEX2BIN函数将十六进制数转换为二进制数 365
278 使用HEX2DEC函数将十六进制数转换为十进制数 366
279 使用HEX2OCT函数将十六进制数转换为八进制数 367
280 使用OCT2BIN函数将八进制数转换为二进制数 368
281 使用OCT2DEC函数将八进制数转换为十进制数 369
282 使用OCT2HEX函数将八进制数转换为十六进制数 370
11.3 复数函数 371
283 使用IMABS函数计算复数的绝对值 371
284 使用IMAGINARY函数计算复数的虚系数 372
285 使用IMREAL函数返回复数的实系数 373
286 使用IMCONJUGATE函数返回复数的共轭复数 374
287 使用IMSIN函数计算复数的正弦值 375
288 使用IMSQRT函数计算复数的平方根 376
289 使用IMSUB函数计算两个复数的差 377
11.4 其他工程函数 378
290 使用DELTA函数检测两个函数的相等性 378
291 使用GESTEP函数检验数字是否大于阈值 379
292 使用ERF函数计算误差函数的积分 380
293 使用ERFC函数计算补余误差函数 381
Chapter 12 其他函数应用技巧 382
12.1 数据库函数 383
294 使用DAVERAGE函数计算满足给定条件数据的平均值 383
295 使用DCOUNT函数计算数据库中满足给定条件的单元格个数 384
296 使用DCOUNTA函数计算满足给定条件的非空单元格个数 385
297 使用DGET函数计算数据库中满足给定条件的单个值 387
298 使用DMAX函数计算数据库中满足给定条件的最大数值 388
299 使用DMIN函数计算数据库中满足给定条件的最小数值 389
300 使用DPRODUCT函数计算满足给定条件数值的乘积 390
301 使用DSTDEV函数计算数据库中样本总体标准偏差 391
302 使用DSTDEVP函数计算数据库中总体标准偏差 392
303 使用DSUM函数计算数据库中满足给定条件的数字和 393
304 使用DVAR函数根据数据库条目样本估算数据的方差 395
305 使用DVARP函数计算数据的总体方差 396
12.2 信息函数 397
306 使用CELL函数返回引用单元格信息 397
307 使用ERROR.TYPE函数返回对应错误类型数值 400
308 使用INFO函数返回与当前操作环境有关的信息 401
309 使用N函数返回转换为数字后的值 402
310 使用TYPE函数返回表示值的数据类型的数字 403
311 使用NA函数返回错误值 404
12.3 检测类函数 405
312 使用ISBLANK函数判断测试对象 405
313 使用ISEVEN函数判断数值是否为偶数 407
314 使用ISODD函数判断数值是否为奇数 408
315 使用ISERR函数检测值是否有＃N/A以外的错误值 409
316 使用ISERROR函数检测值是否为错误值 410
317 使用ISLOGICAL函数检测值是否是逻辑值 411
318 使用ISNA函数检测值是否为＃N/A错误值 412
319 使用ISNONTEXT函数检测值是否不为文本值 413
320 使用ISNUMBER函数检测值是否为数值 414
321 使用ISREF函数检测值是否为引用 415
322 使用ISTEXT函数检测值是否为文本 416
Chapter 13 数组公式应用技巧 418
13.1 输入数组公式 419
323 输入一维横向数组 419
324 输入一维纵向数组 420
325 输入二维数组 421
326 由行列函数生成数组 422
327 将二维数组转换为一维数组 423
13.2 提取子数组 425
328 从一维数组中提取子数组 425
329 从二维数组中提取子数组 427
13.3 数组运算 428
330 在一个单元格中进行数组运算 428
331 多个单元格的数组运算 429
13.4 应用数组公式 430
332 对数据区域满足条件的值进行求和 430
333 为指定范围内的数值分类 432
334 创建交叉数据分析表 433
335 利用数组公式排序 433