2016年3月6日 星期日

Excel-在儲存格中輸入資料時設定切換是否使用資料驗證

有網友問到:通常我們會在 Excel 中,使用「資料驗證」工具來決定是否要在儲存格中輸入時接受(拒絶)某些資料。但是某些狀況下又想放行不符合條件的狀況,所以如果能有一個切換機制,就不用一直修改資料驗證的設定,該如何處理呢?
以下圖為例,假設當我們在儲存格A2:A10中設定輸入的資料必須是數值,因此當輸入文字(abc)時會出現提示不符合資料驗證準則的訊息。我們要以這個例子,來試試製作一個切換機制。
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證
1. 在儲存格D2中設定資料驗證,使其可以選取「V,X」。
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證
其中資料驗證設定如下:
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證
2. 在儲存格D2中輸入公式:=D1="V"
如此在儲存格D1為『V』時,儲存格D2顯示『TRUE』;在儲存格D1不為『V』時,儲存格D2顯示『FALSE
3. 選取儲存格A2:A10,設定資料驗證。
資料驗證如下設定,其中公式:=IF($D$2,ISNUMBER(A2),TRUE)
其中ISNUMBER(A2)乃在判斷儲存格A2是否為數值,並傳回 TRUE/FALSE
公式 IF($D$2,ISNUMBER(A2),TRUE) 的設計概念:
當儲存格D2傳回 TRUE 時,則會執行 ISNUMBER(A2) 的判斷並傳回 TRUE/FALSE(傳回 TRUE 時讓資料驗證生效),否則直接傳回 TRUE(讓資料驗證生效)。
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證
如此,當你在儲存格D1選取『X』時,就可以輸入文字了(ABC)。
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證
這個巧妙的設計在於當你取消啟用資料驗證後所輸入的資料,並不會因為之後重新啟用資料驗證而發生問題,啟用資料驗證只對當時輸入資料是的狀況做驗證。
Excel-在儲存格中輸入資料時設定切換是否使用資料驗證

【歸納結論】
依照此設計方式,你只要修改原來的資料驗證公式即可。
公式:=IF($D$2,原來的資料驗證公式,TRUE)

沒有留言:

張貼留言

好康東東