Convertir hipervínculos a texto

Hace un poco más de un año atrás publiqué una nota mostrando una técnica para seleccionar celdas que contienen un hipervínculo.

Uno de mis lectores me consulta ahora cómo convertir la dirección del hipervínculo a texto que aparezca como valor de la celda. En otras palabras, queremos que en las celdas de la columna B aparezca las direcciones de los hipervínculos de las celdas en la columna A

Excel no tiene una función nativa para esta tarea por lo que tendremos que usar una UDF, es decir, escribir nuestra propia función.

Esta función es muy sencilla

Function hyp_to_text(rngCell As Range) As String
  
    hyp_to_text = rngCell.Hyperlinks(1).Address

End Function


Para usarla podemos ponerla en una módulo común del cuaderno Personal y la aplicamos usando el asistente de funciones con la categoría "Definidas por el usuario"

y finalmente apretamos Aceptar

Si la celda no contiene un hipervínculo, el resultado es #¡VALOR!

Cálculo de semanas en Excel

Excel no tiene una función que calcule la cantidad de semanas entre dos fechas. Para calcular la cantidad de años, meses y días podemos usar la “indocumentada” función SIFECHA.

En esa nota muestro cómo calcular también la cantidad de semanas, pero dado que sigo recibiendo consultas sobre el tema mostraré aquí la técnica en forma sucinta. A quien no esté familiarizado con SIFECHA le recomiendo leer la nota mencionada.

Supongamos que queremos calcular la cantidad de meses, semanas y días entre dos fechas

En la celda B4 usamos la fórmula

=SIFECHA(comienzo,fin,"ym")

donde “comienzo” es un nombre que se refiere a la celda B1 y “final” es un nombre que se refiere a la celda B2.

(aclaración: dependiendo de las definiciones del sistema hay que usar “a” en lugar de “y” para los años)

En B5 ponemos =SIFECHA(comienzo,fin,"md")

Finalmente en B8 usamos =B4&" meses y "&B5&" días"

Excel no incluye el día de comienzo cuando calcula el intervalo, de manera que quien quiera incluirlo en el resultado deberá agregar “+1” a la fórmula.

Nosotros queremos este cálculo

En la celda B5 (semanas) ponemos

=ENTERO(SIFECHA(comienzo,fin,"md")/7)

para calcular la cantidad de semanas de los días no incluidos en la cuenta de los meses.

En la celda B6 ponemos

=SIFECHA(comienzo,fin,"md")-(B5*7)

Es decir, descontamos del total de días calculado por SIFECHA la cantidad de días de las semanas en la celda B5.

Si queremos una fórmula que no dependa del B5 tendríamos que usar

=SIFECHA(comienzo,fin,"md")-(ENTERO(SIFECHA(comienzo,fin,"md")/7)*7)

La fórmula en B9 es

=B4&" meses, "&B5&" semanas y "&B6&" días"

Planificar proyectos – pedido de ayuda a mis lectores

Necesito la ayuda de mis lectores para verificar y corregir, donde haga falta, mi nuevo modelo para planificar actividades. Los primeros cinco lectores que estén dispuestos a ayudarme recibirán un enlace para descargar el modelo.

La ayuda consiste en usar el modelo, para encontrar "bugs" y sugerir mejoras de funcionamiento o diseño.

A cambio de la ayuda, una vez terminadas las correcciones, recibirán una copia del modelo con la contraseña que les permitirá ver todas las fórmulas y códigos utilizados en su desarrollo.

El mail aceptando ayudar debe enviarse a la dirección que figura en el enlace "Ayuda" (de ninguna manera poner la dirección del mail en un comentario!!). Desde ya, muchas gracias.

Y ahora, ¿qué es este modelo de planificación de actividades? Hemos tocado el tema tangencialmente en las notas sobre cómo crear un diagrama de Gantt en Excel. Este modelo va más lejos. La idea es poder definir actividades de un proyecto, crear dependencias entre las actividades (una actividad "precedente" determina la fecha de inicio de la actividad "dependiente"), mostrar la relación de la actividades en un diagrama de Gantt y crear una cuadro de control de la actividades.

Parte de los datos son calculados automáticamente: al determinar la fecha de comienzo y la duración, la fecha de finalización es calculada automáticamente. Lo mismo si se ingresa la fecha de finalización y la duración, etc.

Este modelo tiene dos objetivos:

• Dar una alternativa sencilla a la planificación de proyectos cuando el uso de aplicaciones como MSProject son un "overkill"
• Servir como material para estudiar las distintas técnicas empleadas (controles, nombres dinámicos, eventos, macros, gráficos dinámicos, etc.)

El modelo ha sido desarrollado en tres versiones: Excel 2003, Excel 2007 y Excel 2010.
Está compuesto de cuatro hojas visibles, dos ocultas y una que puede ser creada "al vuelo".

Inicio: definición del nombre del proyecto e instrucciones para el uso del modelo.

Actividades: definición de las actividades del proyecto (nombre, descripción, responsable, fecha inicio, final fin, duración, precedente, estatus de cálculo, estatus de datos). El modelo permite determinar "precedentes", actividades que deben ser completadas antes que otra actividad (la dependiente) pueda comenzar.

Gantt: diagrama de Gantt que se actualiza automáticamente de acuerdo a los cambios en la hoja Actividades. Se muestran también el número de días requerido para completar el proyecto (total y sólo días laborales).

To Do: permite controlar el estado de las actividades en relación a la fecha corriente.

Detalle de actividad: el hacer un doble clic en el nombre de una actividad (en la hoja Actividades), se crea automáticamente una hoja que permite detallar sub-tareas para la actividad. Si la hoja existe, pasa a ser la hoja activa.

Feriados: oculta; se puede acceder desde la hoja de actividades. Aquí se definen los días feriados para el cálculo del total de días laborales entre el principio y fin del proyecto.

Actualización: el modelo está disponible! Los enlaces para la descarga se encuentran en esta página.

Control de saldos de bancos con Excel.

Ariel me consulta cómo hacer para manejar en una única tabla los movimientos y saldos de varios bancos. Supongamos esta tabla

¿Cómo calculamos el saldo? Como prefiero usar una única fórmula para la columna propongo poner en la celda F2 ésta

=SUMA($D$2:D2)-SUMA($E$2:E2)

y copiarla a lo largo del campo

Préstese atención a las referencias semi-absolutas en los rangos de la fórmula.

El problema se nos presenta, obviamente, al filtrar la lista para ver los movimientos y el saldo de uno de los bancos

El saldo correcto es 2,495.00. Podemos solucionar esta situación usando SUBTOTALES. En F2 ponemos

=SUBTOTALES(9,$D$2:D2)-SUBTOTALES(9,$E$2:E2)

y la copiamos al todo el rango de la columna

SUBTOTALES suma sólo los valores de las celdas visibles, con lo que al filtrar el resultado será el correcto

Podemos mejorar notablemente nuestro modelo convirtiendo el rango de los datos en una “tabla” (o “lista” en Excel 2003).

Las tablas/listas tienen varias ventajas:

• Formato automático
• Las fórmulas en las columnas son copiadas automáticamente al agregar filas
• La fila de totales
• Actualización automática de todo objeto ( fórmulas, gráficos, tablas dinámicas) basados en la tabla

El archivo con el ejemplo se puede descargar aquí.

Etiquetas personalizadas en gráficos de Excel - segunda nota

En la nota anterior vimos cómo agregar etiquetas definidas en un rango de celdas. En esta nota veremos un ejemplo más avanzado de lo que podemos hacer con esta técnica. Supongamos esta tabla que analiza las ventas por mes en comparación a un objetivo mensual

Podemos representar todos estos datos en un gráfico combinado. Pero inmediatamente vemos que se nos crea un problema de escala. Si bien podemos poner las ventas en el eje principal de la Y y la diferencia en un eje secundario, no tenemos solución para la diferencia en porcentaje (no podemos agregar un tercer eje).

Mi propuesta en este caso es representar las ventas en un gráfico de columnas y poner los datos de la diferencia, en valor absoluto y en porcentaje, en una etiqueta

El primer paso para crear este gráfico es crear un rango con las etiquetas

Usamos la fórmula

=TEXTO(C5;"#.##0")&CARACTER(10)&" ("&TEXTO(D5;"0,0%")&")"

La función TEXTO es indispensable para obtener el texto combinado con el formato adecuado para los números.

Hemos quitado el eje de las Y, y en su lugar hemos agregado las etiquetas estándar de Excel con los valores de cada uno de los puntos de las serie. Para poder agregar las etiquetas personalizadas necesitamos otra serie.

La "Series2" es de hecho la misma serie de las ventas (el rango B5:B10). Para volver "invisibles" los puntos de la nueva serie definimos la serie en un eje secundario y fijamos la propiedad de relleno a "sin relleno" y la de bordes a "sin línea". Esto hace que no veamos las columnas (los puntos de las series) pero que podamos agregar etiquetas.

Para lograr esto usamos la macro que hemos definido (ver la nota anterior)

Finalmente, agregamos un cuadro de texto y lo ligamos a la celda que contiene el valor "objetivo" (la celda C2 en nuestro ejemplo). Como con las etiquetas, creamos el cuadro y lo ligamos a la celda creando una referencia (ponemos el signo "=" en la barra de las fórmulas y hacemos clic a la celda).

El archivo con los ejemplos y las macros se puede descargar aquí.

Etiquetas personalizadas en gráficos de Excel

Excel permite agregar etiquetas en las series de los gráficos, pero no tiene un método para establecer un rango de celdas que contenga las etiquetas. Veamos este ejemplo

 La tabla en el rango A2:C5 nos permite calificar el resultado de ventas de cada mes en el rango C9:C14. Esto lo hacemos en forma dinámica usando la fórmula

 =INDICE($A$2:$A$5;COINCIDIR(B9;$C$2:$C$5))

en el rango C9:C14.

El gráfico nos muestra las ventas de cada mes.
Queremos poner etiquetas que muestren la calificación de cada mes. Excel nos permite agregar etiquetas con los valores (como en la imagen), con la categoría, con el nombre de la serie o con una combinación de las tres.

Pero nosotros queremos poner como etiquetas las calificaciones, es decir, los valores del rango C9:C14.

La forma más inmediata de hacerlo es manualmente. El proceso es el siguiente

1 – Agregamos etiquetas a la serie

2 – Hacemos un clic para seleccionar todas las etiquetas.
3 – Un segundo clic selecciona una única etiqueta.
4 – Con la etiqueta seleccionada ponemos el signo "=" en la barra de la fórmulas y creamos una referencia a la celda correspondiente en el rango de las etiquetas (en nuestro caso C9 para el primer punto de la serie).

Este video muestro el proceso

 

Este método es ineficiente cuando tenemos una serie con muchos datos. El proceso pasa a ser trabajoso y la posibilidad de error crece con el número de operaciones que debemos realizar. En esos casos una macro es la solución más eficiente.





Como se ve, empezamos por seleccionar el gráfico. Luego seleccionamos el rango de las etiquetas personalizadas y apretamos OK.

En un módulo corriente del editor de Vba (preferentemente en el cuaderno Personal) copiamos este código

  Sub label_data()

    Dim iD As Long
    Dim rCustomLableRange As Range

    If ActiveChart Is Nothing Then
        MsgBox "Debe seleccionar un grafico ", 0
        Exit Sub
    End If

    On Error GoTo errNoData
    Set rCustomLableRange = Application.InputBox(prompt:="Seleccione el rango de etiquetas", _
                                                    Title:="Etiquetas personalizadas", _
                                                    Type:=8)


    For iD = 1 To rCustomLableRange.Count
        With ActiveChart.SeriesCollection(1).Points(iD)
            .HasDataLabel = True
            .DataLabel.Text = rCustomLableRange(iD)
        End With
    Next iD

    Exit Sub

errNoData:
If rCustomLableRange Is Nothing Then Exit Sub

End Sub

Este código aplica las etiquetas sólo a la primera serie de datos (en caso de haber más de una serie). 

Rob Bovey publicó un complemento que ofrece todo tipo de soluciones para el etiquetado de gráficos. 

En la próxima nota mostraré un ejemplo más elaborado.