绪论 最佳Excel学习方法 1
1成为Excel高手的捷径 1
1.1积极的心态 1
1.2正确的学习方法 2
1.3善用资源，学以致用 4
1.4多阅读，多实践 5
2通过互联网获取学习资源和解题方法 5
2.1搜索引擎的使用 6
2.2搜索业内网站的内容 7
2.3在新闻组或BBS中学习 7
2.4在视频网站中学习 10
2.5利用微博和微信学习 10
3函数与公式学习指南 11
3.1打好函数基础 11
3.2由浅入深，由简至繁学好函数公式 11
3.3学好函数公式的基本方法 11
3.4厚积薄发、脱胎换骨 12
3.5学以致用，自由驰骋 12
第一篇 函数导读 13
第1章 函数与公式基础介绍 14
技巧1什么是公式 14
技巧2运算符的类型及用途 14
技巧3公式的运算顺序 15
技巧4透视“单元格引用” 16
4.1A1引用 16
4.2R1C1引用 17
4.3引用运算符 17
4.4相对引用 18
4.5绝对引用 19
技巧5灵活处理跨工作表、工作簿引用 21
5.1跨表引用 21
5.2跨工作簿引用 21
技巧6公式的快速批量复制 22
技巧7公式的自动扩展 23
技巧8公式中的结构化引用 24
8.1直观的结构化引用 24
8.2自动扩展 26
技巧9慧眼识函数 26
技巧10函数的类型 28
技巧11 Excel2010函数的新特性 29
技巧12函数参数的省略与简写 30
技巧13使用通配符进行模糊匹配 32
技巧14通配符在多表引用中的妙用 33
技巧15认识公式中的数据 34
15.1数据类型 34
15.2数据排序规则 35
15.3常量数据 35
技巧16文本型数字转换为数值 35
16.1利用查错工具批量转换 36
16.2文本型数字转数值的公式方法 36
技巧17逻辑值与数值的互换 38
17.1逻辑值与数值互换准则 38
17.2用数学运算替代逻辑函数 39
技巧18正确区分空文本与空单元格 40
18.1空单元格与空文本的差异 40
18.2防止空单元格影响统计结果 40
18.3让空单元格不显示为0 41
技巧19计算规范与限制 42
19.1数字计算精度限制 42
19.2函数嵌套层数限制 43
19.3函数参数个数的限制 44
技巧20以显示的精度进行计算 44
技巧21易失性函数 45
21.1什么是易失性 45
21.2具有易失性表现的函数 45
21.3易失性的利用与规避 46
技巧22自动重算和手动重算 47
技巧23循环引用和迭代计算 47
23.1记录操作时间 48
23.2已知和值求组合 49
23.3 For&Next循环 50
23.4 Do&While循环 52
技巧24自动完成公式 52
技巧25妙用函数提示 53
25.1提示函数语法 54
25.2参数含义提示 54
25.3快速选定相应参数 55
25.4获取函数帮助信息 55
技巧26公式的查错 55
26.1公式常见错误类型 55
26.2错误自动检查 57
技巧27公式的审核和监控 58
27.1错误检查 58
27.2审核和监控 58
技巧28分步查看公式结果 59
28.1使用公式审核工具分步求值 60
28.2用F9键查看公式运算结果 61
技巧29如何阅读和理解复杂公式 61
第2章 数组公式入门 65
技巧30数组的概念及分类 65
技巧31数组的维度和尺寸 65
技巧32多项计算 66
技巧33数组公式 67
技巧34多单元格数组公式 68
技巧35数组的直接运算 69
35.1数组与单值直接运算 69
35.2同方向一维数组之间的直接运算 69
35.3不同方向一维数组之间的直接运算 70
35.4一维数组与二维数组之间的直接运算 70
35.5二维数组之间的直接运算 71
技巧36数组公式中的逻辑运算 72
第3章 灵活使用名称 74
技巧37名称的奥秘 74
技巧38多种方法定义名称 76
38.1使用“定义名称”功能 76
38.2使用名称框创建 77
38.3根据所选内容批量创建名称 78
技巧39名称的规范与限制 80
技巧40通过名称定位区域 80
技巧41以图形方式查看名称 81
技巧42查看和修改名称 82
42.1导出名称列表 82
42.2编辑修改名称 83
42.3删除名称 83
技巧43认识名称的作用范围 84
技巧44根据单元格颜色进行统计 85
技巧45固定引用区域的名称 86
技巧46使用名称动态引用图片 87
技巧47在其他功能中使用名称 89
47.1在条件格式和有效性中使用他表数据 89
47.2在图表中使用名称 91
技巧48创建动态引用名称 91
技巧49自动更新的打印区域 92
第二篇 函数常用技巧 95
第4章 逻辑与信息函数 96
技巧50 IF函数评定业务员等级 96
技巧51逻辑值在条件汇总中的应用 97
51.1判断员工是否符合退休条件 97
51.2逻辑函数与乘法、加法运算 98
技巧52数据类型的判断检验 99
52.1屏蔽公式返回的错误值 100
52.2有错误值的数据求和 101
技巧53利用ISNUMBER函数模糊汇总 101
技巧54根据身份证号码判断性别 102
技巧55设置自动更新的文件标题 103
技巧56制作方便核对数据的聚光灯 104
技巧57为公式添加注解信息 106
第5章 文本处理函数 107
技巧58意义非凡的文本连接符& 107
技巧59处理文本的基本手段 108
59.1文本截取 108
59.2查找定位 109
59.3文本替换 110
59.4文本比较 112
59.5文本重复 112
技巧60处理字符的基本技巧 112
60.1字符和数字代码 112
60.2字母大小写转换 113
60.3字符全半角转换 114
60.4删除多余的空格 114
60.5处理非打印字符 115
技巧61生成字母序列ABC 115
61.1 CHAR+CODE方式 115
61.2列标提取方式 116
技巧62生成可换行的文本 117
技巧63规整不规范的日期数据 118
63.1 MID函数文本截取法 118
63.2 TEXT函数方式 119
技巧64巧用身份证填写个人信息 120
64.1提取性别 120
64.2出生日期 121
64.3年龄 122
技巧65财务数字金额拆分实例 122
技巧66分离中文、数字和字母 124
66.1分离中文与数字 124
66.2字母与中文 125
66.3提取数字 125
技巧67统计不重复数字个数 126
技巧68根据固定间隔符号截取字符串 127
68.1 FIND+SUBSTITUTE 127
68.2展宽隔离符号 128
技巧69根据间隔符统计项目个数 128
技巧70格式化设备编号 129
技巧71数字格式化为中文数字 130
71.1数字整体转换 130
71.2逐字符转换 131
71.3日期格式 131
技巧72转换中文大写金额 132
技巧73以小时累计时间 132
技巧74合并单元格区域中的文本 133
第6章 数学计算函数 135
技巧75基本的数学计算函数 135
75.1求和与求积 135
75.2乘幂与对数 136
75.3组合数与阶乘 137
75.4最小公倍数和最大公约数 137
75.5进制转换函数 138
75.6绝对值和π值 138
75.7弧度转换为度数 139
75.8符号函数 139
技巧76基本取整函数 139
技巧77指定数字位舍入 140
技巧78特定基数取整 141
技巧79新增的基数取整函数 142
技巧80舍入至奇数和偶数 143
技巧81特定条件的舍入设计 143
81.1设计一舍二入方式 143
81.2 0.2单位和0.5单位舍入 144
技巧82四舍六入五单双 144
技巧83指定有效数字 145
技巧84初识求模函数 146
技巧85判断数字奇偶性 147
技巧86生成循环序列 148
技巧87随机产生数字与字母 149
87.1初识随机函数 149
87.2随机产生数字与字母 150
技巧88生成不重复随机序列 150
88.1辅助列方式 151
88.2直接方式 151
第7章 日期与时间函数 153
技巧89认识日期与时间数据 153
89.1日期与时间数据的本质 153
89.2日期与时间数据的识别 153
89.3输入日期时间的注意事项 154
技巧90基本的日期函数 154
90.1日期生成函数 154
90.2年、月、日函数 155
90.3当前日期 156
90.4星期相关函数 156
技巧91基本的工作日相关函数 156
技巧92增强的工作日相关函数 157
技巧93基本的时间函数 158
93.1时间生成函数 158
93.2时、分、秒 159
93.3当前时间 160
技巧94处理1900年之前的日期 160
技巧95判断闰年和平年 161
技巧96 2014年巴西世界杯倒计时 162
技巧97快速确定员工的退休日期 162
技巧98判断一个日期所在的季度 163
技巧99推算所在季度的季末日期 164
技巧100计算日期所在季度天数 165
技巧101计算指定日期邻近月份的天数 165
技巧102动态生日提醒 166
技巧103计算公司员工的工龄 167
103.1以年为单位 167
103.2精确到月份 168
技巧104身份证中提取生日与周岁信息 169
技巧105目标日期是所在年第几天 171
105.1点对点相减 171
105.2日期值序列 171
技巧106计算两个日期相差的年月日数 171
技巧107下一个星期日和最近的星期日 172
107.1日期值序列思路 172
107.2星期几的归零思路 173
技巧108指定日期范围内星期几出现的次数 173
技巧109推算指定月份第几个星期几的日期 174
技巧110日期A从日期B算起是第几周 175
技巧111计算指定月份第几周的天数 176
技巧112根据停车时间计算费用 177
112.1计费时间 177
112.2每小时费用 178
112.3总费用 178
技巧113将时间转换成标准格式 178
技巧114三班倒工作时长计算 179
技巧115在时间日期数据中提取时间或日期 180
115.1提取日期数据 180
115.2提取时间数据 180
技巧116生成指定范围的随机时间 181
116.1以分为单位随机变化 181
116.2以秒为单位随机变化 181
第8章 查找与引用函数 182
技巧117认识INDIRECT函数 182
技巧118恒定的引用区域 183
技巧119轻松调用名称和“表”数据 183
技巧120返回指定时间段的销售额 184
技巧121生成自然数序列 185
技巧122将单列转换为多行多列 186
技巧123认识OFFSET函数 186
技巧124 OFFSET函数偏移方式 187
技巧125以数组为参数产生多维引用 189
技巧126计算最近N天的平均销售额 189
技巧127使用OFFSET函数生成工资条 190
技巧128使用CHOOSE函数完成等级考评 191
技巧129使用INDEX函数隔列取值 192
技巧130二维数据表中查询数据 192
技巧131不重复姓名提取 194
技巧132认识VLOOKUP函数和HLOOKUP函数 195
技巧133正向查询员工信息 195
技巧134逆向查询员工信息 196
技巧135提成比例查询 197
技巧136认识LOOKUP函数 197
技巧137根据客户简称查询全称 198
技巧138有合并单元格的数据查询 199
技巧139计算各营业部销售总额 200
技巧140创建有超链接的工作表目录 201
第9章 统计分析函数 202
技巧141统计销量前N名的平均销售额 202
技巧142求解条件均值 203
技巧143设定社保缴费基数上下限 204
技巧144统计各部门职工学历情况 205
技巧145多表统计人员工资补助 206
技巧146统计有效调查问卷 207
技巧147了解COUNTIF函数条件判断 208
技巧148统计不同区间的对应人数 209
技巧149多条件计数应用 209
技巧150统计区域中不重复值个数 210
技巧151检查重复身份证号 210
技巧152 COUNTIFS函数多条件计数 211
技巧153 SUMPRODUCT函数分类统计 212
技巧154模糊条件统计业务量 214
技巧155 SUMIF函数求和计算 214
技巧156 SUMIF函数计算商品结存 215
技巧157条件求和与多条件求和 216
技巧158不同币种统计汇总销售额 217
技巧159 SUMIFS函数多字段多条件求和 217
技巧160 SUMIFS函数多表多条件求和 218
技巧161分段统计销售业绩 219
技巧162制作销售数据汇总表 221
162.1销售额去除重复后由高到低列示 221
162.2根据销售额完成情况，列示对应业务员姓名 222
技巧163统计销售业绩排名 222
163.1部门内部业绩排名 222
163.2全公司业绩排名 223
技巧164使用RANK.AVG函数排名 223
技巧165计算百分位排名 224
技巧166计算剔除极值后的平均值 225
技巧167筛选、隐藏状态下的计算 225
技巧168筛选条件下生成连续序号 227
技巧169有错误值的统计 227
技巧170统计出现频率最高的值 229
170.1数值统计众数 229
170.2统计得票最高人员 230
技巧171统计筛选状态下的不重复值 230
第10章 数据库函数 232
技巧172数据库函数的用途和用法 232
技巧173利用通配符查询产品信息 234
技巧174在条件中使用公式 235
技巧175精确匹配的几种方法 237
技巧176数据库函数的批量使用 239
技巧177使用数据库函数进行查询 240
第11章 财务金融函数 243
技巧178普通复利终值与现值计算 243
178.1普通复利终值计算 243
178.2普通复利现值计算 243
技巧179年金终值与现值计算 244
179.1年金终值计算 244
179.2年金现值计算 244
技巧180固定利率下混合现金流的终值计算 245
技巧181变动利率下混合现金流的终值计算 246
技巧182固定资产投资的动态回收期计算 246
技巧183现金流不定期条件下决策分析 247
183.1计算净现值 248
183.2计算内部收益率 248
技巧184名义利率与实际利率 250
184.1将名义利率转为实际利率 250
184.2将实际利率转为名义利率 250
技巧185债券发行价格的计算 251
技巧186每年付息债券的持有收益率计算 252
技巧187折旧计算函数 252
187.1用直线法计算固定资产折旧 252
187.2用年数总和法计算固定资产折旧 253
187.3用双倍余额递减法计算固定资产折旧 254
第12章 工程函数 257
技巧188贝塞尔（Bessel）函数 257
技巧189度量衡转换函数 258
技巧190数字进制转换函数 259
技巧191用于筛选数据的函数 260
191.1 DLETA函数 261
191.2 GESTEP函数 262
技巧192误差函数相关计算的函数 263
192.1 ERF函数 263
192.2 ERFC函数 263
技巧193复数计算的函数 264
第13章 宏表函数 265
技巧194认识宏表及宏表函数 265
194.1什么是宏表 265
194.2建立自己的宏表 265
194.3如何让宏表程序工作 266
194.4认识宏表函数 268
194.5在Excel中触发重新计算的条件及解决方法 269
技巧195取指定路径下所有文件名 269
技巧196判断是否打开了指定的工作簿 270
技巧197取得当前工作簿中所有工作表名称 271
技巧198取得当前行所在的打印页码 271
技巧199获取单元格属性值 272
技巧200利用分级显示的级数来计算提成量 273
技巧201提取单元格数字格式进行计算 274
技巧202计算文本表达式的结果 274
技巧203将文本字符串变成常量数组 276
技巧204按背景色统计 277
第三篇 函数综合应用技巧 279
第14章 循环引用 280
技巧205认识循环引用 280
技巧206记录单元格操作时间 281
技巧207循环引用计算的一般技巧 282
技巧208利用循环引用实现字符串连接 283
第15章 数字及文本处理技术 284
技巧209提取文本中的数值 284
209.1基础知识 284
209.2当文本中只有一处数值时提取数值 284
209.3当文本中只有一处数值时提取数值文本 285
209.4当文本中有多段数值时提取某段数值 286
技巧210提取文本中所有数字按原序排列 286
技巧211提取文本中不重复数字按升序排列 287
技巧212数值格式化修约 288
技巧213插值计算 288
技巧214提取带有固定分割符文本中的一项内容 289
技巧215提取含有某些特性的文本 290
215.1基础知识 290
215.2只有一段单字节字符文本的提取 291
215.3多段文本取其中一段 292
第16章 条件筛选技术 293
技巧216一列数据提取不重复数据 293
216.1方法一 293
216.2方法二 294
技巧217二维表提取不重复值 294
技巧218筛选条件下提取唯一值 295
技巧219有其他附加条件下提取唯一值 296
技巧220另类的提取唯一值 297
技巧221统计唯一值的数量 298
技巧222根据铁路里程表筛选里程 299
技巧223用先进先出法计算出货价值 299
第17章 数据排名与排序技术 301
技巧224对一列数据进行中式排名 301
技巧225有条件的中式排名 302
技巧226按排名占比确定等级 303
技巧227对数值大小进行排序 303
227.1方法一 303
227.2方法二 304
技巧228对文本进行排序 304
228.1 MMULT函数法 304
228.2 COUNTIF 函数法 305
技巧229按自定义顺序排序 306
技巧230按数据出现次数排序 307
第18章 数据重构技巧 308
技巧231数字的按位与或非 308
技巧232利用MMULT函数实现数据累加 309
技巧233几个典型构造矩阵的应用 309
233.1行倒置 310
233.2列倒置 310
233.3全倒置 311
233.4两次复制 311
233.5插入空行 311
技巧234计算最大值中的最小值 312
技巧235填充合并单元格 313
技巧236文本型一维内存数组倒置 314
第19章 数据表处理技巧 315
技巧237对数据总表的模糊查询 315
237.1一次性显示 315
237.2分页显示 316
技巧238按日期和品牌进行汇总 318
238.1 SUMPRODUCT或SUM 函数方式 318
238.2 SUMIFS 函数方式 319
技巧239按客户名对送货清单进行分组 319
技巧240将分表数据合并到总表 321
技巧241将总表数据拆分到分表 322
第20章 处理日期数据 324
技巧242转换中文日期为规范日期 324
技巧243将标准日期转为英文日期 325
技巧244推算母亲节和父亲节 326
技巧245制作简易万年历 327
245.1普通公式 327
245.2数组公式 328
技巧246阳历与农历之间的转换 328
246.1阳历转换为农历 329
246.2农历转换为阳历 330
246.3直接使用比对表 331
技巧247推测某年度二十四节气的具体日期 332
技巧248推算阳历日期所属生肖年 333
248.1由阳历日期返回对应生肖年份 333
248.2由生肖年份返回对应的生肖 334
技巧249年月日时的天干地支 334
249.1干支纪年 334
249.2干支纪月 335
249.3干支纪日 337
249.4干支纪时 337
第21章 多维引用技术 339
技巧250计算应收款账龄 339
技巧251统计营销任务完成的员工人数 340
技巧252多表多条件汇总技术 342
技巧253创建动态适应筛选条件的统计公式 343
技巧254求取各小组最高分的总和 344
第四篇 其他功能中的函数应用 347
第22章 条件格式中使用函数 348
技巧255突出显示总分最高的学生 348
技巧256标示三个月销售量均在90以上的员工 349
技巧257按凭证号分色显示 351
技巧258日程安排提醒 352
技巧259制作动态表格线 353
技巧260突出显示包含关键字的记录 354
技巧261动态标示计划完成情况 356
技巧262按模糊条件标示符合条件的记录 358
技巧263复杂条件下的条件格式设置 360
技巧264在包含错误值的数据区域中标示特定值 361
第23章 数据有效性中使用函数 363
技巧265限制输入数据的类型 363
265.1只能输入整数 363
265.2只能输入日期 365
265.3只能输入文本 365
265.4只能输入数值 367
技巧266限制重复录入 367
技巧267只能输入身份证号码 368
技巧268根据库存量自适应的限制范围 369
技巧269使用公式设置下拉菜单式输入 371
技巧270设置二级下拉菜单 374
技巧271根据输入内容动态更新下拉选项 376
第24章 数据透视表函数综合应用 378
技巧272初识数据透视表函数 378
技巧273自动汇总方法下静态获取数据透视表数值 380
技巧274自动汇总方法下动态获取数据透视表数值 382
技巧275自定义汇总方法下获取数据透视表数据 384
技巧276特殊的Excel 2000版数据透视表函数 386
技巧277使用Excel 2000版数据透视表函数公式静态获取数据 387
技巧278使用Excel 2000版数据透视表函数公式动态获取数据 388
技巧279数据透视表函数与其他函数联合使用 390
技巧280同时引用多个字段进行计算 392
技巧281从多个数据透视表中获取数据 393
技巧282应用数据透视表函数为排名评定星级 395
技巧283应用数据透视表函数根据关键字取值 396
技巧284应用数据透视表函数制作进货单 397
技巧285计算分类百分比 399
第25章 图形制作中的函数应用 402
技巧286突出显示极值 402
技巧287制作可排序的动态条形图 404
技巧288高亮显示光标选定数据 407
技巧289制作动态滚动曲线图 408
第五篇 实例作证 413
第26章 经典实例 414
技巧290计划排产表格 414
技巧291判断火车是否晚点 415
技巧292汽车里程修理费补贴计算 416
技巧293速算个人所得税 418
技巧294提示丰富的万年历 419
294.1丰富的提示信息 420
294.2条件格式凸显特殊日期 422
294.3显示生肖图像 423
294.4轮训日期的天干地支 424
294.5当月的二十四节气 425
技巧295巧妙提取缺失的发票单号 425
技巧296加油站投资项目动态盈亏平衡分析 427
技巧297制作会计三栏账 432
技巧298按最新价格编制出库商品统计表 439
技巧299综合计算进货商品平均单价 440
技巧300制作保险缴费数据汇总表 442
附录 444
附录A Excel2010限制和规范 445
附录B Excel2010常用快捷键 448
附录C Excel2010常用函数 452