函数篇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
目录.indd 8 2016-1-12 16:17:36
鄂公网安备 42010302001612号 