totale parziale (noto anche comesomma cumulativa) è usato molto comunemente in molti casi.Questa è una metrica che ti dice qual è la somma dei valori finora.
Ad esempio, se disponi di dati sulle vendite mensili, un totale parziale ti indicherà quante vendite sono state effettuate dal primo giorno del mese a una data specifica.
Ci sono altre situazioni in cui vengono spesso utilizzati i totali, come il calcolo dei saldi di cassa negli estratti conto/libro contabili, il calcolo delle calorie nei piani pasto, ecc.
In Microsoft Excel esistono diversi modi per calcolare i totali parziali.
Il metodo scelto dipende anche dalla struttura dei dati.
Ad esempio, se si dispone di dati tabellari 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 li tratterò tuttiCalcola un totale parziale in Excelmetodi diversi.
Quindi iniziamo!
Contenuti
Calcola i totali parziali utilizzando i dati tabulari
Se disponi di dati tabulari (ovvero tabelle in Excel che non vengono convertite in tabelle Excel), puoi utilizzare alcune semplici formule per calcolare i totali parziali.
Utilizzare l'operatore di addizione
Supponiamo di avere dati sulle vendite per data e lo desideri在 nella colonna CcalcoloTotale parziale.
Ecco i passaggi per farlo.
Passo 1 – Nella cella C2, la prima cella per la quale si desidera eseguire il totale, immettere
= B2
Questo ottiene solo lo stesso valore di vendita nella cella B2.
Passaggio 2– Nella cella C3, immettere la seguente formula:
= DO2 + SI3
Passaggio 3– Applicare la formula all'intera colonna.Puoi utilizzare il quadratino di riempimento per selezionarlo e trascinarlo, o semplicemente copiare e incollare la cella C3 in tutte le celle rimanenti (questo regolerà automaticamente il riferimento e darà il risultato corretto).
Questo ti darà il risultato mostrato di seguito.
Questo è un metodo molto semplice che funziona bene nella maggior parte dei casi.
La logica è semplice: ogni cella prende il valore su di essa (che è la somma cumulativa fino al giorno precedente) e aggiunge il valore nella cella adiacente (che è il valore di vendita del giorno corrente).
C'è solo un aspetto negativo: se elimini una riga esistente in questo set di dati, tutte le celle seguenti restituiranno un errore di riferimento (#RIF!)
Se il tuo set di dati ha questa possibilità, usa il metodo successivo usando la formula SUM
Usa SUM con riferimenti di cella parzialmente bloccati
Si supponga di disporre di dati sulle vendite per data e di voler calcolare un totale parziale nella colonna C.
Di seguito è riportata la formula SUM che ti darà un 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 questo riferimento non cambia quando copio la stessa formula nella cella sottostante.Quindi, quando copi la formula nella cella sottostante, la formula cambierà in SUM($B$2:B3)
- B2 - Questa è la seconda parte del riferimento, è un riferimento relativo, il che significa che quando copio la formula in basso oa destra, questo si adatta.Quindi, quando si copia la formula nella cella sottostante, il valore diventa B3
La cosa bella di questo approccio è che se elimini qualsiasi riga nel set di dati, questa formula si adatta e ti dà comunque il totale parziale corretto.
Calcola il totale parziale nel foglio Excel
Quando si lavora con dati tabulari in Excel, è meglio convertirli in una tabella Excel.Semplifica la gestione dei dati e semplifica anche l'utilizzo di strumenti come Power Query e Power Pivot.
L'uso di una tabella di Excel offre numerosi vantaggi, come i riferimenti strutturati (che rendono molto semplice fare riferimento ai dati nella tabella e utilizzarli nelle formule) e la regolazione automatica dei riferimenti quando si aggiungono o rimuovono dati dalla tabella.
Mentre puoi ancora usare la formula sopra che ti ho mostrato in un foglio Excel, lascia che ti mostri alcuni modi migliori per farlo.
Supponiamo di avere una tabella Excel come quella seguente e di voler calcolare un totale parziale nella colonna C.
Ecco la formula per farlo:
=SOMMA(Dati vendite[[#Intestazioni],[Vendita]]:[@Vendita])
La formula sopra può sembrare un po' lunga, ma non devi scriverla tu stesso.Ciò che vedi in una formula di sommatoria è chiamato riferimento strutturato ed è un modo efficiente per Excel di fare riferimento a punti dati specifici in una tabella di Excel.
Ad esempio, SalesData[[#Headers],[Sale]] si riferisce all'intestazione Sales nella tabella SalesData (SalesData è il nome della tabella Excel che ho fornito quando ho creato la tabella)
Considerando che [@Sale] si riferisce al valore in una cella nella stessa riga nella colonna Sale.
Lo sto solo spiegando qui per la tua comprensione, ma anche se non sai nulla di riferimenti strutturati, puoi comunque creare facilmente questa formula.
Ecco i passaggi per farlo:
- Nella cella C2, inserisci =SUM(
- Seleziona la cella B1, che è l'intestazione della colonna con il valore di vendita.Puoi usare il mouse o usare i tasti freccia.Noterai che Excel inserisce automaticamente un riferimento strutturato alla cella
- Aggiungi: (due punti)
- Seleziona la cella B2.Excel inserirà nuovamente automaticamente i riferimenti strutturati alle celle
- Chiudi la parentesi e premi Invio
Noterai anche che non devi copiare la formula nell'intera colonna, il foglio Excel lo fa automaticamente per te.
Un altro vantaggio di questo approccio è che se si aggiunge un nuovo record a questo set di dati, il foglio Excel calcola automaticamente un totale parziale per tutti i nuovi record.
Sebbene abbiamo incluso le intestazioni di colonna nella formula, tieni presente che la formula ignora il testo dell'intestazione e considera solo i dati nella colonna
Calcola i totali parziali con Power Query
Power Query è uno strumento straordinario quando si tratta di connettersi a database, estrarre dati da più origini e trasformarli prima di inserirli in Excel.
Se stai già utilizzando Power Query, è più efficiente aggiungere i totali parziali durante la trasformazione dei dati nell'editor di Power Query stesso (piuttosto che ottenere prima i dati in Excel e quindi aggiungere i totali parziali utilizzando uno dei metodi precedenti).
Sebbene non ci siano funzionalità integrate in Power Query per aggiungere totali parziali (vorrei che ci fosse), puoi comunque farlo con una semplice formula.
Supponiamo di avere una tabella Excel simile a questa e di voler aggiungere totali parziali a questi dati:
Ecco i passaggi per farlo:
- Seleziona una cella qualsiasi in una tabella di Excel
- fare clic sui dati
- Nella scheda Ottieni e trasforma, fai clic sull'icona da tabella/intervallo.Verrà aperta la tabella nell'editor di Power Query
- [Facoltativo] Se la colonna della data non è già ordinata, fai clic sull'icona del filtro nella colonna della data, quindi fai clic su Ordina 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 obliqua nera accanto ad essa per visualizzare più opzioni)
- Fare clic sull'opzione "Da 1".In questo modo verrà aggiunta una nuova colonna di indice che inizierà da 1 e inserirà numeri incrementati di 1 in tutta la colonna
- Fare clic sull'icona Colonne personalizzate (anche nella scheda Aggiungi colonne)
- Nella finestra di dialogo Colonne personalizzate che si apre, inserisci un nome per la nuova colonna.In questo esempio userò il nome "Totale corrente"
- Nel campo Formula colonna personalizzata, inserisci la seguente formula:List.Sum(List.Range(#"Indice aggiunto"[Vendita],0,[Indice]))
- Assicurati che sia presente una casella di controllo nella parte inferiore della finestra di dialogo che dice "Nessun errore di sintassi rilevato"
- Fare clic su OK.Ciò aggiungerà una nuova colonna del totale parziale
- trascina la colonna dell'indice
- Fare clic sulla scheda File, quindi su Chiudi e carica
I passaggi precedenti inseriranno un nuovo foglio di lavoro nella cartella di lavoro con una tabella con totali parziali.
Ora, se pensi che questi siano troppi passaggi rispetto al precedente approccio con formule semplici, hai ragione.
Se disponi già di un set di dati e tutto ciò che devi fare è aggiungere i totali parziali, è meglio non usare Power Query.
L'uso di Power Query ha senso in situazioni in cui è necessario estrarre dati da un database o combinare dati da diverse cartelle di lavoro e aggiungere anche totali parziali nel processo.
Inoltre, dopo aver automatizzato questa operazione con Power Query, la prossima volta che il set di dati cambia, non devi farlo di nuovo, puoi semplicemente aggiornare la query e ti darà risultati in base al nuovo set di dati.
Come funziona?
Ora lascia che ti spieghi rapidamente cosa succede con questo approccio.
La prima cosa che facciamo nell'editor di Power Query è inserire una colonna di indice che inizia da 1 e aumenta man mano che scende nella cella.
Lo facciamo perché dobbiamo usare questa colonna per calcolare il totale parziale in un'altra colonna che viene inserita nel passaggio successivo.
Quindi inseriamo una colonna personalizzata e utilizziamo la seguente formula
List.Sum(List.Range(#"Indice aggiunto"[Vendita],0,[Indice]))
Questa è una formula List.Sum che ti darà la somma dell'intervallo specificato in essa.
L'intervallo viene specificato utilizzando la funzione List.Range.
La funzione List.Range prende come output l'intervallo specificato nella colonna di vendita e l'intervallo varia in base al valore dell'indice.Ad esempio, per il primo record, l'intervallo è il primo valore di vendita.Questo intervallo si espande man mano che si scende nella cella.
Quindi, per la prima cella.List.Sum ti darà solo la somma del primo valore di vendita, per la seconda cella ti darà la somma dei primi due valori di vendita e così via.
Sebbene questo approccio funzioni bene, può diventare molto lento per set di dati di grandi dimensioni (migliaia di righe).
Calcola i totali parziali in base a criteri
Finora, abbiamo visto esempi di calcolo di un totale parziale di tutti i valori in una colonna.
Ma in alcuni casi, potresti voler calcolare un totale parziale per un record specifico.
Ad esempio, di seguito ho un set di dati in cui voglio contare il totale parziale di stampanti e scanner in due colonne diverse.
Questo può essere fatto usando la formula SUMIF, che calcola un totale parziale assicurando che le condizioni specificate siano soddisfatte.
Ecco la formula per farlo per la colonna della stampante:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Allo stesso modo, per calcolare il totale parziale dello scanner, utilizzare la seguente formula:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Nella formula sopra, ho usato SUMIF che mi darà una somma in un intervallo quando sono soddisfatte le condizioni specificate.
La formula accetta tre parametri:
- gamma: Questo è l'intervallo di condizioni che verrà verificato rispetto alla condizione specificata
- criteri: questo è il criterio che verrà verificato solo se questo criterio è soddisfatto, quindi verrà aggiunto il valore nel terzo parametro, l'intervallo di somma
- [intervallo_somma]: questo è l'intervallo della somma dei valori da sommare quando la condizione è soddisfatta
Inoltre, ingamma和intervallo_sommaparametro, ho bloccato la seconda parte del riferimento in modo che mentre ci muoviamo verso il basso nella cella, l'intervallo continua ad espandersi.Questo ci consente di considerare e aggiungere solo valori fino a quell'intervallo (quindi un totale parziale).
In questa formula utilizzo le colonne di intestazione (Stampanti e Scanner) come criteri.Puoi anche codificare lo standard se le intestazioni delle colonne non sono esattamente le stesse del testo standard.
Se devi controllare più condizioni, puoi utilizzare la formula SUMIFS.
Totali parziali in una tabella pivot
Se desideri aggiungere un totale parziale ai risultati della tabella pivot, puoi farlo facilmente utilizzando la funzionalità incorporata nelle tabelle pivot.
Supponiamo di avere una tabella pivot come quella qui sotto con le date in una colonna e i valori di vendita in un'altra.
Di seguito sono riportati i passaggi per aggiungere una colonna aggiuntiva che mostrerà le vendite cumulative per data:
- Trascina il campo Vendita e rilascialo nell'area Valore.
- Questo aggiungerà un'altra colonna con il valore delle vendite
- Fare clic sull'opzione Vendite totali 2 nell'area Valore
- Fare clic sull'opzione Impostazioni campo valore
- Nella finestra di dialogo Impostazioni campo valore, modificare Nome personalizzato in Totale parziale
- Fare clic sulla scheda "Visualizza valore come".
- Nel menu a tendina Visualizza valore come, seleziona l'opzione "Totale progressivo in".
- Nelle opzioni dei campi di base, assicurati che la data sia selezionata
- Fare clic su OK
I passaggi precedenti cambieranno la seconda colonna delle vendite in una colonna "totale parziale".
Quindi, questi sono alcuni dei metodi che puoi utilizzare per calcolare i totali parziali in Excel.Se disponi di dati tabulari, puoi utilizzare formule semplici e, se disponi di tabelle Excel, puoi utilizzare formule che utilizzano riferimenti strutturati.
Ho anche spiegato come calcolare i totali parziali utilizzando Power Query e le tabelle pivot.
Spero che tu abbia trovato utile questo tutorial.
