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

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:

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

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

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