如何在 Excel 中從下拉列表選擇值後自動填充其他單元格:綜合指南
在使用 Excel 時,自動化數據輸入可以顯著提高生產力。一個常見的任務是在從下拉列表中選擇值後自動填充相關數據。本指南探討了四種全面的方法,從 Excel 原生函數到 VBA 和第三方工具如 Kutools。
方法 3:使用 Kutools for Excel 自動填充
首先:創建下拉列表
在實施任何自動填充方法之前,您需要先設置一個下拉列表。此下拉列表作為填充相關單元格的觸發器。
步驟:
步驟 1. 準備數據源。
步驟 2. 創建下拉列表。
進入您希望放置下拉列表的單元格(例如,Sheet1!D2)
導航至 數據 > 資料驗證 > 資料驗證。
在「資料驗證」對話框中,選擇「允許」部分中的「列表」,並選擇數據源。點擊確定。
一旦您的下拉列表準備好,您可以繼續實施以下任意一種自動填充方法。
方法 1:使用 VLOOKUP 函數自動填充
VLOOKUP 是 Excel 中最常用於數據檢索的函數之一。當與下拉列表配對時,它可以快速從參考表中獲取相關數據。
步驟:
在下拉列表相鄰的單元格(例如,E2),輸入:
🔓 公式解釋:
- 在 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),輸入:
🔓 公式解釋:
- 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),輸入:
步驟 5. 按 Enter 鍵。
✨ 注意事項
- 需要啟用宏的工作簿(.xlsm)
常見問題
問1:如果我的數據範圍經常改變怎麼辦?
使用命名範圍或動態表格來保持引用。
問2:我可以使用 VLOOKUP 進行左向查找嗎?
不可以,考慮使用 INDEX+MATCH 或 Kutools。
問3:Kutools 是否安全使用?
是的,它被廣泛使用和信任,但請始終從官方網站下載。
問4:VBA 是否適用於所有 Excel 版本?
大多數桌面版本都支持 VBA,但默認情況下它是禁用的,且不支持 Excel Online。
問5:Kutools 是否免費使用?
Kutools for Excel 不是一個完全免費的工具,但它提供免費試用期,之後是一次性購買選項:
- 30 天全功能免費試用——無需信用卡。
- 單用戶永久許可:約 US $49,包括 2 年免費更新和支持。
- 在 2 年支持期結束後,您可以繼續無限期使用您擁有的版本——只是不再有進一步的更新。
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!