ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 3848|回复: 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 | 显示全部楼层
想法蛮好,我赌对于那些由于Sumproduct而很慢的表,加这个后会更慢~
每一次切换表单都会全文搜索~

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2012-12-10 22:56 | 显示全部楼层
实践是检验真理的唯一标淮 我只不过是抛砖引玉 还望赐教

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 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2012-12-17 11:32 | 显示全部楼层
来学习一下,看看是不是真的SUMPRODUCT函数那么不好用。

TA的精华主题

TA的得分主题

发表于 2013-4-9 17:52 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

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

GMT+8, 2019-10-23 04:01 , Processed in 0.075031 second(s), 13 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

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

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

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