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

¤j¶q¸ê®Æ¤ñ¹ï»P¦X¨Ö

¤j¶q¸ê®Æ¤ñ¹ï»P¦X¨Ö

¦U¦ì«e½ú¦n!
¤p§Ì§Ú¦³¤@­Ó°ÝÃD­n½Ð¨D¤j®aÀ°¦£
§Ú»Ý­n±N³\¦hSheet(¤j¬ù¦³60­Ó) ªº¸ê®Æ¦X¨Ö¦bSumªº³o­ÓSheet¤º
¨C­ÓSheet ¤j¬ù¦³1¸Uµ§¸ê®Æ¡A¦ÓSUM ¤j¬ù¦³5¸Uµ§
¤@¶}©l¥Î VLOOKUP¥h°µ......¥u¦³¤@­ÓºG
¤§«á§ï¥Î°j°é...ÁÙ¬O¤@¼Ëµ¥¨ì¤Ñ¯î¦a¦Ñ
«á¨Ó¥Î Find ¦ý¬O¹J¨ìªÅ¥ÕµL­È·|¿ù»~...
·Q¥Î °}¦C & Dictionary ¦ý¬O¤@ª½¥dÃö..
·Q½Ð¨D¤j®aÀ°¦£·Q¤@·Q¡A¦ý¬O¤S­n¦³°õ¦æ³t«×¡A¦]¬°¸ê®Æ¯uªº¤Ó¦h¤F
ÁÂÁÂ

Book2.rar (11.34 KB)

  1. Sub ex()
  2. Dim ar()
  3. Set D = CreateObject("Scripting.Dictionary")
  4. Application.ScreenUpdating = False
  5. With Sheets.Add
  6. For Each sh In Sheets(Array("01", "02", "03", "04"))
  7.     ReDim Preserve ar(s)
  8.     ar(s) = sh.[A1].CurrentRegion.Address(, , xlR1C1, 1)
  9.     s = s + 1
  10. Next
  11. .Range("A1").Consolidate Sources:=ar, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False
  12. For Each a In .Range(.[B1], .[IV1].End(xlToLeft))
  13.   If IsEmpty(D(Split(a, "-")(0))) Then
  14.      Set D(Split(a, "-")(0)) = a.Resize(.UsedRange.Rows.Count, 1)
  15.      Else
  16.      Set D(Split(a, "-")(0)) = Union(a.Resize(.UsedRange.Rows.Count, 1), D(Split(a, "-")(0)))
  17.   End If
  18. Next
  19. Set Rng = .Range(.[A1], .[A65536].End(xlUp))
  20. For Each ky In D.keys
  21.    With Sheets("SUM-" & ky)
  22.      Rng.Copy .[A1]
  23.      D(ky).Copy .[B1]
  24.    End With
  25. Next
  26. Application.DisplayAlerts = False
  27. .Delete
  28. Application.DisplayAlerts = True
  29. End With
  30. Application.ScreenUpdating = True
  31. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·PÁ´£¨Ñ¤èªk¡AÁöµM»P§Ú¹ê»Ú¤W°õ¦æ·|¦³§xÃø¦]¬°§Ú¨C­ÓSheet ¥Î±¼208­ÓÄæ¦ì¡A¨C­Ó.xls ¤S¦³52­ÓSheet¡A·PÁÂHsiehªO¤jÀ°¦£!§Ú¤w¸gª¾¹DÅÞ¿è¤èªk»P­ì²z³Ñ¤Uªº´NÅý§Ú¦Û¤v¥h±´¯Á§a!!

TOP

        ÀR«ä¦Û¦b : §g¤l¬°¥Ø¼Ð¡A¤p¤H¬°¥Øªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD