Hoy, vamos a clasificar automáticamente en Excel los costes de un listado en función de las empresas que lo forman. Para ello, partimos de un listado como el que se muestra en la figura.
El ejemplo corresponde a los gastos generales de un hotel, como son el servicio de catering, jardinería, limpieza, mobiliarios y traslados de personas. Para ello, el hotel cuenta con el servicio de varias empresas, recogidas en la tabla de la derecha. Además, cada una de las empresas realiza más de un tipo de servicio al hotel.
Como resultado, queremos clasificar y calcular el desglose de gastos de cada uno de los servicios sin tener que usar filtros ni calculadoras en Excel, simplemente pulsando un botón.
De esta forma, hemos creado dos macros; una para limpiar resultados y otra para clasificar y calcular los costes en la hoja Excel de manera automática.
Macro 1. Limpiar tabla de gastos.
'Macro que limpia la tabla de gastos
Application.ScreenUpdating = False
'Mostramos advertencia de que se borrarán los datos
resultado = MsgBox("¿Seguro? Se perderán los datos", vbYesNo + vbExclamation, "Advertencia")
Select Case resultado
Case vbNo:
GoTo final
End Select
'Borramos los valores de la tabla de gastos
Range("K9:K18").ClearContents
final:
Application.ScreenUpdating = True
End Sub
En primer lugar, mostramos una advertencia avisándonos de que los datos se perderán si seguimos adelante. En caso de elegir “No”, saltamos al final de la macro sin hacer nada. En caso contrario, borramos el rango de celdas correspondientes al resultado.
A continuación, te dejo un enlace al tema donde explico cómo utilizar la función msgbox en VBA.
Ahora, vamos a ver con mayor detalle el código para clasificar y calcular los costes en un listado Excel.
Macro 2. Calcular el desglose de gastos.
'Macro que calcula automáticamente el desglose de gastos de un hotel
Application.ScreenUpdating = False
'Buscamos la última fila de datos
f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
'Inicializamos las variables que vamos a usar como contadores
cateringalmuerzo = 0
cateringcena = 0
jardinespoda = 0
jardinesriego = 0
limpiezahabitaciones = 0
limpiezacomunes = 0
mobiliarioscama = 0
mobiliariosmueble = 0
trasladosaeropuerto = 0
trasladosestaciontren = 0
'Comenzamos la iteración para calcular los gastos en servicios de cada empresa a lo largo del mes
'Servicio de Catering
For i = 2 To f
'Si la columna empresa es "CATERING S.A." y además la columna concepto es "ALMUERZO" entonces
If (Cells(i, "D") = "CATERING S.A." And Cells(i, "B") = "ALMUERZO") Then
cateringalmuerzo = cateringalmuerzo + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "CATERING S.A." y además la columna concepto es "CENA" entonces
If (Cells(i, "D") = "CATERING S.A." And Cells(i, "B") = "CENA") Then
cateringcena = cateringcena + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "JARDINES S.A." y además la columna concepto es "PODA" entonces
If (Cells(i, "D") = "JARDINES S.A." And Cells(i, "B") = "PODA") Then
jardinespoda = jardinespoda + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "JARDINES S.A." y además la columna concepto es "RIEGO" entonces
If (Cells(i, "D") = "JARDINES S.A." And Cells(i, "B") = "RIEGO") Then
jardinesriego = jardinesriego + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "LIMPIEZA S.A." y además la columna concepto es "HABITACIONES" entonces
If (Cells(i, "D") = "LIMPIEZA S.A." And Cells(i, "B") = "HABITACIONES") Then
limpiezahabitaciones = limpiezahabitaciones + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "LIMPIEZA S.A." y además la columna concepto es "COMUNES" entonces
If (Cells(i, "D") = "LIMPIEZA S.A." And Cells(i, "B") = "COMUNES") Then
limpiezacomunes = limpiezacomunes + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "MOBILIARIOS S.A." y además la columna concepto es "CAMA" entonces
If (Cells(i, "D") = "MOBILIARIOS S.A." And Cells(i, "B") = "CAMA") Then
mobiliarioscama = mobiliarioscama + Cells(i, "C")
GoTo salto
'Si la columna empresa es "MOBILIARIOS S.A." y además la columna concepto es "MUEBLE" entonces
ElseIf (Cells(i, "D") = "MOBILIARIOS S.A." And Cells(i, "B") = "MUEBLE") Then
mobiliariosmueble = mobiliariosmueble + Cells(i, "C")
GoTo salto
End If
'Si la columna empresa es "TRASLADOS S.A." y además la columna concepto es "AEROPUERTO" entonces
If (Cells(i, "D") = "TRASLADOS S.A." And Cells(i, "B") = "AEROPUERTO") Then
trasladosaeropuerto = trasladosaeropuerto + Cells(i, "C")
GoTo salto
'Si la columna empresa es "TRASLADOS S.A." y además la columna concepto es "MUEBLE" entonces
ElseIf (Cells(i, "D") = "TRASLADOS S.A." And Cells(i, "B") = "ESTACION TREN") Then
trasladosestaciontren = trasladosestaciontren + Cells(i, "C")
End If
salto:
Next
'Representamos los resultado en la tabla de gastos
Cells(9, "K") = cateringalmuerzo
Cells(10, "K") = cateringcena
Cells(11, "K") = jardinespoda
Cells(12, "K") = jardinesriego
Cells(13, "K") = limpiezahabitaciones
Cells(14, "K") = limpiezacomunes
Cells(15, "K") = mobiliarioscama
Cells(16, "K") = mobiliariosmueble
Cells(17, "K") = trasladosaeropuerto
Cells(18, "K") = trasladosestaciontren
Application.ScreenUpdating = True
End Sub
Desmenuzamos el código poco a poco para aprender a clasificar y calcular costes en un listado Excel.
Lo primero que debemos hacer cuando realizamos cálculos es desactivar la pantalla para que el código se ejecute de una manera más fluida.
'Macro que calcula automáticamente el desglose de gastos de un hotel
Application.ScreenUpdating = False
A continuación buscamos la última fila del listado que contiene valores en la columna A. En este caso la fila nº 50.
f = Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row
Ponemos a cero las variables que vamos a utilizar para ir acumulando cantidades conforme vamos recorriendo las filas sumando.
cateringalmuerzo = 0
cateringcena = 0
jardinespoda = 0
jardinesriego = 0
limpiezahabitaciones = 0
limpiezacomunes = 0
mobiliarioscama = 0
mobiliariosmueble = 0
trasladosaeropuerto = 0
trasladosestaciontren = 0
Para optimizar el código, hacemos una sola iteración, es decir, barremos una sola vez el listado. Podríamos haber caído en la tentación de hacer tantos bucles como conceptos de gastos hay, pero eso ralentizaría bastante la ejecución del código y cargaría demasiado la CPU.
Conforme vamos recorriendo filas, vamos comprobando a qué partida corresponde el gasto.
Empezamos con la primera iteración i=2 (fila 2), y comprobamos si el valor de la columna D “Empresa” es “CATERING S.A.” y, además, el valor de la columna B “Concepto” es “ALMUERZO”.
'Servicio de Catering
For i = 2 To f
'Si la columna empresa es "CATERING S.A." y además la columna concepto es "ALMUERZO" entonces
If (Cells(i, "D") = "CATERING S.A." And Cells(i, "B") = "ALMUERZO") Then
cateringalmuerzo = cateringalmuerzo + Cells(i, "C")
GoTo salto
End If
No es el caso, así que pasamos al siguiente condicional. Comprobamos si el valor de la columna D “Empresa” es “CATERING S.A.” y, además, el valor de la columna B “Concepto” es “CENA”.
If (Cells(i, "D") = "CATERING S.A." And Cells(i, "B") = "CENA") Then
cateringcena = cateringcena + Cells(i, "C")
GoTo salto
End If
Tampoco es el caso, así que pasamos al siguiente. Comprobamos si el valor de la columna D “Empresa” es “JARDINES S.A.” y, además, el valor de la columna B “Concepto” es “PODA”.
If (Cells(i, "D") = "JARDINES S.A." And Cells(i, "B") = "PODA") Then
jardinespoda = jardinespoda + Cells(i, "C")
GoTo salto
End If
End Sub
En este caso sí, así que sumamos el valor de la columna C “Importe” a lo que tenga almacenado la variable cateringalmuerzo (que todavía es 0 porque no le hemos sumado nada aún). Quedaría:
jardinespoda = 0 + 500 = 500
A continuación, saltamos a la marca “salto” porque no hace falta seguir preguntando a qué concepto pertenece ya que ha sido identificado como “Poda” de la empresa “JARDINES S.A.”
Continuamos con la segunda iteración i=3 (fila 3), y vamos comprobando con qué concepto y empresa se corresponde. En este caso, se trata de la empresa “LIMPIEZA S.A.” y el concepto “HABITACIONES”. El contador quedaría de la siguiente manera:
Limpiezahabitaciones = 0 + 200 = 200
If (Cells(i, "D") = "LIMPIEZA S.A." And Cells(i, "B") = "HABITACIONES") Then
limpiezahabitaciones = limpiezahabitaciones + Cells(i, "C")
GoTo salto
End If
En la séptima iteración i=8 (fila 8), volvemos a encontrarnos con la empresa “JARDINES S.A.” y el concepto “PODA”. Ahora la variable contiene el valor que le introdujimos en la primera iteración, y el cálculo sería el siguiente:
jardinespoda = 500 + 500 = 1000
acumulándose de esta manera en la variable la suma de todos los conceptos de “PODA”.
Una vez realizadas todas las iteraciones, en las variables nos quedan las partidas de cada uno de los conceptos, y las mostramos a continuación en el casillero creado para tal fin.
Cells(9, "K") = cateringalmuerzo
Cells(10, "K") = cateringcena
Cells(11, "K") = jardinespoda
Cells(12, "K") = jardinesriego
Cells(13, "K") = limpiezahabitaciones
Cells(14, "K") = limpiezacomunes
Cells(15, "K") = mobiliarioscama
Cells(16, "K") = mobiliariosmueble
Cells(17, "K") = trasladosaeropuerto
Cells(18, "K") = trasladosestaciontren
Application.ScreenUpdating = True
End Sub
Con esto llegamos al final de la vigesimosexta lección, en la que hemos aprendido a clasificar y calcular costes de manera automática en Microsoft Excel.
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
« EP2. Cómo buscar en un listado con macros.
EP4. Cómo cruzar datos de tablas con macros. »
0 comentarios