E’ probabile che vi imbattiate nel bisogno di totali in corso se avete a che fare con qualsiasi tipo di dati giornalieri.
Immaginate di tenere traccia delle vendite ogni giorno. I tuoi dati contengono una riga per ogni data con un totale delle vendite, ma forse vuoi sapere il totale delle vendite del mese per ogni giorno. Questo è un totale corrente, è la somma di tutte le vendite fino al giorno corrente incluso.
In questo post copriremo diversi modi per calcolare un totale corrente per i vostri dati giornalieri. Esploreremo come usare le formule del foglio di lavoro, le tabelle pivot, power pivot con DAX e power query.
Esamineremo anche cosa succede al calcolo del totale in corso quando si inseriscono o cancellano righe di dati e come aggiornare i risultati.
Ottieni il file con tutti gli esempi.
Tabella del contenuto
Totali correnti con una formula semplice
E’ possibile creare una formula di base per i totali correnti usando l’operatore +.
Tuttavia, avremo bisogno di usare due formule diverse per fare il lavoro.
- =C3 sarà la prima formula e sarà solo nella prima riga del totale a correre.
- =C4+D3 sarà nella seconda riga e può essere copiato nelle righe rimanenti per il totale corrente.
La formula nella nostra prima riga non può aggiungere la cella sopra di essa al totale poiché contiene un valore di testo per un titolo di colonna. Questo causerebbe un errore #VALORE! nel totale in corso poiché il + non può gestire valori di testo. Lo evitiamo con una formula diversa nella prima riga che non fa riferimento alla cella sopra.
Cosa succede al totale in corso quando inseriamo o cancelliamo righe nei nostri dati?
Inserendo una nuova riga ci sarà uno scarto nel totale in corso. Per risolvere questo problema, dovremo copiare la formula dalla prima cella sopra le nuove righe inserite fino all’ultima riga.
Eliminare qualsiasi riga comporterà un errore #REF! poiché eliminare una riga significa eliminare una cella referenziata dalla formula sottostante. Per risolvere questo problema, dovremo copiare la formula dall’ultima cella senza errori fino all’ultima riga.
Eseguire i totali con una formula SUM
Possiamo evitare l’imbarazzo di usare due formule diverse nella nostra colonna dei totali utilizzando la funzione SUM invece dell’operatore +. Quando la funzione SUM incontra una cella di testo la tratterà come se contenesse uno 0.
In questo modo possiamo usare la seguente formula in modo uniforme per ogni riga, inclusa la prima.
=SUM(C3,D2)
Questa formula farà riferimento all’intestazione della colonna che contiene testo per la prima riga, ma va bene perché è trattata come uno 0.
Quando inseriamo o cancelliamo righe, incontreremo ancora gli stessi problemi con celle vuote ed errori. Possiamo risolverli allo stesso modo dei totali in esecuzione con il metodo della formula semplice.
Eseguire i totali con un intervallo parzialmente fisso
Un’altra opzione con la funzione SOMMA è quella di fare riferimento solo alla colonna Vendite e usare un intervallo parzialmente fisso.
Se usiamo la seguente formula =SUM($C$3:C3), possiamo copiare e incollare questa formula nell’intervallo. Non farà riferimento a nessuna intestazione di colonna e l’intervallo referenziato crescerà ad ogni riga.
Purtroppo, anche questo avrà gli stessi problemi (e soluzioni) con l’inserimento o l’eliminazione di righe.
Eseguire i totali con un intervallo denominato relativo
Possiamo evitare i problemi di inserimento e cancellazione di righe dai nostri dati se usiamo un intervallo denominato relativo. Questo farà riferimento alla cella direttamente sopra di noi, non importa quante righe inseriamo o cancelliamo.
Questo è un trucco che consiste nel cambiare temporaneamente lo stile di riferimento di Excel da A1 a R1C1. Poi definire un intervallo denominato usando la notazione R1C1. Poi si commuta lo stile di riferimento di nuovo in A1.
Nello stile di riferimento R1C1, le celle sono riferite da quanto sono lontane dalla cella che usa il riferimento. Per esempio, =RC si riferisce alla cella 2 in alto e 3 a destra della cella che usa questa formula.
Possiamo usare questo riferimento relativo per creare un intervallo di nome che è sempre una cella sopra la cella di riferimento con la formula =RC.
Per cambiare stile di riferimento, andare alla scheda File e scegliere Opzioni. Andate alla sezione Formula nel menu Opzioni di Excel e spuntate la casella dello stile di riferimento R1C1 e poi premete il pulsante OK.
Ora possiamo aggiungere il nostro intervallo denominato. Andate nella scheda Formula della barra multifunzione di Excel e scegliete il comando Definisci nome.
Inserite un nome come “Sopra” come nome dell’intervallo. Aggiungete la formula =RC nell’input Riferimenti a e premete il pulsante OK.
Possiamo ora riportare Excel allo stile di riferimento predefinito. Andate nella scheda File > Opzioni della sezione Formula > deselezionate la casella dello stile di riferimento R1C1 > poi premete il pulsante OK.
Ora possiamo usare la formula =SUM(,Sopra) nella nostra colonna del totale corrente.
L’intervallo denominato Sopra si riferirà sempre alla cella direttamente sopra. Quando inseriamo o cancelliamo delle righe, il relativo intervallo denominato si adatterà di conseguenza e non sarà necessaria alcuna azione.
In effetti, se mettiamo i nostri dati in una tabella Excel, la formula si riempirà automaticamente per ogni nuova riga, poiché la formula è uniforme per l’intera colonna. Nessuna azione è necessaria per copiare le formule.
Eseguire i totali con una tabella pivot
Le tabelle pivot sono super utili per riassumere qualsiasi tipo di dati. C’è molto di più che aggiungere, contare e trovare le medie. Ci sono molti altri tipi di calcolo incorporati, e c’è anche un calcolo del totale in corso!
Prima di tutto, dobbiamo inserire una tabella pivot basata sui dati. Selezionate una cella all’interno dei dati e andate nella scheda Inserisci e scegliete il comando Tabella pivot. Poi passate attraverso la finestra Crea tabella pivot per scegliere dove volete la tabella pivot, o in un nuovo foglio di lavoro o da qualche parte in uno esistente.
Aggiungi il campo Data nell’area Righe della tabella pivot, poi aggiungi il campo Vendite nell’area Valori della tabella pivot. Ora aggiungete un’altra istanza del campo Vendite nell’area Righe.
Ora dovremmo avere due campi Vendite identici con uno di essi etichettato Somma delle vendite2. Possiamo rinominare questa etichetta in qualsiasi momento semplicemente scrivendoci sopra qualcosa come Running Total.
Facciamo clic destro su qualsiasi valore nel campo Sum of Sales2 e selezioniamo Show Value As e scegliamo Running Total In.
Vogliamo mostrare il running total per data, quindi nella prossima finestra dobbiamo selezionare Date come campo base.
Ecco fatto, ora abbiamo un nuovo calcolo che mostra il totale corrente delle nostre vendite all’interno della tabella pivot.
Cosa succede se aggiungiamo o cancelliamo una riga nei nostri dati sorgente, come influisce sul totale corrente? I calcoli della tabella pivot sono dinamici e terranno conto di tutti i nuovi dati nel calcolo del totale in corso, dovremo solo aggiornare la tabella pivot.
Clicca con il tasto destro del mouse in un punto qualsiasi della tabella pivot e scegli Aggiorna dal menu.
Eseguire i totali con Power Pivot e misure DAX
I primi due passi sono esattamente gli stessi che si fanno con una normale tabella pivot.
Selezionare una cella all’interno dei dati e andare alla scheda Inserisci e scegliere il comando Tabella Pivot.
Quando arrivate al menu Crea tabella pivot, spuntate la casella Aggiungi questi dati al modello dati per aggiungere i dati al modello dati e abilitarli all’uso con power pivot.
Posizionate il campo Data nell’area Righe e il campo Vendite nell’area Valori della tabella pivot.
Con power pivot, avremo bisogno di creare i calcoli extra che vogliamo usando il linguaggio DAX. Cliccate con il tasto destro del mouse sul nome della tabella nella finestra PivotTable Fields, quindi selezionate Add Measure per creare un nuovo calcolo. Nota, questo è disponibile solo con il modello di dati.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Ora possiamo creare la nostra nuova misura di totale in corso.
- Nella finestra Misura, dobbiamo aggiungere un Nome Misura. In questo caso possiamo chiamare la nuova misura come Running Total.
- Abbiamo anche bisogno di aggiungere la formula di cui sopra nella casella Formula.
- La cosa bella di power pivot è la possibilità di assegnare un formato numerico a una misura. Possiamo scegliere il formato Valuta per la nostra misura. Ogni volta che usiamo questa misura in una tabella pivot il formato sarà automaticamente applicato.
Premere il pulsante OK e la nuova misura sarà creata.
Ci sarà un nuovo campo elencato nella finestra PivotTable Fields. Ha una piccola icona fx sulla sinistra per indicare che si tratta di una misura e non di un normale campo nei dati.
Possiamo usare questo nuovo campo come qualsiasi altro campo e trascinarlo nell’area Valori per aggiungere il nostro calcolo del totale corrente nella tabella pivot.
Cosa succede al totale corrente quando aggiungiamo o rimuoviamo dati dalla tabella sorgente? Proprio come una normale tabella pivot, abbiamo semplicemente bisogno di cliccare con il tasto destro del mouse sulla tabella pivot e selezionare Aggiorna per aggiornare il calcolo.
Totali in esecuzione con una Power Query
Possiamo anche aggiungere i totali in esecuzione ai nostri dati utilizzando power query.
Per prima cosa dobbiamo importare la tabella in power query. Selezionare la tabella dei dati e andare alla scheda Dati e scegliere l’opzione Da tabella/range. Questo aprirà l’editor di power query.
Poi possiamo ordinare i nostri dati per data. Questo è un passo opzionale che possiamo aggiungere in modo che se cambiamo l’ordine dei nostri dati sorgente, il totale corrente apparirà ancora per data.
Clicchiamo sulla levetta del filtro nell’intestazione della colonna della data e scegliamo Sort Ascending dalle opzioni.
Dobbiamo aggiungere una colonna indice. Questa sarà usata nel calcolo del totale in corso più avanti. Andate nella scheda Aggiungi colonna e cliccate sulla piccola freccia accanto a Colonna indice per inserire un indice a partire da 1 nella prima riga.
Abbiamo bisogno di aggiungere una nuova colonna alla nostra query per calcolare il totale corrente. Andiamo nella scheda Aggiungi colonna e scegliamo il comando Colonna personalizzata.
Possiamo nominare la colonna come Running Total e aggiungere la seguente formula.
La funzione List.Range crea una lista di valori dalla colonna Vendite a partire dalla prima riga (0° elemento) che si estende su un certo numero di righe in base al valore della colonna indice.
La funzione List.Sum somma quindi questa lista di valori che è il nostro totale corrente.
La colonna indice non ci serve più, ha raggiunto il suo scopo e possiamo eliminarla. Clicchiamo con il tasto destro del mouse sull’intestazione della colonna e selezioniamo Remove dalle opzioni.
Abbiamo il nostro totale corrente e abbiamo finito con l’editor di query. Possiamo chiudere la query e caricare i risultati in un nuovo foglio di lavoro. Andate alla scheda Home dell’editor di query e premete il pulsante Chiudi & Carica.
Cosa succede con il totale corrente quando aggiungiamo o rimuoviamo righe dai nostri dati sorgente? Avremo bisogno di aggiornare la tabella di output di Power Query per aggiornare il totale in corso con le modifiche. Cliccate con il tasto destro del mouse in un punto qualsiasi della tabella e scegliete Aggiorna per aggiornare la tabella.
Con il passo di ordinamento opzionale di cui sopra, se aggiungiamo date fuori ordine ai dati di origine, Power Query ordinerà per data e restituirà l’ordine corretto per data per il totale in corso.
Conclusioni
Ci sono molte opzioni diverse per calcolare i totali in corso in Excel.
Abbiamo esplorato opzioni che includono formule nel foglio di lavoro, tabelle pivot, formule DAX power pivot e power query. Alcuni offrono una soluzione più robusta quando si aggiungono o rimuovono righe dai dati, altri metodi offrono un’implementazione più facile.
Le formule semplici nel foglio di lavoro sono facili da impostare ma non gestiranno facilmente l’inserimento o l’eliminazione di nuove righe di dati. Altre soluzioni come tabelle pivot, DAX e power query sono più robuste e gestiscono facilmente l’inserimento o la cancellazione di righe di dati, ma sono più difficili da impostare.
È bene essere consapevoli dei pro e dei contro di ogni metodo e scegliere quello più adatto. Se non volete inserire o cancellare nuovi dati, allora le formule del foglio di lavoro potrebbero essere la via da seguire.