Microsoft Excel-nozioni Avanzate-I Parte

Microsoft Excel Nozioni avanzate

Prima parte

Roberto Mariconda

Roberto Mariconda Microsoft Excel – Nozioni avanzate – I^ parte

Pag. 2

Sommario Usare Excel come database ...........................................................3

Una definizione di database.......................................................................3 Creazione di un database tramite il modulo ...................................................4 Interrogazione tramite il modulo del database................................................5

I caratteri “jolly” ....................................................................................6 Interrogare un database............................................................................7

L’utilizzo dei filtri .......................................................................................7 I filtri automatici ....................................................................................7

Gli operatori logici AND ed OR .....................................................................8 I filtri avanzati ..................................................................................... 11

Controllo dell’input.................................................................................. 18 Completamento automatico ...................................................................... 18 Immissione assistita ................................................................................ 19 Convalida............................................................................................... 20 Estensione delle regole di convalida: Il comando “Incolla Speciale” .......... 21

Stili di visualizzazione ............................................................................. 23 Formattazione automatica ........................................................................ 23 Formattazione condizionale....................................................................... 24 Formati numerici personalizzati ................................................................. 31

Struttura di un formato numerico personalizzato ....................................... 31 Simboli e loro significato........................................................................ 31 I formati personalizzati applicati alle celle contenenti date ed ore ................. 36 Un formato per nascondere il contenuto delle celle .................................... 37 Un esempio di utilizzo dei formati personalizzati ........................................ 37

Gli operatori condizionali di formato ........................................................... 41 Tecniche di visualizzazione...................................................................... 43

Zoom .................................................................................................... 43 Blocco riquadri........................................................................................ 43 Caselle di divisione .................................................................................. 44

Gestione della sicurezza dei dati .............................................................. 46 Protezione.............................................................................................. 46 Nascondere righe, colonne, fogli e celle....................................................... 48

Ordinamento dei dati............................................................................... 51 Analisi dei dati - Subtotali........................................................................ 54 Analisi dei dati - Strutture ....................................................................... 59

Database – Un approfondimento ................................................... 62 Creare e gestire i grafici con Microsoft Excel..................................... 67

Introduzione.............................................................................................. 67 Elementi di un grafico ................................................................................. 67 Creazione di un grafico................................................................................ 67 Aggiunta e rimozione di una serie di dati........................................................ 75


Pag. 3

Usare Excel come database Una definizione di database Un database è una raccolta di dati organizzata in modo da poter essere facilmente accessibile per la consultazione, la modifica e gli aggiornamenti. Microsoft Excel consente una agevole gestione di elenchi di dati di consistenza limitata (alcune migliaia di record). Ci occuperemo delle funzionalità di gestione del database offerte da Microsoft Excel e parleremo in particolare di:

Creazione di un database Aggiunta, modifica e cancellazione di record Controllo della correttezza dei dati immessi Tecniche di visualizzazione Tecniche di ordinamento Ricerca di informazioni Tecniche di analisi dei dati mediante l’utilizzo

dei subtotali e delle strutture Prendiamo innanzitutto confidenza con i termini utilizzati nei database:

Ogni colonna (campo) contiene dati omogenei; Ogni riga (record) contiene l'insieme dei dati relativi ad un determinato soggetto raccolti nelle varie colonne.

Campo: ogni colonna del database

Record: ogni riga del database

Tabella: l’insieme del database

Nome del campo: titolo della colonna.


Pag. 4

Creazione di un database tramite il modulo

Un database è costituito da una prima riga contenente i nomi dei campi (intestazioni delle colonne...) e, immediatamente dopo, senza righe vuote, i dati. Vediamo ora come creare il database di esempio raffigurato qui sotto: [Clienti.xls]

La via più intuitiva è quella di scrivere le intestazioni e successivamente, riga per riga, i dati richiesti. Ma esiste un altro modo di procedere: Innanzitutto, scriviamo su una riga i nomi dei campi; quindi posizioniamoci sulla riga immediatamente successiva e dal menu “Dati” scegliamo la voce “Modulo”:


Pag. 5

Come si può notare, poiché Excel non trova alcun dato, ci chiede di confermare la correttezza dell’operazione. Clicchiamo su OK ed Excel ci presenta il modulo:

Il modulo presenta i nomi dei campi del database ed uno spazio idoneo ad immettere i valori. In questa fase, come si nota dal messaggio posto in alto a destra del modulo, ci troviamo in modalità immissione dati; ma si può facilmente arguire come attraverso il medesimo modulo possano essere compiute ulteriori operazioni di eliminazione e di ricerca. Una volta compilati i dati del modulo, un click sul pulsante “Nuovo” scrive sulla prima riga vuota del database i dati impostati e predispone il programma ad una nuova immissione. Il pulsante “Chiudi”, come si può intuire, scarica dalla memoria il modulo. Interrogazione tramite il modulo del database

Proviamo ora ad operare una ricerca. Innanzitutto attiviamo una cella della riga 3 del database ed apriamo il modulo. Successivamente, premiamo il pulsante “Criteri”. Il modulo viene in tal modo svuotato per consentire l’immissione di criteri di ricerca. Proviamo a individuare tutti i soggetti con Città = Udine:


Pag. 6

Individueremo due record, che potremo visualizzare con i pulsanti “Trova succ.” e “Trova prec.”. Una volta individuato il record interessato possiamo eliminarlo (pulsante “Cancella”) o modificarlo. Il pulsante “Ripristina” ci consente di annullare tutte le modifiche operate e di ritornare ai contenuti originari, mentre la pressione del pulsante “Nuovo” consente di memorizzare le eventuali variazioni. Ovviamente possiamo procedere alla ricerca secondo più di un criterio: per esempio possiamo cercare i record che soddisfano contemporaneamente il criterio Titolo = Geometra e Città = Udine. I caratteri “jolly”

Una ulteriore possibilità di affinare la ricerca è costituita dall’utilizzo dei c.d. “caratteri jolly”, “*” e “?”, universalmente utilizzati anche per effettuare ricerche di file e contenuti all’interno dell’interfaccia di MS Windows. Vediamone l’utilizzo. Il carattere “*” – asterisco – serve per indicare “zero o più caratteri”. Per esempio, nel nostro caso, scrivendo nella casella “Intestazione” il criterio Gal* otterremmo l’estrazione di tutti i record, mentre il criterio Gallo* estrarrebbe due soli record (il sig. Gallo e il sig. Galloni). Il carattere “?” invece indica un qualsiasi carattere che si trova in una determinata posizione all’interno di una stringa.


Pag. 7

Vediamone un esempio:

In questo caso Excel individuerà due record, quelli relativi al cav. Galli e all’ing. Gatti. Interrogare un database L’utilizzo dei filtri

Abbiamo visto come tramite l’utilizzo del modulo del database sia possibile individuare i record che soddisfano determinati criteri. Tale funzionalità però talvolta può non essere sufficiente:

perché ci consente di visualizzare solo un record alla volta; perché non ci consente di trattare contemporaneamente, per

esempio con un’operazione di Copia-Incolla i record che soddisfano determinati criteri.

Esamineremo qui di seguito ulteriori strumenti di estrazione, detti filtri. I filtri automatici

Una prima opzione è costituita dai così detti Filtri automatici. Prendiamo in esame la cartella di lavoro “Sedi e strutture.xls”. Desideriamo, per esempio, individuare tutte le strutture presenti nella sede di via Palladio. Non è assolutamente necessario che il database sia ordinato secondo un particolare criterio. Come al solito, però, rendiamo attiva prima di procedere una cella del database e dal menu Dati scegliamo il comando “Filtro” e poi “Filtro Automatico”. A seguito di tale comando le intestazioni delle colonne si sono trasformate in caselle a


Pag. 8

discesa. Apriamo quindi la casella a discesa accanto all’intestazione di colonna “Sede” e clicchiamo su “Via Palladio”. Immediatamente verranno visualizzati solo i record che soddisfano la condizione.

Si noti la diversa colorazione (in blu) delle righe estratte e della freccia accanto alla colonna utilizzata per impostare i criteri. Come si è in precedenza accennato, è possibile impostare in fase di ricerca più di un criterio. Ad esempio, si è detto che utilizzando il modulo di database è possibile impostare due o più criteri in base al quale individuare i record voluti (nel nostro caso, Titolo = Geometra e Città = Udine). Nel caso in esame, la ricerca ha successo se contemporaneamente vengono soddisfatte ambedue le condizioni. Ma potrebbe essere necessario impostare anche un diverso criterio per fare in modo, per esempio, che la ricerca venga soddisfatta anche se solo uno dei due criteri fosse verificato. Gli operatori logici AND ed OR

Le funzioni di ricerca possono utilizzare gli operatori logici AND ed OR (che, come noto, in Excel vengono utilizzati nelle formule mediante le funzioni =E(….) ed =O(….). In estrema sintesi, l’operatore logico AND restituisce il valore VERO se ambedue le relazioni sono vere, mentre l’operatore logico OR restituisce il valore VERO se almeno una delle due relazioni è vera. Proviamo ora, servendoci del database “Vendite.xls” ad impostare un criterio più complesso, previo annullamento del criterio di filtro impostato in precedenza: Cliccare sull’intestazione “Prodotto” e scegliere “[Tutto]”:


Pag. 9

Vogliamo estrarre le vendite effettuate nei mesi di Maggio e Luglio che hanno superato l’importo di 9.000 €. Il primo criterio di selezione andrà applicato alla colonna Mese. Dovremo estrarre tutti i record per i quali Mese=Maggio oppure Mese= Luglio. Dovremo quindi applicare l’operatore logico “OR”. Il secondo criterio verrà applicato al campo “Vendite”. Occupiamoci innanzitutto del campo “Mese”. Apriamo la casella a discesa e scegliamo la voce “(Personalizza…)”:

Nella finestra successiva impostiamo la ricerca:


Pag. 10

Occupiamoci ora del criterio da applicare al campo “Vendite”. Apriamo la casella a discesa associata alla colonna ed impostiamo il filtro, scegliendo dalla casella a discesa “è maggiore di…” e scrivendo il valore 9000 (in altre circostanze potremmo anche scegliere uno dei valori proposti dalla casella a discesa):


Pag. 11

Il risultato ottenuto è il seguente:

Si noti che il risultato dell’interrogazione può essere ordinato utilizzando i comandi presenti nelle caselle a discesa associate ai campi. Si esamineranno in seguito ulteriori modalità per ordinare i contenuto di un foglio di lavoro (vedasi capitolo “Ordinamento dei dati”): §

In seguito, se necessario, le righe estratte ed elaborate potranno essere copiate (Copia + Incolla) in un nuovo foglio della cartella corrente o in un nuovo file. I filtri avanzati

Occupiamoci ora dei filtri avanzati. Mediante l’uso dei filtri avanzati potremo:

Combinare in maniera complessa vari criteri di ricerca applicando gli operatori logici AND e OR;

Specificare tre o più criteri per una determinata colonna; Copiare in un’area definita a priori i dati estratti senza

utilizzare i comandi copia/incolla; Applicare ai dati estratti funzioni specifiche di analisi.

Per utilizzare i filtri avanzati è necessario definire alcune aree:


Pag. 12

L’area origine: corrisponde al database e viene riconosciuta automaticamente da Excel se prima di attivare il filtro si ha cura di rendere corrente una qualsiasi cella del database;

L’area criteri: l’area criteri è composta da una prima riga contenente i nomi dei campi in base ai quali vogliamo estrarre le informazioni e dal numero delle righe immediatamente successive che effettivamente conterranno i criteri veri e propri:

Si noti che l’area criteri è stata posta al di sopra dell’area origine. Sebbene non obbligatoria, questa disposizione presenta il vantaggio di poter controllare contemporaneamente l’impostazione dei criteri ed i dati estratti, anche servendosi della funzionalità “blocca riquadri” accessibile, come si vedrà, dal menu “Finestra”. Nel caso in esame, posizionarsi sulla cella B11 prima di impartire il comando. Tutte le righe fino alla 10 rimarranno visibili anche scorrendo verso il basso. La zona criteri prevede al minimo la presenza di due righe; la prima contenente i nomi dei campi in base ai quali si intende operare il filtro, la seconda contenente i criteri. E’ importante notare che la zona criteri non prevede la presenza di righe vuote, pena il mancato funzionamento del filtro. Si noti ancora che nell’area criteri i nomi dei campi del database non sono tutti presenti, e che gli stessi non appaiono nel medesimo ordine della zona origine. Ciò è perfettamente lecito. E’ fortemente

Area criteri

Area origine


Pag. 13

consigliabile inoltre, piuttosto che riscrivere i nomi dei campi, procedere alla loro copia per evitare errori di battitura.

L’area di emissione: Excel consente, oltre alla visualizzazione dei dati estratti nell’area origine, analogamente a quanto avviene nell’uso dei filtri automatici, la copia dei medesimi un’apposita area, detta area di emissione. Per la sua definizione è necessaria e sufficiente l’indicazione dell’indirizzo di una cella che costituirà il vertice superiore sinistro della tabella dei dati estratti. È possibile tuttavia definire l’area di emissione evidenziando le celle contenenti le intestazioni delle colonne opportunamente copiate in una nuova area. Ciò e particolarmente utile quando si desidera limitare l’output a determinate colonne e/o con una disposizione delle stesse diversa da quella presente nel database. Una volta eseguito il filtro i criteri di estrazione potranno essere cancellati o modificati per successive operazioni che si rendessero eventualmente necessarie.

Vediamo alcuni esempi, servendoci della cartella di lavoro “Vendite.xls”: Per accedere ai filtri avanzati scegliere l’apposita voce selezionando il menu Dati/Filtro:

Proviamo ad impostare un semplice criterio di selezione. Contrariamente a quanto avviene con i filtri automatici, con i filtri avanzati dovremo esprimere le relazioni servendoci degli operatori relazionali: In questo esempio, si intendono ricercare le vendite che hanno superato la soglia di 9000 €.

Maggiore: > Minore: < Maggiore/Uguale: >= Minore/Uguale: <= Diverso: <>


Pag. 14

Come si può notare è stato utilizzato l’operatore relazionale “>”, e la zona criteri è composta da due righe: quella contenente i nomi dei campi e quella contenente il criterio vero e proprio. Si raccomanda una particolare attenzione a questo aspetto; gran parte dei motivi di insuccesso nelle operazioni di ricerca risiede in una non corretta definizione della zona criteri. Inoltre, poiché si è scelta l’opzione “filtra l’elenco sul posto”, i dati estratti sostituiranno provvisoriamente le righe costituenti l’intero database:

Proviamo ora ad estrarre i record che soddisfano le seguenti condizioni: Vendite > 9000 e Mese = Maggio.


Pag. 15

Si noti che perché la ricerca abbia successo devono essere contemporaneamente vere ambedue le relazioni. In altre parole, uniamo le due relazioni con l’operatore logico AND. Questa volta, dopo aver cancellato i precedenti criteri di ricerca, impostiamo i nuovi e chiediamo ad Excel di visualizzare i dati filtrati in un’altra posizione:

Il risultato è il seguente:


Pag. 16

Procediamo ora ad una nuova interrogazione. Ci proponiamo, questa volta, di estrarre tutte le vendite secondo i seguenti criteri: Mese = Maggio Oppure Mese = Luglio La nostra ricerca produrrà righe se almeno una delle due condizioni verrà soddisfatta. In altre parole, uniamo le due relazioni con l’operatore logico OR.

Si noti in questo caso la diversa definizione dell’area criteri. Proviamo ora, come ultimo esempio, a filtrare i dati secondo i seguenti criteri: Vendite > 9000 E Mese = Maggio Oppure Mese = Luglio Ciò significa che la ricerca avrà successo se esistono dei record che soddisfano almeno una coppia delle seguenti relazioni: a) Vendite > 9000 AND Mese = Maggio b) Vendite > 9000 AND Mese = Luglio


Pag. 17

Significa, ancora, che le relazioni a) e b) sono unite dall’operatore logico “OR”

Da quanto sinora osservato possiamo concludere che, nell’uso dei filtri avanzati:

I criteri da impostare con l’uso di AND vanno posti nell’area criteri sulla medesima riga;

I criteri da impostare con l’utilizzo di OR vanno posti nell’area criteri su righe diverse.

Due ulteriori annotazioni riguardanti i criteri:

nella definizione dell’area criteri si sono sempre incluse tutte le colonne, anche se queste non presentavano alcun criterio di selezione. Ciò è ininfluente ai fini della ricerca. Ciò che è importante, al contrario, è che la definizione dell’area non includa righe vuote;

OR

AND


Pag. 18

Anche nell’ambito dei filtri avanzati sono utilizzabili i caratteri “jolly” “?” e “*” già esaminati parlando dei filtri automatici e della ricerca tramite il modulo del database.

Controllo dell’input

Entrare in possesso delle tecniche idonee a controllare la correttezza dei dati immessi significa attribuire coerenza al database. Si è affermato che ogni campo (colonna) deve contenere dati omogenei; quindi, per esempio, solo numeri, piuttosto che dati riferiti ad un preciso range, quali ad esempio un insieme finito di date, di nomi, ecc.. Ancora, i dati che si riferiscono ad un preciso insieme devono essere immessi correttamente, per evitare che alcuni record vengano inaspettatamente esclusi dalle funzioni di filtro/ricerca. Ai nostri occhi scrivere “Direzione amministrativa” e “Direzione amm.va” è la stessa cosa; per un computer sono cose alquanto differenti! Completamento automatico

Una prima tecnica consente di completare automaticamente le immissioni di testo che si sono iniziate a digitare in una cella. Per usufruire di questa utile funzione controllare che sia spuntata la casella “Abilita completamento automatico dei valori nella cella” nella finestra di dialogo raggiungibile tramite il comando “Opzioni” / “Modifica” del menu “Strumenti”:

Una volta attivata l’opzione, Excel controllerà il testo durante la digitazione, lo confronterà con i contenuti delle celle immediatamente


Pag. 19

precedenti nella medesima colonna e proporrà automaticamente il testo trovato. Provare, ad esempio, ad immettere su una prima cella il testo “Stampante Laserjet 2600n”, e nella cella immediatamente più in basso iniziare la digitazione della medesima descrizione. Immediatamente Excel proporrà il completamento del contenuto della cella, contenuto che ovviamente potrà essere opportunamente modificato o accettato con la pressione del tasto <Invio>.

Immissione assistita

Una ulteriore tecnica consente di fare apparire all’altezza della cella attiva un elenco dei contenuti derivato da quello delle celle precedenti. Per accedere a tale funzionalità è sufficiente premere la combinazione di tasti <Alt>+<Freccia in basso> e cliccare sulla voce prescelta.


Pag. 20

Convalida

Un controllo più stretto dei dati immessi può essere ottenuto mediante le funzioni messeci a disposizione dal comando “Convalida” del menu “Dati”.

Una volta scelto il tipo di convalida dall’elenco “Consenti” si potrà ulteriormente personalizzare il tutto aggiungendo un messaggio di input, per esempio “Immetti il numero di scarpa” (che apparirà come “tooltip” una volta resa attiva la cella interessata), ed un messaggio di errore nel caso che il dato immesso non corrisponda alle regole di convalida (“in questa cella è consentita unicamente l’immissione di numeri interi”).


Pag. 21

Estensione delle regole di convalida: Il comando “Incolla Speciale”

Una volta memorizzata una regola di convalida, essa potrà essere estesa a tutte le celle interessate mediante una semplice operazione di Copia-Incolla; fermo restando che, volendo riprodurre unicamente le regole di convalida, evitando per esempio di copiare le formattazioni, le regole potranno essere estese sostituendo al comando “Incolla” il comando “Incolla speciale…”.

Una regola di convalida particolarmente utile è quella fornita dall’opzione “Elenco”, che trasforma la cella in possesso della regola in una casella a discesa:

Vediamo come funziona. Innanzitutto, bisogna creare, sul medesimo foglio della cartella di lavoro attiva, l’elenco che verrà visualizzato nella casella a discesa. E’ ovvio che tale elenco potrà essere personalizzato ed ordinato nella maniera più idonea.


Pag. 22

Evidenziamo ora le celle che conterranno la regola di convalida, apriamo il box di dialogo “Convalida” e scegliamo “Elenco”. Premiamo il pulsante di selezione ed evidenziamo l’area contenente l’elenco. E’ possibile includere nella selezione ulteriori righe vuote per eventuali future integrazioni. Premere OK per concludere l’operazione.

Nelle celle così personalizzate verranno accettate solo immissioni corrispondenti all’elenco contenuto nella zona origine, anche se l’immissione avvenisse tramite digitazione diretta piuttosto che tramite scelta degli elementi della casella a discesa. Un’ultima annotazione riguardo alle regole di convalida: se dopo avere impostato nelle celle alcune regole di convalida utilizziamo il modulo del database, le regole di convalida verranno applicate nel momento in cui i dati verranno effettivamente trasferiti sul foglio di lavoro. In caso contrario, le regole di convalida analizzeranno le celle al momento di inserirne o variarne il singolo contenuto.


Pag. 23

Stili di visualizzazione Formattazione automatica Un consistente aiuto alla fruizione delle informazioni può essere conferito anche da un’adeguata formattazione dei contenuti. La formattazione può consistere nell’applicazione di contenuti meramente estetici ma può anche consentire di evidenziare dinamicamente particolari contenuti. Per esempio, potremmo avere la necessità di mettere in risalto determinati valori che in ragione dei calcoli e dei valori immessi assumono un valore superiore o inferiore ad una certa soglia. Oltre alla possibilità di formattare manualmente un insieme di celle, Excel offre anche uno strumento immediato, denominato “formattazione automatica”, per mezzo del quale un determinato stile complesso di visualizzazione può essere applicato istantaneamente all’area selezionata. Proviamo ad applicare uno stile di formattazione automatica al database contenuto nella cartella di lavoro “Vendite.xls”. A tal fine è sufficiente rendere attiva una qualsiasi cella dell’intervallo da formattare e scegliere il comando “Formattazione automatica…” dal menu “Formato”:

Una volta aperto il box di dialogo sarà sufficiente scegliere lo stile più adatto. Ulteriori scelte circa la formattazione vengono messe a disposizione cliccando sul pulsante “Opzioni” (“Formati da applicare”, nella parte bassa della finestra). Si noti che la formattazione automatica non viene integralmente applicata poiché nell’esempio proposto mancano delle righe di totale, che Excel riconosce al fine di applicarvi la formattazione automatica.


Pag. 24

Nell’esempio successivo (Cartella di lavoro “Fondi.xls”), invece, Excel provvederà a formattare anche gli elementi mancanti in precedenza:

Formattazione condizionale

La formattazione condizionale mette a disposizione un potente strumento che analizza dinamicamente i contenuti delle celle cui è applicata e ne varia la formattazione al verificarsi delle condizioni specificate. Vediamone un esempio, servendoci della cartella di lavoro “Temperature.xls”:

Si desiderano evidenziare con una adeguata formattazione (carattere bianco su sfondo rosso) le temperature di Gennaio 2008 che sono inferiori alla temperatura media del Gennaio 2007. A tal fine ci serviremo della formattazione condizionale, il cui box di dialogo è raggiungibile attraverso l’apposita voce del menu “Formato”.


Pag. 25

Iniziamo selezionando preventivamente le celle che dovranno essere formattate (colonna D), poi apriamo il dialogo “Formattazione condizionale” ed immettiamo la condizione da verificare:

Il secondo passo consiste nell’impostare un formato di visualizzazione che verrà attivato al verificarsi della condizione. A tal fine basta agire sul pulsante “Formato…” per trovare il familiare box di dialogo di formattazione delle celle, dove potremo impostare quale motivo uno sfondo rosso e come colore carattere il bianco grassetto:


Pag. 26

La pressione del pulsante OK ci riporta al box “Formattazione condizionale, ove potremo visualizzare l’anteprima del formato delle celle al verificarsi delle condizioni poste:

La pressione del pulsante OK consente di evidenziare le celle che soddisfano la condizione:

Si noti che la formattazione è dinamica. Se infatti si prova ad immettere una temperatura molto alta, per esempio 10, in una cella riferentesi al gennaio 2008 si constaterà che la formattazione varia di conseguenza.


Pag. 27

Come può evincersi dalla presenza del pulsante “Aggiungi>>>”, è inoltre possibile impostare ulteriori condizioni che dovranno essere contemporaneamente verificate affinché la formattazione condizionale abbia luogo:

Si ricorda infine che, analogamente a quanto già esaminato nell’ambito delle regole di convalida, la formattazione condizionale può essere estesa ad altre celle con una semplice operazione di “Copia/Incolla” o di “Copia/Incolla Speciale” nel caso in cui sia richiesta un’operazione più selettiva. Esaminiamo ora una seconda opzione offertaci dalla formattazione condizionale. La casella a discesa “Condizione”, infatti, ci consente di scegliere, oltre che “Il valore della cella è”, come fatto sinora, anche “La formula è”:

La formula da immettere deve restituire un valore logico VERO (ed allora la formattazione verrà attivata) o FALSO. Si utilizzeranno, in altre parole, gli operatori di confronto. Vediamone un possibile utilizzo. Desideriamo creare un foglio di lavoro “a lettura facilitata”, nel quale, cioè, il colore delle righe si alterni:


Pag. 28

Allo scopo utilizzeremo le funzioni =Rif.Riga() e =Pari() La funzione Rif.Riga() ha come argomento un indirizzo di cella, e restituisce il numero della riga della cella; La funzione Pari() ha come argomento un numero (o una funzione che restituisce un valore numerico) e restituisce il numero pari immediatamente superiore al numero dell’argomento:

=Rif.Riga(B57) - 57 =Pari(57) ------ 58 =Pari(Rif.Riga(B57)) 58 Vediamo ora applicate le due funzioni su un foglio di lavoro:

Come si noterà, il confronto in colonna “C” restituisce alternativamente il valore logico VERO e FALSO; ciò è esattamente quello che vogliamo ottenere.


Pag. 29

Proviamo ora ad applicare il tutto alla formattazione condizionale. Evidenziamo le celle interessate ed apriamo il box di dialogo della formattazione condizionale. Nell’immettere la formula inseriremo quale indirizzo la cella attiva, che possiamo facilmente identificare nella casella Indirizzo:

Sceglieremo successivamente un opportuno formato:

Ad operazione conclusa otterremo il risultato desiderato:


Pag. 30

Un ulteriore miglioramento estetico può essere conferito dall’eliminazione della visualizzazione della griglia (“Strumenti-Opzioni-Visualizza”)… e dalla formattazione delle celle con un bordo adatto:

Per poter utilizzare altrove i formati condizionali creati in una cartella di lavoro si possono seguire due strade:

la prima consiste nel copiare ed incollare nella cartella di lavoro di destinazione le celle personalizzate della cartella di origine;

la seconda strada è quella di salvare la cartella contenente le formattazioni personalizzate come modello. Ciò, come noto, può essere fatto scegliendo l’opportuno formato in sede di salvataggio della cartella oppure, successivamente, variando l’estensione della cartella da “.XLS” a “.XLT”. In tal modo, facendo “doppio clic” sull’icona del modello si creerà una nuova cartella di lavoro contenente le personalizzazioni.


Pag. 31

Formati numerici personalizzati

Excel consente all’utente di estendere la già ricchissima collezione di formati predefiniti. Si esamineranno di seguito i simboli necessari a definire i formati di visualizzazione e le possibilità di formattazione offerte anche tramite l’uso del colore. Per ultimo si fornirà un esempio di come l’uso creativo dei formati personalizzati consente di risolvere un problema apparentemente arduo. Struttura di un formato numerico personalizzato Un formato personalizzato può avere due tipi di struttura. Esaminiamo innanzitutto il primo. Nella sua forma più generale un formato personalizzato è formato da quattro parti:

Formato_Num_Positivo;Formato_Num_Negativo;Formato_per_Zero;Formato_per_testo

Nelle varie sezioni del formato si immetteranno le opportune stringhe che interpretate da Excel consentiranno di visualizzare la cella secondo le esigenze dell’utilizzatore. Simboli e loro significato

Esaminiamo ora due dei simboli che vengono più di frequente utilizzati per la formattazione di valori numerici: 0 (zero) e #. Ambedue fungono da segnaposto per le cifre, ma una differenza fondamentale fra i due simboli è che mentre lo zero consente di far visualizzare ad Excel anche gli zeri non significativi, ciò non accade se si utilizza l’altro simbolo (#). Vediamo immediatamente come si imposta un formato personalizzato. Un formato personalizzato si imposta immettendo la stringa di formato nella finestra delle proprietà della cella (Comando “Cella” dal menu “Formato” o “Formato celle” dal menu contestuale aperto su una cella appartenente alla selezione), avendo cura di evidenziare la voce “Personalizzato” della scheda “Numero”. In alternativa, si può accedere velocemente alla scheda formato mediante la combinazione di tasti Ctrl+1;


Pag. 32

Come si può notare, sotto “Tipo” appare un elenco contenente una serie di formattazioni disponibili e, immediatamente sopra, una casella di testo ove è possibile editare il formato corrente o digitarne uno completamente nuovo.

Proviamo ora ad immettere un formato personalizzato. Sebbene il formato completa preveda quattro parti, in effetti ciò non è sempre necessario. Se il formato personalizzato include solo una parte, Excel applica quel formato a valori positivi, negativi e zeri; se il formato include due parti, Excel applica la prima parte ai valori positivi ed agli zeri e la seconda ai valori negativi. Nell’esempio illustrato qui sotto desideriamo attribuire al contenuto della cella contenente il numero 120000 il formato 0,0:

Il risultato sarà quello di seguito rappresentato:

Poiché nella stringa di formato dopo la virgola abbiamo indicato uno zero, esso verrà rappresentato anche se esso non è significativo.

Vediamo altri esempi:

Si noti: l’utilizzo del simbolo “#” nell’ultima colonna, che nasconde lo zero prima della virgola. Si noti inoltre come il numero dei simboli che seguono la virgola definiscono le posizioni decimali da

interezza, e potranno apparire eventualmente degli zeri non significativi se è stato utilizzato il simbolo “0” e se la lunghezza della stringa di formattazione è maggiore del numero delle


Pag. 33

visualizzare (sempre, se formate da simboli “0” o solo se significative in caso di utilizzo di “#”). Il numero di simboli che riguarda la parte del numero prima della virgola invece non influisce sulla rappresentazione del numero stesso, nel senso che il numero verrà comunque sempre rappresentato nella sua

cifre:

Nel caso sopra illustrato, il numero 125 viene visualizzato come “00125,00” perché la formattazione personalizzata della parte intera è composta da cinque simboli “0”. Se provassimo ad immettere un numero composto da più di cinque cifre, comunque, esso verrebbe correttamente rappresentato:

Un numero di simboli di formattazione della parte intera maggiore di uno, a parte il caso sopra descritto utile per visualizzare anche posizioni non significative, viene utilizzato per attribuire al numero il simbolo delle migliaia. Si noti, nel caso qui di seguito rappresentato, che poiché la stringa di formato non prevede la presenza di parti decimali, il numero (32650458,96) viene arrotondato alla cifra superiore:


Pag. 34


Pag. 35

Si osservi ancora come ancora prima di applicare il formato una sua anteprima può essere visualizzata nel medesimo box di dialogo:

E’ d’obbligo ancora ricordare come la

rappresentazione del formato del

contenuto sia cosa diversa dal contenuto effettivo della cella. Vediamo adesso ulteriori esempi, nei quali si utilizzeranno, oltre alle stringhe di formato, i codici colore, in modo da far apparire il contenuto della cella del colore voluto a seconda delle circostanze. I codici colore devono essere scritti fra parentesi quadre e precedere ogni parte della stringa di formato. Per esempio:

I codici colore utilizzabili sono i seguenti:

[Nero] [Blu] [Celeste] [Verde] [Fucsia] [Rosso] [Bianco] [Giallo]


Pag. 36

In alternativa possiamo esprimere il colore anche col suo codice:

[Colore14]€.#.###,00;[Rosso]-€. #.###,00

Possiamo inoltre aggiungere ai formati personalizzati simboli e messaggi di nostro gradimento:

I formati personalizzati applicati alle celle contenenti date ed ore

I formati applicabili alle date ed ore sono i seguenti: h: indica l’ora senza zeri significativi [es: 8] hh: indica l’ora con zeri non significativi [es: 08] m e mm, s e ss indicano rispettivamente minuti e secondi, con significato analogo a quello illustrato per le ore. g e gg indicano il numero del giorno rispettivamente senza e con zeri significativi; ggg indica il nome del giorno in forma abbreviata [es: mar] gggg indica il nome del giorno in forma completa [es: martedì] m,mm,mmm,mmmm indicano il mese e funzionano esattamente come per il giorno; a, aaa indicano rispettivamente l’anno esposto in forma breve [es: 08] e completa [es: 2008]


Pag. 37

Vediamo alcuni esempi di formattazione di date ed ore e il risultato ottenuto:

Si noti che all’interno delle stringhe di formato sono stati aggiunti talvolta contenuti testuali, che devono essere racchiusi fra virgolette.

Un formato per nascondere il contenuto delle celle

Esaminiamo ora un formato personalizzato che consente di nascondere alla vista il contenuto della cella. Per nascondere qualsiasi contenuto di una o più celle usare il seguente formato: ;;; Il contenuto della cella rimane però visibile sulla barra della formula sino a quando viene attivato l’attributo “Nascosto” dal box di dialogo “Formato celle” e viene attivata la protezione del foglio. Di ciò si parlerà più avanti nel capitolo r”Gestione della sicurezza dei dati”, al paragrafo “Nascondere celle”. Un esempio di utilizzo dei formati personalizzati Prendiamo come riferimento la cartella di lavoro “Cassa.xls”. Ci si propone, data una giacenza iniziale, di annotare su colonne dedicate le entrate e le uscite giornaliere ottenendo su una successiva colonna il saldo di cassa corrente:

Il saldo corrente sarà ottenuto addizionando al saldo precedente le entrate e sottraendo le uscite giornaliere. In cella H6, quindi, dovremo scrivere la formula: =F3+F6-G6. Con ciò otterremo il saldo corrente dopo la prima giornata.


Pag. 38

Successivamente, però, a partire dalla riga 7, per calcolare il saldo non potremo più fare riferimento alla cella F3 ma alla cella della riga sopra in colonna H, che contiene il saldo precedente. La formula da immettere, quindi sarà: =H6+F7-G7. Chiaramente, questa formula potrà essere copiata nelle celle più in basso. Apparentemente tutto funziona. Ma problemi potrebbero nascere nel momento in cui si presenti la necessità di ordinare il prospetto dei movimenti. In tal caso la prima riga potrebbe venire spostata verso il basso e sostituita da un’altra riga contenente il riferimento alla cella contenente il saldo nella riga precedente. Per quanto riguarda la formula nella prima riga, possiamo risolvere il problema rendendo il riferimento alla cella F3 misto (quindi la formula diventerebbe =F$3+F6-G6). Rimane però insoluto il problema della formula che ricerca il saldo nella riga precedente. Un esempio chiarirà meglio il tutto. Situazione di partenza:

Nel momento in cui si procederà all’ordinamento ascendente in base alla data, i contenuti delle righe 6 e 7 verranno scambiati. In tal caso, mentre la formula in cella H6 continuerà a funzionare, la formula in cella H7, spostata una riga più sopra darà un errore:

Si nota, infatti, che la formula per il calcolo del saldo ora in cella H6 ricerca il saldo precedente nella riga superiore, ma trova al suo posto una stringa alfanumerica.


Pag. 39

Il problema può essere risolto uniformando le formule per il calcolo del saldo corrente, facendo sempre riferimento alla cella superiore contenente il saldo precedente. A tal fine, però, dobbiamo riprodurre in cella H5, al posto della stringa “Saldo precedente”, il contenuto della cella F3. Ciò può essere ottenuto con la semplice formula: =F3:

In tal modo la formula per calcolare il saldo sarà sempre la stessa, del tipo:

=Cella_superiore + Entrate_giornaliere - Uscite_giornaliere

… tranquillamente riproducibile su tutto il prospetto. Ovviamente non avremo più i problemi che si evidenziavano con la soluzione precedente.

Dovremo però ora affrontare un problema meramente estetico che ci consenta di far apparire nella cella di intestazione H5 la stringa “Saldo corrente”, pur mantenendo la formula ivi contenuta, che come si è constatato è indispensabile per il funzionamento del prospetto. A tal fine ricorriamo ad un formato personalizzato. Faremo cioè in modo che per qualsiasi valore, positivo, negativo o pari a zero il contenuto numerico della cella H5 appaia rappresentato dalla stringa “Saldo corrente”.


Pag. 40

Rendiamo attiva la cella H5 ed accediamo al box di dialogo di formattazione, dove creeremo il seguente formato personalizzato:


Pag. 41

Come noto, la prima parte della stringa di formattazione verrà applicata ai valori positivi o pari a zero, la seconda ai numeri negativi. Il risultato sarà, come voluto, il seguente:

Si noti l’effettivo contenuto della cella H5 sulla barra della formula e ciò che invece vi appare. Gli operatori condizionali di formato

Come affermato nell’introduzione all’argomento, un formato personalizzato può avere due tipi di struttura. Esaminiamo ora la seconda possibilità. I formati personalizzati possono essere utilizzati anche per adattare la visualizzazione con l’ausilio degli operatori di confronto. A tal fine, la struttura della stringa di formato diventa la seguente:

[Condiz_1]Formato Condiz_1;[Condiz_2]Formato Condiz_2];Formato per altro

Ad esempio:

Si noti che le condizioni poste fanno riferimento a valori immessi direttamente all’interno della stringa di formato. Non è cioè possibile, all’interno della stringa di formattazione, riferirsi a contenuti dinamici posti in una o più celle. Per conferire maggiore flessibilità e dinamicità alla formattazione è necessario operare attraverso la formattazione condizionale precedentemente illustrata.


Pag. 42

Eliminazione e copia di formati personalizzati

I formati personalizzati possono essere eliminati evidenziandoli nella finestra “Formato” e premendo il pulsante “Elimina”. Ciò è possibile esclusivamente per i formati creati dall’utente. I formati offerti nativamente da Excel non sono invece eliminabili (ed il pulsante “Elimina” è disattivato). I formati personalizzati appartengono alla cartella di lavoro nella quale essi sono stati creati. Ciò significa che senza gli opportuni accorgimenti essi rimarranno in tale ristretto ambito. Per poter utilizzare altrove i formati personalizzati creati in una cartella di lavoro si possono seguire due strade:

la prima consiste nel copiare ed incollare nella cartella di lavoro di destinazione le celle personalizzate della cartella di origine;

la seconda strada è quella di salvare la cartella contenente le formattazioni personalizzate come modello. Ciò, come noto, può essere fatto scegliendo l’opportuno formato in sede di salvataggio della cartella oppure, successivamente, variando l’estensione della cartella da “.XLS” a “.XLT”. In tal modo, facendo “doppio clic” sull’icona del modello si creerà una nuova cartella di lavoro, basata sul modello, contenente le personalizzazioni.


Pag. 43

Tecniche di visualizzazione

Occupiamoci adesso delle tecniche che ci consentono di visualizzare il contenuto del foglio. L’esigenza di possedere le corrette tecniche è tanto più utile quanto più è esteso e complesso il contenuto della cartella di lavoro. Zoom

Innanzitutto, per esaminare una maggiore o minore quantità di dati possiamo agire sul fattore di ingrandimento, tramite la voce “Zoom” del menu “Visualizza” o tramite l’apposito pulsante sulla barra degli strumenti:

Possiamo accettare uno dei valori offerti dalla casella a discesa o impostarne uno personalizzato scrivendolo direttamente nella casella e premendo <Invio>. Blocco riquadri

Un altro comando utile è quello che ci consente di mantenere sempre visibile, anche durante lo scorrimento verticale e/o orizzontale, le parti del documento poste rispettivamente sopra ed a sinistra della cella corrente. Prendiamo ad esempio il database contenuto nella cartella di lavoro “Sedi e strutture.xls”:

Durante l’operazione di scorrimento verticale di un elenco costituito da molte righe potrebbe essere utile mantenere sempre visualizzata la riga contenente le intestazioni dei campi e la colonna contenente il codice. A tal fine operare come segue:


Pag. 44

Cliccare sulla cella immediatamente più in basso ed a destra delle righe e colonne che si vogliono mantenere “fisse”; nel nostro caso, sceglieremmo la cella B4; Scegliere il comando “Blocca riquadri” dal menu “Finestra”. Una volta impartito il comando, si potrà constatare agevolmente che le righe da 1 a 3 e la colonna A risultano sempre visibili, anche se si scorre il foglio verticalmente ed orizzontalmente (notare i riferimenti di riga e di colonna…):

Il comando “Sblocca riquadri” del menu “Finestra” consente di tornare alla situazione precedente. Caselle di divisione

Un’ulteriore possibilità di visualizzare contemporaneamente parti anche distanti del foglio è data dall’uso delle caselle di divisione, poste immediatamente sopra la barra di scorrimento verticale ed immediatamente a destra di quella orizzontale:


Pag. 45

Posizionandosi su tali oggetti (il puntatore del mouse assume la forma di una doppia freccia) e trascinando, è possibile ottenere la divisione del foglio in più riquadri, ognuno dotato di proprie barre di scorrimento:

Si notino i riferimenti di riga e di colonna nei vari riquadri. Un’azione di trascinamento dei delimitatori dei riquadri verso l’esterno riporta il foglio alla visualizzazione normale.


Pag. 46

Gestione della sicurezza dei dati

Protezione

Excel consente di proteggere le celle o addirittura interi fogli da involontarie variazioni e cancellazioni. E’ intuibile, per esempio, che celle contenenti criteri di calcolo complessi (formule, funzioni) non debbano solitamente essere modificate. La protezione delle celle selezionate è gestita tramite la pagina “Protezione” del box di dialogo “Formato celle”:

Come si può notare, tutte le celle sono per default “bloccate”. Viene spontaneo domandarsi il perché, allora, sia possibile modificarle senza alcun problema. La risposta è che per rendere effettivo il blocco delle celle, è necessario procedere alla sua attivazione mediante il comando “Protezione” – “Proteggi Foglio” del menu “Formato”:

Come si nota, è possibile impostare anche una password di protezione (non sicura, in effetti…). La parte inferiore della finestra riporta le azioni che è consentito compiere sulle celle bloccate. Si potrebbe per esempio bloccare le modifiche al contenuto, ma consentire una diversa formattazione o una diversa impostazione dell’altezza delle righe. Come si è detto, tutte le celle possiedono l’attributo “Bloccato”. In ragione di ciò nel momento in cui verrà attivata la protezione tutto il foglio non sarà modificabile. Excel gestisce la sicurezza in via “cautelativa”: tutte le


Pag. 47

celle vengono bloccate, a meno che l’utente non decida che su alcune di esse debba essere possibile agire. Proviamo a gestire questa situazione, servendoci della cartella di lavoro “Fattura.xls”:

Le aree che l’utente potrà modificare sono quelle relative con sfondo colorato, relative alla Descrizione, Quantità, Importo unitario, %IVA. Attribuire uno sfondo riconoscibile alle celle modificabili non è ovviamente indispensabile, ma in tal modo l’utente è in grado di valutare a colpo d’occhio le aree sulle quali gli è consentito agire. Prima di abilitare la protezione dovremo attribuire la proprietà “Non bloccata” alle aree sopra citate. A tal fine evidenziamo le celle desiderate (si ricorda la possibilità di tenere premuto il tasto Ctrl per evidenziare celle/aree non contigue…) e togliamo alle celle l’attributo “Bloccato”. Fatto ciò, attiviamo la protezione:

Il risultato di tale operazione è che l’utente potrà posizionarsi solo sulle celle sbloccate. Ovviamente, se dovessero risultare necessarie delle modifiche, la protezione potrà essere momentaneamente disattivata (Menu Strumenti – Protezione…).


Pag. 48

Nascondere righe, colonne, fogli e celle

Prendiamo ancora in considerazione la cartella “Fattura.xls”. Potremmo per esempio ritenere che la colonna riportante il calcolo dell’importo dell’IVA non debba essere visualizzata. Essa però è vitale per il funzionamento delle formule, e non dovrebbe essere eliminata. La soluzione consiste nel nascondere tale colonna. In tal modo le formule verranno preservate ma la colonna non sarà più visualizzata. Procediamo alla modifica. Innanzitutto, se presente, dovremo disattivare la protezione al foglio:

Successivamente dovremo evidenziare la colonna da nascondere, cliccando sulla sua intestazione e scegliere la voce “Nascondi” dal menu che si apre cliccando sulla selezione col pulsante destro:

Si procederà in maniera analoga se fosse necessario nascondere una o più righe.


Pag. 49

Il comando “Scopri”, presente nel medesimo menu sopra raffigurato viene invece utilizzato per tornare a visualizzare le colonne e le righe nascoste. E’ però necessario che preventivamente vengano selezionate almeno due colonne/righe, a sinistra/sopra ed a destra/sotto della/delle colonne o righe da rivisualizzare. Nel caso in esame, per esempio prima di impartire il comando “Scopri” sono state selezionate le colonne F ed H:

Si procede in maniera analoga se fosse necessario sottrarre alla vista uno o più interi fogli della cartella di lavoro. A tal fine rendere attivi i fogli che si desiderano nascondere e procedere come qui sotto indicato:

Si noti con l’occasione che il medesimo menu offre una modalità alternativa, a quella prima esaminata per nascondere e scoprire righe e colonne.


Pag. 50

Nascondere celle Dall’esame della finestra di dialogo che ci consente di agire sulla protezione si sarà notata anche la possibilità di nascondere le celle:

In realtà l’attivazione delle casella di selezione “Nascosta” non provoca l’invisibilità del contenuto della cella quanto l’impossibilità di visualizzarne il contenuto sulla barra della formula. Proviamo questa funzionalità servendoci del foglio “Fattura.xls”: Vogliamo, per esempio, nascondere alla vista la formula che calcola l’importo totale:

Dopo aver attribuito alle celle interessate l’attributo “Nascosta” ed attivato la protezione del foglio (“Strumenti/Protezione”) la formula sarà ancora presente e funzionante ma non sarà più visibile sulla barra:

Per nascondere il contenuto della cella anche dal foglio è possibile utilizzare il formato numerico personalizzato “;;;”. Vedasi, al proposito, il capitolo “Stili di visualizzazione”, paragrafo “Un formato per nascondere il contenuto delle celle”


Pag. 51

Ordinamento dei dati

Talvolta, per consultare in modo efficiente il database, ma anche per svolgere determinate operazioni (come si vedrà in seguito a proposito dei subtotali) è necessario procedere all’ordinamento dei record del database secondo uno o più criteri. Innanzitutto due concetti:

Per ordinamento ascendente si intende l’ordinamento alfabetico dalla A alla Zeta, o l’ordinamento di numeri dal più piccolo al più grande;

Per ordinamento discendente si intende, al contrario, l’ordinamento alfabetico dalla Zeta alla A o l’ordinamento numerico dal numero più grande a quello più piccolo.

Si noti che l’ambito dell’ordinamento non è limitato alle espressioni letterali ed ai numeri. Si ricorda che anche le date e gli orari sono numeri rappresentati secondo determinate convenzioni (gg-mm-aaaa, g/m/aa, h:m:ss, ecc) e pertanto possono essere ordinati (e, naturalmente, costituire elementi di una formula per calcolare, ad esempio, i giorni intercorrenti fra due date). Vediamo ora come procedere all’ordinamento. Prima di iniziare è indispensabile rendere attiva una cella dell’intervallo che vogliamo ordinare. Ciò è necessario e sufficiente perché Excel riesca a determinare l’ambito complessivo sul quale agire. Un primo metodo consente di ordinare un elenco secondo i contenuti di una colonna. Individuata la colonna, attiviamone una cella. Osserviamo ora sulla barra degli strumenti due pulsanti che ci consentono rispettivamente di ordinare i dati in modo ascendente o discendente:

Ordinamento ascendente

Ordinamento discendente

Il riferimento a lettere che appare sui pulsanti potrebbe essere ingannevole. In realtà è possibile ordinare anche contenuti numerici.

La pressione del pulsante causerà l’immediato ordinamento secondo il criterio prescelto.


Pag. 52

Un secondo metodo consente di ordinare il database secondo più di un criterio. Prendiamo a riferimento il seguente database (Sedi e Strutture.xls)

Si desidera visualizzare tutte le strutture localizzate in una determinata sede. Per ogni sede, inoltre, si desidera che le strutture siano ordinate alfabeticamente. A tal fine dal menu “Dati” scegliamo il comando “Ordina”, sempre avendo cura di rendere attiva una cella dell’intervallo:


Pag. 53

Le caselle a discesa ci consentono di scegliere i campi in base ai quali procedere all’ordinamento. Si noti, al proposito, che Excel ci fa vedere i nomi dei campi poiché considera automaticamente la prima riga del database come la riga di intestazione, e non la prende in considerazione durante l’operazione di ordinamento. Se così non fosse, per esempio nel caso in cui abbiamo importato dei dati senza intestazioni da un altro applicativo, per ordinare l’intero intervallo dovremo attivare il pulsante di opzione “Senza riga di intestazione”. In tal caso Excel ci consente ancora di scegliere i criteri di ordinamento ma non può fare altro che proporci riferimenti alle colonne (quindi, piuttosto che “Sede”, vi troveremmo “Colonna B”).


Pag. 54

Analisi dei dati - Subtotali

Excel mette a disposizione numerosissimi strumenti di analisi a mezzo dei quali i dati presenti possono essere facilmente trasformati in informazioni. Esamineremo qui di seguito i subtotali e le strutture. Prenderemo come riferimento il database contenuto nella cartella di lavoro “Vendite.xls” qui sotto raffigurato:

La funzionalità “Subtotali” consente di operare dei calcoli su dei sottoinsiemi omogenei di dati e di fornirci delle risposte a quesiti del tipo:

Quante vendite di carne sono state effettuate nel corso del mese di Luglio 2006 ?

Qual è il totale delle vendite per ogni agente nel corso del 2007 ?

Qual è il totale delle vendite per anno, per area e per agente ? Qual è l’importo medio, massimo e minimo delle vendite

effettuate nell’area Sud nel 2006 ? ecc…

Si accede al calcolo dei subtotali attraverso la voce “Subtotali” del menu “Dati”, previa selezione di una cella del database:


Pag. 55

Con questa finestra di dialogo possiamo chiedere ad Excel di operare dei calcoli sulla colonna specificata in “Aggiungi subtotali a:” (nel nostro caso, la colonna Vendite”) ogni volta che cambia il contenuto del campo indicato sub “Ad ogni cambiamento in:” Nel caso in esame, per esempio, verrà fatta la somma delle vendite ogni volta che cambierà la descrizione del prodotto. Ora, se l’obiettivo è quello di ottenere i totali delle vendite di ogni prodotto, dovremo preliminarmente curarci di avere un database ordinato in base a questa colonna. Si è affermato, infatti, che i sottoinsiemi sui quali operare devono essere omogenei. Operiamo quindi un ordinamento preliminare del database cliccando su una cella qualsiasi della colonna Prodotto e successivamente sul pulsante di ordinamento crescente presente sulla barra degli strumenti.

Una volta portata a termine questa operazione apriamo il box di dialogo “Subtotali” tramite il menu “Dati” ed impostiamo le scelte come da figura qui sotto:


Pag. 56

Cliccando su OK otterremo il seguente risultato:

Si noti la comparsa:

di una riga che riporta il totale delle vendite di ogni singolo prodotto e di una riga riassuntiva, posta alla fine dell’elenco, riportante il totale generale delle vendite;

di una serie di pulsanti detti “pulsanti di struttura” in alto a sinistra;

di una serie di indicatori di struttura lungo il lato verticale della finestra

Questi due ultimi strumenti vengono utilizzati per nascondere o visualizzare i dettagli. I pulsanti della struttura agiscono su tutto il database, e la pressione di ognuno di essi consente di vedere una maggiore o minore sintesi; per esempio il pulsante 1 visualizza il totale generale, ecc. I pulsanti “-“ e “+” presenti negli indicatori di struttura vengono invece utilizzati per nascondere / visualizzare dettagli relativi ad un singolo gruppo di righe.


Pag. 57

Negli esempi qui sopra raffigurati si è agito sui pulsanti “-“/”+” per visualizzare solo il dettaglio delle vendite dei prodotti caseari (a sinistra); si è agito sul pulsante “2” (figura di destra) per visualizzare i totali di tutti i gruppi ed il totale complessivo. La struttura così ottenuta può essere annullata premendo il pulsante “Rimuovi tutti” presente nella finestra di dialogo “Subtotali”. Vediamo ora un esempio più complesso. Ci proponiamo di ottenere le somme delle vendite effettuate per anno, per area e per agente. Per prima cosa dovremo procedere ad un ordinamento del database sulla base dei calcoli voluti. Ma prima ancora, se non lo avessimo fatto in precedenza, dobbiamo eliminare ogni subtotale presente:

Rendere attiva una qualsiasi cella del database; Dal box di dialogo Subtotali premere il pulsante

“Rimuovi tutti” e chiudere la finestra. Procediamo ora all’ordinamento del database. Poiché è richiesto un ordinamento su tre livelli (Anno, Area, Agente), agiremo tramite la finestra “Ordina” del menu “Dati”:


Pag. 58

Ottenuto l’ordinamento desiderato procediamo al calcolo dei subtotali. Procederemo a tre successive operazioni di calcolo dei subtotali, avendo cura di chiedere ad Excel di non sostituire i nuovi subtotali ai precedenti. Procediamo al calcolo dei subtotali per Anno e premiamo OK. Successivamente procediamo analogamente per quanto riguarda l’Area e l’Agente:

Il risultato ottenuto, una volta compressa la struttura, sarà il seguente:


Pag. 59

Analisi dei dati - Strutture

Le strutture generate dalla funzionalità “Subtotali” costituiscono un valido strumento di analisi utilizzabile anche indipendentemente dal calcolo dei subtotali. Immaginiamo di disporre di un elenco come quello rappresentato qui di seguito (la cartella di lavoro è “Moda & Sport.xls”:

Il foglio raffigurato contiene formule di calcolo dei totali di righe (in grassetto) e colonne (Semestre1/2, Tot. Anno). Excel è in grado di riconoscere automaticamente i calcoli presenti e generare la conseguente struttura. A tal fine è sufficiente rendere attiva una qualsiasi cella dell’intervallo da analizzare e tramite il menu “Dati” scegliere “Raggruppa Struttura” e poi “Struttura automatica”. Il risultato ottenuto una volta impartito il comando ed operata la compressione parziale della struttura è il seguente:


Pag. 60

Si notino i simboli ed i pulsanti di struttura sia per le righe per le colonne. Le formule infatti si riferiscono sia a somme di righe che a somme di colonne. E’ inoltre possibile generare una struttura partendo da elenchi che non riportano formule ma solo valori che esprimono dei totali. E’ il caso che potrebbe verificarsi se, per esempio, venissero importati in Excel dei dati generati da un altro applicativo, come ad esempio un report proveniente da un programma di contabilità funzionante su un minicomputer aziendale. In tali casi sarà l’utente a dover indicare ad Excel quali sono le righe e le colonne cui gli importi raffiguranti i totali fanno riferimento. Vediamo un esempio:

Come si può notare, questo elenco non contiene alcuna formula. Proviamo a generare la struttura per le righe della categoria “Scarpe normali”: Evidenziamo innanzitutto le righe da sommare (quindi la zona A6:H9) e diamo il comando “Dati”- “Raggruppa e struttura” – “Raggruppa” – “Righe” e premiamo OK:


Pag. 61

Operiamo analogamente per ottenere la struttura relativa ai semestri della categoria “Scarpe normali”, evidenziando prima le vendite dei trimestri 1 e 2 e successivamente operando analogamente per i restanti due trimestri:

La cancellazione della struttura può avvenire:

Globalmente, tramite il comando Raggruppa e struttura – Cancella struttura;

Individualmente, per ogni singolo subtotale creato, tramite il comando Raggruppa e struttura – Cancella struttura – Separa.


Pag. 62

Database – Un approfondimento I database vengono classificati in base alla tipologia di organizzazione delle informazioni. Diffusi in particolare sono i database relazionali, nei quali le varie tabelle costituenti il database sono poste fra loro in relazione (da qui il nome...) attraverso un campo comune, detto campo chiave. Nei progetti complessi di database è rilevante, ai fini della facilità di accesso ai dati, l'occupazione dello spazio sul disco e la sicurezza, un procedimento di ottimizzazione della struttura del database medesimo che prende il nome di "normalizzazione". Per comprendere meglio questi concetti esaminiamo qui di seguito un esempio elementare, riguardante il prestito di libri da parte di una biblioteca. Una prima struttura di database che ci consente di registrare i prestiti potrebbe prevedere un'unica tabella, dedicata alla memorizzazione delle informazioni, organizzata in questo modo:

Data prestito Scadenza prestito Titolo del libro Editore Prezzo Anno di acquisto Cognome e nome utente Indirizzo utente Città utente Telefono utente

Questo approccio, sebbene efficace, non è ottimale perché per ogni operazione di prestito devono essere ripetute le informazioni riguardanti il libro e l'utente. Ciò porta ai seguenti svantaggi:

Spreco di spazio: le stesse informazioni devono essere ripetute più volte, esattamente per tutte le volte in cui un libro viene prestato ad un determinato utente;

Difficoltà di modifica: se ad esempio ci accorgiamo che abbiamo scritto erroneamente l'indirizzo dell'utente dovremo procedere alla correzione di tutte le registrazioni dei prestiti pregressi.

La normalizzazione del database ci consente di porre rimedio facilmente a tale situazione. Prendiamo ancora in considerazione il nostro database:

Data prestito Scadenza prestito Titolo del libro


Pag. 63

Editore Prezzo Anno di acquisto Cognome e nome utente Indirizzo utente Città utente Telefono utente

I colori evidenziano tre distinte categorie di informazioni: In nero, i dati relativi al prestito; In rosso, i dati relativi ai libri; In blu, i dati relativi agli utenti Facciamo ora corrispondere ad ogni colore una distinta tabella: Tabella prestiti Data prestito Scadenza Prestito Codice libro Codice utente Tabella Libri Codice libro Titolo del libro Editore Prezzo Anno di acquisto Tabella Utenti Codice utente Cognome e nome utente Indirizzo utente Città utente Telefono utente Quando abbiamo accennato, in precedenza, ai database relazionali, si è affermato che le varie tabelle costituenti il database sono poste fra loro in relazione attraverso un campo comune. A tale scopo, come si sarà notato,sono stati aggiunti ove necessario i campi "Codice utente" e "Codice libro". Vediamo ora servendoci di uno schema come le tre tabelle che abbiamo qui sopra definito interagiscono fra di loro:


Pag. 64

"Codice libro" e "Codice utente" sono appunto i campi comuni attraverso i quali le tabelle interagiscono. Quando, ad esempio, nell'esaminare la registrazione di un prestito si desiderano conoscere gli estremi dell'utente, vi si potrà accedere attraverso il campo chiave "Codice utente"; analogamente per quanto riguarda le informazioni sui libri. I vantaggi di questo tipo di organizzazione sono evidenti. Ora, per ogni nuovo prestito sarà sufficiente annotare i codici utente e del libro (risparmio notevolissimo di spazio su disco), e nel caso in cui debba essere corretto un dato dell'utente o di un volume, basterà farlo una sola volta nelle rispettive tabelle pe vedere immediatamente riflessi tali cambiamenti in tutto il database. Uno strumento complesso come i database relazionali richiede l'utilizzo di programmi appositamente concepiti per la loro creazione ed utilizzo, come ad esempio, Microsoft Access.


Pag. 65


Pag. 66

Parte seconda Creare e gestire i grafici con Microsoft Excel


Pag. 67

Creare e gestire i grafici con Microsoft Excel Introduzione

Un’immagine, si usa dire, vale più di mille parole. Ciò è vero, in effetti, anche quando si tratta della rappresentazione grafica dei dati. Excel mette a disposizione funzioni complesse ma allo stesso tempo intuitive per creare grafici dall’aspetto professionale ed accattivante. In questa sezione si illustreranno le principali tecniche per la creazione e la formattazione di un grafico. Elementi di un grafico Prima di iniziare, è opportuno prendere confidenza con la terminologia relativa ai grafici, servendoci dell’immagine sottostante:

E’ opportuno precisare subito che tutti gli elementi sopra evidenziati sono modificabili e possono apparire o no a scelta dell’utilizzatore. Quanto alla rappresentazione grafica dei dati, essa è dinamica. Infatti si potrà facilmente constatare che al variare dei dati che hanno originato il grafico anche quest’ultimo si modificherà per riflettere le variazioni intervenute. Creazione di un grafico Il primo passo per la creazione di un grafico è la selezione delle aree del foglio contenenti i dati. Utilizzeremo per i nostri esempi la cartella di lavoro “Grafici.xls”. Iniziamo esaminando il foglio “Temperature”, parzialmente riprodotto qui sotto:


Pag. 68

La rappresentazione grafica dell’andamento delle temperature richiede che:

Vengano rappresentate due serie di dati (per gli anni 2007 e 2008);

Che le serie di dati corrispondano a quelli presenti nelle due colonne 2007 e 2008;

I valori dell’asse X (asse delle ordinate) corrispondano ai giorni (1,2….);

I valori presenti sulla prima riga (2007, 2008) costituiscano le legende dei valori rappresentati.

Excel si occupa automaticamente di soddisfare le esigenze sopra descritte se i dati da rappresentare possiedono la struttura illustrata. In particolare, è opportuno che la cella posta all’incrocio fra titoli delle righe e delle colonne (cella B4, nel caso) rimanga vuota.

Proviamo ora a creare un primo grafico con le impostazioni predefinite di Excel.

Il grafico verrà in questo caso posto su un foglio a parte, un c.d. “foglio grafico”, oggetto che Excel mette a disposizione proprio per accogliervi rappresentazioni grafiche.

1. Per prima cosa selezioniamo l’area che contiene i dati da rappresentare, e cioè la zona B4:D35;

2. Premiamo ora il tasto funzionale F11. 3. Excel provvederà a creare immediatamente alla creazione di

un grafico secondo le impostazioni predefinite: Com

e si accennava, il tipo di grafico potrebbe essere diverso da quello


Pag. 69

rappresentato (istogramma), poiché ciò dipende dalle impostazioni predefinite scelte dall’utente. Ma è agevole, comunque, passare da un momento all’altro alla visualizzazione di un altro tipo di grafico. Proviamo per esempio a cambiare il grafico in un grafico a linee. A questo scopo è sufficiente aprire il menu “Grafico” e scegliere la voce “Tipo di grafico”(ovviamente il grafico deve essere selezionato…):

Il grafico così ottenuto è completamente modificabile nel suo aspetto e nelle sue proprietà. Per cominciare a conoscere le possibilità di personalizzazione, proviamo a creare un grafico con una modalità più


Pag. 70

interattiva, servendoci della procedura assistita “creazione grafico” messaci a disposizione da Excel. Ci serviremo ancora del foglio “Temperature” e questa volta, anziché creare il grafico su un foglio a parte, provvederemo a posizionarlo sullo stesso foglio contenente i dati origine. Per prima cosa rendiamo attivo il foglio “Temperature” e controlliamo che siano selezionate le celle come nell’esempio precedente. Ciò fatto, iniziamo la creazione guidata del grafico premendo il pulsante presente sulla barra degli strumenti oppure utilizzando il comando “Grafico” presente nel menu “Inserisci”:

Per prima cosa ci viene proposta la scelta del tipo di grafico; successivamente dovremo decidere se la serie dei dati che vogliamo rappresentare si trovano disposti su delle colonne (è il nostro caso) o su righe:

Si noti anche che l’intervallo dati appare già definito, e corrisponde alle celle selezionate prima di lanciare la procedura assistita di creazione. E’


Pag. 71

comunque possibile procedere alla selezione delle aree interessate, o alla loro correzione, anche a partire da questa finestra. Premendo il pulsante “Avanti” si apre una finestra con più schede che ci consente di personalizzare ulteriormente il grafico:

Ci occuperemo in questa sede della pagina “Titoli”, dove inseriremo, ad esempio, come titolo del grafico la stringa “Andamento delle temperature” (senza virgolette) e come asse dei valori X ed Y rispettivamente “Giorni” e “Temperature” (sempre senza virgolette).

Trascuriamo per ora il resto e soffermiamoci sulla pagina “Tabella dati”:


Pag. 72

Attivando la relativa casella di controllo avremo la possibilità di far apparire immediatamente sotto il grafico i dati origine. Una volta scelta questa opzione e premuto il pulsante “Avanti” avremo la possibilità di scegliere dove posizionare il grafico. La nostra scelta sarà il foglio “Temperature”. Una volta operata questa scelta e premuto il pulsante “Fine” otterremo il seguente risultato:

Sebbene apprezzabile, il risultato ottenuto costituisce la base per ulteriori personalizzazioni. A tal fine è sufficiente cliccare col pulsante destro sull’elemento desiderato e scegliere la voce “Formato…”. Molti elementi, inoltre, quali ad esempio la legenda, il titolo, le etichette degli assi, sono spostabili e ridimensionabili mediante trascinamento. Egualmente ridimensionabile è l’area del tracciato e l’area del grafico.


Pag. 73

Proviamo a personalizzare il grafico cambiando le proprietà di alcuni elementi: La posizione della legenda, ridimensionandola e trascinandola in basso a sinistra:

Cliccare sulla legenda e trascinarla in basso a sinistra; Allargarne le dimensioni in modo da fare apparire le

descrizioni disposte su una sola riga. Le dimensioni dell’area del tracciato:

Innanzitutto ridimensioniamo l’area del grafico mediante un’operazione di trascinamento:

Quindi ridimensioniamo l’area del tracciato:


Pag. 74

Possiamo anche adeguare la formattazione della tabella dati sottostante cliccandovi col pulsante destro e scegliendo la voce “Formato tabella dati”. In questo caso riduciamo la dimensione del carattere a 8 punti.

Il nome dell’etichetta dell’asse X (“Giorni”):

Modifichiamo il contenuto dell’etichetta dell’asse X da “Giorni” a “Giorni e temperature”. A tal fine è sufficiente selezionare con un click l’oggetto e successivamente, con un secondo click, posizionare il cursore all’interno del testo da modificare.

Lo sfondo ed il bordo del grafico (area del tracciato):

A tal fine clicchiamo col pulsante destro nell’area del grafico. Si apre un box di dialogo con il quale possiamo impostare il colore, lo stile e lo spessore del bordo dell’area nonché decidere lo stile di riempimento dello sfondo. Riguardo a quest’ultimo è possibile:

o Scegliere uno dei colori disponibili; o Scegliere un effetto di riempimento secondo varie

combinazioni di colore, trame ecc.. Notevole, in particolare, la possibilità di scegliere quale sfondo una immagine scelta dall’utente.


Pag. 75

In considerazioni del fatto che la legenda già appare nella tabella dati possiamo eliminarla cliccandoci sopra per selezionarla e premendo il tasto “Canc”. Il formato delle serie dati:

Cliccare col pulsante destro su un punto del tracciato e scegliere la voce “Formato serie dati”

Aggiunta e rimozione di una serie di dati

Si esamineranno ora le procedure per aggiungere, successivamente alla creazione del grafico, nuove serie di dati e le tecniche necessarie alla creazione di un grafico quando i dati di origine non siano formattati in maniera adeguata. A tali scopi ci serviamo ancora della cartella di lavoro “Grafici.xls” e del foglio “Vendite” qui di seguito rappresentato.


Pag. 76

Provvederemo innanzitutto a creare un grafico per le vendite dei mesi di Gennaio e Febbraio; successivamente aggiungeremo i dati delle vendite di Marzo ed aggiorneremo il grafico. L’area origine per il grafico di Gennaio e Febbraio è B3:D9. Dopo alcune formattazioni l’aspetto del grafico è il seguente:

Successivamente provvederemo ad immettere i dati di vendita del mese di Marzo e ad aggiungere la nuova serie al grafico:

A tal fine selezioniamo le vendite di Marzo includendo anche l’intestazione della colonna;

Successivamente, posizionandoci sul bordo della selezione (il puntatore del mouse assume la forma di una croce con quattro frecce) trasciniamo all’interno dell’area del tracciato.


Pag. 77

Al rilascio del pulsante del mouse Excel ci chiederà conferma dell’operazione ed una volta premuto il pulsante Ok provvederà ad aggiungere la nuova serie al grafico. I valori selezionati per il mese di Marzo comprendono anche la riga di intestazione della colonna. Ciò è importante se si desidera che, assieme ai dati, venga aggiornata anche la legenda.

Occupiamoci ora della selezione di aree di origine del grafico non contigue. A tal fine, sempre servendoci del foglio “Vendite”, ci proponiamo di generare un grafico che riporti la percentuale delle vendite del trimestre per ogni negozio.


Pag. 78

Come si nota, gli elementi necessari si trovano in colonne agli estremi del prospetto (Colonne B e G). Per generare il grafico sarà necessario procedere alla definizione delle aree innanzitutto selezionando le celle B5:B9 e successivamente, tenendo premuto il tasto Ctrl, le celle della colonna G. Poiché vogliamo analizzare la distribuzione di un singolo dato, utilizzeremo questa volta un grafico a torta 3D:

I valori associati ad ogni singola serie sono stati ottenuti modificandone le proprietà della serie dei dati:


Pag. 79

Microsoft Excel-nozioni Avanzate-I Parte

Documents

Transcript of Microsoft Excel-nozioni Avanzate-I Parte