Excel資料驗證及條件式格式設定入門介紹

相信大家在使用Excel工作簿或分享工作簿給他人使用時，都曾發生需要除錯的狀況，例如想要的數據顯示不出來，進行檢查後才發現是數據輸入錯誤所致，處理諸如此類的問題相當耗時，甚至有時無法找出錯誤原因，導致工作簿失去它的功能，Excel資料驗證的功能可以大幅降低此類”輸入端”異常發生。

另外，條件式格式設定則是利於”輸入後”的檢查，本篇文章將以實際案例介紹資料驗證及條件式格式之設定。

資料驗證 條件式格式設定 總結與補充 下圖為本篇文章講解之範例：某公司每月人員評核分數。下左圖為經辦人員每月手工輸入的資料表，下右圖為其主管自己設定的公式，以查詢每月每人的評核分數，主管經檢查發現6月份”張進達”同仁竟查無資料，一氣之下罵了經辦一頓，並請經辦再重新確認資料。

接下來，小編將依步驟逐一說明資料驗證及條件式格式的使用方式。

資料驗證：

此範例將介紹如何以資料驗證功能設定下拉式選單(Drop Down List)，以避免同仁輸入錯誤。

設定資料驗證第一步驟為將需設定之範圍全選起來，(下圖)Excel資料表某欄位全選的方式為：將滑鼠放在該欄位表頭處(Header)，會出現一個黑白色箭頭，箭頭出現後按下滑鼠左鍵即可完成欄位全選(本案例為全選「姓名」欄位。

全選設定範圍後，從Tab Data -> Data Validation -> Data Validation，進入資料驗證視窗。

進入資料驗證視窗後，於Tab Settings(設定)裡，先選擇資料驗證之驗證類型，此案例為清單(List)，即為下拉式選單。

接著設定清單(下拉式選單)的資料來源，此範例為正確之人員姓名清單($H$3:$H$7)，接著點選OK即完成設定。

設定完成後，會發現6月份張進達的儲存格左上角出現了綠色三角形的提示符號，點開提示後出現資料驗證異常提示項目(Data validation error)如下下圖，再仔細一看，發現原來是”張進達”誤輸入成”張進逵“了，此Excel儲存格綠色三角形提示符號功能與資料驗證無關(這裡順帶一提而已)，資料驗證主要是防止輸入錯誤的發生。

接著隨意點選姓名欄位任一儲存格，會出現下拉式選單符號，點開後即可看到姓名清單(如下圖)。

如果我們將某一個姓名改為ABC，按下Enter後會出現異常提示視窗，此提示內容為Excel預設值，可再進行修改提示內容。

修改提示內容方式為先全選需設定之範圍(姓名欄位)，進入資料驗證視窗後點選Tab Error Alert(錯誤提醒)，選框左方的Style(樣式)不必修改，右方的Title(標題)及Error message(訊息內容)欄位進行修改設定。

完成修改後，輸入錯誤提示視窗已變更為我們所設定之提示內容(如下圖)。

資料驗證還有一個貼心的小設定： 當滑鼠選擇到設有資料驗證之儲存格時，會自動出現輸入提示。此功能設定方式為先全選需設定之範圍(姓名欄位)，進入資料驗證視窗後點選Tab Input Message(輸入訊息)，選框上方的check box Show input message when cell is selected(當儲存格被選取時，顯示輸入訊息)預設值為打勾不用修改，於Input message(輸入訊息)欄位進行輸入提示內容，Title(標題)可不輸入。

設定完後之畫面參考如下圖，以上就完成了資料驗證的設定。

PS 如果怕使用者誤刪除資料驗證設定，還可以再增加工作表上鎖之設定，將另寫文章介紹。

條件式格式設定：

條件式格式設定係利用資料橫條(Data Bars)、圖示集(Icon Sets)及儲存格格式(字體、顏色、框線)等視覺化物件來增強資料視覺化效果，此案例將介紹如何以儲存格格式來進行條件式格式之設定。

設定第一步驟為全選需設定之範圍(姓名欄位)，接著從Tab Home -> Conditional Formatting -> New Rule…進入條件式格式設定規則視窗。

進入條件式格式設定規則視窗後點選Use a formula to determine which cells to format(使用公式來決定要格式化哪些儲存格)，以利設定條件公式，意思是只要此公式敘述成立(True)，就會顯示我們設定之儲存格格式。此案例設定邏輯為同時不等於”阮文豪”、不等於”陳氏水”、不等於”張進達”、不等於”鄧友創”及不等於”阮德造”之條件下，就會顯示設定之格式，此為AND之邏輯，公式可使用：
=(條件1)*(條件2)*(條件3)*…或下下圖=AND(條件1, 條件2, 條件3,…..)。

這邊要特別說明一下，當你在點選儲存格C3時，Excel會預設為$C$3(如下動圖)，意思為Absolute Cell Reference(絕對儲存格參照)，若讀者還不知道儲存格參照的相對與絕對關係，請參考下一篇文章。這裡先簡單說明，如果使用$C$3，那Excel會以為每一列資料格式設定條件皆為儲存格C3(5月份阮文豪)，故要更改為C3或$C3也可以。

設定完條件公式後，接著點選下方的格式(Format…)按鈕進入儲存格格式設定視窗(如下圖)。首先進入Tab Font(字體)，將字體格式設定粗體及紅色。

接著進入Tab Fill(填滿)將填滿顏色改為黃色，完成後點選OK返回條件式格式設定規則視窗。

回到條件式格式設定規則視窗後，會發現下方格式預覽畫面已變為我們剛剛設定之格式，接著點選OK跳出視窗。

這時各位可以看到下圖資料表6月份”張進達”的儲存格格式改變了，仔細一看發現是誤輸入成”張進逵“，此條件式格式設定的功能可以很快地讓大家檢查出數據異常或突出之處。

如此已成功完成了條件式格式設定，接下來小編再補充一下條件式格式設定規則順序之差異，首先點選Conditional Formatting -> Manage Rules…進入規則管理員視窗。

因要說明規則設定順序之分別，故我們需先增加一個條件式格式設定規則，點選Duplicate Rule將既有的規則進行複製。

新的規則預設顯示在最上方(上層)，接著點選Edit Rule(編輯規則)按鍵，依前述之方法將此規則的格式設定為藍底白字，然後點選OK跳出規則管理員視窗。

大家會發現下圖資料表儲存格格式已變更為新的規則了(藍底白字)，此功能可應用於較複雜的條件式格式設定裡，依規則順序來顯示條件式格式。

那要如何調整規則順序呢，讓我們回到條件式格式規則管理視窗，於Duplicate Rule按鈕旁邊會看到兩個上下箭頭之符號，上箭頭代表移至上一層，下箭頭代表移至下一層，接著小編將新的規則(藍底白字)移至下一層。

調整規則順序後，可發現下圖資料表的儲存格格式已經變回舊的規則(黃底紅字)。

以上就是條件式格式設定的說明。

PS 條件式格式設定於工廠中應用廣泛，例如可使用於生產數據資料表，若數據超過管制值(超標)，儲存格即變色提醒同仁進行檢查。

總結及補充：

本篇文章介紹的範例較基礎，資料驗證還有很多其他驗證方式及驗證資料來源自動化設定；條件式格式設定亦可以輸入更複雜之條件公式，或還有資料橫條(Data Bar)、圖示集(Icon Sets)等視覺化物件，未來再分享給大家。

大家不妨先自行於工作中練習看看。