Excel Home编著的《Excel2010函数与公式实战技巧精粹(附光盘)》汇集了用户在使用Excel 2010函数与公式过程中最常见的需求，通过300个技巧的演示与讲解，将Excel高手的过人技巧手把手地教给读者，并帮助读者发挥创意，灵活有效地使用Excel函数和公式来处理工作中遇到的问题。《Excel2010函数与公式实战技巧精粹(附光盘)》主要面向Excel 2010中高级读者，对于初级读者也有一定的参考价值。

通过对Excel Home技术论坛中上百万个提问的分析与提炼，Excel Home编著的《Excel2010函数与公式实战技巧精粹(附光盘)》汇集了用户在使用Excel 2010函数与公式过程中最常见的需求，通过300个技巧的演示与讲解，将Excel高手的过人技巧手把手地教给读者，并帮助读者发挥创意，灵活有效地使用Excel函数和公式来处理工作中遇到的问题。《Excel2010函数与公式实战技巧精粹(附光盘)》分为5篇26章，函数导读篇介绍了函数与公式基础知识、数组公式入门、名称的使用等内容；函数常用技巧篇介绍了逻辑与信息函数、文本处理函数、数学计算函数、日期与时间函数、查找与引用函数、统计分析函数、数据库函数、财务金融函数、工程函数、宏表函数的常用技巧；函数综合应用技巧篇介绍了循环引用、数字及文本处理技术、条件筛选技术、数据排名与排序技术、数据重构技巧、数据表处理技巧、处理日期数据、多维引用技术等内容；其他功能中的函数应用篇介绍了条件格式中使用函数、数据有效性中使用函数、数据透视表函数综合应用、图形制作中的函数应用等内容；实例作证篇列举了多个Excel函数与公式服务于企业实际应用的实例。

《Excel2010函数与公式实战技巧精粹(附光盘)》内容丰富、图文并茂、可操作性强且便于查阅，能有效地帮助读者提高Excel 2010的函数与公式使用水平，提升工作效率。《Excel2010函数与公式实战技巧精粹(附光盘)》主要面向Excel 2010中高级读者，对于初级读者也有一定的参考价值。

绪论　最佳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.1　A1引用　16

4.2　R1C1引用　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　Excel 2010函数的新特性　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　Excel 2010限制和规范　445

附录B　Excel 2010常用快捷键　448

附录C　Excel 2010常用函数　452