modulo4_ud10

7/18/2019 modulo4_ud10

http://slidepdf.com/reader/full/modulo4ud10 1/8

2.3 Le formule in Excel

2.3.1 Introduzione

In un foglio elettronico, una formula è un tipo di dato (vedi RICHIAMO) che, immesso in unacella, determina la regola secondo la quale il valore del dato che apparirà dipende dai valori dialtre celle. Se ad esempio in A3 è inserita la formula A1+A2, in A3 apparirà la somma dei valoricontenuti in A1 e A2.

La scrittura di una formula deve rispettare le regole "sintattiche" del singolo prodotto (vediRICHIAMO), tutte peraltro molto semplici. Sintetizziamo qui le principali regole di Excel,limitando il tutto alle formule più semplici:

• una formula deve essere preceduta da uno dei simboli < = > , < + > , < - >, altrimenti vieneassunta come un testo (vedi RICHIAMO).

• nella barra delle formule si legge la formula inserita in una cella, nella cella il valore risultante(vedi RICHIAMO)

•

la formula contiene costanti (in particolare numeri), riferimenti a celle o a funzioni e"operatori” (p.e. quelli di addizione (+), sottrazione (-), moltiplicazione (*) e divisione (/)),nonché a parentesi aperte e chiuse

• i riferimenti possono essere scritti indifferentemente in maiuscolo a in minuscolo: sonoriferimenti a celle, ad esempio, a3, A3, sono riferimenti a funzioni (vedi in seguito) somma eSOMMA;

• i riferimenti a celle possono essere inseriti nella formula, oltre che manualmente, anche usandoil mouse cliccando sulla cella che si desidera inserire;

• in qualche caso, nelle formule si deve inserire il riferimento ad una zona (p.e, somma(A2..A7), per indicare la somma di tutte le celle fra A2 eA7); in tal caso, con il mouse siseleziona tutta la zona ed Excel provvede ad indicarla correttamente nella formula.

Comando

a menu

Comando-

pulsante

Tasti di

scelta rapidaAzione vedi §

da Excel

Inserisci-Funzione… ALT+I+ZInserisce una funzione, in modoassistito, nella cella attiva

3.3.6

RICHIAMO: § 1.2.2 - I tipi di dato in un foglio elettronico

RICHIAMO: § 1.4.2 - Formule e segnalazioni di errore

RICHIAMO: § 1.2.3 - Tipi assegnati automaticamente o "forzati"

RICHIAMO: § 2.1.3 - La finestra di Excel

2.3.2 Riferimenti relativi, assoluti e misti

Si ricorda (vedi RICHIAMO) che uno dei vantaggi delle formule è che esse sono riproducibili per copia in altre celle del foglio e che, qualora viceversa in una cella è inserita una costante, aquesta occorre fare riferimento "assoluto" (vedi RICHIAMO).

Pertanto in Excel i riferimenti ad una cella possono essere relativi (p.e. A1, B2) o assoluti, edallora si premette il segno di “$” prima della riga e prima della colonna (p.e. $A$1, vediRICHIAMO) oppure anche "misti".

7/18/2019 modulo4_ud10


I riferimenti misti possono essere relativi sulla riga ed assoluti sulla colonna (p.e.A$1) edallora la formula mantiene fisso il riferimento di colonna e varia nella copia il riferimento allariga o viceversa (p.e. $A1). Sul piano pratico, se una costante è valida per tutte le celle della riga,ma è diversa fra le varie righe, allora dovrà avere un riferimento assoluto sulla riga e relativosulla colonna.

RICHIAMO: § 1.5.3 - Immissione per copia di formule

RICHIAMO: § 1.5.4 - I riferimenti assoluti

2.3.3 Le costanti in un foglio

Una costante può essere inserita direttamente in una formula (p.e. 0,2*B5) oppure può essereimmessa in una cella alla quale si fa riferimento (p.e. in A1 si immette 0,2 e la formula diventa=A1*B5). Si segue questa seconda via quando si tratta di valori che sono costanti al momento ma

potrebbero variare nel tempo, come un'aliquota d’imposta o un coefficiente tecnologico.Se peraltro, come in genere accade, alla costante si fa riferimento in più formule e queste

vogliono essere generate anche per copia, allora il riferimento alla costante va fatto in modoassoluto (p.e. =$A$1*B5)

Un esempio classico è il calcolo del prezzo al lordo dell'IVA (oggi al 20%) di un prodotto ilcui prezzo netto è memorizzato nella cella B5 si può scrivere la formula 0,2*B5, ma se cambieràl'IVA occorrerà andare a ripescare lo 0,2 nelle varie formule ed aggiornarlo; ma si può, piùutilmente, memorizzare l'aliquota IVA in A1 e far riferimento assoluto a questa cella.

RICHIAMO: § 1.5.4 - I riferimenti assoluti

2.3.4 Operatori aritmetici e uso delle parentesi

Gli operatori aritmetici fondamentali sono illustrati ed esemplificati in figura. Essi possonoanche essere usati in combinazione tra di loro come nelle classiche "espressioni algebriche" (sinoti soltanto l'utile aggiunta dell'operatore %).

La "priorità" fra gli operatori (prima elevazione a potenza, poi moltiplicazione e divisione, poiaddizione e sottrazione) fornisce le regole per l'interpretazione della formula: sono le classicheregole per il calcolo delle espressioni algebriche. L'uso di parentesi (solo tonde, eventualmenteinnestate le une nelle altre) altera le priorità. Si ha ad esempio:

=A2+B2*C4 prima il prodotto,poi la somma=(A2+B2)*C4, prima la somma, poi il prodotto=((A2+B2)*C2)^2+D6 prima A2+B2, poi *C2, poi elevato al quadrato, infine +D6=A1/B1°C1 equivale a (A1/B1)*C1 e quindi a A1*C1/B1 e non A1/(B1*C1)

Si noti che, nel dubbio, si possono usare parentesi ridondanti, scrivendo ad esempio=A2+(B2*C4) oppure =(A1/B1)°C1

EsempioOperazione Esempio di formula

numerico risultatoAddizione =A1+B1 =6+2 8Moltiplicazione =A1*B1 =6*2 12

7/18/2019 modulo4_ud10


Percentuale =A1*10% =6*10% 0,6Sottrazione =A1-B1 =6-2 4Divisione =A1/B1 =6/2 3Elevazione a potenza =A1^B1 =6^2 36

2.3.5 Messaggi di errore di Excel

L’interprete di Excel nel controllare l’integrità sintattica delle formule e valutarne il risultato può rilevare errori più o meno banali o risultati non previsti, in questi casi viene visualizzatonella cella, non il risultato aspettato, ma un messaggio che dipende dal tipo di anomalia. Nelseguito vengono specificati i messaggi più comuni:

#VALORE! una delle celle a cui la formula si riferisce con contiene un valorenumerico

#DIV/0! (divisione per 0): il divisore è una cella contenente 0 oppure vuota

#RIF! una delle celle cui la formula fa riferimento non esiste#NOME? si fa riferimento ad un nome che non esiste (forse è un nome errato di

funzione oppure il riferimento ad una cella è scritto male)

riferimento circolare Excel non può calcolare la formula perché una cella fa riferimento a sestessa, direttamente o indirettamente (attraverso altre celle)

###### il valore è più lungo della larghezza di colonna della cella (si trattadunque di un formato da rivedere)

2.3.6 Funzioni in ExcelExcel contiene una grossa raccolta di funzioni (vedi RICHIAMO) per il calcolo e per la

manipolazione dei dati nei fogli di lavoro. Una funzione si presenta nella forma

Nome funzione (argomento1; argomento2; ….)

dove gli argomenti, separati dai ";", possono essere uno solo oppure più di uno in dipendenza dellafunzione e ciascuno di essi può essere il riferimento ad una cella o ad una zona oppure anche unaespressione; è ad esempio una funzione:

Somma(A1; A2;B3:B13)

nel significato di somma di A1 con A2 e con tutte celle da B3 a B13.

Le funzioni possono essere inserite oltre che digitandole direttamente, anche con l'ausilio delmouse ed in modo assistito. Esemplifichiamo con la funzione Somma, in un caso più semplice diquello di cui sopra: nella cella E9 del foglio di figura 1 si voglia inserire la somma delle celle daE4 a E7. Si ha allora:

• Digitazione. Si digita in E7 la formula =Somma(E4:E7) (figura 2)• Con l'ausilio del mouse. Si digita Somma (e si seleziona con il mouse l'intervallo E4..E7:

Excel completa automaticamente la formula con la parentesi chiusa• Modo Assistito. Stando nella cella E7, si lancia il comando Inserisci-Funzione: si apre una

finestra di dialogo nella quale è possibile selezionare la categoria e il nome della funzione; nelnostro caso "Matematiche e log" e SOMMA (figura 3) e quindi

•

avendo selezionato la funzione, compare un'altra finestra per la definizione degli argomenti(figura 4)

7/18/2019 modulo4_ud10


• per ciascuno di essi (nel nostro caso il parametro è uno solo) Excel guida l'utente, chiarendoneil significato

• gli argomenti possono dunque essere digitati oppure, agendo sul pulsante , essereselezionati con il mouse come nel punto 2.

• in qualche caso (come nel nostro), Excel suggerisce il parametro se il suggerimento è valido,

lo si accetta cliccando OK• nel nostro caso Excel, vedendo un elenco di dati in verticale, suggerisce E4:E8 (ed il

suggerimento è quello giusto).

In particolare, per la funzione Somma è presente un pulsante sulla barra degli strumenti“Standard” che facilita ulteriormente l’applicazione della stessa.

È importante ricordare che quando le celle contengono formule e funzioni le operazioni dicopia e sposta vengono operate seguendo i riferimenti relativi nel senso che, relativamente al

precedente esempio seguente della somma, copiando la formula, dalla cella E9, nella cella D9,quest’ultima conterrà la somma dell’imposta IVA sui prodotti.

RICHIAMO: § 1.4.3 - Funzioni: generalità

Figura 1

Figura 2

7/18/2019 modulo4_ud10


Figura 3 Figura 4

2.3.7 Funzioni media, minimo, massimo e conteggio

In figura si presentano le più diffuse funzioni di Excel. Per ciascuna di esse è segnalato ilsignificato, è presentato un esempio, l'immagine della sua costruzione in modo assistito (vedi

RICHIAMO) ed il risultato del calcolo che apparirà nella cella del foglio. Si noti quanto segue:• Nella finestra di dialogo è presente una breve descrizione del significato della funzione• Tutte queste funzioni prevedono in genere più argomenti, cioè l'intervallo è in genere

costituito da più zone separate da ";". Sono però state esemplificate solo funzioni realizzatecon un solo argomento.

• In fase di costruzione della funzione, la finestra di dialogo riporta l'elenco dei valori sui qualila funzione agirà ed il risultato del calcolo, che apparirà nella cella.

• Le funzioni esemplificate appariranno nella barra delle formule

RICHIAMO: § 2.3.6 - Funzioni in Excel

Funzione Parametri Esempio Costruzione guidata

Media una o piùzone

=Media(B6:C10)

7/18/2019 modulo4_ud10


Massimouna o piùzone

=Max(B6:C10)

Minimouna o piùzone

=Min(B6:C10)

ContaValori

una o piùzone

=Conta.Valori(B8:D11)

Analogamente all’ultima funzione, per il conteggio esistono altre funzioni tali, come adesempio la funzione Conta Numeri che ha il compito di contare i valori numerici in unintervallo di celle, oppure come Conta Vuote che conta i numero di celle vuote in un intervallo,

ecc.

2.3.8 Operatori di confronto logico

Gli operatori logici, che sono i classici operatori booleani ed hanno come possibile risultatodue valori (Vero – Falso) normalmente utilizzati in matematica, di solito non vengono usati dasoli ma vengono posti all’interno di funzioni e formule più complesse e comunemente usati neitest condizionali. Nella seguente tabella vengono specificati in dettaglio gli operatori logici piùcomuni.

Operatore Esempio Risultato

= (segno uguale) A1=B1Vero se il contenuto delle celle A1 e B1

è identico.

> (segno maggiore) A1>B1Vero se il contenuto della cella A1 èmaggiore (o successivo alfabeticamente)al contenuto di B1.

< (segno minore) A1<B1Vero se il contenuto della cella A1 èminore (o precedente alfabeticamente)al contenuto di B1.

>= (segno di maggiore o uguale) A1>=B1Vero se il contenuto della cella A1 èmaggiore o uguale (o successivo-ugualealfabeticamente) al contenuto di B1.

<= (segni di minore o uguale) A1<=B1

Vero se il contenuto della cella A1 è

minore o uguale (o precedente-ugualealfabeticamente) al contenuto di B1.

<> (segno di diverso da) A1<>B1 Vero se il contenuto delle celle A1 e B1

7/18/2019 modulo4_ud10


è diverso.

2.3.9 La funzione SE

Restituisce un valore in dipendenza di una condizione specificata. Nella cella in cui si scrivela formula si effettua dapprima un test (vedi RICHIAMO), nell’esempio seguente, verificando seil valore presente nella cella B4 è inferiore o uguale a 10 (costante);

• allora se ciò accade si immette, automaticamente, “Basso” come risultato della formula• altrimenti il risultato viene ad essere “Alto”La barra della formula conterrà: =SE(B4<=10; “Basso”; “Alto”)

RICHIAMO: § 2.3.8 - Operatori di confronto logico

2.3.10 La copia delle formule

Una particolare precisazione è data alla copia di celle contenenti formule, infatti, a differenzadelle copie di valori, si copiano le formule con i relativi riferimenti alle celle che concorrono allosviluppo della stessa formula. Le tecniche ed i comandi per la copia sono sempre gli stessi (copia

per trascinamento, CTRL+C, ecc.), ma Excel automaticamente provvederà a modificare ilriferimento alle celle, solo se questo è un riferimento relativo (vedi RICHIAMO). Quindi bisogna

porre attenzione sulla costruzione della formula e dell’eventuale suo uso come copia in altrecelle.

Cioè se nella cella F6 inserisco, p.e., la formula che trova il massimo dei valori nelle celle F1… F5 ( =MAX(F1:F5) ) essa viene vista come “il massimo delle 5 celle immediatamente in testa

alla cella” F6. Nel copiare la formula i riferimenti alle celle, come detto, vengono modificati

automaticamente questo perché il riferimento a queste celle è RELATIVO (predefinito perExcel), quindi copiando questa formula in una qualsiasi altra cella, ad esempio C8, la formulacopiata viene vista come “il massimo delle 5 celle immediatamente in testa alla cella” C8, cioè ilmassimo delle celle C3…C7.

Diversamente quando i riferimenti sono ASSOLUTI (vedi RICHIAMO) i quali non vengonoaggiornati spostando o copiando la formula.

RICHIAMO: § 1.5.1 - Copia di celle, zone, righe e colonne

RICHIAMO: § 1.5.2 - Spostamento di celle, zone, righe e colonne

RICHIAMO: § 1.5.3 - Immissione per copia di formuleRICHIAMO: § 1.5.4 - I riferimenti assoluti

7/18/2019 modulo4_ud10


RICHIAMO: § 1.5.5 - Copie speciali

modulo4_ud10

Documents

Transcript of modulo4_ud10