Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come utilizzare le funzioni SUMPRODUCT e SUMIFS per sommare i dati che soddisfano determinati criteri su più fogli in Excel e Fogli Google.
Somma regolare su più fogli
A volte i tuoi dati potrebbero estendersi su più fogli di lavoro in un file Excel. Questo è comune per i dati che vengono raccolti periodicamente. Ogni foglio in una cartella di lavoro potrebbe contenere dati per un determinato periodo di tempo. Vogliamo una formula che sommi i dati contenuti in due o più fogli.
La funzione SOMMA consente di sommare facilmente i dati su più fogli utilizzando a Riferimento 3D:
| 1 | =SOMMA(Foglio1:Foglio2!A1) |
Tuttavia, questo non è possibile con la funzione SUMIFS. Invece dobbiamo usare una formula più complicata.
Somma se su più fogli
Questo esempio sommerà il Numero di consegne pianificate per ciascuno Cliente su più fogli di lavoro, ciascuno contenente dati relativi a un mese diverso, utilizzando le funzioni SUMIFS, SUMPRODUCT e INDIRETTO:
| 1 | =SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&F3:F6&"'!"&"D3:D7"),INDIRETTO("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Esaminiamo questa formula.
Passaggio 1: creare una formula SUMIFS solo per 1 foglio di input:
Usiamo la funzione SUMIFS per sommare i Numero di consegne pianificate di Cliente per un singolo foglio dati di input:
| 1 | =SOMMA.SE(RE3:RE7,DO3:DO7,H3) |
Passaggio 2: aggiungere un riferimento al foglio alla formula
Manteniamo lo stesso risultato della formula, ma specifichiamo che i dati di input sono nel foglio chiamato 'Passo 2'
| 1 | =SOMMA.SE('Passaggio 2'!D3:D7,'Passaggio 2'!C3:C7,H3) |
Passaggio 3: nidificare all'interno di una funzione SUMPRODUCT
Per preparare la formula per eseguire calcoli SUMIFS su più fogli e quindi per sommare i risultati, aggiungiamo una funzione SUMPRODUCT attorno alla formula
| 1 | =SOMMA.PRODOTTO(SOMMA.SE('Fase 3'!D3:D7,'Fase 3'!C3:C7,H3)) |
L'uso della funzione SUMIFS su un foglio produce un singolo valore. Su più fogli, la funzione SUMIFS emette una matrice di valori (uno per ogni foglio di lavoro). Usiamo la funzione SUMPRODUCT per sommare i valori in questa matrice.
Passaggio 4: sostituire il riferimento del foglio con un elenco di nomi di fogli
Desideriamo sostituire il Nome del foglio parte della formula con un elenco di dati contenente i valori: Jan, febbraio, Mar, e aprile. Questo elenco è memorizzato nelle celle F3:F6.
La funzione INDIRETTA assicura che l'elenco di testo venga visualizzato Nomi dei fogli viene trattato come parte di un riferimento di cella valido nella funzione SUMIFS.
| 1 | =SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&F3:F6&"'!"&"D3:D7"),INDIRETTO("'"&F3:F6&"'!"&"C3:C7"),H3)) |
In questa formula, il riferimento all'intervallo precedentemente scritto:
| 1 | 'Fase 3'!D3:D7 |
è sostituito da:
| 1 | INDIRETTO("'"&F3:F6&"'!"&"D3:D7") |
Le virgolette rendono la formula difficile da leggere, quindi qui viene mostrata con spazi aggiunti:
| 1 | INDIRETTO ( " ' " & F3:F6 & " ' ! " & "D3: D7" ) |
L'utilizzo di questo modo di referenziare un elenco di celle ci consente anche di riepilogare i dati di più fogli che non seguono uno stile di elenco numerico. Un riferimento 3D standard richiederebbe che i nomi dei fogli siano nello stile: Input1, Input2, Input3, ecc., ma l'esempio sopra consente di utilizzare un elenco di qualsiasi Nomi dei fogli e per averli referenziati in una cella separata.
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(SOMMA.SE(INDIRETTO("'"&F3:F6&"'!"&"D3:D7"),INDIRETTO("'"&F3:F6&"'!"&"C3:C7"),H3)) |
Ma queste formule non funzioneranno correttamente se copiate e incollate altrove nel file. Invece, dovresti usare riferimenti di cella bloccati come questo:
| 1 | =SOMMA.PRODOTTO(SOMMA.SE(INDIRETTO("'"&$F$3:$F$6&"'!"&"D3:G7"),INDIRETTO("'"&$F$3:$F$6&"'!"& "DO3:DO7"),LA3)) |
Leggi il nostro articolo sul blocco dei riferimenti di cella per saperne di più.
Somma se su più fogli in Fogli Google
L'utilizzo della funzione INDIRETTO per fare riferimento a un elenco di fogli in una funzione SUMPRODUCT e SUMIFS non è attualmente possibile in Fogli Google.
Invece, è possibile eseguire calcoli SOMMA.PI.SE separati per ciascun foglio di input e i risultati sommati:
| 1234 | =SOMMA.SE(Gen!D3:G7,Gen!C3:C7,H3)+SOMMA.SE(Feb! RE3: RE7, Febbraio! DO3: DO7, LA3)+SOMMA.SE(Mar!D3:D7,Mar!C3:C7,H3)+SOMMA.SE(Apr!G3:G7,Apr!C3:Do7,H3) |
