Excel 2007函数与公式实战技巧精粹

绪论最佳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
2.1运算符的类型及用途14
2.2公式的运算顺序15
技巧3透视“单元格引用”16
3.1A1引用16
3.2R1C1引用17
3.3引用运算符17
3.4相对引用18
3.5绝对引用19
技巧4灵活处理跨工作表、工作簿引用20
4.1跨表引用20
4.2跨工作簿引用21
技巧5公式的快速批量复制21
技巧6公式的自动扩展22
技巧7公式中的结构化引用23
7.1直观的结构化引用23
7.2自动扩展25
技巧8慧眼识函数26
技巧9函数的类型27
技巧10Excel2007函数的新特性29
技巧11函数参数的省略与简写29
技巧12使用通配符进行模糊匹配30
技巧13通配符在多表引用中的妙用32
技巧14认识公式中的数据33
14.1数据类型33
14.2数据排序规则33
14.3常量数据33
技巧15文本型数字转换为数值34
15.1利用查错工具批量转换34
15.2文本型数字转数值的公式方法35
技巧16逻辑值与数值的互换36
16.1逻辑值与数值互换准则36
16.2用数学运算替代逻辑函数37
技巧17正确区分空文本与空单元格38
17.1空单元格与空文本的差异38
17.2防止空单元格影响统计结果39
17.3让空单元格不显示为039
技巧18计算规范与限制40
18.1数字计算精度限制40
18.2函数嵌套层数限制41
18.3函数参数个数的限制41
技巧19以显示的精度进行计算42
技巧20易失性函数43
20.1什么是易失性43
20.2具有易失性表现的函数43
20.3易失性的利用与规避44
技巧21自动重算和手动重算44
技巧22循环引用和迭代计算45
22.1For
22.2Do
22.3记录操作时间48
22.4已知和值求组合49
技巧23自动完成公式50
技巧24妙用函数提示51
24.1提示函数语法51
24.2参数含义提示52
24.3快速选定相应参数52
24.4获取函数帮助信息52
技巧25公式的查错53
25.1公式常见错误类型53
25.2错误自动检查54
技巧26公式审核和监控55
26.1错误检查55
26.2审核和监控55
技巧27分步查看公式结果56
27.1使用公式审核工具分步求值57
27.2用F9键查看公式运算结果58
技巧28如何阅读和理解复杂公式58
第2章数组公式入门62
技巧29神奇的数组62
29.1数组的概念及分类62
29.2数组的维度和尺寸63
技巧30多项计算和数组公式63
30.1多项计算63
30.2数组公式64
技巧31多单元格数组公式65
技巧32数组的直接运算66
32.1数组与单值直接运算66
32.2同方向一维数组之间的直接运算66
32.3不同方向一维数组之间的直接运算67
32.4一维数组与二维数组之间的直接运算67
32.5二维数组之间的直接运算68
技巧33数组公式中的逻辑运算69
第3章灵活使用名称71
技巧34名称的奥秘71
技巧35多种方法定义名称73
35.1使用“定义名称”功能73
35.2使用名称框创建74
35.3根据所选内容批量创建75
技巧36名称的规范与限制76
技巧37通过名称定位区域77
技巧38以图形方式查看名称77
技巧39查看和修改名称78
39.1导出名称列表78
39.2编辑修改名称78
39.3删除名称79
技巧40认识名称的作用范围80
技巧41根据单元格颜色进行统计81
技巧42固定引用区域的名称82
技巧43使用名称动态引用图片83
技巧44在其他功能中使用名称85
44.1在条件格式和有效性中使用他表数据85
44.2在图表中使用名称86
技巧45创建动态引用名称87
技巧46自动更新的打印区域88
第二篇函数常用技巧91
第4章逻辑与信息函数92
技巧47逻辑函数的判断功能92
技巧48逻辑值在条件汇总中的妙用93
48.1条件计数93
48.2条件汇总94
48.3多条件逻辑94
技巧49快速判决数据的简单特性95
技巧50从身份证号码中提取性别96
技巧51数据的模糊汇总97
技巧52获取当前操作环境信息97
技巧53捕捉当前活动单元格信息98
技巧54提取工作表和工作簿的名称100
54.1提取工作表名称100
54.2提取工作簿名称101
技巧55当公式报错时返回空白101
55.1旧版美化公式102
55.2新版简化公式102
技巧56N函数在函数公式中的应用103
56.1将逻辑值转化为数值103
56.2生成内存数组104
技巧57强制INDEX函数返回内存数组105
第5章文本处理函数107
技巧58意义非凡的文本连接符&107
技巧59处理文本的基本手段108
59.1文本截取108
59.2查找定位109
59.3文本替换110
59.4文本比较112
59.5文本重复112
技巧60处理字符的基本技巧112
技巧61生成字母序列ABC115
61.1CHAR+CODE方式115
61.2列标提取方式116
技巧62生成可换行的文本116
技巧63规整不规范的日期数据118
63.1MID函数文本截取法119
63.2TEXT函数方式119
技巧64巧用身份证填写个人信息120
64.1提取性别121
64.2出生日期121
64.3年龄121
技巧65财务数字金额拆分实例122
技巧66分离中文、数字和字母123
66.1分离中文与数字124
66.2字母与中文124
66.3提取数字125
技巧67统计不重复数字个数126
技巧68根据固定间隔符号截取字符串127
68.1FIND+SUBSTITUTE127
68.2展宽隔离符号127
技巧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
76.1基本取整函数139
76.2指定数字位舍入140
76.3特定基数取整141
76.4舍入至奇数和偶数141
技巧77特定条件的舍入设计142
77.1设计一舍二入方式142
77.20.2单位和0.5单位舍入143
技巧78四舍六入五单双143
技巧79指定有效数字144
技巧80驾驭求模函数145
80.1初始求模函数145
80.2判断数字奇偶性146
80.3生成循环序列147
技巧81正整数逐位累加至1位数字148
81.1速算法148
81.2速算扩展149
技巧82数字校验码的应用149
技巧83随机产生数字与字母150
83.1初识随机函数150
83.2随机产生数字与字母150
技巧84生成不重复随机序列151
84.1辅助列方式152
84.2直接方式152
第7章日期与时间函数153
技巧85认识日期与时间数据153
85.1日期与时间数据的本质153
85.2日期与时间数据的识别153
85.3输入日期时间的注意事项154
技巧86基本的日期函数154
86.1日期生成函数154
86.2年、月、日函数155
86.3当前日期155
86.4星期相关函数155
86.5工作日相关函数156
技巧87基本的时间函数157
87.1时间生成函数157
87.2时、分、秒158
87.3当前时间158
技巧88处理1900年之前的日期158
技巧89判断闰年和平年159
技巧902014年巴西世界杯倒计时160
技巧91快速确定员工的退休日期160
技巧92判断一个日期所在的季度161
技巧93推算所在季度的季末日期162
技巧94计算日期所在季度天数163
技巧95计算指定日期邻近月份的天数163
技巧96动态生日提醒164
技巧97计算公司员工的工龄165
97.1以年为单位165
97.2精确到月份166
技巧98身份证中提取周岁信息167
技巧99计算食品的保质期169
技巧100目标日期是所在年第几天169
100.1点对点相减169
100.2日期值序列170
技巧101计算两个日期相差的年月日数170
技巧102下一个星期日和最近的星期日171
102.1日期值序列思路171
102.2星期几的归零思路172
技巧103指定日期范围内星期几出现的次数172
技巧104推算指定月份第几个星期几的日期173
技巧105日期A从日期B算起是第几周173
技巧106计算指定月份第几周的天数174
技巧107计算两个日期之间的净工作日175
技巧108根据停车时间计算费用177
108.1计费时间177
108.2每小时费用178
108.3总费用178
技巧109将时间转换成标准格式178
技巧110三班倒工作时长计算179
技巧111在时间日期数据中提取时间或日期180
111.1提取日期数据180
111.2提取时间数据180
技巧112生成指定范围的随机时间181
112.1以分为单位随机变化181
112.2以秒为单位随机变化181
第8章查找与引用函数182
技巧113动态按行或列生成序列号182
技巧114认识INDIRECT函数183
技巧115建立恒定的引用区域183
技巧116轻松调用名称和“表”数据184
技巧117应用INDIRECT函数组合查询185
技巧118实现数据区域的行列转换186
技巧119引用区域的位置变换187
技巧120批量生成工资条187
技巧121现解OFFSET函数188
121.1图解OFFSET参数含义188
121.2参数为数组产生多维引用189
技巧122应用OFFSET函数分析销售业绩190
技巧123按自定义顺序排序191
技巧124根据多个条件查询数据192
技巧125查找不重复值193
自动编号193
技巧127根据首行或首列查找记录194
技巧128为股票名称编制拼音简码196
技巧129模糊查找符合条件的记录198
技巧130模糊转换数据198
技巧131应用LOOKUP函数实现单条件多值求和199
技巧132多区间判断数值等级200
技巧133乱序查找最后一个满足条件的记录201
技巧134填补合并单元格的空缺202
技巧135多条件逆序筛选202
技巧136多条件顺序筛选203
技巧137根据查找结果建立超链接204
第9章统计分析函数206
技巧138统计总分前N名的平均成绩206
技巧139AVERAGEIF和AVERAGEIFS函数求解条件均值207
技巧140返回数据表中的最小正数208
技巧141利用极限函数设定销售限价209
技巧142实现多表统计人员工资209
技巧143校验编码中数字位数的有效性211
技巧144认识COUNTA与COUNTBLANK函数212
技巧145COUNTIF函数条件判断介绍213
技巧146无序情况下的分类汇总215
技巧147查找不重复值216
技巧148单字段多条件计数217
技巧149多字段多条件计数217
技巧150应用SUMPRODUCT函数多条件计数218
技巧151应用SUMPRODUCT函数多条件求和218
技巧152应用SUMPRODUCT函数排序219
技巧153计算绝对值之和220
技巧154二维区域条件求和220
技巧155计算综合得分222
技巧156多字段多条件求和223
技巧157多表数据条件求和223
技巧158FREQUENCY函数技巧二则224
技巧159按成绩实现班级和年级排名226
技巧160计算百分位排名227
技巧161剔除极值算平均得分228
技巧162筛选和隐藏状态下的统计229
技巧163生成连续的序号230
技巧164众数的妙用231
第10章数据库函数233
技巧165数据库函数的基本技巧233
技巧166利用通配符查询产品信息235
技巧167理解条件区域使用相对引用产生的偏移236
技巧168统计指定条件下公司部门的人员数238
技巧169统计企业各部门的人员工资238
技巧170返回车间生产产品的最低成本239
技巧171计算商场指定商品的平均销售价格240
技巧172提取符合条件的信息240
第11章财务金融函数242
技巧173普通复利终值与现值计算242
技巧174年金终值与现值计算243
技巧175固定利率下混合现金流的终值计算244
技巧176变动利率下混合现金流的终值计算244
技巧177固定资产投资的动态回收期计算245
技巧178现金流不定期条件下的决策分析246
技巧179名义利率与实际利率248
技巧180债券发行价格的计算250
技巧181每年付息债券的持有收益率计算251
技巧182折旧计算函数251
第12章工程函数256
技巧183贝塞尔（Bessel）函数256
技巧184度量衡转换函数257
技巧185数字进制转换函数258
技巧186用于筛选数据的函数259
技巧187误差函数相关计算的函数262
技巧188复数计算的函数263
第13章宏表函数264
技巧189认识宏表及宏表函数264
技巧190取指定路径下的所有文件名269
技巧191判断是否打开了指定的工作簿270
技巧192取得当前工作簿中所有工作表名称271
技巧193取得当前行所在的打印页码271
技巧194取得单元格属性值272
技巧195计算文本表达式中的结果274
技巧196将文本字符串变成常量数组276
第14章循环引用277
技巧197认识循环引用277
技巧198记录单元格操作时间278
技巧199循环引用计算的一般技巧279
技巧200利用循环引用实现字符串连接280
第三篇函数综合应用技巧281
第15章数字及文本处理技术282
技巧201提取文本中的数值282
技巧202提取文本中所有的数字284
技巧203数值格式化修约286
技巧204插值计算286
技巧205提取带有固定分割符文本中的一项内容287
技巧206提取含有某些特性的文本288
第16章条件筛选技术291
技巧207一列数据提取不重复数据291
技巧208二维表提取不重复值292
技巧209带条件的提取不重复值293
技巧210统计唯一值的数量296
技巧211根据铁路里程表筛选里程297
技巧212用先进先出法计算出货价值297
第17章数据排名与排序技术299
技巧213对数据进行中式排名299
技巧214按排名占比确定等级301
技巧215对相同成绩平均排名名次301
技巧216对数值大小进行排序302
技巧217对文本进行排序303
技巧218按自定义顺序排序305
技巧219按数据出现次数排序305
第18章数据重构技巧307
技巧220数字的按位与或非307
技巧221利用MMULT函数实现数据累加308
技巧222几个典型构造矩阵的应用308
技巧223计算各类货品最大入货量中的最小值311
技巧224填充合并单元格312
第19章数据表处理技巧313
技巧225对数据总表的模糊查询313
技巧226按日期和品牌进行汇总316
技巧227按客户名对送货清单进行分组318
技巧228将分表数据合并到总表320
技巧229将总表数据拆分到分表321
第20章处理日期数据323
技巧230转换中文日期为规范日期323
技巧231将标准日期转为英文日期324
技巧232推算母亲节和父亲节324
技巧233制作简易万年历325
技巧234阳历与农历之间的转换327
技巧235推测某年度二十四节气的具体日期330
技巧236推算阳历日期所属生肖年331
技巧237年月日时的天干地支333
第21章多维引用技术338
技巧238计算应收款账龄338
技巧239统计营销任务完成的员工人数339
技巧240多表多条件汇总技术341
技巧241创建动态适应筛选条件的统计公式342
技巧242对数据列进行分区域统计343
第四篇其他功能中的函数应用345
第22章条件格式中使用函数346
第23章数据有效性中使用函数367
第24章数据透视表函数综合应用382
第25章图形制作中的函数应用406
第五篇实例作证415
第26章经典实例416
技巧279先进先出计算进货库存和销售成本416
技巧280汽车里程修理费补贴计算421
技巧281动态有效性点菜单423
技巧282速算个人所得税425
技巧283提示丰富的万年历426
技巧284加油站投资项目动态盈亏平衡分析433
技巧285按照BOM表自动生成材料用量计划表439
技巧286动态统计截至当月的计划完成率441
技巧287从多张工作表中按需查找引用数据442
技巧288根据房间人数智能分摊水电费