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

²Å¦X«ü©w±ø¥ó.¨Ã¦X¨Ö¶Ç¦^¬Û¹ïÀ³Àx¦s®æ¤º®e.

²Å¦X«ü©w±ø¥ó.¨Ã¦X¨Ö¶Ç¦^¬Û¹ïÀ³Àx¦s®æ¤º®e.


¦p¤W¹Ï
¦pªG¬Û¹ïÀ³ªºAÄæ  ¦³¤£¦Pªº¤º®e»P­«ÂЬۦPªº
¦³­þ¦U¤½¦¡¥i¦Û°Ê²M°£­«ÂÐ ¨Ã¦X¨Ö¤£¦P¤º®e¦A¦P¤@¦UÀx¦s®æ

¦^´_ 1# joey3277
³o¥Î Excel VBA ¸û®e©ö¼g :
  1. Sub nn()
  2.   Dim lSou&, lTar&
  3.   Dim sStr$
  4.   Dim vD, vK, vI
  5.   
  6.   Set vD = CreateObject("Scripting.Dictionary")
  7.   lSou = 2
  8.   
  9.   Do While Cells(lSou, 3) <> ""
  10.     sStr = CStr(Cells(lSou, 1))
  11.     If InStr(1, vD(CStr(Cells(lSou, 3))), sStr) = 0 Then
  12.       If vD(CStr(Cells(lSou, 3))) = "" Then
  13.         vD(CStr(Cells(lSou, 3))) = sStr
  14.       Else
  15.         vD(CStr(Cells(lSou, 3))) = vD(CStr(Cells(lSou, 3))) & "&" & sStr
  16.       End If
  17.     End If
  18.     lSou = lSou + 1
  19.   Loop
  20.   
  21.   lSou = 0
  22.   lTar = 2
  23.   vK = vD.keys
  24.   vI = vD.items
  25.   Do While lSou < vD.Count
  26.     Cells(lTar, 5) = vI(lSou)
  27.     Cells(lTar, 6) = vK(lSou)
  28.     lTar = lTar + 1
  29.     lSou = lSou + 1
  30.   Loop
  31. End Sub
½Æ»s¥N½X

TOP

·PÁ¤j¤jªº¦^ÂР 
¤S·Q½Ð°Ý
1.§Ú¨Dªºµ²ªGÅã¥Ü¦b  ¸ò   ¸ê®Æ¦b¤£¦PªºÀɮתº¸Ü   ­n¦p¦ó­×§ï  
¦p¸ê®Æ¦bAÀÉ®×   ¦ý§A´£¨Ñªº¥¨¶°¿z¿ï·QÅã¥Ü¦bBÀÉ®×
2.¦pªG§Úªº­q³æ¤ë¥÷Äæ¿é¤Jªº¤è¦¡§ï¦¨¼Æ¦r«á­±¥[¤@¦U¤p¼ÆÂI.¦p(1.)
³o¼Ë¥Î¤½¦¡ªº¤è¦¡¦X¨ÖAÄ檺¸ê®Æ®É¤]¤Àªº²M·¡.¦p(1.2.).
¦ý·Q½Ð°Ý¤£¥Î¥¨¶°ªº¤è¦¡
¤½¦¡­n¦p¦ó¼g

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2013-11-30 10:35 ½s¿è
·PÁ¤j¤jªº¦^ÂР 
¤S·Q½Ð°Ý
1.§Ú¨Dªºµ²ªGÅã¥Ü¦b  ¸ò   ¸ê®Æ¦b¤£¦PªºÀɮתº¸Ü   ­n¦p¦ó­×§ï  
¦p¸ê®Æ¦bAÀÉ ...
joey3277 µoªí©ó 2013-11-29 10:41

­è­è¬Ý¨ìµ{¦¡¥i¥H¦A°µÂ²¤Æ,
¥u­n¦b¶}ÀY¥[¤W Dim rTar As Range
¦A©ó²Ä¤@­Ó Do ©³¤U¥[ Set rTar = .Cells(lSou, 3)
´N¥i¥H§â¨ä¤Uªº .Cells(lSou, 3) ¥Î rTar ¨ú¥N.

1.
  1. Sub nn()
  2.   Dim lSou&, lTar&
  3.   Dim sStr$
  4.   Dim rTar As Range
  5.   Dim vD, vK, vI
  6.   
  7.   Set vD = CreateObject("Scripting.Dictionary")
  8.   With Workbooks.Open(ThisWorkbook.Path & "\¸ê®Æ.xls").Sheets("Sheet1")
  9.     lSou = 2
  10.   
  11.     Do While .Cells(lSou, 3) <> ""
  12.       Set rTar = .Cells(lSou, 3)
  13.       sStr = CStr(.Cells(lSou, 1))
  14.       If InStr(1, vD(CStr(rTar)), sStr) = 0 Then
  15.         If vD(CStr(rTar)) = "" Then
  16.           vD(CStr(rTar)) = sStr
  17.         Else
  18.           vD(CStr(rTar)) = vD(CStr(rTar)) & "&" & sStr
  19.         End If
  20.       End If
  21.       lSou = lSou + 1
  22.     Loop
  23.   End With
  24.   
  25.   lSou = 0
  26.   lTar = 2
  27.   vK = vD.keys
  28.   vI = vD.items
  29.   Do While lSou < vD.Count
  30.     Cells(lTar, 1) = vI(lSou)
  31.     Cells(lTar, 2) = vK(lSou)
  32.     lTar = lTar + 1
  33.     lSou = lSou + 1
  34.   Loop
  35. End Sub
½Æ»s¥N½X
2. ±N vD(CStr(rTar)) = vD(CStr(rTar)) & "&" & sStr ¤¤ªº "&" §ï¬° "."

3. ³o´N­n¥Î¨ì Excel VBA ¹ê§@ ¦Û­q¤u§@ªí¨ç¼Æ ¤F:
¥H¤Uµ{¦¡©ñ¦b Module ¤º
  1. Function GetData(rIVar As Range, rTar As Range, iInd As Integer) As String
  2.   ' rIVar ­n¿z¿ïªº­È©Ò¦bÀx¦s®æ, rTar ²M³æ¤¤¿z¿ïÄæ­º®æ, iInd ¸ê®ÆÄæ»P¿z¿ïÄ檺Äæ¼Æ®t
  3.   Dim lRows&
  4.   Dim sStr$
  5.   Dim rRng
  6.   Dim vD, vK, vI

  7.   Application.Volatile ' ³]¬°´§µo©Ê¨ç¼Æ(¨C¦¸¬ÛÃöÀx¦s®æ¦³²§°Ê³£­n­«·s­pºâ)
  8.   Set vD = CreateObject("Scripting.Dictionary")
  9.   lRows = rTar.End(xlDown).Row - rTar.Row
  10.   Set rTar = Range(rTar, rTar.Offset(lRows))
  11.   For Each rRng In rTar
  12.     sStr = CStr(rRng.Offset(, iInd))
  13.     If InStr(1, vD(CStr(rRng)), sStr) = 0 Then
  14.       If vD(CStr(rRng)) = "" Then
  15.         vD(CStr(rRng)) = sStr
  16.       Else
  17.         vD(CStr(rRng)) = vD(CStr(rRng)) & "&" & sStr
  18.       End If
  19.     End If
  20.   Next
  21.   
  22.   lRows = 0
  23.   vK = vD.keys
  24.   vI = vD.items
  25.   Do While lRows < vD.Count
  26.     If vK(lRows) = rIVar.Text Then GetData = vI(lRows)
  27.     lRows = lRows + 1
  28.   Loop
  29. End Function

  30. Function NrSmall(rTar As Range, iI As Integer) As Integer
  31.   Dim vD
  32.   Dim rRng As Range
  33.   Dim aDat()

  34.   Application.Volatile ' ³]¬°´§µo©Ê¨ç¼Æ(¨C¦¸¬ÛÃöÀx¦s®æ¦³²§°Ê³£­n­«·s­pºâ)
  35.   Set rTar = Range([c2], [c9])
  36.   Set vD = CreateObject("Scripting.Dictionary")
  37.   ReDim aDat(0)
  38.   For Each rRng In rTar
  39.     If vD(CStr(rRng)) = "" Then
  40.       If aDat(0) <> 0 Then ReDim Preserve aDat(UBound(aDat) + 1)
  41.       aDat(UBound(aDat)) = rRng
  42.       vD(CStr(rRng)) = rRng
  43.     End If
  44.   Next
  45.   NrSmall = Application.Small(aDat, iI)
  46. End Function
½Æ»s¥N½X
E2==GetData(F2,C$2,-2) (¨ä¤UÀx¦s®æ¤½¦¡ªu¥Î)
F2==NrSmall(C$2:C$9,ROW()-1)  (¨ä¤UÀx¦s®æ¤½¦¡ªu¥Î)

TOP

        ÀR«ä¦Û¦b : ¬°¤H³B¥@­n¤p¤ß²Ó¤ß¡A¦ý¤£­n¡u¤p¤ß²´¡v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD