Molte volte, vogliamo calcolare alcuni valori per mese. Ad esempio, quante vendite sono state fatte in un mese specifico. Bene, questo può essere fatto facilmente usando le tabelle pivot, ma se stai cercando di avere un rapporto dinamico, possiamo usare una formula SUMPRODUCT o SUMIFS per sommare per mese.
Iniziamo con la soluzione SUMPRODUCT.
Ecco la formula generica per ottenere la somma per mese in Excel
=SUMPRODOTTO(intervallo_somma,--( TESTO(intervallo_date,"MMM")=testo_mese))
Somma_intervallo : è l'intervallo che si desidera sommare per mese.
Intervallo di date : è l'intervallo di date in cui guarderai per mesi.
Mese_testo: è il mese in formato testo di cui si vogliono sommare i valori.
Ora vediamo un esempio:
Esempio: somma dei valori per mese in Excel
Qui abbiamo un valore associato alle date. Queste date sono del mese di gennaio, febbraio e marzo dell'anno 2019.
Come puoi vedere nell'immagine sopra, tutte le date sono dell'anno 2019. Ora dobbiamo solo sommare i valori in E2: G2 per mesi in E1: G1.
Ora per sommare i valori in base ai mesi scrivi questa formula in E2:
=SOMMA.PRODOTTO(B2:B9,--(TESTO(A2:A9,"MMM")=E1)))
Se vuoi copiarlo in celle adiacenti, usa i riferimenti assoluti o gli intervalli denominati come nell'immagine.
Questo ci dà la somma esatta di ogni mese.
Come funziona?
Partendo dall'interno, diamo un'occhiata al TESTO(A2:A9,"MMM") parte. Qui la funzione TEXT estrae il mese da ogni data nell'intervallo A2: A9 in formato testo in una matrice. Traduzione in formula in =SUMPRODUCT(B2:B9,--({"Gen";"Gen";"Feb";"Gen";"Feb";"Mar";"Gen";"Feb"}=E1) )
Successivamente, TESTO(A2:A9,"MMM")=MI1: qui ogni mese nell'array viene confrontato con il testo in E1. Poiché E1 contiene "Jan", ogni "Jan" nell'array viene convertito in TRUE e l'altro in FALSE. Questo traduce la formula in =SUMPRODUCT($B$2:$B$9,--{VERO;VERO;FALSO;VERO;FALSO;FALSO;VERO;FALSO})
Successivo --(TEXT(A2:A9,"MMM")=E1), converte TRUE FALSE in valori binari 1 e 0. La formula si traduce in =SUMPRODUCT($B$2:$B$9,{1;1;0; 1;0;0;1;0}).
Finalmente SUMPRODOTTO($B$2:$B$9,{1;1;0;1;0;0;1;0}): la funzione SUMPRODUCT moltiplica i valori corrispondenti in $B$2:$B$9 nella matrice {1;1; 0;1;0;0;1;0} e li somma. Quindi otteniamo la somma per valore come 20052 in E1.
SOMMA SE mesi di un anno diverso
Nell'esempio sopra, tutte le date erano dello stesso anno. E se fossero di anni diversi? La formula precedente sommerà i valori per mese indipendentemente dall'anno. Ad esempio, verranno aggiunti gennaio 2018 e gennaio 2019, se utilizziamo la formula sopra. Che è sbagliato nella maggior parte dei casi.
Ciò accadrà perché non abbiamo alcun criterio per l'anno nell'esempio precedente. Se aggiungiamo anche i criteri dell'anno, funzionerà.
La formula generica per ottenere la somma per mese e anno in Excel
=SUMPRODUCT(intervallo_somma,--( TEXT(intervallo_date,"MMM")=testo_mese),--( TEXT(intervallo_date,"aaaa")=TEXT(anno,0)))
Qui abbiamo aggiunto un altro criterio che controlla l'anno. Tutto il resto è uguale.
Risolviamo l'esempio precedente, scriviamo questa formula nella cella E1 per ottenere la somma di Jan nell'anno 2017.
=SOMMA.PRODOTTO(B2:B9,--(TESTO(A2:A9,"MMM")=E1),--(TESTO(A2:A9,"aaaa")=TESTO(D2,0)))
Prima di copiare nelle celle sottostanti, utilizzare intervalli denominati o riferimenti assoluti. Nell'immagine, ho utilizzato intervalli denominati per la copia nelle celle adiacenti.
Ora possiamo vedere la somma del valore anche per mesi e anni.
Come funziona?
La prima parte della formula è la stessa dell'esempio precedente. Consente di comprendere la parte aggiuntiva che aggiunge i criteri dell'anno.
--(TEXT(A2:A9,"yyyy")=TEXT(D2,0)): TEXT(A2:A9,"yyyy") converte la data in A2:A9 come anni in formato testo in una matrice. {"2018";"2019";"2017";"2017";"2019";"2017";"2019";"2017"}.
La maggior parte delle volte, l'anno è scritto in formato numerico. Per confrontare un numero con il testo, ho convertito il testo dell'anno int utilizzando TEXT (D2,0). Successivamente abbiamo confrontato questo anno di testo con una matrice di anni come TEXT(A2:A9,"yyyy")=TEXT(D2,0). Questo restituisce un array di vero-falso {FALSO;FALSO;VERO;VERO;FALSO;VERO;FALSO;VERO}. Successivamente abbiamo convertito true false in number usando -- operator. Questo ci dà {0;0;1;1;0;1;0;1}.
Quindi alla fine la formula verrà tradotta in =SUMPRODUCT(B2:B9,{1;1;0;1;0;0;1;0},{0;0;1;1;0;1;0;1 }). Dove il primo array è i valori. Il prossimo è il mese abbinato e il terzo è l'anno. Alla fine otteniamo la nostra somma di valori come 2160.
Utilizzo della funzione SUMIFS per sommare per mese
Formula generica
=SUMIFS(intervallo_somma,intervallo_date”,>=” & data di inizio, intervallo_di date,”<=” & EOMESE(data_inizio,0))
Qui,Somma_intervallo : è l'intervallo che si desidera sommare per mese.
Intervallo di date : è l'intervallo di date in cui guarderai per mesi.
Data d'inizio : È la data di inizio da cui si desidera sommare. Per questo esempio, sarà il 1° del mese indicato.
Esempio: somma dei valori per mese in Excel
Qui abbiamo un valore associato alle date. Queste date sono del mese di gennaio, febbraio e marzo dell'anno 2019.
Abbiamo solo bisogno di sommare questi valori entro il mese. Ora era facile se avessimo mesi e anni separati. Ma non lo sono. Non possiamo utilizzare alcuna colonna di aiuto qui.
Quindi, per preparare il report, ho preparato un formato di report che contiene il mese e la somma dei valori. Nella colonna del mese, ho effettivamente una data di inizio del mese. Per vedere solo il mese, seleziona la data di inizio e premi CTRL+1.
In formato personalizzato, scrivi “mmm”.
Ora abbiamo i nostri dati pronti. Sommiamo i valori per mese.
Scrivi questa formula in E3 per sommare per mese.
=SOMMA.SE(B3:B10,A3:A10,">="&D3,A3:A10,"<="&EOMESE(D3,0))
Utilizzare riferimenti assoluti o intervalli denominati prima di copiare la formula.
Quindi, finalmente abbiamo ottenuto il risultato.
Allora, come funziona?
Come sappiamo, la funzione SUMIFS può sommare valori su più criteri.
Nell'esempio sopra, il primo criterio è sommare tutti i valori in B3:B10 dove, la data in A3:A10 è maggiore o uguale alla data in RE3. D3 contiene 1-gen. Anche questo si traduce.
=SOMMA.SE(B3:B10,A3:A10,">="& “1-gen-2019” ,A3:A10,"<="EOMESE(D3,0))
Il criterio successivo è la somma solo se Data in A3: A10 è minore o uguale a EOMONTH(D3,0). EOMONTH La funzione restituisce solo il numero di serie dell'ultima data del mese fornita. Finalmente anche la formula si traduce.
=SOMMA.SE(B3:B10,A3:A10,">=1-gen-2019” ,A3:A10,"<=31-gen-2019”)
Quindi, otteniamo la somma per mese in Excel.
Il vantaggio di questo metodo è che puoi regolare la data di inizio per sommare i valori.
Se le tue date hanno anni diversi da quello che è meglio usare le tabelle pivot. Le tabelle pivot possono aiutarti a separare facilmente i dati in formato annuale, trimestrale e mensile.
Quindi sì ragazzi, ecco come potete sommare i valori per mese. Entrambi i modi hanno le loro specialità. Scegli il modo che preferisci.
Se hai domande su questo articolo o su qualsiasi altra domanda relativa a Excel e VBA, la sezione commenti è aperta per te.
Articoli correlati:
Come utilizzare la funzione SOMMA.SE in Excel
SOMMA.ME con date in Excel
SOMMA.SE con celle non vuote
Come utilizzare la funzione SOMMA.PI.SE in Excel
SUMIF utilizzando la logica AND-OR
Articoli popolari
50 scorciatoie Excel per aumentare la produttività: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel: questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel: Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel: Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.