vba 如何仅提取和绘制数组的最小和最大峰值，-图形分析-使用 Matlab 或 excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8994141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract and plot only minimal and maximal peaks of an array , -graph analysis- With Matlab or excel
提问by Zalaboza
I'm doing video analysis.
The end result array I get is something like:
我在做视频分析。
我得到的最终结果数组类似于：
signal =
Columns 1 through 7
73960 73960 73960 73960 68102 68102 68102
Columns 8 through 14
68102 19187 19187 19187 19187 14664 14664
Columns 15 through 21
14664 14664 13715 13715 13715 13715 30832
Columns 22 through 28
30832 30832 30832 53031 53031 53031 53031
Columns 29 through 35
56897 56897 56897 16104 16104 16104 16104
Columns 36 through 42
15188 15188 15188 15188 13973 13973 13973
Note: actual array I get is usually 600+
注意：我得到的实际数组通常是 600+
So when I plot this I get very bad looking graph, so I want to filter this array and only keep the ** maximal and minimal peaks** local maxima and minima so that the graph would have nicer waves
所以当我绘制这个图时，我会得到非常糟糕的图，所以我想过滤这个数组，只保留 ** 最大和最小峰值** 局部最大值和最小值，这样图就会有更好的波浪
Is there a way I can do it with MATLAB?
有没有办法用 MATLAB 做到这一点？
if not then can I do it with excel? as I usually save this array to excel sheet Like this
如果没有那么我可以用excel做吗？因为我通常将此数组保存到 excel 表中 像这样
0.1 68102
0.15 19187
0.2 14664
0.25 13715
0.3 30832
0.35 53031
0.4 56897
0.45 16104
0.5 15188
0.55 13973
0.6 21437
0.65 66950
0.7 65356
0.75 22562
0.8 14154
0.85 13938
0.9 20692
0.95 72823
1 69975
1.05 15328
1.1 14494
1.15 13681
1.2 14205
1.25 65278
1.3 63055
1.35 16999
1.4 14050
1.45 14245
In which the 1st column is the time(y-axis) and 2nd column is the amplitude.(x-axis)
其中第一列是时间（y 轴），第二列是振幅。（x 轴）
I use this formula to count local maxima (Thanks to brettdjfrom stackoverflow.com)
我使用这个公式来计算局部最大值（感谢来自 stackoverflow.com 的brettdj）
=SUMPRODUCT(--(B2:B149>B1:B148),--(B2:B149>B3:B150))
And this formula to count local minima
而这个计算局部最小值的公式
=SUMPRODUCT(--(B2:B149<B1:B148),--(B2:B149<B3:B150))
But what I need is to filter the array to only keep local maxima and local minima so I can get nicely drawn curve without noise.
但我需要的是过滤数组以只保留局部最大值和局部最小值，这样我就可以在没有噪音的情况下绘制出很好的曲线。
采纳答案by Werner
If you want the maximum and minimum values just use:
如果您想要最大值和最小值，只需使用：
[sig_min, idx_min] = min(signal);
[sig_max, idx_max] = max(signal);
But I couldnt understand exactly what you want… since my account is new, I cant comment on your question to try to understand it better.
但是我无法完全理解您想要什么……由于我的帐户是新帐户，因此我无法对您的问题发表评论以尝试更好地理解它。
— edit 1:
— 编辑 1：
Ok, now I understand what you want. I don't know why you have this array with repetitive numbers, but supposing you don't want them, or, at least, that is better to remove them to find local maxima and minima, you should do:
好的，现在我明白你想要什么了。我不知道为什么你有这个带有重复数字的数组，但假设你不想要它们，或者，至少，最好删除它们以找到局部最大值和最小值，你应该这样做：
sinal_norep = signal(find(diff(sinal)));
where signal_norep will be your new array containing only values that differs from the last one:
其中 signal_norep 将是您的新数组，仅包含与上一个不同的值：
Now we can search for the index where occurs local maxima and minima on this array, by doing:
现在我们可以通过执行以下操作来搜索该数组上出现局部最大值和最小值的索引：
minimas_idx = find(signal_norep(2:end-1)<signal_norep(1:end-2) & signal_norep(2:end-1)<signal_norep(3:end))+1;
maximas_idx = find(signal_norep(2:end-1)>signal_norep(1:end-2) & signal_norep(2:end-1)>signal_norep(3:end))+1;
And their values:
以及他们的价值观：
signal_maximas = signal_norep(maximas_idx);
signal_minimas = signal_norep(minimas_idx);
Thats it x)
就是这样 x)
回答by brettdj
This VBA
这个 VBA
- Reads the data from column A and B into a variant arrays
- Find the local minima and maxima and extracts that to second array
- Creates a brand new chart of the minima/maximia (see image)
- 将 A 列和 B 列的数据读入变体数组
- 找到局部最小值和最大值并将其提取到第二个数组
- 创建一个全新的最小值/最大值图表（见图）
Sub NewGraph()
Dim X
Dim Y
Dim lngRow As Long
Dim lngCnt As Long
Dim Chr As ChartObject
X = Range([a1], Cells(Rows.Count, "b").End(xlUp))
Y = Application.Transpose(X)
For lngRow = 2 To UBound(X, 1) - 1
If X(lngRow, 2) > X(lngRow - 1, 2) Then
If X(lngRow, 2) > X(lngRow + 1, 2) Then
lngCnt = lngCnt + 1
Y(1, lngCnt) = X(lngRow, 1)
Y(2, lngCnt) = X(lngRow, 2)
End If
Else
If X(lngRow, 2) < X(lngRow + 1, 2) Then
lngCnt = lngCnt + 1
Y(1, lngCnt) = X(lngRow, 1)
Y(2, lngCnt) = X(lngRow, 2)
End If
End If
Next lngRow
ReDim Preserve Y(1 To 2, 1 To lngCnt)
Set Chr = ActiveSheet.ChartObjects.Add(250, 175, 275, 200)
With Chr.Chart
With .SeriesCollection.NewSeries
.XValues = Application.Index(Application.Transpose(Y), 0, 1)
.Values = Application.Index(Application.Transpose(Y), 0, 2)
End With
.ChartType = xlXYScatter
End With
End Sub
回答by Bernhard
I would just write a simple loop over the array in Matlab to achieve this. I don't see any fundamental problems there?
我只想在 Matlab 中的数组上编写一个简单的循环来实现这一点。我看不出那里有任何根本问题？
If you don't want loops in Matlab, you can do it with some array operations. If you have two equallly long arrays a and b, you can do something like c = a>b, which gives you a list with ones and zeros. You1 can use this as a oneliner for selecting the maxima/minima.
Zo suppose you have an upshifted and downshifted array b, c. Such that (except at endpoints)
b(n-1)=a(n)=c(n+1). You can get an array containing only the extrema and zeros by q=a.*( (a>b).*(a>c) + (a<b).*(a<c) )
如果你不想在 Matlab 中循环，你可以通过一些数组操作来实现。如果您有两个等长数组 a 和 b，您可以执行类似 c = a>b 的操作，它会为您提供一个包含 1 和 0 的列表。You1 可以将其用作选择最大值/最小值的单行。Zo 假设您有一个升档和降档的数组 b, c。这样（除端点外）b(n-1)=a(n)=c(n+1)。您可以通过以下方式获得仅包含极值和零的数组q=a.*( (a>b).*(a>c) + (a<b).*(a<c) )
One advise: If you signal has noise, than also this selection will be noisy. To smoothen the function, you should apply some kind of moving averaging with a kernel you like.
一个建议：如果你的信号有噪音，那么这个选择也会有噪音。为了平滑函数，您应该使用您喜欢的内核应用某种移动平均。
