麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 數列曲線的谷底數值及數列的位置 VBA [打印本頁]

作者: Scott090 時間: 2020-7-20 17:40 標題: 數列曲線的谷底數值及數列的位置 VBA

請問：用VBA 求取數列的每一個谷底數值及其位置

[attach]32306[/attach]

'以下數列的來源是由其他程式產生，不在工作表的儲存格。
'此例數列共有 374 個數字，實際上數字列的長度不一定。
'附圖曲線是此例數字列的表示，用以方便說明。
'
Sub FindValley()
   Dim aa$, valley(2, 10) '谷的位置&谷的數值
   Dim ar, arr
   Dim i%
      '數字列
   aa = "451.8,452.5,453.8,454.4,454.7,455.1,455.6,455.9,457.2,459.1,460.8,463.3,466.7,469.5,471.9,474.2,476.3,"
   aa = aa & "478.4,480.1,481.5,482.4,483.3,483.4,484.3,484.9,485.6,485.7,485.8,485.7,485.3,485.3,485.7,485.8,485.9,486.3,"
   aa = aa & "486.4,486.7,487.3,488.2,488.6,489.1,489.6,490.1,490.4,489.5,488.6,487.2,485.7,485.6,483.9,483.2,482.1,481.2,"
   aa = aa & "479.5,478,477.2,474.5,472.1,467.2,463.3,460.5,458.5,455.1,440.2,430.5,420.3,415.2,400.2,375.1,350.5,336,"
   aa = aa & "328.5,320,308.5,306,305,303.5,302.5,301.2,300.5,301.3,302.1,303.5,306.3,307.6,309,315.5,318.7,320.1,328.7,"
   aa = aa & "335.6,345.5,350.1,352.5,361.3,364.2,365.8,367.4,368.2,370.5,375.3,380.2,384.7,386.4,390.2,395.4,400.8,"
   aa = aa & "408.7,415.2,425.6,440.5,445.8,450.5,457.3,459.1,460.5,462.1,462.3,463.5,465.3,465.5,466,467,467,467.1,467.6,467.8,"
   aa = aa & "467.6,467.7,467.8,467.9,467.8,467.4,466.9,466.1,465.7,465.1,464.4,463.9,463.3,462.6,461.6,460.7,460.2,"
   aa = aa & "459.4,458.3,457,455.9,454.5,453.2,450.5,447.9,445.2,442.6,440.4,438.3,435.6,432.8,430,427.4,424.6,421.4,"
   aa = aa & "417.9,414.5,410.8,407.4,404,401.4,398.8,396.2,393.3,390,386.9,383.9,380.8,"

   aa = aa & "377.5,374.3,371,367.7,364.6,362.1,360.3,358.7,357.1,355.4,353.4,351.3,349.6,347.9,346.2,344.4,342.7,341.2,339.7,"
   aa = aa & "338.4,336.8,335.5,334,332.3,330.3,328.4,326.6,324.9,323.2,321.7,320.4,319.2,317.7,316.5,315.2,315.1,"
   aa = aa & "315.2,315,315,314.9,314.7,314.5,314.3,314.3,314.4,314.7,315.1,315.4,315.7,315.9,316.1,316.2,316.3,316.8,"
   aa = aa & "317.3,317.9,318.5,319.2,319.9,320.4,320.9,321.1,320.8,320.2,319.7,319.1,318.6,318.2,317.8,317.4,316.8,316,"
   aa = aa & "315.3,314.8,314.4,314,313.6,313.1,312.3,311.6,311,310.3,309.7,309,308.2,307.1,305.9,304.2,302.4,300.5,298.7,"
   aa = aa & "296.8,294.8,292.8,290.7,289,287.3,286,284.9,283.7,282.4,281.2,280,278.6,277.2,275.8,274.3,272.6,271.1,269.7,"
   aa = aa & "268.1,266.6,265.1,263.3,261.6,259.7,257.4,254.9,252.5,250.1,247.7,245.2,243,240.8,238.7,236.6,234.6,232.7,230.9,"
   aa = aa & "229.1,227.2,225.4,223.7,222.1,220.4,218.8,217.3,216.1,215.2,214.4,213.6,212.9,212,211.4,210.8,210.3,209.9,210,"
   aa = aa & "210.3,210.6,211.2,211.8,212.7,213.3,213.9,214.5,214.6,214.3,214.4,214.5,214.6,214.7,215,215.5,215.8,216.3,216.9,"
   aa = aa & "217.3,217.8,218.6,219.3,220.5,221.8,223,224.1,225.4,"
   aa = aa & "226.9,228.4,229.7,231.2,233.1,234.9,236.9,238.8,240.7,242.4,244,245.5,247.1,248.8,250.6,252.3,254.1,255.6,"
   aa = aa & "257.2,258.7,259.7,260.6,261.4,262"

'轉成陣列
   ar = Split(aa, ",")
   ReDim arr(UBound(ar))
   For i = 0 To UBound(ar) - 1
      arr(i + 1) = CSng(ar(i))
   Next

   '第1低谷的數字
   valley(1, 1) = Application.Small(arr, 1)

'如何寫VBA程序求取以下的數值：
   '第1低谷的位置 valley(2,1)?

   '第2低谷的數字 valley(1,2)?
   '第2低谷的位置 valley(2,2)?
   '第3低谷的數字 valley(1,3)?
   '第3低谷的位置 valley(2,3)?

   '第n低谷的數字 valley(1,n)?
   '第n低谷的位置 valley(2,n)?

End Sub

作者: Scott090 時間: 2020-7-28 08:34

本帖最後由 Scott090 於 2020-7-28 08:39 編輯

回復 1# Scott090

請高手大大們協助，如何在陣列中搜尋各谷底位置及數字，謝謝
[attach]32337[/attach]

作者: n7822123 時間: 2020-7-29 12:14

本帖最後由 n7822123 於 2020-7-29 12:17 編輯

回復 2# Scott090

我提供一個方法，你把原陣列的值，

用後一個值減去前一個值，做成新陣列

舉例來說新陣列=-8,-5,-3,-1,3,7,9

尋找"變號"的數值位置，以上面來說從-1 變成 3

那麼新陣列值 -1的位置，所對應的原陣列位置，

就是極值所在的位置

由負變正，最後一個負值就是谷底，

由正變負，最後一個正值就是峰值。

這是演算法的範疇，在這裡應該不會有人幫你寫....

作者: quickfixer 時間: 2020-7-29 13:40

回復 3# n7822123

我用這個方法算什來的,和他給的答案不一樣
位置：數值
80 300.5
128 467.6
211 315.1
322 209.9
333 214.3
要嗎他算錯,
不然就是普通的算法不適用在股市的波浪理論?
可能還需要加上很多條件,或是用完全不同的演算法來求高低值

作者: n7822123 時間: 2020-7-29 13:49

本帖最後由 n7822123 於 2020-7-29 13:55 編輯

回復 4# quickfixer

你可能算錯了，我剛剛一不小心就寫完了..... 誰叫我熱愛演算法 >.<

下圖看看是不是你要的，也跟你的圖示不一樣，

圖示第2低點是我第3低點~~圖示第3低點是我第4低點.....我的第2低點不見了!!

也許那個網頁有慮掉連續正負數值轉換的演算法

避免鋒頂與谷底太過接近~~

[attach]32341[/attach]

有點煩雜，感覺可以簡化，不過我懶的想了~~

有時候簡化太多，反而變成不直覺，不好閱讀

有自己做一個排序陣列的副函數，以你的程度應該看的懂~

程式如下

Sub FindValley()
   Dim aa$, valley()
   Dim ar, Arr
   Dim i%

'數字列
   aa = "451.8,452.5,453.8,454.4,454.7,455.1,455.6,455.9,457.2,459.1,460.8,463.3,466.7,469.5,471.9,474.2,476.3,"
   aa = aa & "478.4,480.1,481.5,482.4,483.3,483.4,484.3,484.9,485.6,485.7,485.8,485.7,485.3,485.3,485.7,485.8,485.9,486.3,"
   aa = aa & "486.4,486.7,487.3,488.2,488.6,489.1,489.6,490.1,490.4,489.5,488.6,487.2,485.7,485.6,483.9,483.2,482.1,481.2,"
   aa = aa & "479.5,478,477.2,474.5,472.1,467.2,463.3,460.5,458.5,455.1,440.2,430.5,420.3,415.2,400.2,375.1,350.5,336,"
   aa = aa & "328.5,320,308.5,306,305,303.5,302.5,301.2,300.5,301.3,302.1,303.5,306.3,307.6,309,315.5,318.7,320.1,328.7,"
   aa = aa & "335.6,345.5,350.1,352.5,361.3,364.2,365.8,367.4,368.2,370.5,375.3,380.2,384.7,386.4,390.2,395.4,400.8,"
   aa = aa & "408.7,415.2,425.6,440.5,445.8,450.5,457.3,459.1,460.5,462.1,462.3,463.5,465.3,465.5,466,467,467,467.1,467.6,467.8,"
   aa = aa & "467.6,467.7,467.8,467.9,467.8,467.4,466.9,466.1,465.7,465.1,464.4,463.9,463.3,462.6,461.6,460.7,460.2,"
   aa = aa & "459.4,458.3,457,455.9,454.5,453.2,450.5,447.9,445.2,442.6,440.4,438.3,435.6,432.8,430,427.4,424.6,421.4,"
   aa = aa & "417.9,414.5,410.8,407.4,404,401.4,398.8,396.2,393.3,390,386.9,383.9,380.8,"
   aa = aa & "377.5,374.3,371,367.7,364.6,362.1,360.3,358.7,357.1,355.4,353.4,351.3,349.6,347.9,346.2,344.4,342.7,341.2,339.7,"
   aa = aa & "338.4,336.8,335.5,334,332.3,330.3,328.4,326.6,324.9,323.2,321.7,320.4,319.2,317.7,316.5,315.2,315.1,"
   aa = aa & "315.2,315,315,314.9,314.7,314.5,314.3,314.3,314.4,314.7,315.1,315.4,315.7,315.9,316.1,316.2,316.3,316.8,"
   aa = aa & "317.3,317.9,318.5,319.2,319.9,320.4,320.9,321.1,320.8,320.2,319.7,319.1,318.6,318.2,317.8,317.4,316.8,316,"
   aa = aa & "315.3,314.8,314.4,314,313.6,313.1,312.3,311.6,311,310.3,309.7,309,308.2,307.1,305.9,304.2,302.4,300.5,298.7,"
   aa = aa & "296.8,294.8,292.8,290.7,289,287.3,286,284.9,283.7,282.4,281.2,280,278.6,277.2,275.8,274.3,272.6,271.1,269.7,"
   aa = aa & "268.1,266.6,265.1,263.3,261.6,259.7,257.4,254.9,252.5,250.1,247.7,245.2,243,240.8,238.7,236.6,234.6,232.7,230.9,"
   aa = aa & "229.1,227.2,225.4,223.7,222.1,220.4,218.8,217.3,216.1,215.2,214.4,213.6,212.9,212,211.4,210.8,210.3,209.9,210,"
   aa = aa & "210.3,210.6,211.2,211.8,212.7,213.3,213.9,214.5,214.6,214.3,214.4,214.5,214.6,214.7,215,215.5,215.8,216.3,216.9,"
   aa = aa & "217.3,217.8,218.6,219.3,220.5,221.8,223,224.1,225.4,"
   aa = aa & "226.9,228.4,229.7,231.2,233.1,234.9,236.9,238.8,240.7,242.4,244,245.5,247.1,248.8,250.6,252.3,254.1,255.6,"
   aa = aa & "257.2,258.7,259.7,260.6,261.4,262"

'轉成陣列
   ar = Split(aa, ",")
   ReDim Arr(UBound(ar))
   For i = 0 To UBound(ar) - 1
      Arr(i + 1) = CSng(ar(i))
   Next

   '後一值減前一值陣列-Brr
   ReDim Brr(UBound(Arr))
   For i = 2 To UBound(Arr)
      Brr(i) = Arr(i) - Arr(i - 1)
   Next

   '找極值位置
   For i = 2 To UBound(Brr) - 1
      If Brr(i) >= 0 And Brr(i + 1) < 0 Then 峰位 = 峰位 & "," & i
      If Brr(i) < 0 And Brr(i + 1) >= 0 Then 谷位 = 谷位 & "," & i
   Next

'轉陣列
峰位Arr = Split(Mid(峰位, 2), ",")
谷位Arr = Split(Mid(谷位, 2), ",")

'用位置陣列得數值陣列
ReDim 峰值Arr(UBound(峰位Arr))
For i = 0 To UBound(峰位Arr)
      峰值Arr(i) = Arr(峰位Arr(i))
Next
ReDim 谷值Arr(UBound(谷位Arr))
For i = 0 To UBound(谷位Arr)
      谷值Arr(i) = Arr(谷位Arr(i))
Next

'依鋒值Arr的值把鋒位Arr做排序 (大>小)
峰位Arr = ArrSortBrr(峰值Arr, True, 峰位Arr)
'依谷值Arr的值把谷位Arr做排序 (小>大)
谷位Arr = ArrSortBrr(谷值Arr, False, 谷位Arr)

ReDim valley(2, UBound(谷位Arr) + 1)  '谷的位置&谷的數值

For i = 0 To UBound(谷位Arr)
   valley(2, i + 1) = 谷位Arr(i)          '位置
   valley(1, i + 1) = Arr(谷位Arr(i)) '數值
Next

For i = 1 To UBound(valley, 2)
      Debug.Print "第" & i & "低谷數值=" & valley(1, i) & " ; 第" & i & "低谷位置=" & valley(2, i)
Next

End Sub

Function ArrSortBrr(ByVal Arr, Order, ByVal Brr)
'用Arr陣列的資料順序來排序Brr
For i = LBound(Arr) To UBound(Arr) - 1
  For j = i + 1 To UBound(Arr)
If Order Then '大 > 小
   If Arr(j) > Arr(i) Then
      T = Arr(i): Arr(i) = Arr(j): Arr(j) = T
      T = Brr(i): Brr(i) = Brr(j): Brr(j) = T
   End If
Else                   ' 小 > 大
   If Arr(j) < Arr(i) Then
      T = Arr(i): Arr(i) = Arr(j): Arr(j) = T
      T = Brr(i): Brr(i) = Brr(j): Brr(j) = T
   End If
End If
  Next j
Next i
ArrSortBrr = Brr
End Function

作者: quickfixer 時間: 2020-7-30 00:47

本帖最後由 quickfixer 於 2020-7-30 00:48 編輯

回復 5# n7822123

謝謝你提供範例教學
我改用函數重現過程XD
測試後,原來我的條件是<0、 >0 ，我改成>=0,就和你的結果一樣了
[attach]32345[/attach]

少了31、214、219,這3個位置
就像你說的一樣,不是單純只有找高低數值,還有用未知的特定條件去過濾掉(增加)一些數值

[attach]32343[/attach]

[attach]32344[/attach]

作者: Scott090 時間: 2020-7-30 06:12

回復 5# n7822123
回復 6# quickfixer

謝謝2位高手大大的指導
自當深入了解貴演算法與已揭露的程式碼

再三感恩

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)