Le funzioni Excel condizionate

Cosa sono le funzioni Excel condizionate? Come funzionano e perché possono essere molto utili? Questa sera parliamo di alcune delle funzioni Excel più versatili, le funzioni condizionate, come Somma.Se(), Conta.Più.Se(), Max.Più.Se, eccetera.

Cosa sono le funzioni Excel condizionate?

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 condizionate?

Le funzioni di Excel condizionate sono le seguenti:

Conta.Se
Somma.Se
Media.Se
Conta.Più.Se
Somma.Più.Se
Media.Più.Se
Max.Più.Se
Min.Più.Se
Se.Errore

Come si usano le funzioni Excel condizionate?

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:

L’intervallo, il campo su cui viene verificato il criterio e che può essere il campo dei dati numerici da sommare,
Il criterio in base al quale devono essere sommate le celle,
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:

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);
l’intervallo di celle su cui valutare il primo criterio;
il criterio da verificare;
il secondo intervallo di celle su cui valutare il secondo criterio;
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:

Funzioni database
Formule matriciali
Tabelle pivot
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.

Conclusioni

Le funzioni Excel condizionate sono strumenti versatili di facile comprensione e 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, anche se nella maggior parte dei casi ci ritroviamo a impiegare tabelle pivot o strumenti più avanzati. Perché dopotutto anche questi ultimi sono limitati e non possono arrivare ovunque, come invece le formule possono fare, anche se con una spesa maggiore di tempo.

Naturalmente ti invito a studiarle se non le conosci, o ad approfondire l’argomento se vuoi imparare a usarle bene a livello professionale.

Se ti interessa l’argomento funzioni ti invito a dare un’occhiata al post “Le funzioni di Excel che devi conoscere” e a “Le funzioni di Excel che è utile conoscere“.

PS: Se questo post ti è piaciuto o ti è stato utile, condividilo con gli amici sui social.

Puoi farlo attraverso i pulsanti qui di seguito. Grazie.