Расчет интервала между вхождениями

Роджер спросил, есть ли способ вычислить интервал между появлением значений в списке. Например, у него несколько тысяч чисел в столбце A. Если посмотреть на значение в ячейке A351, то последний раз, когда это значение появилось в списке, было в ячейке A246. Ему нужна формула, которую можно было бы поместить в ячейку B351 и вернуть 105, разницу между 351 и 246.

Этот подход сложно реализовать в Excel, потому что Excel не очень хорош для поиска в обратном направлении – столбец вверх. Если эту предпосылку можно перевернуть, задача станет намного проще. Например, если формула в ячейке B246 может возвращать значение 105, указывающее интервал до следующего появления значения в ячейке A246, вместо вычисления последнего вхождения. Следующая формула вычисляет следующее вхождение значения в ячейку A1:

= MATCH (A1, A2: $ A $ 65536,0)

Поместите эту формулу в ячейку B1 и скопируйте его, сколько бы ячеек ни потребовалось. Если значение в столбце A больше не встречается в столбце, формула возвращает ошибку # N/A. Если вы предпочитаете, чтобы формула возвращала 0, то работает следующее:

= IF (ISNA (MATCH (A1, A2: $ A $ 65536,0)), 0, MATCH (A1, A2 : $ A $ 65536,0))

Если вам абсолютно необходимо считать в сторону увеличения (найти предыдущее вхождение вместо следующего), то проще всего сделать это с помощью пользовательской функции. Следующая функция, RowInterval, просматривает указанный вами диапазон и возвращает желаемый интервал:

Функция RowInterval (TestCell As Range, LookHere As Range) As Long Dim varValue As Variant Dim lngRow As Long Application.Volatile varValue = TestCell.Value ‘Проверить наличие вхождений тестового значения в диапазоне поиска Если WorksheetFunction.CountIf (LookHere, varValue)> 0 Then With LookHere’ Получить последнюю строку диапазона поиска lngRow = .Row + .Rows .Count — 1 ‘Начните с последней ячейки в диапазоне поиска и продолжайте работать до .Item (lngRow, 1) .Value = varValue lngRow = lngRow — 1 Loop End With’ Вычтите номер строки, содержащей найденное вхождение ‘ от номера строки, содержащей тестовое значение RowInterval = TestCell.Row — lngRow End IfEnd Function

Чтобы использовать функцию, поместите следующую формулу в ячейку B2, а затем скопируйте формула вниз по количеству желаемых ячеек:

= RowInterval (A2, A $ 1: A1)

Источник: frestage.ru

Бытовой вопрос