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

[µo°Ý] ½Ð°Ý¦p¦ó§ä¥X³Ì±µªñ¤é´Á

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

ÁÂÁ­㴣³¡ªL¤j¤jªºÀ°¦£¡A¤£¹L¤½¦¡¦³¨Ç·|¥X°ÝÃD¡G
¨Ò¡G
¿é¤J202307_B ®É¡A¤½¦¡À³¸Ó­n¦^¶Ç¡u202305_B¡v, ¦ý«o¸õ¥X202307_B
¿é¤J202212_A®É¡A¤½¦¡À³¸Ó­n¦^¶Ç¡uµL¡v, ¦ý«o¸õ¥X202212_A

TOP

¦^´_ 9# gaishutsusuru


    ÁÂÁ½׾Â,ÁÂÁ«e½ú¦^´_
«á¾Ç¤µ¤Ñ½Æ²ß¤è®×,¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú°Ñ¦Ò,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Function GetSerial(ST$)
'¡ô¦Û­q¨ç¼ÆGetSerial() «Å§iÅܼÆ:¿é¤J¨ç¼Æªº STÅܼƬO ¦r¦ê
Dim Brr, X, Q, Z$, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
'¡ô«Å§iÅܼÆ:(Z,K,V0,V1,T0,T1)¬O¦r¦êÅܼÆ,i¬Oªø¾ã¼ÆÅܼÆ,
'(Y,M)¬Oµu¾ã¼ÆÅܼÆ,(Brr,X,Q)¬O³q¥Î«¬ÅܼÆ

Dim D As Date, P As Date, P1 As Date
'¡ô(D,P,P1)¬O¤é´ÁÅܼÆ
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥H[A1]¨ìAÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ­È±a¤J
T0 = ST: V0 = Mid(T0, InStr(T0, "_") + 1)
'¡ô¥OT0³o¦r¦êÅܼƬO ¨Ï¥ÎªÌ¿é¤J¨ç¼Æ¸ÌªºÀx¦s®æ­È,
'¥OV0³o¦r¦êÅܼƬO T0Åܼƨú©³½u¦r¤¸(¤£§t)«áªº©Ò¦³¦r¤¸²Õ¦¨ªº·s¦r¦ê

Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
'¡ô¥OY³oµu¾ã¼Æ¬O T0ÅܼÆÂà¾ã¼Æ¼Æ­È«á¨ú¥ª°¼4¦r¤¸ªº¼Æ­È,
'¡ô¥OM³oµu¾ã¼Æ¬O T0ÅܼÆÂà¾ã¼Æ¼Æ­È«á¨ú¥k°¼2¦r¤¸ªº¼Æ­È

D = CDate(Y & "/" & M & "/01")
'¡ô¥OD³o¤é´ÁÅܼƬO YÅܼƳs±µ"/",¦A³s±µMÅܼÆ,³Ì«á³s±µ"/01"²Õ¦¨ªº¤é´Á
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   '¡ô¥OT1³o¦r¦êÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦C­È,
   '¥OV1³o¦r¦êÅܼƬO T1Åܼƨú©³½u¦r¤¸(¤£§t)«áªº©Ò¦³¦r¤¸²Õ¦¨ªº·s¦r¦ê

   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   '¡ô¥OY³oµu¾ã¼Æ¬O T1ÅܼÆÂà¾ã¼Æ¼Æ­È«á¨ú¥ª°¼4¦r¤¸ªº¼Æ­È,
   '¡ô¥OM³oµu¾ã¼Æ¬O T1ÅܼÆÂà¾ã¼Æ¼Æ­È«á¨ú¥k°¼2¦r¤¸ªº¼Æ­È

   Y = Y + M \ 12: M = M Mod 12 + 1
   '¡ô¥OYÅܼƬO ¦Û¨­+(MÅܼư£12ªº¾ã¼Æ°Ó)
   '¥OMÅܼƬO ¦Û¨­°£12«áªº¾l¼Æ,¦A+1

   P = CDate(Y & "/" & M & "/01") - 1
   '¡ô¥OP³o¤é´ÁÅܼƬO YÅܼƳs±µ"/",¦A³s±µMÅܼÆ,
   '³Ì«á³s±µ"/01"²Õ¦¨ªº«e¤@¤Ñ¤é´Á(¨D«e¤@­Ó¤ëªº³Ì«á¤@¤Ñ)

   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   '¡ô¦pªG³o¤T­Ó±ø¥óªº¨ä¤¤¤@­Ó¤£¬O0!´N¸õ¨ì i01¦ì¸mÄ~Äò°õ¦æ
   If P1 - Date < P - Date Then
   '¡ô¦pªGP1ÅܼÆ-¤µ¤Ñ¤é´Á ¤p©ó PÅܼÆ-¤µ¤Ñ¤é´Á?
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
      '¡ô¥OP1ÅܼƸËPÅܼÆ,¥OZ³o¦r¦êÅܼƬO P1ÅܼÆÂà4½X¦~2½X¤ë,
      '¦A³s±µ©³½u¦r¤¸,³Ì«á³s±µV0Åܼƪº·s¦r¦ê

   End If
i01: Next
GetSerial = IIf(Z <> "", Z, "µL")
'¡ô¦^¶Ç­Èµ¹¦Û­q¨ç¼Æ:
'¦pªGZÅܼƤ£¬OªÅ¦r¤¸´N¦^¶ÇZÅܼÆ,§_«h¦^¶Ç"µL"¦r¤¸

Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 11# gaishutsusuru


§â <= §ï¦¨ <

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¨S¦³©Ò¦³Åv¡A¥u¦³¥Í©Rªº¨Ï¥ÎÅv¡C
ªð¦^¦Cªí ¤W¤@¥DÃD