作者heavendemon ()
看板Office
标题[问题] sumifs多条件 VBA阵列
时间Tue Mar 28 22:15:34 2017
软体:excel
版本:2010
爬了一下文，发现之前so大的资料已经不在dropbox了QQ
因为用了函数发现严重影响计算效率
我原始资料(sheet1)只要一更新，其他工作页上的函数就会重新计算
导致我原始资料每输入一笔资料就耗费快一分钟在计算函数上，函数如下
=IF(SUMIFS(sheet1!J:J,sheet1!B:B,A3,sheet1!C:C,B3)<H3,"未完成","完成")
因此想到用VBA设置按钮让需要计算的时候按下按钮即可，程式码如下
Set rngpo = Sheets(1).Range("b1:b" & lstrow)
Set rngno = Sheets(1).Range("c1:c" & lstrow)
Set rngout = Sheets(1).Range("j1:j" & lstrow)
With ActiveSheet
myrow = .Range("b3").End(xlDown).Row
For i = 3 To myrow
If Application.SumIfs(rngout, rngpo, Cells(i, 1).Value, rngno, Cells(i,
2).Value) < Cells(i, 8).Value Then
Cells(i, 13).Value = "未完成"
Else: Cells(i, 13).Value = "完成"
End If
Next i
後来发现按下按钮後还是非常没有效率，平均100rows的资料要25秒
自己在网上搜寻後，发现使用阵列会加速很多
但对VBA完全新手的我 array 的使用方式研究好久还是不太清楚
找到使用阵列的优化程式码如下
Sub sumif()
Const n& = 50000
Dim d As Object, a, u&(), i As Long
Set d = CreateObject("scripting.dictionary")
a = Range("A1:B" & n)
ReDim u(1 To n, 1 To 1)
For i = 1 To n
d(a(i, 1)) = d(a(i, 1)) + a(i, 2)
Next i
For i = 1 To n
u(i, 1) = d(a(i, 1))
Next i
Range("E1:E" & n) = u
End Sub
原本函数的sample如下
=RANDBETWEEN(10,99) in A1:A50000
and
=RANDBETWEEN(50,500000) in B1:B50000
Then in C1
=SUMIF(A:A,A1,B:B)
我是完全不懂他在哪个地方有做加总的动作
不知道哪位大大可以看出这个外国人的逻辑
最後同场加映似乎更快的方法，这个我比较看得懂(因为没有阵列)
但我找不到他的criteria他只合并了criteria range 成为另外一个range
但是他的criteria在哪?
还有他用排序的方式去加总，不是应该要在合并完AB栏位後就要先排序一次吗?
太多疑问不知道有没有大神可以教学阵列的逻辑(愿意付学费)
Sub FasterThanSumifs()
'FasterThanSumifs Concatenates the criteria values from columns A and B -
'then uses simple IF formulas (plus 1 sort) to get the same result as a
sumifs formula
'Columns A & B contain the criteria ranges, column C is the range to sum
'NOTE: The data is already sorted on columns A AND B
'Concatenate the 2 values as 1 - can be used to concatenate any number of
values
With Range("D2:D25001")
.FormulaR1C1 = "=RC[-3]&RC[-2]"
.Value = .Value
End With
'If formula sums the range-to-sum where the values are the same
With Range("E2:E25001")
.FormulaR1C1 = "=IF(RC[-1]=R[-1]C[-1],RC[-2]+R[-1]C,RC[-2])"
.Value = .Value
End With
'Sort the range of returned values to place the largest values above the
lower ones
Range("A1:E25001").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("E1"), Order2:=xlDescending, Header:=xlYes
Sheet1.Sort.SortFields.Clear
'If formula returns the maximum value for each concatenated value match &
'is therefore the equivalent of using a Sumifs formula
With Range("F2:F25001")
.FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C,RC[-1])"
.Value = .Value
End With
End Sub
第一次发文 如果排版有问题请告知
--
※ 发信站: 批踢踢实业坊(ptt.cc), 来自: 47.89.55.16
※ 文章网址: https://webptt.com/cn.aspx?n=bbs/Office/M.1490710537.A.C9F.html
※ 编辑: heavendemon (47.89.55.16), 03/28/2017 22:18:31
※ 编辑: heavendemon (47.89.55.16), 03/28/2017 22:20:37
1F：→ soyoso: 以a(i,1)，a栏的值做为d的索引值，并於d(a(i,1))=d(a(i,1) 03/29 00:02
2F：→ soyoso: )+a(i,2)做累加，a(i,2)为b栏 03/29 00:03
3F：→ soyoso: 第二个为於range("e2:e25001")处以公式=if(d2=d1,c2+e1,c2 03/29 00:23
5F：→ soyoso: 将累加的最後一笔，以排序方式d栏小至大，e栏大至小移至分 03/29 00:29
6F：→ soyoso: 组的第一笔 03/29 00:29
7F：→ soyoso: 原文有写到"不是应该要在合并完AB栏位後就要先排序一次" 03/29 00:32
8F：→ soyoso: 应是备注处已有写到The data is already sorted on 03/29 00:33
9F：→ soyoso: columns A AND B的原因，故巨集内无再多加入 03/29 00:34
10F：→ soyoso: 个人觉得下方巨集不一定会输出正确，例如a栏9，b栏99合并 03/29 00:37
11F：→ soyoso: 为999，a栏99，b栏9，合并也是999 03/29 00:38
12F：→ soyoso: 就会加总起来了，这和sumifs的判断上不同的 03/29 00:39
14F：→ heavendemon: 谢谢so大 指点 关於阵列的部分 小弟我实测後结果也是 03/29 00:44
15F：→ heavendemon: 和sumif函数结果不同 我用监看阵列後也看不明白 03/29 00:44
16F：→ heavendemon: a(1,1)并不是A1的值 不过是有出现在A栏 但a(1,2)的值 03/29 00:46
17F：→ heavendemon: 不是B1外 根本没有出现在原本的range里 是小弟理解错 03/29 00:46
18F：→ heavendemon: 误还是这个阵列本身就有问题 如果so大 方便 可否用阵 03/29 00:47
19F：→ heavendemon: 列示范 sumifs的写法 如果是要两个criteria就会变成 03/29 00:49
20F：→ heavendemon: 三栏的阵列? a(i,1),(i,2),(i,3) 03/29 00:49
21F：→ heavendemon: 或有任何加速sumifs在VBA的方法 几千笔跑下来很费时 03/29 00:58
22F：→ heavendemon: 另外我发现这两个方法应该都是用本身栏位中的值当作 03/29 01:14
23F：→ soyoso: 如a指定以a1:b1起的范围的话a(1,1)应是a1的值，a(1,2)为b1 03/29 01:15
25F：→ heavendemon: criteria 但我的函数是将跨工作页的cell当criteria 03/29 01:16
26F：→ heavendemon: 所以才不能用枢纽分析 不知道这样是不是还可以用阵列 03/29 01:16
27F：→ heavendemon: 的方式执行出sumifs的效果 03/29 01:17
29F：→ soyoso: 其他字元来区别，再将d栏排序的话，是否可以排除回文内所 03/29 01:28
30F：→ soyoso: 提到的问题呢？ 03/29 01:29
31F：→ soyoso: 如要以上方巨集改为sumifs的话，可以 03/29 01:37
33F：→ soyoso: 函数做为比对而已 03/29 01:38
34F：→ heavendemon: 所以在dictionary里面 前面一定是key 後面就是item 03/29 10:34
35F：→ heavendemon: d(a(i,1)&"_"&a(i,2))就是一定是对应到a(i,3) 03/29 10:37
36F：→ heavendemon: 不知道我这样理解有没有错 非常感谢so大这麽晚还解答 03/29 10:38
37F：→ soyoso: 理解上会以d(a(i,1)&"_"&a(i,2))会对应到a(i,1)&"_"&a(i,2 03/29 10:51
38F：→ soyoso: )并将a(i,3)累加进去，看是否也和原po回文的理解上相同 03/29 10:52
39F：→ heavendemon: 了解 另外我要将不连续的range放到阵列里面 先使用 03/29 11:38
40F：→ heavendemon: application.union 再丢进阵列 但阵列只读取到第一个 03/29 11:39
41F：→ heavendemon: range 阵列一定要连续range没有其他办法?或是另开一 03/29 11:40
42F：→ heavendemon: 阵列去除存另外的range ? 03/29 11:41
43F：→ soyoso: 不连续方式想到的是回圈或以Array的方式 03/29 12:03
※ 编辑: heavendemon (47.89.55.16), 03/29/2017 15:14:04
45F：→ heavendemon: on error resume next 之後在B栏某一criteria的结果 03/29 16:03
46F：→ soyoso: 有错误讯息？ 03/29 16:03
47F：→ heavendemon: 是错的 不过是有顺利跑完 就是16140.41这个crieria 03/29 16:04
48F：→ heavendemon: 结果和sumifs不同 03/29 16:05
49F：→ heavendemon: 错误是型态不符合 03/29 16:06
50F：→ soyoso: sumarr方面是否为文字类型呢？ 03/29 16:16
52F：→ soyoso: d内，如又有符合a、b栏时，进行累加上就会出现型态不符合 03/29 16:19
54F：→ heavendemon: 比对後，速度反而更慢 平均100row40秒 这个比对的部 03/29 18:55
55F：→ heavendemon: 有没有比较聪明效率的写法 感觉绕了一大圈回到原点.. 03/29 18:56
56F：→ soyoso: 将判断的结果先以丢进变数array内，回圈结束後再一次，以 03/29 19:24
57F：→ soyoso: range=变数的方式写入 03/29 19:25
58F：→ soyoso: 写入上应会用到工作表函数transpose 03/29 19:28
59F：→ heavendemon: 小弟对阵列真的不熟悉 不知道能不能有简单的示范参考 03/29 20:10
60F：→ soyoso: 原文上面的巨集内Range("E1:E" & n) = u和上面的回圈，就 03/29 20:51
61F：→ soyoso: 约是回文内的意思 03/29 20:51
非常感谢so 大不厌其烦解答
我最後用了录制巨集的方式取得原本sumifs函数的formulaR1C1格式
直接将R1C1的函数丢到指定的range范围
最後把函数取代成值
达到每100rows低於一秒的效率
花了很久的时间 才回头发现最简单的方法
希望能给有遇到函数公式太多导致原始资料更新耗时的朋友
一些参考和帮助
※ 编辑: heavendemon (47.89.55.16), 03/30/2017 18:10:19