第 1 章公式基础知识••••••••••••••••••••••••••••••••••••••••••••••••••1
1.1 熟悉 Excel 的工作环境••••••••••••••••••••••••••••••••••••••••••••••••••••1
疑难 1 如何减小功能区所占屏幕空间•••••••••••••••••••••••••••••••••••••••••1
Excel 2013 操作界面概览•••••••••••••••••••••••••••••••••••2
疑难 2 如何不用设置公式实现统计结果预览••••••••••••••••••••••••••••••••••••3
设置 Excel 2013 状态栏••••••••••••••••••••••••••••••••••••••••••3
疑难 3 如何在一个工作簿中管理多张表格•••••••••••••••••••••••••••••••4
理解工作簿、工作表和单元格之间的关系※••••••••••••••••••••••••5
疑难 4 为什么表格列标题显示的是数字而不是字母•••••••••••••••••••••••••••••5
设置 R1C1 引用样式••••••••••••••••••••••••••••••••••••••••••••6
A1 引用样式和 R1C1 引用样式••••••••••••••••••••••••••••••••••••6
疑难 5 如何根据开头字母的提示快速输入函数名称•••••••••6
开启“公式记忆式键入”功能的两种方法•••••••••••••••••••••••7
疑难 6 如何不让函数提示信息遮盖到工作表的列号•••••••••••••••••••••••7
函数屏幕提示工具的妙用•••••••••••••••••••••••••••••••••••••8
疑难 7 为什么公式计算结果不会变化•••••••••••••••••••••••••••••••••••••••9
设置自动或手动计算模式••••••••••••••••••••••••••••••••••••••••••••9
疑难 8 为什么单元格中只显示公式文本而不显示计算结果••••••••••••••10
检查“显示公式”选项状态•••••••••••••••••••••••••••••••••••••••10
检查单元格是否为“文本”格式••••••••••••••••••••••••10
疑难 9 单元格左上角的绿色小三角图形是什么含义•••••••••••••••••11
错误检查规则及含义※•••••••••••••••••••••••••••••••••••••••••••••••••12
使用错误检查巧换文本形式的数字为数值•••••••••••••••••••••••••••••••13
疑难 10 如何在新输入的行中自动填充上一行的公式••••••••••••••••••••••••••13
设置自动扩展公式功能••••••••••••••••••••••••••••••••13
1.2 公式基础概念•••••••••••••••••••••••••••••••••••••••••••14
疑难 11 为什么此“公式”不能根据圆的直径求面积••••••••••••••••••••••••••••14
什么是公式••••••••••••••••••••••••••••••••••••15
什么是函数•••••••••••••••••••••••••••••••••••••••••••••••••15
公式的组成结构••••••••••••••••••••••••••••••••••••••••••••••••15
疑难 12 为什么 IF 函数只有两个参数也能计算•••••••••••••••••••16
必需参数与可选参数•••••••••••••••••••••••••••••••••••••••••••••16
省略参数与省略参数的值•••••••••••••••••••••••••••••••••16
疑难 13 为什么上百万的网友算错数学题 6÷2 × (1+2)•••••••••••••••••••••17
公式中运算符的类型及含义•••••••••••••••••••••••••••••••••••18
公式中运算符的计算优先级•••••••••••••••••••••••••••••••••••••18
疑难 14 为什么开奖号末位判断总是“大”••••••••••••••••••••••••••••••••••••••19
Excel 的数据类型※••••••••••••••••••••••••••••••••••••••••••••••20
数据排列顺序的规则•••••••••••••••••••••••••••••••••••••••20
公式中文本形式数字如何转换为数值••••••••••••••••••••••20
疑难 15 为什么数字与“空”单元格相加会出错•••••••••••••••••••••••••20
空文本与空单元格的区别••••••••••••••••••••••••••••••••21
公式中的&""有什么作•••••••••••••••••••••••22
疑难 16 为什么两个时间相减得到一长串的#号••••••••••••••••••••22
产生一长串#号的原因分析及解决方法•••••••••••••••••••••••••••••••••23
疑难 17 如何输入编号 1-2、1/2 以及比分 1:3 •••••••••••••••••••••••••••••••23
日期、时间数据的表示方法•••••••••••••••••••••••••••••••24
打开“设置单元格格式”对话框的 3 种方法••••••••••••••••••••••••••••24
疑难 18 如何快速规范以小数点间隔的伪日期••••••••••••••••••••••••••••25
查找替换法规范“伪日期”数据•••••••••••••••••••••••••••••••••••••25
数据分列法规范“伪日期”数据•••••••••••••••••••••••••••••26
根据使用习惯改变默认日期与时间格式•••••••••••••••••••••••••••••26
Excel 中两位数字的年份••••••••••••••••••••••••••••••••••••••••••••••27
疑难 19 如何拯救小数表示的伪出生年月•••••••••••••••••••••••••••••••28
用剪贴板规范含有两位小数的伪日期数据••••••••••••••••••••••••••••••••28
疑难 20 如何实现输入数字 1、2 后自动将其显示为性别•••••••••••••••••30
为单元格自定义数字格式•••••••••••••••••••••••••••••••••••••••30
Excel 的数字格式种类••••••••••••••••••••••••••••••••••••••••••••31
常用数字格式代码组成部分••••••••••••••••••••••••••••••••••••••31
疑难 21 如何让单位为元的金额显示为万元••••••••••••••••••••••••••••32
数字格式代码及含义••••••••••••••••••••••••••••••••••••••••••••33
日期相关格式代码•••••••••••••••••••••••••••••••••••••34
时间相关格式代码••••••••••••••••••••••••••••••••••••••••34
数字格式代码中的日历和语言区域••••••••••••••••••••••••••••••••••••••34
疑难 22 如何让累计求和引用的区域随公式向下复制变化•••••••••••••••••••••••38
相对引用、绝对引用、混合引用••••••••••••••••••••••••••••••••••••39
疑难 23 不同表格相同表头能否只填一处••••••••••••••••••••••••••••••••••40
跨表引用单元格的表示方法••••••••••••••••••••••••••••••41
疑难 24 为什么身份证号码 15 位以后的数字都显示为 0 •••••••••••••••42
Excel 的计算限制••••••••••••••••••••••••••••••••••••••••••••43
疑难 25 为什么有时候汇总金额会比实际差 1 分钱••••••••••••••••••••44
浮点运算导致意外计算误差••••••••••••••••••••••••••••••45
疑难 26 为什么即使没有修改内容关闭表格时也会提示保存文件•••••••••45
哪些函数具有易失性•••••••••••••••••••••••••••••••••••••••••••••••46
增加“撤销”次数••••••••••••••••••••••••••••••••••••••••••••••••••••46
疑难 27 为什么在 Excel 2003 中打开公式会显示_xlfn. 前缀••••••••••••••••••••••47
Excel 2003 不支持的新增函数有哪些•••••••••••••••••••••••••••••••••••••48
疑难 28 为什么用 Excel 2013 打开早期版本的文件后只有 65536 行•••••••••••••48
Excel 2013 支持哪些文件格式••••••••••••••••••••••••••••••••••••••••••50
如何在早期 Excel 版本中打开、另存为高版本文件•••••••••••••••••••••••50
1.3 公式常用操作技巧•••••••••••••••••••••••••••••••••••••••••••••••50
疑难 29 如何选择合适的函数来解决问题•••••••••••••••••••••••51
如何搜索函数•••••••••••••••••••••••••••••••••••••••••••••••••51
从分类中选择所需函数•••••••••••••••••••••••••••••••••••52
函数的分类••••••••••••••••••••••••••••••••••••••••••••••••52
疑难 30 如何快速获取函数的帮助信息••••••••••••••••••53
获取函数实时提示信息••••••••••••••••••••••••••••••••••••••53
函数帮助文件中有哪些信息••••••••••••••••••••••••••••54
疑难 31 如何快速将公式复制到其他单元格•••••••••••••••••••••••••••••••••54
复制粘贴公式方法•••••••••••••••••••••••••••••••55
填充柄复制公式法••••••••••••••••••••••••••••••••55
填充命令法复制公式••••••••••••••••••••••••••••••••••••••••••••56
多个单元格快速输入相同公式••••••••••••••••••••••••••••••••56
疑难 32 如何复制公式且保持相对引用地址不变••••••••••••••••••••57
用辅助工作表法保持单元格相对引用•••••••••••••••••••••••••••••••••57
疑难 33 如何快速在多个合计行中填充求和公式••••••••••••••••••••••••••••••••59
定位空单元格与批量求和•••••••••••••••••••••••••••••••••••••••••••59
筛选可见单元格求和••••••••••••••••••••••••••••••••••••••••••••••••60
疑难 34 如何按照合并单元格求对应金额小计••••••••••••••••••••••••••61
混合引用妙求合并单元格对应数据之和•••••••••••••••••••••62
计算合并单元格包含多少行•••••••••••••••••••••••••••••••••••62
合并与拆分单元格•••••••••••••••••••••••••••••••••••••••••••••••62
疑难 35 如何让合并单元格中的每个单元格都有内容••••••••••••••••63
格式备份让“合并”单元格都有内容••••••••••••••••••••••••••64
格式合并与单元格合并的区别••••••••••••••••••••••••••••••••••••••••••65
疑难 36 如何查看公式分步运算结果以便找出错误••••••••••••••••••66
分步查看运算结果•••••••••••••••••••••••••••••••••••••••••••••66
用【F9】键查看运算结果••••••••••••••••••••••••••••••67
疑难 37 如何去掉报表中的公式只保留计算结果•••••••••••••••••••••••••••68
择性粘贴法保留公式计算结果••••••••••••••••••••••••••••••68
粘贴数值的 3 种效果••••••••••••••••••••••••••••••••••69
断开链接法去除公式••••••••••••••••••••••69
疑难 38 如何允许填报数据又防止破坏报表中的公式••••••••••••••••••••••70
保护含有公式的单元格•••••••••••••••••••••••••••••••••71
如何隐藏公式•••••••••••••••••••••••••••••••••••••••••••••••72
如何仅可选定需要填报的单元格•••••••••••••••72
疑难 39 如何不切换工作表监控不同区域变化情况••••••••••••••••••••••••••72
使用监视窗口监控数据•••••••••••••••••••••••••••••••••••••••••••••73
神奇的单元格“照相机”•••••••••••••••••••••••••••••••••••74
疑难 40 如何让新插入的行被原有公式引用•••••••••••••••75
自动计算新插入行数据••••••••••••••••••••••••••••76
1.4 数组与数组公式••••••••••••••••••••••••••••••••••••••••••••••••••• 77
疑难 41 如何不用单元格存储个税税率关系表••••••••••••••77
什么是数组••••••••••••••••••••••••••••••••••••••••••••••••••••78
数组的维度和尺寸••••••••••••••••••••••••••••••••79
疑难 42 如何根据一组商品的单价与数量直接求总金额••••••••••••79
数组多项运算原理••••••••••••••••••••••••••••••••••79
疑难 43 为什么运行公式后大括号{}消失而计算结果出错•••••••••••••80
什么是数组公式••••••••••••••••••••••••••••••••••••••••••81
如何在合并单元格输入数组公式•••••••••••••••••••••81
为何建议不使用整列单元格创建数组公式••••81
疑难 44 如何判断多项运算是否需要使用数组公式•••••••••••••••••••••••••••••82
图解数组公式与普通公式运算的差异•••••••••••••••••••••••••82
【Ctrl+Shift+Enter】组合键对于数组公式的意义••••••••••••••82
疑难 45 为什么会出现“不能更改数组的某一部分”的提示•••••••••••83
输入多单元格数组公式••••••••••••••••••••••••••••••••84
为何使用多单元格数组公式••••••••••••••••••••••••••84
疑难 46 为什么相同公式在不同位置的计算结果不同•••••••••••••••••••••••••85
什么是绝对交集引用•••••••••••••••••••••••••••••••••••••••••••••85
数组的绝对交集•••••••••••••••••••••••••••••••••••••••••••••••••••86
如何判别公式是否生成内存数组••••••••••••••••••••••••••••86
疑难 47 为什么销售业绩与提成点数相乘出现#N/A 错误•••••••••••••••••••••••87
数组间多项运算与数组维度、尺寸的关系•••••••••••••••••••••88
疑难 48 为什么 AND、OR 函数不能返回多项逻辑判断结果•••••••••••••••••••••89
多项运算需要逻辑判断返回数组结果••••••••••••••••••••••••••••••90
疑难 49 如何快速取得自然数等差数列•••••••••••••••••••••••••••••90
ROW 函数返回行号作为等差数列•••••••••••••••••••••••91
ROWS 函数与 ROW 函数的区别••••••••••••••••••••••••••••••91
获取数字 0~9 数列••••••••••••••••••••••••••••••••••••••••••••••••••••92
疑难 50 如何汇总连续多表相同单元格的销售额••••••••••••••••92
什么是连续多表三维引用••••••••••••••••••••••••••••••••••••••••••••••93
疑难 51 为什么 INDIRECT 函数引用多表相同单元格求和出错••••••••••••••••••94
什么是函数产生的多维引用•••••••••••••••••••••••••••••••94
为何使用 N 函数计算结果不一定正确••••••••••••••••••••••••95
函数产生多维引用模型图解••••••••••••••••••••••••••••••••95
1.5 在公式中使用名称•••••••••••••••••••••••••••••••••••••••••••••••96
疑难 52 如何用汉字“税率”代替常量数组•••••••••••••••••••••••••96
为什么要使用名称•••••••••••••••••••••••••••••••••••••••••••••97
疑难 53 为什么字母 C 不能作为名称使用•••••••••••••••••••••••••••••••••97
名称的命名原则•••••••••••••••••••••••••••••••••••••••••••••98
疑难 54 如何快速将单元格区域定义为多个名称•••••••••••••••••••••••••98
使用名称框快速定义名称•••••••••••••••••••••••••••••••99
使用“以选定区域创建名称”功能批量定义名称••••••••••••••••99
疑难 55 如何在修改名称中的引用位置时使用方向键••••••••••••••••••100
切换编辑框中的点选与编辑模式••••••••••••••••••••••100
疑难 56 如何在不同工作表定义表示不同数据的相同名称•••••••••••••••••••••100
定义工作表级名称••••••••••••••••••••••••••••••••••••••••••••••••101
工作簿级、工作表级名称•••••••••••••••••••••••••••••102
疑难 57 如何在名称中实现工作表标签“相对引用”••••••••••••••••••••102
名称中工作表的“相对引用”••••••••••••••••••••103
疑难 58 如何直接使用表格标题来引用数据••••••••••••••••••••••••104
创建“表格”区域•••••••••••••••••••••••••••••••••••••••••••••••••••••104
什么是“表格”功能••••••••••••••••••••••••••••••••••••••••••••••••••105
什么是“结构化引用”•••••••••••••••••••••••••••••••••••••••••••••••••105
疑难 59 如何让图片随选择的生肖自动变化••••••••••••••••••••••••••••106
使用名称动态引用图片••••••••••••••••••••••••••••••••••••••••••••••••106
疑难 60 如何让打印区域随数据输入自动扩展••••••••••••••••••••••••••••107
使用名称设置动态打印区域•••••••••••••••••••••••••••••••••••••108
疑难 61 如何在单元格中设置打印顶端标题行和左端标题列•••••••••••••••••109
使用名称在单元格中设置打印标题行和列••••••••••••••••••••••••••••••109
疑难 62 如何快速删除多个错误名称••••••••••••••••••••••••••••••111
使用名称管理器筛选错误名称•••••••••••••••••••••••••••••••••••••111
疑难 63 如何让名称隐藏起来•••••••••••••••••••••••••••••••••112
使用 VBA 隐藏名称•••••••••••••••••••••••••••••••••112
1.6 练习与思考••••••••••••••••••••••••••••••••••••••••113
第 2 章逻辑判断••••••••••••••••••••••••••••••••••114
2.1 逻辑关系•••••••••••••••••••••••••••••••••••••••••••••••114
疑难 64 如何判断串联、并联、短路、双控电路的连通状态•••••••••••••••••••••••••114
使用 IF 函数进行逻辑判断•••••••••••••••••••••••••••••••••••••••••••115
与、或、非、异或 4 种逻辑关系•••••••••••••••••••••••••115
疑难 65 如何判断购房是否需要提供个税或社保证明••••••••••••••••••••••••116
逻辑非关系的三种判断方法••••••••••••••••••••••••••••••••••••••••117
疑难 66 如何判断同时满足购买经济适用住房的 3 个条件••••••••••••••••••117
逻辑值与数值转换规则••••••••••••••••••••••••••••••••••••••••118
疑难 67 如何根据职工性别和职务判断退休年龄••••••••••••••••••••••••118
使用四则运算代替逻辑判断••••••••••••••••••••••••••••••••••••••••••••119
2.2 多层判断•••••••••••••••••••••••••••••••••••119
疑难 68 如何根据成绩判断优良中差等级•••••••••••••••••••••••••••••••••119
多层 IF 函数嵌套的逻辑关系树•••••••••••••••••••120
避免逻辑关系重复、遗漏、冗余•••••••••••••••••••••••••120
疑难 69 如何给金卡和银卡客户按消费额派发赠品•••••••••••••••••••••••••••••120
不同分支多层逻辑判断••••••••••••••••••••••••••••••••••••••••••••121
多层级对应关系逻辑判断问题变通处理••••••••••••••••••••••••••122
2.3 常用条件设置••••••••••••••••••••••••••••••••••••••••••••••• 123
疑难 70 如何将公式返回的错误值转为其他值•••••••••••••••••••••123
IS 类函数判断屏蔽错误值通用公式•••••••••••••••••••••••••••••••••••124
IFERROR、IFNA 函数屏蔽错误值通用公式••••••••••••••••••••••••••••••124
常见 7 种错误的产生原因及解决方案••••••••••••••••••••••••••125
疑难 71 如何判断必填字段填写是否完整••••••••••••••••••••••••••••••••••••127
判断真空单元格••••••••••••••••••••••••••••••••••••127
疑难 72 如何判断单元格内是否有公式并标识颜色••••••••••••••••••••128
使用 ISFORMULA 函数判断单元格是否为公式•••••••••••••••••••129
使用宏表函数 GET.CELL 函数判断单元格是否为公式••••••••••••129
疑难 73 如何判断某年是否为闰年•••••••••••••••••••••••••••••••130
判断闰年的 4 种解法••••••••••••••••••••••••••••••••••••••••••131
疑难 74 如何判断开奖号码之和的奇偶性•••••••••••••••••••••••••••131
数字奇偶性判断••••••••••••••••••••••••••••••••••••••••••••••132
疑难 75 如何根据身份证号码判断性别••••••••••••••••••••••••••••••••••133
取得身份证号中的性别数字••••••••••••••••••••••••••••••••••••••133
判断奇偶的函数限制•••••••••••••••••••••••••••••••••••••••••••134
疑难 76 判断数据是否存在重复现象••••••••••••••••••••••••••••••••••••••••••134
区分大小写判断重复数据•••••••••••••••••••••••••••••••••••••135
疑难 77 如何判断一个字符是否汉字•••••••••••••••••••••••••••136
双字节法判断字符是否为汉字••••••••••••••••••••••••••••••••••136
与“吖”字比较判断字符是否为汉字••••••••••••••••••••••••••••••••136
Unicode 函数判断字符是否为汉字••••••••••••••••••••••••••••••••••••137
2.4 练习与思考••••••••••••••••••••••••••••••••••••••••••137
第 3 章数据汇总•••••••••••••••••••••••••••••••••••••••••••••••••••••138
3.1 汇总求和•••••••••••••••••••••••••••••••••••••••••••138
疑难 78 如何汇总连续多个表中相同单元格区域的数据•••••••••••••••••••138
通配符在输入连续多表三维引用中的应用••••••••••••••••••••••••••••139
疑难 79 如何汇总茶叶中铁观音的销量•••••••••••••••••••••••••••139
SUMIF 函数单条件求和••••••••••••••••••••••••••••••••••140
SUMIF 函数第 3 参数的简写形式••••••••••••••••••••••••••••••••••140
在 SUMIF 函数中使用通配符和数组••••••••••••••••••••••••••••141
疑难 80 如何汇总月薪在 2000 至 4000 元的工资总额••••••••••••••••••••••••141
SUMIFS 单列多条件求和•••••••••••••••••••••••••••141
其他单列多条件求和解法••••••••••••••••••••••••••••••••••••142
SUMIFS 与 SUMIF 函数的区别••••••••••••••••••••••••••••142
疑难 81 如何根据考评系数及评分计算员工的综合得分•••••••••••••••••••••••142
SUMPRODUCT 求数组乘积之和••••••••••••••••••••••••••••••••••••••143
SUM 数组公式与 SUMPRODUCT 求数组乘积之和的区别•••••••••••••••143
疑难 82 如何避开单元格中的错误值求和••••••••••••••••••••••••••••••144
使用 SUMIF 函数排错求和••••••••••••••••••••••••••••••••••••••••••••144
疑难 83 如何验算现金流量表的数据勾稽关系是否正确••••••••••••••144
使用通配符进行条件求和••••••••••••••••••••••••••••••••••••••••145
疑难 84 如何求出某月各项支出中最大值之和•••••••••••••••••••••••••••••146
数据库函数中使用数组求多列最大值••••••••••••••••••••••••••••••••146
疑难 85 如何忽略隐藏的行汇总数据••••••••••••••••••••••••••••••••••••••147
SUBTOTAL 函数对应功能及忽略不可见单元格特性••••••••••••••••••••••147
疑难 86 如何忽略隐藏的列汇总数据•••••••••••••••••••••••••••••••••••148
忽略隐藏列求和••••••••••••••••••••••••••••••••••••••••••••••149
疑难 87 如何汇总固定间隔 n 行的数据••••••••••••••••••••••••••••••••••149
MOD 函数构建间隔 n 行的循环••••••••••••••••150
间隔 n 行数据求和通用公式••••••••••••••••••••••••••••••••150
疑难 88 如何汇总某月数据••••••••••••••••••••••••••••••150
按月汇总数据 ? •••••••••••••••••••••••••••••••••••••••151
疑难 89 如何汇总本科学历男员工的工资总和••••••••••••••••••••••••••••••••151
SUMIFS 函数多条件求和通用公式•••••••••••••••••••152
SUMPRODUCT 或 SUM 函数多条件求和通用公式•••••••••••••••••••••152
疑难 90 如何按月分别汇总每个销售员的销售额••••••••••••••••••••••••••••••153
MMULT 函数双条件求和••••••••••••••••••••••••••••••••••••••••••153
疑难 91 如何根据代码等式求对应数值之和•••••••••••••••••••••••154
根据代码等式对应求和••••••••••••••••••••••••••••155
疑难 92 如何根据多个条件跨表汇总销售数据•••••••••••••••••••••••155
SUMIFS 函数多表多条件求和•••••••••••••••••••••••••••••••••••••156
3.2 数据计数••••••••••••••••••••••••••••••••••••••••••••••••••••••••• 157
疑难 93 如何统计成绩表中的及格人数•••••••••••••••••157
COUNTIF 函数常见条件统计用法•••••••••••••••••158
常用特殊条件计数••••••••••••••••••••••••••••••••••••••••••••159
疑难 94 如何统计 35 岁以上有房有车的职工人数•••••••••••••••••159
COUNTIFS 函数多条件计数••••••••••••••••••••••••••••••159
SUMPRODUCT 或 SUM 函数多条件计数通用公式••••••••••••••••••••••••••160
COUNT 函数多条件计数通用公式•••••••••••••••••••••••••••••161
疑难 95 如何统计甲车间生产的产品规格带*号的产品种类•••••••••••••••••••161
包含通配符的条件计数••••••••••••••••••••••••••••••••••162
疑难 96 如何统计不重复数据个数•••••••••••••••••••••••••••••••••••••••••162
1/COUNTIF 函数统计不重复值的优缺点••••••••••••••••••••••••••••••163
MATCH=ROW 法统计不重复值个数的优缺点••••••••••••••••••••••164
FREQUENCY 函数统计不重复值的优缺点•••••••••••••••••••••••165
疑难 97 如何区分字母大小写统计不重复数据个数••••••••••••••••••••165
用 1/MMULT 法求不重复值••••••••••••••••••••••••••••••••••••••••••••166
3.3 极值均值••••••••••••••••••••••••••••••••• 166
疑难 98 如何设置提成奖金的上、下限•••••••••••••••••••••••••••••••••••••166
使用 MAX、MIN 函数设置上、下限通用公式•••••••••••••••••••167
使用 MEDIAN 函数设置上、下限通用公式•••••••••••••••••••••••••167
使用 IF、TEXT 函数设置上、下限通用公式••••••••••••••••••••168
疑难 99 如何按先进先出法进行商品库龄分析••••••••••••••••••••••••168
混合引用与上下限解决先进先出问题通用公式••••••••••••••••••••••••••••170
疑难 100 如何标记体检指标与参考范围的高低关系•••••••••••••••••••••171
中值法判断数值与上、下限范围的关系•••••••••••••••••••••••171
复数函数法判断数值与上、下限范围的关系••••••••••••••••••••••••••172
疑难 101 如何进行线性插值计算•••••••••••••••••••••••••••••••••••••••••••172
使用 TREND 函数线性插值计算•••••••••••••••••••••••••••••••••173
疑难 102 如何不计重复返回一组数中的次大、次小值••••••••••••••••••••174
提取不计重复的次大、次小值••••••••••••••••••••••••••174
疑难 103 如何计算一组数据中大于某数的最小值••••••••••••••••••••••••••175
提取大于平均值的最小值•••••••••••••••••••••••••••••••••••••••••••175
疑难 104 如何统计前 10 名成绩的平均分••••••••••••••••••••••176
计算前 10 名成绩平均分••••••••••••••••••••••••••••••••••••••••••••••••177
疑难 105 如何剔除一个最大值和一个最小值后求平均值••••••••••••••••••••••••177
计算去掉 n 个最大值和 n 个最小值后的均值通用公式••••••••••••••178
疑难 106 如何根据条件计算平均值••••••••••••••••••••••••••••••••••••178
使用 AVERAGEIF 函数单条件求均值•••••••••••••••••••••••••••179
AVERAGEIFS 函数计算条件均值••••••••••••••••••••••••••••180
疑难 107 如何计算加权平均值•••••••••••••••••••••••••••••181
计算加权平均值 ?••••••••••••••••••••••••••••••••••••••••••••••••••••••••181
疑难 108 如何计算各条生产线最高工时的平均值••••••••••••••••••••••182
SUBTOTAL 函数+多维引用对合并单元格计数•••••••••••••••••••••••••••182
配权除权法提取多个最大值••••••••••••••••••••••••••••••••••••••••••••182
3.4 频率分布••••••••••••••••••••••••••••••••••••••••183
疑难 109 如何获取一组数值中出现频率最多的数据••••••••••••••183
提取多个出现频率最高的数值•••••••••••••••••••••••••••184
疑难 110 如何根据分数段统计成绩分布情况•••••••••••••••••••••••••••184
FREQUENCY 函数统计分段分布频率•••••••••••••••••••••••••••••185
疑难 111 如何统计彩票号码最大连续出现次数••••••••••••••••••••••186
计算连续出现频率通用公式•••••••••••••••••••••••••••••••••••••187
疑难 112 如何判断是否存在连续 5 次超标的数据•••••••••••••187
COUNTIF+OFFSET 多维引用计算连续超标次数•••••••••••••••••188
3.5 排名排序•••••••••••••••••••••188
疑难 113 如何实现多列成绩统一排名••••••••••••••••••••••••••••••••••••188
RANK 函数多区域数据排名•••••••••••••••••••••••••••••••••••••••189
新旧 RANK 函数差异 ?•••••••••••••••••••••••••••••••••••••••••189
疑难 114 如何进行跨工作表数据总排名•••••••••••••••••••••••••189
RANK 函数连续多表三维引用求排名•••••••••••••••••••••••••190
疑难 115 如何在年级成绩总表中统计学生的班级排名•••••••••••••••••••••••••190
成绩总表中班级排名的 4 种算法••••••••••••••••••190
疑难 116 如何让并列排名统计只占一个名次••••••••••••••••••••••••••192
中国式排名的 3 种算法••••••••••••••••••••••••••••••••••••••193
疑难 117 如何对智育德育成绩按比例折算后排名•••••••••••••••••••••••194
多科目折算比例排名••••••••••••••••••••••••••••••••••••••••••194
疑难 118 如何根据足球比赛成绩进行多关键字排名••••••••••••••••••••••195
加权算法多关键字排名技术••••••••••••••••••••••••••196
疑难 119 如何统计中学生运动会奖牌榜排名并按升序排序••••••••••••••••••196
多关键字升、降序排列通用公式•••••••••••••••••••••••197
疑难 120 如何提取员工信息并按照姓名升序排序••••••••••••••••••••••••••••198
文本排序 ?••••••••••••••••••••••••••••••••••••••••••••••••••••••••••199
疑难 121 如何统计参赛选手的最后得分并按降序排序••••••••••••••••200
多组成绩按剔除极值后均值排名••••••••••••••••••••••••••••••201
3.6 练习与思考••••••••••••••••••••••••••••••••••••••••••••••••••••••••• 201
第 4 章查找与引用•••••••••••••••••••••••••••••••••••••••••••202
4.1 查询定位•••••••••••••••••••••••••••••••••••••••• 202
疑难 122 如何定位记录在数据中首次出现的位置•••••••••••••••••••••••••••202
MATCH 函数定位查找•••••••••••••••••••••••••••••••••••••••••••••••203
MATCH 函数定位与 MIN+IF 数组公式定位的区别••••••••••••••••••••••••••204
疑难 123 如何在升序排列的数据中查找近似匹配值••••••••••••••••••••••••204
VLOOKUP 函数首列查找对应结果••••••••••••••••••••••••••••••205
LOOKUP 函数近似匹配查询••••••••••••••••••••••••••••••••••••••206
疑难 124 如何根据父子级别关系计算物料数量••••••••••••••••••••••••••••••••207
VLOOKUP 函数精确匹配查询••••••••••••••••••••••••••••••••••••••207
疑难 125 如何根据输入工号自动显示员工基本信息••••••••••••••••••••208
VLOOKUP+MATCH 函数组合交叉双条件精确匹配查询••••••••••••••••208
HLOOKUP+MATCH 函数组合交叉双条件精确匹配查询••••••••••••••••••209
引用函数与 MATCH 组合的交叉双条件精确匹配查询•••••••••••••••••209
疑难 126 如何根据品名和规格双列条件查询物品单价•••••••••••••••••••••209
INDEX+MATCH 函数组合双列条件精确匹配查询通用公式•••••••••••••••210
VLOOKUP+IF 双列条件精确匹配查询•••••••••••••••••••••••••••••212
疑难 127 如何查找某行或某列最后一个非空单元格数据••••••••••••••••••••212
LOOKUP 精确匹配查询的通用公式•••••••••••••••••••••••••••••••••••••••213
LOOKUP 返回最后一个文本或数值•••••••••••••••••••••••••••••••••••213
疑难 128 如何根据物料编号查找最新价格•••••••••••••••••••••••••••••••••••••214
倒数法查找多列第一个数值••••••••••••••••••••••••••••••••••••214
文本通配符法查找多列第一个数值••••••••••••••••••••••••••••••••••••215
疑难 129 如何根据基准单元格偏移引用数据•••••••••••••••••••••••••216
多行多列数据转换为一列•••••••••••••••••••••••••••••••216
OFFSET 函数的偏移引用•••••••••••••••••••••••••••••••••216
疑难 130 如何根据供货商名称跨表查询••••••••••••••••••••••••••••••••••••217
将代表单元格地址的文本转换为引••••••••••••••••••••••••••••218
疑难 131 如何用函数实现行列内容转置••••••••••••••••••••••••••••••••••218
获取转置后的数据••••••••••••••••••••••••••••••••••••••219
疑难 132 如何始终引用上一行数据而不受删除行的影响•••••••••••••••••••••••219
始终引用上一行单元格•••••••••••••••••••••••••••••••••220
疑难 133 如何按条件筛选提取数据记录••••••••••••••••••••220
筛选满足条件记录列表的通用数组公式•••••••••••••••••••••••221
疑难 134 如何提取满足条件的多行多列数据并放到同一列中••••••••••••••222
多行多列筛选满足条件记录列表通用数组公式••••••••••••••••••223
疑难 135 如何将满足条件的多条记录放在一个单元格•••••••••••••••••••••••223
合并满足条件的多个查询结果••••••••••••••••••••••••••••224
疑难 136 如何将一个单元格内多个“字段”与内容进行分离•••••••••••••••225
VLOOKUP 单列查找妙用 ?••••••••••••••••••••••••••••••••••226
VLOOKUP 单列查找在线实时查询 JSON 格式股票数据••••••••••226
4.2 超级链接••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••227
疑难 137 如何跳转到最后一行数据下方的空单元格•••••••••••••••••••••••••••227
设置动态跳转超链接••••••••••••••••••••••••••••••••••••••227
疑难 138 如何在 Excel 中用关键字搜索网页••••••••••••••••••228
在公式中使用常用搜索引擎•••••••••••••••••228
疑难 139 如何跳转到查询的结果••••••••••••••••••••••••••••••••••••229
跳转到满足条件的查询结果•••••••••••••••••••••••••••230
疑难 140 如何使用函数超链接打开带#号的文件•••••••••••••••••••230
新增 ENCODEURL 函数转换 URL 编码••••••••••••••••••••••••••231
4.3 筛选重复••••••••••••••••••••••••••••••••••••••••••••••••••••••231
疑难 141 如何列出某列数据的不重复记录•••••••••••••••••••••••••••••••••••231
MATCH=ROW 方法提取不重复值••••••••••••••••••••••••••232
MIN+COUNTIF=0 方法提取不重复值••••••••••••••••••••232
疑难 142 如何列出多行多列单元格区域中的不重复记录•••••••••••••••••••••234
多行多列数据提取不重复值••••••••••••••••••••••••••••234
疑难 143 如何列出自动筛选后的不重复姓名•••••••••••••••••••••••••••235
自动筛选结果中的不重复值提取••••••••••••••••••••••••••••••••235
疑难 144 找出 A 列有而 B 列没有的数据并剔除重复值•••••••••••••••••••236
筛选 A 列有而 B 列没有的记录•••••••••••••••••••237
疑难 145 如何提取连续出现次数最多的数据记录••••••••••••••238
LOOKUP 判断不连续断点的应用••••••••••••••••••••••••••••••••••••••••238
疑难 146 如何剔除重复值并按数据出现频率降序排序•••••••••••••••239
筛选满足条件记录并排序通用公式•••••••••••••••••••••••240
4.4 Web 查询••••••••••••••••••••••••••••••••••••••••••240
疑难 147 如何读取 XML 字符串中的数据••••••••••••••••••••••241
FILTERXML 函数读取 XML 指定路径数据••••••••••••••••••••••••••242
XML 格式文档结构•••••••••••••••••••••••••••••••••••••243
XPath 表达式简介••••••••••••••••••••••••••••••••••••••••••••••••••••243
疑难 148 如何在线翻译单元格中的内容•••••••••••••••••••••••••••••••••••••244
获取 XML 格式的有道翻译结果••••••••••••••••••••••••••••••244
WEBSERVICE 函数获取 Web 服务数据••••••••••••••••••••••••••••••••••245
疑难 149 如何在线查询货币汇率••••••••••••••••••••••••••••••••••••••246
运用 API 工具在线查询•••••••••••••••••••••••••247
在线转换汉字为拼音••••••••••••••••••••••••••••••••••••••••••••••••••248
在线查询手机号码归属地•••••••••••••••••••••••••••••••••••248
疑难 150 如何在线查询中国气象网天气情况•••••••••••••••••••••••••248
在线查询天气情况•••••••••••••••••••••••••••••••••••••••••••••••••••249
4.5 练习与思考•••••••••••••••••••••••••••••• 251
第 5 章文本处理••••••••••••••••••••••••••••••••••••••••••••252
5.1 截取与连接••••••••••••••••••••••••••••••••••••••• 252
疑难 151 如何将连续单元格区域的字符串合并起来•••••••••••••••••••••••••••••252
合并连续单元格区域的字符串••••••••••••••••••••••••••••••••253
字符串连接两种通用方法•••••••••••••••••••••••••••••253
疑难 152 如何提取混合字符串中的连续汉字•••••••••••••••••••••••••••••••••254
根据单双字节截取连续汉字••••••••••••••••••••••••••••••••••••••••••••254
截取字符串左侧或右侧的连续汉字•••••••••••••••••••••••••••255
疑难 153 如何让公式结果可以换行显示••••••••••••••••••••••••••••256
换行符在公式中的应用••••••••••••••••••••••••••••••••••••256
疑难 154 如何将字符串按特定分隔符分列•••••••••••••••••••••••••••••••••••••257
函数分列法通用公式•••••••••••••••••••••••••••••••••••••••••••••••••••257
函数分列法截取子串长度临界值的推算••••••••••••••••••••••••••258
5.2 查找与替换••••••••••••••••••••••••••••••••••••• 259
疑难 155 如何剔除姓名信息中的空格•••••••••••••••••••••••••••259
替换法删除空格字符•••••••••••••••••••••••••••••••••259
疑难 156 如何替换文本中的字符••••••••••••••••••••••••••••••••••••260
批量将指定字符替换为其他字符•••••••••••••••••••••261
疑难 157 如何判断字符串中是否包含某个字符•••••••••••••••••••••••••••••261
FIND 函数判断字符串包含子字符串的 4 种方法••••••••••••••••••262
替换法判断包含字符••••••••••••••••••••••••••••••••••••••••263
COUNTIF 函数判断包含字符••••••••••••••••••••••••••••••••••263
疑难 158 如何查找某字符在字符串中第 N 次出现的位置•••••••••••••••••••••••263
使用替换法查询第 N 次出现的字符•••••••••••••264
疑难 159 如何提取包含特定字符的所有记录•••••••••••••••••••••••••••••••264
提取包含指定字符的记录•••••••••••••••••••••••••••••••••265
疑难 160 如何计算包含字符的个数••••••••••••••••••••••••••••••••••••265
使用替换法统计字符个数••••••••••••••••••••••••••••••••••••••••••••••266
疑难 161 如何在特定位置插入字符串•••••••••••••••••••••••••••••••••267
使用替换零个字符的方法在字符串中插入新字符•••••••••••••••••267
5.3 比较与转换•••••••••••••••••••••••••••••• 268
疑难 162 如何改变英文字母大小写••••••••••••••••••••••••••••268
改变字母大小写的 3 个函数••••••••••••••••••••269
疑难 163 如何对比两个文本大小写是否一致•••••••••••••••••••••••269
区分大小写比较文本••••••••••••••••••••••••••••••••••••••270
疑难 164 如何转换单双字节字符•••••••••••••••••••••••270
转换单、双字节字符的两个函数••••••••••••••••••••••••••••••••••••271
疑难 165 如何查询汉字区位码•••••••••••••••••••••••••••••••••271
汉字区位码的推算方法••••••••••••••••••••••••••••••••••••272
疑难 166 如何制作汉字 Unicode 编码表••••••••••••••••••••••••••••••••••••••273
5.4 数字变幻••••••••••••••••••••••••••••••••••••••••••••••275
疑难 167 如何加密手机号码••••••••••••••••••••••••••••••••275
替换指定位置和长度的字符••••••••••••••••••••••••••••••••••••275
疑难 168 如何升级电话号码位数••••••••••••••••••••••••••275
根据条件升级电话号码•••••••••••••••••••••••••••••••••••••••••••276
疑难 169 如何将数字接上英文序数后缀••••••••••••••••••••••••••••••••••••••277
数字转换为带英文序数后缀的文本•••••••••••••••••••••••••••277
数字转换为英文序数添加后缀的规则•••••••••••••••••••278
疑难 170 如何批量修改 IP 地址格式••••••••••••••••••••••••••••••••••••••••278
用函数分列法规范 IP 地址格式•••••••••••••••••••••••••••279
疑难 171 如何为数组设置上、下限••••••••••••••••••••••••279
为数组设置上、下限的通用公式••••••••••••280
疑难 172 如何将阿拉伯数字转为人民币大写金额••••••••••••••••••••••••••280
将数字转为人民币大写金额••••••••••••••••••••••••••••••••••281
中文大写金额的填写规则•••••••••••••••••••••••••••••••••••••••••••••281
疑难 173 如何将金额分列填充到各个级位••••••••••••••••••••••••••••••••••••282
金额分列填充••••••••••••••••••••••••••••••••••••••••••••••••••282
疑难 174 如何合并计算商品库存数量剩余几箱几支••••••••••••••••••••••••••••283
“X 箱+Y 支”格式的数据汇总•••••••••••••••••••••••••••••••••••••284
疑难 175 如何根据图书目录的大纲级别制作分级编码••••••••••••••284
根据不同列按级别进行编码••••••••••••••••••••••••••••285
疑难 176 如何根据图书目录编码制作分级显示•••••••••••••••••••286
根据不同级别编码分列显示•••••••••••••••••••••••286
5.5 练习与思考••••••••••••••••••••••••••••••••••••••••••••287
第 6 章日期与时间处理•••••••••••••••••••••••••••••••••288
6.1 日期推算••••••••••••••••••••••••••••••••288
疑难 177 如何获取日期的年月日要素•••••••••••••••••••••••••••••288
获取日期的年月日•••••••••••••••••••••••••••••••••••••••••••••289
疑难 178 如何推算某个日期所在的月首和月末日期•••••••••••••••••••••290
DATE 函数返回月首、月末日期••••••••••••••••••••••••290
EOMONTH 函数计算月首、月末日期•••••••••••••••••••••291
疑难 179 如何推算某个日期所在季度的季首和季末日期••••••••••••••••••291
计算季度首、季末日期••••••••••292
疑难 180 如何计算两个日期相隔的年、月、天数•••••••••••••••••••••••••••••••••293
DATEDIF 函数计算日期间隔时长••••••••••••••••293
疑难 181 如何推算与某个日期相隔数月的日期••••••••••••••••••••••••••294
EDATE 与 DATE 函数推算数月之后的日期差异•••••••••••••••••••••294
疑难 182 如何推算职工的退休日期••••••••••••••••••••••••••••••••••••••295
根据出生日期推算退休日期•••••••••••••••••••••••••••••••••••••••••••••295
疑难 183 如何判断某个日期在第几季度•••••••••••••••••••••296
推算日期归属季度••••••••••••••••••••••••••••••••••••296
疑难 184 如何判断某个日期在上旬、中旬还是下旬••••••••••••••••••••••297
判断日期上旬、中旬、下旬的 3 种解法•••••••••••••••••298
疑难 185 如何将 M/D/YYYY 类型的伪日期转换成规范日期•••••••••••••••••••••298
规范月日年类型的伪日期•••••••••••••••••••••••••••••••••••••••298
疑难 186 如何实现生日提醒功能••••••••••••••••••••••••••••••••••••299
实现生日提醒•••••••••••••••••••••••••••••••••••••299
疑难 187 如何按某年份的季度分摊费用•••••••••••••••••••••••••••••••••299
设置日期的上、下限，计算时段归属••••••••••••300
疑难 188 如何根据出生日期得到星座•••••••••••••••••••••••••••••••300
根据生日查询星座••••••••••••••••••••••••••••301
疑难 189 如何根据公历日期返回农历生肖•••••••••••••••••301
根据公历生日查询农历生肖•••••••••••••••••••••302
6.2 时间计算•••••••••••••••••••••••••••••••••••••••••••••••••••••••••••• 303
疑难 190 如何获取时间的时、分、秒要素••••••••••••••••••••••303
从时间数据中提取时、分、秒要素•••••••••••••••••••••303
疑难 191 如何返回超过 24 小时的时间•••••••••••••••••••••••••••••••••••••304
计算超过 24 小时的时长••••••••••••••••••••••••••••••••••••304
疑难 192 如何将十进制数时间转换为标准时间格式••••••••••••••••••••••••304
转换十进制数字为标准时间••••••••••••••••305
转换用小数表示分钟的“伪时间”••••••••••••••••••305
疑难 193 如何提取时间中的毫秒数•••••••••••••••••••••••••••••••••••••••••••306
提取时间数据中的毫秒••••••••••••••••••••••••••••••••••••••••••306
疑难 194 如何计算加班的小时数••••••••••••••••••••••••••••••••306
计算加班小时数••••••••••••••••••••••••••••••••••••••••••••••307
疑难 195 如何计算列车运行时长••••••••••••••••••••••307
跨零点计算两站点列车运行时间••••••••••••••••••••••••308
疑难 196 如何统计设备开启后在某个月度中的运行时长•••••••••••••••••••••••308
设置数组上、下限在跨时段统计中的应用••••••••••••••••••••••309
6.3 星期与工作日••••••••••••••••••••••••••••••••••••••••••••••••••••••••• 310
疑难 197 如何判断某个日期是否为周末••••••••••••••••••••••••••••••••310
MOD 函数判断星期几••••••••••••••••••••••••••••••••••••••310
WEEKDAY 函数判断星期几•••••••••••••••••••••••••••••••••311
疑难 198 如何计算离职员工最后一个月的实际工作天数•••••••••••••••311
NETWORKDAYS.INTL 函数计算任意工作日•••••••••••••••••••••••••312
疑难 199 如何计算两个日期间星期日的天数••••••••••••••••••••••••••••••••313
计算某时段内星期几的天数••••••••••••••••••••314
疑难 200 如何计算某月按周末单双号休息的休假天数•••••••••••••••••••314
构建某月日期系列的 3 种方法•••••••••••••••••••••••••••••315
疑难 201 如何根据购销合同签订日期推算交货日期•••••••••••••••••••315
WORKDAY.INTL 函数推算若干个工作日后的到期日••••••••••••••316
疑难 202 如何推算某个日期处于当年中的第几周•••••••••••••••••••316
计算指定日期的周数•••••••••••••••••••••••••••••••••••••••317
ISO 周数 ?••••••••••••••••••••••••••••••••••••••••••••••••317
疑难 203 如何推算某年母亲节的日期••••••••••••••••••••••••••••••318
推算母亲节日期的两种方法••••••••••••••••••••••••••••318
推算某年第 m 个星期 n 的日期通用公式•••••••••••••••••••••••••••••319
疑难 204 如何推算某年第 n 周的日期范围••••••••••••••••••••••••••••••••319
推算某年第 n 周的日期范围•••••••••••••••••320
使用 WEEKDAY 函数推算第 n 周日期范围••••••••••••••••••••••••320
6.4 练习与思考••••••••••••••••••••••••••••••••••••••••••••321
第 7 章其他计算•••••••••••••••••••••••••••••••••••••••••••••••••••322
7.1 获取信息•••••••••••••••••••••••••••••322
疑难 205 如何获取文件所在的路径••••••••••••••••••••••••••••••••••322
获取单元格位置、格式、内容信息••••••••••••••••••••323
CELL 函数无法正确返回文件路径的原因••••••••••••••••••••••324
疑难 206 如何检测错误值类型并返回错误分析信息•••••••••••••••••••••••••••324
获取错误值类型信息•••••••••••••••••••••••••••••••••••••••••••••••••325
疑难 207 如何获取与计算机操作环境相关的信息•••••••••••••••••••325
获取计算机操作环境信息••••••••••••••••••••••••••••••••••••••••326
疑难 208 如何在公式中添加注解••••••••••••••••••••••••••••••••••••••••••327
使用 N、T 函数过滤文本、数值•••••••••••••••••••••••••327
7.2 数值舍入•••••••••••••••••••••••••••••••••••••••••328
疑难 209 如何对数值进行取整••••••••••••••••••••••••••••••••••••••••••••••328
数值向下舍入取整的 4 种方法••••••••••••••••••••••••••••••••••••329
取整函数之间的区别•••••••••••••••••••••••••••••••••••••329
疑难 210 如何按照四舍六入五单双规则修约数值•••••••••••••••••••••••••329
四舍六入五单双法修约••••••••••••••••••••••••••••••330
疑难 211 如何根据货物数量计算所需纸箱数•••••••••••••••••••••••••331
数值向上舍入取整的两种方法•••••••••••••••••••331
疑难 212 如何根据工资预备各种面额的钞票•••••••••••••••••••••332
根据工资准备各面额钞票•••••••••••••••••••••••332
疑难 213 如何将不足 0.25 舍弃且超过 0.75 进位其余变为 0.5••••••••••••••••••333
数值按 0.5 舍入•••••••••••••••••••••••••••••••••••••••••••••333
疑难 214 如何按四舍五入法将数值保留 n 位有效数字••••••••••••••••••••333
按指定位数保留有效数字••••••••••••••••••••••••••••334
7.3 数学计算•••••••••••••••••••••••••••••••••••••••••• 334
疑难 215 如何进行开方运算•••••••••••••••••••••••334
计算数值开方••••••••••••••••••••••••••••••••••••••••••••••••••••••335
疑难 216 如何计算长方体体积••••••••••••••••••••••••••••••••••••••••••335
计算数组元素之间的乘积•••••••••••••••••••••••••••••••335
疑难 217 如何将正整数各数字循环累加到只剩一位数••••••••••••••••••336
对 9 求余法妙算各数位循环累加结果•••••••••••••••••••••••••••336
疑难 218 如何转换角度与弧度••••••••••••••••••••••••••••••••••••••••••••••337
角度与弧度转换•••••••••••••••••••••••••••••••••••••••337
疑难 219 如何根据视距及倾斜角计算水平距离••••••••••••••••••••••••••337
根据视距、倾斜角计算水平距离••••••••••••••••338
疑难 220 如何根据两点坐标求方位角•••••••••••••••••••••••••••338
根据两点坐标计算方位角••••••••••••••••••••••••••••••••••••••••339
疑难 221 如何将十进制数转换为七进制数•••••••••••••••••••••••••340
进制转换原理•••••••••••••••••••••••••••••••••••••••••••••340
十进制数转换为 R 进制数的通用公式•••••••••••••••••••••••••••340
疑难 222 如何将数组中的数值进行累加生成新的数组••••••••••••••••••••342
MMULT 构建累加数组••••••••••••••••••••••••••••••••••••343
疑难 223 如何求解多元一次联立方程••••••••••••••••••••••••••••••••343
矩阵算法解多元一次方程组•••••••••••••••••••••••••••••••••••344
疑难 224 如何根据 LED 色坐标统计某色区内点的数量•••••••••••••••••••344
矢量叉积法判断一个点在矢量的右侧••••••••••••••••••345
判断单点 M 在凸四边形内部••••••••••••••••••••••••••••••345
计算凸四边形内部点的数量•••••••••••••••••••••••••••••345
疑难 225 如何用位异或运算制作简单密钥••••••••••••••••••••••••••346
位异或运算加密解密原理••••••••••••••••••••••••••••347
位运算系列函数简介•••••••••••••••••••••••••••••••347
位异或运算制作简单密钥•••••••••••••••••••••348
疑难 226 如何计算投入产出表的直接和完全消耗系数矩阵•••••••••••••••••••348
MUNIT 函数生成单位矩阵••••••••••••••••••••••••••••••••••••349
投入产出表简介•••••••••••••••••••••••••••••••••••••••••••••349
7.4 随机计算•••••••••••••••••••••••• 350
疑难 227 如何制作加减乘除随机练习题•••••••••••••••••••••••••••350
生成随机加减乘除题目••••••••••••••••••••••••••••••••••••••••••351
疑难 228 如何生成年份不变的随机日期•••••••••••••••••••••••••••351
生成年份不变的随机日期•••••••••••••••••••••••••••••352
疑难 229 如何产生一组设定超限个数及范围的随机数••••••••••••••••••••••••••352
产生限定个数及范围的随机数••••••••••••••••••••••••••••••••••••••••••353
疑难 230 如何产生一组正态分布的随机数•••••••••••••••••••••••••••••••••••••••••••••••353
产生正态分布随机数••••••••••••••••••••••••••••••••••••••••••••354
疑难 231 如何产生一组对称区间且不包含 0 值的随机数•••••••••••••••••••354
产生区间对称且不含 0 值的随机数的通用公式••••••••••••••••••••354
疑难 232 如何产生一组不重复的随机整数••••••••••••••••••••••••••355
产生不重复随机数的两种方法•••••••••••••••••••••••••••355
疑难 233 如何产生一组随机的英文字母•••••••••••••••••••••••••••••356
产生随机的英文字母•••••••••••••••••••••••••••••••••356
疑难 234 如何根据指定平均值产生随机数•••••••••••••••••••357
产生指定均值的随机数•••••••••••••••••••••••••••••••••••••••••357
7.5 财务金融••••••••••••••••••••••••••••••••••••••••••••357
疑难 235 如何制作按揭贷款计算器•••••••••••••••••••••••••••358
根据利率计算分期付款额••••••••••••••••••••••••••••••••359
疑难 236 如何制作养老金投资计算器••••••••••••••••••••••••••••••••••••••••359
计算等额分期付款的利息•••••••••••••••••••••••••••360
疑难 237 如何用直线法计算固定资产折旧••••••••••••••••••••••••••••••360
资产线性折旧计算••••••••••••••••••••••••••••••••••••••••••••••••361
疑难 238 如何用双倍余额递减法计算固定资产折旧•••••••••••••••••361
双倍余额递减法计算资产折旧••••••••••••••••••••••••••••••362
疑难 239 如何用年数总和法计算固定资产折旧•••••••••••••••••••••••••363
年数总和法折旧计算••••••••••••••••••••••••••••••••••••••••••••••••364
疑难 240 如何根据等比数列首项及前 n 项之和求公比••••••••••••••••••••364
RATE 函数求解等比数列公比通用公式••••••••••••••••••••••••••365
计算贷款年利率••••••••••••••••••••••••••••••••••••••••••••••••••••••366
计算投资年化报酬率•••••••••••••••••••••••••••••••••••••366
疑难 241 如何按照超额累进法计算提成奖••••••••••••••••••••••••••••••••••367
超额累进制税率与速算扣除数简介••••••••••••••••••••••••••••367
速算扣除数法计算超额累进税额通用公式•••••••••••••••••••••••••368
速算扣除数计算通用公式•••••••••••••••••••••••••••••••••••••••••••368
7.6 练习与思考••••••••••••••••••••••••••••••368
第 8 章循环引用••••••••••••••••••••••••••••••••••••••••••••370
8.1 循环引用现象与迭代计算原理••••••••••••••••••••••••••••••••••••••370
疑难 242 为什么新产品查找进价时会出现循环引用警告••••••••••••••••••••370
公式引用自身单元格不一定是循环引用•••••••••••••••••••••371
检查和纠正意外的循环引用•••••••••••••••••••••••372
疑难 243 如何让输入的值累加到另一单元格•••••••••••••••••••••••372
循环引用与迭代计算模型•••••••••••••••••••••••••••••••373
能否直接使用名称完成迭代计算•••••••••••••••••••••••••••374
最多迭代次数与最大误差的作用••••••••••••••••••••••••••••374
迭代计算设置的作用范围••••••••••••••••••••••••374
疑难 244 如何让“累加”计算器清零••••••••••••••••••••••••••••••••374
设置启动开关与迭代变量初始值•••••••••••••••••••••••375
如何修正刷新引起的累加问题•••••••••••••••••••••••••••••375
表单控件与 ActiveX 控件复选框的区别•••••••••••••••••••••••••••••••••••376
疑难 245 如何遍历值班表合并查询结果•••••••••••••••••••••••••••376
使用计数器遍历数据•••••••••••••••••••••••••••••••••••••••••••••377
疑难 246 为什么计数器移位后引用公式的计算结果不同•••••••••••••••••••••377
循环引用模式下的单元格运算顺序•••••••••••••••••••••••••••••378
8.2 应用案例详解••••••••••••••••••••••• 378
疑难 247 如何逆序返回字符串•••••••••••••••••••••••••••••••••••••••••••••••379
逆序遍历与合并字符•••••••••••••••••••••••••••••••••••••••••••379
巧用字符长度做计数器•••••••••••••••••••••••••380
疑难 248 如何获取一组数字串的交集、并集和补集••••••••••••••••••••••••••••••380
数字交集、并集和补集的判断与提取••••••••••••••••••••••381
疑难 249 如何剔除重复后合并查询结果••••••••••••••••••••••••••••381
字符串中的重复数据判别与合并••••••••••••••••••••••382
疑难 250 如何提取规格描述字符串中的尺寸信息••••••••••••••••••••••382
提取字符串中的多个数字子字符串••••••••••••••••••383
疑难 251 如何将单元格历次输入的数据记录到其他单元格••••••••••••••••••••••383
记录单元格历史值•••••••••••••••••••••••••••••••••••••••••••••••••384
疑难 252 如何根据税后月工资倒推税前应发金额••••••••••••••••••••••••••••••384
模拟单变量求解功能••••••••••••••••••••••••••••••••••••••••••385
疑难 253 如何根据税后年终奖倒推税前应发金额•••••••••••••••••••••••••••••••385
查表法计算年终奖个税•••••••••••••••••••••••••••••386
年终奖个税通用公式•••••••••••••••••••••••••••••••••••••388
疑难 254 如何根据预算收入和利润推算地价及增值税额••••••••••••••••••388
循环引用倒推地价及增值税•••••••••••••••••••••389
疑难 255 如何列举多种钢筋下料方案••••••••••••••••••••••••••••••••••••••••389
模拟规划求解功能枚举多组方案•••••••••••••••••••••••••••••390
疑难 256 如何求解多组规定总额的数据不重复组合••••••••••••••••••••••••••391
模拟规划求解规定总值明细••••••••••••••••••••••••••••••••392
求解规定总额的明细组成•••••••••••••••••••••••••••••••••393
如何求解最接近规定总额的明细组成•••••••••••••••••••••••••••393
8.3 练习与思考•••••••••••••••••••••••••••••••••••••• 394
第 9 章函数与其他功能组合应用•••••••••••••••••••••••••••••395
9.1 数据验证与条件格式••••••••••••••••••••••••••••••••••••••••••••••••••395
疑难 257 如何防止输入重复姓名和身份证号码••••••••••••••••••••••••395
设置数据验证，防止重复录入••••••••••••••••••••••396
疑难 258 如何快速输入不变的当前日期和时间••••••••••••••••••397
快捷键法输入当前日期、时间•••••••••••••••••••••••••397
下拉菜单法输入当前日期、时间••••••••••••••••••••••••397
疑难 259 如何让新增数据自动添加到下拉菜单中•••••••••••••398
动态引用制作下拉菜单••••••••••••••••••••••••••••••399
“表格”动态区域制作下拉菜单••••••••••••••••••••••••••••400
忽略空值与提供下拉箭头选项••••••••••••••••••••••••••••••••••401
疑难 260 如何制作随输入缩小范围的药品名下拉菜单••••••••••••••••••••401
类似记忆式键入的下拉菜单•••••••••••••••••••••••••••••••••••••••••••••402
疑难 261 如何设置随输入自动追加的不重复下拉菜单•••••••••••••••••••403
制作既可输入又可选择的下拉菜单•••••••••••••••••403
生成唯一值列表? •••••••••••••••••••••••••••••••••••••••••••••••404
疑难 262 如何制作二级级联下拉菜单•••••••••••••••••••••••••••••••404
定义名称法制作二级级联下拉菜单•••••••••••••••••••••••••405
“表格”结构化引用法制作二级级联下拉菜单••••••••••••••••••••••406
OFFSET 函数动态引用二维数据源制作二级级联下拉菜单••••••••••407
OFFSET 函数动态引用一维数据源制作二级级联下拉菜单•••••••••••••••408
疑难 263 如何制作省、市、区县三级级联下拉菜单••••••••••••••••••••••409
混合数据表制作三级级联下拉菜单•••••••••••••••••••••••••••410
双二维表法制作三级级联下拉菜单••••••••••••••••••••••••••••••412
整理三级级联菜单数据源的两种方式•••••••••••••••••••••414
疑难 264 如何标识日记账与银行对账单不相符的金额••••••••••••••••••••••414
在条件格式中文字标识多余数据••••••••••••••••••••••••••••415
疑难 265 如何标识 OFFSET 函数返回区域•••••••••••••••••••••••••••••••416
标识归属特定区域的单元格•••••••••••••••••••••••416
疑难 266 如何标识回弹数据中 3 个较大值和 3 个较小值•••••••••••••••••417
加权比较法标识含重复数据的极值•••••••••••••••••417
加权比较法原理••••••••••••••••••••••••••••••••••••••••418
疑难 267 如何设计标识周末的简易工作日记表••••••••••••••••••••••418
标识周末日期••••••••••••••••••••••••••••••••••••••••••422
使用分类汇总批量添加标题行技巧••••••••••••••422
9.2 数据透视与图表应用•••••••••••••••••••••••••••••••••••••••••••••••••••422
疑难 268 如何对数据透视表按半小时进行组合•••••••••••••••••••••••••422
辅助列法制作透视表组合字段•••••••••••••••••••••••••423
疑难 269 如何在公式中引用透视表中的汇总数据•••••••••••••••••••••••••••••••424
获取透视表中的数据•••••••••••••••••••••••••••••••••••••••••••424
Excel 2000 版 GETPIVOTDATA 函数通用公式••••••••••••••••••425
疑难 270 如何根据给定多个条件进行大数据量汇总•••••••••••••••••••••••426
透视表法多条件求和••••••••••••••••••••••••••••••••426
使用透视表缓解公式速度瓶颈•••••••••••••••••••428
疑难 271 如何根据客流登记明细表分析销售顾问的战斗力••••••••••••••••••••••429
动态引用名称作为透视表数据源••••••••••••••••••••••••429
疑难 272 如何制作可随选择区域变化的市场分布饼图•••••••••••••••••••431
制作动态饼图?••••••••••••••••••••••••••••••••••••••••••••••••••••431
图表中的 SERIES 公式•••••••••••••••••••••••••••••••••434
制作数据透视图?•••••••••••••••••••••••••••••••435
疑难 273 如何制作汽车销量在前 5 名的对比条形图••••••••••••••••436
使用辅助列制作图表坐标轴标签••••••••••••••••••••437
疑难 274 如何制作滚动周期的计划与实际对比进度图••••••••••••••••••439
制作固定滚动周期的动态柱形图•••••••••••••••••••440
疑难 275 如何使用分离的数据源制作计划与实际对比图•••••••••••••••••••442
内存数组构建动态图表数据源•••••••••••••••••••••••••••443
9.3 数据库函数与高级筛选••••••••••••••••••••••••••••• 444
疑难 276 如何查询某商品在某日期之前的最新进货价格••••••••••••••••••••••444
数据库函数简介•••••••••••••••••••••••••••••••••••••••••••••••••••445
数据库函数语法••••••••••••••••••••••••••••••••••••••••••••••••••••••446
数据库函数条件参数设置要点•••••••••••••••••••••••••446
在条件区域使用公式 4 忌••••••••••••••••••••••••••••••••••••••••••••••447
疑难 277 如何计算日考勤两次员工某天在岗时长•••••••••••••••••••••••••••448
数据库函数求按指定条件求单个在岗时长••••••••••••••••••449
IFERROR 函数与倒数巧妙配合过滤不满足条件数据•••••••••••449
计算日考勤两次员工某天上班时段内在岗时长•••••••••••••449
疑难 278 如何计算日考勤 4 次员工某天在岗时长•••••••••••••••••••••••450
计算日考勤两次员工某天上班时段内在岗时长•••••••••••••••••••••450
疑难 279 如何筛选销售额超过两万元的明细记录•••••••••••••••••••••••••451
使用公式作为高级筛选条件••••••••••••••••••••••••••••••••••452
疑难 280 如何删除包含小写字母的数据行••••••••••••••••••••••••••••••••454
区分大小写的数据筛选••••••••••••••••••••••••••••••454
9.4 模拟运算表妙用••••••••••••••••••••••••••••••••••••••••• 455
疑难 281 如何分析各门店销售总额、均值、最高额及冠军••••••••••••••••••••••455
模拟运算表简介•••••••••••••••••••••••••••••••••••••••••••••••••••••456
单变量多表头公式模拟运算表•••••••••••••••457
单变量单表头公式模拟运算表••••••••••••••••••••••••••457
模拟运算表的特点••••••••••••••••••••••••••••••••••••••••458
疑难 282 如何根据不同长宽尺寸批量套算塑钢门窗型材费•••••••••••459
模拟运算表批量替代复杂的过程计算 ? ••••••••••••••••••••••••••460
图解模拟运算表模型•••••••••••••••••••••••••461
模拟运算表设计步骤••••••••••••••••••••••••••461
疑难 283 如何列出日考勤两次的多个员工每天在岗时长表•••••••••••••••••••461
数据库函数的局限••••••••••••••••••••••••••••••••••••••••••••••463
模拟运算表巧妙配合突破数据库函数限制••••••••••••••463
列出日考勤 4 次的多个员工每天在岗时长表••••••••••••••••463
疑难 284 如何根据股票的月收益率计算年化收益率•••••••••••••••••••464
DPRODUCT 函数条件连乘计算年化收益率••••••••••••••••••••465
PRODUCT 函数连乘计算年化收益率•••••••••••••••••••••••466
疑难 285 如何根据税后年终奖批量倒推税前应发金额•••••••••••••••••••••466
模拟运算表配合循环引用批量执行迭代运算••••••••••••••••••467
疑难 286 如何批量计算工商注册号校验码••••••••••••••••••••••••••••••••••••468
递归求余计算工商注册号校验码••••••••••••••••••••••470
9.5 单变量求解与规划求解•••••••••••••••••••••••••••470
疑难 287 如何已知税后收入求解税前应发金额••••••••••••••••••••470
单变量求解推算税前应发金额•••••••••••••••••••••••471
如何理解单变量求解模型••••••••••••••••••••••472
单变量求解一元 n 次方程•••••••••••••••••••••••••••••••••••••472
疑难 288 如何根据货品数量与纸箱规格制定装货策略••••••••••••••••••••••472
规划求解最小值优化耗材方案•••••••••••••••••••••••••••••••••473
为何“数据”选项卡中没有“规划求解”按钮•••••••••••••••••••••••474
如何理解规划求解优化模型•••••••••••••••••••••••••••••••••••475
如何理解规划求解的参数••••••••••••••••••••••••••••••••••••••••••476
疑难 289 如何合理安排服务员值班•••••••••••••••••••••••••••••••••••••••476
规划求解最小值优化排班方案•••••••••••••••••••••••••••••••••••••477
疑难 290 如何优化年终奖方案合理避税••••••••••••••••••••••••••••••••478
规划求解最小值优化年终奖方案••••••••••••••••••••••••••••••••••••••479
如何规划年薪制合理避税方案•••••••••••••••••••••••••••••480
疑难 291 如何组合消费清单进行“满五减二”最实惠••••••••••••••••••••481
规划求解优化“满五减二”优惠方案•••••••••••••••••••482
9.6 宏表函数应用案例•••••••••••••••••••••••••••••••••••••••••484
疑难 292 如何让查询结果引用自定义数字格式••••••••••••••••••••••••••••484
获取单元格数字格式代码•••••••••••••••••••••••••••••••••••••••484
如何使用 Excel 4.0 宏表函数••••••••••••••••••••••••484
宏表函数获取单元格自定义数字格式•••••••••••••••••••••485
CELL 函数提取格式代码••••••••••••••••••••••••••••••••••••••485
疑难 293 如何获取预算表单元格中的计算式••••••••••••••••••••••••••485
宏表函数获取单元格中的计算式••••••••••••••••••••••••486
新增函数 FORMULATEXT 获取单元格中的计算式•••••••••••487
疑难 294 如何转换包含备注的文本公式为计算结果•••••••••••••••••487
将文本计算式转换为计算结果•••••••••••••••••••••••••••••487
疑难 295 如何用十六进制表示 IP 地址••••••••••••••••••••••••••488
转换十进制 IP 地址为十六进制•••••••••••••••••••••••489
疑难 296 如何多条件汇总相同结构的多个工作表数据•••••••••••••••••••••••••••489
? 获取工作簿中各表名称••••••••••••••••••••••••••••••••••••••••490
疑难 297 如何制作工作表目录超链接导航页面•••••••••••••••••••••••491
建立工作表目录超链接••••••••••••••••••••••••••••••••••••••491
疑难 298 如何列出指定路径下所有文件名••••••••••••••••••••492
获取指定路径文件名列表?•••••••••••••••••••••••••••••••••493
9.7 练习与思考••••••••••••••••••••••••••••••••••••• 494
第 10 章综合应用实战•••••••••••••••••••••••••••••••495
10.1 职工信息管理系统设计•••••••••••••••••••••••••••••••••••••••••••••••• 495
疑难 299 如何设计职工信息管理系统的首页•••••••••••••••••••••••••495
设计职工信息管理系统首页•••••••••••••••••••••••495
疑难 300 如何设计信息录入模块•••••••••••••••••••••••••••••••••••••••••497
设计职工信息录入模块••••••••••••••••••••••••••••••497
疑难 301 如何设计信息查询和修改模块••••••••••••••••••••••••••••500
设计职工信息查询、修改模块•••••••••••••••••••••••••••••••••500
疑难 302 如何设计信息输出模块•••••••••••••••••••••••••••••••••••••••••••••502
设计职工信息输出模块••••••••••••••••••••••••••••••••••502
疑难 303 如何制作职工信息统计报表••••••••••••••••••••••••••••••••504
制作职工信息统计报表•••••••••••••••••••••••••••••••••••••504
10.2 公司收发文管理系统设计••••••••••••••••••••••••••••••••••••••• 506
疑难 304 如何制订电子文档管理方案•••••••••••••••••••••••••••506
规范管理电子文档••••••••••••••••••••••••••••••••••••••••506
疑难 305 如何制作发文管理系统•••••••••••••••••••••••••••••••••••••••508
制作发文管理系统•••••••••••••••••••••••••••••••••••••••••••508
模糊查询指定目录下文件名•••••••••••••••••••••••••••511
实现文件编号提醒功能••••••••••••••••••••••••••••••511
制作美观、方便实用的超链接•••••••••••••••••••••••512
疑难 306 如何制作收文查询系统••••••••••••••••••••••••••••512
制作收文查询系统••••••••••••••••••••••••••••••••••••••••512
设置可忽略的日期查询条件•••••••••••••••••••••••••514
返回指定时段文件名列表••••••••••••••••••••••••515
可以提示及时维护的信息查询•••••••••••••••••••515
疑难 307 如何制作系统首页界面••••••••••••••••••••••••••••••••••515
制作系统首页页面•••••••••••••••••••••••••••••••••••••••••••••••••••515
在图形图片中引用单元格文字•••••••••••••••••••••••••••••••519
10.3 工资表设计••••••••••••••••••••••••••••••519
疑难 308 如何设计工资表首页••••••••••••••••••••••••••••••••••519
设计工作表首页•••••••••••••••••••••••••••519
疑难 309 如何设计工资计算表•••••••••••••••••••••••••••••••••••••••••520
设计工资计算表•••••••••••••••••••••••••••••••••••••521
疑难 310 如何设计工资条••••••••••••••••••••••••••••••••••523
设计工资条•••••••••••••••••••••••••••••••••••••523
疑难 311 如何设计工资查询表••••••••••••••••••••••••••••••••••••••••524
设计工资查询表••••••••••••••••••••••••••••••••••••••••••••••••••525
附录 A Excel 2013 规范与限制•••••••••••••••••••••••••••••••••••••527
· · · · · · (收起)