疑难千寻千解丛书 Excel 2013 函数与公式 内容简介

《疑难千寻千解丛书 Excel 2013 函数与公式》是“疑难千寻千解”丛书之一，为读者展示运用函数与公式解决疑难问题的实战技巧。包括基础理论、操作技巧、分类函数应用、综合应用及开发实战，本书将系统的知识点融入于各种实战案例，在解决问题的同时，引导读者学习深层次原理和扩展应用思路。阅读本书，可以直接使用成熟的通用公式和案例，解决工作中的诸多疑难杂症，大大提升工作效率，且有助于提升函数与公式的理论水平和综合实战能力。

《疑难千寻千解丛书 Excel 2013 函数与公式》在函数语法描述中，采用中文表述习惯，对Excel 2013帮助文件进行了提炼和修正，帮助读者在理解函数时少走弯路。

《疑难千寻千解丛书 Excel 2013 函数与公式》包括311个案例，最后一章是设计职工信息、公文管理、工资管理系统3个大型案例。本书采取疑难描述、解决方案、操作方法、原理分析、知识扩展五个方面进行讲解，力图解决问题之外还让读者可以通晓其思路和原理。

疑难千寻千解丛书 Excel 2013 函数与公式 目录

第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

疑难千寻千解丛书 Excel 2013 函数与公式 精彩文摘

1.1 熟悉 Excel 的工作环境

本节主要解答 Excel 2013 的操作界面和常用技巧方面的问题，帮助读者快速熟悉工作环境，掌握使用函数与公式所需的基本技能。
疑难 1 如何减小功能区所占屏幕空间与 Excel 2003 版相比，自 Excel 2007 版采用功能区以来界面变化较大而 Excel 2013 屏幕上方的功能区所占据的空间较大，可见的表格区域小，特别是使用分辨率较低的计算机的用户在查阅表格内容时较为不便，如何减小其所占用的屏幕空间？

解决方案

将功能区收起，只显示选项卡或设置自动隐藏功能区。

操作方法

以下 5 种方法可以将功能区最小化：

方法 1 按【Ctrl+F1】组合键。

方法 2 双击当前选项卡标签。

方法 3 单击功能区右下角的“折叠功能区”按钮。

方法 4 用鼠标右键单击功能区，在弹出的快捷菜单中选择“折叠功能区”命令。

方法 5 单击窗口右上角的“功能区显示选项”→“显示选项卡”按钮。

使用上述 5 种方法可将功能区折叠，仅显示选项卡，通过单击任意选项卡可以显示其相应的内容，当用鼠标激活工作表区域时，功能区又将自动折叠。其中方法 1、方法 2、方法 5 同样可以用于恢复功能区命令的显示。此外，使用方法 5 还可以选择“自动隐藏功能区”命令全屏显示表格。

Excel 2013 的窗口主要分为：标题栏、功能区、编辑栏、工作表区域和状态栏几个部分。

（1）标题栏：居中显示工作簿名称；左侧为快速访问工具栏，提供用户常用的保存、撤销、重做、打印预览等命令按钮。快速访问工具栏也可以在功能区下方显示。

（2）功能区：主要由文件、开始、插入、页面布局、公式、数据、审阅、视图、开发工具等选项卡组成，用户还可以通过单击“文件”→“选项”→“自定义功能区”命令，或者用鼠标右键单击功能区，在弹出的快捷菜单中选择“自定义功能区”命令来创建新的选项卡。每个选项卡根据功能分为若干个“组” ，例如“开始”选项卡中有“剪贴板” 、 “字体” 、 “对齐方式” 、“数字” 、 “样式” 、 “单元格” 、 “编辑”等组。

（3）编辑栏：位于功能区下方，用于显示、编辑单元格中的内容；左侧为名称框，用于显示或定义选定区域的名称。

（4）工作表区域：由行标题、列标题、单元格、导航按钮、工作表标签、水平滚动条和垂直滚动条等组成。

（5）状态栏：显示单元格模式、录制宏、统计信息等，右侧为视图模式切换按钮和显示比例调节滑块。

疑难2 如何不用设置公式实现统计结果预览

在阅读报表或检验数据时，经常需要临时性地对单元格数值进行一些求和、最大值/最小值、均值等简单的统计，Excel 2013能否提供方便快捷的计算功能吗？

解决方案

设置Excel 2013的状态栏，查看相关统计信息。

操作方法

设置Excel 2013状态栏 ？

步骤1 用鼠标右键单击状态栏，在弹出的快捷菜单中勾选“平均值”、“计数”、“数值计数”、“最小值”、“最大值”、“求和”等选项。

步骤2 选中表格中B2：B8区域，状态栏将显示该区域的上述统计信息，用户可以通过鼠标选择实现不同区域统计结果的预览，而无须设置任何公式。其中，“计数”选项统计的是所选区域中非空单元格的个数，“数值计数”只统计含有数值的单元格个数。

疑难3 如何在一个工作簿中管理多张表格

在统计和管理数据时，经常需要按月收集或提供数据，如果将一年的数据按月分成12个工作簿来管理，则对其操作和统计都较为不便，如何才能实现在一个工作簿中管理全年各个月份的表格数据呢？

解决方案

了解Excel的对象模型，使用工作簿管理多个表格。

操作方法

步骤1 启动Excel 2013以后，默认建立一个名为“工作簿1”的文件，其中包含名为Sheet1、Sheet2、Sheet3的3个工作表。

步骤2 双击工作表标签，或右键单击工作表标签，在弹出的快捷菜单中选择“重命名”命令，即可将“Sheet1”等默认工作表名改为“1月”、“2月”等。

步骤3 单击工作表标签右侧的“新工作表”按钮，或按【Shift+F11】组合键，即可插入一个新的工作表。

步骤4 单击“文件”→“另存为”命令，将工作簿另存为“销售数据”。

重复步骤2、步骤3，建立多个工作表并重命名为不同月份，最后在表中输入相应的数据。

知识扩展

理解工作簿、工作表和单元格之间的关系 ？

Excel文件被称为“工作簿”（Workbook），它可以由多张工作表（WorkSheet）组成，在Excel 2013中每张工作表由1 048 576行×16 384列个单元格组成，比Excel 2003及早期版本的65 536行×256列个单元格扩大了许多，可以满足多数用户存储与分析数据的需求。

工作簿可以理解为一本账簿，每个工作表是账簿中的一页，并且其可以填写的表格区域要比实际的账簿大得多。

默认的新建工作簿包含3张工作表，单击“文件”→“选项”→“常规”命令，在“新建工作簿时”区域设置“包含的工作表数”，可以为1～255的整数。超过255张工作表时可以使用“新工作表”按钮建立新的工作表，一个工作簿可以包含的工作表数仅受计算机内存限制。

疑难4 为什么表格列标题显示的是数字而不是字母

为什么打开Excel之后，列标题显示的是数字1、2、3而不是字母A、B、C？如何才能恢复列标题用字母显示？

解决方案

取消“R1C1”引用样式的选项设置。

操作方法

设置R1C1引用样式 ？

步骤1 单击“文件”→“选项”→“公式”命令。

步骤2 在“使用公式”区域取消勾选“R1C1引用样式”复选框，单击“确定”按钮。

知识扩展

A1引用样式和R1C1引用样式 ？

单元格是组成工作表的最小元素，在Excel中用“地址”来表示每一个单元格，并且可以通过使用这种“地址”来引用单元格中的数据。即：以左上角单元格为坐标原点，沿水平方向向右延伸为“行”，沿垂直方向向下延伸为“列”，根据表示方法不同，引用分为A1引用样式和R1C1引用样式。

A1引用样式是使用列标为字母与行号为数字来表示单元格地址的。例如C3单元格表示C列与第3行交叉处的单元格。

R1C1引用样式是使用字母R（Row，即“行”的单词缩写）与数字来表示第几行，使用字母C（Column，即“列”的单词缩写）与数字来表示第几列的，例如R4C3单元格表示的是第4行与第3列交叉处的单元格，即C4单元格；R3C4表示的是第3行与第4列交叉处的单元格，即D3单元格。

疑难5 如何根据开头字母的提示快速输入函数名称

Excel 2013的内置函数有数百个，对于初学者来说，记函数名是一件很令人头疼的事情，有时候会因为不小心输错函数名，比如将“SUBSTITUTE”输成“SUBTSTITUTE”等，从而得到#NAME？错误值。如何根据输入函数开头字母给出的提示实现快速输入函数名称？

解决方案

开启“公式记忆式键入”功能，实现边输入边查询函数的功能。

操作方法

开启“公式记忆式键入”功能的两种方法 ？

方法1 如图1-8所示，单击“文件”→“选项”→“公式”命令，在“使用公式”区域中勾选“公式记忆式键入”复选框，单击“确定”按钮。

方法2 在输入或编辑公式时，按【Alt+↓】组合键，可以开启或关闭“公式记忆式键入”功能。

开启“公式记忆式键入”功能后，在单元格中输入“=I”，将自动显示以字母I开头的函数，如IF、IFERROR、IMABS等，并在函数的一旁显示函数功能的简要信息。随着继续输入字母的增多，列表范围逐步缩小，当输入“=if ”时，列表中只剩下IF、IFERROR和IFNA函数。用鼠标单击列表中的函数或按↑、↓方向键移动选择所需的函数，并按【Tab】键即可将函数输入到公式中。

此外，如果用户定义了名称，或者创建了“表格”，也可以使用此功能实现快速输入。相比Excel 2003及早期版本，公式记忆式键入功能帮助用户摆脱了记忆函数名称的苦恼，极大地提高了公式输入的效率和准确率。