Fórmula genérica
=MATCH(bignum,range)
Resumen
Para obtener la última posición relativa (es decir, la última fila, la última columna) para los datos numéricos (con o sin celdas vacías), puede usar la función COINCIDIR con un "número grande".
En el ejemplo que se muestra, la fórmula en E5 es:
=MATCH(9.99E+307,B4:B9)
Última posición * relativa *, no fila en la hoja de trabajo
Al crear fórmulas avanzadas que crean rangos dinámicos, a menudo es necesario averiguar la última ubicación de datos en una lista. Dependiendo de los datos, esta podría ser la última fila con datos, la última columna con datos o la intersección de ambas. Nota: queremos la última * posición relativa * dentro de un rango dado, no el número de fila en la hoja de trabajo:
Explicación
Esta fórmula utiliza la función COINCIDIR en el modo de coincidencia aproximada para localizar el último valor numérico en un rango. Coincidencia aproximada habilitada estableciendo el tercer argumento en COINCIDIR en 1, u omitiendo este argumento, que por defecto es 1.
El valor de búsqueda es un llamado "número grande" (a veces abreviado "bignum") que es intencionalmente más grande que cualquier valor que aparecerá en el rango.
El resultado es que MATCH "retrocederá" hasta el último valor numérico del rango y devolverá esa posición.
Nota: este enfoque funciona bien con celdas vacías en el rango, pero no es confiable con datos mixtos que incluyen tanto números como texto.
Sobre bignum
El mayor número que puede manejar Excel es 9.99999999999999E + 307.
Al usar MATCH de esta manera, puede usar cualquier número grande que esté garantizado como mayor que cualquier valor en el rango, por ejemplo:
=MATCH(1E+06,range) // 1 million =MATCH(1E+09,range) // 1 billion =MATCH(1E+12,range) // 1 trillion
La ventaja de usar 9.99E + 307 o similar es que es (1) un número enorme y (2) reconocible como marcador de posición para un "número grande". Verá que se usa en varias fórmulas avanzadas de Excel.
Gama dinámica
Puede utilizar esta fórmula para crear un rango dinámico con otras funciones como INDICE y OFFSET. Consulte los enlaces a continuación para ver ejemplos y explicaciones:
- Rango dinámico con INDEX y COUNTA
- Rango dinámico con OFFSET y COUNTA
La inspiración para este artículo provino del excelente libro de Mike Girvin Control + Shift + Enter, donde Mike explica el concepto de "última posición relativa".
