相册

如下图所示的工作表中A列用于存放职工工号。由于姓名允许重复，但工号不允许重复，因此要求在A输入数据时，Excel自动检查是否重复，如果重复则弹出提示信息，要求用户重新输入。

解题步骤

限制某区域不能输入重复值宜用数据验证，具体步骤如下。

1.选择A2：A10区域。

2.单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“自定义”。

4.在公式框中输入以下公式，操作界面如图3.14所示。

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“当前值在A2：A10区域已经存在，请重新录入”，设置界面如图3.15所示。

图3.14 使用公式限制A2∶A10只能输入唯一值

图3.15 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面，在A2：A3单元格输入工号34和35，然后在A4单元格再次输入工号34，当单击Enter键后，Excel会弹出图3.16所示的提示框，必须单击“重试”按钮，然后输入其他不重复的值，否则无法输入成功。

图3.16 输入重复值时的错误提示

知识扩展

1.COUNTIF函数属于Excel的统计函数，用于计算区域中符合某个条件的数据个数。例如，等于100的单元格数量、大于999的单元格数量、包含“合格”二字的单元格数量，或者计算不等于某个单元格的单元格数量。本例采用COUNTIF函数计算A2：A10区域中值等于当前单元格的值的单元格数量。

2.COUNTIF函数的语法如下：

COUNTIF(range,criteria)

它包含两个必选参数，第一参数代表要对其进行计算的区域，第二参数代表要计算的条件。假设要计算A1：A10区域中大于500的单元格数量，那么宜采用以下公式：

=COUNTIF(A1:A10,＂＞500＂)

假设要计算A1：A10区域中等于500的单元格数量，那么宜采用以下公式：

=COUNTIF(A1:A10,500)

当第二参数是数字时可以不用等号也不加双引号。

假设要计算A1：A10区域中包含“螺丝”的单元格数量，那么宜采用以下公式：

=COUNTIF（A1：A10，＂*螺丝*＂）

第二参数“*螺丝*”表示包含“螺丝”，前后的“*”代表任意长度的任意字符，即以任意字符开始、任意字符结尾、中间包含“螺丝”二字的字符串。公式的运算效果如图3.17所示。

图3.17 计算A1∶A10区域中包含“螺丝”的单元格数量

3.本例中公式“=COUNTIF（$A$2：$A$10，A2）=1”代表A2：A10区域中任意单元格的值只允许出现一次，如果重复则会弹出提示对话框警告用户。

4.数据验证仅对设置后手工输入数据有效，如果先输入字符后设置数据验证，那么 Excel不会提示不符合规则的数据。

5.数据验证对公式也没有限制，限制A1单元格只能输入1～10之间的数值，当在A1单元格手工输入11时会禁止输入，但是A1单元格通过公式引用A2单元格的值，在A2单元格中输入11时，A1单元格也会产生11，而且不产生任何提示。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

工作中会经常收到同事发来的包含不规范日期值的报表，从而导致后期运算出错。例如“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等格式都属于不规范的日期值，无法使用日期函数对它们执行运算。是否可以在前期设计表格模板时禁止用户输入不规范的日期？

解题步骤

假设有图3.6所示的销量报表模板，要求对A2：A9区域加以限制，从而提升日期值的准确性，操作步骤如下。

图3.6 销量报表模板

1.选择A2：A9区域。

2.单击功能区的“数据”→“数据验证”，弹出“数据验证”对话框。

3.将对话框中“允许”下拉列表中的“任何值”修改为“日期”，表示只允许在 A2：A9 区域输入日期。

4.根据实际工作需求对“开始日期”和“结束日期”指定范围。例如，当前工作表只允许输入2015年和2016年的日期，那么就分别输入“2015-1-1”和“2016-12-31”。设置结果如图3.7所示。

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“请按标准的日期格式输入，有效范围在2015-1-1到2016-12-31之间”，设置结果如图3.8所示。

图3.7 指定日期范围

图3.8 设置出错警告

6.单击“数据验证”对话框中的“确定”按钮返回工作表界面，在 A2 单元格输入不规范的日期“2015.2.28”，当单击Enter键后将弹出图3.9所示的错误提示信息。

图3.9 输入格式不规范的日期时产生错误提示

7.单击“重试”按钮，继续输入日期“2014-5-7”，Excel会产生相同的提示信息，表示格式有误、范围有误时都禁止操作。

知识扩展

1.“2015.7.15”、“2015年7月15号”、“07.15.2015”、“20150715”等都不是规范的日期，所有日期函数都无法对它们执行日期运算。

例如，Month函数可以计算一个日期属于几月份，图3.10和图3.11两个图片分别展示了错误格式的日期和正确格式的日期的运算结果。

图3.10 错误的日期执行日期运算时出错

图3.11 正常计算出月份

2.正确的长日期有三种格式（排除英文格式），包含“2015-7-15”、“2015/7/15”和“2015年7月15日”。当在单元格中输入最后一种格式的日期时，Excel会自动将它转换成“2015/7/15”格式的日期。

3.日期其实就是数值，只是它呈现出的形态与数值不同而已，本质上是一样的。例如2015年8月8日等同于数值42224，只是显示为日期样式而已。因此日期必须是数值，不管其外观如何变化。假设按文本形式输入日期则无法参与数值运算，对工作带来负面影响。“2015.8.8”就是文本形式，所有日期函数都无法对该值执行日期运算。

4.为什么42224代表2015年8月8日呢？Excel以1900年1月1日作为初始日期，日期1900年1月1日被当作第一天，那么相对于1900年1月0日之后的第42224天则刚好是2015年8月8日。可以如此验证：在A1单元格录入数值42224，然后依次单击功能区的“开始”→“数字格式”→“长日期”，单元格中的42224马上会显示为2015年8月8日，效果如图3.12所示。

图3.12 通过设置格式改变数值的显示形态

在图3.1中，C2：D11区域需要录入数值（E2：E11、C12：E12是公式计算出来的，不需要输入值），由于担心该区域错误地输入了文本而导致后续运算出错，因此要求限制该区域不能输入文本。如何操作才能仅允许C2：D11区域输入数值呢？

图3.1 产量表

解题步骤

对区域进行数据类型限制一般通过设置数据验证来实现，具体步骤如下。

1.选择C2：D11区域。

2.单击功能区的“数据”→“数据验证”，弹出如图3.2所示的“数据验证”对话框。

3.在“允许”下拉列表框中将“任何值”改为“整数”，表示此区域禁止输入整数以外的值。

4.由于产量不能是负数，同时不会大于2000，因此将“最小值”设置为0，最大值设置为2000。设置界面如图3.3所示。

图3.2 默认状态的“数据验证”对话框

图3.3 为C2∶D11区域指定验证条件

5.打开“数据验证”对话框的“出错警告”选项卡，“样式”保持“停止”不变，将“标题”设置为“友情提示”，将“错误信息”设置为“不能录入文本，只能录入0到2000之间的数值”，设置结果如图3.4所示。

6.在“数据验证”对话框中单击“确定”按钮返回工作表界面，然后在C11单元格输入字符9oo（后面两位不是数值0而是字母o），当按下Enter键后将弹出图3.5所示的提示框。

图3.4 设置出错警告

图3.5 输入文本时自动产生警告

7单击“重试”按钮，然后重新输入数值900，当按下Enter键后Excel不再弹出任何提示，表示输入的数据符合要求。

知识扩展

1.数据验证在Excel 2010及更早的版本中称为数据有效性，它用于控制区域中允许录入的字符类型，可以限制区域中只能输入某个范围的整数、小数、日期值、时间值，也可以限制区域中只能输入某些具体的单词，其中最强大、灵活的是“自定义”，通过公式可以限制任何类型。

2.数据验证可以理解为单元格的一种属性、一种格式，它可以很轻易地被清除。例如，将一个未设置数据验证的单元格复制到已经设置了数据验证的单元格时，由于复制时默认包含单元格的一切格式信息，因此目标单元格的格式会被覆盖掉，导致单元格的数据验证设置丢失，从而不会报警。换言之，数据验证仅对手工输入数据有效，对复制、粘贴操作无效。

3.对于错误输入时产生提示，Excel提供了两套安全机制：其一是数据验证——位于“数据”选项卡；其二是使用条件格式，其菜单位于“开始”选项卡。两者的区别是数据验证有强制性，数据错误时会弹出提示对话框从而中断操作，而条件格式仅通过单元格颜色、边框或字体颜色来提醒用户，用户可以完全忽略这些提示而继续操作。因此通俗地讲，如果只想获得通知信息则采用条件格式；如果需要在接收到提示信息后必须做出响应才能消除该信息，则宜采用数据验证。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。