如何在Excel 2013中实现基于产品与店铺选择的动态位置下拉列表？

Great question—your bottlenecks with array formulas and unsupported custom functions are super common when dealing with dynamic, large datasets in Excel. Let’s walk through three actionable solutions tailored to your skills with Power Query, VBA, and Excel formulas, each addressing the scalability and data validation limitations you’ve hit.

方案1：Power Query + 动态命名范围（最适合动态更新的数据） #

This approach leverages Power Query’s ability to handle dynamic datasets efficiently, then pairs it with named ranges to feed valid, filtered positions into data validation. It’s low-maintenance since it auto-updates when your Nav server data refreshes.

Step-by-Step Setup: #

1. Convert raw data to an Excel Table: Select your imported product data, press Ctrl+T, check "My table has headers", and name it tblProductLocations (this makes it dynamic for Power Query).
2. Clean and structure data with Power Query:
   • Go to Data > From Table/Range to open the Power Query Editor.
   • Use the Group By feature (under Transform):
     • Group by PRODUCT CODE and STORE IN WHICH IT CAN BE LOCATED.
     • For the aggregation, select "All Rows" and name the column PositionRows.
   • Add a custom column to extract the unique positions from the grouped rows:

     = List.Distinct([PositionRows][POSITION INSIDE THE STORE])
     

   • Remove the PositionRows column, then close and load the cleaned data to a new worksheet (name it LookupTable). Convert this new data to another Excel Table named tblLookup.
3. Create a dynamic named range:
   • Go to Formulas > Define Name. Name it AvailablePositions, then use this formula (adjust sheet names to match your setup):

     =XLOOKUP(1, (tblLookup[PRODUCT CODE]=SalesReport!$A2)*(tblLookup[STORE IN WHICH IT CAN BE LOCATED]=SalesReport!$B2), tblLookup[Custom], "")
     

   • Note: Custom is the name of the column you created in Power Query with the distinct positions.
4. Set up data validation:
   • Select your green position cells (e.g., C2:C1000 on the SalesReport sheet).
   • Go to Data > Data Validation > Allow: Sequence, then enter =AvailablePositions as the source.

方案2：VBA Worksheet Change Event（即时响应，高度自定义） #

Since you’re comfortable with VBA, this method bypasses data validation’s formula limitations by dynamically updating the validation list whenever a product or store is selected. It’s fast even with 1500 rows because it uses Excel’s native filtering engine.

Step-by-Step Setup: #

1. Open the VBA Editor (Alt+F11), then double-click your sales report worksheet (e.g., SalesReport) in the Project Explorer.
2. Paste this code (adjust range references to match your actual cell positions):

   Private Sub Worksheet_Change(ByVal Target As Range)
       Dim wsData As Worksheet
       Dim rngProduct As Range, rngStore As Range, rngPosition As Range
       Dim productCode As String, storeName As String
       Dim positionList As Variant
       
       ' Define your target cell ranges
       Set rngProduct = Me.Range("A2:A1000") ' Product code cells
       Set rngStore = Me.Range("B2:B1000") ' Store selection cells
       Set rngPosition = Me.Range("C2:C1000") ' Position dropdown cells
       Set wsData = ThisWorkbook.Worksheets("ProductData") ' Raw data sheet
       
       ' Only trigger if product or store cells are changed
       If Not Intersect(Target, Union(rngProduct, rngStore)) Is Nothing Then
           productCode = Me.Cells(Target.Row, rngProduct.Column).Value
           storeName = Me.Cells(Target.Row, rngStore.Column).Value
           
           ' Clear existing validation and content if inputs are empty
           If productCode = "" Or storeName = "" Then
               Me.Cells(Target.Row, rngPosition.Column).ClearContents
               On Error Resume Next
               Me.Cells(Target.Row, rngPosition.Column).Validation.Delete
               On Error GoTo 0
               Exit Sub
           End If
           
           ' Filter positions matching the product and store
           positionList = Application.Filter( _
               wsData.Range("tblProductLocations[POSITION INSIDE THE STORE]").Value, _
               (wsData.Range("tblProductLocations[PRODUCT CODE]").Value = productCode) * _
               (wsData.Range("tblProductLocations[STORE IN WHICH IT CAN BE LOCATED]").Value = storeName), _
               True)
           
           ' Update data validation
           On Error Resume Next
           Me.Cells(Target.Row, rngPosition.Column).Validation.Delete
           On Error GoTo 0
           
           If IsArray(positionList) Then
               Me.Cells(Target.Row, rngPosition.Column).Validation.Add _
                   Type:=xlValidateList, _
                   AlertStyle:=xlValidAlertStop, _
                   Formula1:=Join(positionList, ",")
           ElseIf positionList <> "" Then
               ' Handle single matching position
               Me.Cells(Target.Row, rngPosition.Column).Validation.Add _
                   Type:=xlValidateList, _
                   AlertStyle:=xlValidAlertStop, _
                   Formula1:=positionList
           End If
       End If
   End Sub
   

3. Save your workbook as a .xlsm (macro-enabled) file.

方案3：Excel 365/2021 Dynamic Array Formula（No-Code, Minimal Setup） #

If you’re using Excel 365 or 2021, you can skip Power Query/VBA entirely by using the FILTER function directly in data validation. This is the simplest solution and leverages Excel’s optimized dynamic array engine.

Step-by-Step Setup: #

1. Ensure your raw product data is converted to an Excel Table named tblProductLocations.
2. Select your green position cells (e.g., C2:C1000).
3. Go to Data > Data Validation > Allow: Sequence.
4. Enter this formula as the source (adjust cell references to match your product/store columns):

   =FILTER(tblProductLocations[POSITION INSIDE THE STORE], (tblProductLocations[PRODUCT CODE]=A2)*(tblProductLocations[STORE IN WHICH IT CAN BE LOCATED]=B2), "")
   

5. Check the "Ignore blank" box if you want to allow empty selections when no matches exist.

Which Solution Should You Choose? #

• 方案1: Best if your data refreshes frequently and you prefer a no-macro, maintainable setup.
• 方案2: Ideal if you need custom logic (e.g., error messages for no matches) or use an older Excel version.
• 方案3: Perfect for Excel 365/2021 users who want a zero-code, fast solution.

内容的提问来源于stack exchange，提问作者Dave