文書處理教學

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

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

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

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

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

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

excel_combobox-1.jpg

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

但你有發現到怪怪的嗎?

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

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

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

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

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

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

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

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

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

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

excel_combobox-2.jpg

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

excel_combobox-3.jpg

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

excel_combobox-4.jpg

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

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

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

excel_combobox-5.jpg

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

excel_combobox-6.jpg

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

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

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

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

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

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

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

excel_list-1.jpg

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

excel_list-2.jpg

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

excel_list-3.jpg

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

excel_list-4.jpg

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

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

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

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

excel_name_manager-1.jpg

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

excel_name_manager-2.jpg

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

excel_name_manager-3.jpg

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

excel_name_manager-4.jpg

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

85 thoughts on “Excel深度教學:使用「資料驗證」來製作「下拉式選單」

  1. 好神奇!!
    文書軟體不難上手,但最困難的莫過於如何化簡為繁了!
    這些教學不只實用、生活化、也非常易懂!
    真的非常感謝: )

  2. Dear 版大…請教一下~
    我做到–結合『「名稱管理員」的「清單」資料驗證』中的第2步驟
    「來源」中,直接填入「=文具清單」時,它卻出現提示
    【來源清單必須是一個有分隔符號的清單,或是一個列或欄的參照】…我就無法再繼續了?? 是miss了什麼嗎?? (我有用"=")

  3. 您好,不曉得您有先看過這篇文章了嗎?
    http://changyang319.com/archives/1843

    因為您在定義名稱的範圍是不正確的。

    正確的範圍定義,你只有「廠區」這個名稱是正確的,我大概寫一下應該要如何定義:

    廠區 => A1:I1
    單位 => A2:I2
    Au精煉課 => A3:A9
    FCT課 => B3:B9
    其餘以此類推。

  4. 謝謝你的教學!我仿照相同方式在excel2003操作,在清單選擇建立清單卻發現無法命名…這是2003/2007的差別嗎?

  5. 我是覺得應該是不會這樣,以前我用2003時,並沒有這樣的狀況,但我手邊沒有2003的版本,沒有辦法做測試。

  6. 你好,
    感謝你的說明,很詳細,另外想請教一下,如果要將所有的輸入藍未清單整合在一個檔案內,讓所有的工作表都能使用此清單,是否就是要用VB ? 謝謝!!

  7. 我不確定您所要描述的動作,所以沒辦法回答這個問題。
    只有動作太複雜時,才會需要使用到VBA。

  8. 您好, 拜讀貴文學習甚多, 有一件額外的EXCEL功能想請教….

    如果想把頁籤(SHEET)名稱當件清單範圍,
    是否有其可行性?

  9. 清單能否依據某欄位值顯示不同清單內容

    縣市 地區
    台北市 中山區/萬華區
    高雄市 左營區/三民區

  10. 感謝您的回覆, 不過我是想把sheet1, sheet2 放在清單中, 但無奈技術不足無法成功! 不知道是有別的作法可達成或是完全不能這樣做呢?

  11. 您好,謝謝您的分享十分實用
    另外想請問,在選擇整攔時要如何掠過標題?
    例如:此選項為性別,而此格並不受男女的限制
    謝謝您

  12. Dear Sir:請問 在excell 2003 點選 ==>資料\匯入外部資料 \匯入資料
    在2010版要如何操作??

  13. 如果是比較複雜的功能,就只能寫VBA程式,利用現有的公式來達成,複雜度可能還比寫程式還難。

  14. 若你「性別」已經填好,然後再設定「資料驗證」的話,那「標題」並不會有警告訊息。
    如果你是設定好「資料驗證」之後,再去填入「標題」名稱的話,就會出現警告訊息,所以就單獨去取消邊標題的資料驗證功能即可。

    我自己都是這樣做的。

  15. 謝謝您的誇獎哦,我在寫這篇教學時,除了教大家怎麼用之外,最重要的是,我還說明了「為什麼」要使用它,一般書上應該都只著重在怎麼使用而已。

  16. 這個資料驗證清單, 好好用, 多謝分享
    但當清單資料在拉下選擇時, 文字如何較大? 請給予指導! 謝謝

  17. 謝謝版主,寫得讓我好容易懂,這個問題我讓我想破頭了一個晚上,雖參考另幾位版主的分享,但還是無法理解.今天有緣看到你的分享,我的疑惑立即迎刃而解,感謝你的分享.

  18. 版主~~謝謝你的教學,讓我受益良多,想請教一個問題喔!!
    發現到下拉式選單如果命名為英文或是有空格就無法順利跑去結果
    除非將空格改成_才可以跑出來~~~不知道這方面怎麼處理呢?!

  19. 真是太感謝了對我來說太有用了
    還有一事請教:
    如何將一個活頁簿的整個資料和其他資料夾內的活頁簿做相關聯(類似同步)

  20. 謝謝版大提供這個資訊呢~很實用唷~~~
    另外想請問版大~
    若資料是好幾個項目裡面有好幾個選項
    例如一個表單裡面彙整了整個資料 且還會陸續增加
    A公司裡面有A/B/C/D人員
    B公司裡面有A/B/C/D人員
    C公司裡面有A/B/C/D人員

    需要在另外一個表格製作類似GOOGEL EXCEL 裏頭的"驗證"
    在搜尋指定欄位可以打第一個字A 就會有A公司所有人可以選 打B就會有B公司所有人可以選
    有辦法做到跟GOOGLE 一樣的功效嗎? 因為這個也算滿方便的

  21. 版主您好,請教一個問題,
    如何將Excel 2010的插入符號字型(P22 GDT No Frames)匯出至Excel 2013,
    因為我在Excel 2010儲存的檔案,於Excel 2013開啟時插入字型會變成亂碼,
    或是您有其他的方式可以讓插入符號字型(P22 GDT No Frames)保留至Excel 2013,
    再請您撥空回復,謝謝!

  22. 真的變得超容易理解的!
    可還有一個問題想請教一下,可否在實現下拉選單的同時使用excel最基礎的匹配功能?有夠貪心的……
    例如: 我的下拉選項是:1.XXX 2.YYY 3.ZZZ……
    可否在我輸入3的時候自動出現3.ZZZ呢?

    因為在快速輸入資料時突然轉成鼠標真的有夠麻煩的XD

  23. 先謝謝版主,不過我照版主的步驟在最後卻出現"找不到您所指定的名稱範圍",請問問題出在哪?

  24. 如果我想設定
    半斤
    1斤
    3斤
    多一欄空格(可以自行填寫)
    請問空格怎麼設定呢?
    我剛剛試了他會出現

    你所輸入的值不對
    你必須在儲存格內輸入符合資料驗證準則的資料

  25. to: #45
    如需空白格可在定義名稱 "參照到"的範圍包含一個空白儲存格,在下拉清單時就可以多一個空白格的選項,即可自行填寫.

  26. 您好:
    謝謝您超實用的分享!
    我已按照步驟完成下拉式選單,但如果是複選,請問該怎麼做呢?
    謝謝您的回覆~

發表迴響