秒懂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