Diagramas Gantt con Excel

Todo quien se haya ocupado alguna vez de proyectos sabe qué es un diagrama Gantt. De acuerdo a Wikipedia: el diagrama de Gantt es una popular herramienta gráfica cuyo objetivo es el de mostrar el tiempo de dedicación previsto para diferentes tareas o actividades a lo largo de un tiempo total determinado.

Desde su introducción los diagramas de Gantt se han convertido en una herramienta básica en la dirección de proyectos de todo tipo, con la finalidad de representar las diferentes fases, tareas y actividades programadas como parte de un proyecto o para mostrar una línea de tiempo en las diferentes actividades.

Excel no cuenta con una herramienta propia para desarrollar diagramas Gant. Quien quiera desarrollar un diagrama Gantt profesional, con interdependencias entre actividades y demás elementos, debe usar una herramienta apropiada como MS Project, por ejemplo.

Pero si nuestras aspiraciones son menores y podemos contentarnos con un diagrama Gantt sencillo, podremos hacerlo con Excel.

Hay más de una forma de crear un diagrama Gantt en Excel. Los ejemplos de esta nota se encuentran en este cuaderno.

La más sencilla es ubicando los datos en un rango y luego crear el diagrama usando formato condicional.

Los pasos son los siguientes:

1 – Creamos una tabla con los datos (actividades, fechas de inicio y duración)




2 – Diseñamos un rango que contenga el diagrama Gantt. En nuestro ejemplo, la tabla con los datos ocupa el rango A1:D8. El rango para el diagrama ocupa el rango F1:S8. El rango F2:F8 contiene los nombres de las actividades; el rango G1:S1 contiene las fechas por semana (la primer celda se refiera a la celda B2, las restantes son el resultado de sumar 7 a la celda de la izquierda).

3 – Seleccionamos el rango G2:S8 y abrimos el menú de formato condicional.



En la ventanilla de Condición 1 usamos la opción Fórmula e introducimos esta:
=Y(G$1>=$B2,G$1<$D2)
4 – Para lograr el efecto de colores alternados podemos usar dos condiciones con las siguientes fórmulas:
para la primera condición (color azul) =Y(G$1>=$B2,G$1<$D2,RESIDUO(FILA(),2)=0)
para la segunda condición (color mora) =Y(G$1>=$B2,G$1<$D2,RESIDUO(FILA(),2)=1)


El efecto final es:



Otra alternativa es utilizar los gráficos de Excel para representar el diagrama Gantt. Para lograr esto usaremos el gráfico de tipo barras. Esta técnica requiere usar algunos "trucos".
Los pasos son los siguientes:

1 – Usamos la misma tabla de datos del ejemplo anterior

2 – Seleccionamos el rango A1:C8 (sin incluir la columna "Final"), abrimos el menú de gráficos y seleccionamos el tipo "Barras" subtipo "barra apilada"





3 – El primer truco consiste en hacer desaparecer la serie "comienzo"



Para lograr esto seleccionamos la serie "Comienzo" en el gráfico, abrimos el menú de formato de la serie y seleccionamos Borde--ninguno, Área--ninguna. Esto hará "desaparecer" la barra. También seleccionamos la leyenda y la borramos



4 – Seleccionamos el eje Y (donde aparecen las actividades) y en el menú de formato seleccionamos "Categorías en orden inverso" y "Eje de valores cruza en máxima categoría"



5 – Luego seleccionamos el eje X y lo adaptamos de manera que muestre un rango de fechas apropiado.



Como pueden ver, podemos introducir los valores en formato de fechas.

El formato final de nuestro diagrama es el siguiente



Como pueden ver, este formato es mucho más preciso que el anterior que estaba basado en formato condicional.

Fuentes y enlaces para esta entrada:
Gantt Charts in Microsoft Excel por Jon Peltier
Gantt Charts en MrExcel
Enlaces a notas sobre diagramas Gantt por Jon Peltier
Floating bars tutorial page de Tushar Mehta
Categorías: Graficos_, Varios_

Technorati Tags: Excel, Gantt en Excel

Importar lista de archivos a Excel

Ya vimos que las funciones XLM (macrofunciones Excel 4) nos permiten realizar tareas con fórmulas que de otra manera solo serían posibles con macros.
Una de estas funciones es ARCHIVOS. De acuerdo al archivo de ayuda:

Devuelve una matriz de texto horizontal con los nombres de todos los archivos que se encuentran en el directorio o en la carpeta especificados. Use ARCHIVOS para crear una lista de nombres de archivo sobre los que desea que actúe su macro.

La sintaxis es: ARCHIVOS(directorio)

donde "directorio" especifica los directorios o carpetas que contienen los archivos cuyos nombres se van a devolver.

Un lector del blog me consulta como se puede importar a una hoja de Excel una lista de los archivos de una carpeta.
Supongamos que quiero importar a una hoja de Excel los archivos

Esto la hacemos usando la macrofunción ARCHIVOS (FILES en la versión inglesa).
Los pasos son:
1 - definimos el nombre "Archivos" (Insertar--Nombres--Definir), que contiene esta formula: =ARCHIVOS($A$1).





Prestar atención a la referencia absoluta en la fórmula.

2 - En al celda A1 escribimos:
D:\My Music\Mercedes Sosa\Cantata Sudamericana \*.*

3 - En la celda B1 escribimos la formula: =INDICE(Archivos,FILA())

4 - Copiamos la formula hacia abajo (celdas B2, B3, etc) hasta que recibimos como resultado #REF.

El resultado es el siguiente:




Ahora, seleccionamos todo el rango de la columna B y hacemos Copiar--Pegado especial--Valores, para anular las formulas.

Si queremos usar la fórmula en otras hojas del cuaderno, en el diálogo de definición de nombres, borramos el prefijo Hoja1 en la ventanilla "se refiere a", cuidándonos de dejar el signo "!". De esta manera el nombre Archivos se referirá a la celda A1 de la hoja donde se encuentre, y a la hoja donde fue definido.




En esta entrada hay un enlace para descargar el archivo de ayuda en español.

Categorías: Funciones&Formulas_,

Technorati Tags: Excel

Algo más sobre macrofunciones XLM (Excel 4)

En la anterior nota sobre uso de macrofunciones XLM, vimos que el "truco" para poder usarlas en hojas de cálculo consiste en incluirlas en nombres.

También señalamos la importancia de prestar atención al uso de referencias relativas.

En esa nota puse un enlace al archivo de ayuda con la lista de las funciones en inglés. Aquí se puede descargar el archivo con la lista en español (después de descargar el fichero, abrirlo y guardar el archivo Xlmcr8.hlp).

La función INDICAR.CELDA (GET.CELL en inglés) es, tal vez, la más útil en hojas de cálculo. Como vimos en la nota anterior, su sintaxis es INDICAR.CELDA(tipo, ref).
Tipo es un argumento que indica que información queremos recibir. En nuestro ejemplo usamos el número de argumento 63, que devuelve el color de llenado (de fondo) de la celda. El número 20 nos permitiría, en forma similar, sumar todos los números en un rango que estén en negrita.

Otro uso interesante es con el argumento 53. Este argumento da como resultado, con formato de texto, el contenido de la celda tal y como aparece en pantalla. De esta manera podemos evitar errores de redondeo que surgen dado que Excel efectúa los cálculos con todos los dígitos del número y no sólo con los exhibidos en la pantalla. Un ejemplo se puede ver en el excelente sitio JKP.


Categorías: Funciones&Formulas_

Technorati Tags: Excel

Algo mas sobre archivos residuo y Excel

En la nota de ayer sobre archivos residuo, vimos que al arrastrar el icono del escritorio a una hoja Excel, se genera un objeto incrustado.
Si queremos evitar que la aplicación vinculada se abra al hacer doble clic, todo lo que tenemos que hacer es borrar la fórmula INCRUSTAR que aparece en la barra de fórmulas cuando seleccionamos el objeto.



Al borrar la esta fórmula el objeto incrustado permanece como objeto gráfico solamente.

Categorías: Varios_

Technorati Tags: Excel

Archivos residuos y Excel

Uno de los blogs que visito con cierta regularidad es Unofficial Office Staff de Doug Klipert. Doug publicó ayer una entrada sobre el uso de archivos residuo. Si bien no creo que ningún usuario de Excel vaya a hacer uso intensivo de esta funcionalidad, vale la pena hacer una reseña sobre el tema.

Según la base de conocimientos de Microsoft, un residuo es un archivo que se crea al arrastrar parte de un documento hasta el escritorio. Es decir, podemos seleccionar parte del documento, arrastrar la selección al escritorio de Windows y, más tarde, utilizarlo en otro documento.

Veamos un ejemplo:
Seleccionamos parte de una hoja de Excel y la arrastramos al escritorio de Windows (para lo cual Excel no tiene que estar maximizado)

Abrimos un nuevo cuaderno de Excel y arrastramos el icono del escritorio a nuevo documento



Como podemos ver, el resultado es una "imagen" de la información que seleccionamos, similar al recorte que hacíamos con la cámara fotográfica de Excel. Pero a diferencia de esta, si hacemos un doble clic sobre esta "imagen", se abrirá todo el documento original, incluyendo las fórmulas y los nombres, en un nuevo cuaderno.

El archivo residuo es un objeto OLE especial y no puede ser leído. Sólo podemos arrastrarlo del escritorio a otro documento.

Esta técnica es aplicable también a Word.


Categorías: Varios_

Technorati Tags: Excel

Busqueda exacta en Excel

Supongamos esta lista de palabras en una hoja de Excel



Supongamos ahora que queremos buscar la palabra "trato", no como parte de otra palabra sino exactamente "trato".

Usando el menú Buscar (Ctrl+B), da como resultado todas las celdas donde "trato" aparece de una u otra manera




Para que Excel señale únicamente la celda A2, es decir, realizar una búsqueda exacta, debemos hacer lo siguiente:
Pulsar el botón "Opciones" y en la ventanilla que se abre señalar la opción "Coincidir con el contenido de toda la celda"




Una vez hecho esto, Excel señalará sólo la celda que contiene exactamente la palabra "trato"




Categorías: Varios_

Technorati Tags: Excel

Usando funciones XLM (Excel 4) en hojas de cálculo.

Con anterioridad a la versión 5, Excel utilizaba un lenguaje macro llamado XLM (eXceL Macro, no confundirse con XML - eXtensible Markup Language).
Este lenguaje cuenta con una colección de funciones que pueden ser usadas en hojas de cálculo de Excel. Estas funciones nos permiten realizar cálculos que de otra manera sólo serían posibles utilizando Vba (Visual Basic for Applications, el lenguaje macro de Excel desde la versión 5).

Para utilizar estas funciones debemos incluirlas en nombres. Veamos algunos ejemplos.

Ejemplo 1 – Sumando de acuerdo al color de relleno de la celda

Fuente: JKP Application Development Services

Usamos la función XLM INDICAR.CELDA (Get.Cell en inglés) para extraer el número de índice del color del relleno.





Creamos el nombre Numero_de_Color que contiene esta fórmula: =INDICAR.CELDA(63,Hoja1!A2)

Copiamos el nombre a la celda B2 y al rango E2:E11. En la celda B3 introducimos esta fórmula: =SUMAR.SI($E$2:$E$11,B2,$D$2:$D$11)
Como puede verse, el resultado es la suma de todos los valores con color de relleno verde.

Dado que el cambio de color no fuerza a Excel a recalcular la hoja, debemos pulsar F9 para forzar a Excel a recalcular la fórmula cada vez que cambiemos el color en la celda B2.
Otro método es convertir a nuestra fórmula en volátil agregándole una función volátil que no afecte el resultado: =INDICAR.CELDA(63,Hoja1!A2)+AHORA()*0

Algunos otros parámetros de INDICAR.CELDA
14: da VERDADERO si la celda está bloqueada, o FALSO si no lo está
18: nombre de la fuente (font) usada en la celda
19: tamaño de la fuente, en puntos
48: da VERDADERO si la celda contiene una fórmula o FALSO si contiene una constante
62: da el nombre del cuaderno y la hoja activos en la forma

Al final de la entrada hay un enlace para descargar el archivo de ayuda con todas las fórmulas XLM.

Ejemplo 2 – Evaluar texto como fórmula

Fuente: aporte de KL en el foro Exceluciones

Supongamos el siguiente texto en la celda A1




Usaremos la fórmula XLM EVALUAR (Evaluate en inglés) para usar ese texto como fórmula.
Creamos el nombre LeerFormula que contiene la fórmula =EVALUAR(A1&T(HOY()))

Ponemos el nombre en la celda B1 y obtenemos el resultado



Como en el caso anterior, la expresión "&T(HOY()" fuerza a Excel a recalcular la fórmula.

Actualización - Algunas aclaraciones importantes de KL:

1 - Es ESENCIAL subrayar que antes de definir el nombre se debe seleccionar la celda [B1]. Es decir, estamos jugando con la relatividad de la referencia A1 (!!! sin el dolar) con respecto a la B1.

2 - Ademas, si se quiere usar el truco en todas las hojas del mismo libro, la formula deberia introducirse en el siguiente formato (o sea, precediendo la referencia de la celda de un signo de exclamacion): =EVALUAR(!A1&T(HOY()))


Aquí se puede encontrar información sobre el lenguaje de macros Excel 4.0 (XLM) y descargar el archivo con la lista de funciones.


Categorías: Funciones&Formulas_


Technorati Tags: NAMES

Listas personalizadas en Excel

Una de las muchas funcionalidades de Excel son las listas. Por ejemplo, si introducimos en una celda "enero" y luego arrastramos el mouse apuntando al cuadrado pequeño en la esquina inferior derecha del borde de la celda



veremos que Excel introduce en forma automática los meses subsiguientes: febrero, marzo, abril, etc.

Excel permite crear nuestras propias listas, listas personalizadas. Para hacer esto seguimos los siguientes pasos:

1 – Activamos el menú Herramientas--Opciones. En el diálogo de opciones pulsamos la pestaña "Listas Personalizadas"




2 – En esta ventana podemos agregar o eliminar listas. Listas pueden ser agregadas de dos maneras: introduciendo los valores manualmente en la ventanilla "entradas de lista", o copiándolas de un rango de alguna hoja.

Por ejemplo, si tenemos un rango de celdas con los días de la semana




Abrimos el la ventana de opciones en la pestaña "Listas personalizadas" y señalamos el rango en la ventanilla "importar". Al apretar "importar" los valores del rango aparecen en la ventanilla "entradas de lista" y todo lo uqe queda por hacer es apretar "Aceptar"




Categorías: Varios_

Technorati Tags: Excel

Métodos abreviados en MS Excel para aplicar bordes a celdas

Los métodos abreviados de teclado son "atajos" que nos permiten usar el teclado en lugar del mouse y/o reducir la cantidad de operaciones que debemos hacer para cumplir una tarea.
Personalmente encuentro esto muy conveniente y siempre trato de recordar estos métodos y usarlos en mi trabajo diario.
Algunos de los atajos más populares son Ctrl+C para copiar, Ctrl+V para pegar, Ctrl+N para aplicar formato de negrita, Ctrl+G para guardar el cuaderno activo.

En Daily Dose of Excel, me encuentro con una entrada sobre métodos abreviados de teclado para aplicar bordes a celdas. Esta entrada me dirige al artículo de Microsoft sobre el tema.
El método consiste en abrir el diálogo de formato de celdas con el atajo Ctrl+1 y luego usar distintos atajos para aplicar bordes o desecharlos.




Este artículo tiene un serio problema para el usuario de Excel configurado en español. Las teclas del método abreviado son erróneas. El artículo al ser traducido automáticamente del inglés no se adapta a la configuración de Excel para trabajar en español. Por ejemplo, si usamos el atajo ALT+T propuesto para aplicar el borde superior, no ocurrirá nada.

Los atajos correctos son:







Categorías: Varios_

Technorati Tags: Excel

Enviar e-mail desde Excel

Estaba buscando algunas referencias para una entrada sobre las diferencias entre las funciones REEMPLAZAR y SUSTITUIR, cuando me encontré con un viejo conocido: el excelente complemento (Add-In) de Ron de Bruin y Norman Harper para enviar e-mails directamente desde Excel.
Así que el tema de las funciones quedará para más adelante, y en esta entrada hablaremos sobre este excelente complemento.
Paras los usuarios menos experimentados aclaremos que complementos (Add-Ins) son aplicaciones que permiten extender la funcionalidad de Excel. Así, por ejemplo, este complemento permite enviar rangos, hojas o todo un cuaderno directamente desde Excel.
El complemento desarrollado por Ron de Bruin y Norman Harper tiene dos versiones, una para Outlook u Outlook Express, y otra sólo para Outlook. Esta última tiene más posibilidades y funcionalidad.
Para utilizar este complemento lo primero que tenemos que hacer es descargarlo e instalarlo en alguna carpeta. La mejor forma de saber dónde guardarlo es abrir el el menú Herramientas--Complementos y apretar el botón Examinar, para ver dónde Excel busca los complementos. Alternativamente se puede guardar en cualquier carpeta y crear el vínculo con el botón Examinar.


Al intentar instalarlo podemos toparnos con algunos problemas, como este anuncio




Lo que tenemos que hacer es, simplemente, seguir las instrucciones:


Una vez instalado el complemento, veremos que en el menú Herramientas aparece un nuevo elemento "SendMail".

Este complemento es no solamente útil sino también muy intuitivo en cuanto a su uso. Las posibilidades son diversas, desde enviar cuadernos completos, hojas o rangos.


Se puede descargar también un manual de instrucciones (en inglés), en formato Word o PDF.





Categorías: Varios_

Technorati Tags: Excel

Valores únicos en lista de Validación de Datos – Explicación de las fórmulas

En la entrada sobre valores únicos en una lista de Validación de datos, usamos fórmulas un tanto complicadas. Me han pedido una explicación más detallada sobre las fórmulas, así que aquí va!

Primero analizaremos las fórmulas en uso en las tablas auxiliares.

El rango en la columna C contiene la fórmula

=SI(CONTAR.SI($A$5:A5,A5)=1,A5,"")

En esta fórmula CONTAR.SI cuenta cuantas veces aparece el valor de la celda en la columna A en el rango. Si aparece una sola vez el resultado es el valor de la celda de la misma fila en la columna A. Si aparece más de una vez, el resultado es "blanco".
Con esta fórmula copiamos al rango en la columna C valores únicos de los valores que aparecen en la columna A.

El problema es que esta lista contiene celdas en blanco, y por lo tanto es poco "elegante" para ser usada como referencia para la lista de Validación de Datos.
Nuestra tarea ahora es reordenar la lista en la columna C de manera que los espacios en blanco aparezcan al final de la lista.

Para lograr esto utilizamos una nueva lista auxiliar en la columna D. Lo que queremos es dar un número de orden a las celdas en la columna C que contengan valores y dejar en blanco cuando la celda en la columna C no contenga ningún valor.
Para lograr esto usamos la fórmulas =SI(CELDA("contents",C5)="","",FILA(C5))

La función CELDA da como resultado el contenido de la celda analizada ("contents" es uno de los parámetros posibles; más información se puede obtener en la ayuda on-line de Excel). La función FILA da como resultado el número de fila de la celda. Si la función CELDA da como un resultado distinto de "blanco", el resultado de la fórmula será el número de fila. Este número nos ayudará a reordenar los valores en la lista auxiliar en la columna F.

En la columna F usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

La fórmula INDICE da como resultado el valor del miembro de la matriz indicada (en nuestro caso $C$5:$C$25) que ocupa el lugar indicado por el segundo argumento de la función.

Este segundo argumento utiliza la función COINCIDIR con la función (K.ESIMO.MENOR($D$5:$D$25,FILA()-4) como primer argumento.

Esta función devuelve el k-ésimo menor valor de un conjunto de datos. Una explicación sobre esta función se puede encontrar aquí.

La expresión FILA()-4, el segundo argumento de K.ESIMO.MENOR es la posición, dentro de la matriz de los datos que se van a devolver, determinada a partir del menor de los valores. Como empezamos nuestra lista de la fila 5, restamos 4 para obtener 1 en la primera celda del rango, 2 en la segunda y así sucesivamente.

De esta manera veremos aparecer en la lista en la columna F los valores de acuerdo a su aparición en la columna A.

Para evitar resultados #NUM! cuando la celda en la columna D está en blanco, anidamos la fórmula dentro de una función condicional SI, como explicamos en la entrada sobre el tema.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Excel