多条件求和求助

zhangcheng6688

借楼学习一下字典和正则表达式
Sub test()
Application.ScreenUpdating = False
arr = [a1].CurrentRegion
Set dic = CreateObject("scripting.dictionary")
Set reg = CreateObject("vbscript.regexp")
reg.Pattern = "\d+"
For i = 2 To UBound(arr)
   m = Month(arr(i, 2))
   strkey = arr(i, 1) & arr(i, 3) & m
   If Not dic.exists(strkey) Then
      Set dic(strkey) = CreateObject("scripting.dictionary")
   End If
   For j = 4 To UBound(arr, 2)
      If arr(i, j) <> "" Then
         dic(strkey)(arr(1, j)) = dic(strkey)(arr(1, j)) + arr(i, j)
      End If
   Next
Next
brr = [h2].CurrentRegion
For i = 3 To UBound(brr, 2)
   If brr(1, i) = "" Then
      brr(1, i) = brr(1, i - 1)
   End If
Next
For i = 3 To UBound(brr)
   For j = 3 To UBound(brr, 2)
      If reg.test(brr(1, j)) Then
         Set mh = reg.Execute(brr(1, j))
         strkey = brr(i, 1) & brr(i, 2) & mh(0)
         If dic.exists(strkey) Then
            If dic(strkey).exists(brr(2, j)) Then
               brr(i, j) = dic(strkey)(brr(2, j))
            End If
         End If
      End If
   Next
Next
[h2].CurrentRegion = brr
Set dic = Nothing
Application.ScreenUpdating = True
End Sub

saisai0102

一杯绿茶来了 发表于 2025-5-5 20:11
=SUMPRODUCT((MONTH($B$2:$B$119)=LEFTB(LOOKUP("座",$J$1:J$1),2)*1)*($A$2:$A$119=$H3)*($C$2:$C$119=$I3 ...

这个提取月份用的好，LEFTB(LOOKUP("座",$J$1:J$1),2)，因为还有两位月份

meiya

设置了一个动态名称 日期，并结合多条件求和函数sumifs做的

逐渐陌生

你是那块小饼干

=SUM((LEFTB(LOOKUP("座",$J$1:J$1),2)*1=MONTH($B$2:$B$119))*($A$2:$A$119=$H3)*($C$2:$C$119=$I3)*CHOOSECOLS($D$2:$F$119,MATCH(J$2,$D$1:$F$1,0)))