如何用Excel宏或公式获取图表中特定峰值的起止点

我有一个用于查找图表中特定峰值的表单，需要编写宏或公式来获取该图表中特定峰值的起止点。

对应图表 #

已编写的VBA代码 #

Dim wb As Workbook
Set wb = ThisWorkbook

Dim dl_input As Worksheet
Set dl_input = wb.Worksheets("Graph")

lastrow = dl_input.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).Row
lastrow = Int(lastrow / 5)

Dim correlvalues() As Single
ReDim correlvalues(lastrow - 13)

Dim peaks() As Single
ReDim peaks(lastrow - 13)

Dim index() As Single
ReDim index(lastrow - 13)

Dim counting As Integer
counting = 1

Dim maxpeak As Single

'计算信号与不同滞后版本的自相关系数
For counter = 1 To lastrow - 13
    Debug.Print "Counter: " & counter & " Last Counter: " & lastrow - 13 & " Range1: " & range1 & " Range2: " & range2
    
    range1 = "D" & (11 + counter) & ":" & "D" & lastrow
    range2 = "D11:D" & (lastrow - counter)
    
    correlvalues(counter) = Application.WorksheetFunction.Correl(Range(range1), Range(range2))
Next counter

'自相关系数数组反映了滞后信号与原信号的相似度
'数值越高相似度越高，说明对应频率下模式重复出现

'提取自相关结果中的峰值
For counter = 2 To lastrow - 14
    If correlvalues(counter) > correlvalues(counter - 1) And correlvalues(counter) > correlvalues(counter + 1) And correlvalues(counter) > 0 Then
        peaks(counting) = correlvalues(counter)
        index(counting) = counter
        counting = counting + 1
    End If
Next counter

'最高峰值对应的滞后时长是与原信号最相似的周期，即下一个周期的起始点
'减去12是因为：得到的数值是下一个周期起始点，减1得到周期时长，再减11是因为数据从第11行开始

maxpeak = WorksheetFunction.Max(peaks)
cycleduration = WorksheetFunction.Match(maxpeak, correlvalues, 0) - 12

dl_input.Range("G1").Value = cycleduration

'根据用户输入的周期数，计算目标数据的起止行
dl_start = 10 + (cycleduration * (TXT_COUNT.Text - 1))
dl_end = dl_start + cycleduration

dl_input.Activate
dl_input.Range("D" & dl_start & ":" & "D" & dl_end).Copy

补充说明 #

相关宏为xlsm格式文件，可直接运行测试。

内容的提问来源于stack exchange，提问作者StoledInk