目录
第1章.创建高效Excel函数公式必备技能..............01
1.1 公式的本质，是如何进行计算的逻辑思维..................................................02
1.1.1 仔细阅读表格，寻找解决思路....................................................................02
1.1.2 梳理逻辑思路，绘制逻辑流程图................................................................04
1.2 必备重要技能，高效输入嵌套函数公式......................................................06
1.2.1.同一个函数嵌套：函数参数对话框+名称框方法..................................07
1.2.2 不同函数嵌套：函数参数对话框+名称框方法.......................................08
1.2.3 复杂的嵌套公式：分解综合法.....................................................................10
1.3 使用名称创建高效、灵活的数据分析公式..................................................13
1.3.1 使用名称简化公式..........................................................................................13
1.3.2 名称应用案例：使用名称制作动态图表....................................................16
1.4 使用数组公式解决复杂计算问题.................................................................17
1.4.1 数组基本知识..................................................................................................18
1.4.2.数组公式基本知识..........................................................................................19
1.4.3 数组公式应用案例：计算前N大数据之和...............................................19
第2章.数据逻辑判断处理案例精讲........................23
2.1 条件表达式应用技能与案例.........................................................................24
2.1.1 使用条件表达式的注意事项........................................................................24
2.1.2.条件表达式的书写规则.................................................................................24
2.1.3 条件表达式应用案例.....................................................................................25
2.2 信息函数及其应用案例................................................................................27
2.2.1 判断单元格状态..............................................................................................27
2.2.2 判断数据类型..................................................................................................28
2.2.3 判断是否为错误值..........................................................................................30
2.2.4 判断数字的奇偶..............................................................................................31
2.2.5 获取工作表信息..............................................................................................33
2.2.6 获取工作簿信息..............................................................................................33
2.2.7 构建自动化的累计数计算公式....................................................................34
2.3 IF函数基本逻辑与应用................................................................................35
2.3.1 IF函数基本原理与逻辑.................................................................................36
2.3.2 IF函数基本应用案例.....................................................................................36
2.4 IF函数嵌套应用...........................................................................................38
2.4.1 绘制逻辑思路图..............................................................................................38
2.4.2 IF函数嵌套应用：单流程..............................................................................39
2.4.3 IF函数嵌套应用：多流程..............................................................................40
2.5 多条件组合判断...........................................................................................42
2.5.1 AND函数应用：多个与条件组合................................................................42
2.5.2 OR函数应用：多个或条件组合...................................................................43
2.5.3 AND函数和OR函数联合应用....................................................................44
2.6 IF函数高级应用...........................................................................................46
2.6.1 使用条件数组进行判断.................................................................................46
2.6.2 使用常量数组进行判断：VLOOKUP函数反向查找................................47
2.6.3 使用常量数组进行判断：多个或条件求和................................................48
2.7 其他逻辑判断函数.......................................................................................49
2.7.1 IFS函数及其应用............................................................................................50
2.7.2 IFERROR函数应用：处理错误值.................................................................51
第3章.文本数据处理案例精讲..............................53
3.1 清理数据中的垃圾.......................................................................................54
3.1.1 TRIM函数：清理文本中的空格...................................................................54
3.1.2 CLEAN函数：清除非打印字符....................................................................54
3.2 统计字符的长度...........................................................................................56
3.2.1 LEN函数和LENB函数的基本应用............................................................56
3.2.2 综合应用案例：拆分列..................................................................................57
3.2.3 综合应用案例：设置数据验证，只能输入汉字名称..............................58
3.2.4 综合应用案例：设置数据验证，只能输入固定位数的不重复数据....59
3.3 截取字符......................................................................................................60
3.3.1 LEFT函数：从左侧截取字符........................................................................60
3.3.2 RIGHT函数：从右侧截取字符.....................................................................61
3.3.3 MID函数：从指定位置截取字符.................................................................62
3.3.4 截取字符综合应用案例：从身份证号码提取信息...................................62
3.4 连接字符串..................................................................................................63
3.4.1 用连接运算符&连接字符串........................................................................64
3.4.2 使用CONCAT函数或CONCATENATE函数连接字符串.......................64
3.4.3 使用TEXTJOIN函数连接字符串.................................................................64
3.4.4 综合应用案例：设计摘要说明.....................................................................65
3.5 替换字符......................................................................................................67
3.5.1 SUBSTITUTE函数及其应用.........................................................................67
3.5.2 REPLACE函数及其应用................................................................................68
3.5.3 综合应用案例：快速统计汇总.....................................................................68
3.6 查找字符......................................................................................................69
3.6.1 FIND函数及其应用........................................................................................69
3.6.2 SEARCH函数及其应用..................................................................................70
3.6.3 综合应用案例：复杂的数据分列.................................................................71
3.7 转换字符......................................................................................................72
3.7.1 为什么要使用TEXT函数转换字符.............................................................72
3.7.2 TEXT函数及其应用........................................................................................72
3.7.3 综合应用案例：直接使用原始数据进行汇总计算...................................75
3.8 其他实用的文本函数及引用.........................................................................76
3.8.1 CHAR函数及其应用.......................................................................................76
3.8.2.综合应用案例：在图表中显示更多信息....................................................77
第4章.日期和时间数据处理案例精讲....................80
4.1 处理文本格式的日期....................................................................................81
4.1.1 使用SUBSTITUTE函数处理非法日期......................................................81
4.1.2 使用TEXT函数处理非法日期.....................................................................81
4.1.3 使用DATEVALUE函数处理文本型日期....................................................81
4.2 日期组合与还原...........................................................................................82
4.2.1 使用DATE函数组合年月日数字.................................................................82
4.2.2 使用DATE函数或DATEVALUE函数和其他函数生成具体日期..........83
4.3 从日期中提取重要信息................................................................................84
4.3.1 从日期中提取年月日数字.............................................................................84
4.3.2 从日期中提取年份、月份和日名称...........................................................85
4.3.3 从日期中提取季度名称.................................................................................87
4.3.4 从日期中提取星期几及星期名称................................................................87
4.3.5 从日期中提取周次名称.................................................................................91
4.4 获取当前日期和时间....................................................................................93
4.4.1 TODAY函数及其应用....................................................................................93
4.4.2 NOW函数及其应用........................................................................................94
4.4.3 NOW函数与TODAY函数的区别................................................................95
4.5 计算将来日期和过去日期............................................................................96
4.5.1 EDATE函数：计算将来或过去的具体日期...............................................96
4.5.2 EOMONTH函数：计算将来或过去的月底日期.......................................97
4.5.3 综合应用案例：计算合同到期日.................................................................97
4.5.4 综合应用案例：计算付款截止日.................................................................99
4.6 计算两个日期之间的期限..........................................................................100
4.6.1.DATEDIF函数及其应用..............................................................................100
4.6.2 YEARFRAC函数及其应用...........................................................................102
4.6.3 综合应用案例：直接用身份证号码计算年龄........................................103
4.7 计算工作日................................................................................................103
4.7.1 计算一段时间后的工作日是哪天.............................................................104
4.7.2 计算两个日期之间的工作日天数.............................................................105
4.8 计算时间....................................................................................................106
4.8.1 时间计算规则及注意事项..........................................................................106
4.8.2 时间计算经典案例：指纹打卡数据处理.................................................107
4.8.3 时间计算经典案例：机器工时计算..........................................................108
第5章.数据统计与汇总案例精讲.........................110
5.1 数据计数统计分析.....................................................................................111
5.1.1 COUNTA函数：统计不为空的单元格个数............................................111
5.1.2 COUNTIF函数：统计满足一个指定条件的单元格个数......................112
5.1.3 COUNTIFS函数：统计满足多个指定条件的单元格个数...................113
5.1.4 综合应用案例：统计数据出现次数..........................................................114
5.1.5 综合应用案例：员工属性分析报告..........................................................117
5.1.6 综合应用案例：删除两个表都存在的数据.............................................120
5.1.7 构建条件值数组进行或条件的计数........................................................121
5.1.8 使用COUNTIF函数和COUNTIFS函数的注意事项............................123
5.2 数据求和统计分析.....................................................................................124
5.2.1 SUMIF函数基本原理与基本应用.............................................................124
5.2.2.SUMIF函数经典案例：对小计行求和.....................................................125
5.2.3 SUMIF函数经典案例：隔列求和..............................................................126
5.2.4 SUMIF函数经典案例：关键词匹配求和.................................................127
5.2.5 使用SUMIF函数实现多个或条件值的求和：精确值匹配条件.........127
5.2.6 使用SUMIF函数实现多个或条件值的求和：关键词匹配条件.........128
5.2.7 使用SUMIF函数计算指定季度的合计数..............................................129
5.2.8 使用SUMIF函数做单条件数据查找.......................................................130
5.2.9 SUMIFS函数基本原理与基本应用..........................................................131
5.2.10 SUMIFS函数经典案例：比较值条件求和.............................................132
5.2.11 SUMIFS函数经典案例：关键词匹配条件求和....................................133
5.2.12 使用SUMIFS函数实现多个或条件值的求和：精确值匹配条件.....134
5.2.13 使用SUMIFS函数实现多个或条件值的求和：关键词匹配条件.....136
5.2.14 使用SUMIFS函数做多条件数据查找...................................................137
5.2.15 使用SUMIF函数和SUMIFS函数做关键词匹配条件的数据查找...138
5.2.16 使用SUMIF函数和SUMIFS函数的注意事项.....................................139
5.3 数组求和函数SUMPRODUCT及其应用...................................................139
5.3.1 SUMPRODUCT函数基本原理与基本应用.............................................140
5.3.2 SUMPRODUCT函数统计公式值不为空的单元格个数.......................142
5.3.3 SUMPRODUCT函数用于单条件计数.....................................................143
5.3.4 SUMPRODUCT函数用于多条件计数.....................................................144
5.3.5 SUMPRODUCT函数用于多条件计数：关键词匹配............................145
5.3.6 SUMPRODUCT函数用于单条件求和.....................................................146
5.3.7 SUMPRODUCT函数用于多条件求和.....................................................146
5.3.8 SUMPRODUCT函数用于条件求和：关键词匹配.................................147
5.3.9 SUMPRODUCT函数用于复杂条件的数据查找....................................148
5.3.10 SUMPRODUCT函数综合应用案例........................................................149
5.4 数据最大值、最小值和平均值计算...........................................................152
5.4.1 计算数据最大值（无条件）........................................................................152
5.4.2 计算数据最大值（指定条件）...................................................................153
5.4.3 计算数据最小值（无条件）........................................................................154
5.4.4 计算数据最小值（指定条件）...................................................................155
5.4.5 计算数据平均值（无条件）........................................................................156
5.4.6 计算数据平均值（指定条件）...................................................................157
5.4.7 指定几个或条件下的最大值、最小值和平均值...................................159
5.4.8 计算数据中位数...........................................................................................160
5.4.9 计算数据四分位值.......................................................................................161
5.5 数字编码超过15位情况下的计算问题.....................................................163
5.5.1 一个案例揭示的问题..................................................................................163
5.5.2 解决方法........................................................................................................163
第6章.数据查找与引用案例精讲.........................165
6.1 VLOOKUP函数及其案例...........................................................................166
6.1.1 基本原理与使用方法..................................................................................166
6.1.2 使用关键词条件查找数据..........................................................................168
6.1.3 从不确定的列中查找数据：使用MATCH函数.....................................169
6.1.4 利用VLOOKUP函数模糊查找..................................................................170
6.1.5 利用VLOOKUP函数做反向查找..............................................................172
6.1.6 利用VLOOKUP函数一次查找并返回多个值.........................................173
6.1.7 利用VLOOKUP函数制作动态分析图表.................................................174
6.1.8 VLOOKUP函数的注意事项........................................................................175
6.2 LOOKUP函数及其案例.............................................................................176
6.2.1 基本原理与应用方法..................................................................................177
6.2.2 综合应用案例：获取最后一个不为空的单元格数据...........................178
6.2.3 综合应用案例：获取满足多个条件下最后一个不为空的数据..........179
6.2.4 综合应用案例：替代嵌套IF函数和VLOOKUP函数做模糊判断.....180
6.3 XLOOKUP函数及其案例...........................................................................182
6.3.1 基本原理与使用方法..................................................................................182
6.3.2 单列匹配条件，返回一个结果.................................................................183
6.3.3 单行匹配条件，返回一个结果.................................................................184
6.3.4 单列匹配条件，返回多个结果.................................................................186
6.3.5 单行匹配条件，返回多个结果.................................................................186
6.3.6 关键词条件匹配查找..................................................................................187
6.3.7 模糊条件匹配查找.......................................................................................188
6.3.8 自动处理错误值............................................................................................189
6.4 MATCH函数及其案例................................................................................191
6.4.1 MATCH函数：关键词匹配定位................................................................191
6.4.2 MATCH函数：模糊匹配定位.....................................................................191
6.4.3 MATCH函数与其他函数联合使用...........................................................192
6.5 INDEX函数及其案例.................................................................................194
6.5.1 基本原理与使用方法..................................................................................194
6.5.2 综合应用案例：单列或单行查找数据.....................................................195
6.5.3 综合应用案例：多行多列区域查找数据（简单情况）.........................196
6.5.4 综合应用案例：多行多列区域查找数据（复杂情况）.........................196
6.5.5 综合应用案例：制作动态图表（表单控件）..........................................199
6.6 OFFSET函数及其案例...............................................................................201
6.6.1 基本原理与使用方法..................................................................................201
6.6.2 基本应用案例：引用某列动态区域，制作随数据增减自动调整的
图表.................................................................................................................203
6.6.3 基本应用案例：引用某行的动态区域，计算累计数...........................206
6.6.4 综合应用案例：分析指定起止月份之间的预算执行情况...................207
6.6.5 小技巧：如何判断OFFSET函数结果是否正确.....................................208
6.7 INDIRECT函数及其案例............................................................................209
6.7.1 基本原理与使用方法..................................................................................209
6.7.2 综合应用案例：快速从各个工作表查询汇总数据................................211
6.7.3 综合应用案例：各月费用跟踪分析..........................................................212
6.7.4 INDIRECT函数应用的注意事项...............................................................216
6.7.5 小技巧：如何判断INDIRECT函数结果是否正确.................................217
第7章.数据排名分析案例精讲............................218
7.1 LARGE函数和SMALL函数及其案例........................................................219
7.1.1 基本原理与使用方法..................................................................................219
7.1.2 数据基本排序及名称匹配..........................................................................220
7.1.3 相同数据排序及名称匹配问题.................................................................221
7.1.4 综合应用案例：建立自动化排名分析模板.............................................223
7.1.5 LARGE函数和SMALL函数的注意事项..................................................227
7.2 SORT函数及其案例..................................................................................228
7.2.1 基本原理与使用方法..................................................................................228
7.2.2 使用数组进行多列多方式排序.................................................................229
7.2.3 综合应用案例：建立自动化排名分析模型（简单情况）.....................230
7.2.4 综合应用案例：建立自动化排名分析模型（复杂情况）.....................231
7.3 SORTBY函数及其案例..............................................................................233
7.3.1 基本原理与使用方法..................................................................................233
7.3.2 任意指定条件下的动态排序（按列排序）..............................................234
7.3.3 任意指定条件下的动态排序（按行排序）..............................................236
7.3.4 综合应用案例：按照自定义序列排序.....................................................237
7.4 排位分析及其案例.....................................................................................239
7.4.1 基本原理与使用方法..................................................................................239
7.4.2 综合应用案例：业务员排名及额外奖金计算........................................241
第8章.数据筛选分析案例精讲............................242
8.1 FILTER函数及其案例.................................................................................243
8.1.1 基本原理与使用方法..................................................................................243
8.1.2 单条件筛选....................................................................................................245
8.1.3 多个与条件筛选...........................................................................................246
8.1.4 多个或条件筛选...........................................................................................247
8.1.5 多个与条件和或条件组合下的筛选........................................................248
8.1.6 计算条件下的筛选.......................................................................................249
8.1.7 关键词匹配条件下的筛选..........................................................................251
8.2 FILTER函数综合应用案例.........................................................................253
8.2.1 建立任意指定单列条件的动态筛选表....................................................253
8.2.2 建立多条件动态筛选表..............................................................................254
8.2.3 筛选与排序联合使用：基本应用..............................................................256
8.2.4 筛选与排序联合使用：综合应用..............................................................258
8.2.5 建立多条件动态筛选与排序模型.............................................................260
8.2.6 剔除零值的动态分析..................................................................................261
8.2.7 对比筛选两个表格.......................................................................................262
8.2.8 嵌套筛选数据...............................................................................................264
8.3 使用其他查找函数快速制作明细表...........................................................265
8.3.1 利用其他函数做滚动查找：基本原理和方法........................................265
8.3.2 利用其他函数做多条件滚动查找.............................................................268
第9章.数据其他处理案例精讲............................270
9.1 数字舍入计算函数及应用..........................................................................271
9.1.1 使用ROUND函数将数字常规四舍五入................................................271
9.1.2 使用RUNDUP函数和ROUNDDOWN函数将数字向上/向下舍入...273
9.2 数据分组分析函数及应用..........................................................................275
9.2.1 使用FREQUENCY函数统计数据频数分布............................................275
9.2.2 使用COUNTIFS函数统计数据频数分布...............................................277
9.3 数据预测分析函数及应用..........................................................................278
9.3.1 建立一元线性预测模型..............................................................................278
9.3.2 建立多元线性预测模型..............................................................................280
9.3.3 建立指数预测模型.......................................................................................281
9.4 其他数据处理函数及应用..........................................................................283
9.4.1 绘制折线图必需的NA函数......................................................................283
9.4.2 对公式空值做算式计算必需的N函数...................................................285
9.4.3 将全角字符转换为半角字符必需的ASC函数......................................286
第10章.函数公式综合应用案例精讲....................287
10.1 滚动汇总每天各个门店的销售日报.........................................................288
10.1.1 门店每天销售额滚动汇总......................................................................288
10.1.2 门店每天销售毛利滚动汇总..................................................................289
10.2 跟踪分析各个门店的每天销售数据.........................................................290
10.2.1 各个门店的销售额跟踪分析..................................................................290
10.2.2 各个门店的销售毛利跟踪分析.............................................................292
10.3 门店销售排名分析...................................................................................293
10.3.1 各个门店的累计销售额排名分析.........................................................293
10.3.2 各个门店的累计销售毛利排名分析.....................................................294
10.3.3 各个门店的毛利率排名分析..................................................................295