Ejercicio práctico 4: Cómo cruzar datos en tablas relacionales de Excel con macros.

cruzar-tablas-excel

Escrito por Administrador

30 de agosto de 2021

Hoy aprenderemos a cruzar datos de dos tablas relacionadas entre sí mediante un campo en Excel, pero al más puro estilo Access.

He usado como ejemplo dos tablas, una de Clientes y otra de Transacciones. En la primera, se encuentran registrados los datos de cada uno de nuestros clientes, mientras que en la segunda, aparecen cada una de las compras que han hecho dichos clientes en nuestro comercio.

total-operaciones-matematicas-macros
total-operaciones-matematicas-macros

En este caso, el campo clave (campo que une la información de ambas tablas) es el nombre del cliente.

Imagina que, en un momento determinado, quisiésemos hacer balance y analizar dichas transacciones para realizar una campaña publicitaria. Seguramente, sería muy interesante saber en qué provincias estamos realizando un mayor número de ventas para focalizar allí nuestros esfuerzos.

Para ello, nos haría falta rellenar el campo “Provincias” de la tabla “Transacciones”. Pero, si tuviésemos del orden de miles de transacciones registradas la cosa se complicaría. Sin embargo, por suerte, tenemos VBA.

De esta forma, vamos a crear un botón que, al pulsarlo, rescate esta información de la tabla “Clientes” y la deposite en cada uno de los registros de la tabla “Transacciones”.

Así mismo, crearemos un botón para limpiar el campo “Provincias” de la tabla “Transacciones” y poder repetir el ejercicio cuantas veces queramos a la vez que modificamos o añadimos registros a las tablas a nuestro antojo.

A continuación, te dejo un enlace donde explico cómo asignar una macro a un botón.

Espero que al final de este ejemplo, te quede claro cómo cruzar datos de distintas tablas en Microsoft Excel.

Macro 1. Limpiar datos de la columna “Provincias” de la tabla “Transacciones”.

Sub Limpiar()

'Macro que recupera la provincia del cliente de la tabla Clientes para cada registro de la tabla 'Transacciones

'Buscamos el último registro en la tabla Transacciones

h = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

'Borramos los datos de la columna Provincias en la tabla Transacciones

Range(Cells(5, "F"), Cells(h, "F")).ClearContents

End Sub

'Borramos los valores de la tabla de gastos

Range("K9:K18").ClearContents

final:

Application.ScreenUpdating = True

End Sub

Macro 2. Recuperar información “Provincias” de la tabla “Clientes” y copiar en “Transacciones”.

Sub Provincias()

'Macro que recupera la provincia del cliente de la tabla Clientes para cada registro de la tabla 'Transacciones

'Desactivamos el refresco de pantalla

Application.ScreenUpdating = False

'Buscamos el último registro en la tabla Clientes

g = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

'Buscamos el último registro en la tabla Transacciones

Sheets("Transacciones").Activate

h = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

'Borramos los datos de la columna Provincias en la tabla Transacciones

Range(Cells(5, "F"), Cells(h, "F")).ClearContents

'Comenzamos la iteracion para buscar la provincia de cada cliente de la tabla Transacciones

For i = 5 To h

Sheets("Transacciones").Activate

nombre = Cells(i, "C")

Sheets("Clientes").Activate

For k = 5 To g

'Si encontramos una coincidencia, copiamos la provincia y pasamos a otra transaccion

If Cells(k, "B") = nombre Then

provincia = Cells(k, "E")

Sheets("Transacciones").Activate

Cells(i, "F") = provincia

GoTo otratransaccion

End If

Next

otratransaccion:

Next

Application.ScreenUpdating = True

End Sub

En primer lugar, vamos a analizar la segunda de las macros paso a paso. (La primera es muy sencilla, pero si tuvieras cualquier pregunta, no dudes en consultármela).

El primer paso, es desactivar el refresco de pantalla. En este caso es algo obligado, ya que el programa va a estar consultando dos tablas, en hojas distintas, y va a estar yendo de una a otra. En caso contrario, además de estar viendo pestañeos, puede ralentizarse la ejecución del programa.

Sub Provincias()

'Macro que recupera la provincia del cliente de la tabla Clientes para cada registro de la tabla 'Transacciones

'Desactivamos el refresco de pantalla

Application.ScreenUpdating = False

Para optimizar el número de iteraciones, buscamos la última fila con registros en ambas tablas y borramos los datos del campo “Provincias” de la tabla “Transacciones” por si no hemos pulsado antes el botón “1. Borrar Provincias”.

'Buscamos el último registro en la tabla Clientes

g = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

'Buscamos el último registro en la tabla Transacciones

Sheets("Transacciones").Activate

h = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

'Borramos los datos de la columna Provincias en la tabla Transacciones

Range(Cells(5, "F"), Cells(h, "F")).ClearContents

Vamos a usar dos iteraciones, una anidada dentro de otra.

En primer lugar, barremos las transacciones de la tabla “Transacciones” (iteración i), y en cada uno de los registros, nos iremos a la tabla “Clientes” a buscar la provincia que le corresponde al cliente de la transacción (iteración k).

 

Primera iteración (i=5).

Así que, comenzamos en la fila i=5 que es la que contiene la primera iteración.

'Comenzamos la iteracion para buscar la provincia de cada cliente de la tabla Transacciones

For i = 5 To h

A continuación, en la hoja “Transacciones”, guardamos el nombre del cliente (celda C5) en la variable “nombre”, que en este caso es “Joaquin Fernandez”.

Sheets("Transacciones").Activate

nombre = Cells(i, "C")

Seguidamente, nos vamos a la hoja “Clientes” y buscamos este nombre desde la fila k=5 hasta la última fila que contiene datos (k=g).

Sheets("Clientes").Activate

For k = 5 To g

Si encontramos alguna coincidencia durante la búsqueda, guardamos el valor de la celda del campo provincia en la variable “provincia”.

En este caso, el cliente se encuentra en k=5, y guardamos el valor de la celda E5 (Alicante) en la variable “provincia”.

'Si encontramos una coincidencia, copiamos la provincia y pasamos a otra transaccion

If Cells(k, "B") = nombre Then

provincia = Cells(k, "E")

Por último, volvemos a la hoja “Transacciones”, pegamos el valor de la variable “provincia” en el registro correspondiente. A continuación, salimos de la iteración secundaria (k), volviendo a la principal (i) donde pasaríamos a la siguiente transacción i=6.

 

Segunda iteración (i=6).

En este caso, pegamos el valor de la variable “provincia” (Alicante) en la celda F5 de la tabla “Transacciones”, volveríamos a la iteración principal con i=6.

Sheets("Transacciones").Activate

Cells(i, "F") = provincia

GoTo otratransaccion

End If

Next

otratransaccion:

Next

Application.ScreenUpdating = True

End Sub

Finalmente, este sería el resultado.

total-operaciones-matematicas-macros

Además, como complemento al ejemplo, he programado un código para que cuando rellenemos el campo “Artículo”, automáticamente se complete el importe. Éste se aloja directamente en la hoja “Transacciones” y vigila cualquier cambio que haya en la misma.

Aunque no tenga nada que ver con cómo cruzar tablas en Excel, no está de más.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'Macro que al rellenar el campo Articulo, rellena automaticamente el importe

Si este cambio sucede en la columna D, guardamos el valor de la fila donde se ha producido el cambio en la variable “ThisRow”.

'Si varía algún valor de la 4ª columna

If Target.Column = 4 Then

'Guarda en la variable ThisRow el valor de la fila

ThisRow = Target.Row

Si el cliente compra el artículo A, cargaremos un importe de 80€, si es B de 100€ y si es C de 120€. Así, dejamos abierta la posibilidad de que posteriormente podamos modificar dicha cantidad.

‘La tabla de precios es la siguiente:

‘A = 80€

‘B = 100€

‘C = 120€

On Error GoTo final

If Target.Value = «A» Or Target.Value = «a» Then

Range(«E» & ThisRow) = 80

ElseIf Target.Value = «B» Or Target.Value = «B» Then

Range(«E» & ThisRow) = 100

ElseIf Target.Value = «C» Or Target.Value = «c» Then

Range(«E» & ThisRow) = 120

Else

En caso de introducir algo distinto a A, B o C, nos aparece un mensaje de advertencia y se borrarán los datos introducidos de artículo e importe.

'En caso de introducir un artículo distinto a A, B o C, mostramos mensaje de aviso y borramos los valores 'introducidos

MsgBox "Artículo no válido"

Range(Cells(ThisRow, "D"), Cells(ThisRow, "E")).ClearContents

Exit Sub

End If

End If

final:

End Sub

Prueba a añadir registros a la tabla de “Transacciones” y verás cómo al rellenar el campo “Artículo”, se autocompleta el campo “Importe” según hemos programado.

Con esto llegamos al final de la vigesimoséptima lección, en la que has aprendido a cruzar datos de tablas relacionales en Microsoft Excel.

Finalmente, desear que te esté gustando el curso y pedirte que me plantees cualquier duda que te surja. También, puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario. De hecho, soy todo oídos. Nos vemos en el próximo capítulo.

Descarga el archivo del ejemplo

« EP3. Cómo clasificar automáticamente con macros.

EP5. Cómo filtrar registros de una tabla con macros. »

0 comentarios

Enviar un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Quizá te interese,

Contacto