Funzioni condizionali nei fogli di calcolo

Quando si impara praticamente qualsiasi linguaggio di programmazione, una delle prime cose che si affrontano sono i condizionali. Cioè le classiche istruzioni IF-ELSEIF-ELSE, che permettono ai programmi di eseguire operazioni diverse in base a condizioni logiche. Tuttavia, questo potrebbe non essere la prima cosa a cui si pensa in Excel. Eppure, ci sono, e sono piuttosto importanti quando lavori su report Excel con un certo livello di complessità. In particolare, in questo tutorial vedremo come sono strutturate le istruzioni IF-ELSE in Excel e alcune funzioni più avanzate come COUNTIF() che tornano utili in determinate situazioni.

IF di base

Per iniziare, vediamo come scrivere un'istruzione IF di base in Excel. La sintassi è la seguente:

IF(condition, value_if_true, value_if_false)

Dove:

• condition: valore o operazione logica che restituisce TRUE o FALSE
• value_if_true: Il valore da restituire se la condizione è TRUE
• value_if_false: Il valore da restituire se la condizione è FALSE

Detto questo, passiamo a un semplice esempio di come usare un condizionale di base con una tabella di prova:

Semplice, vero? Per chi conosce R, avrai probabilmente notato che questa sintassi è sostanzialmente la stessa della funzione ifelse(). Tuttavia, ci sono alcune differenze. Per esempio, gli operatori logici. In questo semplice esempio ho usato l'operatore logico ">", che è un modo standard per indicare maggiore di. Altri, invece, non sono così standard. Tra questi c'è l'operatore diverso da, che si scrive come "<>"

Un elenco completo di tutti gli operatori logici usati in Excel è riportato di seguito:

OR(), AND() e NOT()

Gli operatori di confronto non sono gli unici componenti delle istruzioni condizionali di Excel che possono differire da quelli che potresti usare in Python, R o Matlab. Infatti, il modo in cui definisci operazioni booleane come OR, AND e NOT è diverso. Ad esempio, in Excel scriveresti un'espressione OR così:

IF(OR(value_1 = k, value_2 = y), value_if_true, value_if_false)

Per vedere tutti gli operatori booleani in azione in Excel, guarda l'esempio qui sotto:

IF annidati

Così come puoi annidare istruzioni IF-ELSEIF-ELSE in Python, R o Matlab, puoi farlo anche in Excel. Per farlo, basta aggiungere un'altra istruzione IF dentro una precedente quando questa valuta a TRUE o FALSE. Questo ti consente di testare più condizioni e restituire più risultati in base a tali condizioni. Excel può annidare fino a 64 istruzioni IF. Tuttavia, sconsiglio vivamente di annidare troppe istruzioni IF: bisogna fare attenzione alla logica e il debug o le modifiche successive possono diventare molto macchinosi.

Detto ciò, ecco un semplice caso d'uso di IF annidati direttamente in Excel:

COUNTIF() e COUNTIFS()

Oltre alle classiche istruzioni IF, Excel dispone di altre funzioni che possono eseguire operazioni basate su un insieme di condizioni. In questa sezione mi concentrerò sulle funzioni di conteggio condizionale e, più specificamente, sulle funzioni COUNTIF() e COUNTIFS().

COUNTIF() consente di contare le celle che soddisfano un singolo criterio. La sua sintassi è la seguente:

COUNTIF(cell_range_to_count, criteria)

Dove:

• cell_range_to_count: L'intervallo specifico di celle che desideri contare
• criteria: Il criterio che determina quali celle devono essere contate. Può essere molto vario, ad esempio:
  • COUNTIF(A1:A10, 10) - Conta quante celle nell'intervallo da A1 ad A10 sono uguali a 10
  • COUNTIF(A1:A10, B5) - Conta quante celle nell'intervallo da A1 ad A10 sono uguali al valore della cella B5
  • COUNTIF(A1:A10, "Gandalf") - Conta quante celle nell'intervallo da A1 ad A10 sono uguali a "Gandalf"
  • COUNTIF(A1:A10, ">20") - Conta quante celle nell'intervallo da A1 ad A10 sono maggiori di 20
  • COUNTIF(A1:A10, "<>Gandalf") - Conta quante celle nell'intervallo da A1 ad A10 sono diverse da "Gandalf"

COUNTIFS, come avrai intuito, differisce da COUNTIF in quanto consente di contare le celle che soddisfano più criteri. La sua sintassi è la seguente:

COUNTIFS(cell_range_to_count_1, criteria_1, cell_range_to_count_2, criteria_2,...cell_range_to_count_n, criteria_n)

È importante notare che per ogni intervallo aggiuntivo specificato in COUNTIFS, devi avere lo stesso numero di righe e colonne dell'intervallo originale (cell_range_to_count_1), altrimenti otterrai un errore.

Queste due funzioni sono fantastiche nei report aziendali. Uno dei migliori esempi, per me, è quanto rendano semplice contare il numero di appuntamenti che i nostri rappresentanti di vendita fissano ogni mese e dove si trovano nel nostro funnel di vendita.

Nei video qui sotto, mostrerò come funzionano queste due funzioni in pratica. Per iniziare, contiamo semplicemente il totale degli appuntamenti di un gruppo di venditori fittizi:

Facile, no? Hai appena totalizzato quanti appuntamenti ha avuto in totale ciascuno dei nostri venditori fittizi. In teoria, potresti farlo anche solo con IF di base, ma richiederebbe molto più lavoro per ottenere lo stesso identico risultato.

Ora, supponiamo che tu voglia anche totalizzare gli appuntamenti che ciascuno di questi venditori ha avuto ogni mese. In questo caso, COUNTIFS() è la risposta; puoi vederla in azione nel video qui sotto:

Nota: Avrai notato che nell'ultimo video ho racchiuso l'intervallo che volevo contare tra segni $. Questo serve a bloccarlo e impedire che cambi quando trascino la formula verso il basso.

Voilà, abbiamo totalizzato gli appuntamenti dei nostri venditori di esempio per mese senza fatica. Vale la pena menzionare che potresti farlo in modo più "efficiente" se giochi con i segni $ per bloccare determinate celle e intervalli quando trascini le formule (cioè, avrei potuto scrivere la formula in F2 e poi trascinarla nel resto). Tuttavia, ho preferito essere più esplicito scrivendo le formule COUNTIFS per gennaio e febbraio per dare maggiore visibilità. Se però sei curioso, ti incoraggio a provare a riscrivere la formula COUNTIFS() in F2, in modo da poterla trascinare nelle restanti celle ottenendo gli stessi risultati. Può essere un buon esercizio, perché è molto comodo giocare con il blocco di intervalli e celle per trascinare le formule quando i tuoi report Excel diventano grandi. Ecco la tabella dati usata in questo tutorial.

SUMIF() e SUMIFS()

Abbiamo visto come contare celle che soddisfano un singolo criterio o più criteri con COUNTIF() e COUNTIFS(), ma se invece di contarle devi sommarle? Ecco SUMIF() e SUMIFS(). Queste funzioni sono particolarmente utili quando, ad esempio, devi sommare il fatturato generato da un determinato venditore in totale o in un determinato mese. La sintassi di entrambe le funzioni è la seguente:

SUMIF(criteria_range, criteria, (optional) sum_range)

SUMIFS(sum_range, criteria_range_1, criteria_1, criterion_range_2, criteria_2...criteria_range_n, criteria_n)

Dove:

• criteria_range: L'intervallo di celle in cui verrà verificato il criterio.
• criteria: Il criterio condizionale utilizzato per determinare quali celle verranno sommate.
• sum_range: Le celle che verranno sommate. Se non viene fornito in COUNTIF(), vengono sommate le celle in criteria_range.

Ora, senza ulteriori indugi, passiamo a un esempio che usa sia SUMIF() sia SUMIFS() per calcolare il fatturato totale e quello per mese generato dai nostri rappresentanti di vendita fittizi:

È curioso notare che, nel caso di SUMIFS(), sum_range è un argomento obbligatorio ed è il primo argomento, mentre è opzionale e l'ultimo in SUMIF(). Questo può creare confusione quando usi entrambe le formule, come si vede quando stavo digitando la funzione SUMIFS per febbraio. Pertanto, consiglio cautela quando lavori con entrambe. Assicurati sempre di sommare gli intervalli di celle corretti.

IFERROR()

Chi conosce un linguaggio di programmazione/script sa che esistono i blocchi try/catch per la gestione degli errori. In Excel, abbiamo la funzione IFERROR() per fare esattamente questo. La funzione IFERROR consente di "intercettare" errori come #N/A, #VALUE! o #REF! e fornire un output più significativo. La sintassi della funzione IRERROR è la seguente:

IFERROR(value, value_if_error)

Dove:

• value: Il valore o la formula da controllare per un errore
• value_if_error: Il valore da restituire quando la funzione incontra un errore.

Vediamo come funziona in pratica nel seguente video:

Quando lavori con report di vendita aziendali, di solito è una buona pratica coprire alcuni calcoli con funzioni IFERROR. A volte capiterà che nuovi venditori abbiano pochissimi appuntamenti, opportunità qualificate o contratti chiusi, il che può causare divisioni per 0 quando si calcola, ad esempio, la percentuale di appuntamenti che diventano opportunità qualificate per un dato venditore. Questo può generare errori poco gradevoli che probabilmente faranno sembrare il tuo report disordinato e più difficile da leggere per chi decide. Tuttavia, se avvolgi questi calcoli con istruzioni IFERROR(), puoi sostituire quegli errori con qualcosa di più gradevole alla vista come un trattino, rendendo il tuo report più presentabile. Inoltre, le istruzioni IFERROR() vanno oltre il semplice output di messaggi di errore più carini. Puoi anche annidarle per eseguire, ad esempio, VLOOKUP in catena.

Conclusione

Congratulazioni! Ora conosci diversi modi per sfruttare i condizionali in Excel. In questo tutorial ho cercato di coprire le funzioni che uso più spesso quando devo completare report aziendali in Excel. Tuttavia, se hai ancora voglia di approfondire l'argomento, esistono altre funzioni come SWITCH(), AVERAGEIF() o AVERAGEIFS() di cui non ho parlato qui e che potresti trovare interessanti.

Inoltre, ci sono aspetti come l'uso dei caratteri jolly in funzioni come COUNTIFS(), che sono modi perfettamente validi per contare corrispondenze parziali di stringhe. Per esempio, supponi di avere un file Excel con un elenco di voci del menu di un ristorante e di voler contare quante di queste menzionano la parola "fish". In questo caso, potresti scrivere COUNTIF(cell_range,"*fish") e questa funzione conterebbe voci come swordfish o dolphinfish. Ti incoraggio vivamente a saperne di più sui caratteri jolly: possono essere molto utili e non sai mai quando potrebbero tornarti comodi nel tuo prossimo progetto Excel.

Infine, potresti anche dare un'occhiata al corso Data Analysis with Spreadsheets qui su DataCamp, che include una sezione dedicata alle funzioni condizionali e può esserti di ulteriore riferimento.

Dai un'occhiata al nostro tutorial if…elif…else in Python.