Excel 职场手册：260招菜鸟变达人【QQ群号更新为 806295411】

版权信息

书名：Excel 职场手册：260招菜鸟变达人

ISBN：978-7-115-45157-6

本书由人民邮电出版社发行数字版。版权所有，侵权必究。

您购买的人民邮电出版社电子书仅供您个人使用，未经授权，不得以任何方式复制和传播本书内容。

我们愿意相信读者具有这样的良知和觉悟，与我们共同保护知识产权。

如果购买者有侵权行为，我们可能对该用户实施包括但不限于关闭该帐号等维权措施，并可能追究法律责任。

•　编　　著　聂春霞　佛山小老鼠

　 责任编辑　王峰松

•　人民邮电出版社出版发行　　北京市丰台区成寿寺路11号

　 邮编　100164 　电子邮件　315@ptpress.com.cn

　 网址　http://www.ptpress.com.cn

•　读者服务热线：(010)81055410

　 反盗版热线：(010)81055315

内容提要

本书循序渐进、由浅入深地介绍了Excel丰富而又强大的功能。全书共18章，依次讲解了数据操作与处理、数据分析、公式与函数应用、图表制作，以及宏与VBA、Excel与Access双剑合璧等多种综合和高级使用技巧。每章都有大量的案例，在讲解操作方法和技巧时，采用了详细的步骤图解，部分内容还配有操作过程演示动画、示例文件和视频，读者可到“异步社区”网站下载。附录中提供Excel限制和规范及Excel常用快捷键，方便读者随时查阅。

本书适合各层次的Excel用户，对于Excel新手来说是一把进入Excel大门的金钥匙；对于有一定经验的人士来说，又是进一步提升的阶梯。书中大量的实例适合读者在日常工作中借鉴。

对本书的赞誉

Excel作为常用的处理工具，对工作有很多帮助。本书介绍了Excel常用的功能，凝聚了作者多年的经验，文字、截图、动画相结合，简单易学。相信你读完本书，将很快成为Excel高手，工作效率将大幅提升。

——卓越强　腾讯公司WXG行业合作部总经理

有人说，从工作中学胜于从书本上看。本书的作者正是实践出真知的典型代表。作为普通员工，她在日常工作中每天使用Excel。熟能生巧，在她将Excel运用得非常娴熟的同时，也发现了很多的细节问题并总结了有针对性的案例。通过不断查阅资料和自己钻研，她找到了很多解决方法，并共享到公司论坛上，得到了同事们的高度关注。2011年她通过认证，成为腾讯公司Excel讲师。经过5年多的授课，她积累了大量来自各部门同事咨询的问题、案例，成为优秀Excel讲师。因为案例均来源于日常工作，所以实操性很强，而她讲授的这门看似枯燥的技能课程则场场爆满。这些总结出来的技巧、经验和案例，经过作者的甄选收录在本书中。通过循序渐进的方式让读者能更好地使用Excel,尤其是部分复杂的操作，采用“文字+图解+操作动画”的方式，相信能让大家用平凡的Excel做出不平凡的效果。

——李致峰　腾讯公司MIG运营商业务部助理总经理

本书是我读过的最接地气的工具书，没有冗余的理论章节，只有一个个由浅入深的实际案例。读完本书，发现Excel原来如此神奇！

——包玉杰　腾讯公司MIG运营商业务部业务运营中心副总监

本书介绍了Excel常用的功能以及Excel和Access在数据处理方面的相互配合，作者在长期使用Excel的过程中积累了很多经验，在本书中用生动准确的方式呈现出来。寓教于乐，简单易学，相信你读完本书，你将很快成为Excel高手，工作效率将大幅提升。

——胡振东　腾讯公司MIG无线安全产品部总经理

学好Excel要先学会“偷懒”，当你只有20个数据时，会不会Excel并不重要；而当你要处理2000个甚至上百万个数据时，Excel就成了最好的利器。本书作者用自己多年来在顶尖科技公司的实战经验，教你如何做个幸福的“懒人”。

——陈广域　腾讯公司MIG无线合作开发部总经理

作为作者多年的好友，看到她自主开发的Excel课程集结成书，由衷地感到开心，更佩服她在兼顾忙碌的工作与家庭生活之余以极大的热情和毅力投入到教案研究、内部培训工作上的敬业精神。书中很多数据图表的技巧分享带给人眼前一亮的感觉，而文字背后所体现的作者执着、创新、专业、务实的人生追求更加值得我们学习。预祝今后有更多新课程、新感悟与我们分享！

——周文娟　中国移动通信集团公司

第1版推荐序　别小看这“一招鲜”

Excel是职场中几乎人人都要用的工具，我很羡慕那些Excel用得特别好的同事。在腾讯学院为员工开设的培训课程中，Excel系列的课程总是大受欢迎。看来，职场驰骋，这些工具的确很重要。而春霞老师作为个中高手，2011年成为我们腾讯学院此类课程的公司级认证讲师。特别欣喜的是，她不仅仅是课程讲得棒，还不懈钻研，对很多问题不断寻找更多、更佳的解决方案，同时还特别善于总结。而所有这些，终于结晶成了今天呈现在大家面前的这本书。

我说的“别小看这本书，更别小看Excel这一招鲜”，不是随便说说。首先，较强的Excel技能，的确能让你的工作效率和工作质量提升不少，无论你从事哪个岗位；其次，我也看过一些讲Excel技巧的图书，与其他书相比，我喜欢这本“小”书的原因有以下三点。

❶ 源于实践：Excel的功能很多，不可能每人都完全掌握，而春霞老师自己作为职场人士，会更了解大家工作中最需要的场景和技能，介绍的技能更有针对性。所有培训，最有价值的莫过于此。

❷ 形式创新：我自己就经常有学习这类课程半途而废的经历，就是因为教学方法太枯燥和不易学。这本书的一个亮点，就是配套提供直观、生动的操作动画。这种创新教学形式的好处，相信读者看过就知道了。

❸ 持续学习：希望大家看过本书后，还可以利用微信、微博继续和春霞老师切磋交流。这种持续的学习过程，我本人特别看好，甚至还可以延伸到职场技能的很多方面。

最后，还特别想说的是，分享是一种美德。随着腾讯学院的发展，越来越多的腾讯同事加入了公司讲师队伍，这本身就是一件大好事。而有些老师不满足于单纯的授课，还把自己在工作中的心得与体会利用业余时间整理成书，从而惠及更多朋友。这非常值得尊敬，也算是互联网开放与分享精神的一个体现吧。

谢谢春霞老师。

腾讯学院院长　马永武

2014年11月

前言

2015年4月，我人生中的第一本书《Excel高手捷径：一招鲜，吃遍天》出版了。出版后的第一个月在当当网计算机新书热榜排名第一，这让我感到很意外。出书后向我请教问题的人更多了，不仅仅是腾讯内部的同事，还有公司外面的粉丝。在帮助大家解决问题的过程中，我钻研了更多的技巧，更加深刻地体会到Excel的博大精深。我特别想把这些技巧分享给大家，因此，两年后我准备再版。在此特别感谢腾讯公司以及老东家江西移动的领导和同事们对我的大力支持，感谢我的亲朋好友以及粉丝们的支持，感谢人民邮电出版社给予的指导和付出。

第2版与第1版不同之处有以下5点：

1. 应读者和出版社的要求，第2版书名变更，封面方案重新设计，尽可能以新的面貌出现在大家面前。

2. 增加了更多实用的技巧，全书共260招技巧，部分技巧是Excel 2016版本新增功能。

3. 第1版同样的问题增加了更多解决方法，读者可以选择自己喜欢的方法。

4. 细分目录，第1版分为7章，第2版分为5大篇18章，根据目录查找相对容易些。第一篇：数据操作与处理（共5章：单元格数据录入与编辑、整理单元格数据、查找与替换、行与列的操作、工作表以及文件的操作）；第二篇：数据分析（共2章：排序、筛选、分类汇总、合并计算以及数据透视表）；第三篇：公式与函数（共6章：公式与函数基础、常用的数学和统计函数、文本函数、查找与引用函数、日期与时间函数、其他函数的应用）;第四篇：图表制作（共2章：常用图表制作以及图表优化、高级图表的制作）；第五篇：高级应用与综合应用（共3章：宏与VBA、Excel与Access双剑合璧、多种技巧综合运用）。

5. 对部分技巧录制视频，读者可以结合书中的文字+截图以及操作动画、视频，边看边练，一步一步做出效果。光说不练假把式，一定要亲自实践才能体会Excel的美妙。欢迎读者到异步社区下载本书配套的示例文件、动画和视频。

由于本职工作很忙，时间仓促，水平有限，错误之处在所难免，欢迎读者批评指正。作者的个人微信公众号“Excel原来如此简单”，欢迎添加关注。Excel交流学习QQ群465693036，欢迎入群交流。

致谢

感谢腾讯公司COO任宇昕先生和腾讯学院院长马永武老师的推荐，感谢MIG副总裁王波先生、MIG运营商业务部总经理毛涛、助理总经理李致峰、业务运营中心崔岩、包玉杰、冯智等领导的大力支持，感谢部门片区片总和项目经理对我的支持。感谢腾讯学院刘建军老师一直以来的鼓励和信任，感谢腾讯学院Excel讲师团队梁悦、李欣蓝、林小玲等小伙伴们的分享和交流，感谢学员和粉丝对我的信任。感谢老领导谢平章、胡振东、陈广域、赵强、沈敏敏、辛建华、章显、唐卫民、王朝炜对我曾经的支持和帮助！感谢同事曹钟元对我出书提供的帮助。感谢ExcelHome和Excel精英论坛、Excel完美论坛提供的学习资源。

聂春霞

2016年11月

彩图插页

图1-2-29

图4-14-4

图4-14-5

图4-14-6

图4-14-9

图4-14-10

图4-14-13

图4-14-15

图4-14-16

图4-14-17

图4-14-18

图4-14-19

图4-14-22

图4-14-23

图4-14-27

图4-14-31

图4-14-36

图4-14-50

图4-14-52

图4-14-54

图4-14-55

图4-14-56

图4-14-58

图4-14-61

图4-14-62

图4-14-63

图4-14-65

图4-14-68

图4-14-69

图4-14-70

图4-14-72

图4-14-74

图4-14-76

图4-14-79

图4-14-80

图4-14-81

图4-14-83

图4-14-84

图4-14-87

图4-14-88

图4-14-89

图4-14-93

图4-14-94

图4-14-95

图4-14-96

图4-14-97

图4-14-100

图4-14-111

图4-14-112

图4-14-113

图4-14-115

图4-14-121

图4-14-129

图4-14-131

图4-14-132

图4-14-134

图4-14-136

图4-14-137

图4-14-138

图4-14-142

图4-14-144

图4-15-1

图4-15-2

图4-15-4

图4-15-6

图4-15-7

图4-15-9

图4-15-13

图4-15-14

图4-15-15

图4-15-16

图4-15-20

图4-15-22

图4-15-23

图4-15-24

图4-15-33

图4-15-34

图4-15-35

图4-15-36

图4-15-38

图4-15-39

图4-15-42

图4-15-45

图4-15-47

图4-15-48

图4-15-50

图4-15-54

图4-15-55

图4-15-56

图4-15-57

图4-15-58

图4-15-59

图4-15-62

图4-15-64

图4-15-66

图4-15-68

图4-15-71

图4-15-73

图4-15-76

图4-15-77

图4-15-79

图4-15-80

图4-15-92

图4-15-94

图4-15-95

图4-15-97

图4-15-99

图4-15-101

图4-15-103

图4-15-105

图4-15-108

图4-15-110

图4-15-117

图4-15-120

图4-15-122

图4-15-126

图4-15-127

图4-15-132

图4-15-136

图4-15-138

图4-15-141

图4-15-144

图4-15-146

图4-15-147

图4-15-148

图4-15-149

图4-15-151

图5-16-15

图5-16-16

图5-16-17

图5-16-18

图5-16-19

图5-18-121

图5-18-122

图5-18-125

图5-18-129

第一篇　数据操作与处理

本篇讲解单元格数据录入与编辑以及整理、查找与替换、行与列、工作表和文件等方面的操作技巧，主要内容包含快捷键的功能介绍、特殊数据的录入技巧、查找替换、条件格式、数据有效性定义、自定义单元格、合并单元格取消后快速输入空白区域、文本与数字格式的相互转换、工作表以及工作表标签的显示与隐藏、工作表和工作簿的保护、Excel文件减肥瘦身秘诀、Excel文件打印技巧等。

第1章　单元格数据录入与编辑

本章介绍单元格数据录入与编辑技巧，主要内容包含快捷键（组合键）、特殊数据的录入技巧、数据验证、快速填充、合并单元格取消后快速输入空白区域等。

第 1 招　快捷键的妙用（基于Windows操作系统）

Excel中常用的快捷键以及功能汇总参见附录2，下面我们重点介绍几个快捷键在日常工作中的应用。需要说明的是，下面介绍的这些快捷键基于Windows操作系统，如果是Mac，快捷键不一样。

1．单元格内强制换行——Alt + Enter

比如，日常工作中需要在一个单元格写工作总结，分几行展示，在需要分行的地方按组合键【Alt + Enter】，就可以实现单元格内换行，如图1-1-1所示。

也许你通过鼠标右键设置单元格格式，把“自动换行”打钩也可以实现单元格换行，如图1-1-2所示，但是如果需要在某个固定的内容后面换行，还是无法实现。

图1-1-1

图1-1-2

举个例子，单元格内有一首古诗，，需要在每句诗的最后一个字换行，用自动换行，调整单元格的宽度和行高，能得到想要的结果，如图1-1-3所示。

但是如果单元格列宽或行高变了就不行，如图1-1-4所示。

如果在每句诗的末尾按组合键【Alt + Enter】，不管行高和列宽如何变化，单元格内始终会换行展示，如图1-1-5所示。

图1-1-3

图1-1-4

图1-1-5

2．批量输入相同的内容——Ctrl+ Enter

用鼠标左键选中某个区域，随便输入一个数字20，按组合键【Ctrl+Enter】，这些区域全部填充相同的内容20，如图1-1-6所示。

图1-1-6

内容可以是常量，也可以是单元格引用。

3．快速选中选择框内容——Ctrl + Shift +↓（↑、←、→）

你是否碰到这样的情况，要选中Excel表格某一列或多列数据，行数多达几万甚至几十万行，用鼠标拖动好几分钟，右边的滚动条还没到底部，是不是觉得要崩溃了？用快捷键再多的行列都不怕。组合键【Ctrl + Shift +向下键↓】可以快速拉动选择框到最后一行数据，一秒钟搞定。如果需要从数据区域的末端快速选中上面区域，用组合键【Ctrl + Shift +向上键↑】。如果从数据区域的最左边开始，组合键【Ctrl + Shift +向右键→】可以快速选中到最右边一列数据，相反，从最右边一列数据到最左边，就用组合键【Ctrl + Shift +向左键←】，见表1-1-1。

表1-1-1

4．重复上次操作和切换单元格引用类型——F4

F4键功能有2个，一是重复上一次的操作，一是切换单元格引用类型（绝对引用、相对引用、混合引用）。我们先看看第一个功能，如把某个单元格字体颜色标红，再选择其他单元格内容，按F4键，发现选中的这个单元格字体颜色也变了。

在使用公式与函数的时候需要引用单元格内容，有时候需要相对引用，有时候需要绝对引用，有时候相对和绝对引用同时用到。相对引用就是公式随着单元格的变化而变化，绝对引用就是单元格固定不变。就像初中物理课讲到的参照物，把车窗外的电线杆当参照物，电线杆是固定不动的，这个就相当于绝对引用，车行走过程中位置不断变化，这个就相当于相对引用。绝对引用前面有个$，相对引用则没有。如果您手工录入$，量少的时候估计还能接受，如果量很大，成千上万行的数据需要绝对引用，您还是手工录入，那就要崩溃了。告诉您一个很简单的办法，用F4键可以灵活自如地切换引用类型。如鼠标选中G3单元格，按F4键就是绝对引用，再按一次F4键就变成混合引用（行不变，列在变），再按一次F4键还是混合引用（行在变，列不变），再按一次F4键又变为相对引用，如图1-1-7所示。

图1-1-7

5．如何快速全部选中非连续区域的空白单元格——定位F5

如图1-1-8所示的表格有空白单元格，需要在空白单元格全部填充0，如果一个个空白单元格手工输入0，效率非常低。

图1-1-8

选中数据区域，按F5键，弹出如图1-1-9所示对话框。

单击“定位条件”，选择“空值”，如图1-1-10所示，单击“确定”按钮。

图1-1-9

图1-1-10

这样就可以把空白区域全部选中了，再在其中一个空白单元格输入0，按组合键【Ctrl + Enter】，就可以批量填充空白单元格。

6．快速输入当前日期——Ctrl +；（分号）

在单元格输入【Ctrl + ；】显示系统今天的日期，对比下，比手工录入今天的日期是不是快多了？

7．快速输入当前时间——Ctrl + Shift +；（分号）

在单元格输入【Ctrl + Shift +；】显示系统当前时间。

8．切换公式和结果——Ctrl + ～

如果要查看单元格内的公式，按组合键【Ctrl + ～】，如果想看结果，再次按组合键【Ctrl + ～】。

9．Alt键的妙用

在Excel中按住Alt键不放，再按小键盘上的数字键，能够快速输入一些特殊字符和符号。

例如，如果在单元格输入打钩符号√，按住Alt键不放，再按小键盘上的数字41420，就可以输入√。如果输入打叉符号╳，按住Alt键不放，再按小键盘上的数字43127，就可以输入符号╳。

打钩符号也可以在单元格输入a或b，鼠标右键设置单元格格式，选择Marlett字体，如图1-1-11所示。

图1-1-11

Alt+数字键输入其他特殊字符和符号见附录2。

10．快速找到表格最后一个单元格——Ctrl + End

无论你在哪个单元格编辑，按组合键【Ctrl+End】，都会回到最后一个单元格，可以用来查看表格中有没有多余的空白行或空白列。

第 2 招　常用快捷键Windows与Mac对照

以上快捷键都是基于Windows操作系统，Mac操作系统里的快捷键对应如图1-1-12所示。

图1-1-12

第 3 招　怎样快速输入几十万相同数据

以20万行为例，要在空白工作表中批量输入20万行相同的数据，先在名称框输入单元格地址A1:A200000，如图1-1-13所示，按Enter键，在任意单元格输入数据，再按组合键【Ctrl + Enter】，就可以批量填充A1到A200000为相同的数据。

如果工作表中A列有数据，需要在B列输入同样行数的相同数据，只需在B1单元格输入数据，点击单元格右下角的黑色+双击就可以。

图1-1-13

第 4 招　身份证号码、银行卡号等超过15位数据的录入技巧

身份证号码和银行卡号一般在15位以上，如果直接在单元格内输入数字后返回结果是科学记数的数值，如，如果在数值前面加个英文状态下的单引号’，就可以显示所有数字。也可以先设置单元格格式为文本，再录入数字。Excel单元格对数字的录入是有规范和限制的，超过11位自动显示科学记数，超过15位就无效，例如，在单元格输入11个1，显示为，输入16个1，第16个1就显示0，。

第 5 招　录入的分数怎么变成日期了

分数输入之前先输入0，再按空格键，再输入分数，如，

如果直接输入分数，返回日期格式，。

第 6 招　带有上标的指数录入

比如，要在单元格内输入M的平方，先输入M2，选中2，单击鼠标右键，设置单元格格式，在“特殊效果”中选中“上标”复选框，如图1-1-14所示。

图1-1-14

这样能在单元格输入M的平方，但是一旦用公式连接，上标就会失效，如图1-1-15所示。

如果想输入真正的平方，可以用组合键【Alt+178】，先输入M，然后按住Alt键不放，再输入数字178即可，如图1-1-16所示。如果输入立方，用组合键【Alt+179】。

图1-1-15

图1-1-16

第 7 招　等差与等比数列的录入技巧

如果输入等差数列，只需要先在2个单元格输入内容，比如A1、A2分别输入1、3，选中A1、A2，单击单元格右下角+，鼠标左键向下拖动就可以输入等差数列。“聪明”的Excel能够自动判断两个数之间的步长值（也就是差值），如图1-1-17所示。

如果输入等比数列，在A1单元格输入起点数据2，单击单元格右下角黑色+，右键拖动，松开，选择“序列”，序列类型选择“等比序列”，步长值改为2，单击“确定”按钮，得到等比数列，如图1-1-18～图1-1-20所示。

图1-1-17

图1-1-18

图1-1-19

如果要输入几十万行的等差或等比数列，用这种方法就太慢了，试想一下，如果输入30万行，鼠标拖动30万行得花多少时间啊。只需要在单元格输入等差或等比数列的第一个数字，在“终止值”输入最后一个数字，“步长值”输入步长，就可以快速输入几十万行的数据，如图1-1-21所示。

图1-1-20

图1-1-21

第 8 招　怎样在多张工作表录入相同的数据——创建工作组

一个工作簿有多张工作表，需要在多张工作表相同区域输入相同的数据，或者删除数据，如果一张张工作表操作，很费时间，我们可以把这些工作表创建一个工作组，在其中一个工作表做某个操作，在其他工作表也做同样的操作。怎样创建工作组呢？如果多个工作表是连续的，选中第一张工作表，按住Shift键，再用鼠标选中其他工作表，这些工作表就成了一个工作组。如果工作表不是连续的，选中其中一张工作表，按住Ctrl键，再用鼠标选中其他工作表。比如，工作簿有Sheet1、Sheet2、Sheet3，想在3张工作表同样的单元格区域输入相同的数据，用鼠标左键选中Sheet1，按住Shift键，再选中Sheet2和Sheet3，在工作簿名称旁边看到字样。如果要撤销工作组，操作方法同创建工作组一样。大家记住，Excel有个特点，怎么来就怎么回去。

第 9 招　合并单元格取消后如何批量填充空白区域

在排序、组合、分类汇总等操作之前需要取消合并单元格，合并单元格取消后，空白的部分如何一次性填充呢？如果合并单元格有成千上万个，一个个手工填充，那就悲催了。在前面的快捷键我们讲到了【Ctrl+Enter】可以批量输入相同的内容，这个内容可以是常量，也可以是单元格引用，这里介绍一个很简单的方法来实现批量填充，如图1-1-22所示。

先选中A列，单击合并后居中按钮，可以批量取消合并单元格，得到图1-1-23。

图1-1-22

图1-1-23

再按F5键，定位条件选择“空值”，如图1-1-24所示，这样就把空白的部分全部选中了。

在A3单元格输入公式=A2，这个时候不要按回车键Enter，按组合键【Ctrl+Enter】，就可以批量填充空白部分，如图1-1-25所示。

图1-1-24

图1-1-25

最后，因为空白部分带有公式，复制，再选择性粘贴为数值就可以了。

第 10 招　神奇的快速填充

正如每个人都有不同的脾气一样，Excel单元格也有它的“脾气”，在数据录入的时候需要了解单元格的特点，数据录入要规范，如果不规范，后续数据统计和数据分析会非常麻烦。经常有人问到因为录入的数据不规范，要提取数字和字符串，用公式比较复杂，难以理解，如果你的Excel版本是2013或以上版本，用快速填充功能就可以搞定。这个功能智能到让你惊叹，强大到足以让分列功能和文本函数下岗，看完下面几个案例就能体会到。

1．提取数字和字符串

如果要将图1-1-26中的字符串中的数字提取出来，由于原数据缺乏规律，无法使用LEFT、RIGHT、MID、FIND等文本函数来提取。使用“快速填充”功能则立刻搞定。

图1-1-26

复制A2单元格的“手机”，粘贴到B2单元格，按组合键【Ctrl+E】，或者单击菜单开始→填充→快速填充，如图1-1-27所示。这样就可以把A列左边的文字提取出来。提取数字的方法类似，在C2单元格输入A2单元格中单位为元的数字5400，C3输入15000，再按组合键【Ctrl+E】，其他单元格单位为元的数字就全部提取出来了。在D2输入2，再按组合键【Ctrl+E】，A列中最后的一个数字也提取出来了。

图1-1-27

提示：如果输入一个单元格数字无法正确填充，就再输入一个单元格数字，根据两次输入的数字，快速填充就明白你的意思了。这好比你和别人解释某个问题，解释一遍人家没有明白，再解释一遍就明白了。

2．提取身份证的出生日期

要把图1-1-28中A列身份证的出生日期提取出来，用函数和分列都可以实现，用快速填充更快，先设置B列单元格格式为日期格式，在B1输入A1的出生日期1982-12-05，按组合键【Ctrl+E】就可以迅速填充所有A列身份证的出生日期。

图1-1-28

3．多列合并

例如，要把图1-1-29的A列和B列合并，通常用&连接，只要在C1单元格输入A1和B1的内容，按组合键【Ctrl+E】就可以快速合并。

图1-1-29

4．向字符串中添加字符

要把图1-1-30中A列的电话号码区号、总机、分机号码用“-”隔开，在B1和B2单元格输入分隔好的A1、A2内容，在B3单元格按组合键【Ctrl+E】就可以快速填充A列其他单元格的内容。

图1-1-30

需要提醒的是，如果只是在B1单元格输入分隔好的A1的内容，在B2单元格按组合键【Ctrl+E】，其他单元格填充的都是B1的内容，这里需要输入2次快速填充Excel才能理解你的意图。

5．快速填充功能组合

“快速填充”功能不仅可以实现批量提取的效果，而且在提取的同时还可以将两列单元格的不同内容合并起来。例如提取图1-1-31中省市中的市，提取街道中的号码，将两者合并为新的地址，同样可以利用“快速填充”一步到位解决这一问题。

图1-1-31

在C1单元格输入成都198，按组合键【Ctrl+E】得到的默认是A列的城市名称和B列的数字。

6．调整字符串的顺序

例如要把A列的中英文互换位置，在B1输入A1的互换内容，在B2按组合键【Ctrl+E】就可以快速填充A列其他单元格需要互换位置的内容，如图1-1-32所示。

图1-1-32

7．大小写的转换

A列是大写字母，需要在B列转换为小写，C列首字母大写，其他字母小写，只需要在B1和C1输入相应的内容，按组合键【Ctrl+E】就可以把A列其他单元格内容批量转换，如图1-1-33所示。

图1-1-33

看完以上案例，是不是感觉快速填充如此“懂你”，让你感觉这个功能真是太贴心了，真是“知心姐姐”。

第 11 招　为单元格设置数据录入的范围——数据验证

数据有效性在Excel 2013和2016版本菜单都为“数据验证”，是为特定单元格定义可以接受信息的范围的工具，这些信息可以是数值、序列、时间日期、文本等，也可以自定义。当输入单元格的信息不在可接受范围内，屏幕上就会出现出错信息提示的对话框，而其中的出错信息也是由自己来定义的。例如，用序列来定义单元格，打开菜单数据→数据验证，设置验证条件和输入信息、出错警告。

例如：对性别设置序列如图1-1-34所示。

图1-1-34

单元格下拉框就可以选择序列男、女，如图1-1-35所示。

图1-1-35

序列内容可以手工输入，注意不同序列之间一定要用英文状态下的逗号，也可以引用单元格地址，2013及以上版本可以跨工作表引用。如在Sheet1设置数据验证，序列引用Sheet2内容，可以用鼠标选中Sheet2相应的单元格区域，如图1-1-36所示。

图1-1-36

假如单元格内容为年龄，设置如图1-1-37所示的条件，数据只能输入0～100之间的数据。

如果要输入身份证号码，防止录入错误，可以设置文本长度为18，如图1-1-38所示。

图1-1-37

图1-1-38

第 12 招　数据验证引用序列不在同一行或同一列怎么办

进行数据验证时需要引用的序列多行多列怎么办呢？可以把多列变为一列，方法见第57招到第59招。本招另辟蹊径，通过定义名称的方法实现，解决思路：定义一列或一行数据名称→数据验证定义→修改定义的名称引用位置为多行多列。如果要引用的序列是多行多列，如图1-1-39所示，序列有3行2列，进行数据验证定义鼠标选中后会提示错误，如图1-1-40所示。

图1-1-39

图1-1-40

选中需要引用的B列，输入定义的名称：业务，再对A列做数据验证定义，如图1-1-41和图1-1-42所示。

图1-1-41

这时在A列做了数据有效性定义的单元格下拉框能选择的只有B列内容。单击公式→名称管理器，修改引用位置为B列和C列的数据区域B1:C3，如图1-1-43所示。

图1-1-42

图1-1-43

再来看看A列，下拉框中就有了B列和C列的内容。

第 13 招　利用数据验证给单元格添加注释，不用批注

在Excel中需要添加注释的时候，你可能毫不犹豫选择插入批注。但批注插入多了，有点眼花缭乱的感觉，而且不能批量插入。想给单元格加个注释怎么办？选取单元格，单击菜单数据→数据验证→输入信息→输入要注释的文字，如图1-1-44所示。

图1-1-44

添加后的效果如图1-1-45所示。

图1-1-45

如果有一列数据，需要提醒输入者不能输入大于100的数字，每个单元格插入批注是不现实的，用添加提示的方法则可以完美解决，如图1-1-46所示。

图1-1-46

添加后的效果如图1-1-47所示。

图1-1-47

第 14 招　利用数据验证记录数据录入时间

如果需要记录数据录入时间，借助函数NOW和数据验证可以实现，比如，要记录每张申请单扫描时间，在D2单元格输入公式=NOW()，并将单元格格式自定义为yyyy-m-d h:mm:ss，在B列需要数据验证的单元格区域单击菜单数据→数据验证，数据来源处引用D2单元格内容。这样单击下拉框就可以记录申请单扫描时间，如图1-1-48所示。

图1-1-48

第 15 招　利用数据验证限制单元格数据类型

如果需要限制单元格只能输入文本，不能输入数字，借助函数ISTEXT，该函数功能是检测单元格内容是否为文本，如果是文本则返回TRUE，否则返回FALSE。如果要限制单元格只能输入数字，不能输入文本，借助函数ISNUMBER，该函数功能是检测单元格内容是否为数值，如图1-1-49所示。

图1-1-49

第 16 招　利用数据验证防止重复录入相同的内容

如果要在A列输入数据，为了防止数据重复录入，可以用数据验证定义，如图1-1-50所示。

这样，如果数据重复录入了就会提示，如图1-1-51所示错误。

图1-1-50

图1-1-51

第 17 招　避免数据重复录入

在数据录入的时候为了避免数据重复录入，让Excel给出提示，设置条件格式如下，如果重复录入，字体颜色就变红，如图1-1-52所示。

图1-1-52

第 18 招　怎样插入符号或

要在Excel单元格内容为“是”和“否”后面插入方框，并且在方框内打钩，。

光标放在“是”后面，单击插入→符号，字体选择Wingdings，选择方框符号，如图1-1-53所示。

图1-1-53

光标放在“否”后面，单击插入→符号，字体选择Wingdings，拖动右边的滚动条，拖到最下面，倒数第二个符号就是方框内打钩符号，如图1-1-54所示。

图1-1-54

这个符号还有一个非常便捷的录入技巧，输入大写R，字体选择Wingdings2。如果输入大写T，字体选择Wingdings2，则显示为。

第 19 招　Excel中汉字怎样加上拼音

给汉字加上拼音，好像是Word的特长吧？有没有想过在Excel里为汉字加上拼音呢？想到就能做到，不如现在就试试！你会发现，Excel的拼音功能甚至比Word里更强大。我们在单元格内输入汉字“腾讯”，在工具栏上找到“文”字上带拼音的图标，如图1-1-55所示。

图1-1-55

单击拼音图标右边的小箭头，展开功能，要显示拼音，单击第一项，此时允许显示和编辑拼音，拼音图标也会变成深色。单击第二项编辑拼音，第三项设置拼音格式，如图1-1-56所示。

了解了功能，下面开始设置，先单击要编辑的文字，再单击第一项，打开拼音，点第二项，编辑拼音，文字上方出现编辑框，输入拼音，注意：是输入拼音，不是自动生成。设置好后的效果为，单击拼音图标第三项，打开拼音设置，此页面设置拼音的排列方式，有左对齐、居中和分散对齐三种方式，设置拼音的字体、大小、颜色等属性，这样拼音就设置好了，如图1-1-57所示。

图1-1-56

图1-1-57

如果不想在汉字上方加拼音，想加上其他信息，也可以随意编辑，例如，在汉字上方添加文字：。

第 20 招　怎样在单元格文字前加空白

单元格文字前加空白，例如，从图1-1-58到图1-1-59，可以在文字前面敲空格，如果需要加空白的单元格很多，一个个敲空格，很费时间，也许你会说我可以敲完一个单元格的空白，再用格式刷，这样比一个个单元格文字前敲空格进步了点。然而，这样做后续可能还是有问题，因为文字前有空格，如果其他表文字前没有空格，用VLOOKUP函数匹配明明两张表有相同内容就是匹配不到。怎样避免这个问题呢？选中A2:A4，按住Ctrl键，再选中A6:A9，单击菜单开始→对齐方式→增加缩进量，如图1-1-60所示，就可以完成从图1-1-58到图1-1-59的转换，而且单元格文字前面没有空格，后续VLOOKUP匹配也不会出问题。

图1-1-58

图1-1-59

图1-1-60

第 21 招　怎样在单元格中添加项目符号

项目符号是放在文本（多为列表中的项目）前以强调效果的点或其他符号。在Word文档中可以轻松建立或取消项目符号，如果要在Excel单元格内添加项目符号，该如何操作呢？

方法一：使用Wingdings字体

Wingdings是一个符号字体系列，它将许多字母渲染成各式各样的符号。如果需要插入项目符号的单元格不多，可以手动逐一添加项目符号。单击菜单插入→符号，弹出“符号”对话框，选择“符号”选项卡，从“字体”右侧的下拉列表中选择Wingdings字体，然后选择所需的符号，如图1-1-61所示。

图1-1-61

例如，在A1单元格内每行插入项目符号◆，得到图1-1-62。

图1-1-62

方法二：在Word中插入项目符号后粘贴到Excel中

当需要插入项目符号的单元格数量较多时，逐一添加项目符号有些烦琐。由于在Word中插入项目符号十分方便，可以先在Word中操作再粘贴到Excel中。

先复制需要添加项目符号的单元格区域，将其粘贴到Word中。

再选择所有文本内容，在Word中单击菜单格式→项目符号和编号，在弹出的对话框中选择“项目符号”选项卡，选择一种项目符号类型，单击“确定”按钮。

例如，上面那个例子是一个单元格，如果是很多个单元格，就可以用方法二。

方法三：巧妙利用Alt+数字键

按组合键【Alt+41460】输入得到符号◆，Excel自动在单元格中添加选定的项目编号。

第 22 招　如何输入千分号

在Excel中输入百分号（%）大家都会，如果需要在Excel中输入千分号（‰），你会吗？介绍两种方法。

方法一：快捷键

用组合键【Alt+41451】可以快速输入千分号，如果B1单元格内容是千分号，公式=CODE(B1)返回结果是41451。

方法二

单击“插入”，选择“符号”，再选择字体和子集，即插入→符号→字体（Lucida Sans Unicode）→子集（广义标点），就可以找到千分号，如图1-1-63所示。

图1-1-63

插入的符号只是文本，无法参与正常的数字运算，如果要参与运算，可以用LEFT和LEN函数截取千分号前面的数字，例如，A1单元格内容是20‰，要取数字20，在B1单元格输入公式=LEFT(A1,LEN(A1)-1)。

第 23 招　如何批量添加批注和删除批注

在Excel单元格添加批注可以对单元格内容详细解释说明，如何给多个单元格添加相同的批注呢？一个一个添加太麻烦，如何批量添加、隐藏、删除批注呢？

操作步骤如下：

Step1　在一个单元格里面添加批注内容“2016-3-4”，复制此单元格，然后按Ctrl键选择好需要添加批注的单元格。

Step2　在需要添加批注的任意单元格里单击右键，选择选择性粘贴→批注，如图1-1-64所示。这样就把所选中的单元格批量添加批注了，如图1-1-65所示。

图1-1-64

如何快速把这些批注批量隐藏或删除呢?

我们选中带有数据的单元格区域，按F5键，进入定位对话框，定位条件选择“批注”，再单击“确定”按钮，如图1-1-66所示。在其中任意一个带有批注的单元格上单击右键，选择“隐藏批注”（所有批注就被隐藏）或“删除批注”（所有批注就被立刻删除）。

图1-1-65

图1-1-66

第2章　整理单元格数据

本章介绍单元格数据整理技巧，主要内容有单元格格式的转换、单元格醒目标识、自定义单元格等。

第 24 招　文本与数字格式的相互批量转换

如果数据格式是文本格式，只能计数，不能做求和等运算，需要转换为数字格式才可以。如果只是一个单元格文本转换为数字，只需删掉单元格数据前面的单引号；如果是一列或多列文本数据转换为数字格式，最简单的方法用组合键【Ctrl + Shift +向下键↓】，再用组合键【Ctrl + Shift +向右键→】选中需要转换为数字格式的数据区域，单击下拉框选择“转换为数字”，如图1-2-1所示。

文本转换为数字还有一种常用的方法，就是把文本数据乘以1，除以1或者+0，-0，如图1-2-2所示，A列是文本格式的数据，B列通过乘以1得到数字格式的数据。

图1-2-1

图1-2-2

反过来，数字格式要转换为文本格式，如果只有一个单元格，在数据前面加单引号，记住，一定是英文状态下的单引号。如果要把一列数据由数字格式转换为文本格式，用分列功能实现，前两步选择默认的选择项，第三步把默认的“常规”改为“文本”，如图1-2-3到图1-2-5所示。

图1-2-3

图1-2-4

图1-2-5

单击“完成”即可。

第 25 招　如何快速把数据的单位从元转换为万元

若单元格内容带有公式，复制粘贴的时候需要用到选择性粘贴，这里介绍一个非常有用的技巧。我们工作中经常会遇到把以“元”为单位的数据转换为以“万元”为单位的数据，这个时候就需要用到选择性粘贴。如果数据多行多列，怎么办呢？如果一个个单元格用公式那也得复制粘贴多次，太麻烦了。例如：把表1-2-1的数据转换为以万为单位。

表1-2-1

在数据区域旁边任意一个空白单元格输入10000，选中复制，然后选择性粘贴，选择运算的“除”，就得到想要的结果，如图1-2-6所示。

图1-2-6

第 26 招　对齐两个字的名字

为了对称美观，在对齐两个字名字的时候，很多人会在两个字的中间敲空格键，其实用分散对齐就可以。选中需要设置对齐的单元格区域，按组合键【Ctrl+1】设置单元格格式，单击对齐→水平对齐→分散对齐（缩进）即可，如图1-2-7所示。

图1-2-7

第 27 招　单元格数值满足一定条件字体或背景颜色标红

使用Excel条件格式可以直观地查看和分析数据，发现关键问题以及识别模式和趋势，用户可以为自己的数据区域设置精美而又宜于阅读的格式。这种设置效果不同于单元格格式，它是完全动态的，无论在数据区域中增加或删除行、列，格式都会自动进行相应调整，保持原有的风格。

例如，要把表1-2-2的超过100%的数字标红，以突出显示。

表1-2-2

单击“开始”菜单下面的条件格式，新建格式规则，单击“格式”把字体颜色设为红色（见图1-2-8）。

单击“确定”后效果如图1-2-9所示。

图1-2-8

图1-2-9

第 28 招　单元格数值满足一定条件标上相应的图标

财务上对应收账款的回笼总是希望越快越好，对于未回笼的账款根据账龄长短用三色交通灯提醒，账龄小于1个月用绿色交通灯，表示账款目前安全；2个月黄色预警，表示有点危险；3个月及以上红色告警，表示账龄太久，需要加紧催款，如图1-2-10所示。

选择图标集，找到交通灯图标，设置如图1-2-11所示。

图1-2-10

图1-2-11

再举个比较常见的例子，数据增长和下降用图标突出显示，如图1-2-12所示，设置方法如图1-2-13所示。

图1-2-12

图1-2-13

看到这个例子，有人可能会问为什么不能做到像股市的“红涨绿跌”呢？如果想要箭头的颜色变换，可以通过“插入”形状，设置形状的填充颜色，。

第 29 招　将单元格数字变成条形图

用数据条可以清晰看到数据的大小，不需要通过制作图表就能看出来，如图1-2-14所示。

图1-2-14

如果只想显示数据条，不显示数字，在编辑格式规则时在“仅显示数据条”前面方框内打钩，如图1-2-15所示。

图1-2-15

第 30 招　查找重复值或唯一值

如果需要查找某数据区域是否存在重复值，将重复值醒目标识，单击条件格式→突出显示单元格规则→重复值，设置为自己想要的格式，如图1-2-16所示。如果选中“唯一”，则把唯一值醒目标识，如图1-2-17所示。

图1-2-16

图1-2-17

第 31 招　国际象棋棋盘式底纹设置方法

要设置如图1-2-18所示的国际象棋棋盘式底纹，方法如下。

图1-2-18

选中要设置底纹的数据区域，单击条件格式。

单击新建规则，选择最下面的“使用公式确定要设置格式的单元格”，输入公式=MOD(ROW()+ COLUMN(),2)=0，单击“格式”按钮，在“单元格格式”对话框的“图案”选项卡中选择单元格底纹颜色为浅橙色，单击“确定”按钮，如图1-2-19所示。

函数ROW返回行号，COLUMN返回列号，MOD是两数相除求余数，该条件格式的公式用于判断行号与列号之和除以2的余数是否为0。如果为0，说明行数与列数的奇偶性相同，则填充单元格为浅橙色，否则就不填充。

同样的方法，再设置第2种颜色，如图1-2-20所示。

图1-2-19

图1-2-20

第 32 招　奇偶行不同的斑马纹

当单元格数据行较多时，我们为了让显示效果更加醒目，可以让工作表间隔固定行显示阴影，效果如图1-2-21所示。

条件格式公式设置方法如图1-2-22所示。

图1-2-21

图1-2-22

该条件格式的公式用于判断行号是否被2整除。如果公式返回结果为1，则对奇数行填充灰色底纹；如果公式返回结果为0，偶数行不填充。

隔行填充颜色除了传统的条件格式公式设置，还有一个最简单的方法，只需要1秒钟就可以轻松搞定，单击数据区域任意单元格，按组合键【Ctrl+T】创建表，2007版本以及以上的表默认就自动隔行填充颜色，可以在“表格样式”中选择自己喜欢的颜色。

第 33 招　永恒的间隔底纹

上一招的斑马纹是完全动态的，无论在数据表中插入行或者删除行，其风格都不会改变。但是有一种情况会例外。如果对此数据表进行自动筛选操作，并设置A列的筛选，则间隔底纹效果就被破坏了，如图1-2-23所示。

如果不希望自动筛选对间隔底纹效果产生不良影响，可以通过下面的方法来实现，如图1-2-24所示。

图1-2-23

图1-2-24

利用SUBTOTAL函数来判断可见行的奇偶次序，另外，在条件格式的公式中，使用的是相对引用，这会让条件格式的判断对象随着行的变化而自动改变，达到“只判断当前行的序号”的目的。关于SUBTOTAL函数的功能参考公式与函数篇第9章第135招。

第 34 招　每隔N行批量填充颜色

第32招和第33招的隔行填色，只是隔一行，如果需要每隔N行批量指定单元格格式，如何做？比如，每隔5行为一个项目区域，如图1-2-25所示。为了方便辨别每个项目而不是每行，我们需要按照每隔5行的规则填充单元格。

图1-2-25

编辑规则“为符合此公式的值设置格式”中输入以下公式=MOD(ROUNDUP(ROW()/5,0),2)=1（或等于0），挑选合适的格式，单击“确定”按钮，随后输入该条件格式应用范围，本例为=$B$1:$K$30，随后应用，即可得到如图1-2-26所示中格式的单元格。

图1-2-26

公式解释：用行号除以5，得到的值针对个位进行向上取整，随后针对该整数对2求余，结果为1的行进行格式变化。也就是说，如行号为1、2、3、4、5，除以5分别得到0.2、0.4、0.6、0.8、1，对它们进行个位的向上取整为1、1、1、1、1；结果对2求余皆为1，因此前5行满足公式全部填充。以此类推，由于6～10行结果对2求余为0，因此不填充。所以当需要每隔N行批量填充时，我们利用行号除以N，将结果圈定在(0,1]、(1,2]、…之间，再用ROUNDUP()函数统一成1、2、…。因此我们应用通用公式=MOD(ROUNDUP(ROW()/N,0),2)=1（或等于0）即可达到每隔N行批量填充的目的。为什么这里不能使用ROUNDDOWN()函数？

让我们用刚才的例子想一下：行号为1、2、3、4、5的行除以5分别得到0.2、0.4、0.6、0.8、1，对它们进行个位的向下取整为0、0、0、0、1，因此对2求余的结果不统一，无法满足我们的填充要求。大家可以在图1-2-27红框中查看使用ROUNDUP()及ROUNDDOWN()函数所得到公式结果的区别，图1-2-28即为使用ROUNDDOWN()函数得到的错误填充结果。关于ROUNDUP和ROUNDDOWN函数功能和用法参考公式与函数篇第9章第128招。

图1-2-27

图1-2-28

第 35 招　突出显示符合要求的日期

把下面的日期设置条件格式，周六用粉色标识，周日用绿色标识，其他不变，如图1-2-29所示。

条件格式公式设置如图1-2-30和图1-2-31所示。

图1-2-29

图1-2-30

图1-2-31

注意：条件格式设置公式需要把应用范围固定，如图1-2-32所示。

图1-2-32

如果应用范围是A、B整列，如图1-2-33所示，公式还是=WEEKDAY($A3,2)=6，则得到错误的结果，把不是周末的标识颜色，例如，如图1-2-34所示，2012-1-5是周四却标识粉色的周六，2012-1-6是周五却标识绿色的周日。

图1-2-33

图1-2-34

第 36 招　自动实现生日提醒

如果需要对员工的生日实现按周自动提醒，可以这样设置公式：=ABS(DATE(YEAR(TODAY()),MONTH($B2),DAY($B2))-TODAY())<=7，如图1-2-35所示。

设置后的表格自动显示最近一周过生日的名单，如图1-2-36所示。

图1-2-35

图1-2-36

第 37 招　用条件格式制作项目进度图

制作进度图的数据源如图1-2-37所示。

图1-2-37

首先准备数据并进行初步的单元格格式设置，调整列宽，去掉网格线和边框，如图1-2-38所示。

图1-2-38

选择D3:X8数据区域，单击开始菜单→条件格式→新建规则→使用公式确定要设置格式的单元格，输入公式=(D$2>=$B3)*(D$2<=$C3)，设置单元格背景颜色，公式意思是如果第2行日期大于等于起始日小于等于结束日就表示项目某进程的完成时间，如图1-2-39所示。

再新建规则，输入公式=D$2=TODAY()，这个公式目的是突出显示整个项目进程中的当前日期。单击格式，设置虚线边框和字体颜色，如图1-2-40所示，最终效果如图1-2-41所示。

图1-2-39

图1-2-40

图1-2-41

第 38 招　 你知道你出生那天是星期几吗？——Excel奇妙的自定义单元格格式

相信每个人都知道自己的生日，但是如果问你生日那天是星期几，估计没有几个人知道，一般人都需要查万年历。其实，不用查万年历，用Excel自定义单元格格式就知道，我们在单元格中输入出生日期，比如，1990-5-31，右键设置单元格格式，在自定义里输入aaaa，示例就提示星期四，如图1-2-42所示，如果自定义格式输入dddd，就显示日期的英文Thursday，如果输入ddd，显示日期英文简称Thu。

图1-2-42

自定义格式代码可以为4种类型的数值指定不同的格式：正数、负数、零值和文本。在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。完整格式代码的组成结构为：“大于条件值”格式;“小于条件值”格式;“等于条件值”格式;文本格式，在没有特别指定条件值的时候，默认的条件值为0，因此，格式代码的组成结构也可视作：正数格式、负数格式、零值格式和文本格式，用户并不需要每次都严格按照4个区段来编写格式代码，只写1个或2个区段也是可以的。常用自定义格式代码与示例如表1-2-3所示。

表1-2-3

自定义格式常见的几种应用。

1．自动添加文本

在输入数据之后自动添加文本，使用自定义格式为：@＂文本内容＂；要在输入数据之前自动添加文本，使用自定义格式为：＂文本内容＂@。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置，双引号得用英文状态下的。

实例一："腾讯公司"@"办事处"，在单元格输入郑州，单元格内容显示为“腾讯公司郑州办事处”。

在输入数字之后自动添加文本。

实例二：如在日常财务工作中，常常需要在金额数字后加单位“元”，这时就可以使用：0.00"元"或0"元"，这种通过自定义单元格设置的数字格式是可以做加减乘除等运算的，而如果直接在数字后面加文本，就变成文本格式了，不能做运算。

常见的单位设置如表1-2-4所示，原始数据为100000000，不同设置显示内容如下。

表1-2-4

数字前自动加前后缀。

数字前自动加前后缀，正数和负数显示如表1-2-5。

表1-2-5

2．在自定义格式中使用颜色

要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有[黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、[黄色] 8种不同的颜色。此外Excel还可以使用[颜色X ]的方式来设置颜色，其中X 为1～56的数字，代表了56种不同的颜色。例如：当用户需要将单元格中的负数数字用蓝色来表示，只要使用“#,##0.00;[蓝色]-#,##0.00”自定义数字格式，用户在单元格中录入负数时，Excel就会将数字以蓝色显示。

3．在自定义格式中使用条件格式

在Excel自定义数字格式中用户可以进行条件格式的设置。例如：在学生成绩工作表中，当我们想以红色字体显示大于等于90分的成绩，以蓝色字体显示小于60分的成绩时，其余的成绩则以黑色字体显示，这时只需将自定义数字格式设置为“[红色][>=90];[蓝色][<60];[黑色]”即可。

值得注意的是，当以后需要继续使用刚才所创建的成绩条件自定义数字格式时，你会发现在“单元格格式”的“自定义”分类类型中找不到“[红色][>=90];[蓝色][<60];[黑色]”格式，这是因为Excel自动将你所创建的“[红色][>=90];[蓝色][<60];[黑色]”格式修改成“[红色][>=90]G/通用格式;[蓝色][<60]G/通用格式;[黑色]G/通用格式”，你只需选择此格式即可达到同样的使用效果。实例：把“数学”“语文”成绩中90分以上的替换成“优”：[>=90]"优"。

4．隐藏单元格中的数值

在Excel工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;;;”（3个分号）的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来，但该单元格中的数值可以被其他单元格正常引用，如表1-2-6所示。

表1-2-6

其他常见设置如表1-2-7所示。

表1-2-7

无论为单元格应用何种数字格式，都只会改变单元格的显示形式，而不会改变单元格存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。

第 39 招　筛选后粘贴

筛选后粘贴分为两种情况，一是从其他表复制粘贴到筛选后的表中，二是筛选后在本表格中把一列内容复制粘贴到另一列。

我们先看看第一种情况。表格一部分内容截图如图1-2-43所示；表格二是工作地点为深圳总部的员工，如图1-2-44所示，现在要把图1-2-44内容复制粘贴到表格一筛选后结果为深圳总部对应的英文名列。对表格一筛选出工作地点为深圳总部，由于筛选后不符合条件的行隐藏了，如果直接把图1-2-44英文名复制粘贴到筛选后的表格一，则隐藏行也会粘贴上。想要完成筛选后粘贴，在图1-2-43表格增加一个字段自然数列，如图1-2-45所示，再按照工作地点进行排序，如图1-2-46所示，再把图1-2-44的英文名复制粘贴到排序后的结果处，如图1-2-47所示。

图1-2-43

图1-2-44

图1-2-45

图1-2-46

图1-2-47

如果筛选后要填充的内容在筛选后的表格里，要怎样快速填充呢？如图1-2-48所示，要把D列筛选后结果为√的对应B列内容填充到F列。

图1-2-48

筛选后结果如图1-2-49所示。

图1-2-49

按住Ctrl键，选取筛选后的B列和F列，按组合键【Ctrl+R】，可以快速填充F列，如图1-2-50所示。

图1-2-50

第 40 招　从字母和数字的混合字符串中提取数字

从字母和数字的混合字符串中提取数字，一般用复杂的函数公式完成，本招介绍一个很简单的方法来实现，如图1-2-51所示，要求把A列的数字提取出来放在B～D列。

图1-2-51

操作步骤：

Step1 　复制A列的字符到E列，然后把E列的列宽调整为一个汉字大小的宽度，如图1-2-52所示。

图1-2-52

Step2　选中E列，选择菜单开始→编辑→填充→两端对齐，如图1-2-53所示，执行操作后字符串和数字就会被拆分显示，如图1-2-54所示。

图1-2-53

图1-2-54

Step3　从E列的第一个数字按组合键【Ctrl+ Shift+向下键↓】向下选取全部，打开下拉提示，单击转换为数字，如图1-2-55所示。

图1-2-55

Step4　选中E列，按F5键，定位条件选“文本”，如图1-2-56所示，执行这一步会选取所有非数字的行，然后单击右键选择删除文本就行了，如图1-2-57所示，得到的结果如图1-2-58所示。

图1-2-56

图1-2-57

图1-2-58

Step5　最后再将数字复制，选择性粘贴，把“转置”打钩。

这个案例也可以用第10招介绍的快速填充，但是如果数字和文字完全没有规律，快速填充不一定能实现，此时用两端对齐再删除文本的方法就很简单。