贊助廠商

點此看我的部落格所有文章列表

搜尋我的部落格文章資料

最近又有網友問到如何在 Excel 的工作表中設計二層的下拉式選單(參考下圖)?在第一層選單中可以選取類別名稱,根據第一層的名稱,在第二層選單中可以選取對應的項目名稱。

在設計上會用到「定義名稱、資料驗證、INDIRECT」等項目。

【準備工作】

1. 選取儲存格D1:F1,定義名稱為:TITLE。(由[公式/名稱管理員]中設定名稱)

image

2. 選取儲存格D1:F7。按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「數字、英文、中文」。

【設計第一層選單】

3. 選取儲存格A2:A7,選取[資料/資料工具]中的「資料驗證」。

4. 在[資料驗證]對證框中,設定[儲存格內允許]為「清單」,[來源]方塊中輸入「=TITLE」。

TITLE 名稱所定義的資料範圍為儲存格D1:F1,用以擷取類別名稱。

如此,儲存格A2:A7都可以使用下拉式清單選取類別名稱。

【設計第二層選單】

5. 選取儲存格B2:B7,選取[資料/資料工具]中的「資料驗證」。

6. 在[資料驗證]對證框中,設定[儲存格內允許]為「清單」,[來源]方塊中輸入「=INDIRECT(A2)」。

INDIRECT(A2):將儲存格A2中的文字轉換為儲存格位址。

此處雖然只用到儲存格A2,實際上 Excel 會將往下各列,自動調整為儲存格A3、A4 …。

如此,即可使用第二層的選單了。

依此設計觀念,如果需要設計第三層、第四層也是可以的。

Posted by vincent at 痞客邦 PIXNET Guestbook(8) 人氣()


留言列表 (8)

Post Comment
  • NI
  • 您好,不好意思,請問「=INDIRECT(A2)」跟EXCEL的版本有關係嗎?我的是2003版,但是我試了好多次都無法成功,不知道哪裡出了問題,麻煩你了!!
  • 訪客
  • 不好意思,想請教如果頂端列的數值會變動該如何處理,因為變動後便需要再設定一次,不然下拉式的選單就跑不出來,麻煩您了。
  • 小搞
  • 我想請問可以示範第三層嗎?
    因為第三層的清單,有點不知道該怎麼設定上去?
    再麻煩你。
  • sa597898
  • 請問我是用Excel2003.我有看過您相關教學.但是看不懂
    意思.所以才留言想請教您.
    我想設定的表格是.如在A1欄的下拉式清單如點取出4930.
    時則在B1欄的下拉式清單可點出一列.例如:a.b.c可供選取.
    如在A1欄點取選出4779.則在B1欄的下拉式清單.可點出e.f
    .g.h來供選擇取.A2.A3.A4.A5欄設定同A1欄
    請問A1.A2.A3欄..等.要怎設定.才可隨A1欄的下拉式清單
    選取例:4930時.B1欄可在下拉式清單選取a.b.c.又如A1欄如
    選取4779時.B1欄可在下拉式清單選取e.f.g.h.
    請不吝指教. 先說聲:謝謝~~
  • 我重新寫了一篇類似的做法:http://isvincent.pixnet.net/blog/post/45294110
    您參考看看。

    vincent replied in 2015/09/09 15:27

  • sa597898
  • 首先感謝您的連結教學.我有進入試著照教學操作.不過似乎我描述不清楚.作起來好像不一樣.所以我附上我MAIL的雲端硬碟連結:https://drive.google.com/file/d/0B5l76bZ5MDgRczRpVnU5c2hIQlE/view?usp=sharing這裡面有我原表格.
    我想問的是D.E.F.G.H欄是怎設定.它裡面有連結Q.R.S.T.U.V.W.X.Y欄.你試試在D欄下拉式清單點出不同品項時.E.F.G.H欄就可隨D欄品項不同而出現不同品項(藥名)供選取.但是其中有個頂項用數字4930(疾病碼)時.E.F.G.H欄就點取不出他設定要品項(藥名).為何會這樣?是Excel這項設定不能用數字?
    這是我一離職同事.他說之前是照你網路上一篇教學設定的.所以現已無人可問.況且他當初的Q.R.S.T.U.V.W.X.Y欄是用簡稱.我也想改用疾病碼(疾病碼都是數字).所以只好找您.您如有空再請幫我看看要如何設定這種表格.還有Q.R.S.T.U.V.W.X.Y欄的頂項是否可改用數字設定.也請你教導. 先說聲謝謝~
  • 因為你的Y欄標題為:HY/DM,不可以做為「名稱」,所以被自動修定為HY_DM了。
    在Excel的名稱定義中不可以是字母或底線開頭、不可以包含空格或其他無效字元、不可以是內建的名稱。
    而「/」即為無效字元。

    vincent replied in 2015/09/12 22:13

  • sa597898
  • 老師您好!我還有一問題想請教您.Excel表格中如D欄2裡的年月日是(例:104/10/10).
    那C欄2裡我要套入啥函數.才可自動顯示出前10天日期(例:104/10/01或104/10/1).

    又在C欄2裡的日期.要再套入啥函數或格式化.才會讓C欄2裡的日期.在D欄2的前10天時自動變成紅字體.

    希望老師能不厭其煩再次幫解答.則不勝感激 再次說聲 謝謝~
  • sa597898
  • 老師您好:表格已經做好了.謝謝您的指導. 感恩!

You haven’t logged in yet, please use guest status to leave message. You can also log in with above service account and leave message

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

Please input verification code on left:

Cannot understand, change to another image

請輸入驗證碼