[µo°Ý] §P§O­«½Æ«á¡A¨Ì·Ó±ø¥ó§R°£

Michelle-W

½Ð°Ý­n¹F¦¨¥H¤Uªº¥Øªºªº¸Ü¡A¥Ø«eªºµ{¦¡½XÀ³¸Ó¦p¦ó°µ­×§ï?
©Î¬O¦³¨ä¥L¸û¦nªº¼gªk? (¸ê®Æµ§¼Æ¤£¤îªþ¥ó³o¨Ç¡A¥u¬O²¤Æ¦¨¤ñ¸û®e©ö¸ÑÄÀ^^")
1. ¥ý¤ñ¹ïAÄæ¬O§_¦³­«½Æ
2. µo²{­«½Æ«á¡A¤ñ¹ï«á­±(C:G)¬O§_¦³«DªÅ®æ
3.1 ­Y¨âªÌ¬ÒªÅ®æ¡A§R°£¨ä¤@
3.2 ­Y¨ä¤¤¦³¤@­Ó«á­±¦³¤å¦r¡A«h§R°£¥t¤@­Ó«á­±¬°ªÅ®æªº¨º­Ó



·PÁÂ~~

§PÂ_¨Ã§R°£.rar (12.85 KB)

¤U¸ü¦¸¼Æ: 15

2016-7-20 16:23

vinejason

¦^´_ 1# Michelle-W

§Ú³q±`¥ý¥Î¥H¤U¨ç¼Æ§ä¥X­«½Æ¸ê®Æ , µM«á¦A¿z¿ï"­«½Æ" §R°£
´£¨Ñµ¹§A°Ñ¦Ò
=IF(COUNTIF($A$2:$A$6,A2)=1,"","­«½Æ")

c_c_lai

¦^´_ 1# Michelle-W

1. Sub Ex()
   
2.     Dim rng As Range, dic As Object
   
3.     Dim r As Long
   
4.    
   
5.     r = [A2].End(xlDown).Row
   
6.     Range("$A$2:$G$" & r).RemoveDuplicates Columns:=Array(2, 4), Header:=xlYes
   
7.    
   
8.     Set dic = CreateObject("scripting.dictionary")
   
9.     For Each rng In Range("A2", [A2].End(xlDown))
   
10.         If Not dic.exists(CStr(rng.Offset(, 1).Value)) Then dic(CStr(rng.Offset(, 1).Value)) = ""
    
11.         If rng.Offset(, 3) <> "" Then dic(CStr(rng.Offset(, 1).Value)) = rng.Offset(, 3)
    
12.     Next
    
13.    
    
14.     For r = Range("A2").End(xlDown).Row To 2 Step -1
    
15.         If Cells(r, 4) <> dic(CStr(Cells(r, 2).Value)) Then Rows(r).EntireRow.Delete
    
16.     Next
    
17. End Sub

½Æ»s¥N½X

¤U¸ü (48.21 KB)

2016-7-20 20:24

   §PÂ_¨Ã§R°£.rar (16.53 KB)

¤U¸ü¦¸¼Æ: 5

2016-7-20 20:24

Michelle-W

¦^´_ 2# vinejason


ÁÂÁ±zªº«üÂI :D

Michelle-W

¦^´_ 3# c_c_lai


±z¦n
¬Q¤é¨p¤U½Ð±Ð±zÃö©ó³o¬qµ{¦¡½Xªº§t·N¡A¦ý¬ã¨s¤F¤@¤ÑÁÙ¬O¦³¨Ç¦a¤è¤£À´QQ
¦]¬°§Ú¬P´Á¤@¨ì¤­³£·|¥i¯à·|¦³¸ê®Æ­n§PŪ(¸ê®Æµ§¼Æ¤£¤Ö)¡A¤£¬O¥u«]­­¦b¬P´Á¤G¦Ó¤w
§Ú·Q»¡¬ã¨s±z´£¨Ñªºµ{¦¡½X°µ­×§ï
¦ýÁÙ¬O§ï¤£¥X¨ÓQQ"
¯à¦b½Ð±zÀ°¦£¬Ý¬Ý¶Ü?
(¤£À´­þ³¡¤Àªºµ{¦¡½X¬O­«½Æ«á§P§O"¯d¤U«á­±¦³¤å¦rªº,§R°£¥t¤@­Ó­«½Æ-«á­±¬°ªÅ¥Õªº")
¦A¦¸·PÁÂ


§PÂ_¨Ã§R°£.rar (20.15 KB)

¤U¸ü¦¸¼Æ: 7

2016-7-22 14:20

c_c_lai

¦^´_ 5# Michelle-W

1. Sub Ex()
   
2.     Dim rng As Range, dic As Object
   
3.     Dim r As Long, txt As String
   
4.    
   
5.     r = [A2].End(xlDown).Row
   
6.     Range("$A$1:$G$" & r).RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes
   
7.    
   
8.     Set dic = CreateObject("scripting.dictionary")
   
9.     For Each rng In Range("A2", [A2].End(xlDown))
   
10.          If Not dic.exists(CStr(rng.Value)) Then dic(CStr(rng.Value)) = ""
    
11.         If rng.Offset(, 2) <> "" Or rng.Offset(, 3) <> "" Or rng.Offset(, 4) <> "" Or rng.Offset(, 5) <> "" Or rng.Offset(, 6) <> "" Then
    
12.             txt = Left(rng.Offset(, 2) & "          ", 10) & Left(rng.Offset(, 3) & "          ", 10) & _
    
13.                   Left(rng.Offset(, 4) & "          ", 10) & Left(rng.Offset(, 5) & "          ", 10) & Left(rng.Offset(, 6) & "          ", 10)
    
14.         Else
    
15.             txt = ""
    
16.         End If
    
17.         
    
18.         If txt <> "" Then dic(CStr(rng.Value)) = txt
    
19.      Next
    
20.    
    
21.     For r = Range("A2").End(xlDown).Row To 2 Step -1
    
22.         If Cells(r, 3) <> "" Or Cells(r, 4) <> "" Or Cells(r, 5) <> "" Or Cells(r, 6) <> "" Or Cells(r, 7) <> "" Then
    
23.             txt = Left(Cells(r, 3) & "          ", 10) & Left(Cells(r, 4) & "          ", 10) & _
    
24.               Left(Cells(r, 5) & "          ", 10) & Left(Cells(r, 6) & "          ", 10) & Left(Cells(r, 7) & "          ", 10)
    
25.         Else
    
26.             txt = ""
    
27.         End If
    
28.          If txt <> dic(CStr(Cells(r, 1).Value)) Then Rows(r).EntireRow.Delete
    
29.     Next
    
30. End Sub

½Æ»s¥N½X

¤U¸ü (56.67 KB)

2016-7-22 17:06

­ã´£³¡ªL

Sub TEST()
Dim xR As Range, xDic, xU As Range, N&, V&, XX As Range
Set xDic = CreateObject("Scripting.Dictionary")
For Each xR In Range([A2], [A65536].End(xlUp))
¡@¡@N = xDic(xR.Value)
¡@¡@If N = 0 Then xDic(xR.Value) = xR.Row: GoTo 101
¡@¡@V = Application.CountA(Range(xR(1, 3), xR(1, 7)))
¡@¡@Set XX = xR
¡@¡@If V > 0 Then Set XX = Range("A" & N): xDic(xR.Value) = xR.Row
¡@¡@If xU Is Nothing Then Set xU = XX Else Set xU = Union(xU, XX)
101: Next
If Not xU Is Nothing Then xU.EntireRow.Delete
End Sub

ªí®æ©³¤Uªº»¡©ú¥ý§R°£¦A°õ¦æ¡A
µ{¦¡½X¤£¤Ó¦n¸ÑÄÀ¡A¥ý¥Î¬Ý¬Ý¡I

Michelle-W

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


ÁÂÁ±zªºÀ°¦£¡A¥i¥H¥Î~
¦ý§Ú¦³ÂI¦n©_±zµ{¦¡½Xªº·N«ä
¦pªG¥i¥Hªº¸Ü¡A¥i¥H³Â·Ð±z¤j·§Á¿¸Ñ¤@¤U¶Ü?^^"
·PÁÂ

­ã´£³¡ªL

¦^´_ 9# Michelle-W

¡Õ«ôª÷¤kªº¦n¤Í¦W³æ¡Ö
¥H¤U¥u¸ÑÄÀÅÞ¿è¡A¤£¸ÑÄÀ»yªk¡A¬°¤F©ö©ó¤F¸Ñ¡A³¡¥÷¦³­×§ï¡A
¡exDic¦r¨åÀÉ¡f¤Î¡eUnionÁp¶°Àx¦s®æ¡fªº¥Îªk¡A¦Û¦æ¥h¬d»¡©ú¡G
¡@
Sub TEST()
Dim xR As Range, xDic, xU As Range, N&, V&, XX As Range
Set xDic = CreateObject("Scripting.Dictionary")
For Each xR In Range([A2], [A65536].End(xlUp))
¡@
¡@¡@N = xDic(xR.Value)
¡@¡@If N = 0 Then xDic(xR.Value) = xR.Row: GoTo 101
¡@¡@'¡Ä²Ä¤@¦¸¹J¨£§A¡A½Ð¯d¤U±zªº¹q¸Ü¸¹½X(¦C¸¹)¡GxDic(xR.Value) = xR.Row
¡@¡@'¡ÄN = 0¡Aªí¥Ü²Ä¤@¦¸ªº¬Û¹J¡A°O¦í¦C¸¹«á¡A²¤¹L¤U¤èªº»y¥y(GoTo 101)
¡@
¡@¡@V = Application.CountA(Range(xR(1, 3), xR(1, 7)))¡@
¡@¡@'¡Ä²Ä¤G¦¸¡]¤Î¥H«á¡^¹J¨£§A¡A½Ð°Ý§A¤f³U¦³¨S¦³¿ú¡]Àˬd«DªÅ®æ¡^
¡@
¡@¡@If V = 0 Then Set XX = xR
¡@¡@'¡Ä¦pªG¨S¦³¿ú¡A§A³o¦¸ªº·s¹q¸Ü¸¹½X§Ú¤£·Q¯d¡GSet XX = xR
¡@
¡@¡@If V > 0 Then Set XX = Range("A" & N): xDic(xR.Value) = xR.Row
¡@¡@'¡Ä¦pªG¦³¿ú¡A¤W¦¸¯dªº¹q¸Ü§@¼o¡GSet XX = Range("A" & N)
¡@¡@'¡Ä´«¯d³o¦¸ªº·s¹q¸Ü¸¹½X¡GxDic(xR.Value) = xR.Row
¡@
¡@¡@If xU Is Nothing Then Set xU = XX Else Set xU = Union(xU, XX)
¡@¡@'¡Ä±N­n§@¼oªº¹q¸Ü¸¹½X¶°¤¤°_¨Ó
¡@
101: Next
¡@
If Not xU Is Nothing Then xU.EntireRow.Delete
'¡Ä¤@¦¸§R¥h§@¼o¹q¸Ü¸¹½X
End Sub

c_c_lai

¦^´_ 7# ­ã´£³¡ªL
­ã¤j¦b¦¹½d¨Ò¤¤¡AÀ³¥Îªwªj±Æ§Ç (Bubble Sort) ªº³B²z¡A
¯u¬O«ê·í¦n³B¡Aªí²{ªº²OºvºÉ­P¡A¦b¦¹¨ü±Ð¤F¡AÁÂÁ±z¡I