Archivo de la categoría: Macros

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

Publicado el por

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

ELIMINAR FILAS VACÍAS CON VBA EN EXCEL

Publicado el por

Hola a todos!

Hoy toca un post un poco más reducido que los anteriores, y que seguro que os resulta útil.

Es habitual que en algunas ocasiones nos encontremos con bases de datos, tablas o rangos con filas en blanco. Aunque no suele ser un problema, dado que perfectamente podemos utilizar código o formulación que se adapte a esta circunstancia, lo cierto es que siempre que podamos debemos trabajar con base de datos sin filas o columnas en blanco.

Para eliminar filas en blanco, por ejemplo en una columna (OJO, que no tenga formato de tabla), lo podríamos hacer así, con esta macro:

Sub ELIMINAR_FILAS_VACIAS()
With Sheets("DATOS")
.Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End With
End Sub

Lo único que estamos haciendo es seleccionar la columna “A” de una hoja llamada DATOS, posteriormente seleccionar las celdas en blanco (sin datos), y proceder a eliminar todas las filas en las que se encuentran esas celdas.

Pero esto no funcionará si estamos trabajando con datos en formato tabla, cosa que desaconsejo si estamos realizando trabajos con programación y que aconsejo cuando realizamos trabajos con formulación, sin acudir a macros.

Para que podamos eliminar celdas vacías (y filas en este caso), debemos utilizar bucles, aunque se puedan utilizar otras técnicas, creo que esta es la más recomendable, sobre todo porque también nos va a servir con rangos normales, sin formato de tabla.

Voy a poner un pequeño ejemplo para verlo mejor. Imaginad que en nuestro proceso nos hemos importado en una TABLA la relación de provincias de España, pero resulta que algunas celdas están vacías y necesitamos eliminar todas esas filas:

ELIMINAR FILAS VACÍAS CON VBA EN EXCEL

Pues bien, tan solo tendremos que utilizar esta macro y eliminaremos las celdas en blanco:

Sub ELIMINAR_FILAS_VACIAS()
Dim Fin As Integer, i As Integer
Application.ScreenUpdating = False
With Sheets("DATOS")
'Seleccionamos la tabla o el rango
.Range("Tabla1").Select
'Seleccionando el rango, no la tabla
'Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select
'Contamos los Items de esa tabla o rango
With Selection
Fin = .Count
End With
'Si el ítem o celda está vacía, entonces eliminamos la fila
For i = Fin To 1 Step -1
If .Cells(i, 1) = vbNullString Then .Cells(i, 1).EntireRow.Delete
Next i
.Cells(1, 1).Select
End With
Application.ScreenUpdating = True
End Sub

Si os fijáis, veréis que dado que tenemos una tabla, vamos a seleccionarla, en este caso es la tabla1 y contamos los los Ítems de la selección (celdas en blanco o con datos). Luego pasamos el bucle de abajo hacía arriba y eliminamos las celdas en blanco.

Si no tuviésemos una tabla, utilizaríamos la otra parte de la macro que está comentada:

'Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

Y funcionaría perfectamente.

Hoy otros ejemplos y técnicas para realizar este ejercicio, pero estas dos macros son mis preferidas. Aunque, si estos datos los estamos importando de otro lugar, quizás lo mejor sería realizar la importación con SQL eliminando los registros nulos y así sería todo más sencillo, pero bueno, ese ejercicio lo dejo para otro momento 🙂

Os dejo el ejemplo pero con la tabla (formato), si necesitáis aplicarlo a un rango normal, utilizad la primera macro, o esta activando la parte del código que hace referencia a las celdas especiales.

Descarga el archivo de ejemplo pulsando en: ELIMINAR FILAS VACÍAS CON VBA EN EXCEL

 

EXPORTAR DATOS SELECCIONADOS DE UN LISTBOX A OTRO LISTBOX EN OTRO FORMULARIO

Publicado el por

Hace unos días recibí una consulta sobre formularios, la petición en concreto era sobre la necesidad de pasar la información seleccionada en varios listbox de un formulario a los mismo listbox pero en otro formulario.

Además, pedía que aunque se seleccionasen listbox diferentes, se pasase la misma información al segundo formulario y sin duplicar los datos.

Aunque puede parecer una consulta sencilla, la especificación de poder seleccionar cualquier item en cualquier listbox y que la información no se duplique durante el proceso, hacen que requiera un desarrollo a medida.

El post anterior, en el que trataba acerca de cargar datos en un listbox y realizar consultas y filtros con SQL y ADO, ya formaba parte de esta consulta, solo que no quería publicar un post demasiado extenso cuando se puede hacer en dos partes y bien explicado 🙂

Por lo tanto, vamos a utilizar el formulario del post: CARGAR DATOS EN LISTBOX Y REALIZAR BÚSQUEDAS CON ADO Y CONSULTAS SQL que cargaba los datos que previamente habíamos seleccionado a través de los criterios indicados en un buscador, por ejemplo todos los nombres que contienen una “A”.

EXPORTAR DATOS SELECCIONADOS DE UN LISTBOX A OTRO LISTBOX EN OTRO FORMULARIO1

Ahora lo que se pide es pasar los items que queramos seleccionar a un segundo formulario (para nosotros userform2). Por ejemplo así:

EXPORTAR DATOS SELECCIONADOS DE UN LISTBOX A OTRO LISTBOX EN OTRO FORMULARIO2

Como podéis ver, seleccionamos en una ocasión el nombre, en otra la asignatura y en otra la calificación.

Para poder exportar esta selección debemos incluir un botón de comando y pegar el siguiente código:

Private Sub CommandButton2_Click()
'Declaramos las variables
Dim i As Integer, j As Integer, n As Integer
Dim Lista As MSForms.ListBox
Dim nLista As MSForms.ListBox
Dim Control As Control
'Contamos el número de Listbox en el formulario
For Each Control In Me.Controls
If TypeName(Control) = "ListBox" Then
Lbox = Lbox + 1
End If
Next
'Primer bucle: recorremos todos los formularios
For j = 1 To Lbox
Set Lista = Me.Controls("Listbox" & j)
'Segundo bucle: por cada formulario, recorremos los item que contiene
'y detectamos el item seleccionado.
For i = 0 To Lista.ListCount - 1
If Lista.Selected(i) = True Then
'Tercer bucle, volvemos a recorrer todos los items
' y si existen varios items seleccionados en la misma fila (misma persona)
'desmarcamos todos menos el primero (evitando así duplicados).
For n = j + 1 To Lbox
Set nLista = Me.Controls("Listbox" & n)
If nLista.Selected(i) = True Then nLista.Selected(i) = False
Next n
'Pasamos los datos seleccionados al formulario 2
UserForm2.ListBox1.AddItem Me.ListBox1.List(i)
UserForm2.ListBox2.AddItem Me.ListBox2.List(i)
UserForm2.ListBox3.AddItem Me.ListBox3.List(i)
UserForm2.ListBox4.AddItem Me.ListBox4.List(i)
UserForm2.ListBox5.AddItem Me.ListBox5.List(i)
End If
Next i
Next j
'Mostramos formulario 2
UserForm2.Show
End Sub

Una vez pulsado el botón, se muestra el formulario 2 con los datos que hemos seleccionado. Es decir, realiza la tarea correctamente.

EXPORTAR DATOS SELECCIONADOS DE UN LISTBOX A OTRO LISTBOX EN OTRO FORMULARIO3

Pero ¿qué pasaría si seleccionamos para un mismo item (o persona) varias informaciones?, en lógica si el código importa todo lo seleccionado … ¡duplicaría la información!

Pero esto se soluciona con la siguiente parte del código: donde indicamos que si un ítem ha sido seleccionado varias veces, solo tenga en cuenta la primera de ellas, de esta forma no duplicaremos la información:

For n = j + 1 To Lbox
Set nLista = Me.Controls("Listbox" & n)
If nLista.Selected(i) = True Then nLista.Selected(i) = False
Next n

Os he dejado el código muy comentado para que sea sencillo comprender cómo funciona. Espero que os resulte interesante y os sea de utilidad 🙂

Por supuesto, también está la macro del ejercicio anterior, necesaria para cargar los listbox y hacer las búsquedas.

Por cierto, tanto en este post, como en el anterior, el formulario se abre con el evento Workbook.Open, es decir, en el momento que abrimos el archivo se carga automáticamente el formulario. Es posible que si tenéis activada la seguridad en las macros, en el momento de abrir genere un error. Esto se soluciona guardando el archivo y volviéndolo a abrir, o con un on error resume next (pero no me gusta abusar de este recurso).

Descarga el archivo de ejemplo pulsando en: EXPORTAR DATOS SELECCIONADOS DE UN LISTBOX A OTRO LISTBOX EN OTRO FORMULARIO

CARGAR DATOS EN LISTBOX Y REALIZAR BÚSQUEDAS CON ADO Y CONSULTAS SQL

Publicado el por

Aunque estoy preparando varios post sobre análisis estadístico de los datos, hoy trataré sobre algo totalmente diferente, los listbox en formularios y la posibilidad de utilizar ADO y las consultas de SQL para buscar y filtrar información.

Vamos seguidamente con un ejemplo para ilustrar el ejercicio. Utilizaré el listado de alumnos que usé para el post de las funciones matriciales, este:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL

Y ahora vamos a construir un sencillo formulario en el que vamos a incluir 5 listbox en los que mostraremos la información de la hoja ALUMNOS y también un cuadro de texto (textbox) y un botón de comando para realizar las búsquedas:

Ahora vamos utilizar el siguiente código VBA que vamos a pegar en el evento click del botón de búsqueda, luego lo iremos comentando:

Private Sub CommandButton1_Click()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, MiLibro As String
Dim Control As control, Nombre As String
'Vaciamos todos los Listbox
For Each control In Me.Controls
If TypeName(Control) = "ListBox" Then
Control.Clear
End If
Next
'grabamos el dato a buscar, si no hay dato la variable Nombre es nula
Nombre = IIf(UCase(Me.TextBox1.Value) = vbNullString, IsNull(Me.TextBox1.Value), UCase(Me.TextBox1.Value))
'Creamos la instrucción SQL según los parámetros que nos interesan, en este caso, el nombre
'y los diferentes carácteres comodín y el operador "like"
obSQL = "SELECT [ALUMNOS$].* " & _
"FROM [ALUMNOS$] " & _
"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"
'Guardamos el nombre del libro activo para utilizarlo en la conexión ADO
MiLibro = ActiveWorkbook.Name
'Dejamos el cuadro de búsqueda vacío después de iniciar la consulta
Me.TextBox1.Value = vbNullString
'Iniciamos la conexión ADO
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
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
'Pasamos la información a cada ListBox a través del recordset
With UserForm1
.ListBox1.AddItem Dataread("ID")
.ListBox2.AddItem Dataread("NOMBRE")
.ListBox3.AddItem Dataread("CLASE")
.ListBox4.AddItem Dataread("ASIGNATURAS")
.ListBox5.AddItem Dataread("CALIFICACIONES")
End With
Dataread.MoveNext
Loop
'Liberamos y cerramos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Con esta macro podremos realizar consultas a través de lo que indiquemos en el cuadro de búsqueda del formulario (textbox1) o lo que es lo mismo, en la variable “Nombre”.

Pero vamos explicando poco a poco el código. Para borrar o limpiar los datos que van a contener los listbox durante las sucesivas búsquedas tenemos que utilizar el siguiente proceso.

For Each control In Me.Controls
If TypeName(Control) = "ListBox" Then
Control.Clear
End If
Next

Una vez que tenemos los listbox libres de datos ya podemos iniciar la consulta SQL:

obSQL = "SELECT [ALUMNOS$].* " & _
"FROM [ALUMNOS$] " & _
"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"

Me gustaría comentar que aquí utilizamos el dato contenido en la variable “Nombre”, importante para poder utilizar el operador Like y por lo tanto, comodines en nuestra consulta.

Cuando la información del cuadro de texto está vacía, simplemente no mostrará nada en los listbox, eso es porque he introducido mediante una condición que si el dato de búsqueda es vacío, entonces es nulo (y por eso no muestra nada).

Nombre = IIf(UCase(Me.TextBox1.Value) = vbNullString, IsNull(Me.TextBox1.Value), UCase(Me.TextBox1.Value))

Podríamos quitar la condición y entonces al pulsar “Buscar” y cargaríamos todos los datos en los listbox, pero creo que así es más funcional.

En la parte condicional de la sentencia SQL, estamos indicando mediante el uso de caracteres comodín, que se busquen los nombres que empiecen por la información escrita en el textbox.

"Where [ALUMNOS$].[NOMBRE] like " & "'" & Nombre & "%'"

Por ejemplo, todos los nombre que empiecen por “MA”:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL2

Si queremos extraer los nombres que acaban en “A”, el código sería así:

"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "'"

Y el resultado este:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL3

Y si quisiéramos que se buscase el nombre a partir de cualquier fragmento de texto, simplemente colocaríamos “%” a ambos lados de la variable “Nombre”:

"Where [ALUMNOS$].[NOMBRE] like " & "'%" & Nombre & "%'"

y podríamos realizar búsquedas más abiertas, por ejemplo, todos los nombres que contengan “AL” (independientemente si está delante, en el centro o al final). Este es el resultado:

CARGAR DATOS EN LISTBOX Y REALIZAR BUSQUEDAS CON ADO Y CONSULTAS SQL4

Como podéis observar, la clave está en saber utilizar los caracteres comodín correctamente en la sentencia SQL. Es algo sencillo pero hay que tener especial cuidado con la posición de las comillas simples y los espacios.

Por último, ya sabéis que para este tipo de método es necesario activar la referencia Microsoft ActiveX Data Object 2.8 Library en el editor de VBA.

En el archivo de descarga, la búsqueda de los nombres está condicionada a que contengan parte del dato contenido en el buscador.

Descarga el archivo de ejemplo pulsando en: CARGAR DATOS EN LISTBOX Y REALIZAR BÚSQUEDAS CON ADO Y CONSULTAS SQL

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

Publicado el por

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 la extracción de información.

En esta web hay varios post dedicados a la extracción de la información, en algunos casos con tablas dinámicas, en otros con ADO usando SQL y también directamente con código VBA usando diferentes métodos.

Cuando trabajamos con grandes volúmenes de información suele ocurrir que con tanta información se hace complicado, tanto la gestión de los datos, como la implementación de herramientas de cálculo.

Y no es un problema menor, dado que seleccionar la información de forma manual o embarcarnos a utilizar una tabla dinámica con 428 campos o trabajar con ADO nos hará perder mucho tiempo y seguramente cometer errores.

Por ello, vamos a proponer una forma de realizar esta extracción de información. Siguiendo con nuestro ejemplo del post anterior (TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 1: IMPORTAR LA INFORMACIÓN), en el que habíamos extraído los datos del archivo TXT de la Encuesta Europea de Salud en España, ahora nos encontramos con una base de datos bastante grande, no tanto en longitud de filas pero sí de columnas (428).

Dado que los límites que tiene Access y ADO (Microsoft ACE.OLEBD)  son de 255 columnas no nos van a permitir realizar consultas ni tampoco extraer columnas con información relevante, ni en Excel ni en Access (con dichas herramientas).

Sí podríamos utilizar tablas dinámicas (no gráficos dinámicos) en versiones actuales de Excel donde el límite está en la memoria disponible para mostrar campos y filas (aunque los campos de valores tendrán el límite de 256). O también podríamos realizar instrucciones con VBA para recuperar aquellas columnas o rangos que nos interesen.

Aunque la tabla dinámica podría ser una buena opción, para este ejemplo utilizaré VBA de tal manera que me permita extraer de la base de datos aquellos campos que voy a necesitar para posteriormente aplicar consultas, fórmulas, gráficos o tablas dinámicas.

Una vez que he acabado con la parte más teórica o de reflexión, ¡vamos con la teoría!.

Para poder confeccionar la herramienta necesito utilizar algo que me permita seleccionar cada ítem o campo de la tabla y exportarlo a una nueva hoja. Por ello, creo que la figura, o mejor, el objeto LISTBOX es perfecto para este cometido.
Por ello, en la hoja ESPECIFICACIONES vamos a insertar un Control ActiveX, un cuadro de lista o ListBox (que será ListBox1). Una vez que lo hemos insertado, tenemos que programarlo para realizar las siguientes acciones, cargar los campos de la tabla y vaciarlos o desmarcarlos.

Para cada acción crearemos una macro que vamos a pegar en un módulo estándar de nuestro editor de VBA:

Para cargar la información de  todos los campos:

Vamos a pegar este código en nuestro módulo de VBA:

Sub CARGAR_LISTBOX()
'Definimos variables
Dim fin As Integer
fin = Application.CountA(Sheets("ESPECIFICACIONES").Range("A:A"))
'Cargamos datos en el listbox de la hoja espeficificaciones
'Dimensionamos listbox
'indicamos propiedad Multiselect
With Sheets("ESPECIFICACIONES").ListBox1
.Height = 220 'Alto
.Width = 180 'Ancho
.Top = 186.75 'Arriba
.Left = 635 'Izquierda
.MultiSelect = fmMultiSelectMulti
.List = Sheets("ESPECIFICACIONES").Range("A2:A" & fin).Value
End With
End Sub

Como podéis observar además de cargar los datos del ListBox, estoy indicando los parámetros necesarios para dimensionarlo (dado que la propiedad IntegralHeight está como true y si no indicamos sus dimensiones, cada vez que carguemos datos el listbox se irá reduciendo de tamaño).  También indicamos que se pueden seleccionar varios elementos (multiselect).

Para vaciar la información del listbox

Vamos a pegar este código en nuestro módulo de VBA:

Sub VACIAR_LISTBOX()
'Vaciamos los datos que hemos cargado en el listbox
With Sheets("ESPECIFICACIONES")
.ListBox1.Clear
End With
End Sub

Para desmarcar la información de los campos seleccionados:

Vamos a pegar este código en nuestro módulo de VBA:

Sub DESMARCAR_LISTBOX()
'Definimos variables
Dim i As Integer
'Desmarcamos los datos seleccionados en el listbox
With Sheets("ESPECIFICACIONES")
For i = 0 To .ListBox1.ListCount – 1
.ListBox1.Selected(i) = False
Next
End With
End Sub

En este caso con una sencilla instrucción For desmarcamos todos los ítems seleccionados.

Ahora que ya tenemos nuestro ListBox y las macros que lo controlan preparadas:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION

ya podemos pegar esta otra macro extraer la información que seleccionemos en el listbox. Por lo tanto vamos a pegar esta macro en el mismo editor de VBA:

Sub EXTRAER_INFORMACION()
'Definimos las variables
Dim Item As Integer, nItem As Variant, nSel As Integer
Dim campo As Integer, fin As Integer
Dim Origen As Worksheet, Destino As Worksheet
'Desactivamos actualización de plantalla
Application.ScreenUpdating = False
'Eliminamos información que pueda contener la Hoja Query
Sheets("QUERY").Select
With Sheets("QUERY")
.Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlToLeft
.Range("A1").Select
End With
'Detectamos el nombre de cada campo seleccionado (nItem) y el número de items seleccionados (nSel)
With Sheets("ESPECIFICACIONES")
For Item = 0 To .ListBox1.ListCount – 1
If .ListBox1.Selected(Item) = True Then
nItem = .ListBox1.List(Item)
nSel = nSel + 1
'Identificamos el número de columna en la hoja datos con el nombre del cada Item
campo = Application.Match(nItem, Sheets("DATOS").Range("1:1"), 0)
'Pasamos las columnas detectadas en la hoja Datos a la hoja Query
Set Origen = Sheets("DATOS")
Set Destino = Sheets("QUERY")
fin = Application.CountA(Sheets("DATOS").Range("A:A"))
With Destino
.Range(.Cells(1, nSel), .Cells(fin, nSel)).Value = Origen.Range(Origen.Cells(1, campo), Origen.Cells(fin, campo)).Value
Columns(nSel).EntireColumn.AutoFit
End With
End If
Next
End With
End Sub

Es un código muy sencillo y a la vez efectivo. Simplemente, mediante un bucle, por cada ítem seleccionado en el listbox buscamos el valor de la columna en la tabla de la hoja “DATOS” y por medio de una instrucción SET pasamos el valor a la hoja “QUERY”.

Por ejemplo, vamos a seleccionar en nuestro listbox el campo CCAA y Edad (EDADa):

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION2

Y una vez ejecuta la consulta pulsando en el botón EXTRAER INFORMACIÓN, este es el resultado en la hoja “QUERY”:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL PARTE 2 EXTRAER LA INFORMACION3

Como podéis ver, los datos que hemos extraído de una hoja a la otra son idénticos, simplemente movemos información para luego poder tratarla de una forma mucho más efectiva.

Por ejemplo, si quisiera conocer la correlación entre la edad y la frecuencia del consumo de tabaco, a priori, solo tendría que traer dos columnas, la edad y el campo V121, y luego utilizar funciones que me permitan realizar el cálculo, o determinar si es estadísticamente existe una relación entre la cantidad y frecuencia de consumo de alcohol y el consumo de tabaco, la edad y la comunidad autónoma de procedencia, etc.  Pero eso será en el siguiente post 🙂

Recordad que cuando trabajamos en Excel, la solución más sencilla puede ser la más eficaz 🙂

Os dejo el archivo de ejemplo para que realicéis pruebas, investiguéis el código o lo que necesitéis. El archivo pesa unos 18,5 megas y se debe a que tiene la información importada del TXT grabada en la hoja “DATOS” para que no tengáis que volver a importar la información, sin embargo, si la borráis o la queréis volver a importar solo tenéis que ir al primer post y bajaros la información.

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

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

Publicado el por

Hola a todos ¿Cómo estáis?.

Llevo unos días preparando nuevo material para actualizar Excel Signum. Desde hace tiempo tengo ganas de abordar el tema de los grandes volúmenes de datos, del famoso “BIG DATA” y las posibilidades que nos puede ofrecer Excel (tanto sus funciones como VBA) para trabajar con estos datos.

Antes de comenzar me gustaría realizar algunas apreciaciones sobre este asunto:

– Existen múltiples herramientas y plataformas especializadas en el BIG DATA. En esta web vamos a ver algunos ejemplos que podemos utilizar para trabajar con Excel llegando a rozar sus limitaciones, pero con resultados efectivos. Es decir, no pretendo indicar con esto que Excel puede cubrir todas nuestras necesidades en lo que a BIG DATA se refiere, dado que contamos con limitaciones importantes que iré comentando a lo largo del POST.

– De la misma forma que Excel tiene sus limitaciones, también podemos contar con algunas funciones, herramientas y código VBA para llegar a resultados muy interesantes y dignos cuando estamos trabajando con grandes volúmenes de información.

– Existen varios complementos en Excel como Power Query y Power Pivot que nos pueden resultar muy interesantes y también facilitar mucho nuestro trabajo. No obstante, de estos recursos hablaré en futuros post.

– Dado que será un tema bastante largo y posiblemente “denso” (habrá mucho código), he decidido realizar el post en tres partes:

* La primera: dedicada a la importación masiva de datos a Excel.
* La segunda: dedicada a la extracción de información.
* La tercera: dedicada al análisis de los datos, con herramientas y funciones estadísticas. Data Mining.

Por lo tanto, vamos a comenzar con la primera parte: La importación masiva de la información.

Cuando hablamos de importación masiva de datos, podemos hacer referencia a muchos tipos de datos, formatos, tipos de archivo, etc. y cada uno con su técnica específica de importación. Para este ejemplo vamos a trabajar con un fichero plano, un TXT de ancho fijo.

Aunque ya he dedicado un post al tema de importar archivos TXT de ancho fijo:  IMPORTAR ARCHIVOS TXT DE ANCHO FIJO, para este ejemplo vamos a utilizar otra técnica que veremos a continuación. Pero antes necesitaré un archivo TXT en el que basar el ejemplo, y que mejor lugar para encontrarlo que el INE!

Buscando entre toda la información y tipos de encuestas, me ha parecido muy interesante esta: Encuesta Europea de Salud en España donde tenemos una gran cantidad de información que nos servirá para realizar todo el ejercicio.

En esta captura de pantalla podéis ver en el lugar en el que debemos descargar el archivo TXT, en la pestaña “Microdatos”  >  Encuesta 2014 > Fichero de Microdatos > Elegir Formato y elegimos Formato TXT en ZIP:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL

Una vez que lo hemos descargado, debemos descargar otro archivo importante, que se encuentra justo encima “Fichero de registro y valores válidos de las variables”.

Este fichero es imprescindible, dado que nos informará de la longitud de los campos que vamos a importar en el archivo PDF, el ancho fijo y el nombre de cada campo o variable así como su definición valor, es decir las especificaciones.

Por ejemplo, en la pestaña “Diseño de Registro” podemos ver el nombre de la variable, la longitud, la posición de inicio, la posición final y la descripción de la variable:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL1

En la pestaña: Variables y valores, veremos los valores que pueden tener cada una de las variables:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL2

Para esta primera parte solo vamos a necesitar de la pestaña “Diseño de Registro”: el nombre de la variable y la longitud. Estos campos los vamos a copiar a nuestro archivo de Excel en la pestaña “ESPECIFICACIONES” y añadiremos una nueva columna que vamos a denominar “TIPO FORMATO”:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL3

También vamos a renombrar una nueva pestaña como “DATOS”, que es donde mostraremos los datos que importemos.

Ahora os dejo el código que debemos pegar en nuestro editor VBA en un módulo estándar:

Sub IMPORTAR_TXT_ANCHO_FIJO()
'Definimos variables
Dim Ancho As Variant, Tipo As Variant, Titulo As Variant
Dim Archivo As String, nFilas As Integer
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Generamos los array necesarios para determinar
'ancho y tipo de datos a la hora de importar el TXT
With Sheets("ESPECIFICACIONES")
nFilas = Application.CountA(.Range("A:A"))
Titulo = Application.Transpose(.Range("A2:A" & nFilas).Value)
Ancho = Application.Transpose(.Range("B2:B" & nFilas).Value)
Tipo = Application.Transpose(.Range("C2:C" & nFilas).Value)
End With
'Abrimos cuadro de diálogo para seleccionar TXT
Filtro = " TXT(*.TXT),"
Archivo = Application.GetOpenFilename(Filtro)
'Si no seleccionamos nada, salimos del proceso
If Archivo = "Falso" Or Archivo = Empty Then
Exit Sub
End If
'Eliminamos los datos de la hoja "DATOS"
Call ELIMINA_DATOS
'Iniciamos el proceso
Sheets("DATOS").Select
With Sheets("DATOS")
'Indicamos encabezado de columnas
'que se encuenta en las especificaciones
.Range("A1:PL1").Value = Titulo
'Iniciamos Query y nos traemos la información del TXT
With .QueryTables.Add(Connection:= _
"TEXT;" & Archivo, Destination:=Range( _
"$A$2"))
.Name = "CONSULTA_1"
.AdjustColumnWidth = False
.TextFileParseType = xlFixedWidth
'Hacemos referencia al tipo de datos que queremos importar
'en este caso, número (1)
.TextFileColumnDataTypes = Array(Tipo)
'Indicamos el ancho de cada columna
'que se encuentra también en las especificaciones y que hemos
'convertido en un array, al igual que el tipo y el título
.TextFileFixedColumnWidths = Array(Ancho)
.TextFileTrailingMinusNumbers = True
'On Error Resume Next
.Refresh BackgroundQuery:=False
'On Error GoTo 0
End With
End With
Application.ScreenUpdating = True
End Sub

Para que la macro funcione correctamente, es necesario pegar esta otra a continuación:

Sub ELIMINA_DATOS()
'Definimos variables
Dim cnn As Object, table As Object
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Seleccionamos la hoja Datos
Sheets("DATOS").Select
With Sheets("DATOS")
'Borramos TODAS las conexiones que tenga el libro
For Each cnn In ThisWorkbook.Connections
cnn.Delete
Next cnn
'Borramos todas las tablas de la hoja activa
For Each table In .QueryTables
table.Delete
Next table
'Borramos todos los contenidos de la hoja activa
.Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlToLeft
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub

Y ahora vamos a comentar algunas partes del código que me parece interesante. En primer lugar, para que podamos capturar el archivo TXT he incluido un cuadro de diálogo para seleccionarlo más cómodamente.

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL5

Dado estamos utilizando el método “QueryTables.Add“, es necesario que automaticemos las matrices que dan valor al ancho fijo y al tipo de formato de los datos que vamos a importar con la consulta.

Para eso, obtendremos las matrices de cada campo con los datos que hemos pegado anteriormente en la hoja ESPECIFICACIONES. Estos son los tres campos que necesitamos:

Titulo = Application.Transpose(.Range("A2:A" & nFilas).Value)
Ancho = Application.Transpose(.Range("B2:B" & nFilas).Value)
Tipo = Application.Transpose(.Range("C2:C" & nFilas).Value)

Y los vamos a utilizar en las siguientes líneas de código.

.TextFileFixedColumnWidths = Array(Ancho)
.TextFileColumnDataTypes = Array(Tipo)

Para el tipo de formato, indicar que estoy utilizando el 1, que se refiere a número. Si quisiéramos importar con formato texto sería el 2, (es importante decidir el formato que nos interesa, dado que posteriormente puede ser relevante para los cálculos).

Como también tenemos el nombre de todos los campos y los hemos pasado a una matriz, podemos pasar el rango a la primera fila de la hoja datos, indicando la última fila, es decir la columna 428 o PL, esto lo podemos automatizar, pero he preferido dejarlo así para mayor claridad.

.Range("A1:PL1").Value = Titulo

Por último, la macro ELIMINA_DATOS(), elimina la tabla que hemos importado anteriormente por si es necesario repetirla varias veces.

Es importante comentar que con esta macro, a diferencia de la que escribí en el post anterior, nos ahorramos 10 minutos en la importación. Tanto los métodos utilizados como la posibilidad de automatizar la carga de las matrices, son la forma más eficiente de trabajar con esta información en Excel.

No vamos a poder trabajar directamente con ADO, dado que tiene la limitación de los 255 campos y hacerlo mediante procesos for ralentizaría demasiado la tarea.

Una vez que ejecutamos la macro, esta es la información que obtenemos:

TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL4

En total, tenemos 428 columnas y 22.843 filas, una cantidad bastante importantes de datos con la que podemos empezar a extraer información (aunque esto lo haremos en el siguiente post) 😉

Esta macro está pensada y diseñada para trabajar con versiones de Excel 2010 en adelante. No lo he probado en Excel 2007, pero debería funcionar correctamente. La extensión del archivo siempre ha de ser xlsm, dada la extensión de las columnas (superan las 255). De hecho, si estuviésemos trabajando en Access tendríamos la limitación de los 255 campos (incluso en Access 2016), sin embargo en Excel no tenemos ese problema.

Con los ajustes adecuados podremos realizar importaciones hasta los límites que nos asigne EXCEL, tanto en filas como en columnas, e incluso podríamos pensar en utilizar varias hojas para guardarnos tablas mucho más grandes.

Y este ha sido el primero de los tres post sobre trabajar con grandes bases de datos. Os dejo la información en Google Drive, dado que en WordPress no se permiten archivos TXT ni XLSM.

En el próximo post trataré el cómo vamos a extraer la información que nos interesa, y no voy a utilizar bucles, sino otra herramienta que nos va a venir muy bien y que estamos acostumbrados a usar de otra forma.

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

 

PASAR INFORMACIÓN DE UN RANGO A UNA CELDA

Publicado el por

Hola a todos 🙂

Hace unos días me enviaron una consulta sobre la necesidad de pasar la información contenida en un rango de celdas a una única celda.

Hola Segu:
Tengo varias columnas con datos y quiero pasarlos a una celda. No es transponer esos datos, es introducir todo el rango en esa celda y separarlos con una coma ¿Cómo podría hacerlo?.
Muchas gracias.

Aunque la pregunta resulta extraña, este tipo de formato resulta útil para cuando tenemos que trabajar con matrices en VBA (Array). Pero vayamos a la consulta, in situ:

Voy a aprovechar la información de otro post para realizar el ejemplo. Imaginad que tenemos los datos de facturación de los comerciales de una empresa distribuidos por meses.

PASAR INFORMACION DE UN RANGO A UNA CELDA

Y queremos pasar los importes de cada mes (el rango) a una ÚNICA celda. En este ejemplo pasaremos los datos a la hoja “RESULTADO”.

Para realizar este ejercicio, utilizaremos la siguiente macro:

Sub RANGO_A_CELDA()
'Declaramos las variables
Dim i As Integer, j As Integer
Dim ncolumna As Integer, nfila As Integer
Dim sCadena As String
'Eliminamos cualquier información en la hoja RESULTADO
Sheets("RESULTADO").Select
With Sheets("RESULTADO")
.Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
.Range("A1").Select
End With
'Iniciamos bucle por columna
With Sheets("DATOS")
ncolumna = Application.CountA(.Range("1:1"))
For i = 1 To ncolumna
'Iniciamos bucle por cada fila de datos
nfila = Application.CountA(.Columns(i))
'En cada Rango debemos vaciar los datos de sCadena
sCadena = vbNullString
'Podemos elegir el separador entre los datos,
'en este caso una coma ","
For j = 2 To nfila
sCadena = sCadena & .Cells(j, i) & ","
Next j
'Llevamos los datos a la hoja RESULTADO
'y formateamos a texto cada celda
With Sheets("RESULTADO")
.Range("A1") = "RESULTADO"
.Range("A" & i + 1).NumberFormat = "@"
.Range("A" & i + 1) = Trim(Mid(sCadena, 1, Len(sCadena) - 1))
End With
Next i
End With
End Sub

Una vez que ejecutamos el código, obtenemos esta información:

PASAR INFORMACION DE UN RANGO A UNA CELDA1

Y como podéis observar, hemos codificado en cada celda toda la información del rango. En total son doce celdas (una por cada mes).

Sobre esta línea del código:

.Range("A" & i + 1) = Trim(Mid(sCadena, 1, Len(sCadena) - 1))

La he utilizado para eliminar el último carácter, que en este caso es una coma. De otra forma, la cadena de datos finalizaría de forma errónea (bajo mi punto de vista).

Como ya dije, estoy utilizando una coma como separador de la información, pero se podía utilizar cualquier otro carácter (o un espacio).

Y eso es todo, si en algún momento tenéis que pasar los datos de una columna a una celda, ahora lo tendréis más fácil.

Descarga el archivo pulsando en: PASAR INFORMACIÓN DE UN RANGO A UNA CELDA