ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦ó­pºâ¨C­ÓÄ檺²Ö­p³Ì¤j¥¢¤À

¦^´_ 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©ì¦²¶ñº¡¤½¦¡
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 21# stillfish00

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

TOP

¦^´_ 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)

TOP

¦^´_ 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
¯u¤ß·PÁ¨C¤@¦ìÄ@·N¤À¨É©Ò¾Ç¡B«ü¾É·s¤âªº¤H!

TOP

¦^´_ 24# VBALearner

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

TOP

¦^´_ 21# stillfish00

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

TOP

ÁÙ¬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©Ô
¡@
¡@

TOP

ÁÙ¬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)¦³¤°»ò§@¥Î?

TOP

¦^´_ 28# cdkee


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

TOP

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

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

TOP

        ÀR«ä¦Û¦b : Ä@­n¤j¡B§Ó­n°í¡B®ð­n¬X¡B¤ß­n²Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD