《Excel效率手册 早做完，不加班》系列丛书自从出版以来受到99%的读者好评，长期占据热销榜，畅销10万多册！本书由ExcelHome站长、培训讲师、技术论坛超级版主等业界人士极力推荐！相信，一定会带给你意想不到的提高与效率！快快入手，告别繁琐的Excel，高效办公，让你早做完不加班！

作者简介

陈锡卢，网名卢子，新浪微博：Excel之恋。中国会计视野论坛Office版主、IT部落窝论坛Excel超级版主，10年的Excel实战经验，精通Excel函数与公式、数据透视表，每天在微信跟微博分享Excel技能。

目录

目录

第1章 养成好习惯

1.1 了解Excel的四类表格 2

1.2 以身说法 8

1.3 小白重蹈覆辙 12

1.3.1 Excel小白如何做表累死队友 13

1.3.2 你Excel这么牛，BOSS知道吗 14

1.4 注重细节 17

1.5 小结与练习 19

第2章 向有经验的人学习

2.1 再当一回学生 22

2.1.1 初识Excel 22

2.1.2 这条线怎么画 22

2.1.3 边框怎么显示不全 25

2.1.4 输入的0跑哪去了 27

2.1.5 分数怎么变成了日期 28

2.1.6 生成编号序列 29

2.1.7 不相邻单元格输入同一数据 31

2.1.8 快速录入大量小数 33

2.2 偷 师 35

2.2.1 输入多个0有技巧 35

2.2.2 录入特殊字符 36

2.2.3 只允许输入某范围的日期 39

2.2.4 防止录入重复姓名 40

2.2.5 数据验证其实很好骗 42

2.2.6 眼见不一定为实 44

2.2.7 所见即所得 47

2.2.8 哪里不同刷哪里 48

2.2.9 将两列的内容合并成一列 49

2.2.10 将行变成列 50

2.2.11 给单元格加把锁 51

2.2.12 转换成PDF，放心将文档交给供应商 55

2.3 十年磨一剑 57

2.3.1 拯救未保存文档居然如此简单 57

2.3.2 轻松逆透视，数据巧转置 59

2.3.3 二维表格转一维表格原来如此简单 62

2.4 小结与练习 64

第3章

常用小技巧

3.1 排序 66

3.1.1 排序其实很简单 66

3.1.2 按城市的发展情况排序 67

3.2 筛 选 70

3.2.1 筛选中的搜索功能 70

3.2.2 将带颜色的项目挑选出来 71

3.2.3 借助高级筛选，让数据筛选更贴心 73

3.3 分 列 75

3.3.1 按分隔符分列 75

3.3.2 将姓名分成两列显示 77

3.3.3 将不标准日期转换成标准日期 78

3.4 零散小技巧 80

3.4.1 闪电式数据填充 80

3.4.2 删除错误值 81

3.4.3 让数据输入更简单 84

3.4.4 重复值问题 86

3.4.5 套用表格样式 88

3.5 小结与练习 92

……

4.1 一起来学习函数与公式 94

4.1.1  基本概述 94

4.1.2 初识函数 96

4.1.3 快速、准确输入函数名的两种方法 96

4.1.4 输入公式三招 98

4.1.5 三种引用方式 99

4.1.6 两种引用样式 100

4.1.7 借助名称让公式更容易理解 103

4.1.8 了解另外一片天地——数组公式 104

4.2 别怕，不会英语也能轻松

学会函数 107

4.2.1 如果这都不会的话，那又和按计算器

有什么区别 108

4.2.2 不用手机，也能对日期和时间

了如指掌 112

4.2.3 1+1=3，难道Excel也会算错？ 114

4.3 函数原来也能玩嵌套 116

4.3.1 重复生成序列 116

4.3.2 获取金额前三大（小） 117

4.3.3 重要的事儿说三遍 118

4.4 函数经典再现 121

4.4.1 闲聊SUM 121

4.4.2 求和之王SUMPRODUCT 131

4.4.3 既生SUMIF(COUNTIF)，

何生SUMPRODUCT 135

4.4.4 无处不在的IF 140

4.4.5 学VLOOKUP，认识Excel爱好者 143

4.4.6 LOOKUP潮汕 150

4.4.7 经典的INDEX+MATCH组合 158

4.4.8 OFFSET的运用 163

4.4.9 百变神君TEXT 166

4.4.10 字符提取之MID、LEFT和RIGHT

三兄弟 170

4.4.11 SUBSTITUTE的运用 174

4.5 为自己量身定做的函数 176

4.5.1 连接所有字符LJ 176

4.5.2 将借阅的书籍合并HB 179

4.5.3 提取批注内容PZ 182

4.5.4 自动录入当前时间NowTime 183

4.6 小结与练习 184

第5章 看透数据的数据透视表

5.1 多变的要求 188

5.2 跟领导聊聊数据透视表 190

5.2.1 什么是数据透视表 190

5.2.2 多角度分析数据 191

5.2.3 更改值的汇总依据 195

5.2.4 排序让数据更加直观 196

5.2.5 原来手工也能排序 197

5.2.6 对销售额进行排名 198

5.2.7 筛选销售额前5名的客户 199

5.2.8 让更改的数据源随时刷新 199

5.2.9 插入表格，让数据源“动”起来 204

5.2.10 日期组合真奇妙 206

5.2.11 手工组合，实现客户分级 210

5.2.12 善借辅助列，实现客户实际

销售额分析 211

5.2.13 利用数据透视图让分析更直观 212

5.3 惬意的生活 216

5.4 小结与练习 217

第6章 神奇的SQL语句

6.1 系统是浮云 220

6.2 SQL是神马 220

6.3 试探性查询 222

6.3.1 认识SQL的储存地 222

6.3.2 查询所有记录 226

6.3.3 查询不重复客户 227

6.3.4 查询符合条件的客户 228

6.4 有目的统计 229

6.4.1 统计猪肉金额 229

6.4.2 统计批发和零售金额 231

6.4.3 统计产品用量 232

6.5 强强联合，见证神奇 233

6.5.1 进销存分析 233

6.5.2 资金流动分析 236

6.6 小结与练习 238

第7章 学E千日，用在一时

7.1 年度数据分析 242

7.1.1 数据核对 243

7.1.2 数据汇总 245

7.1.3 数据分析 249

7.2 小结与练习 254

第8章 在娱乐中学习

8.1 模仿手机功能 256

8.1.1 手机号码的显示格式 256

8.1.2 姓名简称 257

8.1.3 防骚扰 258

8.1.4 联系人去重复 260

8.1.5 智能拨号 261

8.1.6 QQ通讯录开启人员 262

8.2 其实我是算命的 263

8.2.1 戏说生辰八字 264

8.2.2 星座学 265

8.2.3 生日密码 266

8.2.4 面相 268

8.2.5 点菜无烦恼 270

8.2.6 别用你的无知挑战概率 271

8.3 不会数学计算，Excel来帮你 272

8.3.1 数学基本计算 272

8.3.2 解方程 273

8.3.3 预测数字 276

8.3.4 随机抽样 278

8.3.5 利润最大化 279

8.4 小结与练习 281

第9章 用合适的方法做合适的事

9.1 简单就好 284

9.1.1 粘贴成图片，排版无烦恼 284

9.1.2 计算文本表达式 288

9.1.3 史上最简单的工资条制作方法 288

9.1.4 多表数据核对只需几秒 293

9.1.5 多行多列提取不重复 294

9.1.6 逗号分隔符 297

9.1.7 Excel和Word双剑合璧提取数字 300

9.1.8 多表关联 302

9.2 聊聊与Excel有关的事儿 308

9.2.1 不知者无罪 308

9.2.2 假如生活就是Excel 314

9.3 小结与练习 315

后记 317

序言

前言

十年磨一剑，写在书籍再版之时。

毕业并不代表学习的结束，而是另一种学习的开始。2007年毕业的时候，连电脑打字都不会，因香姐的一句话开始接触Excel，因要将一段感情遗忘就将业余的90%的时间投入在学习Excel上，接近疯狂。在2009年年底，因学习Excel曾有过一周瘦7斤的经历，从此以后肥胖与我无缘，至今体重从没变化过。

当Excel能力提升到一定程度的时候，就开始有种想跟大家分享自己所懂知识的想法。有人也许会说，你傻啊，教会了别人，你以后价值就变得很小。教会徒弟饿死师傅的时代已经一去不复返。互联网时代，必须时时刻刻地学习，否则不用几个月你就被淘汰了，所以你必须逼着自己学习更多的知识。现在我还有一周看1～2本书的习惯。

在这几年时间，也出版了“Excel效率手册 早做完，不加班”系列丛书（5本）。因为不断地免费分享，我朋友看不过去了，你天天分享这些，别人就不用买你的书，看这些就够了。其实我想说，Excel是我的兴趣，当分享成为习惯就很难改变，只要分享的知识对大家有用就可以。但也奇怪，我分享得越多，书籍反而卖得更好，2年的时间居然畅销20万本。借用一个粉丝的话来解释这个现象，我是被你无私分享的精神感动，每天在群内帮人解答疑难，所以我买了全套书籍。

网络上流传着这么一句话：Excel虐我千百遍，我待你如初恋，你如同我心中另一个世界，用我的真心跟你谈一场天长地久的恋爱。虽然Excel不是我的恋人，但6年来与Excel朝夕相处，不是恋人胜似恋人。

这是一场注定相恋终身的恋情。Excel给了我现在的工作，让我结识了很多人，让我感受到生活的美好和人与人之间的温情。我有一种萌动，需要将这几年我所感受到的、获得的快乐分享给更多人。唯有如此，才能表达我心中的喜悦，才能表达我对Excel以及所有曾帮助过我的人的热爱和感激。

本书以卢子为主角，通过他与各种各样职场人士的对话，来了解Excel的常用功能和提高工作效率的技巧。对效率的追求和表述，将贯穿全书。有时卢子会直接说出来，有时卢子只是通过前后对比来展现。

在这里，我最想说的是学习方法。这是感受温情和爱的学习方法。刚开始学习的时候，要善于向身边的同事及朋友请教问题。同时也要学会自觉学习，毕竟没有人可以帮助你一辈子。学到新知识要及时与同事、朋友、网友分享，这样你将会获得别人的好感，以后别人有好的方法也会告诉你。拥有6个苹果，如果拿5个分给别人，将会得到6种不同口味的水果；如果不分享，你只能有1种水果。当学习到一定程度的时候，要善于抓住在领导面前展示自己的机会，你不表现自己，学得再多又有何用，并不是每个领导都是伯乐。不善于表现自己，能力越强，你会越痛苦。除了工作上使用Excel，你还可以将Excel当成游戏。自从学了她以后，我就很少玩游戏，她成了我的游戏。当然也可以在生活上使用Excel，从2009年开始我就拿她来记账。有一个表格用了3年还在继续使用，用久了也是有感情的。学Excel不是以炫耀技能为目的，要以合适的方法做合适的事。无招胜有招，这才是Excel的最高境界。

再说一段励志的话：Excel与我们的生活息息相关，只要有数据的地方就有Excel，学好并应用她能快速地提高工作效率。坚持吧，朋友们！学习与不学习的人，在每天看来没有任何区别，在每个月看来差异也是微乎其微。在每年看来的差距虽然明显，但好像也没什么了不起。在每5年来看的时候，那就是财富的巨大分野。等到了10年再看的时候，也许就是一种人生对另一种人生不可企及的鸿沟。闲暇时把充电这事儿提上议程吧！

记住这个经典的公式：不学习是0.9，而学习是1.1，10年的差距就是1.13650与0.93650的差距，这两者的差异何止十万八千里！

最后，我想直接说出我的感谢。感谢Excel，我的“恋人”；感谢“猪之星愿E之恋”群，虽然群里面只有3个人，却打开了我人生新的一面。感谢周庆麟，是他的帮助让我获得了学习Excel的快乐。感谢IT部落窝所有的Excel网友，是你们让我感受到更广泛地分享的快乐。还要感谢您——拿起这本书的读者，您的阅读，将我对Excel的爱传向广阔的世界。

另外，还得感谢参与本书编写的以下人员：郑晓芬、李应钦、邱显标、龚思兰、邓丹、陆超男、刘宋连、邓海南。

编 者

文摘

第3章 常用小技巧

小小技巧能让你的工作效率提高很多倍。我们不必掌握每个技巧，只要了解一些常用的就可以了。排序、筛选、分列、查找、替换等就是*常用的，下面通过一些小实例一起来认识它们。学技巧没有捷径，唯有勤练，趁着有空多看看书。

排 序

3.1.1  排序其实很简单

如图3-1所示，刚收到一份快递费用汇总表，但中转费没有按从高到低排列，看起来很乱。怎么按中转费降序排序呢？

图3-1 乱序的快递费用表

如图3-2所示，选择区域D1:D10，然后切换到“数据”选项卡，在“排序和筛选”组里单击按钮，在弹出的“排序提醒”对话框中保持默认设置不变，单击“确定”按钮。

图3-2 按中转费降序排序

在“排序提醒”对话框中有个“以当前选定区域排序”单选按钮，如果选中这个单选按钮，排序结果会怎么样？

“以当前选定区域排序”只能针对选择的区域排序，而没有选择的区域不排序。正常情况下，这种排序是没有意义的，同时也很危险。从图3-3可以很清楚地看出两种方式的差别。

图3-3 两种排序结果对比图

3.1.2  按城市的发展情况排序

如图3-4所示，不管按升序排序还是按降序排序都不能得到我想要的排序结果。有没有办法实现我需要的排序呢？如现在这几个城市的发展情况从好到差依次是：揭阳、汕头、潮州、梅州，现按发展情况排序。

图3-4 按城市发展升序

汉字的排序方法通常有两种，一种是按首字母排序，还有一种是按笔画排序， 如图3-5所示。 但这两种方法都得不到所需要的结果，既然没有所需要的排序结果，那就自定义一个排序方法吧。

图3-5 “排序选项”对话框

单击“文件”按钮，然后选择“选项”命令，再在弹出的“Excel选项”对话框的左侧选择“高级”选项，单击“编辑自定义列表”按钮，如图3-6所示。

图3-6 “Excel选项”对话框

弹出“自定义序列”对话框，如图3-7所示，在“输入序列”列表框中依次输入城市(注：如果事先已经在单元格中输入排序依据，也可以用导入序列功能) ，再单击“添加”按钮。“自定义序列”列表框中就出现了新增加的序列，单击“确定”按钮，返回工作表。

图3-7 “自定义序列”对话框

如图3-8所示，选择需要排序的区域，然后单击“排序”按钮。在弹出的“排序”对话框中，添加排序条件，在“次序”下拉列表框中选择“自定义序列”选项，找到自定义的新序列，单击“确定”按钮。

图3-8 自定义排序

如果需要多条件排序，只需在“排序”对话框中单击“添加条件”按钮，再选择需要排序的主要关键字，其他操作跟上面一样。

如图3-9所示，得到想要的排序结果。

图3-9 自定义排序后的效果

自定义排序虽然挺好，但总让人感觉烦琐，如果你懂得函数，这个问题会变得更加简单。借助MATCH函数，将其作为辅助列，然后根据辅助列进行升序即可轻松完成，如图3-10所示。

图3-10 将MATCH函数作为辅助列排序

MATCH函数是用于获取排位的，比如，汕头在{"揭阳"，"汕头"，"潮州"，"梅州"}中排第2，就返回2，同理，潮州就返回3。有了排位，就可以按数字进行排序。

筛 选

3.2.1  筛选中的搜索功能

如图3-11所示，在搜索引擎中输入“潮州”，就会出现很多跟潮州有关的信息。在Excel中能否做到这点呢？

图3-11 搜索引擎

海量数据筛选，没有*快只有更快，利用筛选中的搜索功能可以让数据筛选更方便快捷。

如图3-12所示，选择A1区域，然后单击“数据”选项卡下的“筛选”按钮。

图3-12 单击“筛选”按钮

如图3-13所示，单击“寄件站”的筛选按钮，在搜索框中输入“潮州”，就会出现跟潮州有关的信息，再单击“确定”

按钮。

图3-13 搜索功能

旧版本操作：单击“寄件站”的筛选按钮，选择“文本筛选”→“包含”命令，输入“潮州”，就会出现跟潮州有关的信息，再单击“确定”按钮。

使用者有需求，Excel开发者就会改进。即使目前没有这个功能，只要需求的人多了，耐心等待，在不久的将来一定会有的。这个搜索功能就是一个明显的例子，2010版以后才有该功能。后文还会提到很多新功能，到时就不逐一说明了。

3.2.2  将带颜色的项目挑选出来

如图3-14所示，在核对数据的时候，将有异常的数据用黄色填充色标示出来。现在要将这样的异常值筛选出来以便逐一修改，应该怎么做呢？

图3-14 数据表

单击“应收中转费”的筛选按钮，选择“按颜色筛选”→“黄色填充色”命令，如图3-15所示。

图3-15 按黄色筛选

旧版本操作需要借助宏表函数才可以。

如图3-16所示，单击F2单元格，切换到“公式”选项卡，单击“定义名称”按钮，弹出“新建名称”对话框，设置名称为“颜色”，在“引用位置”文本框中输入下面的公式，然后单击“确定”按钮。

=GET.CELL(63,将带颜色项目挑选