您的一個「讚」,就能讓我高興一整天!
  • 若文章中的教學解決不了您的問題,請直接找電腦公司會比較快。
  • 原本我不寫廠商的邀約文,一方面真的是很忙,另一方面我也不是什麼超高人氣部落格,但還是許多廠商會寄信過來邀約。因此,我現在決定如果廠商所提供的產品,我覺得很有用或有趣的,我會「免費」接案( changyang319@gmail.com ),但寫好寫壞,還有截稿的時間不要限制我喔。
電腦重灌 還原鍵製作還原光碟製作修復光碟WinXPWin 7(英文)Win7(中文)Windows 8重灌前準備驅動程式安裝更新Hotfix學習DOS檢查硬碟壞軌測試記憶體如何進入BIOS開機順序/電腦溫度安全模式
常見問題 Wi-Fi不能上網螢幕變黑開機到一半卡住主機不斷重開機喇叭沒有聲音麥克風沒有聲音主機風扇很吵主機自動斷電光碟機退不出來
選購經驗 筆記型電腦平板電腦桌上型電腦印表機分享器雙螢幕LCD液晶螢幕滑鼠UPS不斷電系統電視卡無線網卡MacBook Air平板VS筆電
辦公文書 Word必犯錯誤Word合併列印Excel樞紐分析表備份Outlook郵件FTP軟體操作Word打一半當機設定Outlook收Gmail
IP分享器 無線WiFi延伸(WDS)串接分享器另類分享器接法測Wi-Fi訊號頻道DDNS(D-Link)DDNS(No-IP)

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

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

, , ,

唐先生 發表在 痞客邦 PIXNET 留言(33) 人氣()


留言列表 (33)

發表留言
  • CARON
  • 我依此方法做第二層下拉選單,但並無出現第2層的值,請問如何解決
  • 因為我用可以,你用就不行,一定有什麼地方有做錯,所以可以把你所做的步驟、內容,詳細提供出來,要不然我也是束手無策哦。

    唐先生 於 2014/02/24 14:17 回覆

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

  • 謝謝您的誇獎。

    唐先生 於 2014/03/08 14:41 回覆

  • 悄悄話
  • 澤
  • 教的很清楚,不過STEP4那裡的B1好像應該換成B2。
  • 謝謝您,然後另外Step 4的問題,那裡是因為我點選了一整個「Column欄」,所以設定時要從B1開始,如果我是選擇B2再設定公式的話,就是要如你所說的從B2開始。

    唐先生 於 2014/05/23 06:25 回覆

  • Ric
  • Caron遇到的問題可能是在定義名稱時,給了不同的名稱所導致
    ex: excel第一欄用"書寫用品",定義名稱時用"書寫用品清單",這樣就會抓不到。
  • 這我就不清楚了,因為如果定義名稱時是用「書寫用品清單」的話,你就一定要用相同的名稱,若沒有用到相同的名稱,當然就一定無法顯示的。

    唐先生 於 2014/07/19 23:29 回覆

  • 荔枝
  • 很受用,感謝~!!
  • 不客氣哦。

    唐先生 於 2014/09/04 23:13 回覆

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

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

    想請問一下遇到此異常狀況該怎麼解決呢?
  • 你需要把你設定了什麼說明清楚一點,這一定都是有設定錯誤,要不然是不會有這樣的情況的。
    或是把檔案寄到 changyang319@gmail.com 我若有時間的話,我再看一下。

    唐先生 於 2014/09/08 02:02 回覆

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

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

    唐先生 於 2014/09/25 23:34 回覆

  • 猴
  • 謝謝版主!
    對於用了很久Excel的我,現才知道可以用INDIRECT()這個秘技,版主真的很厲害呵,讚!
    至於Kubee的提供也很棒,這可能是Excel的bug。令我想起,以前在Excel及Word也遇過類似developer的bug。版主如有相類經驗,希望有機會可以另題分享。謝謝!
  • 謝謝您的誇獎喔。
    另外Kubee所提供的訊息,我也沒有試過,也不曉得是否真的會有這樣的情況?當然我是覺得應該是不會有這個問題啦。

    唐先生 於 2014/10/15 19:11 回覆

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

    唐先生 於 2015/07/02 15:15 回覆

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

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

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

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

    唐先生 於 2015/08/29 11:39 回覆

  • Toby
  • 謝謝您的詳細教學,
    目前在實作上和你的範例相似,有一問題想求解答,假設:1.使用者分類選「辦公用品」。2.品名選「美工刀」。
    此時,使用者將分類選「書寫用品」,然後忘記重選品名,這樣資料就呈現「書寫用品」、「美工刀」了,
    請問有辦法防呆嗎?謝謝。
  • rainbow
  • 你的解說示範真受用,非常感謝.
    我有個疑問,如果我的下拉式選單清單是001-5cm 002-10cm ,請問我能設定選擇後只顯示001嗎?想套用在材料編碼上.謝謝.
  • 如果是用寫VBA程式,就比較容易達到,但如果是用現有功能,我目前也沒有想到要怎麼做,抱歉喔。

    唐先生 於 2016/01/01 09:50 回覆

  • 訪客
  • 非常實用而且簡單的教學
  • 謝謝喔。

    唐先生 於 2016/01/01 11:17 回覆

  • lsl615080
  • 謝謝˙ˇ˙
  • 不客氣喔。

    唐先生 於 2016/01/12 11:55 回覆

  • 訪客
  • 打=INDIRECT(B1)之後根本就沒東西 zzzz
  • 一定是你沒弄好的。

    唐先生 於 2016/02/25 21:53 回覆

  • 幹你娘騙肖維
  • 超爛的 根本就沒東西 騙人的啦
  • 這跟智商有關,不能怪你。

    唐先生 於 2016/02/25 21:59 回覆

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

    唐先生 於 2016/02/25 21:59 回覆

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

    先謝謝您的指教!
  • 抱歉,不寫VBA的話,我好像也想不出什麼辦法。

    唐先生 於 2016/03/01 19:38 回覆

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

    唐先生 於 2016/03/02 18:28 回覆

  • 幼稚鬼
  • 非常感謝你的解說 很實用~
  • 不客氣,也謝謝您的造訪。

    唐先生 於 2016/03/31 19:38 回覆

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

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

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

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

    http://changyang319.pixnet.net/blog/post/38868721

    唐先生 於 2016/05/05 14:42 回覆

  • 訪客
  • 您好:
    想請教您,如果在來源填入=INDIRECT()之後,還可以再加入其他選項或刪除某一選項嗎?
    謝謝您。
  • 我認為光只靠這公式,沒辦法做到這個功能。

    唐先生 於 2016/06/30 09:41 回覆

  • 訪客
  • 大大,很感謝您的教學,不好意思我可以再請教您第三層的做法嗎@@? 如果做到第三層,是不是在(B1)這部分要改成(C1)呢?
  • 是的,不過這是要在選取「整欄」時,才輸入C1,若你是「單一」設定某個儲存格時,例如你設定的是C2儲存格時,你就要用 =indirect(C2)

    唐先生 於 2016/07/29 20:25 回覆

  • 悄悄話
  • Aristo
  • 我也是一樣文章影片看了很多次,也都照步驟走,不知為何就是無法讓第二層以後的下拉有東西,全是空的!我的分類建檔也都是中文沒任何英數符號,動作不斷重複做幾次都這樣...而且我也沒有讓他用絕對位置,都是相對位置(Ex.=INDIRECT(D2),而不是=INDIRECT($D$2)皆無效)拜託救救我~
  • 你能把你製作的「半成品」Excel檔案寄給我嗎?我可以幫你看一下問題在哪。

    寄到
    changyang319@gmail.com

    唐先生 於 2016/09/02 16:18 回覆

  • Aristo
  • 要煩請教您指導我方向了!!謝謝
  • 記得寄給我喔。

    唐先生 於 2016/09/02 16:18 回覆

  • Jin Xie
  • 你好~~這個方式超級受用
    另外想請問,我是否可以指定當輸入A後會自動帶入B的方式呢?
    例如當我選擇某菜單名稱後,後面會自動帶入它的價格
    謝謝您~
  • Jin Xie
  • 我找到如何做了~~XD
    原來已經有文章了 ><
  • 嗯嗯。

    唐先生 於 2016/10/02 18:12 回覆

  • 陳佩汶
  • 您好~~

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

    唐先生 於 2016/10/02 18:57 回覆

  • Julie
  • 謝謝您
    非常適用
  • 不客氣哦。

    唐先生 於 2016/10/23 18:01 回覆

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

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

    唐先生 於 2016/10/28 10:00 回覆