Andy2483

¦^´_ 9# Andy2483


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

¤U¸ü (99.25 KB)

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

hcm19522

¦^´_ 6# ranceymm

¤½¦¡³]©w¦r¦êªø ³Ì¦h199¦r  9­Ó¼Æ¦r     ²Ä¤GºØ ©Ò¦³199§ï299 ¸Õ¸Õ

Andy2483

¦^´_ 6# ranceymm

¥H«e³£·d¤£À´¦Û©w¸q¨ç¼Æ,¬O³o©«¾Ç¨Óªº
http://forum.twbts.com/thread-23804-1-1.html

«á¾Ç¦A½m²ß¥t¤@ºØ¤èªk¨Ñ°Ñ¦Ò

¤U¸ü (64.48 KB)

2022-12-14 08:27

¤U¸ü (66.29 KB)

2022-12-14 08:27

Option Explicit
Function MX(Àx¦s®æ As Range) As Double
Dim Y, V, A$, j&, P#
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
A = Replace(Replace(Replace(Àx¦s®æ, " ªø", ","), "¡B", ","), " ", ",")
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

½Ð«e½ú±`¤W½×¾ÂÂsÄý,¤@°_¾Ç²ß!

Andy2483

¦^´_ 7# ranceymm


    ÁÂÁ«e½ú¦^´_
¤µ¤Ñ¦AÀ˵ø&½Æ²ß¤F¦¹©«,µo²{«Ü¦hªº¦h¾l»P¤£¨¬,«á¾Ç¤]¦A¾Ç²ß¤¤,¥ý¨D¦³,¦A¨D¦n,¤ß±oµù¸Ñ¨Ñ°Ñ¦Ò

Option Explicit
Function ³Ì¤j­È(«e¦r¤¸ As String, «á¦r¤¸ As String, Àx¦s®æ As Range) As Double
'¡ô³Ì¤j­È()¬O¦Û¤v©w¸qªº¨ç¼Æ¦W¦r,¸Ì­±ªº:
'«e¦r¤¸ As String :«e¦r¤¸«Å§i¬O¦r¦êÅܼÆ,¬O¨Ï¥Î¦¹¨ç¼Æ»Ý¿é¤Jªº­È "ªø"
'«á¦r¤¸ As String :«á¦r¤¸¤]«Å§i¬O¦r¦êÅܼÆ,¬O¨Ï¥Î¦¹¨ç¼Æ»Ý¿é¤Jªº­È "¡B"
'Àx¦s®æ As Range :Àx¦s®æ«Å§i¬OÀx¦s®æÅܼÆ,¦Ó¥B¬O¨Ï¥Î¦¹¨ç¼Æ»Ý¿é¤J(©Î¥Î¿ïªº)ªºÀx¦s®æ¦ì§}
'As Double¬O«Å§i³o³Ì¤j­Èµ²ªG¬OÂùºë«×¤p¼Æ
'¦pªG­n¤@¥y¸Ü±Ô­z:µ¹³o¨ç¼Æ¨â­Ó¦r¦ê,¤@­Ó³æ®æªºÀx¦s®æ¦ì§},«á­±ªºµ{§Ç¦^¶Ç Âùºë«×­È,
'¦Ü©ó¦^¶Çªº¬O¤£¬O³Ì¤j­È?? ´N­n¬Ý¦¨¬O¦³¨S¦³§ì¨ì³Ì¤j­È
'­«­nªº¬O«e½ú¹ï¸ê®ÆªºÅÞ¿è³W«h¬O§_¥¿½T! ¸ê®Æ¿ù»~´Nºâ¤£¥X¨Ó!¦³¦nªº¶}©l,¦¨¥\´N¤£»·¤F
Dim Y, V, A$, j&, P#
'¡ô«Å§iÅܼÆ:(Y,V)¬O³q¥Î«¬ÅܼÆ,(A)¬O¦r¦êÅܼÆ,(j)¬Oªø¾ã¼ÆÅܼÆ,(P)¬OÂùºë«×¤p¼Æ
Application.Volatile
'¡ô±N¨Ï¥ÎªÌ©w¸qªº¨ç¼Æ¼Ð¥Ü¬°©öÅÜ¡C
'https://learn.microsoft.com/zh-tw/office/vba/api/excel.application.volatile
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY¬O¦r¨å
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/dictionary-object
   A = Replace(Replace(Replace(Àx¦s®æ, " " & «e¦r¤¸, ","), «á¦r¤¸, ","), " ", ",")
   '¡ô¥OA³o¦r¦êÅܼƬO ¨ç¼Æ¨Ï¥Î®É¿é¤J D3 Àx¦s®æ¸Ìªº­È,¸g¹L¤T¦¸¸m´«¦r¤¸ (" ªø":",")("¡B":",")(" ":",")
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/replace-function
   V = Split(A, ",")
   '¡ô¥OV³o³q¥Î«¬ÅܼƸˤJ A¦r¦ê³QSplit()¤À³Î¦¨ªº¼Æ­Ó¦r¦ê,V´NÅܦ¨¬O¤@ºû°}¦C
   'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/split-function
   For j = 3 To UBound(V) Step 4
   '¡ô³]¶¶°j°é!j±q3¨ìV°}¦Cªº³Ì«á¤@­Ó¯Á¤Þ¸¹½X,j°j°é¨C¶¦^¨Ó¤@¦¸´N¥[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
      '¡ô¦pªG¯Á¤Þ­Èj¦ì¸mªºV°}¦C­È¸g¹LIsNumeric()¨ç¼Æ§PÂ_¬O¼Æ¦r,¦Ó¥B¤£¬OªÅ¦r¤¸ ??
      'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/isnumeric-function
         P = V(j): Y(P) = ""
         '¡ô·íIF±ø¥ó¦¨¥ß!´N¥OP³oÂùºë«×ÅܼƸˤJ ¯Á¤Þ­Èj¦ì¸mªºV°}¦C­È
         'µM«á±N³oPÅܼƷíkey,item¬OªÅ¦r¤¸,­Ë¤JY¦r¨å¸Ì
      End If
   Next
   ³Ì¤j­È = Application.Max(Y.keys())
   '¡ô¥O ³Ì¤j­È ³oÂùºë«×ÅܼƥΠMax() ¨ú±oY¦r¨å¸ÌKEYSªº³Ì¤j­È,Åã¥Ü¦bÀx¦s®æ¸Ì
   'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheetfunction.max
End Function

ranceymm

¦^´_ 5# Andy2483


    ·PÁ¡AVBA¬O¤p§Ìªºµu¶µ¡A¨S·Q¹L¥Î¦Û©w¸q´N¥i¥H¨ú±o·Q­nªº¼Æ­È¡A³o­Ó¥i¥H¬ã¨s¤@°}¤l¤F...
    ­è­è´ú¸Õ¡AµL½×¸ê®Æ¦h¤Öµ§³£¥i¥Hºâ¥X¨Ó¡C

ranceymm

¦^´_ 4# hcm19522


    ·PÁÂhcm¤j´£¨Ñ³o»ò¦hºØ¤èªk¡A
    «e¨â­Ó¨ç¼Æ¼gªk¦b¸ê®Æµ§¼Æ¥X²{²Ä8­Ó®É´N·|¥X²{¿ù»~¡A¤pªº¥\¤O¤£¨¬¡A¬Ý¤£¥X¬O­þÃ䪺°ÝÃD...
   
    ²Ä¤T­Ó¨ç¼Æ¬O³v¦r¤ñ¹ï¡AµM«á¦A§ì¨ú¼Æ¦r¨úMAX¶Ü¡H
    ¨S·Q¹LLARGE¸òSMALL¯à³o¼Ë¥Î¡A³o­Ó¼gªk¦b¨ä¥L¸ê®Æ¤ñ¹ïÁÙº¡¸U¥Îªº©O¡I

Andy2483

¦^´_ 1# ranceymm


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
¦Û©w¸q¨ç¼Æ¤è¦¡¨Ñ°Ñ¦Ò
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¦Û©w¸q¨ç¼Æ()

¨Ï¥Î¤èªk:
1.±N¤U¦Cµ{¦¡½X©ñ¤JModule1
2.[B3]Àx¦s®æ¿é¤J"=³Ì¤j­È("ªø","¡B",D3)"

¤U¸ü (32.34 KB)

2022-12-13 13:37

Option Explicit
Function ³Ì¤j­È(«e¦r¤¸ As String, «á¦r¤¸ As String, Àx¦s®æ As Range) As Double
Dim Y, A$, j&, P#, V
Application.Volatile
Set Y = CreateObject("Scripting.Dictionary")
   A = Replace(Replace(Replace(Àx¦s®æ, " " & «e¦r¤¸, ","), «á¦r¤¸, ","), " ", ",")
   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
   ³Ì¤j­È = Application.Max(Y.keys())
End Function

hcm19522

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

ranceymm

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


·PÁ¤j¯«À°¦£¡A·Q¤£¨ì­W´o¤F¨â­Ó¬P´Áªº¨ç¼Æ¥i¥H³o¼Ë¼g¡C
³o¼Ë¥i¥H¨D¥X»Ý­nªº­È¡A­Y¸ê®Æ§ó¦hµ§¥u­nROW¨ºÃä§â¦C¼Æ¼W¥[´N¥i¥H¹ïÀ³¤F¡C

  ¥ÎMID¸òFIND¤gªk·Ò¿û¥i¯à­n¼g­Ó¤Q´X¦æªº¤½¦¡...

­ã´£³¡ªL

C3/
°}¦C¤½¦¡(¤TÁä»ô«ö)//
=MAX(--TEXT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(D3,"ªø","¡Bªø"),"¡B",REPT(" ",99)&"|"),ROW($1:$19)*99-98,99),"|ªø",),"G/³q¥Î®æ¦¡;0;0;\0"))

­Y¤å¦r¤Óªø, ´N¥ÎVBA§a!