TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3: ANÁLISIS DE LOS DATOS

Hola a todos!. Espero que todo vaya bien 🙂

Hoy vamos a seguir con la serie de post dedicados a trabajar con grandes bases de datos en Excel. Y en esta ocasión hablaremos sobre el análisis de la información.

Al igual que en las dos partes anteriores, usaremos los datos que estamos usando como ejemplo: Encuesta Europea de Salud en España y los dos post anteriores que podéis consultar son los siguientes:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 1: IMPORTAR LA INFORMACIÓN

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 2: EXTRAER LA INFORMACIÓN

Bien, siguiendo con nuestros ejercicios, llegamos al momento de buscar y obtener información relevante y útil en los datos, es decir, al análisis de la información.

Ni que decir tiene, que aquí podemos aplicar cientos de técnicas, cada una con indicadores y métodos diferentes. Así que voy a elegir una función para realizar un ejemplo complejo de obtener información y mostrarla con gráficos.

La función que voy a desarrollar en VBA es el Coeficiente de Correlación que como veremos, nos va a servir para comprobar si existe relación entre dos variables, y a su vez determinar el tipo de relación (si existe). Esta función necesita 2 matrices (variables) para poder ser calculada, y en nuestro ejemplo, me ha parecido interesante usar la edad y “Consumo de tabaco”, ambas variable se corresponden en nuestros datos con EDADa Y V121 respectivamente.

Una vez que hemos extraído la información seleccionando los datos en el listbox de la primera hoja, tendremos la siguiente información en la hoja “QUERY” y vamos a aprovechar para añadir otra hoja y nombrarla como “CORRELACIONES”.

Ahora que tenemos la estructura preparada, vamos a incluir esta serie de macros en un módulo estándar. Son varias macros, pero en realidad ejecutamos la primera, dado que realizamos varios “Call” al resto de macros. Creo que así se comprende de forma más clara la funcionalidad del código.

Se trata de una macro con cierto nivel de complejidad, así que la iremos viendo poco a poco, estas son las macros:

En primer lugar vamos a ejecutar esta macro que en su proceso va a llamar (“Call”) a otras macros para completar toda la instrucción. Es la macro principal que realiza una conexión ADO y obtiene ordenamos los datos para poder aplicar el coeficiente de correlación entre las diferentes variables.

Sub CONEXION_SQL_CORRELACION()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, Final As Integer, i As Integer, j As Variant, Comodin As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de correlaciones anteriores
Sheets("CORRELACIONES").Select
With Sheets("CORRELACIONES")
'Eliminamos imágenes y correlaciones anteriores
Call ELIMINAR_IMAGENES
'Creamos variable para conocer el número más alto del rango indicado
Final = Application.WorksheetFunction.Max(Sheets("QUERY").Range("B:B"))
'Creamos un bucle para generar tantas consultas SQL como máximo sea el rango.
For j = 1 To Final + 1
'Creamos un caracter comodín para mostrar todos los items.
Comodin = "'%" & "%'"
'Cuando superemos el máximo de valores, el valor +1 será el comodín
If j = Final + 1 Then j = Comodin
'Eliminamos datos de la consutla SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:C" & Fin).ClearContents
'Realizamos consulta SQL, en este caso son dos variables, la edad y el habito de fumar según varias preguntas
obSQL = "SELECT [QUERY$].[EDADa], [QUERY$].[V121], COUNT ([QUERY$].[V121]) AS CUENTA " & _
"FROM [QUERY$] " & _
"WHERE [QUERY$].[V121] like " & j & " " & _
"GROUP BY [QUERY$].[EDADa], [QUERY$].[V121] " & _
"ORDER BY [QUERY$].[V121]"
MiLibro = ActiveWorkbook.Name
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pegamos datos y añadimos encabezados
Do Until Dataread.EOF
Dataread.MoveFirst
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
Loop
'Si el caracter no es el comodín, realizamos la correlación de los datos
If j <> Comodin Then
Call CORRELACION
End If
'Indicamos que no se muestre el error cuando pasamos la variable string
'en el último ciclo del bucle
On Error Resume Next
Next
On Error GoTo 0
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
'Movemos las imagenes a la altura de la celda M3
.Pictures.Select
Selection.ShapeRange.IncrementTop -368
.Range("M3").Select
End With
Application.ScreenUpdating = True
End Sub

Con esta macro, realizamos la correlación entre la edad y cada pregunta realizada sobre la frecuencia de consumo de tabaco. Se ejecuta con la macro CONEXION_SQL_CORRELACION.

Sub CORRELACION()
'Definimos variables
Dim CORRELACION As Double, D As Integer, A As Integer
Dim i As Integer, Fin As Integer, Final As Integer
With Sheets("CORRELACIONES")
Final = Application.WorksheetFunction.Max(Sheets("QUERY").Range("B:B"))
Fin = Application.CountA(.Range("A:A"))
.Cells(1, 5) = "ID"
.Cells(1, 6) = "CORRELACION"
.Cells(1, 7) = "R2"
'Con un bucle realizamos la correlación hasta el máximo de rango indicado en "Final"
For i = 1 To Final
'Evitamos errores cuando con los datos no sea posible extraer correlación
On Error Resume Next
CORRELACION = Application.WorksheetFunction.Correl(.Range("A2:A" & Fin), .Range("C2:C" & Fin))
On Error GoTo 0
'Mostramos ID (pregunta de la encuesta)
'Mostramos correlación

If .Cells(2, 2) = i Then
D = Application.CountA(.Range("E:E"))
A = D + 1
.Cells(A, 5) = .Cells(2, 2)
.Cells(A, 6) = CORRELACION
'Generamos gráficos
Call GRAFICO_IMAGEN
End If
Next
End With
End Sub

Desde la macro anterior llamamos a esta otra macro, que generará cada gráfico después de haber realizado cada correlación por Item. Una vez realizado el gráfico, se pasa a imagen y se borra el gráfico. Se ejecuta con la macro “CORRELACION”

Sub GRAFICO_IMAGEN()
'Declaramos variables
Dim D As Integer, X As Double, nITEM As String, r2 As Double
Dim id1 As String, id2 As String
With Sheets("CORRELACIONES")
'Generamos gráficos de dispersión, los configuramos
'y los pasamos a imagenes y eliminamos el gráfico.
D = Application.CountA(.Range("E:E"))
X = Round(D * 30 / 2, 0)
.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range("A:A,C:C")
.ChartObjects.Width = 320
.ChartObjects.Height = 190
'Adaptamos el tipo de puntos en la nube de puntos y tamaño
With ActiveChart.SeriesCollection(1)
.Select
Selection.MarkerStyle = 8
Selection.MarkerSize = 3
ActiveChart.PlotArea.Select
.Trendlines.Add
.Trendlines(1).Select
Selection.DisplayEquation = True
Selection.DisplayRSquared = True
Selection.NameIsAuto = True
'Seleccionamos línea de tendencia Polinomial
'de grado 3, que es el que mejor se ajusta
'a nuestros datos
With Selection
.Type = xlPolynomial
.Order = 3
End With
'Movemos la fórmula y R2 arriba a la derecha
.Trendlines(1).DataLabel.Select
Selection.Left = 178
Selection.Top = 34
End With
'Eliminamos lineas divisorias horizontales
ActiveChart.Axes(xlValue).MajorGridlines.Select
Selection.Delete
'Nombramos el gráfico
nITEM = .Cells(2, 2)
ActiveChart.ChartTitle.Text = nITEM
'Obtenemos Rcuadrado y se aplicamos su raiz cuadrada para
'obtener la correlación correcta

W = ActiveChart.SeriesCollection(1).Trendlines(1).DataLabel.Text
id1 = InStr(W, "R²")
id2 = InStr(id1, W, "=")
r2 = Trim(Mid(W, id2 + 1))
Sheets("CORRELACIONES").Cells(D, 7) = Round(r2, 2)
'Pasamos el gráfico a imagen
.ChartObjects(1).Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
.Range("M" & X).Select
.Pictures.Paste.Select
.ChartObjects(1).Delete
End With
End Sub

Con esta macro eliminamos todas las imágenes de tipo “Picture” en la hoja. Se ejecuta con la macro CONEXION_SQL_CORRELACION.

Sub ELIMINAR_IMAGENES()
Dim Shape As Excel.Shapes, Fin As Integer
'Eliminamos todas las imagenes de la hoja CORRELACIONES
With Sheets("CORRELACIONES")
Fin = Application.CountA(.Range("A:A"))
For Each Shapes In .Shapes
With Shapes
If .Type = 13 Then
.Delete
End If
End With
Next
If Fin > 0 Then .Range("E2:G" & Fin).ClearContents
End With
End Sub

En rojo os he marcado aquellas macros que ejecutamos con un procedimiento “Call”. Imaginad que incorporamos todas las macros en una sola (sería demasiado confuso).

Recordamos las preguntas de la encuesta sobre hábitos y consumo de tabaco:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS

Pues bien, este es el resultado de realizar la correlación y generar los gráficos de puntos (pulsad en el gráfico para hacer zoom en la imagen)

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS2

Como podéis observar, en los casos 1, 2, 3 y 4 existe una relación entre ambas variables, NO es una relación lineal (tal y como podéis ver) y además el R cuadrado que mostramos en el gráfico NO se corresponde con la correlación obtenida cuando hemos aplicado el coeficiente de Correlación.

Por lo tanto, si bien podemos apreciar que existe relacion entre las 4 primeras preguntas, no se puede tener como referencia el coeficiente de correlación que hemos calculado en la columna “CORRELACIÓN”, dado que los datos no son lineales y, por ejemplo, en la primera pregunta vemos una correlación de -0.45 cuando la correlación debería ser muy superior.

Para poder obtener la relación correcta, deberíamos calcular la “Razón de Correlación” que se aplica a relaciones no lineales, en este caso curvilíneas. Pero si os habéis fijado, en cada uno de los gráficos tenemos, por una parte la fórmula utilizada para calcular la nube de puntos y por otra el Coeficiente de Determinación o R cuadrado.

R2 es un índice estadístico que establece una medida del grado de asociación lineal entre la variable dependiente y la variable independiente, concretamente entre la variable dependiente y la recta de regresión estimada (o en este caso, la curva de regresión estimada).

Teniendo esto en cuenta y dado que el coeficiente de correlación que hemos calculado no podemos tenerlo en cuenta porque que se trata de datos no lineales, utilizaremos como índice estadístico el R2 para explicar los gráficos. En el primer gráfico, que se corresponde con la primera respuesta “SÍ, FUMO A DIARIO”. Podemos observar como R2 es de un 90% y podemos ver como la nube de punto se agrupa en torno a la línea de tendencia.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_PRIMERA PREGUNTA
Según este gráfico, podemos establecer que los casos de personas que afirman fumar todos los días aumentan a medida que su edad aumenta, pero descienden a medida que la edad pasa de un determinado umbral, (a partir de los 70 años).

En el segunda gráfico nos encontramos con que la nube de puntos no está tan cohesionada entorno a la linea de tendencia, también nos lo indica R2 con un 77%.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_SEGUNDA PREGUNTA
Aquí la respuesta es de “SÍ FUMO, PERO NO A DIARIO”, aunque nos encontramos en que el número de casos en determinadas edades rompe con la tendencia y aleja los puntos. Es decir, que en algunos casos la edad no puede explicar que una persona fumara antes y ahora no lo haga (existen otras variables que pueden explicar esto: enfermedades, percepción social … etc. Aunque la relación entre variables es significativa.

En la tercera pregunta, volvemos a los índices de la primera pregunta, R2 es de un 90% (podemos comprobarlo en el gráfico).

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_TERCERA PREGUNTA

La respuesta es, “NO FUMO ACTUALMENTE, PERO HE FUMADO ANTES”. Se trata de ex fumadores, de nuevo la mayor cantidad de casos se encuentra en edades centrales, siendo en las edades “extremas” donde se encuentra el menor número de casos.

En el caso de la cuarta respuesta, “NO FUMO NI HE FUMADO NUNCA DE MANERA HABITUAL”.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS_CUARTA PREGUNTA

Existe una menor relación entre ambas variables, es decir, que la edad no explicar de forma rotunda que las personas no fumen ni hayan fumado, es decir, existen otras variables que explicarían esta respuesta respecto al hábito de fumar.

Para los casos 8 y 9, NO SABE y NO CONTESTA aunque hemos calculado la correlación, R2 y el gráfico, no son representativos estadísticamente, y no los tendremos en cuenta.

Finalmente sacaremos de cada gráfico el dato de los coeficientes de determinación y los pondremos al lado de las correlaciones que hemos calculado y que no nos sirven.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 3 ANALISIS DE LOS DATOS3

Podríamos obtener la misma información, pero introduciendo los datos de hábitos de alcohol (o cualquier otra temática). No he automatizado la parte en la que obtenemos la información y generamos los gráficos y las correlaciones, si decidís actualizar otra información, debéis indicarlo en la cadena SQL:

obSQL = "SELECT [QUERY$].[EDADa], [QUERY$].[V121], COUNT ([QUERY$].[V121]) AS CUENTA " & _
"FROM [QUERY$] " & _
"WHERE [QUERY$].[V121] like " & j & " " & _
"GROUP BY [QUERY$].[EDADa], [QUERY$].[V121] " & _
"ORDER BY [QUERY$].[V121]"

Y en esta parte podríamos seguir jugando con otros indicadores estadísticos, otras técnicas, otros datos, etc. Pero creo que como ejemplo es suficiente.

Podríamos establecer nuevas variables independientes que nos ayuden a entender la relación, como el cambio de percepción de este tipo de hábito, las enfermedades, el gasto, etc, que sin ninguna duda juntas, explicarían el numero de casos por edad en cada pregunta. Pero como podéis ver, ¡esta investigación no ha hecho más que empezar!!

Pueden parece macros demasiado complejas, pero en realidad no lo son, si observáis los procesos con detenimiento podréis visualizar perfectamente el funcionamiento de la macro.

Interesante el proceso para obtener el R2 de los gráficos, también la utilización de la correlación como primera alternativa (que en caso de ser una relación lineal serviría perfectamente).

He dejado el archivo TXT importado en el Excel por eso tiene 18 mb. Para obtener el TXT solo tenéis que visitar alguno de los post anteriores.

Descarga el archivo de ejemplo pulsando en: TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. ANÁLISIS DE LA INFORMACIÓN

Anuncios

GENERAR UN GRÁFICO CALIBRADOR DE PROCESOS

Hola a todos!. ¿Qué tal estáis?, espero que bien.

Llevo ya tiempo sin subir ninguna actualización a Excel Signum, pero es que estos días he tenido bastante de todo y muy poco de tiempo!!.

La entrada de hoy va a ser sobre gráficos, quizás una temática de Excel que en este blog, orientado básicamente a programación, no he tratado demasiado. En el post de hoy quiero tratar un tipo de gráfico en particular, mejor dicho, la confección de un gráfico para un tipo de dato en particular, esto es, un gráfico al que se suele denominar “Calibrador de Procesos”.

Para resumir de una forma adecuada lo que es, no hay mejor forma que hacerlo mediante un ejemplo:

Imaginad que somos agentes comerciales de una determinada empresa y cada mes tenemos que reportar un informe de ventas a nuestros superiores. Hemos decidido que mostraríamos los resultados en % logrado de un máximo de un 100%. Pero como queremos que nuestro informe sea más visual y profesional, vamos a incluir un gráfico que muestre el avance de nuestros objetivos. Dado que estamos en la primera semana del mes, hemos conseguido un 38% de logro, (vamos muy bien!!) y acompañamos los resultado con este gráfico:

grafico-calibrador

Como podéis ver, el formato es parecido al velocímetro de un coche, donde vamos marcando el avance de los resultado de forma gráfica.

Para realizar este gráfico, debemos trabajar previamente una serie de datos en nuestra hoja Excel:

En una celda, vamos a incluir el % que queremos ver reflejado, en este caso el 38% y la celda será la B1, es dato lo iremos actualizando, bien de forma automática con una fórmula o bien manualmente.

Ahora debemos incluir los tres datos siguientes, que quedarán fijos y que se actualizarán automáticamente y serán la fuente de datos del gráfico:

En una celda (B4) incluiremos la siguiente fórmula: =MIN(B1;100%)/2 
con esta fórmula vamos a determinar el % de logro, lo que llevamos realizado. Por ello de los dos datos incluido en la función, donde 100% es el máximo, mostramos el mínimo, que es el dato en B1, es decir, el 38% y lo dividimos entre 2, dado que estamos trabajando con la mitad de un gráfico circular.

En la siguiente celda (B5) obtendremos lo que nos falta de nuestros objetivos y esto lo haremos restando el resultado de B4 menos el 50% que hemos insertado en la celda B6.

Esto es lo que deberíais tener en la hoja:

grafico-calibrador1

El hecho de tomar solo el 50% es que solo vamos a utilizar la mitad del gráfico circular, la otra mitad la vamos a ocultar. Ahora que tenemos los datos, vamos a por el gráfico:

Seleccionamos B4:B6 e insertamos un gráfico circular:

grafico-calibrador5

Como podéis apreciar, vemos que el gráfico debería estar colocado con la parte verde, que representa el 50% que queremos ocultar en la parte inferior, y sin embargo está en la izquierda.

Para resolver este problema, seleccionamos el gráfico y pulsamos en “Formato de punto de datos”, una vez en la nueva ventana, en opciones de serie, damos 270 puntos de giro y aceptamos.

grafico-calibrador3

Ahora  que ya está correctamente orientado el siguiente paso será ocultar la parte del 50%, para ellos vamos a pulsar sobre el 50% que queremos ocultar y de nuevo “Formato de punto de datos” y en “Relleno” marcamos “Sin relleno“:

grafico-calibrador2

Y ya está, ya tenemos casi acabado nuestro gráfico. Ahora solo queda ponerle un título y utilizando un cuadro de texto que vamos a vincular a la celda B1 mostraremos siempre el % al lado del título, así:

grafico-calibrador

Ha sido un post muy sencillo y donde en la web podréis encontrar multitud de ejemplos. Ahora solo queda que le deis un formato adecuado y realicéis el mismo proceso para el resto de objetivos, os quedará un gran informe.

Y esto ha sido todo, os dejo como siempre el archivo de ejemplo, espero que os sea de utilidad.

Descarga el archivo de ejemplo pulsando en: GENERAR UN GRÁFICO CALIBRADOR DE PROCESOS

 

OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RÁPIDOS DE SMARTART PARA VBA

Hace unos meses escribí un post acerca de las grandes posibilidades que ofrece el uso de SmartArt en vba: GENERAR ORGANIGRAMA EN SMARTART CON VBA EN EXCEL

Sin embargo y dado que estoy trabajando actualmente con otra entrada relacionada con esta temática, creo que es necesario hacer un post sobre los nombres y los ID de los diseños, los colores y los estilos rápidos que solemos utilizar en SmarArt y que son fundamentales para realizar un buen trabajo.

En la macro del post de los organigramas veíamos varias líneas de código que hacían referencia por un lado al DISEÑO:

Application.SmartArtLayouts("urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart")

Por ejemplo, este hace referencia a un Organigrama con Título y Nombre, y hacemos referencia a su ID: "urn:microsoft.com/office/officeart/2008/layout/NameandTitleOrganizationalChart"

Pero también podríamos usar su código, que en Excel 2016 es el 89 y su nombre es Organigrama con Nombres y Puestos. Es decir que en Excel 2016 funcionaría perfectamente también así: Application.SmartArtLayouts(89)

Efectivamente, el ID siempre será el mismo en todas las versiones, mientras que el nombre (número clave) cambiará según versión. Por ese motivo siempre es más recomendable usar el ID que el nombre (a no ser que tengamos la certeza que todos los equipos en los que se ejecutará el código tengan la misma versión de Excel).

Con los colores y con los estilos rápidos sucede lo mismo, por ejemplo:

Colores:
Shape.SmartArt.Color = Application.SmartArtColors("urn:microsoft.com/office/officeart/2005/8/colors/accent0_1") 
y que podemos hacer referencia al nombre y en 2016 sería Contorno Oscuro 1 el 1, es decir: Shape.SmartArt.Color = Application.SmartArtColors(1)

Diseño Rápido:
Shape.SmartArt.QuickStyle = Application.SmartArtQuickStyles("urn:microsoft.com/office/officeart/2005/8/quickstyle/simple3") que se correspondería con el nombre Efecto Sutil código 1, y por lo tanto nos valdría poner: Shape.SmartArt.QuickStyle =Application.SmartArtQuickStyles(3)

Una vez comentado esto, os dejo una macro con la que podemos obtener tanto los nombres con los ID de cada uno de los elementos que hemos visto. De esta forma podréis obtener esta información para la versión con la que vayáis a trabajar. Este sería el código para los ID:

Sub SMARTART_ID()
Dim i As Double
With Sheets(2)
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then Range("A1:C" & Fin).ClearContents
'Obtenemos los id de todos los diseños
Layaut = Application.SmartArtLayouts.Count
For i = 1 To Layaut
.Cells(1, 1) = "DISEÑO"
.Cells(i + 1, 1) = i & ": " & Application.SmartArtLayouts(i).ID
Next
'Obtenemos los ID de todos los colores
Colors = Application.SmartArtColors.Count
For i = 1 To Colors
.Cells(1, 2) = "COLORES"
.Cells(i + 1, 2) = i & ": " & Application.SmartArtColors(i).ID
Next
'Obtenemos el ID de todos los estilos rápidos
Styles = Application.SmartArtQuickStyles.Count
For i = 1 To Styles
.Cells(1, 3) = "ESTILOS RÁPIDOS"
.Cells(i + 1, 3) = i & ": " & Application.SmartArtQuickStyles(i).ID
Next
End With
End Sub

Para obtener los nombre solo es necesario sustituir la palabra .ID por .Name. De todas formas os dejo las dos macros en el archivo adjunto. Una vez que ejecutéis cada botón, se mostrará toda la información, en la pestaña 1 los Nombres (con su clave) y en la 2 los ID.

Listado con los ID:

OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RAPIDOS DE SMARTART PARA VBA

Listado con el código y el nombre:

OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RAPIDOS DE SMARTART PARA VBA1

Como siempre, espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: OBTENER NOMBRE E ID DE LOS DISEÑOS, COLORES Y ESTILOS RÁPIDOS DE SMARTART PARA VBA

GENERAR DIAGRAMA DE GANTT EN UN GRÁFICO CON VBA

Hace unas semanas escribí un post acerca de cómo confeccionar un diagrama de Gantt usando únicamente nuestra hoja Excel, fórmulas y vba: CONFECCIONAR DIAGRAMA DE GANTT EN EXCEL CON VBA (Y FÓRMULAS)

Siguiendo con esta temática, y dadas algunas consultas que he recibido, donde me preguntaban si podría realizarse este mismo trabajo pero empleado un gráfico (de barras) donde se pueda mostrar el tiempo consumido y el tiempo pendiente, me he decidido a realizar otro post complementario del anterior respondiendo a esas consultas.

Veamos, vamos a seguir con mismo ejemplo de proyecto que el post anterior, pero explicaremos dos maneras de realizar el ejemplo, uno donde tendremos que introducir los datos manualmente para tener actualizado nuestro diagrama y otro donde una vez introducidas las fechas programadas, Excel lo hará todo automáticamente.

Primero os mostraré la parte de fórmulas (en la hoja) y luego vemos la macro, que será la misma para ambos casos.

Para el ejemplo de forma manual vamos a realizar en nuestra hoja el siguiente cuadro, con cuatro columnas: Detalle de las tareas, fecha de inicio, tiempo consumido (días) y tiempo restante (días):

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA1

Para este tipo de informe, vamos a necesitar que cuando un valor sea “0” no aparezca, que quede en blanco (en las columnas de tiempo consumido y tiempo restante). Esto lo podemos hacer con una macro, pero me ha parecido interesante introducir un pequeño truco para lograrlo de una forma igual de eficiente. Tan solo tenéis que seleccionar las dos columnas y en formato de celda > personalizada > introducís el siguiente formato: #"" de esta manera el cero no aparecerá.

El resultado, una vez aplicada la macro sería este:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA

Como podéis observar, aquí seremos nosotros los encargados en ir modificando los tiempos de forma manual, es decir que a medida que avance el proyecto el tiempo consumido tenderá a aumentar y el tiempo restante a disminuir, de manera que cuando finalicemos todas las barras de progreso serán de color azul.

Sin duda, el tener que ir modificando los datos de forma manual puede llegar a ser (si el proyecto es muy grande) en un trabajo bastante tedioso, para ello, realizaremos las siguientes modificaciones añadiendo algunas columnas y fórmulas de apoyo. Esta es la segunda forma que os comentaba anteriormente:

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA2

Añadimos una columna para determinar la fecha final estimada.
Añadimos otra columna denominada “Control” en la que vamos a introducir la siguiente fórmula:
=SI(E2<HOY();E2;HOY()) de manera que si la fecha fin es menor que hoy() ponemos la fecha fin, de lo contrario indicamos la fecha actual

Añadimos otra columna denominada “Tiempo Total” indicando el tiempo en días desde la fecha inicio hasta la fecha fin, con la siguiente fórmula:
=SIFECHA(B2;E2;"D")

En la columna de “Tiempo Consumido” introducimos la siguiente fórmula:
=SIFECHA(B2;F2;"D") que es la diferencia en días desde la fecha de inicio a la fecha de la columna “Control”, y nos devolverá los días que hemos consumido hasta hoy().

En la columna “Tiempo Restante” introducimos la siguiente fórmula:
=SI(C2>G2;"0";G2-C2) donde si el Tiempo Consumido es mayor que el Tiempo Total ponemos un cero (pero al tener formateada la columna aparecerá vacío), y si no lo es extraemos la diferencia en días entre el tiempo Consumido y el Tiempo Total, obteniendo el tiempo restante.

Puede parecer un poco complejo, pero el resultado es interesante, ya que ahora siempre tendremos actualizado el progreso de nuestro proyecto al día en el que nos encontramos. Si se producen modificaciones, tan solo tendríamos que modificar la fecha fin a mano y listo.

Si confeccionáis el proyecto a futuro, el gráfico os saldrá completamente en blanco (es obvio) ya que todavía no habéis empezado, y en el momento de finalizar estará completamente en azul.

GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA3

Esta imagen dependiendo del momento en el que descarguéis el archivo no se corresponderá con lo que estáis viendo ahora, ya que los días han pasado y seguramente esté todo en azul.

Ahora os dejo la macro comentada, que debéis pegar en un módulo estándar:

Sub Gantt()
'Definimos variables
Dim Chart As Excel.ChartObject
Dim gGrafic As Chart
'Con la hoja activa
With ActiveSheet
'Si existe algún tipo de gráfico, lo borramos
For Each Chart In .ChartObjects
Chart.Delete
Next
'Guardamos longitud de datos
fin = Application.CountA(.Range("A:A"))
'insertamos gráfico
.Shapes.AddChart.Select
'Seleccionamos área de gráfico
ActiveChart.ChartArea.Select
With ActiveChart
'Configuramos gráfico con ChartWizard
.ChartWizard Source:=ActiveSheet.Range("A1:D" & fin), Gallery:=xlBar, Format:=3, PlotBy:=xlColumns, CategoryLabels:=1, SeriesLabels:=1
'Indicamos posición del gráfico
.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top
'Indicamos el largo del gráfico
.ChartArea.Width = 800
'Indicamos el ancho
.ChartArea.Height = 200
'Ancho de intervalo que sea igual a cero
'de forma que las barras ocupen todo el ancho
.ChartGroups(1).GapWidth = 0
'La primera colección es la columna de las fechas de inicio
'debemos hacerlas invisibles en el gráfico, y lo configuramos así:
With .SeriesCollection(1)
With .Border
.LineStyle = xlNone
End With
.InvertIfNegative = True
.Interior.ColorIndex = xlNone
End With
'la segunda colección (tiempo consumido)
With .SeriesCollection(2)
'Damos color azul (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(0, 153, 255)
'Mostramos etiquetas
.ApplyDataLabels
End With
'la tercera colección (tiempo restante)
With .SeriesCollection(3)
'Damos color rojo (dejo formato RGB para mayor elección de color)
.Interior.Color = RGB(255, 0, 0)
'Mostramos etiquetas
.ApplyDataLabels
End With
'Eje de los valores
With .Axes(xlValue)
'Indicamos la fecha mínima
.MinimumScale = Application.WorksheetFunction.Min(ActiveSheet.Range("B2:B" & fin))
'Ocultamos las barras divisorias principales en vertical
.HasMajorGridlines = False
End With
'Eje de categorías
With .Axes(xlCategory)
'Invertimos el orden
.ReversePlotOrder = True
'Mostramos las barras divisorias principales en horizontal
.HasMajorGridlines = True
End With
End With
End With
End Sub

Además en el módulo de cada hoja que contenga los datos, debéis pegar lo siguiente, así con cada cambio que hagáis en la hoja, los datos se actualizarán.

Private Sub Worksheet_Change(ByVal Target As Range)
Call Gantt
End Sub

También podéis utilizar solo la macro principal y vincular a un botón en la hoja, pero debéis cambiar activesheet por la hoja en la que tengáis los datos.

Por supuesto, el gráfico lo podéis situar en el lugar de la hoja que mejor consideréis, la instrucción está en estas líneas de la macro, donde situamos el gráfico según la posición de las celdas, en este caso fila 11, columna 1.

.ChartArea.Left = Sheets(2).Cells(11, 1).Left
.ChartArea.Top = Sheets(2).Cells(11, 1).Top

La clave de todo este proceso consiste en ocultar (transparentes) las barras que hacen referencia a las fechas de inicio y dar color a las otras dos columnas (además de invertir posición y configurar el ancho).

Y aquí finalizamos este pequeño ejercicio. Sin duda una buena opción para controlar diariamente y de forma automática el progreso de nuestros proyectos.

Espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: GENERAR DIAGRAMA DE GANTT EN UN GRAFICO CON VBA

 

CONFECCIONAR UN GRÁFICO PIRAMIDAL

Normalmente no suelo realizar entradas sobre gráficos, a no ser que sea para profundizar en aspectos de programación, pero creo que hoy voy a escribir un poco sobre un tipo específico de gráfico que me gusta particularmente, sobre todo por la sencillez a la hora de hacerlo y la información que desprende con solo observarlo.

El gráfico del que os hablo es el de tipo piramidal, muy útil para ilustrar datos poblacionales, franjas de edad, comparativas entre sexos, etc. En el ejemplo que voy a escoger para hoy es sobre la población en España según edad y sexo. Veamos los datos base que he extraído a través del INE:

GRAFICO PIRAMIDAL

Como podéis observar, tenemos una distribución de número de personas por sexo y por tramos de edad. Estos datos, así como están son difíciles de interpretar, es necesario echar mano de un gráfico que nos pueda mostrar toda la información de otra manera. Ahora os voy a mostrar cómo quedaría el gráfico una vez realizado:

GRAFICO PIRAMIDAL5

Pero este tipo de gráfico no se genera automáticamente, previamente debemos preparar los datos. Y lo primero que vamos a hacer es crear una nueva columna y con los datos, o bien de hombres o bien de mujeres, multiplicarlos por -1, de forma que tengamos el mismo número pero en negativo. En este ejemplo utilizaremos el datos de mujeres:

GRAFICO PIRAMIDAL1

El siguiente paso es seleccionar las columnas que necesitamos para confeccionar nuestro gráfico, es decir “Total España”, “Hombres” y “Mujeres (en negativo)”:

GRAFICO PIRAMIDAL2

Una vez seleccionadas las columnas, tan solo tendremos que insertar un gráfico, la modalidad sería apilado y de barras. Si tenéis instalado Excel 2016 tenéis una novedad en la cinta de opciones, “gráficos recomendados”, donde, en función del análisis previo, Excel recomienda la mejor opción, así lo veréis mejor:

GRAFICO PIRAMIDAL3

Pulsamos aceptar y ya tenemos nuestro gráfico piramidal, tan solo nos queda un pequeño detalle para que el efecto sea más visual, tenemos que dar mayor ancho a las barras para sean uniformes, con un ancho de 4,25 milímetros será suficiente:

GRAFICO PIRAMIDAL4

Y ya está, ya tenemos el gráfico que anteriormente os había mostrado. Ahora sí es más sencillo interpretar los datos obtenidos de la población en España durante el año 2014. Por cierto, parece que la inversión de la pirámide se va consolidando, de forma que dentro de muy pocos años, estará completamente invertida, o lo que es lo mismo, la mayor parte de la población serán ancianos.

De ahí la importancia de fomentar políticas de natalidad, veraces, serias y comprometidas.

Descarga el archivo de ejemplo pulsando en: GRÁFICO PIRAMIDAL

GENERAR GRÁFICO DINÁMICO CON VBA

Después de unas merecidas vacaciones toca volver con fuerza y con ganas. La verdad es que los aires del Atlántico me han revitalizado lo suficiente y el haber pasado unos días cerca de la familia siempre vienen bien.

Hoy además, para los que no os habéis dado cuenta, os comunico que Excel Signum ahora tiene dominio propio y su url ahora es: www.excelsignum.com. La decisión de realizar este cambio obedece a que el Blog afortunadamente tenía un volumen alto de visitas y comentarios y he creído oportuno dar un salto más, y “oficializar” la herramienta.

Para esta entrada, y siguiendo una consulta que en su momento ya había respondido, trabajaremos con gráficos dinámicos en VBA y algún que otro código interesante.

La consulta era la siguiente: “¿podría automatizar, a partir de una tabla de datos, que mediante un botón se generase un gráfico dinámico, especificando una situación concreta en la hoja (a la misma altura que la tabla dinámica) y que actualice todos los datos de la hoja fuente en caso que aumente la información?”.

Bien, la pregunta me resultó interesante por dos motivos:

– El programar en VBA la forma de situar el gráfico alineado a la misma altura que la tabla dinámica.
– Hacer que la tabla dinámica obtenga la referencia al rango de la hoja fuente incluyendo todos los registros incluso cuando estos varíen.

Para comenzar, es necesario confeccionar una tabla con los datos con los que queremos trabajar, en este caso, he tomado como referencia la plantilla de empleados de unos grandes almacenes. Para ello he tenido que echar mano de los datos del INE para componer los nombres de las personas (en la información de nacimientos, existen datos de nombres y apellidos que permiten componer esta información (ver aquí):

GENERAR GRAFICO DINAMICO_1

En la tabla tenemos los siguientes Campos: Id, Nombre Completo, Sección, Nacimiento, Sexo, 2º Idioma, Estudios. Estos datos están en la hoja (“Datos”) y que queremos generar un gráfico dinámico en la segunda pestaña, denominada (“Resultado”), donde nos muestre la información de empleados por Sección.

Para ello, incluiremos esta macro en un nuevo módulo:

Sub GENERAR()
'DEFINIMOS EL RANGO DE DATOS DE LA HOJA "DATOS"
final = Application.CountA(Worksheets("DATOS").Range("A:A"))
'---------------------------------------GRAFICO DINÁMICO--------------------------------------------
'INDICAMOS QUE LA FUENTE DE DATOS SIEMPRE VAN A SER EL RANGO COMPLETO DE LOS DATOS DE LA
'TABLA DE LA HOJA DATOS. Y COLOCAMOS LA TABLA DINÁMICA A PARTIR DE LA FILA3 COLUMNA1
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATOS!R1C1:R" & final & "C7", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="RESULTADO!R3C1", TableName:="Tabla dinámica1", _
DefaultVersion:=xlPivotTableVersion10
'MOSTRAMOS LA INFORMACIÓN POR SECCIÓN EN LA TABLA DINÁMICA
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("SECCION")
.Orientation = xlRowField
.Position = 1
‘OCULTAMOS DATOS VACÍOS
End With
'AJUSTAMOS QUE LA TABLA DINÁMICA MUESTRE EL NÚMERO DE EMPLEADOS POR SECCIÓN.
ActiveSheet.PivotTables("Tabla dinámica1").AddDataField ActiveSheet.PivotTables _
("Tabla dinámica1").PivotFields("ID"), "Suma de ID", xlSum
With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("Suma de ID")
.Caption = "Cuenta de ID"
.Function = xlCount
End With
'SE AGREGA UN NUEVO GRÁFICO
ActiveSheet.Shapes.AddChart.Select
'SE DETALLA EL TIPO DE GRÁFICO
ActiveChart.ChartType = xlColumnClustered
'SE SELECCIONA EL GRÁFICO Y SE LE DAMOS UN ANCHO (600)
ActiveChart.ChartArea.Select
ActiveChart.ChartArea.Width = 600
'POSICIONAMOS EL GRÁFICO EN LINEA CON LA FILA 3 Y COLUMNA 4 PARA QUE QUEDE SIEMPRE 'ALIENADO A LA TABLA DINÁMICA
ActiveChart.ChartArea.Left = Sheets("RESULTADO").Cells(3, 4).Left
ActiveChart.ChartArea.Top = Sheets("RESULTADO").Cells(3, 4).Top
'DETERMINAMOS QUE LOS DATOS DEL GRÁFICO SEAN DE SECCIÓN.
'AGREGAMOS VALORES A ETIQUETA DE DATOS
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ApplyDataLabels
'DESACTIVAMOS MOSTRAR LISTA DE CAMPOS
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

Una vez aplicado este será el resultado:

GENERAR GRAFICO DINAMICO_2

En este código observamos que previamente a generar el gráfico debemos insertar una tabla dinámica, que será nuestra fuente de información para crear el gráfico. Para determinar que todos los datos de la tabla de la hoja (“Datos”) se incluyan automáticamente en la tabla dinámica, debemos definir en primer lugar el rango y lo hacemos como siempre, contando 🙂

final = Application.CountA(Worksheets("DATOS").Range("A:A"))

Ahora que sabemos en todo momento cuantas filas tenemos, podemos hacer referencia dentro de la tabla dinámica a este rango, y lo hacemos de esta forma, especificándolo en:

SourceData :=  "DATOS!R1C1:R" & final & "C7"

De esta forma ya estamos contestando a una de las dudas que nos planteaban.

Saltando ahora al código que define el gráfico, podemos determinar su situación con el siguiente procedimiento:

ActiveChart.ChartArea.Left = Sheets("RESULTADO").Cells(3, 4).Left
ActiveChart.ChartArea.Top = Sheets("RESULTADO").Cells(3, 4).Top

Así indicamos tanto vertical como horizontalmente dónde queremos situar el gráfico. Además de eso, vamos a aplicar ancho nuevo al gráfico para que muestre todas las secciones:

ActiveChart.ChartArea.Width = 600

Tal y como podemos ver, ya hemos contestado a parte de la pregunta que nos hacía el lector, pero queda un detalle importante, el que cada vez que se ejecute la macro, se borren todos los datos de la hoja (“Resultado”), incluyendo también el propio gráfico, de lo contrario se irían acumulando gráficos por cada ejecución.

Lo haremos incluyendo al principio de la macro anterior estas dos instrucciones, así limpiamos todos los datos incluido en la hoja (que no sean objetos insertados):

Sheets("RESULTADO").Select
Cells.Select
Selection.Clear

De esta forma limpiamos gráficos, donde mediante una condición  si hay algún gráfico en la hoja, lo borramos.

Sheets("RESULTADO").Select
If ActiveSheet.Shapes.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If

Y ahora ya tenemos automatizado completamente la generación y configuración de un gráfico dinámico a partir de una base de datos en otra hoja. Actualiza todos los datos a partir de una tabla base y permite crear nuevas consultas con solo introducir un nuevo campo en la siguiente línea de código:

With ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("SECCION")

Por ejemplo cambiando el campo “Sección” por “Estudios”. Y ahora os dejo el archivo de prueba para que examinéis directamente el código 🙂

Descarga el archivo de ejemplo pulsando en: GENERAR GRÁFICO DINÁMICO

 

AUTOMATIZAR MINIGRÁFICOS EN NUESTRA HOJA EXCEL

Hace unos días me bajé desde el INE una estadística con los datos de población en España por provincias desde 1998. Lo que en un principio era una tarea para comprobar unos datos, resultó ser el tema mi próxima entrada.

Cuando vi los datos, lo primero que se me ocurrió fue generar un gráfico y comparar todas las tendencias en el mismo cuadro, pero luego consideré la posibilidad de usar minigráficos para ver la evolución por provincia de forma individual al final de cada una de las filas, como sabéis, la ventaja de los minigráficos es que los puedes generar en una sola celda.

Pero después de pensarlo, me pregunté si podría automatizar esa tarea, es decir, mediante vba hacer posible que se generase automáticamente un minigráfico al final de cada linea de datos. Y lo logré  🙂

Veamos antes de nada la tabla con los datos y el lugar en donde se generarán los minigráficos:

AUTOMATIZAR MINIGRAFICOS

Así es, los minigráficos irán al final de cada línea, es ahí donde representaremos la evolución poblacional de cada provincia. El problema lo encontraremos cuando queramos asignar una variable que marque el rango de los datos necesarios para generar el gráfico, dado que el proceso debería recorrer cada fila, captar el rango adecuado y luego generar el gráfico automáticamente hasta el final.

Para que podáis ver las modificaciones necesarias para realizar la tarea propuesta, este sería el código si insertamos un minigráfico y grabamos el proceso:

Sub MacroGrabada()
Range("S2").Select
Range("$S$2").SparklineGroups.Add _
Type:=xlSparkColumn, SourceData:="B2:R2"
End Sub

Para poder convertirlo en algo que funcione automáticamente, en primer lugar debemos definir la longitud de los datos, es decir contar cuantas celdas con datos existen desde “A” hasta el final. Lo siguiente es resolver lo más complicado, ¿cómo introducir la variable en “SourceData”?. Y es aquí donde he tenido que realizar varias pruebas porque las técnicas que empleo habitualmente con SQL, Tablas Dinámicas …etc no funcionaban.

La solución os la presento con el código completo, que es como mejor se puede ver:

Sub GenerarMinigrafico()
'Declaramos las variables necesarias
Dim i As Double
'Verificamos la longitud de los datos
Final = Application.CountA(Worksheets("EJEMPLO").Range("a:a"))
'Borramos cualquier gráfico creado cada vez que active el código
Columns("S:S").Select
Selection.SparklineGroups.Clear
Range("S1").Select
'Comenzamos la variable desde la línea dos hasta el final, evitando los encabezados
For i = 2 To Final
'Definimos el rango y lo combinamos con la variable inicial
rango = "B" & i & ":" & "R" & i
'Creamos el minigráfico en la última celda "S"
If Worksheets("EJEMPLO").Cells(i, 1) <> "" Then
Worksheets("EJEMPLO").Range("S" & i).SparklineGroups.Add _
Type:=xlSparkColumn, SourceData:=rango
End If
Next
End Sub

Así es como se introduce una variable en este tipo de gráficos, básicamente componemos el rango y relacionamos la primera columna y la variable con la última columna y la variable, de forma que el código siempre va a saber cuál es el rango de la fila que va indicando la variable “i”.

El resultado es muy visual, pero sobre todo práctico, ideal para este tipo de datos o los relacionados con índices bursátiles:

AUTOMATIZAR MINIGRAFICOS_1

Aquí finaliza el ejercicio de hoy, espero que os haya resultado curioso y que lo tengáis en cuenta para implementarlo en vuestras hojas de trabajo.

Importante: Los minigráficos se introducen en Excel a partir de la versión 2010, por lo tanto en versiones anteriores no se guardarán. Es necesario que la hoja sea .xlsm, pero dado que en WordPress no se puede subir este tipo de archivos, lo subo como xls en modo de compatibilidad para que conserve la macro. Lo único que tendréis que hacer es abrirlo y guardarlo como xlsm en vuestros equipos. De todas formas lo podréis ejecutar si vuestra versión de excel es 2010 o superior, pero al guardarlo, si no lo hacéis como xlsm borrará los minigráficos creados.

Descarga el archivo de ejemplo pulsando en: AUTOMATIZAR MINIGRAFICOS