返回列表 上一主題 發帖

[發問] 數列曲線的谷底數值及數列的位置 VBA

[發問] 數列曲線的谷底數值及數列的位置 VBA

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

數列曲線

數列曲線.JPG
2020-7-20 16:32

   
    '以下數列的來源是由其他程式產生,不在工作表的儲存格。
    '此例數列共有 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:39 編輯

回復 1# Scott090

     請高手大大們協助,如何在陣列中搜尋各谷底位置及數字,謝謝
   
數列曲線.JPG
2020-7-28 08:33

TOP

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

回復 2# Scott090


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

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

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

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

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

就是極值所在的位置

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

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

這是演算法的範疇,在這裡應該不會有人幫你寫....
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

回復 3# n7822123


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

TOP

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

回復 4# quickfixer


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

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

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

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

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


谷底值與位.png
2020-7-29 13:47


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

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

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

程式如下



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
程式是依需求寫的,需求表達不清楚
或者沒有上傳附件,愛莫能助

TOP

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

回復 5# n7822123


    謝謝你提供範例教學
我改用函數重現過程XD
測試後,原來我的條件是<0、 >0 ,我改成>=0,就和你的結果一樣了
Image 41.png
2020-7-30 00:43


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

Image 39.png
2020-7-30 00:37


Image 40.png
2020-7-30 00:37

TOP

回復 5# n7822123
回復 6# quickfixer

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

   再三感恩

TOP

        靜思自在 : 有願放在心裡,沒有身體力行,正如耕田不播種,皆是空過因緣。
返回列表 上一主題