Ultimo valor en el rango (fila o columna)

En la nota sobre hipervínculos describimos una fórmula para obtener la dirección de la última celda en la columna que contiene un valor.
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

Hipervínculos en Excel

Excel permite crear hipervínculos con facilidad. Todo lo que tenemos que hace es seleccionar una celda y pulsar Ctrl+Alt+K,




o usar el menú contextual pulsando el botón derecho del Mouse sobre la celda



o usar el menú Insertar-Hipervínculo



En este caso



creamos un vínculo a la celda A1 de la hoja Lista en nuestro cuaderno.

De la misma manera podemos crear vínculos a celdas en otras hojas del cuaderno, celdas en hojas de cuadernos remotos y también a páginas en la WEB.



Al señalar la celda con un hipervínculo, podemos ver la información de pantalla, dato que podemos modificar en el diálogo de creación del hipervínculo.

Además podemos agregar hipervínculos no sólo a celdas sino también a otros objetos como gráficos o imágenes, por ejemplo.

Otra forma de crear un hipervínculo es usar la función HIPERVINCULO. Por ejemplo, podemos crear el mismo hipervínculo como en la celda A1, usando el asistente de funciones


Sin embargo al referirse a un rango en una hoja del cuaderno, la función no funcionará





Para solucionar este problema usamos el símbolo # combinado con el nombre de la hoja y la referencia a la celda, en forma de texto, como argumento de la función
=HIPERVINCULO("#Clientes!A1","Pasar a la hoja Clientes)



La ventaja de usar la función en lugar del menú, es que podemos crear vínculos dinámicos.

En esta nota nos referiremos sólo a vínculos dentro del cuaderno activo.

Por ejemplo, supongamos que tenemos un cuaderno con varias hojas, una de ellas llamada "Clientes" y otra llamada "Proveedores".
En lugar de crear dos hipervínculos, uno a cada hoja, podemos crear una estableciendo la hoja a la que queremos pasar en forma dinámica.
Por ejemplo, en la celda A1 creamos una lista desplegable (usando el menú Validación de Datos) con los nombres de las hojas del cuaderno



En la celda C1 ponemos está fórmula con la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!A1","Pasar a la hoja "&A1)

Como ven, usamos el valor de la celda A1 también para crear la referencia a la hoja y también para crear el nombre descriptivo



Tanto la referencia como el nombre descriptivo cambian de acuerdo a la selección en la celda A1.

Hasta aquí hemos creado hipervínculos que nos llevan a la celda A1 de la hoja buscada. Ahora supongamos que queremos que el vínculo nos lleve automáticamente a la primer celda vacía de una determinada columna.
En la hoja Clientes tenemos una lista de clientes y sus direcciones. Queremos que el hipervínculo nos lleve, en nuestro ejemplo, a la celda A9 de la hoja Clientes



Dado que la columna A de la hoja Clientes contiene valores de texto, podemos usar esta fórmula para encontrar la dirección de la primer celda vacía

=DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1)

En esta fórmula estamos suponiendo que el símbolo * no aparece como texto en ninguna de las celdas del rango de búsqueda.
Ponemos esta fórmula en la celda B1 y la usamos como referencia en la función HIPERVINCULO



En lugar de usar una celda auxiliar, podemos incluir la fórmula de la celda B1 como argumento en la función HIPERVINCULO

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",Clientes!A:A,-1)+1,1),"Pasar a la hoja "&A1)

Finalmente podemos dar un paso más adelante y vincular dinámicamente también la búsqueda de la primer celda libre en función del valor de la celda A1

=HIPERVINCULO("#"&A1&"!"&DIRECCION(COINCIDIR("*",INDIRECTO(A1&"!A:A"),-1)+1,1),"Pasar a la hoja "&A1)
Aquí tenemos que usar la función INDIRECTO para que Excel considere el valor de la celda A1 en la función COINCIDIR.



El archivo con la fórmula se puede descargar aquí





Technorati Tags: MS Excel

Ajuste automático de fórmulas en Excel

Excel ajusta las referencias dentro de una fórmula automáticamente. Supongamos esta operación en una hoja de Excel



La celda B7 calcula el total de unidades vendidas con =SUMA(B2:B6). Como ya habrán notado, hemos olvidado de incluir el Producto 4.
Como queremos guardar el orden en nuestra lista de productos incluimos el Producto 4 entre la línea 4 y la línea 5



Como pueden ver, Excel ha corregido automáticamente el rango en la función SUMA.

Agreguemos una nueva columna a nuestra tabla, Ingresos. En la celda A12 hemos puesto una lista desplegable con los productos y en la celda B12 una fórmula con la función BUSCARV que nos muestra los ingresos del producto elegido



Ahora agreguemos la columna Precio entre las columnas "Unidades Vendidas" e "Ingresos"



Como pueden ver Excel ha ajustado el rango de la fórmula de A2:C7 a A2:D7 para incluir la nueva columna, pero no ha modificado el tercer argumento de la función BUSCARV que sigue siendo 3. Es decir, BUSCARV sigue buscando en la tercer columna a la derecha de la columna A, que ahora es Precio y no Ingresos.
No se trata de un "bug" de Excel, ya que el argumento es un número de referencia estático y no un rango.
Veamos algunas técnicas para sobreponernos a este problema. Si nuestra función BUSCARV siempre se refiere a la última columna en el rango, podemos crear una referencia dinámica con la función COLUMNAS.

Empecemos por incluir nuestra tabla de datos en un nombre, tabla_productos



Ahora modificamos nuestra fórmula de la siguiente manera

=BUSCARV(A12,$A$2:$D$7,COLUMNAS(tabla_productos),0)

El resultado es el siguiente



Como ven, nuestra fórmula se ha ajustado automáticamente. Lo mismo sucederá si agregamos una nueva columna dentro del rango



La función COLUMNAS cuenta el número de columnas en una matriz o rango al cual se refiere.
Si queremos que la función se refiera dinámicamente a la anteúltima columna, usaremos la expresión COLUMNAS(referencia)-1.

Technorati Tags: MS Excel