Ejercicio práctico 5: Cómo filtrar filas o registros de una tabla Excel, repartiéndolos en varias hojas mediante macros.

filtrar-filas-excel

Escrito por Administrador

29 de agosto de 2021

Hoy veremos un ejercicio con el que aprenderemos a filtrar las filas de una tabla Excel y, en función del valor de uno o varios campos, repartirlos entre distintas hojas del mismo libro. Además, ordenaremos las tablas-resultado en función del criterio que elijamos. Como resultado, ganaremos un considerable ahorro de tiempo.

Para ello, usaremos de ejemplo un informe diario de una empresa de mantenimiento, en el que se recogen los movimientos de salida del almacén del material utilizado en cada uno de los trabajos.

total-operaciones-matematicas-macros

El significado de cada uno de los campos es el siguiente:

  • OT: número de la orden de trabajo
  • Descripción OT: descripción de la orden de trabajo
  • Código artículo: código que tiene el artículo en el almacén de la empresa
  • Descripción artículo: descripción del artículo
  • Importe: importe total. Unidades x precio unitario.
  • Unidades: número de artículos que se han usado.
  • Precio unitario: precio por unidad.
  • Fecha: fecha en la que se ha sacado del almacén el artículo.
  • Usuario: trabajador que ha sido el encargado de realizar el trabajo.
  • Tipo de trabajo: Si el trabajo ha consistido en una reparación o una mejora de la instalación.
  • Grupo de trabajo: número del grupo de trabajo al que pertenece.

 

En realidad, la información no siempre nos llega como queremos. De hecho, en la tabla, están mezclados los movimientos de materiales de distintos grupos de trabajo. La idea de este ejercicio es filtrar las filas de la tabla Excel y distribuir la información en varias hojas en función del grupo de trabajo de manera automática.

De esta manera, obtendríamos en la hoja “Grupo 1” solo los movimientos de materiales que han realizado las personas que conforman el grupo 1, y lo mismo para el resto de grupos. Como resultado, obtenemos las siguientes figuras.

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

¿Cómo podemos conseguir filtrar las filas de la tabla Excel y repartirlas en varias hojas? A continuación te lo muestro, es muy fácil.

En primer lugar, vamos a crear dos botones, el primero ejecutará la macro para generar los informes, y el segundo lo usaremos para borrarlos.

Macro 1. Generar informes de materiales según grupo de trabajo.

Sub Informe()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

'Buscamos la última fila con datos de cada hoja

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Recorremos los registros de la tabla y repartiéndolos en el resto de hojas en función del grupo de trabajo

Sheets("Informe").Select

f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

For i = 2 To f

Sheets("Informe").Select

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 1").Select

Cells(filagrupo1, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo1 = filagrupo1 + 1

goto salto

End If

'Si pertenece al GRUPO 2

If Cells(i, "K") = 2 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 2").Select

Cells(filagrupo2, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo2 = filagrupo2 + 1

goto salto

End If

'Si pertenece al GRUPO 3

If Cells(i, "K") = 3 Then

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 3").Select

Cells(filagrupo3, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo3 = filagrupo3 + 1

End If

salto:

Next

'Calculamos el importe total de cada grupo, dos filas más abajo del último registro

'GRUPO 1

Sheets("Grupo 1").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo1, "E")))

Cells(filagrupo1, "E") = totalizar

Cells(filagrupo1, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 2

Sheets("Grupo 2").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo2, "E")))

Cells(filagrupo2, "E") = totalizar

Cells(filagrupo2, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 3

Sheets("Grupo 3").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo3, "E")))

Cells(filagrupo3, "E") = totalizar

Cells(filagrupo3, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'Llamamos a la macro para ordenar el informe

Call Ordenfinal

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

'Activamos el refresco de pantalla

Application.ScreenUpdating = True

End Sub

Macro 2. Borrar informes de materiales.

Sub Borrar()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

'Mostramos mensaje de aviso porque se borrarán datos

resultado = MsgBox("¿Seguro? Se perderán los cálculos", vbYesNo + vbExclamation, "Advertencia")

Select Case resultado

Case vbNo:

GoTo final

End Select

'Borramos los datos de las hojas de Grupos

Sheets("Grupo 1").Activate

Range("A2:K500").ClearContents

Sheets("Grupo 2").Activate

Range("A2:K500").ClearContents

Sheets("Grupo 3").Activate

Range("A2:K500").ClearContents

'Volvemos a la hoja Informe

Sheets("Informe").Activate

final:

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = True

End Sub

Macro 3. Ordenar informes según importe descendente.

Sub Ordenfinal()

'Ordenamos las hojas de los grupos por el campo importe, de mayor a menor

'GRUPO 1

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo1, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 2

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo2, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 3

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo3, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

End Sub

Seguidamente, vamos a analizar la primera macro, que se encarga de filtrar las filas de la tabla Excel y repartirlas en varias hojas en función del grupo de trabajo.

Antes de nada, y lo haremos prácticamente en todos los desarrollos, desactivaremos el refresco de pantalla. Es más, en este caso es algo obligado, ya que el programa va a estar consultando una tabla, y copiando la información en distintas hojas. En caso contrario, además de estar viendo pestañeos, puede ralentizarse la ejecución del programa.

Sub Informe()

'Desactivamos el refresco de pantalla para agilizar la macro

Application.ScreenUpdating = False

A continuación, como vamos a ir copiando registros de la tabla a las hojas de cada grupo, tendremos que saber cuál es la última fila con datos de cada hoja, para copiar el registro seleccionado justo en la siguiente y no solapar información.

Por eso, hemos usado un offset(1,0), es decir, al último número de fila que contiene información, le sumamos uno y lo guardamos en la variable. Además, deberemos hacer esto en cada hoja.

'Buscamos la última fila con datos de cada hoja

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Después de filtrar las filas de la tabla Excel por grupo de trabajo, toca repartirlas entre las distintas hojas según el grupo de trabajo al que pertenecen.

Para ello, hemos preparado un bucle que va a recorrer todos los registros de la tabla.

Como no conocemos de antemano el número de registros que trae el informe matriz, el bucle va a barrer desde la fila 2 hasta la fila f, que es la última fila que contiene información en la hoja “Informe”. En este caso el offset es 0.

'Recorremos los registros de la tabla y repartiéndolos en el resto de hojas en función del grupo de trabajo

Sheets("Informe").Select

f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Seguidamente, comenzamos a barrer los registros de la tabla de la hoja “Informe” para i=2, y comprobamos si el valor de la columna K es 1, es decir, si pertenece al grupo de trabajo 1.

For i = 2 To f

Sheets("Informe").Select

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

total-operaciones-matematicas-macros

Como vemos en la figura anterior, el primer movimiento de la tabla lo realizó una persona del grupo 1, por tanto debe ser copiado en su correspondiente hoja.

En este caso vamos a usar un copiado total (valores y formato) usando la propiedad xlPasteAll.

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 1").Select

Cells(filagrupo1, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

Una vez que hemos copiado el registro, sumamos una unidad a la variable que almacena el número de fila, para que apunte a la siguiente fila y sea donde se copie el próximo registro del grupo 1.

filagrupo1 = filagrupo1 + 1

Por último, como el registro ya ha sido copiado, ya no hay que seguir comprobando si pertenece a los grupos 2 y 3, luego saltamos a la etiqueta salto y pasamos al siguiente registro.

goto salto

End If

Así mismo, los dos siguientes registros (i=3 e i=4) pertenecen al grupo 1 y son copiados en su correspondiente hoja.

total-operaciones-matematicas-macros

Sin embargo, en el caso i=5, al no cumplirse la condición de pertenecer al grupo 1, pasaría a la siguiente,

'Si pertenece al GRUPO 1

If Cells(i, "K") = 1 Then

'Si pertenece al GRUPO 2

If Cells(i, "K") = 2 Then

total-operaciones-matematicas-macros

Y, como se cumple, será copiado en la hoja del grupo 2.

Range(Cells(i, "A"), Cells(i, "K")).Select

Selection.Copy

Sheets("Grupo 2").Select

Cells(filagrupo2, "A").Select

Selection.PasteSpecial Paste:=xlPasteAll, operation:=xlNone, skipblanks _
:=False, Transpose:=False

filagrupo2 = filagrupo2 + 1

goto salto

End If

Y, así seguiríamos hasta i=101, que es la última fila con información de nuestro ejemplo.

Además, como añadido, he querido incluir dos detalles.

En primer lugar, el cálculo del importe total gastado en materiales por cada grupo de trabajo. Así, este resultado se coloca automáticamente en la siguiente fila a la última con registros de cada hoja, en la columna de “importe”.

Para ello, usamos la función Suma aplicada a la columna “importe” del rango de registros copiados a cada hoja. Después, simplemente hemos dado un poco de formato centrando el resultado en la celda y marcándolo en negrita.

'Calculamos el importe total de cada grupo, dos filas más abajo del último registro

'GRUPO 1

Sheets("Grupo 1").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo1, "E")))

Cells(filagrupo1, "E") = totalizar

Cells(filagrupo1, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 2

Sheets("Grupo 2").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo2, "E")))

Cells(filagrupo2, "E") = totalizar

Cells(filagrupo2, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

'GRUPO 3

Sheets("Grupo 3").Select

totalizar = Application.WorksheetFunction.Sum(Range(Cells(2, "E"), Cells(filagrupo3, "E")))

Cells(filagrupo3, "E") = totalizar

Cells(filagrupo3, "E").Select

With Selection

.HorizontalAlignment = xlCenter

End With

Selection.Font.Bold = True

En segundo lugar, he incluido la ordenación automática de los registros de cada hoja de forma descendente según el importe.

Para ello, hemos creado la tercera macro, y simplemente la llamamos desde la primera usando la función call.

'Llamamos a la macro para ordenar el informe

Call Ordenfinal

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

'Activamos el refresco de pantalla

Application.ScreenUpdating = True

End Sub

También, como recordatorio para la tercera macro, os dejo el enlace donde explico cómo ordenar una tabla usando macros.

Sub Ordenfinal()

'Ordenamos las hojas de los grupos por el campo importe, de mayor a menor

'GRUPO 1

Sheets("Grupo 1").Select

filagrupo1 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo1, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 2

Sheets("Grupo 2").Select

filagrupo2 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo2, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'GRUPO 3

Sheets("Grupo 3").Select

filagrupo3 = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells(2, "A"), Cells(filagrupo3, "K")).Sort Key1:=Range("E:E"), Order1:=xlDescending, Header:=xlNo

'Abrimos la hoja Grupo 1

Sheets("Grupo 1").Select

End Sub

Por último, la segunda macro simplemente realiza una selección de celdas y borra su contenido. Es muy básica, pero si tenéis cualquier duda, no dudéis en consultármela.

Si quisiéramos que el filtrado tuviese en cuenta otro campo además del grupo de trabajo, por ejemplo importes superiores a 100€, modificaríamos el condicional de la siguiente manera,

'Si pertenece al GRUPO 1 y el importe es superior a 100€

If Cells(i, "K") = 1  and Cells(i, "E")>100 Then

Así, solo se copiarían en la hoja “Grupo 1”, los movimientos superiores a 100€ realizados por personas pertenecientes a este grupo.

Con esto llegamos al final de la vigesimoctava lección, en la que has aprendido a filtrar las filas de una tabla Excel y repartir la información en varias hojas en función de la información.

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

« EP4. Cómo cruzar datos de distintas tablas con macros.

EP6. Cómo buscar errores 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