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

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

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-6 11:56 ½s¿è

¦^´_ 18# ML089

¦³¤@­Ó·Qªk¡A §Q¥ÎVBA ªº InStr()¡Afilter, join µ¥ ¨ç¼Æ¡C¦ý¬OÁÙ¨S·Q¨ì¨ãÅé¦p¦ó¹ê²{
ss

TOP

¦^´_ 18# ML089

¤@¯ë¨Ó»¡­n´£°ª¥¨¶°®Ä²vÀ³¸Ó´î¤Ö¨Ï¥Î¬¡­¶Ã¯¨ç¼Æ©M¹ï¬¡­¶Ã¯ª«¥óªºÅª¼g

Sub test1()
    Dim i%, j%
    Dim Ar(1000, 1000) As Double
    For i = 0 To 1000
        For j = 0 To 1000
            Ar(i, j) = i * 4 + j
        Next j
    Next i
    Range(Cells(1, 1), Cells(1001, 1001)) = Ar
End Sub

Sub test2()
    Dim i%, j%
    For i = 1 To 1000
        For j = 1 To 1000
           Cells(i, j) = i * 4 + j
        Next j
    Next i
End Sub
ss

TOP

§â¸ê®Æ±q¤u§@ªíŪ¤J°}¦C, ¦A§@¹Bºâ, ¹Bºâ¦¨°}¦C,¦A¼g¤J¤u§@ªí
³o¼Ë­n§Ö«Ü¦h, ¨Ò¦p¼Ó¤Wªº¨Ò¤l
ss

TOP

¦^´_ 22# sunnyso

¥H«e³£¬Oª½±µ§Q¥ÎÀx¦s®æ¹Bºâ¦s¨ú¡A¸g¹L³o¦¸¤ñ¸û¤~¤F¸Ñ¤£¦Pªº¤è¦¡®t²§¨º»ò¤j¡A¯u±oº¡¦³¦¬Ã¬ªº¡C
©Ò¥H­n°µ¬d¸ß¹BºâÁÙ¬O­n¥þ³¡¥ýŪ¦ÜÅܼư}¦C¡A¥þ³¡­pºâ¦n¤F¦A±NÅܼư}¦C¤@¦¸©Ê«ü¦Vµ¹Àx¦s®æ¡C

°õ¦æ®É¶¡®t«Ü¦h¡A°õ¦ætest2«á·|¦º·í6¤ÀÄÁ¡A­è¦n¥i¥H³Ü¤@ªM©@°Ø¡C
test1 1.4’
test2 299.3’
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 24# ML089

¤£ª¾¹Dªº·|¥H¬°¹q¸£·í¾÷¤F, ±j¦æµ²§ôExcel·|¯}ÃaexcelÀÉ,³y¦¨·l¥¢
ss

TOP

¦^´_ 22# sunnyso

¦p¦ó¦bSheet3°õ¦æ¥¨¶°test1¡A¸ê®Æ¼g¦ÜSheet1
§Ú¥[¤F¬õ¦â¦rµ²ªG°õ¦æ¿ù»~¡AÀ°¦£¬Ý¬Ý¿ù¦b­þ¸Ì¡A
¦bSheet1°õ¦æ¥¨¶°test1´N¥¿½T

    Sub test1()
    Dim i%, j%
    Dim Ar(1000, 1000) As Double
    For i = 0 To 1000
        For j = 0 To 1000
            Ar(i, j) = i * 4 + j
        Next j
    Next i
    Sheets("Sheet1").Range(Cells(1, 1), Cells(1001, 1001)) = Ar
End Sub

VBA Åܼư}¦C»PÀx¦s®æ­pºâ§å¦¸Åª¼g¤ñ¸û.rar (9.47 KB)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-6 16:43 ½s¿è

¦^´_ 26# ML089

1. Change it to
Sheets("Sheet3").Range("A1").Resize(1000,1000) = Ar

2. or Change it to
     With Sheets("Sheet3")
        .Range(.Cells(1, 1), .Cells(1000, 1000)) = Ar
     End With

3. .xls - Excel 2003 and below  only have 256 Columns ("IV")
ss

TOP

¦^´_ 18# ML089

MATCH ¥i¥H¥Î Scripting.Dictionary¨Ó¹ê²{
¸Õ¤U­±ªºcode
  1. Sub sonny3_dict()
  2.     t1 = Timer
  3.     Dim RowsCnt, m, SubTotalAr() As Double
  4.     Dim DataArea As Variant
  5.     Dim i%, j%
  6.     AllType = Array("AÃþ", "BÃþ", "CÃþ", "DÃþ", "EÃþ", "FÃþ", "GÃþ", "HÃþ", "IÃþ", "JÃþ")
  7.     AllMnth = Array("1¤ë", "2¤ë", "3¤ë", "4¤ë", "5¤ë", "6¤ë", "7¤ë", "8¤ë", "9¤ë", "10¤ë", "11¤ë", "12¤ë")
  8.     ReDim SubTotalAr(0 To UBound(AllType), 0 To 11)
  9.     Set TypeDict = CreateObject("Scripting.Dictionary")
  10.     Set MnthDict = CreateObject("Scripting.Dictionary")
  11.     For i = 0 To UBound(AllType)
  12.         TypeDict(AllType(i)) = i
  13.     Next i
  14.         For i = 0 To UBound(AllMnth)
  15.         TypeDict(AllMnth(i)) = i
  16.     Next i

  17.     Application.ScreenUpdating = False
  18.     With Sheets("­ì©l¸ê®Æ")
  19.         RowsCnt = .Range("A1").CurrentRegion.Rows.Count
  20.         DataArea = .Range("A2").Resize(RowsCnt - 1, 3)
  21.     End With
  22.     For m = 1 To UBound(DataArea)
  23.         i = TypeDict(DataArea(m, 1))
  24.         j = MnthDict(Month(DataArea(m, 2)) & "¤ë")
  25.         SubTotalAr(i, j) = SubTotalAr(i, j) + DataArea(m, 3)
  26.     Next m
  27.    
  28.     With Sheets("Á`ªí")
  29.         .Range("B4").Resize(UBound(AllType) + 1, 12) = SubTotalAr
  30.         .Range("B14:R14").FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
  31.         .Range("N4:N13").FormulaR1C1 = "=SUM(RC[-12]:RC[-1])"
  32.         .Range("O4:R13").FormulaR1C1 = "=SUM(RC[-13]:RC[-11])"
  33.     End With
  34.     Application.ScreenUpdating = True
  35.     t2 = Timer
  36.     Sheets("­ì©l¸ê®Æ").Range("m7") = t2 - t1
  37.     MsgBox "¯Ó®É" & t2 - t1
  38. End Sub
½Æ»s¥N½X
ss

TOP

¦^´_ 27# sunnyso

¦U¦¡ªº°õ¦æ®É¶¡¨ÃµL¤Ó¤j®t²§
Range(Cells(1,1),Cells(1001,1001)) À³¸Ó¬Oµ¥©ó Range("A1:ALL1000")¡A¥i¬O¤£¯à°õ¦æµLªk²z¸Ñ¡A¥u¦n°O¤U¨Ó¡C



¤U¦¡°õ¦æ¿ù»~
Sheets("Sheet1").Range(Cells(1,1),Cells(1001,1001))=Ar

¤U¦¡°õ¦æOK-1.5¬í
WithSheets("Sheet1")
.Range(.Cells(1,1),.Cells(1000,1000))=Ar
EndWith

¤U¦¡°õ¦æOK-1.5¬í
Sheets("Sheet1").Range("A1").Resize(1000,1000)=Ar

¤U¦¡°õ¦æOK-1.5¬í
Sheet1.[A1].Resize(1000,1000)=Ar

¤U¦¡°õ¦æOK-1.5¬í
Sheet1.[A1:ALL1000]=Ar

¤U¦¡°õ¦æOK-1.5¬í
Sheet1.Range("A1:ALL1000")=Ar
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-6-6 23:30 ½s¿è

¦^´_ 29# ML089

¦pªG§A¦bsheet3, ­n§â¸ê®Æ¼g¤Jsheet1

Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1,1),Sheets("Sheet1").Cells(1001,1001))=Ar

¦]¬°cells(1, 1) ¬O sheets("sheet3").cells(1,1), ¤£¯à¥Î©ósheet1 ªºrange
ss

TOP

        ÀR«ä¦Û¦b : ¡i°µ¤Hªº¶}©l¡j¨C¤@¤Ñ³£¬O¬G¤Hªº¶}©l¡A¨C¤@­Ó®É¨è³£¬O¦Û¤vªºÄµ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD