Ahora veremos como se puede aplicar una macro para realizar una suma condicional con información contenida en múltiples hojas, esto es, con un solo clic poder obtener un total en base a una condición y datos que se encuentran en diferentes hojas, esta macro es de gran ayuda en formatos contables que requieren procesar muchos datos contenidos en varias hojas.
Particularmente no me gusta separar datos del mismo tipo o de la misma naturaleza en distintas hojas, prefiero poner todo en una sola hoja como base de datos, de ese modo es mas sencillo trabajar después con esos datos, pero si tu prefieres separar por ejemplo, las ventas mensuales, una hoja para cada mes o incluso una hoja para cada día, entonces este ejemplo te sera de enorme ayuda.
Link de descarga al final del post
Para poder hacer ese consolidado o suma de algún dato en particular, sera necesario recorrer las hojas del libro que contienen la informacion necesaria para ese caso, no importa si son 10 o 100 hojas, la idea es que se haga de una manera rápida y automatizada, con un solo clic.
Cabe precisar que la estructura de todas esas hojas que contienen la informacion deberán tener la misma estructura, es decir, que tengan las mismas columnas de datos en el mismo orden.
Obtener el total de ventas de un vendedor en particular
La idea en este ejemplo es poder obtener el total de ventas que un vendedor en particular ha realizado, para ello sera necesario simplemente indicar el nombre del vendedor y al presionar en un botón deberá recorrerse todas las hojas del libro ( excepto la que contendrá la suma ), buscar el nombre de ese vendedor en la columna que corresponda y sumar el importe de la venta de cada registro donde aparece.
En la primera hoja del libro se realizara dicha suma, para ello usaremos una celda donde vamos a poner en nombre del vendedor, en la celda de a lado deberá aparecer la suma al momento de presionar en un botón.
La celda donde se pone el nombre del vendedor tiene una lista desplegable que esta ligada a una hoja donde esta la lista de todos los vendedores, de ese modo se podrá seleccionar el nombre de esa lista.
Este libro contiene múltiples hojas, la macro deberá recorrer a partir de la tercera hoja, las dos primeras no porque la primera es donde se realizara la suma y la segunda es la que contiene la lista de vendedores.
Macro para recorrer las hojas y hacer la suma condicional
La siguiente macro es la que recorre las hojas y realizara la suma de las ventas del vendedor seleccionado en la primera hoja.
Option Explicit
Sub SumaDato()
Dim i, j As Integer
Dim filas As Integer
Dim Vendedor As String
Dim Suma As Double
Vendedor = range("nombre")
Suma = 0
For i = 3 To Worksheets.Count
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To filas
If Worksheets(i).Cells(j, 3) = Vendedor Then
Suma = Suma + Worksheets(i).Cells(j, 5).Value
End If
Next j
Next i
range("total").Value = Suma
End Sub
Explicando la macro que recorre las hojas y hace la suma
Dim i, j As Integer
Dim filas As Integer
Dim Vendedor As String
Dim Suma As Double
En esa parte se definen las variables que usaremos en el proceso
Vendedor = range("nombre")
A la variable vendedor le asignamos el dato que ponemos en la celda del nombre del vendedor en la primera hoja, a esa celda le ponemos un nombre de rango, en este caso “nombre”.
Suma = 0
Aquí se inicia la variable suma en cero.
Vamos a recorrer las hojas con un ciclo for.
For i = 3 To Worksheets.Count
i es el contador, en este caso el contador inicia en 3 porque es el numero de la hoja donde iniciaremos, recordar que las dos primeras no se recorren, con Worksheets.Count
obtenemos en numero de hojas que tiene el libro, así que con ese código le estamos diciendo que recorreremos desde 3 hasta n numero de hojas.
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
Con ese código obtenemos el numero de la ultima fila con datos en la hoja que en ese momento se esta procesando.
For j = 2 To filas
Con este código le decimos que vamos a recorrer las filas de esa hoja a partir de la numero dos, hasta la ultima fila con datos.
If Worksheets(i).Cells(j, 3) = Vendedor Then
Suma = Suma + Worksheets(i).Cells(j, 5).Value
End If
Este código es el que hace la comparación del nombre de empleado que se pone en la primera hoja, mismo que esta referenciado con la variable “Vendedor” contra el nombre del vendedor que se encuentra en la fila que en ese momento esta recorriendo la macro, si es igual entonces sumara la cantidad que esta en la columna que contiene el importe de la venta realizada, si no es igual continuara con el bucle para ir a la siguiente fila.
Una vez que termina de recorrer todas las hojas, se pone el valor de la variable suma en la celda donde va el total de la primera hoja del libro, justo a lado del nombre del vendedor seleccionado.
range("total").Value = Suma
Como hemos visto, esta macro es muy corta, pero a la vez muy eficiente y poderosa, espero que les sea de mucha ayuda, estaré atento a sus comentarios.
A continuación les dejo el vídeo con la explicación para una mejor comprensión del ejercicio.
Alejo Sanchez dice
Nolberto buenos días…
Muchas gracias por la explicación…utilizando la lista desplegable me funcionó perfectamente la macro, pero al querer hacer el ejercicio con la alternativa de que salgan todos los vendedores con su respectiva suma, no me funcionó la macro que publicó en los comentarios…me sale el mismo error de la persona que le pregunta allí mismo…(Error de compilación: No se ha definido la variable)…Le agradezco si nos puede ampliar la información.
Luis Martínez dice
Si en lugar de la suma acumulada quisiera el promedio, ¿cómo sería?
Manuel dice
necesito hacer esto pero para diferentes archivos
ayuda plisssssss
Edgar Acedo dice
Buenos días Nolberto, gracias por su aporte, lo he probado y me ha funcionado, sin embargo necesito habilitarlo para que se repita en varias celdas a la vez, he visto que alguien comentó esta duda en su video en youtube, pero no me ha quedado clara la respuesta:
Formulas Excel
Hace 7 meses
Si es posible, para ello es necesario meter el ciclo for que se indica en el video, dentro de otro ciclo.
Sub test()
Dim VendedorAs String
Range(“a1”).Activate
Do While ActiveCell.Value “”
Vendedor= ActiveCell.Value
for i = 1 to Worksheets.Count
….
…..
Next i
Loop
End Sub
De qué manera se añadiría esta información en el macro original? Gracias de antemano, que tenga buen día.
Camila dice
Tengo esta misma duda, lo he intentado de varias formas pero no lo consigo. Por favor colaborenos
Gabriel dice
Me podrias ayudar, tambien intento hacerlo pero no se como
Juan Gonzalez dice
Hola muchas gracias por la ayuda….tengo unda duda quedaria masomenos asi? para que sume sin poner listas? es decir que escriba yo los valores que quiero y me los valla acomodando en cada una de las filas…. la vdd no lo entiendo muy bien.. una diculpa,, nos e donde meter ese ciclo…. para que se repita con varios valores o no en una lista…
Option Explicit
Sub SumaDato()
Sub test()
Dim VendedorAs String
Dim i, j As Integer
Dim filas As Integer
Dim Vendedor As String
Dim Suma As Double
Vendedor = range(“nombre”)
Suma = 0
Range(“a1”).Activate
Do While ActiveCell.Value “”
Vendedor= ActiveCell.Value
for i = 1 to Worksheets.Count
For i = 3 To Worksheets.Count
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To filas
If Worksheets(i).Cells(j, 3) = Vendedor Then
Suma = Suma + Worksheets(i).Cells(j, 5).Value
End If
Next j
Next i
range(“total”).Value = Suma
Next i
Loop
End Sub
End Sub
paola hernandez dice
Buen dia Juan, no he podido resolver la formula, esta que ud publica no me arranca.
Nolberto Lopez dice
Se debe contar con un bucle exterior que recorra la columna que contiene los nombres en la hoja destinada para ello, luego en cada ciclo se debe obtener el nombre y poner en 0 la suma.
Por cada nombre se genera un ciclo para todas las hojas y en cada hoja hay otro ciclo para recorrer las filas de la hoja activa en el momento.
Al final de cada ciclo exterior se pone la suma del nombre que corresponde, en el ejemplo se considera que la suma va en la columna siguiente a la de los nombres.
Sub testSumaHojas()
Dim i, j As Integer
Dim filas As Integer
Dim Vendedor As String
Dim Suma As Double
Dim rng As Range
Dim r As Range
Dim uFila As Long
Application.ScreenUpdating = False
uFila = Sheet3.Cells(Rows.Count, 1).End(xlUp).Row
‘Sheets3 es el nombre interno de la hoja donde esta la lista de vendedores, los cuales estaran en la col B a partir de la fila 1
Set rng = Sheet3.Range(“B2:B” & uFila)
For Each r In rng
Vendedor = r
Suma = 0
For i = 1 To Worksheets.Count
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row
For j = 2 To filas
If Worksheets(i).Cells(j, 3) = Vendedor Then
Suma = Suma + Worksheets(i).Cells(j, 5).Value
End If
Next j
Next i
r.Offset(0, 1) = Suma
Next r
Application.ScreenUpdating = True
End Sub
Julio Enrique Barrenechea Montenegro dice
Buenas noches Sr Nolberto, quise aplicar la macro que envio, a la misma lista de vendedores que explico en You tube, pero no me corre la macro, reemplace la anterior macro por la nueva, me sale error de compilación y me señala sheet 3.
Lo que necesito para mi proyecto es que sume el total y lo coloque al lado de cada nombre en un lista la suma, pero no la lista desplegable. osea vendedor1 suma total, vendedor2 suma total, vendedor3 suma total y asi sucesivamente hasta el final
Barbara dice
lograste hacerlo?
Saul LA dice
Sr. Nolberto Lopez muchas muchas gracias por el grandisimo aporte, lo he adaptado a mi proyecto y me ha funcionado de maravilla, Dios le bendiga!
Saludos!!
Raúl JIRÓN dice
Muchas gracia por su aporte, me a servido mucho, lo modifique para mi trabajo, agradecido desde Talcahuano, Chile
Danilo dice
Excelente ejercicio
Gracias por compartir tus conocimientos.
Danilo
Mario Herrera dice
Gracias por tu Ayuda, Nolberto
Danilo dice
Excelente ejercicio.
Servirá de mucho.
Gracias por compartir conocimiento.
Saludos!!!!!
RAYMUNDO VALENZUELA dice
no funciona correctamente
Nolberto Lopez dice
Saludos Raymundo.
Puede descargar de nuevo.
Se sustituye
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp)
por
filas = Worksheets(i).Cells(Rows.Count, 1).End(xlUp).Row