搵字串最大值

bongbong3481

有一個column 字串,  請問一下,  如果搵T的最大值，會出t5,  如果搵Tea 的最大值會出Tea7 , 如此類推， 請問python 點樣寫？ （念左好耐都解決吾到）

T1
t5
T2
TEa3
Tea7
TeA2
.
.
.
/////////////////////////////////////////COPILOT 寫的VBA 版
Sub FindMaxTeaValue()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim maxValue As Double
    Dim currentValue As Double
   
    ' 假設你的資料在 Sheet1 的 A 列（第一列）
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
   
    ' 初始化最大值
    maxValue = 0
   
    For Each cell In rng
        If UCase(cell.Value) Like "T*" Then
            ' 檢查是否以 "T" 開頭（不區分大小寫）
            currentValue = Val(Mid(cell.Value, 2))
            If currentValue > maxValue Then
                maxValue = currentValue
            End If
        End If
    Next cell
   
    MsgBox "以 T 開頭的最大值為 " & maxValue
End Sub

javacomhk

ask chatgpt or Github Copilot

提示:可以使用正则表达式 regular expression 来查找字符串并获取包含指定子字符串的最大值

jetdone

這條題目是基本功深度, 如果咁都要問人, 就要想想是否應該向寫程式的方向發展.

bongbong3481

回覆 2# javacomhk

問左Ai 解決了

bongbong3481

回覆 3# jetdone


我本身做文職，吾係IT，工作上遇到呢個問題，人手可以解決，但想試下用PROGRAM 解決。

java2

文職要用python

回覆  jetdone


我本身做文職，吾係IT，工作上遇到呢個問題，人手可以解決，但想試下用PROGRAM 解決。 ...
bongbong3481 發表於 2024-6-27 06:59

bongbong3481

回覆 6# java2


    大量重覆既文書處理e.g. 計數，email 多，本身用開vba , 但vba 使用範圍太窄，所以學python

s20012797

1. Sub ExtractAndGroupValues()
   
2.     Dim df As Variant
   
3.     Dim dfExtracted As Variant
   
4.     Dim dfMax As Variant
   
5.     Dim i As Long
   
6.     Dim substring As String
   
7.     Dim value As Long
   
8.    
   
9.     ' Assume your data is in the range "A1:A6" on the "Sheet1" worksheet
   
10.     df = Worksheets("Sheet1").Range("A1:A6").Value
    
11.    
    
12.     ' Initialize the new dataframe
    
13.     ReDim dfExtracted(1 To UBound(df), 1 To 2)
    
14.    
    
15.     ' Process each row of data one by one
    
16.     For i = 1 To UBound(df)
    
17.         ' Extract the substring and value
    
18.         substring = Extract_Substring(df(i, 1))
    
19.         value = Extract_Value(df(i, 1))
    
20.         
    
21.         ' Store the extracted values in the new dataframe
    
22.         dfExtracted(i, 1) = substring
    
23.         dfExtracted(i, 2) = value
    
24.     Next i
    
25.    
    
26.     ' Group by the substring and find the maximum value
    
27.     dfMax = GroupAndFindMax(dfExtracted)
    
28.    
    
29.     ' Display the result on the "Sheet2" worksheet
    
30.     Worksheets("Sheet2").Range("A1").Resize(UBound(dfMax), 2).Value = dfMax
    
31. End Sub
    
32. 
    
33. Function Extract_Substring(input As String) As String
    
34.     Dim match As Object
    
35.     Dim regEx As Object
    
36.    
    
37.     Set regEx = CreateObject("VBScript.RegExp")
    
38.     regEx.Pattern = "([a-zA-Z]+)(\d+)"
    
39.    
    
40.     Set match = regEx.Execute(input)
    
41.     If match.Count > 0 Then
    
42.         Extract_Substring = match(0).SubMatches(0)
    
43.     Else
    
44.         Extract_Substring = ""
    
45.     End If
    
46. End Function
    
47. 
    
48. Function Extract_Value(input As String) As Long
    
49.     Dim match As Object
    
50.     Dim regEx As Object
    
51.    
    
52.     Set regEx = CreateObject("VBScript.RegExp")
    
53.     regEx.Pattern = "([a-zA-Z]+)(\d+)"
    
54.    
    
55.     Set match = regEx.Execute(input)
    
56.     If match.Count > 0 Then
    
57.         Extract_Value = CLng(match(0).SubMatches(1))
    
58.     Else
    
59.         Extract_Value = 0
    
60.     End If
    
61. End Function
    
62. Function GroupAndFindMax(data As Variant) As Variant
    
63.     Dim result As Variant
    
64.     Dim dict As Object
    
65.     Dim key As Variant
    
66.     Dim maxValue As Long
    
67.     Dim i As Long
    
68.    
    
69.     ' Initialize the result
    
70.     ReDim result(1 To 0, 1 To 2)
    
71.    
    
72.     ' Create a dictionary to group and find the maximum value
    
73.     Set dict = CreateObject("Scripting.Dictionary")
    
74.    
    
75.     ' Process each row of data
    
76.     For i = 1 To UBound(data)
    
77.         key = data(i, 1)
    
78.         If dict.Exists(key) Then
    
79.             If data(i, 2) > dict(key)(2) Then
    
80.                 dict(key) = Array(key, data(i, 2)) ' Fix: Store the value in the second column
    
81.             End If
    
82.         Else
    
83.             dict.Add key, Array(key, data(i, 2))
    
84.         End If
    
85.     Next i
    
86.    
    
87.     ' Store the result in the new dataframe
    
88.     i = 1
    
89.     For Each key In dict.Keys
    
90.         ReDim Preserve result(1 To i, 1 To 2)
    
91.         result(i, 1) = dict(key)(0) ' Fix: Store the key in the first column
    
92.         result(i, 2) = dict(key)(1)
    
93.         i = i + 1
    
94.     Next key
    
95.    
    
96.     GroupAndFindMax = result
    
97. End Function

複製代碼

你確定vba 使用範圍太窄???

bongbong3481

回覆 8# s20012797


    我表達得吾好，我指vba 範圍窄，主要是指office 軟件以外既野，eg. 圖像處理，pdf，網上資料，等等

java2

今時今日做文職咁高要求
去返你個case, 可以每個字串用regulatr expression 去拆開key 同 value,  用hashmap (vba 叫dictionary )記下最大值, 在相同key 下將大於前一個值的put 入去, 然后想搵某key 最大值可直接用key 由hashmap 取.

vba 範圍唔窄也可以寫到.

Dim dict As Object

Sub a()
    Set dict = CreateObject("Scripting.Dictionary")
    For i = 1 To 6
        Debug.Print findMax(Cells(i, 1))
    Next i

    For Each Key In dict.Keys
        Debug.Print Key, dict(Key)
    Next Key
End Sub

Function findMax(s As String) As String

    Dim oRe As Object
    Set oRe = CreateObject("vbscript.regexp")

    oRe.Pattern = "([a-zA-Z]+)(\d+)"

    Set oMatches = oRe.Execute(s)
    If oMatches.Count > 0 Then
        Set oMatch = oMatches(0)
        skey = UCase(oMatch.SubMatches(0))
        sval = oMatch.SubMatches(1)
        If sval > dict(skey) Then dict(skey) = sval
        findMax = skey + ":" + sval
    Else
        findMax = "no data"
    End If
End Function

回覆  java2


    大量重覆既文書處理e.g. 計數，email 多，本身用開vba , 但vba 使用範圍太窄，所以學py ...
bongbong3481 發表於 2024-6-27 12:36