Excel隐秘的角落里，竟然还藏着这些知识点

编者按：本文来自微信公众号“LinkedIn”（ID:LinkedIn-China），作者 北大小笨，36氪经授权发布。

为什么你做了一天的表格，老板看一眼就不想看了？

处理简单的数据没问题，但是一有较为复杂的数据就懵了。一行一行地去手动处理数据，交给老板后却发现错误百出。

Excel作为职场人必备的底层能力，几乎每个人的简历上都写着“熟练掌握”，但是真正懂门道的可能没几个。

其实Excel比我们想象的要智能很多，学会数据验证、快速填充等功能，我们的效率不仅能提高，准确率也能大幅提升。

不要让你的劳力去弥补技能的不足。如果你还不会快速处理较为复杂的数据，就让我们一起进入这期的Excel魔法学院，学学吧！

数据验证

数据验证，也就是数据有效性，是指对单元格设置一定的规则，符合规则的允许录入，而不符合规则的则禁止录入。

数据验证是一种效率极高的数据输入方式，可以对数据的类型、范围、输入条件等进行限定，是Excel中非常有价值的工具。

技巧1：限制数据输入长度

很多信息都有固定的长度，比如手机号是11位，身份证号一般是18位，如何保证数据的输入长度不多不少呢？

操作步骤：选中数据区域，调出【数据验证】对话框，【允许】下拉列表中选择【文本长度】，【数据】为【等于】，长度为【11】，点击【确定】。手机号输入必须是11位就设置完成啦！

技巧2：限制重复输入

如何避免信息重复录入？

操作步骤：选中数据区域，调出【数据验证】对话框，【允许】下拉列表中选择【自定义】，【公式】中输入=COUNTIF($A$4:$A$7,$A4)=1，点击【确定】。

在这里我们通过COUNTIF函数来协助，只要数据个数是1，就代表不重复，超过1，就代表重复了，不能输入。

技巧3：设置下拉菜单，变输入为选择

对于表格中需要输入的固定信息，比如部门名称、性别、城市等，我们可以通过设置下拉菜单的方式变输入为选择。

操作步骤：选中数据区域，调出【数据验证】对话框，【允许】下拉列表中选择【序列】，【来源】处单击右侧折叠按钮，选中菜单项所在区域，点击【确定】。

技巧4：设置二级联动下拉菜单

数据间经常存在着前后关联关系，比如某个部门下对应的员工列表，某个地市所辖的区县等。那么，设置完一级下拉菜单后，对应的二级下拉菜单能否随着一级下拉菜单的不同而出现的选项也不同呢。

答案当然是可以的。下面我们以关联地市-区县的二级下拉菜单为例进行设置。

步骤一：批量创建名称

在Excel中，名称可以用来代替单元格区域的引用。通过定义名称，可以搭建起一级、二级下拉菜单的桥梁。

操作步骤：同时按下【CTRL】+【G】，在【定位条件】对话框中，选择【常量】，点击【确定】。我们这样就选中了包含信息的所有单元格。

之后在【公式】选项卡下，选择【根据所选内容进行创建】命令，默认勾选【首选】，批量创建名称完成。

调出名称管理器，我们可以看到已经定义的名称及其包含的数据区域。

步骤二：设置二级菜单

在城市信息列中，我们已经设置了一级菜单项。接下来在区/县信息列中设置二级下拉菜单。

操作步骤：选中数据区域，调出【数据验证】对话框，【允许】下拉列表中选择【序列】，【来源】中输入“=INDIRECT($M2)”，点击【确定】。

技巧5：设置事先提醒

数据验证，不仅能够制定数据输入规则，还能提醒你该如何填写，是不是非常贴心！

操作步骤：选中数据区域，调出【数据验证】对话框，点击上方【输入信息】项，可以设置标题和信息。比如我们输入信息“请输入信件可送达的地址”，填写时，就会出现这句提醒了。

技巧6：数据无效，那就圈出来

数据验证还能对已经存在的数据进行检查。检查无效，那就圈出来。

操作步骤：先设置数据验证，选中数据区域，调出【数据验证】对话框，【允许】下拉列表中选择【整数】，【数据】选择【介于】，【最小值】为60，【最大值】为100，点击【确定】。

然后在【数据验证】下点击【圈释无效数据】，不在60-100之间的而成绩就被“朱批”出来了。

选择性粘贴

复制粘贴似乎人人都会，不就是CTRL+C/CTRL+V。但其实很多人都不知道，在粘贴下方还有一个被大多数人忽略的选择性粘贴，其实非常实用，不仅能进行数据转换，还能进行各种计算。

技巧7：将公式粘贴为数值

有时候我们希望直接呈现结果而不保留计算过程，就需要将带有公式的数据区域转换为数值。如何转换呢？

操作步骤：复制已填充公式的数据区域，在【开始】选项卡的【粘贴】下，选择【粘贴数值】，公式即可转为数值。

技巧8：将文本转换为数值

从数据系统里导出的表格经常无法求和，SUM一下就会显示结果为0。这都是数字格式惹的祸，看起来像数值，实则为文本格式。如何将这种文本转换为数值？

操作步骤：先在某个空白单元格输入1，复制这个数字1。

选中要将文本转换为数值的单元格区域，调出【选择性粘贴】对话框，点选【数值】按钮，运算选择【乘】，点击【确定】。

技巧9：金额显示单位为万元

如何将数字单位显示变成以万为单位？只要我们将数值批量缩小10000倍，显示的数值就是以万为单位。如何快速实现呢？

操作步骤：先在某个空白单元格输入10000，复制。选中现将金额显示以万为单位的区域，调出【选择性粘贴】对话框，点选【数值】按钮，运算选择【除】，点击【确定】。

技巧10：快速两表比对

如何快速核对两个数据表的值是否一致？选择性粘贴给你解决这个问题的新方法。

操作步骤：先复制其中一个表格，然后选中另一个表格，调出【选择性粘贴】对话框，运算选择【减】，点击【确定】。

两个表格相减为0，则数据一致；不为0，则数据不一致。

技巧11：粘贴为图片/带链接的图片

有时为了排版简洁或阅读方便，我们需要将数据表转换为图片格式。可如果当图中数据会反复发生变化时，就会很麻烦。

不用烦恼，我们可以转换为带链接的图片，原数据表变化，图片上的数据也会直接变化。

操作步骤：复制数据区域，选中任意一个空白单元格，在【开始】选项卡的【粘贴】下，点击【其他粘贴选项】中的“粘贴为带链接的图片”标识。

分列功能

Excel中的分列功能，相信大家都有所耳闻，平常也可能经常会用到。分列功能非常强大，不仅可以拆分、提取，还能规范数据。

技巧12：根据固定符号拆分

当我们要对打卡信息进行拆分，将日期和时间放在两列，但日期和时间中间都存在着空格符号。我们就可以以空格这个固定符号来进行信息拆分。

操作步骤：选中数据列，点击【数据】选项卡下的【分列】，在【文本】分列向导中选择【分隔符号】，下一步中勾选【空格】，在下一步中选择存放数据的位置，点击【确定】，拆分完成。

技巧13：根据固定宽度拆分

除了根据固定符号，还可以根据固定宽度来拆分。比如提取邮政编码，固定宽度都是6位，用分列就非常容易。

操作步骤：选中数据列，点击【数据】选项卡下的【分列】，在【文本】分列向导中选择【固定宽度】，在下一步中，单击目标区域即可添加分割线，分割完成后，点击【确定】，拆分完成。

技巧14：批量修改日期格式

分列还可以直接用来修改数据格式，比如对于各种不规范的日期数据，通过分列功能，可以马上规范统一起来。

操作步骤：选中数据列，点击【数据】选项卡下的【分列】，在【文本】分列向导中直接点击【下一步】，一直到最后一步，将【列数据格式】选择为日期，点击【确定】。

快速填充

自Excel2013以来,表格中就多了“快速填充”功能。什么是快速填充呢？快速填充是“基于示例填充数据，自动识别数据规律并重复运行，能让一些不太复杂的字符串处理工作变得更简单”。

使用快速填充，我们可以轻松完成提取/拆分、合并/组合、位置转换等操作。快速填充的操作非常简单，先输入1-2个数据结果，拖曳填充后，填充方式选择【快速填充】即可。

技巧15：智能合并

技巧16：智能提取

技巧17：智能组合

今天的内容就到这里了，下周将会给大家带来排序、筛选、单元格格式及条件格式的内容，敬请期待！

本文由LinkedIn原创，作者北大小笨，人力资源经理、DiSC国际认证顾问、国家职业生涯规划师、职场办公专家。转载自微信公众号生产力咖啡馆(id:X-Excelers)。