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

Cálculo de fecha de pago con Excel.

Un caso particular del cálculo de fechas es el de fechas de pago. Si las condiciones de pago de una factura, o cualquier otra obligación, son, por ejemplo, 30 días de la fecha de la factura, el cálculo con Excel es muy sencillo. Para calcular la fecha de pago de una factura cuya fecha es el 25/07/2008 y las condiciones de pago son fecha factura + 30 días, usamos



Pero si la intención es que la fecha de pago sea el mismo día de la fecha de la factura pero un mes más tarde, usamos la función FECHA.MES (en algunos sistemas tendremos que usar la versión inglesa: EDATE)



Para poder usar esta función necesitamos que esté instalado el complemento Analysis ToolPak.
Como puede verse, los resultados son distintos. En el primer caso, 24/08/2008 es exactamente 30 días; el segundo resultado es el 25 del mes siguiente.

Ciertas empresas e instituciones tienen días de pago determinados. Por ejemplo, supongamos una empresa que paga sólo los 15 de cada mes. Si la condición de pago es "fecha de la factura + 30 días", las facturas con fecha posterior al 15 del mes serán pagadas de hecho dos meses más tarde.
Veamos este caso



La fecha de pago de la segunda factura será un mes más tarde que el de la primera. Cómo hacemos para calcular estas fechas con Excel? Obviamente creando un fórmula condicional con la función SI

=SI(DIA(A3+30)<=15,FECHA(AÑO(A3+30),MES(A3+30),15),FECHA(AÑO(A3+60),MES(A3+60),15))



Usamos la función FECHA para "armar" la fecha de pago basándonos en la fecha de la factura.
En caso que queramos establecer un mes después de la fecha de la factura (o dos, o cualquier otro número de meses) usaremos la función FECHA.MES

Technorati Tags: MS Excel