PJChen

¦^´_ 10# Andy2483
±z¦n,
§Ú§âµ{¦¡©ñ¶iÀɮפ¤¤F,­ã¤jªºµ{¦¡ÁÙ¬OµLªk¹Bºâ,
½ÐÀ°§Ú¬Ý¤U¸Ó«ç»òÅý¥¦°õ¦æ?
~~·PÁÂ~~
¸ê®ÆÅçºâ.rar (17.02 KB)

¤U¸ü¦¸¼Æ: 4

2023-4-26 22:23

Andy2483

¦^´_ 11# PJChen


    ÁÂÁ«e½ú¦^´_,ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«e½ú³o©«½d¨ÒÄæ¦ì¤£¤@¼Ë,­è¦n¥i¥H½Ð«e½ú¸Õ§ï¬Ý¬Ý
«á¾Ç¦b#4¼Ó ¸ò #8¼Ó³£¦³¤ß±oµù¸Ñ¥i¥H¾Ç²ß,«á¾Ç»¡©ú¤£²M·¡©Î­þ¸Ì¤£À´±o,½Ð´£¥XºÃ°Ý

¯¬ ¦¨¥\

ML089

C17 °}¦C¤½¦¡(­n¨Ï¥Î¤TÁä¿é¤J¤½¦¡)
=SUM(IFERROR(TEXT(RIGHT(TEXT(RIGHT("X"&TRIM(RIGHT(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A17,"(",","),")",","),",",REPT(" ",99)),ROW($1:$99)*99-98,99)),"-",REPT(" ",99)),{1,1,1,1,1,2,2,2,2,2}*99),99)),{2,3,4,5,6,2,3,4,5,6}),),{1,2,3,4,5,1,2,3,4,5}),"0;;;")*{-1,-1,-1,-1,-1,1,1,1,1,1}+{0,0,0,0,0,1,1,1,1,1},""))

­ã´£³¡ªL

¦^´_ 11# PJChen


¦Û­q¨ç¼Æ¥Îªk//¤TºØ
¸ê®ÆÅçºâ-1.rar (14.22 KB)

¤U¸ü¦¸¼Æ: 4

2023-4-27 10:36

PJChen

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

­ã¤j¦n,
½Ð±Ð¦Û©w¸q Function GetSerial(ST$),µ{¦¡»P­n­pºâªºÀɮשñ¦b¤@°_,´N¥i¥H­pºâ,
¦ý¦]¬°§Ú¬O§âµ{¦¡»P­pºâÀɤÀ¶}¦s©ñ,¦ý§Ú°µ¤F«ü©w¤´¬OµL§@¥Î??

Function GetSerial(ST$)
Dim Sh As Worksheet, W As Workbook
Set W = Workbooks("¥X³f¤å¥ó_PO.xlsx"): Set Sh = W.Sheets("½c¸¹­pºâ"): Sh.Activate

Dim a, Tr, V1%, V2%, S%
If InStr(ST, "(") = 0 Then ST = "(" & ST
ST = Split(Replace(ST, ")", ""), "(")(1)
For Each a In Split(ST, ",")
    Tr = Split(a & "-" & a, "-")
    V1 = Val(StrReverse(Mid(Val(StrReverse(Tr(0) & 1)), 2)))
    V2 = Val(StrReverse(Mid(Val(StrReverse(Tr(1) & 1)), 2)))
    If V1 + V2 <> 0 Then S = S + Abs(V2 - V1) + 1
Next a
If S > 0 Then GetSerial = S Else GetSerial = ""
End Function