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

[µo°Ý] «O¯d¨S¦³­«½ÆªºÄæ¦ì

[µo°Ý] «O¯d¨S¦³­«½ÆªºÄæ¦ì

¥»©«³Ì«á¥Ñ ssooi ©ó 2020-12-3 00:20 ½s¿è

·Q­n¹F¨ì¥kÃäªí®æªº®ÄªG

AA.jpg
2020-12-3 00:10


¦p¹Ï¤ù©Ò¥Ü¡A§Ú·Q¹F¨ì¥kÃäªí®æªº®ÄªG
(¬õ¦â¬O§Ú¬G·N¼Ðªº¡Aªí¥Ü¨º¬O¸Ó§Ç¸¹¤º°ß¤@ªº­È)
¤£¦P§Ç¸¹­n°µ°Ï§O
­Ó§O¡u§Ç¸¹¡v¤º¦³­«½Æªº¡uµP¤l¡v
¸Ó¦C´N§R°£
­Ó§O¡u§Ç¸¹¡v¤º¤£«O¯d¦³­«½Æªº¡uµP¤l¡v


§Ú¸Õ¹LRemoveDuplicates¡A¦ý¬O¥L·|±N¦³­«½Æªº§R°£¡AµM«á«O¯d¤@­Ó¡uµP¤l¡v
·Q¤FÆZ¦h¤èªk¡A¦ý¬O´N¥d¦b§Ç¸¹¨Ì§Ç¤U¨Ó(¦³¦n´X¦Ê­Ó§Ç¸¹)¡AµM«á¬O¦b¦P­Ó¤u§@ªí¡A
¹ê¦b·Q¤£¥X¤è¦¡¤F
³Â·Ð¦U¦ì¥ý¶i
·P¿E¤£ºÉ!!!!!!!

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-3 01:24 ½s¿è

¦^´_ 1# ssooi

¨S´ú¸ÕÀɮסA¤£·Q¼gVBA~´£¨Ñ¤@­Ó¤èªkµ¹§A

ÀH«K§ä¤@­ÓÄæ¦ì(H)§â§Ç¸¹ ¸ò µP¤l ¥ÎÀH«K¤@­Ó¤À¹j²Å¸¹ ¦ê±µ°_¨Ó Ex  H1¡G =A1 & "-" & C1

¦A¥h±¼¤½¦¡Âন­È(½Æ»s>¿ï¾Ü©Ê¶K¤W>­È)¡AµM«á¥ÎExcelªº "²¾°£­«½Æ"¥\¯à¡A°w¹ï­è­è¦ê±µªºHÄæ¦ì°µ²¾°£­«½Æ

³Ì«á¦b¥Î"¸ê®Æ­åªR"¥\¯à¡A§â­è­è ²¾°£­«½ÆªºHÄæ¡A¥Î§A­è­è³]ªº¤À¹j²Å¸¹"-"¡A­«·s©î¦¨ ¨âÄæ(§Ç¸¹»PµP¤l)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2020-12-3 04:55 ½s¿è
¦p¹Ï¤ù©Ò¥Ü¡A§Ú·Q¹F¨ì¥kÃäªí®æªº®ÄªG
(¬õ¦â¬O§Ú¬G·N¼Ðªº¡Aªí¥Ü¨º¬O¸Ó§Ç¸¹¤º°ß¤@ªº­È)
¤£¦P§Ç¸¹­n°µ°Ï ...
ssooi µoªí©ó 2020-12-3 00:17

­n¿z¿ï¦C¥X¨S¦³­«½Æ(¤Ï¤§¥ç¥i)ªº¸ê®Æ,
¨Ï¥ÎDictionary¨ç¼Æ´N¥i¥H¤F:
  1. Sub aa()
  2.   Dim iI%
  3.   Dim lRow&
  4.   Dim sStr$
  5.   Dim vD1, vD2, vA
  6.   
  7.   lRow = Cells(Rows.Count, 5).End(xlUp).Row
  8.   If lRow < 2 Then lRow = 2
  9.   Range([E2], Cells(lRow, 7)).Clear ' ²M¸ê®Æ
  10.   Set vD1 = CreateObject("Scripting.Dictionary") ' ©ñ­«½Æ¦¸¼Æ
  11.   Set vD2 = CreateObject("Scripting.Dictionary") ' ©ñ¸ê®Æ¦C¸¹
  12.   lRow = 2
  13.   Do While Cells(lRow, 1) <> ""
  14.     sStr = Cells(lRow, 1) & "_" & Cells(lRow, 3) ' "_" ¸ê®Æ°Ï¹j²Å¸¹,¥i§ï¦¨¸ê®Æ¤¤¤£·|¥X²{ªº¯S©w¦r¤¸
  15.     If IsEmpty(vD1(sStr)) Then
  16.       vD1(sStr) = 1
  17.       vD2(sStr) = lRow
  18.     Else
  19.       vD1(sStr) = vD1(sStr) + 1
  20.     End If
  21.     lRow = lRow + 1
  22.   Loop
  23.   
  24.   lRow = 2
  25.   For Each vA In vD1.keys
  26.     If vD1(vA) = 1 Then ' ­«½Æ¦¸¼Æ¬°1ªº¤~¦C¥X¨Ó
  27.       For iI = 1 To 3
  28.         Cells(lRow, 4 + iI) = Cells(vD2(vA), iI)
  29.       Next
  30.       lRow = lRow + 1
  31.     End If
  32.   Next
  33. End Sub
½Æ»s¥N½X

«O¯d¨S¦³­«½ÆªºÄæ¦ì-Q1.zip (14.16 KB)

TOP

¦^´_  ssooi

¨S´ú¸ÕÀɮסA¤£·Q¼gVBA~´£¨Ñ¤@­Ó¤èªkµ¹§A

ÀH«K§ä¤@­ÓÄæ¦ì(H)§â§Ç¸¹ ¸ò µP¤l ¥ÎÀH«K¤@­Ó ...
n7822123 µoªí©ó 2020-12-3 01:22



³o¦ì¤j¤j~~¤£¦n·N«ä~¤p§Ì¤wªþ¤WÀÉ®×
TEST.zip (6.33 KB)
§Ú·Ó¤j¤jªº¤è¦¡°µ¤F¤@¦¸
¤£¹L¤£¬O§Ú·Q­nªº¤è¦¡~¤£¾å±o¬O¤£¬O§Ú¤èªk°µ¿ù

Excelªº "²¾°£­«½Æ"¥\¯à
¥L·|§R°£¨ä¥L­«½Æ¶µ¡A«O¯d¤@­Ó­È
§Ú·Q­nªº¬O~~¥u­n¬O­«½Æªº~~¥þ³¡§R°£
·P¿E¤£ºÉ
½Ð°Ý¦³¨ä¥L¤è¦¡¶Ü?
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-12-3 11:43 ½s¿è

¦^´_ 4# ssooi

§â§Ç¸¹¸òµP¤l ¦ê±µ°_¨Ó¡A´N¤£·|¥u°w¹ïµP¤l²¾°£­«½Æ

¨Ò¦p  1-T1  »P 2-T1  ·|³Qµø¬°¤£¤@¼Ëªº¡A¤£¥i¯à³Q²¾°£­«½Æ

§Ú¤j·§ª¾¹D§Aªº·N«ä¤F......¥u­n­«½Æªº³£§R°£¡A¤£¬O«O¯d°ß¤@­È
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 4# ssooi

luhpro ¤j¤jªºµ{¦¡À³¸Ó¥i¥H¥Î¡A§Ú¤]¥Î§Aªºªþ¥ó¼g¤Wµ{¦¡

µ{¦¡¦p¤U


Sub test1203()
Dim D As Object, Arr, R&, Key$
Set D = CreateObject("Scripting.Dictionary")
Arr = [A1].CurrentRegion
For R = 1 To UBound(Arr)
  Key = Arr(R, 1) & "-" & Arr(R, 3)
  If D.Exists(Key) Then D(Key) = "X" Else D(Key) = R
Next R
R = 0
For Each Item In D.Items
  If Item <> "X" Then
    R = R + 1
    Cells(R, "F").Resize(, 3) = Cells(Item, "A").Resize(, 3).Value
  End If
Next
End Sub


Àɮצp¤U

TEST-1203.rar (13.32 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 4# ssooi

§Ú¤]ı±o luhpro ¤j¤jªºµ{¦¡À³¸Ó¥i¥H¥Î¡A©Ò¥H§Úªºµ{¦¡¦p¤U¡A½Ð¦b½T»{¡AÁÂÁ¡C

Sub test()
Dim Arr, xD, N&, i&, j&, T$
Arr = Range([A1], [C65536].End(xlUp))
Set xD = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Arr)
    T = Arr(i, 1) & "_" & Arr(i, 3)
    xD(T) = xD(T) + 1
Next
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "_" & Arr(i, 3)
    If xD(T) > 1 Then GoTo 100
    N = N + 1
    For j = 1 To 3: Arr(N + 1, j) = Arr(i, j): Next
100: Next
If N > 0 Then [E1].Resize(N + 1, 3) = Arr
End Sub

TOP

¥»©«³Ì«á¥Ñ luhpro ©ó 2020-12-3 20:41 ½s¿è
¦^´_  ssooi

...
For i = 2 To UBound(Arr)
    T = Arr(i, 1) & "_" & Arr(i, 3)
  1.     If xD(T) > 1 Then GoTo 100
  2.     N = N + 1
  3.     For j = 1 To 3: Arr(N + 1, j) = Arr(i, j): Next
  4. 100: Next
½Æ»s¥N½X
...
samwang µoªí©ó 2020-12-3 13:55

¥Hµ²ºc¤Æµ{¦¡ªº·§©À¨Ó»¡,
·|«Øij¾¨¶q¤£­n¨Ï¥Î Goto«ü¥O.
¤W­z³o¬qµ{¦¡¥i¥H­×§ï¦¨: (³æ¯ÂÂà´«¦¨µ¥®Ä«ü¥O, ¨Ã¥¼ÅçÃÒ§Aªºµ{¦¡°õ¦æµ²ªG)
  1.     If xD(T) = 1 Then
  2.         N = N + 1
  3.         For j = 1 To 3: Arr(N + 1, j) = Arr(i, j): Next
  4.     End if
  5. Next
½Æ»s¥N½X
®ÄªGÀ³¸Ó¬O¬Û¦Pªº,
¤W­z¶È¨Ñ°Ñ¦Ò...

TOP

ÁÂÁ¤j®a
¤Ó·P°Ê¤F
luhpro ¤j¤jªºµ{¦¡§Ú¤@¶}©l´N¦³¸Õ¹L
¬O§¹¥þ¨S°ÝÃDªº
¥u¬O¥Lªºµ{¦¡¤ÓÃø~§ÚµLªk®ø¤Æ
ǢǢ
¦A¦¸·PÁ¤j®a

TOP

¦^´_ 8# luhpro


    ¤F¸Ñ¡A·PÁ«ü¾É

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD