Zi 字媒體

使用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自動檢查儲存格中是否有輸入重複的資料 excel教學下拉式選單資料驗證 87 Comments Add Comment → rene 請問假如選了文具之後，要讓他帶出不同的價格，該如何進一步設定呢？謝謝 2017-09-09 Reply changyang319 這個只要參考這篇文章就可以了。 https://mrtang.tw/blog/post/38903383 2017-09-17 Reply 訪客 我看懂了….非常感謝!!! 2017-03-23 Reply 貓貍過客 簡單明瞭，能很快速的學起來並應用，感謝版主教學！ 2017-03-09 Reply Irene Lu 您好： 謝謝您超實用的分享！ 我已按照步驟完成下拉式選單，但如果是複選，請問該怎麼做呢? 謝謝您的回覆~ 2017-03-07 Reply R 非常實用。謝謝~ 2017-03-06 Reply changyang319 原來還能這樣喔，謝謝您的解答喔。 2017-02-06 Reply 333 實用！馬上就懂了，非常感謝教學！ 2017-02-05 Reply 訪客 to: #45 如需空白格可在定義名稱 "參照到"的範圍包含一個空白儲存格,在下拉清單時就可以多一個空白格的選項,即可自行填寫. 2017-01-05 Reply changyang319 應該沒有辦法這樣。 2016-12-10 Reply 您好 如果我想設定 半斤 1斤 3斤 多一欄空格(可以自行填寫) 請問空格怎麼設定呢? 我剛剛試了他會出現 你所輸入的值不對 你必須在儲存格內輸入符合資料驗證準則的資料 2016-11-15 Reply carol 學到一招~~~感謝教學分享~~ <3 2016-10-11 Reply sindy lee 感謝分享 助益良多 2016-09-08 Reply 松 非常感謝 2016-08-01 Reply changyang319 謝謝。 2016-07-22 Reply 訪客 簡潔易懂，讚 2016-07-14 Reply changyang319 應該是「名稱」沒有設好。 2016-06-02 Reply 訪客 先謝謝版主,不過我照版主的步驟在最後卻出現"找不到您所指定的名稱範圍",請問問題出在哪? 2016-05-26 Reply yi 謝謝分享 ! 超棒的 ! 2016-03-01 Reply changyang319 謝謝哦。 2016-02-16 Reply 訪客 簡潔易懂，大推!! 2016-01-27 Reply changyang319 我覺得應該沒有辦法。 2016-01-17 Reply changyang319 這個好專業，我可能幫不上忙，抱歉喔。 2016-01-17 Reply 訪客 真的變得超容易理解的! 可還有一個問題想請教一下，可否在實現下拉選單的同時使用excel最基礎的匹配功能?有夠貪心的…… 例如: 我的下拉選項是:1.XXX 2.YYY 3.ZZZ…… 可否在我輸入3的時候自動出現3.ZZZ呢? 因為在快速輸入資料時突然轉成鼠標真的有夠麻煩的XD 2016-01-10 Reply robbielee0723 版主您好,請教一個問題, 如何將Excel 2010的插入符號字型(P22 GDT No Frames)匯出至Excel 2013, 因為我在Excel 2010儲存的檔案,於Excel 2013開啟時插入字型會變成亂碼, 或是您有其他的方式可以讓插入符號字型(P22 GDT No Frames)保留至Excel 2013, 再請您撥空回復,謝謝! 2016-01-07 Reply 54 謝謝版大提供這個資訊呢~很實用唷~~~ 另外想請問版大~ 若資料是好幾個項目裡面有好幾個選項 例如一個表單裡面彙整了整個資料 且還會陸續增加 A公司裡面有A/B/C/D人員 B公司裡面有A/B/C/D人員 C公司裡面有A/B/C/D人員 — 需要在另外一個表格製作類似GOOGEL EXCEL 裏頭的"驗證" 在搜尋指定欄位可以打第一個字A 就會有A公司所有人可以選 打B就會有B公司所有人可以選 有辦法做到跟GOOGLE 一樣的功效嗎? 因為這個也算滿方便的 2016-01-04 Reply changyang319 謝謝喔。 2016-01-01 Reply 訪客 超級實用~讚 2015-12-23 Reply changyang319 大概就要寫VBA程式了。 2015-12-17 Reply lz407636 真是太感謝了對我來說太有用了 還有一事請教: 如何將一個活頁簿的整個資料和其他資料夾內的活頁簿做相關聯(類似同步) 2015-12-01 Reply changyang319 命名為英文應該是可以的，但有空格的話，就真的不行。 2015-09-10 Reply 訪客 版主~~謝謝你的教學,讓我受益良多,想請教一個問題喔!! 發現到下拉式選單如果命名為英文或是有空格就無法順利跑去結果 除非將空格改成_才可以跑出來~~~不知道這方面怎麼處理呢?! 2015-09-06 Reply 訪客 謝謝版主,寫得讓我好容易懂,這個問題我讓我想破頭了一個晚上,雖參考另幾位版主的分享,但還是無法理解.今天有緣看到你的分享,我的疑惑立即迎刃而解,感謝你的分享. 2015-09-03 Reply Amy.H 很受用，謝謝您無私的分享。 2015-08-27 Reply 訪客 謝謝。很實用。寫的也很詳細，一學就上手了。 2015-07-27 Reply Violet 謝謝你的熱心教學，受益良多：） 2015-07-17 Reply changyang319 謝謝您的誇獎哦。 2015-04-16 Reply Shinperng Wu 講解得好詳細！ 太有幫助了，謝謝!!! 2015-04-13 Reply changyang319 不客氣。 2015-03-31 Reply 阿強 非常感謝~很實用! 2015-03-25 Reply changyang319 預設的文字大小是沒有辦法變更的，如果真的想要變更，只能寫VBA程式，然後重新建立一個ComboBox控制項，如下網址： http://www.contextures.com/xlDataVal10.html 2015-03-17 Reply JUDY 這個資料驗證清單, 好好用, 多謝分享 但當清單資料在拉下選擇時, 文字如何較大? 請給予指導! 謝謝 2015-03-14 Reply changyang319 也謝謝你的造訪哦。 2015-03-05 Reply Lisa 謝謝您的教學 受用無窮! 2015-03-03 Reply changyang319 謝謝您的誇獎哦，我在寫這篇教學時，除了教大家怎麼用之外，最重要的是，我還說明了「為什麼」要使用它，一般書上應該都只著重在怎麼使用而已。 2014-12-25 Reply 悶飯 謝謝版主! 您真的太厲害太厲害了! 書局很多書寫得還沒有您寫得詳細呢! 2014-12-17 Reply changyang319 我這邊只有Excel 2010 Starter版本，也不曉得2010的版面有什麼不一樣？ 可是我看網路，2010的介面應該和2007差不多才是。 http://office.microsoft.com/zh-tw/excel-help/HP010342598.aspx 2014-12-03 Reply changyang319 若你「性別」已經填好，然後再設定「資料驗證」的話，那「標題」並不會有警告訊息。 如果你是設定好「資料驗證」之後，再去填入「標題」名稱的話，就會出現警告訊息，所以就單獨去取消邊標題的資料驗證功能即可。 我自己都是這樣做的。 2014-12-01 Reply changyang319 如果是比較複雜的功能，就只能寫VBA程式，利用現有的公式來達成，複雜度可能還比寫程式還難。 2014-12-01 Reply changyang319 我剛測試的結果，好像不行。 2014-12-01 Reply changyang319 你可以再參考這篇，兩階層以上的選單。 https://mrtang.tw/blog/post/38903383 2014-12-01 Reply Peter Kuo Dear Sir：請問 在excell 2003 點選 ==>資料\匯入外部資料 \匯入資料 在2010版要如何操作?? 2014-11-26 Reply g902147 您好，謝謝您的分享十分實用 另外想請問，在選擇整攔時要如何掠過標題? 例如:此選項為性別，而此格並不受男女的限制 謝謝您 2014-11-24 Reply 小楊 感謝您的回覆, 不過我是想把sheet1, sheet2 放在清單中, 但無奈技術不足無法成功! 不知道是有別的作法可達成或是完全不能這樣做呢? 2014-11-23 Reply 初學者 初學者 – 上述下拉式清單中之資料，可否參照另一活頁簿中已命名之資料? 2014-11-22 Reply 訪客 清單能否依據某欄位值顯示不同清單內容 如 縣市 地區 台北市 中山區/萬華區 高雄市 左營區/三民區 2014-11-22 Reply changyang319 你的意思是這張圖這樣嗎？我把工作表的名稱，命名為一個「清單」名稱，然後在I7的位置上使用它，測試結果是可以的。 https://mrtang.tw/album/photo/283944862 2014-11-18 Reply 小楊 您好, 拜讀貴文學習甚多, 有一件額外的EXCEL功能想請教…. 如果想把頁籤(SHEET)名稱當件清單範圍, 是否有其可行性? 2014-11-14 Reply changyang319 我不確定您所要描述的動作，所以沒辦法回答這個問題。 只有動作太複雜時，才會需要使用到VBA。 2014-10-29 Reply Scott Chen 你好， 感謝你的說明，很詳細，另外想請教一下，如果要將所有的輸入藍未清單整合在一個檔案內，讓所有的工作表都能使用此清單，是否就是要用VB ? 謝謝!! 2014-10-23 Reply changyang319 不客氣哦。 2014-10-22 Reply changyang319 哇，謝謝您的誇獎哦，好開心喔。 2014-10-15 Reply Jessie 感恩您的用心與付出～讚讚讚 2014-10-15 Reply 猴 謝謝版主，看得出很有心去寫和照顧讀者！ 2014-10-09 Reply changyang319 我是覺得應該是不會這樣，以前我用2003時，並沒有這樣的狀況，但我手邊沒有2003的版本，沒有辦法做測試。 2014-09-07 Reply changyang319 我覺得應該不是，在2003應該也是要可以建立名稱的。 2014-09-07 Reply Ryan 版主,你好.關於清單選擇建立清單卻發現無法命名,這是2003/2007的差別嗎? 2014-08-27 Reply Ryan 謝謝你的教學!我仿照相同方式在excel2003操作,在清單選擇建立清單卻發現無法命名…這是2003/2007的差別嗎? 2014-08-25 Reply changyang319 您好，不曉得您有先看過這篇文章了嗎？ http://changyang319.com/archives/1843 因為您在定義名稱的範圍是不正確的。 正確的範圍定義，你只有「廠區」這個名稱是正確的，我大概寫一下應該要如何定義： 廠區 => A1:I1 單位 => A2:I2 Au精煉課 => A3:A9 FCT課 => B3:B9 其餘以此類推。 2014-08-12 Reply 穎子 http://devilkin47.pixnet.net/album/set/17755756 我有上傳圖片~ 有空的話..可以幫我看看嗎?? 是我定義名稱錯了..還是…這種資料沒法做3層?? 2014-08-07 Reply 穎子 Dear 版大…請教一下~ 我做到–結合『「名稱管理員」的「清單」資料驗證』中的第2步驟 「來源」中，直接填入「=文具清單」時，它卻出現提示 【來源清單必須是一個有分隔符號的清單，或是一個列或欄的參照】…我就無法再繼續了?? 是miss了什麼嗎?? (我有用"=") 2014-08-07 Reply changyang319 不客氣喔。 2014-07-21 Reply changyang319 謝謝您的造訪。 2014-07-19 Reply v 好實用，謝謝喔！ 2014-07-15 Reply changyang319 不客氣，感謝您的造訪。 2014-07-15 Reply 訪客 非常非常好，感谢， 希望楼主多多更新 2014-07-11 Reply 訪客 學懂了, 感謝! 2014-07-10 Reply 訪客 很棒～非常好懂～ 辛苦了!!! 2014-06-26 Reply changyang319 謝謝您的誇獎，我花這麼多時間寫了這一篇，總算是值得了。 2014-05-09 Reply ivy 圖文並茂，簡單好懂~非常感謝您!!! 2014-05-07 Reply changyang319 謝謝您的誇獎喔。 2014-04-07 Reply Iris 內容淺顯易懂，謝謝您的用心。 2014-04-06 Reply Ann 你的內容淺顯易懂,好用 謝謝你的付出!! 2014-04-01 Reply changyang319 謝謝您的誇獎。 2014-03-31 Reply 阿咪 好神奇!! 文書軟體不難上手，但最困難的莫過於如何化簡為繁了! 這些教學不只實用、生活化、也非常易懂! 真的非常感謝: ) 2014-03-29 Reply changyang319 感謝您的誇獎。 2014-02-15 Reply Daniel 寫的人用心 看的人容易 謝了 2014-02-14 Reply