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

[¦r¨åª«¥ó]³æ¤@Äæ¦ìªº­È»P¦h­Ó°}¦C¤ñ¹ï«á¨Ã¤ÀÃþ¦Ü¤£¦PÄæ¦ì

[¦r¨åª«¥ó]³æ¤@Äæ¦ìªº­È»P¦h­Ó°}¦C¤ñ¹ï«á¨Ã¤ÀÃþ¦Ü¤£¦PÄæ¦ì

¦U¦ì«e½ú¦n,
¤p§Ì¤§«e¦³¦bºô¤W§ä¨ì¤@­Óµ{¦¡½X,
°Ñ¦Òºô§}¦p¤U:
http://club.excelhome.net/thread-868892-1-1.htmlz
¨ä¤¤ªº¹ê¨Ò4,
¥i±N¤@Ä椺ªº­È¨Ï¥ÎInStr¥h¤ñ¹ï¦h­Ó°}¦Cªº­È,
°²³]¦r¦ê¦³²Å¦X,¥i¤ÀÃþ¦Ü¤£¦PÄæ¦ì.

²{¦b¹J¨ìªº²Ä¤@­Ó°ÝÃD¬O,
¸Óµ{¦¡½X¬O±N°}¦Cªº­È¼g¦bµ{¦¡¤§¤¤,Ãþ¦ü³o¼Ë:
brr1 = Array("ASUS", "Sony")
¦ý³o¦b¹ê§@¤W¬O¤£¤Ó¥i¯à³o¼Ë¥h¹B¥Îªº,³q±`·|·Q­n±NÀx¦s®æ¤ºªº­È¼g¤J°}¦C¤¤,
¦Ó«D¤@­Ó¤@­Ó¤â°Ê¥´¦bµ{¦¡½X¤º.

¥i¬O,­Y·Q±NÀx¦s®æªº­È¦s¦Ü°}¦C¤¤,Ãþ¦ü³o¼Ë:
brr1 = Range("j2:j" & Cells(Rows.Count, 10).End(xlUp).Row)
«hµ{¦¡·|¥X²{¿ù»~,¤p§Ì¹ê¦b¤£ª¾¸Ó¦p¦ó¸Ñ¨M.

²Ä¤G­Ó°ÝÃD¬O,
­Y­ì©l¸ê®Æ¤ºªº­È§¹¥þ¨S¦³²Å¦X°}¦C­È¥ô¤@¦r¦êªº¸Ü,¤]·|¥X²{¿ù»~.

ªþ¤W¤p§Ì­×§ï«áªºµ{¦¡½X¤Îªþ¥ó¦p¤U:

20161020_°ÝÃD-¤ñ¹ï³æ¦æ¸ê®Æ»P¦h­Ó°}¦C.zip (11.13 KB)
  1. Sub test1()
  2. '°Ñ¦Òºô§}:http://club.excelhome.net/thread-868892-1-1.htmlz
  3. '¹ê¨Ò4


  4. Dim arr, myD1, myD2, myD3
  5. arr = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row)

  6. '­Y¨Ï¥Î¥H¤U¤è¦¡±NÀx¦s®æ¹ïÀ³¦Ü°}¦C«h·|¥X°ÝÃD
  7. 'brr1 = Range("j2:j" & Cells(Rows.Count, 10).End(xlUp).Row)
  8. 'brr2 = Range("k2:k" & Cells(Rows.Count, 11).End(xlUp).Row)
  9. 'brr3 = Range("L2:L" & Cells(Rows.Count, 12).End(xlUp).Row)


  10. brr1 = Array("ASUS", "Sony")
  11. brr2 = Array("Samsung")
  12. brr3 = Array("ªø¦¿", "¤p¦Ì")

  13. Set myD1 = CreateObject("Scripting.Dictionary")
  14. Set myD2 = CreateObject("Scripting.Dictionary")
  15. Set myD3 = CreateObject("Scripting.Dictionary")
  16. Set myD4 = CreateObject("Scripting.Dictionary")

  17. For x = 1 To UBound(arr)

  18. For i = 0 To UBound(brr1)
  19. If InStr(arr(x, 1), brr1(i)) > 0 Then
  20. myD1(arr(x, 1)) = ""
  21. GoTo 100
  22. End If
  23. Next i

  24. For j = 0 To UBound(brr2)
  25. If InStr(arr(x, 1), brr2(j)) > 0 Then
  26. myD2(arr(x, 1)) = ""
  27. GoTo 100
  28. End If
  29. Next j


  30. For k = 0 To UBound(brr3)
  31. If InStr(arr(x, 1), brr3(k)) > 0 Then
  32. myD3(arr(x, 1)) = ""
  33. GoTo 100
  34. End If
  35. Next k

  36. myD4(arr(x, 1)) = ""

  37. 100:
  38. Next x


  39. Range("b2").Resize(UBound(myD1.keys) + 1, 1) = Application.Transpose(myD1.keys)
  40. Range("c2").Resize(UBound(myD2.keys) + 1, 1) = Application.Transpose(myD2.keys)
  41. Range("d2").Resize(UBound(myD3.keys) + 1, 1) = Application.Transpose(myD3.keys)
  42. Range("e2").Resize(UBound(myD4.keys) + 1, 1) = Application.Transpose(myD4.keys)


  43. End Sub
½Æ»s¥N½X
­Y¥i¥Hªº¸Ü§Æ±æ«e½ú¯à°÷¤£§[«üÂI°g¬z,¤Q¤À·PÁÂ.

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-10-20 16:43 ½s¿è
  1. Sub U_Test()
  2. Dim xR As Range, xD, Arr, Brr, Mx&, N%, G(1 To 4), DK
  3. Arr = Range([A2], Cells(Rows.Count, 1).End(xlUp))
  4. Set xD = CreateObject("Scripting.Dictionary")
  5. For Each xR In [J2:L40]
  6.     If xR <> "" Then xD(UCase(xR)) = xR.Column - 9 'ÃöÁä¦r¨Ì¨äÄæ¦ì±a§Ç¸¹
  7. Next
  8. ReDim Brr(1 To UBound(Arr), 1 To 4)

  9. For i = 1 To UBound(Arr)
  10.     N = 4 '¹w³]§Ç¸¹¬°4
  11.     For Each DK In xD.keys
  12.         If InStr(UCase(Arr(i, 1)), DK) Then N = xD(DK): Exit For '¦³²Å¦X,¨ú¥X§Ç¸¹
  13.     Next
  14.     G(N) = G(N) + 1 '¨Ì§Ç¸¹¤£¦P, ¦U¦Û²Ö­pÄæ¦ìªºµ§¼Æ
  15.     If G(N) > Mx Then Mx = G(N) '¨ú±o³Ì¤jµ§¼Æ
  16.     Brr(G(N), N) = Arr(i, 1) '«ö§Ç¸¹¤Îµ§¼Æ¶ñ¤J¸ê®Æ¨ì°}¦C
  17. Next i
  18. [B2].Resize(Mx, 4) = Brr
  19. End Sub
½Æ»s¥N½X


InStr ·|°Ï¤À­^¤å¤j¤p¼g, ¥²¶·¨Ï¥Î UCase ²Î¤@Âର¤j¼g(LCase¤]¥i)

TOP

¦^´_ 2# ­ã´£³¡ªL

«D±`·PÁ²a´£³¡ªL«e½ú´£¨Ñ¸Ñµª,¤ñ­ì¥»ªºµ{¦¡½XÁY´î«Ü¦h,¤p§Ì¹ÄªA.
¥Ø«eÁÙ¦b§V¤OÆp¬ã¦r¨åª«¥ó¤¤,
¤@©w§V¤O¾Ç²ß,
§Æ±æ¦³´Â¤@¤é¯à¤Î±o¤W«e½úªº¸U¤À¤§¤@.

TOP

¦^´_ 3# greetingsfromtw
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. Set Rng = Range("J1").CurrentRegion.SpecialCells(xlCellTypeConstants) '¤ñ¹ï°}¦C
  4. For Each a In Range([A2], [A2].End(xlDown)) '­ì©l¸ê®Æ°j°é
  5.   For Each c In Rng
  6.      If InStr(UCase(a), UCase(c)) > 0 Then
  7.         d(c.Column) = "" '°O¦í¤ñ¹ï¨ì°}¦CªºÄæ¦ì
  8.      End If
  9.   Next
  10.   If d.Count > 0 Then 'ªí¥Ü­ì©l¸ê®Æ¤ñ¹ï¦¨¥\
  11.      For Each ky In d.keys
  12.        Cells(65536, ky - 8).End(xlUp).Offset(1, 0) = a
  13.      Next
  14.      d.RemoveAll '²MªÅ¦r¨å
  15.      Else
  16.      Cells(65536, "E").End(xlUp).Offset(1, 0) = a '¤ñ¹ï¤£¦¨¥\
  17.   End If
  18. Next
  19. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 3# greetingsfromtw
¤£¥Î¦r¨åª«¥ó¤]¥i¥H¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng As Range, R As Range, E As Range, i(1 To 2) As Integer
  4.     With Range("J1").CurrentRegion
  5.         i(1) = .Columns.Count + 1
  6.         Set Rng = .Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeConstants)  '¤ñ¹ï¦r¦ê
  7.     End With
  8.     Range([A2], [A2].End(xlDown)).Offset(, 1).Resize(, i(1)) = ""
  9.     For Each R In Range([A2], [A2].End(xlDown)) '­ì©l¸ê®Æ°j°é
  10.             i(2) = i(1)
  11.             For Each E In Rng
  12.                 If InStr(UCase(R), UCase(E)) Then
  13.                     i(2) = E.Column - Range("J1").Column + 1
  14.                     Exit For
  15.                 End If
  16.             Next
  17.           With Cells(65536, 1 + i(2))
  18.             If .EntireColumn.Find(R, Lookat:=xlPart) Is Nothing Then .End(xlUp).Offset(1) = R
  19.             '¸ê®Æ¤£­«½Æ
  20.           End With
  21.     Next
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# Hsieh

«D±`·PÁÂHsieh«e½ú¯S¦a´£¨Ñ¦r¨åª«¥óªº¥tÃþ¸Ñªk,
¤]¬O±N­ìµ{¦¡½XÁY´î¤@¤j³¡¥÷,¥O¤H¨ØªA.
¦¹½×¾Â¯u¬O°ª¤â¶³¶°,¤p§Ì¤@©w§V¤O¾Ç²ß.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2016/10/21 12:26): ¤ñ¹ïªº¸Ü¥i¥H¥Î¦bListBox¸Ì­±¶Ü,ªþÀɬݬÝ

¦^´_ 4# Hsieh


    ¤ñ¹ïªº¸Ü¥i¥H¥Î¦bListBox¸Ì­±¶Ü
    µ¥©ó§Ú¤Ï¥Õ¸Ì­±¨ä¤¤1¶µ¸ê°T
    Åý¥L¹ïÀ³«á¥h°µ¿z¿ï
    ³o¬O¥i¦æªº¶Ü

TOP

¦^´_ 5# GBKEE

«D±`·PÁÂGBKEE«e½úÄ@·N¥Î¨ä¥L¤è¦¡¨Ó´£¨Ñ¦¹°ÝÃDªº¸Ñªk,
­ì©lªºµ{¦¡½X¸û¬°¤¾ªø,
¦ý¤T¦ì«e½ú§¡»´´y²H¼g¦a¥Î¤Ö¶qµ{¦¡½X¼g¥Xº}«G¸Ñµª,
¦¹½×¾Â¹ê¦b¬Oª×ªêÂÃÀs,¦³³\¦hexcel°ª¤â«e½ú«LÁô©ó¦¹,
¦Ó¥BÁÙÄ@·NµL¨p«üÂI¹³·s¤â,¤p§Ì»Ê·P¤­¤º.

Æg½à¤§¨¥»¡±o¹L¦h¤]¾á¤ß¦³¯B¸Ø¤§¶û,

¤p§Ì¦¬¤J¤£¬O¤Ó¦h,©Ò¥H¥i¯à¨S¿ìªk«ùÄòÃÙ§U°ªÃB¶O¥Î,
¦ýÄ@·N¥H"¤ÖÃBÃÙ§U"»P"§V¤O¾Ç²ß",
¨Ó¤À¾á¯¸ªø¤j¤HºûÅ@¦¹½×¾Âªº³]³Æ¶O¥Î»P¦^À³ªO¤W«e½ú­ÌµL¨p«üÂIªº®¦¼w.
¦A¦¸·PÁÂ.

TOP

¦^´_ 8# greetingsfromtw


¢°¡D¯àÃÙ§U§Y¥i¶Q¡A³o¤]À°§U¥L¤H¥i¥H¦b¦¹Àò±oª¾ÃÑ
¢±¡D¢³¼Ó¶WªOªº¤èªk¤]³\¸û²Å¹ê±¡¡]¥H¿z¿ï¦Ó¨¥¡^¡A¦P¤@¤å¦r¥]§t¨â­ÓÃöÁä¦r®É¡A«h¨â¦¸¤À§O¨ú¥X¡A
¡@¡@¥i´ú¸Õ½d¨ÒÀɪº¨â­Óµ{¦¡¥h¤ñ¸û
¢²¡D¥t´£¨Ñ¤@­Ó¤è«K°w¹ï¿ï¨ú³æ¤@ÃöÁä¦rªº¿z¿ïªk

20161020_¤ñ¹ï³æ¦æ¸ê®Æ»P¦h­Ó°}¦Cv01.rar (14.18 KB)
¡@
¡@

TOP

¦^´_ 9# ­ã´£³¡ªL

«D±`ÁÂÁ²a´£³¡ªL«e½úªº¦^À³.

ÁöµM¥»¨­°^Ämªº¤£¬O«Ü¦h,´N¬O§Æ±æ¯à°÷¹ï½×¾Â¦³¨Ç·LÀ°§U.
¤p§Ì¦³±N¤T¦ì«e½úªº¤è¦¡³£¦³µy·L´ú¸Õ,»¡¯uªº,ÁöµM¤p§Ì³}¹Lªº¬ÛÃö½×¾Â«Ü¤Ö,
¦ý«e½ú­Ìªºµ{¦¡½X©M§Ú¦b¨ä¥L¦a¤è¬Ý¨ìªº¼gªk¥i»¡¬O§¹¥þ¤£¦P.¬Û·íÅå¤H,­È±o¬ã¨s.

¦]¥Ø«e¥»¨­ªºVBAµ{«×¤Ó®t,©Ò¥H²{¦b¥i¯à¬O¸ÕµÛ¼Ò¥é«e½úªº¼gªk,
¥ý¥h¼g¤@¨Ç°ò¥»ªº¥\¯à³o¼Ë,ÁöµM¬O²Â¤èªk,¤£¹L°_¨BÁ`¬Oµh­Wªº.
¥u¯à»¡¥ú¥Î¬Ýªº¸ò¦Û¤v¼g¬O§¹¥þ¨â¦^¨Æªü....ÁÙ±o¦A§V¤O.

¥t¥~¤]¤Q¤À·PÁ«e½ú©Ò´£¨ÑªºªþÀÉ,
·Æ¹«ÂI¨â¤Uª½±µ¿z¿ïªº³o­Ó¥\¯à¹ê¦b¤Ó²r¤F,«e©Ò¥¼¨£,
¤p§Ì¤æÁx,¤£ª¾¬O§_¤è«K³Â·Ð«e½ú«üÂI¬O«ç»ò°µ¨ìªº?
¦n¹³¤£¬O¼g¦b¥¨¶°¤¤,¤]¨S¦³¨Ï¥ÎÀx¦s®æ¤½¦¡.

TOP

        ÀR«ä¦Û¦b : ¨ü¤HÂI¤ô¤§®¦¡A¶··í´é¬u¥H³ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD