ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 6097|回复: 15

[原创] SUMPRODUCT函数 瘦身

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-12-10 21:56 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:SUMPRODUCT
本帖最后由 KCFONG 于 2012-12-11 07:40 编辑

大家都遇到 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

TA的精华主题

TA的得分主题

发表于 2012-12-10 22:50 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
想法蛮好,我赌对于那些由于Sumproduct而很慢的表,加这个后会更慢~
每一次切换表单都会全文搜索~

楼主考虑设置全局变量,先判断是不是要执行全文扫描

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-10 22:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
实践是检验真理的唯一标淮 我只不过是抛砖引玉 还望赐教

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-10 23:01 | 显示全部楼层
这构思是计对
大家常把公式的范围设定很大,
而源资料范围不大,
可以动态设定源资料范围

TA的精华主题

TA的得分主题

发表于 2012-12-10 23:04 来自手机 | 显示全部楼层
本帖最后由 lee1892 于 2012-12-10 23:22 编辑

比如设置全局变量记录每个表单的最大行数,你不是设了100的缓冲吗,最大行数变化没超过的就不用后面执行了嘛。当然,这样对新的公式没效果~

嗯,这种事用常用事件驱动总是不太好的。


======
平板回帖好麻烦~

==========
嗯,另外不知道楼主测试过行*行和块*块不,代码看上去可能会有问题~

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-10 23:24 | 显示全部楼层
十分多谢你的提点
亦佩服你的技术和见微知着
我有一个想法
现在的电脑速度很快
我很多时不求极速
只求简单
让别人易於明白

TA的精华主题

TA的得分主题

发表于 2012-12-10 23:27 | 显示全部楼层
这样啊,那当我没说,呵呵~

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-11 16:25 | 显示全部楼层
多谢lee1892提点, 已更新宏提高效率

TA的精华主题

TA的得分主题

发表于 2012-12-17 11:32 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
来学习一下,看看是不是真的SUMPRODUCT函数那么不好用。

TA的精华主题

TA的得分主题

发表于 2013-4-9 17:52 | 显示全部楼层
前来学习,比较深奥,基本上没弄明白
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 17:40 , Processed in 0.067455 second(s), 9 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表