我想用 Excel 在庞大的数据集中找到局部最大值,我一直尝试的方法是比较前一个值和下一个值以确保它们较小,例如:
=IF(AND(C4>C3,C4>C5),"Local maxima","")
但这个公式的问题在于,如果峰值跨越多行,它不会将其作为局部最大值捕获。尽管当值开始下降时,它确实是局部最大值。
(如果你想知道为什么要在一组光谱数据中找到局部最大值)
答案1
您走对了路!您可以使用几个辅助列来实现这一点。请参见下图。
第一个辅助列“斜率”使用 Excel 的 SLOPE 函数。它计算两个相邻点之间的斜率。
第二个辅助列“用于标签”检查从正斜率到负斜率的过渡。从正斜率到负斜率的过渡标记为“最大值”。
然后,您可以使用如下宏用这些最大值来标记图形:
Sub CustomLabels()
Dim i, myCount, pt
ActiveSheet.ChartObjects("myChart").Activate
myCount = ActiveChart.SeriesCollection(1).Points.Count
For i = 1 To myCount
ActiveChart.SeriesCollection(1).Points(i).ApplyDataLabels
ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = Range("D" & i + 1).Value
Next i
End Sub
答案2
我想为这个问题添加的另一个解决方案如下。如果数据集很嘈杂或有很多 x 和 y 值,它可能会变得方便。使用“格式化为表格”创建表格并执行以下操作:
D 列(第 1 行中的名称)作为 X
E 列(第 1 行中的名称)作为 Y
新建 F 列(第 1 行中的名称)作为斜率并快速填充该列。
=slope(B2:B3,A2:A3)
新列 G(第 1 行中名为“solver”),从第 7 行开始
=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")
单元格 I1:阈值
它寻找 Y 的局部最大值(上下 5 行),斜率为正,且 Y 值高于某个阈值。如果成功,则返回 X 值。
例如,选定的单元格返回 X (D214) 1.0004397