stillfish00

¦^´_ 20# cdkee
´¡¤J¤@¯ë¼Ò²Õ¡A¶K¤W¤U­±code

1. Function FindMaxLoss(rngInput As Range, Optional order As Integer = 1)
   
2.     On Error GoTo ErrHandle
   
3.    
   
4.     Dim ar, arLoss
   
5.     ar = Application.Transpose(rngInput)
   
6.     ReDim arLoss(1 To UBound(ar))
   
7.    
   
8.     Dim i, count As Integer, sum As Double
   
9.     Dim indMin As Long, indMax As Long
   
10.     indMin = 1: indMax = 1
    
11.     i = 2
    
12.     Do While i <= UBound(ar)
    
13.         If ar(i) < ar(indMin) Then indMin = i
    
14.         If ar(i) > ar(indMax) Or i = UBound(ar) Then
    
15.             If indMin <> indMax Then
    
16.                 count = count + 1
    
17.                 arLoss(count) = ar(indMin) - ar(indMax)
    
18.                 i = indMin
    
19.             End If
    
20.             indMin = i
    
21.             indMax = i
    
22.         End If
    
23.         i = i + 1
    
24.         DoEvents
    
25.     Loop
    
26.         
    
27.     ReDim Preserve arLoss(1 To count)
    
28.     FindMaxLoss = Application.WorksheetFunction.Small(arLoss, order)
    
29.     Exit Function
    
30.    
    
31. ErrHandle:
    
32.     FindMaxLoss = CVErr(xlErrValue)
    
33. End Function

½Æ»s¥N½X

D7¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,1)
D8¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,2)
D9¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,3)
¦V¥k©ì¦²¶ñº¡¤½¦¡

cdkee

¦^´_ 21# stillfish00

ÁÂÁ¤j¤jÀ°§U!
´ú¸Õ«á¡Aµo²{²Ä¤@³Ì¤j¥¢¤À¡A·|¥H0´î³Ì¤j²Ö­p¤À¼Æ¡A½Ð±Ð¦p¦ó­×§ï?ÁÂÁÂ!

cdkee

¦^´_ 21# stillfish00

§@¤F¥H¤U­×§ï¡AÁÂÁ¦U¦ì¤j¤jÀ°¦£!
D7¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,2)
D8¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,3)
D9¶ñ¤W¤½¦¡  =FindMaxLoss(D$20:D$34,4)

VBALearner

¦^´_ 19# cdkee

§Ú»¡±z¤]µy·L¤F¸Ñ¤@¤UVBA°ò¥»ªº«¬ºA­­¨î§a= ="
¥ÎintegerµLªk­pºâ¨ì³o»ò¤jªº¼Æ¦r¡A«Å§iÅܼƭn§ï¦¨­þ­Ó«¬ºAºô¯¸¤W¦³¼g
http://edisonx.pixnet.net/blog/post/42112370-vba-%E5%9F%BA%E6%9C%AC%E8%B3%87%E6%96%99%E5%9E%8B%E6%85%8B

cdkee

¦^´_ 24# VBALearner

¤´µM¤F¸Ñ¤¤¡AÁÂÁ¤j¤j«ü¾É¡C

cdkee

¦^´_ 21# stillfish00

¤j¤jªº¨S¦³°ÝÃD¡A¤µ¤Ñ¥¿±`¤F¡C

­ã´£³¡ªL

ÁÙ¬O¦³ÂI¬Ý¤£À´¡A¤j¬ù¤]¼g¤@­Ó¡G

1. Function GetLoseVal(xRng As Range, xInd%)
   
2. Dim Arr, xMin, i&, j&, k%, N%, xD
   
3. xMin = "":  GetLoseVal = "":  Arr = xRng.Value
   
4. Set xD = CreateObject("Scripting.Dictionary")
   
5. For i = 1 To UBound(Arr)
   
6. For j = i + 1 To UBound(Arr)
   
7.     If Arr(j, 1) >= Arr(i, 1) Then Exit For
   
8.     If Arr(j, 1) < xMin Then k = j: xMin = Arr(j, 1)
   
9. Next
   
10.     If xMin <> "" Then N = N + 1: xD(N) = xMin - Arr(i, 1): i = k: xMin = ""
    
11. Next
    
12. If xInd <= N Then GetLoseVal = Application.Small(xD.items, xInd)
    
13. End Function

½Æ»s¥N½X

¢Ò¢¶¤½¦¡¡G=GetLoseVal(D$20:D$34,ROW(A1))¡@¥k©Ô¤U©Ô
¡@
¡@

cdkee

ÁÙ¬O¦³ÂI¬Ý¤£À´¡A¤j¬ù¤]¼g¤@­Ó¡G



¢Ò¢¶¤½¦¡¡G=GetLoseVal(D$20$34,ROW(A1))¡@¥k©Ô¤U©Ô
¡@
¡@
­ã´£³¡ªL µoªí©ó 2016-8-17 22:45

ÁÂÁ­㴣³¡ªLª©¤j!¬Oªº¡A´N¬O³o¼Ë¡C
½Ð±Ð¤j¤j¡AD7ªº¤½¦¡¤¤¡A¥ÎROW(A1)¦³¤°»ò§@¥Î?

­ã´£³¡ªL

¦^´_ 28# cdkee


=ROW(A1)  >>>> = 1
¤U©Ô«h»¼¼W
=ROW(A2)  >>>> = 2
...
...

cdkee

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

¾Ç¦h¼Ë§Þ¥©¡AÁÂÁª©¤j±Ð¾É!