Totale parziale (chiamato anche somma cumulativa) è abbastanza comunemente usato in molte situazioni. È una metrica che ti dice qual è la somma dei valori finora.
Ad esempio, se disponi dei dati sulle vendite mensili, un totale parziale ti dirà quante vendite sono state effettuate fino a un giorno specifico dal primo giorno del mese.
Ci sono anche alcune altre situazioni in cui viene spesso utilizzato il totale parziale, come il calcolo del saldo di cassa nei tuoi estratti conto/contabilità bancaria, il conteggio delle calorie nel tuo piano alimentare, ecc.
In Microsoft Excel esistono diversi modi per calcolare i totali parziali.
Il metodo che scegli dipende anche da come sono strutturati i tuoi dati.
Ad esempio, se si dispone di dati tabulari semplici, è possibile utilizzare una semplice formula SUM, ma se si dispone di una tabella Excel, è meglio utilizzare riferimenti strutturati. Puoi anche usare Power Query per farlo.
In questo tutorial, tratterò tutti questi diversi metodi per calcolare i totali parziali in Excel.
Quindi iniziamo!
Calcolo del totale parziale con dati tabulari
Se disponi di dati tabulari (ad esempio una tabella in Excel che non viene convertita in una tabella Excel), puoi utilizzare alcune semplici formule per calcolare i totali parziali.
Utilizzo dell'operatore di addizione
Supponiamo di avere dati di vendita per data e di voler calcolare il totale parziale nella colonna C.
Di seguito sono riportati i passaggi per farlo.
Passo 1 - Nella cella C2, che è la prima cella in cui desideri il totale parziale, inserisci
=B2
Questo otterrà semplicemente gli stessi valori di vendita nella cella B2.
Passo 2 - Nella cella C3, inserisci la formula seguente:
=C2+B3
Passaggio 3 - Applicare la formula all'intera colonna. Puoi usare il quadratino di riempimento per selezionarlo e trascinarlo, o semplicemente e copiare e incollare la cella C3 su tutte le celle rimanenti (che regolerebbe automaticamente il riferimento e darebbe il risultato giusto).
Questo ti darà il risultato come mostrato di seguito.
È un metodo davvero semplice e funziona bene nella maggior parte dei casi.
La logica è semplice: ogni cella preleva il valore sopra di essa (che è la somma cumulativa fino alla data precedente) e aggiunge il valore nella cella adiacente (che è il valore di vendita per quel giorno).
C'è solo un inconveniente: nel caso in cui elimini una delle righe esistenti in questo set di dati, tutte le celle sottostanti restituirebbero un errore di riferimento (#REF!)
Se questa è una possibilità con il tuo set di dati, usa il metodo successivo che utilizza la formula SUM
Utilizzo di SUM con riferimento a celle parzialmente bloccate
Supponiamo di avere dati di vendita per data e di voler calcolare il totale parziale nella colonna C.
Di seguito è riportata la formula SUM che ti darà il totale parziale.
=SOMMA($B$2:B2)
Lascia che ti spieghi come funziona questa formula.
Nella formula SUM sopra, ho usato il riferimento per aggiungere come $ B $ 2: B2
- $ B $ 2 - questo è un riferimento assoluto, il che significa che quando copio la stessa formula nelle celle sottostanti, questo riferimento non cambierà. Quindi, quando si copia la formula nella cella sottostante, la formula cambia in SUM($B$2:B3)
- B2 - questa è la seconda parte del riferimento che è un riferimento relativo, il che significa che questo si regolerebbe mentre copio la formula in basso oa destra. Quindi, quando si copia la formula nella cella sottostante, questo valore diventerà B3
Leggi anche: Riferimenti di cella assoluti, relativi e misti in Excel
La cosa grandiosa di questo metodo è che nel caso in cui elimini una qualsiasi delle righe nel set di dati, questa formula si regolerebbe e ti darebbe comunque i totali correnti corretti.
Calcolo del totale parziale nella tabella di Excel
Quando si lavora con dati tabulari in Excel è una buona idea convertirli in una tabella Excel. Rende molto più semplice la gestione dei dati e consente inoltre di semplificare l'uso di strumenti come Power Query e Power Pivot.
L'utilizzo delle tabelle di Excel offre vantaggi come i riferimenti strutturati (che rende davvero facile fare riferimento ai dati nella tabella e utilizzarli nelle formule) e la regolazione automatica dei riferimenti nel caso in cui si aggiungano o eliminino dati dalla tabella.
Sebbene tu possa ancora utilizzare la formula sopra che ti ho mostrato in una tabella di Excel, lascia che ti mostri alcuni metodi migliori per farlo.
Supponiamo di avere una tabella Excel come mostrato di seguito e di voler calcolare il totale parziale nella colonna C.
Di seguito è la formula che farà questo:
=SOMMA(DatiVendite[[#Intestazioni],[Vendite]]:[@Vendite])
La formula sopra può sembrare un po' lunga, ma non è necessario scriverla da soli. ciò che vedi all'interno della formula di somma è chiamato riferimenti strutturati, che è il modo efficiente di Excel per fare riferimento a punti dati specifici in una tabella di Excel.
Ad esempio, SalesData[[#Headers],[Sale]] si riferisce all'intestazione Sale nella tabella SalesData (SalesData è il nome della tabella Excel che ho fornito quando ho creato la tabella)
E [@Sale] si riferisce al valore nella cella nella stessa riga nella colonna Sale.
L'ho appena spiegato qui per la tua comprensione, ma anche se non sai nulla sui riferimenti strutturati, puoi comunque creare facilmente questa formula.
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Nella cella C2, inserisci =SOMMA(
- Seleziona la cella B1, che è l'intestazione della colonna che ha il valore di vendita. Puoi usare il mouse o usare i tasti freccia. Noterai che Excel inserisce automaticamente il riferimento strutturato per quella cella
- Aggiungi un : (simbolo dei due punti)
- Seleziona la cella B2. Excel inserirebbe di nuovo automaticamente il riferimento strutturato per la cella
- Chiudi la parentesi e premi invio
Noterai anche che non devi copiare la formula nell'intera colonna, una tabella Excel lo fa automaticamente per te.
Un'altra cosa grandiosa di questo metodo è che nel caso in cui si aggiunga un nuovo record in questo set di dati, la tabella Excel calcolerà automaticamente il totale parziale per tutti i nuovi record.
Sebbene abbiamo incluso l'intestazione della colonna nella nostra formula, ricorda che la formula ignorerebbe il testo dell'intestazione e considererebbe solo i dati nella colonna
Calcolo del totale parziale utilizzando Power Query
Power Query è uno strumento straordinario quando si tratta di connettersi con i database, estrarre i dati da più origini e trasformarli prima di inserirli in Excel.
Se stai già lavorando con Power Query, sarebbe più efficiente aggiungere totali parziali mentre stai trasformando i dati all'interno dell'editor di Power Query stesso (invece di ottenere i dati in Excel e quindi aggiungere i totali parziali utilizzando uno dei precedenti metodi sopra descritti).
Sebbene non sia disponibile alcuna funzionalità integrata in Power Query per aggiungere totali parziali (che vorrei che ci fossero), puoi comunque farlo utilizzando una formula semplice.
Supponiamo di avere una tabella Excel come mostrato di seguito e di voler aggiungere i totali parziali a questi dati:
Di seguito sono riportati i passaggi per eseguire questa operazione:
- Seleziona qualsiasi cella nella tabella di Excel
- Fare clic su Dati
- Nella scheda Ottieni e trasforma, fai clic sull'icona da tabella/intervallo. Questo aprirà la tabella nell'editor di Power Query
- [Facoltativo] Nel caso in cui la colonna Data non sia già ordinata, fai clic sull'icona del filtro nella colonna Data, quindi fai clic su Ordina in ordine crescente
- Fare clic sulla scheda Aggiungi colonna nell'editor di Power Query
- Nel gruppo Generale, fai clic sul menu a discesa Colonna indice (non fare clic sull'icona Colonna indice, ma sulla piccola freccia nera inclinata accanto ad essa per mostrare più opzioni)
- Fare clic sull'opzione "Da 1". In questo modo si aggiungerà una nuova colonna indice che inizierebbe da uno e si inseriranno i numeri incrementando di 1 nell'intera colonna
- Fare clic sull'icona "Colonna personalizzata" (che si trova anche nella scheda Aggiungi colonna)
- Nella finestra di dialogo della colonna personalizzata che si apre, inserisci un nome per la nuova colonna. in questo esempio, userò il nome "Totale parziale"
- Nel campo Formula colonna personalizzata, inserisci la formula seguente: List.Sum(List.Range(#”Indice aggiunto”[Saldi],0,[Indice]))
- Assicurati che nella parte inferiore della finestra di dialogo sia presente una casella di controllo che dice: "Non sono stati rilevati errori di sintassi"
- Fare clic su OK. Ciò aggiungerebbe una nuova colonna del totale parziale
- Rimuovi la colonna dell'indice
- Fare clic sulla scheda File e quindi fare clic su "Chiudi e carica"
I passaggi precedenti inserirebbero un nuovo foglio nella cartella di lavoro con una tabella con i totali parziali.
Ora, se stai pensando che questi siano troppi passaggi rispetto ai metodi precedenti di utilizzo di formule semplici, hai ragione.
Se hai già un set di dati e tutto ciò che devi fare è aggiungere totali parziali, è meglio non usare Power Query.
L'uso di Power Query ha senso quando è necessario estrarre i dati da un database o combinare i dati da più cartelle di lavoro diverse e nel processo aggiungere anche i totali parziali.
Inoltre, una volta eseguita questa automazione utilizzando Power Query, la prossima volta che il set di dati viene modificato non è necessario farlo di nuovo, puoi semplicemente aggiornare la query e ti darebbe il risultato in base al nuovo set di dati.
Come funziona?
Ora lascia che ti spieghi rapidamente cosa succede in questo metodo.
La prima cosa che facciamo nell'editor di Power Query è inserire una colonna di indice a partire da uno e aumentando di uno man mano che scende nelle celle.
Lo facciamo perché dobbiamo usare questa colonna mentre calcoliamo il totale parziale in un'altra colonna che inseriamo nel passaggio successivo.
Quindi inseriamo una colonna personalizzata e utilizziamo la formula seguente
List.Sum(List.Range(#"Indice aggiunto"[Saldi],0,[Indice]))
Questa è una formula List.Sum che ti darebbe la somma dell'intervallo specificato al suo interno.
E quell'intervallo è specificato usando la funzione List.Range.
La funzione List.Range fornisce l'intervallo specificato nella colonna vendita come output e questo intervallo cambia in base al valore dell'indice. Ad esempio, per il primo record, l'intervallo sarebbe semplicemente il primo valore di vendita. E mentre scendi nelle celle, questo intervallo si espanderà.
Quindi, per la prima cella. List.Sum ti darebbe solo la somma del primo valore di vendita e per la seconda cella ti darebbe la somma per i primi due valori di vendita e così via.
Sebbene questo metodo funzioni bene, diventa molto lento con set di dati di grandi dimensioni: migliaia di righe. Se hai a che fare con un set di dati di grandi dimensioni e desideri aggiungere totali parziali, dai un'occhiata a questo tutorial che mostra altri metodi più veloci.Calcolo del totale parziale in base a criteri
Finora, abbiamo visto esempi in cui abbiamo calcolato il totale parziale per tutti i valori in una colonna.
Ma potrebbero esserci casi in cui si desidera calcolare il totale parziale per record specifici.
Ad esempio, di seguito ho un set di dati e voglio calcolare il totale parziale per stampanti e scanner separatamente in due colonne diverse.
Questo può essere fatto utilizzando una formula SOMMA.SE che calcola il totale parziale assicurandosi che la condizione specificata sia soddisfatta.
Di seguito è la formula che farà ciò per le colonne Stampante:
=SOMMA.SE($C$2:C2,$D$1,$B$2:B2)
Allo stesso modo, per calcolare il totale parziale per gli scanner, utilizzare la formula seguente:
=SOMMA.SE($C$2:C2,$E$1,$B$2:B2)
Nelle formule sopra, ho usato SUMIF, che mi darebbe la somma in un intervallo quando i criteri specificati sono soddisfatti.
La formula accetta tre argomenti:
- gamma: questo è l'intervallo di criteri che verrebbe verificato rispetto ai criteri specificati
- criteri: questo è il criterio che verrebbe verificato solo se questo criterio è soddisfatto, quindi i valori nel terzo argomento, che è l'intervallo di somma, verrebbero aggiunti
- [intervallo_somma]: questo è l'intervallo di somma da cui verrebbero aggiunti i valori se i criteri fossero soddisfatti
Inoltre, in gamma e somma_intervallo argomento, ho bloccato la seconda parte del riferimento, in modo che man mano che scendiamo nelle celle, l'intervallo continua ad espandersi. Questo ci consente di considerare e aggiungere solo valori fino a tale intervallo (quindi i totali correnti).
In questa formula, ho utilizzato la colonna di intestazione (stampante e scanner) come criteri. puoi anche codificare i criteri se le intestazioni delle colonne non sono esattamente uguali al testo dei criteri.
Nel caso in cui tu abbia più condizioni da verificare, puoi utilizzare la formula SOMMA.PI.SE.
Totale parziale nelle tabelle pivot
Se desideri aggiungere totali parziali in un risultato di una tabella pivot, puoi farlo facilmente utilizzando una funzionalità integrata nelle tabelle pivot.
Supponiamo di avere una tabella pivot come mostrato di seguito dove ho la data in una colonna e il valore di vendita nell'altra colonna.
Di seguito sono riportati i passaggi per aggiungere una colonna aggiuntiva che mostrerà il totale parziale delle vendite per data:
- Trascina il campo Vendita e inseriscilo nell'area Valore.
- Questo aggiungerà un'altra colonna con i valori delle vendite
- Fare clic sull'opzione Somma delle vendite2 nell'area Valore
- Fare clic sull'opzione "Impostazioni campo valore"
- Nella finestra di dialogo Impostazioni campo valore, modifica il nome personalizzato in "Totali parziali"
- Fare clic sulla scheda "Mostra valore come"
- Nel menu a discesa Mostra valore come, seleziona l'opzione "Totale parziale in"
- Nelle opzioni del campo Base, assicurati che sia selezionata la data
- Fare clic su OK
I passaggi precedenti modificherebbero la seconda colonna di vendita nella colonna Totale parziale.
Quindi questi sono alcuni dei modi in cui puoi utilizzare per calcolare il totale parziale in Excel. Se disponi di dati in formato tabellare, puoi utilizzare formule semplici e se hai una tabella Excel, puoi utilizzare formule che utilizzano riferimenti strutturati.
Ho anche spiegato come calcolare il totale parziale utilizzando Power Query e nelle tabelle pivot.
Spero che tu abbia trovato utile questo tutorial.
