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

[µo°Ý] ¦P¤@Àx¦s®æ¤º¸ê®Æ¶i¦æ¤j¤p¤ñ¹ï

¦^´_ 9# Andy2483


    ¦^´_¦Û¤v¤S´ú¸Õ¨ì§ó¶i¤@¨B»{ÃÑ ¦Û©w¸q¨ç¼Æ
¨ç¼Æ¸Ì«Å§i¦r¦ê,¨Ï¥ÎªÌ¿é¤JÀx¦s®æ¦ì§},·|¦Û°ÊÂà¤Æ¬°¦r¦ê­È,¥i¥H¤Ö¥Î¤@­ÓÅܼÆ
4.jpg
2022-12-14 13:45


Option Explicit
Function MXA(¦r¦ê As String) As Double
Dim Y, V, j&, P#
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
¦r¦ê = Replace(Replace(Replace(¦r¦ê, " ªø", ","), "¡B", ","), " ", ",")
V = Split(¦r¦ê, ",")
For j = 3 To UBound(V) Step 4
   If IsNumeric(V(j)) And V(j) <> "" Then
      P = V(j): Y(P) = ""
   End If
Next
MXA = Application.Max(Y.keys())
Set Y = Nothing: Erase V
End Function
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 11# Andy2483

regexpªº¸Ñªk

    Sub test()
    Dim Find_Num, reg As Object, i As Integer, mxa As Double
    Set reg = CreateObject("VBScript.RegExp")
    reg.Pattern = "ªø(.+?)¡B"
    reg.Global = True
    Set Find_Num = reg.Execute(Range("d3"))
    If Find_Num.Count > 0 Then
        For i = 0 To Find_Num.Count - 1
            If mxa < Find_Num(i).submatches(0) Then mxa = Find_Num(i).submatches(0)
        Next i
    End If
    MsgBox mxa
    Set reg = Nothing
End Sub

TOP

vba¦Û­q¨ç¼Æ//
¦Û­q¤å¦t¶}ÀY«á­±ªº¼Æ¦r
¥i¨D MAX, MIN, SUM...
Xl0000311-1.rar (12.99 KB)

TOP

¦^´_ 12# quickfixer


    ÁÂÁ«e½ú«ü¾É ¥¿«h¤èªk,«á¾Ç²ß±o«Ü¦hª¾ÃÑ
¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É,ÁÂÁÂ

¾Ç²ß°õ¦æ¹Lµ{:
20221215_1.jpg
2022-12-15 08:26


¾Ç²ß°õ¦æµ²ªG:
20221215_2.jpg
2022-12-15 08:27


Option Explicit
Sub test_quickfixer()
Dim Find_Num As Object, reg As Object, i As Integer, mxa As Double
'¡ô«Å§iÅܼÆ!(Find_Num,reg)¬O³q¥Î«¬ÅܼÆ,(i)¬Oªø¾ã¼ÆÅܼÆ,(mxa)¬OÂùºë«×¤p¼Æ¼Æ¦r
    Set reg = CreateObject("VBScript.RegExp")
    Set Find_Num = CreateObject("VBScript.RegExp")
    '¡ô¥Oreg³oª«¥óÅܼƬO ¥¿«h
    reg.Pattern = "ªø(.+?)¡B"
    '¡ô¥O¥¿«hªº³W«h¬O "ªø"¦r¤¸»P"¡B"²Å¸¹¤§¶¡(§t)
    '·|­p¬ì¥Ø¦WºÙ,(.+?)¥i¥H¬O¥ô·N¦r²Å¦ê¬G±Ä¥Î.¥[¸¹ªí¥Ü¬ì¥Ø¦WºÙªø«×¤£¯à¬°0 ,
    '¥[¸¹¤§«áªº°Ý¸¹ªí¥Ü¦pªG»P¤§«áªº¼Ò¦¡½Ä¬ð®É, ºÉ¥i¯à¤Ö¦a¤Ç°t¬ÛÀ³ªº¦r²Å¦ê(«D³g°ý¤Ç°t)

    reg.Global = True
    '¡ô¥¿«hªºµ²ªG³£­n«O¯d
    Set Find_Num = reg.Execute(Range("d3"))
    MsgBox Find_Num(1)
    'MsgBox Find_Num(1, 1) '¥¢±Ñ °»¿ù
    'MsgBox Find_Num(1, 0) '¥¢±Ñ °»¿ù
    'MsgBox Find_Num(0, 1) '¥¢±Ñ °»¿ù

    '¡ô¥OFind_Num³o³q¥Î«¬ÅܼƬO [D3]Àx¦s®æ­È¥Hreg°õ¦æªº¥¿«h
    '¤§«e¤£½T©wFind_Num¬O°}¦CÁÙ¬O¤°»ò? ¤µ¤Ñ¶i¤@¨B½T©w¥L¤]¬Oª«¥ó
    '1.SET
    '2.Find_Num As Object
    '3.Set Find_Num = CreateObject("VBScript.RegExp")
    '4.MsgBox Find_Num(1)

    If Find_Num.Count > 0 Then
    '¡ô¦pªGFind_Num³o¥¿«hªº¼Æ¶q¬O¤j©ó 0 ??
        For i = 0 To Find_Num.Count - 1
        '¡ô³]¶¶°j°é!i±q0¨ì Find_Num¥¿«hªº¼Æ¶q-1 (¥Nªí¥¿«hªº¯Á¤Þ¸¹¤]¬O0¶}©l)
            If mxa < Find_Num(i).submatches(0) Then
            '¡ô¦pªG°j°é¼Æ¥¿«h¤¤ ¨úPattern = "ªø(.+?)¡B" ³W«hªº¤p¬A¸¹¤¤ªº¼Æ­È,
            '¤j©ómxa³oÂùºë«×¤p¼Æ­È??
            '§ä¤F¦n¤[!submatches(0)¥Nªí²Ä¤@­Ó¤l¤Ç°t,§Y¤p¬A¸¹¤¤ªº¼Æ­È

               mxa = Find_Num(i).submatches(0)
               '¡ômxaªºªì©l­È¬O0,«áÄò°j°é¥¿«h¸gIf§PÂ_¦¡¤ñ¸û«á,·|¨ú±o³Ì¤j­Èªº
            End If
        Next i
    End If
    MsgBox mxa
    Set reg = Nothing
    Set Find_Num = Nothing
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-15 10:44 ½s¿è

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


    ÁÂÁ«e½ú«ü¾É,«á¾Ç¦b¦¹©«¾Ç¨ì«Ü¦hª¾ÃÑ
½Ð«e½ú¦A«ü¾É,ÁÂÁÂ
¨ç¼Æ¿é¤J:
20221215_3.jpg
2022-12-15 10:27


µ²ªG:
2022-12-15_102402.JPG
2022-12-15 10:27


Function GetNum(xS As String, X$, TY$)
'¡ô¦Û­q¨ç¼Æ¦WGetNum,ÅܼÆ1(xS)¬O¦r¦ê,ÅܼÆ2(X)¬O¦r¦ê,ÅܼÆ3(TY)¬O¦r¦ê
Dim T, k, s%, xD
'¡ô«Å§i(T,k,xD)¬O³q¥Î«¬ÅܼÆ,(s)¬Oµu¾ã¼Æ
'«á¾ÇÁÙ¤£¯à½T©w%&¦p¦ó¥¿½T¨Ï¥Î,¥i¯à¬O¨¾¤î·¸¦ì??

GetNum = ""
'¡ô¥OGetNum³o¦Û­q¨ç¼Æ­È¬OªÅ¦r¤¸!
'¥Nªíªº¬O¥ý¤£ºÞ«á­±¯à¤£¯à¦³­È,¥ýµ¹ªÅ®æ

xS = Replace(xS, X, "|" & X)
'¡ô¥OxS³o¿é¤JªºÅܼƦr¦ê¶i¦æ¦r¤¸¸m´«(¥Î¿é¤JªºXÅܼƦr¦ê´«¦¨ "|"²Å¸¹³s±µXÅܼƦr¦ê)
s = Len(X)
'¡ô¥Os³oµu¾ã¼ÆÅܼƬO ¿é¤JªºXÅܼƦr¦êªº¦r¤¸¼Æ
If s = 0 Then Exit Function
'¡ô¦pªGs¦r¤¸¼Æ¬O 0,´Nµ²§ô¦Û­q¨ç¼Æ
Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD¬O ¦r¨å
For Each T In Split(xS & "|", "|")
'¡ô³]¶¶°j°é!¥OT¬O (xS¿é¤JªºÅܼƦr¦ê³s±µ"|"²Å¸¹«á ³Q"|"²Å¸¹¤À³Îªº¤@ºû°}¦C)°}¦C¤l¦r¦ê
'& "|" ¼È¤£²M·¡¬°¦ó­n¥[³o­Ó??Àµ½Ð«ü¾É
    If Left(T, s) = X Then
    '¡ô¦pªG°j°é°}¦C¤l¦r¦êTªº¥ª°¼ s(¿é¤JªºXÅܼƦr¦êªº¦r¤¸¼Æ) ªº¦r¦ê,¬O ¿é¤JªºXÅܼƦr¦ê??
       k = k + 1
       '¡ô¥Ok³o³q¥Î«¬ÅܼÆ+1
       xD(k) = Val(Mid(T, s + 1))
       '¡ô¥OK³o¼Æ¦r¬Okey,
       'ITEM¬O °j°é°}¦C¤l¦r¦êT ¨ú±qs + 1­Ó¦r¤¸¶}©lªº¥þ³¡¦r¤¸,¸gVal¨ç¦¡Âà¤Æªº¼Æ­È
       '¾Ç¨ì¤F
       '1.xD(k) = Val(Mid(T, 1)),Val¨ç¦¡Âà¤Æªº¦r¦ê¶}ÀY­Y¤£¬O¼Æ¦r,¦^¶Ç­È¬°0
       '2.Val¨ç¦¡Âà¤Æªº¦r¦ê§PŪ¨ì ¥iÂà¤Æ¬°¼Æ­È³sÄò¦r¤¸,¤§«áªº¦r¤¸±Ë±ó

    End If
Next
If k = 0 Then Exit Function
'¡ô¦pªGk¬O 0,´Nµ²§ô¦Û­q¨ç¼Æ
If UCase(TY) = "MAX" Then
'¡ô¦pªG¨ç¼Æ¿é¤JªºTYÅܼƦr¦ê¬O "MAX" ??
   GetNum = Application.Max(xD.Items)
   '¡ô¥OGetNum¦^¶ÇxD¦r¨å¸Ìªºitems³Ì¤j­È
ElseIf UCase(TY) = "MIN" Then
'¡ô§_«h¦pªG¨ç¼Æ¿é¤JªºTYÅܼƦr¦ê¬O "MIN" ??
   GetNum = Application.Min(xD.Items)
   '¡ô¥OGetNum¦^¶ÇxD¦r¨å¸Ìªºitems³Ì¤p­È
ElseIf UCase(TY) = "SUM" Then
'¡ô¦A§_«h¦pªG¨ç¼Æ¿é¤JªºTYÅܼƦr¦ê¬O "SUM" ??
   GetNum = Application.Sum(xD.Items)
   '¡ô¥OGetNum¦^¶ÇxD¦r¨å¸Ìªºitems¥­§¡­È
End If
End Function
http://forum.twbts.com/thread-23804-1-1.html
'¤WÃìµ²©«¥Î Application.Volatile ¬O¦]¬°®æ¦¡ÅܤƷ|¼vÅT¦Û­q¨ç¼Æ,
'©Ò¥H±N¨Ï¥ÎªÌ©w¸qªº¨ç¼Æ¼Ð¥Ü¬°©öÅÜ,
'´ú¸Õµ²ªG¬O´å¼Ð®æÅܤƴN·|­«ºâ¦Û­q¨ç¼Æ


'¦¹©«¥i¥H¤£¥²¨Ï¥ÎApplication.Volatile,¥Ø¼Ð®æ½s¿è´N·|¦^¶Ç­È§Y¥i
Sub T_20221215()
MsgBox Val("4.21¡B03/04 12:00 AA-173.25(x)")
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-16 07:47 ½s¿è

¦^´_ 15# Andy2483


    ­×¥¿¦Û¤vªºªZÂ_,¤£¨D¬Æ¸Ñ,
¤£­n©Ç§O¤H¤£±Ð§Ú­Ì,¸ÓÀ˵ø¦Û¤v§V¤O¤F¦h¤Ö,°µ¿ù¤F¦h¤Ö¨M©w,³à¥¢¤F¦h¤Ö¾Ç²ß¾÷·|
¦^µª¿ù¤F,­×¥¿¦^¨Ó´N¦n¤F!

'1.xD(k) = Val(Mid(T, 1)),Val¨ç¦¡Âà¤Æªº¦r¦ê¶}ÀY­Y¤£¬O¼Æ¦r,¦^¶Ç­È¬°0
'2.Val¨ç¦¡Âà¤Æªº¦r¦ê§PŪ¨ì ¥iÂà¤Æ¬°¼Æ­È³sÄò¦r¤¸,¤§«áªº¦r¤¸±Ë±ó


Sub T_20221216_1() '¥i¥H¶}ÀY¬O¤p¼ÆÂI
MsgBox Val(".21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_2() '¥i¥H¦Û°ÊTrim±¼«e¤èªÅ¥Õ¦r¤¸
MsgBox Val(" 4.21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_3() '¥i¥H§PÂ_¶}ÀY¬O­t²Å³s±µ¤p¼ÆÂI
MsgBox Val("-.21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_4() '¥i¥H§PÂ_¶}ÀY¬O­t²Å
MsgBox Val("-4.21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_5() '¥i¥H¦Û°ÊTrim±¼«e«á¤èªÅ¥Õ¦r¤¸
MsgBox Val(" -4.21 ¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_6() '¥i¥H§PÂ_¶}ÀY¬O¥¿²Å
MsgBox Val("+4.21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_7() '¤£°µ¹Bºâ
MsgBox Val("+4.21+1¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_8() '¦Û°Ê¥h°£¤¤¶¡ªºªÅ¥Õ¦r¤¸
MsgBox Val("+4.21 1 1¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_9() '¤£·|±N¤é´Á«¬¦¡ªº¦r¦ê§P¬°¤é´Á,¥B¾ã¼Æªº¦h©ó0·|¦Û°Ê¥h°£
MsgBox Val("03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_10() '¤£·|±N®É¶¡«¬¦¡ªº¦r¦ê§P¬°®É¶¡
MsgBox Val("12:00 AA-173.25(x)")
End Sub
Sub T_20221216_11() '·|§P§O©_©Ç²Å¸¹
Workbooks.Add
[A1] = "1200" & ChrW(160) & "00 AA-173.25(x)"
MsgBox Val("1200" & ChrW(160) & "00 AA-173.25(x)")
http://forum.twbts.com/viewthrea ... &from=favorites
End Sub
Sub T_20221216_12() '¥i¥H¶}ÀY¬O¤p¼Æ¤]¥i¥H¥hªÅ¬Ù¹s
MsgBox Val("00 0.21¡B03/04 12:00 AA-173.25(x)")
End Sub

'¦pªGÁÙ¦³·Q¨ì¸É¥Rªº,«á¾Ç·|¦A¦^¨Ó­×¥¿,ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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


    ÁÂÁ«e½ú«ü¾É
«á¾Ç¬ã¨s«e½úªº½d¨Ò°}¦C¤½¦¡µLªk²z¸Ñ,½Ð«e½ú¼·ªÅ¦A«ü¾É
½Ð±Ð«e½ú:
1.¬O¤£¬O¦b²Ä5¤j¶µ¶}©l´N¬O°}¦C?
2.ROW($1:$19)*199-198,199 ¬O¤°»ò·N¸q?
3.¬°¤°»ò --TEXT() «e­±­n¦³¨â­Ó - ­t¸¹ ?

Xl0000311-1_20221216_1.zip (19.09 KB)

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

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-23 11:46 ½s¿è

¦^´_ 16# Andy2483

¸É¥R:
Sub T_20221216_11() '·|¿ë»{³æ¤Þ¸¹
MsgBox Val("'4.21¡B03/04 12:00 AA-173.25(x)")
End Sub
Sub T_20221216_12() '·|¿ë»{"^"²Å¸¹,¦Ó¥B¥X²{ «¬ºA¤£²Åªº°»¿ù
MsgBox Val("4.21^2¡B03/04 12:00 AA-173.25(x)")
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 18# Andy2483

²{¦b·Q±o¨ìªº°£¿ù¤è¦¡:
Sub T_20221216_13() '·|¿ë»{³æ"^"²Å¸¹,¦Ó¥B¥X²{ «¬ºA¤£²Åªº°»¿ù
MsgBox Val(Replace("4.21^2¡B03/04 12:00 AA-173.25(x)", "^", ""))
MsgBox Val(Split("4.21^2¡B03/04 12:00 AA-173.25(x)", "^")(0))
'¦pªG­n¹Bºâ:
MsgBox Val(Split("4.21^2¡B03/04 12:00 AA-173.25(x)", "^")(0)) ^ Val(Split("4.21^2¡B03/04 12:00 AA-173.25(x)", "^")(1))
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-3-8 16:39 ½s¿è

¦^´_ 15# Andy2483


ÁÂÁ ­ã´£³¡ªL«e½ú   
'& "|" ¼È¤£²M·¡¬°¦ó­n¥[³o­Ó??Àµ½Ð«ü¾É

[D3]="03/04 10:00 AA-179.25(x) ªø-4.21¡B03/04 12:00 AA-173.25(x) ªø-3.019¡B03/05 12:00 AA-179(x) ªø-5.169¡B875.09 ªø"
[B2]=GetNum(D3,"ªø","max")=0

======================================
©êºp!¦n¹³¤]¤£¬O³o­ì¦],Ä~Äò±´¯Á
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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