Dispensa microsoft excel, Appunti di Informatica

Scarica Dispensa microsoft excel e più Appunti in PDF di Informatica solo su Docsity!

Ing. Bortolini Nicola

Appunti di Microsoft Excel

Indice dei contenuti

1. ORGANIZZARE I FILE DI EXCEL

Quando si lavora con programmi di tipo "foglio di calcolo", (Microsoft Excel ed Openoffice Calc sono le due applicazioni decisamente più popolari), si ha a disposizione uno strumento molto efficace per analizzare strutture dati, implementare funzioni di calcolo, estrarre informazioni di riepilogo e generare reportistica di ogni tipo. Spesso l'utilizzo di queste applicazioni e' limitato alla conoscenza degli strumenti di base: si realizza una veloce tabella contenente i dati da gestire, se ne gestisce il formato in modo che sia presentata nella veste (anche grafica) piu' efficace, e si inseriscono le formule ed i calcoli necessari. Utilizzando applicazioni di questo tipo ci sono alcuni aspetti che in genere non vengono presi in considerazione, e che permetterebbero di ottimizzare i documenti e la propria produttività. Non esiste una ricetta "perfetta" per realizzare e gestire i propri fogli di calcolo, e' pero' possibile pensare ad un insieme di "linee guida", di best-practices che permettono nel tempo di realizzare documenti ben strutturati, riutilizzabili, ma soprattutto efficaci e rispondenti alle proprie esigenze e necessità di calcolo.

CENTRALIZZARE LA POSIZIONE DEI FILE

Spesso i file vengono creati "dove capita"; il desktop o la cartella "documenti" del proprio profilo utente sono parcheggi temporanei e veloci per qualunque tipo di documento. Non si tiene conto pero' del fatto che queste cartelle spesso non sono coperte da processi di backup, e quindi sono esposte innanzitutto al grave rischio di non poter essere recuperate in caso di danni al disco, di cancellazioni o modifiche (anche accidentali) a file e cartelle. Se si lavora in un contesto aziendale e' bene utilizzare le cartelle di rete specifiche, predisposte dal proprio personale IT. Una cartella di rete ha il vantaggio di essere accessibile a piu' utenti, di essere raggiungibile indipendentemente dal pc utilizzato (autorizzazioni di accesso permettendo) e di essere periodicamente ispezionata dai processi di backup. Le cartelle di rete condivise sono quindi la soluzione migliore per il posizionamento (fin dalla loro creazione) dei fogli di calcolo. Se si lavora in un contesto domestico o comunque personale, non sempre si ha a disposizione una rete di pc con servizi di condivisione o di backup; si possono comunque implementare le stesse funzionalità semplicemente condividendo la cartella del pc in cui si decide di ospitare i file e le cartelle di lavoro, e ricordandosi di fare periodicamente una copia delle stesse risorse (anche una usb pen puo' servire efficacemente da dispositivo di backup, in un contesto non aziendale). Disponendo quindi di una adeguata struttura di cartelle, conviene posizionarvi i documenti di lavoro fin dalla creazione, per evitare che collegamenti dati a documenti esterni, se presenti, non risultino piu' funzionanti.

UTILIZZARE MODELLI DI DOCUMENTO

Una pratica comune, quando si deve creare un documento "simile" ad uno precedentemente realizzato, ma differente per parte del contenuto, consiste nell'utilizzare la voce di menu "salva con nome", presente nella maggior parte delle applicazioni; in questo modo si crea una copia del documento da cui si e' partiti, copia che successivamente viene "specializzata" variando il contenuto in modo da adattare il documento alla nuova esigenza. Si pensi alla realizzazione di preventivi, di offerte, di moduli di acquisto materiale, o di tabelle di anagrafica di vario genere. I modelli servono appunto a coprire necessità di questo tipo: in sostanza un modello di documento (ed Excel non fa eccezione) permette di creare un "template", ossia uno schema di documento di lavoro, a cui devono essere aggiunte le informazioni specifiche, quelle che in sostanza differenzierebbero un documento da tutti gli altri su questo basati. Il modello di documento previene inoltre, per la sua stessa natura, il problema della sovrascrittura accidentale di un documento già esistente, possibile se si lavora con la strategia del "salva con nome". Lavorando infatti con file basati su modelli, l'unica opzione di salvataggio lecita e' proprio "salva con nome", e quindi l'autore del documento sarà obbligatoriamente invitato ad assegnare un nome al file ed un percorso; il modello non può venire modificato, a meno che non si stia espressamente lavorando al suo contenuto (e' sufficiente aprire il file come modello, e non come documento basato su modello). Organizzare i propri documenti creando modelli permette, in contesto aziendale, di standardizzare la produzione dei contenuti : l'azienda puo' realizzare documenti con logo, contatti e riferimenti e farli utilizzare ai propri dipendenti per semplificare le attività di creazione delle strutture dati, e per uniformare contenuti e logiche di presentazione degli stessi.

SEPARARE I DATI DALLA LOGICA DI CALCOLO

E' pratica frequente, per chi lavora con fogli di calcolo, quella di avere all'interno di uno stesso foglio di lavoro sia la struttura dati, sia la sezione in cui si effettuano calcoli e si estraggono informazioni di riepilogo. E' in realtà piu' comodo ed efficace mantenere separati (quando possibile) i due aspetti, ossia avere le strutture dati all'interno di uno (o piu' fogli), e i calcoli o le elaborazioni di riepilogo in altri; in questo modo le tabelle dati sono libere di permettere l'inserimento di nuove righe, senza che sia necessario riposizionare le formule ed i calcoli, rischiando di avere intervalli di riferimento nelle formule da verificare o aggiornare. Un ulteriore vantaggio di tale pratica e' quello che permette, nei fogli in cui si inseriscono i calcoli, di specificare riferimenti di celle che coinvolgano intere colonne (o righe) : in questo modo, aggiungendo nuove righe alle tabelle, non si avrà il problema di verificare ed aggiornare gli intervalli, le nuove celle saranno incluse automaticamente in quanto appartenenti ad una colonna interamente coinvolta nelle formule.

VINCOLARE L'INSERIMENTO DEI CONTENUTI

I fogli di calcolo normalmente permettono l'inserimento di qualsiasi tipo di contenuto nelle celle. Ci sono pero' alcuni strumenti che permettono di indirizzare l'utilizzatore dei documenti, stabilendo dei vincoli o dei limiti a contenuti e funzionalità. E' presente in Excel lo strumento "convalida dati", che permette di definire quale sia il contenuto lecito per celle o gruppi di celle. In questo modo si possono prevenire inserimenti di valori non compatibili con le logiche di calcolo definite (per evitare ad esempio che si inseriscano dei testi in celle il cui contenuto e' coinvolto in operazioni di somma, o dei numeri decimali all'interno di celle che devono ospitare solo valori interi). Uno strumento del genere, intervendo a monte del processo di inserimento dati, garantisce una maggiore robustezza dei calcoli e quindi una migliore affidabilità dell'intero documento di lavoro. Un altro strumento interessante in questo contesto e' quello che permette di gestire la protezione di celle o funzionalità, e limitarne l'accesso ai soli utenti autorizzati (normalmente a conoscenza della password per rimuovere la protezione). In questo modo si possono prevenire alterazioni, a volte accidentali, di porzioni del documento, come ad esempio celle o gruppi di celle che contengano formule di una certa importanza e che in nessun modo possono correre il rischio di essere modificate. Una terza funzionalità che vale la pena citare in questa sede riguarda la possibilità di definire "tabelle dati"; una tabella dati in Excel non e' un semplice insieme di celle valorizzate, organizzate in righe e colonne, bensì un'area, con limiti ben definiti, che contiene al suo interno la struttura dati su cui si lavora. La tabella cosi' creata permette di lasciar gestire ad Excel l'inserimento automatico delle nuove righe, permette di attivare automaticamente gli indicatori per il filtro sui dati, e consente di aggiungere in coda alla struttura una riga di riepilogo, la cui posizione si adatta automaticamente a nuove righe eventualmente inserite.

IMPORTARE E COLLEGARE DATI ESTERNI

uno degli aspetti piu' interessanti di Microsoft Excel riguarda la possibilità di usare l'applicazione come strumento di calcolo su strutture dati non necessariamente definite in Excel. Utilizzando infatti le cosiddette "origini dati", dei canali di collegamento tra il sistema operativo e strutture dati di ogni tipo, e' possibile agganciare (collegare) o riversare (importare) le informazioni su cui poi effettuare i calcoli. Operando in questo modo Excel mette a disposizione gli strumenti piu' avanzati (tabelle pivot, funzioni della libreria, subtotali e strumenti di analisi sui dati) per operare su ogni possibile tabella dati, sia essa definita in un database (Oracle, SQLServer, MySQL, IBM AS400), sia essa l'esportazione in formato testuale di una qualsiasi piattaforma gestionale, sia essa una tabella dati contenuta in una pagina web. Si ritorna in questo caso alla separazione dei contenuti rispetto alla logica di calcolo, in quanto all'interno di uno o piu' fogli si avranno le tabelle dati collegate o importate, e in altri fogli le formule, le informazioni di riepilogo, gli strumenti di analisi. Avere un documento di Excel contenente dati collegati a sorgenti esterne permette inoltre di realizzare e mantenere documenti il cui contenuto si aggiorni automaticamente se i dati provenienti dall'esterno vengono modificati, e quindi aumenta la produttività utente in quanto non costringe l'utilizzatore a creare nuovi documenti per analizzare strutture dati che nel corso del tempo siano soggette ad aggiornamento dei contenuti.

La funzione “casuale”, restituisce pero’ un risultato differente ad ogni azione effettuata all’interno della cartella di lavoro, e quindi conviene sia utilizzata solo per generare in una ulteriore colonna a lato della tabella un elenco di valori casuali desiderati, da ricopiare poi nella colonna utile (importi e quantità) utilizzando lo strumento “incolla speciale” :

selezionare i valori da copiare
dx
copia
posizionamento su celle di destinazione
dx
incolla speciale

(solo valori)

completato il riempimento delle colonne dati, si possono eliminare le colonne usate per la generazione dei valori casuali (eventualmente si possono nascondere). E’ anche lecito incollare “su sé stessa” una colonna, selezionando l’opzione “incolla speciale > valori”, senza ricorrere alla creazione di colonne aggiuntive.

CREAZIONE DEL MODELLO DI DOCUMENTO “DATI.XLT”

Una volta riempita la tabella, e’ possibile :

• rinominare il foglio in “dati”
• eliminare i fogli non in uso (se presenti)
• salvare come modello di documento “dati.xlt”, nella cartella desiderata

Riutilizzando il modello per gli esempi successivi, si avrà la possibilità di partire da una base dati gia’ pronta all’uso.

3. FORMATTAZIONE CONDIZIONALE

Lo strumento permette di applicare un formato desiderato ad una selezione di celle al verificarsi di una condizione logica. E’ possibile impostare, per una selezione di celle, più formati da applicare in corrispondenza di differenti condizioni logiche. Lo strumento è incorporato nel menu “formato” :

selezione celle
menu formato
formattazione condizionale

Figura 1- Applicazione di formati differenti a seconda del valore numerico della cella

Una volta impostate le giuste condizioni, e’ possibile utilizzare lo strumento “copia formato” per estendere la formattazione condizionale alle celle desiderate (anche a intere righe o colonne) Nota Non e’ possibile definire più di tre formattazioni condizionali nella versione 2003 di Microsoft Excel

ESERCIZIO

Realizzare la seguente tabella ed applicare la formattazione condizionale come indicato :

UTILIZZO DI FORMULE

4. DEFINIZIONE DI FORMATI PERSONALIZZATI

E’ possibile definire formati personalizzati per visualizzare secondo esigenze opportune i valori contenuti nelle celle. Excel utilizza alcune regole e caratteri “jolly” : 0 cifra numerica da mostrare

cifra numerica opzionale

g giorno di una data in cifre gg giorno di una data con due cifre numeriche ggg giorno in formato descrizione breve (lun, mar, mer, …) gggg giorno in formato descrizione estesa (lunedì, martedì, mercoledì, …) m mese di una data in cifre mm mese di una data con due cifre numeriche mmm mese in formato descrizione breve (gen, feb, mar, …) mmmm mese in formato descrizione estesa (gennaio, febbraio, marzo, …) a anno, con due cifre aaaa anno, con quattro cifre h ora, una sola cifra hh ora, due cifre m (in contesto di orario) minuti, in una sola cifra mm (in contesto di orario) minuti, due cifre s secondi, una sola cifra ss secondi, due cifre

• separa due contenuti, allineando il primo a sinistra ed il secondo a destra E’ possibile incorporare porzioni di testo utilizzando i doppi apici, es :

“kg” 000

E’ possibile, in caso di valori numerici, definire formati differenti per visualizzare valori positivi e negativi

[Verde]000+;[Rosso]000-

In generale, per i valori numerici, e’ possibile utilizzare lo strumento di formattazione personalizzata per esprimere il contenuto della cella qualora contenga:

un numero positivo; un numero negativo; il numero 0; un testo [Verde] #.##0+;[Rosso] #.##0-;"niente"; "testo : "@

ESERCIZIO

Utilizzando formati personalizzati, verificare la visualizzazione dei dati come da tabella:

Testo in cella Testo visualizzato

1/2/3 1 febbraio, 2003 15:30:45 Sono le ore 15, 30 minuti e 45 secondi

4/5/6 giovedì, 4 maggio 06

123 123,00 €/kg 4 qtà 0004

100 100 pezzi (numero allineato a sinistra, testo allineato a destra)

5. FORMATTAZIONE AUTOMATICA

Microsoft Excel offre lo strumento “formattazione automatica” che permette di impostare rapidamente uno stile grafico ad una struttura dati tabellare. Lo strumento e’ accessibile all’interno del menu “formato” :

formato > formattazione automatica

Conviene sempre ricordare che Excel identifica automaticamente la tabella dati a cui applicare il formato se prima di attivare questo strumento si e’ selezionata una qualsiasi cella della tabella.

Figura 3 - Selezione di un formato da applicare

L’immagine acclusa mostra la finestra di dialogo con le opzioni avanzate attive : e’ possibile quindi decidere anche in maniera selettiva quali proprietà applicare del formato selezionato.

Figura 4 - Prima Figura 5 - Dopo

Figura 9 - Schemi specifici per la gestione di più campi di riepilogo

città Dati Bologna Milano …

agente Totale vendite

Media quantità Totale vendite

Media quantità … Anna Neri 61250 47 119750 9 … Antonio Verdi 78750 60 57500 26 … Mario Rossi 87750 8 133000 66 … Simona Bianchi 118250 23 123000 28 … Totale complessivo 346000 138 433250 129 … Figura 10 - Formattazione applicata

6. ELENCHI E SERIE DATI

Un elenco in Excel e’ costituito da una successione di valori testuali. E’ possibile utilizzare alcuni semplici elenchi predefiniti, e comporne di nuovi.

UTILIZZO DI ELENCHI PREDEFINITI

E’ possibile ripetere gli elementi che appartengono ad un elenco semplicemente inserendo in una cella uno qualsiasi dei valori, e trascinando nella direzione desiderata il riquadro che appare in basso a destra della cella o del gruppo di celle selezionate :

Figura 11- Trascinamento degli elenchi predefiniti

Si accede al pannello di gestione degli elenchi dal menu :

strumenti
opzioni
scheda “elenchi”

CREAZIONE DI NUOVI ELENCHI

All’interno del pannello di gestione, e’ possibile creare un nuovo elenco inserendo manualmente gli elementi (separandoli con la virgola), o facendo riferimento a celle presenti in un foglio (importazione degli elementi) :

Figura 12- Per confermare il nuovo elenco, selezionare il pulsante "aggiungi"

Figura 13- Utilizzo del nuovo elenco Una volta creato, un nuovo elenco sarà utilizzabile trascinando uno qualsiasi degli elementi che ne fanno parte. E’ possibile creare “al volo” un nuovo elenco, selezionando (tutte) le celle che contengono la sequenza di elementi da ripetere, e trascinando il quadretto nella direzione desiderata :

Figura 16- Serie con trascinamento manuale, l’ultima e’ una progressione decrescente

E’ possibile realizzare successioni decrescenti impostando un passo negativo, come si può vedere dall’immagine precedente

7. NOMI LOGICI ED ETICHETTE

E’ possibile assegnare ad una cella o ad un gruppo di celle un nome logico, che può poi essere utilizzato e coinvolto nelle formule all’interno del foglio di lavoro e della cartella di lavoro Microsoft Excel. Per definire un nome logico :

1. attivare il pannello di gestione dei nomi logici :

menu inserisci
nome
definisci

1. inserire il nome logico da assegnare (se non sia già stato identificato dal programma in base alla eventuale selezione)
2. selezionare la cella o le celle a cui assegnare il nome logico (anche una riga o una colonna), utilizzando l’apposita casella di testo nella parte bassa del pannello
3. fare click sul pulsante “aggiungi” per confermare l’assegnazione del nome logico Considerando l’immagine di esempio riportata, sara’ in questo caso disponibile il nome logico “importi”, che fa riferimento ad una intera colonna di celle (la colonna E del fglio “dati”). Sara’ quindi possibile utilizzare il nome logico “importi” all’interno di formule di calcolo, come ad esempio :

=somma(importi) =media(importi) =max(importi) =conta.numeri(importi)

In questo caso le formule riportate permettono di calcolare la somma, il valore medio, il valore massimo ed il conteggio delle celle valorizzate con importi all’interno della colonna a cui il nome logico “importi” fa riferimento Nella versione 2007 : per assegnare, modificare, gestire nomi logici ed etichette e' sufficiente aprire (nella barra multifunzione) la scheda “formule”, sezione “nomi definiti”; sarà possibile ritrovare tutte le funzionalità utilizzate nella versione 2003.

ESERCIZIO – ESTRATTO CONTO

Realizzare all’interno di un foglio dati, una tabella con i seguenti campi di intestazione (e le formattazioni corrispondenti) :

data (data), descrizione (testo generico), entrata (contabilità), uscita (contabilità)

Valorizzare almeno 15 righe di dati; per ogni riga valorizzare una sola delle due celle di importo (facendo cosi’ riferimento ad una voce di entrata o di uscita) Assegnare alle due colonne di importo opportuni nomi logici (ad esempio “entrate” e “uscite”) Calcolare, all’interno di un nuovo foglio (della stessa cartella di lavoro) :

1. la somma delle entrate
2. la somma delle uscite
3. il saldo (entrate –uscite)
4. il valore medio delle entrate
5. il valore medio delle uscite
6. il numero di uscite superiori alla cifra di 1000 € (utilizzare la funzione “conta.se”)

DEFINIZIONE AUTOMATICA DI ETICHETTE

E’ possibile far creare ad Excel tutte le etichette o i nomi logici necessari partendo da una tabella esistente. Si prenda come esempio la struttura definita nel modello “dati.xlt”, si selezionino tutte le colonne della tabella : dal menu :

inserisci > nome >crea

8. CONVALIDA DEI DATI

Microsoft Excel offre la possibilità di verificare il contenuto delle celle di un foglio, permettendo così di realizzare documenti in grado di validare la consistenza dei dati in fase di inserimento. Dopo aver selezionato le celle per le quali e’ necessario inserire il meccanismo di convalida, dal menu :

dati > convalida …

si ha la seguente finestra di dialogo :

E’ possibile specificare, nel pannello “impostazioni”, il tipo di dato che ci si aspetta verrà inserito dall’utente nelle celle selezionate. Se si richiede la convalida di numeri (interi o decimali) o di informazioni di tipo “data/ora”, e’ possibile specificare l’intervallo dei valori di controllo, o limiti superiori/inferiori :

Se si seleziona un criterio di convalida di tipo “elenco”, sarà possibile specificare l’esatta sequenza dei valori distinti leciti per l’inserimento. Tale sequenza può essere scritta nella maschera di composizione del criterio di convalida oppure può essere presa da una successione di celle all’interno del foglio stesso :

Figura 17 - Il punto e virgola separa gli elementi dell'elenco

Figura 18 - Nelle celle selezionate apparirà una casella combinata per la scelta del valore

Figura 19 - Elenco costruito con celle del foglio corrente

GESTIONE DEI MESSAGGI DI INPUT E DI ERRORE

Lo strumento per la verifica e la convalida dei dati da inserire nelle celle permette di definire dei messaggi da mostrare all’utente in fase di inserimento e di impostare dei box di segnalazione che verranno mostrati qualora i dati inseriti non siano compatibili con quelli consentiti. Per i box di errore e’ possibile selezionare tra tre tipologie differenti di avviso (message box) : interruzione, avviso e informazione; di seguito un esempio.

Figura 20 - Preparazione della segnalazione

Figura 21 - Il messaggio appare sotto forma di "tooltip"

Figura 22 - Preparazione del messaggio di errore

Figura 23 - Messaggio bloccante

CONVALIDARE I VALORI CON UN ELENCO SEMPRE AGGIORNATO

E’ possibile definire l’elenco dei valori utilizzati per la convalida all’interno di un foglio di Excel; tale elenco può essere modificato anche a posteriori e puo’ anche essere ordinato in senso crescente o decrescente, e costituirà una lista sempre aggiornata di valori per il controllo dei dati inseriti nelle celle. E’ necessario creare, all’interno di un foglio, la sequenza di valori ed assegnare ad essa un nome logico o etichetta.