Excel函数应用500例

第1章　Excel函数基础　1
1.1　Excel 2003的基础知识　1
1.1.1　工作簿、工作表与单元格　1
1.1.2　函数与参数　3
1.2　单元格的引用　3
1.2.1　A1和R1C1引用样式　3
1.2.2　绝对引用、相对引用和混合引用　5
1.2.3　三维引用　8
1.3　使用公式　10
1.3.1　公式的基本元素　10
1.3.2　常见运算符　10
1.3.3　公式的使用　12
1.3.4　为公式命名　14
1.4　使用函数　16
1.5　函数的分类　19
第2章　数学与三角函数　21
1．返回数字的绝对值：ABS　21
2．返回数字的反余弦值：ACOS　21
3．返回数字的反双曲余弦值：ACOSH　22
4．返回数字的反正弦值：ASIN　22
5．返回数字的反双曲正弦值：ASINH　23
6．返回数字的反正切值：ATAN　24
7．返回给定坐标值的反正切值：ATAN2　24
8．返回数字的反双曲正切值：ATANH　25
9．按条件向上舍入：CEILING　26
10．计算组合数：COMBIN　27
11．计算余弦值：COS　28
12．计算数字的双曲余弦值：COSH　28
13．将弧度转换为度：DEGREES　29
14．将数字舍入为偶数：EVEN　29
15．返回e的n次幂：EXP　30
16．计算数字的阶乘：FACT　31
17．计算数字的双倍阶乘：FACTDOUBLE　31
18．按条件向下舍入：FLOOR　32
19．返回最大公约数：GCD　33
20．将数字向下舍入取整：INT　34
21．返回最小公倍数：LCM　34
22．返回数字的自然对数：LN　35
23．返回指定底数的对数：LOG　35
24．返回以10为底的对数：LOG10　36
25．返回数组的矩阵行列式值：MDETERM　36
26．返回数组矩阵的逆矩阵：MINVERSE　37
27．返回矩阵的乘积：MMULT　39
28．求两数的余数：MOD　39
29．返回按指定基数舍入数值：MROUND　40
30．返回和的阶乘与阶乘乘积的比值：MULTINOMIAL　41
31．返回舍入奇数：ODD　42
32．返回数学常量：PI　43
33．返回数字的乘幂：POWER　43
34．返回乘积值：PRODUCT　44
35．返回商的整数部分：QUOTIENT　45
36．返回弧度值：RADIANS　45
37．返回随机数：RAND　46
38．返回指定两数之间的随机数：RANDBETWEEN　46
39．转换成文本形式罗马数字：ROMAN　47
40．返回按指定位数四舍五入的数字：ROUND　48
41．向下舍入数字：ROUNDDOWN　48
42．向上舍入数字：ROUNDUP　49
43．返回幂级数之和：SERIESSUM　50
44．返回数字的符号：SIGN　50
45．返回正弦值：SIN　51
46．返回双曲正弦值：SINH　52
47．计算平方根：SQRT　52
48．返回与PI乘积的平方根：SQRTPI　53
49．返回分类汇总计算：SUBTOTAL　53
50．对数字求和：SUM　55
51．按条件求和：SUMIF　56
52．返回对应元素的乘积之和：SUMPRODUCT　57
53．求平方和：SUMSQ　58
54．求平方差之和：SUMX2MY2　58
55．求平方和之和：SUMX2PY2　59
56．求对应数值差的平方和：SUMXMY2　60
57．计算正切值：TAN　61
58．求双曲正切：TANH　62
59．取整函数：TRUNC　62
第3章　日期与时间函数　64
1．返回代表特定日期的序列号：DATE　64
2．返回两个日期之间的差值：DATEDIF　65
3．返回文本日期的系列数：DATEVALUE　67
4．返回以系列数表示的日期的天数：DAY　68
5．按每年360天返回两个日期相差的天数：DAYS360　68
6．返回某个日期之前或之后指定月份数的日期系列：EDATE　69
7．返回某个日期之前或之后指定月份最后一天的序列号：EOMONTH　70
8．返回时间值的小时数：HOUR　71
9．返回时间值的分钟数：MINUTE　72
10．返回以序列号表示的日期中的月份：MONTH　72
11．返回两个日期之间完整的工作日数：NETWORK DAYS　73
12．返回当前日期和时间对应的序列号：NOW　74
13．返回时间值的秒数：SECOND　75
14．返回特定时间的小数值：TIME　75
15．返回文本字符串代表的时间值：TIMEVALUE　76
16．返回当前日期的序列号：TODAY　77
17．返回某个日期的星期数：WEEKDAY　77
18．返回某个日期在一年中是第几周：WEEKNUM　78
19．返回起始日期之前或之后相隔指定工作日的日期值：WORKDAY　79
20．返回日期对应的年份：YEAR　80
21．返回两个日期之间的天数占全年总天数的百分比：YEARFRAC　81
第4章　逻辑函数　83
1．返回逻辑值交集的计算结果：AND　83
2．返回逻辑值FALSE：FALSE　83
3．返回逻辑判断结果：IF　84
4．返回数字的相反值：NOT　85
5．返回逻辑值并集的计算结果：OR　86
6．返回逻辑值TRUE：TRUE　87
第5章　查找与引用函数　88
1．查找与引用函数：ADDRESS　88
2．返回引用中包含的区域个数：AREAS　89
3．选出参数列表中的数值：CHOOSE　90
4．返回给定引用的列标：COLUMN　90
5．返回数组或者引用的列数：COLUMNS　91
6．在首行查找指定的数值：HLOOKUP　92
7．创建快捷方式(跳转)：HYPERLINK　93
8．返回指定内容：INDEX　95
9．返回由文本字符串指定的引用：INDIRECT　98
10．查找数据：LOOKUP　99
11．在数组中查找值的位置：MATCH　101
12．返回引用的偏移：OFFSET　102
13．返回引用的行号：ROW　103
14．返回引用或者数组的行数：ROWS　104
15．返回实时数据：RTD　105
16．返回转置单元格区域：TRANSPOSE　106
17．垂直查找：VLOOKUP　106
第6章　文本函数　109
1．将全角字符转换为半角字符：ASC　109
2．转换为泰语文本：BAHTTEXT　109
3．返回数字代码的字符：CHAR　110
4．删除不能打印的字符：CLEAN　111
5．返回字符的数字代码：CODE　111
6．合并字符串：CONCATENATE　112
7．转换为货币文本：DOLLAR　113
8．转换为货币文本：RMB　114
9．比较两个字符串是否相同：EXACT　114
10．查找文本字符串：FIND　115
11．查找文本字符串：FINDB　116
12．按小数位取整并返回文本格式：FIXED　116
13．按指定字符数返回左边字符串：LEFT　117
14．按指定字节数返回左边字符串：LEFTB　118
15．返回字符串的字符数：LEN　119
16．返回字符串的字节数：LENB　119
17．将文本转换为小写：LOWER　120
18．返回特定字符：MID　120
19．返回特定字符：MIDB　121
20．提取文本中的拼音字符：PHONETIC　122
21．将部分字符转换为大写：PROPER　123
22．替换文本：REPLACE　123
23．替换文本：REPLACEB　124
24．重复显示文本：REPT　125
25．返回右边字符串：RIGHT　125
26．返回右边字符串：RIGHTB　126
27．查找字符串：SEARCH　127
28．查找字符串：SEARCHB　128
29．替换指定文本：SUBSTITUTE　129
30．返回文本：T　129
31．将数值转换为文本：TEXT　130
32．清除文本中的空格：TRIM　131
33．将文本转换为大写：UPPER　132
34．将文本转换为数字：VALUE　132
35．将半角转换为全角：WIDECHAR　133
第7章　信息函数　135
1．返回单元格信息：CELL　135
2．空白单元格数：COUNTBLANK　138
3．判断错误类型：ERROR.TYPE　138
4．当前操作环境的信息：INFO　139
5．判断是否为错误值：ISBLANK　140
6．判断是否为错误值：ISERR　141
7．判断是否为任意错误值：ISERROR　142
8．判断是否为逻辑值：ISLOGICAL　142
9．判断是否为错误值#N/A：ISNA　143
10．判断是否为非文本：ISNONTEXT　143
11．判断是否为数字：ISNUMBER　144
12．判断是否为引用：ISREF　144
13．判断是否为逻辑值：ISTEXT　145
14．判断参数是否为偶数：ISEVEN　146
15．判断参数是否为奇数：ISODD　146
16．转换为数值：N　147
17．返回错误值＃N/A：NA　147
18．返回数值的类型：TYPE　148
第8章　数据库函数　150
1．条目的平均值：DAVERAGE　150
2．符合条件的数字单元格数：DCOUNT　151
3．符合条件的非空单元格数：DCOUNTA　152
4．符合条件的单个值：DGET　153
5．符合条件的最大值：DMAX　154
6．符合条件的最小值：DMIN　155
7．符合条件的数值的乘积：DPRODUCT　156
8．作为样本计算标准偏差：DSTDEV　157
9．作为样本计算标准偏差：DSTDEVP　158
10．符合条件的数值之和：DSUM　158
11．作为样本计算方差：DVAR　159
12．作为样本总体计算方差：DVARP　160
13．检索汇总数据：GETPIVOTDATA　161
第9章　工程函数　164
1．修正Bessel函数值：BESSELI　164
2．返回Bessel函数值：BESSELJ　165
3．返回Bessel函数值：BESSELK　166
4．返回Bessel函数值：BESSELY　166
5．二进制转换为十进制：BIN2DEC　167
6．二进制转换为十六进制：BIN2HEX　168
7．二进制转换为八进制：BIN2OCT　169
8．转换为复数：COMPLEX　170
9．将数字转换到不同的度量系统：CONVERT　171
10．十进制转换为二进制：DEC2BIN　174
11．十进制转换为十六进制：DEC2HEX　175
12．十进制转换为八进制：DEC2OCT　176
13．测试两个数值是否相等：DELTA　177
14．求误差函数在上下限之间的积分：ERF　178
15．求ERF函数的余误差函数：ERFC　178
16．判断某值是否大于等于一个特定值：GESTEP　179
17．十六进制转换为二进制：HEX2BIN　180
18．十六进制转换为十进制：HEX2DEC　181
19．十六进制转换为八进制：HEX2OCT　182
20．求复数的绝对值：IMABS　183
21．返回复数的虚系数：IMAGINARY　184
22．返回以弧度表示的角：IMARGUMENT　184
23．返回共轭复数：IMCONJUGATE　185
24．求复数的余弦：IMCOS　186
25．求两个复数的商：IMDIV　186
26．求复数的指数：IMEXP　187
27．求复数的自然对数：IMLN　187
28．求复数的常用对数：IMLOG10　188
29．求复数以2为底的对数：IMLOG2　189
30．求复数的n次幂：IMPOWER　190
31．求复数的乘积：IMPRODUCT　191
32．返回复数的实系数：IMREAL　192
33．求复数的正弦值：IMSIN　192
34．求复数的平方根：IMSQRT　193
35．求两个复数的差：IMSUB　194
36．求两个复数的和：IMSUM　194
37．八进制转换为二进制：OCT2BIN　195
38．八进制转换为十进制：OCT2DEC　196
39．八进制转换为十六进制：OCT2HEX　197
第10章　统计函数　199
1．计算绝对偏差绝对值：AVEDEV　199
2．计算算术平均值：AVERAGE　199
3．计算非空单元格的平均值：AVERAGEA　200
4．返回Beta累积分布函数：BETADIST　201
5．返回Beta累积分布函数的反函数：BETAINV　202
6．返回一元二项式分布的概率值：BINOMDIST　203
7．返回r2分布的单尾概率：CHIDIST　204
8．返回r2分布单尾概率的反函数值：CHIINV　205
9．返回独立性检验值：CHITEST　206
10．返回平均值的置信区间：CONFIDENCE　208
11．返回两组数值的相关系数：CORREL　208
12．计算数值单元格的个数：COUNT　210
13．计算非空值的单元格的个数：COUNTA　211
14．计算空白单元格的个数：COUNTBLANK　211
15．计算满足给定条件的单元格的个数：COUNTIF　212
16．协方差：COVAR　213
17．返回累积二项式分布大于等于临界值的最小值：CRITBINOM　214
18．求平均值偏差的平方和：DEVSQ　215
19．求指数分布：EXPONDIST　216
20．求F概率分布：FDIST　217
21．求F概率分布的反函数值：FINV　218
22．返回点x的Fisher变换：FISHER　219
23．返回Fisher变换的反函数值：FISHERINV　220
24．根据已有的数值返回未来值：FORECAST　220
25．返回数据的频率分布：FREQUENCY　221
26．返回F检验的结果：FTEST　222
27．计算伽玛分布：GAMMADIST　224
28．返回伽玛分布的反函数：GAMMAINV　225
29．计算伽玛函数的自然对数：GAMMALN　225
30．计算几何平均值：GEOMEAN　226
31．预测指数增长值：GROWTH　227
32．计算数据集合的调和平均值：HARMEAN　228
33．返回超几何分布：HYPGEOMDIST　229
34．求截距：INTERCEPT　230
35．返回数据集的峰值：KURT　232
36．返回数据集中第k个最大值：LARGE　233
37．对已知数据进行最佳直线拟合：LINEST　233
38．计算最佳指数回归拟合曲线：LOGEST　235
39．返回x的对数累积分布函数的反函数：LOGINV　237
40．返回x的对数累积分布函数：LOGNORMDIST　238
41．返回一组值中的最大值：MAX　239
42．返回参数列表中的最大值：MAXA　240
43．返回给定数值集合的中值：MEDIAN　241
44．返回一组值中的最小值：MIN　241
45．返回参数列表中的最小值：MINA　242
46．返回出现频率最多的数值：MODE　243
47．返回负二项式分布：NEGBINOMDIST　244
48．返回正态分布函数：NORMDIST　245
49．返回正态累计分布函数的反函数：NORMINV　246
50．返回标准正态分布的累积函数：NORMSDIST　247
51．返回标准正态分布累积函数的反函数：NORMSINV　248
52．返回Pearson乘积矩相关系数：PEARSON　249
53．返回数值的第K个百分点的值：PERCENTILE　250
54．返回特定数值的百分比排位：PERCENTRANK　251
55．返回对象集合中选取若干个对象的排列数：PERMUT　252
56．返回泊松分布：POISSON　253
57．返回特定数值落在某区间内的概率：PROB　254
58．返回数据集的四分位数：QUARTILE　255
59．返回数字在列表中的排位：RANK　256
60．返回Pearson乘积矩相关系数的平方：RSQ　258
61．返回分布的偏斜度：SKEW　258
62．返回拟合的线性回归直线的斜率：SLOPE　260
63．返回数据集中第K个最小值：SMALL　261
64．返回正态分布的数值：STANDARDIZE　262
65．返回样本的标准偏差：STDEV　263
66．返回给定样本的标准偏差：STDEVA　264
67．返回样本总体的标准偏差：STDEVP　265
68．返回给定样本总体的标准偏差：STDEVPA　266
69．返回标准偏差：STEYX　267
70．返回学生T分布的概率：TDIST　268
71．返回学生T分布的T值：TINV　270
72．返回线性回归拟合线的Y值：TREND　271
73．返回数据集的内部平均值：TRIMMEAN　272
74．返回学生T检验的概率值：TTEST　273
75．返回样本的方差：VAR　275
76．返回给定样本的方差：VARA　276
77．返回样本总体的方差：VARP　277
78．返回给定样本总体的方差：VARPA　278
79．返回韦伯分布：WEIBULL　279
80．返回Z检验的单尾概率值：ZTEST　280
第11章　财务函数　282
1．返回有价证券的应计利息：ACCRINT　282
2．返回一次性付息证券的应计利息：ACCRINTM　283
3．返回每个折旧期间的折旧值：AMORDEGRC　285
4．按线性折旧法计算折旧值：AMORLINC　286
5．返回截止到成交日的天数：COUPDAYBS　288
6．返回成交日所在的付息期的天数：COUPDAYS　289
7．返回成交日到下一个付息日之间的天数：COUPDAYSNC　289
8．返回成交日之后下一个付息日的数字：COUPNCD　290
9．返回成交日和到期日之间的付息次数：COUPNUM　291
10．返回成交日之前的上一个付息日：COUPPCD　292
11．返回某个期间内累计要偿还的利息数额：CUMIPMT　293
12．返回某个期间内累计要偿还的本金数额：CUMPRINC　294
13．返回用固定余额递减法计算的资产的折旧值：DB　295
14．返回用双倍余额递减法计算的折旧值：DDB　296
15．返回有价证券的贴现率：DISC　298
16．将按分数表示的价格转换为小数：DOLLARDE　299
17．将按小数表示的价格转换为分数：DOLLARFR　299
18．返回有价证券的修正期限：DURATION　300
19．计算有效的年利率：EFFECT　301
20．返回投资的未来值：FV　302
21．返回本金的未来值：FVSCHEDULE　304
22．返回一次性付息证券的利率：INTRATE　305
23．返回利息偿还额：IPMT　306
24．返回一组现金流的内部收益率：IRR　307
25．返回特定投资期内要支付的利息：ISPMT　309
26．返回有价证券的Macauley修正期限：MDURATION　309
27．返回修正内部收益率：MIRR　310
28．返回名义年利率：NOMINAL　311
29．返回投资的总期数：NPER　312
30．返回投资的净现值：NPV　314
31．返回有价证券的价格：ODDFPRICE　315
32．返回有价证券的收益率：ODDFYIELD　316
33．返回末期付息日不固定的有价证券的价格：ODDLPRICE　317
34．返回末期付息日不固定的有价证券的收益率：ODDLYIELD　319
35．返回贷款的每期付款额：PMT　320
36．返回某一个给定期间内的本金偿还额：PPMT　321
37．返回定期付息的有价证券的价格：PRICE　322
38．返回折价发行的有价证券的价格：PRICEDISC　323
39．返回到期付息的有价证券的价格：PRICEMAT　324
40．返回投资的现值：PV　325
41．返回年金的各期利率：RATE　327
42．返回到期收回的总金额：RECEIVED　327
43．返回资产的线性折旧值：SLN　329
44．返回按年限总和折旧法计算的折旧值：SYD　330
45．返回国库券的等效收益率：TBILLEQ　330
46．返回国库券的价格：TBILLPRICE　331
47．返回国库券的收益率：TBILLYIELD　332
48．返回指定期间的资产折旧值：VDB　333
49．返回一组现金流的内部收益率：XIRR　335
50．返回现金流的净现值：XNPV　336
51．返回有价证券的收益率：YIELD　338
52．返回折价发行的有价证券的年收益率：YIELDDISC　339
53．返回到期付息的有价证券的年收益率：YIELDMAT　340
附录　函数应用常见技巧　342
1．如何进行单元格的引用？　342
2．如何输入18位身份证号码？　342
3．如何自动计算应收账款滞纳金？　343
4．如何在Excel中统计某个数的位置(在哪一行)？　343
5．如何在工作表之间查找相同的内容并将其复制到新的工作表中？　343
6．如何统计偶数单元格中的数据之和？　344
7．如何将数字直接转换为日期？　344
8．如何进行列转换？　344
9．如何快速地计算年龄？　345
10．如何对列进行跳跃求和？　347
11．如何对公式进行排列，使其清晰易懂？　347
12．如何进行大小写转换？　347
13．如何筛选出特定的行？　348
14．如何以数组的形式输出结果？　348
15．如何加载【分析工具库】？　348
16．如何将某一个函数作为另一个函数的参数来使用？　348
17．如何快速地选取工作表中所有包含公式的单元格？　349
18．如何简化求平均值的公式？　350
19．如何利用函数实现公式无法实现的计算？　351
20．如何将整个行或整个列作为参数？　352
21．如何把文字值作为参数？　352
22．如何把表达式作为参数？　353
23．如何将数组作为参数？　353
24．如何提高编辑任务的速度？　353
25．如何利用函数实现判断功能？　354
26．如何自动地输入函数？　355