¦P¤@Àx¦s®æ¤º¸ê®Æ¶i¦æ¤j¤p¤ñ¹ï_¾ã²z«á§ä³Ì¤j­È¼ÐµùV

Andy2483

¦U¦ì«e½ú¦n
«á¾Ç·Q¾Ç²ß¦UºØ¤£¦Pvba¼gªk,ÁܽзQ½m²ß°}¦C»P¦r¨åªº¦P¾Ç«e½ú¤@°_¤W½×¾Â¬ã°Q!ÁÂÁÂ
½Ð¦U¦ì«e½ú­Ì«ü¾É
«á¾ÇÂÇ ranceymm«e½úªº¥DÃD½d¨Ò§@¬°ÃD§÷,ÁÂÁ ranceymm«e½ú,¦p¦³«_¥Ç ½Ð¨£½Ì
¤£¦P¥DÃD¥t¶}ÃD§@¾Ç²ß
http://forum.twbts.com/thread-23842-1-1.html
¨D¸Ñ.zip (6.93 KB) ½d¨Ò¦p¤W½×¾ÂÃìµ²

«á¾Ç¾Ç²ß°õ¦æµ²ªG:

¤U¸ü (35.64 KB)

2022-12-13 10:15

Option Explicit
Sub test_20221213()
Dim Brr, i&, X&, V, Y, Z$(5), A$, N&, j&, R&, C$, Q#(99), P, M#, K&
Set Y = CreateObject("Scripting.Dictionary")
Brr = Range([D3], Cells(Rows.Count, "D").End(3))
For i = 1 To UBound(Brr)
   A = Replace(Replace(Replace(Brr(i, 1), " ªø", ","), "¡B", ","), " ", ",")
   V = Split(A, ",")
   R = R + 1
   N = 0
   P = Q
   For j = 0 To UBound(V)
      If V(j) = "" Then
         M = Application.Max(P)
         For K = 1 To N
            C = "'" & R & "." & K
            P = Y(C)
            If P(4) = M Then
               P(5) = "V"
               Y(C) = P
            End If
         Next
         Exit For
      End If
      X = j Mod 4
      N = IIf(X = 0, N + 1, N)
      If X = 0 Then C = "'" & R & "." & N: Z(X) = C
      Z(X + 1) = V(j)
      If X = 3 Then P(N) = V(j): Y(C) = Z
   Next
Next
Workbooks.Add
[A1].Resize(1, 6) = [{"N0","¤é´Á","®É¶¡","³W®æ","¼Æ­È","MX"}]
[A2].Resize(Y.Count, 6) = Application.Transpose(Application.Transpose(Y.ITEMS))
[B:B].NumberFormatLocal = "yyyy/m/d"
Cells.Columns.AutoFit
End Sub

Andy2483

¦U¦ì«e½ú¦­¦w

½Ð±Ð¦U¦ì«e½ú:
³oÃD¥i¥H¥Î ¥¿«h¨Ó¸Ñ¶Ü?
¤£¥Î¦r¨å»P°}¦C¥i¥H¸Ñ¶Ü?
¦r¨å¤¤ªº¦r¨å¦n¹³¥i¥H?
ÁÙ¦³¤°»ò¤èªk?

hcm19522

https://blog.xuite.net/hcm19522/twblog/590655290

Andy2483

¦^´_ 3# hcm19522


    ÁÂÁ«e½ú«ü¾É
³o½d¨Ò¦nÃø,¥i¬O«á¾Ç·|ÂǦ¹¾÷·|¾Ç°_¨Ó,²{¦b¥¿¬ã¨s¨ä¥L©«,¹J§xÃø¦A½Ð±Ð,ÁÂÁÂ

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÁÙ¨S¤W½×¾Â«e·|¥ÎWIith ~End WIith,ı±o¥i¥H¶]´N¦n,¯Ó®É´Nºâ¤F!
®É¶¡¬y³u,Àɮ׸ê®ÆÅܤj,­«½ÆFIND()¤ÓºC¤F
¤W½×¾Â¾Ç²ß°}¦C»P¦r¨å«á,¦³¨Çµ{¦¡½X­ì¨Ó 1 ¤ÀÄÁªºÂ¤èªkµ{§Ç,§ï°}¦C&¦r¨å«á²{¦b¥u­n 0.1 ¬í
¥H¤U¬O³o¥DÃD½d¨Ò¥Î¥H«e¨S¦³¥Î°}¦C©M¦r¨åªºÂ¤èªk:
³s«Å§i³£¤£¦b©I,«Å§i¤@­ÓÅܼƥi¯à¥i¥H²¤Æ«Ü¦h¦æµ{¦¡½X

Sub test_20221214()
AC_WO_NA = ActiveWorkbook.Name
Workbooks.Add
[A1] = "N0"
[B1] = "¤é´Á"
[C1] = "®É¶¡"
[D1] = "³W®æ"
[E1] = "¼Æ­È"
[F1] = "MX"
N = 1
With Workbooks(AC_WO_NA).Sheets("SOS")
   For i = 3 To .[D65536].End(xlUp).Row
      If .Cells(i, "D") Like "*##/## *:* *-* ªø* *#*¡B*" = True Then
        R = R + 1
        C = 0
        For j = 1 To Len(.Cells(i, "D"))
           If Mid(.Cells(i, "D"), j, 5) Like "##/##" = True Then
              C = C + 1
              N = N + 1
              Cells(N, 1) = "'" & R & "." & C
              Cells(N, 2) = Mid(.Cells(i, "D"), j, 5)
           End If
           If Mid(.Cells(i, "D"), j, 5) Like "##:##" = True Then
              Cells(N, 3) = Mid(.Cells(i, "D"), j, 5)
           End If
           If Mid(.Cells(i, "D"), j, 99) Like " AA-* ªø*" = True Then
              Cells(N, 4) = Mid(.Cells(i, "D"), j + 1, InStr(Mid(.Cells(i, "D"), j, 99), " ªø") - 2)
           End If
           If Mid(.Cells(i, "D"), j, 99) Like " ªø#*¡B*" = True Then
              Cells(N, 5) = Mid(.Cells(i, "D"), j + 2, InStr(Mid(.Cells(i, "D"), j, 99), "¡B") - 3)
           End If
        Next
      End If
   Next
End With
M = 0
For i = 2 To [A65536].End(xlUp).Row + 1
   If Mid(Cells(i, 1), 1, InStr(Cells(i, 1), ".")) = Mid(Cells(i + 1, 1), 1, InStr(Cells(i + 1, 1), ".")) Then
      If Cells(i, 5) > M Then
         N = i
         M = Cells(i, 5)
      End If
      If Cells(i + 1, 5) > M Then
         N = i + 1
         M = Cells(i + 1, 5)
      End If
      Else
         Cells(N, 6) = "V"
         M = 0
   End If
Next
[B:B].NumberFormatLocal = "yyyy/m/d"
Cells.Columns.AutoFit
End Sub

¦³½m²ß¤~·|¹J¨ì§xÃø©M¿ù»~! ¤@°_¨Ó¾Ç²ß

Andy2483

¦^´_ 3# hcm19522


    ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß°}¦C¤½¦¡ªº²Ä¤@©«,¤£ª¾¹D±Ô­z±o¬O§_¥¿½T»P¾A·í

{=IF(INT(A4)/1%%+MAX((INT(A$2:A$16)=INT(A4))*F$2:F$16)=INT(A4)/1%%+F4,"V","")}
¦pªG ·í®æ¦P¦CAÄæÀx¦s®æ­È¥h°£¤p¼Æªº10000­¿­È+AÄæ«ü©w°Ï¶¡Àx¦s®æ¦P¾ã¼Æ­ÈÀx¦s®æ¹ïÀ³ªºFÄæ³Ì¤j­È = ·í®æ¦P¦CAÄæÀx¦s®æ­È¥h°£¤p¼Æªº10000­¿­È+·í¦CFÄæ­È,´N¦b·í®æÅã¥Ü"V"¦r¤¸,§_«hÅã¥Ü""¦r¤¸

0.{} : °}¦C¤Æ¤½¦¡,«ü©wªº°Ï¶¡Àx¦s®æ¬O°}¦C,§_«h¥u¨ú·í®æ¦b«ü©wªº°Ï¶¡Àx¦s®æªº¬Û¦P¦C©ÎÄ檺Àx¦s®æ
1.IF¨ç¼Æ(§PÂ_¦¡,Ture­È,False­È)
1.1.§PÂ_¦¡: INT(A4)/1%%+MAX((INT(A$2:A$16)=INT(A4))*F$2:F$16)=INT(A4)/1%%+F4
1.1.1.INT(A4)/1%% :  [A4]Àx¦s®æ±Ë±ó¤p¼Æ«áªº¾ã¼Æ°£0.0001
1.1.2.MAX((INT(A$2:A$16)=INT(A4))*F$2:F$16) : [A2:A16]¦U­ÓÀx¦s®æ±Ë±ó¤p¼Æ«áªº¾ã¼Æ,»P[A4]Àx¦s®æ±Ë±ó¤p¼Æ«áªº¾ã¼Æ°µ=ÅÞ¿è§PÂ_­È¦^¶Ç(0©Î1),¦A±N¦¹ÅÞ¿è­È»P[F2:F16]¦UÀx¦s®æ¬Û­¼«á¨ú³Ì¤j­È
1.1.3.INT(A4)/1%%+F4 : [A4]±Ë±ó¤p¼Æ«áªº¾ã¼Æ°£0.0001 + [F4]Àx¦s®æ­È

1.2.Ture­È : "V"

1.3.False­È : ""

¤U¸ü (213.33 KB)

2022-12-15 15:14

hcm19522

¦^´_ 6# Andy2483


¾Ç²ßºA«×¨Î   «ö­ÓÆg

Andy2483

¦^´_ 7# hcm19522


    ÁÂÁ«e½ú
«e½ú«ü¾Éªº¬O¤½¦¡,¹³¬O¥Ó½×ÃD,¦pªG«e½ú¦³ªÅ,Àµ½Ð¦Ñ®v«ü¥¿«á¾ÇªºÄY­«¿ù»~,«á¾Çªº¤p¿ù·|¦Û¤wºCºC§ï¶i,ÁÂÁÂ