原标题:Excel数据有效性,最全面的教程在这里(2)
小伙伴们好啊,昨天咱们认识了Excel数据有效性
除了直接引用单元格区域作为序列来源,还可以设置动态的数据区域引用,让有效性中的下拉列表能够随着数据源的增减动态调整。
首先说一下跨工作表引用有效性序列来源的方法:
在员工花名册工作表中自定义名称:
花名册
=OFFSET(员工花名册!$A$2,,,COUNTA(员工花名册!$A:$A)-1)
然后在员工出勤表工作表中设置数据有效性。有效性条件设置为允许序列,来源输入框中写上自定义的名称。
咱们对自定义名称中公式的意思简单说明一下。
公式中的“COUNTA(员工花名册!$A:$A)”部分,用来计算员工花名册!工作表A列非空单元格的个数。
OFFSET函数的作用是以指定的引用为参照系,通过给定偏移量返回新的引用。
整个公式的意思是:
以员工花名册!$A$2单元格为基点,向下偏移的行数为0行,向右偏移的列数为0列,新引用的行数为A列非空单元格个数减去1。因为不需要引用员工花名册A1单元格的列标题,所以这里减去1。
设置完数据有效性,下拉列表的最后一个姓名是“陈秀雯”。
如果在员工花名册工作表中删除部分姓名,员工考勤表工作表的数据有效性下拉列表内容就会自动更新。
如果使用同一工作表内的数据作为有效性序列来源,也可以使用插入列表的方法来实现动态更新。
单击数据源任意单元格,依次单击【插入】,【表格】,弹出【创建表】对话框。Excel会自动判断数据区域,因为G1单元格是列标题,所以这里保留“表包含标题”的勾选,单击【确定】。
选中A2:A9单元格区域,依次单击【数据】,【数据有效性】,在弹出的【数据有效性】对话框中,有效性条件选择序列,来源输入框中写上:
=$G$2:$G$9
设置完数据有效性后,如果在G列的数据源中增加数据,A列的有效性下拉列表就会自动更新。
注意,这种方法仅限于数据源在当前工作表内,如果需要跨工作表引用,则只能使用自定义名称的方法。
对于已经输入的内容,再设置数据有效性后,如何标识不符合要求的内容呢?
接下来咱们就一起来看一下,下图中,员工出勤表的A列已经用数据有效性的下拉列表输入了部分内容。
这时候,咱们将数据源员工花名册最后几个单元格的内容清除:
然后在员工出勤表中单击【数据】,再单击【数据有效性】按钮下面的小三角,在下拉列表中选择【圈释无效数据】,Excel会判断已经输入的内容是否符合有效性条件,对不符合条件的,会自动添加一个红色的标识。
如果需要清除这些标识,只要单击【数据】,再单击【数据有效性】按钮下面的小三角,在下拉列表中选择【清除无效数据标识圈】即可。
如果需要清除数据有效性,首先选中数据区域,依次单击【数据】,【数据有效性】,在【数据有效性】对话框中单击【全部清除】,单击【确定】。
好了,今天的内容就是这些吧,明天继续学习二级下拉菜单的应用,祝各位一天好心情!
责任编辑: