ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó´£°ª¦h±ø¥ó¥[Á`®Ä²v SUMIFS vs SUMPRODUCT vs VBA

¦^´_ 1# sunnyso
½Ð±Ð¤@­Ó°ÝÃD¡G
  1. fSumIFs = "=SUMIFS(­ì©l¸ê®Æ!R2C3:R" & RowsCnt & "C3,­ì©l¸ê®Æ!R2C1:R" & RowsCnt & "C1,Á`ªí!RC1,­ì©l¸ê®Æ!R2C5:R" & RowsCnt & "C5, COLUMN(R[-3]C[-1]))"
½Æ»s¥N½X
¨ä¤¤³Ì§ÀºÝ¤§ COLUMN(R[-3]C[-1]) «üªº¬O¡H ¥¦ªº¥Î·N¦ó¦b¡H
ÁÂÁ§A¡I

TOP

¦^´_  c_c_lai

=column(a1)=1 to 12 (¤ë¡^
sunnyso µoªí©ó 2013-6-1 16:50

­ì¨Ó¦p¦¹¡A¸U¤À·P¿E¡I

TOP

¦^´_ 1# sunnyso
¸Õ¸Õ³o­Óµ{¦¡½X¡A¯Ó®É 0.921875
  1. Sub Ex_VBA_Array()          '  VBA Code Array
  2.     Dim RowsCnt As Long, m As Long, SubTotalAr() As Double
  3.     Dim t1 As Variant, t2 As Variant, AllType As Variant
  4.     Dim DataArea As Variant
  5.     Dim i%, j%
  6.    
  7.     t1 = Timer
  8.     AllType = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
  9.     ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
  10.     Application.ScreenUpdating = False
  11.    
  12.     '  ²M²z¼ƾÚ
  13.     '  Sheets("Á`ªí").Activate
  14.     Sheets("Á`ªí").Range("A3").CurrentRegion.Offset(1, 1).Clear
  15.    
  16.     With Sheets("­ì©l¸ê®Æ")
  17.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
  18.         DataArea = .Range("A2").Resize(RowsCnt, 3)
  19.         
  20.         For m = 1 To UBound(DataArea)
  21.             For i = 0 To UBound(AllType) '  AÃþ To JÃþ
  22.                 If DataArea(m, 1) = AllType(i) Then           ' Jan to Dec
  23.                     SubTotalAr(i, Month(DataArea(m, 2)) - 1) = SubTotalAr(i, Month(DataArea(m, 2)) - 1) + DataArea(m, 3)
  24.                 End If
  25.             Next i
  26.         Next m
  27.     End With
  28.    
  29.     With Sheets("Á`ªí")
  30.         .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
  31.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
  32.         For i = 0 To 3
  33.             '  .Range(Chr(79 + i) & 4 & ":" & Chr(79 + i) & 13).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
  34.             .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).FormulaR1C1 = "=SUM(RC[-" & (13 - i * 2) & "]:RC[-" & (11 - i * 2) & "])"
  35.             .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1) = .Range(Chr(79 + i) & 4).Resize(UBound(AllType) + 1).Value
  36.         Next i
  37.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
  38.     End With
  39.     Application.ScreenUpdating = True
  40.     t2 = Timer
  41.     MsgBox "¯Ó®É" & t2 - t1
  42.     '  Sheets("­ì©l¸ê®Æ").[F3] = "¯Ó®É¡G " & (t2 - t1)
  43. End Sub
½Æ»s¥N½X
04.png
2013-6-1 22:26

TOP

¬Ý§¹1#ªþ¥ó¡A²Ä¤@¤ÏÀ³¬Oı±o²Ä¤TºØ¤èªkªºVBA CODE ¤¤ For¤Uªº¤£¤Ó¦n¡A­n¬°¥L¥­¤Ï !!! À³¸Ó¬O¹³c_c_lai¤j³o¼Ë ...
stillfish00 µoªí©ó 2013-6-4 20:05

ÁÂÁ«ü¾É¡I
Exit Sub ¾A«×ªº¸m¤J¡Aªº½T¯àÁ×±¼¤£¥²­nªº®ö¶O°j°é¡A
¥ÎÅܼƨӨú¥N½T¹ê¤]¦³¨Ç³\ªº§U¯q¡A¦pªG Month()
¨Ï¥ÎÀW²v¤jªº¸Ü¡A¨º´N·|¦³«Ü¤j¦a®Ä²v´£¤É¤F¡C
¦A¦¸¦V§A»¡ÁnÁÂÁ¡C

TOP

¦^´_  sunnyso
ML089 µoªí©ó 2013-6-5 21:38

¦p¦¹­×§ï«á½T¹ê¦³¤£¿ù®Ä¯q´£¤É¡C
¦h¨¤«×ªº«äºû¥ç¯à¦ÒÅç»P¼W¶i§Ú­Ì«ä¦Òªº¯à¤O¡C
ÁÂÁ§A¡C

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD