帖子: 18
主題: 5
精華: 0
積分: 63
點名: 0
作業系統: Win10
軟體版本: Office365
閱讀權限: 20
性別: 男
來自: 雲林縣
註冊時間: 2020-11-9
最後登錄: 2023-7-22

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2022-12-12 17:32 | 只看該作者

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

各位大大

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

求解.zip (6.93 KB)

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

樓主熱帖

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2834
主題: 10
精華: 0
積分: 2890
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-25

2^#

發表於 2022-12-12 21:31 | 只看該作者

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

若文字太長, 就用VBA吧!

TOP

ranceymm

中學生 Rank: 2

帖子: 18
主題: 5
精華: 0
積分: 63
點名: 0
作業系統: Win10
軟體版本: Office365
閱讀權限: 20
性別: 男
來自: 雲林縣
註冊時間: 2020-11-9
最後登錄: 2023-7-22

3^#

發表於 2022-12-12 22:39 | 只看該作者

回復 2# 准提部林

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

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

TOP

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-25

熱烈參與勳章

4^#

發表於 2022-12-13 10:54 | 只看該作者

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

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

5^#

發表於 2022-12-13 13:45 | 只看該作者

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

回復 1# ranceymm

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

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

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

ranceymm

中學生 Rank: 2

帖子: 18
主題: 5
精華: 0
積分: 63
點名: 0
作業系統: Win10
軟體版本: Office365
閱讀權限: 20
性別: 男
來自: 雲林縣
註冊時間: 2020-11-9
最後登錄: 2023-7-22

6^#

發表於 2022-12-13 22:54 | 只看該作者

回復 4# hcm19522

感謝hcm大提供這麼多種方法，
前兩個函數寫法在資料筆數出現第8個時就會出現錯誤，小的功力不足，看不出是哪邊的問題...

第三個函數是逐字比對，然後再抓取數字取MAX嗎？
沒想過LARGE跟SMALL能這樣用，這個寫法在其他資料比對還滿萬用的呢！

TOP

ranceymm

中學生 Rank: 2

帖子: 18
主題: 5
精華: 0
積分: 63
點名: 0
作業系統: Win10
軟體版本: Office365
閱讀權限: 20
性別: 男
來自: 雲林縣
註冊時間: 2020-11-9
最後登錄: 2023-7-22

7^#

發表於 2022-12-13 23:11 | 只看該作者

回復 5# Andy2483

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

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

8^#

發表於 2022-12-14 07:59 | 只看該作者

本帖最後由 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

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1446
主題: 40
精華: 0
積分: 1470
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-10-21

9^#

發表於 2022-12-14 08:32 | 只看該作者

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

回復 6# ranceymm

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

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

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

hcm19522

大學生 Rank: 4

帖子: 1387
主題: 4
精華: 0
積分: 1397
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-11-25

熱烈參與勳章

10^#

發表於 2022-12-14 09:20 | 只看該作者

回復 6# ranceymm

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

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP