关于条件求和公式简化求教

xulb

表中的公式比较繁琐，若添加工作表的话，容易出错，请教如何修改简化公式更好些？ 统计表本.rar (17.03 KB, 下载次数: 15)

2024-8-9 21:33 上传

点击文件名下载附件

我的版本是EXCEL 2007，自定义了宏表函数sh=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())，试着改为SUMPRODUCT((indirect(sh&"!$A$2:$A$10000")=$A5)*(YEAR(indirect(sh&"!$H$2:$H$10000"))=YEAR(C$1))*(MONTH(indirect(sh&"!$H$2:$H$10000"))=MONTH(C$1))*(indirect(sh&"!$G$2:$G$10000“)))，但是提示公式错误？

niko88819

B2

1. =SUMPRODUCT(SUMIFS(INDIRECT({"中","天","时","风"}&"!G:G"),INDIRECT({"中","天","时","风"}&"!A:A"),$A2,INDIRECT({"中","天","时","风"}&"!H:H"),">="&B$1,INDIRECT({"中","天","时","风"}&"!H:H"),"<="&EOMONTH(B$1,0)))

复制代码

无关岁月

1. =NPV(-2,SUMIFS(INDIRECT({"中";"天";"时";"风"}&"!G:G"),INDIRECT({"中";"天";"时";"风"}&"!H:H"),"<="&EOMONTH(B$1,{-1,0}),INDIRECT({"中";"天";"时";"风"}&"!A:A"),$A2))

复制代码

hxj119

=REDUCE(0,{"中";"天";"时";"风"},LAMBDA(x,y,x+NPV(-2,SUMIFS(INDIRECT(y&"!G:G"),INDIRECT(y&"!H:H"),"<="&EOMONTH(B$1,{-1,0}),INDIRECT(y&"!A:A"),$A2))))

hxj119

综合为一个公式，不用下拉右拉拖动公式：
=DROP(REDUCE(0,B1:M1,LAMBDA(i,t,HSTACK(i,MAP(A2:A5,LAMBDA(m,REDUCE(0,{"中";"天";"时";"风"},LAMBDA(x,y,x+NPV(-2,SUMIFS(INDIRECT(y&"!G:G"),INDIRECT(y&"!H:H"),"<="&EOMONTH(t,{-1,0}),INDIRECT(y&"!A:A"),m))))))))),,1)

无关岁月

365倒是能短点

1. =NPV(-2,(VSTACK(中:风!$A$2:$A$25)=$A2)*(VSTACK(中:风!$H$2:$H$25)<=EOMONTH(B$1,{-1,0}))*VSTACK(中:风!$G$2:$G$25))

复制代码

xulb

自定义了宏表函数sh=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())，想改为SUMPRODUCT((indirect(sh&"!$A$2:$A$10000")=$A5)*(YEAR(indirect(sh&"!$H$2:$H$10000"))=YEAR(C$1))*(MONTH(indirect(sh&"!$H$2:$H$10000"))=MONTH(C$1))*(indirect(sh&"!$G$2:$G$10000“)))，但提示公式错误？

xulb

niko88819 发表于 2024-8-9 21:46
B2

我的版本是EXCEL 2007，自定义了宏表函数sh=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())，试着改为SUMPRODUCT((indirect(sh&"!$A$2:$A$10000")=$A5)*(YEAR(indirect(sh&"!$H$2:$H$10000"))=YEAR(C$1))*(MONTH(indirect(sh&"!$H$2:$H$10000"))=MONTH(C$1))*(indirect(sh&"!$G$2:$G$10000“)))，但是提示公式错误？

xulb

我的版本是EXCEL 2007，自定义了宏表函数sh=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW())，试着改为SUMPRODUCT((indirect(sh&"!$A$2:$A$10000")=$A5)*(YEAR(indirect(sh&"!$H$2:$H$10000"))=YEAR(C$1))*(MONTH(indirect(sh&"!$H$2:$H$10000"))=MONTH(C$1))*(indirect(sh&"!$G$2:$G$10000“)))，但是提示公式错误？

niko88819

xulb 发表于 2024-8-9 22:18
我的版本是EXCEL 2007，自定义了宏表函数sh=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(NOW() ...

B2  下拉及右拉

1. =SumMultipleSheets($A2,$A2,B$1,EOMONTH(B$1,0))

复制代码

1. Function SumMultipleSheets(criteriaRange As Range, criteriaValue As Variant, startDate As Date, endDate As Date) As Double
   
2.     Dim sheetNames As Variant
   
3.     Dim totalSum As Double
   
4.     Dim ws As Worksheet
   
5.     Dim criteriaColumn As Range
   
6.     Dim sumRange As Range
   
7.     Dim dateRange As Range
   
8.     sheetNames = Array("中", "天", "时", "风")
   
9.     totalSum = 0
   
10.     For Each wsName In sheetNames
    
11.         On Error Resume Next
    
12.         Set ws = ThisWorkbook.Worksheets(wsName)
    
13.         On Error GoTo 0
    
14.         If Not ws Is Nothing Then
    
15.              Set sums = ws.Range("G:G")
    
16.             Set criteriaColumn = ws.Range("A:A")
    
17.             Set dateRange = ws.Range("H:H")
    
18.             totalSum = totalSum + Application.SumIfs(sums, criteriaColumn, criteriaValue, dateRange, ">=" & startDate, dateRange, "<=" & endDate)
    
19.         End If
    
20.     Next wsName
    
21.     SumMultipleSheets = totalSum
    
22. End Function

复制代码