8. Proyecto: Automatizar un Reporte

En este proyecto integrador:

Construirás una macro que lee una tabla de ventas y genera un resumen por categoría.
Usarás variables, bucles, condicionales, rangos y una función auxiliar.
Aplicarás buenas prácticas: ScreenUpdating, limpieza previa y estructura en Sub separadas.

Estructura del libro de trabajo

Crea un archivo .xlsm con dos hojas:

Vista tipo Excel: la primera fila muestra las letras de columna; la primera columna muestra los números de fila. En las celdas con fórmula (=...), cada referencia lleva un color distinto. Clic en la referencia: resalta solo esa celda. Clic en el resto de la celda (por ejemplo el = o los operadores): resalta todas las celdas citadas. Repetir el mismo gesto sobre lo ya resaltado lo oculta.

Estructura del libroResumen

Nombra las hojas exactamente 'Ventas' y 'Resumen' para que el código funcione sin modificaciones.

	A	B
1	Nombre de la hoja	Contenido
2	Ventas	Tabla de datos fuente (columnas: Fecha \| Producto \| Categoría \| Monto)
3	Resumen	Aquí la macro escribirá los totales por categoría

Datos de ejemplo para la hoja Ventas (copia desde la fila 1):

Tabla fuente: hoja VentasCatálogo / datos maestros

La categoría está en la columna C (índice 3). La macro agrupará los montos por ese valor.

	A	B	C	D
1	Fecha	Producto	Categoría	Monto
2	2024-01-05	Laptop Pro	Electrónica	1200.00
3	2024-01-07	Teclado	Electrónica	85.00
4	2024-01-08	Silla ergonómica	Muebles	340.00
5	2024-01-10	Monitor 27"	Electrónica	450.00
6	2024-01-12	Escritorio	Muebles	220.00
7	2024-01-14	Mouse inalámbrico	Electrónica	45.00
8	2024-01-15	Lámpara LED	Muebles	60.00
9	2024-01-18	Audífonos	Electrónica	130.00
10	2024-01-20	Estante	Muebles	95.00
11	2024-01-22	Webcam HD	Electrónica	75.00

Función auxiliar: SumarPorCategoria

Primero creamos una función que suma todos los montos de una categoría dada:

Modulo1.bas — función auxiliar

VBA

  Function SumarPorCategoria(ByVal categoria As String) As Double 
      Dim hVentas    As Worksheet 
      Dim ultimaFila As Long 
      Dim i          As Long 
      Dim total      As Double 
    
      Set hVentas = ThisWorkbook.Sheets("Ventas") 
      ultimaFila = hVentas.Cells(hVentas.Rows.Count, 1).End(xlUp).Row 
      total = 0 
    
      For i = 2 To ultimaFila 
          If hVentas.Cells(i, 3).Value = categoria Then 
              total = total + hVentas.Cells(i, 4).Value 
          End If 
      Next i 
    
      SumarPorCategoria = total 
  End Function 

Sub principal: GenerarReporte

Modulo1.bas — sub principal

VBA

  Sub GenerarReporte() 
      Dim hResumen   As Worksheet 
      Dim categorias(1 To 2) As String 
      Dim i As Integer 
    
      ' Configuración inicial 
      Application.ScreenUpdating = False 
      Set hResumen = ThisWorkbook.Sheets("Resumen") 
    
      ' Limpiar resultados anteriores 
      hResumen.Range("A1:B100").ClearContents 
    
      ' Escribir encabezados 
      hResumen.Range("A1").Value = "Categoría" 
      hResumen.Range("B1").Value = "Total Ventas" 
    
      ' Lista de categorías a resumir 
      categorias(1) = "Electrónica" 
      categorias(2) = "Muebles" 
    
      ' Escribir totales por categoría 
      For i = 1 To 2 
          hResumen.Cells(i + 1, 1).Value = categorias(i) 
          hResumen.Cells(i + 1, 2).Value = SumarPorCategoria(categorias(i)) 
      Next i 
    
      ' Formatear encabezado 
      With hResumen.Range("A1:B1") 
          .Font.Bold = True 
          .Interior.Color = RGB(29, 107, 68) 
          .Font.Color = RGB(255, 255, 255) 
      End With 
    
      hResumen.Activate 
      Application.ScreenUpdating = True 
      MsgBox "¡Reporte generado correctamente!" 
  End Sub 

Application.ScreenUpdating = False oculta los cambios visuales mientras la macro corre, acelerando la ejecución. Siempre restáuralo a True al terminar.

Practica en Excel

Abre Excel (o copia los datos con el botón de la tabla), sigue los pasos y comprueba el resultado en tu hoja.

Crea el archivo .xlsm con las hojas "Ventas" y "Resumen".
Copia la tabla de datos de ejemplo en la hoja Ventas (desde A1).
Abre el editor VBA (Alt+F11), inserta un módulo y pega ambos procedimientos.
Ejecuta GenerarReporte() desde Alt+F8 → GenerarReporte.
Verifica que la hoja Resumen muestra los totales de Electrónica y Muebles con encabezado verde.
Agrega 3 filas más a Ventas con una nueva categoría "Papelería", añádela al array y al bucle.
Vuelve a ejecutar y confirma que el resumen incluye Papelería.

Ejercicios prácticos

15 ejercicios para ampliar y personalizar el proyecto de automatización de reportes.

Básico

1. Ejecutar y verificar

Crea el libro con las hojas "Ventas" y "Resumen", copia los datos de ejemplo y ejecuta GenerarReporte. Verifica que Electrónica suma 1985 y Muebles suma 715.

Ver solución

Electrónica: 1200 + 85 + 450 + 45 + 130 + 75 = 1985
Muebles: 340 + 220 + 60 + 95 = 715

Si los números coinciden, la macro funciona correctamente.

2. Agregar categoría Papelería

Añade 3 filas con categoría "Papelería" (montos: 30, 55, 80) a la hoja Ventas. Modifica el array categorias y el bucle para incluir esta nueva categoría en el reporte.

Ver solución

GenerarReporte — array ampliado

VBA

  Dim categorias(1 To 3) As String 
  categorias(1) = "Electrónica" 
  categorias(2) = "Muebles" 
  categorias(3) = "Papelería" 
  ' El bucle For i = 1 To 3 ya lo cubre automáticamente 

3. Cambiar color del encabezado

Modifica el bloque With hResumen.Range("A1:B1") para que el encabezado tenga fondo azul marino (RGB(0, 32, 96)) con texto blanco.

Ver solución

Fragmento de GenerarReporte

VBA

  With hResumen.Range("A1:B1") 
      .Font.Bold = True 
      .Interior.Color = RGB(0, 32, 96) 
      .Font.Color = RGB(255, 255, 255) 
  End With 

4. Mostrar total general

Después de escribir los totales por categoría, agrega una fila de Total General que sume todos los montos de la columna B en la hoja Resumen.

Ver solución

Al final de GenerarReporte

VBA

  Dim filaTotal As Long 
  filaTotal = 2 + UBound(categorias) 
  hResumen.Cells(filaTotal, 1).Value = "TOTAL" 
  hResumen.Cells(filaTotal, 2).Formula = "=SUM(B2:B" & (filaTotal - 1) & ")" 
  hResumen.Cells(filaTotal, 1).Font.Bold = True 
  hResumen.Cells(filaTotal, 2).Font.Bold = True 

5. Ancho de columnas automático

Al final de GenerarReporte, ajusta automáticamente el ancho de las columnas A y B de la hoja Resumen con AutoFit.

Ver solución

Final de GenerarReporte

VBA

1 hResumen.Columns("A:B").AutoFit

Intermedio

6. Categorías dinámicas

En lugar de declarar el array con categorías fijas, reescribe la lógica para que la macro detecte automáticamente todas las categorías únicas de la columna C y las use en el reporte.

Ver solución

Modulo1 — categorías dinámicas

VBA

  Sub GenerarReporteDinamico() 
      Dim hVentas As Worksheet, hResumen As Worksheet 
      Dim ultimaFila As Long, i As Long, j As Long 
      Dim categorias() As String 
      Dim numCats As Integer 
      Dim catActual As String, yaExiste As Boolean 
    
      Set hVentas = ThisWorkbook.Sheets("Ventas") 
      Set hResumen = ThisWorkbook.Sheets("Resumen") 
      Application.ScreenUpdating = False 
      hResumen.Range("A1:B200").ClearContents 
    
      ultimaFila = hVentas.Cells(hVentas.Rows.Count, 1).End(xlUp).Row 
      numCats = 0 
      ReDim categorias(1 To 1) 
    
      For i = 2 To ultimaFila 
          catActual = hVentas.Cells(i, 3).Value 
          yaExiste = False 
          For j = 1 To numCats 
              If categorias(j) = catActual Then yaExiste = True: Exit For 
          Next j 
          If Not yaExiste Then 
              numCats = numCats + 1 
              ReDim Preserve categorias(1 To numCats) 
              categorias(numCats) = catActual 
          End If 
      Next i 
    
      hResumen.Range("A1").Value = "Categoría" 
      hResumen.Range("B1").Value = "Total" 
      For i = 1 To numCats 
          hResumen.Cells(i + 1, 1).Value = categorias(i) 
          hResumen.Cells(i + 1, 2).Value = SumarPorCategoria(categorias(i)) 
      Next i 
    
      hResumen.Columns("A:B").AutoFit 
      Application.ScreenUpdating = True 
      MsgBox "Reporte con " & numCats & " categorías generado." 
  End Sub 

7. Filtrar por mes

Agrega un parámetro mesFiltro As Integer a SumarPorCategoria para que solo sume ventas del mes indicado (usa Month(Cells(i,1).Value)).

Ver solución

Modulo1

VBA

  Function SumarPorCategoriaYMes(ByVal categoria As String, ByVal mesFiltro As Integer) As Double 
      Dim hVentas As Worksheet 
      Dim ultimaFila As Long, i As Long, total As Double 
      Set hVentas = ThisWorkbook.Sheets("Ventas") 
      ultimaFila = hVentas.Cells(hVentas.Rows.Count, 1).End(xlUp).Row 
      total = 0 
      For i = 2 To ultimaFila 
          If hVentas.Cells(i, 3).Value = categoria Then 
              If Month(hVentas.Cells(i, 1).Value) = mesFiltro Then 
                  total = total + hVentas.Cells(i, 4).Value 
              End If 
          End If 
      Next i 
      SumarPorCategoriaYMes = total 
  End Function 

8. Contar ventas por categoría

Agrega una columna C "Cantidad" al reporte que muestre cuántas ventas (filas) tiene cada categoría, no el monto total.

Ver solución

Función auxiliar

VBA

  Function ContarPorCategoria(ByVal categoria As String) As Long 
      Dim hVentas As Worksheet 
      Dim ultimaFila As Long, i As Long, cuenta As Long 
      Set hVentas = ThisWorkbook.Sheets("Ventas") 
      ultimaFila = hVentas.Cells(hVentas.Rows.Count, 1).End(xlUp).Row 
      cuenta = 0 
      For i = 2 To ultimaFila 
          If hVentas.Cells(i, 3).Value = categoria Then cuenta = cuenta + 1 
      Next i 
      ContarPorCategoria = cuenta 
  End Function 

En GenerarReporte, agrega hResumen.Range("C1").Value = "Cantidad" y hResumen.Cells(i+1, 3).Value = ContarPorCategoria(categorias(i)).

9. Ordenar el reporte por total descendente

Después de escribir todos los datos en la hoja Resumen, ordena el rango de datos (sin encabezado) por la columna B de mayor a menor usando el método Sort.

Ver solución

Al final de GenerarReporte

VBA

  Dim rngDatos As Range 
  Dim filaFin As Long 
  filaFin = 1 + UBound(categorias) 
  Set rngDatos = hResumen.Range("A2:B" & filaFin) 
  rngDatos.Sort Key1:=hResumen.Range("B2"), Order1:=xlDescending, Header:=xlNo 

10. Botón para ejecutar la macro

En la hoja Resumen, inserta una forma (rectángulo redondeado) con el texto "Generar Reporte" y asígnale la macro GenerarReporte. Indica los pasos en el editor VBA y en Excel.

Ver solución

Pasos:

En la hoja Resumen, ve a Insertar → Formas → Rectángulo redondeado.
Dibuja la forma y escribe el texto "Generar Reporte".
Haz clic derecho sobre la forma → Asignar macro.
Selecciona GenerarReporte y acepta.

También puedes hacer esto desde VBA:

Crear botón por código

VBA

  Sub AgregarBoton() 
      Dim ws As Worksheet 
      Dim btn As Shape 
      Set ws = ThisWorkbook.Sheets("Resumen") 
      Set btn = ws.Shapes.AddShape(msoShapeRoundedRectangle, 10, 10, 150, 40) 
      btn.TextFrame.Characters.Text = "Generar Reporte" 
      btn.OnAction = "GenerarReporte" 
  End Sub 

Avanzado

11. Calcular ticket promedio

Agrega una columna D "Promedio" al reporte que muestre el monto promedio por venta de cada categoría (total / cantidad). Formatea las celdas con 2 decimales.

Ver solución

En GenerarReporte

VBA

  ' Encabezado 
  hResumen.Range("D1").Value = "Promedio" 
  ' Dentro del bucle For i = 1 To numCats: 
  Dim total As Double, cantidad As Long 
  total = SumarPorCategoria(categorias(i)) 
  cantidad = ContarPorCategoria(categorias(i)) 
  If cantidad > 0 Then 
      hResumen.Cells(i + 1, 4).Value = total / cantidad 
  Else 
      hResumen.Cells(i + 1, 4).Value = 0 
  End If 
  hResumen.Cells(i + 1, 4).NumberFormat = "0.00" 

12. Exportar resumen a hoja nueva

Al final de GenerarReporte, copia el rango de la hoja Resumen a una hoja nueva llamada "Reporte_YYYYMMDD" (con la fecha de hoy en el nombre) para conservar un histórico.

Ver solución

Final de GenerarReporte

VBA

  Dim nombreHoja As String 
  Dim wsHistorico As Worksheet 
  nombreHoja = "Reporte_" & Format(Date, "YYYYMMDD") 
    
  ' Eliminar hoja previa del mismo día si existe 
  On Error Resume Next 
  Application.DisplayAlerts = False 
  ThisWorkbook.Sheets(nombreHoja).Delete 
  Application.DisplayAlerts = True 
  On Error GoTo 0 
    
  Set wsHistorico = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) 
  wsHistorico.Name = nombreHoja 
  hResumen.UsedRange.Copy wsHistorico.Range("A1") 
  MsgBox "Histórico guardado en hoja: " & nombreHoja 

13. Insertar gráfico de barras

Después de generar el resumen, crea un gráfico de barras (xlBarClustered) en la hoja Resumen que muestre las categorías y sus totales.

Ver solución

Agregar gráfico al reporte

VBA

  Sub AgregarGrafico() 
      Dim hResumen As Worksheet 
      Dim cht As ChartObject 
      Dim filaFin As Long 
    
      Set hResumen = ThisWorkbook.Sheets("Resumen") 
      filaFin = hResumen.Cells(hResumen.Rows.Count, 1).End(xlUp).Row 
    
      ' Eliminar gráfico previo si existe 
      Dim co As ChartObject 
      For Each co In hResumen.ChartObjects 
          co.Delete 
      Next co 
    
      Set cht = hResumen.ChartObjects.Add(Left:=200, Top:=10, Width:=350, Height:=220) 
      With cht.Chart 
          .SetSourceData hResumen.Range("A1:B" & filaFin) 
          .ChartType = xlBarClustered 
          .HasTitle = True 
          .ChartTitle.Text = "Ventas por Categoría" 
          .SeriesCollection(1).Name = "Total" 
      End With 
  End Sub 

14. Progreso con barra de estado

Cuando la macro procese muchas categorías, muestra el progreso en la barra de estado de Excel (Application.StatusBar) con el formato "Procesando: Electrónica (1/5)…". Al terminar, restaura Application.StatusBar = False.

Ver solución

Dentro del bucle For i

VBA

  Application.StatusBar = "Procesando: " & categorias(i) & " (" & i & "/" & numCats & ")..." 
  ' ... resto del código del bucle 
    
  ' Al salir del bucle: 
  Application.StatusBar = False 

15. Reporte con evento automático

Combina todo: usa un evento Worksheet_Change en la hoja Ventas para que, cada vez que se agregue un valor en la columna D (Monto), se ejecute automáticamente GenerarReporteDinamico y actualice el resumen en tiempo real.

Ver solución

Módulo de hoja Ventas

VBA

  Private Sub Worksheet_Change(ByVal Target As Range) 
      ' Solo reaccionar si se edita la columna D (Monto) 
      If Target.Column = 4 Then 
          Application.EnableEvents = False 
          Application.ScreenUpdating = False 
          Call GenerarReporteDinamico 
          Application.ScreenUpdating = True 
          Application.EnableEvents = True 
      End If 
  End Sub 

Nota: asegúrate de que GenerarReporteDinamico también tenga ScreenUpdating y EnableEvents desactivados internamente, o usa la versión simplificada sin esos controles para evitar conflictos.

Contenido

📚 Elige un curso

8. Proyecto: Automatizar un Reporte

Estructura del libro de trabajo

Estructura del libroResumen

Tabla fuente: hoja VentasCatálogo / datos maestros

Función auxiliar: SumarPorCategoria

Sub principal: GenerarReporte

Practica en Excel

Ejercicios prácticos

Básico

1. Ejecutar y verificar

2. Agregar categoría Papelería

3. Cambiar color del encabezado

4. Mostrar total general

5. Ancho de columnas automático

Intermedio

6. Categorías dinámicas

7. Filtrar por mes

8. Contar ventas por categoría

9. Ordenar el reporte por total descendente

10. Botón para ejecutar la macro

Avanzado

11. Calcular ticket promedio

12. Exportar resumen a hoja nueva

13. Insertar gráfico de barras

14. Progreso con barra de estado

15. Reporte con evento automático

Pon a prueba tus conocimientos

¡Hecho con mucho cariño! ☕