秒懂EXCEL函数应用技巧

第1章　与Excel公式相约
1.1　为何要学Excel公式 2
1.1.1　提高数据统计效率 2
1.1.2　简化数据分析流程 3
1.1.3　加快数据处理速度 4
1.2　Excel公式快速入门 5
1.2.1　了解Excel公式的基本形态 5
1.2.2　提高公式输入速度的关键 7
1.2.3　在公式中插入函数的方法 8
1.2.4　按具体情况选择填充方式 11
1.2.5　单元格引用方式不能忽视 13
1.2.6　省心的自动计算 15
1.2.7　公式的高阶玩法——数组公式 16
1.3　初学者学函数的正确方法 18
1.3.1　认识函数类型 18
1.3.2　快速了解不熟悉的函数 19
1.3.3　搞懂每个参数的含义 20
1.3.4　适时查看帮助信息 21
1.3.5　解锁嵌套函数的规则 22
1.4　名称让公式更容易理解 23
1.4.1　为区域定义名称 23
1.4.2　常用公式的名称定义 24
1.4.3　名称的应用与管理 25
1.5　解决初学者常见问题 26
1.5.1　为什么公式不自动计算 27
1.5.2　为什么公式返回了错误值 29
1.5.3　单元格中为什么出现绿色小三角 30
1.5.4　公式求和结果为什么是0 31
1.5.5　如何排查公式错误 32
1.5.6　无法退出公式编辑状态怎么办 33
初试锋芒 34

第2章　典型的10种函数必修课
2.1　SUM函数为求和而生 36
2.1.1　SUM函数的基础用法 36
2.1.2　参数的设置原则 37
2.2　IF函数帮助选择正确结果 38
2.2.1　先听懂函数的语言 38
2.2.2　有判断的地方要想到IF 39
2.2.3　循环嵌套进行多次判断 40
2.3　数据查询的明星函数 41
2.3.1　单向查询用LOOKUP函数 41
2.3.2　关键词查询用VLOOKUP函数 43
2.3.3　位置查询用MATCH函数 45
2.3.4　列表索引用CHOOSE函数 47
2.3.5　行列定位坐标用INDEX函数 48
2.4　精确完成日期计算 49
2.4.1　DATE函数告知具体日期 50
2.4.2　日期间隔天数DAYS函数准知道 51
2.5　先确定字符位置再执行后续操作 52
2.5.1　FIND函数确定字符起始位置 52
2.5.2　嵌套函数提取字符 53
初试锋芒 54

第3章　大数据时代的统计分析
3.1　完成平均值计算 56
3.1.1　计算各科平均成绩——AVERAGE函数 56
3.1.2　计算包含缺考同学在内的平均成绩——AVERAGEA函数 57
3.1.3　计算指定班级和指定科目的平均成绩——AVERAGEIF函数 58
3.1.4　计算指定班级所有及格分数的平均分——AVERAGEIFS函数 59
3.2　按条件统计单元格数量 60
3.2.1　统计生产线实际开工天数——COUNT函数 60
3.2.2　统计通过面试的人数——COUNTA函数 61
3.2.3　统计员工正常休息的天数——COUNTBLANK函数 62
3.2.4　统计销售额超过6万元的人数——COUNTIF函数 63
3.2.5　统计指定门店业绩达标的人数——COUNTIFS函数 64
3.3　统计*大值和*小值 65
3.3.1　统计会员单笔消费*高记录——MAX函数 66
3.3.2　从负数和文本值中统计*高增长率——MAXA函数 67
3.3.3　从所有人员中提取*小年龄——MIN函数 68
3.3.4　提取商品*低销售数量——MINA函数 69
3.4　对数据进行排位统计 70
3.4.1　对比赛成绩进行排名——RANK函数 70
3.4.2　提取指定名次的销售业绩——LARGE函数 71
3.4.3　淘汰评分*低的3名成员——SMALL函数 72
3.4.4　计算一周血糖值的中值——MEDIAN函数 73
初试锋芒 75

第4章　一丝不苟的数学运算
4.1　四则运算函数 78
4.1.1　统计商品销售金额——SUM函数 78
4.1.2　统计指定商品的销售总额——SUMIF函数 79
4.1.3　根据多个条件计算指定产品的出库数量——SUMIFS函数 80
4.1.4　求在预算内能购买的商品数量——QUOTIENT函数 81
4.1.5　计算两数相除的余数——MOD函数 82
4.1.6　计算商品折扣价——PRODUCT函数 84
4.1.7　根据单价和数量直接计算总销售额——SUMPRODUCT函数 85
4.1.8　计算同期营业额的相差值——ABS函数 86
4.2　舍入函数 87
4.2.1　舍去金额中的零钱部分——INT函数 87
4.2.2　将平均单价四舍五入保留1位小数——ROUND函数 88
4.2.3　将投资收益取整——TRUNC函数 89
4.2.4　计算车辆租赁计费时长——ROUNDUP函数 91
4.2.5　对食品成分含量进行舍入处理——ROUNDDOWN函数 92
4.2.6　计算业绩提成金额——FLOOR函数 93
4.2.7　计算包装是否达到*大容量——EVEN函数 94
4.2.8　将发货数量补充为奇数——ODD函数 95
4.3　随机数函数 96
4.3.1　随机安排选手入场顺序——RAND函数 96
4.3.2　随机生成6位数密码——RANDBETWEEN函数 97
初试锋芒 99

第5章　明辨是非的逻辑函数
5.1　逻辑判断函数 102
5.1.1　判断员工是否具备晋升资格——AND函数 102
5.1.2　判断考生是否有一项成绩考了满分——OR函数 103
5.1.3　判断面试者性别是否符合要求——NOT函数 104
5.2　直观呈现逻辑判断结果 105
5.2.1　根据综合考评分数自动输入评语——IF函数 105
5.2.2　判断综合评分是否低于平均值——IF函数 106
5.2.3　判断食品添加剂含量是否超标——IF函数 107
5.2.4　根据性别和年龄判断是否为退休人员——IF函数 108
5.2.5　屏蔽公式产生的错误值——IFERROR函数 109
初试锋芒 111

第6章　火眼金睛检索数据
6.1　查找指定的数据 114
6.1.1　根据商品名称查询商品库存——VLOOKUP函数 114
6.1.2　抽取随机奖品——CHOOSE函数 115
6.1.3　根据书名查找在货架上的位置——LOOKUP函数 116
6.1.4　查询指定员工的实际工作天数——HLOOKUP函数 118
6.1.5　查询10天内第几天的产量*高——MATCH函数 120
6.1.6　自动提取员工各项考勤数据——INDEX函数 122
6.2　引用单元格 124
6.2.1　根据给定参数返回单元格地址——ADDRESS函数 124
6.2.2　统计引用的数组个数——AREAS函数 126
6.2.3　解决VLOOKUP函数无法完成的查询任务——OFFSET函数 127
6.2.4　跨表合并员工各月工资——INDIRECT函数 131
6.2.5　自动输入不会打乱顺序的序号——ROW函数 133
6.2.6　统计生产步骤——ROWS函数 134
6.2.7　自动生成工资条——COLUMN函数 135
6.2.8　统计工资构成项的数量——COLUMNS函数 137
初试锋芒 138

第7章　专注时效的日期与时间函数
7.1　返回当前日期和时间 140
7.1.1　计算从当前日期到项目结束剩余天数——TODAY函数 140
7.1.2　计算秒杀活动距离结束的天数——NOW函数 141
7.2　根据给定的参数计算日期和时间 142
7.2.1　根据给定年份和月份返回**天和*后一天日期——DATE函数 142
7.2.2　提取直播结束的具体时间——TIME函数 143
7.2.3　提取出生年份——YEAR函数 144
7.2.4　提取员工出生月份——MONTH函数 146
7.2.5　根据发货日期计算结款日期——DAY函数 146
7.2.6　计算*迟还款日期——EOMONTH函数 148
7.2.7　设置近期还款提醒——EDATE函数 149
7.2.8　计算指定日期是星期几——WEEKDAY函数 150
7.2.9　计算指定日期是当年的第几周——WEEKNUM函数 153
7.2.10　从时间值中提取小时数——HOUR函数 154
7.2.11　计算计时工资——MINUTE函数 155
7.2.12　计算游船租赁总时长——SECOND函数 156
7.3　计算期间差 157
7.3.1　计算项目历时天数——DAYS360函数 157
7.3.2　根据出生日期计算实际年龄——DATEDIF函数 158
7.3.3　计算产品的实际加工天数——NETWORKDAYS函数 159
7.3.4　根据起始日期返回指定工作日之后的日期——WORKDAY函数 160
初试锋芒 162

第8章　精打细算的财务函数
8.1　投资计算函数 164
8.1.1　计算银行存款的本金和利息总额——FV函数 164
8.1.2　计算投资的期值——FVSCHEDULE函数 164
8.1.3　计算贷款每年应偿还的利息——IPMT函数 166
8.1.4　计算等额本金还款时需要支付的利息——ISPMT函数 167
8.1.5　计算指定期限内达到目标存款额时每月存款额——PMT函数 168
8.1.6　计算一笔投资5年后的净现值——NPV函数 169
8.1.7　根据已知条件计算贷款总额——PV函数 170
8.1.8　求存款额达到20万需要几年——NPER函数 171
8.2　折旧计算函数 172
8.2.1　计算资产折旧值——DB函数 173
8.2.2　使用双倍余额递减法计算资产折旧值——DDB函数 174
8.2.3　计算指定时间段的资产的折旧值——VDB函数 175
8.2.4　按年或月计算商务车的线性折旧值——SLN函数 176
8.2.5　计算设备在指定年限时的折旧值——SYD函数 177
8.3　偿还率计算函数 178
8.3.1　计算投资火锅店的收益率——IRR函数 178
8.3.2　计算投资的修正收益率——MIRR函数 179
8.3.3　计算分期投资的年利率——RATE函数 180
8.3.4　计算不定期投资和收入的收益率——XIRR函数 180
初试锋芒 182

第9章　玩转数值格式的文本函数
9.1　查找字符位置与提取文本 184
9.1.1　提取指定字符的位置——FIND函数 184
9.1.2　按字节数量提取目标字符的位置——FINDB函数 185
9.1.3　计算指定文本串的长度——LEN函数 186
9.1.4　忽略大小写查找指定字符的位置——SEARCH函数 187
9.1.5　从地址中提取城市信息——LEFT函数 188
9.1.6　根据字节提取产品规格——LEFTB函数 189
9.1.7　从*后一个字向前提取字符——RIGHT函数 190
9.1.8　利用字节数量提取业主的楼号——RIGHTB函数 192
9.1.9　从字符串的指定位置提取指定数量的字符——MID函数 192
9.2　文本新旧替换 194
9.2.1　替换字符串中指定位置的内容——REPLACE函数 194
9.2.2　替换字符串中的指定内容——SUBSTITUTE函数 196
9.3　文本格式的转换 197
9.3.1　将全角字符转换为半角字符——ASC函数 197
9.3.2　将数字转换为带$符号的货币格式——DOLLAR函数 198
9.3.3　将数字转换为带￥符号的货币格式——RMB函数 198
9.3.4　将文本型数字转换成数值型数字——VALUE函数 200
9.3.5　按照代码将数字转换成指定格式——TEXT函数 201
9.3.6　判断销售盈亏情况——TEXT函数 204
9.3.7　自动返回销售业绩考核等级——TEXT函数 206
9.3.8　为数字添加千位分隔符并四舍五入到指定位数——FIXED函数 207
9.3.9　识别文本字符串——T函数 208
9.3.10　将所有字母转换为大写——UPPER函数 209
9.3.11　将品牌名称的所有英文字母转换为小写——LOWER函数 209
9.3.12　将英文商品名称的每个单词转换为首字母大写——PROPER函数 210
9.4　数据的整理 211
9.4.1　合并品牌和型号获取手机基本信息——CONCATENATE函数 211
9.4.2　删除多余空格只保留一个作为分隔——TRIM函数 212
9.4.3　清除产品规格参数中的换行符——CLEAN函数 213
9.4.4　比较应付款和实付款是否存在差异——EXACT函数 214
9.4.5　用心形图标直观展示商品好评率——REPT函数 215
初试锋芒 217

第10章　Excel中的信息提取专家
10.1　提取文件或操作环境信息 220
10.1.1　提取当前工作表和工作簿信息——CELL函数 220
10.1.2　提取操作系统版本——INFO函数 221
10.2　检查数值属性 223
10.2.1　判断当日销售的各种产品是否有销量——ISNONTEXT函数 224
10.2.2　判断产品检测结果是否合格——ISTEXT函数 225
10.2.3　提取有效的考试成绩——ISNUMBER函数 226
10.2.4　根据尾号判断车辆是否限行——ISEVEN函数 227
10.2.5　根据身份证号码判断性别——ISODD函数 228
10.2.6　判断数据的类型——TYPE函数 229
10.2.7　判断哪些员工无销量——ISBLANK函数 230
10.2.8　判断微量元素的计算结果是否为错误值——ISERROR函数 231
10.2.9　判断查询的商品是否在商品列表中——ISERR/ISNA函数 232
10.2.10　判断员工是否有开单奖金——ISLOGICAL函数 233
初试锋芒 234

第11章　Excel数据分析的秘密
11.1　函数在条件格式中的应用 236
11.1.1　内置条件格式的应用 236
11.1.2　从第2处开始突出显示重复姓名 237
11.1.3　突出显示每门学科的*高成绩 239
11.1.4　将合计销量*小的商品整行突出显示 240
11.1.5　将30天内到期的合同整行突出显示 241
11.1.6　将大于等于90分的成绩标识成“优秀” 242
11.2　函数在数据验证中的应用 243
11.2.1　数据验证的基本应用方法 243
11.2.2　禁止输入重复的工号 244
11.2.3　禁止在单元格中输入空格 245
11.2.4　禁止向指定区域内输入任何内容 246
初试锋芒 247