第1部分函数基础 1
第1章 函数的基础知识 1
1-1公式 2
1-1-1公式的概述 2
1-1-2公式的输入 4
1-1-3复制公式 6
1-1-4单元格引用 7
1-2函数 9
1-2-1函数的概述 9
1-2-2函数的输入 11
1-2-3函数的修改 15
1-2-4函数的嵌套 15
1-3错误分析 17
1-3-1常见错误信息 17
1-3-2检查与纠正错误 18
1-4数组 20
1-4-1使用数组常量 20
1-4-2使用数组公式 21
第2部分函数应用 23
第2章 数据计算函数的应用 23
2-1计算各商品的总销量【SUM函数】 24
2-2计算指定商品的总销量【SUMIF函数】 26
2-3计算各商品销售额【PRODUCT函数】 28
2-4计算商品的总销售额【SUMPRODUCT函数】 29
2-5计算体力测试结果偏差平方和【SUMSQ函数】 30
2-6计算队员两次短跑测试偏差平方和之和【SUMX2PY2函数】 31
2-7计算队员两次短跑测试偏差平方差之和SUMX2MY2函数】 32
2-8计算两个季度收益额差异平方和【SUMXMY2函数】 33
2-9计算各部门工资总和【SUBTOTAL函数】 34
2-10计算预算内购买商品的数量【QUOTIENT函数】 36
2-11计算预算内购买商品后的余款额【MOD函数】 37
2-12判断人体中性粒细胞是否正常【ABS函数】 38
2-13检测员工的工作量是否完成【SIGN函数】 39
2-14计算三根钢丝截取数及每段最长值【GCD函数】 40
2-15计算300～400之间机器零件的个数【LCM函数】 41
2-16计算e的近似值（使其误差不超过10-5）【SERIESSUM函数】 42
第3章 零数处理函数的应用 43
3-1计算给定金额可购买的数量【INT函数】 44
3-2计算给定长度钢材要截取可用段数【TRUNC函数】 45
3-3计算比赛得分【ROUND函数】 46
3-4计算以角为单位的企业获得利润【ROUNDUP函数】 47
3-5计算以元为单位的销售额【ROUNDDOWN函数】 49
3-6计算订货单位所定商品的箱数【CEILING函数】 51
3-7确定订货数量必须保持一致【FLOOR函数】 53
3-8计算与订货单位保持均衡状态的数量【MROUND函数】 55
3-9计算最接近偶数的房间人数【EVEN函数】 56
3-10计算会议室预约所需座位数【ODD函数】 57
第4章 三角函数的应用58. 59
4-1将角度转换为弧度【RADIANS函数】 59
4-2将弧度转换为角度【DEGREES函数】 61
4-3测量建筑物高度AB的距离【SIN函数】 63
4-4通过山顶铁塔的高度计算山体高度【COs函数】 64
4-5根据汽车行驶情况计算山体高度【TAN函数】 65
4-6根据最短距离计算两条公路形成夹角的度数【ASIN函数】 66
4-7根据两条相交公路的长度计算形成的夹角度数【ACOS函数】 67
4-8根据汽车行驶情况计算东偏南25°的仰角度数【ATAN函数】 68
4-9计算指定坐标的反正切值【ATAN2函数】 69
4-10计算环形跑道的内圈长度【PI函数】 70
第5章 指数与对数函数的应用 71
5-1计算企业裁员后的纯收益额【POWER函数】 72
5-2计算自然对数e的a次幂值【EXP函数】 74
5-3计算A地地震是B地地震震幅的多少倍【LOG函数】 75
5-4计算数值的自然对数【LN函数】 76
5-5根据声源的声音强度计算对应的分贝数【LOG10函数】 77
5-6随机产生一组双色球彩票的选号【RAND函数】 78
5-7随机抽取中奖号码【RANDBETWEEN函数】 79
5-8根据正方体面积计算正方体的边长【SQRT函数】 80
5-9计算圆周率PI的倍数平方根【SQRTPI函数】 81
5-10计算3D游戏旋转坐标矩阵的行列式值【MDETERM函数】 82
5-11计算3D游戏旋转坐标矩阵的逆矩阵【MINVERSE函数】 83
5-12计算两个矩阵的乘积【MMULT函数】 84
5-13计算从7个球中抽取3个球的排列方法种类【FACT函数】 85
5-14计算从7个球中取出3个球的组合数【COMBIN函数】 86
5-15计算指定多项式的系数【MULTINOMIAL函数】 87
5-16将座位次序号转换为罗马数字【ROMAN函数】 88
第6章 基础统计函数的应用 89
6-1计算应考人数【COUNTA函数】 90
6-2计算实考人数【COUNT函数】 91
6-3计算缺考人数【COUNTBLANK函数】 92
6-4计算员工培训成绩在70分以上的人数【COUNTIF函数】 93
6-5获取培训成绩频率分布【FREQUENCY函数】 94
6-6计算员工培训平均成绩【AVERAGE函数】 95
6-7计算实际考试成绩的平均值【AVERAGEA函数】 96
6-8计算员工奖金的内部平均值【TRIMMEAN函数】 97
6-9计算过去3年业绩的平均增长率【GEOMEAN函数】 98
6-10获取员工工资的中值【MEDIAN函数】 99
6-11计算员工的普遍工资【MODE函数】 100
6-12计算从出发地到第3地点的平均速度【HARMEAN函数】 101
6-13获取员工当月最高工资额【MAX函数】 102
6-14获取员工当月最低工资额【MIN函数】 103
6-15获取员工工作能力测试成绩最高分【MAXA函数】 104
6-16获取员工工作能力测试成绩最低分【MINA函数】 105
6-17计算各区域销售额的四分位数【QUARTILE函数】 106
6-18计算上半年销售额的百分位数【PERCENTILE函数】 108
6-19计算自己业绩在销售统计中的排位【PERCENTRANK函数】 109
6-20计算各部门能力测试成绩样本的方差【VAR函数】 110
6-21计算除空白单元格外测试成绩样本的方差【VARA函数】 111
6-22计算电池使用时间基于样本总体的方差【VARP函数】 112
6-23计算除空白单元格外的电池使用时间样本总体的方差【VARPA函数】 113
6-24估算员工智力测试样本的标准偏差【STDEV函数】 114
6-25获取除空白单元格外员工测试成绩样本的标准偏差【STDEVA函数】 115
6-26计算生产量的平均偏差【AVEDEV函数】 116
6-27计算生产量的偏差平方和【DEVSQ函数】 117
6-28根据上半年销量计算偏斜度【SKEW函数】 118
6-29根据各区域销量计算峰值【KURT函数】 119
6-30计算各区域年度销售额的名次【RANK函数】 120
6-31获取年度销售额中第三名的数据【LARGE函数】 121
6-32计算倒数第10名的销售额【SMALL函数】 122
6-33计算参加活动不同人数的排列数【PERMUT函数】 123
第7章 概率分布和检验函数的应用 124
7-1计算棒球员击球成功的概率【BINOMDIST函数】 125
7-2计算抽样产品中允许的不合格产品数【CRITBINOM函数】 126
7-3计算彩票中9和19出现的概率和【PROB函数】 127
7-4计算中标次数达到5次的投标失败率【NEGBINOMDIST函数】 128
7-5计算不合格产品的超几何分布概率【HYPGEOMDIST函数】 129
7-6 计算10天消耗油的概率密度【POISSON函数】 130
7-7计算从贩卖机上购买水花费2分钟以内的概率【EXPONDIST函数】 131
7-8计算螺丝尺寸误差的标准正态分布【NORMDIST函数】 132
7-9依据指定正态分布概率计算尺寸误差值【NORMINV函数】 133
7-10计算生产饮用水容量误差的标准正态分布【NORMSDIST函数】 134
7-11根据标准正态分布概率计算切割地板的误差【NORMSINV函数】 135
7-12计算单价与利润样本数据的正态化数值【STANDARDIZE函数】 136
7-13计算声音强度的对数正态分布函数值【LOGNORMDIST函数】 137
7-14利用韦伯分布计算机器使用寿命WEIBULL函数】 138
7-15计算产品尺寸平均值落入区间范围【CONFIDENCE函数】 139
7-16根据电阻与温度计算温度的概率【CHIDIST函数】 140
7-17根据电阻的X2分布单尾概率计算温度值【CHIINV函数】 141
7-18检验喝酒与脂肪肝的关系【CHITEST函数】 142
7-19检验13岁男孩和女孩身高方差相同时的概率【FTEST函数】 143
7-20使用T检验孩子身高平均值相同的概率【TTEST函数】 144
7-21分析宣传费与收益额的关系【COVAR函数】 145
7-22计算宣传费与收益额关系的相关系数【CORREL函数】 146
7-23利用回归直线分析投资与收益的关系【 SLOPE函数】 147
7-24根据投资额与收益额计算回归直线的截距[INTERCEPT函数】 148
7-25根据成本与利润关系的一元线性回归预测下一年收获利润【LINEST函数】 149
7-26计算企业销售额达到50万元时的纯收入额【FORECAST函数】 151
7-27根据上半年月产量预测下半年的月产量【TREND函数】 152
7-28根据成本与利润快速计算预测标准误差值【STEYX函数】 154
7-29计算成本与利润预测的精确度【RSQ函数】 155
7-30根据现有销售数据预测指数增长值【GROWTH函数】 156
7-31使用非线性回归分析住房面积与估价的关系【LOGEST函数】 158
第8章 日期与时间函数的应用 160
8-1计算到货日期【TODAY函数】 161
8-2记录购货时间【NOW函数】 163
8-3提取商品生产的年份【YEAR函数】 164
8-4提取商品生产月份【MONTH函数】 165
8-5提取销售记录中的销售日【DAY函数】 166
8-6提取列车到达所需时间的小时数【HOUR函数】 168
8-7提取列车到达所需时间的分钟数【MINUTE函数】 169
8-8提取列车到达所需时间的秒数【SECOND函数】 170
8-9计算2010年1月1日是星期几【WEEKDAY函数】 171
8-10计算编写书稿历经周数【WEEKNUM函数】 173
8-11获取出纳日记账中具体日期【DATE函数】 174
8-12显示火车列刻表具体日期与时间【TIME函数】 175
8-13提取现金日记中日期的序列号【DATEVALUE函数】 176
8-14计算汽车时间表中时间的小数值【TIMEVALUE函数】 177
8-15 计算产品保修期的最后时限【EDATE函数】 178
8-16计算产品结款日期【EOMONTH函数】 179
8-17计算产品的预计交货期【WORKDAY函数】 180
8-18计算产品的实际工作日数【NETWORKDAYS函数】 181
8-19提取商品货款剩余支付天数【DAYS360函数】 182
8-20计算货款剩余支付天数的百分比【YEARFRAC函数】 183
8-21计算车辆剩余使用年限【DATEDIF函数】 184
8-22在领料单中填写汉字日期【DATESTRING函数】 185
第9章 查找与引用函数的应用 186
9-1获取产品对应的价格【VLOOKUP函数】 187
9-2获取产品的预计销量 【HLOOKUP函数】 188
9-3根据销售区域编号查找商品销量 【LOOKUP向量形式函数】 189
9-4在数组中根据编号查找销量 【LOOKUP数组形式函数】 191
9-5显示特定人员的工资【INDEX数组形式函数】 192
9-6获取指定位置的销量 【INDEX引用形式函数】 193
9-7计算销售员提取销售佣金【CHOOSE函数】 194
9-8查找销售提取佣金在查找区域的位置【MATCH函数】 195
9-9按照会议室使用的预约表输入人数【ADDRESS函数】 196
9-10查找与区域代号对应的销量 【OFFSET函数】 197
9-11获取指定员工对应的应发工资【INDIRECT函数】 198
9-12通过引用区域计算部门的个数【AREAS函数】 199
9-13获取指定产品所在的行号【ROW函数】 200
9-14获取指定商品所在的列标【COLUMN函数】 201
9-15计算销售记录中的记录条数【ROWS函数】 202
9-16计算员工工资组成的项目数【COLUMNS函数】 203
9-17转置销售表中的行/列重新编制表格【TRANSPOSE函数】 204
9-18为工资表创建超链接以查看数据来源【HYPERLINK函数】 205
第10章 文本函数的应用 206
10-1确认输入的身份证位数【LEN函数】 207
10-2计算身份证号码对应的字节数【LENB函数】 208
10-3只显示信用卡账号指定位数【CONCATENATE函数】 209
10-4提取图书名称【FIND函数】 210
10-5从图书中提取图书编号【FINDB函数】 211
10-6模糊查找员工陈某所在字符位置【SEARCH函数】 212
10-7提取员工陈某的姓所在字节数位置【SEARCHB函数】 214
10-8抽取姓氏制作称呼表【LEFT函数】 215
10-9 截取商品中的品牌名称【LEFTB函数】 216
10-10截取证件编号中部门编号【MID函数】 217
10-11截取商品的型号【MIDB函数】 218
10-12截取员工证件中的员工编号代码【RIGHT函数】 219
10-13截取银行账号的后7位【RIGHTB函数】 220
10-14将电话号码中间4位以“*”代替【REPLACE函数】 221
10-15 替换所有商品的品牌名称【REPLACEB函数】 222
10-16变更部分商品名称【SUBSTITUTE函数】 223
10-17将公司英文名称转换为半角状态【ASC函数】 224
10-18提取并转换网址中的公司名称【WIDECHAR函数】 225
10-19将产品名称中的字母转换为大写【UPPER函数】 226
10-20将公司邮箱地址统一为小写形式【LOWER函数】 227
10-21将员工的英文名更改为首字母大写【PROPER函数】 228
10-22以百分比形式显示业绩占总业绩的比例【TEXT函数】 229
10-23计算产品销售额并以文本格式显示【FIXED函数】 230
10-24以货币格式显示出差费用金额【RMB函数】 231
10-25按现有兑换率将人民币转换为美元【DOLLAR函数】 232
10-26以人民币大写表示结算额【NUMBERSTRING函数】 233
10-27将时间转换为序列号计算出差天数【VALUE函数】 234
10-28根据数值、汉字或字符制作ASCII对应表【CODE函数】 235
10-29在英文姓名间插入分隔圆点“·”【CHAR函数】 236
10-30检测会议签到【EXACT函数】 237
10-31将同一单元格中多行数据合并为一行【CLEAN函数】 238
10--32 清除商品名称中的多余空格【TRIM函数】 239
10-33以☆图形标示员工业绩评比成绩【REPT函数】 240
第11章 基本财务函数的应用 241
11-1计算贷款月偿还额【PMT函数】 242
11-2计算贷款偿还本金额【PPMT函数】 244
11-3计算贷款指定期数的偿还利息【IPMT函数】 245
11-4计算贷款特定期内要支付的利息【ISPMT函数】 246
11-5计算贷款第2年应交纳的总利息额【CUMIPMT函数】 247
11-6计算货款第2年应支付的本金总额【CUMPRINC函数】 248
11-7计算指定贷款额的还款总期数【NPER函数】 249
11-8计算购买债券利息支付次数【COUPNUM函数】 250
11-9计算企业期末支付贷款的年利率【RATE函数】 251
11-10计算购车贷款的实际年利率【EFFECT函数】 252
11-11计算购房贷款的名义年利率【NOMINAL函数】 253
11-12根据年利率和月存款额计算账户总存款额【FV函数】 254
11-13计算投资额在复利计算后期值【FVSCHEDULE函数】 255
第12章 现值和折旧函数的应用 256
12-1计算保险的年金现值【PV函数】 257
12-2计算企业投资的净现值【NPV函数】 258
12-3计算企业不定期发生的现金流的净现值【XNPV函数】 259
12-4计算企业投资的内部收益率【IRR函数】 260
12-5计算企业投资不定期现金流量的内部收益率【XIRR函数】 261
12-6计算企业投资5年后的修正内部收益率【MIRR函数】 262
-12-7用余额递减法计算固定资产的年度折旧费【DB函数】 263
12-8用线性折旧法计算固定资产的年折旧额【SLN函数】 264
12-9用双倍余额递减法计算给定期间内的折旧值【DDB函数】 265
12-10用双倍余额递减法计算指定期间的折旧值【VDB函数】 266
12-11按年限总和折旧法计算第2年资产折旧费【YD函数】 267
12-12计算第一个评估期间的折旧值【AMORDEGRC函数】 268
12-13根据线性折旧法计算第一个期间的折旧值【AMORLINC函数】 269
第13章 证券财务函数的应用 270
13-1计算未来5年期内有价证券的价格【PRICEMAT函数】 271
13-2根据价格计算5年期债券的年收益率【YIELDMAT函数】 272
13-3根据年利率计算票面价值为25000元债券的应计利息【ACCRINTM函数】 273
13-4根据清偿价格计算债券价格【PRICE函数】 274
13-5根据购买价格计算债券的收益率【YIELD函数】 275
13-6根据票息利率计算票面价值为10000元债券的应计利息【ACCRINT函数】 276
13-7计算债券贴现后的价格【PRICEDISC函数】 277
13-8根据购买价格与清偿价格计算债券的贴现率【DISC函数】 278
13-9计算一次性投资10000元债券的利率【INTRATE函数】 279
13-10计算10年期贴现证券的年收益率【YIELDDISC函数】 280
13-11计算投资5万元债券到期回收金额【RECEIVED函数】 281
13-12根据付息方式计算前一个付息日期【COUPPCD函数】 282
13-13计算成交日之后第一个付息日【COUPNCD函数】 283
13-14计算当前付息期内截止到成交日的天数【COUPDAYBS函数】 284
13-15 计算成交日与下一付息日之间的天数【COUPDAYSNC函数】 285
13-16计算成交日所在付息期的天数【COUPDAYS函数】 286
13-17计算首期付息日不固定的3年期债券收益率【ODDFYIELD函数】 287
13-18计算末期付息日不固定的3年期债券收益率【ODDLYIELD函数】 288
13-19计算首期付息日不固定的3年期债券价格【ODDFPRICE函数】 289
13-20计算末期付息日不固定的3年期债券价格【ODDLPRICE函数】 290
13-21根据贴现率计算国库券的价格【TBILLPRICE函数】 291
13-22根据国库券贴现率计算等效收益率【TBILLEQ函数】 292
13-23计算半年期国库券的收益率【TBILLYIELD函数】 293
13-24以小数表示外汇证券的欧元价格【DOLLARDE函数】 294
13-25以分数表示外汇证券的欧元价格【DOLLARFR函数】 295
第14章 信息函数的应用 296
14-1统计食品的销售收入汇总额【CELL函数】 297
14-2根据错误值获取相应的处理方法【ERROR.TYPE函数】 298
14-3获取当前操作系统的版本号【INFO函数】 300
14-4检测一组数据的数值类型【TYPE函数】 301
14-5检测员工联系方式是否填写完整【ISBLANK函数】 302
14-6检测表达式的结果是否为逻辑值【ISLOGICAL函数】 303
14-7检测商品生产数量的数据类型【ISNONTEXT函数】 304
14-8检测销量数据类型并计算销售业绩【ISNUMBER函数】 305
14-9根据购买分红保险年限的奇偶性确定返还金额【ISEVEN函数】 306
14-10根据星期奇偶数填写周工作内容【ISODD函数】 307
14-11检测value参数是否为单元格引用【ISREF函数】 308
14-12检测电话号码数据类型并添加短横线【ISTEXT函数】 309
14-13检测成绩表中是否出现＃N/A以外的错误值【ISERR函数】 310
14-14检测成绩总分计算公式是否出现错误【ISERROR函数】 311
14-15 检测成绩总分计算公式是否出现错误#N/A 【ISNA函数】 312
14-16修正销量数据并计算相应的销售金额【N函数】 313
14-17统计打折商品型号个数与总数量【NA函数】 314
第15章 逻辑函数的应用 315
15-1使用IF函数评定员工业绩是否达标【IF函数】 316
15-2 判断应聘者的身高是否符合条件【AND函数】 318
15-3判断应聘者所会语种是否符合条件【OR函数】 320
15-4获取参与汽车驾照桩考人员名单【NOT函数】 322
15-5统计员工业绩达标人数【TURE函数】 324
15-6统计员工业绩不达标人数【FALSE函数】 325
15-7检测商品成本单价计算公式是否出错【IFERROR函数】 326
第16章 数据库函数的应用 328
16-1统计销售部员工的工资总和【DSUM函数】 329
16-2判断销售部员工有无缺勤者【DPRODUCT函数】 331
16-3计算房屋户型为三室一厅的均价【DAVERAGE函数】 333
16-4计算房屋户型为三室一厅的最高购房价格【DMAX函数】 334
16-5计算房屋户型为三室一厅的最低购房价格【DMIN函数】 335
16-6计算销售部测评成绩在7分以上领取奖金的人数【DCOUNT函数】 336
16-7计算销售部测评成绩在7分或7分以上的有奖金人数【DCOUNTA函数】 338
16-8查找销售部业绩最高的员工姓名【DGET函数】 339
16-9抽样检测同一户产品的品质标准偏差【DSTDEV函数】 341
16-10计算产品日产量的标准偏差【DSTDEVP函数】 342
16-11抽样产品品质测评估算样本总体方差【DVAR函数】 343
16-12计算产品品质测试的方差【DVARP函数】 344
第17章 工程函数的应用 345
17-1实现kg到g的度量转换【CONVERT函数】 346
17-2获取商品二进制条形码【DEC2BIN函数】 348
17-3以八进制数表示商品的条形码【DEC20CT函数】 349
17-4以十六进制数表示商品的条形码【DEC2HEX函数】 350
17-5将商品二进制条形码转换为商品编号【BIN2DEC函数】 351
17-6将二进制条形码转换为八进制条形码【BIN20CT函数】 352
17-7将二进制条形码转换为十六进制条形码【BIN2HEX函数】 353
17-8将十六进制基因代码转换为二进制基因代码【HEX2BIN函数】 354
17-9将十六进制基因代码转换为八进制基因代码【HEX20CT函数】 355
17-10将十六进制基因代码转换为十进制基因代码【HEX2DEC函数】 356
17-11将八进制基因代二码转换为二进制基因代码【OCT2BIN函数】 357
17-12将八进制基因代码转换为十进制基因代码【OCT2DEC函数】 358
17-13将八进制基因代码转换为十六进制基因代码【OCT2HEX函数】 359
17-14核算员工应扣社保费用是否正确【DELTA函数】 360
17-15查找工资高于5000元的员工并统计总人数【GESTEP函数】 362
17-16将两组数据作为实部和虚部合成复数【COMPLEX函数】 363
17-17提取复数的实部系数【IMREAL函数】 364
17-18提取复数的虚部系数【IMAGINARY函数】 365
17-19计算复数的共轭复数【IMCONJUGATE函数】 366
17-20计算复数的绝对值【IMABS函数】 367
17-21计算复数的偏角（以弧度表示）【IMARGUMENT函数】 368
17-22计算多个复数的和【IMSUM函数】 369
17-23计算两个复数的差【IMSUB函数】 370
17-24计算两个复数的积【IMPRODUCT函数】 371
17-25计算两个复数的商【IMDIV函数】 372
17-26 计算复数的平方根【IMSQRT函数】 373
17-27计算复数的指数值【IMEXP函数】 374
17-28计算复数5-2i的5次幂值【IMPOWER函数】 375
17-29计算复数7-5i对应的正弦值【IMSIN函数】 376
1730计算复数7-5i的余弦值【IMCOS函数】 377
17-31计算复数7-5i的自然对数【IMLN函数】 378
17-32计算复数7-5i的常用对数【IMLOG10函数】 379
17-33计算复数7-5i的以2为底的对数【IMLOG2函数】 380
17-34计算5.8的4阶修正BESSEL函数值In（X） 【BESSELI函数】 381
17-35计算5.8的4阶修正BESSEL函数值Jn（X）【BESSELJ函数】 382
17-36计算5.8的4阶修正BESSEL 函数值Kn（X）【BESSELK函数】 383
17-37计算5.8的4阶修正EBESSEL函数值Yn（X） 【BESSELY函数】 384
17-38计算误差函数在2.2～3.5之间的误差值【ERF函数】 385
17-39计算误差函数在2.2～3.5之间的余误差值【ERFC函数】 386
第18章 外部函数与自定义函数的应用 387
18-1将现持有去郎兑换为爱尔兰磅【EUROCONVERT函数】 388
18-2自定义提取电话号码区位号【FUNCTION函数】 390
18-3自定义返回最大数据所在行指定字段值【FOR EACH函数】 392
18-4自定义函数获取指定范围内的最大值【SELECT CASE函数】 394
18-5自定义函数提取商品名称中的汉字【ASC函数】 396
18-6自定义函数隐藏银行账号中间数据【STRING函数】 397
18-7自定义函数检测身份证号码是否合法【LCASE函数】 398
18-8自定义函数根据身份证号码计算年龄【DATEDIFF函数】 399