Corso di Laurea in Ingegneria per l’Ambiente e il Territorio

Informatica per l’Ambiente e il Territorio

Docente: Giandomenico SpezzanoTutor: Alfredo Cuzzocrea

A.A. 2003-2004

Introduzione a Excel

Il foglio di lavoro (worksheet o spreadsheet) è stato ideato come uno strumento di contabilità computerizzata, in grado di svolgere calcoli matematici e statistici e di ordinare liste di dati.

Oggi è diventato un potente programma matematico utilizzato da ingegneri e scienziati.

Oltre ai calcoli matematici i fogli di lavoro permettono di generare agevolmente diagrammi di qualità, analizzare dati, svolgere operazioni sui database, rappresentare graficamente i dati.

Introduzione a Excel

Un foglio di lavoro è formato da celle.

Una cella è l’intersezione di una riga con una colonna.

Il simbolo C2 rappresenta l’indirizzo di una cella.

Un foglio è costituito da 256 colonne e 65536 righe.

Ogni cartella può contenere fino a 256 fogli di lavoro.

In una cella del foglio di lavoro è possibile inserire tre tipi di dati: numeri, formule e testi.

I numeri rappresentano il tipo di input fondamentale di un foglio di lavoro.

Introduzione ad Excel

E’ possibile impostare il numero di cifre da visualizzare nel formato decimale (per esempio 3,14159), nella notazione scientifica (per esempio 6,02E23) o 6.02*10^23.

Le formule consentono di effettuare calcoli utilizzando numeri e indirizzi di celle. Quando definita una formula, sulla cella appare il risultato della formula.

Per inserire una formula in Excel è necessario iniziare la formula con il segno di uguale (esempio =2+3, =A3+B7/10).

I testi sono utilizzati per inserire commenti nel foglio di lavoro.

Vediamo il seguente esempio.

Gestione dei dati

Non sempre servono tutti i dati presenti in un foglio di lavoro. Spesso è necessario operare solo su un sottoinsieme dei dati per preparare rapporti riepilogativi o esaminare dettagli specifici.

In questa parte ci occuperemo di

Elenchi Filtro di elenchi Ordinamento dei dati

Elenchi Un elenco è una raccolta di informazioni correlate tra loro e

ordinate secondo un preciso schema logico.

Di solito gli elenchi sono composti da una riga intestazione, che contiene i titoli o le descrizione del contenuto, e da diverse righe di dati che possono essere di diverso tipo: numeri, stringhe di testo, date ed altro.

Come vedremo un elenco è simile ad una tabella di database: le colonne rappresentano i campi, le righe che contengono i dati rappresentano i record dell’archivio elettronico.

Excel dispone di diverse funzionalità progettate per lavorare con gli elenchi.

Inserimento dati in un elenco

I dati in un elenco possono essere inseriti utilizzando tasti di scelta rapida: completamento automatico, seleziona da elenco

Il completamento automatico consente di cercare nella colonna le voci corrispondenti ai caratteri inseriti dall’utente.

Seleziona da elenco consente di selezionare una voce di una cella in base al fatto che essa già esiste da qualche altra parte nella colonna. Clic pulsante destro e Seleziona da elenco.

Utilizzo del modulo dati per inserire i record. Funziona come una maschera di un database. Click su una cella, aprire menu Dati e selezionare il comando Modulo.

Filtro di elenchi

I filtri consentono di visualizzare nella finestra di lavoro solo i dati che soddisfano determinati criteri.

I filtri nascondono (non eliminano) le righe che non soddisfano i criteri di ricerca indicati dall’utente

Filtro automatico. Il comando Dati|Filtro|Filtro automatico selezionato dopo aver evidenziato una cella dell’elenco, permette di filtrare automaticamente le informazioni inserite nella struttura.

Il foglio di lavoro mostra solo le righe che soddisfano il criterio impostato,il pulsante che mostra l’icona a forma di freccia diventa azzurro, la barra di stato visualizza il numero di record trovati, insieme con il numero di record totali

Filtro di elenchi

Le formule che operano sulle colonne non ripetono i calcoli per le righe filtrate. Se una formula genera la somma di 50 righe e l’operazione di filtro visualizza 10 righe, la formula continua a visualizzare il valore delle 50.

Selezionando come filtro il valore Tutto si ritorna a visualizzare tutto l’elenco.

primi 10: filtra automaticamente in base alle prime 10 voci personalizza: da la possibilità di filtrare in base a più

elementivuote: filtra in base alle celle vuotenon vuote: esegue l’operazione opposta

E’ anche possibile definire filtri a più colonne.

Filtri personalizzati

Per definire criteri personalizzati è necessario selezionare il comando Personalizza

E’ possibile stabilire due insiemi di criteri e farli eseguire entrambi. Esempio cercare tutti i dipendenti di biologia e informatica.

Si possono usare i criteri jolly * e ? Insieme alle stringhe di testo (In* tutte le stringhe che iniziano con In mente In? Tutte le parole contenenti le lettere indicate).

Per poter applicare un filtro avanzato è necessario definire un criterio di partenza

Filtri avanzati

Impostare un criterio di partenzaDeve avere almeno due righeNella prima riga devono apparire i nomi dei campiLe righe successive definiscono il criterio

Dopo aver stabilito l’intervallo di criteri, selezionare una cella che si trova nell’elenco aprire il menu Dati|Filtro e scegliere Filtro avanzato

Nel gruppo di opzioni si può scegliereFiltra l’elenco sul posto. Si attiva il filtro sull’elenco

esistenteCopia in un’altra posizione. Viene visualizzato l’elenco in

un’altra posizione.

Ordinamento dei dati

E’ possibile ordinare i dati di un elenco ad esempio in ordine alfabetico oppure sistemando le colonne in modo che i valori appaiano in ordine crescente.

salvare il file prima di effettuare l’ordinamento

Regole Excel per l’ordine crescente

Numeri dal più piccolo negativo al più grande positivoDate e ore Valori reali (è ignorata la formattazione alfabetica)Testo 0-9, spazio!”#$%&()*+,./:,<=<?@[\]^{!}A-ZValori logici Falso prima di veroCelle vuote sono le ultime in qualsiasi tipo di ordinamento

Ordinamento dei dati

Per l’ordinamento si utilizzano i pulsanti sulla barra degli strumenti standard.

Si seleziona uno dei valori della colonna. Se si scelgono i valori intestazione il programma ordina i valori della colonna ma i record possono risultare mischiati.

Se il record contiene una formula che fa riferimento a una cella che si trova all’esterno del record, dopo l’ordinamento le formule possono risultare non corrette.

Ordinamento dei dati

Ordinamento con più campi. Scegliere il comando ordina dal menu Dati, selezionare il campo da utilizzare per l’ordinamento. Poi selezionare Quindi per e selezionare il successivo campo di ordinamento.

Attraverso il pulsante opzioni l’utente trova altre opzioni per rifinire l’ordinamento

Prima chiave di ordinamentoMaiuscole/minuscoleOrientamento

Ordinamento personalizzato

E’ possibile specificare la propria definizione di crescente e decrescente.

La procedura è la seguente:

Aprire Strumenti e scegliere Opzioni. Scegliere elenchi – evidenziare NUOVO ELENCO Inserire i vari elementi premendo INVIO. Premere aggiungi

E’ possibile effettuare l’ordinamento utilizzando formule =PICCOLO(C4:C15, RIF.RIGA(a1:a12))

Uso avanzato delle funzioni

Abbiamo analizzato l’utilizzo di formule e funzioni.

Ci occuperemo ora di come utilizzare le formule di matrice e i nomi definiti.

Ricordo che per inserire le formule di matrice nel foglio di lavoro è necessario premere la combinazioni di tasti CTRL + MAIUSC+ INVIO. In questo caso Excel racchiude una formula fra { }

Esempio : {= SOMMA(A1:A12)}

Funzioni che richiedono di essere inserite come

matrice

Per scambiare le righe di una matrice con le colonne e viceversa si deve usare la funzione MATR.TRASPOSTA

Esempio

{=Matr.Trasposta(F1:O4)}

Deve essere selezionata una matrice di celle per l’output per poter utilizzare la funzione

Altre funzioni sono: Frequenza, Regr.Lin, Regr.Log, Tendenza, Matr.Inversa e Matr.Prodotto.

Funzioni le cui componenti richiedono l’inserimento in forma di matrice

Le formule di matrice vengono adoperate principalmente nel calcolo condizionale

Vediamo un esempio

{= SOMMA(SE(A2:A15=“Rossi”; B2:B15))}

Formule che richiedono più celle

Un tipo di tabella molto comune è la seguente:

Due matrici (B19:F19 e B20:F20) sono moltiplicate fra loro e il risultato è restituito in un’altra matrice (D4:H4)

Esempio

Uso dei nomi E’ possibile usare nomi da assegnare a intervalli di dati (es.

B1:b13 > Pagamenti)

Perciò le formule

=Somma(B1:B13) e =Somma(Pagamenti)

Restituiscono lo stesso risultato.

Per definire nomi usare Inserisci|Nome|Definisci. Utilizzando la casella di riepilogo nomi è possibile vedere i valori associati ai nomi definiti.

E’ possibile inserire nomi anche per le formule.

Definizioni dinamiche per intervalli con nomi

Spesso, dopo aver aggiunto dati a intervalli esistenti, si desidera aggiornare dinamicamente oggetti nella cartella di lavoro, per esempio grafici e formule secondo i nuovi dati

Supponiamo di avere un elenco di numeri nella casella B che rappresenta le entrate mensili. I dati si trovano nelle celle B2:B10.

Il totale è calcolato con la formula =SOMMA(EntrateMensili). Un grafico mostra l’andamento delle entrate.

Vogliamo aggiornare automaticamente la formula che somma i totali e il grafico ogni volta che nelle celle di colonna B è inserito un valore relativo alle entrate di un nuovo mese

Definizioni dinamiche per intervalli con nomi

La formula dipende dall’intervallo $B$2:$B$10 denominato EntrateMensili.

Il problema si risolve usando la funzione CONTA.NUMERI che conta quanti numeri ci sono in una colonna. Per sapere i numeri in B

=CONTA.NUMERI(B:B)

E’ anche necessaria la funzione

=SCARTO(Riferimento;Righe;Colonne:Altezza;Larghezza)

Definizioni dinamiche per intervalli con nomi

E’ possibile utilizzare la funzione CONTA.NUMERI per determinare quanti numeri ci sono in un intervallo; il risultato può essere successivamente utilizzato per determinare l’altezza in righe di un intervallo

La formula

=SCARTO($B$2;0;0; CONTA.NUMERI($B:$B);1)

Definisce un intervallo spostato di zero righe e zero colonne da B2, con tante righe quAnti sono i numeri nella colonna B e una sola colonna. Esempio

Valori duplicati I valori duplicati creano problemi.

La funzione

MAX(FREQUENZA(A1:A10;A1:A10))

Restituisce il numero di volte che un valore nella matrice di dati è uguale a se stesso

=SOMMA(SE(FREQUENZA(A1:A10;A1:A10)>1;1;0))

Grafici di dati E’ possibile usare la creazione guidata per i grafici

Selezionare un grafico

Gli assi: a colonna, a barre, lineare XY

Grafici a torta ed anello, ad area e radar

Cilindro piramide cono

Grafico di superficie

Excel e WEB

Per poter usare una cartella di lavoro attraverso il Web è necessario salvare la cartella in formato HTML

E’ possibile salvare una cartella in formato HTML da usare in una finestra di Internet Explorer usando il comando FILE!Salva pagina Web.

Il comando apre la finestra di dialogo Salva con nome utilizzando i seguenti controlli

Tutta la cartella. Salva tutti i fogli di lavoro Seleziona. Salva solo la selezione corrente.

Excel e WEB

Aggiungi interatività. Questa opzione rende il documento interattivo e permette agli utenti finali di elaborare il documento. Altrimenti il documento è un oggetto statico

Esempio

Pubblica pubblica il documento sul sito Web finale (è necessaria una password)

Salva e Cambia titolo. Si può salvare il documento in formato html e cambiare il titolo del ddocumento Web

Excel e WEB

Se la cartella contiene elementi grafici questi saranno convertiti nella forma più adatta ad essere usati dal Web

Opzioni WEB Si selezionano con il comando Strumenti!Opzioni

usare scheda Generale e pulsante Opzioni Web.

Salva dati aggiuntivi nascosti per la gestione delle formule che si riferiscono a intervalli che non appartengono all’intervallo salvato nel file HTML

Carica immagini non create con Excel.

Apertura di un documento HTML

Quando si riceve un foglio di lavoro HTML è possibile visualizzare i dati direttamente dal browser.

Se il foglio è stato memorizzato in maniera interattiva, l’utente può modificare i valori.

Nel foglio di lavoro non esiste la barra della formula e quindi non è possibile distinguere fra valori inseriti e calcolati. E’ possibile modificare lo stesso i dati del documento.

Modifica dati

Facendo clic su una cella, la cella assume alcune delle funzionalità della barra della formula Excel

Con un doppio clic è possibile sapere sei dati rappresentano valori o formule.

I vecchi dati si modificano utilizzando i tasti CANc e inserendo le nuove informazioni.

Inserimento ed eliminazione righe colonne

Non esistono comandi che permettono di inserire nuove righe o colonne.

E’ possibile ottenere lo stesso risultato adoperando i comandi contenuti nel menu di scelta rapida che appare selezionando sul foglio il pulsante destro del mouse.

La larghezza delle colonne e righe può essere modificata trascinando la linea che separa i pulsanti di riga e colonna.

Quando si elabora un foglio di lavoro interattivo tramite browser si può aprire la finestra Comandi dal pulsante che si trova sulla barra.

Query Web Mediante le query Web l’utente può recuperare testo e tabelle da

documenti Web e quindi utilizzare gli strumenti e le funzioni Excel per elaborare i dati.

Per creare una query Web è sufficiente aprire il menu Dati e scegliere il comando Carica dati esterni!nuova Query Web

Inserire l’URL della pagimna Web nell’Indirizzo e premere va. Quando appare la pagina individuare le tabelle da importare e fare click sul pulsante posto nell’angolo superiore sinistro dell’area di lavoro.

Utilizzando il pulsante Importa i dati sono importati nel foglio di lavoro corrente

Query Web Dopo aver importato i dati Excel assegna un nome al

nuovo intervallo

Successivamente sarà possibile selezionare qualunque cella di quell’intervallo e scegliere il comando Dati!aggiorna per ripetere la query e aggiornare i dati.

E’ possibile salvare la query Web in un file e farla utilizzare da altre cartelle di lavoro.

Si usa salva query e la si salva in un file. Le altre cartelle usano il comando Dati!Importa dati esterni!Importa dati

Macro È possibile creare macro per eseguire comandi ripetitivi.

Quando l’utente dovrà eseguire la stessa sequenza di operazioni basta che riproduca la registrazione , ovvero esegua il programma.

Le macro possono essere personalizzate.

Per registrare una macro. Un file contiene data ora e temperatura. L’utente deve processare i dati per usarli in un grafo di controllo processo.

Macro La colonna A deve indicae l’anno non solo il giorno o il

mese, inoltre gli orari visualizzati nelle celle della colonna B devono apparire nella notazione basata sulle 24 ore.

I valori della temperatura espressi in gradi Fahrenheit devono essere trasformati in gradi Celsius.

La prima cosa è quella di scegliere Strumenti|Macro|Registra

Che consente di creare una macro. Il pulsante interrompi registrazione disattiva il registratore di macro.

Macro La procedura è la seguente ESempio

Cliccare il pulsante di intestazione della colonna A per selezionare l’intera colonna. Attivare il comando Formato|celle portare in primo piano la scheda Numero, selezionare Data fare click sull’opzione 14/3/01 e poi OK.

Cliccare la colonna B Attivare il comando Formato |celle, portare in primo piano la scheda Numero selezionare ORA e scegliere l’opzione 13:30 e poi ok

Selezionare la cella D2 e inserire la formula =5/9*(C2-32)

Per convertire i gradi

Macro Alla fine interrompere la registrazione.

Ora abbiamo una macro che realizza quanto richiesto

Le macro sono memorizzate in un cartella di lavoro

Per attivare una macro basta attivare i tasti di scelta rapida che gli sono stati assegnati, oppure da Strumenti|Macro una volta scelta la macro si preme il pulsante esegui.

Attenzione al foglio usato

Excel usa ActiveCell.FormulaR1C1=“Numero telefonico”

Esempio di iterazione Vogliamo usare un foglio di lavoro per risolvere i

problemi di diffusione

Tali problemi possono essere applicati a:

Trasferimento calore in una piastra

Dissolvimento di zucchero o sale in acqua

Movimento nebbia

Esempio di iterazione Uso di una griglia Passi discreti per cambiare lo stato nelle celle Ogni cella ha un valore Ad ogni iterazione, o time step, il valore della cella è

ricalcolato usando la media delle quattro celle vicine (esempio)