Come realizzare maschere Excel automatizzate senza conoscere il vba? In molti mi chiedono questo, con quesiti più o meno specifici, e per questo ho realizzato la lunga serie di tutorial dedicati che trovi sul canale di ExcelProfessionale che mostrano come si possono realizzare maschere automatizzate con conoscenze minime dell’automazione.
Cosa sono le maschere Excel automatizzate?
Abbiamo già risposto a questo quesito nella guida relativa alle maschere.
Volendo riassumere i punti essenziali, le maschere sono “finestre” ricche di strumenti di controllo e di automatismi che svolgono le funzioni per cui sono progettate.
Maschere sono molti degli strumenti di Excel (es: finestra proprietà, trova, VaiA, ecc), ma sono anche strumenti personalizzati dall’utente: userform realizzati con l’editor vba e automatizzate con le macro, come pagine di Excel equipaggiate con controlli, strumenti e automazione (maschere su pagina).
Le maschere Excel realizzate dagli utenti di distinguono in:
- maschere su pagina, integrate nelle pagine di Excel,
- maschere utente, o userform, installate in finestre che possiamo realizzare con il visual basic editor e il linguaggio vba.
Le maschere sono composte da:
- una piattaforma, uno spazio dedicato su finestre o su pagina,
- controlli con cui svolgere le attività relative,
- automatismi che fanno funzionare le maschere o ne facilitano l’uso.
Maschere su pagina e maschere utente
In sintesi le maschere su pagina non sono altro che gruppi di controlli installati nella schermata di una pagina con cui possiamo eseguire le attività previste.
Una maschera utente è in sostanza una finestra di grandezza variabile su cui posizioniamo serie di controlli che l’operatore utilizzerà per controllare le attività automatizzate.
Le maschere utente richiedono la conoscenza del vba per impostare e gestire correttamente tutti i controlli.
Le maschere su pagina sono strumenti più semplici e spesso più efficienti, in quanto possono usare gli strumenti di Excel per svolgere parte delle attività e l’automazione necessaria può in molti casi essere realizzata con il registratore di macro.
Per esempio, nelle maschere su pagina si possono usare le celle della pagina o le tabelle Excel o pivot al posto di caselle di testo, listbox e altri controlli; come si possono usare elenchi e controlli realizzati con la convalida dei dati, le formule e i nomi.
I tipi di maschere Excel automatizzate
Le maschere possono essere classificate in diversi modi, ma di solito vengono distinte per le attività che svogliamo con esse. Possiamo per esempio distinguere maschere di:
- inserimento, con cui inseriamo dati nei nostri fogli e tabelle,
- ricerca, con cui cerchiamo dati o informazioni,
- revisione, con cui revisioniamo dati e record già presenti,
- elaborazione, con cui eseguiamo calcoli ed elaborazioni,
- visualizzazione e stampa, con cui richiamiamo dati e li mostriamo nel modo più efficace,
- controllo, con cui controlliamo strumenti diversi,
- gestione, con cui controlliamo l’automazione del foglio o di strumenti specifici.
Come si costruiscono le maschere
Le maschere Excel automatizzate sono strumenti complessi, strutture di controlli con cui impostiamo, gestiamo ed eseguiamo le attività relative.
Sono di solito composte da:
- una piattaforma, nel caso delle maschere su pagina una porzione di una pagina di Excel impostata in modo da essere stabile e comoda da usare,
- diversi controlli, specifici per l’uso a cui sono destinati, da elenchi a discesa a caselle combinate, da pulsanti a caselle di testo o celle impostate per l’inserimento di dati, meglio se assistito, eccetera,
- automatismi che gestiscono il funzionamento della maschera, in parte o in toto,
- eventuali altri strumenti che sostituiscono gli automatismi o una parte della maschera,
- istruzioni che spiegano l’uso dello strumento.
Gli strumenti per creare maschere su pagina
Gli strumenti che usiamo per creare maschere su pagina possono essere davvero molti e di solito dipendono dalle nostre competenze e dalle funzionalità che vogliamo installare nelle maschere Excel automatizzate.
Gli strumenti più usati per realizzare i controlli sono la convalida dei dati e la conoscenza dei controlli, formule e funzioni, link, forme, la gestione dei nomi.
Per l’automazione sarebbe necessaria almeno una conoscenza di base del vba, oltre che una conoscenza consolidata degli strumenti correlati (registratore di macro, vbe). Ma come vedrai qui di seguito è consigliato, ma spesso non è necessario.
Altri strumenti, complementari o sostitutivi, che possiamo usare nelle maschere su pagina dipendono dal tipo di maschera e dalle nostre competenze. I principali sono: tabelle excel, tabelle di formule, query, filtri avanzati, pivot, grafici, strumenti dei dati, slicers e altri ancora.
Per esempio possiamo usare il filtro avanzato o “recupera e trasforma” per una maschera di ricerca, oppure possiamo usare convalida e macro per una maschera di inserimento, o formule, funzioni, nomi e macro per una maschera di elaborazione. E così via.
Come si realizza una maschera su pagina
Il primo passo è progettare la maschera:
- definire lo scopo,
- le attività che devono essere svolte,
- le funzionalità necessarie,
- il flusso dei dati,
- gli strumenti che vogliamo usare per realizzarle,
- gli automatismi necessari,
- fino a definire un progetto particolareggiato.
Il secondo passo è la revisione o realizzazione degli strumenti a cui deve essere collegata la maschera (basedati, tabella Excel, query, ecc). Dipende dal tipo di maschera e dalle funzionalità integrate.
Una maschera di inserimento trasferirà i dati inseriti in una tabella Excel, una maschera di controllo sarà associata agli strumenti che deve controllare, una maschera di ricerca lavorerà su una o più base dati interne o esterne al foglio.
Gli strumenti correlati alle maschere Excel automatizzate devono essere preparati e verificati prima della realizzazione della maschera, secondo il progetto che abbiamo definito.
Il terzo passo è la creazione della maschera:
- preparazione della pagina
- installazione dei controlli
- preparazione degli eventuali strumenti complementari o sostitutivi,
- sviluppo e verifica degli automatismi
- completamento della maschera
- revisione del layout
- verifica del funzionamento
- verifica dell’esperienza dell’utente
- eventuale revisione e ottimizzazione
- preparazione delle istruzioni
Le fasi della realizzazione della maschera Excel
Abbiamo già parlato alle singole fasi della realizzazione, riassumendo quelle principali sono:
- Impostare la pagina con un layout professionale (senza griglie, impostando tema, font e colori specifici, aggiungendo un’immagine di fondo, inserendo un’intestazione e spazi per i pannelli di controllo, eccetera),
- Impostare i controlli necessari al funzionamento della maschera (elenchi, pulsanti, caselle di testo, pulsanti di opzioni, caselle di controllo e altro) e gli strumenti di supporto se necessari (formule, elenchi convalida, liste, eccetera),
- Preparare l’automazione necessaria, usando il registratore di macro, o scrivendo il codice direttamente nell’editor o in altri strumenti. Complessità e numero di routine necessarie alla gestione delle maschere dipendono dal tipo di strumento e da come lo realizziamo.
Su quest’ultimo punto è importante sottolineare che come vedremo più sotto è possibile realizzare maschere senza automazione, in quanto si basano su strumenti evoluti, così come maschere con poche macro di bassa complessità realizzabili con il registratore.
Ma una maschera professionale può arrivare a richiedere anche decine di routine con cui gestire le attività della maschera. Una solida conoscenza del vba oltre che darti la possibilità di realizzare maschere Excel più efficienti ed efficaci, permette di realizzare maschere multifunzione e maschere complesse che richiedono automazione evoluta.
Ora vediamo una decina di esempi di maschere Excel semplice da realizzare che richiedono la conoscenza del registratore di macro o conoscenze minime del vba.
Crea maschere Excel di inserimento
Una maschera di inserimento è uno strumento che possiamo associare a una tabella excel o a un intervallo di dati e che possiamo installare nella stessa pagina o preferibilmente in una pagina diversa dedicata.
Sono strumenti con cui possiamo inserire e controllare i dati che dobbiamo aggiungere alla basedati, senza dover applicare controlli sui dati inseriti a tutti i record. Inoltre la maschera evita di lavorare direttamente sulla basedati, riducendo al minimo modifiche non volute, inoltre la maschera può essere equipaggiata con automazione che assiste nell’inserimento dei dati e nei controlli.
In sintesi la maschera di inserimento ci permette di inserire i record dei dati in modo assistito digitandoli nella maschera, invece che in fondo a una tabella di migliaia di record.
Come si crea la maschera di inserimento?
In pochi semplici passaggi:
- creando la pagina in cui installare la maschera,
- impostando maschera e layout,
- installando i controlli nella maschera,
- aggiungendo titoli, note ed istruzioni necessarie all’utente per sapere come usarla.
Come si installano i controlli?
Si utilizza la convalida dei dati per generare controlli automatici all’inserimento dei dati nelle celle.
Questo strumento ormai classico permette a chi conosce formule e funzioni di poter impostare controlli evoluti, anche multipli e dinamici. Puoi trovare una guida alla convalida dei dati su Excel Professionale.
L’automazione di una maschera di inserimento
Come si automatizza la maschera di inserimento?
Come puoi vedere nei tutorial qui di seguito, è possibile automatizzare la maschera in modo semplice utilizzando il registratore di macro. Per imparare a usare il registratore di macro puoi guardare i tutorial dedicati su questo canale, leggere la guida, o iscriverti al corso dedicato che trovi su ExcelProfessionale.it.
Usare le maschere pronte dello strumento Modulo per inserire dati nelle tabelle di Excel
In realtà Excel offre già uno strumento che crea sul momento maschere di inserimento e di ricerca. Lo strumento è Modulo dati (form in inglese), è presente in Excel e nel tutorial seguente ti spiego dove si trova, come usarlo, quando usarlo, i pro e i contro di questo strumento.
Modulo dati è uno strumento che produce maschere di inserimento e ricerca per le tabelle selezionate. Non si trova nella barra multifunzione di Excel, ma può essere aggiunto appositamente attraverso la finestra Opzioni di Excel, usando le apposite schede per personalizzazione della barra di accesso rapido o di quella multifunzione.
Lo strumento richiede che sia selezionata una tabella o un intervallo di dati con struttura standard verticale e produce una maschera specifica che presenta gli stessi campi della tabella e comandi aggiuntivi per la gestione.
Le maschere Excel così create sono strumenti che permettono di inserire, revisionare, eliminare e cercare tra i record della tabella collegata. Facile da usare, le maschere passano da tre stati diversi, inserimento nuovo record, revisione e visualizzazione e ricerca tramite criteri.
Come puoi vedere nel tutorial sono strumenti flessibili, utilizzabili anche tramite tastiera, ma nonostante la comodità, presentano numerosi limiti e lacune.
Crea maschere Excel di ricerca con i filtri avanzati e un pizzico di vba
Una maschera di ricerca è uno strumento che possiamo associare a una tabella Excel o a un intervallo di dati e che possiamo installare in una pagina del foglio di calcolo con cui possiamo ricercare ed estrarre record, singoli valori o parti di record usando strumenti di Excel, controlli dinamici e un poco di automazione.
Come si crea una maschera di ricerca?
Lo si può fare in molti modi diversi, per esempio affidando a strumenti di Excel predisposti l’intera funzionalità, come per esempio query, pivot e slicers.
O viceversa possiamo affidare all’automazione aggiunta tutte le funzioni richieste dallo strumento, ma questo richiede una conoscenza solida delle basi dell’automazioni. D’altra parte queste soluzioni permettono di ottenere strumenti su misura, personalizzati come vuoi, più efficienti e multifunzione.
Oppure possiamo usare soluzioni intermedie, dove agli strumenti di Excel affianchiamo un poco di automazione. Come nel tutorial in basso, dove creo una maschera di ricerca usando lo strumento filtro avanzato, alcuni controlli su pagina e un pizzico di vba.
Si procede in questo modo:
- prepara la pagina con la maschera,
- si installano uno o più controlli, nel modo che preferiamo (elenchi della convalida, caselle combinate, ecc),
- registra la macro che usa il filtro avanzato di Excel,
- revisiona la macro,
- si installano i pulsanti per lanciare l’automazione aggiunta nella maschera,
- si rifinisce la maschera.
Lo strumento ottenuto non è il migliore, ma è efficace e flessibile.
Prima di continuare con maschere Excel automatizzate più evolute, ti propongo alcune soluzioni di maschere senza automazione:
Una maschera di ricerca realizzata con le query
Come accennato prima è possibile usare strumenti di Excel avanzati per realizzare maschere di ricerca senza automazione aggiunta. In questo caso usiamo un modo efficiente e veloce, grazie alle caratteristiche dello strumento Power query, ora chiamato Recupera e Trasforma, delle tabelle Excel e degli slicers.
Come si procede?
- Imposti una query con Recupera e trasforma,
- integri la query in una tabella Excel,
- installi i controlli per filtrare (slicers) la tabella dei campi che vogliamo usare,
- aggiungi titoli, note ed istruzioni necessarie all’utente per sapere come usarla.
Lo strumento così ottenuto è limitato e relativamente comodo, ma funziona, è efficiente e come puoi vedere nel tutorial seguente è veloce da realizzare.
Due nota: lo strumento lo puoi realizzare dalla versione 2016 di Excel (non mac) e in Excel 365 puoi alimentare direttamente un pivot e non solo una tabella Excel, con vantaggi significativi.
Una maschera di ricerca con le tabelle pivot
Altra soluzione di maschera di ricerca ottenuta usando strumenti di Excel senza automazione aggiunta. Nel tutorial qui sotto ti mostro come si può creare una funzione di ricerca in una pagina dedicata usando le tabelle pivot di Excel.
Le pivot sono strumenti di elaborazione, ma possono essere usati in altri modi, per esempio anche per visualizzare record o porzioni di record.
Una maschera di ricerca con formule e funzioni di Excel
Si può creare una maschera per ricercare dati nelle tabelle di Excel in modo classico, usando formule e funzioni. Ci sono molte funzioni di ricerca e molti modi diversi per usarle per realizzare strumenti più o meno evoluti. Nel tutorial qui di seguito vedremo come si costruisce uno strumento per cercare stringhe in testi all’interno della base dati.
In particolare useremo un misto di funzioni, alcune classiche come Trova e altre evolute e recenti come Filtro, e insieme a nomi e controlli della convalida costruire una maschera flessibile e veloce da realizzare.
Una maschera di visualizzazione e stampa con le funzioni di Excel
Con le funzioni si possono realizzare altre funzionalità utili, come maschere di stampa e visualizzazioni con cui mostriamo set di dati estratti dalla basedati per poterli consultare, stampare o condividere.
Nel tutorial seguente vediamo il modo più semplice per creare una maschera di visualizzazione su pagina usando formule e funzioni.
Come?
- Creando la maschera nella pagina, costruendo il layout attorno alle indicazioni delle informazioni e dei valori che verranno visualizzati,
- installando controlli appositi per gli utenti,
- aggiungendo gli strumenti adatti per popolare la maschera con i dati da visualizzare.
Ci sono molte funzioni di ricerca e modi diversi per usarle per realizzare strumenti più o meno evoluti, nel tutorial useremo la funzione Cerca.Vert e ci limiteremo a importare una porzione di un record da una singola basedati.
Maschere di ricerca flessibili con le funzioni di Excel 365
Una soluzione più evoluta della precedente è quella del tutorial seguente, in cui ti mostro come si estraggono record, campi, o intere matrici estratte da una basedati usando formule e funzioni.
In particolare userò funzioni recenti come Filtro e altre più classiche come Indiretto, Trova e Val.Numero, insieme a nomi e agli elenchi della convalida e altri strumenti ancora.
Naturalmente ti invito a provare a realizzarlo e ad utilizzarlo nei tuoi fogli Excel.
Questi sono alcuni esempi di come si possono realizzare funzionalità su pagina con gli strumenti di Excel senza automazione aggiunta. Ora invece vedremo soluzioni evolute di maschere multifunzione con automazione aggiunta, realizzata con un pizzico di vba.
Aggiungi automazione a una tabella Excel per renderla più efficiente e comoda
Il tutorial che segue è una vera e propria lezione, un esempio della realizzazione di un progetto di automazione.
Il progetto consiste nell’aggiungere funzionalità a una tabella Excel per renderla più efficiente e comoda. Come?
Registrando macro con il registratore di macro che eseguono attività su una tabella Excel. L’intenzione è di trasformarla in uno strumento più adatto all’inserimento, ricerca e revisione dei dati.
Come una maschera multifunzione. E in effetti trasformeremo la tabella Excel in una vera maschera multifunzione, installando sopra la tabella un pannello di comando con cui controllare gli automatismi realizzati.
Nonostante la lunghezza ti invito a seguire fino in fondo il tutorial per cogliere tutti gli spunti offerti per poterli riutilizzare nei tuoi fogli Excel.
Creare maschere di inserimento personalizzate con un pizzico di vba
Nel tutorial seguente, ti mostro come è possibile realizzare maschere di inserimento personalizzate, cioè strutturate come preferisci, e farle funzionare con un pizzico di vba. È il completamento della prima maschera automatizzata più in alto, dove spiego come è possibile usare la stessa soluzione per maschere personalizzate, verticali e orizzontali, anche su più righe
Realizzare una maschera multifunzione in Excel con un pizzico di vba
Una maschera multifunzione è uno strumento con cui possiamo svolgere diverse attività in modo assistito e facilitato, come inserire dati in una basedati, ricercare e caricare i dati dalla basedati, eliminare record, aggiornare record, eccetera.
Nel tutorial seguente mostro come si crea l’automazione per una maschera multifunzione usando il registratore di macro e alcune delle conoscenze di base del vba.
Il registratore di macro permette di realizzare macro semplici senza conoscere il vba e se sai come usarlo al meglio puoi realizzare macro flessibili e riutilizzabili. Se conosci le basi del linguaggio allora puoi revisionare le macro registrate realizzando automazione efficiente, comoda e flessibile.
Nel tutorial vediamo come automatizzare una maschera multifunzione su pagina non limitandoci alla registrazione, ma lavorando in modo semplice sul codice.
Come creare una maschera multifunzione professionale con un pizzico di Vba
La coppia di tutorial seguenti mostrano come realizzare maschere Excel automatizzate multifunzione in pagina separata con un pizzico di vba, realizzato con il registratore e talvolta revisionato.
Nella prima parte ti mostro come aggiungere alla maschera la funzione di caricamento di un record dalla basedati alla maschera.
Nella seconda parte ti mostro come installare la funzione di aggiornamento dei record e non solo.
Se sei nuovo al vba ti consiglio di guardare il mini corso introduttivo all’automazione di Excel che trovi sul canale.
Conclusioni
Questa carrellata di tutorial vuole offrirti un percorso alla scoperta delle maschere e dell’automazione aggiuntiva realizzata con un pizzico di Vba.
Come hai visto le maschere sono funzionalità aggiunte costruire con gli strumenti del foglio di calcolo e un poco di automazione per rendere le soluzioni più efficienti, comode e professionali.
Quello che ti ho mostrato funziona ed è piuttosto semplice realizzarlo, ma sono poca cosa rispetto alle maschere pienamente automatizzate su pagina o su userform. Questo però richiedono la conoscenza del vba, almeno delle base per l’automazione delle maschere su pagina, a livello intermedio per l’automazione delle userform.
Se sei interessato ti invito a leggere le guide e a iscriverti ai corsi che trovi su ExcelProfessionale.it.
Se vuoi imparare a realizzare i tuoi fogli automatizzati posso insegnartelo
Puoi farlo imparando l’automazione di Excel. Anche per questo ho realizzato questo corso:
Vuoi una mano a realizzare o migliorare i tuoi strumenti?
Se hai bisogno di aiuto per realizzare o migliorare i tuoi strumenti forse posso darti una mano:
Cerchi strumenti professionali?
Se cerchi strumenti più efficienti, comodi e sicuri vai alla pagina degli strumenti pronti di Excel Professionale:
Come ottenere i fogli dimostrativi
Per conoscere tutti i fogli dimostrativi gratuiti pubblicati da Excel Professionale puoi cliccare qui.
Per riceverli registrati tramite il form qui di seguito e scaricali gratuitamente insieme agli altri contenuti gratuiti.
PS: Se questo post sulle maschere Excel automatizzate ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie
