《Excel 2013 函式与公式》是2015年电子工业出版社出版的图书，作者是黄朝阳。
基本介绍
- 书名Excel 2013 函式与公式
- 作者黄朝阳
- 出版社电子工业出版社
- 出版时间2015年07月
内容简介
包括基础理论、操作技巧、分类函式套用、综合套用及开发实战，本书将系统的知识点融入于各种实战案例，在解决问题的，引导读者学习深层次原理和扩展套用思路。阅读本书，可以直接使用成熟的通用公式和案例，解决工作中的诸多疑难杂症，大大提升工作效率，且有助于提升函式与公式的理论水平和综合实战能力。
目录
第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
FREQUENCY函式统计不重複值的优缺点 165
疑难97 如何区分字母大小写统计不重複数据个数 165
用1/MMULT法求不重複值 166
3.3 极值均值 166
疑难98 如何设定提成奖金的上、下限 166
使用MAX、MIN函式设定上限或下限 167
使用MEDIAN函式设定上、下限 167
使用IF、TEXT函式设定上、下限通用公式 168
疑难99 如何按先进先出法进行商品库龄分析 168
疑难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
疑难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
疑难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
附录B常用宏表函式语法说明 529