Excel的数据有效性功能的几个不一定为人所知技巧

Excel的数据有效性（2013版本叫数据验证）功能可以防止数据录入错误，节省录入时间。关于数据有效性的有些技巧不一定为人所知。

一、要引用的序列不在同一行或同一列怎么办？

解决思路：定义一列或一行数据名称→数据有效性定义→修改定义的名称引用位置为多行多列。如果要引用的序列是多行多列，如图1，序列有3行3列，进行数据有效性定义鼠标选中后会提示如图2的错误：

图 1

图 2

选中需要引用的B列，输入定义的名称：业务，再对A列做数据有效性定义，

图 3

图 4

这时在A列做了数据有效性定义的单元格下拉框能选择的只有B列内容。点击“公式”→“名称管理器”，修改引用位置为B列和C列的数据区域B1:C3。

图 5

再来看看A列，下拉框中就有了B列和C列的内容。

二、给单元格添加注释，不用批注

在Excel中需要添加注释的时候，你可能毫不犹豫选择插入批注。但批注插入多了，有点眼花缭乱的感觉，而且不能批量插入。想给单元格加个注释？选取单元格，点击菜单“数据”→“数据有效性”→“输入信息”→“输入要注释的文字”。

图6

添加后的效果图：

图7

如果有一列数据，需要提醒输入者不能输入大于100的数字，每个单元格插入批注是不现实的，用添加提示的方法则可以完美解决。

图8

添加后的效果图：

图9

三、记录数据录入时间

如果需要记录数据录入时间，借助函数now和数据有效性可以实现，比如，要记录每张申请单扫描时间，在D2单元格输入公式=now()，并将单元格格式自定义为yyyy-m-d h:mm:ss，在B列需要数据有效性定义的单元格区域点击“数据”→“数据有效性”，数据来源处引用D2单元格内容。这样点击下拉框就可以记录申请单扫描时间。

图10

四、限制单元格数据类型

如果需要限制单元格只能输入文本，不能输入数字，借助函数istext,该函数功能是检测单元格内容是否为文本，如果是文本则返回true,否则返回false。如果要限制单元格只能输入数字，不能输入文本，借助函数isnumber,该函数功能是检测单元格内容是否为数值。

图11

五、防止重复录入相同的内容

比如，要在A列输入数据，为了防止数据重复录入，可以用数据有效性定义，如图12：

图 12

这样，如果数据重复录入了就会提示图13错误：

图 13