Excel中如何对已索引匹配的数组值求和（支持ALL选项）

公式方案（优先推荐） #

通用Excel版本（兼容2019及更早） #

直接在目标单元格输入以下公式，替换W4为你的下拉选择器单元格，Z2:AZ2为索引表中存储Unit Num的目标行，'Asset Pivot'!C:D为透视表中Unit Num和Count Sum的区域：

=IF(W4="ALL",SUMPRODUCT(IFERROR(VLOOKUP(Z2:AZ2,'Asset Pivot'!C:D,2,FALSE),0)),VLOOKUP(INDEX($Z$1:$AZ$61,2,MATCH(W4,Z1:AZ1,0)),'Asset Pivot'!C:D,2,FALSE))

• 逻辑：用IF判断下拉值，若为ALL，则通过SUMPRODUCT遍历目标行所有Unit Num，结合IFERROR过滤空值/匹配失败的情况，求和对应的Count Sum；若为单个索引，则沿用你原有的匹配逻辑取值。

Excel 365/2021版本（更简洁） #

利用XLOOKUP的数组特性，公式更简洁：

=IF(W4="ALL",SUM(XLOOKUP(Z2:AZ2,'Asset Pivot'!C:C,'Asset Pivot'!D:D,0)),XLOOKUP(INDEX($Z$1:$AZ$61,2,MATCH(W4,Z1:AZ1,0)),'Asset Pivot'!C:C,'Asset Pivot'!D:D))

• 优势：XLOOKUP比VLOOKUP更稳定，无需担心查找列位置，且支持数组直接求和。

VBA方案 #

若需要更灵活的控制，可添加工作表事件代码：

1. 打开Excel，按Alt+F11进入VBA编辑器
2. 找到你的索引表工作表（左侧工程窗口），双击打开代码窗口
3. 粘贴以下代码，根据实际情况修改工作表名、目标行、单元格范围：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim pivotWs As Worksheet
    Dim targetCell As Range
    Dim lookupRow As Integer
    Dim indexCol As Range
    Dim unitNum As Variant
    Dim sumVal As Double
    
    ' 替换为你的索引表和透视表名称
    Set ws = ThisWorkbook.Worksheets("索引表")
    Set pivotWs = ThisWorkbook.Worksheets("Asset Pivot")
    ' 替换为显示结果的目标单元格
    Set targetCell = ws.Range("W5")
    ' 替换为存储Unit Num的目标行（对应你原公式中的第2行）
    lookupRow = 2
    
    If Not Intersect(Target, ws.Range("W4")) Is Nothing Then
        targetCell.ClearContents
        sumVal = 0
        
        If Target.Value = "ALL" Then
            ' 遍历目标行的Unit Num区域（Z到AZ列）
            For Each unitNum In ws.Range("Z" & lookupRow & ":AZ" & lookupRow)
                If Not IsEmpty(unitNum) Then
                    On Error Resume Next
                    sumVal = sumVal + Application.VLookup(unitNum.Value, pivotWs.Range("C:D"), 2, False)
                    On Error GoTo 0
                End If
            Next unitNum
            targetCell.Value = sumVal
        Else
            ' 查找单个索引对应的列，匹配Unit Num后取值
            Set indexCol = ws.Range("Z1:AZ1").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not indexCol Is Nothing Then
                unitNum = ws.Cells(lookupRow, indexCol.Column).Value
                targetCell.Value = Application.VLookup(unitNum, pivotWs.Range("C:D"), 2, False)
            Else
                targetCell.Value = "索引不存在"
            End If
        End If
    End If
End Sub

• 逻辑：当下拉选择器W4的值变化时，自动触发计算：选择ALL时遍历目标行所有Unit Num并求和，选择单个索引时匹配对应值。

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