第1章 函数与公式 1

1.1 公式的输入与编辑 1

技巧1 不会函数也能快速求和（或平均值） 1

技巧2 公式函数不分家 2

技巧3 启用“插入函数”对话框编辑函数 3

技巧4 手写编辑公式 7

技巧5 公式的修改 7

技巧6 不记得函数全称也能正确输入函数 8

技巧7 快速查找和学习某函数用法 9

技巧8 使用批量计算 11

技巧9 大范围公式复制的方法 11

技巧10 跳过非空单元格批量建立公式 12

技巧12 为什么数字与“空”单元格相加出错 15

技巧13 为什么明明显示的是数据，计算结果却为0 16

技巧14 暂时保留没有输入完整的公式 17

技巧15 将公式运算结果转换为数值 18

技巧16 隐藏公式实现保护 19

1.2 公式中数据源的引用 20

技巧17 相对引用数据源计算 20

技巧18 绝对引用数据源计算 21

技巧19 引用当前工作表之外的单元格 22

技巧20 引用多工作表的同一单元格计算 24

技巧21 引用其他工作簿中的数据源 25

技巧22 用“F4”键快速改变数据源引用类型 27

技巧23 为什么要定义名称 27

技巧24 快速定义名称的方法有哪些 28

技巧25 引用定义的名称创建公式 29

技巧26 将公式定义为名称 30

技巧27 将表格创建为动态名称实现数据计算即时更新 31

技巧28 重新修改名称的引用位置 32

1.3 学会分解、理解公式 33

技巧29 查看长公式中某一步的计算结果 33

技巧30 追踪公式引用的单元格 34

技巧31 通过“公式求值”功能逐步分解公式 35

技巧32 在单元格中显示所有公式 36

1. AND函数（检验一组数据是否都满足条件） 38

技巧33 考评学生的各门课程是否全部及格 38

技巧34 一次性判断一组数据是否都满足条件 39

2. NOT函数（对所给参数求反） 40

技巧35 筛选掉小于某一年龄的应聘人员 40

3. OR函数（检验一组数据是否有一个满足条件） 41

技巧36 检验员工是否通过考评 41

4. IF函数（根据条件判断真假） 42

技巧37 利用IF函数进行金额校验 43

技巧38 根据不同返利比计算返利金额 43

技巧39 根据业务处理量判断员工业务水平 44

技巧40 分性别判断成绩是否合格 45

技巧41 根据消费卡类别与消费额派发赠品 47

技巧42 根据职工性别和职务判断退休年龄 48

技巧43 根据年龄与学历对应聘人员二次筛选 49

技巧44 根据商品的名称与颜色进行一次性调价 50

技巧45 根据3项业务的完成率计算综合完成率 52

技巧46 比较两个采购部门的采购价格 53

技巧47 计算个人所得税 54

技巧48 当被除数为空值（或0值）时返回“计算数据源有错误”文字 56

1. LEFT函数（从最左侧开始提取指定数目的字符） 58

技巧50 从商品全称中提取产地信息 59

2. LEFTB函数（按字节数从最左侧提取指定数目的字符） 60

技巧51 根据产品编号提取类别 60

3. RIGHT函数（从最右侧开始提取指定数目的字符） 61

技巧52 将电话号码的区号与号码分离 62

技巧53 从右侧提取字符并自动转换为数值 63

技巧54 发票金额的分列填写 64

4. RIGHTB函数（按字节数从最右侧提取指定数目的字符） 67

技巧55 提取产品的规格数据 67

5. MID函数（从指定位置开始提取字符） 68

技巧56 从规格数据中提取部分数据 69

技巧57 提取产品的货号 69

6. MIDB函数（按字节数从指定位置开始提取字符） 70

技巧58 从房号数据中提取单元号 71

技巧59 合并商品货号码数及颜色 72

技巧60 提取任意有用数据并合并 73

8. FIND函数（查找指定字符并返回其位置） 74

技巧61 查找品名所在位置并提取 74

技巧62 问卷调查时实现自动统计答案 75

9. REPLACE函数（替换字符中的部分字符） 77

技巧63 屏蔽中奖手机号码的后几位数 77

10. SEARCH函数（查找字符并返回其起始位置） 78

技巧64 从项目编码中提取合同号 79

技巧65 去掉报表中多余的空格 80

技巧66 查找特定文本且将第一次出现的删除，其他保留 81

技巧68 根据报名学员统计人数 83

12. FIXED函数（按指定的小数位数进行取整） 84

技巧69 消除因四舍五入而造成的显示误差 84

技巧70 将半角英文字母转换为全角英文字母 85

技巧71 将小写英文文本一次性变更为大写 86

15. LOWER函数（大写字母转换为小写字母） 86

技巧72 将大写字母转换为小写字母 86

技巧73 一次性将每个单词的首字母转换为大写 87

17. RMB函数（四舍五入数值，并添加千分位符号和￥符号） 88

技巧74 将销售额一次性转换为人民币格式 88

18. DOLLAR函数（四舍五入数值，并添加千分位符号和$符号） 89

技巧75 将销售金额一次性转换为美元货币格式 89

19. TEXT函数（将数值转换为指定格式的文本） 90

技巧76 让计算得到金额显示为特定形式 90

技巧77 返回值班日期对应在的星期数 91

技巧78 按上下班时间计算加班时长并显示为“*时*分”形式 91

技巧79 解决日期计算返回日期序列号问题 93

技巧80 让数据统一显示固定的位数 94

技巧81 让合并的日期显示正确格式 95

20. VALUE函数（将文本型数字转换成数值型数字） 96

技巧82 解决总金额无法计算的问题 96

21. LEN函数（返回字符串的字符数） 97

技巧83 从身份证号码中提取出生年份 97

技巧84 从身份证号码中提取性别 98

技巧85 配合多函数从身份证号码中提取完整的出生年月日 99

技巧86 输入身份证号码填写框 101

技巧87 根据销售额用“★”评定等级 101

技巧88 删除文本单词中多余的空格 103

24. EXACT函数（比较两个字符串是否相同） 103

技巧89 比较两个店铺的平均售价是否相同 103

1. NOW函数（返回当前日期时间） 105

技巧90 计算员工在职天数 105

技巧91 判断应收账款是否到期 106

3. DATE函数（返回指定日期的序列号） 107

技巧92 建立倒计时显示牌 107

4. DAY函数（返回某日期的天数） 108

技巧93 显示出全年中各月的天数 108

技巧94 计算本月上旬的销售总额 109

5. YEAR函数（返回某日期中的年份） 110

技巧95 计算出员工年龄 110

技巧96 计算出员工工龄 111

技巧97 返回日期对应的星期数 113

技巧98 判断值班日期是平时加班还是双休日加班 114

7. MONTH函数（返回某日期中的月份） 115

技巧99 自动填写销售报表中的月份 115

技巧100 计算本月账款金额总计 116

8. EOMONTH函数（返回某个月份最后一天的序列号） 117

技巧101 根据活动开始日期计算各月活动天数 117

技巧102 根据当前月份自动显示本月所有日期序列 118

技巧103 自动返回各日期对应的星期数 120

技巧104 快速得知2016年中各节日在第几周 121

10. DATEDIF函数（计算两个日期之间的年、月和天数） 122

技巧105 计算总借款天数 123

技巧106 计算借款至今日的时长 123

技巧107 根据员工工龄自动追加工龄工资 124

技巧108 计算还款剩余天数 125

技巧109 计算固定资产已使用月份 126

12. YEARFRAC函数（计算两日期间天数占全年天数的百分比） 128

技巧111 计算各地点工作时间占全年天数百分比 129

技巧112 根据休假天数自动显示出休假结束日期 130

技巧113 计算元旦到春节之间的工作日 131

技巧114 计算年假占全年工作日的百分比 131

15. EDATE函数（计算与指定日期相隔指定月份数的日期） 133

技巧115 计算应收账款的到期日期 133

16. HOUR函数（返回某时间中的小时数） 134

技巧116 计算来访签到时间的区间 134

技巧119 显示高于或低于标准时间的值 137

19. DATEVALUE函数（将文本日期转换为可识别的日期序列号） 138

技巧121 将指定时间转换为时间小数值 140

技巧122 一次性对多列数据求和 141

技巧123 根据销售数量与单价计算总销售额 142

技巧124 只计算某两个产品的合计金额 143

技巧125 分奇偶行统计数据 144

技巧126 按经办人计算销售金额 145

技巧127 统计各部门工资总额 146

技巧128 分别统计前半个月与后半个月的销售额 147

技巧129 用通配符对某一类数据求和 148

技巧130 计算销售金额前3名合计值 149

技巧131 满足指定类别指定时间统计总销售额 150

技巧132 多条件统计某一类数据总和 152

技巧133 按不同性质统计应收款 153

技巧134 统计销售部女员工人数 155

技巧135 统计出指定班级中分数大于指定值的人数 156

技巧136 统计出指定部门获取奖金的人数（去除空值） 157

技巧137 统计出指定部门、指定职务的员工人数 158

技巧138 统计非工作日销售金额 159

技巧139 按月汇总出库数量 160

技巧140 统计大于12个月的账款 161

技巧141 统计出某两种商品的销售金额 162

技巧142 统计某一时间段出现的次数 163

技巧143 统计指定日期区间中指定性别的人数 164

技巧144 分单位统计账龄 165

技巧145 比较今年销售额与去年销售额 168

技巧146 计算支出金额总计值 169

6. MOD函数（求两个数相除后的余数） 169

技巧147 按奇数月与偶数月统计销量 170

技巧148 计算每位员工的加班时长 171

技巧149 计算指定数值对应的算术平方根 173

技巧150 返回两个或多个整数的最大公约数 173

技巧151 计算两个或多个整数的最小公倍数 174

技巧152 根据指定的底数和指数计算出方根值 175

技巧153 计算平均销售数量时取整数 176

技巧154 计算销售金额时取整或保留指定位数小数 177

技巧155 以6秒（不足6秒按6秒计算）为单位计算通话费用 179

技巧156 以一个百分点为单位计算奖金或扣款 181

技巧159 根据给定时间界定整点范围 185

技巧160 计件工资中的奖金计算 186

技巧161 计算商品运送车次 187

技巧162 计算参加某活动的每组人数 189

5.3 阶乘、随机数计算函数 189

20. FACT函数（求指定正数值的阶乘） 189

技巧163 求指定正数值的阶乘值 190

21. MULTINOMIAL函数（计算指定数值阶乘与各数值阶乘乘积的比值） 190

技巧164 计算指定数值和的阶乘与各数值阶乘乘积的比值 190

22. RAND函数（返回一个大于等于0小于1的随机数） 191

技巧165 随机获取选手编号 192

技巧166 自动生成彩票7位开奖号码 192

技巧167 自动随机生成3位数编码 193

技巧168 快速自动求平均值 194

技巧169 在成绩表中忽略0值求平均分 195

技巧170 计算指定学校学生的平均成绩 196

2. AVERAGEA函数（求包括文本和逻辑值的平均值） 197

技巧171 计算平均分时将“缺考”的也计算在内 197

技巧172 统计各月份的平均销售额（计算区域含文本值） 198

技巧173 统计各班级平均分 199

技巧174 计算月平均出库数量 200

技巧175 排除新店计算平均利润 201

技巧176 计算一车间女职工平均工资 202

技巧177 求介于某一区间内的平均值 203

技巧178 统计指定店面所有男装品牌的平均利润 203

技巧179 忽略0值求指定班级的平均分 204

技巧180 比较两种产品的销售利润的稳定性 205

技巧181 通过10位评委打分计算选手的最后得分 206

技巧182 统计出故障的机器台数 207

技巧183 统计各个部门获取交通补助的人数 208

技巧184 统计出指定学历员工人数 209

技巧185 统计其中一科得满分的人数 210

8. COUNTA函数（求包括文本和逻辑值的数目） 211

技巧186 统计参与某项课程的总人数 211

技巧187 统计非正常出勤的人数 211

技巧189 统计某课程的报名人数 213

技巧190 在成绩表中分别统计及格人数与不及格人数 213

技巧191 统计出成绩大于平均分数的学生人数 215

技巧192 统计是三好学生且参加数学竞赛的人数 215

技巧193 统计连续3次考试都进入前10名的人数 216

技巧194 统计各店面男装的销售记录条数（双条件） 217

技巧195 统计指定时间指定类别商品的销售记录数 218

技巧196 统计指定产品每日的销售记录数 219

6.3 最大值与最小值统计函数 221

技巧198 快速返回数据区域中的最大值 221

技巧199 返回企业女性员工的最大年龄 222

技巧200 返回上半个月单笔最高销售金额 223

技巧201 计算单日销售金额并返回最大值 224

技巧202 返回最低销售金额 225

技巧203 忽略0值求最低分数 226

技巧204 返回多次短跑中用时最短的编号 227

14. MAXA函数（包含文本与逻辑值时求最大值） 228

技巧205 返回成绩表中的最高分数（包含文本） 228

15. MINA函数（包含文本与逻辑值时求最小值） 229

技巧206 返回最低利润额（包含文本） 229

16. LARGE函数（返回某一数据集中的某个最大值） 230

技巧207 返回排名前3位的销售金额 230

技巧208 计算成绩表中前5名的平均值 231

技巧209 分别统计各班级第一名成绩 231

17. SMALL函数（返回某一数据集中的某个最小值） 232

技巧210 统计成绩表中后5名的平均分 232

技巧211 统计成绩表中后5名的平均分（忽略0值） 233

技巧212 返回被投诉次数最多的客服的编号 234

技巧213 为学生考试成绩排名次 235

技巧214 对不连续单元格排名次 236

20. RANK.AVG函数（排位有相同名次时返回平均排位） 236

技巧216 返回数值区域的k指定百分比对应的数值 238

技巧217 将各月销售利润按百分比排位 239

技巧218 统计全年各月利润值中的中位数 241

技巧219 从全年各月销售利润数据集中求四分位数 242

1. PMT函数（计算贷款每期付款额） 244

技巧220 计算贷款的每年偿还额 244

技巧221 计算贷款的每月偿还额 245

2. IPMT函数（计算每期偿还额中的利息额） 245

技巧222 计算贷款每年偿还金额中有多少是利息 245

技巧223 计算贷款每月偿还金额中有多少是利息 246

技巧224 计算贷款每年偿还金额中有多少是本金 247

技巧225 计算贷款每月偿还金额中有多少是本金 248

4. ISPMT函数（等额本金还款方式下的利息计算） 249

技巧226 在等额本金还款方式下计算某贷款的利息 249

5. FV函数（计算投资未来值） 250

技巧227 计算分期存款的未来值 250

技巧228 计算购买某项保险的未来值 251

6. FVSCHEDULE函数（投资在变动或可调利率下的未来值） 251

技巧229 计算某项整存整取存款的未来值 251

7. PV函数（计算投资现值） 252

技巧230 判断购买某项保险是否合算 252

8. NPV函数（计算投资净现值） 253

技巧231 计算某投资的净现值 253

9. XNPV函数（计算一组不定期现金流的净现值） 255

技巧232 计算出一组不定期盈利额的净现值 255

技巧233 计算出贷款的清还年数 256

技巧234 计算出某项投资的投资期数 256

11. IRR函数（计算一组现金流的内部收益率） 257

技巧235 计算某项投资的内部收益率 257

12. XIRR函数（计算一组不定期现金流的内部收益率） 258

技巧236 计算出一组不定期盈利额的内部收益率 258

13. MIRR函数（计算修正内部收益率） 259

技巧237 计算某项投资的修正内部收益率 259

14. RATE函数（计算年金每个期间的利率） 260

技巧238 计算购买某项保险的收益率 260

技巧239 用直线法计算出固定资产的每年折旧额 261

技巧240 用直线法计算出固定资产的每月折旧额 261

技巧241 用年数总和法计算出固定资产的每年折旧额 262

17. DB函数（固定余额递减法） 263

技巧242 用固定余额递减法计算出固定资产的每年折旧额 263

技巧243 用双倍余额递减法计算出固定资产的每年折旧额 264

19. VDB函数（计算部分期间内的折旧值） 265

技巧244 计算出固定资产部分期间的设备折旧值 266

技巧245 计算指定会计期间的折旧值 268

1. CHOOSE函数（从参数列表中选择并返回一个值） 269

技巧246 配合IF函数找出短跑成绩的前三名 269

技巧247 根据产品不合格率决定产品处理办法 270

2. LOOKUP函数（向量型）（按条件查找并返回值） 271

技巧248 根据产品编号查询库存数量（向量型语法） 272

3. LOOKUP函数（数组型）（按条件查找并返回值） 273

技巧249 根据产品编号查询库存数量（数组型语法） 274

技巧250 按姓名查询学生的各科目成绩 275

技巧251 根据不同的返利率计算各笔订单的返利金额 278

技巧252 快速查询任意科目的成绩序列 279

技巧253 在销售表中自动返回产品单价 282

技巧254 将多张工作表中的数据合并到一张工作表中 283

技巧255 根据多条件派发赠品 285

回指定位置的值） 288

技巧258 查找任意指定销售员的销售总金额 290

技巧259 查找指定月份指定专柜的销售金额 291

技巧260 返回成绩最高的学生的姓名 293

技巧261 查找迟到次数最多的学生 294

技巧262 查询最高总金额所对应的店铺 295

7. ADDRESS函数（建立文本类型单元格的地址） 296

技巧263 查找最大销售额所在位置 296

技巧264 在一行中快速输入月份 298

技巧265 实现隔列求总销售金额 298

技巧266 返回参与考试的科目数量 300

技巧267 让数据自动隔4行（自定义）加1（自定义） 300

技巧268 提取季度合计值计算全年销售额 301

技巧269 根据借款期限返回相应的年数序列 302

技巧270 统计销售记录条数 304

技巧271 判断值班人员是否重复 304

技巧272 解决合并单元格引用数据列出现跳跃的问题 306

技巧273 按指定的范围计算平均值 307

技巧274 INDIRECT解决跨工作表查询时名称匹配问题 309

13. OFFSET函数（根据指定偏移量得到新引用） 310

技巧275 实现数据的动态查询 311

技巧276 对每日出库量累计求和 312

技巧277 OFFSET函数常用于创建动态图表数据源 313

技巧278 标注出缺考学生 316

技巧279 快速统计缺考人数 317

技巧280 检验数据是否为逻辑值 318

技巧282 统计销售量总计值 319

5. ISNA函数（判断数据是否为错误值“ N/A”） 320

技巧283 检验数据是否为“ N/A”错误值 320

6. ISERR函数（判断数据是否为“ N/A”之外的任意错误值） 321

技巧284 检验数据是否为“ N/A”之外的任意错误值 321

7. ISODD函数（判断数据是否为奇数） 321

技巧285 根据身份证号码判断其性别1 322

技巧286 根据身份证号码判断其性别2 323

9. CELL函数（返回单元格、位置等） 323

技巧287 判断测试结果是否达标 324

技巧288 根据错误代码显示错误原因 326

11. N函数（返回转换为数值后的值） 326

技巧289 用订单生成日期的序列号与当前行号生成订单的编号 327

技巧290 返回数值对应的类型数字 328

1. DSUM函数（从数据库中按给定条件求和） 329

技巧291 计算指定经办人的订单总金额 329

技巧292 计算上半月中指定名称产品的总销售额（满足双条件） 330

技巧293 去除某一个（或多个）部门计算总工资 331

技巧294 使用通配符实现利润求和统计 332

技巧295 解决模糊匹配造成统计错误问题 332

2. DAVERAGE函数（从数据库中按给定条件求平均值） 334

技巧296 计算指定班级平均分 334

技巧297 计算指定车间指定性别员工的平均工资（双条件） 335

技巧298 实现对各科目平均成绩查询 336

3. DCOUNT函数（从数据库中按给定条件统计记录条数） 337

技巧299 统计成绩小于60分的人数（忽略0值） 337

技巧300 统计出指定性别且工资大于3000元的人数 338

技巧301 统计记录条数时使用通配符 339

4. DCOUNTA函数（从数据库中按给定条件统计非空单元格数目） 339

技巧302 统计业务水平为“好”的人数 340

技巧303 统计出指定性别测试合格的人数 341

技巧304 按条件统计来访总人数（使用通配符） 341

5. DMAX函数（从数据库中按给定条件求最大值） 342

技巧305 返回指定车间指定性别员工的最高工资 342

技巧306 实现查询各科目成绩中的最高分 343

6. DMIN函数（从数据库中按给定条件求最小值） 345

技巧307 返回指定班级的最低分 345

技巧308 实现查询各科目成绩中的最低分 346

7. DGET函数（从数据库中提取符合条件的单个值） 347

技巧309 在列表或数据库中按条件查询 347

8. DPRODUCT函数（从数据库中返回满足指定条件的数值的乘积） 348

技巧310 判断指定类别与品牌的商品是否被维修过 349

9. DSTDEV函数（按条件计算样本标准偏差） 350

技巧311 计算女性员工工龄的样本标准偏差 351

技巧312 计算女性员工工龄的总体标准偏差 352

11. DVAR函数（按条件计算样本总体方差） 352

技巧313 计算女性员工工龄的样本总体方差 353

技巧314 计算女性员工工龄的总体方差 354

  第11章 用公式设置单元格格式及限制数据输入 355

11.1 函数在条件格式中的应用 355

技巧315 判断两列数据是否完全相同 355

技巧316 自动标识值班安排表中的周末日期 357

技巧317 为不包含某一特定字符的单元格设置格式 359

技巧318 将成绩高于平均值的标注为“优” 360

技巧319 突出标识非数字的单元格 361

技巧320 满足条件的整行突出显示 362

技巧321 突出显示每行的最高最低分 364

技巧322 次日值班人员自动提醒 365

技巧323 库存量过小时提示补货 367

11.2 函数在数据有效性中的应用 368

技巧324 避免输入重复的产品编码 368

技巧325 提示出货数量大于进货数量 370

技巧326 避免输入文本值 371

技巧327 限制输入的数据必须包含两位小数 372

技巧329 禁止录入不完整的产品规格 374

技巧330 设置单元格输入必须包含指定内容 375

技巧331 限制数据输入的长度 376

问题1 想复制公式却找不到填充柄 378

问题2 利用公式计算时不显示计算结果只显示公式 379

问题3 两个日期相减时不能得到差值天数，却返回一个日期值 380

问题4 更改数据源的值，公式的计算结果为什么不自动更新 381

问题5 公式引用单元格明明显示的是数据，计算结果却为0 381

问题6 数字与“空”单元格，结果却报错 382

问题7 新输入的行中不能自动填充上一行的公式 383

问题8 合并单元格中公式怎么复制 384

问题9 LOOKUP查找总是找不到正确结果 385

问题10 VLOOKUP查找时，查找内容与查找区域首列内容不精确匹配，用办法 

问题11 日期数据与其他数据合并时，不能显示原样日期，只显示一串序列 号 387

问题12 在设置按条件求和（按条件计数等）函数的criteria（用于条件判 

断的）参数时，如何处理条件判断问题 388

问题13 解决浮点运算造成ROUND函数计算不准确的问题 389

2. 公式返回错误值问题 390

问题14 公式返回“ DIV>0!”错误值（“0”值或空白单元格被作除数）  390

问题15 公式返回“ N>A”错误值（公式中引用的数据源不正确或不能使用 ） 391

问题16 公式返回“ NAME?”错误值1（输入的函数和名称拼写错误） 392

问题17 公式返回“ NAME?”错误值2（公式中使用文本作为参数时未加双引 号） 393

问题18 公式返回“ NAME?”错误值3（在公式中使用了未定义的名称） 393

问题19 公式返回“ NAME?”错误值4（引用其他工作表时工作表名称包含空 格） 394

问题20 公式返回“ NUM!”错误值（引用了无效的参数） 395

问题21 公式返回“ VALUE!”错误值1（公式中将文本类型的数据参与了数 

问题22 公式返回“ VALUE!”错误值2（公式中函数使用的参数与语法不一 

”组合键结束） 397

问题24 公式返回“ REF!”错误值（公式计算中引用了无效的单元格） 397

问题25 滑动鼠标中键向下查看数据时，工作表中的内容却随之进行缩放  398

问题26 要输入一串产品代码，按下回车后显示为了科学计数方式的数字  398

问题27 填充序号时不能自动递增 399

问题28 填充时间时为何不能按分钟数（或秒数）递增 400

问题29 向单元格中输入数据老是弹出对话框 401

问题30 对于文本型数字，为它应用了“数值”格式后怎么还是没有变成数 值（无法计算） 402

问题31 只想复制筛选结果但粘贴后是所有未筛选前的数据 403