Excel 2010函数与公式综合应用技巧 双色精华版

精选提升办公效率的实用技巧！
为您节约网上搜索与求助的时间！
8年口碑相传的经典工具书《随身查》作者团队诚意打造，
帮您解决问题，提高效率，不加班！

本书精选了日常办公中常用的Excel函数进行讲解，全书共11章，前两章为公式与函数基础知识介绍，包括公式创建、复制、错误值分析与处理等方面的技巧，为后面的学习做铺垫；第3章～第11章介绍了逻辑函数、数学与三角函数、时间与日期函数、文本函数、查找和引用函数、信息函数、统计函数、财务函数、数据库函数等9类共540个函数的使用技巧，每个函数都辅以实例进行讲解，力求向读者展示Excel函数在数据计算、处理和分析方面的强大功能。
本书内容实用、结构清晰、表述简练，采用双色印刷，以图文并茂的形式来进行讲解。本书非常适合经常使用Excel进行数据计算、处理与分析的办公人员阅读，也可作为行政管理人员、数据分析人员、财务人员、统计人员和营销人员案头函数速查参考书。

雏志资讯工作室由多位办公高手组成，其编写的“随身查”系列图书受到读者的一致认可，9年时间30余个品种50万册图书，在读者中口碑相传，成为经典的便携工具书。

目录

1．1公式操作基础 1

技巧1修改公式的几种方法 1
技巧2快速复制公式的4种方法 2
技巧3大范围复制公式 3
技巧4在多工作表的同一单元格建立相同
公式 4
技巧5跳过非空单元格批量建立公式 4
技巧6保留没有输入完整的公式 5
技巧7合并单元格中公式的复制 6
技巧8复制公式但不使用相对引用 6
技巧9查看长公式中某一步的计算结果 7
技巧10在新输入的行中自动填充公式 7
技巧11快速在多个合计行中填充求和公式 8
技巧12批量将文本型数字转换成数值类型 8
技巧13让大写数字金额可用于计算 9
技巧14将公式结果转换为数值 10
技巧15将公式转换为文本 10
技巧16为什么更改数据后公式计算结果不
自动更新 11
技巧17为什么显示公式而不显示计算结果 11
技巧18为什么数字与“空”单元格相加
出错 12
技巧19为什么有时候汇总金额会比实际差
1分钱 12
技巧20隐藏公式 13
技巧21禁止任何人修改公式 15

1．2函数操作基础 16

技巧22快速输入函数的3种方法 16
技巧23快速查找和学习某函数用法 18
技巧24函数参数的修改 18
技巧25分步理解函数的计算过程 19
技巧26函数出错后的修改 20
技巧27嵌套函数 20

1．3运算符优先级与通配符的使用 21

技巧28使用算术运算符 21
技巧29使用比较运算符 21
技巧30使用引用运算符 21
技巧31使用文本运算符 22
技巧32运算符的优先级顺序 22
技巧33改变运算符的优先级方法 22
技巧34公式中使用通配符*、？ 23
技巧35公式中巧用通配符~ 23

1．4数组公式 24

技巧36使用数组公式 24
技巧37数组的维数 25
技巧38普通公式与数组公式 25
技巧39单个单元格数组公式与多单元格数
组公式 26
技巧40修改多单元格数组公式 27
技巧41复制多单元格数组公式 27
技巧42常量数组在公式中的应用 27
技巧43多项计算与数组公式的区别 28
技巧44扩展或缩小多单元格数组公式 29
技巧45准确定位数组公式所在区域 29

1．5公式计算错误值分析与处理 30

技巧46通过“监视窗口”来监视数据 30
技巧47注意括号不匹配 30
技巧48注意循环引用 31
技巧49用“Excel帮助”获取错误值的处理
方法 31
技巧50“#DIV/0!”错误值分析与处理 32
技巧51“#N/A”错误值分析与处理 32
技巧52“#NAME?”错误值分析与处理 32
技巧53“#NUM!”错误值分析与处理 33
技巧54“#VALUE!”错误值分析与
处理 33
技巧55“#REF!”错误值分析与处理 34
技巧56“#NULL!”错误值分析与
处理 34
2．1数据源的引用 35

技巧1使用A1格式引用数据源 35
技巧2使用R1C1格式引用数据源 36
技巧3输入引用地址 36
技巧4用鼠标选择引用地址 36
技巧5相对引用 37
技巧6绝对引用 37
技巧7混合引用 38
技巧8用“F4”键改变数据源引用类型 39
技巧9引用其他工作表中的数据 39
技巧10引用其他工作簿中的数据 40
技巧11引用多工作表中的同一单元格 40
技巧12更新外部公式 41

2．2名称定义与使用 42
技巧13为什么要定义名称 42
技巧14快速定义名称 42
技巧15定义工作簿级与工作表级名称 43
技巧16将常量定义为名称并应用于
公式 44
技巧17将单元格区域定义为名称并应用于
公式 44
技巧18将公式定义为名称 45
技巧19名称定义中“相对引用”与“绝对引
用”的区别 46
技巧20创建动态引用名称 47
技巧21将整表创建为动态名称并
使用 48
技巧22重新编辑定义的名称 49
技巧23快速查看所有定义的名称 50
技巧24快速删除不用的名称 50
AND（判断多个条件是否同时成立） 51
OR（判断多个条件是否至少有一个条件
成立） 53
NOT（对逻辑值求反） 54
IF（根据条件判断返回指定的值） 55
IFERROR（根据公式返回的错误结果返回
指定的值） 62
TURE（返回逻辑值TRUE） 62
FALSE（返回逻辑值FALSE） 62

4．1常规数学计算 63

ABS函数（求绝对值） 63
SUM（对给定区域的数据求和） 64
SUMIF（按照指定条件求和） 66
SUMIFS（对满足多重条件的单元格求和） 71
SUMPRODUCT（将数组间对应的元素相乘，并返回乘积之和） 73
SUMTOTAL（返回列表或数据库中的分类
汇总） 77
SUMSQ（计算所有参数的平方和） 79
SUMXMY2（求两个数组中对应数值之差的
平方和） 79
SUMX2MY2（求两个数组中对应数值的平方
差之和） 79
PRODUCT（求指定的多个数值的乘积） 80
MOD（求两个数值相除后的余数） 80
MROUND（求按指定基数舍入后的数值） 81
GCD（求两个或多个整数的最大公约数） 82
LCM（计算两个或多个整数的最小公倍数） 82
SQRT（求数据的算术平方根） 83
SQRTPI（计算某数与π的乘积的平方根） 83
4．2数据的舍入 83

INT（将数字向下舍入到最接近的整数） 83
CEILING（向上舍入为最接近指定数值的
倍数） 84
CEILING．PRECISE（向上舍入数字） 84
FLOOR（向下舍入到最接近指定数值的
倍数） 85
FLOOR．PRECISE（向下舍入数字） 86
ROUND（对数据进行四舍五入） 86
ROUNDUP（远离零值向上舍入计算） 88
ROUNDDOUWN（靠近零值向下舍入数值） 89
EVEN（将数字向上舍入到最接近的偶数） 90
ODD（将数字向上舍入到最接近的奇数） 90
QUOTIENT（返回商的整数部分） 91
TRUNC（将数字的小数部分截去返回整数） 91

4．3阶乘、随机与矩阵计算 92

FACT（计算指定正数值的阶乘） 92
FACTDOUBLE（返回数字的双倍阶乘） 92
MULTINOMIAL（返回参数和的阶乘与各参
数阶乘乘积的比值） 93
RAND（返回大于等于0小于1的随机数） 93
RANDBETWEEN（返回指定数值之间的随
机数） 93
MDETERM（返回一个数组的矩阵行列式
的值） 94
MINVERSE（返回数组矩阵的逆矩阵） 94
MMULT（返回两个数组的矩阵乘积） 95

4．4指数、对数与幂计算 95

EXP（返回e为底数指定指数的幂值） 95
LN（返回一个数的自然对数） 96
LOG（计算数值的对数值） 96
LOG10（计算以10为底数的对数值） 96
SERIESSUM(返回基于数字的幂级数之和） 97
POWER（返回给定数字的乘幂） 97

4．5三角函数计算 98

SIN（返回某一角度的正弦值） 98
COS（返回某一角度的余弦值） 98
TAN（返回某一角度的正切值） 99
SINH（返回给定数字的双曲正弦值） 99
ASINH（返回给定数字的反双曲正弦值） 99
ACOS（返回给定数字的反余弦值） 100
ATAN（返回给定数字的反正切值） 100
ATAN2（返回直角坐标系中给定X及Y的反正
切值） 101
SIGH（返回数字的符号） 101
COSH（返回任意实数的双曲余弦值） 101
TANH（返回任意实数的双曲正切值） 102
ACOSH（返回指定数值的反双曲余弦值） 102
ATANH（返回指定数值的反双曲正切值） 103
PI（返回圆周率π） 103
RADIANS（将角度转换为弧度） 103
DEGREES（将弧度转换为角度） 104

4．6其他函数 98

ROMAN（将任意阿拉伯数字转换为罗马
数字） 104
5．1返回当前日期和时间 105

NOW（返回当前日期和时间） 105
TODAY（返回当前日期） 106

5．2日期和时间处理 107

DATE（返回日期的序列号） 107
YEAR（返回指定日期对应的年份） 109
MONTH（返回指定日期中的月份） 110
DAY（返回指定日期中的天数） 111
WEEKDAY（返回指定日期对应的星期数） 113
EDATE（计算出与指定日期相隔指定
月份数的日期） 114
EOMONTH（根据给定日期返回此月最后
一天的日期） 114
WORKDAY（根据给定日期返回与此日期
间隔指定工作日的日期） 115
WEEKNUM（返回给定日期是一年中的第
几周） 116
HOUR（返回时间值的小时数） 116
MINUTE（返回时间值的分钟数） 118
SECOND（返回时间值的秒数） 119
TIME（返回某一特定时间的小数值） 120

5．3日期差值计算 121

DATEDIF（用指定的单位计算起始日和
结束日之间的天数） 121
DAYS360（按照一天360天的算法计算
两日期间相差的天数） 123
NETWORKDAYS（计算某时段的工作日
天数） 123
YEARFRAC（返回两个日期之间的天数
占全年天数的比例） 124

5．4文本与日期、时间格式转换 125

DATEVALUE（将文本日期转换为可识别
的日期序列号） 125
TIMEVALUE（将时间转换为对应的小
数值） 126
6．1查找字符串并返回所在位置 127

LEFT（按指定字符数从最左侧提取字
符串） 127
LEFTB（按指定字节数从最左侧提取字
符串） 130
MID（从任意位置提取指定字符数的字
符串） 130
MIDB（从任意位置提取指定字节数的
字符） 131
FIND（查找指定字符在一个字符串中的
位置） 131
FINDB（查找指定字符在一个字符串中
的位置“按字节算”） 133
RIGHT（按指定字符数从最右侧提取字
符串） 133
RIGHTB（按指定字节数从最右侧提取字
符串） 134
SEARCH（查找字符串字符起始位置） 134
SEARCHB（查找字符串字符起始位置
“按字节算”） 135

6．2文本格式的转换 136

TEXT（设置数字格式，并将其转换为文本） 136
DOLLAR（四舍五入数值，并添加千分位
符号和$符号） 138
RMB（四舍五入数值，并添加千分位
符号和￥符号） 138
FIXED（将数字按指定的位数取整并以
文本形式返回） 139
UPPER（将文本转换为大写形式） 139
LOWER（将文本转换为小写形式） 140
PROPER（将文本字符串的首字母转换成
大写） 140
CODE（返回文本字符串中第一个字符的
数字代码） 141
CHAR（返回对应于数字代码的字符） 141
VALUE（将文本字转换成数值） 141
T（将给定内容转换为文本） 142

6．3文本的其他操作 142

CONCATENATE（合并两个或多个文本字
符串） 142
LEN（返回文本字符串的字符数量） 144
LENB（返回文本字符串的字节数量） 146
REPLACE（用指定的字符和字符数替换
文本字符串中的部分文本） 146
REPLACEB（用指定的字符和字节数替换
文本字符串中的部分文本） 148
SUBSTITUTE（替换旧文本） 148
EXACT（比较两个文本字符串是否完全
相同） 151
REPT（按照给定的次数重复显示文本） 151
TRIM（删除文本中的多余空格） 152
CLEAN（删除文本中不能打印的字符） 152
7．1查找数据 154

CHOOSE（从给定参数列表中选择值） 154
HLOOKUP（查找数组的首行，并返回
指定单元格的值） 156
LOOKUP（从单行或单列区域返回值-
向量型） 157
LOOKUP（从单行或单列区域返回值-
数组型） 160
VLOOKUP（在给定单元格区域首列查找
并返回该区域上同行的上值） 161
MATCH（返回指定值在指定区域中的位置） 164
INDEX（使用索引从引用或数组中选择值） 165
GETPIVOTDATA（返回存储在数据透视表
中的数据） 169
7．2引用数据 171

AREAS（返回引用中涉及的区域个数） 171
ADDRESS（建立文本类型的单元格
地址） 171
COLUMN（返回引用的列号） 172
COLUMNS（返回引用中包含的列数） 173
ROW（返回引用的行号） 174
ROWS（返回引用中包含的行数） 176
INDIRECT（返回由文本字符串指定的引用） 176
OFFSET（按指定引用与偏移量返回新的
引用） 177
TRANSPOSE（返回转置单元格区域） 179
HYPERLINK（创建一个可以打开存在于
网络服务器中的文件的快捷方式） 179
8．1返回信息与类型值 181

CELL（返回有关单元格格式、位置或内容的
信息） 181
INFO（返回当前操作环境的信息） 182
TYPE（返回单元格内的数值类型） 183
ERROR．TYPE（返回与错误值对应的数字） 184
N（将参数转换为数值） 184
NA（返回错误值 #N/A） 185

8．2使用IS函数进行判断 186

ISBLANK（如果值为空，则返回TRUE） 186
ISNUMBER（如果值为数字，则返回
TRUE） 188
ISTEXT（如果值为文本，则返回TRUE） 190
ISEVEN（如果数字为偶数，则返回TRUE） 191
ISODD（如果数字为奇数，则返回TRUE） 192
ISERROR（如果值为任何错误值，则返回
TRUE） 193
ISERR（判断值是否为除#N/A之外的其他
任意错误值） 194
ISLOGICAL（检测一个值是否为逻辑值） 196
ISNA（检测一个值是否为#N/A错误值） 196
ISNONTEXT（检测一个值是否不是文本） 197
ISREF（检测一个值是否为引用） 198
9．1平均值计算 199

AVERAGE（计算算术平均值） 199
AVERAGEA（返回平均值） 202
AVERAGEIF（返回满足条件的平均值） 202
AVERAGEIFS（返回满足多重条件的平
均值） 204
GEOMEAN（返回几何平均值） 206
HARMEAN（返回数据集的调和平均值） 206
TRIMMEAN（去除数据集头尾求平均值） 207

9．2数目统计函数 207

COUNT（统计单元格区域中数字的个数） 207
COUNTA（返回单元格区域中任意值的
个数） 210
COUNTIF（统计满足给定条件的单元格
个数） 211
COUNTIFS（统计满足多条件的单元格
个数） 212
COUNTBLANK（统计空白单元格的个数） 214

9．3最大值与最小值函数 214

MIN（返回数据集的最小值） 214
MAX（返回数据集的最大值） 216
MAXA（参数含文本与逻辑值时返回最
大值） 218
MINA（参数含文本与逻辑值时返回最小值） 219
LARGE（返回数据集中某个最大值） 219
SMALL（返回数据集的某个最小值） 220

9．4排位统计函数 221

MEDIAN（返回中位数） 221
PERCENTILE．INC（返回第k个百分点值） 222
PERCENTRANK．INC（返回百分比排位） 222
QUARTILE．INC（返回四分位数） 223
PERMUT（返回排列数） 224
RANK．EQ（返回一个数字在数字列表中的
排位） 224
RANK．AVG（返回一个数字在数字列表中的
排位） 225
PERCENTRANK．EXC（返回数值在一个
数据集中的百分比） 226

9．5方差、协方差与偏差 226

COVARIANCE．P（返回总体协方差） 226
COVARIANCE．S（返回样本协方差） 227
DEVSQ（返回平均值偏差的平方和） 227
STDEV．S（计算基于样本估算标准偏差） 228
STDEVA（计算基于给定样本的标准偏差） 228
STDEVPA（计算样本总体的标准偏差） 229
VARA（估算给定样本的方差） 229
VARPA（计算样本总体的方差） 229
VAR．S（估算基于样本的方差） 230
VAR．P（计算基于整个样本总体的方差） 230

9．6数据检验 231

CHISQ．TEST（返回独立性检验值） 231
F．TEST（返回F检验的结果） 231
FREQUENCY（计算数值在某个区域内的
出现频率） 231

9．7回归分析 233

FORECAST（根据已有的数值计算或预测
未来值） 233
GROWTH（对给定的数据预测指数增长值） 234
LINEST（返回描述直线拟合的数组） 234
INTERCEPT（计算直线与y轴的截距） 235
LOGEST（回归拟合曲线返回该曲线的数值） 235
SLOPE（求拟合的线性回归直线的斜率） 236
STEYX（返回预测值时产生的标准误差） 236
TREND（返回一条线性回归拟合线的值） 237

9．8相关系数 237

CORREL（返回两个不同事物之间的相
关系数） 237
PEARSON（返回Pearson乘积矩相关系数） 238
RSQ（返回皮尔生乘积矩相关系数的平方） 238

9．9概率分布 239

BETA．DIST（返回beta分布） 239
BINOM．INV（返回二项式分布概率） 239
BETA．INV（返回Beta累积概率密度函数的反
函数） 240
BINOM．DIST（返回一元二项式分布的概率） 240
CHISQ．DIST．RT（返回X2分布的单尾概率） 240
CHISQ．INV（返回X2分布的左尾概率的
反函数） 241
CHISQ．INV．RT（返回X2分布的右尾概率的
反函数） 241
EXPON．DIST（返回指数分布） 241
F．DIST（返回F概率分布函数值） 242
F．DIST．RT（返回F概率分布） 242
F．INV（返回F概率分布函数的反函数） 243
F．INV．RT（返回F概率分布的反函数值） 243
GAMMA．DIST（返回伽马分布函数的
函数值） 243
GAMMA．INV（返回伽马累积分布函数的
反函数值） 244
GAMMALN（返回伽马函数的自然对数） 244
HYPGEOM．DIST（返回超几何分布） 245
KURT（返回数据集的峰值） 245
LOGNORM．INV（返回x的对数累积分布
函数的反函数） 245
LOGNORM．DIST（返回x的对数累积分布
函数） 246
MODE．MULT（返回数据区域中出现频率最高的数值数组） 246
MODE．SNGL（返回数组中出现频率最多的
数值） 247
NEGBINOM．DIST（返回负二项式分布） 247
NORM．S．DIST（返回标准正态分布的累积
函数） 248
NORM．INV（返回正态累积分布的
反函数） 248
NORM．DIST（返回指定平均值和标准偏差的
正态分布函数） 248
NORM．S．INV（返回标准正态累积分布函数的
反函数） 249
PROB（返回数值落在指定区间内的概率） 249
POISSON．DIST（返回泊松分布） 250
SKEW（返回分布的偏斜度） 250
STDEV．P（返回整个样本总体计算标准
偏差） 251
T．DIST（返回学生的左尾t分布） 251
T．DIST．2T（返回学生的双尾t分布） 252
T．DIST．RT（返回学生的右尾t分布） 252
T．INV．2T（返回学生t分布的双尾反函数） 252
T．INV（返回学生t分布的左尾反函数） 253
T．TEST（返回与学生t检验相关的概率） 253
WEIBULL．DIST（返回韦伯分布） 253
Z．TEST（返回z检验的单尾P值） 254

9．10其他统计函数 255

CONFIDENCE．NORM（使用正态分布返回
总体平均值的置信区间） 255
CONFIDENCE．T（使用学生的t分布返回
总体平均值的置信区间） 255
10．1投资计算函数 257

PMT（计算贷款的每期付款额） 257
PPMT（返回给定期间内的本金额） 258
IPMT（返回给定期限内的利息额） 259
ISPMT（计算投资期内的利息额） 260
FV（返回某项投资的未来值） 260
FVSCHEDULE（计算某项投资在变动或
可调利率下的未来值） 261
PV（返回投资的现值） 262
NPV（返回一项投资的净现值） 262
XNPV（返回一组不定期现金流的净现值） 263
NPER（返回某项投资的总期数） 263

10．2偿还率计算函数 264

IRR（计算内部收益率） 264
MIRR（计算修正内部收益率） 264
XIRR（计算内部收益率） 265
RATE（返回年金的各期利率） 265

10．3资产折旧计算函数 266

AMORDEGRC（计算会计期间的折旧值） 266
AMORLINC（返回每个会计期间的折旧值） 267
DB（固定余额递减法计算折旧值） 267
DDB（双倍余额递减法计算折旧值） 268
VDB（双倍余额递减法计算指定期间
折旧值） 269
SLN（返回线性折旧值） 270
SYD（年限总和法计算折旧值） 271

10．4转换美元价格的格式 272

DOLLARDE（将价格转换为小数表示） 272
DOLLARFR（将价格转换为分数表示） 272

10．5证券与国券 273

ACCRINT（返回定期付息有价证券的利息） 273
ACCRINTM（返回到期一次性付息有价
证券的利息） 273
CUMPRINC（返回贷款在给定的两个期间
累计偿还的本金数额） 274
CUMIPMT（返回贷款在给定的两个期间
累计偿还的利息数额） 274
COUPDAYBS（返回付息期到成交日的
天数） 275
COUPDAYS（返回成交日所在付息期天数） 275
COUPDAYSNC（返回从成交日到下一付息日
之间的天数） 276
COUPNCD（返回一个表示在成交日之后
下一个付息日） 276
COUPNUM（返回成交日和到期日之间的
利息应付次数） 277
COUPPCD（返回成交日之前的上一付息日
日期） 277
DISC（返回贴现率） 278
DURATION（返回有价证券的修正期限） 278
EFFECT（计算实际年利率） 279
NOMINAL（返回名义年利率） 279
INTRATE（返回一次性付息证券的利率） 279
MDURATION（返回有价证券的Macauley修正
期限） 280
ODDFPRICE（返回首期付息日不固定的
有价证券的价格） 280
ODDFYIELD（返回首期付息日不固定的
有价证券的收益率） 281
ODDLPRICE（返回末期付息日不固定的
有价证券的价格） 282
ODDLYIELD（返回末期付息日不固定的
有价证券的收益率） 282
PRICE（返回定期付息有价证券的价格） 283
PRICEDISC（返回折价发行有价证券的
价格） 283
PRICEMAT（返回到期付息有价证券的
价格） 284
RECEIVED（返回有价证券到期收回的
金额） 284
TBILLEQ（返回国库券的等效收益率） 285
TBILLYIELD（返回国库券的收益率） 285
TBILLPRICE（返回面值$100的国库券的
价格） 286
YIELD（返回定期付息有价证券的收益率） 286
YIELDDISC（返回折价发行的有价证券的
年收益率） 287
YIELDMAT（返回到期付息的有价证券的
年收益率） 287
11．1常规数据统计 288

DSUM（返回满足条件的数字之和） 288
DAVERAGE（对满足条件的数据求平均值） 291
DPRODUCT（对满足条件数值求乘积） 293
DGET（提取符合指定条件的单个值） 293
DMIN（返回满足条件的最小值） 294
DMAX（返回满足条件的最大值） 296
DCOUNT（统计满足条件的单元格个数） 297
DCOUNTA（统计非空单元格的个数） 299
11．2散布度统计 301

DSTDEV（返回满足条件的数字作为一个
样本估算出的总体标准偏差） 301
DSTDEVP（返回满足条件的数字作为样本
总体计算出的总体标准偏差） 301
DVAR（返回满足条件的数字作为一个
样本估算出的总体方差） 302
DVARP（返回满足条件的数字作为样本
总体计算出的样本总体方差） 303