超实用Excel函数范例速查宝典PDF电子书下载

CHAPTER 00函数的基础知识 1

1函数简介 2

2输入函数 6

3函数的复制和单元格引用 15

4函数的修改 24

5函数的组合 27

6使用数组 32

7启用加载项（Excel 2000～2003） 35

8检查错误 36

9多维数据集函数（Excel 2007～2013） 42

CUBEKPIMEMBER返回关键绩效指标（KPI）的属性 42

CUBEMEMBER返回多维数据集的成员或群组 43

CUBEMEMBERPROPERTY返回多维数据集内的成员属性值 43

CUBERANKEDMEMBER返回多维数据集合内所指定顺序的成员 43

CUBESET 将集合表达式传送到服务器上的多维数据集以定义项目 44

CUBESETCOUNT 返回多维数据集合内的项目数 44

CUBEVALUE从多维数据集中返回汇总值 45

CHAPTER 01数学与三角函数 48

数学与三角函数的基础知识 48

计算 52

SUM求和 52

SUMIF带条件求和 56

SUMIFS以多个条件来求出数值的合计值 60

PRODUCT 求多个数值的积 62

SUMPRODUCT 将数组元素相乘并求出乘积的合计值 63

SUMSQ求数值的平方和 67

SUMX2PY2求两个数组中各元素平方和的总和 68

SUMX2MY2求两个数组中各元素平方差的总和 68

SUMXMY2求两个数组中各元素差的平方和 68

SUBTOTAL求11种的汇总数值 70

AGGREGATE求19种的汇总数值 72

QUOTIENT求两数相除后商的整数部分 74

MOD求两数相除后的余数 75

ABS求数值的绝对值 77

SIGN查询数值的正负号 78

SERIESSUM用幂级数求近似值 80

GCD求最大公约数 82

LCM求最小公倍数 82

数值处理 83

INT把数值小数点之后的部分舍去 83

TRUNC返回舍去到指定位数的数值 84

ROUND返回四舍五入到指定位数的数值 86

ROUNDUP将数值无条件向上舍入到指定位数 88

ROUNDDOWN将数值无条件舍去到指定的位数 89

CEILING将数值向上舍入，使其最接近基数的倍数 90

CEIlING.PRECISE／ISO.CEILING将数值向上舍入，使其最接近“基数”的倍数 90

FLOOR将数值向下舍入到最接近基数的倍数 92

FLOOR.PRECISE将数值向下舍去到最接近基数的倍数 92

MROUND将数值四舍五入到指定数值的倍数 96

EVEN将数值向上舍入到最接近的偶数值 98

ODD将数值向上舍入到最接近的奇数值 99

三角函数 100

RADIANS将角的单位由度转换成弧度 100

DEGREES将角的单位由弧度转换成度 101

SIN求数值的正弦值 102

COS求数值的余弦值 104

TAN求数值的正切值 106

ASIN求数值的反正弦值 108

ACOS求数值的反余弦值 109

ATAN求数值的反正切值 110

ATAN2求指定坐标点的反正切值 111

SINH求数值的双曲正弦值 112

COSH求数值的双曲余弦值 113

TANH 求数值的双曲正切值 114

ASINH求数值的反双曲正弦值 115

ACOSH求数值的反双曲余弦值 116

ATANH求数值的反双曲正切值 117

指数与对数函数 118

POWER求数值的乘幂 118

EXP 求指数的函数 120

LOG求以指定数值为底数的对数 122

LN求数值的自然对数 123

LOG10求数值的常用对数 124

随机数 125

RAND产生大于等于0且小于1的随机数 125

RANDBETWEEN产生整数的随机数 127

圆周率·平方根 128

PI求圆周率（π）的近似值 128

SQRTPI求圆周率（π）的倍数的平方根 129

SQRT求正数的平方根 130

矩阵行列式 132

MDETERM求数组矩阵行列式的值 132

MINVERSE求数组矩阵的逆矩阵 134

MMULT求两个数组的矩阵乘积 136

组合 138

FACT求数值的阶乘 138

FACTDOUBLE求数值的双阶乘 139

COMBIN求数值的组合数（二项系数） 140

MULTINOMIAL求数值的多项系数 142

字符转换 143

ROMAN将阿拉伯数字转换成罗马数字 143

CHAPTER 02统计函数 145

统计函数的基础知识 146

基础统计 152

COUNT 求数值的个数 152

COUNTA求非空单元格的个数 154

COUNTBLANK求空白单元格的个数 156

COUNTIF求符合条件的单元格个数 158

COUNTIFS求符合多个条件的单元格个数 162

FREQUENCY 计算数值在某个区域内的出现频率，然后返回一列垂直数组 164

AVERAGE求数值的平均值 166

AVERAGEA求非空单元格的数据平均值 169

AVERAGEIF求满足条件的数值平均值 170

AVERAGEIFS求满足多个条件的数值平均值 172

TRIMMEAN求除去异常值的数值平均值 174

GEOMEAN求数值的几何平均值 176

HARMEAN求数值的调和平均值 177

MEDIAN求数值的中间值 178

MODE／MODE.SNGL求数值中第一个被查找到且出现频率最高的值（众数） 179

MODE.MULT求数值中多个出现频率最高的值 180

MAX求数值的最大值 182

MIN求数值的最小值 184

MAXA求非空数据的最大值 186

MINA求非空数据的最小值 187

VAR/VAR.S计算基于样本的方差 188

VARP／VAR.P求总体数值的方差 190

VARA求非空数据的样本方差 192

VARPA求非空数据的总体方差 193

STDEV／STDEV.S求数值的样本标准差 194

STDEVP／STDEV.P 求数值的总体标准差 196

STDEVA求非空数据的样本标准差 198

STDEVPA求非空数据的总体标准差 199

AVEDEV求一组数值的平均差 200

DEVSQ求各数值与其平均值的偏差的平方和 201

SKEW求相对于正态分布的偏斜度 202

KURT求相对于正态分布的峰度值 204

排序 206

RANK／RAN K.EQ求数值在数值列表中的排名 206

RANK.AVG求数值在数值列表中的排名（如果排名相同则返回平均排名） 209

LARGE返回数据集中第k个最大值 210

SMALL返回数据集中第k个最小值 212

QUARTILE／QUARTILE.INC求数值集的四分位数 214

PERCENTILE／PERCENTILE.INC求区域中数值的第k个百分点的值 216

QUARTILE.EXC求去除数值集中的最小值和最大值后的四分位数 218

PERCENTILE.EXC返回区域中数值的第k个百分点的值（0＜k＜1） 219

PERCENTRANK／PERCENTRANK.INC求特定数值在一个数值集中的百分比排名 220

PERCENTRAN K.EXC求特定数值在一个数值集中的百分比排名（不包括0和1） 222

概率分布 223

PERMUT 求排列数 223

BINOMDIST／BINOM.DIST求二项分布的概率值 224

CRITBINOM／BINOM.INV求累积二项分布概率达到目标值的最小值 228

NEGBINOMDIST 求负二项分布的概率值 230

NEGBINOM.DIST 求负二项分布的概率值和累积概率值 230

HYPGEOMDIST 求超几何分布的概率值 232

HYPGEOM.DIST求超几何分布的概率值和累积概率值 232

POISSON／POISSON.DIST求泊松分布的概率值 236

PROB 求随机变量在指定范围内的概率值 238

NORM DIST／NORM.DIST求出正态分布的概率密度函数值和累积分布函数值 239

NORM INV／NORM.INV求正态分布的累积分布函数的反函数 242

NORMSDIST求标准正态分布的累积分布函数值 244

NORM.S.DIST求标准正态分布的概率值和累积概率值 244

NORMSINV／NORM.S.INV求标准正态分布的累积分布函数的反函数值 246

STANDARDIZE求正态化数值 248

LOGNORMDIST 求对数正态分布的累积分布函数值 250

LOGNORM.DIST 求对数正态分布的概率值和累积概率值 250

LOGINV／LOGNORM.INV求对数正态分布的累积分布函数的反函数值 252

EXPONDIST／EXPON.DIST求指数分布的概率值和累积概率值 254

WEIBULL／WEIBULL.DIST求韦伯分布的概率值和累积概率值 256

GAMMADIST／GAMMADIST求伽马分布的概率值和累积概率值 258

GAMMAINV／GAMMA.INV求伽马分布函数的反函数值 260

GAMMALN／GAMMALN.PRECISE求伽马分布的自然对数值 262

BETADIST求Beta分布函数值 263

BETA.DIST求Beta分布的概率值和累积概率值 263

BETAINV／BETA.INV求Beta分布函数的反函数值 266

估计·检验 268

CONFIDENCE／CONFIDENCE.NORM求总体平均值的置信区间 268

CONFIDENCE.T 求遵循t分布的总体平均值的单侧置信区间 270

CHISQ.DIST求X2分布的概率和左尾概率 272

CHIDIST／CHISQ.DIST.RT求X2分布的右尾概率 274

CHISQ.INV求X2分布的左尾概率的反函数值 276

CHIINV／CHISQ.INV.RT求X2分布的右尾概率的反函数值 278

CHITEST／CHISQ.TEST进行x2检验 280

F.DIST求F分布的概率和左尾概率 282

FDIST／F.DIST.RT求F分布的右尾概率 284

F.INV求F分布的左尾概率的反函数值 286

FINV／F.INV.RT求F分布的右尾概率的反函数值 288

FTEST／F.TEST 进行F检验 290

T.DIST求t分布的概率和左尾概率 292

TDIST求t分布的右尾概率或双尾概率 294

T.DIST.RT求t分布的右尾概率 296

T.DIST.2T求t分布的双尾概率 297

TINV／T.INV.2T求t分布的双尾概率的反函数值 298

T.INV求t分布的左尾概率的反函数值 300

TTEST／T.TEST 进行t检验 302

ZTEST／Z.TEST 进行Z检验 304

FISHER返回点X的Fisher变换值 306

FISHERINV求Fisher变换的反函数值 308

协方差、相关系数和回归分析 310

COVAR／COVARIANCE.P求两个变量的协方差 310

COVARIANCE.S求两个变量的协方差 310

CORREL求两个变量间的相关系数 312

PEARSON求皮尔森积矩相关系数 312

SLOPE求回归直线的斜率 314

INTERCEPT 求回归直线的截距 316

LINEST 求回归直线的系数与常数项 318

FORECAST 求两个变量间回归直线的预测值 320

TREND求回归直线的预测值 322

STEYX求回归直线的标准误差值 324

RSQ求回归直线的判断系数 326

GROWTH求指数回归曲线的预测值 328

LOGEST 求指数回归曲线的系数与底数 330

CHAPTER 03日期和时间函数 333

日期和时间函数的基础知识 334

当前的日期和时间 338

TODAY显示当前的日期 338

NOW显示当前的日期和时间 340

由序列值求指定日期和时间 342

YEAR根据日期序列值显示年 342

MONTH根据指定的序列值显示月 344

DAY根据指定的序列值显示日 346

HOUR根据指定的序列值显示时 348

MINUTE根据指定的序列值显示分 348

SECOND根据指定的序列值显示秒 348

WEEKDAY根据指定的序列值显示星期几 350

WEEKNUM根据序列值显示该年第几周的号码 354

由指定日期和时间求序列值 356

DATE由年、月、日的数值求日期的序列值 356

TIME由时、分、秒的数值求时间的序列值 358

由字符串求序列值 361

DATEVALUE将日期字符串转换为序列值 361

TIMEVALUE将时间字符串转换成序列值 362

计算日期 364

EDATE计算指定月数后的日期 364

EOMONTH 计算指定月数后该月的最后一天 365

WORKDAY求得去除周六、日和节假日之后指定天数后的日期 366

WORKDAYINTL求得去除公休日和节假日之后指定天数后的日期 368

期间差 370

NETWORKDAYS计算指定期间去除周六、日与节假日后的工作天数 370

NETWORKDAYS.INTL计算指定期间内去除公休日与节假日的工作日 372

DAYS360求出一年以360天计算时开始日到结束日的天数 374

YEARFRAC求出一年以1计算时经过天数的比例 375

DATEDIF计算从开始日到结束日的期间差 376

将序列值转换为文本字符串 378

DATESTRING将日期序列值转换为文本字符串 378

CHAPTER 04逻辑函数 379

逻辑函数的基础知识 380

条件的分支 382

IF根据条件判断的结果分别处理 382

IFERROR公式的计算结果错误时返回指定的值，否则返回公式结果 386

条件的判断 388

AND判断设置的多个条件是否全部成立 388

OR判断所设置的多个条件中是否至少有一个条件成立 390

NOT判断设置的条件是否不成立 392

逻辑值的显示 394

TRUE返回逻辑值TRUE 394

FALSE返回逻辑值FALSE 394

CHAPTER 05查找和引用函数 395

查找和引用函数的基础知识 396

数据的查找 398

VLOOKUP 从单元格区域首列查找符合的值，并返回当前行中指定列处的值 398

HLOOKUP从单元格区域首行查找符合的值，并返回当前列中指定行处的值 402

LOOKUP（向量形式）从单行或单列区域中查找并返回匹配的值 406

LOOKUP（数组形式）从数组中查找并返回匹配的值 408

CHOOSE从值参数列表中查找相匹配的值 410

位置的查找 412

MATCH返回查找值在单元格区域中的相应位置 412

INDEX（数组形式）返回指定行号列标交叉处的单元格值 414

INDEX（引用形式）返回指定行号列标交叉处的单元格值 416

单元格的引用 418

ADDRESS按指定的行号列标返回文本形式的单元格地址 418

INDIRECT 返回文本形式的单元格地址中的内容 420

OFFSET 以指定的单元格地址为基准，返回移动指定偏移量后的新地址引用 422

AREAS返回引用地址中的区域个数 426

ROW返回指定单元格的行号 428

COLUMN返回指定单元格的列标 430

ROWS返回指定区域的行数 432

COLUMNS返回指定区域的列数 432

TRANSPOSE行列转置 434

超链接 436

HYPERLINK制作一个超链接显示指定链接地址的文件 436

数据的提取 438

GETPIVOTDATA从数据透视表中提取数据 438

GETPIVOTDATA（Excel 2000／97）从数据透视表中提取数据 440

RTD从支持COM加载项的程序中提取数据 442

CHAPTER 06文本函数 443

文本函数的基础知识 444

文本的操作 447

LEN求文本的字符数 447

LENB求文本的字节数 448

CONCATENATE将多个文本合并成一个文本 449

FIND求出查找文本位于另一文本的第几个字符（区分英文大小写） 450

SEARCH求出查找文本位于另一文本的第几个字符（不区分英文大小写） 452

FINDB求出查找文本位于另一文本的第几个字节（区分英文大小写） 454

SEARCHB求出查找文本位于另一文本的第几个字节（不区分英文大小写） 455

LEFT 从文本的最左端开始向右提取出指定字符数的文本 456

LEFTB从文本的最左端开始向右提取出指定字节数的文本 458

MID 从文本中的指定位置提取出指定字符数的文本 460

MIDB从文本中的指定位置提取出指定字节数的文本 462

RIGHT从文本的最右端开始向左提取出指定字符数的文本 464

RIGHTB从文本的最右端开始向左提取出指定字节数的文本 465

文本的替换 466

SUBSTITUTE查找文本内部，用新字符替换文本的一部分 466

REPLACE将文本中的部分文本根据指定的字符数替换为新文本 468

REPLACEB将文本中的部分文本根据指定的字节数替换为新文本 470

文本的转换 472

ASC将全角字符转换成半角字符 472

WIDECHAR将半角字符转换成全角字符 473

UPPER将小写英文字母转换成大写 475

LOWER将大写英文字母转换成小写 476

PROPER将英文单词转换成首字母大写 477

TEXT 将数值转换为指定格式的文本 478

FIXED将数值按指定的位数四舍五入，用文本形式返回 482

RMB将数值四舍五入，转换为加人民币符号（？）和千位分隔符的文本 484

DOLLAR将数值四舍五入，转换为加美元符号（$）和千位分隔符的文本 486

BAHTTEXT 将数值转换成泰铢的货币形式 487

NUMBERSTRING将阿拉伯数字转换成汉字 488

VALUE将文本转换为数值 489

T 返回值中的文本 490

CODE返回文本字符串中第一个字符的数字代码 491

CHAR返回对应于数字代码的文本 492

文本的比较、删除和重复 494

EXACT比较两个文本，判断是否完全相同 494

CLEAN删除文本中无法打印的字符 496

TRIM删除文本中多余的空格 497

REPT 按指定的次数重复显示文本 498

CHAPTER 07财务函数 501

财务函数的基础知识 502

求支付金额 506

PMT 求出固定还款本金与利息的合计支付额 506

PPMT 求出偿还金额中本金的金额 508

IPMT求出偿还金额中利息的金额 508

ISPMT 求出等额本金返还时指定期数的利息金额 510

求累计金额 512

CUMPRINC求出两个周期之间支付本金的总额 512

CUMIPMT 求出两个周期之间支付利息的总额 512

求支付次数 514

NPER求出贷款偿还或储蓄的支付次数（期数） 514

求利率 516

RATE求出年金的各期利率 516

EFFECT 求出实际年利率 518

NOMINAL求出名义年利率 519

求现值 520

PV求出现值 520

NPV通过贴现率以及一系列未来投资或收入，求出净现值 522

XNPV用已知不定期发生的投资或收入，求出净现值 524

求未来值 525

FVSCHEDULE求出利率会变动的投资的未来值 525

FV有固定利率和等额分期付款时，求出未来值 526

求内部收益率 528

IRR求出定期发生现金流的内部收益率 528

XIRR求出不定期发生现金流的内部收益率 530

MIRR求出再投资与收益率不同的情况下的内部收益率（修正内部收益率） 531

求折旧值 532

DB使用固定余额递减法计算折旧值 532

SLN使用直线折旧法计算折旧值 534

DDB使用双倍余额递减法计算折旧值 535

VDB使用双倍余额递减法或直线折旧法计算折旧值 536

SYD按年数总和折旧法计算折旧值 538

AMORDEGRC对应资产的使用年数计算各会计期间的折旧值（法式计算法） 539

AMORLINC计算各会计期间的折旧值（法式计算法） 539

证券计算 540

PRICEMAT求出到期付息面值相当于100美元的证券价格 540

YIELDMAT求出到期付息证券的年利率 541

PRICE求出定期支付利息面值相当于1 00美元的证券价格 542

YIELD求出定期支付利息的证券的收益率 543

ACCRINTM求出到期付息证券的应计利息 544

ACCRINT 求出定期付息证券的应计利息 545

折价证券 546

PRICEDISC求出折价发行的面值为100美元的证券价格 546

YIELDDISC求出折价发行的面值为100美元的证券年收益率 547

INTRATE求出证券的年收益率 548

DISC求出证券的贴现率 549

RECEIVED求出到期付息证券到期后的清偿额 550

证券的日期 551

COUPNUM求出证券购买日与到期日之间的利息支付次数 551

COUPPCD求出证券购买日之前最近一次的利息支付日 552

COUPNCD求出证券购买日之后最近一次的利息支付日 552

COUPDAYBS求出证券当前利息期内截至购买日的天数 554

COUPDAYSNC求出从证券购买日到下一付息日之间的天数 554

COUPDAYS求出包含证券购买日在内的付息期天数 554

中间天数的附息证券 556

ODDFPRICE求出购买在第一次利息支付期中间的面值为100美元的证券价格 556

ODDLPRICE求出购买在末期利息支付期中间的面值为100美元的证券价格 556

ODDFYIELD.求出初次投资在第一次利息支付期中间的面值为100美元的证券收益率 558

ODDLYIELD求出初次投资在末期利息支付期中间的面值为100美元的证券收益率 558

证券的久期 560

DURATION求出麦考利久期 560

MDURATION求出修正久期 561

关于TB的计算 562

TBILLEQ求出美国短期国债（TB）的等值收益率 562

TBILLPRICE求出面值为100美元的美国短期国债（TB）的价格 563

TBILLYIELD求出美国短期国债（TB）的年利率 564

美元的计算 565

DDOLLARDE将分数形式的美元价格转换成小数形式 565

DOLLARFR将小数形式的美元价格转换成分数形式 566

CHAPTER 08信息函数 567

信息函数的基础知识 568

信息的获取 570

CELL显示单元格信息 570

ERROR.TYPE返回错误值所对应的数值 572

INFO显示Excel当前的操作环境 574

TYPE返回单元格中的数据类型 576

IS函数 578

ISBLANK判断测试对象是否为空 578

ISLOGICAL判断测试对象是否为逻辑值 579

ISNUMBER判断测试对象是否为数值 580

ISTEXT 判断测试对象是否为字符串 580

ISNONTEXT 检查测试对象是否不为字符串 580

ISEVEN判断测试对象是否为偶数 582

ISODD判断测试对象是否为奇数 583

ISREF判断测试对象是否为单元格引用 584

ISERR判断测试对象是否为“＃N／A”以外的错误 585

ISERROR判断所指定的单元格是否有错误 586

ISNA判断测试对象是否为“＃N／A”错误 588

数据的转换、错误的产生 589

N把参数所指定的数据转换为数值 589

PHONETIC提取文本字符串中的注音 590

NA产生错误值“＃N／A” 591

CHAPTER 09数据库函数 593

数据库函数的基础知识 594

数据库的计算 596

DSUM求出满足条件的数据记录的总和 596

DPRODUCT求出满足条件的数据记录的乘积 600

DAVERAGE求出满足条件的数据记录的平均值 602

数据库的统计 604

DMAX求出满足条件的数据记录的最大值 604

DMIN求出满足条件的数据记录的最小值 604

DCOUNTA求出满足条件的数据记录中，非空的单元格个数 606

DCOUNT 求出满足条件的数据记录中，有数值的单元格个数 606

DGET 求出满足条件的单个记录 610

DSTDEV以满足条件的数据记录中的数值为样本，求出样本标准差 612

DSTDEVP 以满足条件的数据记录中的数值为总体，求出标准差 612

DVAR以满足条件的数据记录中的数值为样本，求出样本方差 614

DVARP以满足条件的数据记录中的数值为总体，求出方差 614

CHAPTER 10工程函数 617

工程函数的基础知识 618

单位的转换 620

CONVERT转换数值的单位 620

DEC2BIN把十进制数转换为二进制数 622

DEC2OCT把十进制数转换为八进制数 622

DEC2HEX把十进制数转换为十六进制数 622

BIN2DEC把二进制数转换为十进制数 624

OCT2DEC把八进制数转换为十进制数 624

HEX2DEC把十六进制数转换为十进制数 624

BIN2OCT 把二进制数转换为八进制数 626

BIN2HEX把二进制数转换为十六进制数 626

OCT2BIN把八进制数转换为二进制数 627

HEX2BIN把十六进制数转换为二进制数 627

OCT2HEX把八进制数转换为十六进制数 628

HEX2OCT 把十六进制数转换为八进制数 628

DELTA比较两个数值是否相等 629

数据的比较 629

GESTEP把数值和基准值比较，判断是高于还是低于基准值 630

复数 632

COMPLEX把实系数x与虚系数y转换为x＋yi形式的复数 632

IMREAL从复数中取出实数x 634

IMAGINARY 从复数中取出虚数y 634

IMCONJUGATE求复数的共轭复数 635

IMABS求复数的绝对值 636

IMARGUMENT求复数的幅角 636

IMSUM求复数的和 637

IMSUB求复数的差 637

IMPRODUCT求复数的积 638

IMDIV求复数的商.......6 639

IMSQRT求复数的平方根 640

IMEXP求复数的指数函数值 641

IMPOWER求复数的n次幂 642

IMSIN求复数的正弦 643

IMCOS求复数的余弦 644

IMLN求复数的自然对数值 645

IMLOG10求复数的常用对数值 646

IMLOG2求复数以2为底的对数值 646

特殊计算 647

BESSELJ求n阶第一类Bessel函数值 647

BESSELY求n阶第二类Bessel 函数值 648

BESSELI求n阶第一类修正Bessel函数值 649

BESSELK求n阶第二类修正Bessel函数值 650

ERF求误差函数在上下限之间的积分值 651

ERF.PRECISE求误差函数的积分值 651

ERFC返回从x到∞（无穷）积分的ERF函数的补余误差函数值 652

ERFC.PRECISE返回从x到∞（无穷）积分的ERF函数的补余误差函数值 652

CHAPTER 11自定义函数 653

自定义函数的基础知识 654

欧洲货币的换算 656

EUROCONVERT欧盟成员国货币的相互换算 656

索引 658