Роджер спросил, есть ли способ вычислить интервал между появлением значений в списке. Например, у него есть несколько тысяч чисел в столбце 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, просматривает указанный вами диапазон назад и возвращает желаемый интервал:

Function RowInterval(TestCell As Range, LookHere As Range) As Long     Dim varValue As Variant     Dim lngRow As Long

Application.Volatile     varValue = TestCell.Value          'Check for occurrences of the test value in the search range     If WorksheetFunction.CountIf(LookHere, varValue) > 0 Then         With LookHere             'Get the last row of the search range             lngRow = .Row + .Rows.Count - 1             'Start with the last cell in the search range and work up             Do Until .Item(lngRow, 1).Value = varValue                 lngRow = lngRow - 1             Loop         End With         'Subtract the number of the row containing the found occurrence         'from the number of the row containing the test value         RowInterval = TestCell.Row - lngRow     End If End Function

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

=RowInterval(A2,A$1:A1)

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (2338) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Calculating_the_Interval_between_Occurrences [Расчет интервала между вхождениями].