Archivo de la categoría: Fórmulas y Macros

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA2

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA (Y FÓRMULAS)

Hace unos días un lector me solicitó una propuesta de diagrama de Gantt, le era indiferente si lo realizaba con fórmulas, macros o incluso gráficos.

Lo cierto es que después de pensarlo varias veces, me decidí por un desarrollo híbrido, es decir, parte con fórmulas y otra parte con código. Pero veamos poco a poco el diseño (siempre es algo subjetivo) que le he propuesto.

Lo fundamental en un diagrama de Gantt es tener una matriz con la información (en la escala temporal que nos interese) en donde podamos indicar el horizonte temporal de nuestros proyectos. En este caso en particular vamos a utilizar el día como unidad de medida y de referencia, de forma que nuestra hoja Excel nos indique en cada columna el día (en letra) del mes y el número del día, así:

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA

En la primera fila de forma manual vamos a usar celdas combinadas para nombrar todo el mes (Enero).

En la segunda fila como una dato auxiliar vamos a colocar la fecha, desde el 01/01/2016 hasta el final (en este archivo he puesto hasta 31/12/2016, un año completo), es sencillo, solo tendréis que poner las dos primeras fechas y arrastrar la información. Esta información luego la podéis ocultar o poner el texto de color blanco para que no se vea.

En la tercera fila recogeremos la primera letra del día que se trate, para esto en una hoja auxiliar “APOYO” (podéis utilizar la ubicación que mejor os venga), indicamos que el 1 se corresponde con el lunes y el 2 con el martes y así hasta el 7 que es viernes. Y en tercera fila introducimos la siguiente fórmula:

=EXTRAE(BUSCARV(DIASEM(G2;2);APOYO!$A$1:$B$7;2;0);1;1)

Con esta fórmula obtenemos el día de la semana DIASEM de la fecha introducida en la segunda fila y elegimos la modalidad 2 (lunes 1 a domingo 7), con esto obtenemos el número del día de la semana, ahora tan solo tenemos que buscar el nombre en letra en la Hoja “Apoyo” y luego extraer el primer carácter.

En la cuarta línea indicaremos el día según la fecha, es decir los días del mes. Es decir función =DIA(G2).

Todo este proceso lo reproducimos hasta diciembre. Y colocamos las rayas en horizontal que más adelante nos servirán para introducir las líneas de colores del diagrama.

Como habéis podido ver, he comenzado el gráfico en la columna “G” y eso se debe a que ahora debemos introducir una serie de campos necesarios para que la macro funcione correctamente:

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA1

En la primera columna indicamos un ID (es igual que sea un número o un nombre, lo importante es que contenga un dato, si no lo tiene la macro no funcionará para esa fila.

En la segunda columna indicaremos la tarea que queremos cuantificar en nuestro diagrama.

En la tercera columna indicaremos la fecha en la que vamos a iniciar la actividad.

En la cuarta columna indicamos la duración (en días)*

En la quinta columna indicamos la fecha fin. * en el momento en que la indiquemos automáticamente el campo DURACIÓN mostrará el número de días entre las dos fechas. Si no indicamos fecha fin, debemos introducir número de días de duración, o de lo contrario la macro no mostrará nada.

Y por último, la sexta columna contiene un campo de comentarios, que se reflejará dentro de las barras de color del diagrama.

Ahora que ya lo tenemos todo, procedemos a incluir la siguiente macro en un módulo estándar:

Sub DiagramaGantt()
'desactivamos actualización de pantalla
Application.ScreenUpdating = False
'para evitar parpadeos en el cursos del ratón lo cambiamos
Application.Cursor = xlNorthwestArrow
'definimos variables
Dim i As Double
Dim j As Double
With ActiveSheet
'indicamos longitud de columnas y filas (con datos)
frow = Application.CountA(.Range("A:A")) + 3
fcol = Application.CountA(.Range("4:4"))
'limpiamos el área del gráfico con color y el contenido de texto cuando actualicemos
.Range("G5:NH" & frow).Interior.Pattern = xlNone
.Range("G5:NH" & frow) = vbNullString
On Error Resume Next
'Iniciamos un doble bucle para "pintar" de azul los días de cada actividad
'teniendo en cuenta el campo duración y fecha
For i = 5 To frow
'si contiene fecha fin calculamos los días entre las fechas
If CDate(.Cells(i, 3).Value) And CDate(.Cells(i, 5).Value) Then
Dias = DateDiff("D", CDate(.Cells(i, 3).Value), CDate(.Cells(i, 5).Value))
.Cells(i, 4).Value = Dias
End If
For j = 5 To fcol
If CDate(.Cells(i, 3).Value) = CDate(.Cells(2, j).Value) And .Cells(i, 4).Value > 0 Then
'color azul de la barra de progreso
.Range(Cells(i, j), .Cells(i, j + .Cells(i, 4))).Interior.Color = RGB(36, 150, 228)
'mayusculas en texto
.Cells(i, j) = UCase(.Cells(i, 6))
'negro en el texto
.Cells(i, j).Font.Color = vbBlack
'resaltado en negrita
.Cells(i, j).Font.Bold = True
End If
Next j
Next i
End With
Application.ScreenUpdating = True
Application.Cursor = xlDefault
End Sub

En la macro estamos haciendo referencia a la hoja activa, y esto es porque ahora debemos incluir la siguiente macro en el módulo de cada hoja:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call DiagramaGantt
End Sub

De manera que no tengamos que estar ejecutando la macro con cada cambio que hacemos, sino que solo con pulsar la hoja, la macro se ejecutará según el evento: Worksheet_SelectionChange(ByVal Target As Range)

Y ya tenemos nuestro diagrama de Gantt perfectamente montado 🙂

CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA2

Antes de acabar me gustaría comentar algunos temas:

El formato del archivo debe ser .xlsm la razón son las columnas, si guardamos como xls el archivo entrará en modo compatibilidad y responderá al número máximo de columnas permitido para esa versión (256 columnas) y como podéis imaginar un año tiene 365 días, de forma que no funcionaría correctamente.

La estructura de los datos, es decir, las columnas y las filas a partir de donde ingresamos la información interactúan con el código, cualquier modificación hará que no funcione correctamente. Tan solo debéis introducir ID, FECHAS Y DURACIÓN y si procede comentario, el resto lo hará el código.

Descarga el archivo de ejemplo pulsando en: CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA

Como post complementario, también puedes visitar este enlace, donde propongo otra alternativa pero con gráficos: Pulsa aquí

 

 

Anuncios
SUMAR CADA N VALOR DE UN RANGO

SUMAR CADA N VALOR DE UN RANGO

No es la primera vez que tengo que recurrir a código cuando necesito realizar alguna tarea a la que las fórmulas no son capaces de llegar. Pero esto no siempre sucede, en algunas ocasiones, fruto de la combinación de funciones podemos llegar a resultados igual de satisfactorios.

Por ejemplo, podemos sumar cada n valor de un rango de celdas, es decir, por ejemplo, sumar el contenido de cada dos celdas.

SUMAR CADA N VALOR DE UN RANGO

En este ejemplo he marcado en azul las celdas que necesito sumar, que son las que van de dos en dos (sin tener en cuenta el encabezado). El resultado debe darnos 156.

Para lograr el cálculo debemos utilizar la siguiente fórmula o función:

{=SUMA(SI(RESIDUO(FILA(INDIRECTO("1:"&CONTARA(A1:A26)))-1;2)=0;A1:A26;""))}

En realidad la clave de esta función matricial se apoya en el uso de la función residuo, condicionando su resultado a que los números del rango (matriz) se consideren o no para la suma. Esto lo podéis observar si aplicáis solo parte de la fórmula pero en otra columna, en la “B”:

{=SI(RESIDUO(FILA(INDIRECTO("1:"&CONTARA($A$1:$A$26)))-1;2)=0;$A$1:$A$26;"")}

Este sería el resultado:

SUMAR CADA N VALOR DE UN RANGO1

Como podéis ver, cuando el resultado de Residuo es cero, es lo mismo que decir que la fila es divisible por ese “n” número definido, en este caso el 2. Al ser cierto, la función “si” condicional, toma ese número en la suma y si no lo es, no lo incluye. En el ejemplo de arriba, simplemente he aplicado parte de fórmula para lo que veáis.

De nuevo os recuerdo que las matrices se introducen: seleccionando la fórmula y luego presionando CTRL + MAYUS + ENTRAR

Aunque no tenía intención, al final no me pude resistir y he reproducido el mismo ejercicio pero con macros. Se puede hacer de muchas formas, pero lo he resuelto con un bucle for next:

Sub SUMAR_N_VALORES()
With Sheets(1)
Dim i As Double
Dim j As Double
fin = Application.CountA(.Range("A:A"))
'Desde la celda 2 hasta fin menos 1 (cabecero) e incrementando de 2 en 2
For i = 2 To fin - 1 Step 2
'Acumulamos en j el resultado de la suma
j = j + i
Next i
'Igualamos valor de celda a resultado
.Cells(2, 3) = j
End With
End Sub

El resultado es exactamente el mismo utilizando tanto la fórmula como la macro:

SUMAR CADA N VALOR DE UN RANGO

Ahora solo queda que lo adaptéis a vuestros proyectos y trabajos y obtengáis los beneficios de trabajar con Excel.

Descarga el archivo de ejemplo pulsando en: SUMAR CADA N VALOR DE UN RANGO

INSERTAR NAVEGADOR WEB EN HOJA EXCEL

Hoy quiero subir una entrada muy interesante y un poco distinta a las anteriores, se trata de la posibilidad de insertar el explorador web en una hoja Excel.

Para empezar a montar nuestro pequeño explorador tenemos que seleccionar la hoja en la que vamos a insertar la herramienta. Será la “hoja1” de un libro cualquiera de Excel.

En la parte superior vamos a colocar un botón de comando del cuadro control de formulario (Pestaña “Programador”>”Insertar”>”Controles de formulario”) y lo vamos a llamar “Navegador”. Seguidamente seleccionamos unas cuantas celdas, en este caso desde “D3” a “K3” y las combinamos, (aquí será donde escribamos la ruta de las páginas web a consultar). Os debería quedar algo así:

Ahora que tenemos la forma, podemos ir al fondo de la cuestión. Para que todo esto funcione debemos insertar el controlador Microsoft Web Browser en la hoja1. Para ello vamos a (Pestaña “Programador”>”Insertar”>”Control activeX”) .Pulsamos en el símbolo de las herramientas que pone más controles. Se nos abrirá un cuadro de diálogo y debemos buscar y seleccionar Microsoft Web Browser:


Una vez que pulsemos y aceptemos, automáticamente se activa la pestaña de “Modo Diseño” y pulsamos en la hoja y seleccionamos el área que queremos para que el explorador muestre los contenidos, una ve que tengamos el área bien definida, desactivamos el “Modo Diseño”.

 
Ahora que tenemos la hoja lista, es el momento de hacer unos cuantos ajustes en la programación. Entramos en Visual Basic y nos vamos a la “hoja1” y escribimos el siguiente código:

Private Sub WebBrowser1_StatusTextChange(ByVal Text As String)
End Sub
Sub Verificarmapa()
'Decimos que el navegador lea la celda D3 de la Hoja1 y nos muestre la web que hemos escrito.
WebBrowser1.Navigate2 Sheets("Hoja1").Range("D3").Value
End Sub

Cerramos VBA y nos vamos a la hoja1 y con el botón derecho del ratón seleccionamos el Botón que habíamos hecho llamado “Navegador”. Elegimos la macro que se llama “Verificarmapa” y ejecutamos. El resultado sería este:

Y esto ha sido todo por hoy. En la próxima entrada trataré algo parecido pero un poco más elaborado, os propondré un calculador de distancias basado en Google Maps.

Como siempre os dejo el archivo para que echéis un vistazo y podáis examinarlo con calma.

Saludos.

Descarga el archivo pulsando enINSERTAR NAVEGADOR

 

ELIMINAR TILDE (ACENTO ORTOGRÁFICO) EN EXCEL

Aunque es recomendable no trabajar con acentos en Excel, muchas veces tenemos bases de datos que contienen palabras acentuadas y que tenemos que cruzar con otra información que no tiene acentos.

La solución a este problema pasa por sacar todos los acentos de la base de datos en cuestión. Para ello se puede hacer de dos formas, o bien mediante fórmula o bien mediante una macro.

La fórmula: SUSTITUIR

Con la función sustituir podremos solventar este problema fácilmente. Para ello vamos a tener en cuenta la siguiente sintaxis:

=sustituir(texto;texto_original;nuevo_texto)

El ejemplo se puede hacer con una lista de nombres. Para sacar los acentos, utilizaremos la siguiente fórmula siguiendo con la sintaxis anterior:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A1;"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U")

Puede suceder que las letras sean minúsculas. En ese caso podremos utilizar la misma fórmula, pero aplicando las minúsculas:

=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(A1;"á";"a");"é";"e");"í";"i");"ó";"o");"ú";"u")

Lamentablemente, la función no permite anidar los diez casos que se pueden dar, mayúsculas y minúsculas. Para ello, podemos pasaremos primero una fórmula y luego la otra. O podemos echar mano de una macro para poder hacerlo de una sola vez.

MACRO: Eliminaracentos()

Sub eliminaracentos()
With Selection
.Replace What:=Chr(225), Replacement:=Chr(97), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' á por a
.Replace What:=Chr(233), Replacement:=Chr(101), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' é por e
.Replace What:=Chr(237), Replacement:=Chr(105), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' í por i
.Replace What:=Chr(243), Replacement:=Chr(111), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' ó por o
.Replace What:=Chr(250), Replacement:=Chr(117), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' ú por u
.Replace What:=Chr(193), Replacement:=Chr(65), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' Á por A
.Replace What:=Chr(201), Replacement:=Chr(69), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' É por E
.Replace What:=Chr(205), Replacement:=Chr(73), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' Í por I
.Replace What:=Chr(211), Replacement:=Chr(79), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' Ó por O
.Replace What:=Chr(218), Replacement:=Chr(85), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True ' Ú por U
End With
End Sub

De esta forma al ejecutar la macro, tendrá en cuenta tanto mayúsculas como minúsculas. La macro funciona por selección. Es decir, seleccionáis toda la columna y luego ejecutáis la macro, automáticamente limpiará todos los acentos de las palabras. En caso de otro tipo de acentuaciones y caracteres, habría que buscar el tipo de carácter y añadirlo a la macro.

Esta macro es muy útil tenerla en una hoja auxiliar para sacar acentos. Algo así:

Y eso es todo, espero que os sea de utilidad esta pequeña entrada de hoy : )

Saludos.

 

Descarga el archivo pulsando en: ELIMINAR ACENTOS