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

xulb

谢谢大家的回复

xulb

niko88819 发表于 2024-8-10 08:09
B2  下拉及右拉

08.   sheetNames = Array("中", "天", "时", "风")怎样改为自动调用除“总价汇总”外的所有工作表？谢谢

niko88819

xulb 发表于 2024-8-10 09:57
08.   sheetNames = Array("中", "天", "时", "风")怎样改为自动调用除“总价汇总”外的所有工作表？谢谢

1. Function SMultipleSheets(criteriaRange As Range, criteriaValue As Variant, startDate As Date, endDate As Date) As Double
   
2.     Dim totalSum As Double
   
3.     Dim ws As Worksheet
   
4.     Dim criteriaColumn As Range
   
5.     Dim sums As Range
   
6.     Dim dateRange As Range
   
7.     totalSum = 0
   
8.     For Each ws In ThisWorkbook.Worksheets
   
9.         If ws.Name <> "Summary" And ws.Name <> "OtherExclusionSheetName" Then
   
10.             Set sums = ws.Range("G:G")
    
11.             Set criteriaColumn = ws.Range("A:A")
    
12.             Set dateRange = ws.Range("H:H")
    
13.             totalSum = totalSum + Application.WorksheetFunction.SumIfs(sums, criteriaColumn, criteriaValue, dateRange, ">=" & startDate, dateRange, "<=" & endDate)
    
14.         End If
    
15.     Next ws
    
16.     SMultipleSheets = totalSum
    
17. End Function

复制代码

niko88819

xulb 发表于 2024-8-10 09:57
08.   sheetNames = Array("中", "天", "时", "风")怎样改为自动调用除“总价汇总”外的所有工作表？谢谢

新代码审核中，请耐心等待

xulb

统计表本 - 移动公式前、后.rar (34.51 KB, 下载次数: 5)

2024-8-10 14:15 上传

点击文件名下载附件

为何将B8~M11中的公式拖至B2~M5中时，会提示有一个或多个循环引用，并且计算结果不正确(改变4张表的数据时，汇总结果不对)？
而移动公式前没有任何问题？

niko88819

Summary  改为你工作表名称

xulb

niko88819 发表于 2024-8-10 10:24

录入数据后，好像统计结果不能实时更正刷新，需要怎样解决呢？

niko88819

xulb 发表于 2024-8-11 10:16
录入数据后，好像统计结果不能实时更正刷新，需要怎样解决呢？

试一试把代码装在ThisWorkbook存盘，再重启EXCEL

Function SMultipleSheets(criteriaRange As Range, criteriaValue As Variant, startDate As Date, endDate As Date) As Double
    Dim totalSum As Double
    Dim ws As Worksheet
    Dim criteriaColumn As Range
    Dim sums As Range
    Dim dateRange As Range
    totalSum = 0
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "总价汇总" Then
            Set sums = ws.Range("G:G")
            Set criteriaColumn = ws.Range("A:A")
            Set dateRange = ws.Range("H:H")
            totalSum = totalSum + Application.WorksheetFunction.SumIfs(sums, criteriaColumn, criteriaValue, dateRange, ">=" & startDate, dateRange, "<=" & endDate)
        End If
    Next ws
    SMultipleSheets = totalSum
End Function