返回列表 上一主題 發帖

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

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

各位大大

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

求解.jpg
2022-12-12 17:15


求解.zip (6.93 KB)

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

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

若文字太長, 就用VBA吧!

TOP

回復 2# 准提部林


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

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

TOP

隨意窩 "EXCEL迷"  blog  或https://blog.xuite.net/hcm19522/twblog
已收集8500篇 EXCEL函數

TOP

本帖最後由 Andy2483 於 2022-12-13 13:48 編輯

回復 1# ranceymm


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

使用方法:
1.將下列程式碼放入Module1
2.[B3]儲存格輸入"=最大值("長","、",D3)"
1.jpg
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
看得懂是應該的,懂得應用才像學生,臉皮厚點學會更謹慎積極

TOP

回復 4# hcm19522


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

TOP

回復 5# Andy2483


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

TOP

本帖最後由 Andy2483 於 2022-12-14 08:07 編輯

回復 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
看得懂是應該的,懂得應用才像學生,臉皮厚點學會更謹慎積極

TOP

本帖最後由 Andy2483 於 2022-12-14 08:43 編輯

回復 6# ranceymm

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

後學再練習另一種方法供參考
2.jpg
2022-12-14 08:27


3.jpg
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

請前輩常上論壇瀏覽,一起學習!
看得懂是應該的,懂得應用才像學生,臉皮厚點學會更謹慎積極

TOP

回復 6# ranceymm

公式設定字串長 最多199字  9個數字     第二種 所有199改299 試試
隨意窩 "EXCEL迷"  blog  或https://blog.xuite.net/hcm19522/twblog
已收集8500篇 EXCEL函數

TOP

        靜思自在 : 時時好心就是時時好日。
返回列表 上一主題