Excel 2016公式与函数应用大全PDF电子书下载

第1章 公式基础 2

1.1 认识公式 2

1.1.1 公式的组成 2

实战实例：根据销售额和销售单价计算总销售额 2

1.1.2 公式中运算符的使用 3

实战实例：计算每月总销售额 3

1.1.3 公式中“？”“＊”通配符的使用 4

实战实例：统计风衣类服装的总销售额 4

1.2 公式输入与修改 5

1.2.1 公式输入 5

实战实例：统计产品总销售额 5

1.2.2 公式修改 6

实战实例：重新计算每月总销售额 6

1.3 公式复制与填充 7

1.3.1 公式复制 7

实战实例：统计考核总成绩 8

1.3.2 公式填充 8

实战实例：统计每一季度的总销售量 9

1.4 公式保护、隐藏与显示 10

1.4.1 公式保护 10

实战实例：保护工作表中的公式 10

1.4.2 公式隐藏 12

实战实例：隐藏工作表中的公式 12

1.4.3 公式显示 14

实战实例：显示工作表中所有的公式 14

1.5 改变公式的计算方式 15

1.5.1 将公式结果转化为数值 15

实战实例：将公式结果转化为数值 15

1.5.2 将公式转换为文本 16

实战实例：将公式转换为文本 16

1.6 为什么公式正确却得到不正确的结果 17

第2章 函数基础 19

2.1 认识函数 19

2.1.1 函数的组成 19

实战实例：计算每位学生的总分 19

2.1.2 Excel函数类别 20

实战实例：判断各销售员月销量是否达标 20

2.2 函数输入 21

2.2.1 手动输入 21

实战实例：统计员工平均工资 22

2.2.2 使用“fx”按钮输入 23

实战实例：使用“fx”按钮输入公式 23

2.2.3 使用“函数自动完成”功能输入 24

实战实例：使用“函数自动完成”功能输入 24

2.2.4 使用“插入函数”功能输入 26

实战实例：使用“插入函数”功能输入 26

2.3 函数修改与删除 27

2.3.1 双击公式单元格修改函数 27

实战实例：双击公式单元格修改函数 27

2.3.2 在“编辑栏”中修改函数 28

实战实例：在“编辑栏”中修改函数 28

2.3.3 使用单元格引用颜色标识来修改引用单元格 29

实战实例：拖动颜色标识修改公式 29

2.3.4 删除公式中不要的函数 30

实战实例：删除公式中不要的函数 31

2.4 嵌套函数 31

实战实例1：根据平均销量判断业绩突出销售员 32

实战实例2：查找指定消费者是否发放奖品 33

第3章 单元格的引用 35

3.1 名称定义和使用 35

3.1.1 快速定义名称 35

实战实例1：使用“定义名称”功能定义名称 35

实战实例2：在名称框中直接创建名称 36

3.1.2 修改名称 37

实战实例：定义名称之后修改其引用位置 37

3.1.3 编辑名称 38

实战实例：编辑名称 38

3.1.4 删除名称 38

实战实例：删除不再使用的名称 39

3.1.5 定义公式名称 39

实战实例：定义公式名称 39

3.1.6 创建动态名称 40

实战实例：创建动态名称 40

3.1.7 应用名称 42

实战实例：在公式中应用名称 42

3.2 单元格引用 43

3.2.1 相对引用 43

实战实例：在公式中引用相对数据源 43

3.2.2 绝对引用 44

实战实例：在公式中引用绝对数据源 44

3.2.3 混合引用 45

实战实例：在公式中引用混合数据源 45

3.2.4 引用当前工作表之外的单元格 46

实战实例：引用当前工作表之外的单元格 46

3.2.5 跨工作簿引用 47

实战实例：跨工作簿引用单元格 47

3.2.6 引用多个工作表中的同一单元格 48

实战实例：引用多个工作表中的同一单元格 48

3.2.7 使用【F4】键互换引用类型 49

实战实例：使用【F4】键互换引用类型 50

第4章 数组的应用 52

4.1 数组类型 52

4.1.1 一维水平数组 52

实战实例：一维水平数组 52

4.1.2 一维垂直数组 53

实战实例：一维垂直数组 53

4.1.3 二维数组 53

实战实例：二维数组 54

4.1.4 常量数组在公式中的应用 54

实战实例：常量数组在公式中的应用 54

4.1.5 内存数组 55

实战实例：内存数组 55

4.2 使用数组公式 56

4.2.1 普通公式与数组公式 56

实战实例：使用数组公式计算销售额 56

4.2.2 单个单元格数组公式与多个单元格数组公式 57

实战实例1：使用数组公式计算1到100的自然数之和 57

实战实例2：一次性计算总分 58

4.3 多项计算与数组公式的区别 58

实战实例1：根据销量和销售单价计算总销售额 59

实战实例2：统计总销售金额 59

4.4 修改或删除数组公式 60

实战实例：修改数组公式 60

第5章 公式审核与修正 63

5.1 公式检测与审核 63

5.1.1 使用“监视窗口”监视数据 63

实战实例：使用“监视窗口”监视数据 63

5.1.2 使用“错误检查”来检查公式 65

实战实例：使用“错误检查”来检查公式 65

5.1.3 使用“追踪错误”来追踪公式错误 66

实战实例：使用“追踪错误”来追踪公式错误 66

5.1.4 使用“追踪引用单元格” 66

实战实例：使用“追踪引用单元格” 66

5.1.5 使用“追踪从属单元格” 67

实战实例：使用“追踪从属单元格” 68

5.1.6 移去追踪箭头 68

实战实例：移去追踪箭头 68

5.2 错误公式的修正 69

5.2.1 使用“显示公式”来修正 69

实战实例：使用“显示公式”来修正 69

5.2.2 使用“公式求值”来查看公式结果 70

实战实例：使用“公式求值”来查看公式结果 70

5.2.3 使用【F9】键查看公式中部分公式的结果 71

实战实例：使用【F9】键查看公式中部分公式的结果 72

5.2.4 修正循环引用不能计算的公式 72

实战实例：修正循环引用不能计算的公式 72

5.2.5 修正公式输入的参数不对 74

实战实例：修正公式输入的参数不对 74

5.2.6 修正输入的公式中包含错误 75

实战实例：修正输入的公式中包含错误 75

5.2.7 修正公式复制后得不到正确结果 75

实战实例：修正公式复制后得不到正确结果 75

5.2.8 修正按顺序书写的公式不按顺序计算 77

实战实例：修正按顺序书写的公式不按顺序计算 77

5.2.9 修正四舍五入后计算结果不正确 78

实战实例：修正四舍五入后计算结果不正确 78

5.2.10 修正小数计算结果出错 79

实战实例：修正小数计算结果出错 79

第6章 分析与解决公式返回错误值 81

6.1 分析与解决“＃＃＃＃”错误值 81

实战实例：解决“＃＃＃＃”错误值 81

6.2 分析与解决“＃DIV／O！”错误值 82

实战实例：解决“＃DIV／O！”错误值 82

6.3 分析与解决“＃N／A”错误值 83

实战实例1：解决“＃N／A”错误值 83

实战实例2：数组公式中使用的参数的行数或列数与包含数组公式的区域的行数或列数不一致 84

6.4 分析与解决“＃NAME？”错误值 84

实战实例1：在公式中引用文本时没有加双引号 85

实战实例2：解决“＃NAME？”错误值 85

实战实例3：区域引用中漏掉了冒号“：” 86

6.5 分析与解决“＃NUM！”错误值的解 87

实战实例：解决“＃NUM！”错误值 87

6.6 分析与解决“＃VALUE！”错误值 88

实战实例1：在公式中将文本类型的数据参与了数值运算 88

实战实例2：解决“＃VALUE！”错误值 89

6.7 分析与解决“＃REF！”错误值 89

实战实例：解决“＃REF！”错误值 90

6.8 分析与解决“＃NULL！”错误值 91

实战实例：解决“＃NULL！”错误值 91

第7章 逻辑函数 93

7.1 逻辑判断函数 93

7.1.1 AND：判断指定的多个条件是否全部成立 93

实战实例1：判断面试人员是否能被录用 93

实战实例2：判断是否为消费者发放赠品 94

7.1.2 OR：判断参数值是否有一个为TRUE 95

实战实例1：判断是否为员工发放奖金 95

实战实例2：判断是否为消费者发放赠品 96

7.1.3 NOT：判断指定的条件不成立 97

实战实例1：判断员工月业绩是否达标 97

实战实例2：筛选出20岁以下的应聘人员 98

7.1.4 IF：根据逻辑测试值返回指定值 99

实战实例1：判断员工本月业绩是否优秀 99

实战实例2：分区间判断业绩并返回不同结果 100

实战实例3：判断能够获得公司年终福利的员工 101

实战实例4：筛选出符合条件的员工 102

实战实例5：判断数据是否存在重复现象 103

实战实例6：根据所有销售员总销量计算单人销量是否达标 103

7.1.5 IFERROR：根据错误值返回指定值 104

实战实例：解决被除数为空值（或0值）时返回错误值问题 105

7.2 逻辑值函数 105

7.2.1 TRUE：返回逻辑值TRUE 106

实战实例：判断产品是否达标（达标的显示出TRUE） 106

7.2.2 FALSE：返回逻辑值FLASE 107

实战实例：判断产品是否达标（不达标的显示出FALSE） 107

第8章 日期与时间函数 109

8.1 返回当前日期和时间 109

8.1.1 TODAY：返回当前日期 109

实战实例1：统计实习员工的工作天数 109

实战实例2：判断会员是否升级 110

8.1.2 NOW：返回当前的日期和时间 111

实战实例：计算活动剩余时间 111

8.2 用序列号表示或计算时间和日期 112

8.2.1 YEAR：返回某日对应的年份 112

实战实例：计算员工工龄 112

8.2.2 DATE：返回特定的日期和序列号 114

实战实例1：将分列记录的日期转换为标准格式日期 114

实战实例2：统计公司产品的促销天数 115

8.2.3 DATEVALUE：将日期值从字符串转换为序列号 116

实战实例：统计借书天数 116

8.2.4 DAY：返回以序列号表示的日期中的天数 117

实战实例1：提取给定日期中的日数 117

实战实例2：按本月缺勤天数计算缺勤扣款 118

8.2.5 TIME：返回某一时间的特定小数值 119

实战实例：将给定的数值转化为12小时制 119

8.2.6 TIMEVALUE：返回由文本字符串所代表的小数值 120

实战实例：将指定时间转化为时间小数值 120

8.2.7 EOMONTH：从序列号或文本中算出指定月最后一天的序列号 120

实战实例1：统计各月份对应的月末日期 120

实战实例2：根据促销开始时间计算促销天数 122

8.2.8 MONTH：返回以序列号表示的日期中的月份 123

实战实例1：自动填写报表中月份 123

实战实例2：统计指定月份的销售额 123

8.2.9 WEEKDAY：返回指定日期对应的星期数 124

实战实例1：判断值班日期是星期几 125

实战实例2：统计周日营业额 125

8.2.10 WORKDAY：从序列号或文本中计算出指定工作日之后的日期 126

实战实例：计算交稿日期 127

8.2.11 EDATE：计算出所制定月数之前或之后的日期 127

实战实例：快速计算食品过期日期 128

8.2.12 HOUR：返回时间值的小时数 128

实战实例：确定点击某网站的时间区间 129

8.2.13 MINUTE：返回时间值的分钟数 130

实战实例：计算产品秒杀分钟数 130

8.2.14 SECOND：返回时间值的秒数 131

实战实例：计算产品秒杀的秒数 131

8.3 期间差 132

8.3.1 DAYS360：返回两日期间相差的天数（按照一年360天的算法） 132

实战实例1：计算账龄 133

实战实例2：计算还款剩余天数 133

8.3.2 NETWORKDAYS：计算某时段的工作日天数 134

实战实例：计算员工退休前的工作天数 134

8.3.3 YEARFRAC：从开始到结束日所经过的天数占全年天数的比例 135

实战实例：计算全年的盈利额 135

8.3.4 DATEDIF：用指定的单位计算起始日和结束日之间的间隔数 136

实战实例1：计算员工工龄 137

实战实例2：计算各分店开业至今的时长 137

实战实例3：动态生日提醒 138

8.4 其他日期与时间函数 139

8.4.1 WEEKNUM：返回序列号对应的一年中的第几周 139

实战实例：返回各项活动是全年中的第几周 139

8.4.2 DATESTRING：将指定日期的序列号转换为文本日期 140

实战实例：将指定日期的序列号转换为中文格式的日期 140

第9章 数学与三角函数 142

9.1 计算函数 142

9.1.1 SUM：求和 142

实战实例1：统计费用支出总额 142

实战实例2：统计总营业额 143

实战实例3：按月份统计销售额 144

9.1.2 MOD：求余 144

实战实例：对数据进行取余数 145

9.1.3 SUMIF：根据指定条件对若干单元格求和 145

实战实例1：统计各仓库的出库总量 145

实战实例2：统计某个时段的销售业绩总金额 146

实战实例3：按部门统计销售业绩之和 147

实战实例4：用通配符对某一类数据求和 148

实战实例5：用通配符求所有车间人员的工资和 149

实战实例6：对产量大于1500的记录进行求和 150

9.1.4 SUMIFS：对区域中满足多个条件的单元格求和 150

实战实例1：统计某一日期区域的销售总金额 151

实战实例2：统计指定店面中指定品牌的销售总金额 152

9.1.5 GCD：求最大公约数 152

实战实例：求两个或多个整数的最大公约数 153

9.1.6 LCM：求最小公倍数 153

实战实例：求两个或多个整数的最小公倍数 154

9.1.7 PRODUCT：求所有参数的乘积 155

实战实例：计算指定数值的阶乘 155

9.1.8 SUMSQ：求参数的平方和 155

实战实例：计算平方和 156

9.1.9 QUOTIENT：求除法的整除数 156

实战实例：对数据进行取整 157

9.1.10 SUMX2MY2：求两数组中对应数值的平方差之和 157

实战实例：计算数值的平方差之和 157

9.1.11 SUMX2PY2：求两数组中对应数值的平方和之和 158

实战实例：计算数值的平方和之和 158

9.1.12 SUMXMY2：求两数组中对应数值差的平均和 159

实战实例：计算数值之差的平方和 159

9.1.13 SUMPRODUCT：求数组间对应的元素乘积的和 160

实战实例1：计算多产品数量与金额乘积并计算总金额 160

实战实例2：计算商品的折后金额 160

实战实例3：统计指定班级中大于指定分值的人数 161

实战实例4：统计非工作日消费金额 162

实战实例5：统计各店铺的总销售金额 163

9.1.14 SUBTOTAL：求数据列表或数据库中的分类汇总 164

实战实例1：统计平均销售金额 164

实战实例2：统计最高销售金额 165

9.1.15 ABS：求出相应数值或引用单元格中数值的绝对值 166

实战实例1：求两地温差 166

实战实例2：比较销售员上月与本月销售额 167

9.1.16 SIGN：求数值的符号 168

实战实例：返回指定数值对应的符号 168

9.2 零数处理函数 169

9.2.1 INT：返回实数向下取整后的整数值 169

实战实例：对平均销售量取整 169

9.2.2 TRUNC：将数字的小数部分截去，返回整数 170

实战实例：汇总金额只保留一位小数 170

9.2.3 ROUND：按指定位数对数值四舍五入 171

实战实例1：按指定位数对数值四舍五入 171

实战实例2：随机获取参赛人员编号 172

实战实例3：以1个百分点为单位计算奖金或扣款 172

9.2.4 MROUND：按照指定基数的倍数对参数四舍五入 173

实战实例：求解指定倍数的数值 173

9.2.5 ROUNDUP：按指定的位数向上舍入数值 174

实战实例1：对小数位数进行向上进行舍入计算 174

实战实例2：计算物品的快递费用 175

9.2.6 ROUNDDOWN：按照指定的位数向下舍入数值 176

实战实例：按小数位数进行向下舍入计算 176

9.2.7 CEILING：将参数向上舍入为最接近的基数的倍数 177

实战实例：根据通话总秒数以7为计价单位来计算总话费 177

9.2.8 CEILING.PRECISE：将数字舍入为最接近的整数或最接近的指定基数的倍数 178

实战实例：对数据向上舍取求值 178

9.2.9 FLOOR：将参数向下舍入到最接近的基数的倍数 179

实战实例：使用FLOOR函数对数据进行向下舍取求值 179

9.2.10 FLOOR.PRECISE：对数据进行向下舍取求值 180

实战实例：对数据向下舍取求值 180

9.2.11 EVEN：将数值向上舍入到最接近的偶数 180

实战实例：将数字向上舍入到最接近的偶数 181

9.2.12 ODD：将数值向上舍入到最接近的奇数 181

实战实例：将数值向上舍入到最接近的奇数 181

9.3 圆周率与平方根函数 182

9.3.1 PI：求圆周率的近似值 182

实战实例：计算圆周率 182

9.3.2 SQRTPI：求圆周率π的倍数的平方根值 183

实战实例：计算指定正数值与π的乘积的平方根值 183

9.3.3 SQRT：求数值的平方根 184

实战实例：计算指定数值对应的算术平方根 184

9.4 其他数学与三角函数 185

9.4.1 AGGREGATE：返回一个数据列表或数据库的合计 185

实战实例：计算区域中最大值（忽略错误值） 185

9.4.2 COMBIN：求组合数 186

实战实例：计算一组对象所有可能的组合数目 186

9.4.3 MDETERM：求数组的矩阵行列式的值 187

实战实例：求一个数组的矩阵行列式的值 187

9.4.4 MINVERSE：求数组阵列的逆阵列 187

实战实例：求矩阵行列式的逆矩阵 188

9.4.5 MMULT：求数组的矩阵乘积 188

实战实例：求两组矩阵行列式的乘积 188

9.4.6 RAND：返回大于等于0及小于1的均匀分布随机数 189

实战实例：自动生成彩票的开奖号码 189

9.4.7 RANDBETWEEN：产生整数的随机数 190

实战实例：随机生成两个指定数之间的整数 190

9.4.8 ROMAN：将阿拉伯数字转换为罗马数字 191

实战实例1：将阿拉伯数字转换为罗马数字 191

实战实例2：计算计件工资中的奖金 192

第10章 文本函数 195

10.1 字符串的查找与位置返回 195

10.1.1 LEFT：从最左侧提取指定个数字符 195

实战实例1：提取出类别编码 195

实战实例2：统计各个地区参会的人数合计 196

实战实例3：根据商品的名称进行一次性调价 197

实战实例4：从地址提取出省名 198

10.1.2 LEFTB：从最左侧提取指定个数字符（以字节为单位） 198

实战实例：提取出员工的姓氏 199

10.1.3 LEN：返回文本字符串的字符数 199

实战实例1：返回字符串的长度 199

实战实例2：检查输入的身份证号码位数是否正确 200

实战实例3：从身份证号码中提取出生年份 201

实战实例4：从身份证号码中提取性别 202

10.1.4 LENB：返回文本字符串的字节数 203

实战实例：返回文本字符串的字节数 203

10.1.5 MID：提取文本字符串中从指定位置开始的特定个数的字符 203

实战实例：提取出产品的类别编码 204

10.1.6 MIDB：提取文本字符串中从指定位置开始的特定个数的字符（以字节数为单位） 204

实战实例：从文本字符串中提取指定位置的文本信息 205

10.1.7 REPLACE：将一个字符串中的部分字符用另一个字符串替换 205

实战实例1：升级身份证号码的位数 206

实战实例2：屏蔽手机号码的后几位数字 207

实战实例3：将产品型号规范化显示 207

10.1.8 REPLACEB：将部分字符根据所指定的字节数用另一个字符串替换 208

实战实例：快速更改输入错误的名称 209

10.1.9 RIGHT：从最右侧开始提取指定字符数的字符 209

实战实例1：提取出电话号码 209

实战实例2：从客户代表全称中提取出姓名 210

10.1.10 RIGHTB：从最右侧开始提取指定字节数的字符 211

实战实例：返回文本字符串中最后指定的字符 211

10.1.11 CONCATENATE：将多个文本字符串合并成一个文本字符串 212

实战实例1：自动生成完整的电子邮件地址 212

实战实例2：在数据前统一加上相同文字 213

10.1.12 FIND：返回字符串在另一个字符串中的起始位置 214

实战实例1：从产品编码中查找分隔线（“-”）的位置 214

实战实例2：从编码中提取出品牌名称 215

10.1.13 FINDB：返回字符串在另一个字符串中的起始位置（以字节为单位） 216

实战实例：返回字符串中“人”字所在的位置 216

10.1.14 SEARCH：查找字符串中指定字符起始位置（不区分大小写） 217

实战实例1：返回文本指定字符的位置 217

实战实例2：从项目编码中提取出合同号 217

10.1.15 SEARCHB：查找字符串中指定字符起始位置（以字节为单位） 218

实战实例：返回指定字符在文本字符串中的位置 219

10.2 文本格式的转换 220

10.2.1 ASC：将全角字符更改为半角字符 220

实战实例：将全角字符更改为半角字符 220

10.2.2 UPPER：将文本转换为大写形式 221

实战实例：将文本转换为大写形式 221

10.2.3 LOWER：将文本转换为小写函数 221

实战实例：将文本转换为小写形式 222

10.2.4 VALUE：将文本转换为数值 222

实战实例：将文本型数字转换为数值 222

10.2.5 BAHTTEXT：将数字转换为泰语文本 223

实战实例：将销售金额转换为泰铢形式 223

10.2.6 NUMBERSTRING：将数字转换为大写汉字 224

实战实例：将小写数字转换为中文大写数字 224

10.2.7 NUMBERVALUE：将文本转化为数字 225

实战实例：将文本字符串转换为数字格式 225

10.2.8 PROPER：将文本字符串的首字母转换成大写 226

实战实例：将单词的首字母转换为大写 226

10.2.9 TEXT：设置数字格式，并将其转换为大写汉字 227

实战实例1：解决合并编码前面的0被忽略的问题 227

实战实例2：按指定的时间格式显示每台机器运行的完成时间 228

实战实例3：将数字转换为电话号码格式 229

10.2.10 WIDECHAR：将半角字符转化成全角字符 229

实战实例：将半角字符更改为全角字符 230

10.2.11 DOLLAR：四舍五入数值，并转换为带＄和位符号的格式 230

实战实例：将金额转换为美元格式 230

10.2.12 RMB：四舍五入数值，并转换为带￥和位符号的格式 231

实战实例：将数字转换为人民币 231

10.2.13 FIXED：将数字按指定的位数取整并以文本形式返回 232

实战实例：将数据按指定小数位数取整 232

10.2.14 CODE：返回文本字符串中有一个字符的数字代码 233

实战实例：返回字符代码对应的数字 233

10.2.15 UNICODE：返回对应于文本的第一个字符的数字（代码点） 234

实战实例：返回由空格字符和大写字母B表示的数字 234

10.2.16 CHAR：返回对应于数字代码的字符 235

实战实例：返回数字对应的字符代码 235

10.2.17 UNICHAR：返回给定数值引用的Unicode字符 235

实战实例：用UNICHAR函数返回数字对应的字符 236

10.2.18 T：返回value引用的文本 236

实战实例：将区域中的文本连接显示 236

10.3 文本的其他操作 237

10.3.1 SUBSTITUTE：用新字符串替换字符串中的部分字符串 237

实战实例1：删除文本中的多余空格 238

实战实例2：将日期规范化再进行求差 238

实战实例3：查找特定文本且将第一次出现的删除，其他保留 239

10.3.2 TRIM：删除文本中的多余空格 240

实战实例：删除文本中多余的空格 240

10.3.3 CLEAN：删除文本中不能打印的字符 241

实战实例：删除无法打印的字符 241

10.3.4 EXACT：比较两个文本字符串是否完全相同 242

实战实例：比较预期与实际报销额是否一致 242

10.3.5 REPT：按照给定的次数重复显示文本 243

实战实例：快速输入多个相同符号 243

第11章 查找与引用函数 245

11.1 数据的查找 245

11.1.1 VLOOKUP：查找指定的数值并返回当前行中指定列处的数值 245

实战实例1：根据员工工号自动查询相关信息 245

实战实例2：在销售表中自动返回产品单价 247

11.1.2 HLOOKUP：在首行查找指定的数值并返回当前列中指定行处的数值 248

实战实例1：根据不同的提成比率计算销售额的提成金额 248

实战实例2：查询任意科目的成绩序列 249

11.1.3 LOOKUP：从向量（数组）中查找一个数值 250

实战实例1：根据查询编号返回销售额 251

实战实例2：返回加班次数最多的员工的姓名 252

实战实例3：根据员工编号自动查询测评信息 252

11.1.4 INDEX：返回指定行列交叉处引用的单元格 254

实战实例1：返回指定行列交叉处的值 254

实战实例2：反向查询最高利润额所对应的品牌 255

11.1.5 MATCH：返回指定方式下与制定数值匹配的元素的相应位置 257

实战实例1：查找目标数据的位置 257

实战实例2：查找指定消费者是否发放奖品 258

实战实例3：查找指定月份指定品牌的利润 259

11.1.6 CHOOSE：根据给定的索引值，返回数值参数清单中的数值 260

实战实例：快速判断成绩是否合格 260

11.2 数据的引用 261

11.2.1 ADDRESS：按给定的行号和列标，建立文本类型的单元格地址 261

实战实例1：返回单元格的地址 262

实战实例2：查找最高评分所在的位置 263

11.2.2 OFFSET：以制定引用为参照系，通过给定偏移量得到新引用 264

实战实例1：实现数据的动态查询 264

实战实例2：对产量进行累计求和 265

11.2.3 INDIRECT：返回由文本字符串指定的引用 266

实战实例1：返回引用处的值 267

实战实例2：按指定的范围计算平均值 267

11.2.4 AREAS：返回引用中包含的区域个数 268

实战实例：统计共有几个车间 269

11.2.5 ROW：返回引用的行号函数 270

实战实例1：返回给定单元格引用的行号 270

实战实例2：自动生成大批量序号 270

实战实例3：根据借款期限返回相应的年数序列 271

11.2.6 COLUMN：返回引用的列号函数 272

实战实例1：在表格中快速输入车间 272

实战实例2：实现隔列求总报销额 273

11.2.7 ROWS：返回引用或数组的行数 274

实战实例：统计共有多少笔销售记录 274

11.2.8 COLUMNS：返回数组或引用的列数 275

实战实例：统计学生档案统计的总项数 275

11.3 其他函数 276

11.3.1 GETPIVOTDATA：返回存储在数据透视表中的数据 276

实战实例：返回数据透视表中的数据 277

11.3.2 HYPERLINK：创建一个快捷方式以打开存储在网络服务器中的文件 278

实战实例：指定管理层级人员的邮件地址 278

11.3.3 TRANSPOSE：转置单元格区域 279

实战实例：对数据进行行列转置 279

11.3.4 FORMULATEXT：以文本形式返回给定引用处的公式 280

实战实例：以文本形式返回公式 280

第12章 信息函数 282

12.1 信息获得函数 282

12.1.1 CELL：返回单元格的信息 282

实战实例：库存量过小时提示“补货” 283

12.1.2 ERROR.TYPE：返回与错误值对应的数字 284

实战实例：返回各类错误值对应的数字 285

12.1.3 INFO：返回当前操作环境的信息 286

实战实例1：返回当前文件夹的路径 287

实战实例2：返回操作系统的版本号 287

12.1.4 TYPE：返回单元格内的数值类型 287

实战实例：测试数据是否是数值型 288

12.2 IS函数 289

12.2.1 ISBLANK：判断测试对象是否为空单元格 289

实战实例1：统计停留车辆数 289

实战实例2：将没有成绩的同学统一标注“缺考” 290

实战实例3：提示信息表中的数据是否为空值 291

12.2.2 ISERR：检测一个值是否为＃N／A以外的错误值 292

实战实例：检验数据是否为＃N／A以外的错误值 293

12.2.3 ISERROR：检测一个值是否为错误值 294

实战实例1：检验数据是否为错误值 294

实战实例2：在对应数据的单元格内做出标记 294

12.2.4 ISEVEN：检测一个值是否为偶数 296

实战实例：根据员工编号判断其性别 296

12.2.5 ISLOGICAL：检测一个值是否为逻辑值 297

实战实例：检验数据是否为逻辑值 297

12.2.6 ISNA：检测一个值是否为＃N／A错误值 298

实战实例：查询编码错误时显示“编码错误” 298

12.2.7 ISNONTEXT：检测一个值是否不是文本 299

实战实例：快速统计实考人数 299

12.2.8 ISNUMBER：检测一个值是否为数值 300

实战实例：快速统计出席人数 300

12.2.9 ISODD：检测一个值是否为奇数 301

实战实例：分奇偶月计算总销售数量 301

12.2.10 ISREF：检测一个值是否为引用 302

实战实例：检验数据是否为引用 303

12.2.11 ISTEXT：检测一个值是否为文本 304

实战实例：返回最高利润额 304

12.2.12 ISFORMULA：检测是否包含公式的单元格引用 305

实战实例：检验是否包含公式的单元格引用 305

12.3 其他函数 306

12.3.1 N：将参数转换为数值形式 306

实战实例1：将指定的数据转换为数值 306

实战实例2：根据订单日期与当前行号生成订单编号 307

12.3.2 PHONETIC：提取文本字符串中的拼音字符 307

实战实例：提取拼音字符 308

12.3.3 NA：返回错误值函数 308

实战实例：NA函数返回错误值 308

12.3.4 SHEET：返回引用工作表的工作表编号 310

实战实例：返回引用工作表的工作表编号 310

12.3.5 SHEETS：返回引用中的工作表数 311

实战实例：返回引用中的工作表数 311

第13章 统计函数 314

13.1 基础统计函数 314

13.1.1 AVERAGE：返回参数的平均值 314

实战实例：计算平均分数 314

13.1.2 AVERAGEA：计算参数列表中非空单元格中数值的平均值 315

实战实例：求包含文本值的平均值 315

13.1.3 AVEDEV：返回一组数据点到其算术平均数的绝对偏差的平均值 315

实战实例：求绝对偏差的平均值 316

13.1.4 AVERAGEIF：查找给定条件指定的单元格的平均值 316

实战实例1：计算指定班级的平均成绩 317

实战实例2：统计各个部门的平均工资 317

13.1.5 AVERAGEIFS：查找一组给定条件指定的单元格的平均值 318

实战实例1：计算满足双条件的平均值 319

实战实例2：计算一车间女职工的平均工资 320

实战实例3：在AVERAGEIFS函数中使用通配符 321

13.1.6 COUNT：统计参数列表中含有数值数据的单元格个数 322

实战实例1：统计获取奖金的人数 322

实战实例2：统计缺考人数 323

13.1.7 COUNTA：计算指定单元格区域中非空单元格的个数 323

实战实例：统计出异常出勤的人数 324

13.1.8 COUNTBLANK：计算空白单元格的个数 324

实战实例：检查员工信息是否完整 325

13.1.9 COUNTIF：求满足给定条件的数据个数 326

实战实例1：统计汉语言文学专业的人数 326

实战实例2：统计大于平均分的学生人数 326

13.1.10 COUNTIFS：统计一组给定条件所指定的单元格数 327

实战实例1：统计业绩大于9500元的优秀员工人数 328

实战实例2：统计指定产品每日的销售记录数 328

13.1.11 DEVSQ：返回数据点与各自样本平均值偏差的平方和 329

实战实例：计算零件质量系数的偏差平方和 330

13.1.12 FREQUENCY：以一列垂直数组返回某个区域中数据的频率分布 330

实战实例：统计出客服人员被投诉的次数 331

13.1.13 TRIMMEAN：求一组数据的内部平均值 331

实战实例：通过6位评委打分计算选手的最后得分 332

13.1.14 GEOMEAN：求数值数据的几何平均数 332

实战实例：计算出上半年销售量的几何平均销售量 333

13.1.15 MEDIAN：求一组数的中值 333

实战实例：返回中间的成绩 334

13.1.16 MODE.SNGL：求数值数据的众数 334

实战实例：返回数组中的众数（即出现频率最高的数） 335

13.1.17 HARMEAN：求数据集合的调和平均值 335

实战实例：计算一周每天的平均产量 335

13.1.18 MAX：返回一组值中的最大值 336

实战实例1：返回最高销售业绩 336

实战实例2：返回女职工的最大年龄 337

实战实例3：计算出单日最高的销售额 338

13.1.19 MAXA：返回参数列表中的最大值 338

实战实例：返回各店面的最高销售金额（包含文本） 339

13.1.20 MIN：返回一组值中的最小值 339

实战实例1：返回最低销售业绩 339

实战实例2：根据工龄计算可休假天数 340

实战实例3：忽略0值求出最低分数 341

13.1.21 MINA：返回参数列表中的最小值 342

实战实例：返回各店面的最低销售金额（包含文本） 342

13.1.22 QUARTILE：返回数据集的四分位数 343

实战实例：在一组学生身高统计数据中求四分位数 343

13.1.23 PERCENTILE.INC：返回数组的K百分点值 345

实战实例：返回数组的K百分点值 345

13.1.24 PERCENTILE.EXC：返回区域中数值的第K个百分点的值 346

实战实例：返回数组区域的K百分比数值点 346

13.1.25 PERCENTRANK.INC：返回特定数值在一组数中的百分比排名 347

实战实例：将各月销售利润按百分比排位 347

13.1.26 PERCENTRANK.EXC：返回某个数值在一个数据集中的百分比 348

实战实例：返回数值7在数组中的排位 348

13.1.27 VARS：估算基于给定样本的方差 349

实战实例：估算样本方差 349

13.1.28 VARS：估算基于整个样本总体的方差 350

实战实例：计算样本总体的方差 350

13.1.29 VARA：求空白单元格以外给定样本的方差 350

实战实例：计算员工工龄样本的方差（含未统计者） 351

13.1.30 VARPA：计算空白单元格以外基于整个样本总体的方差 351

实战实例：计算员工工龄样本总体的方差（含未统计者） 352

13.1.31 STDEVP：计算基于给定的样本总体的标准偏差 352

实战实例：返回整个样本总体计算标准偏差 352

13.1.32 STDEV.S：估算基于给定的样本总体的标准偏差 353

实战实例：估算样本的标准偏差 353

13.1.33 STDEVA：求空白单元格以外给定样本的标准偏差 354

实战实例：计算基于给定样本的标准偏差 354

13.1.34 STDEVPA：计算空白单元格以外的样本总体的标准偏差 355

实战实例：计算员工工龄样本总体的标准偏差 355

13.1.35 SKEW：返回分布的偏斜度 356

实战实例：计算商品在一段时期内价格的不对称度 356

13.1.36 SKEW.P：返回基于样本总体的分布不对称度 357

实战实例：返回样本总体数据集分布的不对称度 357

13.1.37 KURT：返回数据集的峰值 357

实战实例：计算商品在一段时期内价格的峰值 358

13.2 协方差、相关与回归函数 358

13.2.1 COVARIANCE.P：返回总体协方差 359

实战实例：计算上下旬销量的总体协方差 359

13.2.2 COVARIANCE.S：返回样本协方差 359

实战实例：计算上下旬销量的样本协方差 360

13.2.3 CORREL：返回两变量的相关系数 360

实战实例：返回两个不同事物之间的相关系数 361

13.2.4 PEARSON：返回皮尔生乘积矩相关系数 361

实战实例：返回两个数值集合之间的线性相关程度 361

13.2.5 FISHER：返回点x的Fisher变换值 362

实战实例：返回点x的Fisher变换值 362

13.2.6 FISHERINV：求Fisher变换的反函数值 363

实战实例：返回Fisher变换的反函数值 363

13.2.7 SLOPE：返回线性回归直线的斜率 364

实战实例：求拟合的线性回归直线的斜率 364

13.2.8 INTERCEPT：求回归直线的截距 365

实战实例：计算直线与y轴的截距 365

13.2.9 FORECAST：求两变量间的回归直线的预测值 365

实战实例：预测出未来值 366

13.2.10 TREND：求回归直线的预测值 366

实战实例：预算出七、八月份的产品销售额 367

13.2.11 STEYX：求回归直线的标准误差 367

实战实例：返回预测值时产生的标准误差 368

13.2.12 RSQ：求回归直线的判定系数 368

实战实例：返回皮尔生乘积矩相关系数的平方 368

13.2.13 GROWTH：根据现有的数据预测指数增长值 369

实战实例：预测出指数增长值 370

13.2.14 LOGEST：求指数回归曲线的系数和底数 370

实战实例：根据指数回归拟合曲线返回该曲线的数值 371

13.2.15 LINEST：求回归直线的系数和常数项 371

实战实例：预测出9月份的产品销售量 372

第14章 财务函数 374

14.1 投资计算函数 374

14.1.1 FV：基于固定利率及等额分期付款方式，返回期指 374

实战实例1：计算住房公积金的未来值 374

实战实例2：计算投资的未来值 375

实战实例3：计算某项保险的未来值 376

14.1.2 FVSCHEDULE：基于一系列复利返回本金的期指 376

实战实例：计算投资在可变利率下的未来值 376

14.1.3 IPMT：返回给定期数内对投资的利息偿还率 377

实战实例1：计算每月偿还额中的利息额 377

实战实例2：计算每年偿还额中的利息额 378

14.1.4 ISPMT：计算特定投资期内要支付的利息 379

实战实例：计算投资期内需支付的利息额 379

14.1.5 PMT：基于固定利率，返回贷款的每期等额付款额 380

实战实例1：计算贷款的每年偿还额 380

实战实例2：按季度（月）支付时计算每期应偿还额 381

14.1.6 PPMT：求偿还额的本金部分 381

实战实例1：计算第一个月与最后一个月的本金偿还额 382

实战实例2：计算指定期间的本金偿还额 382

14.1.7 NPV：基于一系列现金流和固定贴现率，返回净现值 383

实战实例：计算一笔投资的净现值 384

14.1.8 PV：返回投资的现值 384

实战实例：计算一笔投资的贷款额 385

14.1.9 XNPV：基于不定期发生的现金流，返回它的净现值 385

实战实例：计算出一组不定期盈利额的净现值 386

14.1.10 EFFECT：求实际的年利率 386

实战实例：计算债券的年利率 386

14.1.11 NOMINAL：求名义利率 387

实战实例：将实际年利率转换为名义年利率 387

14.1.12 NPER：返回某项投资的总期数 388

实战实例：计算一笔投资的期数 388

14.1.13 PDURATION：返回投资到达指定值所需的期数 389

实战实例：返回投资年数 389

14.1.14 RRI：返回投资增长的等效利率 390

实战实例：返回投资增长的等效利率 390

14.2 折旧计算函数 391

14.2.1 AMORDEGRC：返回每个结算期间的折旧值 391

实战实例：计算会计期间的折旧值 392

14.2.2 AMORLINC：返回每个结算期间的折旧值 392

实战实例：以法国会计系统计算每个会计期间的折旧值 393

14.2.3 DB：使用固定余额递减法计算折旧值 394

实战实例1：用固定余额递减法计算出固定资产的每月折旧额 394

实战实例2：用固定余额递减法计算出固定资产的每年折旧额 395

14.2.4 DDB：使用双倍余额递减法计算折旧值 396

实战实例：双倍余额递减法计算固定资产的每年折旧额 396

14.2.5 SLN：返回某项资产在一个期间中的线性折旧值 397

实战实例1：直线法计算固定资产的每月折旧额 397

实战实例2：直线法计算固定资产的每年折旧额 398

14.2.6 SYD：按年限总和折旧法计算折旧值 399

实战实例：年数总和法计算固定资产的年折旧额 399

14.2.7 VDB：使用双倍余额递减法或其他指定方法，返回折旧值 400

实战实例：双倍余额递减法计算指定期间的资产折旧值 400

14.3 偿还计算函数 402

14.3.1 IRR：返回一组现金流的内部收益率 402

实战实例：计算一笔投资的内部收益率 402

14.3.2 MIRR：返回某一连续期间内现金流的修正内部收益率 403

实战实例：计算不同利率下的修正内部收益率 403

14.3.3 RATE：返回年金的各期利率 404

实战实例：计算一笔投资的年增长率 404

14.3.4 XIRR：返回不定期内产生的现金流量的内部收益率 405

实战实例：计算一组不定期盈利额的内部收益率 405

第15章 数据库函数 407

15.1 数据库计算函数 407

15.1.1 DSUM：返回数据库的列中满足指定条件的数字之和 407

实战实例1：统计指定部门的总报销费用 407

实战实例2：使用通配符求利润之和 408

实战实例3：实现双条件查询 409

实战实例4：统计除了某一位或多位销售员之外的销售数量 410

15.1.2 DPRODUCT：返回数据库的列中满足指定条件的数值的乘积 411

实战实例：查询指定公司指定人员是否签到 411

15.2 数据库统计函数 412

15.2.1 DAVERAGE：返回数据库的列中满足指定条件的数值的平均值 412

实战实例1：统计出指定产品的平均销售额 413

实战实例2：对各部门的平均考核成绩进行查询 414

15.2.2 DCOUNT：返回数据库的列中满足指定条件的单元格的个数 415

实战实例1：统计出指定学校分数大于指定值的人数 416

实战实例2：忽略0值统计出不及格的人数 417

实战实例3：统计满足指定条件的销售记录条数 417

15.2.3 DCOUNTA：返回数据库的列中满足指定条件的非空单元格的个数 418

实战实例1：统计满足指定条件的公司部门人数 419

实战实例2：统计指定日期下能按时交纳首付款的项目个数 420

实战实例3：统计销售业绩为优的人数 421

15.2.4 DGET：求满足条件的唯一记录 421

实战实例：提取出符合条件的信息 422

15.2.5 DMAX：返回数据库的列中满足指定条件的数值的最大值 423

实战实例1：返回某一类产品的最高出库单价 423

实战实例2：统计各科目考核成绩的最高分 424

15.2.6 DMIN：返回数据库的列中满足指定条件的数值的最小值 426

实战实例1：返回 426

实战实例2：统计各科目考核成绩的最低分 427

15.2.7 DSTDEV：返回数据库的列中满足指定条件数值的样本标准偏差 428

实战实例：计算女员工工龄的样本标准偏差 429

15.2.8 DSTDEVP：将满足指定条件的数字作为样本总体，计算标准偏差 430

实战实例：计算女员工工龄的总体标准偏差 430

15.2.9 DVAR：将满足指定条件的数字作为一个样本，估算样本总体的方差 431

实战实例：计算女员工工龄的样本总体方差 431

15.2.10 DVARP：将满足指定条件的数字作为样本总体，计算总体方差 432

实战实例：计算女员工工龄的总体方差 433

第16章 员工档案管理 435

16.1 员工档案管理表 435

16.1.1 创建员工档案管理表 435

16.1.2 利用数据有效性防止工号重复输入 437

16.1.3 身份证号中提取有效信息 438

16.1.4 计算员工年龄和工龄 440

16.2 员工档案查询表 441

16.2.1 创建员工档案查询表 441

16.2.2 使用VLOOKUP函数查询人员信息 443

16.3 使用函数制作到期提醒 446

16.3.1 生日到期提醒 446

16.3.2 试用期到期提醒 448

16.4 员工学历层次分析 450

16.4.1 编制员工学历数据透视表 450

16.4.2 制作员工学历透视图 453

16.5 员工年龄段分析 455

16.5.1 编制员工年龄段数据透视表 455

16.5.2 制作员工学历透视图 457

第17章 员工考勤管理 460

17.1 员工考勤登记表 460

17.1.1 绘制员工基本考勤登记表 460

17.1.2 设置考勤日期和星期格式 463

17.1.3 冻结窗格显示固定数据 466

17.1.4 添加考勤符号 466

17.2 员工考勤统计表 468

17.2.1 创建员工考勤统计表 468

17.2.2 用特殊颜色标记迟到早退人员 470

17.3 员工出勤情况分析 472

17.3.1 员工出勤率统计 472

17.3.2 筛选出勤情况最差的员工 474

17.3.3 使用数据透视表统计各部门出勤情况 475

17.4 员工考勤扣款统计 478

17.4.1 员工出勤率统计 478

17.4.2 筛选出勤扣款最多的5位员工 480

第18章 员工薪资管理 483

18.1 员工销售业绩奖金 483

18.1.1 创建销售记录表 483

18.1.2 计算本月基本业绩奖金 484

18.2 员工基本工资表 485

18.2.1 创建基本表格 485

18.2.2 计算员工工龄及工龄工资 486

18.3 员工考勤扣款及满勤奖 486

18.3.1 复制员工考勤扣款统计工作表 486

18.3.2 计算员工满勤奖 487

18.4 员工福利补贴表 488

18.4.1 创建员工福利表 488

18.4.2 计算员工福利补贴 489

18.5 员工社保缴费表 490

18.5.1 创建员工社保缴费表 490

18.5.2 计算各项社保缴费情况 491

18.6 工资统计表 492

18.6.1 创建员工月度工资表 492

18.6.2 计算工资表中应发金额 493

18.6.3 计算工资表中应扣金额 494

18.7 员工工资单 496

18.7.1 建立员工的工资单 496

18.7.2 快速生成每位员工的工资单 498

第19章 销售管理 500

19.1 销售记录表 500

19.1.1 创建销售记录表 500

19.1.2 标记出排名前三的销售金额 500

19.1.3 筛选出指定的销售记录 502

19.2 按产品系列统计销售收入 504

19.2.1 创建基本表格 504

19.2.2 创建饼图显示各产品销售情况 506

19.3 销售收入变动趋势分析 509

19.3.1 创建基本表格 509

19.3.2 创建图表显示销售变动趋势 510

19.4 销售员业绩分析 512

19.4.1 创建数据透视表 512

19.4.2 创建数据透视图 514

第20章 固定资产管理 519

20.1 固定资产清单 519

20.1.1 创建固定资产清单 519

20.1.2 使用公式计算固定资产状态 521

20.1.3 查询部门固定资产清单 522

20.2 固定资产查询表 524

20.2.1 返回固定资产各项信息 524

20.2.2 计算固定资产折旧情况 527

20.3 企业固定资产折旧计算 528

20.3.1 余额法折旧计算 528

20.3.2 双倍余额递减法折旧计算 529

20.3.3 年限总和法折旧计算 531