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
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
'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:

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)

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.

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%.

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).

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”.

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.

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.

Importante. Si la macro os muestra un error 3706 (que no encuentra el proveedor especificado), lo más probable es que sea un problema con las versiones de Excel y el proveedor que ha utilizado para este post:

.Provider = "Microsoft.Jet.OLEDB.4.0"

Debéis modificarlo por

.Provider = "Microsoft.ACE.OLEDB.12.0"

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