Excel - 找到具有多细胞峰的局部最大值

Excel - 找到具有多细胞峰的局部最大值

我想用 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

例子

相关内容