Excel深度教學：使用「資料驗證」來製作「下拉式選單」

使用Excel「下拉式選單」來輸入資料

「Excel」和「Word」一樣，看似很簡單，像是Word只是把文字給輸入進去，而Excel充其量也只是把一些數字與文字，填進去一個個儲存格罷了，但實際上，在每個小細節之中，都有更「高明」的做法出現。

之前我曾經寫過「使用Microsoft Word最常犯的五個錯誤」及「每個人都要學的Word實用技巧教學-合併列印」這幾篇文書處理的文章，雖然瀏覽的人數並不高，但卻受到網友們的肯定，剛好我對於Excel也還蠻熟的，因此也想分享一下使用Excel的一些經驗。

而這篇文章，我會針對「輸入文字到儲存格」的方式，由簡單到困難，來示範一些相當有用的做法，這些做法都是非常實用的技巧，學起來後，包準您能製作出高等的Excel文件，當然如果您是高手，還請高抬貴手，不要笑我教這麼粗淺的東西啊。

經常我們都會在Excel上輸入一些少量的「固定清單資料」，像是在輸入人事資料中的「性別」：「男,女」，「血型」：「A,B,AB,O」…等等，如以下的人事資料清單：

這些資料是我自己杜撰的，而在清單中所輸入的性別及血型有一個特點，就是「不會變動」，因為不會有「男、女」之外的選項出現。

但你有發現到怪怪的嗎？

由於這些資料都是我「手動」輸入的，相信有大部份的人也都一樣，在輸入一些固定資料時，都是用直接打字的方式，將資料給填入儲存格中。你一定覺得很奇怪，若不用手輸入，難不成用腳嗎？

我的意思不是這樣的，直接用打字的方式輸入這種固定資料，可能是最簡單的的方式，但並不是一個最好的方式，原因就是「容易出錯」，你一、兩次一定不會打錯，但要是打了上百次，你能保證你完全都不出錯，而且都能注意到嗎？就好比我輸入的這四筆員工資料中，就已經有出錯的地方了，你看出來了嗎？

答案就是「黃色小鴨」的「血型」出錯了，原本應該是英文的「O」，結果被打成數字的「0」，但因為都是一個圈圈，因此不容易看出來，你會覺得就算打錯，會有什麼問題？

我舉個例子，假設你今天到銀行裡面存錢，行員不小心少收了1塊錢，而你也沒有發現到，但因為只有1塊錢，你會想說銀行自己貼錢進去就好了，就好像一般商店的店員，當每日結算的帳款不符時，若金額只有差幾塊錢而已，可能就都是當班店員自行吸收就好，但銀行可就不一樣了，雖然我沒有在銀行任職過，但聽說他們碰到這種情況時，就要加班把帳抓出來，你能想像這浩大的工程嗎？簡直可以拍攝Discovery的節目了。

回到我們這個員工資料表，如果您用「樞紐分析表」或是「資料庫」來分析員工在每個血型的人數時，你會發現到「A型」1人、「B型」0人、「AB型」1人、「O型」1人，怎麼所有血型加起來只有3人？整個公司不是有4人嗎？怎麼少1人？這就是輸入資料錯誤時，會產生的問題。

現在由於只有4筆，所以仔細檢查一下，還可以找出問題所在，但要是員工資料有1萬筆時，是不是就像大海撈針一樣，而且，並不是所有的資料，都可以透過最後的結果，來看出所得出的資料是有問題的資料，若出現這種問題時，事情可就大條了。

限制可以輸入文字資料的「資料驗證」

在Excel裡，有一個功能叫做「資料驗證」，就是設計用來避免這種輸入無效資料的狀況，使用的方式如下：

Step 1. 首先，選取您想要設定的欄位，因為我們要設定的通常是一整個欄位，因此在選取時，一次選取一整個「欄」或一整個「列」即可，因為總不能一格一格的設定吧？

在選取之後，再點擊「資料」頁籤中的「資料驗證」，如下圖：

Step 2. 接著在「資料驗證」對話盒中，在「儲存格內允許」中選擇「清單」，並在「來源」的位置，填入「男,女」，也就是將所有想要出現的文字數值，用「,」分號來做區隔，而組合而成的一個字串，最後再點擊〔確定〕即可。

Step 3. 經過這樣的設定，只要點擊每一個「性別」的儲存格，就會出現一個下拉式的選單，而這個選單內的內容，就是我們剛才所設定的「男」和「女」，如下圖：

相信透過這樣的選擇，就不會有輸入錯誤的情況發生了。

除此之外，在「生日」欄位，也可以利用此「資料驗證」的方式，來避免使用者手誤，而填入太誇張的日期，操作方式如下：

和之前一樣，同樣選取想要設定的儲存格，接著在「儲存格內允許」中選擇「日期」，並且設定「開始日期」及「結束日期」，如下圖：

從這樣的設定之後，只要你在生日這個欄位，沒有輸入介於你所設定的日期時，就會出現「您所輸入的值不正確」的錯誤訊息。

從以上的兩個例子可以了解到「資料驗證」的好用之處，而在「資料驗證的準則」中，除了「清單」及「日期」之外，還有「任意值」、「整數」、「實數」、「時間」、「文字長度」及「自訂」可供設定。

碰到會常變動及新增的清單資料怎麼辦？

相信您對這個好用的功能已經躍躍欲試了，可是至今，我們輸入的「清單」都是很「簡單」，而且是「不變」的，但要是我們輸入的，是一個產品資料表呢？相信那可就麻煩了吧。

你能想像要在一個充滿分號的字串中，維護數以百計的產品項目嗎？

麻煩到包你不想再使用這個「資料驗證」的功能，但這可就失去這功能原本的立意了，但其實建立清單的方式，是可以改變的。

你一定是想，若清單也可以直接建立在「工作表」上的「儲存格」中，那「清單」就更好維護了吧？

沒問題，清單也是可以另外建立在工作表上的喔，首先，一樣開啟「資料驗證」，接著我們不直接輸入想要的清單文字，而是點擊「來源」欄位右邊的小圖示，如下圖：

接著選擇「文具清單」中的這些「文具項目」，如下圖：

最後這個「來源」就會以「儲存格範圍」的方式，來設定來源，如下圖：(當然，我們也可以直接填入儲存格欄位的值，若您會填的話)

最後在左方的「文具用品」中，只要點選了裡面的儲存格，就會出現右方清單中的項目，日後若是文具用品有新增的話，只要再去修改「儲存格範圍」的值即可。

但您有沒有看出，這有一個非常「致命」的問題，我們「通常」不會把「清單資料」，和要輸入的儲存格建立在「同一工作表」中，而是會將這些清單資料，獨立到另一個工作表裡，這樣版面才會比較整齊乾淨，但要怎麼做呢？

結合「名稱管理員」的「清單」資料驗證

由於在資料驗證中輸入儲存格範圍時，會有需同一個工作表的限制，因此這個限制要透過「定義名稱」來解決。

Step 1. 首先，我們先在其它的資料表中，建立這個「文具清單」的項目，接著選取這些項目，並點擊「公式」裡的「定義名稱」，接著在跳出的「新名稱」對話盒中，在「名稱」處，輸入您想要定義的名稱，我們這填「文具清單」即可，最後再點擊〔確定〕，操作如下圖：

經過這樣的設定，您若是點擊「名稱管理員」時，就會看到您所設定的「名稱」已經出現在這裡面了。

Step 2. 定義好名稱之後，同樣的操作來到「資料驗證」中，這時我們就可以直接在「來源」中，直接填入「=文具清單」這個名稱(注意有要「=等於符號」)，如下圖：

Step 3. 接著就是見證奇蹟的時刻了，你會發現，不需要在同一個資料表，也能有相同的效果出現，但要注意的是，將來若清單中的項目有增減，你就要使用「名稱管理員」來變修改一下所定義名稱的範圍設定。

在我舉出這一連串的例子，相信您應該能充份了解到「資料驗證」的使用了吧，實際上，還有另一個使用VBA程式來製作選單的方式，這個由於比較困難，因此我將來會獨立出一篇文章，來做這方面的教學。

Excel教學相關文章

• EXCEL實用技巧教學「樞紐分析表(Pivot Tables)」
• Excel深度教學：使用「資料驗證」來製作「下拉式選單」
• Excel深度教學：「二層」、「三層」的動態「下拉式選單」設定
• Excel深度教學：VLOOKUP查表函數及比對中文字串時發生錯誤的問題
• 讓Excel自動檢查儲存格中是否有輸入重複的資料