Somma progressiva Stampa
Scritto da Ignazio Putignano   
Lunedì 04 Gennaio 2010 16:32

A volte in Excel si ha l’esigenza di eseguire la somma progressiva, ossia un calcolo in cui (a cascata) si sommano dei valori al risultato di una formula analoga contenuta nella cella precedente.

Consideriamo, ad esempio, il caso della numerazione di un elenco di nomi, in cui il numero in corrispondenza di ciascun nome si ottiene sommando 1 al numero contenuto nella cella sopra.

Tralasciando l’uso di funzioni come RIF.RIGA o RIGHE (che risolverebbero il problema specifico della numerazione, ma non quello più generico della somma progressiva), la prima soluzione che forse ci viene in mente è quella di digitare il numero 1 nella cella B3, quindi immettere nella cella B4 la formula =B3+1 e copiarla sotto (trascinandola fino alla cella B9).

Semplice! Rapido! Efficace! ... Efficace!? ... Efficace forse no! ... Vediamo perché.

Usare le funzioni SOMMA e INDIRETTO al posto dell’operatore +

Aggiungiamo a destra dell’elenco un’altro elenco identico (per distinguerlo coloriamo diversamente lo sfondo dell’intestazione) ma stavolta adottiamo per la numerazione progressiva un’altra formula:

=SOMMA(1;INDIRETTO("R[-1]C";0))

e osserviamo cosa succede alla numerazione dei due elenchi quando si elimina/inserisce un nome (aiuta affiancare orizzontalmente 2 finestre dello stesso foglio, con la finestra superiore che visualizza i risultati e la finestra inferiore che visualizza le formule).

Eliminare una riga

Se proviamo ad eliminare “Giulia” (quarto nome in elenco), dalla finestra superiore (dei risultati) notiamo che:

  • nell’elenco di sinistra (da “Antonio” in giù) viene visualizzato il messaggio d’errore #RIF!
  • nell’elenco di destra la numerazione si aggiorna automaticamente

 

Se diamo uno sguardo alla finestra inferiore (delle formule) notiamo che l’errore è contenuto solo nella formula di fianco al nome “Antonio” (che ha perso il riferimento di cella quando abbiamo eliminato la riga precedente che conteneva il nome “Giulia”).

Per ripristinare la numerazione nell’elenco di sinistra dobbiamo sostituire la formula in corrispondenza di “Antonio”, ad esempio copiando in B6 la formula contenuta in B5 (trascinandola sotto di 1 cella).

Inserire una riga

Se proviamo ad inserire il nome “Angelo” (lo scriviamo in rosso per individuarlo più facilmente nell’elenco) tra i nomi “Giulia” e “Antonio”, dalla finestra superiore (dei risultati) osserviamo che in entrambi gli elenchi si è creato un buco nella numerazione, ma con una differenza:

  • nell’elenco di sinistra la numerazione viene interrotta
  • nell’elenco di destra la numerazione viene spezzata

Evidentemente occorre estendere la formula al nome aggiunto, ma basta fare questo per aggiornare correttamente la numerazione di entrambi gli elenchi?

Se diamo un’occhiata alla finestra inferiore (delle formule) notiamo che, mentre nell’elenco di destra questo è sufficiente, nell’elenco di sinistra non basta perchè di fianco al nome “Antonio” la formula non è =B7+1 (ma è =B6+1, la stessa che c’era prima dell’inserimento del nome “Angelo”).

Per ripristinare la numerazione nell’elenco di sinistra dobbiamo anche sostituire la formula in corrispondenza di “Antonio”, ad esempio copiando non solo in B7, ma anche in B8, la formula contenuta in B6 (trascinandola sotto di 2 celle).

Conclusioni

Attraverso l’esempio (semplice, ma frequente) della numerazione di un elenco, abbiamo visto come le funzioni SOMMA e INDIRETTO permettono di scrivere una formula di somma progressiva più duttile rispetto al semplice uso dell’operatore +, in quanto:

  • SOMMA consente di usare la formula anche per la prima riga dell’elenco
  • INDIRETTO consente di far riferimento alla riga sopra senza usare “direttamente” il riferimento di cella

Il vantaggio è di usare una formula che non deve essere riadattata quando in qualunque posizione (potrebbe essere anche in cima all’elenco) si inserisce/elimina una riga.

Prima di terminare, vediamo come possiamo scrivere la formula di somma progressiva nel caso del calcolo del saldo di un conto corrente.

Il saldo in corrispondenza di qualsiasi data si ottiene sommando/sottraendo la corrispondente entrata/uscita al saldo precedente, con una formula che ha un riferimento diretto alle celle della stessa riga e un riferimento indiretto alla cella della riga precedente.

Ad esempio, in E3 la formula usata è:

=SOMMA(C3;-D3;INDIRETTO("R[-1]C";0))

Se inoltre disponiamo di Excel 2007 possiamo convertire l’intervallo di dati in una tabella con il comando “Inserisci > Tabella”

e creare la seguente Tabella1, con tutti i vantaggi che ne derivano, alcuni dei quali sono stati evidenziati in figura, come l’attivazione dell’opzione “Riga Totale” (per il calcolo delle Entrate Totali e delle Uscite Totali) e l’attivazione dell’opzione “Righe alternate evidenziate” (prima di Excel 2007 questo risultato si poteva ottenere in maniera più laboriosa impostando un’opportuna formattazione condizionale).

E le formule? Rimangono le stesse oppure vanno modificate?

Potremmo tranquillamente usare le stesse formule, ma se intendiamo sfruttare un’altra caratteristica dalle tabelle (introdotta per rendere più leggibili le formule) potremmo usare i riferimenti strutturati al posto dei riferimenti di cella.

In tal caso, come mostrato in figura, le formule per il calcolo del saldo progressivo diventano:

=SOMMA(Tabella1[[#Questa riga];[ENTRATA]];-Tabella1[[#Questa riga];[USCITA]];INDIRETTO("R[-1]C";0))

Da notare come non possiamo usare un riferimento strutturato per il riferimento (indiretto) alla cella sopra, ma dobbiamo continuare ad usare la funzione INDIRETTO.

Infatti tra gli specificatori dei riferimenti strutturati esiste l’elemento speciale “#Questa riga”, ma non esiste l’elemento speciale per puntare alla riga superiore, del tipo “#Riga sopra” che avrebbe permesso di sostituire “INDIRETTO("R[-1]C";0)” con qualcosa tipo “Tabella1[[#Riga sopra];[SALDO]]”.

 


Sistema operativo: WIN32
Applicazioni: Excel 97, Excel 2000, Excel 2002/XP, Excel 2003, Excel 2007

 

Ultimo aggiornamento Martedì 19 Gennaio 2010 22:31