Excel公式与函数逆引大全PDF电子书下载

第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