La fórmula en cuestión nos da la dirección de la última celda no vacía a condición que todos los valores del rango, o por lo menos el de la última celda, sean texto.
Por supuesto hay casos en que los valores en el rango son numéricos, en otros casos son sólo texto y también hay casos en que hay valores de ambos tipos.
Supongamos estos dos rangos de valores
La fórmula que nos dará la dirección de la última celda en la columna que contiene un valor de tipo texto es
=DIRECCION(COINCIDIR("*",A:A,-1),1)
El problema con esta fórmula es que si en una celda aparece el símbolo * como texto, dará como resultado la dirección de esa celda. Podemos reemplazar esa fórmula por ésta
=DIRECCION(COINCIDIR(REPETIR("z",250),A:A),1)
Hemos reemplazado el valor de la celda A4 por "*". Usando la primer fórmula (en la celda D3) obtenemos el resultado $A$4, que es incorrecto. Con nuestra nueva fórmula obtenemos el resultado correcto (en la celda D4).
Si el rango a evaluar contiene sólo valores numéricos, como en la columna B, usamos esta fórmula
=DIRECCION(COINCIDIR(9.99999999999999E+307,B:B),2)
La expresión 9.99999999999999E+307 es el mayor número que Excel puede considerar.
Para obtener el valor de la última celda podemos usar la función INDIRECTO usando como referencia la celda que contiene la dirección o anidando la fórmula dentro de INDIRECTO. Por ejemplo =INDIRECTO(D3) dará como resultado "Laura"
También podemos usar las funciones anidadas en una única fórmula
=INDIRECTO(DIRECCION(COINCIDIR("*",A:A,-1),1))
Y en el caso de la columna B con valores numéricos
=INDIRECTO(DIRECCION(COINCIDIR(9.99999999999999E+307,B:B),2))
Para encontrar la dirección de la última celda en un rango mixto (valores numéricos y texto) podemos usar esta fórmula matricial
={MAX((FILA(A1:A65535)*(A1:A65535<>"")))}
o esta que es la misma pero con SUMAPRODUCTO
=DIRECCION(SUMAPRODUCTO(MAX((FILA(A1:A65535)*(A1:A65535<>"")))),1)
La expresión (A1:A65535<>"") crea una matriz de valores VERDADERO o FALSO.
La expresión crea una matriz con el número de fila. Al multiplicar los vectores, el número de fila de toda celda vacía será 0. Por lo tanto la función MAX nos dará el número de fila de la última celda no vacía.
Otro tipo de solución es utilizar funciones definidas por el usuario (UDF) como las que publicó John Walkenbach hace ya mucho tiempo.
Aquí pueden descargar el cuaderno con las funciones y las macros.Technorati Tags: MS Excel
y para hacerlo con filas como se debería modificar la fórmula?
ResponderBorrarGracias.
Haciendo la búsqueda en la fila. Es decir, en la función COINCIDIR el segundo argumento sería "2:2" para hacer la búsqueda en la fila 3.
ResponderBorrarhola!, como puedo hace para que jale el último valor mayor a cero?
ResponderBorrarGracias
Adaptando la fórmula que aparece al final de la nota de esta manera (suponiendo que los valores están en el rango A1:A15)
ResponderBorrar=INDICE(A1:A15,SUMAPRODUCTO(MAX((FILA(A1:A15)*(A1:A15<>0)))))
En lo personal yo utilizo una variante lógica de la función BUSCAR, si deseo el último valor de una columna simplemente escogo una celda y digito: =BUSCAR(10000;A:A) y esto me da el ultimo valor de la columna A pero si lo que deseo es el último valor de la fila 6 entonces digito: =BUSCAR(10000;A6:MM6) con lo que se busca el último valor introducido en esa fila hasta la MM6, la distancia de busqueda la escoge el usuario.
ResponderBorrarGracias por compartir. He tocado el tema hacia varios años atrás (en esta nota) y tal vez te interese ver esta otra que trata sobre cómo encontrar el último elemento en un rango bajo condiciones.
ResponderBorrarEn cuanto a esta nota, fijate que el tema es encontrar el último elemento en rangos mixtos (números y texto). La fórmula que mencionas da error si el rango contiene sólo texto, o da el último número si el rango contiene tambiéin números y también texto.
Gracias!!!, realmente muy útil, yo lo utilice para que muestre la fecha de registro que se encuentra en otra columna al lado de los datos
ResponderBorrar