Guida alle funzioni Excel

Questa è una guida alle funzioni di Excel. Le funzioni Excel sono le regine degli strumenti per l’elaborazione di dati e sono una competenza necessaria per chi vuole usare il foglio di calcolo per l’analisi dei dati. Questa è una guida introduttiva centrata sugli argomenti fondamentali e su quelli più avanzati di questi strumenti, che non ha la pretesa di essere esaustiva, nè completa. E’ una guida “aperta” che verrà ampliata quando troverò il tempo per lavorarci.

Le formule di Excel

Le formule sono strumenti fondamentali del foglio di calcolo, strumenti potenti e flessibili con cui elaboriamo e analizziamo dati.

Ma in poche parole cosa sono le formule di Excel?

Sono sequenze di istruzioni codificate che inseriamo nelle celle per farle eseguire dal foglio di calcolo.

Le formule iniziano con un uguale (“=”) e sono composte da valori, riferimenti, operatori e soprattutto funzioni.

Perché soprattutto? Perché le funzioni permettono di semplificare le formule, offrendo “scorciatoie” per ottenere il risultato desiderato.

Cosa sono le funzioni di Excel?

Sono strumenti, sono automatismi che elaborano in modo veloce, preciso e specifico i dati che forniamo loro.

Inserire il nome della funzione in una formula comunica a Excel che deve eseguire l’automatismo corrispondente, utilizzando i parametri inseriti tra parentesi per ottenere il risultato che verrà visualizzato, oppure utilizzato nella formula, o perfino come parametro di altre funzioni.

A cosa servono le formule di Excel

Le formule sono strumenti per eseguire calcoli ed elaborare i dati di una o più basedati. Con una formula puoi fare una semplice somma di una serie di valori, ma puoi elaborare interi campi, milioni di dati, per ottenere un valore specifico o una serie di valori (formule matriciali).

Puoi anche estrarre una porzione di una basedati, o conteggiare i valori univoci di un campo, come puoi cercare un valore preciso all’interno della matrice dei dati. Puoi calcolare parametri statistici, come puoi usare le formule di Excel per eseguire controlli anche multipli su una o molte celle, anche molti campi di una basedati.

Come sono fatte le formule di Excel

Le formule iniziano con un uguale, la cui presenza attiva la modalità di inserimento formule nella cella. All’uguale seguono valori e operatori, ma i valori possono essere restituiti da un riferimento a una cella o a un intervallo di celle, oppure possono essere restituiti da una funzione, uno strumento di elaborazione specifico.

Quindi una formula è composta da:

1. valori, inseriti direttamente o tramite riferimenti,
2. riferimenti, con la notazione colonna-riga, o con quella delle tabelle Excel,
3. operatori, per eseguire operazioni matematiche,
4. funzioni, per svolgere calcoli o elaborazioni complesse in modo semplice.

I riferimenti in Excel

Cos’è un riferimento?

È come un indirizzo stradale.

Un riferimento è un indirizzo interno alle pagine del foglio di calcolo che indica alle formule una o più celle della pagina (o di un’altra pagina, o anche di un altro foglio), da cui prendere i valori da elaborare. È Excel che si occupa di recuperare i valori presenti nelle celle indicate per usarli nell’elaborazione e sarà sempre il foglio di calcolo che quando i valori cambiano aggiornerà le formule collegate ricalcolando il risultato.

Nella realtà è piuttosto raro che in una formula venga inserito un valore, che di solito vengono appunto restituiti da riferimenti.

I riferimenti classici standard, sono i riferimenti colonna-riga, come A1, G5:L25, A:A, eccetera, con cui possiamo indicare una singola cella, come intere matrici di dati, colonne o righe. Naturalmente le lettere sono i nomi delle colonne, mentre i numeri sono quelli delle righe.

I riferimenti delle tabelle Excel

Con l’introduzione delle tabelle Excel è stato introdotto un nuovo sistema di riferimenti dedicati, riconoscibili dalla presenza delle parentesi quadre ([ e ]). Il sistema indica una cella o una porzione di una tabella (campo, record, dati, intestazioni, totali) in modo parlante tramite i nomi della tabella e dei campi, come per esempio:

1. NomeTabella per riferirsi ai dati dell’intera tabella;
2. NomeTabella[NomeCampo] per riferirsi ai dati del campo indicato;
3. NomeTabella[[#Intestazioni];[NomeCampo]] per riferirsi alla cella con l’intestazione del campo indicato;
4. NomeTabella[[#Tutti];[NomeCampo]] per riferirsi all’intero campo indicato.

Se vuoi approfondire i riferimenti delle tabelle Excel, ti invito a leggere la guida relativa che trovi su ExcelProfessionale.it.

I riferimenti delle tabelle Excel sono assoluti e vengono aggiornati in automatico. Cosa intendo? Intendo dire per esempio che se aggiungiamo record alla tabella Excel, il riferimento “NomeTabella[NomeCampo]” non dovrà essere aggiornato per comprendere i nuovi record, ma si riferirà sempre a tutte le celle del campo indicato, anche quelle aggiunte.

Le funzioni in Excel

Le funzioni sono centinaia, un fiume di strumenti che funzionano apparentemente in modo simile: inserisci il nome, imposti i parametri e chiudi correttamente l’ultima parentesi. Le funzioni possono restituire numeri, vero o falso, riferimenti, errori, stringhe e altro.

Per poter usare le funzioni in modo efficace ed efficiente, devi studiare e fare pratica con ognuna di esse. Per fortuna oggi Excel offre strumenti davvero efficaci e comodi che ci assistono nell’apprendimento delle funzioni.

Gli strumenti che ci aiutano e guidano i nostri primi passi con le funzioni sono i seguenti:

1. la guida online di Excel

se premiamo F1, o clicchiamo sul comando “Guida” presente nella scheda omonima della barra multifunzione, compare a destra la fineda della guida in cui possiamo fare ricerche per parole chiave; inserendo il nome della funzione richiamiamo la scheda della funzione con descrizione, suggerimenti ed esempi; la prima volta che usiamo una funzione dovremmo leggere la scheda con attenzione e provare prima gli esempi indicati; possiamo richiamare la guida anche mentre scriviamo le formule o attraverso gli strumenti di scrittura;

2. i video formativi di base di Excel

microsoft ha realizzato una serie di video formativi di base dedicati ai principianti, che possiamo trovare sul sito Office, o che possiamo raggiungere e vedere tramite la guida o cliccando sul comando “mostra formazione” che trovi nella scheda “Guida” della barra multifunzione; comparirà la scheda della guida alla pagina “video di formazione”, cliccando sull’icona “Formule e funzioni” potremo accedere a un video introduttivo e a una serie di video specifici; la voce italiana è sintetica e poco gradevole, ma sono comunque utili e ne consiglio la visione;

3. il sito di microsoft office

il sito di office è una miniera di informazioni e guide ed è il primo luogo che dovresti consultare se cerchi qualsiasi informazione su Excel che non riesci a trovare tramite la guida; l’url della pagina di supporto a office è: https://support.microsoft.com/it-it/office, ma ti invito a saltare direttamente alla pagina di Excel, qui, dove puoi trovare anche le pagine dedicate a formule e funzioni che naturalmente ti invito a leggere dopo aver finito di leggere questa guida;

4. la libreria delle funzioni

mi riferisco al gruppo di comandi e menu che trovi nella scheda “Formule” della barra multifunzione; se puoi, apri excel ora e studiala; come puoi vedere il gruppo libreria di funzioni è composto da un comando per richiamare la finestra “inserisci funzioni”, dal comando somma automatica per inserire la somma con un clic o la pressione di ALT+=, e da una serie di menu che propongono le funzioni a disposizione e che puoi inserire semplicemente cliccando sul nome; queste verranno inserite nella cella selezionata e verrà aperta la finestra argomenti funzione che ti assisterà nell’inserimento dei parametri della funzione;

5. la finestra “Inserisci funzioni”

è uno strumento classico pensato per i principianti che assiste nell’inserimento delle funzioni; una volta selezionata la cella, o mentre si sta scrivendo la formula, cliccando sul comando o premendo MAIUS+F3 si richiama la finestra che offre una barra per la ricerca, un elenco filtrato, una descrizione della funzione e la possibilità di richiamare la guida alla funzione con un clic;

6. il tuo motore di ricerca preferito

nel caso tu non sia soddisfatto delle informazioni e degli strumenti precedenti, puoi trovare molto altro sui siti e i forum dedicati a Excel che puoi individuare con una ricerca tramite il tuo motore di ricerca preferita; non devi fare altro che inserire il nome della funzione e aggiungere “excel ed eventuali termini per precisare quello che cerchiamo; questa soluzione non serve ai principianti, ma a chi vuole approfondire l’uso pratico delle funzioni e per esempio cerca esempi che si avvicinano ai suoi bisogno;

7. gli strumenti che ci assistono nella scrittura delle formule di Excel
   

sono il set di strumenti avanzati che appaiono durante la scrittura delle formule e ci assistono nella scrittura di cui parleremo qui di seguito. Questi strumenti ci offrono l’elenco interattivo delle formule, una descrizione sintetica, lo schema dei parametri e altro.

Come imparare le funzioni se sei un principiante

In sostanza devi:

1. studiare le funzioni,
2. fare pratica con le funzioni.

Studiare le funzioni significa:

1. leggere la guida a formule e funzioni che trovi su ExcelProfessionale
2. guardare i video formativi di Excel
3. imparare a usare la guida di Excel
4. studiare le schede delle funzioni attraverso la guida di Excel
5. esplorare la libreria delle funzioni
6. eventualmente fare approfondimenti tramite ricerche sul web.

Come fare pratica?

Scrivendo formule e funzioni per elaborare dati preferibilmente reali per ottenere risultati precisi e concreti. Se non è possibile puoi usare dati diversi.

Se sei un principiante, ti invito a partire dai modelli che trovi già dentro Excel. Come? Apri Excel, clicca su “file” (prima voce della barra multifunzione) per accedere alla finestra con la barra verde a sinistra, clicca sul comando “Nuovo”.

Nelle ultime versioni di Excel puoi accedere ai modelli pronti che il produttore offre. Tra questi trovi quelli formativi, riconoscibili dalla barra verde in basso, tra cui puoi trovare la guida introduttiva alle formule di Excel, che permette di fare qualche esercizio.

Il passo seguente è scaricare gli altri modelli che puoi trovare per:

1. usare le basedati dove presenti per fare pratica con le formule
2. studiare le formule presenti nei modelli, replicarle e dove utile adattarle ai propri bisogni.

Ricorda che in realtà le funzioni più usate di solito sono una trentina al massimo e ti invito a leggere questo post e anche questo per scoprire quali sono.

Ma prima di lavorare sulle funzioni ti invito ad imparare come si scrivono le formule e quali strumenti devi conoscere per farlo nel modo migliore.

Come si scrivono le formule di Excel

Le formule si scrivono:

1. digitandole direttamente nella cella o nella barra multifunzione, assistiti dagli strumenti dedicati,
2. inserendole tramite finestre e strumenti di supporto.

Il primo modo è quello naturale di chi ha una conoscenza di base delle formule e delle funzioni, mentre il secondo è quello dei principianti che devono ancora imparare il funzionamento delle funzioni e fare abbastanza pratica per comprendere l’uso efficiente ed efficace degli strumenti di supporto.

Quindi se sei un principiante ti invito a partire dal secondo, ma ti invito ad abbandonarlo il più presto possibile per il primo, molto più efficiente e comodo.

Prima di approfondire l’argomento però vediamo quali sono gli strumenti che ci assistono durante la scrittura delle formule di Excel.

Gli strumenti che ci aiutano nella scrittura delle formule

Quali sono gli strumenti che ci assistono nella scrittura delle formule?

Gli strumenti principali che devi imparare a conoscere sono questi:

1. l’elenco delle funzioni e dei nomi

è una piccola finestra che compare sotto la cella quando iniziamo a digitare nomi e funzioni all’interno di una formula; mostra un elenco che viene filtrato in automatico via via che digitiamo e che possiamo scorrere con i tasti Su e Giù per selezionare una delle voci; per inserire la voce selezionata devi premere Tab;

2. l’help delle funzioni

è una piccola finestra che compare insieme all’elenco delle funzioni per descrivere brevemente cosa può fare la funzione selezionata, è analogo all’help che compare se sosti sui comandi della barra multifunzione, sui nomi delle funzioni dai menu della libreria delle funzioni;

3. la compilazione assistita dei riferimenti

quando entriamo in modalità di inserimento formule o in modalità di revisione, possiamo inserire i riferimenti:

1. a) scrivendoli direttamente,
2. b) o selezionandoli tramite il mouse (cliccando sulla cella, o selezionando più celle tenendo premuto il pulsante del mouse),
3. c) o tramite la tastiera (usando i tasti direzione per selezionare la cella e il tasto maiuscolo insieme ai tasti direzione per selezionare più celle);

i riferimenti verranno mostrati di colore diverso, uguali tra loro se si riferiscono allo stesso intervallo o cella; inoltre se si selezionano i riferimenti verranno evidenziate le celle con lo stesso colore:

4. la compilazione assistita delle funzioni

è una piccola finestra che compare quando inseriamo la funzione (es: premendo tab dall’elenco delle funzioni, o inserendo la parentesi dopo aver scritto il nome della fuzione), o quando revisioniamo la formula;

è uno strumento molto utile che mostra i parametri/argomenti della funzione che dobbiamo inserire ed è interattivo:

1. a) cliccando sul nome della funzione apriamo la guida alla scheda della funzione,
2. b) cliccando sui diversi parametri, se sono presenti, vengono selezionati nella formula;
3. la barra delle formule

è la barra di inserimento orizzontale sopra le intestazioni delle colonne della pagina e mostra il contenuto della cella selezionata; possiamo inserire valori o formule direttamente nella cella o nella barra delle formule e il foglio di calcolo ci assisterà nello stesso modo, con poche differenze che riguardano la visualizzazione delle formule e i limiti degli strumenti;

6. la guida online

è una finestra apposita collegata online al servizio dedicato del produttore, che possiamo richiamare premendo F1, o cliccando sul comando “Guida” della scheda omonima della barra multifunzione, o cliccando sui link porposti dai diversi strumenti (finestra interattiva delle funzioni, finestra inserisci funzioni, ecc);

la finestra compare agganciata al bordo destro del programma e ci offre comandi per realizzare ricerche per parole chiave; per esempio inserendo il nome della funzione richiamiamo la scheda della funzione con descrizione, suggerimenti ed esempi; è lo strumento fondamentale per i principianti;

7. finestra “Inserisci funzione”

è uno vecchio strumento di Excel che assiste nell’inserimento delle funzioni pensato per chi non conosce le funzioni; per richiamarlo si deve cliccare sul comando omonimo che troviamo nella scheda “Formule” della barra multifunzione, cliccare sul simbolo “fx” a sinistra della barra delle formule, o semplicemente premendo MAIUS+F3;

la finestra offre una barra per la ricerca, un elenco delle categorie, un elenco filtrabile, una descrizione della funzione e la possibilità di richiamare la guida alla funzione con un clic sul link preimpostato; una volta che hai individuato la funzione che vuoi inserire basta cliccare su “Ok” per inserirla nella cella o nella formula che si sta scrivendo e aprire la finestra “Argomenti funzione”;

8. finestra “Argomenti funzione”

questa finestra è l’altra faccia della finestra “Inserisci funzione” e non a caso la possiamo richiamare con gli stessi comandi della precedente (simbolo “fx”, MAIUS+F3, comando “Inserisci funzione”); questo strumento serve per inserire gli argomenti/parametri delle funzioni tramite caselle di inserimento in cui possiamo inserire valori, riferimenti, anche tramite selezione sulla pagina (usando il comando apposito);

alla selezione dell’argomento compare la spiegazione del parametro da inserire anche con esempi e spiegazioni; la finestra offre anche la possibilità di vedere il risultato con i parametri impostati e il link per aprire la guida alla scheda della funzione impostata;

9. la libreria delle funzioni

è il gruppo di comandi e menu che trovi nella scheda “Formule” della barra multifunzione, composto dal comando per richiamare la finestra “inserisci funzioni”, dal comando per inserire la somma con un clic e da una serie di menu che propongono le funzioni a nostra disposizione;

sono lunghi elenchi che puoi inserire nella cella attiva o selezionata cliccando sul nome a cui seguirà l’apertura della finestra “Argomenti funzione” che ti assisterà nell’inserimento dei parametri come spiegato qui sopra; per sapere quale funzione usare, è fondamentale l’help che compare sopra al nome della funzione che offre anche il link per richiamare la guida alla pagina relativa;

10. la gestione dei nomi

come abbiamo visto in altri post e guide Excel permette di impostare e usare nomi personalizzati al posto dei riferimenti ufficiali (colonna-riga e tabelle Excel), ebbene questo è uno strumento utile per la scrittura delle formule e non solo perché possiamo usare nomi parlanti che compariranno nell’elenco delle funzioni e che quindi potremo inserire comodamente;

i nomi permettono di ottenere molto di più, come per esempio permettono di impostare riferimenti dinamici, cioè riferimenti che si aggiornano secondo il contenuto delle celle, o secondo impostazioni controllate tramite maschere apposite o altro ancora; non spiegherò qui tutte le possibilità dei nomi, ma ti rimando alla guida relativa che trovi sul sito; i nomi sono strumenti che devi usare se vuoi ottenere il massimo dalle formule;

11. gli strumenti di controllo degli errori

infine non posso non citare gli strumenti per la gestione degli errori che trovi nella scheda “Formule” della barra multifunzione, con cui possiamo ricercare le cause degli errori che le formule restituiscono; non spiegherò qui questo argomento non semplice, ma ti rimando alla guida introduttiva che trovi sul sito.

Scrivere formule in pratica

Ora che abbiamo un’idea degli strumenti riprendiamo l’argomento su come si scrivono le formule. Abbiamo detto che le formule si scrivono in 2 modi fondamentali (non consideriamo le varianti):

1. digitandole direttamente nella cella o nella barra multifunzione, assistiti dagli strumenti dedicati,
2. inserendole tramite finestre e strumenti di supporto.

Scrivere la formula nella cella

È il modo più efficiente se hai una solida conoscenza delle funzioni che vuoi usare e si procede come segue:

1. si parte digitando un uguale per passare alla modalità inserimento per poi proseguire con la formula;
2. quando devi inserire una funzione inizi a digitarla, di solito sono sufficienti 2-4 caratteri per richiamare l’elenco delle funzioni filtrato secondo quello che hai digitato;
3. puoi scorrere l’elenco premendo Giù e Su fino a selezionare la funzione che desideri inserire
4. premi Tab per inserire la funzione ed excel aggiungerà la parentesi per passare in modalità inserimento dei parametri della funzione, che ti verranno rammentati dalla finestra interattiva che ti assiste nella compilazione
5. se devi inserire riferimenti puoi digitarli, per esempio se hai impostato dei nomi personalizzati o se ti riferisci a una porzione di una tabella Excel; oppure puoi farti aiutare da Excel selezionando con mouse o tastiera l’intervallo da inserire;
6. se invece devi inserire operatori o valori, puoi inserirli direttamente tramite tastiera.

Naturalmente puoi usare funzioni dentro a funzioni, per restituire parametri della funzione più esterna, così come puoi usare le funzioni più volte per restituire valori diversi nella formula.

Per revisionare o modificare la formula puoi entrare nella modalità inserimento premendo F2, per poi usare la tastiera e lo strumento per la compilazione assistita per navigare tra le parti della funzione, selezionare e riscriverle.

Inserire la formula tramite finestre e strumenti

Se invece sei un principiante o comunque hai una conoscenza minima di formule e funzioni, allora l’invito è iniziare usando gli strumenti pensati per assisterti, mentre studi le basi dell’argomento, come suggerito in precedenza.

Se la formula inizia con una funzione

Si procede come segue:

1. si sceglie la funzione dalla libreria delle funzioni della scheda “Formule” e si clicca sul nome della funzione per inserirlo nella cella; di seguito si aprirà la finestra “Argomenti funzioni”;
2. si impostano i parametri/argomenti delle funzioni tramite la finestra, inserendo valori, riferimenti, eventuali formule e funzioni;
3. se necessario si richiama la finestra della guida dedicata alla funzione per rileggere gli esempi e i suggerimenti;
4. si clicca sul pulsante “Ok” per confermare l’inserimento dei parametri;
5. si prosegue nell’inserimento della formula fino al completamento;
6. per poi premere invio per l’inserimento.

Se la formula non inizia con funzioni o è complessa

Si procede come segue:

1. si parte digitando un uguale per passare alla modalità inserimento per poi proseguire con la formula inserendo riferimenti, operatori, eventuali valori;

2.1. quando si deve inserire una funzione, o si sceglie la funzione dalla libreria delle funzioni della scheda “Formule” e si clicca sul nome,

2.2. oppure si clicca sul comando “Inserisci funzione” per richiamare la finestra omonima;

3.1. nel primo caso si impostano i parametri/argomenti delle funzioni tramite la finestra, inserendo valori, riferimenti, eventuali formule e funzioni;

3.2. nel secondo caso si ricerca la funzione tramite la finestra “Inserisci funzione”, si seleziona e si clicca su “Ok” passando alla finestra “argomenti funzione” con cui impostare i parametri come indicato in 3.1. qui sopra;

4. se necessario si richiama la finestra della guida dedicata alla funzione per rileggere gli esempi e i suggerimenti;
5. si prosegue nell’inserimento della formula fino al completamento;
6. per poi premere invio per l’inserimento.

In sostanza di usano strumenti che guidano passo passo e offrono la possibilità di accedere alla guida per assisterti nella compilazione degli elementi delle funzioni e delle formule. Questo finché la pratica non ti rende in grado di procedere da solo e non comprendi che questi strumenti per principianti sono in realtà poco efficienti.

Prima di scriverle le formule si progettano

Fino ad ora abbiamo parlato dell’aspetto tecnico dello scrivere le formule, degli strumenti con cui lo facciamo e di come si usano. In realtà scrivere una formula è qualcosa di più.

E non mi riferisco alla conoscenza della matematica o della statistica necessarie per la scrittura di una formula matematica o statistica, necessarie per sapere, per esempio, cosa sono e quali sono gli operatori e come si usano.

Mi riferisco invece alla comprensione di quello che vogliamo ottenere dalla formula che vogliamo scrivere. Qual obiettivo vuoi raggiungere? Quale bisogno vuoi soddisfare? Scrivere una formula parte da questo.

Il primo passo non è quindi la scrittura della formula, ma è definire con precisione cosa vuoi ottenere dalla formula. Se sei un principiante ti invito a scrivere l’obiettivo, nel tuo linguaggio naturale, per esempio: “voglio sapere qual è il fatturato dell’anno in corso”.

Il passo successivo è definire l’obiettivo con la massima precisione, per esempio: “voglio calcolare la somma del fatturato dell’azienda dell’anno in corso”.

Il terzo passo è trasformare l’obiettivo in una o più formule, estraendo gli elementi fondamentali: operazioni da svolgere, valori da elaborare, condizioni e filtri da applicare. Nel nostro esempio l’operazione è una somma, i valori da sommare sono gli importi del fatturato, i filtri riguardano la data della fattura, vano sommati solo gli importi delle fatture con data compresa nell’anno in corso. Quindi è una somma condizionata.

Il passo successivo è verificare la disponibilità dei dati e se necessario l’estrazione degli stessi dal database dell’azienda.

Il quinto passo è definire gli elementi della formula, quindi funzioni, operatori e riferimenti che useremo. Nel nostro esempio, essendo una somma condizionata useremo la funzione apposita SOMMA.Più.SE() e riferimenti ai campi della tabella Excel che contiene i dati del fatturato.

Infine puoi procedere alla scrittura della formula, con gli strumenti e i metodi che abbiamo visto.

Quali sono le funzioni Excel che devi conoscere?

Le funzioni di Excel sono centinaia e svolgono calcoli ed elaborazioni complesse in modo semplice da impostare e gestire. Le funzioni più utilizzate e che devi conoscere se usi il foglio di calcolo per elaborare dati sono queste:

1. SOMMA(num1, num2, … numN)

   La prima funzione che si impara, con perfino un pulsante dedicato nella barra principale (“Formule” poi “Somma automatica”) e una sequenza di tasti veloci dedicati (Alt+=, o Alt+Maiusc+0). Un tempo era necessario selezionare l’intervallo di celle, ma nelle ultime versioni è Excel che analizza i dintorni della cella in cui stai inserendo la funzione e riconosce l’intervallo di dati da sommare (di solito la colonna di celle sopra a quella che stai compilando). E’ utile, all’inizio, ma imparando a usare gli strumenti più avanzati, per esempio le pivot, la utilizzi sempre meno.

2. SOMMA.SE(intervallo; criterio; int_somma)

   La prima delle funzioni avanzate che si imparano: la somma condizionata, cioè l’ordine di sommare solo i dati dei record che soddisfano una precisa condizione. Presa una tabella di dati, la funzione richiede l’indicazione del campo (colonna) su cui applicare il criterio, un valore o una stringa, e del campo dei valori da sommare.
   Per esempio, dai dati delle vendite vogliamo estrarre il fatturato di una singola nazione, quindi impostiamo SOMMA.SE(D:D; “Italia”; F:F), dove la colonna D è il campo della nazione, “Italia” è il criterio di ricerca, la colonna F è il campo del fatturato. Naturalmente il criterio può essere contenuto in una cella, il cui riferimento inseriamo nella funzione. Anche questa funzione viene rapidamente abbandonata quando si imparano a usare strumenti più evoluti.

3. SOMMA.PIU.SE(int_somma; intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2]; …)

   Dove non riusciamo ad arrivare con somma.se, possiamo riuscire con questa funzione che permette di sommare valori secondo più criteri. Per esempio se vogliamo conoscere il fatturato determinato da un certo tipo di prodotto per il mercato europeo, allora usiamo SOMMA.PIÙ.SE(F:F; B:B; “Canadese”; C:C; “Europa”), dove la colonna F contiene il fatturato, quella B il prodotto in cui cercare “Canadese” e quella C il mercato in cui cercare “Europa”.
   Questa funzione avanzata è veramente potente e risulta utile nella costruzione di report finanziari o similari. Nonostante questo anch’essa può essere sostituita da una tabella pivot, almeno nella fase di analisi dei dati o di controllo.

4. CONTA.SE(intervallo;criterio)

   Altra funzione che si impara in fretta agli inizi e si abbandona una volta imparate le pivot è il “conta condizionale”, utilizzato per contare le celle che rispettano un certo criterio, cioè un determinato valore o intervallo di valori. Per esempio CONTA.SE(C:C; >=1000) conta le celle della colonna C con valori maggiori di 1000. Il rapporto di SOMMA.SE e CONTA.SE, con gli stessi criteri, può essere impiegato per calcolare una media condizionata.

5. MEDIA(num1, num2, … numN)

   La prima funzione statistica che si impara, anche questa destinata a essere soppiantata da strumenti più avanzati. Le condizioni sono o i valori da mediare o le celle che li contengono, es: MEDIA(A2:A30). Altre funzioni statistiche che è opportuno conoscere sono la MEDIANA(), valore centrale della distribuzione, MODA(), il valore più ripetuto, MIN(), il valore minimo, MAX(), il massimo, DEV.ST(), la deviazione standard. In realtà ce ne sono molte altre e, se ti occupi di analisi statistiche, le conosci già tutte.

6. CERCA(valore; vettore; risultati)

   E’ di solito la prima funzione di ricerca che si impara ad usare e serve quando si deve cercare un valore su un singola riga o colonna di dati. Per esempio, se hai bisogno di conoscere il prezzo di un articolo, puoi usare CERCA(103456; A:A; G:G) che cerca nella colonna A il codice articolo indicato per poi restituire il prezzo del campo G.
   Non viene utilizzata per ricerche veloci o controlli dei dati, dove ce la caviamo egregiamente con i filtri delle tabelle o con la finestra di dialogo “Trova” (Ctrl+Maius+T). Invece è piuttosto utile in report o plance di controllo.

7. CERCA.VERT(valore; matrice_tabella; indice; intervallo)

   Quando dobbiamo lavorare su matrici è meglio utilizzare l’inestimabile cerca.vert, gioia e sudore per ogni esperto di Excel. Sostanzialmente la funzione cerca un valore in una matrice e restituisce il valore di un altro campo, così: CERCA.VERT(“Car836d”;B2:E700;5;FALSO), dove cerca il valore indicato nella prima colonna della matrice B2:E700 e restituisce il dato contenuto nella quinta colonna della matrice, con corrispondenza esatta.
   Primo, il dato viene ricercato sempre nel primo campo della matrice, secondo, la ricerca può essere esatta (4°parametro = Falso o 0) oppure approssimata (Vero o 1) e la funzione cerca il valore più prossimo, utile in diverse situazioni. Nella realtà il cerca.vert e il suo parente cerca.orizz vengono usati in molti ambiti diversi, come per esempio per confrontare due tabelle di dati o per unire due tabelle di dati separate.

   Approfondiamo, se hai due tabelle di dati che contengono una stessa chiave primaria, esempio il codice articolo, puoi aggiungere alla prima uno o più campi della seconda, o perfino tutti. Quando sullo stesso database non hai tutti i dati che ti servono, torna molto utile per realizzare una sola base dati su cui lavorare. Oppure nel caso tu debba verificare la tua base dati e hai a disposizione un’altra tabella più affidabile, allora puoi usare cerca.vert per eseguire una serie di controlli anche incrociati e in questi casi viene di solito utilizzata con funzioni tipo SE e VAL.ERRORE.

   

8. RICERCA(testo; stringa; inizio) o TROVA(testo; stringa; inizio)

   Due funzioni di ricerca specifiche per i testi che cercano una stringa di testo all’interno di un’altra stringa, la prima senza considerare le maiuscole, la seconda invece sì. Utili quando sei costretto a lavorare su testi, come la descrizione degli articoli, o campi note degli ordini, o peggio ancora. Per esempio con RICERCA(“Marco”; B5; 3) la funzione cercherà “Marco” nella cella B5 a partire dal terzo carattere e se la troverà restituirà la sua posizione. Di solito sono utilizzate insieme ad altre funzioni di testo, come Stringa.estrai o Sostituisci.

9. DESTRA(testo; num_caratt) e SINISTRA(testo; num_caratt)

   Forse le funzioni di testo più utili dopo quelle di ricerca di testi, servono per estrarre porzioni precise di testo da una stringa. Per esempio, SINISTRA(A3, 3) estrae i primi tre caratteri del testo della cella. Sono funzioni utilizzate per estrarre parti di codici alfanumerici noti e spesso sono utilizzati insieme alla funzione LUNGHEZZA(testo), che conta il numero dei caratteri della stringa, e CONCATENA(testo1, testo, … testoN), con cui possiamo concatenare testi diversi.

10. DATA(anno; mese; giorno)

    Una funzione che lavora sulle data, in particolare per ricostruirle. Prende tre numeri e li trasforma in una data, utile quando hai date scomposte in anno, mese e giorno o date in formato strano che Excel ha difficoltà a riconoscere. In quest’ultimo caso o utilizzi le funzioni ANNO(data), MESE(data) e GIORNO(data), o DESTRA() e SINISTRA() per estrarre le componenti della data. In realtà non dovresti mai aver bisogno di certi artifici, ma mi sono trovato molto spesso a lavorare su dati vecchi o estratti da vecchi sistemi operativi (es: AS400) o estratti male.

11. SE(test; azione se vero; azione se falso)

    Una delle funzioni più usate, amate e abusate di Excel. E’ necessaria nei controlli o per gestire certe lacune o errori, utile nei report o nelle plance di controllo, ma il consiglio è di non farti prendere la mano. Per esempio, SE(B2>F2; “Attenzione controllare il dato”;”ok”) confronta due valori e quando il primo supera il secondo lancia l’allerta; oppure SE(D2>97%;”Alta resa”;SE(D2>92%;”Resa mediocre”;”Bassa resa”)) categorizza la resa produttiva in alta, media e bassa; o SE(G2=””;””;F2*G2) permette di evitare errori nel calcolo della divisione, controllando la presenza di valori nulli.E’ importante citare due funzioni che accompagnano spesso SE, cioè E(test1; test2; testN) e O(test1; test2; testN), in quanto permettono di gestire più condizioni contemporaneamente. Se riprendiamo l’ultimo esempio, possiamo riproporlo così: SE(O(G2=””; G2=0);””;F2/G2) con cui controlliamo la presenza non solo di valori nulli, ma anche di 0.

12. VAL.ERRORE(val)

    Funzione utilissima per riconoscere e gestire errori presenti nei dati o nei calcoli. Di per se non fa altro che restituire un valore 1 o 0, Vero o Falso, a seconda della presenza di un errore di qualsiasi tipo nella cella su cui è puntata o nella funzione al suo interno. Nella pratica permette di intervenire sugli errori, di fatto eliminandoli, trasformandoli in altro. Un esempio classico è l’eliminazione degli errori del cerca.vert quando non trova corrispondenze esatte, in questo modo: SE(VAL.ERRORE(CERCA.VERT(Parametri));”Non presente”;CERCA.VERT(Parametri)), dove se la funzione è vera, quindi c’è un errore, si sostituisce con la stringa, mentre se non è vero si applica il cerca.vert.Lo stesso metodo è applicabile a qualunque funzione, ma val.errore ha altri usi: può essere utilizzato come controllo per verificare la presenza di errori in un campo della tabella in esame, anche nella sua forma base VAL.ERRORE(C3), se il valore è 1 è presente un errore. Oppure puoi usarlo per replicare un campo ripulito dagli errori, così SE(VAL.ERRORE(C3);0;C3), dove gli errori vengono trasformati in numeri 0, permettendo di essere manipolati da altre funzioni o da una pivot.

13. INDICE(matrice; riga; col) e CONFRONTA(val; matrice; corr)

    Un’alternativa a Cerca.vert() e Cerca.orizz(), in quelle situazioni dove queste funzioni non funzionano efficacemente (es: ricerca su parametri non univoci), è dato dall’uso delle funzioni Indice() e Confronta().
    Confronta() cerca un valore in una matrice, il primo se ce ne sono più di uno, o il valore minimo o massimo superiore o inferiore, e restituisce il numero di riga. Per esempio Confronta(F1; C3:C70; 0) cerca il valore esatto della cella F1 nell’intervallo C3:C70 e individuato ne indica la riga, o altrimenti da un errore.
    Indice() invece restituisce il valore della cella di una tabella/matrice individuandola con tramite riga e colonna. Per esempio Indice(A3:E70; 23; 4) mostrerà il valore della cella localizzata alla riga 23 della colonna 4 della matrice A3:E70. Facile, no?
    Insieme le due funzioni permettono di trovare e restituire il valore di una cella di una matrice/tabella partendo dal valore assegnato. In questo modo: Indice(A3:E70; Confronta(F1; C3:C70; 0);4) dove la funzione Confronta() cerca e identifica la prima riga che contiene il valore della cella F1 nella colonna C3:C70 e Indice() restituisce il valore della cella alla stessa riga, ma della colonna D (la quarta della matrice). Questa metodo di ricerca alternativo è usato di solito in report interattivi o dashboard indirizzato su tabelle di piccole dimensioni, ma ti consiglio di tenerlo in considerazione perché in certe situazione è molto potente e comodo, anche più di Cerca.vert().

Qual è il modo migliore per inserire una funzione in una cella?

Scrivendola. Sì, intendo proprio digitandola direttamente nella cella, ma questo richiede di conoscere le funzioni a disposizione. Se non conosci la funzione che vuoi usare, puoi sfruttare la finestra di dialogo delle funzioni (premi Maiusc+F3) e inserire una breve descrizione o una parte del nome. In alternativa puoi aprire la Guida (F1) o il browser alla pagina del tuo motore di ricerca preferito e cercare “Funzioni di Excel”.

Altrimenti, tutto inizia da un uguale (=) con cui si attiva la compilazione della barra delle formule, poi è la volta della prima lettera che provoca la comparsa di una finestra con l’elenco delle funzioni più probabili. L’elenco lo puoi scorrere con i tasti Su e Giù, a fianco vedrai una breve spiegazione della funzione, e puoi scegliere la funzione con Tab per passare all’inserimento del primo dei parametri.

A questo punto, o comunque dopo aver scritto la funzione, un ulteriore aiuto nella compilazione lo possiamo ottenere premendo: Ctrl+A, per visualizzare la finestra di guida della funzione in questione, o Ctrl+Maius+A, per scrivere le parentesi e gli argomenti.

Un ultimo suggerimento è quello di imparare ad usare la sequenza Ctrl+Maiusc+( con cui puoi passare dalla visualizzazione dei valori a quella delle formule e viceversa; le formule compariranno nelle celle e non solo nella barra delle formule. E’ più utile in fase di revisione dei fogli di lavoro e in altre situazioni.

Le funzioni che è utile conoscere

Oltre alle funzioni fondamentali che abbiamo visto, ci sono decine di altre funzioni “secondarie” o specifiche che vale la pena conoscere. Questo è un estratto di quelle più usate:

1. GIORNI(data_fine, data_inizio) e GIORNI.LAVORATIVI.TOT (data_iniziale; data_finale; [vacanze])

Entrambe le funzioni calcolano il numero di giorni tra due date, Giorni() come differenza diretta tanto da essere equivalente alla semplice sottrazione tra i due valori (es: data2–data1), mentre Giorni.lavorativi.tot() è più utile in quanto esclude weekend, festività e anche eventuali vacanze. Queste devono essere comunicate alla funzione, di solito creando una lista a parte di giorni per poi indirizzare la funzione alla lista. Per esempio Giorni.lavorativi.tot(A3;B3;G1:G25) calcola i giorni lavorativi tra due date escludendo anche le vacanze elencate in G1:G25.

Queste funzioni non sono usate spesso, ma sono utili quando dobbiamo considerare periodi diversi da anni e mesi e abbiamo bisogno conoscere il numero di giorni effettivi. Un classico esempio è quello degli approfondimenti degli aspetti produttivi: quanti giorni ha lavorato effettivamente la tal linea produttiva? qual è il costo giornaliero della manodopera diretta? Ma sono funzioni utili ogni volta che abbiamo bisogno di sapere il numero di giorni effettivi o lavorativi.

2. GIORNO.SETTIMANA(num_seriale; [tipo_restituito]) e NUM.SETTIMANA(numero_seriale; [tipo_restituito])

Altre due funzioni di data che servono a lavorare su un periodo di tempo inconsueto, ma non troppo, la settimana. Num.settimana(data) determina il numero di settimana nell’anno, mentre giorno.settimana(data) ti permette di conoscere il giorno della settimana. In entrambi i casi le funzioni ti permettono di calcolare la settimana a partire da qualunque giorno, per usi veramente specifici.

Anche queste funzioni non sono usate spesso, ma sono utili quando si deve lavorare su periodi di tempo diversi dai soliti mesi e anni. Per esempio se devi seguire o visualizzare l’andamento settimanale di un parametro (es: rese produttive, ecc), oppure quando devi fare analisi che tengano in considerazione l’effetto “settimana”, come la valutazione dei volumi spediti nell’arco di una settimana, o le assenze per malattia, o l’andamento della qualità per produzioni con fasi non comprimibili.

Entrambe le funzioni sono spesso usate nei campi calcolati e controlli dei fogli dati.

3. GRANDE(matrice; k) e PICCOLO(matrice; k)

Sono una coppia di funzioni statistiche molto utili, data una matrice o una lista di valori le funzioni permettono di individuare velocemente l’ennesimo numero più grande o piccolo. Per esempio GRANDE(C2:C3000;3) permette di individuare il terzo numero per grandezza della lista C2:C3000. Facile, no?

Ti evitano di prendere il tuo insieme di numeri, ordinarlo e poi individuare l’ennesimo valore. Non sono utilizzati molto nei fogli dati, ma in report e analisi per individuare/visualizzare certi valori.

4. VAL.NUMERO(rif), VAL.FORMULA(rif), VAL.VUOTO(rif) e VAL.ERRORE(rif)

Per quanto poco note e poco usate, le funzioni informative VAL permettono di fare controlli specifici, esaminano il valore specificato tra parentesi o presente nella cella a cui sono indirizzati e restituiscono VERO o FALSO a seconda dell’esito. Per esempio Val.Errore() segnalerà con Vero se il valore nella cella a cui sono indirizzati è un errore, mentre Val.Formula() farà lo stesso in presenza di una formula.

Naturalmente queste funzioni di solito non si usano da sole. Anche quando usate solo per segnalare lo stato/informazione di una cella, sono di solito usate insieme a funzioni logiche, quasi sempre con SE(), per esempio: =SE(VAL.VUOTO(B3);”ATTENZIONE CELLA VUOTA”;”””). Ma di solito queste funzioni sono integrate in formule più o meno complesse per ottenere informazioni su un valore prima di eseguire un calcolo o un’altra azione con esso. Per esempio allo scopo di prevenire errori: =SE( VAL.ERRORE(C5); “Si è verificato un errore.”; A5*2). Questa formula verifica se in C5 è presente una condizione di errore, in caso affermativo, la funzione SE restituirà il messaggio “Si è verificato un errore”, in caso negativo, la funzione SE eseguirà il calcolo A5*2.

Le funzioni proposte sono quelle che uso di più, di solito proprio per controllare dati poco affidabili o ottenuti da sorgenti poco affidabili. Le uso in campo calcolati aggiunti nella pagina “dati” o a fianco di tabelle di elaborazione molto grandi per verificare la presenza di errori. In teoria non dovresti mai usarle, ma la vita riserva spesso soprese e in quei momenti si possono rivelare molto utili.

5. SE.ERRORE(valore; se_errore)

Questa funzione logica intercetta e gestisce direttamente la presenza di errori in una formula. Nel caso la formula generi un errore, la funzione lo segnale con una stringa di avvertimento, in caso contrario esegue il calcolo.

Per esempio =SE.ERRORE(A2*A3; “Errore nel calcolo”) verifica se il calcolo A2*A3 genera un errore e in caso affermativo non lo esegue, ma avverte della sua presenza. Di solito la uso in campi calcolati per intercettare divisioni per 0 o altri errori simili, di solito in tabelle o report più che nei fogli dati.

6. ANNULLA.SPAZI(testo)

Sempre nell’ottica del controllo dei fogli dati, questa funzione di testo rimuove tutti gli spazi dal testo eccetto i singoli spazi tra le parole. Utile in caso controllo o ricerca di stringhe inserite manualmente e malamente, oppure nel caso di estrazioni da gestionali o database vecchi, con campi di testo compilati male o integralmente, o in caso di esportazioni sbagliate di campi di testo. In questi casi spesso ci si trova ad avere a che fare con spazi aggiunti prima e dopo e con questa funzione si evitano inutili sprechi di tempo.

7. IDENTICO(testo1; testo2)

Identico() confronta due stringhe di testo e se le stringhe sono identiche restituisce Vero, altrimenti Falso. La funzione rileva le maiuscole, ma ignora le differenze di formattazione. La usa raramente per eseguire controlli sui campi di testo nei fogli dati per verificare la correttezza delle stringhe, quando ho il sospetto di errori, come in presenza di valori apparentemente uguali nelle liste dei filtri automatici. Di solito viene usata insieme ad altre funzioni di testo, come Destra(), Sinistra() e Lunghezza(), e funzioni logiche per gestire le azioni o gli allarmi.

Per esempio =SE(IDENTICO(SINISTRA(D3; 5); SINISTRA(D4; 5)); “OK”; “Errore di digitazione”), controlla che i primi 5 caratteri di due stringhe corrispondano, o in caso alternativo lancia un allarme.

8. CONTA.VUOTE(intervallo), CONTA.VALORI(val1; [val2]; …) e CONTA.NUMERI(valore1; [valore2]; …)

Queste funzioni statistiche contano le celle dell’intervallo a cui puntano che hanno precise caratteristiche: Conta.Vuote() conta le celle vuote, Conta.Valori() conta le celle non vuote e Conta.Numeri() conta le celle che contengono numeri. A cosa servono? Di solito per eseguire controlli su pagine dati o tabelle di dati elaborati per verificarne l’affidabilità, errori, problemi di formato o la completezza.

Per esempio =CONTA.VUOTE(B2:D130) verifica la presenza di celle vuote nell’intervallo e le conteggia, rilevando in questo modo la presenza di “anomalie” nei dati e indicando quanti.

9. CERCA.ORIZZ(valore; matrice_tabella; indice; [intervallo])

Nel post precedente avevamo visto quanto potente e utile è la funzione CERCA.VERT, funzione che cerca un valore nel primo campo di una matrice e restituisce il valore di un altro campo. Cerca.vert() ha un sosia che lavora in orizzontale e non in verticale, cioè invece di lavorare su tabelle standard dove i campi sono colonne e i record le righe, lavora al contrario.

Si chiama Cerca.orizz() ed è usato raramente in quanto le tabelle dei dati sono di solito standard come appena detto. Quando viene impiegato è di solito su tabelle di elaborazione o report che per leggibilità o altri motivi mostrano i dati in orizzontale.

La funzione lavora in modo speculare a Cerca.vert(). Per esempio, CERCA.ORIZZ(“Bk836004”;B2:S14;3;FALSO), dove cerca il valore indicato nella prima riga della matrice e restituisce il dato contenuto nella terza riga della matrice, con corrispondenza esatta.

Come in Cerca.vert() la chiave primaria viene ricercata sempre nel primo campo, quindi nella prima riga della matrice, e la ricerca può essere esatta (4° parametro = Falso o 0) oppure approssimata (Vero o 1) e la funzione cerca il valore più prossimo, utile in diverse situazioni.

Analogamente a Cerca.vert(), Cerca.orizz() può essere usato in molti ambiti diversi, come per esempio per confrontare due tabelle di dati o per unire due tabelle di dati separate.

Le funzioni Excel di ricerca

Cosa sono le funzioni di Excel di ricerca? Quali sono? A cosa servono? Come si usano? Perché è opportuno conoscerle?

Le funzioni di ricerca sono strumenti potenti di excel con cui possiamo estrarre dati specifici per visualizzarli in tabelle o elaborarli. Sono usate anche per inserire campi interi o per unire tabelle, con conseguenze a livello di prestazioni e risorse impiegate. Le funzioni di ricerca rimangono probabilmente le funzioni più utilizzate in report, dashboard e tabelle di formule, strumenti che devi conoscere bene se lavori con Excel.

Quali sono le funzioni di ricerca che dobbiamo conoscere?

1. Cerca.Vert, che permette di individuare un valore in una matrice di celle;
2. Cerca.Orizz, come sopra, ma per lavorare su tabelle orizzontali;
3. Indice+Confronta, come Cerca.vert, ma più efficiente;
4. Cerca, la versione limitata di Cerca.Vert, ormai in disuso;
5. Ricerca, per trovare stringhe all’interno di testi;
6. Trova, come sopra, ma con la possibilità di distinguere maiuscole e minuscole.

A cosa servono le funzioni Excel di ricerca?

Sono strumenti che ci permettono di individuare valori in una tabella o matrice, oppure stringhe all’interno di un testo, per poi visualizzarli o elaborarli in una formula. Di conseguenza permettono per esempio di:

1. individuare un singolo valore specifico all’interno di una tabella,
2. individuare il valore più prossimo a quello ricercato,
3. aggiungere a una tabella un campo di un’altra tabella,
4. unire due tabelle,
5. analizzare e modificare un testo.

Queste funzioni sono notevoli strumenti di ricerca, che una volta impostate continuano a “lavorare” in modo dinamico, a meno di modifiche “strutturali” o nominative della tabella a cui sono collegate.

Vediamo in modo più approfondito le singole funzioni e il loro utilizzo.

CERCA

Questa funzione ha una doppia forma:

1. una forma vettoriale: CERCA(valore; vettore; risultati)
   che ricerca e restituisce un valore da una singola riga o colonna di dati.
2. una forma “matrice”: CERCA(valore; matrice)
   che ricerca un valore preciso nella prima riga o colonna di una matrice e restituisce il valore corrispondente presente nell’ultima riga o colonna della stessa matrice.
   Questa forma della funzione è in disuso dall’introduzione delle funzioni Cerca.Vert() e Cerca.Orizz() più efficienti ed efficaci.

La forma vettoriale è quella utilizzata e permette di cercare un valore numerico specifico nella riga o colonna indicata (vettore) per poi restituirlo. Oppure restituire il valore corrispondente di un’altra riga o colonna indicata (risultato).

Se la funzione non riesce a trovare un valore preciso cercherà il primo valore minore. Altrimenti se non c’è un valore minore, perché il valore cercato è minore del valore più basso nella riga o colonna indicata, la funzione restituirà un errore (#N/D). Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.

Per esempio, se cerchi quale prodotto ha un prezzo corrispondente o più basso, puoi usare =CERCA(Prezzo; G2:G1000; B2:B1000); la funzione cercherà il valore inserito nella cella nominata “Prezzo” nell’intervallo “G2:G1000” e restituirà il valore corrispondente nell’intervallo “B2:B1000”.

È piuttosto utile in tabelle, report e maschere, ma chi impara a usare Cerca.Vert() e Cerca.Orizz() inevitabilmente abbandonerà questa funzione.

Le funzioni di ricerca di Excel

Nel post “Le funzioni di ricerca” abbiamo visto le funzioni di ricerca di Excel, strumenti che ci permettono di individuare valori in una tabella o matrice di dati, per poi visualizzarli in un’altra posizione, in un’altra tabella o elaborarli in una formula.

Le formule di ricerca permettono per esempio di:

1. individuare un singolo valore specifico all’interno di una tabella,
2. individuare il valore più prossimo a quello ricercato,
3. aggiungere a una tabella un campo di un’altra tabella,
4. unire due tabelle.

Queste funzioni sono notevoli strumenti che una volta impostate continuano a “lavorare” in modo dinamico, a meno di modifiche “strutturali” o nominative della tabella a cui sono collegate.

Tra queste funzioni, la più nota e utilizzata è Cerca Verticale, un notevole strumento non così complesso come appare, che offre più possibilità di quello che si pensa, ma possiede anche diversi limiti e lacune. Questa guida vuole mostrarti tutto questo che devi sapere su questo strumento per ottenerne il meglio per il tuo lavoro.

La sintassi di Cerca Verticale

La sintassi è: CERCA.VERT(valore; matrice_tabella; indice; intervallo)

I parametri della funzione sono:

1. valore

È il valore da cercare e può essere di qualsiasi tipo (numero, stringa, ecc). Può essere scritto direttamente nella formula, ma di solito è ottenuto da un riferimento a una cella diversa, di solito un campo diverso dello stesso record che contiene il valore, tipicamente una chiave univoca come il codice cliente o il numero fattura.

Esempio =CERCA.VERT(“Ac8473”; matrice_tabella; indice; intervallo) cerca la stringa indicata, mentre =CERCA.VERT(B27; matrice_tabella; indice; intervallo) cerca il valore contenuto nella cella B27.

2. matrice_tabella

È una tabella di due o più colonne o campi di dati che nella prima colonna a sinistra contiene il valore di riferimento. Per esempio, =CERCA.VERT(B27; H2:N107; indice; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107.

3. indice

È il numero della colonna in matrice_tabella da cui la funzione restituisce il valore cercato. La colonna si conta da sinistra a destra da 1 all’indice. Per esempio, =CERCA.VERT(B27; H2:N107; 3; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.

4. intervallo

Indica se vuoi fare una ricerca a corrispondenza esatta (falso o 0) o di una corrispondenza approssimativa (Vero o 1 o omesso). Per esempio =CERCA.VERT(B27; H2:N107; 3; 0) cerca il valore esatto della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.

Il cerca verticale in sintesi

In sintesi la funzione Cerca.Vert() permette di cercare nella prima colonna di una tabella (matrice_tabella) il valore di riferimento per individuare il record in cui la funzione cerca e restituisce il valore presente nella ennesima (indice) colonna verso destra.

Il valore di riferimento è indicato come riferimento a una cella (es: A7) e la matrice come intervallo di celle (es: F2:R102) puntata su un’intera tabella di dati o una porzione di essa.

L’indice deve essere fornito come numero della colonna della matrice da sinistra a destra contando a partire dalla prima colonna, quindi il valore da ricercare/estrarre dalla tabella è sempre a destra della prima colonna in cui viene cercato il valore di riferimento.

Cerca verticale può cercare un valore preciso (intervallo = Falso o 0), oppure può cercare il valore più prossimo (intervallo = Vero o 1). Nel primo caso se la funzione non trova il valore restituire un errore (#N/D!), mentre nel secondo caso restituisce il primo valore inferiore se disponibile, altrimenti restituisce sempre un errore (#N/D!).

Esempio di cerca verticale a corrispondenza esatta

Sostanzialmente la funzione cerca un valore preciso nel primo campo della tabella indicata e restituisce il valore di un altro campo del record, per esempio: =CERCA.VERT(C2;TabellaCosti;3;Falso) cerca il valore esatto della cella C2 della pagina nella prima colonna dell’intervallo nominato come “TabellaCosti” e se lo trova restituisce il valore della terza colonna della matrice.

Altrimenti restituisce un errore che deve essere gestito. Gli errori devono essere sempre gestiti, soprattutto se viene usato in formule di elaborazione, altrimenti l’errore si propagherebbe ai risultati.

In caso di un valore non univoco, quindi di valori uguali presenti più volte nella prima colonna, verrà restituito il primo valore individuato dall’alto verso in basso, a meno che non siano consecutivi.

Esempio di Cerca verticale con corrispondenza approssimata

Come già detto Cerca.vert() può anche restituire un valore prossimo a quello ricercato, impostando il parametro intervallo a Vero oppure 1. Questo restituirà il primo valore inferiore, se presente, altrimenti restituisce un errore (#N/D!). La ricerca di valori prossimi è meno utilizzata rispetto ai valori precisi, è usata in situazioni particolari come in presenza di categorizzazioni, scale di valori, oppure per analisi esplorative o confronti.

Per esempio con =CERCA.VERT(B27; H2:N107; 3; 1) cerca il valore più prossimo a quello della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J, sulla stessa riga che contiene il valore di riferimento in H.

I riferimenti del cerca verticale

Di solito il cerca verticale viene utilizzato con riferimenti per il valore e la matrice. Il valore è tipicamente contenuto nella tabella in cui stiamo inserendo la formula con cerca.vert(), in un altro campo dello stesso record. Mentre di solito la tabella in cui cercare il valore di solito punta a una tabella in una pagina diversa.

Come si scrive un riferimento che punta a un’altra pagina?

Per impostare il nome della pagina nel riferimento è sufficiente scriverlo prima del riferimento e inserire un punto esclamativo (!) tra questo e il riferimento. Per esempio =CERCA.VERT(B27; Dati!H2:N107; 3; 0) cerca il valore esatto della cella B27 nella pagina Dati, nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J, sulla stessa riga che contiene il valore di riferimento in H.

In alternativa possiamo usare il classico sistema di puntamento sulla selezione, cioè mentre scriviamo la formula selezioniamo la pagina con la tabella e poi l’intervallo di celle della stessa.

Come si scrive un riferimento a un’altra pagina di un altro foglio?

Se vogliamo impostare il riferimento perfino a un’altra pagina di un altro foglio Excel, dobbiamo inserire il nome del foglio Excel tra parentesi quadra prima del nome della pagina.

Per esempio =CERCA.VERT(B27; [Fatturato.xlsx]Dati!H2:N107; 3; 0) cerca il valore esatto della cella B27 nella pagina Dati del foglio “Fatturato.xlsx”, nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.

I riferimenti se vogliamo copiare la formula

Infine è importante sottolineare che se vogliamo propagare la formula con cerca vert in una colonna di una tabella, cioè dall’alto verso il basso, è importante fissare il riferimento premendo F4 durante la digitazione per bloccare righe e colonne. Per esempio =CERCA.VERT(B27;$H$2:$N$107; 3; 0). In caso contrario quando copieremo la formula Excel adatterà i riferimenti modificando la matrice con conseguenti errori o alterazioni dei risultati.

In modo analogo se abbiamo bisogno di copiare la formula in altri campi o in celle a fianco, da sinistra a destra, allora è opportuno fissare il riferimento del parametro “valore”, premendo F4 durante la digitazione tante volte fino a fissare la colonna, quindi la lettera. Per esempio =CERCA.VERT($B27;$H$2:$N$107; 3; 0). In caso contrario quando copieremo la formula in un’altra colonna Excel adatterà i riferimenti spostando il puntamento a un’altra colonna e non più a quella con i valori da cercare, offrendo risultati errati.

Usare i nomi con il cerca verticale

In alternativa è consigliato usare i nomi di excel per impostare un nome che si riferisce all’intervallo di celle della tabella, o alla porzione che ci interessa. Per esempio =CERCA.VERT(C2;TabellaCosti;3;Falso), usa un nome “TabellaCosti” precedentemente impostato che si riferisce in modo assoluto all’intervallo di celle della matrice.

I nomi sono comodi, facili da ricordare e vengono proposti dagli strumenti di supporto durante la digitazione delle formule in modo intuitivo. E in effetti lo consiglio. Se vuoi approfondire l’argomento dei nomi di Excel, ti invito a leggere la guida relativa che trovi in questa pagina.

Usare i caratteri jolly nel cerca.vert

Anche cerca verticale, come molte altre formule possono usare i caratteri jolly, caratteri speciali che si riferiscono in modo generico a uno o più caratteri non specificati rendendo più flessibili i parametri di ricerca.

Cioè possiamo sostituire la stringa che stiamo cercando o parte di essa, con un carattere che indica a Excel di cercare qualunque carattere o sequenza di caratteri.

I caratteri jolly sono:

1. punto interrogativo (?), che sostituisce qualunque singolo carattere,
2. l’asterisco (*), che sostituisce qualunque sequenza di caratteri.

Qual è lo scopo principale dei caratteri jolly?

I caratteri jolly servono per cercare stringhe e valori alfanumerici di cui non ricordiamo o conosciamo o non vogliamo scrivere l’intera stringa.

Per esempio:

=CERCA.VERT(“ACR*”;H2:N107; 3; 0) cerca il primo valore che inizia con “ACR”;

=CERCA.VERT(“*NZ5?”;H2:N107; 3; 0) cerca il primo valore che contiene “NZ5” nel penultimo, terzultimo e quartultimo carattere;

=CERCA.VERT(“CN??????”;H2:N107; 3; 0) cerca il primo valore che inizia con “CN” ed è lungo 8 caratteri.

Nota sull’uso del cerca verticale con corrispondenza approssimata

Come abbiamo visto, se il quarto parametro del cerca verticale è impostato su “vero”, la formula cerca una corrispondenza approssimativa. O meglio, prima cerca il valore esatto e se lo trova restituisce quello, se invece non la trova cerca e restituisce il primo valore più grande inferiore al valore cercato.

Questo comporta che se i valori non sono ordinati preventivamente non è detto che la funzione cerca verticale restituisce il valore effettivamente più prossimo al valore vero. Ed è anche possibile che restituisca un errore se si cerca il valore più piccolo presente nel campo.

I limiti del Cerca Verticale

Come avrai già notato Cerca.Vert() ha diversi limiti:

1. cerca il valore sempre nella prima colonna della matrice,
2. è probabile che restituisca errori, che devono essere gestiti;
3. ricerca un valore singolo solo nella prima colonna della matrice, restituendo il primo che trova;
4. l’indicazione della colonna del risultato è un numero intero, non un riferimento a un nome di campo, quindi in caso di modifiche della tabella, per esempio aggiungendo campi nella tabella a sinistra del campo indicato dall’indice, la formula necessiterà di essere corretta.

In realtà questi limiti possono essere gestiti o perfino bypassati. Vediamo come.

Gestire il campo con il valore di riferimento

Il campo con il valore di riferimento deve essere sempre a sinistra del campo dei valori da restituire e dove non è già così è necessario intervenire sulla struttura della tabella. Per esempio selezionando l’intera colonna della tabella, tagliandola (Ctrl+X) e spostando il campo a inizio tabella o a sinistra del campo con i valori da restituire.

Naturalmente queste manipolazioni della tabella possono comportare problemi, in presenza di riferimenti e formule, o con strumenti non dinamici collegati. Sconsiglio di inserire campi “copia” della colonna di riferimento a inizio tabella a sinistra per il rischio di generare formule ricorsive.

Come gestire gli errori di cerca verticale

Una funzione cerca verticale è sempre accompagnata da una funzione Se.Errore(), oppure dalla coppia Se() e Val.Errore() proprio allo scopo di intercettare il potenziale errore che la funzione cerca verticale può restituire.

Per esempio in questo modo: =SE.ERRORE( CERCA.VERT( Ricerca;A2:S100;2;FALSO); “Non trovo il valore”), oppure =SE(VAL.ERRORE( CERCA.VERT(Parametri)); ”Non presente”;CERCA.VERT(Parametri)).

Come vedi se Cerca.Vert() non trova il valore e quindi restituisce un errore, questo viene sostituito con la stringa. Attenzione però, se il valore cercato è oggetto di elaborazione ed è numerico, la presenza di una stringa può generare errori. In tal caso è possibile sostituire la stringa con 0, 1 o valori nulli (“”) a seconda dei valori del campo e delle elaborazioni.

Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.

Cercare valori multipli con cerca vert

Come abbiamo visto la funzione cerca verticale ricerca un solo valore alla volta, ma questo limite può essere scavalcato a certe condizioni. Per esempio è possibile ricercare valori diversi di campi diversi concatenandoli in un unico campo iniziale. Oppure possiamo cercare valori alternativi usando altre funzioni come SE(), E() e O() in modo gerarchico.

In pratica se vogliamo cercare due valori in due campi diversi, dobbiamo procedere in questo modo:

1. aggiungere una colonna alla tabella a sinistra di tutte le altre o del campo con il valore che vogliamo sia restituito,
2. impostare nella nuova colonna una formula di concatenazione, (es: =C2 & “ “ & D2) per creare un’unica chiave primaria “multipla”,
3. impostare il cerca verticale in modo che il valore abbiamo la stessa struttura di concatenazione, direttamente nella formula, oppure nella cella di riferimento.

Rendere dinamico il cerca verticale

Per rendere dinamiche le formule con cerca verticale, o meglio per permettere di poter aggiungere campi alla tabella senza danneggiare le formule, è necessario sostituire l’indice con una formula in grado di restituire sempre il numero di colonna corretto.

Naturalmente mi sto riferendo all’uso della funzione Confronta(), in grado di restituire il numero della colonna usando il nome del campo, in questo modo:

=CONFRONTA(“ARTICOLO”; Articoli[#Intestazioni]; 0).

In questo caso confronta() cerca il nome del campo “ARTICOLO” nell’intervallo delle intestazioni della tabella excel di nome “Articoli” e restituisce la sua posizione. Naturalmente la formula deve essere inserita nel cerca verticale al posto dell’indice.

Per esempio CERCA.VERT( F12; Dati!A2:S100; CONFRONTA(“ARTICOLO”; Articoli[#Intestazioni]; 0) ;FALSO) ).

Questa soluzione permette di aggiungere o rimuovere campi dalla tabella, ma però ha come conseguenza il limite di non modificare il nome dei campi indicati. Se modifichiamo il nome del campo la formula verrà danneggiata e non funzionerà più. Quindi possiamo rendere più flessibile il Cerca.Vert, ma limitando le modifiche alle intestazioni dei campi.

Per approfondire la nomenclatura delle tabelle Excel ti invito a leggere la relativa guida che puoi trovare in questa pagina.

Unire tabelle con il cerca verticale

Il cerca verticale viene molto spesso usato per unire tabelle, cioè per riportare in una tabella i valori di un campo di un’altra tabella che contiene una chiave primaria e secondaria che correli le due basi dati.

Per esempio se vogliamo visualizzare nella tabella del fatturato, campi con informazioni relative ai prodotti venduti, per esempio costi, tempi di produzione o consegna, eccetera, allora il cerca verticale può farlo in modo relativamente semplice.

Possiamo farlo per un campo, o due, o in teoria anche tutti i campi, arrivando a unire due intere tabelle, ma l’uso esteso di formule con cerca verticale per molti campi e molte migliaia di record può appesantire notevolmente i nostri fogli riducendone le prestazioni e l’efficienza.

Parleremo dell’unione di tabelle in un post dedicato e vedremo anche le alternative più efficace al cerca verticale. Perché ci sono alternative migliori a questa funzione che spesso viene usata in modo eccessivo.

Note sul cerca vert

1. Nelle formule Cerca.Vert(), i valori non sono case sensitive, cioè possono essere sia in maiuscolo che in minuscolo, perché considerati equivalenti.
2. Se l’indice è inferiore a 1 o negativo la formula Cerca.Vert() restituirà un errore (#VALORE!). Invece se l’indice indicato è maggiore del numero di colonne presenti nella tabella indicata, la formula restituirà un errore (#RIF!).

E il cerca orizzontale?

CERCA.ORIZZ(valore; matrice_tabella; indice; intervallo)

È la funzione analoga a Cerca.vert(), ma permette di lavorare in orizzontale. Funziona nello stesso modo, ma ruotato di novanta gradi. In pratica Cerca.orizz() è usato raramente, in quanto le tabelle dei dati in Excel sono prevalentemente standard, cioè verticali. Quando lo usiamo? Viene impiegato di solito su tabelle di elaborazione o tabelle dei risultati, tabelle di formule costruite in orizzontale.

Per il resto, la funzione lavora come Cerca.vert(), ma in modo trasposto. Per esempio, CERCA.ORIZZ(“Bk836004”;B2:S14;3;FALSO) cerca il valore indicato nella prima riga della matrice e restituisce il dato contenuto nella terza riga della matrice, con corrispondenza esatta.

E la nuova funziona Cerca.X?

È l’evoluzione di cerca verticale e cerca orizzontale, resa disponibile in Office 365 per gli iscritti a Office insider a partire da agosto 2019. Questa nuova formula è molto più evoluta e semplice da usare di cerca verticale. Non solo non c’è il limite dell’orientamento, ma gestire direttamente il possibile errore per assenza di valore, può restituire più valori di più campi, può offrire le stesse possibilità di delle funzioni Indice e Confronta e altro ancora.

Parleremo di questa funzione e delle nuove funzioni di Excel pubblicate in 365 e del loro utilizzo in post dedicati. Qui puoi vedere un’anticipazione.

INDICE e CONFRONTA

INDICE (matrice; riga; col)

CONFRONTA (val; matrice; corr)

Usate insieme su una tabella verticale: INDICE (matrice; CONFRONTA (val; matrice; corr); col)

L’uso delle funzioni Indice() e Confronta() è un’alternativa più efficiente a Cerca.vert() e Cerca.orizz(), soprattutto in quelle situazioni dove queste non funzionano efficacemente (es: colonna di riferimento a destra, ricerca su parametri non univoci, ecc).

Indice() ha una doppia forma: matrice e riferimento; la prima restituisce un valore di una cella di una matrice, la seconda restituisce il riferimento. In questo caso ci interessa la forma matrice, che restituisce il valore della cella di una tabella individuandola tramite l’indicazione di riga e colonna. Per esempio Indice( A3:E70; 23; 4) mostrerà il valore della cella localizzata alla riga 23 della colonna 4 della matrice A3:E70. Facile, no?

Confronta() cerca un valore in una matrice, il primo se ce ne sono più di uno, oppure il valore più grande minore, o il più piccolo maggiore del valore cercato, e ne restituisce la posizione. Per esempio Confronta(F1; C3:C70; 0) cerca il valore esatto della cella F1 nell’intervallo C3:C70 e se individuato ne indica la posizione.

Altrimenti restituisce un errore che dovremo gestire, in modo analogo a quanto abbiamo visto per Cerca.Vert().

Insieme le due funzioni Indice e Confronta permettono di trovare e restituire il valore di una cella di una matrice/tabella partendo dal valore assegnato. In questo modo: Indice(A3:E70; Confronta(F1; C3:C70; 0); 4), dove la funzione Confronta() cerca e identifica la prima cella della colonna indicata “C3:C70”, che contiene il valore della cella F1, e di conseguenza identifica la riga relativa, mentre Indice() restituisce il valore della cella alla stessa riga, ma della colonna D, la quarta della matrice.

Naturalmente quando le usiamo insieme è necessario che gli intervalli indicati nei parametri delle due funzioni siano coerenti, cioè siano della stessa grandezza (es: A3:E70 e C3:C70), o potremmo generare errori.

Perché usare Indice e Confronta

Questo metodo di ricerca è usato di solito in alternativa a Cerca.Vert per diversi motivi che lo rendono preferibile. Per esempio:

1. perché permette di cercare i valori non solo nella prima colonna della matrice,
2. permette di gestire con più precisione l’individuazione del valore,
3. indice e confronta realizzano formule dinamiche, in grado di funzionare correttamente anche dopo modifiche alla tabella dei dati come aggiunte e rimozioni di righe e colonne,
4. permette di gestire valori di testo lunghi (con più di 255 caratteri),
5. richiede meno risorse (memoria, dimensioni del foglio, ecc), caratteristica importante quando usiamo le formule di ricerca in tabelle molto grandi con migliaia di record.

Quando parlo di formule dinamiche, mi riferisco all’uso della funzione Confronta anche per l’individuazione della colonna e non solo della riga della matrice su cui cerchiamo i valori. Per esempio =INDICE( DatiProduzione[#Tutti]; CONFRONTA(A5; DatiProduzione[[#Tutti];[ARTCOD]]; 0); CONFRONTA(“Produzione”; DatiProduzione[#Intestazioni]; 0)), cerca il valore presente in A5 nella tabella Excel con nome “DatiProduzione” nel campo “Artcod” e restituisce il valore corrispondente nella colonna con titolo “Produzione”. Dietro la relativa complessità, questa formula continuerà a lavorare nonostante le modifiche compiute sulla tabella, a meno di modificare i titoli dei campi indicati (Artcod e Produzione).

Vale la pena sottolineare che Confronta, oltre a cercare il primo valore preciso indicato (terzo parametro = 0), qualunque sia l’ordine dei record della matrice, permette di cercare valori approssimati.

Se corr viene impostato a 1, oppure viene omesso il parametro, la funzione troverà il valore minore più prossimo o uguale a quello ricercato, ma per questo è necessario che i valori nella matrice siano disposti in ordine crescente.

Se invece corr viene impostato a -1, la funzione cercherà il valore maggiore più prossimo o uguale a quello cercato, e per questo è necessario che i valori nella matrice siano ordinati in modo decrescente.

In pratica queste opzioni vengono utilizzate in modo limitato, per situazioni particolari, analoghe a quelle indicate per Cerca.Vert.

Infine è necessario sottolineare che anche Indice e Confronta, come le altre funzioni di ricerca, richiedono la gestione degli errori. In fondo se una funzione di ricerca non trova il valore indicato, non può restituirlo. Quindi anche queste funzioni vengono spesso accompagnate da funzioni per la gestione degli errori come Se.Errore o Se e Val.Errore.

RICERCA

RICERCA (testo; stringa; inizio)

È la prima funzione di ricerca specifica per i testi, che non fa altro che cercare una stringa all’interno di un testo per restituire un numero intero che corrisponde alla posizione iniziale della stringa individuata nel testo. Per esempio, se cerchiamo “pos” in “Composizione del prodotto” restituirà il valore 4.

Se la stringa non viene trovata viene restituito un errore (#Valore). Inoltre permette di indicare facoltativamente da quale carattere del testo la funzione deve iniziare a cercare (parametro “inizio”). Per esempio con: RICERCA(“yh”; B5; 3) la funzione cercherà “yh” nella cella B5 a partire dal terzo carattere e se la troverà restituirà la sua posizione. Altrimenti un errore.

Perché cercare stringhe in questo modo?

Per diversi motivi, dalla ricerca di parole chiave in un testo, all’estrazione di una porzione identificabile del testo stesso, alla sostituzione o inserimento di stringhe all’interno di testi, codici, chiavi o altro. Questo può essere fatto anche con funzioni come Rimpiazza(), Stringa.Estrai() o Sostituisci().

È importante sottolineare che:

1. Ricerca() non rileva la differenza tra maiuscole e minuscole,
2. mentre è possibile usare i caratteri jolly, per esempio il punto interrogativo (?), che rappresenta un singolo carattere, e l’asterisco (*), che invece rappresenta una sequenza qualsiasi di caratteri,
3. se il valore di inizio è minore di 1 o maggiore della lunghezza del testo, viene restituito un errore (#Value!).

Nonostante Excel non sia un word processor, è dotato di strumenti che possono manipolare finemente testi inseriti in celle o tabelle. Questo può essere utile per costruire e manipolare chiavi alfanumeriche codificate, come per individuare ed estrarre stringhe rilevanti, perfino correggere e costruire testi e note.

TROVA

TROVA (testo; stringa; inizio)

È la funzione gemella della precedente, funziona quasi allo stesso modo, con alcune differenze che possono diventare significative in certe situazioni. In particolare:

1. distingue tra lettere maiuscole e minuscole,
2. non supporta i caratteri jolly,
3. se la stringa è vuota (“”), restituirà il primo carattere della stringa o il numero di inizio.

Trova è utile quando dobbiamo lavorare su maiuscole, ma non permette di usare caratteri jolly con cui rendere più efficiente la ricerca.

Quando si usano le funzioni Excel di ricerca

Si usano per individuare ed estrarre dati da un database allo scopo di visualizzarli (es: stato dell’ordine di fornitura), oppure per elaborarli per ottenere un preciso risultato (es: tempi di produzione).

Queste funzioni sono le protagoniste di tabelle di formule in funzionalità di elaborazione o in report o dashboard, quindi usate in modo preciso e specifico, spesso insieme a funzioni per l’elaborazione numerica (es: somma.più.se) o a funzioni logiche.

Le funzioni di ricerca sono anche usate in tabelle excel o tabelle di formule, per visualizzare o per elaborare dati estratti da tabelle diverse, inserendo o generando interi campi di informazioni. Per esempio, abbiamo bisogno di visualizzare i dati del fornitore insieme alle giacenze del magazzino, o gli ordini di produzione insieme a quelli del cliente, o le ore lavorate nei costi aziendali e attingiamo a tabelle diverse per visualizzare informazioni che non sono già presenti nella tabella, come il fornitore, la data di consegna, i centri di costo, eccetera.

Questo può essere comodo, in effetti permette in modo veloce di inserire nella tabella interi campi di informazioni collegati a tabelle terze che si aggiornano in modo dinamico insieme alla fonte. D’altra parte questo tipo di uso, se applicato in modo estensivo, può generare complicazioni, in primo luogo nell’impegno di grandi quantità di risorse (tempi di gestione, memoria, grandezza del file, ecc) che riducono le prestazioni del foglio anche in modo rilevante.

Quindi è opportuno tenere sotto controllo l’uso di queste funzioni se le impieghiamo a migliaia nei nostri fogli.

Usare le funzioni Excel per unire 2 tabelle

Approfondiamo un attimo l’uso delle funzioni di ricerca come strumenti con cui unire in modo dinamico due tabelle di dati.

Come abbiamo già visto le funzioni di ricerca, quando vengono applicate in campi aggiuntivi specifici, come formule inserite in ogni riga, permettono di aggiungere dati relativi ai singoli record presenti in tabelle esterne.

Questo richiede la presenza nei record di entrambe le tabelle di chiavi univoche, cioè di informazioni che identificano in modo certo i singoli record, come per esempio un codice cliente, un codice articolo, un centro di costo, e così via. La presenza di questi valori permette di connettere i record delle tabelle in modo da garantire la corrispondenza corretta dei dati.

Questi valori sono usati dalle funzioni di ricerca per inserire le informazioni della seconda tabella che vogliamo visualizzare nella prima se non dispone già delle informazioni relative.

È il modo migliore per raggiungere il risultato?

No. Il modo migliore è ottenere una base dati completa da monte, cioè se i dati sono ottenuti dal gestionale, ottenere una base dati con tutte le informazioni che ci servono. Questo è la soluzione al problema che dovremmo cercare prima di unire due tabelle in Excel.

Laddove non è possibile, dove ci troviamo a lavorare su più basi dati e abbiamo l’esigenza di unirle o di elaborarle in modo concertato, ebbene le funzioni di ricerca non sono lo strumento migliore per farlo, sicuramente non quello più efficiente.

Excel dispone di altri strumenti specifici con cui lavorare sulle basi dati o con cui elaborare più tabelle di dati distinte. Parlo naturalmente di Power Query, ora chiamato Recupera e trasforma, dei modelli di dati e di Power Pivot. Il primo permette di creare query sia a database esterni che interni, quindi anche a tabelle Excel, con cui possiamo filtrare, ristrutturare, ripulire i dati e riversarli in tabelle dinamiche. I modelli dei dati permettono di collegare e gestire più database in una struttura unica che può essere usata per l’elaborazione con strumenti come Power Pivot.

Certo, puoi usare anche le funzioni di ricerca che abbiamo visto. Ma se la quantità di dati è considerevole e il foglio Excel di lunga durata ti invito a usare strumenti più efficienti.

Cosa sono le funzioni Excel condizionate?

Cosa sono le funzioni Excel condizionate? Come funzionano e perché possono essere molto utili? Sono funzioni di calcolo, o controllo, che permettono di inserire uno o più criteri di inclusione o esclusione dei dati da elaborare. Cioè preso un campo numerico in una matrice di dati, le funzioni condizionate possono eseguire calcoli (somma, media, conta, max, ecc) non su tutto il campo, ma solo su quei dati che rispondono direttamente o indirettamente a uno o più criteri (es: maggiore di 100, con Nazione = Italia, ecc).

Un esempio classico è la funzione Conta.Se() che conta i record di un campo solo se rispondono a un preciso criterio diretto (es: CONTA.SE(B:B; “>”&1000).

Qual è lo scopo delle funzioni condizionate?

Risparmiare tempo.

Queste funzioni versatili e piuttosto semplici, ci evitano di usare le funzioni Excel database (es: DB.CONTA.VALORI()), o le formule matriciali (es: {=CONTA(SE($A$1:$A$100=”Italia”;$A$1:$A$100))} ), più complicate, ed evitano di filtrare o estrarre a parte i dati da elaborare.

Da un punto di vista diverso puoi vederle come l’integrazione di funzioni di calcolo di base, come media, somma, eccetera, con la funzione SE().

Come funzionano?

Sono funzioni di Excel che eseguono una precisa operazione se viene soddisfatta una o più condizioni dirette, cioè relative alle celle o ai valori da elaborare, o indirette, cioè relative ad altri campi della tabella o matrice.

Quindi i parametri richiesti sono almeno due, quello dell’intervallo di celle, o del campo da elaborare, controllare, eccetera, e quello del criterio, che viene espresso direttamente o tramite riferimento a cella diversa. Nel caso di criteri indiretti e di più condizioni, allora i parametri aumentano.

Quali sono le funzioni Excel condizionate?

Le funzioni di Excel condizionate sono le seguenti:

1. Conta.Se
2. Somma.Se
3. Media.Se
4. Conta.Più.Se
5. Somma.Più.Se
6. Media.Più.Se
7. Max.Più.Se
8. Min.Più.Se
9. Se.Errore

CONTA.SE (intervallo;criterio)

Questa funzione conta il numero di celle, con qualsiasi tipo di valori, che rispettano direttamente un criterio preciso. I parametri richiesti sono il dove, cioè l’intervallo di celle da contare, e il cosa, cioè il criterio che indica cosa contare e cosa no. Per esempio =CONTA.SE(A2:A100;F1) conta le celle che corrispondono al valore della cella F1. Utile per contare record univoci di valori precisi, per esempio il numero di fatture di un certo cliente, o il numero di movimenti a magazzino di un certo articolo.

La funzione non fa distinzione tra maiuscole e minuscole e prevede caratteri jolly (? per singolo carattere e * per caratteri multipli) per impostare criteri più flessibili su stringhe di testo (es: =CONTA.SE(A2:A100;”Art*”) conterà tutte le celle che contengono stringhe che iniziano per Art).

Inoltre permette anche criteri numerici più flessibili con gli operatori di confronto (es: <, >, ecc), per esempio =CONTA.SE(A2:A100;”>10”) conta solo le celle con valori maggiori di 10. Come noti l’uso di simboli richiede che siano racchiusi tra virgolette. Un altro modo valido per indicare il criterio è =CONTA.SE(A2:A100;”>”&10).

In realtà questa funzione Excel è piuttosto limitata, gestisce solo criteri diretti, utile soprattutto nel conteggio di celle con valori non numerici e criteri semplici.

SOMMA.SE (intervallo;criterio;[int_somma])

Questa funzione somma i valori di celle numeriche che soddisfano un determinato criterio. Il criterio può essere diretto o indiretto, cioè può essere riferito ai valori da sommare, oppure può essere riferito a un campo diverso. I parametri sono tre:

1. L’intervallo, il campo su cui viene verificato il criterio e che può essere il campo dei dati numerici da sommare,
2. Il criterio in base al quale devono essere sommate le celle,
3. int_somma, cioè il campo o l’intervallo di celle che contiene i valori da sommare, parametro da omettere se l’intervallo è anche il campo da sommare, da indicare se il criterio è valutato su un campo diverso.

Per esempio se vogliamo sommare i costi relativi al marketing, possiamo usare =SOMMA.SE(D1:D100;”Marketing”;C1:C100) che somma i valori nel campo C quando nel campo D trova il valore Marketing. Oppure =SOMMA.SE(D1:D100;”>”&20) se vogliamo quantificare i costi di spedizione superiori a 20 euro.

Anche questa funzione, come tutte le altre, non fa distinzione tra maiuscole e minuscole e permette l’utilizzo di caratteri jolly (? per singolo carattere e * per caratteri multipli) per impostare criteri flessibili su stringhe di testo.

La funzione Somma.Se() è stata la prima delle funzioni condizionate introdotte prima di Excel 2007, è semplice da usare, flessibile, ma può essere impiegata per gestire un solo criterio. Alcuni la considerano la versione semplificata di DB.SOMMA() senza la complicazione della gestione dei criteri esterni.

MEDIA.SE (intervallo;criterio;[int_media])

Questa funzione Excel ricalca completamente la precedente funzione SOMMA.SE(), gestisce una condizione e richiede 2 o 3 parametri a seconda che questa riguardi direttamente i valori da elaborare o quelli di un altro campo della tabella.

Un esempio classico è quello del calcolo dell’età media degli uomini o delle donne di un gruppo di studio: =MEDIA.SE(Sesso;”M”;Età), dove la media viene calcolata sull’intervallo nominato come “Età”, se il valore del campo nominato come “Sesso” corrisponde a “M”. Ricordo sempre che queste funzioni non fanno distinzione tra maiuscolo e minuscole.

Media.Se() è poco usata rispetto alla precedente e fu introdotta per dare la possibilità di calcolare medie filtrate senza dover utilizzare la corrispondente funzione database, DB.MEDIA(), o una funzione matriciale (es: {=MEDIA(SE(K7:K16=$P5;L7:L16)) } inserita premendo Ctrl+Maiusc+Invio).

SOMMA.PIÙ.SE  (int_somma;intervallo_criteri1;criteri1;[intervallo_criteri2; criteri2];…)

Questa funzione, come le altre funzioni “più.se”, venne introdotta per soddisfare l’esigenza di gestire più criteri sempre in modo versatile senza dover impiegare le funzioni database o formule matriciali.

Questa funzione Excel richiede l’impostazione di un numero di parametri proporzionale al numero di criteri che si vogliono impostare: 3, 5, 7, eccetera. I parametri sono:

1. l’intervallo di celle da sommare, come indicazione diretta (es: A1:A100, o C:C), nome impostato in precedenza (es: Fatturato), o indicazione tabellare (es: Vendite[Fatturato] se ci si riferisce per esempio a una tabella Excel);
2. l’intervallo di celle su cui valutare il primo criterio;
3. il criterio da verificare;
4. il secondo intervallo di celle su cui valutare il secondo criterio;
5. il secondo criterio da verificare;

e via dicendo fino a un massimo di 127 criteri, quindi da un minimo di 3 parametri a un massimo di 255.

Un esempio può essere: =SOMMA.PIÙ.SE(Importo_Fattura; Data_Fattura; ”>=01/01/2018”; Data_Fattura; ”<01/01/2019”; Nazione; ”Italia”) con cui calcoliamo il fatturato per il 2018 realizzato in Italia.

Questa funzione ha sostituito la precedente SOMMA.SE() per la sua maggiore versatilità. Come sempre non fa distinzione tra maiuscole e minuscole e permette l’utilizzo di caratteri jolly (? per singolo carattere e * per caratteri multipli) per impostare criteri più flessibili su stringhe di testo.

MEDIA.PIÙ.SE (intervallo_medio; intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2]; …)

Media.Più.Se() si comporta nello stesso modo della funzione precedente con l’unica differenza che calcola la media invece della somma.

Un esempio è =MEDIA.PIÙ.SE(Importo_Fattura; Data_Fattura; ”>=01/01/2017”; Data_Fattura; ”<01/01/2018”; Cliente; “Cliente 001”) che calcola la media delle fatture del “Cliente 001” per l’anno 2017.

Anche questa funzione Excel sorpassa la più anziana Media.Se(), limitata a una singola condizione, dimostrandosi più versatile e semplice da usare della corrispondente funzione DB.Media() o delle formule matriciali.

CONTA.PIÙ.SE (intervallo_criteri1; criteri1; [intervallo_criteri2; criteri2]…)

Questa funzione ricalca le precedenti due funzioni come struttura e si differenzia in modo significativo dalla più anziana CONTA.SE() in quanto permette di verificare più condizioni contemporaneamente e di fatto non esegue il conteggio delle celle, ma il conteggio delle volte che vengono soddisfatti tutti i criteri. Per questo motivo tra i parametri non è più richiesta l’indicazione dell’intervallo da contare.

Quindi per esempio =CONTA.PIÙ.SE(Data_Fattura; ”>=01/01/2018”; Data_Fattura; ”<01/01/2019”; Cliente; ”Cliente 001”) conta il numero di acuqisti fatti dal cliente “Cliente 001” durante il 2018.

Da sottolineare la necessità che gli intervalli indicati nei parametri corrispondano come dimensioni. Come sempre non fa distinzione tra maiuscole e minuscole e permette l’utilizzo di caratteri jolly (? per singolo carattere e * per caratteri multipli) per impostare criteri più flessibili su stringhe di testo.

Anche questa funzione Excel ha sostituito la precedente CONTA.SE(), limitata a una singola condizione, dimostrandosi più semplice da usare e più versatile della corrispondente funzione database o delle formule matriciali.

MAX.PIÙ.SE e MIN.PIÙ.SE (intervallo_max;intervallo_criteri1;criteri1; [intervallo_criteri2, criteri2]; …)

Introdotte nell’ultima versione di Excel, queste funzioni permettono di determinare il valore massimo e minimo in un intervallo di celle quando rispettano una o più condizioni. La struttura e la definizione dei parametri è analoga alle altre funzioni “più.se”, per esempio =MAX.PIÙ.SE(Costo; Data_Produzione; ”>=01/01/2017”; Data_Produzione; ”<01/01/2018”; Articolo; “Art001”) individua l’articolo che nel 2017 ha richiesto il maggior costo di produzione.

Come tutte le altre funzioni Excel analoghe, queste non fanno distinzione tra maiuscole e minuscole nell’indicazione dei criteri e permettono l’utilizzo di caratteri jolly (? per singolo carattere e * per caratteri multipli) per impostare criteri più flessibili su stringhe di testo.

Queste funzioni sono state introdotte per completare la rosa delle funzione “più.se” più gradite agli utenti perché più semplici da usare delle funzioni database (es: DB.MAX() ) e della gestione del calcolo tramite formule matriciali. Non credo però saranno le ultime a nascere, per esempio mi aspetto la comparsa di funzioni come Grande.Più.Se() e Piccolo.Più.Se() per individuare l’n–simo valore più grande e più piccolo. Vedremo.

SE.ERRORE (valore; se_errore)

Questa funzione logica non elabora dei dati, ma intercetta e gestisce direttamente la presenza di errori in una formula. Nel caso la formula generi un errore, la funzione intercetta l’errore e può segnalarlo oppure proporre un valore o una formula diversa, in caso contrario esegue il calcolo.

Per esempio =SE.ERRORE((A2–B2)/A2; “Errore nel calcolo”) verifica se il calcolo (A2–B2)/A2 genera un errore e in caso affermativo non lo esegue, ma avverte della sua presenza. In alternativa =SE.ERRORE((A2–B2)/A2; B2) può restituire un valore, oppure può restituire il risultato di una diversa formula a sua volta oggetto della funzione: =SE.ERRORE((A2–B2)/A2; SE.ERRORE((C2–B2)/C2;B2)).

Se.Errore() può riferirsi a una formula in una cella differente, per esempio =SE.ERRORE(C1; “Errore nel calcolo”), ma in assenza di errori restituisce il valore della cella, di fatto replicando la cella su cui è indirizzato. Per questo viene di preferenza utilizzata direttamente nelle formule di tabelle e report per prevenire la comparsa di errori, e raramente nei campi calcolati inseriti nella pagina dati.

Se.Errore() nasce per semplificare l’uso della classica funzione di controllo VAL.ERRORE(val) abbinata alla funzione SE(). VAL.ERRORE() non fa altro che restituire un valore 1 o 0, Vero o Falso, a seconda della presenza di un errore di qualsiasi tipo nella cella su cui è puntata o nella funzione al suo interno. Nella pratica veniva usata in abbinamento con Se() per intercettare e intervenire sugli errori, in modo analogo a Se.Errore, per esempio con funzioni di ricerca: = SE(VAL.ERRORE( CERCA.VERT(Parametri)); ”Non presente”; CERCA.VERT(Parametri)). Viene ancora usato oggi come campo di controllo su basi di dati preesistenti per individuare errori (es: divisioni per 0) e gestirli.

Quando si usano le funzioni Excel condizionate?

Le funzioni condizionate si usano di solito nella creazione di tabelle realizzate con formule, quando non si può o non si vuole usare strumenti come le tabelle pivot. Sono di solito utilizzate per fornire somme e conteggi o le relative % di porzioni di dati filtrati dai criteri impostati, per esempio il fatturato mensile totale, per area, per cliente maggiore, per linea di prodotto e via dicendo.

Io per esempio le uso anche nell’intestazione delle pagine per offrire valutazioni generali relative ai valori più significativi delle tabelle presenti, come mostrato negli ultimi fogli di lavoro condivisi (magazzino, vendite, produzione).

Le uso anche nella costruzione di tabelle riassuntive di report e dashboard per offrire in poco spazio la sintesi dell’analisi o dell’elaborazione.

Prima come si faceva?

Prima dell’introduzione delle funzioni condizionate, i calcoli o i controlli venivano realizzati con altri strumenti esistenti ed egualmente efficaci:

1. Funzioni database
2. Formule matriciali
3. Tabelle pivot
4. Formule non condizionate su basi di dati intermedi estratti per l’elaborazione

1.Funzioni database

Le funzioni database (ed: Db.Somma(), Db.Conta.Valori, Db.Media(), Db.Max(), eccetera) sono funzioni Excel classiche ma molto potenti con cui elaborare grandi matrici di dati per eseguire calcoli relativamente semplici, che però sono più complesse da apprendere e da gestire, per esempio per la necessità di avere criteri espressi esternamente con intestazioni.

Le funzioni database sono funzioni “condizionate” in quanto filtrano i dati da elaborare secondo criteri precisi e multipli, dovrebbero essere indicate qui, ma non li ho inserite in questo post in quanto penso meritino uno spazio specifico.

2. Formule matriciali

Le formule matriciali sono uno degli strumenti più potenti di Excel, introdotte all’inizio degli anni 2000, per sopperire alle lacune delle formule. Di fatto le formule matriciali eseguono calcoli su più elementi di una matrice contemporaneamente per restituire uno o più valori contemporaneamente.

Si può inserire una formula matriciale in una singola cella per calcolare un singolo valore, come quella citata inizialmente ({=CONTA(SE($A$1:$A$100=”Italia”;$A$1:$A$100}),  ma si possono inserire in un intervallo di celle, una formula matriciale a celle multiple, per esempio per calcolare i subtotali di una tabella.

Le formule matriciali vengono inserite premendo Ctrl+Maiusc+Invio, invece del solito invio, possono richiedere la selezione di più celle prima dell’inserimento e appariranno nella cella tra parentesi graffe. Offrono molti vantaggi e caratteristiche utili che non approfondiremo in questo post, ma in un post dedicato. Naturalmente soffrono di alcuni svantaggi, per esempio richiedono capacità di calcolo e risorse da parte del pc su cui lavoriamo.

Prima dell’introduzione delle formule condizionate erano il modo più efficace per ottenere gli stessi risultati, integrando la funzione SE() alle funzioni di calcolo matematiche e statistiche. Per esempio =SOMMA(SE(D1:D100=”Marketing”;C1:C100)) offre lo stesso risultato di SOMMA.SE(D1:D100;”Marketing”;C1:C100).

Nonostante la loro efficacia e la loro versatilità le formule matriciali sono strumenti poco conosciuti a causa della complessità di utilizzo.

3. Tabelle pivot

Sulle tabelle pivot, il loro potenziale e le loro caratteristiche ti rimando alla Guida sulle tabelle pivot. Se non le conosci leggila. Le tabelle pivot sono strumenti talmente efficienti e potenti che hanno sostituito le tabelle strutturate con le formule in molti ambiti, soprattutto a livello aziendale e professionale.

Naturalmente non possono essere la soluzione per ogni problema, ci sono situazioni dove è necessario andare oltre i loro limiti e tornare per esempio alle formule, o passare a strumenti più evoluti come Power BI.

Le funzioni e i testi in Excel

I testi in Excel non sono i protagonisti, ruolo destinato ai numeri, ma sono fondamentali e onnipresenti sotto molte forme differenti. Quando parliamo di testi in Excel di solito ci riferiamo a:

1. titoli e sottotitoli di pagine, di tabelle o grafici,
2. note e istruzioni, nelle pagine o in caselle di testo disposte nelle pagine,
3. variabili di basi dati relativi a nomi ed etichette (es: nome cliente, nome prodotto, ecc),
4. campi di basi dati relativi a chiavi alfanumeriche (es: cod cliente, cod prodotto, ecc),
5. variabili di basi dati relativi a note, indirizzi e similari,
6. campi di basi dati relativi a categorie e parametri (es: come linea prodotto, nazione dei clienti, tipo offerta, eccetera).

La maggior dei testi sono importati in Excel come parte delle basi dati, mentre i testi vengono inseriti e gestiti da noi prevalentemente durante la creazione o la revisione dei fogli Excel. Raramente c’è la necessità di gestire o manipolare testi duranti l’uso ordinario di un foglio di lavoro, se non per aggiunge note nelle pagine di elaborazione, oppure per compilare un report o scrivere la mail con cui condividiamo i risultati dell’elaborazione.

Quando allora abbiamo bisogno di usare funzioni o altri strumenti per manipolare o elaborare testi?

Di solito per ragioni precise, come in presenza di basi dati inaffidabili, o testi “sporchi” da ripulire o da cui estrarre stringhe, oppure in presenza di errori di formato, o per la necessità di gestire o creare chiavi univoche, e altro ancora.

I testi come categorie

Per la maggior parte i testi sono presenti nei dati su cui lavoriamo, di solito inseriti a monte ed estratti dal gestionale o dal server. Questi testi dovrebbero essere affidabili e pronti all’uso, quindi non dovrebbero richiedere elaborazioni.

Dove invece le basi dati non sono affidabili, o sono inserite manualmente, o hanno bisogno di controlli, rimodellamento o pulizie, oppure quando presentano testi non predisposti per l’elaborazione (testi raggruppati, frammentati, formati speciali o anomali, ecc), allora è necessario manipolare e gestire questi testi.

Possiamo aver bisogno di intervenire per:

1. pulire i testi da errori, caratteri speciali, o altre stringhe non desiderate,
2. scomporre i testi raggruppati in elementi diversi,
3. estrarre stringhe specifiche,
4. creare chiavi, univoche o meno, concatenando testi o porzioni estratte,
5. controllare la correttezza e qualità dei testi,
6. e altro ancora.

L’elaborazione dei testi nelle basi dati avviene di solito tramite campi calcolati, cioè inserendo campi con formule che eseguono l’elaborazione o il controllo richiesto. Questi campi poi possono sostituire i campi originali in altri strumenti, come tabelle pivot.

Le funzioni Excel sono gli strumenti migliori per l’elaborazione dei testi?

Le funzioni Excel di testo sono strumenti precisi che possono manipolare le stringhe carattere per carattere, ma ci sono strumenti evoluti che permettono di fare modellazione, ristrutturazione e pulizia dei dati e quindi anche dei testi in modo più efficiente. Per esempio recupera e trasforma o l’automazione.

I testi come titoli ed etichette

Come abbiamo già detto, i testi che inseriamo durante la creazione dei fogli di lavoro sono quelli correlati agli strumenti, dalle liste dei controlli ai testi dei grafici, dalle note delle tabelle di analisi, alle istruzioni relative all’intero foglio, eccetera.

Questi testi sono di solito unici e non vengono elaborati, a meno che non siano derivati da o correlati a basi dati, oppure valori presenti in tabelle dinamiche, come parametri usati in elenchi e controlli. In questi casi allora i testi possono diventare oggetti di gestione ed elaborazione attraverso molteplici strumenti, anche funzioni specifiche usate in modo diretto o tramite altri strumenti come nomi assegnati o la convalida dei dati.

Elaborare i testi

I testi si elaborano usando gli strumenti che Excel mette a nostra disposizione, come:

1. operatori di concatenamento,
2. funzioni excel di testo
3. funzioni di ricerca
4. altre funzioni (logiche, di riferimento, informative/di controllo, ecc)
5. query
6. automatismi

Ogni strumento ha specifiche capacità di intervento che dobbiamo conoscere bene per poterlo utilizzare in modo efficace ed efficiente. In questa piccola guida parliamo delle funzioni Excel di testo in quanto sono strumenti precisi ed efficaci per manipolare ed elaborare stringhe di testo.

Le funzioni Excel per elaborare i testi

Quali sono le funzioni Excel di testo?

Sono una trentina di funzioni che offrono scorciatoie comode ed efficaci per agire sui testi in tutti i modi possibili, dalla rimozione di caratteri specifici (es: spazi o caratteri non stampabili), alla concatenazione di testi diversi, dalla ricerca di testi precisi alla loro estrazione, dalle conversioni al controllo, eccetera.

Come tutte le funzioni, queste devono essere inserite in celle delle pagine, integrate nella tabella dei dati o in posizioni esterne, usando gli strumenti per la scrittura delle formule, oppure possono essere inseriti in altri strumenti predisposti per ricevere e gestire formule e funzioni (es: nomi, convalida, formattazione condizionale, ecc).

In questo post non vedremo tutte le funzioni Excel di testo, ma solo le più utili:

1. Spazi(), elimina gli spazi dal testo,
2. Concatena(), Concat() e Testo.Unisci()
3. Trova() e Ricerca(), rileva un valore di testo all’interno di un altro (con e senza distinzione tra maiuscole e minuscole)
4. Sinistra() e Destra(), restituisce il carattere più a sinistra o destra di un valore di testo,
5. Estrai(), restituisce un numero specifico di caratteri di una stringa di testo a partire dalla posizione specificata,
6. Lunghezza(), restituisce il numero di caratteri di una stringa di testo,
7. Ripeti(), ripete un testo per il numero di volte specificato,
8. Rimpiazza(), sostituisce i caratteri all’interno di un testo,
9. Sostituisci(), sostituisce il nuovo testo al testo contenuto in una stringa,
10. Testo(), formatta un valore in un diverso formato e può convertirlo in testo.

Oltre a queste funzioni ce ne sono molte altre, più specifiche, tra cui per esempio possiamo citare alcune delle funzioni di conversione: Minusc(), converte in minuscolo il testo, Maiusc() converte in maiuscolo il testo e Maiusc.Iniz() converte in maiuscolo la prima lettera di ogni parola di un valore di testo.

Come possiamo citare alcune delle funzioni di controllo relative ai testi: Identico(), verifica se due valori di testo sono uguali; T(), restituisce il valore se è un testo; Val.testo(), restituisce vero se il valore controllato è un testo.

Unire testi con le funzioni Excel di testo

Talvolta possiamo aver bisogno di unire testi diversi, per esempio per creare nuove categorie, o chiavi secondarie per collegare tabelle diverse. Per unire due testi in due celle diverse possiamo usare:

1. l’operatore classico di concatenamento, cioè “&”,
2. oppure le funzioni di concatenamento.

L’operatore di concatenamento è il modo classico per unire testi di celle diverse, per esempio la formula =A2& B2 restituirà un testo unico dai testi contenuti nelle celle indicate. Per esempio se A2 contiene “EmiliaRomagna” e B2 contiene “Bologna”, il risultato sarà “EmiliaRomagnaBologna”. È importante sottolineare che se le celle contengono numeri questi verranno tratti come testo e il risultato sarà un testo.

L’alternativa all’operatore sono funzioni come Concatena(), Concat() e Testo.Unisci(). La prima è la funzione classica ancora presente, analoga all’operatore, che prevede di inserire come parametri stringhe o riferimenti a celle.

Quindi inserendo =Concatena(“EmiliaRomagna”; “Bologna”), o =Concatena(A2; B2) otterremo lo stesso risultato visto in precedenza.

Invece Concat() è una funzione introdotta con Excel 2016 che ha sostituito la precedente, fa le stesse identiche cose, ma è anche in grado di restituire stringhe complesse di intervalli molto grandi. Per esempio =Concat(A:A; B:B) restituisce una stringa unica composta dai valori presenti nelle celle della colonna A seguiti dai valori presenti nelle celle della colonna B.

Infine Testo.Unisci(delimitatore,ignora_vuote, testo1, [testo2], …) è simile a Concat, quindi può lavorare su molte celle e restituire stringhe molto lunghe, ma in più può inserire un delimitatore deciso da noi, per esempio una virgola(“,”). In realtà questo è utile solo in situazioni molto particolari.

Quale strumento usare per unire testi?

Il mio consiglio è di usare l’operatore di concatenamento se dobbiamo unire poche stringhe e usare le funzioni solo per concatenare molti testi.

Un’altra funzione che vale la pena conoscere quando uniamo stringhe diverse, o meglio quando creiamo nuovi testi, è Ripeti(testo; volte) che restituisce una stringa ripetendola N volte. Questo insieme alle funzioni di concatenamento appena viste e alle funzioni che vedremo di seguito permette di creare testi anche complessi.

Le funzioni Excel per trovare stringhe in testi

Spesso abbiamo bisogno di cercare un carattere o una stringa in un testo, che non sappiamo dove sia posizionato. Per individuare stringhe in un testo possiamo usare le funzioni di ricerca destinate ai testi: Ricerca() e Trova().

RICERCA (testo; stringa; inizio)

Questa funzione cerca una stringa all’interno di un testo e restituisce un numero intero che corrisponde alla posizione iniziale della stringa individuata nel testo. Per esempio, se cerchiamo “pos” in “Composizione del prodotto” restituirà il valore 4. Se la stringa non è presente viene restituito un errore (#Valore).

Inoltre permette di indicare da quale carattere del testo la funzione deve iniziare a cercare (parametro “inizio”). Per esempio con: RICERCA(“yh”; B5; 3) la funzione cercherà “yh” nella cella B5 a partire dal terzo carattere e se la troverà restituirà la sua posizione. Altrimenti presenta un errore.

È importante sottolineare che:

1. Ricerca() non rileva la differenza tra maiuscole e minuscole,
2. mentre è possibile usare i caratteri jolly, per esempio il punto interrogativo (?), che rappresenta un singolo carattere, e l’asterisco (*), che invece rappresenta una sequenza qualsiasi di caratteri,
3. se il valore di inizio è minore di 1 o maggiore della lunghezza del testo, viene restituito un errore (#Value!).

TROVA (testo; stringa; inizio)

È la funzione gemella della precedente, funziona quasi allo stesso modo, con alcune differenze che possono diventare significative in certe situazioni. In particolare:

1. distingue tra lettere maiuscole e minuscole,
2. non supporta i caratteri jolly,
3. se la stringa è vuota (“”), restituirà il primo carattere della stringa o il numero di inizio.

Trova è utile quando dobbiamo lavorare su maiuscole, ma non permette di usare caratteri jolly con cui rendere più efficiente la ricerca.

Quando si usano le funzioni Excel di ricerca

Si usano di solito in campi calcolati aggiunti a tabelle per esempio per:

1. l’individuazione di parole chiave e quindi per creare nuove categorie,
2. l’estrazione di una porzione identificabile del testo stesso,
3. la sostituzione o l’inserimento di stringhe all’interno di testi, codici, o chiavi,

e altro ancora.

Le funzioni Excel per estrarre stringhe

Un’altra applicazione frequente delle funzioni Excel di testo è quella dell’estrazione di stringhe e può essere realizzata con funzioni e tecniche diverse a seconda se la stringa:

1. è sempre presente e posizionata in modo noto,
2. è presente, ma non si conosce la posizione esatta,
3. può non essere presente

1. Stringa presente posizionata

Se dobbiamo estrarre da un testo una porzione che sappiamo dov’è posizionata, allora possiamo usare le funzioni Stringa.Estrai(), Destra(), Sinistra(), Lunghezza().

Destra(testo; [num_caratt]) e Sinistra(testo; [num_caratt]) restituisce la stringa di N caratteri a partire dal primo carattere a destra o a sinistra del testo. Stringa.Estrai(testo; inizio; num_caratt) restituisce la stringa di N caratteri a partire da un carattere preciso. Lunghezza(testo) restituisce il numero di caratteri del testo.

Queste funzioni permettono di “smontare” ed estrarre stringhe da testi codificati come chiavi univoche (cod cliente, cod prodotto, ecc).

Per esempio =Sinistra(“Aspettando”;3) restituisce la stringa “Asp”. =Stringa.Estrai(“Aspettando”; 4; 3) restituisce “ett” cioè la stringa di 3 caratteri a partire dal 4° carattere. Oppure =Destra(Sinistra(“Aspettando”;7);3) restituisce “tta”. Invece =Destra(Sinistra(B3; Lunghezza(B3)-2);3) restituisce “tan” se la cella B3 contiene “Aspettando”, oppure restituisce la stringa di 3 caratteri a partire dal 3 carattere da destra della stringa.

2. Stringa presente ma in posizione sconosciuta

Se siamo certi che la stringa è presente nel testo, dobbiamo individuarne la posizione e gli strumenti più adatto sono le funzioni di ricerca viste in precedenza: Trova() e Ricerca().

Queste permettono di individuare la posizione della stringa e funzioni come Stringa.Estrai() permettono di estrarla. Tra l’altro Ricerca() permette di usare caratteri jolly e quindi di cercare stringhe non univoche in modo flessibile. Per esempio, =Stringa.Estrai(B3; Ricerca(B3; “w”; 3); 4) cerca il carattere “w” nel testo a partire dal terzo carattere da sinistra e quando lo trova restituisce la stringa di 4 caratteri che inizia con “w”.

3. Stringa presente ma in posizione sconosciuta

E se invece non siamo certi della presenza della stringa?

Usiamo sempre le funzioni di ricerca, ma dobbiamo usare funzioni per gestire gli errori ed eventualmente offrire stringhe alternative. Per esempio =Se.Errore(Stringa.Estrai(B3; Ricerca(B3; “W”; 3); 4); “Assente”), restituisce la stringa vista in precedenza se presente, oppure “Assente” se la stringa ricercata è assente.

Le funzioni Excel per sostituire porzioni di testo

Se invece di estrarre una stringa del testo, hai bisogno di replicare il testo sostituendone una porzione allora puoi usare funzioni specifiche come Sostituisci() e Rimpiazza().

Sostituisci(testo; testo_prec; nuovo_testo; [occorrenza]) ricerca e sostituisce una stringa in un testo con una nuova stringa e se questo si ripete possiamo dirgli dove farlo. RIMPIAZZA(testo_prec; inizio; num_caratt; nuovo_testo) è simile, ma sostituisce un numero preciso di caratteri in una posizione specifica con la stringa che vogliamo.

Per esempio, =SOSTITUISCI(“Articolo A345dg”; “Articolo”; “Prodotto”) nel testo “Articolo A345dg” sostituisce “Articolo” con “Prodotto” restituendo “Prodotto A345dg”. Invece =RIMPIAZZA(“Fatturato 2017”;11;4;”2018″) modifica il testo “Fatturato 2017” sostituendo dal carattere numero 4 caratteri, cioè “2017” con “2018”.

Le funzioni di sostituzione sono meno utilizzate delle funzioni di estrazione, ma sono complementari e vale la pena conoscerle. Mentre Rimpiazza() in sostanza prevede che noi conosciamo la struttura del testo e quindi che c’è una stringa precisa in un posizione precisa del testo, Sostituisci() è in parte una funzione di ricerca che individua una stringa nel testo per poi sostituirla con una nuova stringa. Se Sostituisci non trova la stringa che vogliamo sostituire restituisce il testo originale.

La funzione Excel per rimuovere gli spazi in eccesso

Nel passato gli spazi in eccesso nei testi potevano dare diversi problemi, ad altre funzioni (es: ricerca) o altri strumenti, quindi in presenza di testi “sporchi”, cioè non filtrati da controlli all’inserimento, rimuovere gli spazi in eccesso era un modo veloce per poter preparare i dati all’elaborazione.

Per farlo c’è una classica funzione: Annulla.Spazi(testo) che restituisce il testo senza spazi in più, cioè rimuove gli spazi prima e dopo il testo e lascia un solo spazio tra le parole del testo, rimuovendo spazi in eccesso.

Per esempio se applichiamo questa funzione a “Margherita ”, così =Annulla.Spazi(“Margherita ”), questo restituirà “Margherita”. Invece =Annulla.Spazi(“Mario  Rossi”) restituirà “Mario Rossi”.

Le funzioni per trasformare i testi

Ci sono decine di funzioni specifiche per trasformare i formati dei testi in situazioni specifiche, che non dovrebbero presentarsi se le basi dati sono affidabili o se abbiamo inserito correttamente i nostri testi.

Tra queste funzioni vale la pena ricordare quelle che gestiscono il maiuscolo e il minuscolo dei testi: Maiusc(), Minusc() e Maiusc.Iniz(). Utili se dobbiamo fare revisioni dei testi, per esempio per eliminare il maiuscolo o per correggere nomi propri. Per esempio =Minusc(A3) riduce a minuscolo il testo della cella A3, mentre Maiusc.Iniz(A3) mette le maiuscole a inizio di ogni parola del testo e riduce a minuscolo tutto il resto.

Infine è importante conoscere la funzione =TESTO(Valore da formattare;”Codice formato da applicare”) che in sostanza trasforma il formato di un dato, anche di un numero, in un altro formato. Quindi possiamo trasformare un numero in una data, un numero in un altro formato numerico, un numero in un testo, eccetera.

Per esempio, =TESTO(OGGI();”GG/MM/AA”) visualizza la data di oggi come 13/02/17. Oppure =TESTO(B3;”0,0%”) trasforma il numero decimale della cella B3 in una percentuale con un decimale (17,3%). O =TESTO(C4;”MMMM”) trasforma il mese in forma di numero intero da 1 a 12 nella cella C4 nella versione testuale (es: 1 in “gennaio”). E se volessimo invece “Gennaio”? Dovremmo usare questa formula: =MAIUSC.INIZ(TESTO(C4;”MMMM”)).

Per usare al meglio questa funzione Testo() devi conoscere i formati dei dati di Excel e per questo ti invito a leggere attentamente questo post sul sito microsoft e a esaminare i formati personalizzati che trovi in Excel.

Dove? Premi Ctrl+1 per aprire la finestra formati e clicca sulla voce “Personalizzato” della lista. Prova i diversi formati preimpostati e prova a modificarli per comprendere come funzionano.

Conclusioni

Le funzioni sono strumenti efficaci e flessibili per l’elaborazione dei dati, la cui conoscenza ci permette di ottenere il meglio anche da altri strumenti di Excel (es: convalida, formattazione condizionale, ecc) e di realizzare elementi e controlli di dashboard e report. Conoscere Excel significa conoscere le funzioni di Excel, sono tra gli strumenti fondamentali che possiamo usare anche quando quelli più evoluti raggiungono i loro limiti, o quando abbiamo bisogno di personalizzare funzionalità e fogli, strumenti efficaci anche in acque sconosciute.

In particolare le funzioni di ricerca sono strumenti potenti di excel con cui possiamo estrarre dati specifici per visualizzarli in tabelle o elaborarli, usate anche per inserire campi interi o per unire tabelle. Le funzioni di ricerca rimangono probabilmente le funzioni più utilizzate in report, dashboard e tabelle di formule, strumenti che devi conoscere bene se lavori con Excel.

Invece le funzioni “condizionate” sono strumenti versatili di facile utilizzo che permettono di elaborare i dati senza ricorrere a strumenti più complessi come possono essere funzioni database e formule matriciali. Sono in grado di applicare i calcoli più usati (somma, conta, media, ecc) su una porzione dell’intervallo in esame, definita grazie a criteri precisi, in modo facile, veloce e diretto. Le funzioni condizionate devono essere parte del bagaglio di conoscenze di tutti coloro che usano Excel nella loro professione.

Se vuoi approfondire l’argomento funzioni di excel, ci sono innumerevoli risorse a cui attingere. Quali? Per iniziare consulterei la pagina del sito della Microsoft e secondo me basterebbe leggersi questa, ma se preferisci esempi pratici e approfondimenti esistono numerosi libri sulle funzioni.

Per comodità tua e mia puoi scaricare il PDF della guida tramite Gumroad

Devi solo cliccare sul pulsante sottostante e seguire le istruzioni

Scarica la Guida

NB: non devi pagare, non è necessario lasciare la tua mail

Iscriviti a ExcelProfessionale per scoprire tutto quello che Excel può fare per te

Excel può fare per te più di quello che immagini.
Se vuoi scoprire e apprendere cosa puoi fare realmente con Excel, iscriviti a Excel Professionale usando la maschera seguente.

Riceverai la newsletter bimestrale, accederai ai contenuti gratuiti condivisi (fogli dimostrativi, macro, eccetera), potrai iscriverti gratuitamente ai video corsi introduttivi e ai webinar formativi, riceverai offerte dedicate ai corsi avanzati e molto altro.

PS: Se questa guida ti è piaciuta o ti è stata utile, condividila con gli amici sui social.

Puoi farlo attraverso i pulsanti qui di seguito. Grazie.