IF嵌套多SUMIFS时如何通过VBA识别有效SUMIFS实现数据下钻筛选

核心修改逻辑 #

• 先识别单元格公式是否包含IF嵌套结构，计算IF条件的布尔结果
• 提取当前生效分支对应的SUMIFS完整表达式
• 把提取到的单个SUMIFS表达式传入原有筛选逻辑执行，兼容原有单SUMIFS单元格的使用习惯

完整修改后代码 #

1. 工作表双击触发脚本（无改动，保持原有即可） #

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    FilterBySUMIFs Target.Cells(1)
End Sub

2. 模块SUMIFS处理脚本（已适配IF嵌套场景） #

Option Explicit

Sub FilterBySUMIFs(r As Range)
   Dim v, ctr As Integer
   Dim intField As Integer, intPos As Integer, intIfPos As Integer, intCommaPos As Integer
   Dim strCrit As String, formulaStr As String, sumIfsStr As String
   Dim rngCritRange1 As Range, rngSUM As Range
   Dim wksDataSheet As Worksheet
   Dim ifCondition As String, ifResult As Boolean
   
   ' 排除无SUMIFS的单元格
   If Not r.Formula Like "*SUMIFS(*" Then Exit Sub
   
   formulaStr = r.Formula
   ' 处理IF嵌套两个SUMIFS的场景
   If Left(formulaStr, 3) = "=IF" Then
       ' 提取IF的条件部分
       intIfPos = InStr(1, formulaStr, "(")
       intCommaPos = GetCommaPosition(formulaStr, intIfPos + 1)
       ifCondition = Mid(formulaStr, intIfPos + 1, intCommaPos - intIfPos - 1)
       ' 计算条件结果
       ifResult = Evaluate(ifCondition)
       ' 提取对应分支的SUMIFS
       If ifResult Then
           ' 条件为真，取第一个SUMIFS
           sumIfsStr = Mid(formulaStr, intCommaPos + 1, GetCommaPosition(formulaStr, intCommaPos + 1) - intCommaPos - 1)
       Else
           ' 条件为假，取第二个SUMIFS，去掉末尾右括号
           sumIfsStr = Mid(formulaStr, GetCommaPosition(formulaStr, intCommaPos + 1) + 1, Len(formulaStr) - GetCommaPosition(formulaStr, intCommaPos + 1) - 1)
       End If
   Else
       ' 无IF嵌套，直接用原公式
       sumIfsStr = formulaStr
   End If

   ' 拆分SUMIFS参数，原有逻辑适配修改为用提取的sumIfsStr处理
   v = Split(Left(sumIfsStr, Len(sumIfsStr) - 1), ",")

   ' 以下为原有逻辑，无调整
   Set rngCritRange1 = Range(v(LBound(v) + 1))
   With rngCritRange1
       Set wksDataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
   End With

   With wksDataSheet
       If .AutoFilterMode And .FilterMode Then
           .ShowAllData
       ElseIf Not .AutoFilterMode Then
           rngCritRange1.CurrentRegion.AutoFilter
       End If
   End With

   For ctr = LBound(v) + 1 To UBound(v)
       If ctr Mod 2 <> 0 Then
           With wksDataSheet
               intField = .Range(v(ctr)).Column - .AutoFilter.Range.Columns(1).Column + 1
               strCrit = Evaluate(v(ctr + 1))
               .Range(v(ctr)).AutoFilter Field:=intField, Criteria1:=strCrit
           End With
       End If
   Next

   intPos = InStr(1, v(LBound(v)), "(")
   Set rngSUM = Range(Replace(v(LBound(v)), Left(v(LBound(v)), intPos), ""))
   Application.Goto rngSUM

   ActiveWindow.ScrollRow = 1
End Sub

' 辅助函数：定位IF表达式中不在括号内的逗号分隔位，避免参数内部逗号干扰识别
Private Function GetCommaPosition(str As String, startPos As Integer) As Integer
   Dim i As Integer, parenCount As Integer
   For i = startPos To Len(str)
       Select Case Mid(str, i, 1)
           Case "(": parenCount = parenCount + 1
           Case ")": parenCount = parenCount - 1
           Case ",": If parenCount = 0 Then GetCommaPosition = i: Exit Function
       End Select
   Next
   GetCommaPosition = Len(str) - 1
End Function

Sub KV_FilterBySumIf()
End Sub

适配说明 #

• 新增的GetCommaPosition辅助函数用于定位IF表达式的分支分隔逗号，避免SUMIFS内部参数的逗号干扰识别
• 自动判断IF条件的生效分支，仅提取当前正在计算的SUMIFS参数执行筛选，完全兼容你提供的示例公式结构
• 原有单SUMIFS单元格的使用逻辑不受影响，可正常运行

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