[µo°Ý] ¤£³W«h¤å¦r,¨ú¼Æ¦rÅçºâ

PJChen

¤j¤j¦n,

½Ð°Ý¦pªþ¹ÏJPG©Ò¥Ü,¯à§_¨ú¥X()¤¤ªº¼Æ¦r,°µ¥[´î?()¤¤ªº¼Æ¦r,¨S¦³¤@©wªº³W«h

¤U¸ü (105.78 KB)

2023-4-17 14:05

¼t°Ó¸ê®ÆÅçºâ.rar (7.58 KB)

¤U¸ü¦¸¼Æ: 10

2023-4-17 14:12

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

­ã´£³¡ªL

¦^´_ 11# PJChen


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

¤U¸ü¦¸¼Æ: 4

2023-4-27 10:36

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},""))

Andy2483

¦^´_ 11# PJChen


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

¯¬ ¦¨¥\

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

¦^´_ 9# PJChen


    ÁÂÁ«e½ú¦^´_
https://learn.microsoft.com/zh-t ... strreverse-function
³o¬O«á¾Ç¬d¨ìºô¸ôÃö©óstrreverse() ª¾ÃÑ©ñ¤W¨Ó¥H«K¬d¸ß¥Îªº³sµ²,¦pªG©ñ¨ìVBA¸Ì·|²£¥Í½sĶ¿ù»~ªº°T®§,ÂI±¼¥L´N¥i¥H¤F

¿ù»~°T®§:

¤U¸ü (210.74 KB)

2023-4-25 07:32

¥[­Ó³æ¤Þ¸¹¦b³Ì«e­±Åý¥LÅܵù¸Ñ:

¤U¸ü (189.01 KB)

2023-4-25 07:33

PJChen

¦^´_ 8# Andy2483
½Ð°Ý¦Û­q¨ç¼Æ­n«ç»ò¦bµ{¦¡¤¤¨Ï¥Î?
°õ¦æ¤@ª½¥X²{ "½sö¿ù»~"
¥²¶·¬OEnd sub

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾Ç¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æµ²ªG:

¤U¸ü (151.69 KB)

2023-4-24 09:20

Option Explicit

'¦Û­q¨ç¼Æ//
Function GetSerial(ST$)
'¡ô¦Û­q¨ç¼Æ_GetSerial,STÅܼƬO¦r¦êÅܼÆ(Àx¦s®æ¸Ìªº­È¬O¤å¦r)
Dim a, Tr, V1%, V2%, S%
'¡ô«Å§iÅܼÆ(a,Tr)¬O³q¥Î«¬ÅܼÆ,(V1,V2,S)¬Oµu¾ã¼Æ
If InStr(ST, "(") = 0 Then ST = "(" & ST
'¡ô¦pªGST³o¦r¦êÅܼƸ̤£¥]§t "(" ²Å¸¹!,
'´N¥O¨ä«e¤è¦h¤@­Ó "("²Å¸¹²Õ¦¨·s¦r¦ê
ST = Split(Replace(ST, ")", ""), "(")(1)
'¡ô¥O¥ý±NSTÅܼƸ̪º ")" ²Å¸¹¸m´«¦¨ªÅ¦r¤¸,
'¤§«á¦A±NSTÅܼƥΠ"("¤À³Î¦¨¤@ºû°}¦C¨ú¯Á¤Þ¸¹1ªº¦r¦ê
For Each a In Split(ST, ",")
'¡ô³]³v¶µ°j°é!¥Oa³o³q¥Î«¬ÅܼƬO STÅܼƳQ","¤À³Î¦¨¤@ºû°}¦Cªº¤@°}¦C­È
    Tr = Split(a & "-" & a, "-")
    '¡ô¥OTr³o³q¥Î«¬ÅܼƬO aÅܼƳs±µ"-",¦A³s±µaÅܼƫá,
    '¥H"-"¤À³Î¦¨¬°¤@ºû°}¦C
    V1 = Val(StrReverse(Mid(Val(StrReverse(Tr(0) & 1)), 2)))
    '¡ô¥OV1ÅܼƬO ¥ý±NTr 0¯Á¤Þ¸¹°}¦C­È¸Ìªº¦r¤¸¤ÏÂà,¨ú«e­±¼Æ­È«á,
    '¦A±N³o¼Æ­È¦r¤¸¤ÏÂà¦^¨Ó,³o¼Ëªº¤è¦¡´N·|¥u§ì¨ú¨ì«e¤è¼Æ¦rªº¦r¤¸
    https://learn.microsoft.com/zh-t ... strreverse-function
    V2 = Val(StrReverse(Mid(Val(StrReverse(Tr(1) & 1)), 2)))
    '¡ô¥OV2»PÃþ±À»PV1¬Û¦P¤è¦¡¨ú±o«e¤è¼Æ¦r¦r¤¸
    If V1 + V2 <> 0 Then S = S + Abs(V2 - V1) + 1
    '¡ô¦pªGV1ÅܼÆ+V2ÅÜ¼Æ ¤£µ¥©ó0 ,´NÅýS³oµu¾ã¼ÆÅܼƬO¦Û¨­+µ´¹ï­È(V1ÅܼÆ+V2ÅܼÆ)+1
Next a
If S > 0 Then GetSerial = S Else GetSerial = ""
'¡ô¦pªGSÅܼÆ>0 !´N¥OGetSerial¨ç¼Æ¦^¶Ç SÅܼƭÈ,§_«h´N¦^¶ÇªÅ¦r¤¸
End Function
=================
«á¾Ç©|¦³¤@¨Ç²Ó¸`¤£¤F¸Ñ,Ä~Äò²Ö¿n¸gÅç,ÁÂÁ«e½ú

­ã´£³¡ªL

¦Û­q¨ç¼Æ//
Function GetSerial(ST$)
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

c17//¤½¦¡
=GetSerial(A17)