Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come sommare i dati corrispondenti a numeri di settimane specifici in Excel e Fogli Google.
Somma se per numero di settimana
Per "somma se" in base al numero della settimana, utilizzeremo la funzione SUMIFS. Ma prima dobbiamo aggiungere una colonna di supporto contenente la funzione WEEKNUM.
Il Numero della settimana la colonna helper viene calcolata utilizzando la funzione WEEKNUM:
| 1 | =NUM.SETTIMANA(B3,1) |
Successivamente, useremo la funzione SUMIFS per sommare tutto Saldi che si svolgono in uno specifico Numero della settimana.
| 1 | =SOMMA.SE(RE3:RE9,DO3:DO9,FA3) |
Somma se per numero settimana - Senza colonna Helper
Il metodo della colonna di supporto è facile da seguire, ma puoi anche replicare il calcolo in un'unica formula utilizzando la funzione SUMPRODUCT in combinazione con la funzione WEEKNUM per sommare il Numero totale di vendite di Numero della settimana.
| 1 | =SOMMA.PRODOTTO(--(NUM.SETTIMANA(B3:B9+0,1)=E3),C3:C9) |
In questo esempio, possiamo utilizzare la funzione SUMPRODUCT per eseguire complicati calcoli "somma se". Esaminiamo l'esempio precedente.
Questa è la nostra formula finale:
| 1 | =SOMMA.PRODOTTO(--(NUM.SETTIMANA(B3:B9+0,1)=E3),C3:C9) |
Innanzitutto, la funzione SUMPRODUCT elenca l'array di valori dagli intervalli di celle:
| 1 | =(--(({"1/3/2020"; "1/6/2020"; "1/9/2020"; "1/12/2020"; "1/15/2020"; "1/18/2020"; "1/21/2020"}+0,1)=1), {4; 9; 1; 7; 6; 2; 5}) |
Quindi, la funzione WEEKNUM calcola il Numero della settimana di ciascuno dei Date di vendita.
La funzione WEEKNUM non è progettata per funzionare con i valori dell'array, quindi dobbiamo aggiungere zero ("+0") affinché WEEKNUM elabori correttamente i valori.
| 1 | =SOMMA.PRODOTTO(--({1; 2; 2; 3; 3; 3; 4}=1), {4; 9; 1; 7; 6; 2; 5}) |
Numero della settimana i valori uguali a 1 vengono modificati in valori TRUE.
| 1 | =SOMMA.PRODOTTO(--({VERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}), {4; 9; 1; 7; 6; 2; 5}) |
Successivamente i doppi trattini (-) convertono i valori VERO e FALSO in 1 e 0:
| 1 | =SOMMA.PRODOTTO({1; 0; 0; 0; 0; 0; 0}, {4; 9; 1; 7; 6; 2; 5}) |
La funzione SUMPRODUCT quindi moltiplica ogni coppia di voci negli array per produrre un array di Numero di vendite che hanno un Numero della settimana di 1:
| 1 | =SOMMA.PRODOTTO({4; 0; 0; 0; 0; 0; 0}) |
Infine, i numeri nell'array vengono sommati insieme:
| 1 | =4 |
Questa formula viene poi ripetuta per gli altri possibili valori di Numero della settimana.
Maggiori dettagli sull'utilizzo delle istruzioni booleane e del comando "-" in una funzione SUMPRODUCT sono disponibili qui.
Riferimenti di cella di blocco
Per rendere le nostre formule più facili da leggere, abbiamo mostrato le formule senza riferimenti di cella bloccati:
| 1 | =SOMMA.PRODOTTO(--(NUM.SETTIMANA(B3:B9+0,1)=E3),C3:C9) |
Ma queste formule non funzioneranno correttamente se copiate e incollate altrove nel tuo file. Invece, dovresti usare riferimenti di cella bloccati come questo:
| 1 | =SOMMA.PRODOTTO(--(NUM.SETTIMANA($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
Leggi il nostro articolo sul blocco dei riferimenti di cella per saperne di più.
Somma se per numero di settimana in Fogli Google
Queste formule funzionano esattamente allo stesso modo in Fogli Google come in Excel.
Tuttavia, la funzione WEEKNUM è più flessibile in Fogli Google che in Excel e accetta input e output di array. Pertanto l'operazione {Array}+0 nella formula WEEKNUM(B3:B9+0,1) non è richiesta.
La formula SUMPRODUCT completa può essere scritta in Fogli Google come:
| 1 | =SUMPRODOTTO(--(NUM.SETTIMANA($B$3:$B$9+0,1)=E3),$C$3:$C$9) |
