Si può usare il registratore per automatizzare Excel? La risposta è sì, ma per farlo in modo soddisfacente è necessario ottenere il meglio da questo strumento. Questo significa usarlo nel modo migliore e diventarne esperti. Vediamo esempi pratici di utilizzo del registratore per automatizzare fogli Excel.
Il registratore di macro
Nella guida dedicata al registratore di macro e nel tutorial relativo abbiamo spiegato cos’è il registratore, come funziona e come è possibile usarlo per ottenere macro limitate ma funzionanti con cui è possibile automatizzare i fogli Excel.
Il registratore di macro di Excel rileva e traduce le attività che svolgiamo su Excel e le registra come codice vba, il codice che esegue le stesse modalità in automatica. Quindi è uno strumento che permette di produrre macro in modo intuitivo, secondo in principio “ripeti quello che faccio”.
Naturalmente il registratore produce macro elementari composte solo da sequenze di comandi operativi, senza nessuna intelligenza, solo comandi diretti non condizionati. D’altra parte le macro registrate sono funzionanti e possono essere usate per automatizzare fogli Excel.
Ma da dove cominciamo a farlo?
Dalla definizione dell’obiettivo.
Come si dovrebbe usare il registratore di macro?
La registrazione di una macro non comincia dal pulsante “Registra macro”, ma dalla definizione dell’obiettivo, cioè in parole semplici dalla definizione delle attività che vuoi automatizzare e quindi devi registrare.
Nella guida propongo un metodo che parte dall’obiettivo per arrivare fino alla verifica della macro registrata e se necessario alla revisione, seguendo questo iter:
- Definizione dell’obiettivo
- Valutazione dell’obiettivo,
- Progettazione della macro,
- Registrazione della macro,
- Test della macro,
- (eventuale) Revisione della macro.
Come strumento di supporto all’applicazione pratica del metodo ho realizzato una checklist, scaricabile da chi si è registrato a ExcelProfessionale, che contiene diversi esempi di applicazione del metodo e di realizzazione di macro registrate “flessibili”. Se non sei ancora registrato, puoi farlo attraverso il form nella barra laterale in alto a destra.
La definizione dell’obiettivo
Per una macro registrata scegliere un obiettivo può apparire elementare, dopotutto il registratore rileva le nostre attività su Excel, quindi non dobbiamo fare altro che registrare le attività ripetitive che svolgiamo sul foglio che vogliamo automatizzare.
In sintesi è proprio così, ma per produrre una macro utile e funzionante dobbiamo saper scegliere le attività da registrare e in certi casi dobbiamo modificare le attività per realizzare una macro efficace, cioè in grado di realizzare l’attività specifica e ottenere quel risultato nel tempo.
Questo richiede una buona conoscenza del registratore come delle limitazioni delle macro registrate, ma anche una buona conoscenza di excel e degli strumenti che lo compongono. Con l’esperienza, dopo aver imparato i limiti del registratore e delle macro che produce, grazie alla tua conoscenza di Excel e degli strumenti usati nei tuoi fogli, saprai valutare in anticipo fin dove le macro possono arrivare e quando invece non potranno aiutarti nell’automazione dei tuoi fogli.
Valutazione del tuo obiettivo
Valutare l’obiettivo significa metterlo in discussione, esaminarlo con attenzione anche da punti di vista diversi, misurarne l’utilità e il costo, confrontarlo con soluzioni alternative, prevederne il risultato e quindi la fattibilità.
Per fare questo si deve semplicemente rispondere ad alcune domande: qual è l’obiettivo della macro? Dove la userai? Quale uso ne farai? Quanto tempo ti farà risparmiare e quanto tempo spenderai per realizzarla? Raggiungerà il risultato atteso? Sempre? Ci sono altri modi o strumenti con cui puoi ottenere lo stesso risultato? Quanto costano?
Rispondere a queste domande ti spinge a ragionare sull’obiettivo per capire se merita di spenderci tempo e risorse.
Progettazione della macro
Progettare una macro registrata è semplice:
- dedica due minuti per scrivere in modo particolareggiato l’obiettivo che vuoi raggiungere con la macro che vuoi realizzare,
- traducilo in una lista di attività,
- se necessario riduci le attività in azioni elementari da registrare,
- descrivi ogni azione elementare con brevi frasi del tipo: “azione + oggetto + eventuale valore da inserire o impostare + eventuale effetto o conseguenza”.
Perché farlo? Per comprendere caratteristiche e risultati dell’azione, prima di vederli in pratica. Ricordati
Come sai quali sono le attività e le azioni da svolgere?
Sono quelle che esegui quando usi il tuo foglio Excel, ma solo una parte di esse potranno essere registrate correttamente.
Quali?
Quelle che non prevedono decisioni o che non hanno alternative, quelle che non richiedono la verifica delle condizioni, quelle che vengono sempre eseguite. Questo perché il registratore registra azioni, solo azioni e null’altro, e di conseguenza le macro registrate eseguono azioni e null’altro.
Parlo della modifica di oggetti, valori o caratteristiche, della creazione, eliminazione o qualunque interazione con gli oggetti di Excel, che siano celle, grafici, tabelle, pagine o altro.
Inoltre tutte queste azioni saranno registrate indicando specificatamente gli oggetti su cui le eseguiamo e quindi le macro le ripeteranno solo su quegli oggetti, che dovranno essere presenti all’inizio dell’esecuzione.
Quindi le macro funzioneranno correttamente eseguendo le attività se saranno presenti gli oggetti specifici, nella posizione specifica, o saranno impostate proprietà specifiche o diversamente specifiche, o saranno presenti determinati valori nelle posizioni specifiche, e così via.
Queste sono le condizioni preliminari definite nel progetto, registrate dal registratore e necessarie alle macro. Il corretto funzionamento delle macro registrate dipende dalle condizioni preliminari definite a livello di progetto e registrazione.
Quali sono le condizioni preliminari?
Le condizioni preliminari per esempio sono:
- la presenza degli oggetti specifici con cui la macro interagisce,
- la posizione degli oggetti con cui la macro interagisce e la loro presenza nell’ambito in cui lanciamo la macro,
- la presenza o assenza di impostazioni o proprietà che escludono una o più azioni della macro con o senza errori, (per esempio non si può proteggere una pagina che è già protetta),
- la selezione o attivazione di oggetti,
- l’assenza di errori o di valori
E altro ancora. Se le condizioni iniziali non corrispondono a quelle registrate, allora i risultati saranno diversi da quelli attesi.
Per questo definire a livello di progetto della macro le condizioni preliminari è utile per poterle ripristinare e garantire, per esempio tramite istruzioni precise sulla pagina, ma non solo. Scrivere ed esaminare le condizioni iniziali permette di comprendere se la macro registrata funzionerà come previsto, come e quando funzionerà, e se possiamo migliorare il progetto.
Come migliorare le macro registrate
Come possiamo intervenire sul progetto per migliorarlo?
Modificando la lista delle attività da registrare modifichiamo le condizioni. Se eliminiamo e riduciamo la criticità delle condizioni, riduciamo i limiti delle macro e le rendiamo più efficaci, efficienti e flessibili, arrivando in certi casi a renderle utilizzabili anche in ambiti diversi.
Alcuni modi efficaci sono i seguenti:
- far creare alla macro gli oggetti con cui o su cui lavorare e non interagire con gli oggetti esistenti, questo riduce le condizioni iniziali, o le elimina proprio, per esempio aggiungere una nuova pagina e creare lì tabelle e grafici, oppure aggiungere nuove righe e colonne per aggiungere testi, note, o altro, eccetera;
- usare oggetti con nomi generici e predefiniti, per esempio chiamare “Dati” la pagina dei dati, “TabellaRisultati” la tabella pivot con i risultati dell’elaborazione, o identificare intervalli con nomi standard come “fatture”, “vendite”, eccetera;
- usare gli strumenti dinamici e avanzati del foglio di calcolo, che si adattano da soli alle variazioni, che possono gestire le modifiche o eseguire autonomamente le attività necessarie; per esempio possiamo appoggiare la macro alle tabelle excel in grado di adattarsi a nuovi record e campi e di propagare in automatico le formule in esse inserite;
- muoversi nella pagina e di conseguenza spostare il focus il meno possibile e dove necessario usare i comandi veloci (es Ctrl+Fine per saltare all’ultima cella in basso a destra dell’area di lavoro, o Ctrl+Destra per saltare alla prima o ultima cella vuota o piena); se non conosci i comandi veloci per muoversi in Excel ti invito a scaricare il corso Usa Excel come un professionista e a guardare il tutorial correlato;
- usare il comando “riferimenti relativi”, selezionabile (lo attivi con un clic, lo disattivi con un clic) sulla scheda “Sviluppo” della barra multifunzione; ti permette di registrare i movimenti relativi tra le celle delle pagine invece che registrare il nome esatto delle celle: normalmente il registratore indica nel codice i riferimenti diretti delle celle (es: “K1”), con “usare riferimenti relativi” attivo riporterà il movimento rispetto alla selezione (es: 2 celle a destra, una in basso).
Oltre a lavorare sul progetto della macro, possiamo migliorare le macro lavorando sul foglio e i suoi strumenti, per esempio possiamo “fissare” le situazioni incerte che richiedono decisioni da parte dell’operatore, intervenendo a “monte” (su foglio, dati, strumenti) per eliminarne le cause. In alternativa possiamo registrare macro diverse per ogni alternativa, che però dovranno essere gestite e utilizzate dall’operatore in modo corretto.
Ottimizza la lista delle attività da registrare
Quindi dopo aver fatto una prima lista delle azioni elementari, prima di passare alla registrazione è opportuno rivederla per cercare di ottimizzarla applicando quanto abbiamo detto fino ad ora. L’obiettivo è ottenere il risultato previsto eliminando o riducendo le condizioni iniziali, o perfino dove possibile ampliare la macro rendendola in grado di eseguire più attività.
In questa fase è determinante la nostra conoscenza di Excel, del suo funzionamento e dei suoi strumenti, e l’esperienza che ci siamo costruiti nel tempo. All’inizio il registratore ci presenterà situazioni complesse che ci sembreranno senza soluzione, cioè lo strumento non sarà in grado di fornirci automatismi che eseguano le attività che vogliamo, ma in molti casi questo dipenderà anche dai nostri limiti.
Vediamo un esempio concreto di automazione di un foglio Excel con il registratore.
Usa il registratore per automatizzare fogli Excel
Vogliamo automatizzare un foglio Excel concepito per seguire prove industriali. Se e quando il Mes e il gestionale aziendale non sono in grado di offrire gli strumenti per la raccolta dei dati o il controllo degli effetti delle prove, o quando non è opportuno modificarli per farlo (es: troppo costoso), excel può essere usato come strumento temporaneo di supporto con cui seguire e valutare i risultati dell’introduzione di modifiche o innovazioni.
Il foglio in questione permette e richiede le seguenti attività:
- inserire dati raccolti dagli operatori
- importare dati dal gestionale o database esterni
- verificare la qualità dei dati raccolti
- elaborare i dati per calcolare indicatori specifici
- rilevare valori anomali tramite strumenti di analisi
- controllare andamenti e distribuzioni attraverso una dashboard
- sintesi e presentazione dei risultati tramite report
Il foglio è complesso, composto da una ventina di pagine, con decine di strumenti di vario tipo (tabelle excel, query, pivot, tabelle di formule, grafici, eccetera). La maggior parte dell’automazione è determinata dall’uso di strumenti dinamici ed efficienti che semplificano la gestione.
Per esempio la realizzazione di una query con Recupera e trasforma per importare i dati dal gestionale, permette con un clic di importare e aggiornare i dati in una tabella Excel. Oppure l’uso di tabelle Excel come contenitori dei dati a cui abbiamo collegato le tabelle pivot usate per l’elaborazione permette di aggiornare gli strumenti con un clic o un tasto e di modificare questi strumenti con pochi gesti.
Esempio di automazione
L’uso di macro per automatizzare il foglio potrebbe ridurre il tempo speso sullo strumento di almeno un 20–30%, delegando alle routine l’esecuzione delle attività ripetitive. Purtroppo non conoscendo il vba dobbiamo limitarci all’uso del registratore e l’analisi delle attività ripetitive che svolgiamo sul foglio suggerisce di poter automatizzare:
- l’inserimento e controllo dei dati nel foglio,
- l’importazione di tabelle con i dati inseriti da colleghi,
- l’installazione di strumenti per verificare i dati (formule di controllo, formattazioni condizionali, convalide, ecc),
- l’esportazione di copie delle pagine con dashboard e report e la creazione del pdf
Automazione di una maschera di inserimento
Per quanto l’inserimento di dati raccolti manualmente dagli operatori, oggi sia un’attività in via di estinzione considerata l’evoluzione tecnologica, è ancora possibile in situazioni limitate e specifiche come prove industriali. Un aspetto chiave dell’inserimento manuale è il controllo dei dati inseriti allo scopo di prevenire errori e questo può essere realizzato attraverso strumenti come maschere di inserimento più o meno evolute.
Farlo è piuttosto elementare e sulla creazione di una maschera di inserimento su pagina ti rimando a questo video tutorial, mentre per l’automazione della maschera di inserimento ti rimando a questo tutorial.
Come puoi vedere usare il registratore per creare le funzionalità minime per movimentare i dati è facile, mentre la convalida dei dati fornisce i controlli automatici sui dati inseriti. L’unica complicazione della progettazione del trasferimento del record alla tabella dati è inserire i dati in una riga predefinita, sempre la stessa, usando gli automatismi delle tabelle Excel e la loro dinamicità.
Importare le tabelle dei dati
Se l’inserimento viene eseguito da altri, per esempio attraverso un file Excel con la maschera automatizzata, i dati dovranno essere importati nel foglio. Questo può essere fatto copia–incollando i dati, oppure usando strumenti avanzati, oppure con l’ausilio di una macro anche registrata.
La macro registrata può importare la pagina con i dati o copiare in automatico i dati dal foglio esterno per incollarli nella tabella dei dati, ma per farlo è necessario applicare più metodi tra quelli suggeriti più sopra. È necessario chiamare il foglio e le pagine con nomi generi e predefiniti ed è necessario approfittare di automatismi di Excel e delle tabelle Excel. Questo per poter lavorare su una tabella di origine che può contenere un numero variabile di record e su una tabella di destinazione che cambia nel tempo e la tabella.
Quindi registrare una serie di attività come questa:
- aggiungere una riga vuota nella seconda riga della tabella di destinazione, una tabella Excel,
- aggiungere una stringa “zzzzz” nella prima cella della riga vuota,
- ordinare la tabella dall’A alla Z,
- passare al foglio e alla pagina con i dati da importare, nome file e nome pagina devono essere predefiniti, per muovere la pagina al foglio di destinazione,
- selezionare il corpo dati della tabella di origine con i tasti veloci (Ctrl+Maius+Spazio) e copiare i dati,
- tornare al foglio e alla pagina con la tabella di destinazione, selezionare la cella A1 e poi usare i tasti veloci (Ctrl+Giù o Su) per muoversi prima a fondo pagina per poi tornare all’ultima riga della tabella e incollare i dati.
Nel caso i dati venissero raccolti da plc, palmari o altro e, invece di essere caricati sul server centrale, venissero forniti sotto forma di file di formati diversi, per esempio Csv, una macro registrata potrebbe importare i dati in modo anche più semplice.
Installazione di strumenti per verificare i dati
Le macro registrate di possono essere d’aiuto nell’installazione di strumenti di Excel che possono aiutarci nella verifica della qualità dei dati.
Come?
Per esempio installando campi calcolati nelle tabelle dei dati con formule e funzioni che per esempio verificano la presenza di dati, oppure nell’installazione di controllo realizzati con la convalida dei dati in uno o più campi della tabella dei dati che evidenzino dati invalidi. O nell’installazione di una formattazione condizionale che in modo simile metta in evidenzia per esempio valori non univoci di uno o più campi, o celle che contengono valori precisi o maggiori, minori, diversa, eccetera.
Questo richiede la buona conoscenza di questi strumenti e altri ancora e l’utilizzo di strumenti dinamici e tasti veloci che permettano di gestire “incertezze” e “dinamicità.
Per esempio se vogliamo inserire un campo calcolato, dobbiamo usare una tabella Excel, usare riferimenti relativi e registrare queste attività:
- selezionare la riga dell’intestazione, premere Ctrl+Destra, poi destra,
- scrivere il titolo del campo di controllo,
- inserire nella riga 2 una formula come questa: “=SE( O( VAL.ERRORE(A2);VAL.ERRORE(B2);VAL.ERRORE(C2);VAL.ERRORE(D2);VAL.ERRORE(E2);VAL.ERRORE(F2);VAL.ERRORE(H2);VAL.ERRORE(I2);VAL.ERRORE(J2);VAL.ERRORE(K2)); “Attenzione è presente un errore”; “”), la tabella la propagherà in automatico per tutto il campo.
Installare controlli della convalida o formattazioni condizionali è analogamente semplice, mentre usare strumenti più evoluti come pivot, filtri, query è decisamente più complicato.
Ti invito naturalmente a provare ogni possibilità, ma voglio sottolineare che questa attività è di solito poco frequente, di solito questi strumenti vengono posizionati una volta su una specifica tabella di dati e continueranno a fare il loro lavoro. Quindi una macro registrata che installa controlli è utile quando esegue un’azione eseguita frequentemente, per esempio quando questi devono essere eseguiti sui dati sempre nuovi prima dell’inserimento nella tabella del foglio.
Questa macro, registrata in modo diverso, può essere usata per la caratterizzazione ed elaborazione dei dati e per l’analisi dei risultati, per esempio per installare formattazioni e controlli su pivot o tabelle di formule.
Creare Pdf di dashboard e report o esportarne copia
Esportare copia di dashboard e report può essere utile, ma di solito è più veloce condividere il file. Spesso invece è utile salvare il pdf del report e della dashboard per condividere in modo semplice i risultati del foglio.
Nonostante in fondo sia piuttosto veloce salvare un pdf di una o più pagine, una macro può ridurre tutto a un clic, o al massimo all’inserimento del nome del documento da salvare nella finestra di salvataggio.
Non dovremo fare altro che registrare per esempio:
- selezioni le pagine da stampare se sono più di una,
- passi all’anteprima di stampa con Ctrl+F2,
- imposti come stampante la copia in Pdf (o Pdf creator) e la scala come tutto in una pagina,
- modifichi eventuali altre impostazioni,
- stampi e inserisci il nome del file.
In realtà ci sono diversi modi, più o meno efficienti, per realizzare la stessa macro registrata.
Per il salvataggio di una copia di una o più pagine in un file Excel nuovo, le macro registrate ci aiutano in modo egregio, ma è opportuno usare il comando Copia delle pagine, trasformare tutto in valori con Incolla speciali per rimuovere formule e riferimenti, inserire nuove righe nella pagina copiata per aggiungere eventuali note per poi salvare il foglio creato.
A seconda degli strumenti presenti nella pagina questo comporta complicazioni a volta difficilmente risolvibili. Spesso è molto più semplice il pdf o inviare una copia del foglio.
Conclusioni
Abbiamo visto alcuni esempi di automazione con macro realizzate con il registratore di macro, su un foglio reale ed esistente. Nella realtà il foglio è stato automatizzato in modo più efficace con macro sviluppate dal sottoscritto, ma in precedenza era stato automatizzato con successo dal proprietario usando il registratore. E in più modi di quei pochi descritti finora.
Questo post vuole dimostrare che il registratore per quanto limitato, quando viene utilizzato con gli altri strumenti di Excel e gli automatismi del foglio di calcolo, è in grado di realizzare automatismi efficaci.
Naturalmente una macro sviluppata a confronto di una registrata è come mettere a confronto un elicottero con una bicicletta, ma imparare il vba richiede decine se non centinaia di ore di studio e lavoro, mentre il registratore ne richiede una decina e se conosci bene Excel può darti soddisfazioni.
Quindi se conosci Excel e vuoi automatizzare fogli Excel senza conoscere il vba, ti invito a dedicare un poco di tempo allo studio del registratore. Qui trovi la guida e qui il tutorial.
Ti piacerebbe realizzare automatismi in grado di fare molto di più?
Ti piacerebbe realizzare strumenti su misura dei tuoi bisogni? Rendere i tuoi fogli efficienti e risparmiare tempo?
Per farlo devi imparare l’automazione di Excel.
Questo corso ti guida da zero all’automazione dei tuoi fogli:
Grazie dell’attenzione e a presto ;D
PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie
