Sommare differenze tra valori in celle contigue, in progressione. - dal 04/09/04 pagina vista: volte

Un interessante esercizio nato da una richiesta ricevuta. Il problema : data una tabella come questa

  A B C D E F G H I J K L M N
1 Nome GEN FEB MAR APR MAG GIU LUG AGO SET OTT NOV DIC  
2 Poli 150 280 350 460 510 590 680 770 890 950 1080 1190  

Nella riga 2, per ogni nominativo, vengono riportate le letture del contakm dell'auto prese alla fine del mese (i valori sono d'esempio) e assegnate al mese di pertinenza. Si vuole valutare il chilometraggio mensile ottenuto per differenza tra la lettura dei valori di un mese e il mese precedente, e vogliamo sommare queste differenze per avere un totale progressivo e finale. Ovviamente alla fine dell'anno sarebbe semplice impostare una semplice differenza : =M2 - B2 (risultato 1040), oppure variare di mese in mese la formula con il riferimento della cella dell'ultimo mese segnato meno il primo, oppure ancora ricorrere a lunghe formule con i Se, che legga i valori se le celle non sono vuote, o ancora ad un'altra riga con in ogni cella, a partire dal secondo mese, una formula, a scalare, tipo =C2-B2, nella successiva =D2-C2, ecc. ecc. ed alla fine inserire la funzione =SOMMA(celle con differenze), tipo:

  A B C D E F G H I J K L M N
1 Nome GEN FEB MAR APR MAG GIU LUG AGO SET OTT NOV DIC  
2 Poli 150 280 350 460 510 590 680 770 890 950 1080 1190  
3     130 70 110 50 80 90 ecc. ecc       Somma

Ma anche questa soluzione per funzionare bene, richiede che le formule per le differenze poste nella riga 3 vengano inserite quando nella cella superiore ci sia un valore, altrimenti la somma risulterebbe errata.

Per fortuna il vba ci dà una mano. Possiamo usare un ciclo For Next, che partendo dalla colonna 3 (C) e proseguendo fino alla 13 (M) esegua una differenza tra la cella della colonna in quel momento "spazzolata" e la cella precedente, e che memorizzi la differenza in un totalizzatore. Alla fine del ciclo renderemo una cella, per esempio la O, stessa riga, uguale al valore finale totalizzato.

Avremo però bisogno di "sapere" prima di iniziare il ciclo, quante sono le celle che portano i km, per determinare fino a quale colonna fermarsi per fare le differenze e sommarle. Per questo useremo End per reperire l'ultima cella con numeri, quindi una routine potrebbe essere questa:

Sub SommaDiff1()
tot = 0
'impostiamo il totalizzatore a zero
riga = 2 
'impostiamo la riga di cui scorrere le colonne

'sotto: con Y reperiamo le colonne che portano valori (testo o numeri); si inizia dalla 'colonna 1 dove sicuramente abbiamo il nome agente, e dalla colonna 2 dove abbiamo il 'valore relativo al primo mese (ricordo che con End(xlToRight) è necessario che la prima 'cella di inizio porti un valore).
Set Y = Range(Cells(riga, 1), Cells(riga, 1).End(xlToRight))

'sotto: con ncol otteniamo il numero delle colonne con valori e che forma il limite massimo 'del ciclo seguente.
ncol = Y.Columns.Count

'sotto: si inizia il ciclo a partire dalla colonna 3 (la C dove ci sarà il secondo kmtrggio)
For C = 3 To ncol
'sotto: facciamo la differenza tra la cella spazzolata e la precedente e si somma il valore al 'totalizzatore (tot)
tot = tot + Cells(riga, C).Value - Cells(riga, C - 1).Value
Next
'si continua alla cella della colonna successiva e si ripete differenza e somma
Cells(riga, 15) = tot
'al termine del ciclo si assegna il valore che avrà tot alla cella O15
End Sub

Questa routine ci consente di ottenere il totale dei soli mesi registrati, cioè quello che cercavamo.

Però la routine è incompleta, manca infatti un'altro ciclo che oltre alle colonne, spazzoli anche le righe, per tanti agenti quanti ne saranno. Anche qui per reperire il numero esatto di righe che formerà l'elenco, ricorriamo a End, ma andando verso il basso (xlDown), e partendo dalla prima cella, la A1, quella che porta la scritta "Nomi". La routine è questa, e commenterò solo le istruzioni non presenti nella precedente:

Sub SommaDiff2()
Dim C As Integer

'sotto: con X impostiamo la zona utile dei nomi agenti, a partire dalla cella A1 fino 'all'ultima riga occupata
Set X = Range([A1], [A1].End(xlDown))
nriga = X.Rows.Count
'con nriga contiamo quante righe sono occupate
tot = 0
'impostiamo il totalizzatore a zero

'sotto: iniziamo il primo ciclo che spazzola le righe iniziando dalla riga due(primo agente)
For riga = 2 To nriga


Set Y = Range(Cells(riga, 1), Cells(riga, 1).End(xlToRight))
ncol = Y.Columns.Count
For C = 3 To ncol
tot = tot + Cells(riga, C).Value - Cells(riga, C - 1).Value
Next  
'si continua a scorrere le colonne
Cells(riga, 15) = tot  
'riportiamo il totale nella cella riga, colonna 15 la O
tot = 0
  'azzeriamo il totalizzatore per il successivo ciclo sulle righe
Next
'si continua a scorrere le righe
End Sub

Unica precisazione: il totale lo imposteremo nella colonna O (due colonne oltre la colonna M) per lasciare la colonna N vuota in quanto dovrà servire come colonna di stop alla funzione End nella ricerca di colonne occupate.

 

Buon lavoro

prelevato sul sito www.ennius.altervista.org