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

[µo°Ý] excel vba¸ê®Æ¥[Á`ªº¼gªk

[µo°Ý] excel vba¸ê®Æ¥[Á`ªº¼gªk

·Q½Ð±Ð¤j®a

²{¦b¦³¤@¥÷¸ê®Æ
­n¦p¦ó§Q¥Î vba¨Ó­pºâ¥X·Q­nªºµ²ªG(¤£¬O¥Îexcel³á~)

­ì©l¸ê®Æ¶°¸ê®Æµ²ªG¦pªþ¥ó!!
¥Ø«e¥u·Q¨ì¥Îsumif,¥i¬OÄõÀ¬¦C³£­n¦U¦Û¥[Á`¤@¦¸...
°j°éªº¸Ü¤]¤£ª¾¹D­n¦p¦ó¤U¤â

·Ð½ÐÀ°À°¦£

count.rar (7.35 KB)

POP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2013-2-1 00:45 ½s¿è

¦^´_ 1# smartpearl
¸ê®Æ¥u¦³2ºØItem¶Ü?
  1. Sub ex()
  2. Dim Ay(2), MyID%, Ary(2)
  3. Set d = CreateObject("Scripting.Dictionary")
  4. ar = Range("A1").CurrentRegion  '±Nªí®æ¼g¤J°}¦C
  5. i = 2 '²Ä2¦C¶}©l
  6. Do
  7.    j = 4 '²Ä4Äæ¶}©l
  8.    Do
  9.      MyID = IIf(ar(i, 2) = "Add", 0, 1)  '²Ä2Äæ¦pªG¬OAdd´N¥Î0§_«h¥Î1§@¬°°}¦C¯Á¤Þ
  10.      Ay(MyID) = Ay(MyID) + ar(i, j)  '±N°}¦C­È¬Û¥[
  11.    j = j + 1  'Äæ¦ì¥[1
  12.    Loop Until j > UBound(ar, 2) Or TypeName(Cells(1, j).Value) = "String"  'ª½¨ìÄæ¦ì¤j¹Lªí®æÄæ¦ì©Î¬O²Ä¤@¦Cªº­È¬O¦r¦ê(ÁקK²Ä2¦¸°õ¦æ·|­pºâ¨ì­ì¥»·s¼Wªº²Î­p¦ì¸m)
  13.    Cells(i, j).Resize(, 2) = Ay '¸ê®Æ¦Cªº«á­±Äæ¦ì¥[¤J¥[Á`­È
  14. Erase Ay
  15. i = i + 1  '¦C¼Æ¥[1
  16. Loop Until i > UBound(ar, 1) Or Cells(i, 1) = ""   'ª½¨ì¦C¼Æ¤j¹Lªí®æ¦C¼Æ©Î²Ä1Ä欰ªÅ¥Õ
  17. Cells(i, j) = "=SUM(R2C:R[-1]C)": Cells(i + 1, j + 1) = "=SUM(R2C:R[-1]C)"  'ªí®æ¥k¤U¤è¼g¤J¤½¦¡
  18. Cells(1, j).Resize(, 2) = Array("Count_Add", "Count_None")  '¼g¤J¼ÐÃD
  19. j = 4
  20. Do
  21.    i = 2
  22.    Do
  23.      d(ar(i, 2)) = ar(i, j) + d(ar(i, 2))  '¥H²Ä2Ä椺®e¬°¦r¨å¯Á¤Þ¡A¤À§O­pºâ­Ó¯Á¤Þ¥[Á`
  24.    i = i + 1
  25.    Loop Until i > UBound(ar, 1) Or Cells(i, 1) = ""  'ª½¨ì¦C¼Æ¤j¹Lªí®æ¦C¼Æ©Î²Ä1Ä欰ªÅ¥Õ
  26.    For Each ky In d.keys  '¨ú¥X¨C­Ó¦r¨å¯Á¤Þ
  27.      Ay(s) = d(ky)  
  28.      s = s + 1
  29.    Next
  30.    Cells(i, j).Resize(3, 1) = Application.Transpose(Ay)  'ªí®æ¤U¤è¼g¤J¥[Á`¸ê®Æ
  31.    Erase Ay: s = 0: d.RemoveAll  '²M°£°}¦C»P¦r¨å¤º®e
  32. j = j + 1  'Äæ¼Æ¥[1
  33. Loop Until j > UBound(ar, 2) Or TypeName(Cells(1, j).Value) = "String"  'ª½¨ìÄæ¦ì¤j¹Lªí®æÄæ¦ì©Î¬O²Ä¤@¦Cªº­È¬O¦r¦ê(ÁקK²Ä2¦¸°õ¦æ·|­pºâ¨ì­ì¥»·s¼Wªº²Î­p¦ì¸m)
  34. Cells(i + 2, 4).Resize(, Range("A1").CurrentRegion.Columns.Count - 3) = "=R[-1]C-R[-2]C"    '¼g¤J¥[Á`­È®tÃB¤½¦¡
  35. Cells(i, 3) = "Sub_Add": Cells(i + 1, 3) = "Sub_None": Cells(i + 2, 3) = "Total"  '¼g¤J¼ÐÃD
  36. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

©êºp,¥i§_µy·LÁ¿¸Ñ!!!
POP

TOP

ÁÂÁ±z
§Ú¦A¸ÕµÛ¼g¤@¹M~
POP

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD