有網友問到:通常我們會在 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)

arrow
arrow
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()