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

Sumif À³¥ÎÃD

Sumif À³¥ÎÃD

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-12-3 19:25 ½s¿è

Dear ¤j¤j­Ì

     ¤p§Ì¤S¨Ó½Ð±Ð¤j¤j­Ìªº±Ð»£~ ³o¦¸ªº°ÝÃD¬O¦p¦ó±N¨C¤ëªº¬Û¦P¸ê®Æ¥Îsumif ¥[Á`°_¨Ó (©Î¦³§ó¦nªº¤è¦¡)
       1.¸ê®Æ¤ñ¼Æ¬ù15000µ§  (ªþÀɤ¤¶È¬OÁ|¨Ò)
       °ÝÃD¤@ : ¦bªþ¥ó¤¤ªºsheets(Q1)¤¤,­ì¥»·Q¼g¦¨¥ÎDo Loop¼g¦¨¦Û°Ê±Nsumif´«Äæ¶i¦æ¹Bºâ,¦ý¬O¥¢±Ñ¦³°ÝÃD  (²£¥Í©w¸q¤Wªº°ÝÃD)
       °ÝÃD¤G : ¦bªþ¥ó¤¤ªºsheets(Q2)¤¤,§Úªº­ì©l¸ê®Æ¤¤¨C­Ó¤ë³£¦³O¸òP¨âµ§¼Æ¾Ú
             ²{¦bªº°µªk¬O¥Î¨ç¼Æ¥ý§¹¦¨O-P«á,¦A¨Ï¥ÎSUMIF¶i¦æ¥[Á`
             ·Q½Ð±Ð¤j¤j­Ì,¦b¼¶¼gµ{¦¡½X®É,¬O§_¥i¥H±N¨C¤@¦C¸ê®Æ¥ý¦æ¬Û´î«á,¦A±NÁ`¦X±a¥X¨Ó  (¬Ù±¼O-Pªº¨BÆJ,¦Ó±o¨ìµ²ªG)

¥»©«³Ì«á¥Ñ Hsieh ©ó 2010-12-3 19:57 ½s¿è

­ì°ÝÃD¥X³Bhttp://forum.twbts.com/thread-2074-1-1.html
³oºØ¤j¸ê®Æ¶q¨Ï¥Î¼Ï¯Ã¤ÀªRªí¬O¤ñ¸û¦nªº¿ï¾Ü

SumIFÀ³¥ÎÃD.zip (14.41 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

q1¼Ò²Õ¡G
  1. Private Sub CommandButton1_Click()
  2. Dim n%
  3. [a:a] = [a:a].Value
  4. [w:ai] = ""
  5. Application.ScreenUpdating = 0
  6. n = [a65536].End(3).Row
  7.     [w6].Consolidate "R6C1: R" & n & "c21", xlSum, 1, 1
  8. [x:ae].Delete
  9. [w6] = [a6]
  10. End Sub
½Æ»s¥N½X
q2¼Ò²Õ¡G
  1. Sub yy()
  2. Dim rng, arr, d As Object, i%, j%, m%
  3. [ai:bh] = ""
  4. rng = Range([a6], [ag65536].End(3))
  5. Set d = CreateObject("Scripting.Dictionary")
  6. ReDim arr(1 To UBound(rng), 1 To 13)
  7. arr(1, 1) = rng(1, 1)
  8. For j = 2 To 13
  9. arr(1, j) = Left(rng(1, 8 + c + j), 6): c = c + 1
  10. Next
  11. m = 1
  12. For i = 2 To UBound(rng)
  13. If d(rng(i, 1)) = "" Then
  14. m = m + 1
  15. d(rng(i, 1)) = m
  16. arr(m, 1) = rng(i, 1): c = 0
  17. For j = 2 To 13
  18. arr(m, j) = rng(i, 8 + c + j) - rng(i, 8 + c + j + 1): c = c + 1
  19. Next
  20. Else
  21. c = 0
  22. For j = 2 To 13
  23. arr(d(rng(i, 1)), j) = arr(d(rng(i, 1)), j) + rng(i, 8 + c + j) - rng(i, 8 + c + j + 1): c = c + 1
  24. Next
  25. End If
  26. Next
  27. [ai6].Resize(m, 13) = arr
  28. End Sub
½Æ»s¥N½X

TOP

¼Ï¯Ã¤¤´¡¤J12­Ó­pºâÄæ¦ì¬O«Ü¨¯­Wªº¡A·PıÁÙ¬O¥ý¦bªí¤W¥Î12­Ó»²§UÄæ·|§Ö¨Ç¡C
¤£ª¾½Ñ§g¥i¦³¨ä¥L§ó¦nªº·Qªk¡H

TOP

¦U¦ì¤j¤j~
    ³o­Ó°ÝÃD~ §Ú´N¼È®É±Ä¨úHsieh ¤j¤j~ ¥Î¼Ï¯Ãªº¤è¦¡¨Ó§@·~~
    «áÄò¦³¤j¤j¦³§ó¦nªº¤è¦¡~ ¦A¨Ó­×§ï~

TOP

¥»©«³Ì«á¥Ñ mistery ©ó 2010-12-10 18:45 ½s¿è

¦^´_ 1# hugh0620


    Q1ªº¸Ñªk  ­Ó¤H«Øij¥i¥H¥Î SUM °}¦C¤è¦¡³á ~ ¤è«K§Ö³t¦n¥Î (¨£ªþÀÉ)
     
   ={SUM(($C$1:$C$999="¤û¥¤«Î")*($D$1:$D$999="¦Ñ¤j")*($E$1:$E$999="¤p§Ì")*($F$1:$F$999="A")*($G$1:$G$999="¤j¤ä")*($I$1:$I$999=100)*(J1:J999))}

    PS ¤£¬O«ÜÀ´Q2ªº°ÝÃD­«ÂI...

SumIFÀ³¥ÎÃDQ1.zip (12.55 KB)

TOP

¦^´_ 6# mistery


    Q2ªº°ÝÃD
       1.­ì©l¸ê®Æ¤¤¦³O¸òPªº¸ê®Æ
       2.¥ý±NO-Pªº¸ê®Æ¬Û´î
       3.³Ì«á±NO-P¬Û´î«áªº¸ê®Æ¥ÎSUMIFªº¤è¦¡ §e²{
   »PQ1ªº®t²§¬O¦h¤F­n±NO-P¥ý§¹¦¨,¦A¥ÎSUMIF³B²z

TOP

¥»©«³Ì«á¥Ñ mistery ©ó 2010-12-14 18:42 ½s¿è

¦^´_ 7# hugh0620


    ¦³ÃöQ2ªº³¡¥÷  §Úªº°µªk¦p¤U
   
     =SUM(($C$7:$C$999="¤û¥¤«Î")*($D$7:$D$999="¦Ñ¤j")*($E$7:$E$999="¤p§Ì")*($F$7:$F$999="A")*($G$7:$G$999="¤j¤ä")*($I$7:$I$999=100)*((J7:J999)-(K7:K999)))

    ¥u¬O¦³ÂI¿ò¾Ñ....¤£¯à§â¤½¦¡ª½±µ¥k©Ô¨Ï¥Î
    ©Ò¥H¤p¤p§@¹ú¤@¤U...¦b²Î­pªº¤ë¥÷¤§¶¡´¡¤JªÅ¥ÕÄæ  ---> ¤è«K¤½¦¡ª½±µ¥k©Ô¨Ï¥Î
    (¤§«á¦A§âªÅ¥ÕÄæ§R±¼  ·|¤ñ¤â°Ê§ï¤½¦¡¤º®e§Ö!)

     ´£¨Ñµ¹±z°Ñ¦ÒÅo

    PS  ¤W­z¤½¦¡¤º" " ªº¤å¦r ¨ä¹ê¥i¥Hª½±µ¥ÎÂI¿ï储¦s®æ¤è¦¡

SumIFÀ³¥ÎÃDQ2.zip (15.1 KB)

TOP

        ÀR«ä¦Û¦b : µÊ®ð¼L¤Ú¤£¦n¡A¤ß¦a¦A¦n¤]¤£¯àºâ¬O¦n¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD