Un filtro sui dati: che cosa è - come fare

 

Il problema

Molto spesso mi giungono richieste come queste:

Ma che cosa è un filtro? Quando parliamo di filtri ci sembra di parlare di cose grandi. In realtà, ogni volta scriviamo un Se o un If non facciamo altro che applicare un certo tipo di filtro.

Nel sito non si fa altro che parlare di questo. Certo magari non troviamo come colorare una cella se...; come rendere in grassetto e rosso il nome di una persona se ...

Come vedete le domande, anche se molteplici, sono sostanzialmete sempre e comunque la stessa.

La questione si risolve usando la funzione Se se siamo nel foglio di Excel oppure la istruzione If se siamo in VBA.

 

Questa volta, invece di risolvere tutti questi quesiti, sono io a porne uno.


Preparazione del lavoro

Dal seguente elenco di articoli di un magazzino dobbiamo estrarre prima (filtrare), e stampare dopo, quegli articoli che in una tabella hanno una giacenza inferiore ad una determinata sottoscorta (quantità minima di giacenza) precedentemente determinata. Questa sottoscorta non è uguale per tutti: per alcuni articoli la sottoscorta è più alta e per altri è più bassa come è descritto nella colonna F, quella rosa.

  A B C D E F G H
1   Prodotto Q.tà ord Uscita Giacenza Sottosc P.zo unit Val Mag
2 1 Chai 25 10 15 5 € 2,70 € 40,50
3 2 Chang 30 24 6 5 € 1,19 € 7,13
4 3 Aniseed Syrup 15 14 1 5 € 1,25 € 1,25
5 4 Chef Anton's Cajun Seasoning 50 48 2 10 € 0,69 € 1,38
6 5 Chef Anton's Gumbo Mix 50 46 4 5 € 0,89 € 3,56
7 6 Grandma's Boysenberry Spread 20 18 2 10 € 3,13 € 6,25
8 7 Uncle Bob's Organic Dried Pears 20 18 2 10 € 3,75 € 7,50
9 8 Northwoods Cranberry Sauce 50 12 38 5 € 5,00 € 190,00
10 9 Ecc            

 

L'elenco da stampare lo depositiamo in un altro foglio (il foglio2) usando questo schema:

  A B C D E
1 ID Prodotto Q.tà P.zo unit Tot
2          
3          
4          

 

L'unica cosa di cui dobbiamo tener presente nell'ordine da fare è che la quantità da ordinare la abbiamo fissata a 10 volte la sottoscorta imposta per ogni singolo articolo.

Cominciamo allora a preparare il nostro lavoro.

Nella colonna E del foglio1 per la giacenza in magazzino eseguiamo la differenza tra la quantità di merce entrata in magazzino e la quantità di merce uscita =SE(B2<>"";C2-D2;""). Usiamo la funzione Se per evitare di vedere fastidiosi 0 (zero) se la tabella non è riempita

Nella colonna H calcoliamo il valore del magazzino moltiplicando la giacenza del magazzino per il prezzo unitario di ogni singolo articolo usando ancora la funzione Se per il motivo appena visto: =SE(B2<>"";E2*G2;"")

Finito questo lavoro sul foglio1 passiamo a preparare il foglio2 preparando lo schema appena visto.

In questa tabella, nella colonna E calcoliamo l'importo totale per ogni singolo articolo ordinato =SE(C2<>"";C2*D2;"") usando ancora la funzione Se.

Dopo la scrittura di ciascuna di queste formula, per non ripetere questa incresciosa operazione per ciascuna voce, possiamo usare il metodo del trascinamento verso il basso per copiarla automaticamente in tutte le celle interessate.

 


Preparazione del codice: filtraggio della tabella

Compiute queste operazioni preliminari siamo pronti per andare a scrivere il codice per eseguire la stampa del promemoria dell'ordine in un modulo standard. Quel che dobbiamo fare è semplice:

Se un articolo ha una giacenza inferiore al valore della sottoscorta che abbiamo deciso di avere per i singoli articoli (ovvero, se un articolo ha la sottoscorta superiore al valore della giacenza attuale) dobbiamo metterlo in una lista che poi andremo a stampare.

Cominciamo col dare un nome alla nostra routine

Sub SottoScorta()
 
End Sub

e col definire alcune variabili che ci serviranno nel lavoro.

Queste variabili sono:

Dim Intervallo As Range, CL As Range
Dim Riga

La prima operazione da compiere è quella di determinare la lunghezza della nostra tabella.

Riga = 1
While Cells(Riga, 1) <> ""
Riga = Riga + 1
Wend
Riga = Riga - 1

Ora che sappiamo di quante righe è composta la tabella e possiamo definire l'intervallo su cui lavorare e, precisamente, dalla seconda riga all'ultima contata escludendo di fatto la prima riga che è la riga riservata alle intestazioni di colonne (i nomi dei campi)

Set Intervallo = Range(Cells(2, 6), Cells(Riga, 6))

Fatto questo possiamo iniziare il nostro filtraggio dei dati. Siccome, mentre leggiamo la tabella in esame dobbiamo trasferire i dati che rispettano le nostre condizioni sul secondo foglio, per alleggerire la scrittura del codice iniziamo a referenziare quest'ultimo con l'istruzione:
With ... End With
che ci permette di evitare la referenziazione al foglio2 ogni volta che serve.

Si inizia col cancellare tutta la tabella del Foglio2, facendo eccezzione dell riga delle intestazioni e della colonna delle formule (attenzione al punto posto prima di Range e di Cells; questi fanno riferimento al Foglio2):

With Worksheets("Foglio2")
.Range(.Cells(2, 1), .Cells(Riga, 4)).ClearContents
.............
End With

Fatto questo possiamo iniziare i lavori.

Impostiamo ad 1 la variabile Riga che dovrà puntare alla prima riga disponibile della tabella del Foglio2 che dovrà accogliere l'elenco degli articoli da ordinare ed iniziamo un ciclo, basato su For Each, per spazzolare la colonna F della tabella del Foglio1

With Worksheets("Foglio2")
.Range(.Cells(2, 1), .Cells(Riga, 4)).ClearContents
Riga = 1
For Each CL In Intervallo
'per Intervallo si intende la colonna F della tabella in esame
..............
Next

End With

Ecco come applichiamo questo filtro alla nostra tabella:

If CL.Value >= CL.Offset(0, -1).Value Then

CL è la cella puntata nel ciclo For Each

CL.Offset(0, -1) è la cella immediatamente a sinistra (stessa riga - 0 - , una colonna a sinistra -1)

Ora siamo pronti a scrivere il codice all'interno del ciclo For Each:

With Worksheets("Foglio2")
.Range(.Cells(2, 1), .Cells(Riga, 4)).ClearContents
Riga = 1
For Each CL In Intervallo
If CL.Value >= CL.Offset(0, -1).Value Then
Riga = Riga + 1
.Cells(Riga, 1) = CL.Offset(0, -5)
.Cells(Riga, 2) = CL.Offset(0, -4)
.Cells(Riga, 3) = CL.Value * 10
.Cells(Riga, 4) = CL.Offset(0, 1)
End If

Next
End With

Ecco il lavoro eseguito.

Una volta avviato il ciclo For Each verifichiamo se il valore della cella in esame (la variabile CL) è maggiore o uguale al valore della cella immediatamente a sinistra.

Se la verifica è positiva:

incrementiamo di una unità la variabile Riga che punta alla prima riga disponibile sul Foglio2

Copiamo nel Foglio2 a partire dalla prima colonna

Questa operazione ci restituisce, nel Foglio2, una tabella del genere:

  A B C D E
1 ID Prodotto Q.tà P.zo unit Tot
2 3 Aniseed Syrup 50 € 1,25 € 62,50
3 4 Chef Anton's Cajun Seasoning 100 € 0,69 € 68,75
4 5 Chef Anton's Gumbo Mix 50 € 0,89 € 44,48
5 6 Grandma's Boysenberry Spread 100 € 3,13 € 312,50
6 7 Uncle Bob's Organic Dried Pears 100 € 3,75 € 375,00
7 14 Tofu 50 € 0,87 € 43,60

 

Preparazione del codice: preparazione della stampa del promemoria

Per la stampa del promemoria dobbiamo affidarci ai pochi mezzi che ci offre il VBA per Excel.

Infatti in questo ambiente non abbiamo a disposizione molti mezzi. Infatti, per quel che ne so non abbiamo a disposizione un oggetto Printeer, come lo abbiamo in VB6. Vista la scarsezza dei mezzi non penso valga la pna spremerci per trovare soluzioni persolalizzate per cui niente di meglio che affidarci al registratore di macro per eseguire questa operazione.

Io personalmente, fosse solo per un risparmio di carta e di inchiostro, preferisco andare in stampa tramite l'anteprima di stampa.

Questo è il codice, ovviamento dopo molte limature, che ottengo dal registratore di macro e che implemento nella mia routine:

With Worksheets("Foglio2").PageSetup
.CenterHorizontally = True
.PrintArea = Range("A1").CurrentRegion.Address
.Orientation = xlPortrait
.CenterHorizontally = True
.CenterVertically = False
.Zoom = 150
End With

Worksheets("Foglio2").PrintPreview

Mi sembra che ulteriori spiegazioni per questo segmento di codice siano inutili tranne per dire, forse, che qui si compiono due azioni distinte:

La formattazione della pagina nella prima parte (equivalente a File / Imposta pagina) e l'anteprima di stampa nella seconda parte (equivalente a File / Anteprima di stampa)

A questo punto possiamo senz'altro presentare l'intera routine senza ulteriori commenti.

Sub SottoScorta()
Dim Intervallo As Range, CL As Range
Dim Riga
Riga = 1
' reperimento runghezza della tabella
While Cells(Riga, 1) <> ""
        Riga = Riga + 1
Wend
Riga = Riga - 1
Set Intervallo = Range(Cells(2, 6), Cells(Riga, 6))
Application.ScreenUpdating = False
' ricerca e trascrizione degli articoli che scarseggiano
With Worksheets("Foglio2")
        .Range(.Cells(2, 1), .Cells(Riga, 4)).ClearContents
        Riga = 1
        For Each CL In Intervallo
                CL.Offset(0, -1).Select
                If CL.Value >= CL.Offset(0, -1).Value Then
                        CL.Select
                        Riga = Riga + 1
                        .Cells(Riga, 1) = CL.Offset(0, -5)
                        .Cells(Riga, 2) = CL.Offset(0, -4)
                        .Cells(Riga, 3) = CL.Value * 10
                        .Cells(Riga, 4) = CL.Offset(0, 1)
                End If
        Next
End With
' preparazione del foglio per la stampa
With Worksheets("Foglio2").PageSetup
        .CenterHorizontally = True
        .PrintArea = Range("A1").CurrentRegion.Address '"$A$3:$F$15"
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .CenterVertically = False
        .Zoom = 150
End With
' stampa (anteprima) della pagina
Worksheets("Foglio2").PrintPreview
Application.ScreenUpdating = True
End Sub

Ecco siamo arrivati alla fine. Spero di aver contribuito a chiarire il concetto che molti di noi abbiamo sul filtraggio delle tabelle.

Certamente molti di noi non avranno mai una situazione del genere, ma perlomeno, spero, da questo esempio possiamo partire per eseguire lavori anche più complessi.

 

 

La formula: =SE(B2<>"";C2-D2;"")
Calcolo della giacenza
La formula: =SE(B2<>"";E2*G2;"")
Calcolo del valore del magazzino
La tabella del foglio2
La tabella del foglio2
La formula: =SE(C2<>"";C2*D2;"")
Calcolo dell'importo dell'ordine
Il trascinamento delle formule
Il trascinamento
Il trascinamento delle formule
Il trascinamento