数据有效性自定义公式编写
数据有效性是一个非常有用的工具，能够限制数据输入的正确性。
数据有效性有7个选项：
1、 任何值：这个相当于没有设置任何的数据有效性，对数据输入没有限制。
2、 整数：限制输入整数，会要求输入指定范围的整数。
3、 小数：限制输入指定范围的小数。
4、 序列：输入值再指定序列中选择。
5、 日期：限定输入日期数据。
6、 时间：限定输入时间数据。
7、 文本长度：限定输入指定长度的文本。
8、 自定义：可以自定义公式，限定输入数据的要求。
我们通过一个例子来学习如何编写数据有效性的自定义公式：我们准备收集IP地址，格式是”0.0.0.0″-“255.255.255.255″，掩码是0-32，那么就有可能出现”0.0.0.0/32″这样的格式。
我们先分析一下这个问题：
我们从简单的写起：先来写IP的判断
我们先要在单元格里编写公式，测试通过了，直接复制公式再到数据有效性中就可以了。
这个公式是一个经典的套路，空格替换小数点分成4段，AND函数刚好也支持数组公式，当然要CTRL SHIFT ENTER三键结束。
=AND(IFERROR(–MID(SUBSTITUTE(D2,”.”,REPT(” “,99)),ROW($1:$4)*99-98,99)<=255,FALSE))
接下来增加条件，判断”/”前后的数据是否同时符合要求：
这个公式分成三段来看OR(AND1,AND2)
1、 OR表示两个AND满足任何一个条件，就可以输入。
2、 AND1就是上面的公式一点都没变，就是标准的IP地址判断
3、 AND2要分成两段来看：一个前三位数字的判断 一个AND判断第四位与掩码是否符合条件。
=OR(AND(IFERROR(–MID(SUBSTITUTE(A2,”.”,REPT(” “,99)),ROW($1:$4)*99-98,99)<=255,FALSE)),AND(IFERROR(–MID(SUBSTITUTE(A2,”.”,REPT(” “,99)),ROW($1:$3)*99-98,99)<=255,FALSE),AND(IFERROR(–LEFT(TRIM(MID(SUBSTITUTE(A2,”.”,REPT(” “,99)),4*99-98,99)),FIND(“/”,TRIM(MID(SUBSTITUTE(A2,”.”,REPT(” “,99)),4*99-98,99)))-1)<=255,FALSE),IFERROR(–RIGHT(A2,LEN(A2)-FIND(“/”,A2))<=32,FALSE))))
我们总结一下，其实数据有效性自定义公式，就是一个逻辑表达式，返回的结果是TRUE或FALSE，如果返回TRUE就允许输入，如果返回的是FALSE就禁止输入。
这里的技巧就是，先在单元格里设计逻辑表达式，测试无误，再复制到数据有效性自定义公式中。