1.3 数据规范

1.3.1 利用数据验证（数据有效性）规范数据输入

在Excel单元格中输入数据时，经常会输入不规范或无效的数据，给数据的统计工作带来很大的麻烦。

数据验证能够建立特定的规则，限制输入单元格的内容，从而规范数据输入，提高数据统计与分析效率。

在Excel 2010及以前的版本中，数据验证称为“数据有效性”。

1．规范性别输入

利用数据验证输入性别，不仅规范而且快速。

单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，在“来源”中选择“男，女”，如图1-81所示。

设置了数据验证后，输入性别时只要选择“男”或“女”就可以了。

特别注意：

（1）在序列来源中，“男”“女”两个字之间一定是“英文状态”下的逗号，即半角逗号。

（2）只要对一个单元格设置了数据验证，就可通过鼠标拖动单元格右下角填充柄，将数据验证的设置填充到其他单元格。

2．限定输入内容

在很多时候，要求只在某些特定值内选择输入单元格的内容。例如，如图1-82所示的“评定等级”只有优秀、良好、合格、不合格，所有姓名对应的等级必须出自其中之一，而再无其他值，这时就可以利用数据验证来规范等级输入。

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，在“来源”中选择4个等级所在的L2：L5区域，如图1-82所示。

设置了数据验证后，输入等级时只要选择其中之一就可以了。

3．限定数值范围

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“整数”，“数据”设为“介于”，即可限定输入数值范围。

（1）静态限制输入数值范围。直接输入“最大值”“最小值”，如图1-83所示。

（2）动态限制输入数值范围。在设置了数据验证以后，可以通过修改“最小值”“最大值”单元格的数值，动态调整数据的允许输入数值范围，如图1-84所示。

4．限定文本长度

在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“文本长度”，“数据”设为“等于”，“长度”设为“11”，如图1-85所示。

5．限制输入重复值

单击“数据”→“数据验证”，在弹出的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“自定义”，在“公式”中输入“=COUNTIF（H：H，H1）=1”，如图1-86所示，单击“确定”按钮可以禁止输入重复值。

其中，公式的含义：H列中H1单元格的内容只出现1次。如果H列中H1单元格的内容出现次数超过1，则被禁止输入。

6．限定身份证号码

单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“自定义”，在“公式”中输入“=AND（LEN（11）=18，COUNTIF（D：D，D 1&"*"）=1）”，如图1-87所示。

【公式解析】

•LEN（D1）=18：表示D1单元格数据的长度为18位。

• COUNTIF（D：D，D1&"*"）=1：表示在D列中，D1单元格数据只出现1次，也就是不能重复出现。

•AND（LEN（D1）=18，COUNTIF（D：D，D1&"*"）=1）：表示要满足D1单元格数据的长度为18位且D1单元格数据不能重复出现这两个条件。

7．限制输入空格

单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，“允许”设为“自定义”，在“公式”中输入“=ISERR（FIND（""，ASC（D1）））”，如图1-88所示。

【公式解析】

•ASC（D1）：表示将D1单元格的全角空格转换为半角空格。

• FIND（""，ASC（D1））：表示在D1单元格中查找空格，如果包含空格，则返回空格在D1单元格的位置，即一个数字；如果不包含空格，则返回错误值＃VALUE。

•ISERR（FIND（""，ASC（D1）））：表示通过ISERR函数，将不包含空格时返回的错误值转换为逻辑值TRUE，表示允许输入；将包含空格时返回的数值转换为逻辑值FALSE，表示禁止输入。

1.3.2 设置只能输入规范的日期

【问题】

经常要在Excel单元格中输入日期型数据。日期型数据的格式有很多种，规范的如“2017年11月27日”“2017/11/27”“2017-11-27”等；不规范的如“2017、11、27”“2017，11，27”“2017*11*27”等。

如果在同一个数据表中日期型数据的格式不合规范，势必会影响后期的数据处理与计算。

【实现方法】

（1）设置日期格式。选中要填充日期的单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，在打开的“设置单元格格式”对话框中，将“类型”设置为要求输入的格式，这里选择常用的“*2012/3/14”格式，如图1-89所示。

（2）利用数据验证规范日期区间。选中要填充日期的单元格区域，在“数据验证”对话框中选择“设置”选项卡，将“允许”设为“日期”，“数据”设为“介于”，“开始日期”设为“2000/1/1”，“结束日期”设为“=today（）”，如图1-90所示。

选中“出错警告”选项卡，将“样式”设为“停止”，“标题”设为“请重新输入：”，“错误信息”设为“请输入格式如2012/03/14的日期，并且起始日期介于2000/1/1与今天之间。”，如图1-91所示。

通过以上步骤的设置，单元格中就只允许输入规范格式、特定区域的日期。

1.3.3 巧用数据验证规范时间格式

【问题】

如图1-92所示的单位客户接待登记表，其中在“到达时间”一列，由于是被几个人录入的，所以“到达时间”的格式是五花八门的，这影响了表格的美观及后期的数据计算分析。

其实，这种情况是可以利用数据验证来有效预防的。

【实现方法】

（1）借助一个辅助单元格，这里是E2。在E2中，输入公式“=NOW（）”，显示当前时间。

（2）单击“数据”→“数据验证”，在打开的“数据验证”对话框中选择“设置”选项卡，将“允许”设为“序列”，“来源”设为“=$E$2”，如图1-93所示。在“输入信息”选项卡中，在“输入信息”中输入“请输入当前时间”，如图1-94所示。

（3）设置时间格式，如图1-95所示。最后，单击“确定”按钮即可。

1.3.4 数据输入不规范，部分数据带数量单位，此时怎么计算平均值

【问题】

某公司进行员工考核，并将考核分数录入Excel表格中，但这些考核分数被录入得不规范，如图1-96所示，部分考核分数带有数量单位“分”。现在要计算员工平均的考核分数。

【实现方法】

（1）统一去单位。

数量单位“分”属于文本，不能参与计算。所以，在写公式时，首先要把单位去除。

去除数量单位“分”文本要用SUBSTITUTE函数，即{=SUBSTITUTE（B2：B10，"分"，）}。因为要进行的是数组计算，所以按Ctrl+Shift+Enter组合键执行计算，如图1-97所示。

（2）计算平均值。在B11单元格中输入公式“{=AVERAGE（--SUBSTITUTE（B2：B10，"分"，））}”，按Ctrl+Shift+Enter组合键执行计算，如图1-98所示。

其中，公式内“--”为“减负运算”，SUBSTITUTE（B2：B10，"分"，）的结果是一串文本，前面加一个“-”，表示通过取负数将文本转换成数值，再加一个“-”，即负负得正。

“减负运算”常用于将文本转换为数值，如图1-99所示。

（3）规范数据，使其保留两位小数。将B11单元格中输入的公式完善为“{=ROUND（AVERAGE（--SUBSTITUTE（B2：B10，"分"，）），2）}”，使最终数据保留两位小数，如图1-100所示。

1.3.5 一键添加“能计算”的数量单位

【问题】

在很多正规的Excel表格中，数量单位是不可缺少的。但在很多时候，添加数量单位会妨碍统计计算的，其原因是数量单位是文本，数字和文本混合在一个单元格中，是不可能进行加、减、乘、除等统计计算的。

通过自定义单元格格式，既可以添加数量单位，又不妨碍统计计算。

【实现方法】

选中数据单元格区域，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框。在“分类”中选择“自定义”，直接在“类型”的“G/通用格式”后输入数量单位，就可以了，如图1-101和图1-102所示。

这是一种非常快捷的规范数据的方式。

1.3.6 使用多级联动菜单规范数据输入

【问题】

人事部每次下发Excel表格给员工去填写。但在交上来的Excel表格中，数据都被填写得不规范，这给统计工作带来很多麻烦。

使用多级联动菜单，可以有效规范数据输入。

联动菜单的使用如图1-103和图1-104所示，对单元格中输入的内容根据上一层菜单做了限制。

【实现方法】

（1）分级数据整理。如图1-105所示，红色（深颜色）部分的省份数据是一级菜单，黄色（浅颜色）部分的地市数据是二级菜单，无填充部分的数据是三级区县数据。

（2）自定义名称。选中分级后的数据单元格区域，单击“公式”→“定义名称”→“根据所选内容创建”，在弹出的“根据所选内容创建名称”对话框中，勾选“首行”项，单击“确定”按钮，如图1-106所示。

在打开的“名称管理器”对话框中，可以看到已经建立的名称，如图1-107所示。

（3）建立各级菜单。

一级菜单：选中要添加省份的单元格区域，单击“数据”→“数据验证”，在打开的“数据验证”对话框的“设置”选项卡中，将“允许”设为“序列”，在“来源”中输入公式“=省份”，“省份”是上一步建立的名称之一，如图1-108所示。

二级菜单：选中要添加地市的单元格区域，单击“数据”→“数据验证”，在打开的“数据验证”对话框的“设置”选项卡中，将“允许”设为“序列”，在“来源”中输入公式“=INDIRECT（$A2）”，如图1-109所示。

三级菜单：选中要添加区县的单元格区域，单击“数据”→“数据验证”，在打开的“数据验证”对话框的“设置”选项卡中，将“允许”设为“序列”，在“来源”中输入公式“=INDIRECT（$B2）”，单击“确定”按钮，如图1-110所示。

1.3.7 处理不能计算的“数值”

【问题】

如图1-111所示，在A2：A11单元格区域内，既有数值型数字，又有文本型数字。文本型数字是不能直接参与计算的，所以直接用求和公式是计算不出正确结果的。

【实现方法】

1）选择性粘贴

复制A2：A11单元格区域。将光标定位在B2单元格，右击，在弹出的快捷菜单中选择“选择性粘贴”命令，如图1-112所示。在打开的对话框中，在“运算”中选择“加”项，如图1-113所示。

通过上述步骤，将A2：A11单元格区域中的文本型数字转换成了数值型数字，即可进行正常计算。

2）数据分列

选中A 2：A 11单元格区域，单击“数据”菜单“数据工具”功能区中的“分列”按钮，在打开的“文本分列向导”对话框的“目标区域”中输入公式“=$C$2”，单击“完成”按钮，则将A2：A11单元格区域中的文本型数字转换成了数值型数字，如图1-114所示。

3）VALUE函数

在D2单元格中输入公式“=VALUE（A2）”，可将A2单元格数字转换成数值型数字，再将公式向下填充，即可将A2：A11单元格区域中文本型数字转换为数值型数字，如图1-115所示。

4）SUMPRODUCT函数

在E12单元格中输入公式“=SUMPRODUCT（--E2：E11）”，即可完成A2：A11单元格区域求和运算，如图1-116所示。

1.3.8 规范全角、半角数据

【问题】

如图1-117所示，“详址”一栏中的数字既有全角的又有半角的，如果数据少，通过手工修改即可实现数据全角或半角的统一，但如果数据量大，通过手工修改实现数据全角或半角的统一是不现实的。

【实现方法】

（1）在E3单元格中输入公式“=ASC（D3）”，按Enter键执行计算，然后将公式向下填充。ASC函数的作用是将全角（双字节）字符转换成半角（单字节）字符，如图1-118所示。

（2）在F3单元格中输入公式“=WIDECHAR（D3）”，按Enter键执行计算，然后将公式向下填充。WIDECHAR函数的作用是将半角（单字节）字符转换成全角（双字节）字符，如图1-119所示。

1.3.9 数字与文本分离的方法

【问题】

如图1-120所示，为规范数据，如何将A列中的姓名和工号分开到B列和C列呢？

【实现方法】

1）函数法

（1）先将文本分离。在C2单元格中输入公式“=RIGHT（A2，LENB（A2）-LEN（A2））”，按Enter键执行计算，再将公式向下填充，即可提取所有员工姓名，如图1-121所示。

【公式解析】

• LENB（A2）和LEN（A2）：都用于计算A2单元格的字符数，不同的是，LENB函数是将每个汉字的字符数按照2进行计算的，而LEN函数是将每个汉字的字符数按照1计算的，所以，两者的差值是汉字的个数。

•=RIGHT（A2，LENB（A2）-LEN（A2））：是指从A2单元格的字符右侧开始按照汉字个数取出汉字。

（2）再将数字分离。在B2单元格中输入公式“=LEFT（A2，LENB（A2）-LENB（C2））”，向下填充，即可提取所有员工的工号，如图1-122所示。

【公式解析】

•LENB（A2）-LENB（C2）：是指用A2单元格中的字符数减去C2单元格中的字符数，即数字的个数。

•=LEFT（A2，LENB（A2）-LENB（C2））：是指从A2单元格数据的最左侧开始按数字个数取出所有数字。

2）分列法

选中要分列的数字与文本单元格区域，单击“数据”→“分列”，在打开的“文本分列向导-第1步”对话框中选择“固定宽度”命令，然后单击“下一步”按钮，如图1-123所示。

在“文本分列向导-第2步”对话框中的“数据预览”区，在标尺上对准数字与文字分界处单击，会出现一条分隔线，如图1-124所示，单击“下一步“按钮。

在“文本分列向导-第3步”对话框中，选择“目标区域”为B2，即“=$B$2”，分离后的数字和文本以B2单元格为起始位置向后填充，如图1-125所示。分列结果如图1-126所示。

但这种分列方式，仅限于要分离的两个部分中第一部分位数一致的情况，如本示例中，工号的位数是一致的。

3）快速填充法

快速填充是Excel 2016特有的填充方式，不用函数就可以实现数字与文本的分离，且不受数字与文本个数的限制。

将第1位员工的工号输入B2单元格中，按住鼠标左键拖动填充柄往下填充，单击“自动填充选项”，→“快速填充”，如图1-127所示，即可完成工号提取。姓名的提取也可用同样方法完成。

快速填充也可以使用Ctrl+E组合键，只要录入第一个单元格，直接按Ctrl+E组合键，就实现了向下所有单元格数据的填充。

1.3.10 LOOKUP+FIND函数组合规范标准名称

【问题】

作为管理或者统计工作者，从各个部门收集上来的数据往往填写得非常不规范，如图1-128所示。

A列中同样的设备，填写的名称不一样，这将给后期的数据统计与分析带来麻烦。因此要把这些不规范的设备名称改写成标准名称。

【实现方法】

（1）建立关键字与标准名称的对应表。首先对不规则的商品名称进行分析，提取出关键字，再建立关键字与标准名称之间的对应关系表，如图1-129所示。

（2）函数实现。在B2单元格中输入公式“=LOOKUP（1，0/FIND（$D$2：$D$7，A2），$E$2：$E$7）”，按Enter键执行计算，再将公式向下填充，就可以写出所有的标准名称，如图1-130所示（这种用来填写标准名称的方法，还可用在给物品分类）。

【公式解析】

总公式为“=LOOKUP（1，0/FIND（$D$2：$D$7，A2），$E$2：$E$7）”

•FIND（$D$2：$D$7，A2）：FIND函数返回一个字符串在另一个字符串中的起始位置，如果找不到要查找字符或字符串，返回错误值＃VALUE！。

本示例中的含义是：依次查找$D$2：$D$7区域中的关键字在A2字符串中的起始位置，如果查找到了，就返回关键字在A2字符串中的起始位置，如果查找不到，就返回错误值＃VALUE！。

所以，本部分函数，在本示例中的返回值是由起始位置与错误值＃VALUE！组成的数组（为描述方便，称为数组1）：{＃VALUE；4；＃VALUE；＃VALUE；＃VALUE；＃VALUE}。

• 0/FIND（$D$2：$D$7，A2）：用0除以数组1，得到由0和错误值＃VALUE！组成的新数组（数组2），即{＃VALUE；0；＃VALUE；＃VALUE；＃VALUE；＃VALUE}。

•LOOKUP（1，0/FIND（$D$2：$D$7，A2），$E$2：$E$7）：LOOKUP函数用1作为查找值，由于在数组2中，所有的数字都小于1，所以按照小于1的最大值0进行匹配，匹配出第3个参数$E$2：$E$7数组与数组2中0对应位置的值，即E3单元格的数据。

1.3.11 给同一单元格的姓名和电话号码中间加分隔符号

【问题】

样表如图1-131所示，名字和电话号码都写在一个单元格中，这样太不规范了，须要在中间加个中文冒号“：”。

【实现方法】

在B2单元格中输入公式“=REPLACEB（A2，SEARCHB（"？"，A2），0，"："），按Enter键执行计算，然后将公式向下填充，即完成对所有单元格名字与电话号码之间“：”的添加，如图1-132所示。

【公式解析】

•SEARCHB（"？"，A2）：自左向右，查找并返回第一个数值字符在A2字符串中的位置。

• REPLACEB（A2，SEARCHB（"？"，A2），0，"："）：在A2字符串的第一个数值字符位置处开始替换，替换掉0个字符，也就是只添加一个“：”。