²Å¦X«ü©w±ø¥ó.¨Ã¦X¨Ö¶Ç¦^¬Û¹ïÀ³Àx¦s®æ¤º®e.
- ©«¤l
- 18
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 26
- ÂI¦W
- 0
- §@·~¨t²Î
- win xp
- ³nÅ骩¥»
- office2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2013-6-17
- ³Ì«áµn¿ý
- 2020-6-20
|
²Å¦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®æ |
|
|
|
|
|
|
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 15
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-11-12
|
¦^´_ 1# joey3277
³o¥Î Excel VBA ¸û®e©ö¼g :- Sub nn()
- Dim lSou&, lTar&
- Dim sStr$
- Dim vD, vK, vI
-
- Set vD = CreateObject("Scripting.Dictionary")
- lSou = 2
-
- Do While Cells(lSou, 3) <> ""
- sStr = CStr(Cells(lSou, 1))
- If InStr(1, vD(CStr(Cells(lSou, 3))), sStr) = 0 Then
- If vD(CStr(Cells(lSou, 3))) = "" Then
- vD(CStr(Cells(lSou, 3))) = sStr
- Else
- vD(CStr(Cells(lSou, 3))) = vD(CStr(Cells(lSou, 3))) & "&" & sStr
- End If
- End If
- lSou = lSou + 1
- Loop
-
- lSou = 0
- lTar = 2
- vK = vD.keys
- vI = vD.items
- Do While lSou < vD.Count
- Cells(lTar, 5) = vI(lSou)
- Cells(lTar, 6) = vK(lSou)
- lTar = lTar + 1
- lSou = lSou + 1
- Loop
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 18
- ¥DÃD
- 7
- ºëµØ
- 0
- ¿n¤À
- 26
- ÂI¦W
- 0
- §@·~¨t²Î
- win xp
- ³nÅ骩¥»
- office2003
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2013-6-17
- ³Ì«áµn¿ý
- 2020-6-20
|
·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 |
|
|
|
|
|
|
- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 15
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-11-12
|
¥»©«³Ì«á¥Ñ 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°µÂ²¤Æ,
¥un¦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.- Sub nn()
- Dim lSou&, lTar&
- Dim sStr$
- Dim rTar As Range
- Dim vD, vK, vI
-
- Set vD = CreateObject("Scripting.Dictionary")
- With Workbooks.Open(ThisWorkbook.Path & "\¸ê®Æ.xls").Sheets("Sheet1")
- lSou = 2
-
- Do While .Cells(lSou, 3) <> ""
- Set rTar = .Cells(lSou, 3)
- sStr = CStr(.Cells(lSou, 1))
- If InStr(1, vD(CStr(rTar)), sStr) = 0 Then
- If vD(CStr(rTar)) = "" Then
- vD(CStr(rTar)) = sStr
- Else
- vD(CStr(rTar)) = vD(CStr(rTar)) & "&" & sStr
- End If
- End If
- lSou = lSou + 1
- Loop
- End With
-
- lSou = 0
- lTar = 2
- vK = vD.keys
- vI = vD.items
- Do While lSou < vD.Count
- Cells(lTar, 1) = vI(lSou)
- Cells(lTar, 2) = vK(lSou)
- lTar = lTar + 1
- lSou = lSou + 1
- Loop
- End Sub
½Æ»s¥N½X 2. ±N vD(CStr(rTar)) = vD(CStr(rTar)) & "&" & sStr ¤¤ªº "&" §ï¬° "."
3. ³o´Nn¥Î¨ì Excel VBA ¹ê§@ ¦Ûq¤u§@ªí¨ç¼Æ ¤F:
¥H¤Uµ{¦¡©ñ¦b Module ¤º- Function GetData(rIVar As Range, rTar As Range, iInd As Integer) As String
- ' rIVar n¿z¿ïªºÈ©Ò¦bÀx¦s®æ, rTar ²M³æ¤¤¿z¿ïÄ溮æ, iInd ¸ê®ÆÄæ»P¿z¿ïÄ檺Äæ¼Æ®t
- Dim lRows&
- Dim sStr$
- Dim rRng
- Dim vD, vK, vI
- Application.Volatile ' ³]¬°´§µo©Ê¨ç¼Æ(¨C¦¸¬ÛÃöÀx¦s®æ¦³²§°Ê³£n«·spºâ)
- Set vD = CreateObject("Scripting.Dictionary")
- lRows = rTar.End(xlDown).Row - rTar.Row
- Set rTar = Range(rTar, rTar.Offset(lRows))
- For Each rRng In rTar
- sStr = CStr(rRng.Offset(, iInd))
- If InStr(1, vD(CStr(rRng)), sStr) = 0 Then
- If vD(CStr(rRng)) = "" Then
- vD(CStr(rRng)) = sStr
- Else
- vD(CStr(rRng)) = vD(CStr(rRng)) & "&" & sStr
- End If
- End If
- Next
-
- lRows = 0
- vK = vD.keys
- vI = vD.items
- Do While lRows < vD.Count
- If vK(lRows) = rIVar.Text Then GetData = vI(lRows)
- lRows = lRows + 1
- Loop
- End Function
- Function NrSmall(rTar As Range, iI As Integer) As Integer
- Dim vD
- Dim rRng As Range
- Dim aDat()
- Application.Volatile ' ³]¬°´§µo©Ê¨ç¼Æ(¨C¦¸¬ÛÃöÀx¦s®æ¦³²§°Ê³£n«·spºâ)
- Set rTar = Range([c2], [c9])
- Set vD = CreateObject("Scripting.Dictionary")
- ReDim aDat(0)
- For Each rRng In rTar
- If vD(CStr(rRng)) = "" Then
- If aDat(0) <> 0 Then ReDim Preserve aDat(UBound(aDat) + 1)
- aDat(UBound(aDat)) = rRng
- vD(CStr(rRng)) = rRng
- End If
- Next
- NrSmall = Application.Small(aDat, iI)
- 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¥Î) |
|
|
|
|
|
|