Excel 表單設計小技巧5 限制輸入及主動被動提示

被動提示:輸入錯誤數值時彈出提示訊息盒, 選上格子時提示輸入選項.
主動提示:顯示訊息在附近格子上

做法:

I. 被動錯誤提示

限制得分在於0到100之間的數值.

1.選上要設定的格.
2.然後到 {資料} > 資料工具組[資料驗證]
打開”資料驗證”版面.

3.在{設定}頁:
今次例子是想限制得分在於0到100之間的數值.
“儲存格內允許”: 選上“整數”類型.
“資料”: 設為 “介於”
“最少”和“最大值”: 分別設為0 和100

4. 到{錯誤提醒}頁設定錯誤時會顯示的訊息.
打勾“輸入的資料不正確顯示警訊”
“樣式”: 指提示圖案, 可以選上 “停止”
“標題”: 錯誤訊息框標題,輸入“得分範圍提示” (效果會在下一部顯示)
“訊息內容”: 輸入訊息 “得分必需界乎於0至100之間”

樣式詳解:
“停止”通常表示用戶一定不可以這做
“警告”通常表示用戶可以這樣做,但後果自付.
“資訊”通常表示用戶可以這樣做,但要注意.
錯誤訊息來說通常會用第一項

5. 按下[確定], 完成!
測試一下,把數值改一改.

II. 被動選項提示

選上格子時,提示可接受內容

1.選上要設定的格.
2.然後到 {資料} > 資料工具組[資料驗證]
打開”資料驗證”版面, 進入{提示訊息}頁面

3. 到{錯誤提醒}頁設定錯誤時會顯示的訊息.
打勾“當儲存格被選取時, 輸入的資料不正確顯示警訊”
“樣式”: 指提示圖案, 可以選上 “停止” 顯示提示訊息
“標題”: 錯誤訊息框標題,輸入“得分範圍提示” (效果會在下一部顯示)
“訊息內容”: 輸入訊息 “得分必需界乎於0至100之間”

打勾“輸入的資料不正確顯示警訊”

4. 按下[確定], 完成!
測試一下,選上設定好的格子

III. 主動式錯誤顯示
A.建立公式

在輸入的格子上顯示錯誤提示.
因應內容轉變而顯示適當提示.

例如下圖,未輸入的話會顯示 “還未填寫”,
輸入了但不在0~100範圍的話會顯示 “輸入錯誤”

做法就是放上IF函數
=IF(INDIRECT("C"&ROW())="",
"還未填寫",

IF(OR(INDIRECT("C"&ROW())<0,INDIRECT("C"&ROW())>100),"輸入錯誤",""))

看來比較複習,在這裡拆解一下:
假設現在的位置是E8(輸入位置是C8), 基本上,公式可以寫成這樣
=IF(C8="","還未填寫",IF(OR(C8<0,C8>100),"輸入錯誤",""))
藍色部分檢查輸入是否 少於0 或大於100
OR用來把兩個條件C8<0和C8>100結合.
如果OR裡其中一個成立,就回傳錯誤提示 “輸入錯誤”
無問題的話回傳空值“”
綠色部分檢查有沒有輸入,有輸入的話呼叫藍色部分開始做事,
沒有輸入的話回傳錯誤提示 "還未填寫"

至於INDIRECT(“C”&ROW())
“C”&ROW() 是指當前C欄第幾行的位置意思.
然後INDIRECT函數可以把裡面位置的數值拿出來.
ROW()令公式向下抄時可以換行.
那麼,直接放上C8不是更簡單易明嗎?
當直接用“C8”方法時,
如果用戶用“剪下&貼上”的方法貼上資料時,
會引至 “C8”地址會變成#REF!.公式回傳錯誤.
INDIRECT則沒有這個問題.

上面的公式只是例子一個,可以隨意用IF做出你想要的效果.
比較重要的是要配合INDIRECT來使用.

B.隱藏公式

有些用戶比較頑皮,總想找找公式的弱點.
有一個方法可以把公式隱藏起來,不給用戶看

1.選上要設定的儲存格,右擊選定的儲存格,然後按 [儲存格格式]
(或按鍵盤CTRL+1)

2. 在“儲存格格式”, 到{保護}頁面
把“隱藏”打勾,然後按[確定]

3.到{校閱}菜單, 按一下[保護工作表].
打勾 “保護工作表與鎖定的儲存格內容”
下方 “允許此工作表的所有使用者能”列表,
只選上 “選取鎖定的儲存格”和“選取未鎖定的儲存格”
最後按[確定]

完成!

本篇功能重溫: 資料驗證, 函數(IF,INDIRECT), 保護工作頁, 隱藏儲存格
本篇最新更新2012-12-23