Filtrare i dati di un elenco e ordinare il risultato della filtrazione. - dal 04/09/04 pagina vista: volte

Un'altro esercizio sull'ordinamento dati in un elenco, motivato da una domanda che credo richieda alcuni chiarimenti di interesse generale. Ma vediamo una sintesi della richiesta:

"...Avendo un file di excel dove nella prima colonna si descrive il nome e cognome, nella seconda la data di nascita, nella terza il sesso, nella quarta l'altezza, nella quinta il numero di figli, nella sesta il piano di abitazione e volendo ordinare le righe in ordine crescente per data di nascita a parità di data di nascita in ordine (e quindi per) per sesso (primi i maschi e dopo le donne ) a parità di data di nascita, sesso, per altezza in ordine crescente, a parità di data di nascita, di sesso, di altezza, per numero di figli in ordine decrescente (chi ha più figli) a parità di data di nascita, sesso, altezza, numero figli, per piano di abitazione....."

Il tipo di ordinamento richiesto, ottenibile peraltro con database Access impostando una Query dove con OrderBy possiamo decidere i criteri di priorità, in Excel va affrontato con il Filtro. Solo usando una filtrazione in successione, basata sui criteri voluti, uno dopo l'altro, e completata da un ordinamento poi sui valori risultanti in funzione di una chiave di ordinamento, potremo ottenere la risposta al quesito posto.

Presento quindi un esercizio, che potrà risultare un pò complesso, ma che non è molto difficile, e che può essere ampliato a piacere.

Condizione importante da non trascurare, è che i dati correlati al criterio di filtrazione (quindi tutti i dati della stessa riga), si MUOVANO insieme al criterio scelto, e così pure avvenga quando faremo l'ordinamento. L'esercizio si basa fondamentalmente su due routine (o macro che dir si voglia), una routine che attiva il Filtro Automatico, ed una routine per l'ordinamento dei dati filtrati: quindi SOLO sui dati filtrati.

Per rendere versatile sia la scelta del criterio di filtrazione, sia la scelta della chiave di ordinamento, ho previsto delle InputBox che chiederanno su quale campo effettuarli. Poichè nell'applicazione del filtro, vorremo appunto decidere quale "campo" (intestazione di colonna) scegliere, dobbiamo anche poter selezionare un valore tra quelli presenti nel campo, in modo da effettuare la filtrazione; per questo ho munito la prima macro (quella che attiva il filtro) anche della presenza di una UserForm con una ListBox, dove appariranno le voci relative al campo selezionato. Cliccando su una voce presente nella ListBox (criterio di filtrazione), attiviamo l'esecuzione della filtrazione su quel criterio selezionato. Apparirà quindi la seconda InputBox che ci chiederà di scegliere la chiave di ordinamento, ed una volta effettuata la scelta, otterremo il nostro ordinamento. I più volenterosi potranno incrementare la prima parte delle istruzioni, riproponendo filtrazioni successive prima di passare all'ordinamento finale, io mi limito a formalizzare la procedura di base. Vediamo intanto una ipotetica tabella da usare come esempio:

Come si nota, i prog 8,9 e 14 sono relativi ad uno stesso nome, hanno valori diversi ma già in ordine crescente, ma le date non sono in progressione. Nell'esempio che faremo sceglieremo quindi la colonna B (campo "Nomi"), quindi selezioneremo il nome "ne" ed applicheremo un'ordinamento ascendente sulla colonna D (campo "Data")

Una regola importante è quella di destinare una colonna ad ospitare un numero progresivo (Colonna A nell'esempio). Ci servirà comunque per ripristinare l'ordine originale dei dati dopo i vari ordinamenti. Passiamo ad esaminare le routine, in verde i soliti commenti. Per prima esaminiamo la macro Sub Filtra() che inseriremo in un modulo standard.

Questa routine usa una variabile che dovrà essere visibile anche dalle istruzioni poste nella UserForm; per questo useremo la parola Public anzichè Dim per il suo dimensionamento, dichiarando obbligatoriamente anche di che "tipo" di variabile si tratta, e la posizioneremo nella zona "Generale  -  Dichiarazioni" che si trova a inizio modulo

'Zona   Generale  - Dichiarazioni   del modulo

Public campo As Variant

'--------------------------------------------------------------------------------------------
Sub Filtra()
'sotto: con la variabile "campo" memorizziamo il NUMERO della colonna (anzichè il "nome del 'campo") che rappresenta il campo che vorremo filtrare, e che otterremo tramite la inputbox. 'Questo numero anzichè una stringa o una lettera di colonna, è necessario in quanto l'applicazione 'del filtro prevede che come valore per indicare il Field si indichi un numero, e non altro.
campo = InputBox("Scrivi il NUMERO del campo da filtrare")
If campo = "" Then Exit Sub
'se non scriviamo niente, o si annulla, si esce dalla routine


'ora, la proprietà RowSource che usiamo per "caricare" la ListBox coi valori presenti nelle celle 'della colonna scelta come campo, accetta intervalli di celle di fogli di lavoro di Microsoft Excel. 'Abbiamo quindi bisogno di reperire i riferimenti (gli Address) alle due celle che delimitano le 'celle della colonna scelta con la variabile "campo", per questo uso due variabili, "inizio" e "fine", 'fornendo come riga la riga che contiene le intestazioni di campo (la riga 2) e come colonna uso la 'variabile "campo".
inizio = Cells(2, Val(campo)).Address
fine = Cells(2, Val(campo)).End(xlDown).Address

'sotto: ora chiamo la userform e fornisco i riferimenti appena reperiti al RowSource della listbox1
UserForm1.Show
UserForm1.ListBox1.RowSource = "" & inizio & ":" & fine & ""

'questa istruzione ci consente quindi di avere come elenco nella listbox l'elenco presente nella 'colonna che di volta in volta sceglieremo, rendendo variabile il tutto.
End Sub

Vediamo una sequenza d immagini che illustrano l'istruzione appena citata: applicheremo il filtro alla colonna B, (nomi), quindi la numero 2, chiamando la routine da un pulsante posto sullo stesso foglio: premendo il pulsante OK sulla inputbox, questa sparisce e compare la userform con la listbox già "caricata".

  la InputBox dove scrivere il numero del campo

la UserForm con la lista e il    contenuto della colonna scelta

Vi siete accorti che ancora non abbiamo selezionato niente? Eppure per applicare un filtro dobbiamo definire l'intervallo di celle che formano le intestazioni di campo; lo faremo ora. Le prossime istruzioni le attiveremo con l'evento Click della ListBox. Sarà infatti attraverso la selezione di un valore presente nella lista che generiamo l'evento Click, e qui stanno le istruzioni per l'applicazione del filtro; istruzioni che faranno uso anche della variabile Public posta sul modulo, vediamole:

Private Sub ListBox1_Click()
'sotto con la variabile "scelta" identifichiamo il valore che avremo selezionato nella listbox
scelta = UserForm1.ListBox1.Text
ActiveSheet.Select   'indi selezioniamo il foglio di lavoro
Range("A2:D2").Select
'e quindi selezioniamo l'intervallo su cui applicare il filtro
Unload Me 
'IMPORTANTE!! chiudiamo la userform altrimenti non possiamo continuare le 'istruzioni sottostanti, si genererebbe un errore di run-time. Anche se chiudiamo la form le 'istruzioni sotto vengono eseguite completamente.
Selection.AutoFilter 
'applichiamo il filtro sulla selezione, usando come Field il numero portato da '"campo" e come criterio di filtrazione la variabile "scelta" (selezionata nella listbox)
With Selection
.AutoFilter Field:=campo, Criteria1:=scelta
End With

'una volta applicato il filtro, chiamiamo la macro che si occuperà dell'ordinamento:
OrdinaAScelta
End Sub

Questa sotto sarà la situazione visiva appena dopo aver deciso cosa selezionare: otteniamo la filtrazione sul criterio scelto, ed appare l'ImputBox che ci chiede di indicare la LETTERA della colonna su cui applicare l'ordinamento:

Come si vede abbiamo scritto la lettera "D" che corrisponde alla colonna Date, e appenna premiamo OK, otterremo l'ordinamento in base alla data:

Ora vediamo la macro Sub OrdinaAScelta() responsabile dell'ordinamento appena visto, e che avremo inserito sullo stesso modulo dell'altra. Da notare che la zona che interesserà tutto l'ordinamento, la prendiamo DOPO che il filtro è stato applicato, identificando l'area con End, che ci consente appunto di definire i riferimenti estremi SOLO dell'area filtrata. In questo modo avremo sempre la selezione di un'area qualunque sia la sua estensione:

Sub OrdinaAScelta()

'sotto: con la variabile "campus" reperiamo la lettera di colonna tramite la InputBox
campus = InputBox("Inserire la lettera di Colonna del campo da ordinare")
If campus = "" Then Exit Sub
'usciamo se annulliamo o non scriviamo niente
'sotto: settiamo con la variabile "zonaord" tutta l'area filtrata
Set zonaord = ActiveSheet.Range([a2], [d2].End(xlDown))
'indi si applica l'ordinamento su "zonaord" impostando come chiave di ordinamento il Range 'composto dalla variabile "campus" che rappresenta la lettera di colonna, più la riga intestazione di 'campo (che è la riga 2)
zonaord.Sort Key1:=Range("" & campus & "2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
End Sub

Se poi vogliamo tornare all'elenco originale, dovremo togliere il filtro, tutt'ora selezionato, e ripristinare l'elenco in base al numero progressivo messo nella colonna A:

Sub TogliFiltro()

Range("A2:D2").Select
Selection.AutoFilter
Set zonaord = ActiveSheet.Range([a2], [d2].End(xlDown))
zonaord.Sort Key1:=Range("A2"), Order1:=xlAscending, _

Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom

End Sub

Bene, spero di avervi chiarito una maniera per filtrare e ordinare i vostri dati.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org