Para tener a la vista el último valor registrado en una columna, como la última factura o el último saldo, Excel ofrece varias fórmulas. La más conocida combina INDICE y CONTARA, pero falla si hay celdas vacías entre los datos; la más segura usa BUSCAR. Con una macro, además, se puede ir directamente a la primera celda vacía para seguir escribiendo. ¿Cómo funciona cada opción?
Con INDICE y CONTARA.
Supongamos que en la columna C registramos cada día el valor de las facturas, desde la fila 5. La fórmula clásica para traer el último valor es:
=INDICE(C5:C200;CONTARA(C5:C200))CONTARA cuenta las celdas que no están vacías; si hay once valores, devuelve 11, e INDICE trae el undécimo valor del rango. Al agregar una factura, el conteo sube y la fórmula trae la nueva.
El problema es que la fórmula supone que no hay celdas vacías entre los datos. Si una fila quedó en blanco, CONTARA cuenta un dato menos e INDICE trae el penúltimo valor, que es justamente lo que le ocurrió a una lectora. Tampoco funciona bien si el rango empieza en el encabezado o en la fila 1 y hay títulos por encima.
- Liquidador de intereses propiedad horizontal
- Guía de propiedad horizontal
- Liquidador de pensiones en Colpensiones
Con BUSCAR, aunque haya celdas vacías.
Esta fórmula trae el último valor de la columna, sin importar las celdas vacías intermedias:
=BUSCAR(2;1/(C5:C200<>"");C5:C200)La parte 1/(C5:C200<>"") convierte cada celda con datos en 1 y cada celda vacía en un error. BUSCAR busca el número 2, que nunca encuentra, y cuando no encuentra un valor devuelve el último número de la lista, es decir, el que corresponde a la última celda con datos. No hace falta confirmarla con Ctrl + Mayús + Entrar.
Variantes de la fórmula.
Cambiando el último argumento o la condición se obtienen otros datos:
| Necesidad | Fórmula |
|---|---|
| Fecha de la última factura | =BUSCAR(2;1/(C5:C200<>"");A5:A200) |
| Fila del último dato | =BUSCAR(2;1/(C5:C200<>"");FILA(C5:C200)) |
| Última factura de un cliente | =BUSCAR(2;1/(B5:B200=G12);C5:C200) |
| Último número de la columna | =BUSCAR(9,99E+307;C5:C200) |
| Último texto de la columna | =BUSCAR("zzzz";B5:B200) |
La fórmula del último cliente es útil para consultar, por ejemplo, el último pago de un tercero en un auxiliar. Las dos últimas aprovechan el mismo comportamiento de BUSCAR con un valor más grande que cualquier número o texto de la columna.
En Microsoft 365.
En Microsoft 365, FILTRAR deja solo las celdas con datos y TOMAR, con -1, se queda con la última:
=TOMAR(FILTRAR(C5:C200;C5:C200<>"");-1)El resultado es el mismo que con BUSCAR, con una fórmula más fácil de leer.
Ir a la primera celda vacía con una macro.
Cuando lo que se quiere es ubicarse en la primera celda vacía debajo de los datos para seguir registrando, se usa una macro. Esta selecciona la celda siguiente al último dato de la columna A:
Sub IrAPrimeraCeldaVacia()
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
End Sub
La instrucción va a la última fila de la hoja, sube hasta encontrar un dato, igual que Ctrl + flecha arriba, y baja una celda. A diferencia de recorrer la columna desde arriba hasta la primera celda vacía, salta las celdas vacías intermedias y llega siempre al final de los datos.
Si lo que se busca es precisamente el primer hueco, aunque haya datos más abajo, se recorre la columna desde arriba:
Sub IrAPrimerHueco()
Range("A1").Select
Do While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Para la primera opción no se necesita macro: Ctrl + flecha abajo lleva al final de un bloque de datos. Las macros pueden asignarse a un botón, y otros códigos para moverse por la hoja están en el artículo sobre los códigos de macros más comunes.
Descargar el ejemplo en Excel.
El archivo tiene un registro de facturas con una fila vacía a propósito, para comparar INDICE con CONTARA, que trae el penúltimo valor, con BUSCAR, que trae el último. Incluye las variantes de fecha, fila, último número, último texto y última factura de un cliente. Al agregar facturas al final, todos los resultados se actualizan. Descargar archivo.
Preguntas frecuentes.
A continuación respondemos las preguntas frecuentes de nuestros lectores.
¿Cuál es la fórmula para traer el último valor de una columna?
=BUSCAR(2;1/(C5:C200<>"");C5:C200), que funciona aunque haya celdas vacías.
¿Por qué INDICE con CONTARA me trae el penúltimo dato?
Porque hay una celda vacía entre los datos y CONTARA cuenta un valor menos.
¿Cómo traigo el último valor de un cliente?
Con =BUSCAR(2;1/(B5:B200=cliente);C5:C200).
¿Cómo selecciono la primera celda vacía con una macro?
Con Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select.
Twitter
Facebook
WhatsApp
LinkedIn