Descarga el libro de trabajo de ejemplo
Este tutorial le enseñará cómo buscar el último valor en una columna o fila en Excel.
Último valor en columna
Puede utilizar la función BUSCAR para encontrar la última celda no vacía de una columna.
| 1 | = BUSCAR (2,1 / (B: B ""), B: B) |
Repasemos esta fórmula.
La parte de la fórmula B: B ”” devuelve una matriz que contiene valores verdaderos y falsos: {FALSO, VERDADERO, VERDADERO,…}, probando que cada celda de la columna B esté en blanco (FALSO).
| 1 | = BUSCAR (2,1 / ({FALSO; VERDADERO; VERDADERO; VERDADERO; VERDADERO; VERDADERO; FALSO;…), B: B) |
Estos valores booleanos se convierten en 0 o 1 y se utilizan para dividir 1.
| 1 | = BUSCAR (2, {# DIV / 0!; 1; 1; 1; 1; 1; # DIV / 0!;, B: B) |
Este es el lookup_vector para la función LOOKUP. En nuestro caso, lookup_value es 2, pero el valor más grande en lookup_vector es 1, por lo que la función LOOKUP coincidirá con el último 1 en la matriz y devolverá el valor correspondiente en result_vector.
Si está seguro de que solo tiene valores numéricos en su columna, sus datos comienzan en la fila 1 y su rango de datos es continuo, puede usar una fórmula un poco más simple con las funciones INDICE y COUNT.
| 1 | = ÍNDICE (B: B, CUENTA (B: B)) |
La función COUNT devuelve el número de celdas llenas de datos en el rango continuo (4) y la función INDICE da el valor de la celda en esta fila correspondiente (4ª).
Para evitar posibles errores cuando su rango de datos contiene una mezcla de valores numéricos y no numéricos, o incluso algunas celdas en blanco, puede usar la función LOOKUP junto con las funciones ISBLANK y NOT.
| 1 | = BUSCAR (2,1 / (NO (ESBLANCO (B: B))), B: B) |
La función ISBLANK devuelve una matriz que contiene valores verdaderos y falsos, correspondientes a unos y ceros. La función NO cambia Verdadero (es decir, 1) a Falso y Falso (es decir, 0) a Verdadero. Si invertimos esta matriz resultante (al dividir 1 por esta matriz), obtenemos una matriz de resultados que contiene nuevamente # DIV / 0! errores y unos, que se pueden usar como matriz de búsqueda (lookup_vector) en nuestra función BÚSQUEDA. La funcionalidad de la función LOOKUP es la misma que en nuestro primer ejemplo: devuelve el valor del vector de resultado en la posición del último 1 en la matriz de búsqueda.
Cuando necesite que se devuelva el número de fila con la última entrada, puede modificar la fórmula utilizada en nuestro primer ejemplo junto con la función FILA en su result_vector.
| 1 | = BUSCAR (2,1 / (B: B ""), FILA (B: B)) |
Último valor de la fila
Para obtener el valor de la última celda no vacía en una fila llena de datos numéricos, es posible que desee utilizar un enfoque similar pero con funciones diferentes: la función OFFSET junto con las funciones MATCH y MAX.
| 1 | = DESPLAZAMIENTO (Referencia, Filas, Columnas) |
| 1 | = DESPLAZAMIENTO (B2,0, COINCIDIR (MAX (B2: XFD2) + 1, B2: XFD2,1) -1) |
Veamos cómo funciona esta fórmula.
Función MATCH
Usamos la función COINCIDIR para "contar" cuántos valores de celda están por debajo de 1 + el máximo de todos los valores en la fila2 a partir de B2.
| 1 | = COINCIDIR (lookup_value, lookup_array, [match_type]) |
| 1 | = COINCIDIR (MAX (B2: XFD2) + 1, B2: XFD2,1) |
El valor de búsqueda de la función COINCIDIR es el máximo de todos los valores en la fila2 + 1. Dado que este valor obviamente no existe en la fila2 y el tipo de coincidencia se establece en 1 (menor o igual que el valor de búsqueda), la función COINCIDIR devolverá el la posición de la última celda "marcada" en la matriz, es decir, el número de celdas llenas de datos en el rango de B2: XFD2 (XFD es la última columna en las versiones más recientes de Excel).
Función OFFSET
Luego usamos la función OFFSET para obtener el valor de esta celda, cuya posición fue devuelta por la función MATCH.
| 1 | = DESPLAZAMIENTO (B2,0, C4-1) |
