電癮院

以經常會遇到的問題及「初學者」的角度,來看待「電腦教學」這回事。

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程式來製作選單的方式,這個由於比較困難,因此我將來會獨立出一篇文章,來做這方面的教學。

Previous

「KVM電腦切換器」讓兩台電腦或多台電腦主機共用一台螢幕

Next

Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定

85 Comments

  1. Daniel

    寫的人用心
    看的人容易
    謝了

  2. changyang319

    感謝您的誇獎。

  3. 阿咪

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

  4. changyang319

    謝謝您的誇獎。

  5. Ann

    你的內容淺顯易懂,好用 謝謝你的付出!!

  6. Iris

    內容淺顯易懂,謝謝您的用心。

  7. changyang319

    謝謝您的誇獎喔。

  8. ivy

    圖文並茂,簡單好懂~非常感謝您!!!

  9. changyang319

    謝謝您的誇獎,我花這麼多時間寫了這一篇,總算是值得了。

  10. 訪客

    很棒~非常好懂~
    辛苦了!!!

  11. 訪客

    學懂了, 感謝!

  12. 訪客

    非常非常好,感谢, 希望楼主多多更新

  13. changyang319

    不客氣,感謝您的造訪。

  14. v

    好實用,謝謝喔!

  15. changyang319

    謝謝您的造訪。

  16. changyang319

    不客氣喔。

  17. 穎子

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

  18. 穎子

    http://devilkin47.pixnet.net/album/set/17755756

    我有上傳圖片~ 有空的話..可以幫我看看嗎?? 是我定義名稱錯了..還是…這種資料沒法做3層??

  19. changyang319

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

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

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

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

  20. Ryan

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

  21. Ryan

    版主,你好.關於清單選擇建立清單卻發現無法命名,這是2003/2007的差別嗎?

  22. changyang319

    我覺得應該不是,在2003應該也是要可以建立名稱的。

  23. changyang319

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

  24. 謝謝版主,看得出很有心去寫和照顧讀者!

  25. Jessie

    感恩您的用心與付出~讚讚讚

  26. changyang319

    哇,謝謝您的誇獎哦,好開心喔。

  27. changyang319

    不客氣哦。

  28. Scott Chen

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

  29. changyang319

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

  30. 小楊

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

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

  31. changyang319

    你的意思是這張圖這樣嗎?我把工作表的名稱,命名為一個「清單」名稱,然後在I7的位置上使用它,測試結果是可以的。

    https://mrtang.tw/album/photo/283944862

  32. 訪客

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

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

  33. 初學者

    初學者 – 上述下拉式清單中之資料,可否參照另一活頁簿中已命名之資料?

  34. 小楊

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

  35. g902147

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

  36. Peter Kuo

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

  37. changyang319

    你可以再參考這篇,兩階層以上的選單。

    https://mrtang.tw/blog/post/38903383

  38. changyang319

    我剛測試的結果,好像不行。

  39. changyang319

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

  40. changyang319

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

    我自己都是這樣做的。

  41. changyang319

    我這邊只有Excel 2010 Starter版本,也不曉得2010的版面有什麼不一樣?
    可是我看網路,2010的介面應該和2007差不多才是。
    http://office.microsoft.com/zh-tw/excel-help/HP010342598.aspx

  42. 悶飯

    謝謝版主! 您真的太厲害太厲害了! 書局很多書寫得還沒有您寫得詳細呢!

  43. changyang319

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

  44. Lisa

    謝謝您的教學 受用無窮!

  45. changyang319

    也謝謝你的造訪哦。

  46. JUDY

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

  47. changyang319

    預設的文字大小是沒有辦法變更的,如果真的想要變更,只能寫VBA程式,然後重新建立一個ComboBox控制項,如下網址:

    http://www.contextures.com/xlDataVal10.html

  48. 阿強

    非常感謝~很實用!

  49. changyang319

    不客氣。

  50. Shinperng Wu

    講解得好詳細!
    太有幫助了,謝謝!!!

  51. changyang319

    謝謝您的誇獎哦。

  52. Violet

    謝謝你的熱心教學,受益良多:)

  53. 訪客

    謝謝。很實用。寫的也很詳細,一學就上手了。

  54. Amy.H

    很受用,謝謝您無私的分享。

  55. 訪客

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

  56. 訪客

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

  57. changyang319

    命名為英文應該是可以的,但有空格的話,就真的不行。

  58. lz407636

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

  59. changyang319

    大概就要寫VBA程式了。

  60. 訪客

    超級實用~讚

  61. changyang319

    謝謝喔。

  62. 54

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

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

  63. robbielee0723

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

  64. 訪客

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

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

  65. changyang319

    這個好專業,我可能幫不上忙,抱歉喔。

  66. changyang319

    我覺得應該沒有辦法。

  67. 訪客

    簡潔易懂,大推!!

  68. changyang319

    謝謝哦。

  69. yi

    謝謝分享 ! 超棒的 !

  70. 訪客

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

  71. changyang319

    應該是「名稱」沒有設好。

  72. 訪客

    簡潔易懂,讚

  73. changyang319

    謝謝。

  74. 非常感謝

  75. sindy lee

    感謝分享
    助益良多

  76. carol

    學到一招~~~感謝教學分享~~ <3

  77. 您好

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

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

  78. changyang319

    應該沒有辦法這樣。

  79. 訪客

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

  80. 333

    實用!馬上就懂了,非常感謝教學!

  81. changyang319

    原來還能這樣喔,謝謝您的解答喔。

  82. R

    非常實用。謝謝~

  83. Irene Lu

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

  84. 貓貍過客

    簡單明瞭,能很快速的學起來並應用,感謝版主教學!

  85. 訪客

    我看懂了….非常感謝!!!

發表迴響

Powered by WordPress & Theme by Anders Norén