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

[µo°Ý] ¤ñ¹ï¸ê®Æ½Æ»s¦Ü¤u§@ªí¨Ã±Æ§Ç

[µo°Ý] ¤ñ¹ï¸ê®Æ½Æ»s¦Ü¤u§@ªí¨Ã±Æ§Ç

¦U¦ì«e½ú
1. ¨Ì·Ó ¡ª³æ¦ì¡ª¤u§@ªí¤§D3:G3¡u³æ¦ì½s½X¡v¦W³æ¡A½Æ»s¡ª¸ê®Æ¡ª¤u§@ªí¤º¬Û¦P³æ¦ì½s½Xªº¸ê®Æ¦Ü¡ª³æ¦ì¡ª¤u§@ªí¡C
2. ¦A¨Ì·Ó³æ¦ì½s½X¡B¤é´Á¤Î©m¦W¶¶§Ç±Æ§Ç¸ê®Æ¡C
·PÁ«ü¾É

b211.rar (4.48 KB)
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¦^´_ 1# b9208
  1. Sub ex()
  2. Dim A As Range, Rng As Range
  3. With Sheets("¸ê®Æ")
  4.    For Each A In .Range(.[F6], .[F6].End(xlDown))
  5.       If Application.CountIf(Sheets("³æ¦ì").Rows(3), A) > 0 Then
  6.          If Rng Is Nothing Then Set Rng = A Else Set Rng = Union(Rng, A)
  7.       End If
  8.    Next
  9. End With
  10. With Sheets("³æ¦ì")
  11. .[A19].CurrentRegion.Offset(1) = ""
  12. If Not Rng Is Nothing Then Rng.EntireRow.Copy .[A20]
  13. .[A19].CurrentRegion.Sort key1:=.[F20], key2:=.[E20], key3:=.[H20], Header:=xlYes
  14. End With
  15. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 2# Hsieh
·PÁÂHsiehª©¥D
¥Ø«e¨Ï¥Î¤Wok
§V¤O¾Ç²ßµ{¦¡Å޿褤
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 3# b9208
  1. Option Explicit
  2. Sub Ex()  'AdvancedFilter ¤èªk (¶i¶¥¿z¿ï)
  3.     Dim Rng  As Range, CopyTo As Range
  4.     Set Rng = Sheets("¸ê®Æ").Range("a5").CurrentRegion      '¶i¶¥¿z¿ïªº: ¸ê®Æ²M³æ½d³ò(¸ê®Æ®w)
  5.     'CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ¡C
  6.     With Sheets("³æ¦ì")
  7.         Set CopyTo = .Range(.[A19], .[A19].End(xlToRight))   '«ü©w³Q½Æ»s¦Cªº¥Ø¼Ð½d³ò
  8.         Rng.AdvancedFilter xlFilterCopy, .Range(.[C3], .[C3].End(xlDown)), CopyTo, True
  9.                                         '.Range(.[C3], .[C3].End(xlDown))      '¶i¶¥¿z¿ï:·Ç«h½d³ò
  10.     End With
  11.     With CopyTo.CurrentRegion
  12.         .Sort key1:=.Cells(6), key2:=.Cells(3), key3:=.Cells(8), Header:=xlYes
  13.     End With
  14.         'key1:=.Cells(6) '²Ä¤@­Ó±Æ§ÇÄæ¦ì: .Cells(6) ->³æ¦ì½s½X [F19]
  15.         'key2:=.Cells(3) '²Ä¤G­Ó±Æ§ÇÄæ¦ì: .Cells(3) ->¤é´Á [C19]
  16.         'key3:=.Cells(8) '²Ä¤T­Ó±Æ§ÇÄæ¦ì: .Cells(8) ->©m¦W [H19]
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# GBKEE
·PÁÂGBKEE
«ü¾É¿z¿ï±ø¥óÀx¦s®æ³]­p¤è¦¡
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 4# GBKEE
¦A½Ð±Ðª©¥D
±Æ§Ç«á¡u³æ¦ì½s½X¡B¤é´Á¤Î©m¦W¡v¤T­ÓÄæ¦ì¸ê®Æ¬Û¦PªÌ¡A
±q²Ä¤Gµ§¬Û¦P¸ê®Æ¶}©l¡A¥þ¦C¡]A~MÄæ¡^¸ê®ÆÀx¦s®æ¶ñº¡¶À¦â¡C¦p¤U¹Ï¤ù¡C
ÁÂÁ«ü¾É
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 6# b9208
¬O³o¼Ë¶Ü?
  1. Option Explicit
  2. Sub Ex()  'AdvancedFilter ¤èªk (¶i¶¥¿z¿ï)
  3.     Dim Rng  As Range, CopyTo As Range, i As Integer
  4.     Dim A As String, B As String
  5.     Set Rng = Sheets("¸ê®Æ").Range("a5").CurrentRegion      '¶i¶¥¿z¿ïªº: ¸ê®Æ²M³æ½d³ò(¸ê®Æ®w)
  6.     'CurrentRegion ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥Ø«eªº°Ï°ì¡C¥Ø«e°Ï°ì¬O«ü¥H¥ô·NªÅ¥Õ¦C¤ÎªÅ¥ÕÄ檺²Õ¦X¬°Ãä¬Éªº½d³ò¡C°ßŪ¡C
  7.     With Sheets("³æ¦ì")
  8.         Set CopyTo = .Range(.[A19], .[A19].End(xlToRight))   '«ü©w³Q½Æ»s¦Cªº¥Ø¼Ð½d³ò
  9.         CopyTo.CurrentRegion.Interior.ColorIndex = 0         'Àx¦s®æ©³¦â: ³]¬°µL
  10.         Rng.AdvancedFilter xlFilterCopy, .Range(.[c3], .[c3].End(xlDown)), CopyTo, True
  11.                                         '.Range(.[C3], .[C3].End(xlDown))      '¶i¶¥¿z¿ï:·Ç«h½d³ò
  12.     End With
  13.     With CopyTo.CurrentRegion
  14.         .Sort key1:=.Cells(6), key2:=.Cells(3), key3:=.Cells(8), Header:=xlYes
  15.         For i = 2 To .Rows.Count - 1
  16.             A = .Rows(i).Cells(3) & .Rows(i).Cells(6) & .Rows(i).Cells(8)
  17.             B = .Rows(i + 1).Cells(3) & .Rows(i + 1).Cells(6) & .Rows(i + 1).Cells(8)
  18.             If A = B Then
  19.                 .Rows(i).Interior.Color = vbYellow             'Àx¦s®æ©³¦â: ³]¬°¶À¦â
  20.                 .Rows(i + 1).Interior.Color = vbYellow
  21.             End If
  22.         Next
  23.         
  24.     End With
  25.         'key1:=.Cells(6) '²Ä¤@­Ó±Æ§ÇÄæ¦ì: .Cells(6) ->³æ¦ì½s½X [F19]
  26.         'key2:=.Cells(3) '²Ä¤G­Ó±Æ§ÇÄæ¦ì: .Cells(3) ->¤é´Á [C19]
  27.         'key3:=.Cells(8) '²Ä¤T­Ó±Æ§ÇÄæ¦ì: .Cells(8) ->©m¦W [H19]
  28. End Sub
½Æ»s¥N½X

TOP

¦^´_ 7# GBKEE
«D±`·PÁ«ü¾É
¦ý°õ¦æ«á¡A¨S¦³±N¡ª¸ê®Æ¡ª¤u§@ªí²Å¦X±ø¥óªº¸ê®Æ¦C½Æ»s¨ì¡ª³æ¦ì¡ª¤u§@ªí¡C
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 8# b9208
4#¹Ï¤ù   A3 -> ¶i¶¥¿z¿ï·Ç«hÄæ¦ì ³æ¦ì½s½X  »P¤u§@ªí[¸ê®Æ] ªº³æ¦ì½s½XÄæ¦ì¦WºÙ­n¤@¼Ë

TOP

¦^´_ 9# GBKEE
GBKEEª©¥D
°õ¦æ«á²Ä¤Gµ§¬Û¦P¸ê®Æ¦C¨S¦³½Æ»s¨ì¡ª³æ¦ì¡ª¤u§@ªí¡C¦pªþ¥ó

b3.rar (10.63 KB)
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD