EXCEL小白的进阶之路

从16年3月申请的小号，到现在发表的微文，屈指可数，纠结于想要好的内容，好的页面排版，然而纠结着纠结着也就没了下文，亦或是由于各种坑爹的借口“忙啊，加班啊，累啊”断了写文章的念头，说到底还是执行、专注、毅力不够！看着少年阿段的文章似乎又将我拉回“应该开始写作”的想法，对哦！“不是应该，而是要”的想法，写文到底是为了什么！自己或是别人，为了记录或是分析！浩荡的2017年，经历了啥哦！一半已过，时间给我留下了什么，亦或是在我的时间维度里我为它增贴了什么！

最近这3个月来真正感受到了自己的渺小，原来还有这么多不知道！不知道！不知道！我做了啥哦！少了，暗戳戳的开始了Excel的小白进阶之路！跟着我来一起学，get小技能吧！年末我该成为大咖了吧！明年我该做点不一样的事了吧！（师从卢大神，这个100天我想很值！有需要的可东京搜索“EXCEL不加班系列书籍”）暂且当小说看着吧！看得有点吃力，教材书不错，推荐买！

第1章 7天养成一个好习惯

养成一个好习惯不是两三天就可以的，而是长期坚持的结果，最快也要7天才可以养成一个好习惯。这里先不说什么是好习惯，而是从它的对立面坏习惯说起。在Excel中有哪些习惯是不好的呢？了解这些反面教材，我们引以为戒，从今以后不犯这些低级错误。不犯错，就是最好的习惯。

Day1拒绝合并单元格

卢子：Excel中有这么一个功能叫“合并后居中”，很多人都经常使用这个功能。木木，你是不是也经常使用？

木木：对啊，这么好用的功能干嘛不用，如图 1‑1所示，表格使用这个功能看起来好看多了！

图1‑1使用合并单元格的效果

卢子：确实挺好看的，不错，赞一个。但是，你试过对合并后的单元格进行筛选吗？比如筛选品类为梨子的所有对应值。

木木：这个还真没有，我试试看。如图 1‑2所示，居然只显示第一个对应值，怎么回事呢？

图1‑2只显示第一个对应值

卢子：这种合并居中显示适合在纸中记录，而我们现在使用的是Excel。传统手工这样输入我们能够自己识别，而在Excel中却识别不出来。我们不能用传统的思维来制作表格。如图 1‑3所示，选择梨子这个单元格，单击“合并后居中”按钮。因为已经合并单元格了，再单击“合并后居中”按钮就相当于取消合并单元格。

图1‑3取消合并单元格操作

如图 1‑4所示，取消合并单元格后，就只有第一个有值，其他都是空白的。也就是说在进行筛选操作的时候，实际上只能识别第一个单元格为符合条件，而其他都不满足，所以筛选不到。

图1‑4取消合并单元格后效果

木木：原来这样啊。

卢子：如果后期要对数据进行处理，建议还是尽量不使用合并单元格，这样能避免一些不必要的麻烦。现在给你布置一道作业，如何实现筛选所有符合梨子的对应项目？

木木：感觉好难的样子，回去我好好考虑。

不到半个钟时间，木木就做出来了，让卢子很惊讶！

卢子：木木，你好棒啊，这么难的问题，你居然会了。

木木：偷偷地告诉你，我是百度到的，自己不会做。

卢子：善于借助搜索引擎，是一个很好的习惯，现在是网络时代，要利用好身边的一切资源。那你说一下具体怎么操作吧？

木木：这个操作步骤实在太高大上，容我一步步道来。

Step 01 如图 1‑5所示，选择区域A2:A16，单击“合并后居中”按钮，也就是先取消合并单元格。

图1‑5选择区域取消合并单元格

Step 02 如图 1‑6所示，按F5键调出“定位”对话框，单击“定位条件”。

图1‑6调出定位对话框

Step 03如图 1‑7所示，选择“空值”，单击“确定”按钮。

图1‑7定位空值

Step 04 如图 1‑8所示，输入公式=上一单元格，即=A2。

图1‑8填充公式

Step 05 关键一步，不能直接按回车，否则就前功尽弃了。看清楚了，如图 1‑9所示，按组合键Ctrl+Enter结束，瞬间就将所有内容填充完毕。

图1‑9填充公式操作

现在要筛选什么品类，直接筛选就行，非常方便。

卢子：现学现用，好厉害。

木木：不过我不知道这里为什么要输入=A2，原理是什么呢？只是糊里糊涂跟着操作。

卢子：这里涉及到两个知识点：

①相对引用的作用：在A3输入=A2，在A4就自动变成=A3，在A6就自动变成=A5，也就是说不管到哪一个单元格，始终等于上一个单元格的值。这个在后面函数部分我会跟你详细解释。

②定位空值的作用：只是填充没有值的单元格，而有值的单元格就始终保持不变。

木木：这下明白了，既懂了方法又懂原理。

知识扩展：

填充合并单元格的内容后，区域中是包含有公式的，如果能够将公式变成值的形式会更好。选择区域A2:A16复制，右击选择粘贴成值，也就是“123”，如图 1‑10将公式复制粘贴成值所示。

图1‑10将公式复制粘贴成值

课后练习：

如图 1‑11所示，正常情况下我们都是需要合并单元格才能居中，如何在不合并单元格的情况下实现居中呢？

图1‑11不使用合并单元格的居中

Day2名词缩写带来的麻烦

卢子：如图 1‑12使用简写所示，这是2个会计论坛的每天发帖明细表，有的时候为了偷懒，我们会将会计科普论坛写成会计网，将会计视野论坛写成视野。木木，你是不是这样干过？

图1‑12使用简写

木木：你怎么知道？这样每天少写很多字，多好。

卢子：这样说也合情合理，能偷懒的情况下，谁不想偷懒。一向以懒人著称的我，很多时候连写都不想写，直接复制粘贴上去。如果现在让你分别统计2个论坛的发帖数，你有什么办法统计吗？

木木：就这几天的发帖数，我敲几下计算器就搞定了，不发愁！

卢子：如果现在的数据是10000行呢？你是不是准备敲到明天？

木木：是啊。

卢子：说句实话，这些我曾经也干过，就是因为当初不够懒，说多了都是泪。当一个人懒到了极点，必然会想办法让自己效率更高，让自己更加轻松。

木木：那现在是不是有更好的办法解决？

卢子：因为名词进行缩写，所以不能直接汇总。但我们可以制作一个下拉菜单，通过下拉菜单选择论坛，这样可以实现快速输入，同时保证一致性。名词统一后，就可以借助数据透视表轻松实现汇总。

Step 01 将论坛的全名输入在E列，如图 1‑13输入论坛全名所示。

图1‑13输入论坛全名

Step 02 如图 1‑14设置下拉列表所示，选择区域B2:B17，切换到“数据”选项卡，单击“数据验证”图标。在“允许”的下拉列表框中选择“序列”选项，在“来源”文本框引用=$E$2:$E$3（E列的区域，直接用鼠标选择即可），最后单击“确定”按钮。

图1‑14设置下拉列表

Step 03 如图 1‑15下拉菜单示意图所示，现在要选择论坛名，只需通过下拉菜单进行选择即可，方便快捷。

图1‑15下拉菜单示意图

木木：这样确实方便好多，以后再也不用手工输入。

卢子：这里再简单介绍如何用数据透视表汇总每个论坛的发帖数。

Step 01如图 1‑16创建数据透视表所示，单击数据源任何单元格如A1，切换到“插入”选项卡，单击“数据透视表”图标，默认情况下会自动帮你选择好区域，保持默认不变，单击“确定”按钮。

图1‑16创建数据透视表

Step 02 如图 1‑17勾选字段所示，弹出“数据透视表”字段对话框，只需同时勾选论坛跟发帖数，就可以快速统计每个论坛的发帖数，不到1分钟时间就完成别人1天的工作量。

图1‑17勾选字段

木木：哇！这个功能好牛逼。

卢子：数据透视表是Excel最强大的功能，等你以后熟练了基本操作以后再教你更全面的用法。

知识扩展：

在设置下拉菜单的时候，来源采用引用单元格区域，这样就造成多余了一列。其实来源也可以直接手写，然后用英文状态的逗号隔开，如图 1‑18直接写来源所示。

图1‑18直接写来源

设置了下拉菜单，如果你用手工输入的话，输入名词缩写，会提示警告对话框，不让你输入，如图 1‑19所示。

图1‑19警告对话框

虽然不让输入其他值，但对于以前已经输入的值是无效的。以前输入的缩写可以用“圈释无效数据”这个功能将这些圈释出来，最后再重新更改成标准的，如图 1‑20所示。

图1‑20圈释无效数据

课后练习：

如图 1‑21所示，论坛不在同一个工作表的情况下，如何制作一级下拉菜单？

图1‑21跨工作表制作下拉菜单

Day3统一日期格式

卢子：中国文化博大精深，光表示日期就有一大堆方法：2015年1月30日、1月30日、1-30、1/30、1.30、20150130……

木木，你平常喜欢用哪一种形式的日期呢？

木木：我比较喜欢用2015-1-30这种形式的日期。

卢子：原来木木一直保持着良好的习惯，都输入规范的日期。如图 1‑22所示，如果是标准日期跟不标准日期混合在一起，你如何将2.1这种不标准的转换成标准呢？

图1‑22标准日期跟不标准日期的混合

木木：这个我会。如图 1‑23所示，选择A列，按组合键Ctrl+H，调出“查找和替换”对话框，将.替换成-，单击“全部替换”即可。

图1‑23替换

如图 1‑24所示，一下子就转变成标准日期了。

图1‑24替换后效果

卢子：看来以后不能小看木木了，“查找和替换”功能掌握得挺好的。其实除了替换，还可以用分列完成。跟替换功能比较起来，会显得麻烦一点，不过这里作为另外一种办法，参考下。

Step 01 如图 1‑25所示，选择区域A2:A6，切换到“数据”选项卡，单击“分列”图标，保持默认不变，连续2次单击“下一步”按钮。

图1‑25文本分列第1步

Step 02如图 1‑26所示，选择“日期”格式，单击“完成”按钮，就可以转换成标准日期。

图1‑26文本分列第2步

木木：你多教一种方法，我就多学习一种，赚到了。

知识扩展：

分列是一个很强大的功能，可以将数值跟文本格式互相转换、按分隔符分列、按固定字符分列等。

01按分隔符（*）分列

如图 1‑27所示，尺寸的数据都是用*隔开，有没有办法提取长、宽？

图1‑27提取长、宽

Step 01 如图 1‑28所示，选择单元格A2:A10，单击“数据”选项卡→“分列”按钮，在弹出的“文本分列向导”对话框保持默认不变，单击“下一步”。

图1‑28文本分列第1步

Step 02如图 1‑29所示，“分割符号”在其他文本框输入*，单击“下一步”

图1‑29文本分列第2步

Step 03如图 1‑30所示，目标区域为B2单元格，单击“完成”。

图1‑30文本分列第3步

02按固定字符将姓名分离

如图 1‑31所示，怎么姓名分成姓跟名，并显示在两列呢？

图1‑31姓名清单

Step 01选择单元格A2:A12，将姓名复制到B列。

Step 02单击“数据”选项卡→“分列”按钮，在弹出的“文本分列向导”对话框，选择“固定宽度”，单击“下一步”。

Step 03用鼠标单击第一个汉字处，单击“确定”按钮。

姓名就完成分列，如图 1‑32所示。

图1‑32按固定字符分列

课后练习：

如图 1‑33所示，如何从身份证将出生日期分离出来？

图1‑33提取出生日期

Day4数据与单位分离

卢子：如图 1‑34所示，这是Excel效率手册销售明细表，在记录的时候为了让别人看清单位，所以在最后面添加一个本字。木木，你见过这种吗？

图1‑34数量包含单位

木木：当然见过啦，我们做财务的，很多时候都是这样，在金额后面添加单位元，比如2000元这种。

卢子：这种看起来虽然没什么问题，但实际上却是个大问题。这种数据是不能直接求和的，你可以试试？

木木：我试试看。

Step 01 如图 1‑35所示，将鼠标放在B7这个单元格，单击“自动求和”图标。

图1‑35自动求和

Step 02 如图 1‑36所示，用鼠标选择求和区域B2:B6。

图1‑36选取区域

Step 03 如图 1‑37所示，回车后，总数量为0。

图1‑37数据不能求和

木木：还真是这样，这是怎么回事？

卢子：添加了单位的数字，就不叫数字了，叫文本。文本是不能求和的，直接当0处理。也就是说数字跟单位要分离才可以，这个分离木木应该很熟练吧，你来操作一遍。

木木：好啊。

如图 1‑38所示，借助组合键Ctrl+H调出“查找和替换”对话框，将本替换成空，单击“全部替换”按钮。

图1‑38替换本字

如图 1‑39所示，将单位替换掉，现在就乖乖自动求和了。

图1‑39数量可以求和

卢子：如图 1‑40所示，一格一属性，不同东西的不要放在同一个单元格。单位都统一的话，可以直接放在表头。

图1‑40一格一属性1

如图 1‑41所示，单位不统一的话，可以添加一列。

图1‑41一格一属性2

小小的改变，却能给你统计带来极大的便利。

知识扩展：

如果一定要显示单位“本”，可以通过自定义单元格格式来实现。自定义单元格格式不会改变单元格本身的性质，只是欺骗我们的眼睛而已。

如图 1‑42所示，选择区域B2:B6，按组合键Ctrl+1调出“设置单元格格式”对话框，单击“自定义”，输入类型为：0"本"，单击“确定”按钮。

图1‑42自定义单元格格式

自定义单元格格式后，虽然单元格多了一个“元”字，但照样可以求和。

课后练习：

如图 1‑43所示，输入数字的时候，自动显示包装N部。

图1‑43自定义包装N部

Day5不使用无意义的空格

卢子：如图 1‑44所示，这是刚刚的Excel效率手册的销售明细表改进后的效果，在姓名这里，我们需要将姓名对齐。很多人都是用输入空格的方法，让姓名对齐的，木木你是否也是这样做的？

图1‑44输入多余的空格

木木：是啊，你怎么知道的，我以前经常这样做。难道有其他方法吗？

卢子：方法还真有一个，将对齐方式设置为分散对齐即可。

Step 01 如图 1‑45所示，借助组合键Ctrl+H，调出“查找和替换”对话框，查找内容输入一个空格，单击“全部替换”按钮。

图1‑45替换掉空格

Step 02 如图 1‑46所示，选择区域A2:A6，单击“对齐方式”设置，“水平对齐”方式选择“分散对齐”，单击“确定”按钮。

图1‑46设置分散对齐

如图 1‑47所示，设置完成后，姓名就自动对齐。

图1‑47分散对齐效果

这样设置可以不用录制空格，大大提高了效率，同时也就避免了空格录入多一个或者少一个的情况。

木木：这方法好棒，学习了。

知识扩展：

如图 1‑48所示，用手工添加空格的方法有时难免会多添加一个空格或少添加一个空格，这样会被Excel认为是不同姓名，导致统计出错。

图1‑48统计出错

课后练习：

如图 1‑49所示，金额为了更好看，进行了缩进，除了敲空格，你知道该如何做吗？

图1‑49金额缩进

Day6保护工作表中的公式不被修改

卢子：如图 1‑50所示，这是一份员工信息表，如果要发送给别人，但里面的黄色填充部分设置了公式不想让别人修改。木木，如果是你，你会怎么做呢？

图1‑50员工信息表

木木：直接跟他们说，这里有公式，不能修改，否则会出错啦！

卢子：靠人为提醒始终不是办法，如果要发送的人多，不可能全部提醒，即使提醒了别人也不一定会记住。

木木：那还能怎么办？

卢子：其实Excel中有一个功能叫：保护工作表，也就相当于给工作表加一把锁，要打开这把锁必须有钥匙才行，而这把钥匙就是密码。这个密码只有你才有，别人没有。通过保护，别人想改也改不了。

木木：居然有这么神奇的功能，现在就想看看具体如何操作的？

卢子：这个操作步骤比前面的那些功能稍微繁琐一点，我一步步说给你听。

Step 01 如图 1‑51所示，单击“全选”按钮，按组合键Ctrl+1调出“设置单元格格式”对话框，切换到“保护”选项卡，取消勾选“锁定”和“隐藏”前面的复选框，单击“确定”按钮。

图1‑51取消锁定

Step 02 如图 1‑52所示，选择区域C2:D14，按组合键Ctrl+1调出“设置单元格格式”对话框，切换到“保护”选项卡，勾选“锁定”和“隐藏”前面的复选框，单击“确定”按钮。

图1‑52勾选锁定跟隐藏

Step 03 如图 1‑53所示，切换到“审阅”选项卡，单击“保护工作表”图标，设置密码为123456（设置自己能够记住的号码），单击“确定”按钮。

图1‑53设置工作表保护

Step 04 如图 1‑54所示，再输入一遍密码，单击“确定”按钮。

图1‑54确认密码

大功告成，如图 1‑55所示，现在只要修改公式区域，就会自动警告，让你无法修改。

图1‑55警告提示

木木：好像听懂了，不过我得回去熟练下才行，要不记不住！

知识扩展：

如果以后自己要修改公式，可以撤销密码。如图 1‑56所示，单击“撤销保护工作表”，输入密码123456，即可。

图1‑56撤销工作表保护

课后练习：

如图 1‑57所示，对表格进行保护，除了可以进行图片编辑以外，其他功能都禁止使用。

图1‑57保护后能对图片进行处理

Day7数据备份以及另存为PDF很重要

卢子：木木，最近Excel学得怎么样了？

木木：你教的那些我全部都会啦，哈哈。

卢子：其实这些都是细节问题，稍微提一下就会了。对了，平常你收到别人的Excel文档的时候，你是直接编辑还是？

木木：打开Excel后就直接编辑啊，难不成还要做什么处理？

卢子：对于一些不重要的表格，这样直接编辑没有问题。但对于一些表格模板或者重要的表格，最好实现备份，如图 1‑58所示，利用副本进行编辑，不要在原稿上进行修改，以免造成一些意想不到的麻烦。

图1‑58建立副本

木木：会有什么麻烦呢？

卢子：比如你现在对表格进行了一系列操作以后，要想重新复原到最初的表格基本上是办不到。如果只是用副本操作，不管怎么操作，原来的文档都还在，如果以后需要最初的表格，你依然可以用到。

木木：你考虑的真周到，学习了。

知识扩展：

还有一种情况就是：报告做好了，一份留底，一份传给供应商。为了让报告不被修改，以前都是直接打印出来，然后再用打印件传真过去给供应商。现在提倡绿色办公，节约用纸，有没有办法能直接发送文档过去，别人无法修改内容？

其实可以将Excel转换成PDF，这样别人就不能轻易更改你的内容。如图 1‑59所示，文件“另存为”，选择储存位置，保存类型选择PDF，单击“保存”按钮。

图1‑59另存为PDF

今日无练习，7天的好习惯已经讲完，你有收获吗？

第2章 事半功倍的数据批量处理绝招

好习惯养成了，接下来就得学会快速录入数据，这样才能更好的提高工作效率。

Day8序列数字生成大法

卢子：木木，如图 2‑1所示，这里有一份客户的清单，需要逐个输入序号，你知道怎么做吗？

图2‑1客户清单

木木：这个我会，很容易。

如图 2‑2所示，在A2单元格中输入1，鼠标放在A2单元格右下方，出现“+”字形，按住Ctrl键，拖动鼠标下拉到A15就可以生成1-14的序号。

图2‑2下拉生成序号

卢子：不错。鼠标下拉这种适合生成的序号比较少的，而序号多的话用这种也不太合适。如图 2‑3图所示，这时可以将鼠标放在A2单元格右下方，出现“+”字形，双击单元格，选择“填充序列”就可以自动填充序号。

图2‑3填充序列

木木：对哦，如果有1万个序号，要下拉好久，还是你的方法快捷。

知识扩展：

如果输入的序列为文本型数字的话，操作会略有差异。

如图 2‑4所示，输入第一个序号后，直接下拉就可以。

图2‑4直接下拉

如图 2‑5所示，输入第一个序号后，采用双击填充。

图2‑5双击填充

课后练习：

如图 2‑6所示，如何生成偶数等差序列？

图2‑6偶数等差序列

Day9你所不知道的日期填充秘密

木木：卢子，求救。如图 2‑7所示，我在给人员排班的时候，希望按工作日来填充日期，但直接下拉的话没有排除掉周末，怎么办？

图2‑7人员排班表

卢子：如果你细心观察的话，如图 2‑8所示，你会发现下拉的时候有一个“自动填充选项”，单击这个“自动填充选项”里面出现了各种各样的填充方式，选择“以工作日填充”。

图2‑8以工作日填充

如图 2‑9所示，我们也可以试试“以月填充”跟“以年填充”的效果。

图2‑9以月跟年填充

木木：原来如此，谢谢卢子。

知识扩展：

日期填充还能返回上个月的最后一天。

Step 01 如图 2‑10所示，输入第一个日期选择“以月填充”。

图2‑10以月填充

Step 02如图 2‑11所示，往左边拖拉，选择“以天数填充”。

图2‑11以天数填充

课后练习：

如图 2‑12所示，利用填充日期的功能，填充返回上一个月。

图2‑12返回上个月

Day10神奇的快速填充

Excel中除了填充序列、日期填充，还有一个最NB的填充。正名叫快速填充，别名叫闪电填充。

神奇的快速填充，到底有多神奇呢？

1、从身份证号提取出生日期，如图 2‑13所示。

图2‑13提取出生日期

如图 2‑14所示，单元格事先设置为文本或者输入一个'，输入第一个出生日期，下拉选择快速填充。

图2‑14快速填充

2、从文本和数字混合中提取数字，如图 2‑15所示。

图2‑15提取数字

输入第一个数字，按快捷键Ctrl+E，如图 2‑16所示。

图2‑16Ctrl+E的使用1

3、从电子邮箱中提取名字，如图 2‑17所示。

图2‑17提取名字

输入第一个名字，按快捷键Ctrl+E，如图 2‑18所示。

图2‑18Ctrl+E的使用2

4、合并姓名，如图 2‑19所示。

图2‑19合并姓名

输入第一个姓名，按快捷键Ctrl+E，如图 2‑20所示。

图2‑20Ctrl+E的使用3

5、合并姓名和手机号码，如图 2‑21所示。

图2‑21合并姓名和手机号码

输入第一个姓名和手机号的所有内容，按快捷键Ctrl+E，如图 2‑22所示。

图2‑22Ctrl+E的使用4

课后练习：

从调色配方中提取中间配色，如图 2‑23所示。

图2‑23提取中间配色

Day11高大上的时间录入方法

普通录入

1、手动输入技巧

我们手工录入当年日期时可以不用输入年份，如输入4-25，会自动补全当年年份变成2017-4-25。将单元格格式设置为短日期或者自定义为：yyyy-m-d，如图 2‑24所示。

图2‑24短日期

2、快捷键录入法

按住键盘上的Ctrl，再按键盘上的分号（；），这时单元格上直接显示当前的日期，如图 2‑25所示。

图2‑25显示当前日期

按住键盘上的Ctrl，再按住Shift，再按上分号（；），这时将自动显示当前的时间，如图 2‑26所示。

图2‑26显示当前的时间

3、函数录入法

TODAY函数，返回当前的日期。当您需要在工作表上显示当前日期而不管您是何时打开工作簿时，TODAY 函数会很有用，如图 2‑27所示。

图2‑27TODAY函数

NOW函数，返回当前的时间。在您需要在工作表上显示当前日期和时间，或者基于当前日期和时间计算某一值，并且每次打开工作表该值都更新时，NOW 函数会很有用，如图 2‑28所示。

图2‑28NOW函数

知识扩展：

高大上录入

我们经常要在Excel 表格里录入日期（年月日）＋时间（0:00:00）

有没有一种方法可以直接点到那一格就自动弹出一个选择框，直接在框里选择日期和时间（显示格式：2017-6-11 9:10:01），如图 2‑29所示。

图2‑29选择当前日期+时间

当前时间自动显示在那个选择框里，修改后点确定后直接录入到表格里。

Step 01 将单元格格式设置为：yyyy-m-d h:mm:ss，如图 2‑30所示。

图2‑30自定义单元格

Step 02 在E1输入NOW函数。

=NOW()

Step 03 制作下拉菜单。选择单元格区域C2:C7，切换到“数据”选项卡，单击“数据验证”图标，在允许下拉文本框选择“序列”，来源引用用鼠标引用E1的位置，单击“确定”按钮，如图 2‑31所示。

图2‑31下拉菜单

课后练习：

如图 2‑32所示，将A列的日期跟B列的时间合并起来，显示在C列。

图2‑32将日期和时间合并

Day12录入长字符串的技巧

卢子：木木，如图 2‑33所示，假如现在增加一列内容，录入身份证号码，你知道怎么操作吗？

图2‑33如何录入身份证

木木：这个我会，如图 2‑34所示，身份证号码跟其他不一样，超过15位数字，如果直接输入会出错，15位后面全部变成0。

图2‑34超过15位数字后面全变成0

如图 2‑35所示，在输入的时候，只需在身份证前面输入“'”即可。

图2‑35输入’

卢子：这个也是一种方法，我平常更习惯将单元格设置为“文本” 格式。如图2‑36所示，选择区域C2:C15，将单元格格式改成“文本”。

图2‑36设置文本格式

这种有一个好处就是，一劳永逸。只需设置一次单元格格式，以后只需输入身份证号码即可。

知识扩展：

如图 2‑37所示，在Excel中直接输入12位的数字，居然变成这样9.57536E+11，这个究竟是什么鬼？

图2‑379.57536E+11

这是科学计数法，超过11位的数字，在默认情况下会显示科学计数法。因为金额一般都没有那么多位数，12位的金额是多少，我书读得少不会算，你知道吗？

在遇到这种超过11位的字符，可以先输入'再输入一串数字，或者用设置为文本格式更方便。

除了这种，还有就是0开头的数字，也必须要设置为文本格式，否则0会自动消失，如图 2‑38所示。

图2‑38消失的0

最后一种是分数，也需要设置为文本格式，否则输入后就会出错，如输入3/16，就变成3月16日。默认情况下以/和-作为分隔符都是当做日期处理，如图 2‑39所示。

图2‑39分数变日期

课后练习：

如图 2‑40所示，如何将网页上面的身份证号码复制到Excel中？

图2‑40身份证号码

Day13批量为城市添加省份

卢子：如图 2‑41所示，这是广东省各城市人员对应表，木木，如果是你，你怎么输入这些城市呢？

图2‑41广东省各城市人员对应表

木木：直接输入啊，如：广东潮州，难不成还有其他办法？

卢子：直接输入当然可以，但实际上没必要这么做。因为前面2个字都是“广东”，这个可以通过自定义单元格格式得到，也就是说只需要输入潮州这样的就可以。

如图 2‑42所示，选择区域B2:B16，利用组合键Ctrl+1，弹出“设置单元格格式”对话框。切换到“数字”选项卡，然后选择“自定义”选项，在类型框中输入代码："广东"@，单击“确定”按钮。

图2‑42自定义单元格格式

木木：这个代码是什么意思？

卢子：@代表所有文本，"广东"@也就是在所有文本前面添加广东2个字。

木木：那什么代表所有数字呢？

卢子：数字用：G/通用格式，如果是整数的话可以直接用0表示。比如要输入每个人的年龄，就可以用下面的自定义代码：

G/通用格式"岁"

0"岁"

如图 2‑43所示，现在只需输入数字，就会自动在后面添加岁字。

图2‑43自定义后输入的效果

木木：又学了一招，效率又提高一点点了。

Step 02 选择区域，切换到“公式”选项卡，单击“根据所选内容创建”，取消勾选“最左列”，单击“确定”按钮，如图 2‑66所示。

图2‑66根据所选内容创建定义的名称

Step 03 选择区域，单击“数据”选项卡→“数据验证”，在允许文本框选择“序列”，来源文本框输入下面的公式，单击“确定”按钮，这时会弹出一个对话框，直接忽略即可，如图 2‑67所示。

=INDIRECT(A2)

图2‑67设置函数

经过3个小步骤，就可以进行关联选择，如图 2‑68所示。

图2‑68二级下拉菜单效果

知识扩展：

我们在制作表格的时候，还有一种很特殊的下拉菜单选择法，不过不是借助序列这个功能，而是结合已经输入的内容加快捷键Alt+↓实现的，如图 2‑69所示。

图2‑69特殊下拉菜单

课后练习：

随着时间的推移，经典的书籍会越来越多，如何自动将新增加的经典书籍增加到一级下拉菜单里。比如增加了Excel不加班这本书，如图 2‑70所示。

图2‑70自动更新一级下拉菜单

Day16 快速输入特殊符号

卢子：木木，你在输入√或者×这些符号的时候是怎么输入的呢？

木木：按住Alt+小键盘的数字，√就是41420。

卢子：对于一向使用笔记本的我而言，靠这种方法输入非常麻烦，还有就是不容易记住这些数字。如图 2‑71所示，我一般都是借助搜狗拼音输入法，来输入这些特殊字符，输入dui就能得到√，输入cuo就能得到×。

图2‑71搜狗输入法输入特殊字符

使用搜狗拼音输入法，还可以输入平方米（㎡），立方米（m³）等，大大减轻了记忆的负担。

木木：这个更好，赞一个！

卢子：搜狗拼音输入法中还有“特殊符号”这个功能，借助这个功能可以输入大多数特殊字符。

如图 2‑72所示，搜狗拼音输入法中还有“特殊符号”这个功能，借助这个功能可以输入大多数特殊字符。

图2‑72特殊符号

知识扩展：

Excel输入带框√×新技能：如果数字大于等于0就输入带框的√，小于0就输入带框的×。正常这种很难做到，但其实通过设置字体为Wingdings 2，R就是带框的√，Q就是带框的×，这种方法是不是挺不错！！！如图 2‑73所示。

=IF(A2>=0,"R","Q")

图2‑73录入带框√×