Ordinamento scadenzario con raggruppamento clienti e somma degli importi delle loro fatture.

Fra i diversi metodi che Excel ci offre per ottenere da un elenco, per esempio uno scadenzario, il raggruppamento dei dati relativi ad una chiave prestabilita (per esempio il Codice Cliente o il nominativo Cliente), ottenibili con un filtro, e relativi sub totali del campo "Importo fattura", sempre attraverso le risorse di Excel (menù Dati/Subtotali), presento una soluzione realizzata in vba che non richiede l'uso del filtro. Vediamo un esempio, nella foto sotto vediamo una tipica tabella, che potrebbe essere anche un "Riepilogo Fatture emesse" anzichè uno scadenzario, il concetto non cambia:

Come si nota nell'elenco, i dati non sono raggruppati per Cliente, ma mescolati. Avremo bisogno quindi di eseguire un "ordinamento" in modo da riunire tutti i clienti con lo stesso nome oppure con lo stesso codice cliente, noi scegliamo la chiave "codice cliente". poichè sappiamo da quanti campi (colonne) è formato il nostro elenco, ma non possiamo sapere quante righe occuperà, useremo la funzione End per reperire i "riferimenti" delle ultime celle: sia quella verso il basso (End(xlDown) a partire dalla cella di inizio elenco (la A2), sia quella verso destra (End(xlToRight)) sempre a partire dalla stessa A2. In questo modo selezioneremo tutta l'area dati che ci interessa, e su quell'area applicheremo l'ordinamento usando come chiave di ricerca il codice cliente (SortKey1:= Range("A2")), e queste sono le istruzioni, che assoceremo ad un pulsante:

Sub Ordinadati()

giu = Range("A2").End(xlDown).Address
dx = Range("A2").End(xlToRight).Address

Range(giu & ":" & dx).Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

e questo sarà l'effetto dopo l'ordinamento; come di vede, sono stati riuniti tutti i clienti che hanno lo stesso codice uguale:

A questo punto cosa dovremo ottenere:

  • la separazione tra un cliente e l'altro con l'aggiunta di due righe, nella prima delle quali inseriremo la parola "totale" nella colonna B

  • l'inserimento nella colonna E, sempre nella prima delle due righe aggiunte, di una formula che cerchi, a salire, tutti gli importi relativi alle fatture di quel cliente, e sommi gli importi in modo da ottenere un totale cliente.

Per ottenere questo risultato:

  • useremo una InputBox nella quale inseriremo il codice cliente di cui vogliamo il totale.

  • useremo in ciclo di ricerca di questo valore in modo che venga selezionata l'ultima cella che sia uguale al codice indicato.

  • trovata questa cella, selezioniamo la riga immediatamente sotto, e con la cella in quel momento attiva, diciamo di inserire 2 nuove righe. Operazione necessaria visto che l'inserimento riga in Excel avviene sopra la cella (meglio riga) in quel momento selezionata, non sotto.

  • inseriamo nella prima delle due righe (colonna A) un trattino, nella colonna B la parola "totale" e nella colonna E la funzione SOMMA.

questo il codice impiegato:

Sub cerca()
With Worksheets(1).Range("a1:a20") 'range sui cui si opera
'istruzioni per l'imputbox che ti chiede il
'codice cliente
Dim code, mess, titolo
mess = "Inserisci il Codice Cliente :"
titolo = "Inserimento Codice"
code = InputBox(mess, titolo) '"code" ora è uguale al codice digitato

'inizio il ciclo di ricerca nel range scelto (A1:A20), del valore che
'è stato assegnato a "code"
Set c = .Find(code, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
'viene individuato l'ultimo "code" trovato e seleziono
'la cella sotto per poter aggiungere due righe
c.Offset(1, 0).Select
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
'aggiungo due righe
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.EntireRow.Insert Shift:=xlDown
'inizia l'inserimento nella prima cella di una lineetta (colonna A)
'poi inserisce "totale" nella cella accanto,
ActiveCell = "-"
ActiveCell.Offset(0, 1) = "totale"

'poi controlla la colonna importi, (la E) e ne prende i riferimenti a salire: X sarà uguale al 'riferimento cella attiva + 4, cioè la colonna E, ma la cella nella riga immediatamente 'sopra (-1). Poichè si può verificare il caso che esista un cliente con una sola fattura, 'sarebbe inutile sommare, ed allora eseguiamo un controllo sulla seconda riga a salire, colonna E, Se questa cella conterrà dati (diversa da vuota), allora si ricercano tutte le 'righe a salire con End, per prendere l'ultimo riferimento

If ActiveCell.Offset(-2, 4) <> "" Then
X = ActiveCell.Offset(-1, 4).Address

'poi preso il riferimento dell'ultima cella con End(xlUp) contenete valori Y sarà uguale al riferimento  cella attiva + 4, cioè la colonna E ma alla prima (la più in alto) cella iniziale.

'Ricordo che l'istruzione End cerca l'ultima cella occupata nella direzione indicata dalla 'costante, a partire dalla cella iniziale indicata (in questo caso: ActiveCell) e si ferma 'appena trova una cella vuota.
Y = ActiveCell.Offset(-1, 4).End(xlUp).Address

'indi applica la funzione somma
ActiveCell.Offset(0, 4).Formula = "=SUM(" & X & ":" & Y & ")"

'nel caso previsto che il cliente sia presente con una sola fattura, invece rendiamo il totale 'uguale all'importo della fattura

Else
ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(-1, 4).Value
End If
End Sub

Una nota: perchè la somma si applichi in maniera corretta, sarà necessario procedere rispettando il codice progressivo: se per esempio, si cercasse di ottenere il totale del codice 3402 senza prima avere ottenuto i totali e quindi la separazione con due righe vuote del codice 3401, il codice 3402 verrebbe separato si da quello successivo, ma la somma coinvolgerebbe anche i valori relativi al codice 3401 non essendo separati.

Questa comunque l'immagine del risultato dell'istruzione sopra:

Buon Lavoro.

prelevato sul sito http://ennius.interfree.it