SUMPRODUCT函数 瘦身

KCFONG

大家都遇到 SUMPRODUCT函数 運行太慢,

原因之一, 是因为源资料范围未能动态设定,

大家常把公式的范围设定很大

构思一个宏, 可以动态设定源资料范围

把如下的宏放在 ThisWorkbook 底下的模块

当激活工作表, 宏会动态设定源资料范围 (加大100行)

注意的是, 1.若源资料范围很大 或太多公式, 運行还是会很慢 2.不能 动态设定其它工作簿的源资料范围

多谢lee1892提点, 更新宏提高效率
Public dic As New Dictionary
'引用 c:\windows\system32\scrrun.dll
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Needupdate As Boolean
Application.EnableEvents = False: Application.Calculation = xlCalculationManual
    For Each s In ThisWorkbook.Sheets
        If s.UsedRange.Rows.Count <> 1 Then
            LASTROW = s.Cells.SpecialCells(xlLastCell).Row
            If dic(s.Name) <> LASTROW + 100 Then
                dic(s.Name) = LASTROW + 100
                Needupdate = True
            End If    'For normal update
        End If
    Next
   If Needupdate = False Then Exit Sub
    Set ss = ActiveSheet.UsedRange.Find(What:="SUMPRODUCT", LookIn:=xlFormulas, Lookat:=xlPart)
        If Not ss Is Nothing Then
                sr = ss.Row: sc = ss.Column
                Set ss1 = ActiveSheet.UsedRange.FindPrevious(After:=ss): er = ss1.Row: ec = ss1.Column
                rng = Range(Cells(sr, sc), Cells(er, ec)).Formula
                For Each SSS In Range(Cells(sr, sc), Cells(sr, ec))
                    yy = SSS.Formula
                    If yy Like "*SUMPRODUCT*" Then
                            X = Array("(", ")", "*", "=", ">", "<")
                            For i = 0 To UBound(X)
                                yy = Replace(yy, X(i), ",")
                            Next i
                            ZZ = Split(yy, ",")
                            For i = 0 To UBound(ZZ)
                                If InStr(ZZ(i), ":") <> 0 Then
                                   SNAME = ActiveSheet.Name
                                    If InStr(ZZ(i), "!") <> 0 Then SNAME = Left(ZZ(i), InStr(ZZ(i), "!") - 1)
                                    If IsNumeric(Right(ZZ(i), 1)) Then
                                        If dic.EXISTS(SNAME) Then REP = Left(ZZ(i), InStrRev(ZZ(i), "$")) & dic(SNAME)
                                    ElseIf IsNumeric(Right(ZZ(i), 1)) = False Then   ' A:D
                                        REP = Replace(ZZ(i), ":", "$1:") & "$" & dic(SNAME)
                                    End If
                                    If REP <> "" Then
                                        For R = 1 To UBound(rng)
                                            For c = 1 To UBound(rng, 2)
                                                rng(R, c) = Replace(rng(R, c), ZZ(i), REP)
                                            Next c
                                        Next R
                                    End If
                                 End If
                            Next i
                     End If
                Next SSS
            Range(Cells(sr, sc), Cells(er, ec)) = rng
        End If
Application.EnableEvents = True: Application.Calculation = xlCalculationAutomatic
End Sub

wolf03

这个公式确实是有些效率低，我现在是尽量换用其它的，反正也不用2003了

zo.ct

看不懂，这是做什么用的？但是觉得很强大。

蔡武

这个，主要处理什么问题。

excel_芳婷

我想问问你，为什么有时候数据统计的时候正常，有时候却弹出报错？我统计23-26的数据就正常，当我统计1号，或者10号到26号数据时候就报错……

KCFONG

excel_芳婷 发表于 2015-3-28 09:27
我想问问你，为什么有时候数据统计的时候正常，有时候却弹出报错？我统计23-26的数据就正常，当我统计1号， ...

Upload your file

excel_芳婷

KCFONG 发表于 2015-3-28 13:19
Upload your file

求指导，不知道为什么“统计表”有时候不能统计，一时时的，例如3-1到今天3-30，就会提示类型不匹配，再换个日期，也是这样报错提示，但是如果3-22到今天3-30，就可以统计数据。

KCFONG

range("e24") is a "."
so cannot sum up

KCFONG

you should not input other thing inside the sheet.
macro cannot sum up these things.

excel_芳婷

KCFONG 发表于 2015-3-31 00:04
range("e24") is a "."
so cannot sum up

我看到问题了，登记表出现很多表格只是“.”，所以20号的就不能统计，但是，怎么样才可以避免？