È probabile che si verifichi la necessità di totali correnti se si ha a che fare con qualsiasi tipo di dati giornalieri.

Immagina di tenere traccia delle vendite ogni giorno. I tuoi dati contengono una riga per ogni data con un importo totale delle vendite, ma forse vuoi conoscere il totale delle vendite per il mese in ogni giorno. Si tratta di un totale corrente, è la somma di tutte le vendite fino al giorno di vendita corrente.

In questo post tratteremo diversi modi per calcolare un totale corrente per i dati giornalieri. Esploreremo come utilizzare le formule del foglio di lavoro, le tabelle pivot, power pivot con DAX e power query.

Esploreremo anche cosa succede al calcolo del totale corrente quando si inseriscono o eliminano righe di dati e come aggiornare i risultati.

Ottieni il file con tutti gli esempi.

Totali correnti con una Semplice Formula

È possibile creare una formula totale corrente di base utilizzando l’operatore+.

Tuttavia, avremo bisogno di utilizzare due formule diverse per ottenere il lavoro fatto.

1. =C3 sarà la prima formula e sarà solo nella prima riga del totale corrente.
2. =C4 + D3 sarà nella seconda riga e può essere copiato le righe rimanenti per il totale corrente.

La formula nella nostra prima riga non può aggiungere la cella sopra di essa al totale in quanto contiene un valore di testo per un’intestazione di colonna. Ciò causerebbe un VALORE#! errore nell’apparire nel totale corrente poiché + non può gestire i valori di testo. Evitiamo questo con una formula diversa nella prima riga che non fa riferimento alla cella sopra.

Cosa succede al totale corrente quando inseriamo o cancelliamo righe nei nostri dati?

L’inserimento di una nuova riga comporterà una lacuna nel totale corrente. Per risolvere questo problema, dovremo copiare la formula dalla prima cella sopra le righe appena inserite fino all’ultima riga.

L’eliminazione di qualsiasi riga comporterà #REF! errori dall’eliminazione di una riga significa eliminare una cella a cui fa riferimento la formula sottostante. Per risolvere questo problema, dovremo copiare la formula dall’ultima cella senza errori fino all’ultima riga.

Totali correnti con una formula di SOMMA

Possiamo evitare l’imbarazzo di usare due formule diverse nella nostra colonna totale corrente 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 riga.

=SUM(C3,D2)

Questa formula farà riferimento all’intestazione della colonna contenente il testo per la prima riga, ma questo ok in quanto è trattato come uno 0.

Quando si inseriscono o si eliminano righe, incontreremo ancora gli stessi problemi con celle vuote ed errori. Possiamo risolverli allo stesso modo dei totali correnti nel metodo formula semplice.

Totali correnti con un intervallo parzialmente fisso

Un’altra opzione con la funzione SUM consiste nel fare riferimento solo alla colonna Vendite e utilizzare un riferimento a intervallo parzialmente fisso.

Se usiamo la seguente formula =SUM (C C 3 3:C3), possiamo copiare e incollare questo lungo l’intervallo. Non farà riferimento a nessuna intestazione di colonna e l’intervallo a cui si fa riferimento crescerà a ogni riga.

Sfortunatamente, anche questo avrà gli stessi problemi (e soluzioni) con l’inserimento o l’eliminazione di righe.

Totali in esecuzione con un intervallo relativo denominato

Possiamo evitare i problemi con l’inserimento e l’eliminazione di righe dai nostri dati se usiamo un intervallo relativo denominato. Questo farà riferimento alla cella direttamente sopra, indipendentemente dal numero di righe che inseriamo o cancelliamo.

Questo è un trucco che comporta il passaggio temporaneo dello stile di riferimento di Excel da A1 a R1C1. Quindi definire un intervallo denominato utilizzando la notazione R1C1. Quindi passare lo stile di riferimento a A1.

Nello stile di riferimento R1C1, le celle sono indicate da quanto sono lontane dalla cella usando il riferimento. Ad esempio, =RC si riferisce alla cella 2 in alto e 3 a destra della cella usando questa formula.

Possiamo usare questo riferimento relativo per creare un intervallo denominato che è sempre una cella sopra la cella di riferimento con la formula =RC.

Per cambiare stile di riferimento, vai alla scheda File e scegli Opzioni. Vai alla sezione Formula nel menu Opzioni di Excel e seleziona la casella Stile di riferimento R1C1, quindi premi il pulsante OK.

Ora possiamo aggiungere il nostro intervallo denominato. Vai alla scheda Formula della barra multifunzione di Excel e scegli il comando Definisci nome.

Inserisci un nome come “Sopra” come nome dell’intervallo. Aggiungere la formula = RC nel Si riferisce a ingresso e premere il pulsante OK.

Ora possiamo riportare Excel allo stile di riferimento predefinito. Vai alla scheda File> Opzioni la sezione Formula > deseleziona la casella stile di riferimento R1C1 > quindi premi il pulsante OK.

Ora possiamo usare la formula =SUM(,Sopra) nella nostra colonna totale corrente.

L’intervallo denominato sopra farà sempre riferimento alla cella direttamente sopra. Quando inseriamo o cancelliamo righe, l’intervallo relativo denominato si regolerà di conseguenza e non è necessaria alcuna azione.

Infatti se inseriamo i nostri dati in una tabella Excel, la formula si riempirà automaticamente per qualsiasi nuova riga poiché la formula è uniforme per l’intera colonna. Non è necessaria alcuna azione per copiare le formule.

I totali in esecuzione con una tabella Pivot

Le tabelle pivot sono super utili per riassumere qualsiasi tipo di dati. C’è di più per loro che aggiungere, contare e trovare le medie. Esistono molti altri tipi di calcoli integrati e in realtà esiste un calcolo totale corrente!

Per prima cosa, dobbiamo inserire una tabella pivot basata sui dati. Selezionare una cella all’interno dei dati e andare alla scheda Inserisci e scegliere il comando Tabella pivot. Quindi passare attraverso la finestra Crea tabella pivot per scegliere dove si desidera la tabella pivot, in un nuovo foglio di lavoro o da qualche parte in uno esistente.

Aggiungi il campo Data nell’area Righe della tabella pivot, quindi aggiungi il campo Sales nell’area Valori della tabella pivot. Ora aggiungi un’altra istanza del campo Vendite nell’area Righe.

Ora dovremmo avere due campi di vendita identici con uno di essi etichettato Somma delle Vendite2. Possiamo rinominare questa etichetta in qualsiasi momento semplicemente digitando su di essa con qualcosa di simile a Running Total.

Fare clic destro su uno qualsiasi dei valori nel campo Somma di Sales2 e selezionare Mostra valore come quindi scegliere Esecuzione totale In.

Vogliamo mostrare il totale corrente per data, quindi nella finestra successiva dobbiamo selezionare la Data come Campo Base.

Questo è tutto, ora abbiamo un nuovo calcolo che visualizza il totale corrente delle nostre vendite all’interno della tabella pivot.

Cosa succede se aggiungiamo o cancelliamo una riga nei nostri dati di origine, in che modo questo influisce sul totale di esecuzione? I calcoli della tabella pivot sono dinamici e prenderanno in considerazione tutti i nuovi dati nel suo calcolo totale corrente, avremo solo bisogno di aggiornare la tabella pivot.

Fare clic con il tasto destro del mouse all’interno della tabella pivot e scegliere Aggiorna dal menu.

Totali correnti con Power Pivot e DAX Measures

I primi passi per questo sono esattamente gli stessi usando una normale tabella pivot.

Seleziona una cella all’interno dei dati e vai alla scheda Inserisci e scegli il comando Tabella pivot.

Quando si accede al menu Crea tabella pivot, selezionare la casella Aggiungi questi dati al modello di dati per aggiungere i dati al modello di dati e abilitarlo per l’uso con power pivot.

Inserire il campo Data nell’area Righe e il campo Sales nell’area Valori della tabella pivot.

Con power pivot, dovremo creare qualsiasi calcolo extra che vogliamo usando il linguaggio DAX. Fare clic destro sul nome della tabella nella finestra Campi tabella pivot, quindi selezionare Aggiungi misura 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 totale corrente.

1. Nella finestra di misura, dobbiamo aggiungere un nome di misura. In questo caso possiamo nominare la nuova misura come Totale corrente.
2. Abbiamo anche bisogno di aggiungere la formula di cui sopra nella casella Formula.
3. La cosa interessante di power pivot è la possibilità di assegnare un formato numerico a una misura. Possiamo scegliere il formato di valuta per la nostra misura. Ogni volta che usiamo questa misura in una tabella pivot, il formato verrà applicato automaticamente.

Premere il pulsante OK e verrà creata la nuova misura.

Ci sarà un nuovo campo elencato nella finestra Campi tabella pivot. Ha una piccola icona fx a sinistra per indicare che si tratta di una misura e non di un campo regolare nei dati.

Possiamo usare questo nuovo campo come qualsiasi altro campo e trascinarlo nell’area Valori per aggiungere il nostro calcolo totale corrente nella tabella pivot.

Cosa succede al totale corrente quando aggiungiamo o rimuoviamo i dati dalla tabella di origine? Proprio come una normale tabella pivot, dobbiamo semplicemente fare clic destro sulla tabella pivot e selezionare Aggiorna per aggiornare il calcolo.

Totali correnti con una query di potenza

Possiamo anche aggiungere totali correnti ai nostri dati utilizzando power query.

Per prima cosa dobbiamo importare la tabella in power query. Seleziona la tabella dei dati e vai alla scheda Dati e scegli l’opzione Da tabella/Intervallo. Questo aprirà l’editor power query.

Successivamente possiamo ordinare i nostri dati per data. Questo è un passaggio facoltativo che possiamo aggiungere in modo che se cambiamo l’ordine dei nostri dati di origine, il totale corrente apparirà ancora per data.

Fare clic sull’interruttore filtro nell’intestazione della colonna data e scegliere Ordina crescente dalle opzioni.

Dobbiamo aggiungere una colonna indice. Questo verrà utilizzato nel calcolo totale corrente in seguito. Vai alla scheda Aggiungi colonna e fai clic sulla piccola freccia accanto alla colonna Indice per inserire un indice a partire da 1 nella prima riga.

Dobbiamo aggiungere una nuova colonna alla nostra query per calcolare il totale corrente. Vai alla scheda Aggiungi colonna e scegli il comando Colonna personalizzata.

Possiamo nominare la colonna come Totale corrente e aggiungere la seguente formula.

L’elenco.La funzione Range crea un elenco di valori dalla colonna Sales a partire dalla 1a riga (0a voce) che si estende su un numero di righe in base al valore nella colonna index.

La lista.La funzione Sum aggiunge quindi questo elenco di valori che è il nostro totale corrente.

Non abbiamo più bisogno della colonna indice, ha servito il suo scopo e possiamo rimuoverlo. Fare clic con il tasto destro sull’intestazione della colonna e selezionare Rimuovi dalle opzioni.

Abbiamo il nostro totale di esecuzione e abbiamo finito con l’editor di query. Possiamo chiudere la query e caricare i risultati in un nuovo foglio di lavoro. Vai alla scheda Home dell’editor query e premi il pulsante Chiudi& Carica.

Cosa succede con il totale corrente quando aggiungiamo o rimuoviamo righe dai nostri dati di origine? Avremo bisogno di aggiornare la tabella di output power query per aggiornare il totale corrente con le modifiche. Fare clic destro in qualsiasi punto del tavolo e scegliere Aggiorna per aggiornare la tabella.

Con il passaggio di ordinamento opzionale 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 corrente.

Conclusioni

Ci sono molte opzioni diverse per il calcolo dei totali correnti in Excel.

Abbiamo esplorato le opzioni tra cui formule nel foglio di lavoro, tabelle pivot, formule power pivot DAX e power query. Alcuni offrono una soluzione più robusta quando si aggiungono o rimuovono righe dai dati, altri metodi offrono un’implementazione più semplice.

Le formule semplici nel foglio di lavoro sono facili da configurare ma non gestiscono facilmente l’inserimento o l’eliminazione di nuove righe di dati. Altre soluzioni come le tabelle pivot, DAX e power query sono più robuste e gestiscono facilmente l’inserimento o l’eliminazione di righe di dati, ma sono più difficili da configurare.

È bene essere consapevoli dei pro e dei contro di ogni metodo e scegliere quello più adatto. Se non inserirai o eliminerai nuovi dati, le formule del foglio di lavoro potrebbero essere la strada da percorrere.