Excel函数与公式实战秘技250招

本书通过250个实战秘技，讲解Excel函数与公式实战技巧。
全书共12章，分别介绍公式与函数基础、名称的定义与使用、文本函数、日期与时间函数、逻辑函数、数学和三角函数、查找与引用函数、统计函数、信息函数、财务函数、其他函数以及公式与函数常见问题等内容。
本书结构合理，图文并茂，既适合各行业工作人员使用，也适合经常从事数据统计与分析的管理人员使用，同时还适合学习Excel公式与函数的初学者使用。

李杏林，长沙中青培训机构老师，从事Office教学多年，培养了大量很好的学员，撰写《Excel 2010电子表格制作完美互动手册》，同时参与编写《CorelDRAW X5平面广告设计经典108例》、《淘 宝新手店铺装修一本通》等图书。对淘 宝营销、微信营销、财务方面的知识有深入了解。

第1章公式与函数基础1
招式001公式中的文本连接运算符的使用2
招式002编辑与修改公式3
招式003快速复制公式的多种方法5
招式004查看工作表中的所有公式6
招式005函数也来玩“搜索”7
招式006快速获取函数的帮助信息8
招式007Excel数据源的相对引用9
招式008Excel数据源的绝对引用9
招式009在相对引用和绝对引用之间切换10
招式010引用当前工作表之外的单元格12
招式011跨工作簿引用数据13
招式012引用多个工作表中的同一单元格运算15
招式013公式中函数的记忆式键入16
招式014自动求和功能的使用17
招式015快速选中公式中引用的单元格18
招式016将公式运算结果转换为数值19
招式017函数参数的修改20
招式018函数的嵌套方法21
招式019如何在单元格中自动填入当前日期22
招式020隐藏保护工作表中的公式22
招式021正确区分空文本与空单元格24
招式022为何数据无法进行计算25
招式023按F9键查看公式运算结果26
招式024跳过非空单元格批量建立公式27
招式025公式的监视28
招式026公式错误检查30
招式027逐步分解公式31
招式028普通公式与数组公式32
第2章名称的定义与使用33
招式029定义名称的规则34
招式030使用名称的好处34
招式031使用“定义名称”功能按钮36
招式032使用名称框创建名称36
招式033根据所选内容批量创建名称37
招式034名称应用于公式计算38
招式035定义动态引用的名称39
招式036使用名称管理器筛选名称41
招式037修改与编辑名称42
招式038导出名称列表43
招式039删除名称44
招式040为Excel公式定义名称45
招式041让定义的名称只应用于当前工作表47
第3章文本函数49
招式042CONCATENATE——快速自动生成订单编号50
招式043CONCATENATE——合并面试人员的总分数与录取情况50
招式044FIND——搜索指定字符在字符串中的位置51
招式045LEN——从员工身份证号码中提取出生年份52
招式046LEN——从身份证号码中提取性别53
招式047MID——从身份证号码中提取完整的出生日期54
招式048LEFT——根据编码返回部门名称55
招式049LEFT——从完整地址中提取省份名56
招式050LEFT——根据商品名称实现一次性调价56
招式051LEN——验证身份证号码的位数57
招式052TEXT——让计算的金额显示为“余款:12，25000”的形式59
招式053LEFT——从E-mail地址中提取账号59
招式054REPT——根据销售额用“★”评定等级60
招式055RIGHT——将电话号码的区号与号码隔开61
招式056EXACT——比较两个门店的平均售价是否相同62
招式057PROPER——一次性将每个单词的首字母转换为大写63
招式058REPLACE——屏蔽手机号码的后几位数64
招式059LEFT——快速生成对客户的称呼65
招式060ASC——将全角(双字节)字符更改为半角(单字节)字符66
招式061WIDECHAR——将半角(单字节)字符转换为全角(双字节)字符66
招式062BAHTTEXT——将数字转换为β(铢)货币格式文本67
招式063DOLLAR——将数字转换为带美元符号$的文本68
招式064RMB——将数字转换为带人民币符号￥的文本69
招式065CHAR——返回数字对应的字符代码70
招式066CODE——返回字符代码对应的数字70
招式067UPPER——将文本转换为大写字母71
招式068LOWER——将文本转换为小写字母72
招式069VALUE——将文本型数字转换为可计算的数值数据73
招式070TEXT——将“20111001”格式转换为“2011-10-01”格式74
招式071TEXT——数据合并时避免0被省略75
招式072SEARCH——从项目编码中提取合同号76
招式073TRIM——删除文本中多余的空格77
招式074SUBSTITUTE——替换文本字符串中的特定文本78
招式075SUBSTITUTE——删除文本中的所有空格79
招式076CLEAN——删除文本中所有无法打印的字符80
第4章日期与时间函数81
招式077DAYS360——计算总借款的天数82
招式078DATEDIF——计算两个日期相差的年月日数83
招式079DATEDIF——为员工自动追加工龄工资84
招式080DATEVALUE——计算借款的还款天数85
招式081EDATE——计算食品的过期日期86
招式082DATE——确定明年父亲节的日期86
招式083DAY——指定日期的上一月份的天数87
招式084DATEDIF——计算2010年到2014年共有多少天88
招式085EOMONTH——返回员工结算工资的日期89
招式086HOUR——计算出员工缺勤的小时数91
招式087MINUTE——计算停车时间92
招式088MONTH——自动填写报表中的月份93
招式089NETWORKDAYS——计算年假占全年工作日的百分比94
招式090NOW——返回当前系统日期和时间96
招式091SECOND——计算通话秒数97
招式092TIMEVALUE——将文本格式的时间转换为序列数98
招式093TIME——安排会议时间98
招式094TODAY——返回系统当前日期99
招式095TODAY——计算劳动节倒计时的天数100
招式096WEEKDAY——自动返回各日期所对应的星期数101
招式097WEEKDAY——汇总12月份星期六的收入金额102
招式098WEEKNUM——返回指定日期在一年中是第几周104
招式099WORKDAY——根据休假天数返回休假结束日期104
招式100YEARFRAC——计算连续日期之间天数占全年天数的百分比105
招式101YEAR——计算今天到年末时还有多少天106
第5章逻辑函数109
招式102AND——判断员工的销售额是否达标110
招式103FALSE——返回逻辑值FALSE111
招式104IFERROR——根据公式结果返回不同内容111
招式105IF——根据销售额计算提成额112
招式106IF——评定员工的考核等级113
招式107IF——给持普通积分卡和VIP卡的
客户派发赠品114
招式108IF——计算个人所得税115
招式109NOT——筛选掉年龄在25岁以下的应聘人员117
第6章数学和三角函数123
招式110OR——对员工的考核成绩进行综合评定118
招式111OR——根据年龄判断职工是否退休118
招式112OR——判断未来年份是闰年还是平年119
招式113TRUE——判断员工的身份证号码位数是否正确121
招式114ABS——快速计算相应数字的绝对值124
招式115ABS——比较学生上月和本月的成绩进退情况124
招式116ACOS——求指定角度的反余弦值125
招式117ASIN——求指定角度的反正弦值126
招式118ATAN——求指定角度的反正切值126
招式119CEILING——以5秒(不足5秒按5秒计算)为单位计算通话费用127
招式120COS——求指定角度的余弦值128
招式121DEGREES——将弧度转换为角度129
招式122EXP——计算e的n次方129
招式123FLOOR——对数据进行向下舍取求值130
招式124INT——对平均销量取整131
招式125LN——计算某个数值的自然对数132
招式126LOG10——计算指定正数值以10为底数的对数值133
招式127LOG——计算指定正数值和底数的对数值134
招式128MOD——计算通话剩余秒数134
招式129MOD——按奇偶行汇总数据135
招式130PI——将指定角度转换为弧度值136
招式131POWER——计算数值的乘幂137
招式132RADIANS——将角度转换为弧度137
招式133RANDBETWEEN——随机生成两个指定数之间的整数138
招式134RAND——快速生成随机数字139
招式135RAND——自动生成彩票7位开奖号码140
招式136ROUNDDOWN——根据小数位数进行向下舍入计算140
招式137ROUNDUP——按小数位数进行向上舍入计算141
招式138ROUNDOWN——界定时间的整点范围142
招式139ROUNDUP——计算物品的快递费用143
招式140ROUND——对数据进行四舍五入144
招式141SIN——求指定角度的正弦值145
招式142SQRT——计算数值对应的正平方根146
招式143SUBTOTAL——在工资统计表中得出汇总方式的结果147
招式144SUM——快速计算总销售金额149
招式145SUM——统计营销部男员工人数149
招式146SUMIF——统计各部门工资总额150
招式147SUMIF——统计10月份前半个月与后半个月的销售额152
招式148SUMIF——计算同一类产品的总销售额153
招式149SUMIFS——按多个条件对指定
单元格求和154
招式150SUMPRODUCT——统计出指定部门、指定职务的员工数156
招式151SUMSQ——计算所有参数的平方和157
招式152TRUNC——计算销售金额时取整或保留指定位数小数158
第7章查找与引用函数161
招式153HLOOKUP——根据不同的返利率计算各笔订单的返利金额162
招式154HLOOKUP——计算每位员工的销售提成率164
招式155LOOKUP——返回销售笔数最多的员工姓名165
招式156VLOOKUP——根据多条件计算员工年终奖167
招式157VLOOKUP——查询指定员工的销售数据168
招式158INDEX——查询总金额最高所对应的门店169
招式159INDEX——查询最高总分对应的学号171
招式160CHOOSE——判断员工考试成绩是否合格172
招式161MATCH——返回销售额最高的员工姓名174
招式162COLUMNS——返回数据区域包含的列数175
招式163ADDRESS——返回指定行号和列号所对应的单元格地址176
招式164COLUMN——返回数据区域首列的列号177
招式165ROW——填充一年中所有月的月份名178
招式166ROWS——判断值班人员是否重复180
招式167OFFSET——实现数据的动态查询181
招式168HYPERLINK——为指定内容创建超链接183
招式169TRANSPOSE——数据区域的行列转置184
第8章统计函数185
招式170AVERAGE——计算某件商品的平均售价186
招式171AVERAGEIFS——忽略0值求指定部门的平均销售额187
招式172AVERAGEA——统计各月份的平均销售额(计算区域含文本值)188
招式173AVERAGEIF——计算满足条件的数据的平均值189
招式174MAX——计算单日销售金额并返回最大值191
招式175MAXA——返回数据区域中非空值的最大值192
招式176MIN——返回上半个月单笔最低销售金额192
招式177MINA——返回数据区域中非空值的最小值194
招式178RANKEQ——对员工销售业绩排名次194
招式179COUNTIF——统计员工试用期到期的人数195
招式180COUNTIF——统计销售额大于1000的员工人数196
招式181COUNTIF——统计重复的员工人数197
招式182COUNTIFS——计算满足多个条件的单元格数198
招式183COUNT——统计有销售记录的条数200
招式184COUNTA——统计包含文本值的单元格数202
招式185COUNTBLANK——统计空白单元格的个数203
招式186LARGE——统计数据表中前3名的平均值204
招式187SMALL——统计数据表中后3名的平均值206
招式188RANKAVG——解决出现相同名次时默认的排名问题207
第9章信息函数209
招式189CELL——获取当前工作簿和工作表的名称210
招式190ISTEXT——检验所给的数据是否为文本211
招式191ISBLANK——检验指定值是否为空212
招式192ISNONTEXT——检验数据是否为非文本214
招式193ISLOGICAL——判断指定数据是否为逻辑值214
招式194ISNUMBER——判断所给数据是否为数字215
招式195ISNA——判断值是否为“#N\A”错误值217
招式196ISERR——统计公司各部门的人数217
招式197ISODD——判断数字是否为奇数218
招式198ISEVEN——判断数字是否为偶数220
招式199ISEVEN——根据身份证号判断其性别220
招式200ERRORTYPE——返回错误值所对应错误类型的数字221
招式201N——建立产品的订单编码222
招式202TYPE——返回数值对应的表示数据类型的数字223
第10章财务函数225
招式203FV——计算分期存款的未来值226
招式204FV——计算住房公积金的未来值226
招式205RATE——计算购买某项保险的收益率227
招式206PV——判断某笔贷款是否合算228
招式207PMT——计算贷款的每年付款额229
招式208PPMT——计算贷款在给定期间内需偿还的本金229
招式209IPMT——计算贷款在给定期间内需支付的利息230
招式210ISPMT——计算普通(无担保)贷款需偿还的利息232
招式211FVSCHEDULE——计算某项借款的未来回收金额232
招式212NPV——计算某投资的净现值233
招式213XNPV——计算出一组不定期盈利额的净现值234
招式214NPER——计算出某项投资的期数235
招式215IRR——计算某项投资的内部收益率236
招式216XIRR——计算一组不定期发生现金流的内部收益率236
招式217MIRR——计算正负现金流在不同利率下支付的内部收益率237
招式218SLN——用直线法计算出固定资产的每年折旧额238
招式219SYD——用年数总和法计算出固定资产的每年折旧额239
招式220DB——用固定余额递减法计算出固定资产的每年折旧额240
招式221DDB——用双倍余额递减法计算出固定资产的每年折旧额241
招式222VDB——计算出固定资产部分期间的设备折旧值242
招式223AMORDEGRC——计算每个结算期间的折旧值243
招式224DOLLARDE——将按分数表示的价格转换为按小数表示的价格244
招式225DOLLARFR——将按小数表示的价格转换为按分数表示的价格245
第11章其他函数247
招式226DELTA——测试两个数值是否相等248
招式227GESTEP——测试某值是否大于阈值248
招式228BIN2OCT——将任意二进制编码转换为八进制编码249
招式229BIN2DEC——将任意二进制编码转换为十进制编码250
招式230OCT2BIN——将任意八进制编码转换为二进制编码251
招式231DEC2BIN——将任意十进制编码转换为二进制编码252
招式232BIN2HEX——将任意二进制编码转换为十六进制编码252
招式233ERF——返回误差253
招式234DPRODUCT——判断指定产品是否被维修过254
招式235DSUM——返回列表或数据库中满足指定条件的记录字段(列)中的数字之和255
招式236DAVERAGE——计算指定车间指定性别员工的平均工资257
招式237DCOUNT——统计出指定性别工资大于2500元的人数258
第12章公式与函数常见问题259
招式238VLOOKUP函数查找时出现错误值的几个原因260
招式239完成合并计算后，原表数据更新后的结果不能同步更新261
招式240新输入的行中不能自动填充上一行的公式262
招式241在做日常报表时，怎样才能让月累计数自动加上263
招式242完成计算后，结果单元格中为什么始终显示公式264
招式243“#####”错误及解决方法264
招式244“#DIV/0!”错误及解决方法265
招式245“#N/A”错误及解决方法265
招式246“#NAME?”错误及解决方法266
招式247“#NULL!”错误及解决方法268
招式248“#NUM!”错误及解决方法268
招式249“#REF!”错误及解决方法269
招式250“#VALUE!”错误及解决方法269