Roger preguntó si había una forma de calcular el intervalo entre ocurrencias de valores en una lista. Por ejemplo, tiene varios miles de números en la columna A. Si observa el valor en la celda A351, la última vez que ese valor apareció en la lista fue en la celda A246. Le gustaría una fórmula que pudiera colocarse en la celda B351 y devolver 105, la diferencia entre 351 y 246.

Este enfoque es difícil de implementar en Excel porque Excel no es muy bueno para buscar hacia atrás, una columna. Si la premisa se puede revertir, entonces la tarea se vuelve mucho más simple. Por ejemplo, si una fórmula en la celda B246 podría devolver el valor 105, indicando el intervalo hasta la próxima aparición del valor en la celda A246, en lugar de calcular la última aparición. La siguiente fórmula calcula la siguiente aparición del valor en la celda A1:

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

Coloque esta fórmula en la celda B1 y cópiela sin importar cuántas celdas sean necesarias. Si el valor de la columna A no vuelve a aparecer en la columna, la fórmula devuelve el error # N / A. Si prefiere que la fórmula devuelva 0, entonces funciona lo siguiente:

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

Si es absolutamente necesario contar hacia arriba (buscar la ocurrencia anterior en lugar de la siguiente), entonces la forma más fácil de hacerlo es con una función definida por el usuario. La siguiente función, RowInterval, buscará hacia atrás a través de un rango que especifique y devolverá el intervalo deseado:

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

Para usar la función, debe colocar la siguiente fórmula en la celda B2 y luego copiar la fórmula hacia abajo el número de celdas deseadas:

=RowInterval(A2,A$1:A1)

_Nota: _

Si desea saber cómo usar las macros descritas en esta página (o en cualquier otra página de los sitios ExcelTips), he preparado una página especial que incluye información útil.

link: / excelribbon-ExcelTipsMacros [Haga clic aquí para abrir esa página especial en una nueva pestaña del navegador].

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (2338) se aplica a Microsoft Excel 97, 2000, 2002 y 2003. Puede encontrar una versión de este consejo para la interfaz de cinta de Excel (Excel 2007 y posterior) aquí:

link: / excelribbon-Calculating_the_Interval_between_Occurrences [Calculando el intervalo entre ocurrencias].