Scarica la cartella di lavoro di esempio
Questo tutorial dimostrerà come calcolare i subtotali per gruppo utilizzando la funzione SUMIFS in Excel e Fogli Google.
Tabella dei subtotali per categoria o gruppo
Innanzitutto, dimostreremo come creare una tabella di riepilogo del totale parziale dinamico da un intervallo di dati in Excel 365 in poi o Fogli Google.
Usiamo la funzione UNIQUE e la funzione SUMIFS per subtotale automaticamente il Numero di prodotti di Gruppo di prodotti:
| 1 | =SOMMA.SE(C3:C11,B3:B11,E3) |
Per creare questa tabella dei subtotali, utilizziamo l'applicazione standard della funzione SUMIFS per sommare i Numero di prodotti che corrispondono a ciascuno Gruppo di prodotti. Tuttavia, prima che ciò sia possibile, dobbiamo creare un elenco di unici Gruppi di prodotti. Gli utenti di Microsoft Excel 365 e Fogli Google hanno accesso alla funzione UNIQUE per creare un elenco dinamico di valori univoci da un intervallo di celle. In questo esempio, aggiungiamo la seguente formula alla cella E3:
| 1 | =UNICO(B3:B11) |
Quando viene inserita questa formula, viene creato automaticamente un elenco sotto la cella per mostrare tutti i valori univoci trovati all'interno del Gruppo di prodotti intervallo di dati. In questo esempio, l'elenco si è esteso per coprire E3: E5 per mostrare tutti e 3 gli unici Gruppo di prodotti valori.
Questa è una funzione di matrice dinamica in cui non è necessario definire la dimensione dell'elenco dei risultati e si ridurrà e aumenterà automaticamente man mano che i valori dei dati di input cambiano.
Nota che in Excel 365, la funzione UNICA non fa distinzione tra maiuscole e minuscole, ma in Fogli Google lo è. Considera la lista {“A”; "un"; "B"; "C"}. L'output della funzione UNIQUE dipende dal programma:
- {"UN"; "B"; "c"} in Excel 365
- {"UN"; "un"; "B"; "c"} in Fogli Google
Se stai utilizzando una versione di Excel precedente a Excel 365, dovrai adottare un approccio diverso. Questo è discusso nella prossima sezione.
Tabella dei subtotali per categoria o gruppo - Pre Excel 365
Se si utilizza una versione di Excel precedente a Excel 365, la funzione UNICA non è disponibile per l'uso. Per replicare lo stesso comportamento, puoi combinare la funzione INDICE e la funzione CONFRONTA con una funzione CONTA.SE per creare una formula di matrice per produrre un elenco di valori univoci da un intervallo di celle:
| 1 | {=INDICE($B$3:$B$11,MATCH(0,COUNTIF($E$2:E2,$B$3:$B$11),0))} |
Affinché questa formula funzioni, i riferimenti di cella fissi devono essere scritti con attenzione, con la funzione COUNTIF che fa riferimento all'intervallo $ E $ 2: E2, che è l'intervallo che inizia da E2 fino alla cella sopra la cella contenente la formula.
La formula deve anche essere inserita come formula di matrice premendo CTRL + MAIUSC + INVIO dopo che è stata scritta. Questa formula è a Formula matrice di 1 cella, che può quindi essere copiato e incollato nelle celle E4, E5 ecc. Non inserirlo come formula di matrice per l'intero intervallo E3: E5 in un'unica azione.
Allo stesso modo dell'esempio precedente, viene quindi utilizzata una funzione SOMMA.PI.SE per il subtotale di Numero di prodotti di Gruppo di prodotti:
| 1 | =SOMMA.SE(C3:C11,B3:B11,E3) |
Somma per categoria o gruppo - Subtotali nelle tabelle dati
In alternativa al metodo della tabella riassuntiva mostrato sopra, possiamo aggiungere subtotali direttamente in una tabella dati. Lo dimostreremo usando le funzioni IF insieme alla funzione SUMIFS per aggiungere a Subtotale per gruppo alla tabella dati originale.
| 1 | =SE(B3=B2,"",SOMMA.SE(C3:C11,B3:B11,B3)) |
Questo esempio usa una funzione SUMIFS nidificata all'interno di una funzione IF. Scomponiamo l'esempio in passaggi:
Per aggiungere statistiche di riepilogo direttamente in una tabella di dati, possiamo utilizzare la funzione SUMIFS. Iniziamo con la somma di Numero di prodotti che corrispondono al pertinente Gruppo di prodotti:
| 1 | =SOMMA.SE(C3:C11,B3:B11,B3) |
Questa formula produce un valore subtotale per ogni riga di dati. Per mostrare i subtotali solo nella prima riga di dati di ciascuno Gruppo di prodotti, usiamo la funzione SE. Nota che i dati devono essere già ordinati per Gruppo di prodotti per garantire che i subtotali vengano visualizzati correttamente.
| 1 | =SE(B3=B2,"",SOMMA.SE(C3:C11,B3:B11,B3)) |
La funzione IF confronta ogni riga di dati Gruppo di prodotti valore con la riga di dati sopra di esso e, se hanno lo stesso valore, restituisce una cella vuota ("").
Se la Gruppo di prodotti i valori sono diversi, viene visualizzata la somma. In questo modo, ciascuno Gruppo di prodotti sum viene visualizzato solo una volta (sulla riga della sua prima istanza).
Ordinamento dei set di dati per gruppo
Se i dati non sono già ordinati, possiamo comunque utilizzare la stessa formula per il subtotale.
Il set di dati sopra non è ordinato per Gruppo di prodotti, così il Subtotale per gruppo colonna visualizza ogni subtotale più di una volta. Per ottenere i dati nel formato che desideriamo, possiamo selezionare la tabella dati e fare clic su "Ordina dalla A alla Z".
Riferimenti di cella di blocco
Per rendere le nostre formule più facili da leggere, abbiamo mostrato alcune delle formule senza riferimenti di cella bloccati:
| 1 | =SE(B3=B2,"",SOMMA.SE(C3:C11,B3:B11,B3)) |
Ma queste formule non funzioneranno correttamente se copiate e incollate altrove nel file. Invece, dovresti usare riferimenti di cella bloccati come questo:
| 1 | =SE(B3=B2,"",SOMMA.SE($C$3:$C$11,$B$3:$B$11,B3)) |
Leggi il nostro articolo sul blocco dei riferimenti di cella per saperne di più.
Utilizzo delle tabelle pivot per mostrare i subtotali
Al fine di rimuovere l'obbligo di preordinare i dati per Gruppo di prodotti, possiamo invece utilizzare la potenza delle tabelle pivot per riepilogare i dati. Le tabelle pivot calcolano automaticamente i subtotali e visualizzano totali e subtotali in diversi formati.
Somma per categoria o gruppo in Fogli Google
Queste formule funzionano allo stesso modo in Fogli Google come in Excel. Tuttavia, la funzione UNIQUE fa distinzione tra maiuscole e minuscole in Fogli Google.
