Un test Excel avanzato con soluzioni serve a capire se sai davvero trasformare dati disordinati in analisi affidabili, non solo se ricordi qualche formula. Ho raccolto esercizi realistici su funzioni di ricerca, formule condizionali, tabelle pivot, Power Query e controllo degli errori, con spiegazioni rapide per verificare ogni risposta.
Le competenze che questo test mette davvero alla prova
- 8 esercizi pratici su formule, analisi e automazione.
- Soluzioni commentate, non soltanto il risultato finale.
- Uso di CERCA.X, SOMMA.PIÙ.SE, FILTRO e funzioni dinamiche.
- Valutazione distinta tra conoscenza tecnica e capacità di ragionamento.
- Indicazioni per prepararsi a un colloquio o a una prova aziendale.
Che cosa significa davvero Excel avanzato
Per me, Excel avanzato non significa conoscere decine di formule a memoria. Significa saper scegliere lo strumento più solido per ogni problema, mantenere il file leggibile e arrivare a un risultato che un collega possa controllare senza ricostruire tutto da capo.
Un test serio verifica quindi più aree insieme. Le più frequenti sono ricerca e collegamento dei dati, calcoli con più condizioni, gestione degli errori, tabelle pivot, importazione e pulizia dei dati con Power Query e, nei ruoli più tecnici, automazione tramite VBA.
| Area | Che cosa dimostra | Esempio di prova |
|---|---|---|
| Formule | Precisione e capacità di combinare più criteri | Calcolare il fatturato di una regione in un certo periodo |
| Ricerca dati | Collegare tabelle senza copiare informazioni manualmente | Associare prezzo e categoria a ogni codice prodotto |
| Pivot e report | Sintetizzare grandi quantità di righe | Confrontare vendite per mese, area e commerciale |
| Power Query | Ripetere la pulizia dei dati in modo affidabile | Unire file mensili con la stessa struttura |
| Automazione | Ridurre attività ripetitive e soggette a errore | Creare un report aggiornabile con un pulsante |
Una prova può essere composta da domande a scelta multipla oppure da un file da completare. Nel secondo caso il tempo è spesso più importante della difficoltà teorica: un candidato preparato sa controllare la qualità del risultato, non solo produrre una formula funzionante.
Test pratico con esercizi e dati di esempio
Immagina una tabella denominata Vendite con le colonne Data, Regione, Commerciale, Codice, Quantità, Prezzo e Stato. Prova a risolvere gli esercizi senza guardare subito le risposte.
1. Calcolare il valore di ogni riga
Devi ottenere il valore della vendita moltiplicando quantità e prezzo, ma lasciare vuota la cella quando uno dei due dati manca.
Soluzione: =SE(O([@Quantità]="";[@Prezzo]="");"";[@Quantità]*[@Prezzo])
L’uso di una tabella strutturata rende la formula più leggibile e la estende automaticamente alle nuove righe. È un dettaglio semplice, ma in una prova avanzata dimostra che sai costruire un file destinato a crescere.
2. Recuperare la categoria dal codice prodotto
In un secondo foglio, chiamato Catalogo, sono presenti il codice prodotto e la relativa categoria. Devi riportare la categoria nella tabella delle vendite.
Soluzione consigliata: =SE.ERRORE(CERCA.X([@Codice];Catalogo[Codice];Catalogo[Categoria]);"Codice non trovato")
CERCA.X è preferibile a CERCA.VERT quando la versione di Excel lo supporta, perché cerca in modo più flessibile e non dipende dalla posizione numerica della colonna. Se devi lavorare con file destinati a versioni molto vecchie, INDICE e CONFRONTA restano un’alternativa affidabile.
3. Sommare le vendite con due condizioni
Calcola il fatturato delle vendite concluse nella regione Lombardia. Supponi che il valore della riga sia nella colonna Valore.
Soluzione: =SOMMA.PIÙ.SE(Vendite[Valore];Vendite[Regione];"Lombardia";Vendite[Stato];"Conclusa")
Il punto da controllare è la corrispondenza tra gli intervalli. Tutte le colonne devono avere la stessa dimensione. Un errore frequente consiste nell’usare un intervallo più corto per un criterio, ottenendo risultati sbagliati senza che Excel mostri necessariamente un messaggio evidente.
4. Estrarre un elenco dinamico
Produci un elenco senza duplicati dei commerciali che hanno registrato almeno una vendita conclusa.
Soluzione in Microsoft 365: =ORDINA(UNICI(FILTRO(Vendite[Commerciale];Vendite[Stato]="Conclusa")))
Questa formula sfrutta le matrici dinamiche, cioè risultati che si espandono automaticamente nelle celle sottostanti. Se il file deve essere condiviso con utenti privi di Microsoft 365, è più prudente usare una tabella pivot o una procedura compatibile con versioni precedenti.
5. Individuare le vendite sopra la media
In una colonna di controllo, segnala ogni vendita il cui valore supera la media delle vendite concluse.
Soluzione: =SE([@Stato]<>"Conclusa";"Esclusa";SE([@Valore]>MEDIA.PIÙ.SE(Vendite[Valore];Vendite[Stato];"Conclusa");"Sopra media";"Nella media o sotto"))
Qui non conta solo conoscere la funzione. Bisogna prima definire correttamente la popolazione di confronto. Includere gli ordini annullati nella media altererebbe il benchmark e renderebbe poco utile l’analisi.
6. Creare una classifica con i primi risultati
Restituisci le cinque vendite concluse con il valore più alto. In Microsoft 365 puoi usare una formula dinamica.
Soluzione possibile:
Per limitare il risultato alle prime cinque righe, puoi racchiudere la formula in PRENDI, quando disponibile. In alternativa, una tabella pivot con filtro sui primi 5 elementi è spesso più facile da condividere con chi userà il report.
Le soluzioni che distinguono un candidato preparato
Una formula corretta non è sempre la soluzione migliore. In un test avanzato osservo soprattutto manutenibilità, chiarezza e controllo. Un foglio pieno di riferimenti assoluti e formule annidate può funzionare oggi, ma diventare fragile al primo aggiornamento.
| Problema | Scelta debole | Scelta più robusta |
|---|---|---|
| Collegare due elenchi | Copiare e incollare i dati | CERCA.X o relazione nel modello dati |
| Aggregare vendite | Molte somme manuali | SOMMA.PIÙ.SE o tabella pivot |
| Pulire file ripetitivi | Correzioni manuali ogni mese | Power Query con passaggi registrati |
| Gestire valori mancanti | Nascondere gli errori senza criterio | SE.ERRORE con messaggio interpretabile |
| Automatizzare report | Macro registrata senza controlli | VBA commentato e validato su una copia |
Power Query merita una menzione particolare. È lo strumento che userei per importare file CSV, uniformare i nomi delle colonne, eliminare righe vuote e cambiare i tipi di dato. Il vantaggio non è solo risparmiare tempo, ma poter ripetere lo stesso processo senza modificare manualmente il risultato.
Le tabelle pivot, invece, sono spesso la scelta più rapida per esplorare i dati. Non sostituiscono sempre un modello dati ben progettato, soprattutto quando esistono più tabelle collegate, ma permettono di verificare in pochi secondi se i totali hanno senso.
Come correggere gli errori senza perdere tempo
Durante una prova pratica non conviene inseguire subito la formula perfetta. Io controllo prima tre elementi: i dati sono numerici o testuali, le date sono vere date, e gli intervalli hanno la stessa dimensione. Molti errori attribuiti a Excel nascono da dati importati male.
Leggi anche: Varianza in Excel, quale funzione usare?
Gli errori più comuni
- #N/D indica spesso un codice assente o scritto in modo diverso nella tabella di riferimento.
- #VALORE! può dipendere da numeri memorizzati come testo o da caratteri invisibili.
- #RIF! segnala un riferimento cancellato e richiede un controllo della struttura del file.
- Una somma pari a zero può derivare da criteri con spazi finali, maiuscole non uniformi o date non riconosciute.
- Un risultato duplicato può indicare che la tabella di riferimento non contiene una chiave univoca.
Non coprirei ogni problema con SE.ERRORE(...;""). La cella vuota è gradevole da vedere, ma nasconde la causa dell’anomalia. In un report operativo preferisco messaggi come “Codice non trovato” o “Verificare la data”, perché aiutano chi dovrà correggere il file.
Come interpretare il punteggio del test
Puoi assegnare un punto per ciascuno degli otto esercizi e usare questa scala come riferimento. Non è una certificazione formale, ma offre una fotografia abbastanza utile delle aree da consolidare.
| Risultato | Interpretazione | Che cosa allenare |
|---|---|---|
| 0-3 punti | Livello base o intermedio iniziale | Struttura delle formule, tabelle e riferimenti |
| 4-5 punti | Buona operatività quotidiana | Funzioni con più criteri e controllo degli errori |
| 6-7 punti | Livello avanzato concreto | Power Query, pivot e modelli riutilizzabili |
| 8 punti | Preparazione solida, da verificare sul campo | Velocità, comunicazione e automazione |
Il tempo cambia la lettura del risultato. Risolvere otto esercizi in 20 minuti richiede automatismi diversi rispetto a completarli in un’ora con verifiche accurate. Nei colloqui aziendali può essere valutata anche la capacità di spiegare perché hai scelto una formula e come controlleresti il dato prodotto.
Come prepararsi a una prova aziendale
La preparazione più efficace non consiste nel ripassare liste infinite di funzioni. Costruisci un piccolo dataset con almeno 500 righe, aggiungi errori realistici e prova a creare un report da zero. Inserisci duplicati, date mancanti, codici non presenti e valori numerici salvati come testo.
- Dedica una sessione alle formule di ricerca e alle funzioni con criteri multipli.
- Ricrea lo stesso report con una tabella pivot e confronta i totali.
- Importa tre file mensili con Power Query e prova ad aggiornarli.
- Controlla i risultati con totali indipendenti, filtri e riconciliazioni.
- Ripeti tutto con un limite di tempo realistico, tra 30 e 45 minuti.
Se il ruolo riguarda controllo di gestione o business intelligence, darei priorità a modelli dati, pivot, Power Query e qualità del dato. Per un ruolo amministrativo possono pesare di più funzioni condizionali, riconciliazioni e formattazione. VBA è utile, ma non lo presenterei come prova di competenza avanzata se il file non richiede davvero automazione.
Il criterio più utile per capire se sei pronto
Se riesci a risolvere gli esercizi ma non sai spiegare il risultato, la preparazione è ancora incompleta. Un buon livello avanzato si vede quando puoi scegliere la tecnica adeguata, verificare il dato e consegnare un file comprensibile anche a chi non ha scritto la formula.
Ripeti il test dopo alcuni giorni senza consultare le soluzioni e misura sia il punteggio sia il tempo. È il confronto tra i due valori a dirti se stai imparando davvero o se stai soltanto riconoscendo formule già viste.
