[問題] sumifs多條件 VBA陣列

[heavendemon ()]

軟體: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://www.ptt.cc/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
→ soyoso: 以a(i,1)，a欄的值做為d的索引值，並於d(a(i,1))=d(a(i,1)
→ soyoso: )+a(i,2)做累加，a(i,2)為b欄
→ soyoso: 第二個為於range("e2:e25001")處以公式=if(d2=d1,c2+e1,c2
→ soyoso: )來將c欄同值由上往下加總如https://i.imgur.com/cDzWWTx
→ soyoso: 將累加的最後一筆，以排序方式d欄小至大，e欄大至小移至分
→ soyoso: 組的第一筆
→ soyoso: 原文有寫到"不是應該要在合併完AB欄位後就要先排序一次"
→ soyoso: 應是備註處已有寫到The data is already sorted on
→ soyoso: columns A AND B的原因，故巨集內無再多加入
→ soyoso: 個人覺得下方巨集不一定會輸出正確，例如a欄9，b欄99合併
→ soyoso: 為999，a欄99，b欄9，合併也是999
→ soyoso: 就會加總起來了，這和sumifs的判斷上不同的
→ soyoso: https://i.imgur.com/RBs8Wcd
→ heavendemon: 謝謝so大 指點 關於陣列的部分 小弟我實測後結果也是
→ heavendemon: 和sumif函數結果不同 我用監看陣列後也看不明白
→ heavendemon: a(1,1)並不是A1的值 不過是有出現在A欄 但a(1,2)的值
→ heavendemon: 不是B1外 根本沒有出現在原本的range裡 是小弟理解錯
→ heavendemon: 誤還是這個陣列本身就有問題 如果so大 方便 可否用陣
→ heavendemon: 列示範 sumifs的寫法 如果是要兩個criteria就會變成
→ heavendemon: 三欄的陣列? a(i,1),(i,2),(i,3)
→ heavendemon: 或有任何加速sumifs在VBA的方法 幾千筆跑下來很費時
→ heavendemon: 另外我發現這兩個方法應該都是用本身欄位中的值當作
→ soyoso: 如a指定以a1:b1起的範圍的話a(1,1)應是a1的值，a(1,2)為b1
→ soyoso: 的值 https://i.imgur.com/CxjTRQV
→ heavendemon: criteria 但我的函數是將跨工作頁的cell當criteria
→ heavendemon: 所以才不能用樞紐分析 不知道這樣是不是還可以用陣列
→ heavendemon: 的方式執行出sumifs的效果
→ soyoso: https://i.imgur.com/5VAyGua 下方巨集將a、b欄合併上加上
→ soyoso: 其他字元來區別，再將d欄排序的話，是否可以排除回文內所
→ soyoso: 提到的問題呢？
→ soyoso: 如要以上方巨集改為sumifs的話，可以
→ soyoso: https://i.imgur.com/UzUKrx1 e欄為巨集輸出，f欄為工作表
→ soyoso: 函數做為比對而已
→ heavendemon: 所以在dictionary裡面 前面一定是key 後面就是item
→ heavendemon: d(a(i,1)&"_"&a(i,2))就是一定是對應到a(i,3)
→ heavendemon: 不知道我這樣理解有沒有錯 非常感謝so大這麼晚還解答
→ soyoso: 理解上會以d(a(i,1)&"_"&a(i,2))會對應到a(i,1)&"_"&a(i,2
→ soyoso: )並將a(i,3)累加進去，看是否也和原po回文的理解上相同
→ heavendemon: 了解 另外我要將不連續的range放到陣列裡面 先使用
→ heavendemon: application.union 再丟進陣列 但陣列只讀取到第一個
→ heavendemon: range 陣列一定要連續range沒有其他辦法?或是另開一
→ heavendemon: 陣列去除存另外的range ?
→ soyoso: 不連續方式想到的是迴圈或以Array的方式
※ 編輯: heavendemon (47.89.55.16), 03/29/2017 15:14:04
→ heavendemon: https://imgur.com/a/Csa93 用陣列試了一下出現中斷
→ heavendemon: on error resume next 之後在B欄某一criteria的結果
→ soyoso: 有錯誤訊息？
→ heavendemon: 是錯的 不過是有順利跑完 就是16140.41這個crieria
→ heavendemon: 結果和sumifs不同
→ heavendemon: 錯誤是型態不符合
→ soyoso: sumarr方面是否為文字類型呢？
→ soyoso: 如https://i.imgur.com/88Ct0RD 儲存格c1為文字，置於變數
→ soyoso: d內，如又有符合a、b欄時，進行累加上就會出現型態不符合
→ heavendemon: http://imgur.com/a/v2Tyt 結果做了儲存格和陣列的
→ heavendemon: 比對後，速度反而更慢 平均100row40秒 這個比對的部
→ heavendemon: 有沒有比較聰明效率的寫法 感覺繞了一大圈回到原點..
→ soyoso: 將判斷的結果先以丟進變數array內，迴圈結束後再一次，以
→ soyoso: range=變數的方式寫入
→ soyoso: 寫入上應會用到工作表函數transpose
→ heavendemon: 小弟對陣列真的不熟悉 不知道能不能有簡單的示範參考
→ soyoso: 原文上面的巨集內Range("E1:E" & n) = u和上面的迴圈，就
→ soyoso: 約是回文內的意思
非常感謝so 大不厭其煩解答

我最後用了錄製巨集的方式取得原本sumifs函數的formulaR1C1格式

直接將R1C1的函數丟到指定的range範圍

最後把函數取代成值

達到每100rows低於一秒的效率

花了很久的時間 才回頭發現最簡單的方法

希望能給有遇到函數公式太多導致原始資料更新耗時的朋友

一些參考和幫助
※ 編輯: heavendemon (47.89.55.16), 03/30/2017 18:10:19