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

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

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

¤j¤j¦n,

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



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

¦^´_ 1# PJChen


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

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, V, Z&, i, j&, xR As Range
Set xR = Range([A17], Cells(Rows.Count, "A").End(xlUp)): Brr = xR
With xR.Offset(0, 1)
   .Value = Brr
   For Each i In Array("*(", ")*", " "): .Replace i, "", 2: Next
   For i = 65 To 122: .Replace Chr(i), "", 2: Next
   Brr = .Value
End With
For i = 1 To UBound(Brr)
   V = Split(Brr(i, 1), ",")
   For j = 0 To UBound(V)
      If InStr(V(j), "-") Then
         Z = Z + Abs(Evaluate("=" & V(j))) + 1
         ElseIf V(j) <> "" Then
            Z = Z + 1
      End If
   Next
   Brr(i, 1) = Z: Z = 0
Next
xR.Offset(0, 1) = Brr
Set xR = Nothing: Erase Brr, V
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2023-4-17 16:45 ½s¿è

¦^´_ 2# Andy2483


·PÁÂ,
³o­Óµ{¦¡¤Ó¯«©_¤F,¤£¦Pªº²Õ¦X³£¥i¥Hºâ±o¥X¨Ó~~


½Ð°Ý³o¬q¯àÀ°§Ú¸Ñ»¡¤@¤U¶Ü?
For i = 65 To 122: .Replace Chr(i), "", 2: Next

TOP

¦^´_ 3# PJChen


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

Option Explicit
Sub TEST()
Dim Brr, V, i, j&, Z&, xR As Range
'¡ô«Å§iÅܼÆ:(Brr,V,i)¬O³q¥Î«¬ÅܼÆ,(j,Z)¬Oªø¾ã¼Æ,xR¬OÀx¦s®æÅܼÆ
Set xR = Range([A17], Cells(Rows.Count, "A").End(xlUp)): Brr = xR
'¡ô¥OxR³oÀx¦s®æÅܼƬO [A17]¨ì AÄæ³Ì«á¤@­Ó¦³¤º®eÀx¦s®æ,
'¥OBrr³o³q¥Î«¬ÅܼƬO ¤Gºû°}¦C,¥HxRÅܼÆ(Àx¦s®æ)­È±a¤J

With xR.Offset(0, 1)
'¡ô¥H¤U¬OÃö©óxRÅܼƦV¥k°¾²¾¤@Ä檺µ{§Ç
   .Value = Brr
   '¡ô¥O¨äÀx¦s®æ­È¬O Brr°}¦C­È
   For Each i In Array("*(", ")*", " "): .Replace i, "", 2: Next
   '¡ô³]³v¶µ°j°é,¥Oi³o³q¥Î«¬ÅܼƬO ¤@ºû°}¦C¤¤ªº¤@­Ó¦r¦ê
   '¥O¸Ó½d³òÀx¦s®æ°µ¦r¤¸¸m´«,¥ý±N¥ª¬A©·"(" ¥ªÃ䪺¦r¤¸³£´«¦¨ªÅ¦r¤¸
   '¦A±N¥k¬A©·")" ¥kÃ䪺¦r¤¸³£´«¦¨ªÅ¦r¤¸
   '³Ì«á±NªÅ¥Õ¦r¤¸³£´«¦¨ªÅ¦r¤¸

   For i = 65 To 122: .Replace Chr(i), "", 2: Next
   '¡ô³]¶¶°j°é!i±q65 ¨ì122
   '¥O¸Ó½d³òÀx¦s®æ°µ¦r¤¸¸m´«±qA,B,C,D....°µ¸m´«¦¨ªÅ¦r¤¸,Chr(65)="A"
   ', 2¬O«üÀx¦s®æ¸Ì¦r¦ê³¡¤À¬Û¦P´N°µ¸m´«,¤£»Ý­n¦r¦ê¥þ³¡¬Û¦P¤~¸m´«

   Brr = .Value
   '¡ô¥OBrr³o³q¥Î«¬Åܼƴ«¸Ë¤W­z³o¨Ç°j°éªºÀx¦s®æµ²ªG­È
End With
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q1 ¨ìBrrÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   V = Split(Brr(i, 1), ",")
   '¡ô¥OV³o³q¥Î«¬ÅܼƬO¤@ºû°}¦C,¥Hi°j°é¦C1ÄæBrr°}¦C­È³Q","¤À³Î«áªº­È
   For j = 0 To UBound(V)
   '¡ô³]¶¶°j°é!j±q0 ¨ìV°}¦C³Ì¤j¯Á¤Þ¸¹¼Æ
      If InStr(V(j), "-") Then
      '¡ô¦pªGj°j°éV°}¦C­È¥]§t"-"²Å¸¹??
         Z = Z + Abs(Evaluate("=" & V(j))) + 1
         '¡ô´N¥OZ³oªø¾ã¼ÆÅܼƬO ¦Û¨­ + ­pºâ­È
         '­pºâ­È:j°j°éV°}¦C­È¦b«e­±¥[­Ó"="¤§«á°µ¹Bºâ
         '¨Ò¦p:Evaluate("=2-14") =-12
         'Abs(-12)=12 :¬Oµ´¹ï­È,Åý­È³£ÅÜ¥¿¼Æªº·N«ä,
         '³Ì«á«ö·Ó½d¨Ò·N¸q +1

         ElseIf V(j) <> "" Then
         '¡ô§_«h¦pªGj°j°éV°}¦C­È¤£¬OªÅ¦r¤¸?
            Z = Z + 1
            '¡ô´N¥OZÅܼƲ֥[ 1
      End If
   Next
   Brr(i, 1) = Z: Z = 0
   '¡ô¥Oi°j°é¦C²Ä1ÄæBrr°}¦C­È¬O ÅܼÆZ,
   '¡ô¥OZÅܼƬO 0 (¦]¬°­n¶]iªº¤U¤@­Ó°j°é¤F,©Ò¥H­nÅýZÂk¹s­«ºâ)

Next
xR.Offset(0, 1) = Brr
'¡ô¥OxRÅܼƦV¥k°¾²¾¤@Äæ,³o½d³òÀx¦s®æ­È¥HBrr°}¦C­È±a¤J
Set xR = Nothing: Erase Brr, V
'¡ô¥OÄÀ©ñ³o¨ÇÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

C3{=SUM(RIGHT(SMALL(IFERROR(MID(A3,SMALL(IF(MID(A3,ROW($1:$199),1)="-",ROW($1:$199)),ROW($1:$19))+MATCH(,0/MID(A3,FIND("-",A3)+ROW($1:$9),1),),{1,2,3})*1,)+ROW($1:$19)/1%%,ROW($1:$19)*3),4)-RIGHT(SMALL(IFERROR(MID(A3,SMALL(IF(MID(A3,ROW($1:$199),1)="-",ROW($1:$199)),ROW($1:$19))-{1,2,3},{1,2,3})*1,)+ROW($1:$19)/1%%,ROW($1:$19)*3),4))+LEN(A3)-LEN(SUBSTITUTE(A3,",",))+1

11311.png (20.52 KB)

11311.png

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦Û­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)

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-4-24 09:46 ½s¿è

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


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

°õ¦æµ²ªG:



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½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

TOP

¦^´_ 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®§:


¥[­Ó³æ¤Þ¸¹¦b³Ì«e­±Åý¥LÅܵù¸Ñ:
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD