El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
HOME Els meus cursos Finalitzats
◄ Anterior
◄

Desenvolupar un quadre de comandament amb VBA

Fi!

En aquest tema, aprendrem a desenvolupar un quadre de comandament (dashboard) utilitzant VBA a Excel. Un quadre de comandament és una eina visual que permet monitoritzar i analitzar dades de manera eficient. Utilitzarem VBA per automatitzar la creació i l'actualització del quadre de comandament.

Objectius del tema

  • Comprendre els components bàsics d'un quadre de comandament.
  • Aprendre a crear gràfics i taules dinàmiques amb VBA.
  • Automatitzar l'actualització de dades en el quadre de comandament.
  • Implementar funcionalitats interactives amb VBA.

Components d'un quadre de comandament

Un quadre de comandament típicament inclou:

  1. Gràfics: Per visualitzar les dades de manera gràfica.
  2. Taules dinàmiques: Per resumir i analitzar les dades.
  3. Controls interactius: Com botons, llistes desplegables, etc., per permetre la interacció de l'usuari.

Creació d'un quadre de comandament amb VBA

Pas 1: Preparar les dades

Abans de començar a programar, assegura't que les dades estiguin ben organitzades en un full de càlcul. Per exemple, podem tenir un full de càlcul anomenat "Dades" amb les següents columnes: Data, Vendes, Regió, Producte.

Pas 2: Crear gràfics amb VBA

Utilitzarem VBA per crear un gràfic de línies que mostri les vendes per mes.

Sub CrearGraficVendes()
    Dim ws As Worksheet
    Dim chartObj As ChartObject
    Dim chart As Chart
    
    ' Definir el full de càlcul
    Set ws = ThisWorkbook.Sheets("Dades")
    
    ' Afegir un objecte de gràfic
    Set chartObj = ws.ChartObjects.Add(Left:=100, Width:=375, Top:=50, Height:=225)
    Set chart = chartObj.Chart
    
    ' Definir l'origen de les dades
    chart.SetSourceData Source:=ws.Range("A1:B13")
    
    ' Configurar el tipus de gràfic
    chart.ChartType = xlLine
    
    ' Configurar el títol del gràfic
    chart.HasTitle = True
    chart.ChartTitle.Text = "Vendes per Mes"
    
    ' Configurar els títols dels eixos
    chart.Axes(xlCategory, xlPrimary).HasTitle = True
    chart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "Mes"
    chart.Axes(xlValue, xlPrimary).HasTitle = True
    chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Vendes"
End Sub

Pas 3: Crear una taula dinàmica amb VBA

Crearem una taula dinàmica per resumir les vendes per regió.

Sub CrearTaulaDinamica()
    Dim wsDades As Worksheet
    Dim wsPivot As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable
    
    ' Definir els fulls de càlcul
    Set wsDades = ThisWorkbook.Sheets("Dades")
    Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsDades)
    wsPivot.Name = "TaulaDinamica"
    
    ' Crear una memòria cau de taula dinàmica
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsDades.Range("A1:D13"))
    
    ' Crear la taula dinàmica
    Set pt = pc.CreatePivotTable(TableDestination:=wsPivot.Range("A3"), TableName:="PivotTable1")
    
    ' Configurar els camps de la taula dinàmica
    With pt
        .PivotFields("Regió").Orientation = xlRowField
        .PivotFields("Producte").Orientation = xlColumnField
        .PivotFields("Vendes").Orientation = xlDataField
        .PivotFields("Vendes").Function = xlSum
    End With
End Sub

Pas 4: Automatitzar l'actualització del quadre de comandament

Podem crear un botó que actualitzi les dades del quadre de comandament.

Sub ActualitzarQuadreComandament()
    ' Actualitzar la taula dinàmica
    ThisWorkbook.Sheets("TaulaDinamica").PivotTables("PivotTable1").PivotCache.Refresh
    
    ' Actualitzar el gràfic
    ThisWorkbook.Sheets("Dades").ChartObjects(1).Chart.Refresh
End Sub

Pas 5: Afegir controls interactius

Podem afegir un botó per actualitzar el quadre de comandament.

Sub AfegirBotons()
    Dim ws As Worksheet
    Dim btn As Button
    
    ' Definir el full de càlcul
    Set ws = ThisWorkbook.Sheets("Dades")
    
    ' Afegir un botó
    Set btn = ws.Buttons.Add(Left:=10, Top:=10, Width:=100, Height:=30)
    btn.OnAction = "ActualitzarQuadreComandament"
    btn.Caption = "Actualitzar"
End Sub

Exercici pràctic

  1. Preparar les dades: Crea un full de càlcul amb dades de vendes similars a les descrites anteriorment.
  2. Crear el gràfic: Utilitza el codi proporcionat per crear un gràfic de línies.
  3. Crear la taula dinàmica: Utilitza el codi proporcionat per crear una taula dinàmica.
  4. Automatitzar l'actualització: Implementa el codi per actualitzar el quadre de comandament.
  5. Afegir controls interactius: Afegeix un botó per actualitzar el quadre de comandament.

Solució de l'exercici

Segueix els passos descrits en els exemples de codi per completar l'exercici. Si tens problemes, revisa el codi i assegura't que les referències als fulls de càlcul i les cel·les siguin correctes.

Conclusió

En aquest tema, hem après a desenvolupar un quadre de comandament amb VBA a Excel. Hem cobert la creació de gràfics, taules dinàmiques, l'automatització de l'actualització de dades i la implementació de controls interactius. Aquestes habilitats són essencials per crear eines de monitorització i anàlisi de dades eficients i interactives.

◄ Anterior
◄
Fi!
El Projecte | Sobre nosaltres | Contribuir | Donacions | Llicència
© Copyright 2024. Tots els drets reservats