Aprende a Realizar un organigrama automáticamente tomando la información de una base de datos en excel, el organigrama aplica para una empresa y es tipo vertical o jerarquía.
¿Como hacer un organigrama automáticamente con una base de datos de Excel?
Con esta macro nos va permitir realizar lo siguiente:
- Realizar un organigrama de forma Vertical o Jerarquía.
- Realizar un organigrama con detalle
- Realizar un organigrama general.
Revisa el video para que aprendas a utilizar la macro y en la parte final del articulo se encuentra el enlace para que la puedas descargar.
Importante que para utilizar la macro debemos de tener habilitado el menú de programador o desarrollador dependiendo de la versión de excel que tengas. En este CORTO VIDEO te explico como puedes HABILITAR
Para poder ejecutar la macro antes debemos de tener la base de datos de una manera acorde y se debe tener presente lo siguiente.
Como se muestra en la imagen la información no debe tener encabezados y se debe organizar en la columna A el cargo, en la columna B la posición de jerarquía de 1 hasta el n número, mas adelante explico esta parte y en la columna C el nombre.
La posición de jerarquía por poner un ejemplo empresarial el gerente sería el número 1, posteriormente se debe de colocar todos los de una área por ejemplo el área financiera, entonces en la posición 2 sería el coordinador, posición 3 analista financiero y posición 4 auxiliar de cartera, es decir que el auxiliar de cartera depende del analista financiero y el analista financiero del coordinador, luego en el mismo departamento financiero tiene el analista de presupuesto entonces se coloca el número 3, luego sigue el auxiliar facturación y le colocamos el número 4 y quiere decir que el auxiliar de facturación depende del analista de presupuesto y posteriormente al coordinador financiero y con esa misma lógica se debe de construir la base de datos imaginándonos un organigrama.
Posteriormente nos dirigimos a la pestaña de programador o desorrallador y nos vamos para el icono de insertar y luego un botón
Podemos cambiar el nombre de la macro y le decimos nuevo
Luego seleccionamos la macro 1 de bloc de notas que en la parte inferior se encuentra la macro y la pegamos en la ventana de visual basic.
La primera macro consiste en
Nombrar las variables Diseño, borrar, NumCargos y Fin
Dim Diseño As SmartArtLayout
Dim Borrar As Excel.Shape
Dim NumCargos As SmartArtNodes
Dim i, Fin As Double
Con la hoja Organigrama aquí es muy importante cambiar el nombre de la hoja donde vas a utilizar la macro para el organigrama
With Sheets("Organigrama").Select
Si hay un organigrama en la hoja la borramos
For Each Borrar In .Shapes
Borrar.Delete
Next
Creamos el organigrama partiendo siempre de un tipo concreto: /NameandTitleOrganizationalChart (Tipo de organigrama)
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set NumCargos = inserta.SmartArt.AllNodes
Se utiliza la función contar para saber el numero de cargos
Fin = Application.CountA(Sheets(1).Range("A:A"))
Mientras el numero de cargos sea inferior a las unidades del organigrama Seguimos creando cargos
Do While NumCargos.Count < Fin
NumCargos.Add.Promote
Loop
El organigrama se inicia con 5 cargos, si el # cargos es inferior se eliminaran los nodos.
For i = 1 To Fin
Do While NumCargos(i).Level < Sheets(1).Range("B" & i).Value
NumCargos(i).Demote
Loop
Para introducir texto y formato de cada unidad/caja/elemento del organigrama
With NumCargos(i)
.TextFrame2.TextRange.Text = Sheets(1).Range("A" & i)
El texto dentro de cada unidad es igual a la columna A + el contador
.TextFrame2.TextRange.Font.Size = 9 'Tamaño del texto de cada unidad
End With
Next
Cambiamos el diseño del organigrama Jerarquía
For Each Shape In .Shapes
Shape.SmartArt.Layout = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2005/8/layout/hierarchy1")
Aquí formateamos el color del organigrama a un estilo predefinido, puedes ensayar con cualquier número
Shape.SmartArt.Color = Application.SmartArtColors(4)
Aquí formateamos el estilo del organigrama a un estilo predefinido
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles(4)
Situamos el organigrama según necesidades y tamaño
With .Shapes(1)
.Height = 500 'Alto
.Width = 2500 'Ancho
.Top = 100 'Arriba
.Left = 50 'Izquierda
End With
Next
End With
y la MACRO # 2 para crear un organigrama general consiste en lo siguiente
Nombrar las variables Diseño, borrar, NumCargos y Fin
Dim Diseño As SmartArtLayout
Dim Borrar As Excel.Shape
Dim NumCargos As SmartArtNodes
Dim i, Fin As Double
Aquí debemos de cambiar el nombre de la hoja por el nombre donde estará el organigrama
With Sheets("Organigrama").Select
Borramos el organigrama si tenemos en una hoja
For Each Borrar In .Shapes
Borrar.Delete
Next
Creamos el organigrama partiendo siempre de un tipo concreto para este ejemplo es tipo de organigrama General: /NameandTitleOrganizationalChart
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set NumCargos = inserta.SmartArt.AllNodes
Utilizamos la función contar para saber el número de cargos
Fin = Application.CountA(Sheets(1).Range("A:A"))
Si el numero de cargos es inferior a 5 que es el numero predeterminado del organigrama se borraran
Do While NumCargos.Count < Fin
NumCargos.Add.Promote
Loop
For i = 1 To Fin
Do While NumCargos(i).Level < Sheets(1).Range("B" & i).Value
NumCargos(i).Demote
Loop
La siguiente parte es para introducir texto y formato de cada cargo en cada elemento del organigrama
With NumCargos(i)
.TextFrame2.TextRange.Text = Sheets(1).Range("A" & i)
Asignar el tamaño del texto
.TextFrame2.TextRange.Font.Size = 9
Borramos el organigrama si tenemos en una hoja
For Each Borrar In .Shapes
Borrar.Delete
Next
Creamos el organigrama partiendo siempre de un tipo concreto para este ejemplo es tipo de organigrama General: /NameandTitleOrganizationalChart
Set Diseño = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart")
Set inserta = .Shapes.AddSmartArt(Diseño)
Set NumCargos = inserta.SmartArt.AllNodes
Utilizamos la función contar para saber el número de cargos
Fin = Application.CountA(Sheets(1).Range("A:A"))
Si el numero de cargos es inferior a 5 que es el numero predeterminado del organigrama se borraran
Do While NumCargos.Count < Fin
NumCargos.Add.Promote
Loop
For i = 1 To Fin
Do While NumCargos(i).Level < Sheets(1).Range("B" & i).Value
NumCargos(i).Demote
Loop
La siguiente parte es para introducir texto y formato de cada cargo en cada elemento del organigrama
With NumCargos(i)
.TextFrame2.TextRange.Text = Sheets(1).Range("A" & i)
Asignar el tamaño del texto
.TextFrame2.TextRange.Font.Size = 9
Asignar Color del texto de cada unidad, el color es el rojo
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(139, 0, 0)
'Asignar al texto negrita
.Shapes.Item(1).TextEffect.FontBold = msoTrue
Asignar color de fondo de cada unidad
.Shapes.Fill.ForeColor.RGB = vbWhite
Asignar color del borde de cada unidad
.Shapes.Line.BackColor.RGB = vbBlack
End With
Para escribir en la segunda caja de cada cargo en el organigrama, se asigna el tipo de letra, que el texto sea centrado y el tamaño de la letra
With NumCargos(i).Shapes.Item(2)
.TextFrame2.TextRange = Sheets(1).Range("C" & i)
.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 139)
.TextEffect.Alignment = msoText
.TextEffect.FontSize = 10 'Tamaño de letra
End With
Next
For Each Shape In .Shapes
Shape.SmartArt.Layout = Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart")
'Si deseamos un formato de color predeterminado, solo debemos quitar la comilla simple (') de la sentencia que sigue
'y elegir un formato (cambiando el número de referencia)
'Shape.SmartArt.Color = Application.SmartArtColors(7) '<-- aquí formateamos el color del organigrama a un estilo predefinido
Situamos el organigrama según necesidades y tamaño
With .Shapes(1)
.Height = 500 'Alto
.Width = 1800 'Ancho
.Top = 100 'Arriba
.Left = 50 'Izquierda
End With
Next
End With
En el siguiente ENLACE puedes DESCARGAR la macro #1 para organigrama de detalle en excel
Seguramente quieres sabes más acerca de Excel, no te olvides darte una vuelta por las siguientes secciones disponibles para ti de manera gratuita:
