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

[µo°Ý] ¤Gºû°}¦Cªº±Æ§Ç°ÝÃD¡H

[µo°Ý] ¤Gºû°}¦Cªº±Æ§Ç°ÝÃD¡H

¦U¦ì«e½ú¡G

¸ê®Æ¦pªþ¥ó
Book1.rar (13.16 KB)

¤p§Ì¥Ø«eªº§@ªk¬O±NSHEET2¤¤ªº¸ê®Æ¼g¤J¤@­Ó¤Gºûªº°}¦C¡A¦ý¬O¦p¦³·sªº¸ê®Æ¦C¦pSHEET3¡A
­n¦p¦ó±N¨ä¼g¤J¨ì°}¦C¤¤¦Ó¥B¸ê®Æ«ö·ÓITEMÄæ­«·s±Æ¦C¡A¨Ã±N¬Û¦PªºITEM¸òNO.¥[Á`NUMªº¶q¡C

¥t¥~¡ASHEET1¤¤ªº¸ê®Æ¦p¦ó­pºâ¬Û¦Pªº­pºâ¦³´X­Ó¡H¦C¦p¡GITEM¡GDDD & NO.¡G1ªº¦@¦³45µ§
³o¤]¥i¥H¥ÎDictionaryªº¤è¦¡¨Ó°µ¶Ü¡H

ÁٽЦU¦ì«e½ú«ü¾É¡C

¦^´_ 1# wsx24680


   ¤£ª¾¬O¤£¬O³o­Ó·N«ä
  1. Sub Ex_1() 'Sheet2¸òSheet3¬Û¥[
  2. Dim A As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. d("Item") = Array("ITEM", "NO.", "COUNT")
  5. For Each Sh In Sheets(Array("Sheet2", "Sheet3"))
  6. With Sh
  7. For Each A In .Range(.[A2], .[A65536].End(xlUp))
  8.   If IsEmpty(d(A & A.Offset(, 1))) Then
  9.      d(A & A.Offset(, 1)) = Array(A, A.Offset(, 1), A.Offset(, 2))
  10.      Else
  11.      ar = d(A & A.Offset(, 1))
  12.      ar(2) = ar(2) + A.Offset(, 2)
  13.      d(A & A.Offset(, 1)) = ar
  14.   End If
  15. Next
  16. End With
  17. Next
  18. With Sheet3.[F1].Resize(d.Count, 3)
  19. .Value = Application.Transpose(Application.Transpose(d.items))
  20. .Sort key1:=.Cells(1, 1), Header:=xlYes
  21. End With
  22. End Sub


  23. Sub Ex_2() 'Sheet1­p¼Æ
  24. Dim A As Range
  25. Set d = CreateObject("Scripting.Dictionary")
  26. With Sheet1
  27. d("Item") = Array("ITEM", "NO.", "COUNT")
  28. For Each A In .Range(.[A2], .[A65536].End(xlUp))
  29.   If IsEmpty(d(A & A.Offset(, 1))) Then
  30.      d(A & A.Offset(, 1)) = Array(A, A.Offset(, 1), 1)
  31.      Else
  32.      ar = d(A & A.Offset(, 1))
  33.      ar(2) = ar(2) + 1
  34.      d(A & A.Offset(, 1)) = ar
  35.   End If
  36. Next
  37. End With
  38. With Sheet1.[F1].Resize(d.Count, 3)
  39. .Value = Application.Transpose(Application.Transpose(d.items))
  40. .Sort key1:=.Cells(1, 1), key2:=.Cells(1, 2), Header:=xlYes
  41. End With
  42. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh

Hsiehª©¤j¡G
·PÁ±z§Ö³tªº¦^ÂСAµ{¦¡°õ¦æ¹L«á¨S¤°»ò°ÝÃD¡A
¥Ñ©óªì¾Ç¹ïVBA¤¤ªºª«¥óÁÙ¤£²M·¡¡A¦³´X­Ó¦a¤è¤p§ÌÁÙ­n¬ã¨s¤@¤U¡C

¦A¦¸·PÁ¡I

TOP

¦^´_ 3# wsx24680


    ¦pªGµ²ªG¬O§A­nªº
¸Õ¸Õ¼Ï¯Ã¤ÀªR¤Î¸ê®Æ·JÁ`¥\¯à
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦U¦ì«e½ú¡G

¸ê®Æ¦pªþ¥ó


¤p§Ì¥Ø«eªº§@ªk¬O±NSHEET2¤¤ªº¸ê®Æ¼g¤J¤@­Ó¤Gºûªº°}¦C¡A¦ý¬O¦p¦³·sªº¸ê®Æ¦C ...
wsx24680 µoªí©ó 2010-5-23 18:23


½Ð°Ý¼Ó¥D
±zªº¸ê®Æ¥u­n±N3­Ó¸ê®Æªí·JÁ`¦A¥Î¼Ï¯Ã¤ÀªRªí´N§¹¦¨
¥i¥H½Ð±Ð±z¬°¦ó»Ý­n¯S¦a¼g³o­Ó2ºû°}¦C¶Ü?
ÁÂÁÂ!

TOP

¦^´_ 5# PD961A



PD961A«e½ú¡G

³o­Óªþ¥ó¤¤ªº¸ê®Æ¨Ó·½¬O¤w¸g¾ã²z¹Lªº¤F¡A¹ê»Úªº¸ê®Æªí¸û¤j¡A
¸ê®Æ¤º®e¤]¤£§¹¾ã(itemÄæ¦ì¦³¨Ç¬OªÅ¥Õ¡A¥u¦³¦b²Ä¤@­Ó¤~¦³¡A¤U­±¬Û¦Pitemªº³Q¬Ù²¤)¡A
¦Ó¥B¸ê®Æ¤§¶¡¦³¤@¨Ç¤£¥²­nªºªÅ¦æ¡C

¦]¦¹§Ú­ì¥»ªº·Qªk¬O·Q­n§Q¥Î¤@­Ó¤Gºûªº°}¦C¨Ó§âsheet2¤¤ªº¸ê®ÆÀx¦s¨ì°}¦C
¦pªG¬OªÅ¥Õ´N¸õ¹L¡A¦pªGitem¬OªÅ¥Õ¦ý¬Ono.Äæ¦ì¸ònumÄæ¦ì¤¤¦³­È¡A
´Nµø¬°»P¤W­Óitem¤¤ªº­È¬Û¦P¡A±N¨äitem¤¤ªº­È¸É¤W¡AÅý¨ä¸ê®Æ§¹¾ã¡C

ÁÙ¦³¤@ÂI¬O§Ú·íªì´£°Ý®É¨S¦³·Q¨ìªº¡A¦b³o¥ý¸òHsiehª©¤j»¡Án©êºp¡K
·íªì¨S¦³»¡©ú²M·¡¡A§Ú·Q­nªºµ²ªGÀ³¸Ó¬O­nitemÄd¬O­n·Ósheet2¤¤ªº¶¶§Ç¨Ó±Æ§Ç¡A
¦Óno.Äæ«h¥Ñ¤p¨ì¤j±Æ§Ç¡AnumÄæ¥u­n±N¨ä¬Û¦Pªº¥[Á`¡C

±Nsheet3¤¤ªº¸ê®Æ¥[¤J¨Ã·Ó¤W­±ªº³W«h¨Ó±Æ§Ç¡A
¤p§Ì¥Ø«e¦³·Q¨ìªº·§©À¬OÀ³¸Ó­n¦A§Q¥Î¤@­Ó¤Gºû°}¨Ó·í¼È¦s¡A
¦ýÁÙ¨S·Q¨ì­n¦p¦ó¼g¦¨µ{¦¡¡A©Î¬O¦U¦ì«e½ú¦³¤°»ò§ó¦nªº¤èªkÁٽЦU¦ì«e½ú«üÂI¡C

­«·sªþ¤W¤@­Ó·sªºªþ¥ó¡A¤º®e¦³¨Ç­×¥¿¡A·Q­nªºµ²ªG¦psheet3¤¤ªºJÄæ¨ìLÄæ¡C
**¤º§tHsiehª©¤j¥ý«eªºµ{¦¡½X
**­×§ïsheet2ªºitem¶¶§Ç
**§ì¨úªº­È¥i¥H¤£¥Î§t¼ÐÃD

Book1.zip (21.82 KB)

TOP

¦^´_ 6# wsx24680
  1. Sub Ex_1() 'Sheet2¸òSheet3¬Û¥[
  2. Dim A As Range, Ay()
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")

  5. For Each Sh In Sheets(Array("Sheet2", "Sheet3"))
  6. With Sh
  7. For Each A In .Range(.[A2], .[A65536].End(xlUp))
  8.   If IsEmpty(d(A & A.Offset(, 1))) Then
  9.      d(A & A.Offset(, 1)) = Array(A, A.Offset(, 1), A.Offset(, 2))
  10.      d1(A.Value) = ""
  11.      Else
  12.      ar = d(A & A.Offset(, 1))
  13.      ar(2) = ar(2) + A.Offset(, 2)
  14.      d(A & A.Offset(, 1)) = ar
  15.   End If
  16. Next
  17. End With
  18. Next
  19. With Sheet3
  20. .Columns("F:H") = ""
  21. Set A = .[F1]
  22. For Each ky In d1.keys
  23.    For Each key1 In d.keys
  24.       If d(key1)(0) = ky Then
  25.       ReDim Preserve Ay(s)
  26.       Ay(s) = d(key1)
  27.       s = s + 1
  28.       End If
  29.     Next
  30. With A.Resize(s, 3)
  31. .Value = Application.Transpose(Application.Transpose(Ay))
  32. .Sort key1:=.Cells(1, 2), header:=xlNo
  33. End With
  34. Erase Ay: s = 0: Set A = .[F65536].End(xlUp).Offset(1, 0)
  35. Next
  36. End With
  37. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh

·PÁÂHsiehª©¤j§Ö³tªº¦^ÂСA
¸Õ¹L¤F¨S¦³¤°»ò°ÝÃD¡A­Y¦³¤£¤F¸Ñªº¦a¤è¦A¨Ó½Ð±Ð¡C

TOP

¦^´_ 7# Hsieh


   
Hsiehª©¤j¡G
½Ð°Ý¤@¤U¡A¦]¬°±NSHEET2¸òSHEET3¬Û¥[«á¬Ý¤£¥X¨Ó­þ¤@µ§¬O¨â±iSHEET³£¦³¬Û¥[¹Lªº¡A
§Æ±æ¯àÅã¥Ü¦¨
DDD       2           20+10
©Ò¥H¤p§Ì¸ÕµÛ­×§ï±NHsiehª©¤jµ{¦¡½Xªº²Ä14¦æ§ï¦¨
ar(2) = A.Offset(, 2) + "+" + ar(2)
¦ý¥X²{¿ù»~¡A½Ð°ÝHsiehª©¤j¦pªG§ï¦¨¦¹ºØÅã¥Ü¡A¦p¦ó­×§ïµ{¦¡¡C

TOP

¦^´_ 9# wsx24680


    ar(2) = A.Offset(, 2) + "+" + ar(2)
¦]¬°§A¥«­n¦r¦ê³sµ²
À³¬°ar(2) = A.Offset(, 2) & "+" & ar(2)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD