數據有效性自定義公式編寫

數據有效性是一個非常有用的工具，能夠限制數據輸入的正确性。

數據有效性有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就禁止輸入。

這裡的技巧就是，先在單元格裡設計邏輯表達式，測試無誤，再複制到數據有效性自定義公式中。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部