如何在 Excel 中建立動態階層式清單方塊？

作者Siluvia修改日期2024-08-15

您或許熟悉如何在 Excel 中建立階層式驗證下拉清單，但該如何打造動態階層式清單方塊呢？本文將介紹一種運用 VBA 實現此功能的方法。

使用 VBA 程式碼建立動態階層式清單方塊

如下圖所示，您需要建立一個父層清單方塊，其中包含「飲料」欄位的所有唯一值；並根據父層清單方塊的選擇，在第二個清單方塊中顯示所有對應項目。以下 VBA 程式碼可協助您達成此目標，請依下列步驟操作。

動態層級式清單方塊

1. 首先，您需從「飲料」欄位提取所有唯一值。請選取一個空白儲存格，將陣列公式 =IFERROR(INDEX($A$2:$A$11, MATCH(0,COUNTIF($J$1:J1, $A$2:$A$11), 0)),"") 輸入至編輯欄，然後按下 Ctrl+Shift+Enter 鍵。接著拖曳填滿控點，即可輕鬆取得所有唯一值！詳情請參閱下圖：

使用公式列出所有唯一值

注意：公式中的 $A$2:$A$11 為您要提取唯一值的範圍，而 J1 是公式所在儲存格上方的儲存格。

提示：若覺得公式難以記憶或操作，選擇重複／唯一儲存格工具（由）Kutools for Excel 提供）將是從欄位中快速提取所有唯一值的理想選擇！

選取包含唯一值的欄位，接著點擊 Kutools > 選取 > 選擇重複/唯一儲存格 以啟用此工具。在選擇重複/唯一儲存格對話方塊中，選取唯一值（包括第一個重複值）選項，然後點擊確定按鈕。此時欄位中所有唯一值都會被選取，請立即複製並貼上至新位置！詳情請參閱下圖：

使用 Kutools 選取唯一值

Kutools for Excel：內含超過 200 個實用 Excel 增益集，免費試用 60 天無任何限制！立即下載並免費試用！

2. 分別插入兩個清單方塊：點擊開發人員 > 插入 > 清單方塊（ActiveX 控制項）。請參閱下圖：

從開發人員索引標籤按一下清單方塊

3. 以滑鼠右鍵按一下父層清單方塊，並從快顯功能表中選取屬性。在屬性對話方塊中，將 (Name)欄位改為 Drink 或其他您需要的名稱，並將包含已提取唯一值的儲存格範圍輸入至 ListFillRange 欄位，然後關閉對話方塊。

在內容窗格中指定選項

4. 重複步驟 3，在屬性對話方塊中，將 (Name)欄位內第二個清單方塊的名稱改為 Item。

5. 以滑鼠右鍵按一下工作表標籤，從右鍵功能表中選取檢視程式碼，然後將下方 VBA 程式碼複製到程式碼視窗中。請參閱下圖：

VBA 程式碼：在 Excel 中建立動態階層式清單方塊

Dim xPreStr As String
Private Sub Drink_Click()
'Update by Extendoffice 2018/06/04
    Dim I, xRows As Long
    Dim xRg As Range
    Dim xRegStr As String
    Application.ScreenUpdating = False
    xRegStr = Me.Drink.Text
    Set xRg = Range("A2:A11")
    xRows = xRg.Rows.Count
    If xRegStr <> xPreStr Then
    Me.Item.Clear
   'Me.OtherListBoxName.Clear
    Set xRg = xRg(1)
    For I = 1 To xRows
        If xRg.Offset(I - 1).Value = xRegStr Then
            Me.Item.AddItem xRg.Offset(I - 1, 1).Value
            'Me.OtherListBoxName.AddItem xRg.Offset(I - 1, 2).Value
        End If
    Next
    xPreStr = xRegStr
    End If
    Application.ScreenUpdating = True
End Sub

注意事項：程式碼中的 Drink 與 Item 為兩個清單方塊的名稱，請依您實際的命名進行調整。

按一下「檢視程式碼」將程式碼貼到模組中

6. 按下 Alt+Q 鍵，即可關閉 Microsoft Visual Basic for Applications 視窗。

7. 點擊開發人員 > 設計模式，即可關閉設計模式。

從現在起，當您在父層清單方塊中選擇任一飲料類別（例如咖啡），第二個清單方塊便會立即顯示所有相關的咖啡項目；若選擇茶或酒類，則僅顯示對應的茶或酒類項目。請參閱下圖：

已建立動態層級式清單方塊

太神奇了！在 Excel 中使用高效 Tabs，就像 Chrome、Edge、Firefox 和 Safari 一樣！
每天為您節省 50% 的時間，並減少數千次滑鼠點擊！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多