[問題] Excel sumifs多條件 VBA陣列

看板Office作者heavendemon時間7年前 (2017/03/28 22:15)推噓0(0推 0噓 61→)

留言61則, 2人參與討論串1/1

軟體: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

→