Mostrando las entradas con la etiqueta Dashboards. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Dashboards. Mostrar todas las entradas

lunes, marzo 10, 2014

Hojas Excel con traducción automática

Recientemente tuve que crear una hoja que incluyera la posibilidad de presentar el contenido en distintos idiomas.
La idea era usar una única hoja, de hecho un dashboard, pero que el usuario pudiera elegir en qué idioma presentar los textos de las celdas y demás objetos en la hoja/dashboard.

Este ejemplo muestra el funcionamiento

captura de pantalla


Como puede apreciarse, cuando el usuario elige un idioma apretando alguno de los botones de opción, los titulas de los cuadros de datos y del gráfico cambian.

La traducción se puede hacer fácilmente usando la función INDICE, pero la técnica varía si se trata de una celda o de un objeto. Esto es lo que mostraré en esta nota.

Para hacerlo empezamos por crear un glosario, una tabla con los textos de las palabras que queremos traducir en los distintos idiomas. En nuestro caso creamos un glosario con Castellano, Inglés e Italiano

glosario

En la hoja del dashboard agregamos los botones de opción para la elección de los idiomas. En las definiciones del botón lo asociamos a una celda. Es una buena práctica usar las columnas de la izquierda como columnas auxiliares, de manera que podamos ocultarlas. En nuestro caso vinculamos los botones a la celda A3

Definicion del boton de opcion
Otra buena práctica en Excel es definir un nombre que se refiera a la celda; en nuestro caso definimos el nombre "cel_Idioma" que se refiere a la celda A3

nombre definido

También es recomendable enmarcar los botones de opción en un cuadro de grupos.

Cuando el usuario elige el Castellano, el valor de "cel_Idioma" es 1; si elige English, será 2 y si elige Italiano será 3.

En la celda F3 de la hoja dashboard ponemos esta fórmula:

=INDICE(glosario!B6:D6,cel_Idioma)
formula para traducir

Esta fórmula usa el valor de "cel_Idioma" para extraer el texto en el idioma correspondiente en la tabla Glosario.

Pregunta con premio (copia gratuita totalmente funcional del Planificador de Proyectos) a las primeras tres respuestas correctas:

¿Que tiene de particular la fórmula en la celda F3 del ejemplo?

 La misma técnica empleamos para todas las celda cuyo contenido queremos traducir.

Si nos fijamos en el título de la lista desplegable del gráfico, veremos que se trata de un cuadro de texto "montado" sobre una celda.

cuadro de texto

No podemos ligar un cuadro de texto, o cualquier otra forma gráfica, a una fórmula pero si podemos hacerlo a una referencia. El rodeo para lograr el efecto de traducción consiste en poner la fórmula con la función INDICE que extrae la traducción del Glosario en una celda de la columna A (de manera que luego podamos ocultarla).

Creamos un cuadro de texto (o cualquier otra forma) y la superponemos en la celda adecuada (K2 en nuestro ejemplo). Luego seleccionamos el cuadro de texto y en la barra de las fórmulas ponemos =A9 (o ponemos = y luego hacemos un clic en la celda A9)

ligar forma a celda

La celda A9 contiene la fórmula

=INDICE(glosario!B14:D14,cel_Idioma)

Otra situación es si usamos tablas dinámmicas. A pesar que las tablas ocupan celdas, no podemos introducir fórmulas en los encabezamientos de las áreas (si podemos cambiar el texto).
Para superar este problema usamos nuevamente cuadros de texto ligados a celdas ocultas que contienen la fórmula con la función INDICE. Para mimetizar los cuadros de texto con el resto de la tabla le damos un relleno sólido con el mismo color que la tabla

tabla dinamica

En este video podemos ver el dashboard en funcionamiento



jueves, febrero 27, 2014

Uso de controles en hojas de Excel - Spin Button con valores no enteros

En la nota anterior vimos como superar la limitación del uso de números negativos en los controles Spin Button y Scroll Bar. En esta nota veremos un rodeo para usar números no enteros en estos controles.

Supongamos que queremos crear un Control de Número que vaya de 0 a 5, pero en saltos de 0.5 (en algunos países se usa la coma en lugar del punto para separar la parte decimal del número).

Excel nos deja ingresar un número no entero en la casilla Incremento,

definiciones del control


pero al apretar Aceptar cambia la definición dejando sólo la parte entera del número. En  nuestro ejemplo, la casilla Incremento mostrará 0.

Como en la nota anterior, el rodeo consiste en usar una celda auxiliar. Siguiendo con nuestro ejemplo, el valor mínimo es 0; el máximo será 10 (resulta de dividir 5 por 0.5) y el incremento 1. En nuestro ejemplo la celda vinculada es A3 y en la celda A4 ponemos la fórmula =A3/2 o su equivalente =A3*0.5

definiciones dle control

En resumen, la norma es:

  • mínimo: 0
  • máximo: máximo deseado dividido por el incremento deseado
  • incremento: 1
  • en la celda auxiliar: celda vinculada multiplicada por el  incremento deseado (o dividida por el inverso del incremento deseado)
Asi que si quisiéramos ir de 0 a 10 con incrementos de 1/3, ponemos 30 en la casilla del máximo y en la celda auxiliar (A4) =A3/3

Si usamos el Control de Número (Spin Button) de la colección ActiveX podemos dar una solución sin usar una celda auxiliar programando eventos para definir el control dinámicamente de acuerdo a valores que ingresemos en celdas de la hoja.

Siguiendo con nuestro ejemplo, ponemos el valor máximo deseado (10) en la celda E3 y el valor de incremento enla celda E4. En las propiedades del control dejamos el valor de la propiedad LinkedCell (celda vinculada) en blanco


Tal como indicamos en la nota anerior, abrimos el editor de Vb en el módulo de la hoja que contiene el control seleccionando el control y apreando Ver Código. En el módulo de la hoja ponemos estos dos eventos:

Códigos de los eventos


El evento GotFocus define el valor máximo del control de acuerdo a los valores que ingresamos en las celda E3 y E4 y el evento Change pasa el valor calculado a la celda vinculada con cada cambio del control



lunes, febrero 24, 2014

Uso de controles en hojas de Excel - Spin Button con valores negativos

Desde casi los primeros días de este blog he escrito sobre el uso de controles en hojas de cálculos. Los usos son casi ilimitados: dashboards, gráficos dinámicos, listas desplegables, etc.
Excel cuenta con dos colecciones de controles: Formulario y Activex. Ya hemos escrito sobre las ventajas y desventajas de cada una de estas colecciones. En esta nota veremos como sobreponerse a las limitaciónes de los controles Control de Número (Spin Button) y Barra de Desplazamiento (Scroll Bar)
  • aceptan sólo números enteros
  • no aceptan números negativos
En esta nota veremos un rodeo para poder usar números negativos en estos controles.

Por ejemplo, si queremos usar el control de números (spin button) de la colección Formlarios con valores que vayan de -10 a 10, al tratar de definir el valor mínimo veremos lo siguiente

definiciones del Spin Button

Para superar esta limitación, siguiendo con nuestro ejemplo,usamos las siguientes definiciones:

Definiciones del control

Definimos 0 en el valor mínimo y el doble del máximo deseado para el Valor Máximo. En nuestro ejemplo, el valor del control está ligado a la celda A3; en la celda A4 ponemos la fórmula =A3-10. La celda A4 mostrará los valores deseado al accionar el control


Si usamos el control Activex la situación es diferente. Aparentemente Excel acepta el número negativo en la definición del mínimo

Definiciones del control
Pero al tratar de usar el control veremos lo siguiente:

error con numero negativo
al descender de 0, en lugar de -1 Excel poner en la celda ligada 65535!! (suena familiar, no es cierto? 65536 es el número máximo de filas en las versiones anteriores a Excel 2007).

En el caso del control ActiveX, la solución consiste en programar un evento para el objeto. En el menú Desarrollador apretamos el botón Modo de Diseñño, seleccionamos el control y activamos la opción Ver Código

porgramar evento

Al apretar Ver Código, el editor de Vb se abre en el módulo de la hoja; allí ponemos este código

código del control
Con este código el control pasa los números deseados, también los negativos. La ventaja de usar el control ActiveX es que no necesitamos agregar una celda auxiliar.

En la próxima nota veremos como usar estos controles con valores no enteros.

martes, diciembre 03, 2013

Cámara fotográfica de Excel - otro uso

Una de las herramientas útiles y poco conocidas de Excel es la cámara fotográfica. Esta herramienta nos permite, entre otras cosas, ubicar gráficos y tablas en un dashboard hecho en Excel. Muy útil en particular cuando queremos ubicar tablas de distinto ancho de columnas en una hoja (como en el ejemplo de esta nota sobre dashboards en Excel).

Otra posibilidad es usar la cámara fotográfica de Excel en lugar de la ventana de inspección. Supongamos este ejemplo: en una hoja de un cuaderno tenemos un cuadro de ventas, comisiones y ventas neto y en otra hoja la tabla de los porcentajes de las comisiones en función del volumen de ventas

Uso de la camara en Excel



Ahora supongamos que queremos ver cómo influyen los cambios en los porcentajes de las comisiones en el total neto de las ventas. Como las tablas no están en la misma hoja, tendremos que "saltar" de una hoja a la otra durante el proceso de encontrar el resultado deseado.

Para evitar esto podemos usar, en lugar de la ventana de inspección, la cámara fotográfica de Excel.

Empecemos por poner el icono de la cámara en la barra de acceso rápido



Una vez instalado el icono de la cámara, seleccionamos el rango de la tabla de ventas, hacemos un clic al icono, pasamos a la hoja con la tabla e comisiones y con otro clic copiamos la imagen de la tabla



Como puede verse, cada cambio en la tabla de comisiones se refleja inmediatamente en la imagen de la tabla de ventas.
La ventaja de usar la cámara se hace evidente si vemos como sería el uso de la ventana de inspección para la misma tarea

control de resultados en Excel

lunes, agosto 26, 2013

Power Pivot es la mejor novedad de Excel en 20 años

Si eres un usuario que utiliza fórmulas y características como:
  • BUSCARV()
  • SUMAR.SI()
  • SUMAR.SI.CONJUNTO()
  • Tablas dinámicas
  • Conexiones a bases de datos u fuentes externas
  • Tablas de Excel
  • Consolidación de múltiples datos, VBA, etc
entonces este articulo o post te resultará muy interesante pues te ayudará a hacer tus reportes y análisis de una manera nunca antes vista. Power Pivot es una extensión a las cosas que ya conoces sobre Excel.

pero…¿Qué es Power Pivot en realidad?

Power Pivot es la nueva herramienta (complemento de Excel 2010 y 2013) que permite a los usuarios de negocio tomar un papel mucho más importante en la creación de ideas que son relevantes para la toma de decisiones. Es una herramienta de auto-manejo de Inteligencia de Negocio.
Si eres uno de los miles de usuarios de Excel que lucha con la creación de nuevos códigos VBA o tratar de llegar a soluciones que son semi-dinámicas debido a las limitaciones del tradicional Excel, Power Pivot entonces hará tu vida más fácil en casi todos los sentidos.
Para conocer un poco mas el aspecto técnico sobre Power Pivot puedes visitar mi articulo sobre:
pero ahora, vamos a la parte que nos interesa.

¿Qué valor puede aportarle Power Pivot a mi trabajo actual?

Antes de comenzar, tengo que decirte algo muy importante... Power Pivot es gratuito!  No tienes que pagar absolutamente nada por el complemento.

Ok, ahora podemos comenzar. Voy a darte algunos puntos que necesitas saber sobre Power Pivot que te ayudarán a decidir si en realidad Power Pivot aporta o no valor a tu trabajo:
  1. Limitante de filas/columnas en Excel ya no es un problema: recuerdas que solamente tienes ~1M de filas para trabajar en el Excel tradicional? y que con tan solo 100K ya tienes problemas con el Excel? pues ya no - es un problema del pasado con Power Pivot. Con Power Pivot puedes importar virtualmente una cantidad casi infinita de filas y columnas y seguir trabajando a una velocidad deslumbrante. Mira la imagen de abajo como prueba de una tabla cargada en Power Pivot con un poco mas de 161M de filas :)image                 Imagen tomada de Powerpivotpro (mi buen amigo Rob Collie)
  2. Con Power Pivot puedes trabajar con múltiples tablas: correcto! Power Pivot trabaja con tablas dinámicas y/o funciones de cubo y en vez de utilizar BUSCARV() o VLOOKUP() para "aplanar" las tablas, pues solamente puedes tomar 1 tabla para "pivotear", puedes utilizar múltiples tablas para trabajar y relacionar los datos. Puedes leer un ejemplo de Jorge aquí
  3. Mejores y más flexibles fórmulas: alguna vez has intentado agregar un conteo distintivo en una tabla dinámica? es un problema muy serio y nunca se logra hacer algo dinámico o flexible, pero con DAX, el nuevo lenguaje de fórmulas para Power Pivot, puedes crear cualquier fórmula que te puedas imaginar. Te muestro un ejemplo aquí
  4. Creando una aplicación de BI en la web (Con Excel!): Gracias a la integración de Power Pivot con SharePoint, es posible crear aplicaciones en la Web a partir de Excel que se auto refresquen (actualicen)  con los datos necesarios de acuerdo a un horario. En corto, el reporte que creas en Excel ahora puede ser expuesto mediante un explorador en la web sin que hagas ningún cambio. Te dejo un ejemplo abajo de algo que he creado en Excel con Power Pivot y lo he colgado en mi sito de SharePoint para demostraciones. Más adelante te comentare sobre Power BI que es la nueva oferta de Office365 para crear reportes en la Web (que también puedes consumir desde tu iPad, iPhone, Android y como App de W8)   image
    (puedes darle clic en la imagen para poder ver el reporte desde tu explorador o visitando mi sito demo aquí )
Con estos 4 puntos creo que tenemos muchos que asimilar pero falta MUCHO mas...(a revisar en próximos artículos a publicar en mi blog y en este blog)

Pero vamos a revisar otros puntos y dejaré algo sumamente interesante para el final que te va a dejar boquiabierto.

¿Cómo consigo Power Pivot?

Si tienes Excel 2010 (cualquier versión) puedes descargar el complemento desde la siguiente pagina y luego seleccionando la versión correcta de Power Pivot (32 o 64 bits)
Mientras que para Excel 2013 el complemento viene integrado en ciertas versiones de la aplicación. Para conocer más al respecto te invito a leer la siguiente entrada en mi blog:

Power BI: tu futuro como profesional de Excel es prometedor :)

image
(dar clic en las imágenes para ver mi articulo completo sobre cada uno en mi blog)
Power BI se centra en Power Pivot con Office 365 (SharePoint) y las demás herramientas de BI que obtienes en Excel 2013 como:
didimoPower View: reporte creado netamente con Excel 2013
Animation
Power View nuevamente: todo esto con Excel 2013. Increíble, no?

Mapa de Calor creado con Power Map dentro de Excel 2013 (Población de Panamá)
Sígueme en twitter o en mi blog para conocer más y mantenerte al día sobre Power BI:
Blog 

domingo, agosto 25, 2013

PowerPivot – Bienvenida a un nuevo colaborador

Es para mí un placer anunciar que a partir de hoy Miguel Ángel Escobar comenzará a colaborar en este blog.

Miguel Ángel es, además de experto en Excel, un apasionado profesional del área del BI (Business Intelligence – Inteligencia de Negocios) y en particular del PowerPivot.

En su primer nota, Miguel Ángel nos introduce al nuevo mundo del PowerPivot. En las futuras notas, tratará distintos temas y aspectos de esta nueva y poderosa herramienta.

Miguel Ángel publica este blog bajo el lema "Power Pivot, Power BI, Excel y BI para las masas!" y es el propietario del sitio Powered Solutions.

domingo, enero 29, 2012

Gráfico Big Mac dinámico en Excel

La publicación británica The Economist publica desde hace varios años el índice Big Mac. El Big Mac Index (o Índice Big Mac, en español) es un índice elaborado a partir de una investigación no científica, que permite comparar el poder adquisitivo de distintos países donde se vende la hamburguesa Big Mac de McDonald's (citado de Wikipedia).

El diario argentino La Nación publicó este gráfico que muestra la diferencia del precio del Big Mac en relación al valor en los Estados Unidos



Este gráfico fue construido con la aplicación Tableau.

Como ya habrán intuido, la pregunta es: ¿se puede hacer con Excel?

Veamos qué elementos incluye:

• Gráfico de barras por país
• Controles que permiten visualizar los países por continente
• Los colores de las barras representan el valor (diferencias positivas en rojo, diferencias negativas en verde)

La tabla de datos es la siguiente



Empezamos por ordenar la tabla en orden ascendente según el campo Porcentaje; seleccionamos los campos “País” y “Porcentaje” para construir este gráfico de barras



Este es el gráfico de barras estándar de Excel con unas pocas modificaciones: quitamos las líneas de cuadrícula, fijamos las etiquetas del eje vertical en “bajo” y en formato de series de datos—relleno marcamos la opción “variar colores entre puntos”.

Este gráfico es una primera aproximación. Para poder agregar los elementos dinámicos y los calores del gráfico original tendremos que hacer algunas transformaciones.

Una segunda aproximación es usar una tabla dinámica para generar un gráfico dinámico que muestre sólo los países de los continentes elegidos



Lo que hemos hecho es generar una tabla dinámica y un gráfico dinámico basada en ella; la tabla está en la filas 3 a 27 que hemos ocultado, dejando visible sólo el campo de filtro del informe dinámico. Además hemos agregado una segmentación de datos para mostrar cuáles son los continentes elegidos



Pero para crear un gráfico como el publicado en La Nación tendremos que usar Vba (macros) y controles.
Esto es lo que queremos crear:



La anatomía del modelo es la siguiente:



1 – Controles: insertamos 7 casillas de verificación (de la colección de controles de hoja, no ActiveX) y los ligamos a las celdas en el rango B3:B9. Cuando se señala el control, la celda correspondiente muestra VERDADERO; en caso contrario mostrará FALSO. Usaremos estos valores en las macros que controlan los puntos mostrados en el gráfico.

2 – Celda de control: la celda B11 (el nombre Dimension_del_array se refiere a esta celda), cuenta cuantos controles han sido señalados, es decir, cuantos continente queremos mostrar en el gráfico. Cuando el valor es 6, significa que hemos elegido todos los continentes. También esta celda la usaremos en nuestras macros.

3 – Macros: programamos dos macros para ocultar o mostrar datos con Autofiltro en la tabla delos datos, una que responde a las elecciones de los controles excepto el control “Todos”, la segunda para el caso que el usuario señale la casilla “Todos”

El código de la primer macro es

Sub select_series()

    Dim strContinentes() As String
    Dim iR As Integer
    Dim iCounter As Integer
  
    If Range("Dimension_del_array") = 6 Then
        Range("Todos") = True
    Else
        Range("Todos") = False
    End If

    'redimensionar el array
    ReDim strContinentes(Range("Dimension_del_array"))
  
    iCounter = 0
  
    With Sheets("grafico dinamico")
    For iR = 4 To 9
        If .Cells(iR, 2) Then
                strContinentes(iCounter) = .Cells(iR, 1)
                iCounter = iCounter + 1
        End If
    Next iR
  
    Sheets("datos").Range("$A$1:$C$24").AutoFilter Field:=1, _
                Criteria1:=strContinentes, Operator:=xlFilterValues
  
    End With
    
End Sub



La segunda macro, para el caso que se haya elegido la casilla “Todos” es

Sub all_Continents()
    Dim iR As Integer
  
    'si se elige Todos
    With Sheets("grafico dinamico")
    If Range("Todos") Then
        For iR = 4 To 9
            .Cells(iR, 2).Formula = True
        Next iR
    Else
        For iR = 4 To 9
            .Cells(iR, 2).Formula = False
        Next iR
    End If
    End With

    Call select_series
        
End Sub



En este modelo usamos una única serie de valores, por eso para ocultar algunos de los puntos de la serie con Autofiltro (los países de los continentes que no hemos elegido) nos aprovechamos de la propiedad de los gráficos de no mostrar los valores de celdas ocultas.

El control “Todos” está asociado a la macro “all_Continents”



A los demás controles les hemos asignado la macro “select_series”.

Este modelo puede adaptarse a muchos escenarios.

El cuaderno puede descargarse, sin cargo, aquí (usar el enlace Descargar en la parte inferior de la página). Quien esté interesado en una explicación detallada del modelo y la contraseña para acceder a los códigos, puede descargar el manual (tiene un costo de 5 Euros).

viernes, julio 02, 2010

Fórmulas con resultados múltiples

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):



En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado


Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)




Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional




Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado




En la hoja Datos filtramos la tabla de acuerdo al producto deseado




La imagen en la hoja "cámara" reflejará el cambio instantáneamente




El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado




Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub


Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.


lunes, febrero 15, 2010

Diagramas Gantt dinámicos en Excel

En los albores de este blog publiqué una nota sobre cómo construir un diagrama Gantt con Excel. Mostramos allí dos posibilidades: usando formato condicional o usando gráficos.

En esta nota mostraremos como construir un diagrama de Gantt dinámico que nos permite mostrar en pantalla tareas que se extienden por períodos muy largos.

Cuando construimos el diagrama de Gantt en Excel con formato condicional, usamos una fila para cada tarea y una celda para cada unidad de tiempo. El modelo general consiste en usar la primer columna para definir las tareas, la segunda para la fecha de iniciación, la tercera para la duración, la cuarta para la fecha de finalización y a partir de la quinta columna usamos cada celda para representar una unidad de tiempo. Este es un ejemplo clásico



Las barras de color del diagrama las logramos usando estas fórmulas de formato condicional:



para las filas pares =Y($C3=F$2,$E3=F$2,RESIDUO(FILA(),2)=0)

para las filas impares =Y($C3=F$2,$E3;=F$2,RESIDUO(FILA(),2)=1)

Para construir la escala del tiempo (en la fila 2) ponemos en la celda F2 esta fórmula

=MIN(C3:C6)+7

que calcula la primer fecha más una semana de la primer tarea a realizar. En la celda G2 ponemos "=F2+7" y así sucesivamente.

Si observamos atentamente veremos que si bien la primer tarea concluye el 15/02/10, el diagrama parece indicar que lo hace el 12/02/10. Esto se debe a que usamos una resolución semanal para mostrar las tareas.
Podemos solucionar este problema usando una resolución diaria. En total necesitamos 90 días (la última fecha es el 26/03/10).

Si bien esto resuelve el problema, nos crea uno nuevo, el diagrama excede los límites de la pantalla



La solución ideal es, por lo tanto, trabajar con una resolución diaria pero que no exceda el ancho de la pantalla.

Empezamos por insertar algunas filas por encima de nuestro diagrama.



En la celda E3 calculamos la primer fecha del proyecto usando la función MIN. En la celda F5 creamos una referencia a la celda E3; en la celda G5 ponemos "=F5+1" y así sucesivamente para crear la escala de tiempo.

En la celda F6 creamos una referencia a la celda F5, seleccionamos el rango que comprende la semana (F6:L6) y aplicamos "combinar y centrar"


Cambiamos el ancho de las columnas de la selección a 0.6, quitamos los bordes interiores y obtenemos este resultado


Nótese que la fecha 01/01/10 parece ocupar una columna pero en realidad comprende 7 columnas, una para cada día de la semana.

Volvemos a aplicar esta técnica a cada grupo de siete columnas hasta obtener este resultado


Hemos mejorado en buena medida nuestro diagrama, pero aún nos queda una cuestión por solucionar.

Nuestro diagrama cubre ahora 13 semanas, con resolución diaria. Pero, ¿que pasa si una tarea se extienda por más de de 90 días del inicio del proyecto?
Esto lo solucionaremos creando un diagrama Gantt dinámico con fórmulas sencillas y una barra de desplazamiento de la barra de formularios.

Introducimos estos cambios en nuestro modelo:

1 – insertamos una columna a la izquierda de la hoja (la tabla del diagrama empieza ahora en la columna C).

2 – en la celda G3 ponemos la fórmula "=MIN(D5:D8)+A2"; el papel a cumplir por la celda A2 será explicado enseguida


3 –reemplazamos la fórmula en la celda G4 por una referencia a la celda G3

4 – en la pestaña Programador abrimos la etiqueta Insertar e insertamos una barra de desplazamiento


5 – definimos los parámetros de la barra de desplazamiento en la pestaña "control" del menú "formato de control"


Nótese que vinculamos el control con la celda A2. Esto hará que cuando movemos el cursor de la barra, la fecha en la celda G3 se va incrementando y así el resto de las celdas.

Para ejemplificarlo cambiamos la cantidad de días en la duración y podemos ver como al desplazar el cursor de la barra, se van modificando las fechas y actualizando el diagrama




El archivo puede descargarse aquí 
.