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

¦p¦ó§ä¥X³sÄòªº¼Æ¦r

P1//°}¦C¤½¦¡(¤TÁä¿é¤J)
=iferror(SMALL(IF(MMULT({1,1},COUNTIF($A1:$M1,$A1:$M1+{1;-1}))>0,--$A1:$M1),COLUMN(A1)),"")

TOP

Sub TEST()
Dim Arr, V&, U1&, U2&, i&, j%, T$
Arr = Range([A1], [m65536].End(xlUp))
For i = 1 To UBound(Arr)
    V = -9 ^ 9
    For j = 1 To UBound(Arr, 2)
        U1 = Arr(i, j):  U2 = U1
        If j < UBound(Arr, 2) Then U2 = Arr(i, j + 1)
        If U1 - V = 1 Or U2 - U1 = 1 Then T = T & "," & U1: V = U1
    Next j
    Arr(i, 1) = Mid(T, 2): T = ""
Next i
[O1].Resize(UBound(Arr)) = Arr
End Sub

TOP

¦^´_ 1# f00l01

¥þ³¡³sÄò­ÈÅã¥Ü¦b¦P¤@®æÀx¦s®æ¦p·Ó¤ù¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub tt2()
Dim Arr, TT, T%, T1%, L%, i&, j&
[o1].CurrentRegion = ""
Arr = [a1].CurrentRegion
For i = 1 To UBound(Arr)
    L = 0
    For j = 1 To UBound(Arr, 2)
        If j < UBound(Arr, 2) Then T1 = Arr(i, j + 1)
        T = Arr(i, j)
        If T1 - T = 1 Or T - L = 1 Then
            TT = TT & "," & Arr(i, j): L = T
        End If
    Next
    Arr(i, 1) = Mid(TT, 2): TT = ""
Next
Range("o1").Resize(UBound(Arr)) = Arr
End Sub
Â^¨ú.PNG

TOP

¦^´_ 13# samwang


A1 §ï¦¨ 1 ¸Õ¸Õ~~

TOP

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


·PÁ­ã¤j«ü¾É¡A¯uªº¦³°ÝÃD¡A¦³ªÅ®É¦A¨Ó·Q¬Ý¬Ý¦p¦ó¸Ñ¡A·PÁ¡C

TOP

·PÁ­ã¤j²Ó¤ß¡A¤S¾Ç¨ì¤p§Þ¥©¡A§ó·s¦p¤U¡AÁÂÁÂ

Sub tt3()
Dim Arr, TT, T%, T1%, L&, i&, j&
[o1].CurrentRegion = ""
Arr = [a1].CurrentRegion
For i = 1 To UBound(Arr)
    L = 9 ^ 9
    For j = 1 To UBound(Arr, 2)
        If j < UBound(Arr, 2) Then T1 = Arr(i, j + 1)
        T = Arr(i, j)
        If T1 - T = 1 Or T - L = 1 Then
            TT = TT & "," & T: L = T
        End If
    Next
    Arr(i, 1) = Mid(TT, 2): TT = ""
Next
Range("o1").Resize(UBound(Arr)) = Arr
End Sub

TOP

ÁÂÁ¤j®a¡A§Ú¦A§ä®É¶¡¦n¦n¤F¸Ñ¨C­Ó¸Ñ¤è¡C

¦A¦¸·PÁ¡I¡I

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-31 10:53 ½s¿è

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


    'ÁÂÁ«e½ú
'³o©«¾Ç¨ì
'1.¸ê®Æ«¬ºA:ªø¾ã¼Æªº³Ì¤p­È ¬O-2147483648
'2.ª¾¹D¤°»ò¬O¼Æ¦r·¸¦ì
'3.¤£¯à¥u¬Ý°ÝÃDªº¥¿­±!­t­±ªº¤]­n¦Ò¼{!²¦³º¥D¨¤¬O¼Æ¦r
'4.¨¾»~§P¬O«Ü­«­nªº¨Æ!
'5.«Ü¦hª¾ÃÑ»P¾Ç°Ý¬O½m²ß¤~¯àÅé·|ªº!
'6.¤Ñ§U¦Û§U¤H§U
½Ð«e½ú¦A«ü¾É!ÁÂÁÂ
Option Explicit
Sub TEST()
Dim Arr, V&, U1&, U2&, i&, j%, T$
'¡ô«Å§iÅܼÆ
Arr = Range([A1], [m65536].End(xlUp))
'¡ô¥OArr¬O°}¦C!­Ë¤J[A1]¨ìMÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ,ÂX®i¨ì¤è¥¿³Ì¤p°Ï°ìÀx¦s®æ­È
For i = 1 To UBound(Arr)
'¡ô³]¥~¶¶°j°é!±q1¨ìArr°}¦CÁa¦V³Ì«á¤@¦C¼Æ
    V = -9 ^ 9
    '¡ô¥OV¼Æ¦r¬O9ªº9¦¸¤è­t­È=(9 ^ 9)*(-1)= -387420489
    '¬d¸ß¤FVBA ªº¸ê®Æ«¬ºA: V& ªø¾ã¼Æªº³Ì¤p­È ¬O-2147483648 !
    '¬°¤°»ò¤£¥Î¥¦·íV­È??  ¦]¬°«á¤èµ{§Ç·|ÅýÅܼƷ¸¦ì(¤ñ -2147483648 §ó¤p)
    '¦]¬°Vªºªì©l­È¬O0 ¦pªG¤@¶}©l¨S¦³µ¹V¤@­Ó²Ä¤@¨Ï¥Î­È
    '«á¤èªºµ{§Ç·|»~§P[A1]=1 ®É³sÄò

    For j = 1 To UBound(Arr, 2)
    '¡ô³]¤º¶¶°j°é!±q1¨ìArr°}¦C¾î¦V³Ì«á¤@Äæ¼Æ
        U1 = Arr(i, j)
        '¡ô¥OU1¬OArr°}¦C¤º¥~°j°éªº¥D¨¤(¼Æ¦r)
        U2 = U1
        '¡ôU2 = U1¬O¬°¤F ®ø°£U2¦b«e¦¸°j°é¤¤´Ý¾l¼Æ¦r(U2ªì©l­È¬O0)
        If j < UBound(Arr, 2) Then
        '¡ô¦pªGj³o¤º°j°é¼Æ¤p©ó Arr°}¦C¾î¦V³Ì«á¤@Äæ¼Æ
           U2 = Arr(i, j + 1)
           'U2´NÅܤߦ¨¬° U1¥D¨¤¥kÃä®æªº¨º­Ó­È(¼Æ¦r)
        End If
        If U1 - V = 1 Or U2 - U1 = 1 Then
        '¡ô¦pªG¥D¨¤U1 - V =1,¬O­n§PÂ_­t¼Æ­Èªº³sÄò
        '©Î ÅܤßU2ªº-¥D¨¤U1=1

           T = T & "," & U1
           '¡ô¥OT =T¦r¦ê³s±µ "," ²Å¸¹ ,¦A³s±µ¥D¨¤U1
           V = U1
           '¡ô¥OV¼Æ¦r¬O¥D¨¤U1­È
        End If
    Next j
    Arr(i, 1) = Mid(T, 2)
    '¡ô¥O¥~°j°éArr°}¦Cªº²Ä¤@Äæ¨ú¤º°j°é T¦r¦ê¸Ìªº²Ä2­Ó¦r¶}©l(§t¦Û¤v)ªº¥þ³¡¦r¦ê
    '¡ôMid(T, 2)«á¾Ç¤µ¤Ñ¤~ª¾¹D¥i¥H³o¼Ë³¯­z!¥H«e³£¥ÎMid(T, 2,99)
    '¦]¬°²Ä¤@­Ó¦r¬O "," ²Å¸¹

    T = ""
    '¡ô¥O²MªÅ T¦r¦ê
Next i
[O1].Resize(UBound(Arr)) = Arr
'¡ôArr°}¦C ±q[O1]¶}©l¶K¤J Arr°}¦CÁa¦V³Ì«á¤@¦C¼Æ,¥u¶K¤J1Äæ¦ì¸ê®Æ
'¦P[O1].Resize(UBound(Arr), 1) = Arr,1¥i¥H¬Ù²¤
End Sub

°õ¦æ«e:
2022-10-31_100156.JPG
2022-10-31 10:49


°õ¦æµ²ªG:
2022-10-31_100206.JPG
2022-10-31 10:49

TOP

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


    ÁÂÁ«e½ú
¦¹©««á¾Ç¥Î°}¦C»P¦r¨å½m²ß¤ß±o¦p¤U
½Ð«e½ú¦A«ü¾É!ÁÂÁÂ
1.¯à¤£¥²¥Î¦r¨å´N¤£­n¨è·N¨Ï¥Î! ¸ê®Æ­Ë¶i­Ë¥X³£»Ý­n®É¶¡
2.¥ÎÅܼƪ½±µ¶Ç»¼°T®§¸û§Ö

«e½úªºµ{¦¡½X°õ¦æ10000¦Cªº®É¶¡:
1.jpg
2022-10-31 13:11


«á¾Çªºµ{¦¡½X°õ¦æ10000¦Cªº®É¶¡:
2.jpg
2022-10-31 13:11


Option Explicit
Sub TEST_2()
Dim Arr, i&, j&, T, V, Y, U1, U2, S
S = Timer
Set Y = CreateObject("Scripting.Dictionary")
Arr = Range([A1], [m65536].End(xlUp))
[O:O].ClearContents
For i = 1 To UBound(Arr)
   V = -9 ^ 9
   For j = 1 To UBound(Arr, 2)
      If j < UBound(Arr, 2) Then
         U1 = Arr(i, j): U2 = Arr(i, j + 1)
         If U1 * U2 >= 0 Then
            Y(U1) = IIf(Y(U1) <> 1, Abs((U2) - (U1)), Y(U1))
            Y(U2) = IIf(Y(U2) <> 1, Abs((U2) - (U1)), Y(U1))
         End If
      End If
   Next
   For Each T In Y.KEYS
      If Y(T) <> 1 Then
         Y.Remove (T)
      End If
   Next
   Arr(i, 1) = Join(Application.Transpose(Application.Transpose(Y.KEYS)), ",")
   Y.RemoveAll
Next
[O1].Resize(UBound(Arr)) = Arr
MsgBox Timer - S & "’"
End Sub

TOP

¦r¨åkeyÂà¸mªº¤è¦¡§ï¦¨°j°éª½±µ¼g¤J°}¦C¤Ö!µy¦n!
3.jpg
2022-10-31 13:58


Option Explicit
Sub TEST_3()
Dim Arr, i&, j&, T, V, Y, U1, U2, S
S = Timer
Set Y = CreateObject("Scripting.Dictionary")
Arr = Range([A1], [m65536].End(xlUp))
[O:O].ClearContents
For i = 1 To UBound(Arr)
   V = -9 ^ 9
   For j = 1 To UBound(Arr, 2)
      If j < UBound(Arr, 2) Then
         U1 = Arr(i, j): U2 = Arr(i, j + 1)
         If U1 * U2 >= 0 Then
            Y(U1) = IIf(Y(U1) <> 1, Abs((U2) - (U1)), Y(U1))
            Y(U2) = IIf(Y(U2) <> 1, Abs((U2) - (U1)), Y(U1))
         End If
      End If
   Next
   Arr(i, 1) = ""
   For Each T In Y.KEYS
      If Y(T) <> 1 Then
         Y.Remove (T)
         Else
            Arr(i, 1) = Arr(i, 1) & "," & T
      End If
   Next
   Arr(i, 1) = Mid(Arr(i, 1), 2)
   Y.RemoveAll
Next
[O1].Resize(UBound(Arr)) = Arr
MsgBox Timer - S & "’"
End Sub

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD