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.
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.
¿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.
'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.
'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.
'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.
'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.
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.
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.
Sheets("Informe").Select
'Si pertenece al GRUPO 1
If Cells(i, "K") = 1 Then
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.
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.
Sin embargo, en el caso i=5, al no cumplirse la condición de pertenecer al grupo 1, pasaría a la siguiente,
If Cells(i, "K") = 1 Then
If Cells(i, "K") = 2 Then
Y, como se cumple, será copiado en la hoja del grupo 2.
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.
'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.
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.
'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,
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.
Por último, quiero invitarte a compartir tus dudas en los comentarios o en el foro. Intentaré ayudarte en todo lo que pueda, y así aprenderemos todos.
Nos vemos en el próximo capítulo.
Si te ha servido y quieres donar
Descarga el archivo del ejemplo
2 Comentarios
Enviar un comentario
« EP4. Cómo cruzar datos de distintas tablas con macros.
EP6. Cómo buscar errores con macros. »
Buenas tardes me puedes orientar por favor,
requiero filtar en una tabla todos aquellos articulos que tengan diferencias y me lo traslade a otra hoja tanto el codigo, la descripcion, el lote y la cantidad (direfencia).
saludos!
Hola David,
Para filtrar de una lista los elementos diferentes, tienes el comando AdvancedFilter Unique. Te explico su funcionamiento en el siguiente enlace:
https://www.vbatotal.com/leccion-17-eliminar-duplicados-en-excel-comando-advancedfilter-unique/
Si tienes dudas, abre un hilo en el foro y sube tu archivo para que pueda ayudarte.
Saludos.