Gestire tabelle pivot e grafici Excel con VBA | di Himalaya Bir Shrestha | Giugno 2024 | Intelligenza-Artificiale

Indice contenuti

Dati

Ho preso i dati di esempio dal sito web di Excel-Facile. Questo è un bel sito Web contenente tutorial adatti ai principianti sulle funzionalità di base di Excel e VBA. Il set di dati contiene i record delle vendite di frutta e verdura in paesi specifici nel 2016 e nel 2017. Il set di dati contiene sei campi: ID ordine, Prodotto, Categoria, Importo, Data e Paese. Ho diviso questo set di dati in 2 file CSV chiamati results1.csv E results2.csv per creare tabelle pivot.

Ho estratto questo set di dati nel file RawData foglio di lavoro. Ho creato una gamma dinamica chiamata raw_data_source con la seguente formula:

=OFFSET(RawData!$A$1, 0, 0, COUNTA(RawData!$A:$A), COUNTA(RawData!$1:$1))

Il motivo per cui ho creato un intervallo dinamico era che la dimensione (numero di righe) del set di dati era diversa nei diversi file CSV e volevo utilizzare l'intero set di dati come origine della tabella pivot.

Creazione di un intervallo denominato dinamico come origine per la tabella pivot. Illustrazione dell'autore.

Tabelle e grafici pivot

Il set di dati in raw_data_source gamma nel RawData Il foglio è stato utilizzato come origine dati per creare due semplici tabelle e grafici pivot.

Specificare l'origine della tabella pivot. Illustrazione dell'autore.

Il primo rappresentava le vendite totali di frutta e verdura per singoli prodotti filtrabili per paese con le impostazioni del campo come mostrato.

Primo grafico pivot e sue impostazioni. Illustrazione dell'autore.

Il secondo rappresentava le vendite totali di frutta e verdura per paesi.

Secondo grafico pivot e sue impostazioni. Illustrazione dell'autore.

1. Automatizzazione dell'aggiornamento delle tabelle pivot in base a un nuovo set di dati

In questo passaggio, volevo automatizzare l'aggiornamento dei set di dati dal file che ho specificato. Ho creato un segnaposto nel file Admin foglio per posizionare il percorso dei dati CSV di cui volevo estrarre e aggiornare le tabelle pivot. Ho dato un nome alla cella A2 COME filepath come mostrato di seguito:

Segnaposto per il percorso del file con set di dati. Illustrazione dell'autore.

Il codice per questo passaggio è riportato di seguito nel file UpdateRawData sottoprogramma. Ho dichiarato le variabili wb, ws_admin, ws_rawdataE filepath rispettivamente per i nomi della cartella di lavoro, del foglio Amministrazione, del foglio RawData e del percorso del file contenente il set di dati. Per prima cosa ho cancellato il contenuto del foglio RawData. Quindi sono andato alla cartella di lavoro di origine in base al nome del file, ho selezionato il foglio corrispondente, ne ho copiato il contenuto, sono tornato al file ws_rawdata foglio e incollato il contenuto come valori. Infine, ho aggiornato la cartella di lavoro utilizzando il codice wb.RefreshAll che ha aggiornato l'intera cartella di lavoro inclusi rispettivamente i fogli con la tabella pivot e il grafico.

Sub UpdateRawData()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws_rawdata As Worksheet
Dim filepath As String
Set wb = ThisWorkbook
Set ws_admin = wb.Worksheets(“Admin”)
Set ws_rawdata = wb.Worksheets(“RawData”)
'Clear Rawdata sheet
ws_rawdata.Activate
Cells.Clear
'get file path
filepath = ws_admin.Range(“filepath”)
Application.DisplayAlerts = False
'Open source file and select all contents
Dim src_wb As Workbook
Dim src_ws As Worksheet
Set src_wb = Workbooks.Open(filepath)
Set src_ws = src_wb.Sheets(1)
src_ws.UsedRange.Select
'Copy all
Selection.Copy
'Paste all
ws_rawdata.Range(“A1”).PasteSpecial xlPasteValues
'Close source file
src_wb.Close SaveChanges:=False
wb.RefreshAll
Application.DisplayAlerts = True
End Sub

Ho collegato questa subroutine al file Aggiorna file e grafici pulsante. Dopo aver specificato il nome file nel segnaposto e aver fatto clic sul pulsante, il set di dati e le tabelle pivot sono stati aggiornati automaticamente.

2. Creazione di una tabella di colori utilizzando VBA

Ho creato una tabella nel file Admin foglio contenente l'elenco dei prodotti ortofrutticoli specifici disponibili nel dataset. Nella colonna F ho specificato i codici colore esadecimali provvisori per il colore di ciascun frutto o verdura. Volevo utilizzare questi colori per aggiornare i colori nei grafici pivot. Per prima cosa volevo dipingere la colonna F con il colore che ho specificato in ogni cella.

Creazione di un elenco di frutta e verdura disponibili nel set di dati insieme ai corrispondenti codici colore esadecimali. Illustrazione dell'autore.

Codice colore esadecimale

Il codice esadecimale per ciascun colore è un numero esadecimale di 6 cifre (da 0 a 9 o da A a F) in base 16. In un codice colore esadecimale RRGGBBogni coppia di due lettere rappresenta le varie sfumature di colore rosso, verde e blu. In un sistema Rosso Verde Blu (RGB), il valore di ciascuna tonalità varia da 0 a 255.

Ad esempio, per un codice colore esadecimale ffab23Ho calcolato il codice RGB corrispondente con il seguente calcolo. ffab23 nel sistema esadecimale si traduce in (255, 171, 35) nel sistema RGB riferendosi rispettivamente alle componenti di colore Rosso, Verde e Blu.

Calcolo manuale del codice RGB per un codice colore esadecimale ffab23. Illustrazione dell'autore.

Questo può anche essere visualizzato andando all'opzione Colori personalizzati in Excel come mostrato di seguito:

Dimostrazione dei componenti RGB e del colore effettivo per il codice colore esadecimale ffab23. Illustrazione dell'autore.

In Excel VBA, utilizzando &H in combinazione con un valore implica che si tratti di un numero esadecimale e il Val() la funzione restituisce il numero decimale corrispondente. Nella finestra immediata sottostante, r, gE b rappresentano rispettivamente i valori decimali corrispondenti per ciascuna tonalità di rosso, verde e blu.

Finestra immediata che mostra come i valori decimali corrispondenti vengono derivati in VBA dal codice esadecimale. Illustrazione dell'autore.

Nel codice seguente, ho creato un intervallo denominato color_code_range per la tabella contenente il codice colore esadecimale di ogni frutto o verdura. Ho eseguito il looping di ciascuna cella della selezione, derivato i componenti rosso, verde e blu in numeri decimali e dipinto l'interno della cella con lo stesso codice colore RGB.

Sub refresh_color_table()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
ws_admin.Range("color_code_range").Select
Dim c As Range
Dim r, g, b As Long
Dim Hex As String
For Each c In Selection
Hex = c.Value
r = Val("&H" & Mid(Hex, 1, 2))
g = Val("&H" & Mid(Hex, 3, 2))
b = Val("&H" & Mid(Hex, 5, 2))
c.Interior.Color = RGB(r, g, b)
Next c
End Sub

Quando ho eseguito la subroutine precedente, la colonna F viene dipinta con lo stesso colore del codice colore come mostrato:

Dipingere le celle con il codice colore menzionato in esso. Illustrazione dell'autore.

Se il codice colore viene modificato e il codice viene eseguito nuovamente, genererà nuovi colori nella tabella. Non è bello?

3. Lavorare con un dizionario in VBA

Nel passaggio successivo, volevo assegnare i colori nei grafici pivot in base ai colori personalizzati che ho scelto sopra. A questo scopo ho creato un dizionario contenente il nome del prodotto come chiavi e i corrispondenti codici colore esadecimali come valori.

Il prerequisito per la creazione di un oggetto dizionario in VBA è l'attivazione anticipata di Microsoft Scripting Runtime. Per farlo, puoi andare su Strumenti -> Riferimenti -> Seleziona la casella accanto a Microsoft Scripting Runtime e fai clic su OK.

Prerequisito per lavorare con un dizionario in Excel VBA. Illustrazione dell'autore.

Nel codice seguente, ho creato un oggetto dizionario chiamato colorMap. Ho eseguito il looping dell'intervallo E2: F10 nel file Admin foglio. Ho aggiunto il contenuto nella colonna E come chiavi e il contenuto nella colonna F come valori corrispondenti.

Sub create_dict()Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
Dim colorMap As Dictionary
Set colorMap = New Dictionary
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
For Each Key In colorMap.Keys()
Debug.Print Key & ": " & colorMap(Key)
Next Key
End Sub

Nel secondo ciclo for sopra, ho eseguito il loop di ciascuna chiave del dizionario colorMap e ho stampato le chiavi e i valori nella finestra immediata come mostrato di seguito:

Stampa del contenuto dell'oggetto dizionario colorMap. Illustrazione dell'autore.

4. Gestione degli elementi del grafico pivot utilizzando VBA

Sulla base di quanto appreso dai passaggi precedenti, volevo fare un ulteriore passo avanti e aggiornare gli elementi del grafico pivot utilizzando VBA. In questo caso, volevo impostare automaticamente il titolo del grafico in base al valore di una cella e applicare i colori di frutta e verdura specifici definiti nel Admin foglio ai grafici pivot.

In questo passaggio ho assegnato Plot1 E Plot2 fogli come un array chiamato sheetNames. ho dichiarato chartObj come oggetto grafico. All'interno di ciascun foglio, ho eseguito il looping di ciascun ChartObject tra tutti i ChartObject.

Nota: Oggetto grafico funge da contenitore per a Grafico oggetto in VBA che controlla la dimensione e l'aspetto del grafico incorporato in un foglio di lavoro. È un membro del Oggetti grafici collezione. È importante comprendere le differenze nei metodi e nelle proprietà di ciascuno di questi oggetti in VBA.

Dopo aver esaminato ciascun chartObj, imposto il titolo per ciascun grafico in base al valore nella cella E1. Successivamente, ho eseguito il ciclo di ciascuna serie nella raccolta di serie complessiva dell'oggetto Grafico. Ho assegnato il nome della serie (cioè il nome della frutta o della verdura) a una variabile chiamata itemNamee ho ottenuto il codice colore corrispondente da colorMap dizionario. Analogamente al passaggio 2, ho ottenuto i componenti rosso, verde e blu del codice colore in numeri decimali e ho riempito la barra della serie con i colori RGB.

Sub refresh_plots()Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets("Admin")
Dim colorMap
Set colorMap = CreateObject("Scripting.Dictionary")
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
Dim sheetNames As Variant
Dim sheetName As Variant
sheetNames = Array("Plot1", "Plot2")
Dim hex_color_code As String
Dim r, g, b As Integer
Dim chartObj As ChartObject
For Each sheetName In sheetNames
Set ws = wb.Sheets(sheetName)
For Each chartObj In ws.ChartObjects
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = ws.Range("E1").Value
For Each Series In chartObj.Chart.SeriesCollection
itemName = Series.Name
hex_color_code = colorMap(itemName)
r = Val("&H" & Mid(hex_color_code, 1, 2))
g = Val("&H" & Mid(hex_color_code, 3, 2))
b = Val("&H" & Mid(hex_color_code, 5, 2))
Series.Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next Series
Next chartObj
Next sheetName
End Sub

Di seguito è riportata un'illustrazione dell'uso di questo codice.

Trasformazione degli elementi del grafico pivot utilizzando il codice VBA. Illustrazione dell'autore.

Conclusione

In questo post ho illustrato come personalizzare e automatizzare il lavoro con tabelle pivot e grafici utilizzando VBA. Ho dimostrato l'automazione di quattro attività chiave: aggiornamento di tabelle pivot e grafici con nuovi set di dati; creazione di tabelle colore basate su codice colore esadecimale; come lavorare con i dizionari in VBA; e gestione e aggiornamento degli elementi del grafico pivot utilizzando VBA. Nella seconda fase ho elaborato la conversione dei codici colore esadecimali nei corrispondenti codici colore RGB utilizzando sia Excel che VBA, e ho utilizzato questa tecnica nei passaggi successivi.

Il codice e il file Excel per questo post sono presenti in questo GitHub deposito. Grazie per aver letto!

Fonte: towardsdatascience.com