Skip to main content

如何在 Excel 中從下拉列表選擇值後自動填充其他單元格:綜合指南

Author: Siluvia Last Modified: 2025-08-06

在使用 Excel 時,自動化數據輸入可以顯著提高生產力。一個常見的任務是在從下拉列表中選擇值後自動填充相關數據。本指南探討了四種全面的方法,從 Excel 原生函數到 VBA 和第三方工具如 Kutools。

auto-populate-a-drop-down-list

首先:創建下拉列表

方法 1:使用 VLOOKUP 函數自動填充

方法 2:使用 INDEX 和 MATCH 函數自動填充

方法 3:使用 Kutools for Excel 自動填充

方法 4:使用自定義函數自動填充

方法 4:使用自定義函數自動填充


首先:創建下拉列表

在實施任何自動填充方法之前,您需要先設置一個下拉列表。此下拉列表作為填充相關單元格的觸發器。

步驟:

步驟 1. 準備數據源。

步驟 2. 創建下拉列表。

  • 進入您希望放置下拉列表的單元格(例如,Sheet1!D2)

  • 導航至 數據 > 資料驗證 > 資料驗證

  • 在「資料驗證」對話框中,選擇「允許」部分中的「列表」,並選擇數據源。點擊確定。

    doc-select-list

    doc-drop-down-list

一旦您的下拉列表準備好,您可以繼續實施以下任意一種自動填充方法。


方法 1:使用 VLOOKUP 函數自動填充

VLOOKUP 是 Excel 中最常用於數據檢索的函數之一。當與下拉列表配對時,它可以快速從參考表中獲取相關數據。

步驟:

在下拉列表相鄰的單元格(例如,E2),輸入:

=VLOOKUP(D2,$A$2:$B$5,2,FALSE)

🔓 公式解釋:

  • 在 A2:B5 的第一列中查找 D2 的值。如果找到,則返回第 2 列(B 列)的對應值。如果未找到,則返回錯誤(#N/A)。
  • FALSE 表示必須是精確匹配。

步驟 2. 按 Enter 鍵。

✨ 注意事項

  • 如果未選擇值,請使用 IFERROR() 來隱藏錯誤:
    =VLOOKUP(D2,$A$2:$B$5,2,FALSE)
  • 無法在關鍵列左側進行搜索。

方法 2:使用 INDEX 和 MATCH 函數自動填充

INDEX 和 MATCH 是功能強大的組合,其靈活性超過了 VLOOKUP。它支持向左查找,即使列被重新排列也依然穩定。

步驟:

在下拉列表相鄰的單元格(例如,E2),輸入:

=INDEX($B$2:$B$5,MATCH(D2,$A$2:$A$5,0))

🔓 公式解釋:

  • MATCH(D2, $A$2:$A$5, 0)
    在範圍 A2:A5 中搜索 D2。0 表示精確匹配(類似於 VLOOKUP 中的 FALSE)。
    返回找到 D2 的位置(行號)。
  • INDEX($B$2:$B$5, ...)
    從 MATCH 獲取行號。
    返回 B2:B5 的對應值。

步驟 2. 按 Enter 鍵。

✨ 注意事項

  • 返回範圍(INDEX)和查找範圍(MATCH)必須按行對齊。
  • 可以向左或向右搜索。
  • 比 VLOOKUP 更耐用。

方法 3:使用 Kutools for Excel 自動填充

Kutools 提供了一個基於圖形用戶界面的方法,消除了對公式的需求。對於那些想要快速結果而無需深入研究 Excel 函數的用戶來說尤其有用。

步驟:

步驟 1. 在下拉列表相鄰的單元格(例如,E2),前往 Kutools > 公式助手 > 查找與引用 > 查找一維表

步驟 2. 選擇表格陣列、查找值和列號。點擊確定。

✨ 注意事項

  • Kutools 允許您一次將此應用於整個範圍。
  • 該工具非常適合初學者使用,並且減少手動錯誤。
  • 易於使用。
  • 無需公式。

厭倦了在 Excel 中重複的任務和複雜的公式嗎?Kutools for Excel 是您的全方位生產力提升工具!擁有超過 300 種強大功能——批量編輯、智能填充、自動過濾——讓您的工作效率提升 10 倍。立即下載,將您的 Excel 技能提升到新的水平!


方法 4:使用自定義函數自動填充

對於需要超出公式限制的動態和自動化解決方案的用戶,VBA 提供了最大的控制和邏輯定制能力。

步驟:

步驟 1. 按 Alt +F11 鍵打開 VBA 編輯器。

步驟 2. 點擊 插入 > 模塊。

步驟 3. 將下面的代碼粘貼到模塊中。

'Update by Extendoffice
Function GetProductInfo(productName As String, colIndex As Integer) As Variant
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'the sheet that the data source in

    Dim rng As Range
    Set rng = ws.Range("A2:B5") 'the range of data source

    Dim r As Range
    For Each r In rng.Rows
        If r.Cells(1, 1).Value = productName Then
            GetProductInfo = r.Cells(1, colIndex).Value
            Exit Function
        End If
    Next

    GetProductInfo = "Not found"
End Function

步驟 4. 返回工作表,在下拉列表相鄰的單元格(例如,E2),輸入:

=GetProductInfo(D2,2)

步驟 5. 按 Enter 鍵。

✨ 注意事項

  • 需要啟用宏的工作簿(.xlsm)

常見問題

問1:如果我的數據範圍經常改變怎麼辦?

使用命名範圍或動態表格來保持引用。

問2:我可以使用 VLOOKUP 進行左向查找嗎?

不可以,考慮使用 INDEX+MATCH 或 Kutools。

問3:Kutools 是否安全使用?

是的,它被廣泛使用和信任,但請始終從官方網站下載。

問4:VBA 是否適用於所有 Excel 版本?

大多數桌面版本都支持 VBA,但默認情況下它是禁用的,且不支持 Excel Online。

問5:Kutools 是否免費使用?

Kutools for Excel 不是一個完全免費的工具,但它提供免費試用期,之後是一次性購買選項:


最佳 Office 辦公效率工具

🤖 Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions
熱門功能查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入...
高級 LOOKUP多條件查找|多值查找|多表查找|模糊查找...
高級下拉列表快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ...
列管理器添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ...
精選功能網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)...
前15 大工具集12 款文本工具添加文本刪除特定字符,...)|50+ 種圖表 類型甘特圖,...)|40+ 實用 公式基於生日計算年齡,...)|19 款插入工具插入QR码按路徑插入圖片,...)|12 款轉換工具金額轉大寫匯率轉換,...)|7 款合併和分割工具高級合併行分割儲存格,...)| ...以及更多
使用 Kutools 支援你的語言——支援英語、西班牙語、德語、法語、中文及40 多種語言!

利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...


Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆

  • 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
  • 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
  • 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!