Lezione 12

Foglio elettronico

Parte sesta

 

1.0 Utilizzo dei filtri

1.1 Premessa

1.2 Un po' di teoria

1.3 Ricerche e operatori 

1.4 I Filtri automatici  

2.0 Automatizzare le ricerche con le macro

2.1 Registrazione della macro

2.2 Il listato della macro

2.3 Esecuzione della macro

2.4 Associare la macro ad un pulsante

2.5 Ripristino dell’archivio

3.0 Analisi statistiche di un data base

3.1 La zona criteri

 

 

1.0 Utilizzo dei filtri

1.1 Premessa

Questa lezione, l'ultima relativa al foglio elettronico, è accompagnata da un file che si può scaricare facendo clic qui: si tratta del file zippato di un elenco excel di 2.382 libri, con relativi autori, editore e prezzo.

Per gestire una base dati ci vuole un programma dedicato? 

In realtà lo si può fare molto bene, e in maniera decisamente semplice anche con Microsoft Excel. Basta sfruttare a fondo, le funzionalità dedicate che operano nell’ambito del menu Dati. E c’è di più: impostando opportune espressioni si possono eseguire analisi statistiche anche molto sofisticate sui contenuti di un archivio. 

Prima di cominciare, però, è importante chiarire il concetto di base dati, non solo da un punto di generale, ma soprattutto nell’ottica secondo la quale viene interpretato all’interno di Excel.

1.2 Un po' di teoria

Un data base, una base dati, o più semplicemente un archivio, rappresentano in ogni caso un insieme di dati omogenei raggruppati in unità elementari che vengono definite record

I record, a loro volta, si articolano in un certo numero di campi, una sorta di etichette la cui funzione è quella di classificare convenientemente i dati che vi vengono inseriti. I campi si definiscono in base ai dati che ospitano, così si parla di campi testo, campi numerici, campi data, se rispettivamente contengono testi, valori numerici o date. 

Facciamo un esempio. Un data base Libri, raccoglie tutte le informazioni disponibili relative a questi ultimi, e i suoi record li classificano finemente utilizzando i campi. I record, per esempio, saranno articolati in più campi: Argomento (campo testo), Titolo (campo testo), Prezzo (campo numerico) e così via, e ogni campo conterrà i corrispondenti dati dei libri posseduti.

Insomma, possiamo paragonare un data base ad uno schedario, e le schede cartacee in esso contenute ai record del nostro data base, o archivio che dir si voglia. 

A questo punto, come avevamo anticipato, vediamo come i dati in cui si articola un archivio vengono interpretati in ambiente foglio elettronico: ne più ne meno come una tradizionale tabella. Nelle celle della prima riga, quella che solitamente viene riservata alle intestazioni, si trovano i nomi dei campi, mentre le righe sottostanti corrispondono ai singoli record

Sotto questo punto di vista, pertanto, una  qualsiasi tabella Excel che presenti la suddetta struttura può essere assimilata ad un data base, e non a caso viene definita un archivio tabellare

Si noti che la tabella non deve presentare soluzioni di continuità, nel senso che non devono esservi righe o colonne interamente vuote.

1.3 Ricerche e operatori 

Per estrarre da un data base le informazioni che ci interessano, possiamo interrogarlo in vari modi. Per esempio, facendo riferimento al nostro archivio esemplificativo, è possibile individuare tutte le registrazioni relative ai libri relativi ad un determinato argomento, o quelli scritti da uno specifico autore. 

Queste interrogazioni vengono definite semplici perché ottemperano ad un solo criterio, ma i criteri possono anche essere combinati fra loro. 

Per esempio, si potrebbero individuare tutti i record che si riferiscono contemporaneamente ad un certo autore e a un certo editore. Quest’ultima tipologia di interrogazione viene definita una ricerca in And (o inclusiva),  perché viene ristretto il campo della ricerca. 

Infatti, nel nostro archivio, le registrazioni che si riferiscono, poniamo, all’argomento BIOGRAFIE, sono 55, ma quelle in cui anche il campo Autore soddisfa a un secondo vincolo (che il libro  sia stato scritto, per esempio, da Sperone Gigi) sono decisamente meno, per l’esattezza 2. 

Alle ricerche restrittive in And fanno da contrappunto quelle cosiddette estensive ( o in Or). 

In questo caso il campo di ricerca si può invece estendere notevolmente. Così, se vogliamo individuare i libri che possono essere indifferentemente una biografia o un dizionario, il numero di registrazioni individuate è sicuramente superiore.  

Gli operatori And e Or che vengono utilizzati per tali tipologie di ricerca sono definiti operatori booleani, ma vi sono altri operatori (cosiddetti relazionali), che consentono di interrogare un archivio utilizzando altri criteri. 

Ci riferiamo agli operatori uguale a, diverso da, maggiore di, minore di, e così via che consentono di impostare filtri anche molto sofisticati, e possono essere utilizzati, tanto per fare un esempio, per estrarre i record relativi ai libri il cui è importo è maggiore di un 30.000 lire. 

Excel mette a disposizione due procedure per eseguire una ricerca nell’ambito di un data base tabellare: filtri automatici e ai filtri avanzati che si attivano entrambi aprendo il menu Dati e selezionando le omonime voci. 

L’applicazione di un filtro automatico consente di eseguire estemporaneamente semplici interrogazioni di un archivio tabellare, mentre utilizzando un filtro avanzato si possono impostare interrogazioni anche molto sofisticate che prevedono l’utilizzo estensivo degli operatori booleani e relazionali.

1.4 I Filtri automatici  

I risultati che si possono ottenere  avvalendosi di questo tipo di filtri presentano qualche limitazione, ma sono comunque più che sufficienti per esplorare efficacemente il contenuto di un data base. In compenso sono estremamente facili da utilizzare e la loro risposta è immediata. 

Ma ecco come procedere, per estrarre dal nostro data base esemplificativo i record relativi ai libri con argomento BIOGRAFIE. Fare clic su una qualsiasi cella della tabella-archivio, aprire il menu Dati e selezionare la voce Filtro automatico

A fianco dei nomi dei campi compare un pulsantino con sopra impresso un triangolino con il vertice orientato verso il basso. Facendovi sopra clic si apre un elenco a discesa che ospita tutti dati (unici) che sono contenuti in quel campo dell’archivio. In altre parole, se in un campo compare anche tre volte una certa voce, questa verrà riportata una sola volta. 

A questo punto, selezionare il nome dell'argomento che ci interessa, BIOGRAFIE, determinando l’immediata contrazione della tabella in modo tale che vengono mostrati solo i record che contengono nel campo Prodotto la voce che abbiamo selezionato. 

Come si vede la risposta del sistema è immediata mentre la corrispondente procedura è decisamente semplice. In ogni caso, dal momento che sulla tabella risultante da una prima interrogazione se ne possono condurre altre sempre nello stesso modo, ecco aperta la strada alla esecuzione di un ricerca in And

2.0 Automatizzare le ricerche  

Di seguito svilupperemo una semplice macro per automatizzare le ricerche. Si tratta di una esercitazione che si rivelerà molto utile se in futuro ci dovesse capitare di rendere automatica qualche semplice applicazione. Quello che ci prefiggiamo di fare è impostare un programmino che determini l’esibizione di due box in cui si dichiarano rispettivamente il campo in cui effettuare la ricerca, e la voce da individuare, dopodiché, a inserimenti avvenuti viene eseguita l’interrogazione. La corrispondente macro verrà poi associata ad un pulsante in modo che chiunque, anche completamente all’oscuro della esistenza dei filtri automatici, possa comunque condurre una ricerca. In pratica, stiamo sviluppando una interfaccia che rende trasparente la procedura di interrogazione demandandola alla semplice pressione di un pulsante senza dover necessariamente passare attraverso la trafila dei menu. Tanto per strafare, è stato previsto anche un pulsante la cui pressione riporta l’archivio alle condizioni originali per dare modo di condurre una nuova ricerca partendo da zero Vale la pena di osservare che se dopo aver condotto una interrogazione si preme ancora il primo pulsante, è possibile eseguire un nuovo filtraggio dei risultati per eseguire una ricerca in And

Il primo passo da compiere è registrare una semplice macro che esegua una qualsiasi ricerca, dopodiché ne analizzeremo il listato e la modificheremo opportunamente per metterla in grado di operare a seconda dei nostri input.  Di seguito descriviamo sinteticamente le procedure di registrazione e gli interventi di modifica.  

2.1 Registrazione della macro 

Registrare una macro significa eseguire alla tastiera la procedura da automatizzare, previa attivazione della funzionalità di registrazione. Ecco come si procedere. 

 

Nota – Vale la pena di fare qualche osservazione relativa alla denominazione di una macro. Innanzitutto, si ricordi che i nomi non possono contenere spazi, pertanto se il nome fosse composto bisogna sostituire gli spazi con l’underscore. I nomi devono iniziare con un carattere e la loro lunghezza non può superare i 255 caratteri.

 

 

 

La macro che abbiamo registrata può essere rieseguita indefinitamente. Ecco come provarla.

Nota – La macro esegue sempre la medesima ricerca nel campo Autore, e così come è non serve a molto. In altre parole non è interattiva. Fortunatamente, però, possiamo modificarla opportunamente per renderla più versatile. Il nostro obiettivo è quello di metterla in grado di eseguire una qualsiasi ricerca in qualsiasi campo, ma prima di metterci all’opera vale la pena di dare un’occhiata al listato del programmino che il registratore di Excel ha creato automaticamente.  

2.2 Il listato della macro 

Ecco come procedere per visualizzare il codice della macro che abbiamo appena registrato. 

01.      Sub Ricerca()
02.    '
03.    ' Ricerca Macro
04.    ' Macro registrata il 25/01/2003 da Francesco Lanorte
05.    '
06.    '

07.    Range("D12").Select  

08.    Selection.AutoFilter
09.    Selection.AutoFilter Field:=2, Criteria1:="ARISTOTELE"

10.    Range("D12").Select  

11.    End Sub

I passi che ci interessano sono quelli che vanno dallo 07 allo 09. Analizziamoli in dettaglio.

Passo 07: Seleziona una cella della tabella-archivio, in questo caso la cella D12.

Passo 08: Attiva la funzionalità di Filtro automatico

Passo 09: Specifica il campo in cui ricercare e il criterio di ricerca (Campo 2 e Aristotele). Si noti che anziché il nome del campo viene dichiarato il suo numero d’ordine, e che il nome dell’autore, essendo una stringa, viene posto fra virgolette. 

Passo 10: Riporta il cursore in D12

A questo punto, si delinea la strategia per rendere interattiva la nostra macro e metterla in grado di ricevere input dall’esterno in modo da farle eseguire ogni volta il tipo di interrogazione desiderata. 

In pratica, la dove si fa riferimento al numero del campo e al criterio di ricerca bisogna inserire un segnaposto (una variabile, nel gergo degli addetti ai lavori) in cui verrà registrata la nostra risposta rispettivamente all’invito di dichiarare il numero d’ordine del campo e il criterio di interrogazione da utilizzare. Inoltre, bisogna aggiungere alcuni passi che si occupino di dimensionare le due variabili e di visualizzare i messaggi di richiesta. Ecco come si presenta il precedente listato dopo le suddette modifiche, che si digitano direttamente all’interno del codice stesso in ambiente Editor del Visual Basic.

01.      Sub Ricerca()
02.    '
03.    ' Ricerca Macro
04.    ' Macro registrata il 25/01/2003 da Francesco Lanorte
05.    '
06.    '

07.    Dim Campo 

08.    Dim Criterio
09.    Campo = InputBox("Inserire il numero del campo di ricerca")  

10.    Criterio = InputBox("Inserire il criterio di ricerca")  

11.    Range("D12").Select  

12.    Selection.AutoFilter  

13.    Selection.AutoFilter Field:=Campo, Criteria1:=Criterio  

14.    Range("D12").Select  

15.    End Sub  

Commentiamo  gli interventi operati:

Passi 07 e 08: Tramite l’istruzione Dim dichiarano rispettivamente le variabili Campo e Criterio.

Passi 09 e 10: L’istruzione InputBox determina la visualizzazione dei messaggi di richiesta, e  le nostre risposte vengono registrate nelle corrispondenti variabili. Da questo momento, ogni volta che si farà riferimento a Campo e Criterio è come se si specificassero in chiaro i valori che sono stati assegnati alle suddette variabili. 

Passo 13: Come si può vedere, i riferimenti al campo e al criterio sono stati sostituiti con il nome delle corrispondenti variabili rendendo così interattiva la macro. 

2.3 Esecuzione della macro 

Torniamo ora al foglio in cui è ospitato l’archivio, accediamo tramite menu alla maschera di gestione delle macro (Strumenti/Macro/Macro), selezioniamo il nome del programma: se premiamo il pulsante Esegui viene così visualizzato il primo box di richiesta. 

Inserire il numero d’ordine del campo e premere il pulsante Ok nel box. 

Viene così visualizzato il secondo box di richiesta: digitare il criterio e premere Ok per visualizzare il risultato della ricerca. 

Si noti che l’inserimento del criterio non prevede il rispetto dell’ortografia maiuscola e minuscola. In altre parole, Aristotele, ARISTOTELE, o AriStoTelE sono esattamente la stessa cosa.

Il numero d’ordine del campo indica la posizione che questo occupa nell’ambito della riga di intestazione dell’archivio (1 per il primo posto, due per il secondo, e così via). Vale la pena di osservare che se si riesegue la macro e si lancia una nuova interrogazione senza ripristinare l’archivio, è come se si eseguisse una ricerca in And.

Concludiamo ricordando che quando si digita il criterio di ricerca nel box che lo richiede, è possibile esprimerlo utilizzando le cosiddette wildard. Per chi non lo ricordasse le wildcard sono dei veri e propri caratteri jolly (l’asterisco e il punto interrogativo) che consentono di formulare il criterio stesso anche se non ne ricordiamo esattamente l’ortografia, o per renderlo più generalizzato. 

In particolare, l’asterisco sostituisce un qualsiasi gruppo di caratteri, mentre il punto interrogativo un solo carattere. Così, per ricercare Aristotele, possiamo anche scrivere A*, perché l’asterisco sta anche per tutti i caratteri mancanti, oppure Aristot??e, dal momento che i due punti interrogativi interpretano rispettivamente la e e la l. Ovviamente, se si utilizzano le wildcard, fra i risultati della ricerca potrebbero comparire anche record che non ci interessano.  

Nota – Quando abbiamo modificato la macro non ci siamo preoccupati di intervenire sulle coordinate della cella in cui viene posizionato il cursore all’inizio della ricerca. Ne consegue che la macro deve essere assolutamente lanciata dal foglio in cui è ospitato l’archivio da interrogare. Se, invece, le venisse assegnato un nome, si può operare da qualsiasi foglio della cartella. Denominare celle o zone, soprattutto quando si lavora con le macro è una buona abitudine che si dovrebbe prendere se si vuole rendere più spedito e a prova di errore l’utilizzo di un foglio di lavoro. In particolare, se si lavora con le macro, ci si mette al riparo dagli inevitabili errori che si generano nel caso in cui , dopo aver sviluppato un programma, si aggiungono o cancellano righe o colonne. Infatti, le zone su cui la macro deve intervenire potrebbero non essere più quelle corrette, con tutte le conseguenze del caso. Ma ecco molto sinteticamente come si svolge la procedura di denominazione:

 

2.4 Associare la macro ad un pulsante 

Chi per eseguire il programmino che abbiamo registrato non volesse passare attraverso la maschera delle macro, può più elegantemente associarne il lancio alla pressione di un pulsante. Anche in questo caso ci limitiamo alle informazioni essenziali. 

 

 

 

 

Per determinare l’esecuzione della macro alla pressione  del pulsante si procede nel seguente modo:

 

In caso di malfunzionamenti si consiglia di accertarsi che l’icona con sopra impressi squadra e matita non sia attivata, oppure provare a riposizionare il cursore su una qualsiasi cella del foglio di lavoro.

2.5 Ripristino dell’archivio 

Se dopo avere eseguito una interrogazione se ne vuole eseguire una seconda è necessario ripristinare l’archivio. Per evitare di passare tramite il menu conviene registrare la corrispondente e semplicissima macro, quindi associarla ad un pulsante che potremo etichettare Ripristina. Il listato della macro, che chiameremo anch’essa Ripristina è il seguente:

 

01.     Sub Ripristina()

02.    '

03.    ' Ripristina Macro

04.    ' Macro registrata il … da …

05.     '

06.    Range("D12").Select

07.     ActiveSheet.ShowAllData

08.    Range("D12").Select

09.    End Sub

 

Per associarla al pulsante seguire la procedura che abbiamo appena descritta.

Nota – La macro opera correttamente solo sulle tabelle risultanti da una interrogazione. Se la si lancia con l’archivio già ripristinato viene visualizzato un messaggio di errore. In questi casi premere semplicemente il pulsante Fine nel box che viene visualizzato. Così facendo, dall’editor del Vba in cui si era entrati a seguito dell’errore si può ritornare direttamente in ambiente Excel e condurre correttamente la ricerca. I più esperti che hanno seguito i nostri corsi sulle macro possono inserire un controllo che, qualora il pulsante venisse premuto a sproposito, esibisce un messaggio di errore o, più semplicemente, inibisce l’esecuzione della macro.




 

3.0 Analisi statistiche di un data base  

Chi non fosse soddisfatto dei risultati ottenuti interrogando l’archivio con i filtri automatici, può complementarli anche molto significativamente utilizzando le funzioni statistiche data base ospitate nella libreria di Excel, che si rivelano preziose per analizzare i contenuti di archivi anche molto importanti. 

Per esempio, riferendoci alla nostra base dati campione, possiamo effettuare il conteggio di quanti libri ci sono nella categoria saggi e determinarne, ad esempio, l’ammontare globale.

Le funzioni che utilizzeremo operano sostanzialmente come le equivalenti matematiche (Somma, Media, Max, Min, e via dicendo) ma differiscono da queste perché prevedono due nuovi argomenti oltre a quello che indica la zona su cui condurre le elaborazioni. Ci riferiamo alla specifica del campo dell’archivio su cui bisogna operare e all’indicazione dei criteri da utilizzare per individuare i dati da elaborare. 

Infine, dal punto di vista sintattico, le funzioni data base sono caratterizzate dal prefisso DB (per esempio, DB.SOMMA, DB.MEDIA, e così via…). Ma vediamo come si utilizzano, a cominciare dalla loro sintassi che è la seguente: 

NomeFunzione(zona archivio, nome campo; zona criteri)  

Zona archivio è rappresentata dalle coordinate di quella che ospita la base dati, ma dopo quanto si  è detto a proposito dei nomi vale la pena di assegnargliene uno. Per quanto riguarda il nostro modello esemplificativo l’abbiamo denominata Archivio. Fra l’altro, per dare modo di aggiungere nuovi record e fare in modo che vengano ugualmente presi in considerazione, vale la pena di comprendere nella denominazione una zona più ampia. L’argomento Nome Campo è il nome di quello in cui deve avere luogo l’esplorazione. Lo si indica con il nome in chiaro (fra virgolette), oppure con un numero che ne indica la posizione ricordando che al primo campo dell’archivio viene assegnato il numero 1, al secondo il 2 e così via. Se si opta per la specifica numerica le virgolette non devono essere messe. E veniamo al terzo argomento (Zona criteri), che per l’appunto corrisponde alle coordinate (o meglio, al nome) del gruppo di celle che ospita i criteri di interrogazione. Tale zona va strutturata in modo particolare. Vediamo come.

3.1 La zona criteri

In pratica, si tratta di una zona articolata in due o più righe e una o più colonne. Nelle celle della prima riga si inseriscono i nomi dei campi coinvolti nella interrogazione, mentre nelle sottostanti i corrispondenti criteri. Conviene fare subito un esempio pratico. 

Se vogliamo operare sui record che si riferiscono alla categoria BIOGRAFIE, la zona criteri si articola semplicemente in due celle l’una sopra l’altra (una riga e una colonna). Nella cella superiore si digita il nome del campo (Argomento), e nella sottostante il criterio di interrogazione (BIOGRAFIE). 

È appena il caso di ricordare che il nome del campo deve essere la copia esatta di quello dell’archivio, pertanto la cosa migliore da farsi e trasferirlo con un copia e incolla. 

Se, invece, si utilizza una colonna aggiuntiva, e nella sua prima cella si digita il nome del campo Autore, e in quella sottostante Aristotele, i record elaborati sono solo quelli che si riferiscono ai libri con argomento BIOGRAFIE ed autore Aristotele.  

Se nell’impostare una espressione che prevede l’utilizzo di una funzione data base si omette l’argomento criteri l’elaborazione coinvolgerà tutto il contenuto dell’archivio relativamente al campo specificato. 

Chi vuole saperne di più sulle procedure per la formulazione dei criteri può consultare in proposito la guida in linea di Excel. Ecco come procedere:

A questo punto dovremmo saperne abbastanza per cominciare a lavorare con le funzioni data base.  

Funzione DB.SOMMA

Questa funzione somma i contenuti di un campo numerico riferendosi ai record che soddisfano i criteri specificati nella omonima zona.

Funzione DB.MEDIA

Questa funzione media i contenuti di un campo numerico riferendosi ai record che soddisfano i criteri specificati nella omonima zona.

Funzioni DB.MIN e DB.MAX 

Operano in modo del tutto analogo alle altre ma restituiscono rispettivamente il valore minimo o massimo di un campo numerico dei record che vengono processati in base ai criteri fissati nell’omonima zona.

Funzione DB.CONTA.VALORI

Effettua il conteggio il numero di celle non vuote di un certo campo di testo dei record che vengono processati in base ai criteri specificati.