Novara, 9 giugno 2008Novara, 9 giugno 2008 11

Corso avanzato di Corso avanzato di InformaticaInformatica

LezLez. 3 di 10 . 3 di 10 –– Docente: valter.rolando@med.unipmn.itDocente: valter.rolando@med.unipmn.it

�� Protezione e sicurezzaProtezione e sicurezza�� ModelliModelli�� OrdinamentoOrdinamento�� Ricerca e filtriRicerca e filtri�� CollegamentiCollegamenti�� FunzioniFunzioni

IL FOGLIO ELETTRONICO ( II parte )

22

PROTEZIONE

Parte del materiale riportato in queste diapositive è tratto da:

- ECDL Advanced Modulo AM4 Foglio Elettronico (Mondadori Informatica, 2004)- ECDL ADVANCED AM4 di G. Longhi, R. P. Dameri, G. Foresti (Loescher Editore 200x)

33

PROTEZIONE

Importante quando più utenti, diversi dagli autori delle tabelle, intervengono successivamente sugli stessi dati.

Proteggere i dati di un foglio di lavoro può essere utile per evitare modifiche accidentali o dolose

I dati risulteranno comunque visibili, ma non saràpossibile manometterli

Si possono attuare diversi gradi di protezione dei dati

44

PROTEZIONE

Proteggere l’intero foglio.

w64

Diapositiva 4

w64 Quando più utenti diversi devono intervenire sulla stessa cartella di lavoro, è opportuno valutare l'ipotesi di proteggere i fogli di lavoro con alcuni strumenti che Excel mette a disposizione.

Per proteggere l'intero foglio bisogna:* selezionare il foglio;* seguire il percorso STRUMENTI -> PROTEZIONE -> PROTEGGI FOGLIO..;* inserire nella finestra di dialogo "Proteggi foglio" una password nella casella "Password per rimuovere la protezione";* nella stessa finestra, nel riquadro "Permetti a tutti gli utenti della cartella di lavoro di", cliccare sulle caselle di controllo relative alle azioni che si intende permettere;* inserire nuovamente la password alla richiesta di conferma e cliccare su OK.

Si noti che l'attivazione di "Seleziona celle bloccate" comporta la contestuale attivazione di "Seleziona celle sbloccate"

Come si e' visto, è possibile attivare diverse caselle di controllo per consentire, una volta protetto il foglio di lavoro, di consentire solo alcune operazioni.

Per rimuovere la protezione è necessario conoscere la password, se questa è stata inserita. Infatti Excel consente di inserire anche una password nulla. A che scopo? Evidentemente per attivare una protezione nei confronti degli errori accidentali di chi può lavorare senza alcuna limitazione sul foglio di lavoro. Per rimuovere la protezione:* STRUMENTI -> PROTEZIONE -> RIMUOVI PROTEZIONE FOGLIO* Digitare la password richiesta

A volte può esserci l'esigenza di proteggere soltanto alcune celle, non tutto il foglio di lavoro. Ad es. ci potrebbe essere l'esigenza di proteggere le celle in cui abbiamo definito delle formule, consentendo all'utilizzatore di inserire i dati in celle non protette, ma non di modificare le formule.La procedura è la seguente:* selezionare all'interno del foglio le celle che si intende lasciare sbloccate (ad es. quelle in cui si inseriranno i dati);* FORMATO -> CELLE;* selezionare la scheda "Protezione";* Disattivare la casella di controllo "Bloccata" e premere OK;* STRUMENTI -> PROTEZIONE -> PROTEGGI FOGLIO e quindi seguire lo stesso iter già visto per la protezione dell'intero foglio.

In questo modo le celle selezionate risulteranno sbloccate.Si noti che se si attiva la casella di controllo "Nascosta" nella scheda "Protezione", nella Barra della Formula risulteranno non visibili le formule eventualmente definite nelle celle selezionate (a patto che il Foglio sia stato protetto). Ovviamente l'attivazione va fatta prima di proteggere il foglio, se non si intende permettere l'utilizzo di "Formato celle".

; 16/04/2008

55

PROTEZIONE

Proteggere solo alcune celle del foglio.

Per impostazione predefinita tutte le celle di un foglio sono già Bloccate, ma se il foglio di lavoro non viene protetto, su tutte le celle sono possibili modifiche anche se risultano bloccate.

66

PROTEZIONE

Esercizio (foglio Franchigie NBA)

Consentire la modifica delle presenze e rendere non visibile la formula del totale nella cella C10

77

PROTEZIONE

Proteggere una cartella di lavoro

• Proteggere gli elementi di una cartella di lavoro

• Proteggere una cartella di lavoro in visualizzazione o in modifica

88

PROTEZIONE

Proteggere i fogli di una cartella di lavoro

w65

Diapositiva 8

w65 Si può voler proteggere la struttura dell'intera cartella di lavoro per impedire che altri aggiungano o eliminino fogli.Questa operazione si realizza sempre dal menu STRUMENTI -> PROTEZIONE :

* Scegliere nella finestra "proteggi cartella di lavoro" la voce Struttura, se si desidera impedire lo spostamento, la cancellazione, nascondere, scoprire, la ridenominazione dei fogli di lavoro o l'inserimento di nuovi.* Scegliere Finestre se si desidera negare la possibilità di modifica delle finestre ogni volta che si apre la cartella di lavoro, in modo che siano sempre delle stesse dimensioni e posizione ogni volta che si apre il file.

; 16/04/2008

99

SICUREZZA

Proteggere una cartella di lavoro in visualizzazione o in modifica

w66

Diapositiva 9

w66 Alcuni documenti possono avere caratteristiche di riservatezza o esigenze di sicurezza tali da doverne impedire l'accesso agli utenti non autorizzati.Oltre alle protezioni precedenti è anche possibile impedire totalmente l'accesso a una cartella di lavoro a chi non è autorizzato, vale a dire non conosce la password di accesso.E' infatti possibile proteggere l'intera cartella di lavoro mediante l'utilizzo di una password di scrittura e una di lettura. Le due diverse protezioni servono a impedire che utenti non autorizzati possano aprire il file (protezione per la lettura) o modificarne il contenuto (protezione per la scrittura).In entrambi i casi la password viene assegnata al momento in cui la cartella viene salvata:

* A file aperto dal menu File scegliere "Salva con nome";* Nella finestra "Salva con nome" andare a scegliere "Opzioni generali" dal menu Strumenti;* Nella finestra che si apre è possibile digitare una password di lettura (se si vuole che il file possa essere visualizzato dopo aver inserito la password) e una password di scrittura (se si vuole consentire anche di modificare il file).

E' possibile immettere due password diverse.Se si seleziona "Consigliata sola lettura" il documento non risultera' protetto, ma in fase di apertura comparira' un messaggio in cui l'utente potrà scegliere se aprirlo in sola lettura o no; se vorrà aprirlo per apportare modifiche dovrà comunque digitare (e quindi conoscere) la password di scrittura.

Come si rimuove questa protezione?FILE -> SALVA CON NOME -> STRUMENTI -> OPZIONI GENERALI e quindi si rimuovono le password lasciando i relativi campi in bianco. Dopo aver dato l'OK Excel richiederà se si vuole sostituire il file (nel campo "nome file" ovviamente si lascia il nome corrente del file). Si deve rispondere affermativamente. ; 16/04/2008

1010

SICUREZZA

Proteggere una cartella di lavoro in visualizzazione o in modifica

1111

MODELLI

1212

MODELLI (I)

Un modello è una cartella di Excel predefinita (con già preimpostati dati, formule, formattazioni, stili, intestazioni di righe e di colonne, macro, ecc.)

I modelli sono file con estensione .xlt

E’ possibile utilizzare i pochi modelli già presenti in Excel, scaricarne altri dalla Rete, crearne di nuovi e modificare i modelli già presenti.

Vantaggi: cartelle standard, velocità, minori rischi di sovrascrittura dei file.

w67

Diapositiva 12

w67 Un modello è un documento di Excel che può servire come base per lo sviluppo di nuove cartelle di lavoro. ; 16/04/2008

1313

MODELLI (II)

Come si accede all’elenco dei modelli disponibili

Riquadro attività

1414

MODELLI (III)

Come si accede all’elenco dei modelli disponibili

w68

Diapositiva 14

w68 Su WinXP Excel cerca il modello predefinito (Cartel.xlt) nei seguenti posti quando si avvia:

* "User Excel Startup Folder" : c:\Documents and Settings\<userName>\Application Data\Microsoft\excel\xlstart* "System Excel Startup Folder" : c:\Programmi\Microsoft Office\OFFICE11\xlstart

Tutti i file presenti in quesi due posti vengono aperti quando Excel si avvia. Se si crea un nuovo file Excel utilizzerà il modello Cartel.xlt (vuoto).

I modelli della scheda "Soluzioni fogli di calcolo" si trovano in c:\Programmi\Microsoft Office\Templates\1040\

; 16/04/2008

1515

MODELLI (IV)

Come si crea un nuovo modello

1616

ORDINAMENTO

1717

ORDINARE I DATI (I)

Utilizzo NON CONSIGLIATO dei pulsanti di ordinamento rapido.

w41

Diapositiva 17

w41 Ordinamento dei dati.In Excel i dati possono essere ordinati alfabeticamente o numericamente utilizzando i pulsanti di "Ordinamento rapido" nella barra degli strumenti standard. L'ordinamento può essere crescente (A-Z) o discendente (Z-A).Si noti che per utilizzare questi pulsanti prima di ordinare i dati NON bisogna selezionare l'intera colonna o l'insieme delle celle dell'elenco contenute in una colonna, perchè così facendo SOLO i dati selezionati rischiano (in realtà, come è mostrato in diapositiva, Excel avverte del pericolo con un avviso) di essere ordinati, lasciando il resto dei dati nelle posizioni dove erano, perdendo così l'associazione dei dati nei record!Per ordinare i dati correttamente in Excel occorre - selezionare una singola cella dell'elenco all'interno della colonna contenente il campo secondo il quale cui vogliamo ordinare- cliccare sul pulsante di Ordinamento rapido

Se i dati non erano inizialmente ordinati alfabeticamente o numericamente, una volta che abbiamo realizzato un ordinamento non saremo più ingrado di riportare l'elenco all'ordinamento iniziale (quando abbiamo chiuso il file non possiamo più annullare). Ci sono dei casi in cui è utile potertornare all'ordinamento iniziale: si pensi ad es. a un elenco di registrazioni di persone, aggiornato in tempo reale, in cui è importante conoscere l'ordine di registrazione, ma nell'elenco non è stata inserita una colonna data:ora per tale informazione.

Comunque si può rimediare aggiungendo una colonna di conteggio all'elenco, che numeri ogni riga sequenzialmente. Quando voglio tornare all'ordinamento iniziale è sufficiente ordinare per questa colonna (N nell'es. in diapositiva).

; 08/06/2007

1818

ORDINARE I DATI (II)

Utilizzo CORRETTO dei pulsanti di ordinamento rapido.

1919

ORDINARE I DATI (III)

Comando “Ordina” del menu “Dati”.

w42

Diapositiva 19

w42 I pulsanti di "Ordinamento rapido" ci consentono di ordinare i dati in base a un solo criterio (colonna) per volta.Talvolta c'è la necessità di fare ordinamenti più complessi. Per es. se abbiamo un elenco relativo a persone potremmo voler ordinare i dati primain base al Cognome e poi, in caso di cognomi uguali, in base al Nome.Per questo tipo di ordinamenti dobbiamo ricorrere al comando "Ordina" dal menu "Dati", dopo aver cliccato in una qualunque cella dell'elenco.

Nella finestrella "Ordina" occorre selezionare il campo che vogliamo utilizzare come criterio primario per l'ordinamento (alfabetico o numerico, crescente/decrescente).Inoltre la finestra ci dà la possibilità di selezionare un secondo e un terzo criterio di ordinamento.

E se dovessimo attuare un ordinamento su quattro o più campi?Dovremmo impostare più ordinamenti successivi. Se ad es. dobbiamo ordinare secondo quattro campi, dapprima ordiniamo i dati secondo l'ultimo campo in ordine di importanza. Quindi effettueremo un ulteriore ordinamento con gli altri tre campi. ; 20/04/2008

2020

ORDINARE I DATI (IV)

Comando “Ordina” del menu “Dati”.

2121

ORDINARE I DATI (V)

Opzioni di ordinamentoEsempio: ordinare per mese

w44

Diapositiva 21

w44 Ci sono dei casi in cui le opzioni di ordinamento viste finora non bastano. Per es. come fare quando dobbiamo ordinare un insieme di dati in ordine cronologico per mese?I metodi automatici di ordinamento in Excel sono su base alfabetica o numerica, non cronologica.Si può però forzare Excel a ordinare un elenco secondo altri criteri facendo ricorso agli "Elenchi personalizzati" .Excel dispone già di un minimale insieme di elenchi personalizzati, ma noi ne possiamo definire di nuovi secondo le nostre esigenze.Cliccando su "Opzioni" nella finestrella di "Ordina" possiamo accedere agli elenchi personalizzati di Excel. Per ordinare i nostri dati per mese clicchiamo in una cella qualunque all'interno dell' intervallo contenente i dati, selezioniamo "Ordina" dal menu "Dati" e il primo criterio di ordinamento (per mese). Poi, prima di dare OK clicchiamo su "Opzioni" e fra gli elenchi personalizzati di "Prima chiave di ordinamento" scegliamo "gennaio, febbraio, marzo, ..".

; 07/06/2007

2222

ORDINARE I DATI (VI)

Opzioni di ordinamentoEsempio: ordinare per mese

2323

ORDINARE I DATI (VII)

Elenchi personalizzati ( Strumenti -> Opzioni -> Elenchi )

w46

Diapositiva 23

w46 Se tra gli elenchi personalizzati non vediamo ciò che ci serve, ne possiamo definire uno nuovo:1. Digitiamo gli elementi del nuovo elenco nel campo "Voci di elenco:" nell'ordine che vogliamo ponendo ogni voce su una linea separata (o separando le voci con una virgola).2. Clic sul pulsante "Aggiungi" e quindi su "OK".

Una volta definito questo nuovo elenco lo potremo utilizzare tutte le volte che ci serve. ; 07/06/2007

2424

RICERCHE E FILTRI

2525

RICERCA E SOSTITUZIONE (I)

Il comando “Trova”.

w62

Diapositiva 25

w62 Ricerca e sostituzione di un dato nella tabellaTalvolta si ha bisogno di cercare un dato all’interno di una tabella Excel. Quando questa contiene molte celle trovare ciò che si cerca richiede parecchio tempo.Fortunatamente Excel mette a disposizione dell’ utente uno strumento che consente di trovare dati molto rapidamente: il comando "Trova".

Innanzitutto occorre cliccare sulla voce TROVA presente nel menu MODIFICA.A questo punto si apre la finestra di dialogo “Trova e sostituisci”, nella quale sono presenti due schede: “Trova” e “Sostituisci”. Scegliamo la scheda “Trova” e digitiamo le parole che cerchiamo nel riquadro al centro.- Cliccando sul tasto “Trova tutti” verrà visualizzato un elenco di voci, presenti nella tabella, contenenti le parole ricercate.- Cliccando, invece, sul tasto “Trova successivo” verranno selezionate, una alla volta, tutte le celle che contengono le parole cercate.

Possiamo anche espandere la finestra “Trova e sostituisci” cliccando sul tasto “Opzioni >>”.Nella finestra "allungata" è possibile specificare alcuni criteri per la ricerca dei dati all’internodella tabella:- Possiamo chiedere di effettuare la ricerca all’interno del foglio oppuredell’intera cartella di lavoro;- Si può scegliere di dare priorità alla ricerca per righe anziché per colonne;- Possiamo anche chiedere ad Excel di ricercare il nostro dato all’interno di una formula, di un valore o di un commento. ; 08/06/2008

2626

RICERCA E SOSTITUZIONE (II)

Esempio di “Trova tutti”.

2727

RICERCA E SOSTITUZIONE (III)

Il comando “Sostituisci”.

w63

Diapositiva 27

w63 Il comando "SOSTITUISCI".Possiamo attivare questo comando, sia dalla finestra appena vista, scegliendo la scheda “Sostituisci”, sia cliccando sulla voce SOSTITUISCI presente nel menuMODIFICA.Con questo comando possiamo sostituire una o più stringhe uguali con un’altra indicata da noi.

Supponiamo di volere sostituire la parola “Rossi”, presente nella nostra tabella con la voce “Rosi”. Potremmo modificare il testo, cella per cella, ma quando la parola ricorre molte volte nella tabella, è consigliabile l’utilizzo del comando “Sostituisci”.Dopo avere aperto la finestra di dialogo “Trova e sostituisci”, digitiamo, nel riquadro “Trova”, la stringa che vogliamo sostituire e quella nuova nel riquadro “Sostituisci con”.- Cliccando sul tasto “Sostituisci tutto”, tutte le stringhe vecchie verranno sostituite da quelle nuove.- Cliccando sul tasto “Sostituisci”, solo la stringa selezionata verrà sostituita da quella nuova.- Cliccando sul tasto “Trova tutti” verrà visualizzato un elenco di voci, presentinella tabella, contenenti le parole da sostituire.- Cliccando, invece, sul tasto “Trova successivo” verranno selezionate, una alla volta, tutte le celle che contengono le parole da sostituire.

Anche in questa scheda (come nella scheda “Trova” ) è possibile specificare alcune opzioni per la ricerca dei dati all’interno della tabella. ; 07/06/2007

2828

RICERCA E SOSTITUZIONE (IV)

Esempio di “Trova tutti” per sostituire.

2929

RICERCA E SOSTITUZIONE (V)

Esempio di “Sostituisci tutti”.

3030

FILTRARE I DATI

In presenza di molti dati è spesso opportuno servirsi di filtri per selezionare i record di interesse

Filtrare dati da un elenco significa estrarne i record che soddisfano precisi criteri di selezione

Filtri semplici

Filtri avanzati

Con il percorso DATI, FILTRO.. è possibile applicare due tipologie di filtri:

N.B. : i dati esclusi dai filtri non vengono cancellati.

3131

IL FILTRO AUTOMATICO (I)w69

Diapositiva 31

w69 Quando si ha un elenco formato da molti record e abbiamo bisogno di trovare un dato in breve tempo abbiamo bisogno di un aiuto da Excel.Un modo semplice ed efficace è sicuramente il "filtro automatico".Per Applicare il filtro automatico a una tabella dobbiamo selezionare una cella all’interno della tabella stessa e cliccare sulla voce FILTRO AUTOMATICO nel sottomenu FILTRO, che si apre cliccando sulla voce DATI nella barra dei menu.

Nella prima riga, quella dell’intestazione della tabella, compare ora una freccette nera per ogni colonna.

Cliccando sulla freccetta nell’intestazione del campo si apre un elenco a discesa contenente la lista di tutte le voci distinte digitate in quel campo, ordinata alfabeticamente se il campo è testuale, numericamente se il campo è numerico.

Cliccando su una di queste voci si visualizzano tutti i record contenenti la voce selezionata (ad es. tutti i fornitori di Novara).

Per togliere il filtro dobbiamo cliccare nuovamente sulla freccetta e scegliere la voce “(Tutto)”.

Si noti che quando è applicato un filtro l'intestazione della colonna 'filtrata' presenta un triangolino di colore blu (mentre le altre colonne ce l'hanno nero), mentre tra i numeri di riga alla sinistra del foglio di lavoro non vediamo più quelli relativi alle righe escluse dal filtro. ; 08/06/2007

3232

IL FILTRO AUTOMATICO (II)

3333

IL FILTRO AUTOMATICO (III)

Il comando “(Primi 10…)”

w50

Diapositiva 33

w50 Aprendo l’elenco a discesa, oltre alla lista delle voci contenute nel campo, ci sono altri due comandi: “Primi 10” e “Personalizza”.Il comando “Primi 10” serve a trovare i 10 valori più alti o più bassi dell’intera colonna (in realtà possiamo cambiare il numero 10) .Ovviamente possiamo utilizzare questo comando solo nelle colonne contenenti valori numerici.Da questa finestra scegliendo le opzioni dal riquadro a sinistra possiamo specificare se vogliamo filtrare i valori più alti ("Superiore") o più bassi ("Inferiore"), dal riquadro al centro possiamo decidere il numero di valori da cercare, mentre da quello a destra possiamo scegliere se calcolare il filtro utilizzando i valori oppure la percentuale sul totale.

Il comando “Personalizza” serve a filtrare i dati utilizzando due criteri, entrambi sullostesso campo.Per utilizzare più di un criterio è necessario far ricorso uno dei due operatori logici, chiamati“And” e “Or” (in italiano possiamo tradurli con “E” e con “Oppure”).- Usando “And” io chiedo a Excel di filtrare i dati solo se entrambi i criteri specificati vengono soddisfatti (Criterio1 “And” Criterio2).- Usando “Or”, invece, chiedo a Excel di filtrare i dati se almeno uno dei due criteri specificati viene soddisfatto (Criterio1 “Or” Criterio2).

; 08/06/2007

3434

IL FILTRO AUTOMATICO (IV)

Il comando “(Personalizza …)” : primo esempio

Caratteri Caratteri wildcardwildcard

3535

IL FILTRO AUTOMATICO (V)

Il comando “(Personalizza …)” : secondo esempio

3636

IL FILTRO AUTOMATICO (VI)

Esercizio (due vincoli su una colonna e operatore OR).Visualizzare i record della tabella Laureati il cui cognome è “Rossi” oppure “Verdi”.

3737

IL FILTRO AVANZATO (I)

Con il FILTRO AVANZATO si possono impostare i seguenti criteri di selezione:

• Più vincoli all’interno di una colonna e l’operatore OR

• Più vincoli all’interno di una riga e l’operatore AND

• Un vincolo in una o più colonne e l’operatore OR

• Uno o due vincoli su due colonne e gli operatori AND e OR

• Più di due vincoli in una colonna e gli operatori AND e OR

• Vincoli definiti dal risultato di una formula

w70

Diapositiva 37

w70 Per utilizzare il filtro avanzato è necessario riservare una zona all'interno della quale inserire i criteri di selezione. Questa zona di solito è posta aldi sopra dell'elenco sul quale effettuare la selezione, ma può anche essere collocata in un'altra posizione o in un altro foglio di lavoro.E' inoltre obbligatorio indicare (tranne nel caso in cui il criterio scaturisca da una formula), nella zona riservata ai criteri di selezione, le stesse intestazioni delle colonne dell'elenco da analizzare.

Per visualizzare nuovamente tutti i record filtrati con un Filtro avanazato occorre scegliere "Mostra tutto" dal menu Dati, Filtro..

Nell'esercizio proposto la formula inserita in D14, come criterio di selezione, presenta sia riferimenti assoluti, sia riferimenti relativi.* è obbligatorio utilizzare riferimenti assoluti quando si fa riferimento a celle che si trovano all'esterno dell'elenco, oppure quando si fa riferimento a tutte le righe di una colonna;* bisogna utilizzare riferimenti relativi quando si vuole confrontare ogni singolo valore dell'elenco (nell'esercizio i valori nelle celle D2, D3, ecc.).Inoltre, quando si inserisce in una cella un criterio di selezione rappresentato da una formula, è bene che la cella precedente (nella colonna) nonabbia alcuna etichetta. ; 22/04/2008

3838

IL FILTRO AVANZATO (II)

Esercizio (vincoli dettati da una formula).Visualizzare i record della tabella Laureati il cui voto èsuperiore al voto medio

3939

COLLEGAMENTI

4040

COLLEGAMENTI

In Excel il termine può avere molteplici significati :

• collegamento fra celle di uno stesso foglio

• collegamento fra celle di fogli diversi

• collegamento fra celle di cartelle diverse

• collegamento fra celle e grafici

• collegamento a un foglio di Excel da un altro programma (es. Word)

4141

COLLEGAMENTI FRA CELLEw71

Diapositiva 41

w71 Il risultato mostra una copia dei dati con collegamento alle celle di origine. Sulle celle di destinazione non è stata riportata la formattazione iniziale.Si noti che il contenuto delle celle di destinazione è risultato di un collegamento alle celle di origine. E' sufficiente infatti collocare il cursore del mouse all'interno di una qualsiasi cella dell'intervallo di destinazione per osservare, sulla barra della formula, che il suo contenuto è il riferimentoalla cella di origine (ogni cambiamento di contenuto nella cella di origine verrà quindi immediatamente riprodotto sulla corrispondente cella di destinazione).

Come faccio invece a ottenere una copia assolutamente identica dei dati originali? Con un banale Copia e Incolla.. ; 20/04/2008

4242

COLLEGAMENTI FRA CELLE DI FILE DIVERSI

4343

COLLEGAMENTI FRA EXCEL E WORD

4444

COLLEGAMENTI FRA EXCEL E WORD

4545

COLLEGAMENTI FRA EXCEL E WORD

4646

COLLEGAMENTI FRA EXCEL E WORD

4747

LE FORMULE

LE FUNZIONI

4848

LE FORMULE

Una formula inserita in una cella permette di eseguire calcoli con i valori presenti in altre celle.

Il risultato della formula appare come valore

della cella

Barra delleformule

w56

Diapositiva 48

w56 Uno degli obiettivi principali dei fogli di calcolo è di fornire il risultato di operazioni matematiche.Le formule consentono di eseguire una serie di operazioni sulle celle, dalla semplice somma fino a funzioni complesse.Una formula è composta da due elementi:1. le celle da includere nella formula;2. gli operatori che determinano il tipo di calcolo da eseguire.Le formule iniziano sempre con il segno “=”.

Le operazioni eseguite da una formula possono essere semplici operazioni matematiche (addizione, sottrazione, moltiplicazione,…) o operazioni più complesse, cioè funzioni (media, massimo, arrotondamento,…).Dopo aver immesso una formula in una cella, il testo della formula (come è stata scritta) appare nella barra delle formule, mentre il risultato viene visualizzato nella cella.

Quando i valori a cui fa riferimento la formula vengono modificati (nell’esempio della diapositiva A3 e B3), il risultato viene aggiornato automaticamente: ad esempio modificando il valore della cella B3 da 3 a 5 il risultato della casella C3 viene automaticamente aggiornato a 9.

Una formula inizia sempre con il segno di uguale (=) e può contenere numeri, riferimenti a celle, testo, operatori (aritmetici, di testo e booleani)e infine funzioni di Excel.

Nota: quando si digita una funzione è possibile far scrivere automaticamente l’indirizzo di una cella nella barra della formula cliccandoci sopra con il tasto sinistro del mouse. ; 06/06/2007

4949

OPERATORI ARITMETICI

4^34^3

(4*4*4)(4*4*4)

Elevamento a Elevamento a potenzapotenza

^̂

10%10%PercentualePercentuale%%

12/412/4DivisioneDivisione//

6*56*5MoltiplicazioneMoltiplicazione**

66--22SottrazioneSottrazione--

2+22+2AddizioneAddizione++

EsempioEsempioSignificatoSignificatoOperatore Operatore

Servono per eseguire le operazioni algebriche elementari

5050

PRIORITA’ DEGLI OPERATORI

Alcuni gradi di priorità delle operazioni sono i seguenti:

• moltiplicazione e divisione hanno priorità maggiore di addizione e sottrazione

• l’elevamento a potenza ha priorità maggiore di moltiplicazione e divisione

Se non si ha molta familiarità con i gradi di priorità, per evitare errori è consigliabile utilizzare sempre le parentesi, anche se superflue.

w57

Diapositiva 50

w57 Quando scriviamo una formula che contiene più di un operatore, Excel esegue le operazioni seguendo i criteri di priorità degli operatori. Ad esempio se si scrive una formula del tipo:=3+2*4Excel eseguirà per primo la moltiplicazione (che ha una priorità più alta dell’addizione) 2*4 e poi eseguirà l’addizione fra 3 ed il risultato di 2*4, cioè 3+8. Il risultato della formula sarà 11.

Quindi 3+2*4 è equivalente a 3+(2*4). Se Excel non eseguisse le operazioni per grado di priorità, ma le eseguisse in ordine di lettura (da sinistra a destra), il risultato della formula sarebbe stato: 3+2=5 ed il risultato moltiplicato per 4, cioè avremmo ottenuto 20 (errato).

Se si vuole far eseguire le operazioni secondo un ordine diverso da quello di priorità bisogna utilizzare le parentesi, così verranno eseguite per primo tutte le operazioni racchiuse tra parentesi (cominciando da quelle più interne), ad esempio:=(3+2)*4dà come risultato 20.

Esiste anche un operatore di testo: la "e commerciale" (&) che consente di unire o concatenare una o più stringhe di testo generando una singola stringa. Ad es. "salva" & "gente" = "salvagente". ; 04/06/2007

5151

OPERATORI BOOLEANI

A1<>B1A1<>B1Diverso daDiverso da<><>

A1<=B1A1<=B1Minore o uguale aMinore o uguale a<=<=

A1>=B1A1>=B1Maggiore o uguale aMaggiore o uguale a>=>=

A1<B1A1<B1Minore diMinore di<<

A1>B1A1>B1Maggiore diMaggiore di>>

A1=B1A1=B1Uguale aUguale a==

EsempioEsempioSignificatoSignificatoOperatore Operatore

Si utilizzano per confrontare due valori con gli operatori elencati di seguito. Il risultato sarà un valore logico: VERO o FALSO.

5252

Somma automatica ( Σ )

FUNZIONI ELEMENTARI (I)w58

Diapositiva 52

w58 Le funzioni sono formule predefinite identificate tramite un nome e già presenti all'interno di Excel. Ogni funzione, così come ogni formula, iniziasempre con l'operatore =.

All’interno delle formule è possibile inserire anche le funzioni, che permettono di eseguire calcoli più complessi delle semplici operazioni aritmetiche, di testo e booleane: è possibile ad esempio eseguire la somma di una serie di numeri, fare la media aritmetica di più valori, trovare il resto di una divisione intera, ecc..

Le classi di funzioni più utilizzate sono:1. Matematiche e Trigonometriche: per calcoli su valori numerici2. Statistiche: eseguono calcoli statistici3. Data e ora: eseguono calcoli su valori temporali4. Finanziarie: eseguono calcoli sui valori relativi a prestiti, rendite e movimenti di cassa5. Logiche: calcolano i risultati di formule condizionali

Esempio 1.Ho una serie di numeri da B5 a B10, voglio calcolare la somma da inserire nella cella B11.Seleziono la cella B11 e faccio clic sul pulsante Somma automatica.Noto che ora appaiono selezionate con tratteggio le celle interessate alla somma; compare poi nella cella B11 e nella barra della formula la seguentescrittura: "=SOMMA(B5:B10)".Ciò indica che si sta eseguendo la somma dei numeri tra le celle B5 e B10.Per immettere tale funzione è sufficiente dare invio: nella cella B11 comparirà la somma numerica e la cella attiva diventerà la B12. ; 20/04/2008

5353

Altre funzioni selezionabili con il pulsante Σ

FUNZIONI ELEMENTARI (II)

5454

a) Definire una funzione senza il pulsante Σ

FUNZIONI ELEMENTARI (III)w59

Diapositiva 54

w59 Esempio 2.Vogliamo determinare nella cella C11 il numero massimo tra quelli riportati nelle celle fra B5 e B10.Faccio clic sul pulsante con il simbolo "fx" nellabara della formula. Compare un menu a discesa con varie funzioni. Scelgo MAX con un clic.Nella formula che ho scelto però non è indicato quali celle devono essere interessate dall'operazione. Excel cerca di automatizzare tutto prendendo le celle che sono vicine a sinistra della cella C11 o sopra di essa.In questo caso dobbiamo essere noi a indicare a Excel quali sono le celle da prendere in considerazione. Lo si fa cliccando sulla piccola icona quadrata (indicata dalla freccia nera nella diapositiva 11). Dopo aver cliccato compare una barra orizzontale. A questo punto si selezionano le celle interessate dal calcolo trascinando con il mouse. Al momento del rilascio sia sulla barra orizzontale, sia sulla barra della formula comparirà l'intervallo di celle selezionate (B5:B10).Cliccando nuovamente sull'icona quadrata della barra orizzontale si comunica a Excel l'intervallo di celle su cui dovrà calcolare la formula. Come si può vedere nella diapositiva 12, nella cella C11 comparirà l'espressione "=MAX(B5:B10)".Dando ok Excel calcolerà il valore. ; 07/06/2007

5555

b) Definire una funzione senza il pulsante Σ

FUNZIONI ELEMENTARI (IV)

5656

c) Definire una funzione senza il pulsante Σ

FUNZIONI ELEMENTARI (V)

5757

La funzione SOMMA.SE

FUNZIONI ELEMENTARI (VI)

Sintassi:SOMMA.SE (intervallo dove verificare il criterio; criterio; intervallo delle celle da sommare)

Somma solo i valori delle celle che soddisfano un dato criterio.

In Excel sono disponibili altre funzioni utilizzabili per analizzare i dati in base a una condizione. Per contare ad es. il numero di occorrenze di una stringa di testo o di un numero all'interno di un intervallo di celle, si utilizza la funzione CONTA.SE. Se si desidera che una formula restituisca uno di due valori in base a una condizione, si utilizza la funzione SE.

5858

Esercizio.Nella cella E24 sommare gli incassi dei film di genere storico (foglio Film) con la funzione SOMMA.SE. Soluzione :

FUNZIONI ELEMENTARI (VII)

5959

Esercizio.come ottenere i multipli di un numero contenuto in una cella prefissata (es. A3)

FUNZIONI ELEMENTARI (VIII)

Si è usata la funzione RIF.RIGA() con riferimento relativo per generare la sequenza dei numeri naturali

6060

I messaggi di errore più comuni :

DATI E FORMULE

• #div/0! divisione per zero

• #nd manca un valore necessario per il calcolo

• #nome? nome formula non esatto

• #nullo! errore nei riferimenti

• #num! errore nel tipo di dato utilizzato

• #rif! riferimento errato ad una cella

• #valore! riferimento ad una cella contenente un valore diverso da quello atteso

• ###### valore della cella più lungo di quanto la colonna permetta di visualizzare

6161

Alcune funzioni per data e ora :

FUNZIONI

= OGGI()

= ADESSO()

= DATA(aaaa;mm;gg)

= ORARIO(hh;mm;ss)

w73

Diapositiva 61

w73 In Excel le date vengono memorizzate come il numero di giorni trascorsi a partire dal 1 gennaio 1900 o 1 gennaio 1904 per i Mac. Ad es. il 1 gennaio 1900 corrisponde al numero 1, mentre il 1 gennaio 2008 corrisponde a 39448. Excel utilizza questo sistema di memorizzazione per poter fare più agevolmente i calcoli sulle date.La funzione che restituisce il vaore numerico di una data è DATA(anno;mese;giorno).

Le ore del giorno invece sono memorizzate in valori decimali, che rappresentano la porzione del tempo trascorso in un giorno, a partire dall'inizio (ore 0.00) all'ora specificata. Le ore 12.00, per esempio,corrispondono al valore 0.5. La combinazione ora-data 14:09:03, 23 ottobre 2004, invece, è rappresentata dal valore numerico 38283,5896180556, in quanto la data 23 ottobre 20054 è il giorno 38283, mentre l'intervallo tra mezzanotte e le ore 14:09:03 corrisponde a 0,5896180556 rispetto alle 24 ore del giorno.La funzione ORARIO(ora;minuto;secondo) restituisce il numero decimale corrispondente.

Ai fini della visualizzazione, data e ora vengono convertite da Excel nel formato richiesto. Se si vuole trasformare un numero seriale in data e ora basta formattare la cella in data in una delle forme proposte dalla finestra di dialogo.

Curiosita'Il 22 settembre 2007 si è scoperto che Excel mostrava in modo errato i risultati di alcune operazioni. Nello specifico quando il programma si trova ad eseguire delle operazioni che producono come risultato 65.535 il programma mostra invece 100 000. Il bug è legato a un problema di visualizzazione; eseguendo altre operazioni con la cella del risultato si ottengono dei risultati corretti. Microsoft nel blog dedicato al programma ha riconosciuto il problema e ha promesso a breve un aggiornamento che risolvesse il problema. Il 26 settembre ha dichiarato di aver pronto l'aggiornamento e che lo avrebbe distribuito appena questo avesse superato le fasi di test.

Un altro noto bug si ritrova in tutte le versioni di Excel nella gestione delle date: l'anno 1900 è erroneamente considerato bisestile e quindi le date anteriori al primo marzo 1900 sono sbagliate: ad esempio Excel calcola il giorno 1/1/1900 come una domenica (la funzione =GIORNO.SETTIMANA("1/1/1900") dà risultato "1"), mentre nella realtà si trattava di un lunedì.

Modalità con cui Excel interpreta le date ambigue.Quando interpreta date ambigue, Excel procede per supposizioni. Ad esempio, quando si inserisce una data che include solo il mese e una o duecifre, Excel assume che da 1 a 31 sia il giorno e che l'anno sia l'anno corrente. Excel assume che 01 dicembre sia 1 dicembre dell'anno corrente,non dicembre dell'anno 2001 oppure che dicembre 32 sia 1 dicembre 1932.

Modalità secondo cui Excel interpreta gli anni a due cifre.Per impostazione predefinita, quando si inserisce un valore di anno a due cifre, Excel lo interpreta nel seguente modo:* da 00 a 29 Excel interpreta i valori di anno a due cifre da 00 a 29 come gli anni che vanno dal 2000 al 2029. Ad esempio, se si digita la data 28/5/19, Excel assume che la data sia 28 maggio, 2019.* da 30 a 99 Excel interpreta i valori anno a due cifre da 30 a 99 come gli anni che vanno dal 1930 al 1999. Ad esempio, se si digita la data 28/5/98, Excel assume che la data sia 28 maggio, 1998.

Per assicurarsi che Excel interpreti i valori anno nel modo desiderato, è consigliato digitare i valori anno a quattro cifre (ad esempio, 2001, piuttosto che 01).

Diapositiva 61 (continua)

La funzione DATA(anno;mese;giorno).Anno può avere da una a quattro cifre. L'anno viene interpretato a seconda del sistema di data utilizzato. In base all'impostazione predefinita, Excel per Windows utilizza il sistema di data 1900 . * Se anno è compreso tra lo 0 (zero) e il 1899 (incluso), tale valore viene aggiunto al 1900 per calcolare l'anno. Ad esempio, DATA(100;1;2) restituirà il 2 gennaio 2000 (1900+100). * Se anno è compreso tra 1900 e 9999 (incluso), tale valore viene utilizzato come anno. Ad esempio, DATA(2000;1;2) restituirà il 2 gennaio 2000. * Se anno è minore di 0 o maggiore di 10000, verrà restituito il valore di errore #NUM!.

Mese è un numero che rappresenta il mese dell'anno. Se mese è maggiore di 12, quel numero di mesi verrà aggiunto al primo mese dell'anno specificato. Ad esempio, DATA(1998;14;2) restituirà il numero seriale che rappresenta il 2 febbraio 1999.

Giorno è un numero che rappresenta il giorno del mese. Se giorno è maggiore del numero di giorni del mese specificato, quel numero di giorni verrà aggiunto al primo giorno del mese. Ad esempio, DATA(1998;1;35) restituirà il numero seriale che rappresenta il 4 febbraio 1998. ; 22/04/2008

6262

FUNZIONI

Esercizio:Ottenere la data che cade 200 giorni a partire da oggi.

Esercizio:Calcolare il numero di giorni lavorativi del 2008.

6363

FUNZIONI

Aggiungere altre funzioni: Strumenti di analisi

6464

FUNZIONI

Alcune funzioni per il testo

= MAIUSC(testo) e =MINUSC(testo)

Convertono stringhe di caratteri in maiuscolo o

minuscolo.

= MAIUSC.INIZ(testo)

Converte in maiuscolo la prima lettera di tutte le parole presenti nel testo e le rimanenti in minuscolo.

= CONCATENA(testo1; testo2; ..)

Unisce in un unico testo gli elementi di testo indicati come argomento.

w77

Diapositiva 64

w77 Gli argomenti della funzione CONCATENA possono essere testi (racchiusi da virgolette), indirizzi di celle. Il massimo numero di argomenti è 30.In fase di digitazione non è necessario inserire i testi fra virgolette, perchè queste vengono aggiunte automaticamente dal sistema. Inoltre dopo una virgola occorre lasciare uno spazio, affinchè la virgola sia inserita nel testo finale.In alternativa alla funzione CONCATENA può essere utilizzato l'operatore & ; 23/04/2008

6565

FUNZIONI

La funzione CERCA

[ sintassi vettore ]La funzione CERCA (valore; vettore; risultato) cerca un valore in una singola riga o colonna (vettore) e lo confronta con un valore nella stessa posizione, ma in una riga o colonna diversa.

[ sintassi matrice ]La funzione CERCA (valore; matrice) cerca il valore indicato nella prima riga o nella prima colonna di una matrice.

w76

Diapositiva 65

w76 A volte ci si trova nella situazione di dovere cercare un valore in un'altra tabella.

La funzione CERCA, a differenza di CERCA.VERT e CERCA.ORIZZ, consente di elaborare zone che non siano necessariamente strutturate come le tradizionali tabelle.La sintassi della funzione è la seguente: =CERCA(Che cosa; Dove1; Dove2). In cui "Che cosa" è un valore da ricercare (un valore numerico, unastringa, ecc.), "Dove1" è la zona in cui individuare il suddetto valore, e "Dove2" quella che ospita i dati da restituire.Se il termine di riferimento è una stringa, e non viene trovato, viene visualizzato un messaggio di errore. Se, invece, è un valore numerico, vienecomunque restituito un risultato. In tal caso infatti, si prende come riferimento il valore immediatamente inferiore a quello che si vuole ricercare.

La funzione CERCA può operare su un vettore (insieme di dati a una dimensione) o su una matrice (insieme di dati a due dimensioni), utilizzando a seconda dei due casi due sintassi diverse.E' opportuno utilizzare la funzione CERCA con la sintassi matrice quando i valori che si vuole confrontare si trovano nella prima riga o nella prima colonna della matrice, e utilizzare l'altra sintassi quando si vuole specificare la colonna o la riga.Al posto della funzione CERCA, utilizzata con la sintassi matrice, si possono utilizzare le funzioni CERCA.ORIZZ e CERCA.VERT. ; 27/04/2008

6666

FUNZIONI

Esercizio.

Restituire nella cella C9 della tabella people, con la funzione CERCA, l’età di Giuseppe Verdi

6767

FUNZIONI

Le funzioni CERCA.VERT e CERCA.ORIZZ

La funzione CERCA.VERT (CERCA.ORIZZ) cerca un record valutando l’elemento nella prima colonna (riga) di una tabella o matrice e restituisce il valore del campo corrispondente alla colonna (riga) indicata.

CERCA.VERT (valore; matrice; indice; intervallo)

Valore : oggetto della ricerca (numero, testo, valore logico, ecc.)

Matrice : intervallo di celle in viene eseguita la ricerca

Indice : n.ro di colonna della matrice dalla quale prendere il risultato

Intervallo : se VERO -> corrispondenza approssimativa, se FALSO -> corrispondenza esatta

w80

Diapositiva 67

w80 La funzione CERCA.ORIZZ cerca un dato elemento nella prima riga di una matrice e restituisce il valore corrispondente in una diversa riga della matrice, secondo una nostra indicazione:

=CERCA.ORIZZ(valore; matrice; indice; intervallo)

dove:* valore: è il valore (una costante o un indirizzo di cella) da cercare sulla prima riga della matrice;* matrice: è la matrice nella quale effettuare la ricerca;* indice: è il n.ro di riga, all'interno della matrice, dalla quale deve essere deve essere prelevato il valore desiderato;* intervallo: valore logico (VERO o FALSO) specifica il tipo di ricerca; mettere VERO o omettere l'intervallo significa che si vuole fare una ricerca approssimativa, perchè se il vaore non viene trovato, verrà restituito il vaore successivo più grande minore di quello cercato; per essere certi di avere (o non avere) una corrispondenza esatta di valori occorre mettere FALSO o 0. In tal caso se il valore non viene trovato, la funzione restituisce il messaggio di errore #N/D.Inoltre quando l'opzione intervallo è VERO, i valori nella prima riga della matrice devono essere disposti in ordine crescente, altrimenti la funzione potrebbe non restituire il valore corretto.

La funzione CERCA.VERT cerca un dato elemento nella prima colonna a sinistra di una matrice e restituisce l'elemento corrispondente di una diversa colonna (da noi indicata con l'argomento indice). La sintassi è del tutto analogo a quella di CERCA.ORIZZ con le colonne al posto delle righe. ; 28/04/2008

6868

FUNZIONI

Esercizio.

Restituire nella colonna I la temperatura massima fra Novara e Vercelli (foglio temperature)

6969

FUNZIONI

Esercizio.

Restituire nella colonna I la temperatura massima fra Novara e Vercelli (foglio temperature). Soluzione:

7070

La funzione CONTA.SE

FUNZIONI

Sintassi:CONTA.SE (intervallo dove verificare il criterio; criterio)

Somma il numero di celle in un intervallo che soddisfano un dato criterio.

Come per altre funzioni (es. SOMMA.SE) sono validi criteri in forma di numeri, espressioni o testo che determinano quali celle verranno contate. Ad esempio, il criterio può essere espresso come 32, "32", ">32", "mele".

7171

FUNZIONI

Esercizio.

Ricavare nella cella B11 della tabella people l’età mediadelle persone con cognome “Rossi”