samwang

¦^´_ 2# sillykin


¤£¦n·N«ä¡A¬Ý¤£¤ÓÀ´±zªº»Ý¨D¡A¥i§_½Ð¦A»¡©ú¸Ô²Ó¤@ÂI¶Ü? ÁÂÁÂ

samwang

¦^´_ 4# sillykin


½Ð°Ý¦p¹Ï¤ù¬õ¦â¨ºµ§¦³»Ý­n¦C¶Ü?

samwang

¦^´_ 4# sillykin

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ
¥t¥~¡A±z´£¨Ñ¸ê®Æ¤£¬Û²Å¦Xªºµ²ªG©M§Úªºµ²ªG¤£¤@¼Ë¡A½Ð¦A½T»{¡AÁÂÁÂ

Sub test()
Dim Arr, xD, Brr(), Crr(), T$, T1$, n%, n1%, i&, j%
Arr = Range([¤ñ¹ï!a1], [¤ñ¹ï!d65536].End(3))
Set xD = CreateObject("Scripting.Dictionary")
ReDim Brr(1 To UBound(Arr), 1 To 4)
ReDim Crr(1 To UBound(Arr), 1 To 4)
n = 1
For i = 2 To UBound(Arr)
    If i = UBound(Arr) Then GoTo 99
    If Arr(i, 1) <> "" And Arr(i + 1, 1) = "" Then
        T = Arr(i, 1) & "_" & Arr(i, 3)
        For i2 = i + 1 To UBound(Arr)
            If Arr(i2, 4) <> "" Then
                T1 = Arr(i2, 4) & "_" & Arr(i, 3)
                If T = T1 Then
                    Brr(n, 1) = Arr(i, 1)
                    Brr(n, 3) = Arr(i, 3)
                    Brr(n + 1, 2) = Arr(i2, 2)
                    Brr(n + 1, 3) = Arr(i2, 3)
                    Brr(n + 1, 4) = Arr(i2, 4)
                    n = n + 2: xD(T1) = 1: Exit For
                End If
            Else
                Exit For
            End If
        Next i2
    End If
99: Next i

For i = 2 To UBound(Arr)
    If Arr(i, 1) <> "" Then
        T = Arr(i, 1) & "_" & Arr(i, 3)
    Else
        T = Arr(i, 4) & "_" & Arr(i, 3)
    End If
    If xD(T) <> 1 Then
        n1 = n1 + 1
        For j = 1 To 4: Crr(n1, j) = Arr(i, j): Next
    End If
Next
Sheets("¤ñ¹ï").[h2].Resize(n, 4) = Brr
Sheets("¤ñ¹ï").[o2].Resize(n1, 4) = Crr
End Sub

samwang

¦^´_ 7# sillykin


¬O§_¯à§Q¥Î¤H¤u¦W¥U¡B·|­p¦W¥U¥ý§ì¨ú¨ì¡]¤ñ¹ï°Ï¡^A~DÄæ¦ì¦b¨Ì¡y¤á¦W¡z±Æ§Ç«á¡A¦b°õ¦æ«áÄòµ{§Ç©O¡H¡H
>>¤£¦n·N«ä¡A¬Ý¤£À´±zªº»Ý¨D¡A½Ð¸ÑÄÀ¸Ô²Ó¤@ÂI¡AÁÂÁÂ

samwang

¦^´_ 9# sillykin


§â¡y¤H¤u¦W¥U¡z¡B¡y·|­p¦W¥U¡z¸ê®Æ§ì¨ì¤ñ¹ï°ÏA~DÄæ¦ì¤¤¡A¦b¨Ì¡y¤á¦W¡z±Æ§Ç
>> §Ú¤£¤F¸Ñ¡A¦b¨Ì¡y¤á¦W¡z±Æ§Ç¡A¬°¤°»ò¥Ò1¡B¥Ò10¡B¥Ò11...¥Ò2¡A³W«h¬O¤°»ò?
¸ê®Æ±Æ¦C¡A·|­p¦W¥U*2µ§¡B¤H¤u¦W¥U*1µ§¡B·|­p¦W¥U*1¡B¤H¤u¦W¥U*1µ§.... ¡A³W«h¬O¤°»ò?
¥i§_´y­z¸Ô²Ó¤@ÂI?ÁÂÁÂ

samwang

¦^´_ 11# sillykin


H~Kªºµ²ªG¬O¥ÑA~DÄæ(¤H¤u½Æ»s¤Gªí³æ¦b±Æ§Ç)²£¥X(­ì¥ý±zªº»Ý¨D)¡A  
V~Yªºµ²ªG¬O¥Ñ"¤H¤u¦W¥U"©M"·|­p¦W¥U"²£¥X¨Ì¾Ú±ø¥ó¤@¼Ë(¤á¦W+¬d¸ß¤é)
´N¦p¥ý«e¦b¸ß°Ý±zA~DÄ檺¸ê®Æ³W«h¡A©Ò¥H¤@ª½¤£ª¾¦p¦ó¤U¤â¡A
½Ð°Ý±z¦³»Ý¨DV~Yªºµ{¦¡»Ý¨D¶Ü?

samwang

¦^´_ 13# sillykin

¬Û¦P: V~Yªºµ²ªG¬O¥Ñ"¤H¤u¦W¥U"©M"·|­p¦W¥U"²£¥X¨Ì¾Ú±ø¥ó¤@¼Ë(¤á¦W+¬d¸ß¤é)
¤£¬Û¦P: ±ø¥ó¦p¤W¡Aµ²ªGAA~ADÄæ

¦ý¤]»Ý­nA-DÄæ¦ìªº½Æ»s¤Î±Æ§Ç >> ³oµLªk¼g¡A¦]¬°¨S¦³³W«h

Sub test()
Dim Arr, Brr(), Crr(), xD, xD1, T$, i&, n%, n1%
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
Arr = Range([¤H¤u¦W¥U!a1], [¤H¤u¦W¥U!c65536].End(3))
ReDim Brr(1 To UBound(Arr), 1 To 4) '¬Û¦P
ReDim Crr(1 To UBound(Arr), 1 To 4) '¤£¬Û¦P
For i = 2 To UBound(Arr)
    T = Arr(i, 3) & "_" & Arr(i, 1)
    xD(T) = Array(Arr(i, 1), Arr(i, 3))
Next
Arr = Range([·|­p¦W¥U!a1], [·|­p¦W¥U!c65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 2) & "_" & Arr(i, 3)
    xD1(T) = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3))
Next
For Each ky In xD
    If xD1.Exists(ky) Then
        n = n + 1: Brr(n, 1) = xD(ky)(0): Brr(n, 3) = xD(ky)(1)
        n = n + 1: For j = 2 To 4: Brr(n, j) = xD1(ky)(j - 2): Next
    Else
        n1 = n1 + 1: Crr(n1, 1) = xD(ky)(0): Crr(n1, 3) = xD(ky)(1)
    End If
Next
For Each ky In xD1
    If Not xD.Exists(ky) Then
        n1 = n1 + 1: For j = 2 To 4: Crr(n1, j) = xD1(ky)(j - 2): Next
    End If
Next
With Sheets("¤ñ¹ï")
    If n > 0 Then   '¬Û¦P
        .[v1:y1] = Array("¤é´Á", "²Î¤@½s¸¹", "¤á¦W", "¬d¸ß¤é")
        .[v2].Resize(n, 4) = Brr
    End If
    If n1 > 0 Then  '¤£¬Û¦P
        .[aa1:ad1] = Array("¤é´Á", "²Î¤@½s¸¹", "¤á¦W", "¬d¸ß¤é")
        .[aa2].Resize(n1, 4) = Crr
    End If
End With
End Sub