Scopri come utilizzare le macro di Excel per automatizzare le noiose attività

Una delle funzioni più potenti, ma raramente utilizzate, di Excel è la possibilità di creare facilmente attività automatizzate e logica personalizzata all'interno delle macro. Le macro offrono un modo ideale per risparmiare tempo su attività prevedibili e ripetitive e standardizzare i formati di documenti, molte volte senza dover scrivere una singola riga di codice.

Se sei curioso di sapere quali sono i macro o come effettivamente crearli, nessun problema: ti guideremo attraverso l'intero processo.

Nota: lo stesso processo dovrebbe funzionare nella maggior parte delle versioni di Microsoft Office. Gli screenshot potrebbero apparire leggermente diversi.

Cos'è una macro?

Una macro di Microsoft Office (come questa funzionalità si applica a molte delle applicazioni di MS Office) è semplicemente codice Visual Basic for Applications (VBA) salvato all'interno di un documento. Per un'analogia comparabile, pensa a un documento come HTML e una macro come Javascript. Più o meno allo stesso modo in cui Javascript può manipolare l'HTML su una pagina Web, una macro può manipolare un documento.

I macro sono incredibilmente potenti e possono fare praticamente tutto ciò che la tua immaginazione può evocare. Come (molto) breve elenco di funzioni che puoi fare con una macro:

• Applica stile e formattazione.
• Manipola dati e testo.
• Comunica con le origini dati (database, file di testo, ecc.).
• Crea documenti completamente nuovi.
• Qualsiasi combinazione, in qualsiasi ordine, di uno qualsiasi dei precedenti.

Creazione di una macro: una spiegazione per esempio

Iniziamo con il file CSV della varietà Garden. Niente di speciale qui, solo un insieme di numeri 10 × 20 tra 0 e 100 con intestazione sia di riga che di colonna. Il nostro obiettivo è produrre un foglio dati ben formattato e presentabile che includa i totali di riepilogo per ogni riga.

Come abbiamo detto sopra, una macro è il codice VBA, ma una delle cose belle su Excel è che puoi crearle / registrarle con zero codifiche richieste - come faremo qui.

Per creare una macro, vai su Visualizza> Macro> Registra macro.

Assegna un nome alla macro (senza spazi) e fai clic su OK.

Una volta fatto, tutti delle tue azioni sono registrate - ogni cambiamento di cella, azione di scorrimento, ridimensionamento della finestra, tu lo dai il nome.

Ci sono un paio di posti che indicano che Excel è la modalità di registrazione. Uno è visualizzando il menu Macro e notando che Stop Recording ha sostituito l'opzione per Registra macro.

L'altro è nell'angolo in basso a destra. L'icona 'stop' indica che è in modalità macro e premendo qui fermerà la registrazione (allo stesso modo, quando non è in modalità di registrazione, questa icona sarà il pulsante Registra macro, che puoi utilizzare invece di andare al menu Macro).

Ora che stiamo registrando la nostra macro, applichiamo i nostri calcoli di riepilogo. Per prima cosa aggiungi le intestazioni.

Quindi, applicare le formule appropriate (rispettivamente):

• = SUM (B2: K2)
• = MEDIA (B2: K2)
• = MIN (B2: K2)
• = MAX (B2: K2)
• = MEDIANA (B2: K2)

Ora, evidenzia tutte le celle di calcolo e trascina la lunghezza di tutte le nostre righe di dati per applicare i calcoli a ciascuna riga.

Una volta fatto, ogni riga dovrebbe mostrare i rispettivi riepiloghi.

Ora vogliamo ottenere i dati di riepilogo per l'intero foglio, quindi applichiamo alcuni altri calcoli:

Rispettivamente:

• = SUM (L2: L21)
• = MEDIA (B2: K21) *Questo deve essere calcolato su tutti i dati poiché la media delle medie di riga non necessariamente corrisponde alla media di tutti i valori.
• = MIN (N2: N21)
• = MAX (O2: O21)
• = MEDIANA (B2: K21) * Calcolato su tutti i dati per lo stesso motivo di cui sopra.

Ora che i calcoli sono fatti, applicheremo lo stile e la formattazione. Prima applica la formattazione numerica generale su tutte le celle facendo un Seleziona tutto (Ctrl + A o fai clic sulla cella tra le intestazioni di riga e colonna) e seleziona l'icona "Stile virgola" sotto il menu Home.

Successivamente, applica alcune formattazioni visive alle intestazioni di riga e colonna:

• Grassetto.
• centrato.
• Colore di riempimento dello sfondo.

E infine, applica uno stile ai totali.

Quando tutto è finito, ecco come appare la nostra scheda tecnica:

Dato che siamo soddisfatti dei risultati, interrompiamo la registrazione della macro.

Congratulazioni: hai appena creato una macro di Excel.

Per utilizzare la nostra macro appena registrata, dobbiamo salvare la nostra cartella di lavoro di Excel in un formato file abilitato per la macro. Tuttavia, prima di farlo, dobbiamo prima cancellare tutti i dati esistenti in modo che non siano incorporati nel nostro modello (l'idea è che ogni volta che usiamo questo modello, importeremo i dati più aggiornati).

Per fare ciò, seleziona tutte le celle ed eliminale.

Con i dati ora cancellati (ma le macro ancora incluse nel file Excel), vogliamo salvare il file come file modello abilitato alla macro (XLTM). È importante notare che se si salva questo file come modello standard (XLTX), le macro lo faranno non essere in grado di essere eseguito da esso. In alternativa, è possibile salvare il file come file modello legacy (XLT), che consentirà l'esecuzione di macro.

Una volta salvato il file come modello, vai avanti e chiudi Excel.

Utilizzando una macro di Excel

Prima di illustrare come possiamo applicare questa macro appena registrata, è importante coprire alcuni punti sulle macro in generale:

• Le macro possono essere dannose.
• Vedi il punto sopra.

Il codice VBA è in realtà abbastanza potente e può manipolare file al di fuori dell'ambito del documento corrente. Ad esempio, una macro potrebbe alterare o eliminare file casuali nella cartella Documenti. In quanto tale, è importante assicurarsi di te solo eseguire macro da fonti attendibili.

Per utilizzare la nostra macro in formato dati, apri il file modello Excel che è stato creato sopra. Quando si esegue questa operazione, supponendo che siano attivate le impostazioni di sicurezza standard, verrà visualizzato un avviso nella parte superiore della cartella di lavoro che indica che le macro sono disabilitate. Perché ci fidiamo di una macro creata da noi stessi, fare clic sul pulsante "Abilita contenuto".

In seguito, importeremo il set di dati più recente da un CSV (questa è la fonte del foglio di lavoro utilizzato per creare la nostra macro).

Per completare l'importazione del file CSV, potrebbe essere necessario impostare alcune opzioni affinché Excel possa interpretarlo correttamente (ad esempio delimitatore, intestazioni presenti, ecc.).

Una volta importati i nostri dati, vai al menu Macro (sotto la scheda Visualizza) e seleziona Visualizza macro.

Nella finestra di dialogo risultante, vediamo la macro "FormatData" che abbiamo registrato sopra. Selezionalo e fai clic su Esegui.

Una volta avviato, potresti vedere il cursore saltare per qualche istante, ma come vedrai i dati manipolati Esattamente come l'abbiamo registrato. Quando tutto è stato detto e fatto, dovrebbe apparire proprio come il nostro originale - tranne che con dati diversi.

Guardando sotto i baffi: cosa fa funzionare una macro

Come abbiamo menzionato un paio di volte, una macro è guidata dal codice Visual Basic for Applications (VBA). Quando "registri" una macro, Excel sta effettivamente traducendo tutto ciò che fai nelle rispettive istruzioni VBA. Per dirla semplicemente - non devi scrivere alcun codice perché Excel sta scrivendo il codice per te.

Per visualizzare il codice che esegue la nostra macro, dalla finestra di dialogo Macro fai clic sul pulsante Modifica.

La finestra che si apre visualizza il codice sorgente che è stato registrato dalle nostre azioni durante la creazione della macro. Ovviamente, è possibile modificare questo codice o persino creare nuovi macro interamente all'interno della finestra del codice. Sebbene l'azione di registrazione utilizzata in questo articolo soddisfi la maggior parte delle esigenze, azioni più personalizzate o azioni condizionali richiederebbero la modifica del codice sorgente.

Prendendo il nostro esempio Un passo più in là ...

Ipoteticamente, supponiamo che il nostro file di dati di origine, data.csv, sia prodotto da un processo automatizzato che salva sempre il file nello stesso percorso (ad es. C: \ Data \ data.csv è sempre il dato più recente). Il processo di apertura di questo file e di importazione può essere facilmente trasformato in una macro:

1. Apri il file modello Excel contenente la nostra macro "FormatData".
2. Registra una nuova macro chiamata "LoadData".
3. Con la registrazione macro, importa il file di dati come faresti normalmente.
4. Una volta importati i dati, interrompere la registrazione della macro.
5. Elimina tutti i dati della cella (seleziona tutto e poi elimina).
6. Salva il modello aggiornato (ricorda di utilizzare un formato di modello abilitato alla macro).

Una volta fatto questo, ogni volta che il modello viene aperto ci saranno due macro: una che carica i nostri dati e l'altra che la formatta.

Se davvero volevi sporcarti le mani con un po 'di editing del codice, potresti facilmente combinare queste azioni in una singola macro copiando il codice prodotto da "LoadData" e inserendolo all'inizio del codice da "FormatData".

Scarica questo modello

Per tua comodità, abbiamo incluso sia il modello Excel prodotto in questo articolo che un file di dati di esempio con cui puoi giocare.

Scarica Excel Macro Template da How-To Geek