疑难场景

公司成立于2000年,因此制表时所用到的时间都大于等于2000年、小于等于2015年。同时,由于报表中涉及时间时一律要求升序排列,如果下方单元格的时间小于上方单元格的时间则表示输入有误。此外,禁止日期区域的两个日期之间有空白单元格,即所有日期都需要填写完整,不能跳过一个日期。应该如何设置才能让单元格符合以上3个条件呢?

解题步骤

设置数据验证可以让指定区域只能输入2010年到2015年的升序日期,具体步骤如下。

1.选择需要进行限制的区域A2:A20。

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

3.将“允许”下拉列表中的“任何值”修改为“自定义”,然后在“公式”文本框中输入以下公式:

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

公式表示A2的值必须大于等于2010年1月1日、小于2015年12月31日、大于等于A1单元格的数值(如果A1是文本则当作0计算)、A1单元格不能等于空值。

公式中的A2和A1采用的是相对引用,因此公式在不同单元格中时,公式中的A2和A1将代表不同的单元格。对于A2单元格的数据验证,采用的是公式

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

而A4单元格会将A2变成A4、将A1变成A3,其数据验证公式为

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

简言之,公式中的A2和A1并非代表A2单元格和A1单元格,而是当前单元格与上一个单元格,公式在不同单元格中A2和A1将代表不同的对象。

4.打开“数据验证”对话框的“出错警告”选项卡,“样式”保持“停止”不变,将“标题”设置为“友情提示”,将“错误信息”设置为“1.只能是2010年到2015年之间的日期2.必须是升序的日期3.上方不能有空白单元格”。图3.18和图3.19分别是设置公式与设置出错警告信息。

由于公式较长,在图 3.18 中未完整显示公式,请读者直接使用案例文件,在文件中有完整的公式。

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

图3.18 使用公式限制单元格

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

图3.19 指定警告信息

5.在“数据验证”对话框中单击“确定”按钮返回工作表界面。

6.在A2单元格输入日期2008-9-1,单击Enter键后Excel将弹出如图3.20所示的警告信息,同时禁止用户输入。

7.单击“重试”按钮,重新输入日期“2010-10-5”,由于此日期符合所有要求,因此Excel允许正常输入。

8.在A3单元格录入日期“2010-2-8”,由于当前日期不符合“大于等于上方单元格的日期”这个条件,因此Excel会弹出如图3.21所示的提示信息,同时禁止用户输入。

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

图3.20 日期小于2010年

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

图3.21 非升序日期

9.单击“重试”按钮,然后重新输入日期“2012-12-8”,此日期可以正常输入。

10.选择 A5 单元格,然后输入日期“2015-8-9”,尽管此日期在指定的范围之内,且大于前面的所有日期,但是不符合“上方单元格不能空白”的条件,因此Excel会禁止输入,同时弹出如图3.22所示的警告信息对话框。

Excel 是否能只允许输入2000年到2015年之间的升序日期-Excel22

图3.22 上方单元格空白

通过测试可以确定本例的数据验证设置满足所有需求。

知识扩展

1.DATE(2010,1,1)代表2010年1月1日,不能使用“"2010-1-1"”或“"2010年1月1日"”来代表2010年1月1日,因为它们都是文本,而DATE(2010,1,1)才是真正的日期值。

2.DATE(2015,12,31)代表2015年的最后一天,同理不能使用“"2015-12-31"”或“"2015年12月31日"”。

3.N函数可以将文本转换成0,数值保持不变,例如:

=N("Excel")——运算结果为0

=N(2015)——运算结果为2015

在本例中,由于上方的一个单元格有可能是日期值也有可能是文本,使用N函数将A1转换成数值后再与A2进行比较,否则A2单元格不管输入什么日期都会禁止输入,因为任意日期值都小于文本值。

4.Excel对数据的大小排序是:

逻辑值>文本>字母>数值

其中逻辑值包含 True 和 False,文本包含汉字、标点符号、片假名等,字母即 A~Z 和 a~z各26个英文字母。数值包含正数、负数、0,日期也是数值,仅仅显示形态不同于数值,但其本质仍是数值。

根据以上排序可以得知,任意文本都是大于数值和日期的,因此当 A1 是文本、A2 是日期时,A1总是大于A2。

5.And函数用于校验多个条件是否全部成立,本例中使用了3个条件,即And函数的3个参数。And函数支持1~255个参数。

6.设置数据验证时,不管当前选中了多大的区域,公式中只写左上角一个单元格,Excel会自动将这个公式应用到所有单元格中,而且公式会随单元格地址变化而变化,自动适应,不过前提是公式中的单元格采用相对引用。

7.通过数据验证对单元格的值进行限制时,如果是简单的限制,使用“允许”下拉列表的各种现成设置足以应付,像本例这种复杂的多条件限制则必须通过公式来限制。

8.多条件限制时只能通过公式一次性指定多个条件,不能分多次设置数据验证,像条件格式那样多个条件并存,因此本例中不同的错误类型只能共用一条警告信息。

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