4.3 Conoscenze avanzate di foglio di calcolo
Obiettivo del capitolo In questo capitolo verranno descritte molte funzionalità offerte da Excel 2003 per definire applicazioni di tipo avanzato sui dati contenuti in un generico foglio di lavoro. In particolare verranno approfondite le impostazioni della pagina in modo da rendere più semplice la lettura dei dati in esso contenuti, sia a schermo che sul foglio stampato. Per agevolare la consultazione verrà descritta la formattazione condizionale dei dati, mediante la quale le celle assumono un formato diverso a seconda del valore contenuto. Con il filtro possiamo visualizzare solo quei valori che rispondono a specifici criteri. Vedremo come raggruppare righe (o colonne) in base al loro contenuto, in modo da fare spazio alle rimanenti righe (o colonne). Verranno anche approfondite le impostazioni da definire per personalizzare e per stampare un grafico. Vedremo anche come effettuare collegamenti dinamici tra Excel ed altri applicativi di Office, avendo la possibilità di lavorare con gli strumenti propri di Excel pur lavorando nelle finestre degli altri programmi. Verrà descritta la procedura per pubblicare su Web un foglio di lavoro.
Fase Quattro ◊ 2 Diventare esperti
Nuove abilità Alla fine del capitolo sarai capace di:
Denominare gruppi di celle;
Creare formule per eseguire calcoli;
Impostare le opzioni della pagina in modo da rendere più semplice la consultazione delle stampe;
Lavorare con più gruppi di dati;
Collegare dati tra altre cartelle di lavoro;
Personalizzare le etichette e i numeri di un grafico;
Stampare un grafico;
Stampare una parte di un elenco di dati;
Collegare dinamicamente dati tra Excel ed altri applicativi di Office;
Salvare una cartella di lavoro per il Web e procedere con la pubblicazione;
Gestire i commenti delle celle;
Individuare e correggere errori nei calcoli;
Formattare automaticamente le celle in base ai valori contenuti nelle celle;
Definire dei filtri sulle celle e applicare calcoli sui dati filtrati;
Definire un insieme di valori validi per un intervallo di celle applicando la convalida;
Raggruppare più elenchi di dati;
Ordinare un elenco di dati;
Organizzare i dati in livelli applicando i subtotali;
Definire, modificare e riepilogare gruppi di dati alternativi tramite gli scenari;
Cambiare i dati per ottenere il risultato desiderato tramite la definizione della ricerca dell'obiettivo.
Nuove parole Alla fine di questo capitolo conoscerai i seguenti termini:
Casella nome;
Nidificazione di formule;
Riferimento circolare;
Collegamento dinamico;
Formattazione condizionale;
Conoscenze avanzate di Foglio di calcolo 3 ◊ Fase quattro
Subtotale;
Filtro;
Convalida;
Scenario.
Fase Quattro ◊ 4 Diventare esperti I vari argomenti del seguente capitolo verranno esposti sulla base della cartella di lavoro di esempio Scuole 2003_2004.xls, in cui sono inseriti dati relativi alle scuole italiane in quel biennio.
4.3.0 Denominare gruppi di celle
Come sappiamo, una cella appartenente a un generico foglio di lavoro viene identificata tramite le coordinate che esprimono rispettivamente la colonna e la riga di cui la cella costituisce il punto di incontro. Le coordinate A1 indicano la cella presente nell'intersezione tra la colonna A e la riga 1. Riferimento di colonna A1 Riferimento di riga Se associamo un nome a una cella possiamo riferirla, anziché con le coordinate, con il suo nome, che viene riconosciuto universalmente da tutti i punti della cartella di lavoro. Non sarà necessario specificare il foglio a cui appartiene: si tratta infatti di un riferimento assoluto (fase 1.6.6). I nomi di cella vengono utilizzati per sostituire i riferimenti per quella cella (o intervallo di celle) nelle operazioni di calcolo. Un nome può essere assegnato a una sola cella o a una regione di celle; non possono esistere celle o regioni distinte con lo stesso nome. La procedura è la medesima per la singola cella e per una regione. Nell'esempio Figura 4.3.1 la regione di celle è costituita dall'intervallo K3:L23, dopo averla selezionata digitiamo un nome (Aree) nella Casella nome, il campo posto sulla sinistra della barra della formula; per confermare premiamo il tasto Invio.
Figura 4.3.1 - Assegnazione di un nome a una regione di celle. Il nome può essere costituito da una qualsiasi combinazione di lettere senza spaziature, purché l'iniziale non sia un numero. I nomi definiti compariranno nel menu a discesa associato alla Casella nome; attivando un nome viene selezionata la regione a esso relativa. Per definire un nome possiamo utilizzare anche un’altra procedura. Dopo aver selezionato una cella o una regione facciamo clic sulle voci di menu Inserisci → Nome → Definisci, nella finestra Definisci nome inseriamo il nome che desideriamo assegnare e per completare l’operazione e chiudere la finestra facciamo clic su OK; se facciamo clic su Aggiungi la
Conoscenze avanzate di Foglio di calcolo 5 ◊ Fase quattro finestra non verrà chiusa. La gestione di un nome, associato a una regione o a una sola cella, avviene tramite la stessa finestra: al suo interno possono essere gestite tutte le associazioni esistenti e possono esserne create di nuove (Figura 4.3.2).
Figura 4.3.2 - Finestra per la gestione dei nomi definiti nella cartella di lavoro Come possiamo vedere in figura, tra i vari nomi compare anche quello che abbiamo definito precedentemente (Aree); nella casella di testo Riferito a sono riportate le coordinate relative all'intervallo di riferimento. L'associazione cella-nome può essere modificata cambiando le coordinate nella casella di testo Riferito a:. Facendo clic sul pulsante quadrato sul lato destro della casella possiamo selezionare una cella o un gruppo di celle: le coordinate di riferimento verranno automaticamente inserite nella casella, compresa l'indicazione del foglio in cui essa è presente. Per eliminare l'associazione cella-nome facciamo clic sul pulsante elimina dopo aver selezionato il nome.
4.3.1 Creare formule per eseguire calcoli
Premettiamo quanto andremo a illustrare in questa sezione dicendo che una formula è una combinazione di valori, nomi, funzioni, riferimenti ed operatori preceduta dal simbolo "=". Le formule e le varie funzioni sono state illustrate anche nella sezione 1.6.5 e il modo in cui vengono riferite le celle all'interno delle formule è stato descritto nella sezione 1.6.6. Le funzioni che possono essere richiamate in Excel sono molte e sono tutte raccolte nella finestra di dialogo che viene aperta selezionando la voce di menu Inserisci → Funzione. Le funzioni nella finestra sono suddivise per categoria, per cui una funzione deve essere ricercata nella categoria a cui appartiene. Una funzione viene applicata a un certo numero di argomenti, numero variabile a seconda della funzione, da impostare nella finestra di dialogo che viene aperta dopo aver scelto la funzione. Nella cella contenente la formula viene visualizzato il risultato dell'applicazione della funzione sulle celle impostate come riferimento. Nella barra della formula comunque viene sempre riportata la formula inserita nella cella selezionata. Per cui qualsiasi modifica alla funzione deve essere effettuata all'interno della barra della formula. Funzioni nidificate
Fase Quattro ◊ 6 Diventare esperti La sintassi di una formula segue quella adottata in matematica. L'ordine delle operazioni viene dato dalle parentesi tonde, anche in caso di nidificazioni a più livelli. In pratica non vengono usate le parentesi graffe e le quadre per definire l'ordine di nidificazione. Con nidificazione si intende la possibilità di inserire come parametro di una funzione, il risultato restituito dall'applicazione di un'altra funzione. Un esempio di formula che contiene la nidificazione è la seguente: =SOMMA(MEDIA(A1:B5); MEDIA(B4:F8)) in cui, supponendo che le celle riferite contengano tutte valori numerici, viene calcolata la somma di due numeri che costituiscono rispettivamente la media dei valori contenuti nella regione A1:B5 e la media di quelli contenuti nella regione B4:F8. Riferimento circolare Il riferimento circolare è l'errore dovuto alla definizione di un argomento della funzione contenuto nella cella in cui si sta inserendo la funzione stessa. Un esempio di riferimento circolare è il seguente: supponiamo di inserire nella cella B5 la seguente formula =SOMMA(B1:B6) A excel diamo il comando di inserire nella cella B5 la somma delle celle appartenenti a un intervallo che comprende la cella B5 stessa. Ma come fa Excel a computare il valore della cella B5 se è proprio la cella in cui deve restituire il risultato? In tal caso, non potendo computare la funzione, il programma restituisce l'errore di riferimento circolare.
4.3.2 Rendere più facile la consultazione delle stampe
Spesso il foglio di lavoro è costituito da un'elevata quantità di dati, la stampa su carta ne facilita la lettura. Una delle opzioni di stampa più importanti è quella relativa all’intestazione e al piè di pagina, due sezioni del foglio di lavoro atte a contenere informazioni che verranno ripetute in tutte le pagine stampate. La gestione di tali sezioni è stata descritta nella sezione 1.6.11, alla quale rimandiamo per approfondimenti sull'uso dei vari pulsanti atti allo scopo. Ricordiamo che possiamo lavorare nell'intestazione, o nel piè di pagina, nella scheda Intestaz./Piè di pagina della finestra Imposta pagina, che può essere richiamata selezionando la voce di menu Visualizza → Intestazione e piè di pagina oppure posizionandosi sulla suddetta scheda della finestra associata alla voce di menu File → Imposta pagina. Supponiamo di dover inserire nella sezione sinistra dell'intestazione le informazioni relative al nome del foglio di lavoro e al nome del file, compreso di percorso di memorizzazione, e un'immagine nella sezione destra. Dobbiamo lavorare nella finestra Intestazione (Figura 4.3.3) che viene aperta facendo clic sul pulsante Personalizza intestazione nella scheda Intestaz./Piè di pagina. La procedura è valida anche per la personalizzazione del piè di pagina, facendo clic sul pulsante Personalizza Piè di pagina.
Conoscenze avanzate di Foglio di calcolo 7 ◊ Fase quattro
Figura 4.3.3 - Impostazione dell'intestazione del foglio di lavoro. Quanto inserito nell’area A sinistra: è una combinazione di testo fisso con funzioni che vengono aggiornate automaticamente dal programma e che sono state inserite facendo clic sui relativi pulsanti nella parte centrale della finestra stessa ( e rispettivamente per l'inserimento del nome del file compreso di percorso e del nome del foglio di lavoro). A destra è stata inserita l'immagine selezionata nella finestra che viene aperta facendo clic sul pulsante .Com'è possibile notare in Figura 4.3.3 laddove sono state inserite le funzioni, ne viene indicato il nome tra parentesi quadre, il tutto preceduto dal simbolo "&". Se vogliamo personalizzare le impostazioni dell'immagine, dobbiamo lavorare nella finestra di dialogo (Figura 4.3.4) che viene aperta facendo clic sul pulsante Formato immagine
Figura 4.3.4 nell'intestazione.
.
Impostazione
del
formato
dell'immagine
inserita
L'intestazione e il piè di pagina sono visibili solo quando la pagina è in visualizzazione Anteprima di stampa. Il risultato della nostra impostazione di esempio è riportato in Figura 4.3.5.
Fase Quattro â—Š 8 Diventare esperti
Figura 4.3.5 - Risultato dell'impostazione dell'intestazione del foglio di lavoro.
4.3.3 Posizionare i dati in un documento stampato
Nella sezione 1.6.13 sono state illustrate tutte le opzioni per personalizzare le impostazioni della pagina prima di procedere con la stampa. In particolare se vogliamo centrare il contenuto orizzontalmente o verticalmente nella pagina stampata, dobbiamo selezionare le opzioni Orizzontalmente o Verticalmente, nella parte inferiore della scheda Margini (Figura 4.3.6).
Figura 4.3.6 - Centrare il contenuto nella pagina sia orizzontalmente che verticalmente.
4.3.4 Lavorare con piĂš gruppi di dati
Abbiamo visto nella sezione 4.3.0 come assegnare un nome a una regione di celle; nella 4.3.1 come vengono riferite regioni di celle all'interno delle funzioni. La selezione di regioni di celle, che siano contigue o no, è fondamentale, in quanto rappresenta la fase preliminare dell'applicazioni di alcune funzioni. Per esempio per impostare lo stesso colore di sfondo per un certo insieme di celle oppure per definire un particolare tipo di dato, dobbiamo procedere con la modifica delle impostazioni solo dopo aver selezionato
Conoscenze avanzate di Foglio di calcolo 9 ◊ Fase quattro tutte le celle da modificare. Questo evita di richiamare piÚ volte la stessa funzione per effettuare lo stesso tipo di modifica a regioni di celle distinte. Vediamo ora come effettuare la selezione. La selezione di una cella avviene facendo un clic col tasto sinistro sulla cella stessa, quando il cursore ha la forma di una croce bianca ( ). Selezione di celle contigue Se la regione è composta da celle contigue possiamo selezionarla in uno dei seguenti due modi. Supponiamo di dover selezionare la regione di celle A1:F4. 1. 2.
Selezionare la prima cella appartenente alla selezione (A1) e, tenendone premuto il tasto sinistro, trascinare il mouse fino al raggiungimento dell'ultima cella (F4) della regione; selezionata la prima cella della regione (A1), tenendo premuto il tasto Shift fare clic sull'ultima cella (F4).
Selezione di celle non contigue Per selezionare celle non contigue, per esempio la regione costituita dalle celle appartenenti agli intervalli A1:F4 e A7:F12, dobbiamo combinare l'uso del mouse con quello della tastiera nel seguente modo: selezionata la prima parte della regione (esempio A1:F4) , tenendo premuto il tasto Ctrl dobbiamo selezionare la regione A7:F12, trascinandovi sopra il mouse. Riferimento all'interno delle funzioni Per riferire in una funzione un gruppo di dati contenuti in celle contigue, dobbiamo suddividere le coordinate che costituiscono gli estremi dell'intervallo con il simbolo di due punti ( ":" ). Se le celle da riferire non sono contigue, dobbiamo separare le coordinate con il simbolo di punto e virgola ( ";" ).
4.3.5 Collegare dati di altre cartelle di lavoro
Ogni cella di Excel può essere collegata a un'altra presente nello stesso foglio di lavoro, in un foglio di lavoro diverso ma contenuto nella stessa cartella di lavoro, oppure tra cartelle di lavoro distinte. Elaboriamo i dati del foglio di lavoro Database (Figura 4.3.7) del nostro file di esempio in vario modo.
Fase Quattro ◊ 10 Diventare esperti
Figura 4.3.7 - Cartella di lavoro di esempio. Supponiamo che negli altri due fogli di lavoro (Subtotali e Filtri) debbano essere riportate le informazioni relative alla provincia, alla regione, e all'area geografica. È consigliabile impostare il foglio di lavoro Database come foglio principale, a cui verranno collegate tutte quelle celle il cui valore dovrà essere riportato anche negli altri due fogli. Se deve essere apportata una modifica a valori presenti nei tre fogli, non è necessario apportarla in ciascuno di essi, ma sarà sufficiente apportarle nel foglio di lavoro principale (Database), dal quale attingono gli altri fogli per i loro dati. Il collegamento è alla base di molti strumenti di Excel. Si pensi Per esempio al grafico (si veda la sezione 1.6.15); le modifiche apportate ai dati su cui è stato creato il grafico, vengono riportate automaticamente sul grafico stesso, perché implicitamente un grafico è collegato ai dati sulla base dei quali è stato creato. Creazione di un collegamento Creiamo il collegamento alla cella A1 del foglio di lavoro Database: - attivare la funzione Copia sulla cella A1 del foglio Database; - spostarsi nel punto in cui deve essere creato il collegamento. Se esso deve essere effettuato nello stesso foglio di lavoro, fare clic sulla cella in cui esso deve comparire; se è in un altro foglio della stessa cartella di lavoro, spostarsi sul foglio in questione e fare clic sulla cella; se è in una cartella di lavoro diversa, attivarla e posizionarsi nella cella in questione; - attivare il comando Incolla speciale dal menu Modifica o dal menu di scelta rapida associato alla cella e, nella finestra di dialogo che viene aperta (Figura 4.3.8) fare clic sul pulsante Incolla collegamento.
Conoscenze avanzate di Foglio di calcolo 11 â—Š Fase quattro
Figura 4.3.8 - Attivazione della funzione Incolla speciale. Nella cella collegata viene inserito il valore contenuto nella cella principale; nella barra della formula, quando è selezionata la cella collegata, viene inserita una formula che differisce a seconda della posizione reciproca delle due celle. Se esse risiedono nello stesso foglio di lavoro, nella cella collegata vengono inserite le coordinate della cella precedute dal simbolo uguale ("="); nel nostro esempio sarà =$A$1. Se le due celle sono in due fogli di lavoro diversi, ma nella stessa cartella di lavoro, il collegamento ha il seguente formato: = Database!$A$1. Se le due celle sono in due cartelle di lavoro distinte, il collegamento avrà il seguente formato: =[Scuole_2003_04.xls]Database!$A$1. Possiamo creare un collegamento anche digitando le coordinate della cella precedute dagli elementi sopra indicati, per individuare il foglio di lavoro e, se necessario, anche la cartella di lavoro a cui la cella appartiene.
4.3.6 Personalizzare le etichette e i numeri di un grafico
Nella sezione 1.6.15 abbiamo visto come creare un grafico e come personalizzarne la legenda, i titoli, gli assi e le etichette. Approfondiamo ora la personalizzazione dell'aspetto delle etichette aggiunte alle serie dei dati e il tipo di numero che viene visualizzato in esse. Supponiamo di dover modificare il grafico di Figura 4.3.9, creato sul numero complessivo delle scuole nelle varie regioni d'Italia, suddivise per aree territoriali (Nord, Centro, Isole e Sud).
Fase Quattro ◊ 12 Diventare esperti
Figura 4.3.9 - Grafico rappresentante il numero delle scuole in Italia. Selezioniamo la voce Opzioni grafico dal menu di scelta rapida associato all'area del tracciato o all'area del grafico oppure dal menu Grafico. L'etichetta può contenere vari valori, che corrispondono alle opzioni presenti nella scheda Etichette dati della finestra stessa (Figura 4.3.10).
Figura 4.3.10 – Impostazione delle informazioni contenute nell'etichetta. Se abbiamo scelto la visualizzazione di più di un'informazione nell'etichetta, possiamo impostare anche il simbolo separatore tra le varie informazioni, tra quelli elencati nel menu a discesa associato alla casella relativa a Separatore.
Il tipo di informazione visualizzabile nell'etichetta varia a seconda del tipo di grafico impostato. È inoltre possibile modificare la posizione di alcuni elementi, come il titolo del grafico, le etichette dati e la legenda, semplicemente trascinandoli con il mouse.
Conoscenze avanzate di Foglio di calcolo 13 ◊ Fase quattro Personalizzazione del numero Una volta aggiunte le etichette possiamo personalizzarle lavorando nella finestra che viene aperta selezionando la voce Formato etichette dati dal menu di scelta rapida associato alle etichette. In particolare nella scheda Numero possono essere impostate tutte le proprietà relative al tipo di dato in esse contenuto (numero, testo, valuta, data, percentuale ecc.).
Figura 4.3.11 - Personalizzazione del numero presente nelle etichette.
4.3.7 Stampare un grafico
Per stampare attiviamo l'opzione Grafico selezionato nella finestra di dialogo che viene aperta selezionando la voce Stampa dal menu File. Sempre dopo aver selezionato il grafico, possiamo impostare per la stampa la pagina che lo contiene, selezionando la voce Imposta pagina dal menu File. Molte delle opzioni personalizzabili nella finestra sono le stesse che abbiamo analizzato nella sezione 1.6.13 per impostare le pagine contenenti dati generici. In questo caso vediamo che viene aggiunta la scheda Grafico (Figura 4.3.12), contenente opzioni relative solo alla gestione dell'impostazione della stampa del grafico che può essere stampato a tutta pagina, adattato alla dimensione della pagina o personalizzato. Mediante la selezione del pulsante Anteprima di stampa possiamo vedere come il grafico sarà collocato all’interno della pagina. Infine possiamo decidere se stampare in bianco e nero o in qualità bozza per velocizzare la stampa. Se non vengono personalizzate le opzioni all'interno della sopraindicata scheda, la stampa avviene con le impostazioni predefinite.
Fase Quattro ◊ 14 Diventare esperti
Figura 4.3.12 - Impostazione della pagina per la stampa di un grafico.
4.3.8 Stampare una parte di un elenco di dati
Possiamo stampare solo le celle selezionate presenti all’interno di un foglio di lavoro attivando l'opzione Selezione nell'area Stampa della finestra stessa. Otteniamo lo stesso risultato se impostiamo come Area di stampa la parte dell'elenco prima di procedere con la stampa. Rimandiamo alla sezione 1.6.12 per la definizione dell'Area di stampa.
4.3.9 Includere un documento di Office in un foglio di lavoro di Excel
Un vantaggio offerto da tutti i programmi appartenenti a Office è costituito dalla possibilità di inserire all'interno di documento creato con uno di essi, oggetti creati con altri applicativi del pacchetto. In Excel, all'interno di un foglio di lavoro possiamo inserire, come oggetti, documenti creati con gli altri applicativi di Office. Un oggetto può anche essere creato all’interno del foglio di lavoro. Selezioniamo la voce di menu Inserisci →Oggetto; la finestra che viene aperta è costituita da due schede: la prima Crea nuovo oggetto (Figura 4.3.13) contiene una casella da cui deve essere scelto il tipo di oggetto da creare, nella seconda invece deve essere impostato il percorso dell'oggetto da inserire. Possiamo scegliere di visualizzare l'oggetto come icona, esso verrà aperto all’interno del programma relativo al tipo di file, solo facendo doppio clic sull'icona stessa.
Conoscenze avanzate di Foglio di calcolo 15 ◊ Fase quattro
Figura 4.3.13 - Richiesta di inserimento di un oggetto. Quando inseriamo un oggetto nel foglio di lavoro, viene attivato un riquadro in cui deve essere inserito il testo, l'immagine o l'elemento associato al tipo di oggetto inserito e le barre degli strumenti di Excel vengono sostituite da quelle propri del programma con cui vengono modificati gli oggetti inseriti. Per esempio se nel foglio di lavoro inseriamo come oggetto un nuovo documento di Word, otteniamo la situazione riportata in Figura 4.3.14.
Figura 4.3.14 - Oggetto di Word inserito in un foglio di lavoro. Possiamo notare che il riquadro inserito è un vero e proprio “spaccato” di Word e che gli strumenti attivabili sono quelli di Word. Viene dunque effettuato un collegamento dinamico tra i due programmi a indicare che anche se la finestra contenente è quella di Excel, lavoriamo con gli strumenti di un altro applicativo di Office, nel nostro esempio, quelli di Word. Per tornare a lavorare sul foglio di lavoro Excel è sufficiente fare clic in qualsiasi punto al di fuori della casella dell'oggetto. Gli strumenti applicabili a esso sono quelli di Excel e l'oggetto inserito risulta contenuto in un riquadro, come se fosse un'immagine; sarà possibile spostarlo, ridimensionarlo ed eliminarlo.
Fase Quattro ◊ 16 Diventare esperti
4.3.10 Inserire un documento di Excel come parte di un altro documento di Office
Quanto detto nella sezione precedente, può essere anche effettuato al contrario: all'interno di un documento creato con un altro applicativo di Office, possiamo inserire un file di Excel. Il procedimento è esattamente quello appena visto per Excel. Vediamo la situazione speculare, facendo conto di voler inserire la nostra cartella di lavoro di esempio all'interno di un documento di Word. Posizionato il cursore nel punto del documento di testo in cui deve essere inserito l'oggetto, attiviamo la voce di menu Inserisci →Oggetto e, questa volta, ci posizioniamo nella scheda Da file (Figura 4.3.15).
Figura 4.3.15 - Inserimento di un oggetto esistente. L'oggetto inserito ha le stesse caratteristiche di un'immagine e presenta gli stessi strumenti per la gestione, ridimensionamento ed eliminazione. Facendo doppio clic sull'oggetto possiamo modificarlo usando gli strumenti di Excel. In pratica si presenta una situazione simile a quella descritta nella sezione precedente: si lavora nella finestra di Word, ma con gli strumenti di Excel. Anche in questo caso abbiamo creato un collegamento dinamico tra i due applicativi.
4.3.11 Salvare una cartella di lavoro per il Web
Se salviamo il file come pagina Web, la nostra cartella di lavoro viene gestita come pagina html (approfondiremo la conoscenza di questo linguaggio nella fase 5). Prima di procedere con il salvataggio sul Web, possiamo visualizzarne l'anteprima per vedere se il risultato è quello desiderato. Selezioniamo la voce di menu File → Anteprima pagina Web, viene visualizzata un'anteprima della cartella di lavoro come risulterà in seguito alla pubblicazione, all'interno del browser. In Figura 4.3.16 è riportata l'anteprima Web della nostra cartella di lavoro. Da notare che il file non è aperto all'interno dei Excel, ma in Internet Explorer, come ogni file di tipo html.
Conoscenze avanzate di Foglio di calcolo 17 â—Š Fase quattro
Figura 4.3.16 - Anteprima del salvataggio sul Web. Se il risultato è quello desiderato possiamo procedere con il salvataggio del file, impostando opportunamente le opzioni all'interno della finestra di dialogo che viene aperta (Figura 4.3.17) selezionando la voce di menu File → Salva come pagina Web. In particolare possiamo selezionare la parte della cartella da salvare e possiamo modificare il titolo della pagina risultante che deve essere digitato nella finestra di dialogo che viene aperta facendo clic sul pulsante Cambia titolo.
Figura 4.3.17 - Salvataggio di una cartella di lavoro come pagina Web.
Fase Quattro ◊ 18 Diventare esperti
4.3.12 Pubblicare fogli di lavoro sul Web
Se il nostro lavoro deve essere pubblicato in Rete, dobbiamo lavorare nella finestra associata al pulsante Pubblica nella finestra del salvataggio (Figura 4.3.17).
Figura 4.3.18 - Pubblicazione di una cartella di lavoro come pagina Web. Tra tutti gli elementi può essere personalizzata l'interattività con la pagina. Selezionando l'opzione Aggiungi interattività con viene data la possibilità di interagire all'interno del foglio, che altrimenti presenta il contenuto in modo statico.
4.3.13 Gestire i commenti delle celle
È importante documentare opportunamente il lavoro svolto in un foglio Excel, specie se complesso, in modo, Per esempio, da tenerne traccia per poterlo riprendere agevolmente nel tempo. La documentazione è utile, inoltre, per spiegare ad altre persone, potenziali utilizzatori, i passaggi chiave del lavoro svolto. Lo strumento che permette di inserire in un foglio dei contenuti del tipo descritto è rappresentato dai commenti. In una cella può essere inserito un commento, digitandolo nella casella che viene attivata selezionando la voce Inserisci commento dal menu di scelta rapida associato alla cella stessa.
Figura 4.3.19 - Inserimento di un commento. Come impostazione predefinita, le celle contenenti un commento vengono contrassegnate da un piccolo triangolino rosso nell'angolo superiore destro, esso viene attivato per leggerne il contenuto portando il puntatore al di sopra della cella. La gestione del commento avviene selezionando una delle tre voci – Modifica commento, Elimina commento e Mostra/Nascondi commenti – presenti nel menu di scelta rapida associata alla cella che lo contiene. La prima voce attiva il cursore all'interno del commento stesso per poterne modificare il testo, la seconda lo elimina e la terza per mantenere fissa o per nascondere la
Conoscenze avanzate di Foglio di calcolo 19 ◊ Fase quattro visualizzazione del contenuto. Se desideriamo che tutti i commenti vengano visualizzati e non nascosti, come impostazione predefinita, dobbiamo selezionare le voci Strumenti > Opzioni > Visualizza. Nella scheda Visualizza è presente la sezione Commenti contenente tre opzioni relative rispettivamente alla visualizzazione di nessun elemento in corrispondenza di un commento, del solo indicatore (come è l'impostazione predefinita) o dell'indicatore e del commento. Nell'ultimo caso i commenti risultano perennemente visibili nel foglio. I commenti, come impostazione predefinita, non vengono stampati insieme al foglio di lavoro che li contiene. Se vogliamo che essi compaiano anche nelle stampe, dobbiamo selezionare File > Imposta pagina; nella scheda Foglio deve essere selezionata l'opzione corrispondente all'impostazione desiderata nel menu a discesa relativo alla casella Commenti.
4.3.14 Trovare e correggere errori nei calcoli
Per individuare gli errori, Excel offre un comodo strumento che viene gestito all'interno della finestra corrispondente alla voce di menu Strumenti → Controllo errori (Figura 4.3.20).
Figura 4.3.20 - Strumenti per il controllo degli errori. Nella parte principale della finestra viene riportato l’errore, la cella in cui esso è contenuto e la causa dell’errore. Facendo clic sul pulsante Informazioni sull’errore viene attivata la guida in linea relativa all’errore commesso. Nella finestra che viene aperta facendo clic sul pulsante Mostra passaggi del calcolo vengono visualizzati tutti i passaggi che sono stati compiuti durante la computazione della formula e il punto in cui è avvenuto l’errore. Facendo clic sul pulsante Ignora errore esso verrà considerato inesistente. Al clic sul pulsante Modifica nella barra della formula, viene attivato il cursore all’interno della barra della formula per apportare le opportune modifiche alla formula che ha provocato l’errore. Nella finestra (Figura 4.3.21) che viene aperta facendo clic sul pulsante Opzioni possono essere personalizzate le opzioni della funzione del rilevamento degli errori mediante la selezione di alcune regole.
Fase Quattro ◊ 20 Diventare esperti
Figura 4.3.21 - Impostazione delle opzioni per la personalizzazione del controllo degli errori. Durante la digitazione, come impostazione predefinita, viene attivata una tip (Figura 4.3.22) in cui viene segnalato l’errore e da cui possono essere richiamate le funzioni già viste nella finestra Controllo errori. Nella cella in cui è stato riscontrato un errore viene riportato un piccolo triangolo verde nell'angolo superiore sinistro.
Figura 4.3.22 - Tip associata a una cella contenente un errore.
4.3.15 Rendere i numeri più facili da leggere
Spesso la lettura del contenuto di una pagina risulta difficile per la notevole quantità di valori inseriti nelle varie celle. Possiamo ricorrere a diversi strumenti per rendere la lettura molto più agevole. Per questo ci può aiutare l’impostazione delle opzioni all’interno della finestra per la definizione del formato delle celle (voce di menu Formato > Celle; rimandiamo alla sezione 1.6.13 per approfondimenti sulla formattazione delle celle, in particolare sulla definizione di un bordo, sul colore di sfondo e sulla formattazione del carattere del valore contenuto. Zoom Le modifiche che apportiamo alle impostazioni del formato delle celle verranno riportate anche nelle eventuali future stampe. Se questo non è il risultato che vogliamo, possiamo aumentare solo a visualizzazione a video della dimensione del contenuto, impostando lo zoom a un numero superiore al 100%. Per attivare lo zoom selezioniamo uno dei valori proposti nel menu a discesa associato al pulsante Zoom nella barra degli strumenti Standard oppure selezioniamo la voce di menu Visualizza →Zoom. Se il mouse che stiamo usando è dotato di rotellina centrale, possiamo lavorare con lo zoom ruotando la rotellina mentre teniamo premuto il tasto Ctrl.
Conoscenze avanzate di Foglio di calcolo 21 ◊ Fase quattro
4.3.16 Cambiare l’aspetto dei dati in base al loro valore
La formattazione condizionale è uno strumento che permette di personalizzare la formattazione di una regione di celle (colore del carattere, colore di sfondo oppure impostazione e colore del bordo) che contengono dati che rispondono a certi criteri (come per esempio maggiore o minore di un certo valore). Questo tipo di formattazione si imposta attivando la voce di menu Formato → Formattazione condizionale, dopo aver selezionato le celle da modificare. Supponiamo di voler formattare il carattere in modo che sia verde se il valore è inferiore a 50, rosso se compreso tra 50 e 80 e blu se superiore a 80 (Figura 4.3.23); abbiamo già impostato le prime due condizioni, vediamo come fare per la terza (la procedura è la medesima).
Figura 4.3.23 - Impostazione della formattazione condizionale. Facendo clic sul pulsante Formato nella sezione relativa alla condizione che stiamo definendo, nel nostro caso la Condizione 3, viene aperta una finestra di dialogo (Figura 4.3.24) in cui può essere personalizzata la formattazione della cella, in particolare del carattere.
Figura 4.3.24 - Impostazione della formattazione delle celle. All'attivazione della funzione, nella finestra è contenuta la casella per l'impostazione solo della prima condizione; per aggiungerne un'altra è necessario fare clic sul pulsante Aggiungi e modificare le opzioni all'interno della sezione relativa alla seconda condizione. Possono essere impostate al massimo tre condizioni. Il criterio di ciascuna condizione deve essere
Fase Quattro ◊ 22 Diventare esperti selezionato dal menu a discesa (Figura 4.3.25) associato alla casella centrale dell'area relativa alla condizione stessa.
Figura 4.3.25 - Selezione del tipo di criterio da applicare alle celle. Una condizione viene eliminata facendo clic sul pulsante Elimina. Al clic sul pulsante OK nella finestra Formattazione condizionale viene applicata la formattazione impostata e le celle che rispondono ai criteri definiti vengono formattate secondo le impostazioni effettuate.
4.3.17 Visualizzare i dati sullo schermo selettivamente
Per ricercare i dati contenuti in un foglio di lavoro possono essere usati strumenti efficienti per poter accelerare la procedura. Oltre alla funzione di ricerca tramite parola chiave, attivabile dalla voce di menu Modifica → Trova, possono essere usati gli strumenti per filtrare i dati contenuti nei fogli. Filtrare un elenco significa significa selezionare e visualizzare solo le righe che corrispondono a criteri specificati, nascondendo le altre righe dell’elenco. Il filtro automatico viene utilizzato per criteri di selezione non complessi. Scegliendo la voce di menu Dati → Filtro → Filtro automatico possiamo filtrare i dati contenuti in una tabella in base a un valore scelto. Dopo aver attivato la funzione, in corrispondenza di ogni cella della prima riga della tabella, viene aggiunto un menu a discesa contenente tutte le voci presenti nella colonna stessa (Figura 4.3.26).
Figura 4.3.26 - Selezione di una voce dal menu a discesa associato a una colonna della tabella selezionata. Scegliendo una voce dalla lista vengono visualizzate solo le righe relative alle informazioni che hanno nella colonna "filtrata" il valore selezionato. La scelta riportata in Figura 4.3.26, ("Toscana" dalla colonna "Regione") fa si che vengano visualizzate solo le informazioni relative alla Toscana (Figura 4.3.27). Il pulsante associato alla colonna in base alla quale abbiamo definito il filtro si colora di blu per aiutare a individuare la colonna in base alla quale è stato applicato il filtro.
Conoscenze avanzate di Foglio di calcolo 23 ◊ Fase quattro
Figura 4.3.27 - Risultato dell'applicazione del filtro. Usando la tecnica descritta è molto semplice reperire le informazioni sui dati che assumono un certo valore scelto in una determinata colonna. Osservando il menu del filtro (Figura 4.3.26) possiamo notare che, oltre a uno dei valori assunti nella colonna, si può richiedere la visualizzazione dei primi dieci valori oppure la personalizzazione della ricerca tra i dati in quel campo. Scegliendo la voce (Personalizza…) dal menu a discesa associato al filtro di un campo, possiamo approfondire la ricerca personalizzandola ulteriormente tramite l'impostazione dei parametri inseriti nella finestra di dialogo ottenuta (Figura 4.3.28).
Figura 4.3.28 - Personalizzazione del criterio per il filtro automatico. Nelle due caselle di testo superiori possiamo impostare un criterio che può essere combinato, tramite un AND o un OR, a un'altra condizione da impostare nelle altre due caselle nella finestra. Nell'esempio di Figura 4.3.28 i criteri impostati definiscono la ricerca di tutte le righe contenenti, nella colonna "Regione", sia il valore "Toscana" che "Umbria". Può essere effettuata una ricerca più approfondita impostando il criterio su più colonne; per esempio, se devono essere filtrati i dati relativi alla Toscana con un determinato numero di circoli didattici è sufficiente scegliere le due voci nel filtro associato a entrambe le colonne, effettuando così due filtri in successione: da tutte le righe vengono prima visualizzate solo quelle che hanno "Toscana" nella colonna "Regione" e dal risultato di questo filtro, vengono selezionate le righe relative alle province in cui c'è quella specifica quantità di circoli didattici. La sequenza dei filtri da applicare non è prefissata: il risultato è identico a prescindere dall’ordine scelto. Eliminazione di un filtro Per visualizzare tutte le righe inserite nella tabella può essere scelta la voce di menu Dati → Filtro → Mostra tutto, oppure la voce (Tutto) dal menu a discesa associato alla colonna in cui è stato impostato il filtro. In questo modo non viene eliminato il filtro; per ottenere l’eliminazione è necessario fare clic di nuovo sulla voce di menu Dati → Filtro → Filtro automatico. Il filtro avanzato, oltre a darci la possibilità di filtrare i dati, come abbiamo visto con il filtro automatico, ci dà la possibilità di mantenere visualizzate la tabella originale e i criteri
Fase Quattro ◊ 24 Diventare esperti in base al quale è stato effettuato il filtro. Quindi, mentre il filtro automatico è importante per la visualizzazione dei dati che rispondono a particolari requisiti, il filtro avanzato può essere utilizzato per creare una nuova tabella contenente tutti i valori che rispettano le condizioni impostate. Supponiamo di voler visualizzare le informazioni relative alle scuole del Sud che hanno un numero di circoli didattici superiore a 100 e più di cinque istituti di istruzione superiore. Impostazione di un filtro avanzato Le condizioni devono essere inserite in una regione di celle isolata dalla tabella completa, quella a cui deve essere applicato il filtro. È sufficiente copiare le intestazioni di quelle colonne su cui deve essere applicato il filtro e incollarle in un’altra area del foglio. Osservando l'esempio di Figura 4.3.29, la prima riga della tabella riporta le colonne da filtrare e nella seconda sono indicati i criteri che devono essere soddisfatti.
Figura 4.3.29 - Impostazione dei criteri. Le impostazioni del filtro devono essere effettuate nella finestra di dialogo (Figura 4.3.30) che viene aperta selezionando la voce di menu Dati → Filtro → Filtro avanzato.
Figura 4.3.30 - Impostazione delle opzioni nel filtro avanzato. In tale finestra devono essere impostati i seguenti parametri: - azione da svolgere sui dati filtrati: se devono essere filtrati “sul posto” (ottenendo un risultato simile a quello dato dall'applicazione del filtro automatico) oppure se devono essere copiati in un'altra posizione del foglio; - Intervallo elenco che rappresenta il riferimento alle celle che delimitano la tabella da filtrare; - Intervallo criteri in cui vengono indicate le celle che contengono i criteri del filtro; - la cella a partire dalla quale devono essere copiati i dati filtrati; essa è attiva solo se abbiamo impostato l'opzione Copia in un'altra posizione. - copia unica dei record per togliere gli eventuali valori ripetuti nella selezione. Il risultato dell'applicazione del filtro viene riportato in una tabella (Figura 4.3.31) a partire dalla cella indicata nella finestra Filtro avanzato.
Conoscenze avanzate di Foglio di calcolo 25 ◊ Fase quattro
Figura 4.3.31 - Risultato dell'applicazione del filtro avanzato. Se i dati vengono filtrati sul posto (quindi non viene impostata la cella a partire dalla quale vengono inserite le celle filtrate), le righe relative ai dati che non soddisfano il criterio impostato nel filtro vengono nascoste. Questo è facilmente riscontrabile dal fatto che non c'è la numerazione completa delle righe.
4.3.18 Eseguire calcoli sui dati filtrati
Se dobbiamo applicare una funzione a dati filtrati sul posto, dobbiamo richiamare la funzione specifica SUBTOTALE che ignora le righe nascoste dal filtro. Sottolineiamo che la funzione di SUBTOTALE deve essere applicata ai dati filtrati sul posto. Se per esempio vogliamo calcolare la somma del numero totale delle scuole nelle due città restituite dal filtro dobbiamo inserire la seguente funzione: =SUBTOTALE(9;I4:I103) come indicato in Figura 4.3.32.
Figura 4.3.32 - Applicazione della somma sui dati filtrati. La sintassi generale della funzione è: =SUBTOTALE(Num_funzione;Rif1;Rif2;...) i cui parametri indicano rispettivamente: Num_funzione il tipo di funzione che deve essere applicata; Rif1; Rif2… la regione a cui viene applicata la funzione. A seconda del valore assunto dal primo parametro (nel nostro esempio 9) al SUBTOTALE viene associata una funzione ben precisa, secondo la seguente tabella: Num_funzione 1 2 3 4 5 6 7 8 9 10 11
Funzione Media Conta.Numeri Conta.Valori Max Min Prodotto Dev.St Dev.St.Pop Somma Var Var.Pop
Fase Quattro ◊ 26 Diventare esperti
4.3.19 Ordinare un elenco di dati
La ricerca o l'elaborazione dei dati contenuti in una tabella risulta spesso, molto più semplice se viene fatta su dati ordinati. L'operazione di ordinamento può essere fatta secondo un massimo di tre criteri da impostare nella finestra (Figura 4.3.33) che viene aperta attivando la voce di menu Dati →Ordina.
Figura 4.3.33 - Impostazione dell'ordinamento della tabella. I dati delle colonne scelte, al massimo tre, possono essere ordinati in modo crescente o decrescente. I tre campi presenti nella finestra di dialogo vengono valutati in modo che il primo ordinamento venga eseguito sulla base della prima condizione: solo nel caso di valori uguali nella colonna corrispondente alla prima condizione, si tiene conto della seconda casella di ordinamento ed eventualmente della terza. Questo criterio di ordinamento viene evidenziato dall’indicazione Ordina per del primo campo inserito a differenza delle altre due caselle indicate con Quindi per. Nell'esempio di Figura 4.3.33 i dati vengono ordinati per Area geografica, tutte le regioni appartenenti a ciascuna area geografica vengono ordinate in modo crescente, all'interno di ciascuna regione viene effettuato l'ordinamento delle province a essa appartenenti. L'ordinamento scelto tiene conto del tipo di dato inserito nella colonna; per esempio, se i dati contenuti sono di tipo testo, viene rispettato l’ordine alfabetico. Nella finestra che viene aperta facendo clic sul pulsante Opzioni possono essere impostati dei parametri per personalizzare il criterio di ordinamento (Figura 4.3.34).
Figura 4.3.34 – Impostazione delle opzioni per l'ordinamento dei dati. Nella casella Prima chiave di ordinamento può essere scelto uno tra gli ordinamenti personalizzati. Se viene selezionata l’opzione Maiuscole/Minuscole viene tenuto conto anche della maiuscola e minuscola dei caratteri componenti il testo. Nella sezione Orientamento
Conoscenze avanzate di Foglio di calcolo 27 ◊ Fase quattro può essere scelto il verso in cui devono essere ordinati i dati: dall'alto in basso, in colonna, o da sinistra verso destra, in riga.
4.3.20 Organizzare i dati in livelli
I dati presenti su più righe possono essere organizzati in livelli usando la funzione di Subtotale. Tale funzione permette di riepilogare automaticamente i dati presenti in un elenco, calcolando i valori dei totali complessivi e parziali su una o più colonne. Per applicare la funzione di subtotale su colonna è necessario che essa abbia un'intestazione e che i dati in essa contenuti siano ordinati. Supponiamo di dover lavorare nella nostra tabella di esempio, dopo aver effettuato l'ordinamento impostato nella sezione 4.3.19, cioè per Area geografica, quindi per Regione e infine per Provincia. A tale tabella devono essere applicati i subtotali in modo che venga calcolato il totale parziale del numero delle scuole per ciascuna area geografica. Dal menu Dati selezioniamo la voce Subtotali, nella finestra Subtotali devono essere impostate le seguenti opzioni: - nella casella A ogni cambiamento in deve essere inserito il campo in base al quale devono essere raggruppati i dati; nell’esempio Area geografica; - nella casella Usa la funzione deve essere impostata la funzione di subtotale da applicare ai dati; nell’esempio Somma; - nella casella Aggiungi subtotale a devono essere selezionate le colonne alle quali applicare la funzione scelta; nel nostro esempio Totale.
Figura 4.3.35 - Impostazione delle opzioni per il subtotale. Al clic sul pulsante OK vengono definiti i subtotali secondo le impostazioni effettuate nella finestra. Il risultato dell'applicazione della funzione al nostro esempio è riportato in Figura 4.3.36.
Fase Quattro ◊ 28 Diventare esperti
Figura 4.3.36 - Risultato dell'applicazione del subtotale. Come possiamo notare nella figura in seguito all’applicazione del subtotale sono stati aggiunti degli elementi sulla parte sinistra della finestra. In particolare, la linea verticale indica le righe che appartengono al raggruppamento definito dalla funzione; le righe contenute nella sezione delimitata da ciascuna riga sono relative alle province appartenenti all’area geografica associata. Con l’uso dei pulsanti + o – possiamo espandere o ridurre la visualizzazione delle informazioni. Possiamo ottenere lo stesso effetto facendo clic sui pulsanti 1 o 2 nella parte superiore della sezione sinistra della finestra, contenente le funzioni del raggruppamento. Aggiunta di subtotali Possono essere aggiunti altri subtotali per creare strutture più complesse; per esempio possiamo aggiungere i conteggi delle province per ogni regione, oppure la somma di ogni tipo di istituto contenuto nelle rispettive colonne. Per fare ciò dobbiamo applicare nuovamente i subtotali impostando le opzioni nella finestra Subtotali, seguendo la stessa procedura vista per la precedente applicazione, facendo attenzione a non togliere il segno di spunta all'opzione Rimuovi subtotali correnti. Per rimuovere i subtotali impostati in una tabella è necessario fare clic sul pulsante Rimuovi tutti presente nella parte inferiore sinistra della finestra Subtotali (Figura 4.3.35).
4.3.21 Raggruppare più elenchi di dati
Nella precedente sezione abbiamo visto che l’applicazione dei subtotali definisce un raggruppamento dei dati; se necessario, esso può essere creato anche indipendentemente dall’applicazione dei subtotali. Possiamo scegliere di raggruppare i dati anche manualmente per creare una struttura personalizzata. Questo può servire per esempio in tutti quei casi in cui le colonne (o le righe) sono molte e alcune delle colonne (o delle righe) contigue contengono informazioni simili. Per cui, quando necessario, possono essere raggruppate per fare spazio alle colonne (righe) che altrimenti sono visualizzabili solo scorrendo la finestra tramite la barra orizzontale (verticale).
Conoscenze avanzate di Foglio di calcolo 29 ◊ Fase quattro Vediamo come procedere facendo conto di dover raggruppare le colonne relative a tutti gli istituti della nostra tabella di esempio. Una volta selezionate le colonne relative a tali informazioni dobbiamo attivare la voce di menu Dati → Raggruppa e struttura → Raggruppa. Il risultato del raggruppamento (Figura 4.3.37) ricorda la struttura restituita dall’applicazione del subtotale (Figura 4.3.36).
Figura 4.3.37 - Risultato dell'applicazione del raggruppamento. Le colonne vengono raggruppate facendo clic sul pulsante contenente il simbolo "-". Quando le colonne sono raggruppate, tale pulsante si trasforma in modo da contenere il simbolo "+" a indicare che le colonne in quell'area possono essere espanse. Eliminare il raggruppamento Per togliere dal gruppo alcune colonne dobbiamo attivare la voce Dati → raggruppa e struttura → Separa dopo aver selezionato le colonne da escludere dal gruppo. Per eliminare tutti i raggruppamenti del foglio è necessario selezionare la voce di menu Dati → Raggruppa e struttura → Cancella struttura dopo aver selezionato l'intero foglio di lavoro. Ricordiamo che la selezione dell'intero foglio di lavoro avviene facendo clic sul pulsante che costituisce l'intersezione tra le intestazioni di riga e quelle di colonna.
4.3.22 Definire un insieme di valori validi per un intervallo di celle
Per controllare i valori inseriti in un intervallo di celle dobbiamo applicare la funzione di convalida alla regione stessa. Questa funzionalità è molto utile in tutti quei casi in cui in una regione di celle possono essere inseriti solo dati che possono assumere specifici valori. I criteri che devono essere soddisfatti dai valori contenuti nelle celle appartenenti alla regione selezionata, devono essere impostati nella finestra di dialogo che viene aperta attivando la voce di menu Dati → Convalida. Nella scheda Impostazioni (Figura 4.3.38) della finestra Convalida dati dobbiamo definire i criteri che devono essere soddisfatti dai dati contenuti nelle celle appartenenti alla regione selezionata.
Figura 4.3.38 - Impostazione dei criteri che devono essere soddisfatti dalle
Fase Quattro ◊ 30 Diventare esperti celle appartenenti alla regione selezionata. L'inserimento dei valori può essere guidata da alcuni commenti che vengono attivati non appena viene portato il cursore all'interno di una delle celle appartenenti alla regione stessa. Il messaggio contenuto nel commento viene impostato nella scheda Messaggio di input della finestra Convalida dati (Figura 4.3.39).
Figura 4.3.39 - Impostazione del massaggio di input per la guida dell'inserimento dei dati. Nella scheda Messaggio di errore (Figura 4.3.40) può essere gestito il caso di errore. Nella casella Stile possiamo definire l'azione che deve essere attivata se viene inserito un dato che non soddisfa le condizioni impostate. Dal menu a discesa associato alla casella possiamo scegliere uno dei seguenti tre valori: Interruzione (non viene permesso l'inserimento del dato se non soddisfa il criterio), Avviso (viene aperta una finestra per avvisare che è stato inserito un dato non corretto, secondo le impostazioni, ma non viene bloccato in alcun modo l'inserimento del dato), Informazione (viene avvertito l'utente della non correttezza del dato e viene data la possibilità di modificare o meno il dato). Nella casella Titolo viene digitato il titolo della finestra di dialogo che viene aperta che conterrà il messaggio digitato nella casella Messaggio di errore.
Conoscenze avanzate di Foglio di calcolo 31 ◊ Fase quattro
Figura 4.3.40 - Impostazione della finestra di dialogo per la gestione del caso di errore. Quello riportato in Figura 4.3.41 è il risultato dell'impostazione effettuata nelle tre schede della finestra Convalida dati riportate precedentemente.
Figura 4.3.41 - Risultato dell'applicazione della convalida impostati nei passaggi precedenti.
4.3.23 Definire e modificare gruppi di dati alternativi
Supponiamo di dover prevedere la variazione di alcuni dati a seconda del valore assunto da determinate celle di una tabella. Per poter risolvere agilmente questa situazione possiamo ricorrere all'aiuto degli Scenari. La consultazione delle previsioni viene notevolmente semplificata dai riepiloghi che possono essere creati sulla base dei valori definiti. Supponiamo di dover studiare la variazione della quantità delle scuole in una regione italiana, al variare del numero di determinati tipi di scuole in ciascuna provincia a essa appartenente. Prendiamo il caso delle scuole umbre e applichiamo gli scenari per vedere come varia il numero complessivo delle scuole della regione, variando la quantità di alcuni tipi di scuole. Supponiamo che nella regione Umbria siano stati proposti due progetti: uno ("Progetto1") per aumentare il numero dei circoli didattici diminuendo gli istituti di istruzione superiore e l'altro ("Progetto2") per aumentare il numero di Istituti comprensivi e diminuire quello degli Istituti principali di scuola secondaria di I grado. In Figura 4.3.42 è riportata la tabella contenente i dati relativi alle scuole umbre.
Fase Quattro â—Š 32 Diventare esperti
Figura 4.3.42 - Dati di origine dell'applicazione degli scenari. Gli scenari vengono attivati selezionando la voce di menu Strumenti → Scenari e impostati nella finestra di dialogo (Figura 4.3.43) che viene aperta.
Figura 4.3.43 - Finestra di dialogo per l'impostazione degli scenari. Facendo clic sul pulsante Aggiungi viene aperta la finestra Modifica scenario per l'impostazione di uno degli scenari (Figura 4.3.44). Nella finestra devono esserne definiti il nome, la regione delle celle variabili e, se necessario, un commento come promemoria dello scenario. Da notare che nella casella Celle variabili sono contenuti i riferimenti alle celle il cui valore determina la variazione del dato oggetto di studio.
Figura 4.3.44 - Impostazione delle opzioni relative allo scenario. Al clic sul pulsante OK viene aperta un'ulteriore finestra (Valori scenario riportata in Figura 4.3.45) in cui devono essere definiti i "nuovi valori" per le celle impostate come variabili.
Conoscenze avanzate di Foglio di calcolo 33 ◊ Fase quattro
Figura 4.3.45 - Impostazione dei valori delle celle variabili. Facendo clic su OK viene chiusa la finestra Valori scenario, confermate le impostazioni in essa definite e viene aggiunto lo scenario all'interno della finestra Gestione scenari a indicarne l'avvenuta memorizzazione. Facendo clic sul pulsante Mostra vengono riportati nella tabella di origine i dati variati nello scenario definito. Per modificare i valori definiti in uno scenario dobbiamo digitarli nella finestra che viene aperta facendo clic sul pulsante Modifica dopo aver attivato lo scenario da cambiare. Nella sezione 4.3.24 vedremo come aggiungere altri scenari e nella sezione 4.3.25 come può essere definito un riepilogo delle variazioni ipotizzate negli scenari stessi.
4.3.24 Definire più gruppi di dati alternativi
Se vogliamo aggiungere altri scenari dobbiamo procedere nello stesso modo visto nella sezione precedente per la definizione del primo scenario. L'attivazione della creazione avviene facendo clic sul pulsante Aggiungi nella finestra Gestione scenari. Definito il nome dello scenario (nel nostro esempio Progetto2) e le celle variabili (nell'esempio le celle $C$2:$C$3; $D$2:$D$3) facendo clic sul pulsante OK viene aperta la finestra di dialogo in cui devono essere definiti i nuovi valori per le celle variabili. Possiamo procedere nello stesso modo per definire altri scenari, ognuno relativo a una realtà da rappresentare.
4.3.25 Riepilogare più gruppi di dati
Il riepilogo degli scenari è un comodo strumento per mantenere sotto controllo la situazione definita all'interno degli scenari impostati nei passaggi precedenti. Per l'impostazione del riepilogo è necessario definire le celle oggetto di studio. Nel nostro esempio abbiamo detto che vogliamo vedere la variazione della quantità totale delle scuole in Umbria in seguito alle variazioni impostate in entrambi i progetti definiti (Progetto1 e Progetto 2). Vediamo come procedere per creare il riepilogo sulla base dei due scenari definiti nelle sezioni precedenti. Richiamiamo la funzione per la definizione degli scenari selezionando la voce di menu Strumenti → Scenari. Nella finestra di dialogo Gestione scenari dobbiamo fare clic sul pulsante Riepilogo. Nella finestra di dialogo Riepilogo scenari (Figura 4.3.46) che viene aperta dobbiamo impostare le coordinate alle celle che contengono la cella o le celle oggetto di studio della variazione. Nel nostro caso è la G4, quella cioè in cui è contenuto il valore del totale.
Fase Quattro â—Š 34 Diventare esperti
Figura 4.3.46- Impostazione della cella contenente il valore oggetto di studio. Facendo clic sul pulsante OK viene creato un foglio di lavoro indipendente (Figura 4.3.47), formattato automaticamente, contenente i riferimenti alle celle impostate come variabili con i valori attuali, quelli impostati negli scenari e la variazione della cella impostata come oggetto di studio degli scenari definiti.
Figura 4.3.47 - Risultato del riepilogo degli scenari.
4.3.26 Cambiare i dati per ottenere il risultato desiderato
La funzione Ricerca obiettivo permette di risolvere il seguente problema: dato il risultato di una formula, cercare il valore da attribuire alla variabile. La funzione Ricerca obiettivo viene applicata lavorando nella finestra che viene aperta selezionando l'omonima voce dal menu Strumenti. Vediamo l'applicazione della funzione a un esempio. Supponiamo di conoscere il numero medio dei vari tipi di scuole nelle quattro aree territoriali italiane; se vogliamo conoscere il numero delle scuole di una delle aree, Per esempio delle Isole per raggiungere un certo valore medio delle suole, dobbiamo applicare la funzione Ricerca obiettivo. La situazione iniziale dell'esempio è visualizzata in Figura 4.3.38
Conoscenze avanzate di Foglio di calcolo 35 ◊ Fase quattro
Figura 4.3.48 - Dati di esempio su cui deve essere applicata la funzione Ricerca obiettivo. Supponiamo di voler sapere di quanto deve essere aumentato il numero delle scuole nelle Isole affinché in Italia ci siano in media 2700 scuole. Nella finestra (Figura 4.3.49) selezionando la voce Ricerca obiettivo dal menu Strumenti devono essere impostati: - la cella contenente il valore variabile (nel nostro caso la cella B6 contenente la media del numero delle scuole); - il valore che dovrà essere assunto dalla cella (nel nostro caso 2700); - il riferimento alla cella che dovrà essere variata affinché il valore finale sia quello impostato nella casella Al valore (nel nostro caso la cella B3 contenente il numero delle scuole presenti nelle Isole).
Figura 4.3.49 - Impostazione delle opzioni della Ricerca obiettivo. Al clic sul pulsante OK viene aperta una finestra dell'applicazione della funzione (Figura 4.3.50).
in cui viene riportato l'esito
Figura 4.3.50 - Esito dell'applicazione della funzione Ricerca obiettivo. Facendo clic sul pulsante OK della finestra Stato ricerca obiettivo viene riportato il valore individuato in seguito all'applicazione della funzione come definitivo alla cella impostata come variabile durante l'applicazione della ricerca dell'obiettivo. In seguito all'applicazione della ricerca dell'obiettivo il numero delle scuole nelle Isole viene cambiato da 1615 a 1623 per ottenere una media di 2700 scuole nel territorio nazionale italiano.