在IF语句中嵌套多个SUMIFS时，向下钻入相关的SUMIFS

Question

我有一个VBA脚本，可以深入到包含SUMIFS的单元格中，然后过滤原始数据表以隔离相关的行。脚本在只有一个SUMIFS的单元上工作。

我的一些单元格包含一个包含两个SUMIFS的IF语句(取决于IF是哪个变量)。

我试图找到一种方法，首先在IF语句中找到相关的SUMIFS，然后使用正确的方法进行筛选。

我的代码：

1. 双击加载到工作表上的脚本以触发宏(这是工作的)

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

加载到SUMIFS模块的

• 脚本

Option Explicit

Sub FilterBySUMIFs(r As Range)

   Dim v, ctr As Integer
   Dim intField As Integer, intPos As Integer
   Dim strCrit As String
   Dim rngCritRange1 As Range, rngSUM As Range
   Dim wksDataSheet As Worksheet

   If Not r.Formula Like "*SUMIFS(*" Then Exit Sub

   'split formula by comma, strip the right paren
   v = Split(Left(r.Formula, Len(r.Formula) - 1), ",")

   'the first criteria range is the 2nd element of the array
   Set rngCritRange1 = Range(v(LBound(v) + 1))

   'use first criteria range to get a reference to the data sheet
   With rngCritRange1
       Set wksDataSheet = Workbooks(.Parent.Parent.Name).Worksheets(.Parent.Name)
   End With

   'clear any existing filter, turn filtering on if needed
   With wksDataSheet
       If .AutoFilterMode And .FilterMode Then
           'clear existing autofilter
           .ShowAllData
       ElseIf Not .AutoFilterMode Then
           'display autofilter arrows
           rngCritRange1.CurrentRegion.AutoFilter
       End If
   End With

   'set the filters
   For ctr = LBound(v) + 1 To UBound(v)
       If ctr Mod 2 <> 0 Then
           With wksDataSheet
               'determine field in case table does not start in column A
               intField = .Range(v(ctr)).Column - .AutoFilter.Range.Columns(1).Column + 1
               'use evaluate instead of range(v(ctr + 1))
               'so both cell-reference and hard-coded criteria are handled.
               strCrit = Evaluate(v(ctr + 1))
               .Range(v(ctr)).AutoFilter Field:=intField, Criteria1:=strCrit
           End With
       End If
   Next

   'strip left paren and everything to left of it,
   ' get the sum range from first element of array
   intPos = InStr(1, v(LBound(v)), "(")
   Set rngSUM = Range(Replace(v(LBound(v)), Left(v(LBound(v)), intPos), ""))
   'select the SUM range so total displays in status bar
   Application.Goto rngSUM

   ActiveWindow.ScrollRow = 1

End Sub

Sub KV_FilterBySumIf()

End Sub

我的SUMIFS看起来如下：

=IF($C$6="ALL",SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14),SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6))

Answer 1

我想出了一个函数，可以将IF公式分解为真假部分，并根据表达式返回相关部分。因此，如果表达式为true，则函数返回if公式的True部分。

我所做的函数不是一个健壮的函数，它只有在给定的公式在"= if (< expression >，SUMIFS(.)，SUMIFS(.))“的结构中才能工作。并使用ActiveSheet对表达式进行计算。

Sub Example()
    Dim SumIfsFormula As String
    SumIfsFormula = "=IF($C$6=""ALL"",SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14),SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6))"

    Debug.Print RelevantSumIfs(SumIfsFormula)
    'Output when TRUE : SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14)
    'Output when FALSE : SUMIFS(IS!Actual_Total,IS!Curr_Bud,H$9,IS!Master_Sub_Account,$C14,IS!Project_Desc,$C$6)
End Sub

Function RelevantSumIfs(SumIfsFormula As String) As String
    Dim IfResult As Boolean
    IfResult = Application.Evaluate("=" & Split(Split(SumIfsFormula, "(")(1), ",")(0))
    
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "SUMIFS\([^)]+\)"
    End With
    
    Dim Matches As Object
    Set Matches = regex.Execute(SumIfsFormula)
    
    Dim TargetSumIfs
    If IfResult Then
        TargetSumIfs = Matches(0)
    Else
        TargetSumIfs = Matches(1)
    End If
    
    RelevantSumIfs = TargetSumIfs
End Function

通过将Regex模式从显式搜索SUMIFS更改为搜索任何函数，可以改进此函数。有点像.Pattern = "[A-Za-z0-9]+\([^)]+\)"。但这也可能与公式的许多其他部分相匹配，包括周围的IF(...)。这就是为什么我把它作为.Pattern = "SUMIFS\([^)]+\)"