Sommario
Per sommare per mese in colonne è possibile utilizzare la funzione SUMIFS insieme alla funzione EOMONTH. Nell'esempio mostrato, la formula in G5 è:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Questa formula utilizza tre intervalli denominati: "importo" (G5: D15), "cliente" (B5: B15) e "data" (C5: C15).
Spiegazione
La funzione SUMIFS è progettata per sommare i valori in un intervallo basato su uno o più criteri. In questo caso, abbiamo bisogno di tre criteri:
- Nome cliente = cliente nella colonna F
- Data> = primo del mese (dalla data nella riga 4)
- Data <= fine mese (dalla data nella riga 4)
Punto chiave: i nomi dei mesi in G4: I4 sono effettivamente date valide, formattate con il formato numerico personalizzato "mmm". Ciò ci consente di utilizzare i valori di data in G4: I4 direttamente per i criteri n. 2 e n. 3 sopra.
Di nuovo in SUMIFS, la prima coppia intervallo / criteri viene utilizzata per controllare il nome del cliente:
client,$F5
Si noti che F5 è un riferimento misto, con la colonna bloccata. Ciò consente alla riga di cambiare mentre la formula viene copiata nella tabella, ma il nome del client proviene sempre dalla colonna F.
La seconda coppia intervallo / criteri viene utilizzata per controllare le date rispetto al primo del mese:
date,">="&G$4
Come accennato in precedenza, i valori in G4: I4 sono in realtà le date: 1 gennaio 2019, 1 febbraio 2019 e 1 marzo 2019. Pertanto, questo criterio controlla semplicemente le date superiori alla prima delle date del mese nella riga 4. Si noti che questo è un altro riferimento misto, questa volta con la riga bloccata. Ciò consente alla colonna di cambiare mentre la formula viene copiata nella tabella, ma mantiene fisso il numero di riga. La concatenazione con una e commerciale (&) è necessaria quando si creano criteri che utilizzano un operatore logico e un valore da un'altra cella.
La terza coppia intervallo / criteri viene utilizzata per controllare le date rispetto all'ultimo giorno del mese:
date,"<="&EOMONTH(G$4,0)
Per ottenere l'ultimo giorno di ogni mese, utilizziamo la funzione EOMONTH sulla data dall'intestazione della colonna nella riga 4. Con zero fornito per l'argomento mesi, EOMONTH restituisce l'ultimo giorno dello stesso mese. Anche in questo caso, il riferimento a G4 è misto per evitare che la riga cambi.
Soluzione tabella pivot
Una tabella pivot sarebbe un'ottima soluzione per questo problema, perché può raggrupparsi automaticamente per mese senza alcuna formula. Per un confronto affiancato di formule e tabelle pivot, guarda questo video: Perché le tabelle pivot.
