EXCEL: FILTRAR los primeros o últimos n valores

Para FILTRAR y extraer los primeros o últimos n valores (es decir, los primeros 3 valores, los primeros 5 valores, etc.), puede usar la función FILTRAR junto con INDICE y SECUENCIA. En el ejemplo que se muestra, la fórmula en D5 es:

=INDICE(FILTRAR(data,data<>""),SECUENCIA(3,1,1,1))

donde los datos son el rango nombrado B5: B15.

Cómo funciona esta fórmula

Trabajando de adentro hacia afuera, usamos la función SECUENCIA para construir un valor de número de fila para INDICE como este:

SECUENCIA(3,1,1,1)

Estamos pidiendo a SECUENCIA una matriz de 3 filas x 1 columna, comenzando en 1, con un valor de paso de 1. El resultado es una matriz como esta:

{1;2;3}

que se devuelve directamente a la función INDICE como el argumento row_num:

=INDICE(FILTRAR(data,data<>""),{1;2;3})

Para construir la matriz para INDICE, utilizamos la función FILTRAR para recuperar una lista de entradas no en blanco de los datos de rango con nombre (B5: B15) de esta manera:

FILTRAR(data,data<>"")

El argumento de matriz son datos, y el argumento de inclusión es la expresión de datos <> "". Esto se puede traducir literalmente como "valores de retorno de los datos de rango con nombre donde los valores en los datos no están vacíos". El resultado es una matriz con 9 valores como este:

{"Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis"}

Se han eliminado los valores de aviso asociados con las dos celdas vacías. Esta matriz se devuelve a la función INDICE como su argumento de matriz.
Finalmente, INDICE devuelve los valores primero, segundo y tercero de la matriz devuelta por FILTRAR:

{"Atlanta";"Chicago";"Dallas"}

Últimos n valores

Para obtener los últimos n valores con FILTRAR, utilice la misma estructura de fórmula, con las entradas a SECUENCIA modificadas para construir una "última n" matriz de números de fila. Por ejemplo, para obtener los últimos 3 valores no en blanco en el ejemplo que se muestra, puede usar una fórmula como esta:

=INDICE(FILTRAR(data,data<>""),ORDENAR(SECUENCIA(3,1,SUMA(--(data<>"")),-1)))

El truco principal aquí es contar las entradas no en blanco en los datos de rango con nombre de esta manera:

SUMA(--(data<>""))

Usamos un doble negativo para forzar los valores de VERDADERO FALSO a 1s y 0s, luego usamos la función SUMA para obtener el conteo. El resultado se devuelve como argumento de inicio dentro de SECUENCIA. Suministramos -1 para el paso a paso hacia atrás desde el inicio.
También ajustamos la función ORDENAR alrededor de SECUENCIA para que la matriz devuelta sea {7; 8; 9} y no {9; 8; 7}. Esto garantiza que los valores se devuelvan en el mismo orden en que aparecen en los datos de origen.