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

Escrito por Administrador

25 de agosto de 2021

Vamos a continuar con el ciclo de casos prácticos y a buscar utilidad a todo lo aprendido hasta ahora.

Hoy veremos cómo cruzar datos de dos tablas relacionadas entre sí mediante un campo al más puro estilo Access.

Como ejemplo he usado una tabla de Clientes y otra de Transacciones. En la tabla Clientes se encuentran registrados todos los datos de cada uno de nuestros clientes, mientras que en la tabla Transacciones 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. He completado las tablas con varios registros para poder trabajar con ellos.

Imagínate que, en un momento determinado, quisiéramos hacer balance y analizar dichas transacciones para realizar una campaña publicitaria. Sería bastante 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…y si tuviéramos del orden de miles de transacciones registradas. Suerte que tenemos VBA.

Vamos a crear un botón que, al pulsarlo, va a rescatar esta información de la tabla “Clientes” y la va a depositar en cada uno de los registros de la tabla “Transacciones”.

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

Macro 1. Limpieza de 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. Recupera la información “Provincias” de la tabla “Clientes” y la copia 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

Vamos a analizar la segunda de las macros paso a paso. (La primera es muy sencilla. 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. Si no desactivamos el refresco, 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

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

En primer lugar, vamos a barrer 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).

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

En la hoja “Transacciones”, guardamos el nombre del cliente (celda C5) en la variable “nombre”. En este caso “Joaquin Fernandez”.

Sheets("Transacciones").Activate

nombre = Cells(i, "C")

Ahora 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")

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

Este sería el resultado.

total-operaciones-matematicas-macros

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.

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€. Dejamos abierta la posibilidad de que posteriormente podamos modificar dicha cantidad.

'La tabla de precios es la siguiente:

'Articulo A = 80€

'Articulo B = 100€

'Articulo 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. Espero que te esté gustando el curso y que me plantees cualquier duda que te surja. También puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, 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