跳到主要內容

三種類型的多列下拉列表——分步指南


相關視頻


獲取示例文件:

點擊下載示例文件


製作基於多列的從屬下拉列表

如下面的 GIF 圖像所示,您要為各大洲創建一個主下拉列表,一個包含基於主下拉列表中所選大洲的國家/地區的輔助下拉列表,然後是第三個下拉列表包含基於在二級下拉列表中選擇的國家/地區的城市的列表。 本節中的方法可以幫助您完成此任務。

使用公式製作基於多列的依賴下拉列表

第 1 步:創建主下拉列表

1.選擇要插入下拉列表的單元格(這裡我選擇G9:G13),轉到 數據 標籤,點擊 數據驗證 > 數據驗證.

2。 在裡面 數據驗證 對話框,請進行以下配置。

1)點擊 設定 標籤;
2)選擇 名單 ,在 下拉列表;
3) 點擊 資源 框,在下拉列表中選擇包含要顯示的大陸的單元格;
4)點擊 OK 按鈕。 看截圖:

第 2 步:創建二級下拉列表

1. 選擇包含要在二級下拉列表中顯示的項目的整個範圍。 前往 公式 選項卡,然後單擊 從選擇創建.

2。 在裡面 通過選擇創建名稱 對話框中,僅選中 第一排 框,然後單擊 OK 按鈕。

3. 選擇要插入二級下拉列表的單元格,轉到 數據 標籤,點擊 數據驗證 > 數據驗證.

4。 在裡面 數據驗證 對話框,您需要:

1) 留在 設定 標籤;
2)選擇 名單 ,在 下拉列表;
3)在公式中輸入以下公式 資源 框。
=INDIRECT(SUBSTITUTE(G9," ","_"))
當: G9 是主下拉列表單元格的第一個單元格。
4.4)點擊 OK 按鈕。

5. 選擇這個下拉列表單元格,拖動它的 自動填充句柄 向下將其應用於同一列中的其他單元格。

二級下拉列表現已完成。 當您在主下拉列表中選擇一個大洲時,次級下拉列表中僅顯示該大洲下的國家。

第三步:創建第三個下拉列表

1. 選擇包含要在第三個下拉列表中顯示的值的整個範圍。 前往 公式 選項卡,然後單擊 從選擇創建.

2。 在裡面 通過選擇創建名稱 對話框中,僅選中 第一排 框,然後單擊 OK 按鈕。

3. 選擇要插入第三個下拉列表的單元格,轉到 數據 標籤,點擊 數據驗證 > 數據驗證.

4。 在裡面 數據驗證 對話框,您需要:

1) 留在 設定 標籤;
2)選擇 名單 ,在 下拉列表;
3)在公式中輸入以下公式 資源 框。
=INDIRECT(SUBSTITUTE(H9," ","_"))
當: H9 是輔助下拉列表單元格的第一個單元格。
4.4)點擊 OK 按鈕。

5. 選擇這個下拉列表單元格,拖動它的 自動填充句柄 向下將其應用於同一列中的其他單元格。

第三個包含城市的下拉列表現已完成。 當您在二級下拉列表中選擇一個國家時,在第三個下拉列表中只會顯示該國家下的城市。

上面的方法對於一些用戶來說可能比較麻煩,如果你想要一個更高效更直接的解決方案,下面的方法只需點擊幾下就可以實現。

單擊幾下即可使用 Kutools for Excel 創建基於多列的相關下拉列表

下面的 GIF 圖片顯示了 動態下拉列表 的特點 Excel的Kutools.

如您所見,只需單擊幾下即可完成整個操作。 你只需要:

1.啟用功能;
2. 選擇你需要的模式: 2水平 or 3-5級下拉列表;
3. 選擇你需要創建依賴的下拉列表依據的列;
4. 選擇一個輸出範圍。

上面的GIF圖只是演示了製作2級下拉列表的步驟。 如果你想做一個超過2級的下拉列表, 點擊這裡了解更多 。 或 下載 30 天免費試用版.


在 Excel 的下拉列表中進行多項選擇

本節提供兩種方法來幫助您在 Excel 的下拉列表中進行多項選擇。

使用 VBA 代碼在 Excel 下拉列表中進行多項選擇

下面的 VBA 腳本可以幫助在 Excel 的下拉列表中進行多項選擇而不重複。 請按如下方式操作。

第 1 步:打開 VBA 代碼編輯器並複制代碼

1.轉到工作表選項卡,右鍵單擊它並選擇 查看代碼 從右鍵單擊菜單中。

2.然後 Microsoft Visual Basic for Applications 彈出窗口,需要將下面的VBA代碼複製到 表(代碼) 編輯器。

VBA代碼:允許在下拉列表中進行多項選擇而不重複

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub
第 2 步:測試代碼

粘貼代碼後,按 其他 + 關閉鍵 可視化編輯器 並返回到工作表。

保養竅門:此代碼適用於當前工作表中的所有下拉列表。 只需單擊包含下拉列表的單元格,從下拉列表中一項一項地選擇項目以測試它是否有效。

備註:如果你想在一個下拉列表中允許多選,再次從下拉中選擇時刪除現有的項目,你也可以應用一個VBA代碼來實現: 單擊此處按照教程逐步操作

單擊幾下即可使用 Kutools for Excel 在 Excel 下拉列表中進行多項選擇

VBA 代碼有很多限制。 如果您不熟悉 VBA 腳本,則很難修改代碼以滿足您的需要。 例如,更改工作範圍或項目分隔符。 這裡推荐一個強大的功能—— 多選下拉列表 可以幫助您輕鬆處理此任務。 您可以輕鬆地指定一個範圍來執行該功能,並將分隔符更改為您喜歡的任何一個。

正如您在上面的 GIF 圖像中看到的,整個操作只需點擊幾下即可完成。 假設您已經在工作表中插入了數據驗證下拉列表,現在您只需要:

1.啟用這個 多選下拉列表 特徵;
2.指定範圍(可以指定 範圍, 當前工作簿, 當前工作表 or 另一個特定的工作表 根據您的需要選擇當前工作簿);
3.指定分隔符分隔多选和文字顯示方向 (橫向 or 垂直);

提示:設置完成後,當您點擊包含下拉列表的單元格時,會出現一個包含“+“和”-“標誌將顯示在右側。 只需點擊“+" 號將相應的項目添加到單元格中,然後單擊 "-" 符號將其從單元格中刪除。

點擊 這裡 要了解有關此功能的更多信息,或 下載 30 天免費試用版.


在下拉列表中顯示多列

如下面的屏幕截圖所示,本節將向您展示如何在下拉列表中顯示多個列。

默認情況下,數據驗證下拉列表僅顯示一列項目。 要在下拉列表中顯示多個列,我們建議使用組合框(ActiveX 控件)而不是數據驗證下拉列表。

步驟 1:插入組合框(ActiveX 控件)

1。 轉到 開發者 標籤,點擊 插入 > 組合框(ActiveX控件).

提示: 如果 開發者 選項卡未顯示在功能區中,您可以按照本教程中的步驟操作“顯示開發人員標籤” 來展示它。

2.然後畫一個 組合框 在要顯示下拉列表的單元格中。

第 2 步:更改組合框的屬性

1. 右鍵單擊組合框然後選擇 氟化鈉性能 從上下文菜單。

2。 在裡面 氟化鈉性能 對話框,請進行以下配置。

1)在 列數 字段,輸入一個數字,表示您要在下拉列表中顯示的列數;
2)在 列寬 字段,請定義每列的寬度。 這裡我定義每列的寬度為 80分;100分;80分;80分;80分;
3)在 鏈接單元 字段,指定一個單元格以輸出與您在下拉列表中選擇的值相同的值。 該單元格將用於以下步驟;
4)在 列表填充範圍 字段,在下拉列表中輸入要顯示的數據范圍。
5)在 列表寬度 字段,指定整個下拉列表的寬度。
6)關閉 氟化鈉性能 對話框。

第三步:在下拉列表中顯示指定的列

1。 在下面 開發者 選項卡,關閉 設計模式 只需點擊 設計模式 圖標。

2. 單擊組合框的箭頭,列表將展開,您可以在下拉列表中看到指定數量的列。

注意: 正如您在上面的 GIF 圖像中看到的,雖然下拉列表中顯示了多個列,但單元格中僅顯示了所選行中的第一項。 如果要顯示其他列中的項目,請繼續並應用以下公式。

第 4 步:在特定單元格中顯示其他列中的項目

保養竅門:為了從其他列返回完全相同格式的數據,您需要在以下操作之前或之後更改結果單元格的格式。 在這個例子中,我改變了單元格的格式 C11日期 格式化和更改單元格的格式 C14貨幣 提前格式化。

1. 選擇組合框下的一個單元格,輸入下面的公式,然後按 Enter 鍵獲取同一行中第二列的值。

=IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),””)

2. 要獲得第三、第四和第五列的值,請一一應用以下公式。

=IFERROR(VLOOKUP(B1,B3:F6,3,FALSE),””)
=IFERROR(VLOOKUP(B1,B3:F6,4,FALSE),””)
=IFERROR(VLOOKUP(B1,B3:F6,5,FALSE),””)

筆記:

取第一個公式 =IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),””) 舉個例子,

1) B1 是您在“屬性”對話框中指定為 LinkedCell 的單元格。
2) 號碼 2 表示表格區域“B3:F6”的第二列。
3) VLOOKUP 這裡的函數搜索 B1 中的值並返回範圍 B3:F6 中第二列中的值。
4) 錯誤 處理 VLOOKUP 函數中的錯誤。 如果 VLOOKUP 函數的計算結果為 #N/A 錯誤,則 IFERROR 函數會將錯誤返回為 null。

相關文章

在Excel下拉列表中鍵入時自動完成
如果您有一個包含大值的數據驗證下拉列表,則需要在列表中向下滾動以查找合適的列表,或直接在列錶框中鍵入整個單詞。 如果在下拉列表中鍵入第一個字母時有允許自動完成的方法,一切將變得更加容易。 本教程提供了解決問題的方法。

在Excel中從另一個工作簿創建下拉列表
在工作簿中的工作表之間創建數據驗證下拉列表非常容易。 但是,如果數據驗證所需的列表數據位於另一個工作簿中,您將怎麼辦? 在本教程中,您將詳細了解如何從Excel中的另一個工作簿創建拖放列表。

在Excel中創建可搜索的下拉列表
對於具有眾多價值的下拉列表,找到合適的價值並非易事。 以前,我們已經介紹了一種在下拉框中輸入第一個字母時自動完成下拉列表的方法。 除了自動完成功能之外,您還可以使下拉列表可搜索,以提高在下拉列表中查找適當值時的工作效率。 為了使下拉列表可搜索,請嘗試本教程中的方法。

在Excel下拉列表中選擇值時自動填充其他單元格
假設您已經根據單元格區域B8:B14中的值創建了一個下拉列表。 在下拉列表中選擇任何值時,都希望在選定單元格中自動填充單元格範圍C8:C14中的相應值。 為了解決該問題,本教程中的方法將對您有所幫助。

下拉列表的更多教程...

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!