Intervalli di date nelle funzioni basate su criteri

Nei report reali viene quasi sempre richiesto un intervallo di tempo: le vendite di questo trimestre, gli ordini del mese scorso, le iscrizioni comprese tra due date. Le funzioni con criteri apprese, SUMIFS, COUNTIFS e AVERAGEIFS, gestiscono tutto questo in modo efficace, una volta imparato come esprimere un intervallo di date.

Il punto fondamentale è che un intervallo di date equivale in realtà a due condizioni sulla stessa colonna di date: una data uguale o successiva all'inizio e una uguale o precedente alla fine.

I fogli di calcolo memorizzano le date come numeri seriali: il giorno 1 è il 1° gennaio 1900, oppure il 1899 in Sheets, e ogni giorno successivo aggiunge un'unità. Per questo è possibile confrontare le date con > e < esattamente come normali numeri.

Di conseguenza, dopo il 1° gennaio significa semplicemente un numero seriale maggiore del numero corrispondente a quella data. Questa è l'idea fondamentale che rende possibile il filtraggio per data.

Supponiamo che la colonna A contenga la Data dell'ordine e la colonna C l'Importo. Per ottenere il totale delle vendite di gennaio 2024, si indica due volte la colonna A: una volta per le date uguali o successive al 1° gennaio e una volta per quelle uguali o precedenti al 31 gennaio.

Racchiuda le date nella funzione DATE(year, month, day) per renderle non ambigue con qualsiasi formato locale. Le due condizioni formano un AND e includono solo le righe del mese.

Potrebbe provare a digitare direttamente ">=1/1/2024". Spesso funziona, ma è una soluzione fragile: il foglio di calcolo potrebbe interpretarlo come testo oppure scambiare l'ordine di giorno e mese.

Lo schema più affidabile è ">="&DATE(2024,1,1). La funzione DATE crea un vero numero seriale e & concatena l'operatore a quel numero. Questo funziona in modo affidabile sia in Excel sia in Google Sheets, con qualsiasi impostazione locale.

Le date inserite direttamente nella formula vanno bene per un report fisso, ma un report flessibile legge l'inizio e la fine da alcune celle. Inserire la data di inizio in F1 e quella di fine in F2.

A questo punto il periodo dipende dal foglio. Modificando F1 o F2, ogni totale viene ricalcolato. Come sempre, unire l'operatore alla cella con &: non inserire mai il nome della cella tra virgolette.

A volte è necessario un solo limite. Tutto a partire da una data usa un'unica condizione di maggiore o uguale. Tutto fino a una data usa un'unica condizione di minore o uguale.

Questa formula conta tutti gli ordini effettuati nella data indicata in F1 o dopo, senza alcun limite superiore: è utile per metriche come le "vendite dal lancio".

Le condizioni sulle date possono essere combinate liberamente con condizioni sul testo e sui numeri. Per ottenere il totale delle vendite della regione Est all'interno di un intervallo di date, aggiunga la coppia relativa alla regione alle due coppie relative alle date.

L'ordine non influisce sul risultato: Excel valuta tutte le condizioni come un unico grande AND. In questo caso, le tre coppie di criteri condividono lo stesso average_range o sum_range.

Per ottenere il totale di un intero anno, imposti i limiti sul primo e sull'ultimo giorno dell'anno. Combini DATE per l'inizio con la fine del periodo.

Per un singolo mese, utilizzi il primo giorno del mese come limite inferiore e il primo giorno del mese successivo con un "<" stretto come limite superiore: un metodo efficace per non doversi preoccupare che il mese abbia 28, 30 o 31 giorni.

Per i report a scorrimento, definisca i limiti a partire da TODAY(). Per contare gli ordini degli ultimi 30 giorni, il limite inferiore è oggi meno 30 e quello superiore è oggi.

Poiché TODAY() si aggiorna ogni giorno in cui il foglio viene ricalcolato, l'intervallo scorre automaticamente in avanti: non è necessaria alcuna modifica manuale.

Se la colonna delle date contiene in realtà data e ora, come in un timestamp, una riga con un orario del 31 gennaio avrà un numero seriale leggermente superiore al valore del giorno intero 31 gennaio. Un limite "<="&DATE(2024,1,31) la escluderebbe.

La soluzione sicura è lo schema del giorno successivo con minore stretto: "<"&DATE(2024,2,1) include ogni istante di gennaio, compresi i timestamp.

Lo stesso schema degli intervalli di date funziona con AVERAGEIFS. Per trovare il valore medio degli ordini all'interno di un intervallo, indichi la colonna degli importi come average_range e aggiunga le due condizioni sulle date alla colonna delle date.

Ricordi il problema dei periodi vuoti: se non ci sono ordini nelle date indicate, AVERAGEIFS restituisce #DIV/0!. Racchiuderla in IFERROR mantiene ordinato un dashboard filtrato per periodo anche quando non ci sono dati.

Ricordi il modo più affidabile per confrontare una data nelle funzioni con criteri.

Ora è possibile filtrare SUMIFS, COUNTIFS e AVERAGEIFS in base al tempo:

• Un intervallo di date è formato da due condizioni sulla stessa colonna di date (>= inizio e <= fine).
• Crei le date con DATE(y,m,d) e aggiunga gli operatori usando ">="&.
• Per i mesi, utilizzi come limite superiore il giorno successivo con minore stretto ("<"&DATE(...)) per gestire i timestamp.
• Utilizzi TODAY() per intervalli a scorrimento come gli ultimi 30 giorni.

Con questo si conclude la famiglia IFS per i criteri multipli.