数据有效性,用到最多的是制作下拉菜单,其次是限制单元格输入的数据大小、类型等。你以为掌握这些就是它的全部吗?NO!!今天本文通过5个示例让你认识一个全新的excel数据有效性。
1、借贷方只能一列填数据。
【例1】如下图所示的AB两列中,要求只能在A或B列中的一列输入数据,如果一列中已输入,另一列再输入会弹出错误提示,中止输入。
操作步骤:
选取AB列的区域,数据菜单 - 数据有效性,在有效性窗口中,允许:自定义;公式中输入=COUNTA($A2:$B2)=1
公式说明:counta函数可以统一个区域有多少个非空单元格,本例中设置的条件是Ab两列同一行中统计结果只能是一个数字。
2、判断车牌输入是否正确
【例2】如下图所示,要求A列的车牌号必须输入以汉字开头,且总长度为7位。输入错误就禁止输入。
数据有效性公式:
=AND(LENB(LEFT(B2))=2,LEN(B2)=7)
注:汉字占用2个字节,数字和字母占用1个。
3、每行输入完成才能输入下一行
【例3】在excel表格的A:D输入时,只有上一行的四列都输入数据,在下一行才能输入,否则就无法输入并提示错误信息,如下图所示。
操作步骤:
选取A2:D100,数据选项卡 - 有效性 - 允许 - 自定义,在来源框中输入以下公式:
=COUNTA($A1:$D1)=4
公式说明:counta函数可以统计非空单元格个数。$A1:$D1添加$是把范围固定在A:D列。
4、库存表中有才能出库
【例4】如下图所示,上表为库存表,要求在下表出库列中设置限制,如果为存表中数量不足,禁止输入。
当出库大于库存时
设置方法
数据有效性公式:
=E3<=VLOOKUP(D3,A:B,2,0)
5、输入的内容必须包含指定字符
【例5】如下图所示,要求A列输入的内容必须包括字符A
设置方法:
公式:=COUNTIF(A2,"*A*")
补充:在数据有效性中使有公式,可以完成复杂的判断和输入限制。对于规范表格的数据输入非常有帮助。
更多相关阅读