函数篇CHAPTER 01 函数的基本知识 1

01　求和按钮达人 ····································································2

STEP01　快速输入求和的技巧 ··················································2

STEP02　计算平均值、计数也是如法炮制 ································4

STEP03　聪明的按钮 ·····························································6

02　所谓的函数是何方神圣 ······················································9

STEP01　按钮到底做了些什么 ···············································9

STEP02　什么是函数 ·······························································10

STEP03　什么是参数 ·······························································10

STEP04　什么是引用 ······························································· 11

03　函数的输入方法·······························································13

STEP01　只用键盘输入 ···························································13

STEP02　用“键盘+ 鼠标”输入 ············································15

STEP03　用“插入函数”对话框输入······································17

04　使用拖动就能更改引用区域 ·············································20

STEP01　引用区域的确认························································20

STEP02　引用区域的更改························································21

05　函数功能大提升·······························································24

06　练习题 ·············································································26

问题01　快速将指定区域的合计值显示在不相邻的单元格中 ···26

问题02　将引用区域更改为其他工作表 ···································27

问题03　难题！几乎无限大的加法工具 ···································27

函数篇CHAPTER 02　谜一样的$ 29

01　复制函数 ·········································································30

STEP01　复制公式 ··································································30

STEP02　引用区域被任意调整而感到困惑的范例····················31

STEP03　什么是相对引用························································32

STEP04　粉墨登场——**引用中的“$” ····························33

STEP05　各种**引用 ···························································34

02　排出次序的RANK 函数 ···················································40

STEP01　输入RANK 函数·······················································40

STEP02　设置为**引用························································42

STEP03　从分数低的人开始排名次 ·········································44

03　zui方便的查询函数——VLOOKUP ··································46

STEP01　VLOOKUP 的基础知识 ············································46

STEP02　VLOOKUP 参数详解 ················································48

STEP03　再度登场的**引用 ················································49

 

04　无论是行列转置还是跳过一格都能随意引用的OFFSET

函数 ················································································53

STEP01　不可思议！纵向数据转换成横向数据 ·······················53

STEP02　对角线上的引用························································57

STEP03　不管跳一格还是跳两格都可使用OFFSET 函数 ·······60

05　函数大提升 ······································································61

06　练习题 ·············································································62

问题01　复杂的行加列**引用 ··············································62

问题02　求出累加值 ································································62

问题03　使用一张对照表来连续查找多列数据 ························63

函数篇CHAPTER 03　灵活运用文本 65

01　连接字符串 ······································································66

STEP01　利用“&”连接符来连接字符串································66

STEP02　连接〒、邮政编码、地址，并以空格分隔 ················67

STEP03　能记住CONCATENATE 的拼法吗 ···························68

02　转换文本的类型·······························································70

STEP01　切换大写、小写························································70

STEP02　切换全角、半角························································73

STEP03　显示拼音标注 ···························································76

03　截取字符串的一部分 ·······················································78

STEP01　从左边截取 ·······························································78

STEP02　从右边截取 ·······························································80

STEP03　从任一位置截取想要的部分······································82

04　查看文本的长度、位置 ····················································85

STEP01　查看指定字符的位置 ················································85

STEP02　只截取位于“@”前面的字符串 ······························87

STEP03　查看文本长度（一） ················································90

STEP04　查看文本长度（二） ················································91

05　有趣又实用的文本函数 ····················································94

STEP01　重复文本 ··································································94

STEP02　替换文本中的一部分 ················································97

STEP03　以自己想要的格式来显示字符串 ······························99

06　函数大提升 ····································································103

07　练习题 ···········································································105

问题01　显示中文的拼音 ·······················································105

问题02　在空格处换行 ··························································105

问题03　将“对不起”改为m(__)m、将“哇~”改为(^o^) ···106

 

函数篇CHAPTER 04　彻底学会日期和时间的操作 107

01　Excel 中日期和时间的处理 ············································108

STEP01　日期和时间的输入方式 ···········································108

STEP02　什么是序列值 ························································· 110

STEP03　日期的计算 ····························································· 112

STEP04　NOW 函数 ······························································ 112

02　处理日期和时间····························································· 114

STEP01　更改数字显示格式来显示日期和时间 ····················· 114

STEP02　通过TEXT 函数来更改数字格式 ···························· 115

STEP03　取出年、月、日的元素 ··········································· 116

STEP04　取出时、分、秒的元素 ··········································· 118

03　操作序列值 ····································································120

STEP01　计算序列值的差······················································120

STEP02　从年月日来求出序列值 ···········································122

STEP03　从出生年月日来算出星座 ·······································124

04　麻烦的星期显示·····························································127

STEP01　使用TEXT 函数来显示星期 ···································127

STEP02　出生在星期几 ·························································129

STEP03　使用CHOOSE 函数来显示星期 ·····························132

05　函数大提升 ····································································134

06　练习题 ···········································································135

问题01　古风风格的中文数字时钟 ········································135

问题02　按时间来变换显示信息 ············································135

问题03　万年历当月的行程预订表 ········································136

函数篇CHAPTER 05　向IF 函数挑战 137

01　根据年龄段来处理的IF 函数 ·········································138

STEP01　IF 函数 ···································································138

STEP02　如果为60 以上就显示“good” ·····························139

STEP03　清除不必要的显示 ··················································141

STEP04　将IF 函数设置成嵌套结构 ······································142

02　可统计个数的COUNTIF 函数 ·······································145

STEP01　查找符合条件的数据个数 ·······································145

STEP02　将字符串设置成条件 ··············································147

STEP03　使用通配符 ·····························································148

03　只求指定部分的和 ·························································150

STEP01　只对10 万以下的数据求和 ·····································150

 

STEP02　用SUMIF 函数对其他范围求和 ······························152

STEP03　可更改的查找条件 ··················································153

04　函数大提升 ····································································155

05　练习题 ···········································································156

问题01　根据男女分别计算出英语、数学、语文的合计值 ····156

问题02　心电感应函数 ··························································156

问题03　全自动！超完美！万年历 ··········································158

问题04　全自动！超完美！实用！可作为普通万年历来使用 ····158

函数篇CHAPTER 06　简单学数学 159

01　思考“四舍五入” ·························································160

STEP01　不合理的显示（一） ··············································160

STEP02　不合理的显示（二） ··············································161

STEP03　四舍五入 ································································161

02　随机数和模拟 ································································164

STEP01　随机数 ····································································164

STEP02　无条件舍去 ·····························································168

STEP03　掷骰子模拟 ·····························································169

03　π、弧度、三角函数 ·····················································171

STEP01　π ···········································································171

STEP02　弧度与度 ································································172

STEP03　三角函数 ································································174

04　函数大提升 ····································································176

05　练习题 ···········································································177

问题01　猜拳模拟 ··································································177

问题02　钱币的各种面额计算 ················································177

问题03　描绘山形 ··································································178

函数篇CHAPTER 07　函数活用特辑 179

01　引用其他工作表·····························································180

STEP01　工作表间的引用······················································180

STEP02　立体引用的技巧······················································181

02　公式也可变换成值 ·························································184

STEP01　公式与值 ································································184

STEP02　将公式转换成值······················································185

03　根据喜好为引用的单元格区域命名 ································187

STEP01　为引用的单元格区域命名（一） ····························187

 

STEP02　利用名称来进行引用 ··············································190

STEP03　为引用的单元格区域命名（二） ····························192

04　函数大提升 ····································································193

05　练习题 ···········································································194

问题01　只以函数来显示星期二上的第5 节课 ······················194

问题02　瞬间输入“删除完毕” ············································195

问题03　将企业中文名称全部改为英文名称 ··························196

宏 篇CHAPTER 08　宏入门 197

01　宏的录制、宏入门 ·························································198

STEP01　什么是宏 ································································198

STEP02　录制宏的操作步骤 ··················································199

STEP03　宏的执行 ································································205

STEP04　宏的保存位置 ·························································207

STEP05　使用宏的准备工作 ··················································208

STEP06　将1 000 个数据分开分布——相对引用的

宏录制 ····································································213

02　虽为附加功能却很厉害的VBE ······································217

STEP01　认识VBE ·······························································217

STEP02　制作宏 ····································································219

STEP03　执行宏、错误提示 ··················································220

STEP04　什么是程序 ·····························································222

STEP05　工作表函数与VBA 函数 ·········································222

03　制作超酷宏的InputBox ·················································224

STEP01　用InputBox 来输入文本 ·········································224

STEP02　姓名与“先生/ 小姐”连接 ····································225

STEP03　代入到变量 ·····························································226

STEP04　以InputBox 来显示标准体重 ··································227

STEP05　健康宏 ····································································228

04　按快捷键或单击图形打开宏 ···········································229

STEP01　指定到快捷键 ·························································229

STEP02　将宏指定到图形中 ··················································231

05　练习题 ···········································································232

问题01　立即复制上方的单元格 ············································232

问题02　输入姓名与金额就会显示计算结果 ··························233

问题03　按快捷键就会将表格中的当前记录整理换行后并

打印出来 ·································································234

目录.indd 5 2016-1-12 16:17:36

CONTENTS

6

宏 篇CHAPTER09　输入到单元格中 235

01　单纯输入单元格·····························································236

STEP01　在单元格中输入数据 ··············································236

STEP02　Range——**的数据输入法 ································237

STEP03　组合Range 与函数 ················································238

STEP04　Range 小技巧 ························································240

02　以x 和y 来表示单元格位置 ···········································242

STEP01　使用Cells 输入数据 ···············································242

STEP02　以For~Next 来循环处理 ········································243

STEP03　连续输入的技巧（一） ···········································244

STEP04　连续输入的技巧（二） ···········································245

03　宏的大提升 ····································································249

04　练习题 ···········································································250

问题01　以2、4、8、16…的方法来输入2 的累乘 ···············250

问题02　连接所有词语并显示在对话框中 ······························250

问题03　制作九九乘法表 ·······················································251

宏 篇CHAPTER 10　了解对象、属性和方法 253

01　什么是对象、属性 ·························································254

STEP01　什么是对象 ·····························································254

STEP02　什么是集合 ·····························································255

STEP03　什么是属性 ·····························································256

STEP04　显示属性 ································································258

02　字体大小和颜色·····························································259

STEP01　字体大小 ································································259

STEP02　连续改变属性——文字变大/ 变小··························260

STEP03　改变文字的颜色······················································262

STEP04　更改单元格的颜色 ··················································263

STEP05　制作多彩工作表（一） ···········································264

03　方法闪亮登场 ································································266

STEP01　什么是方法 ·····························································266

STEP02　执行删除操作 ·························································269

STEP03　什么是方法的参数 ··················································271

STEP04　让电脑开口说话······················································272

04　宏的大提升 ····································································276

05　练习题 ···········································································277

问题01　以用户所选的颜色来填充单元格 ······························277

目录.indd 6 2016-1-12 16:17:36

7

CONTENTS

问题02　以语音指引来输入数据 ············································278

问题03　以动画的方式随机将“好开心啊～”变大变小 ········279

宏 篇CHAPTER 11　如果是○○就进行×× 281

01　如果是○○就进行××——If 篇 ···································282

STEP01　什么是If ～ Then ～ Else ·······································282

STEP02　以单元格的值来改变处理 ·······································284

STEP03　只输入空白单元格 ··················································286

02　如果是○○就进行××——Select ～ Case 篇 ·············288

STEP01　什么是Select ～ Case ···········································288

STEP02　改变条件的设置方法 ··············································290

03　宏的大提升 ····································································293

04　练习题 ···········································································295

问题01　如果文字不是白色就变成白色，如果是白色就

　　　　 变成黑色 ··································································295

问题02　随意星座占卜 ··························································295

问题03　复制数据到空白单元格中 ········································296

宏 篇CHAPTER 12　可循环处理的宏 297

01　决定循环处理的次数 ·····················································298

STEP01　For ～ Next 的详细介绍 ··········································298

STEP02　制作多彩工作表（二） ···········································299

02　循环到符合条件为止 ·····················································301

STEP01　什么是Do ～ Loop Until ·········································301

STEP02　Do Until ～ Loop ···················································303

STEP03　Do While ～ Loop ···················································304

03　For Each ～ Next ··························································307

STEP01　什么是For Each ～ Next ········································307

STEP02　针对所有的工作表来执行 ·······································307

STEP03　制作多彩工作表（三） ···········································310

STEP04　将所有单元格的值变成原来的100 倍····················· 311

04　宏的大提升 ····································································313

05　练习题 ···········································································316

问题01　输入随机数 ······························································316

问题02　将“旋转~”文字在一定范围内旋转任意角度 ·········316

问题03　输入考生zui在意的偏差值 ········································317

问题04　输入所有的工作表名称 ············································318

目录.indd 7 2016-1-12 16:17:36

CONTENTS

8

宏 篇CHAPTER13　不可思议的宏 319

01　什么是事件 ····································································320

STEP01　事件概述 ································································320

STEP02　只要打开工作簿就会执行的宏 ································321

STEP03　事件宏的写法 ·························································323

STEP04　在打印时执行的宏 ··················································323

02　通过工作表操作来执行的宏 ···········································325

STEP01　只要将工作表设置为当前就会开始执行的宏 ··········325

STEP02　针对工作表的事件 ··················································326

STEP03　没有密码就无法进入的工作表 ································327

STEP04　只要更改数据就会执行的宏····································329

STEP05　只要双击就会输入数据并填充黄色 ·························331

03　宏的大提升 ····································································333

04　练习题 ···········································································334

问题01　只要更改数据就会将A1 单元格作为当前单元格 ·····334

问题02　双击就会以原来的2 倍大小显示“好痛哦!”文字 ···334

问题03　只要单击就能看见 ···················································335

appENDIX 附录 337

01　各类型函数速查表 ·························································338

02　练习题参考答案·····························································345

03　利用Excel 也能完成文件操作 ·······································357

STEP01　快速输入求和的技巧 ··············································357

STEP02　保存、关闭工作簿 ··················································357

STEP03　复制文件、移动、更改名称····································358

STEP04　删除文件 ································································359

STEP05　查找文件 ································································359

STEP06　操作文件夹 ·····························································359

STEP07　操作文本文件 ·························································360

04　将宏指定到按钮中 ·························································361

当今信息时代，会点电脑的人基本上都会使用Excel，但对于更深的Excel

高级技巧——函数与宏，掌握的人却并不占多数。在职场中，能够游刃有余地驾

驭Excel 函数与宏的人将更受同事的推崇和上司的青睐。

笔者研究Excel 函数与宏已有13 年之久，出版了多本著作，如《一看就

懂Excel 函数与宏》、《易学易懂Excel 函数与宏》以及后期升级的《易学易懂

Excel 函数与宏（修订版）》，承蒙大家的厚爱，已多次重印，并广受好评。而

本书则兼顾了从Excel 2003 到目前主流的Excel 2010、Excel 2013 各个版本，因

此对于读者来说，无论用的是哪个Excel 版本都可使用本书来学习。

本书的目标并不是让学习者成为所谓的“Excel 专家”，而是能够运用合适

的函数，并使用简短的宏来解决工作中的实际问题。许多Excel 图书，很容易将

难懂的内容介绍的让人一头雾水，而将这些难懂的内容介绍的通俗易懂则相当困

难。有感于此，笔者在撰写时，将难啃的函数和VBA 以“浅显易懂”为目标来

编写。

本书分为函数篇和宏篇两个篇章，书中给出了大量实用性很强的范例，营造

在“做中学，学中做”的学习氛围，用通俗易懂又不失趣味性的语言进行讲解。

书中Excel 2000 ～ 2003 意指2000、2002、2003 三个版本，Excel 2007 ～ 2013 意

指2007、2010、2013 三个版本。为了巩固所学知识，每章后附有针对性的练习题，

鼓励大家自己动脑动手来解决，并在书末给出了习题答案以供参考。为了便于上机

演练，书中所有的范例都有相应的电子文件，不必手动输入烦琐的原始数据，可以

直接拿来练习，本书的配套资源可在http://www.crphdm.com/2015/1015/7821.shtml

免费下载。

“虽然一知半解但还是硬着头皮使用Excel”、“虽然看了几本介绍Excel

操作的书，但还不太明白”……本书正是为有这样疑惑的读者而编写的，因此在

学习时放下顾虑和负担，跟着本书一步步学习，掌握函数和宏不是难事。只要领

悟了Excel 的诀窍，一定会发现其中大有乐趣。

西沢梦路