Excel - найти локальные максимумы с многоклеточным пиком

Question

Я хочу найти локальные максимумы в огромном наборе данных с помощью Excel, и я пытался сравнить предыдущее и следующее значения, чтобы убедиться, что они меньше, например:

=IF(AND(C4>C3,C4>C5),"Local maxima","")

Но проблема с этой формулой заключается в том, что если пик растягивается на несколько рядов, он не будет восприниматься как локальные максимумы. И это несмотря на то, что это локальные максимумы, так как значения начинают падать.

(Если вы хотите узнать, почему это найти локальные максимумы в наборе данных спектра)

Answer 1

Вы на правильном пути! Вы можете сделать это с помощью пары вспомогательных столбцов. Смотрите рисунок ниже.

Первый вспомогательный столбец, "slope", использует функцию SLOPE в Excel. Он рассчитывает наклон между двумя соседними точками.

Второй вспомогательный столбец "для меток" проверяет переход от положительного к отрицательному уклону. Переход от положительного к отрицательному уклону помечен как "максимальный".

Затем вы можете пометить график этими максимумами, используя макрос:

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

Answer 2

Другое решение, которое я хотел бы добавить к этому вопросу, заключается в следующем. Это может пригодиться, если набор данных зашумлен или имеет много значений x и y. Создайте таблицу с "Форматом таблицы" и выполните следующие действия:

Столбец D (имя в строке 1) как X

Столбец E (имя в строке 1) как Y

Новый столбец F (имя в строке 1) в качестве наклона и мгновенного заполнения столбца.

=slope(B2:B3,A2:A3)

Новый столбец G (имя "решатель" в строке 1), начало в строке 7

=IF(AND(F214>0,E214=MAX(E209:E219),E214>$I$1),D214,"")

Ячейка I1: пороговое значение

Он ищет локальный максимум Y (5 рядов вверх и вниз) с положительным наклоном и значением Y выше определенного порога. В случае успеха он возвращает X-значение.

Пример с выбранной ячейкой, возвращающей X (D214) 1.0004397