欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  Office

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

程序员文章站 2022-03-14 15:15:56
Excel的数据有效性(2013版本叫数据验证)功能可以防止数据录入错误,节省录入时间。关于数据有效性的有些技巧不一定为人所知。 一、要引用的序列不在同一行或同一...

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

添加后的效果图:

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

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

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

添加后的效果图:

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

三、记录数据录入时间

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

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

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

四、限制单元格数据类型

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

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

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

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

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

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

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