下载掌阅APP，畅读海量书库
立即打开
小张好不容易录完的表，“手机号”“身份证号码”“入职时间”列却出现了E+10、E+17和######等“乱码”。（见图5-1）
图5-1
看到这张表，小张一头雾水地问道：“表姐，我这张表是不是中毒了呀？”（见图5-2）
图5-2
其实，小张的问题是因为做表的时候，数据不规范！
我们先来看看，小张的这张表究竟不规范在哪里。
原因分析：单元格列宽不够，造成显示不全。因此，当单元格内容过多时，会显示为######。
解决方案：将E列“入职时间”的列宽调大一些即可。
原因分析：Excel对于输入超过11位的数字，会以科学记数的表达方式来显示。并且科学记数法的数字精度为15位，超过15位的所有数字，Excel都将其自动改为0。这也是平时工作中，我们录入身份证号码后，有时会发生最后3位显示为“0”的原因。
解决方案：对于长串数字，要以文本的格式录入。
表姐口诀
数字不计算，文本大法，早用早好。
长文本的录入技巧如下。
①在单元格先输入’（英文状态下的单引号），然后再输入数字，此时单元格会默认以文本格式写入。录入完毕后，单元格的左上角出现一个绿色小三角，提示用户该单元格的内容是以文本形式存在的（见图5-3）。
图5-3
②先设置单元格为“文本”格式，再录入内容。重新插入一列E列，选中E列后→选择“开始”选项卡→将“单元格格式”从“常规”改为“文本”（见图5-4）。设置完毕后，再录入任何数字，显示的格式都是“文本”型，也就是无论用户输入多长，Excel都将按照你输入的内容进行显示，不会再改为科学计数法的形式。这个技巧常常用于录入身份证号码、手机号码、银行卡号等长串数字。
图5-4
温馨提示
长串数字录入时，必须先设置单元格格式为文本格式后，再录入数据。否则，如图5-4中D列所示，先录入好数字后，再将单元格格式改为“文本格式”是不起作用的，只能重新录入才行。
我们再看一下图5-5中的“入职时间”F列，这些日期格式都不太一样：有的是以小数点分割年、月、日；有的是写年、月、日；有的没有写日，写的是号；还有的是以斜杠分隔，这让表格显得非常混乱。
图5-5
当选择“开始”选项卡→“排序和筛选”→单击“筛选”（见图5-5）→打开筛选功能后，不难发现有些日期，Excel会自动帮我们分类为“年、月、日”，但是有些却不会（见图5-6中红框的部分）。
原因分析：不能够自动归类的，都是“不规范的假日期”。在平时工作中，不用费劲记哪些是不规范的，因为错误是不可能穷举的。只用记住规范的“真”日期只有以下3种情况。
（1）一横：用短横线“-”分隔的日期，如2019-1-1。
（2）一撇：用斜线“/”分隔的日期，如2091/1/1。
（3）年月日：用中文“年、月、日”分隔的日期，如2019年1月1日。
表姐口诀
一横一撇年月日，任何符号输英文。
图5-6
解决方案：将“假日期”修改为“真日期”。筛选出“假日期”（见图5-7），这3行日期实际上就是错误的日期格式，然后依次修改为图5-8中“真日期”格式。
图5-7
图5-8
修改完后，再单击筛选功能，可以看到所有的日期都自动进行“年、月、日”的分组了，并且单击“日期筛选”，还可以看到更多的筛选方案（见图5-9）。而“真日期”也是后面我们利用数据透视表，自动生成月报、季报、年报的“重要基础”，这利用的就是“真日期”自动分组的功能。
图5-9
温馨提示
如果要把短期日（如2019/1/1）显示为长日期（如2019年1月1日），只需要选中整列→选择“开始”选项卡→将单元格格式改为“长日期”即可（见图5-10）。
图5-10
如图5-11所示的表是公司给员工发住房补贴的表，补贴条件是：如果住在公司没有补贴，不住在公司有1000元补贴。但是，收回来的统计表，总有一些内容是没有办法统计。
这个时候，就得挨个儿跟员工去确认，这些“非主流”的回答后面，真正的含义是什么？
表姐建议大家：“比起事后救火填坑，最好的方法是：事前控制”。在Excel的世界里，像这种事先控制约束，用到的是“数据验证”。顾名思义：当填表人满足了验证条件，才能够往里输入内容，否则就报错。这样发给填表人的时候，填表人只能按照这个规范去做。
回到这个表格空白的时候，也就是新建一张空白工作表，把标题表头按照图5-11填好以后，开始进行“数据验证”的设置。
图5-11
在工作簿中新增一张“参数”工作表，输入部门、是否住公司等参数（见图5-12）。
图5-12
（1）选中“部门”A列→选择“数据”选项卡→“数据验证”（见图5-13）。
图5-13
（2）在弹出的“数据验证”对话框→“允许”→选择“序列”（见图5-14）。
图5-14
（3）设置“来源”→单击折叠窗口按钮→选择参数表里的数据来源（见图5-15）。
图5-15
（4）查看设置效果。设置完成后，单击“部门”的下拉框，其中可选的内容为图5-15中设置的序列来源（见图5-16）；如果要手工输入非允许范围内的值，如“市场营销部”，则会弹出错误提示“此值与此单元格定义的数据验证限制不匹配”（见图5-17）。
图5-16
图5-17
（5）增加序列内容。如果部门内容发生新增或修改，可以在序列允许的范围内，即参数表A1:A8范围内，直接新增或修改（见图5-18）。
图5-18
同理，操作“是否住公司”F列的数据验证效果。
表姐Tips
如果要制作动态数据验证，或二级动态联动效果的数据验证，请查看本书“福利篇”的“巧用超级表制作动态数据验证”。
（1）选中“员工姓名”B列→选择“数据”选项卡→“数据验证”（见图5-19）。
图5-19
（2）在弹出的“数据验证”对话框→“允许”→选择“文本长度”（见图5-20）。
图5-20
（3）设置数据。“介于”指定范围内，如人名的指定长度范围是2～5（见图5-21）。
读书笔记
______________________________________________________
______________________________________________________
图5-21
（1）把手机号、身份证号码列单元格设置成文本格式。选择“开始”选项卡→设置单元格格式→设置为“文本”（见图5-22）。
图5-22
（2）设置手机号数据验证。选中“手机号”C列→选择“数据”选项卡→“数据验证”（见图5-23）→在弹出的“数据验证”对话框→“允许”→选择“文本长度”→“数据”→选择“等于”→“长度”设为11，单击“确定”完成（见图5-24）。
图5-23
图5-24
（3）设置身份证号码数据验证。选中“身份证号码”D列→选择“数据”选项卡→“数据验证”（见图5-25）；在弹出的“数据验证”对话框→“允许”→选择“文本长度”→“数据”→选择“等于”→“长度”设为18（见图5-26）→继续单击“输入信息”页签→在“输入信息”栏（见图5-27）→输入：“请您输入18位身份证号码”→单击“确定”完成。这样当填表人选中此列单元格时，就会自动出现“温馨提示”（见图5-28），避免填入不符合要求的数据。
图5-25
图5-26
图5-27
图5-28
（1）把入职时间列单元格设置为日期格式。选择“开始”选项卡→设置单元格格式→设置为“短日期”（见图5-29）。
图5-29
（2）设置入职时间数据验证。选中“入职时间”E列→选择“数据”选项卡→“数据验证”（见图5-30）→在弹出的“数据验证”对话框中“允许”选择“日期”→“数据”选择“大于或等于”→“开始日期”设置为公司创立的时间，如2010-1-1（注意规范的日期格式写法），单击“确定”完成（见图5-31）。
现在，我们已经通过“数据验证”完成了表格的“事前控制”。这样再交给别人填写时，采集回来的信息就会比较规范了。
图5-30
图5-31
表姐说
本章我们学习到的是数据规范性录入的技巧，例如“早用文本大法”录入身份证号码、银行卡号这样长串的数据，以及怎么去录入规范的“真日期”。
在工作当中，表姐推荐大家通过“数据验证”的方法，给单元格设置一套填写规范，保证我们数据采集的准确。这样往后才可以做数据分析，挖掘数据价值。
（1）在设置了数据验证的单元格，只要单击右下角的小三角打开下拉框，就可以通过“点选”的方式，往单元格内录入内容了。当然，还可以通过快捷键，提高录入效率。
①选中单元格，按ALT+↓键，快速打开“数据验证”列表。
②通过↑、↓、←、→方向键的小箭头，选择列表内容。
③按Enter键快速选中列表内容，完成数据录入。
（2）此外，在录入数据信息表的时候，一般都是标题在上，明细行的数据记录在下，一条记录是横向逐步录入完全的。但是，我们常用的Enter键的方向是“向下”的。可以将其修改为“从左往右”，进一步提高数据录入的效率。
修改Enter键方向：选择“文件”选项卡→“选项”→“高级”→将“按Enter键后移动所选内容”→“方向”→改为“向右”→单击“确定”完成即可（见图5-32）。
图5-32
（3）在录入数据的时候，先把要录入数据的单元格区域选中，如图5-33所示，我们先选中A2:G13单元格区域。然后顺序录入，录完后，按Enter键自动跳转到右侧单元格。当选中区域的第1行按Enter键以后，将直接跳转到选中区域的第2行第1个单元格，这样就大大地提高了录入数据的效率。
图5-33
读书笔记
______________________________________________________
______________________________________________________