目　录

 

绪论　最佳Excel学习方法　1

01　成为Excel高手的捷径　1

02　如何学习函数与公式　5

03　使用Excel联机帮助系统　6

04　通过互联网搜索学习资源和解题方法　9

05　在新闻组或BBS中学习　10

 

第一篇　基础知识篇　13

 

第1章　函数与公式通用技巧　14

技巧1　打造函数公式常用的操作界面　14

技巧2　正确理解Excel对象模型　15

技巧3　慧眼识函数　16

技巧4　理解公式中的运算符　17

技巧5　理解公式中的数据　19

技巧6　正确区分空文本与空单元格　20

技巧7　奇妙的自定义数字格式　21

技巧8　将文本型数字转换为数值　25

技巧9　利用逻辑值与数值互换简化公式　28

技巧10　改变Excel单元格引用样式　29

技巧11　快速切换单元格引用类型　30

技巧12　掌握工作表的引用　31

技巧13　函数公式的限制与突破　32

技巧14　函数参数的省略与简写　34

技巧15　函数的分类与加载应用　35

技巧16　常用函数公式选项设置技巧8则　38

技巧17　快速查找和输入函数　43

技巧18　快速复制函数公式的4种方法　44

技巧19　函数提示工具使用技巧3则　45

技巧20　分步查看公式计算结果　46

技巧21　庖丁解牛巧释义　48

技巧22　理解函数的易失性　49

技巧23　函数易失性的应用及规避　50

技巧24　函数公式查错与监视　51

技巧25　保护和隐藏工作表中的公式　52

 

第2章　数组公式入门　54

技巧26　认识数组的概念和特性　54

技巧27　快速输入常量数组　55

技巧28　明辨多重计算与数组公式的区别　56

技巧29　慎用逻辑函数与多重*、+计算互换　57

技巧30　理解数组之间运算的规则　59

技巧31　使用多单元格数组公式　61

技巧32　图解多维引用模型　63

 

第3章　灵活使用名称　67

技巧33　使用名称的6大理由　67

技巧34　定义名称的3种方法　68

技巧35　名称命名的规则　69

技巧36　修改名称引用位置　70

技巧37　认识名称的作用范围　70

技巧38　快速查看名称中的公式　71

技巧39　在其他功能中使用名称　72

技巧40　相对引用定义名称的妙用　73

技巧41　定义固定引用区域的名称　74

技巧42　列表功能创建动态引用名称　75

技巧43　记录单功能创建动态引用名称　77

技巧44　使用函数创建动态引用名称　78

技巧45　快速选择名称对应的单元格区域　79

技巧46　以图形方式查看名称　79

技巧47　设置动态的打印区域和顶端标题行　80

技巧48　制作动态引用图片的人员简历　81

技巧49　快速册除所有名称　82

 

第二篇　函数常用技巧篇　83

 

第4章　逻辑与信息函数　84

技巧50　逻辑函数在实际工作中的应用　84

技巧51　获取当前操作环境信息　84

技巧52　获取当前工作表的标签名　86

技巧53　取得当前工作簿的名称　86

技巧54　避免屏蔽错误的冗余计算　87

技巧55　根据身份证号码判断性别　88

技巧56　库存表中各种品牌电视机的数量求和　88

技巧57　N函数在数组公式中的应用　89

 

第5章　文本处理函数　91

技巧58　标识首次出现的记录　91

技巧59　字符转换技巧4则　92

技巧60　把文本日期转换为真正的日期　95

技巧61　巧用身份证号码提取人员信息　97

技巧62　财务数字金额拆分实例　99

技巧63　快速查找字符技巧　100

技巧64　统计不重复数字个数　102

技巧65　根据固定间隔符号截取字符串　103

技巧66　计算职称明细表中的员工人数　104

技巧67　将身份证号码升级到18位　104

技巧68　格式化设备编号　105

技巧69　数字格式化为中文数字　106

技巧70　累计加班时间　109

技巧71　转换中文大写金额　110

技巧72　制作姓名生成器　110

 

第6章　数学计算函数　113

技巧73　驾驭神奇的余数　113

技巧74　区分INT和TRUNC函数的异同　115

技巧75　舍入函数的常用技巧　115

技巧76　数字校验码的应用　117

技巧77　按要求返回指定有效数字　117

技巧78　解决四舍六入五单双问题　118

技巧79　计扣个人所得税　119

技巧80　仅随机产生字母和数字的技巧　120

技巧81　产生不重复随机数应用　120

 

第7章　日期与时间函数　122

技巧82　认识日期数据　122

技巧83　2008北京奥运倒计时　122

技巧84　快速确定职工的退休日期　123

技巧85　判断一个日期所在的季度　124

技巧86　计算员工周岁年龄　124

技巧87　计算加工食品的保质期　125

技巧88　计算当前月份的月末日期　126

技巧89　推算目标日期所在季度末日期　128

技巧90　计算指定日期的相邻月份的天数　129

技巧91　计算目标日期所在季度天数　130

技巧92　动态生日提醒　130

技巧93　计算公司员工的工龄　131

技巧94　返回目标日期是全年中的第几天　132

技巧95　计算两个日期相差的年月日数　133

技巧96　判断指定日期最近的下一个星期日的日期　134

技巧97　测算指定日期区间内星期几的数量　135

技巧98　推算指定月份中第几个星期几的日期　136

技巧99　返回目标日期是一个学期中的第几周　136

技巧100　计算指定月份第几周的天数　137

技巧101　计算员工上月考勤天数　138

技巧102　计算购买基金确认入账的日期　139

技巧103　确定一年是否为闰年　139

技巧104　根据停车时间计算停车费用　140

技巧105　转换小时数为标准时间格式　141

技巧106　车间工作时间计算　141

技巧107　取得去除时间的日期　142

技巧108　建立指定范围的随机时间　142

 

第8章　查找引用函数　144

技巧109　动态按行或列生成序列号　144

技巧110　认识INDIRECT函数　145

技巧111　生成垂直、水平序列　146

技巧112　实现数据区域的行列转换　146

技巧113　批量生成工资条　147

技巧114　引用区域的位置变换　148

技巧115　深入了解OFFSET函数　148

技巧116　MATCH函数的应用　150

技巧117　动态计算学员总成绩　151

技巧118　统计员工最高平均工资　152

技巧119　动态制作报表表尾　153

技巧120　根据双重条件查询学员成绩　154

技巧121　根据首行或首列查找记录　155

技巧122　动态行次和列次查找　157

技巧123　逆向查询数据　158

技巧124　按类别查找最早入库产品　158

技巧125　LOOKUP函数的综合运用　159

技巧126　多条件综合查询应用　160

技巧127　多区域综合判断求值　161

技巧128　在合并单元格内进行查询　162

技巧129　返回查找结果的单元格地址　163

技巧130　一次得到引用区域的行数和列数　164

技巧131　根据查找结果建立超链接　165

技巧132　计算两地之间里程　166

技巧133　快速实现学员随机座位编排　167

 

第9章　统计分析函数　169

技巧134　实现多表统计人员工资　169

技巧135　校验编码中数字位数的有效性　170

技巧136　认识COUNTA与COUNTBLANK函数　171

技巧137　计算有效工作日的日均销售额　173

技巧138　统计前N名学生平均成绩　174

技巧139　返回数据表中最小正数　175

技巧140　巧设销售限价　176

技巧141　众数的多重运用　176

技巧142　单字段多条件计数　177

技巧143　无序情况下的分类计算　178

技巧144　查找不重复值　179

技巧145　计算绝对值之和　180

技巧146　COUNTIF和SUMIF函数中的条件判断　180

技巧147　二维区域条件求和　182

技巧148　原料采购动态汇总　183

技巧149　认识FREQUENCY函数　184

技巧150　RANK函数排名技巧　186

技巧151　剔除极值计算最后的平均得分　189

技巧152　筛选和隐藏状态下的计算　189

技巧153　筛选条件下生成连续的序号　191

 

第10章　数据库函数　193

技巧154　数据库函数的基本技巧　193

技巧155　统计指定条件下公司部门的人员数　195

技巧156　计算商场指定商品的平均销售价格　196

技巧157　统计企业各部门的人员工资　196

技巧158　返回车间生产产品的最低成本　197

技巧159　提取符合条件的信息　198

 

第11章　财务函数　199

技巧160　在nper参数中使用负数　199

技巧161　在rate参数中使用负数　200

技巧162　不同利率条件下的终值和现值　200

技巧163　计算等比年金　202

技巧164　利率转换问题　202

技巧165　多次现金流方向改变和多重内部收益率　204

技巧166　会计年度和折旧年度　205

技巧167　无息贷款的实际成本　206

技巧168　退休基金计划　206

技巧169　助学贷款　207

技巧170　信用卡的年名义利率和实际利率　208

技巧171　住房按揭贷款的等额还款方式与等本还款方式　208

技巧172　等额递减投资项目　209

 

第12章　宏表函数　210

技巧173　认识宏表函数　210

技巧174　取指定路径下所有文件名　211

技巧175　取得工作簿和工作表名　211

技巧176　取单元格属性值　213

技巧177　取得表达式和计算结果　215

技巧178　将文本表达式生成数组　217

 

第13章　循环引用　218

技巧179　认识循环引用　218

技巧180　记录单元格操作时间　218

技巧181　产生不重复随机序号　219

技巧182　产生随机不重复组合　220

技巧183　利用循环引用实现提取字符　220

 

第三篇　函数综合应用技巧篇　223

 

第14章　数字处理技术　224

技巧184　提取零件规格中的最后序号　224

技巧185　提取字符串中的连续数字　225

技巧186　提取字符串中所有数字　227

技巧187　生成不重复数字字符串　228

技巧188　数值格式化修约　229

技巧189　计算房地产评估计费金额　230

技巧190　地基层深度拆分数据　231

 

第15章　条件筛选技术　233

技巧191　提取不重复部门数据　233

技巧192　提取唯一品牌名称的高级应用　234

技巧193　二维销售明细表提取唯一客户名称　235

技巧194　根据条件统计产品出货总额　236

技巧195　根据商品类别提取不重复品牌列表　236

技巧196　筛选条件下提取唯一部门　237

技巧197　统计不重复VIP用户数　238

技巧198　根据员工加班情况补休年假　239

技巧199　取得项目招投标中特定报价　240

技巧200　确定产品销量最大的最近月份　241

 

第16章　数据排名与排序应用　242

技巧201　利用统计函数实现中国式排名　242

技巧202　按学员成绩占比划分等级　243

技巧203　根据美式排名计算实际得分　243

技巧204　快速实现员工姓名排序　244

技巧205　比赛成绩降序排序技巧　245

技巧206　使用动态引用实现成绩排名　247

技巧207　根据运动会成绩进行多条件排序　248

技巧208　利用MMULT函数实现排序技术　249

技巧209　提取商品最高销量的月份列表　250

 

第17章　数据重构技巧　251

技巧210　实现数字条码校验码的模拟算法　251

技巧211　利用MMULT函数实现数据累加　252

技巧212　二维数组转换为一维数组　252

技巧213　填充带空值的数组应用　253

技巧214　数据表空档接龙实例　254

 

第18章　数据表处理技巧　256

技巧215　动态查询数据总表应用　256

技巧216　SUMIF函数动态汇总应用　257

技巧217　生成物流公司送货明细清单　258

技巧218　分表明细数据合并总表应用　259

技巧219　将数据总表拆分到明细表　260

 

第19章　处理日期数据　262

技巧220　转换中文日期为正规日期　262

技巧221　将标准日期转为英文序数日期　262

技巧222　推算母亲节和父亲节日期　263

技巧223　制作万年历　264

 

第20章　多维引用技术　266

技巧224　统计多学科不及格人数　266

技巧225　销售量多表综合统计应用　267

技巧226　多表多条件汇总技术　269

技巧227　筛选条件下统计培训部门数　270

技巧228　统计内存最低价格的平均值　271

技巧229　根据比赛评分进行动态排名　272

技巧230　先进先出法应用　273

 

第四篇　其他功能中的函数应用篇　275

 

第21章　条件格式中使用函数　276

技巧231　动态显示有效数据　276

技巧232　设置间隔底纹技巧二则　279

技巧233　为数据表设置间隔底纹　282

技巧234　标记含有公式的单元格　283

技巧235　动态标注各学科成绩最高分　285

技巧236　查找工资表中重复员工　286

技巧237　标注两科以上不及格学员　288

技巧238　查找人员信息表中错误数据　289

技巧239　查找每天内存销售的最低价格　291

技巧240　标注连续满足条件的加班数据　293

 

第22章　数据有效性中使用函数　295

技巧241　最多输入两位小数的单价　295

技巧242　限定输入日期数据技巧两则　296

技巧243　限定员工编号的输入　299

技巧244　限制另类日期数据的输入　300

技巧245　输入设备编号的高级控制　302

技巧246　仅输入指定长度的规格型号　304

技巧247　拒绝输入重复数据　305

技巧248　结合列表功能生成动态下拉列表　307

技巧249　动态名称实现级联下拉列表　309

技巧250　过滤选择数据动态生成下拉列表　311

 

第23章　透视表函数的综合应用　313

技巧251　认识透视表函数　313

技巧252　动态获取透视表数据　314

技巧253　在透视表函数参数中使用公式　315

技巧254　在透视表函数中运用内存数组　317

技巧255　量身定做自己的数据表　318

 

第24章　图表中的函数应用　322

技巧256　用IF函数设置判断条件突现数据点　322

技巧257　用CHOOSE函数动态选择源数据　322

技巧258　用OFFSET和COUNTA函数推移数据源　324

技巧259　直观反映多组数据差异的大小和方向　325

技巧260　筛选状态下的分类汇总图　327

技巧261　构建内存数组制作动态复合饼图　329

技巧262　使用内存数组构建按特殊规则分布的分类标签　332

 

第五篇　实例作证篇　335

 

第25章　精典实例　336

技巧263　辅助单元格区域在查询中的技巧二则　336

技巧264　多维引用在连续时段降雨量统计中的应用　339

技巧265　通过分析表循环引用套算多个单价　341

技巧266　宏表函数制作公文管理系统　343

技巧267　打造单工作簿多表单台账系统　345

技巧268　根据资源曲线分配资源数量　349

技巧269　制作计划与实际进度对比甘特图　351

技巧270　现金日记账与现金日报表的设计　354

技巧271　方格网土方计算表的简易设计　357

技巧272　以时间为序列的海量记录双条件求和　361

技巧273　制作出口货物装箱单打印标签　362

技巧274　加油站投资项目动态盈亏平衡分析　363

技巧275　合并全年现金日记账　370

技巧276　巧算跳高决赛成绩及排名　373

技巧277　根据送货出库单汇总发货明细　376

技巧278　重组项目与分类应用　378

技巧279　学员考试成绩分析应用　380

技巧280　多个工作簿自动汇总原材料管理台账系统　386

 

附录　395

附录A　Excel常用快捷键　396

附录B　Excel常用函数　401

附录C　Excel的各种规范与限制　412

附录D　Excel简、繁、英文词汇对照表　415

附录E　Excel相关的网站推荐　417

附录F　光盘内容介绍　418