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

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

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

¤j®a¦n¡A²×©ó¼õ¨ì¤¤¾Ç¥Í¥i¥Hµo°Ý¡A«Ü·PÁ¯঳³o­Ó¾÷·|¯à§ä¨ì·|Excelªº«e½úÀ°¦£

(ªí®æ¸Ô¦pªþ¥ó)
ªí®æ¤ºªºÀH¾÷¼Æ¦r¤w«ö¤j¤p¥Ñ¥ª¦Ü¥k±Æ¦C¡A½Ð°Ý¬O§_¦³¨ç¼Æ©Î¤½¦¡¯à¦C¥X¨C¤@¦C¤¤³sÄòªº¼Æ¦r¡H
(¨Ò¦p¡G1,,3,4,5,9,12,13,...¡A¯àÅã¥Ü¸¹½X³sÄòªÌ¬°3,4,5ÁÙ¦³12,13)
§¹¾ãªº¼Æ¶q¤j·§·|¦³´X¦Ê~2¤d¦C¤£µ¥¡A¤@¦C¦CÀ°¦£ºâ§Ú¥i¯à¨S¿ìªk@@

¦³¥ý·j´M¹L©¹¤å³¹¡A¦ý¦n¹³©M§Úªº°ÝÃD¤£¤Ó¤@¼Ë  (¤p§Ì©Ò¾Ç¬Æ²L¡A¥i¯à¬Ý¹Lµª®×¦Û¤v³£¤£ª¾¹D¡AÁٽЫe½ú­Ì¨£½Ì)

¦A½Ð½×¾Â¤Wªº¤j¯«­ÌÀ°¦£¡C¦A¦¸·PÁ½׾ºûÅ@¹Î¶¤©M¤j®a¯àµ¹§Ú³o­Óµo°Ýªº¾÷·|¡CÁÂÁ¡I

Files.zip (6.89 KB)

½d¨Òªí®æ

¥»©«³Ì«á¥Ñ quickfixer ©ó 2021-3-29 14:50 ½s¿è

¦^´_ 1# f00l01



   

²Â¤èªk
NÄæ­n¯dªÅ¥Õ
Sub test()
    Columns("O:AA").ClearContents
    For r = 1 To 10 '¨ì²Ä10¦C
    cc = 1
    ccc = 0
    For c = 2 To 14 '³Ì«á¤@Äæ+1
        If Cells(r, c) - Cells(r, c - 1) <> 1 Then
            ccc = ccc + 1
            For i = cc To c - 1
            Cells(r, ccc + 14) = Cells(r, ccc + 14) & "," & Cells(r, i) '¸ê®Æ±q³Ì«á¤@Äæ+2¶}©l©ñ
            Next
            cc = c
        End If
    Next
    Next
End Sub

TOP

¦^´_ 1# f00l01



­×¥¿¤@¤U,¥u¦C¥X³sÄò¼Æ¦r
    Sub test2()
    Columns("O:AA").ClearContents
    For r = 1 To 10
    cc = 1
    ccc = 0
    For c = 2 To 14
        If Cells(r, c) - Cells(r, c - 1) <> 1 Then
            s = ""
            For i = cc To c - 1
            s = s & "," & Cells(r, i)
            Next
            
            If UBound(Split(s, ",")) <> 1 Then
                ccc = ccc + 1
                Cells(r, ccc + 14) = s
            End If
            cc = c
        End If
    Next
    Next
End Sub

TOP

¦^´_ 1# f00l01


¦]¬°A1:M5¸Ìªº¼Æ¦r¦³¨Ç¬O¤å¦r®æ¦¡¡A¨Ï¥ÎN(OFFSET())¤½¦¡®ÉµLªk§ì¥X¼Æ¦r¡A­×¥¿¬°¯Â¼Æ¦r¦p¤U
¤½¦¡A7 =--A1
½Æ»s¦Ü A7:M11

¿ï¾Ü O7:AA7
¿é¤J°}¦C¤½¦¡  = IF(MMULT({1,1},IFERROR(1/((A7:M7+{-1;1}) = N(OFFSET($A7,,COLUMN(A:M)+{-2;0}))),0)),A7:M7,"")
¥HCTRL+SHIFT+ENTER ¿é¤J¤½¦¡

¿ï¾Ü O7:AA7¡A¤U©Ô½Æ»s¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

´ú¸Õ¼Æ¦r.zip (10.19 KB)

´ú¸ÕÀÉ®×
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 1# f00l01

¸Õ¸Õ¬Ý
Sub ex()
Dim Arr As Variant, C As Variant, X%, Y%
[A10].CurrentRegion.ClearContents   '¸ê®Æ©ñ¸m¦ì¸m,²M°£¸ê®Æ(½Ð¦Û¦æ½Õ¾ã)
Arr = [a1].CurrentRegion
Set C = Nothing
For X = 1 To UBound(Arr)
   For Y = 1 To UBound(Arr, 2) - 1
      If Cells(X, Y) - Cells(X, Y + 1) = -1 Then  '§PÂ_¬O§_¬°³sÄò¼Æ­È
         If C Is Nothing Then
            Set C = Cells(X, Y).Resize(, 2)
         Else
            Set C = Union(C, Cells(X, Y).Resize(, 2))
         End If
      End If
   Next
   C.Copy [A10].Offset(X).Resize(, C.Count)    '¸ê®Æ©ñ¸m¦ì¸m(½Ð¦Û¦æ½Õ¾ã)
   Set C = Nothing
Next
End Sub

TOP

¦^´_ 1# f00l01

¤£ª¾¹D¬O§_¬°¼Ó¥D»Ý¨D¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub tt()
Dim Arr, Brr(), T%, T1%, L%, M%, i&, j&, C%
Columns("O:AA").ClearContents
Arr = [a1].CurrentRegion
ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2))
For i = 1 To UBound(Arr)
    M = 0: L = 0: C = 0
    For j = 1 To UBound(Arr, 2)
        If j + 1 > UBound(Arr, 2) Then
            If T1 = T + 1 Then Brr(i, M) = Mid(Brr(i, M) & "," & Arr(i, j), 2)
            Exit For
        End If
        T = Arr(i, j): T1 = Arr(i, j + 1)
        If T1 = T + 1 Then
            If C = 0 Then M = M + 1
            Brr(i, M) = Brr(i, M) & "," & Arr(i, j): C = 1
        Else
            If T > L + 1 Then GoTo 99
            Brr(i, M) = Mid(Brr(i, M) & "," & Arr(i, j), 2): C = 0
        End If
        L = T
99:  Next
Next
Range("o1").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
End Sub

Â^¨ú.PNG (15.89 KB)

Â^¨ú.PNG

TOP

·Q¤£¨ì°Ý¨­Ã䪺¤H³£¸Ñ¤£¥X¨Óªº°ÝÃD

¨Ó³Â»¶®a±Ú°Q½×°Ï¶}°Ý¤@¤U¤l´N¸õ¥X¤j¯«À°¦£~~

·PÁ  quickfixer (¶W§Ö¡I) ÁÙ¦³ ML089 ªº¨â¦¸¶K¤å¸Ô¸Ñ

¤]­nÁÂÁ jcchiang¡B samwang ´£¨Ñ¸Ñ¤è

§Ú²{¦b³£¨Ó¸Õ¸Õ¬Ý¡A¦³°ÝÃD¦A«e¨Ó«ô°Ý

¦A¤@¦¸ÁÂÁÂÀ°¦£ªº¦U¼h¼Ó¥D©M³Â»¶®a±Úªº¹Î¶¤¡I

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 1# f00l01

³sÄò¼Æ¦r³æ¿W­Ó§O©ñ¦b³æ¤@Àx¦s®æ¡A½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub tt1()
Dim Arr, Brr(), T%, T1%, L%, M%, i&, j&
Columns("O:AA").ClearContents
Arr = [a1].CurrentRegion
ReDim Brr(1 To UBound(Arr), 1 To UBound(Arr, 2))
For i = 1 To UBound(Arr)
    M = 0: L = 0
    For j = 1 To UBound(Arr, 2)
        If j + 1 > UBound(Arr, 2) Then
            If T1 = T + 1 Then M = M + 1: Brr(i, M) = Arr(i, j)
            Exit For
        End If
        T = Arr(i, j): T1 = Arr(i, j + 1)
        If T1 = T + 1 Then
            M = M + 1: Brr(i, M) = Arr(i, j)
        Else
            If T > L + 1 Then GoTo 99
            M = M + 1: Brr(i, M) = Arr(i, j)
        End If
        L = T
99:  Next
Next
Range("o1").Resize(UBound(Brr), UBound(Brr, 2)) = Brr
End Sub

Â^¨ú.PNG (15.58 KB)

Â^¨ú.PNG

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD