Appunti su Microsoft Excel

Il problema dei collegamenti ipertestuali che cambiano

Excel (almeno la versione Office 2003) presenta un problema molto fastidioso per quanto riguarda i collegamenti ipertestuali a risorse disponibili su una directory remota condivisa. In certe condizioni questi link possono diventare da assoluti a relativi facendo sì che spostando il file Excel in un altro pc, i collegamenti ipertestuali non funzionino più.

Il fatto è che Excel, quando può, usa sempre i percorsi relativi. Se i file a cui faccio riferimento nel link sono contenuti in una certa struttura di directory che va copiata per forza insieme al file Excel allora questa si rivela un'ottima scelta. A volte invece vogliamo che i link puntino sempre a dei file in una risorsa condivisa. Se Excel usasse dei percorsi relativi, non appena il file venisse copiato su di un altro PC, i link non funzionerebbero più.

Tale situazione solitamente non si presenta ma a volte capita. Nel mio caso è successo perché nel mio gruppo di lavoro si tengono tutti i dati in un NAS, che si interfaccia in rete tramite condivisioni alla Windows. Supponiamo che il suo percorso remoto sia \\PIPPO\dati\tabelle\file.xls dove PIPPO è il nome del NAS.

Il file Excel veniva modificato dagli utenti nel proprio PC, e poi le nuove versioni sostituivano quella presente sul NAS. Il file conteneva molti link a immagini presenti sul NAS all'indirizzo \\PIPPO\dati\foto\foto1.jpg, ecc...

Accadeva però che aprendo e salvando il file Excel direttamente dalla condivisione, senza prima copiarlo sul proprio PC, si perdevano irrimediabilmente tutti i link ipertestuali che da assoluti diventavano relativi, cioè per esempio \\PIPPO\dati\foto\foto1.jpg diventava ..\foto\foto1.jpg. Questo accade a causa dell'opzione "Aggiorna link" che si trova in: Tools, Options, General, Web Options, Files, "Update links on save". Quando Excel apriva il file direttamente sulla cartella remota, si "accorgeva" che poteva usare un percorso relativo anziché quello assoluto e cambiava tutti i link che quindi diventavano funzionanti solo se si apriva il file da remoto oppure si copiavano tutte le immagini in locale, con la stessa struttura delle directory.

Quindi innanzitutto se si ha bisogno di inserire dei link a dei file presenti su un computer di rete, conviene disabilitare l'opzione "Update links on save". Purtroppo però, se oramai il pasticciaccio è fatto è sufficiente impostare una directory di base per i link in modo che non venga più toccata da Excel. Per farlo bisogna andare su File|Properties|Summary Tab|Hyperlink Base e inserire la directory di base dei nostri link che nel mio caso era \\PIPPO\dati\tabelle.

Left Join di due tabelle Excel

Molto spesso le tabelle di Excel vengono usate impropriamente come tabelle di un database anziché come semplici fogli di calcolo. È innegabile però il vantaggio di poter effettuare operazioni matematiche e non solo sui dati della tabella. Cosa impossibile o molto difficile con i database. Questo è quanto accade ogni giorno nel nostro laboratorio dove i campioni vengono registrati, assieme alle loro proprietà, in una grande tabella Excel.

Così facendo però ci si trova presto ad avere necessità "da database" che però sono difficili da soddisfare con un programma basato su fogli di calcolo. Una di queste tipiche necessità è il poter fare delle operazioni di join tra diverse tabelle Excel.

Un esempio tipico: tabella A con i clienti di una azienda telefonica. In una colonna c'è un codice che indica la centrale telefonica che serve quel cliente. La tabella B contiene invece come chiave primaria il codice suddetto e per ognuno di essi tutte le caratteristiche di quella centrale, tra cui, per esempio, l'ubicazione.

Supponiamo di voler avere una vista del database, cioè una tabella creata a partire dai dati già contenuti in esso, che sia identica alla tabella A ma che contenga un'ulteriore colonna che indichi l'ubicazione della centrale telefonica.

Per ogni riga della tabella A dovrei guardare il codice della centrale e andare sulla tabella B a controllare qual è l'ubicazione per quel codice. Questa operazione è chiamata join, in particolare left outer join tra la tabella A e la tabella B. Idealmente consiste nel fare il prodotto cartesiano tra le righe della tabella A con le righe della tabella B e tenere poi solamente le righe che soddisfino una certa condizione che nel nostro caso sarà tabellaA:id_centrale = tabellaB:id_centrale. Dopodiché da questo grande tabellone non resta che prendere le colonne di interesse.

Esistono diversi modi di fare una join tra due o più tabelle. In pratica si distinguono dal lasciare o meno le righe del prodotto cartesiano in cui la condizione di confronto è fatta tra uno o più valori non definiti o null.

Come simulare una join in excel? È sufficiente usare la funzione VLOOKUP che prende quattro argomenti (l'ultimo opzionale): VLOOKUP(1,2,3[,4]).

  1. cosa cercare (stringa o identificativo di cella)
  2. dove cercarlo definito come tabella (cella in alto a sinistra:cella in basso a destra)
  3. questo valore è il numero della colonna della tabella precedentemente definita da cui VLOOKUP resistuirà le informazioni corrispondenti alla riga che matcha la ricerca dell'oggetto al punto 1 contenuto nella prima colonna, che deve essere ordinata
  4. TRUE di default. Se è FALSE restituisce la riga di match solo se ha dato un match esatto. In questo caso la prima colonna della tabella può anche non essere ordinata.

Ricordate che se avete Office in italiano i nomi delle funzioni sono tradotti. Questo per me è uno dei più gravi errori commessi dalla Microsoft. Ecco un link con le corrispondenze fra le varie lingue: dolf.trieschnigg.nl/excel/excel.html. Ed ecco alcune corrispondenze: 

VLOOKUP = CERCA.VERT
FALSE = FALSO
MATCH = CONFRONTA
OFFSET = SCARTO
COUNTA = CONTA.VALORI
, = ;

Copiate in un nuovo foglio (tramite formula) le colonne di interesse della tabella LEFT, tra cui, obbligatoriamente, la chiave primaria. Nella parte restante metteremo le colonne di nostro interesse dalla tabella RIGHT. Per farlo basta usare la funzione VLOOKUP appena introdotta. Per ogni colonna che volete compaia nella vista, usate la funzione

VLOOKUP(chiave primaria LEFT,tabella RIGHT,numero colonna di interesse di RIGHT,FALSE)

La chiave primaria LEFT è solitamente il primo valore della riga attuale e verrà cercato nella prima colonna della tabella RIGHT. L'ultimo FALSE richiede un match esatto. Se il match è andato a buon fine, VLOOKUP prenderà la riga che ha dato il match e restituirà il valore della colonna indicata con numero colonna di interesse di RIGHT.

La colonna di interesse può anche essere estratta in maniera automatica mettendo al posto del numero colonna di interesse di RIGHT: 

MATCH(titolo colonna attuale,dove cercare,0)

dove cercare è la prima riga della tabella RIGHT. 0 significa che cerca una corrispondenza esatta. MATCH resistuirà l'indice di colonna della RIGHT.

Vi ricordo di bloccare in maniera opportuna nelle formule i numeri di riga o di colonna.

Istogrammi

Rispetto a quanto si possa pensare, creare istogrammi con Excel non è poi così semplice.

Il grafico istogramma non fa nient'altro che prendere due colonne e usare la prima per i bin e la seconda come numero di occerrenze per ciascun bin. I bin in Excel vengono chiamati classi. Questo grafico, così come è pensato è fatto per graficare roba del tipo: in x ho pere, mele e banane mentre in y ho quante pere, quante mele e quante banane ci sono. La prima colonna selezionata corrisponde ai nomi (pere, mele e banane) e la seconda alla loro quantità.

Si capisce quindi che se voglio fare un vero istogramma, cioè graficare quante occorrenze ci sono per ogni bin, mi devo preparare la colonna dei bin e la colonna delle occorrenze, da solo. Lo strumento grafico istogramma non fa questa operazione per noi come potrebbe essere per programmi più scientifici come Origin.

Creata la colonna dei bin nella maniera e con gli intervalli che più vi piacciono, per trovare le occorrenze potete procedere in maniera manuale oppure in maniera automatica. La maniera automatica necessita della funzione FREQUENCY (FREQUENZA in italiano). In input prende un intervallo di colonna corrispondente ai bin (le classi come le chiama Excel) e un altro intervallo di colonna corrispondente ai dati da conteggiare. Questa funzione restituisce un array di valori, cioè un altro intervallo di colonna.

Supponete di avere una colonna con i bin. Nella colonna a fianco volete le occorrenze. Nella casella corrispondente al primo bin, inserite la funzione FREQUENCY e dategli in pasto gli opportuni dati di input. A questo punto se date invio avrete un unico risultato e non un array di valori! In poche parole verrà inserito un valore solo nella casella selezionata. Excel purtroppo è un po' stupido. Quando si usano le funzioni che restituiscono degli array, è necessario dire a queste funzioni su quali caselle potranno riportare il risultato. Se l'array è composto da N valori e noi diciamo di usare solo M caselle, verranno mostrati solo i primi M valori dell'array.

E come si fa a dire alla funzione FREQUENCY, o in generale a una funzione che restituisce un array, di riportare questo array in un certo intervallo di celle? Dopo aver inserito la formula si seleziona l'intervallo desiderato e si preme CTRL + SHIFT + INVIO. Questa è la maniera standard in Excel di comunicare che l'intervallo selezionato deve essere un array.

Una volta preparate le due colonne, cioè quella con i bin (classi) e quella con le occorrenze, non resta che selezionare queste due colonne e usare il grafico istogramma. Bisogna però stare attenti che Excel, vedendo che nella prima colonna ci sono dei numeri, pensa che anch'essi siano delle occorrenze e vi grafica quindi due istogrammi, uno sopra l'altro. Per far sì che la prima colonna sia usata come etichettatura per i bin, è sufficiente specificarlo nella scheda opportuna del grafico (Dati di origine).

Lo stile di default è molto brutto quindi consiglio vivamente di selezionare le varie parti del grafico per cambiare le varie caratteristiche grafiche. Per esempio in un vero istogramma non c'è spazio tra le varie colonne.

Infine è da ricordare che esistono dei componenti aggiuntivi di Excel che permettono di automatizzare le operazioni descritte sopra. Il più famoso di questi componenti è l'ANALYSIS TOOL PACK della Microsoft che solitamente si installa quando si installa Excel. In Excel 2003 per aggiungere questo componente andate su Tools->Add in... e selezionate Analysis toolpack (Strumenti di analisi in italiano). Come si usa potete poi scoprirlo da soli. Vi basti sapere che la funziona istogramma di questo pacchetto non fa nient'altro che fare quello che noi abbiamo fatto a mano: definire i bin, contare i dati nei bin con la funzione FREQUENCY e graficare il tutto con il grafico istogramma assegnando correttamente le etichette.