電癮院

以初學者的角度,來看待電腦教學這回事。

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

Excel的多階層選單

在前一篇Excel的教學文中,我介紹了使用「資料驗證」的方式,來製作「下拉式選單」( Excel深度教學:使用「資料驗證」來製作「下拉式選單」),若您還不曉得這是什麼,您應該先看完那篇之後,再回過頭來看這篇。

而本篇要說明的是,有時我們的清單資料,是有「階層」的分類,尤其是產品很多的公司,一定都會把數量龐大的產品分門別類,要不然將所有的產品都設定成單一個「下拉式選單」,你可能也很難找的到。

例如下圖,是我簡單製作的文具用品清單,在每個「分類」中,都有屬於它自己的文具用品項目,因此在「品名」出現的下拉式選單,才不會一次出現所有的文具用品,而讓你選的眼花繚亂。

excel_step_combo-1.jpg

那問題就來了,「分類」還不是什麼問題,就直接設定「清單」的來源就好了,因為它的清單項目都是「固定」的,但「品名」這一欄就不一樣了,因為這一欄的「清單」,是根據「分類」下去改變,是不是將「清單來源」給設死的,因此這邊就有個小技巧來分享給各位,教大家如何來處理這種狀況。

Excel二層下拉式選單設定教學

Step 1. 首先,在其它的工作表(例如:Sheet2)製作好一個有階層的資料清單,在下面的例如中,我簡單製作了一個文具用品的階層資料,所有的文具用品分為三類,分別是「書寫用品」、「辦公用品」及「紙製品」,並將分類的資料,填入第一列中,而該分類中的文具用品項目,就分別填入每個欄位裡,例如「書寫用品」裡有「原子筆、筆芯、鉛筆」等項目,如下圖:

excel_step_combo-2.jpg

Step 2. 製作好分類的資料庫清單後,接著新增「文具用品分類」及每個分類下的文具用品的清單「名稱」,總共會新增四個「名稱」出來,如下圖名稱管理員所示,若您對於新增「名稱」的方法有問題的話,請參考「Excel深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

excel_step_combo-3.jpg

Step 3. 接著先將「分類」的資料驗證,設定為「清單」,並將來源設定為「名稱」中的「文具用品分類」。

excel_step_combo-4.jpg

若您能設定成功,我們就能在分類中,利用「下拉式選單」來選取文具用品的大分類項目了,如下圖:

excel_step_combo-5.jpg

Step 4. 接著就是本篇文章的重點所在,就是「品名」欄位的「資料驗證」如何設定,因為在這個欄位中的清單是「變動」的,也就是隨著我們選擇的「分類」,而在「品名」欄位中,出現該分類的文具用品項目。

首先,先選取整個「品名」欄位,也就是「欄位C」,接著在來源中輸入「=INDIRECT(B1)」,最主要就是利用「INDIRECT」這個函數,而儲存格B1就是分類的那一欄位,因此這邊要根據您的情況來做設定。

這邊你也許會有個問題,就是我怎麼會將這一整個C欄位,都設定「=INDIRECT(B1)」呢?第二列不應該要設定成「=INDIRECT(B2)」,而第三列應該是「=INDIRECT(B3)」嗎?

沒錯,您的疑問是對的,不過Excel真的蠻聰明的,它會主動幫我們做跳號的動作,厲害吧?

excel_step_combo-6.jpg

而由於我們也將「標題」列也設定「資料驗證」了,因此會有底下這個「來源 目前評估為錯誤。您要繼續嗎?」的提示對話盒出現,這個地方不用管它,直接點擊〔是〕即可。

excel_step_combo-7.jpg

Step 5. 最後再來到我們輸入資料的表單中,你就會發現到「品名」這欄位所有的儲存格,已經能根據該列的「分類」來產生出不同的「外拉式選單」了。

excel_step_combo-8.jpg

如果您要設定「三層」的下拉式選單,第三層的作法也和第二層是一樣的。

你可能對這些內容也有興趣:

Previous

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

Next

Excel深度教學:VLOOKUP查表函數及比對中文字串時發生錯誤的問題

64 Comments

  1. NINE

    超清楚啊已成功,感謝喔!

  2. changyang319

    這不就是我這篇文章的教學內容嗎?你都沒看嗎?

  3. 有點困惑的學生

    老師,請問如何製作有條件式的下拉式選單呢?
    想設計一個A選單選擇A時,B選單有A1、A2或A3可以選擇
    當A選單選擇B時,B選單則有B1、B2或B3可以選擇
    請問公式該如何設計呢?

  4. Jerry

    請問如果在第三層我要增加個價格,但是在第二層選好產品~第三層自己跑出價格~要如何去做?

    謝謝~

  5. changyang319

    名稱就是不能有空白,這個是沒辦法的,建議在命名時,以"_"底線符號來代替空白字元。

  6. Kvin

    你好
    如使用名稱管理員設置名稱, 宇元之間是否不可以有空格?
    如想要有空格, 需要怎樣輸入??

    謝謝

  7. Julie

    謝謝您
    非常適用

  8. changyang319

    嗯嗯。

  9. 陳佩汶

    您好~~

    這個功能真的很好用耶~
    一直很困擾多階下拉式選單設定的問題來著
    謝謝您的分享!!!
    受用無窮

  10. changyang319

    記得寄給我喔。

  11. changyang319

    你能把你製作的「半成品」Excel檔案寄給我嗎?我可以幫你看一下問題在哪。

    寄到
    changyang319@gmail.com

  12. Jin Xie

    我找到如何做了~~XD
    原來已經有文章了 ><

  13. Jin Xie

    你好~~這個方式超級受用
    另外想請問,我是否可以指定當輸入A後會自動帶入B的方式呢?
    例如當我選擇某菜單名稱後,後面會自動帶入它的價格
    謝謝您~

  14. Aristo

    要煩請教您指導我方向了!!謝謝

  15. Aristo

    我也是一樣文章影片看了很多次,也都照步驟走,不知為何就是無法讓第二層以後的下拉有東西,全是空的!我的分類建檔也都是中文沒任何英數符號,動作不斷重複做幾次都這樣...而且我也沒有讓他用絕對位置,都是相對位置(Ex.=INDIRECT(D2),而不是=INDIRECT($D$2)皆無效)拜託救救我~

  16. 訪客

    受益良多,謝謝!!!

  17. changyang319

    是的,不過這是要在選取「整欄」時,才輸入C1,若你是「單一」設定某個儲存格時,例如你設定的是C2儲存格時,你就要用 =indirect(C2)

  18. 訪客

    大大,很感謝您的教學,不好意思我可以再請教您第三層的做法嗎@@? 如果做到第三層,是不是在(B1)這部分要改成(C1)呢?

  19. changyang319

    我認為光只靠這公式,沒辦法做到這個功能。

  20. 訪客

    您好:
    想請教您,如果在來源填入=INDIRECT()之後,還可以再加入其他選項或刪除某一選項嗎?
    謝謝您。

  21. changyang319

    如果第二層都是一樣的,那就沒有階層的關係,一、二層都各自用「資料驗證」的方式來做就好了。

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

  22. Yy

    版主您好!
    這幾天剛好要用Excel做個記賬本,中途遇到了這個建立二三層下拉的問題。
    真幸運找到這篇文章,著實幫了我一個大忙!
    非常感謝您!

    但我還遇到另一個問題,如下:
    第一層:A 銀行 –> 第二層:現金存入、提款、轉賬。。。等等
    第一層:B 銀行 –> 第二層:現金存入、提款、轉賬。。。等等
    (還有好幾個銀行戶口、電子錢包等等。。。)

    以上情況是第一層是不同銀行,但是第二層的選項是一樣的,除了替每一個銀行建立不同的名稱管理外,還有什麽辦法嗎?

    感激不盡。。。

  23. changyang319

    不客氣,也謝謝您的造訪。

  24. 幼稚鬼

    非常感謝你的解說 很實用~

  25. changyang319

    有,就是要學VBA來寫程式。

  26. changyang319

    抱歉,不寫VBA的話,我好像也想不出什麼辦法。

  27. changyang319

    OK OK

  28. changyang319

    這跟智商有關,不能怪你。

  29. changyang319

    一定是你沒弄好的。

  30. 黃信瑋

    謝謝您的詳細教學
    請問大大有我現在正在做一個表單可是希望我選完清單後下面會自己出現我要的內容是否有方法
    就是我選單選這個下面的空格就會直接顯示我預設的內容

  31. KKIOU

    板主您好, 請問如果我將主選單內物件A切換至物件B, 可否能令子選單已選物件自動清空。

    先謝謝您的指教!

  32. 很好的教學

    很快就找到我想要的,至於進階的 等以後再說囉 讚!!

  33. 幹你娘騙肖維

    超爛的 根本就沒東西 騙人的啦

  34. 訪客

    打=INDIRECT(B1)之後根本就沒東西 zzzz

  35. changyang319

    不客氣喔。

  36. lsl615080

    謝謝˙ˇ˙

  37. changyang319

    謝謝喔。

  38. changyang319

    如果是用寫VBA程式,就比較容易達到,但如果是用現有功能,我目前也沒有想到要怎麼做,抱歉喔。

  39. 訪客

    非常實用而且簡單的教學

  40. rainbow

    你的解說示範真受用,非常感謝.
    我有個疑問,如果我的下拉式選單清單是001-5cm 002-10cm ,請問我能設定選擇後只顯示001嗎?想套用在材料編碼上.謝謝.

  41. Toby

    謝謝您的詳細教學,
    目前在實作上和你的範例相似,有一問題想求解答,假設:1.使用者分類選「辦公用品」。2.品名選「美工刀」。
    此時,使用者將分類選「書寫用品」,然後忘記重選品名,這樣資料就呈現「書寫用品」、「美工刀」了,
    請問有辦法防呆嗎?謝謝。

  42. changyang319

    「數量」這個欄位,最好不要做成選單,因為數量會變動,除非你數量永遠都是一個。

    所以,要做到不同的品項,可以運算出不同的價格時,就要運用這個「INDIRECT」公式來查表,再用「數量」和查出來的「價格」欄位,相乘出來就是你要的值。

  43. Ruby

    您好~您的文章幫了我一個大忙! 但我想請問 如果我今天想讓它也有"價格功能"有辦法嗎?

    例如在E欄位為總金額表,今天我選紙製品>筆記本>數量1,E欄會出會$35,但我改選紙製品>信封>數量1,E欄位會變更為$10。

    非常謝謝您

  44. changyang319

    不客氣。

  45. 帥勝

    受益匪淺…謝謝

  46. Christine

    很受用 已設定成功 很好用 感謝
    另外請問可以用ㄧ個欄位的值(例如品牌)動態多個欄位(依品牌顯示不同下拉選單)的選項嗎? 感激不盡

  47. changyang319

    謝謝您的誇獎喔。
    另外Kubee所提供的訊息,我也沒有試過,也不曉得是否真的會有這樣的情況?當然我是覺得應該是不會有這個問題啦。

  48. 謝謝版主!
    對於用了很久Excel的我,現才知道可以用INDIRECT()這個秘技,版主真的很厲害呵,讚!
    至於Kubee的提供也很棒,這可能是Excel的bug。令我想起,以前在Excel及Word也遇過類似developer的bug。版主如有相類經驗,希望有機會可以另題分享。謝謝!

  49. changyang319

    謝謝您提供的經驗談。

  50. Kubee

    哈囉,小弟在此分享一個常見錯誤,
    Q:為什麼有時候第二層會無法成功呢?
    A:因為"分類"的定義名稱不可以有「英數字」,品名可以沒關係 :))

    例如分類打:書寫用品 辦公用品 紙製品A
    這時候因為第三個紙製品多了一個A 所以等等會導致這個分類的第二層無法選擇 而前兩個仍然可以正常使用 (PS….A紙製品、紙A製品、紙製品4,凡是出現任何英數字符號的都不可以)

  51. changyang319

    你需要把你設定了什麼說明清楚一點,這一定都是有設定錯誤,要不然是不會有這樣的情況的。
    或是把檔案寄到 changyang319@gmail.com 我若有時間的話,我再看一下。

  52. changyang319

    不客氣哦。

  53. Kim

    你好,謝謝你的教學,非常的仔細。
    但我遇到一個問題想要請教,依照你上方的方式去設定,一個設定卻同時出現了兩種狀況如下:

    正常狀況:點選書寫工具–>第二層出現原子筆, 鉛筆, 筆芯選項
    異常狀況:點選紙製品–>點選下拉式倒三角鍵–>跑不出任何選項
    正常狀況:點選辦公用具–>第二層出現美工刀, 剪刀, 刀片

    想請問一下遇到此異常狀況該怎麼解決呢?

  54. 荔枝

    很受用,感謝~!!

  55. changyang319

    這我就不清楚了,因為如果定義名稱時是用「書寫用品清單」的話,你就一定要用相同的名稱,若沒有用到相同的名稱,當然就一定無法顯示的。

  56. Ric

    Caron遇到的問題可能是在定義名稱時,給了不同的名稱所導致
    ex: excel第一欄用"書寫用品",定義名稱時用"書寫用品清單",這樣就會抓不到。

  57. changyang319

    謝謝您,然後另外Step 4的問題,那裡是因為我點選了一整個「Column欄」,所以設定時要從B1開始,如果我是選擇B2再設定公式的話,就是要如你所說的從B2開始。

  58. 教的很清楚,不過STEP4那裡的B1好像應該換成B2。

  59. 訪客

    跟着指示做了,但在做第二層下拉式選單卻不在左邊第一層選單的旁邊,為甚麼會這麼和如果解決?謝謝﹗
    P.S. 分享很有用,加油唷﹗

  60. changyang319

    謝謝您的誇獎。

  61. fanny 劉

    很仔細的教學,讚!感謝你喔,又多學了一樣可運用!

  62. changyang319

    因為我用可以,你用就不行,一定有什麼地方有做錯,所以可以把你所做的步驟、內容,詳細提供出來,要不然我也是束手無策哦。

  63. CARON

    我依此方法做第二層下拉選單,但並無出現第2層的值,請問如何解決

發佈留言

Powered by WordPress & Theme by Anders Norén