[發問] 同一儲存格內資料進行大小比對

ranceymm

各位大大

想請問一下，
如圖：資料在D行，有辦法用函數在B行顯示出D行"長"後面最大的數值嗎？

下載 (29.95 KB)

2022-12-12 17:15

求解.zip (6.93 KB)

下載次數: 17

2022-12-12 17:29

有試過MID+FIND，不過公式會很長，
SUBSTITUE也沒辦法支援萬用字元，所以無法將"長"前面的字元取代掉...

准提部林

C3/
陣列公式(三鍵齊按)//
=MAX(--TEXT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(D3,"長","、長"),"、",REPT(" ",99)&"|"),ROW($1:$19)*99-98,99),"|長",),"G/通用格式;0;0;\0"))

若文字太長, 就用VBA吧!

ranceymm

回復 2# 准提部林


感謝大神幫忙，想不到苦惱了兩個星期的函數可以這樣寫。
這樣可以求出需要的值，若資料更多筆只要ROW那邊把列數增加就可以對應了。

  用MID跟FIND土法煉鋼可能要寫個十幾行的公式...

hcm19522

https://blog.xuite.net/hcm19522/twblog/590654094

Andy2483

回復 1# ranceymm


    謝謝前輩發表此主題與範例
自定義函數方式供參考
後學藉此帖練習陣列與字典,自定義函數()

使用方法:
1.將下列程式碼放入Module1
2.[B3]儲存格輸入"=最大值("長","、",D3)"

下載 (32.34 KB)

2022-12-13 13:37

Option Explicit
Function 最大值(前字元 As String, 後字元 As String, 儲存格 As Range) As Double
Dim Y, A$, j&, P#, V
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
   A = Replace(Replace(Replace(儲存格, " " & 前字元, ","), 後字元, ","), " ", ",")
   V = Split(A, ",")
   For j = 3 To UBound(V) Step 4
      If IsNumeric(V(j)) And V(j) <> "" Then
         P = V(j): Y(P) = ""
      End If
   Next
   最大值 = Application.Max(Y.keys())
End Function

ranceymm

回復 4# hcm19522


    感謝hcm大提供這麼多種方法，
    前兩個函數寫法在資料筆數出現第8個時就會出現錯誤，小的功力不足，看不出是哪邊的問題...
   
    第三個函數是逐字比對，然後再抓取數字取MAX嗎？
    沒想過LARGE跟SMALL能這樣用，這個寫法在其他資料比對還滿萬用的呢！

ranceymm

回復 5# Andy2483


    感謝，VBA是小弟的短項，沒想過用自定義就可以取得想要的數值，這個可以研究一陣子了...
    剛剛測試，無論資料多少筆都可以算出來。

Andy2483

回復 7# ranceymm


    謝謝前輩回復
今天再檢視&複習了此帖,發現很多的多餘與不足,後學也再學習中,先求有,再求好,心得註解供參考

Option Explicit
Function 最大值(前字元 As String, 後字元 As String, 儲存格 As Range) As Double
'↑最大值()是自己定義的函數名字,裡面的:
'前字元 As String :前字元宣告是字串變數,是使用此函數需輸入的值 "長"
'後字元 As String :後字元也宣告是字串變數,是使用此函數需輸入的值 "、"
'儲存格 As Range :儲存格宣告是儲存格變數,而且是使用此函數需輸入(或用選的)的儲存格位址
'As Double是宣告這最大值結果是雙精度小數
'如果要一句話敘述:給這函數兩個字串,一個單格的儲存格位址,後面的程序回傳 雙精度值,
'至於回傳的是不是最大值?? 就要看成是有沒有抓到最大值
'重要的是前輩對資料的邏輯規則是否正確! 資料錯誤就算不出來!有好的開始,成功就不遠了
Dim Y, V, A$, j&, P#
'↑宣告變數:(Y,V)是通用型變數,(A)是字串變數,(j)是長整數變數,(P)是雙精度小數
Application.Volatile
'↑將使用者定義的函數標示為易變。
'https://learn.microsoft.com/zh-tw/office/vba/api/excel.application.volatile
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y是字典
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/dictionary-object
   A = Replace(Replace(Replace(儲存格, " " & 前字元, ","), 後字元, ","), " ", ",")
   '↑令A這字串變數是 函數使用時輸入 D3 儲存格裡的值,經過三次置換字元 (" 長":",")("、":",")(" ":",")
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/replace-function
   V = Split(A, ",")
   '↑令V這通用型變數裝入 A字串被Split()分割成的數個字串,V就變成是一維陣列
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/split-function
   For j = 3 To UBound(V) Step 4
   '↑設順迴圈!j從3到V陣列的最後一個索引號碼,j迴圈每繞回來一次就加3,7,11,.....
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/fornext-statement
      If IsNumeric(V(j)) And V(j) <> "" Then
      '↑如果索引值j位置的V陣列值經過IsNumeric()函數判斷是數字,而且不是空字元 ??
      'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/isnumeric-function
         P = V(j): Y(P) = ""
         '↑當IF條件成立!就令P這雙精度變數裝入 索引值j位置的V陣列值
         '然後將這P變數當key,item是空字元,倒入Y字典裡
      End If
   Next
   最大值 = Application.Max(Y.keys())
   '↑令 最大值 這雙精度變數用 Max() 取得Y字典裡KEYS的最大值,顯示在儲存格裡
   'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheetfunction.max
End Function

Andy2483

回復 6# ranceymm

以前都搞不懂自定義函數,是這帖學來的
http://forum.twbts.com/thread-23804-1-1.html

後學再練習另一種方法供參考

下載 (64.48 KB)

2022-12-14 08:27

下載 (66.29 KB)

2022-12-14 08:27

Option Explicit
Function MX(儲存格 As Range) As Double
Dim Y, V, A$, j&, P#
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
A = Replace(Replace(Replace(儲存格, " 長", ","), "、", ","), " ", ",")
V = Split(A, ",")
For j = 3 To UBound(V) Step 4
   If IsNumeric(V(j)) And V(j) <> "" Then
      P = V(j): Y(P) = ""
   End If
Next
MX = Application.Max(Y.keys())
Set Y = Nothing : Erase V
End Function

請前輩常上論壇瀏覽,一起學習!

hcm19522

回復 6# ranceymm

公式設定字串長 最多199字  9個數字     第二種 所有199改299 試試