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

gaishutsusuru

¤j®a¦n¡A

·Q½Ð±Ð¦U¦ì¤j¤j¡A

¤U¸ü (7.12 KB)

2023-4-23 22:58

°ÝÃD»¡©ú¡G
1. A1~A11¡G¬O¸ê®Æ¡A¦ý¹ê»Ú¤W¸ê®Æ·|§ó¦h
2. C1¬O¿é¤Jªº­È¡C
3. C4¬O­n³]©w¤½¦¡¡A²Å¦X¥H¤U±ø¥ó
(1) "_"©³½u«á¬Oªø«×¤£¤@¼Ëªº¦r¦ê¡C¥B¦r¦ê¤]¥i¯à·|§t¦³©³½u¡C¨Ò¡G202312_B¡CB¬O¦r¦êªºÂ²¤Æ¨Ò¤l¡A¹ê»Ú¤W¥i¯à·|¥]§t©³½u
(2) §PÂ_­n²Å¦X¦r¦ê«á¡A¤~¯à¶i¦æ¤é´Áªº§PÂ_(§ä¥X¤é´Á³Ì±µªñ¥B¤p©óC1Àx¦s®æªº±ø¥ó)¡C
(3) ¨Ò¤lÁ|¨Ò¦p¤U¡G
Æ¡ ·íC1¿é¤J¡G202312_B ®É¡AC4·|¦^¶Ç202307_B¡C(¦]¬°¦bA1~A11²Å¦X¦r¦êBªº¥B¤é´Á³Ì±µªñ¥B¤p©ó202312ªº¬O202307)
Æ¢ ·íC1¿é¤J¡G202301_A ®É¡AC4·|¦^¶Ç202212_A¡C(¦]¬°¦bA1~A11²Å¦X¦r¦êAªº¥B¤é´Á³Ì±µªñ¥B¤p©ó202301ªº¬O202212)
Æ£ ¦pªG§ä¤£¨ì¡A´N¦^¶Ç¡uµL¡v¡C¨Ò¡G·íC1¿é¤J¡G202212_A ®É¡AC4·|¦^¶Ç¡uµL¡v¡C(¦]¬°¦bA1~A11²Å¦X¦r¦êAªº¥B¤é´Á³Ì±µªñ¥B¤p©ó202212ªº¨S¦³­È)
4. ¥Ø«e¨Ï¥ÎªºEXCEL ªºª©¥»¬O2010¡B2013

³Ì«áªþ¤WÀɮסG °ÝÃD.zip (6.03 KB)

¤U¸ü¦¸¼Æ: 6

2023-4-23 23:08

§Æ±æ¦U¦ì¤j¤j¯à©âªÅÀ°¦£¡A«D±`ÁÂÁ±z­Ì¡C

Andy2483

¦^´_ 1# gaishutsusuru


    ÁÂÁ½׾Â,ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾ÇÂǦ¹©«½m²ßVBAªº¤èªk,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:

¤U¸ü (34.94 KB)

2023-4-24 16:40

Option Explicit
Sub TEST()
Dim Brr, Z$, X, Q, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
Dim D As Date, P As Date, P1 As Date
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
T0 = [C1]: V0 = Mid(T0, InStr(T0, "_") + 1)
Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
Y = Y + M \ 12: M = M Mod 12 + 1
D = CDate(Y & "/" & M & "/01")
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   Y = Y + M \ 12: M = M Mod 12 + 1
   P = CDate(Y & "/" & M & "/01") - 1
   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   If P1 - Date < P - Date Then
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
   End If
i01: Next
[C4] = IIf(Z <> "", Z, "µL")
Erase Brr
End Sub

gaishutsusuru

¦^´_ 2# Andy2483

ÁÂÁÂAndy2483¤j¤jªº©âªÅÀ°¦£¡C§Ú¦A¨Ó¸Õ¬Ý¬Ý¡C

·Q½Ð°Ý¦pªG¥Î¤½¦¡ªº¸Ü¡A¤£ª¾¹D¯à§_¹F¦¨©O? ÁÂÁÂ

Andy2483

¦^´_ 3# gaishutsusuru


    ÁÂÁ½׾Â,ÁÂÁ«e½ú¦^´_
¤½¦¡«ÜÃø,«á¾ÇÁÙ¨S¨º­Ó¥\¤O³]­p,¥H¤U¬O±N#2¼Ó§ï¦¨¦Û­q¨ç¼Æªº¾Ç²ß¤è®×,½Ð«e½ú°Ñ¦Ò

¨Ï¥Î¤è¦¡»Pµ²ªG:

¤U¸ü (59.56 KB)

2023-4-25 07:48

Option Explicit
Function GetSerial(ST$)
Dim Brr, Z$, X, Q, K$, V0$, V1$, T0$, T1$, i&, M%, Y%
Dim D As Date, P As Date, P1 As Date
Brr = Range([A1], Cells(Rows.Count, 1).End(3))
T0 = ST: V0 = Mid(T0, InStr(T0, "_") + 1)
Y = Left(Val(T0), 4): M = Val(Right(Val(T0), 2))
D = CDate(Y & "/" & M & "/01")
For i = 1 To UBound(Brr)
   T1 = Brr(i, 1): V1 = Mid(T1, InStr(T1, "_") + 1)
   Y = Left(Val(T1), 4): M = Val(Right(Val(T1), 2))
   Y = Y + M \ 12: M = M Mod 12 + 1
   P = CDate(Y & "/" & M & "/01") - 1
   If (T0 = T1) + (V0 <> V1) + (P > D) Then GoTo i01
   If P1 - Date < P - Date Then
      P1 = P: Z = Format(P1, "YYYYMM") & "_" & V0
   End If
i01: Next
GetSerial = IIf(Z <> "", Z, "µL")
Erase Brr
End Function

hcm19522

D1{=IFERROR(INDEX(A:A,RIGHT(LARGE(IF((LEFT(A$1:A$11,6)-LEFT(C1,6)<0)*(MID(A$1:A$11,8,99)=MID(C1,8,99)),LEFT(A$1:A$11,6)/1%+ROW($1:$11)),1),2)),"µL")

ML089

D1 °}¦C¤½¦¡¡A¤½¦¡½Æ»s½s¿è¦C(´å¼Ð¦b¤W)¡A¥ý«öShit+Ctrl¦A«öENTER¡A¿é¤J¦¨¥\¤½¦¡¥~¼W¥[ {...}
=TEXT(MAX((MID(A$1:A$20,7,9)=MID(C1,7,9))*(LEFT(A$1:A$20,6)<LEFT(C1,6))*(0&LEFT(A$1:A$20,6))),0&""""&MID(C1,7,9)&""""&";;µL")

¥i¥H¤U©Ô

­ã´£³¡ªL

°}¦C¤½¦¡(¤T键)
=TEXT(MAX(--TEXT(SUBSTITUTE(A$1:A$99,MID(C1,7,9),),"[<="&LEFT(C1,6)&"]0;-1;-1;\-\1")),"0"""&MID(C1,7,9)&""";µL")

¤é´Á¦³±Æ§Ç
=IFERROR(LOOKUP(,-TEXT(SUBSTITUTE(A$1:A$99,MID(C1,7,9),),"[<="&LEFT(C1,6)&"]0;;;"),A:A),"µL")

gaishutsusuru

¦^´_ 5# hcm19522

·PÁÂhcm19522¤j¤jªºÀ°¦£¡A¤½¦¡¥i¥H¦¨¥\¾Þ§@¡C

gaishutsusuru

¦^´_ 4# Andy2483

ÁÂÁÂAndy2483±zªºÀ°¦£¡A§Ú¨Ó¬ã¨s¬Ý¬Ý

gaishutsusuru

¦^´_ 6# ML089

ÁÂÁÂML089±zªºÀ°¦£¡A¤½¦¡¨S¦³°ÝÃD®@¡C