Ejercicio práctico 3: Cómo clasificar automáticamente en Excel con VBA.

clasificar-en-excel

Escrito por Administrador

31 de agosto de 2021

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.

total-operaciones-matematicas-macros

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.

Sub Limpiar()

'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.

Sub 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.

Sub Gastos()

'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.

'Buscamos la última fila de datos

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.

'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

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”.

'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

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”.

'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

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”.

'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

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

'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

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.

'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

total-operaciones-matematicas-macros

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.

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

« EP2. Cómo buscar en un listado con macros.

EP4. Cómo cruzar datos de tablas 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