Dati Filtrati: ottenere il loro totale e il valore massimo presente

Dati Filtrati: ottenere il loro totale e il valore massimo presente - ma anche Somma.se e Conta.se su dati filtrati. Aggiornamento del 26/08/09 - dal 04/09/04 pagina vista: volte

Quando usiamo il filtro lo facciamo perchè vogliamo "raggruppare" e vedere solo i dati corrispondenti al criterio di filtrazione. In tutti i casi in cui sia necessario "lavorare" con i dati così filtrati, ci troviamo di fronte ad alcuni problemini: infatti noi vediamo solo i dati relativi al solo criterio scelto, solo perchè l'applicazione di un filtro "nasconde" le righe con i dati che non interessano (che sono diversi) il criterio scelto, ma Excel le righe nascoste le vede e le considera;; se noi volessimo per esempio sommare tutti i valori relativi al dato scelto, dovremmo fornire per forza di cose i riferimenti all'intervallo di celle da sommare, e qui cominciano i problemi: se infatti i dati cercati sono su righe discontinue, indicando il riferimento iniziale e quello finale, comprenderemmo inevitabilmente anche i dati appartenenti alle celle nascoste, falsando quindi il totale da noi richiesto. Vediamo un esempio di una ipotetica tabella in cui a dei nomi sono associati dei valori:

Qui non ci occupiamo dell'applicazione di un filtro (peraltro trovate esempi specifici sui siti), ma solo di come poter ovviare al problema su descritto. Quindi, se noi si volesse cercare il totale dei valori di Bianchi, troveremmo che l'intervallo che contiene il dato Bianchi, inizia con la riga 1 e termina con la 15, dovremmo quindi indicare l'intervallo B1:B15. Ma tra questi due estremi, esistono dati che con Bianchi non hanno niente a che fare, e che verrebbero visti lo stesso e sommati. L'immagine a destra mostra la tabella con il filtro applicato, dove si vedono chiaramente le righe nascoste (sono infatti visibili solo le righe 2-5-7-9-12 e 15).

Se poi oltre al totale dei valori legati a Bianchi, volessimo calcolare anche l'importo maggiore tra i valori presenti, dovremmo ricorrere alla funzione MAX, che comunque anche lei, necessita dell'intervallo di cui considerare il valore massimo presente, e anche qui ci troveremmo di fronte al fatto che verrebbero valutati tutti i valori dell'intervallo, compresi quelli che con Bianchi non c'entrano, falsando il risultato.

Tra i vari modi di risolvere il problema, presento una soluzione, con due varianti, che si basa sulla lettura dei dati delle SOLE righe visibili. Userò quindi la proprietà Hidden (nascosto) dell'oggetto Range, che a valore True se le righe (Rows) o le colonne sono nascoste, o False se viceversa, creando una condizione If .. Then di controllo.

In questo modo, inserendo la condizione in un ciclo For Next oppure For Each Next, facciamo controllare se le righe NON sono nascoste (quindi Hidden = False ), in questo caso prendiamo il valore che ci interessa e lo totalizziamo. Alla fine del ciclo, il totale ottenuto ci fornirà la somma di tutti i soli valori visibili (cioè quelli filtrati).

Per quanto riguarda invece il trovare il valore più alto (Max) tra quelli visibili, possiamo operare in due modi:

Negli esempi che propongo, sia il Totale che il valore Max li faccio restituire in una MsgBox, ma è chiaro che le variabili che rappresentano i due valori, saranno usate come ognuno meglio crede, o registrandole in altre celle, oppure usare in altre istruzioni.

Vediamo le due alternative: in entrambe faccio svolgere il ciclo cercando il nome anzichè il valore; il perchè è semplice: se il filtro lo abbiamo applicato al campo "nomi", siamo sicuri che le righe visibili sono tutte quelle che corrispondono al criterio scelto., quindi useremo un ciclo sulla colonna A, prendendo poi con Offset, il valore nella cella, stessa riga, una colonna a destra (Offset(0, 1))

Sub TotaleeMassimo1()
Dim cl As Range
'sotto: si inizia un ciclo che scorre ogni cella (cl) dell'intervallo A2:A15 (o quello che sarà nel 'vostro elenco
For Each cl In Range("A2:A15")
'sotto: se la riga (Rows) della cella (cl) in quel momento letta, non è nascosta, allora..
If cl.Rows.Hidden = False Then

'sotto. usiamo una variabile "tot" che sarà uguale al valore di "tot" più il valore della cella a destra 'rispetto a quella letta (cl)
tot = tot + cl.Offset(0, 1).Value

'sotto: lo stesso valore lo riportiamo sulla stessa riga della cella letta, ma tre colonne a destra
cl.Offset(0, 3) = cl.Offset(0, 1).Value
End If
Next 'si continua a ripetere le istruzioni sopra fino all'ultima cella prevista (A15)

'sotto: con la variabile "w" contiamo quante righe sono occupate nella colonna A (la 1, quella dei 'nomi: in questo modo otteniamo un numero che ci assicura la presenza di dati nella colonna A, 'numero che fornirà nella successiva istruzione, il numero dell'ultima riga che rappresenta l'estremo 'dell'intervallo necessario alla funzione Max
w = Range(Cells(2, 1), Cells(2, 1).End(xlDown)).Rows.Count

'sotto: settiamo la variabile "x" come zona su cui eseguire la funzione Max
Set x = Range(Cells(2, 4), Cells(w, 4).End(xlDown))
'sotto: con la variabile "z" otteniamo il valore massimo
z = WorksheetFunction.Max(x)

'sotto: si avvisa con un messaggio (ma voi potrete usare "tot" e "z" come meglio credete). "tot" 'ora è la somma dei valori di Bianchi, e "z" ora è il valore massimo tra qui valori.
MsgBox "Totale € " & tot & " - Importo Max € " & z
x.ClearContents 'indi si pulisce l'area "x"
End Sub

e questa un'imagine del risultato, prima della cancellazione dell'area "x"nella colonna D

Sub TotaleeMassimo2()
Dim cl As Range
cont = 0 'inizializziamo le tre variabili "cont", "conta" e "valore" a zero
conta = 0
valore = 0
For Each cl In Range("A2:A15")   'inizio ciclo
conta = cont    'si rende "conta" uguale a "cont"
If cl.Rows.Hidden = False Then   'si controlla che la riga sia visibile, in tal caso:
valore = cl.Offset(0, 1).Value 'si assegna a "valore" il valore della cella letta in quel momento

tot = tot + cl.Offset(0, 1).Value 'si incrementa il totalizzatore
End If

'si crea, sotto, la condizione di verifica: se "valore" è maggiore di "conta" (e all'inizio è maggiore),
If valore > conta Then
cont = valore ' si rende "cont" uguale a "valore"
End If
Next
MsgBox "Totale € " & tot & " - Importo Max € " & cont 'indi si avvisa con un messaggio, ma voi, 'come detto, potrete usare le variabili "tot" e "cont" come vi pare. Voglio solo precisare che "cont" 'alla fine del ciclo sarà sempre il massimo valore letto.

End Sub

e questa un immagine del risultato, in cui ho filtrato il nome Rossi, che portando il valore massimo in terza posizione, dimostra la validità della routine sopra:

E' evidente che la possibilità di lavorare quindi sulle righe NON nascoste (Hidden = False) ci consente tutta una serie di operazioni sfruttando cicli (For Next) che verificano condizioni (If Then) ed eseguano istruzioni appropriate; una recente domanda è alla base di questo aggiornamento: "...Ho un elenco di 500 alunni, maschi e femmine, di varie classi. Ebbene, quando metto il filtro per filtrare una classe, in una cella ottengo il subtotale di quanti alunni sono nella classe. A me servirebbe anche sommare, per quella classe filtrata, il numero di m o f che compaiono. Usando la formula conta.se o somma.se, non riesco a inserire l'intervallo, in quanto con il filtro la numerazione delle celle resta sempre quella reale."

Mi sembrava che, suggerito come operare in presenza di dati filtrati, fosse relativamente semplice costruirsi nuove istruzioni adattandole ai concetti più sopra esposti, ma evidentemente qualche esempio in più agevola i lettori meno smaliziati.

Dunque, sulla base della domanda di quel lettore, possiamo simulare una tabella del genere: ho impostato solo 4 campi: nome alunno, classe di appartenenza, sesso, un generico campo "valori" giusto per consentire somme di valori diversificati per maschi e femmine;

la tabella ha già il filtro applicato, quindi cliccando sul pulsante "Lanciami", attiveremo le seguenti istruzioni:

Sub ContaMaschiFemmineESommaValori()
ultimarigaoccupata = ActiveSheet.Range("A65536").End(xlUp).Row 'reperiamo il numero dell'ultima riga occupata, colonna A
totmaschi = 0 'impostiamo a zero i totalizzatori
valorem = 0
totfemmine = 0
valoref = 0
For n = 2 To ultimarigaoccupata
If Rows(n).Hidden = False Then 'SE LA RIGA ora letta NON è NASCOSTA (quindi hidden = false) CIOè è FILTRATA
riga = Rows(n).Row 'prendiamo il numero di questa riga scoperta e lo usiamo come primo argomento nella sintassi 'Cell(riga, colonna), quindi controlliamo SE la cella, stessa riga, colonna 3 è uguale a m oppure a f e incrementiamo i totalizzatori
If Cells(riga, 3) = "m" Then totmaschi = totmaschi + 1
If Cells(riga, 3) = "m" Then valorem = valorem + Cells(riga, 4) 'e sommiamo il valore che sarà nella colonna 4 (la D) per i "m"
If Cells(riga, 3) = "f" Then totfemmine = totfemmine + 1
If Cells(riga, 3) = "f" Then valoref = valoref + Cells(riga, 4) 'e sommiamo il valore che sarà nella colonna 4 (la D) per le "f"
End If
Next

'alla fine del ciclo avvisiamo con un messaggio circa i totali (ma potremmo inviare i totali a variabili o a celle sul foglio)
MsgBox "I Maschi sono " & totmaschi & " le Femmine sono " & totfemmine & vbCr _
& "Il Totale valori Maschi è " & valorem & " il totale valori Femmine è " & valoref
End Sub

Credo che le istruzioni non siano difficili da capire, è chiaro che sono solo esempi ma dovrebbero fornire spunto per adattarle alle proprie esigenze.

Io non conosco formule o funzioni del foglio di lavoro che permettano di rilevare se una riga è nascosta o scoperta, e che consentano quindi di ottenere conta.se o somma.se o comunque operazioni sui dati filtrati (a parte i subtotali o i totali). Morale, ulteriore esempio che il vba è veramente malleabile e consente di fare (quasi) tutto con Excel.