Libero
MATELIBERecdlm4mod4_3.htm

Funzioni e formule

In questo capitolo si apprendera come si utilizzano le funzioni aritmetiche e logiche, come si riconoscono i messaggi standard di errore e cosa sono i riferimenti relativi e assoluti.

Inoltre, verrà spiegato come si utilizza lo strumento di riempimento automatico per copiare o incrementare i dati.

Funzioni aritmetiche e logiche

Fare in modo che una cella calcoli e visualizzi un risultato matematico è il perno centrale di tutti i fogli elettronici .

I principi di base che un'espressione deve rispettare sono naturalmente le regole apprese a scuola per quanto riguarda i calcoli matematici.

Per tradurli in espressioni utilizzabili nei fogli elettronici il passo è breve e semplice: in Excel basta inserire un uguale in una cella e farlo seguire dall'espressione matematica.

Per esempio:

=100*(3+9)/4

In questa semplicità manca ancora un dettaglio, ovvero come è possibile scrivere espressioni che prendano i valori contenuti in una o più celle e li utilizzino per calcolare il valore di un'altra.

A questo scopo sono disponibili i cosiddetti riferimenti , dei quali si parlerà più approfonditamente in seguito, ma è comunque utile cominciare ad accennarvi.

Come si può fare apparire nella cella A1 il risultato del prodotto delle celle B2 e B3 diviso per il valore contenuto in C5?

Semplicemente facendo clic sulla cella A1 e scrivendo ciò che si è appena detto:

=B2*B3/C5

Per utilizzare il valore contenuto in una cella in un espressione, basta scrivere il riferimento a essa.

Gli operatori sono:

Altri operatori quali la radice quadrata si ottengono ricorrendo alle funzioni , poiché troppi simboli renderebbero l'espressione poco leggibile oppure potrebbero essere difficili da ricordare.

Le funzioni aritmetiche e logiche di base

La funzione può essere utilizzata al posto di un riferimento , o di un valore, ed è composta da:

Il nome della funzione è predefinito dal software , così come il numero e il tipo di parametri che richiede.

Per esempio, la funzione che esegue la somma di una serie di celle adiacenti richiede come parametro un intervallo, ovvero due riferimenti di celle separati dal carattere due punti.


L'esempio in figura contiene la somma dei valori contenuti nelle celle A1 e B1 mediante l'espressione:
=SOMMA(A1+B1)

Nota: Per azionare rapidamente la funzione somma automatica è possibile servirsi dell' icona che si trova nella barra standard di Excel.
Facendo clic su di essa si otterrà la somma dei valori della riga o della colonna adiacente la cella attiva.

E' importante sottolineare che le funzioni possono essere utilizzate all'interno delle espressioni allo stesso modo dei valori e dei riferimenti.

Ne consegue che si possono utilizzare formule in cui appaiono più funzioni.

Per esempio, le seguenti formule sono equivalenti:

SOMMA (A2:C5)

SOMMA (A2:A5)+ SOMMA (B2:B5) + SOMMA (C2:C5)

Nota: Anche se accade raramente, è utile sapere che in una formula possono trovare posto più funzioni e di tipo diverso.

Ogni software mette a disposizione decine e decine di funzioni, che possono essere raggruppate per comodità in famiglie quali:

Esistono anche funzioni che vengono generalmente ignorate dalla maggior parte degli utilizzatori dei software di fogli elettronici , ma che possono rivelarsi estremamente utili soprattutto per eseguire verifiche sull'esattezza o sulla congruità dei dati .

Tra queste vi sono le cosiddette funzioni logiche; la prima che verrà tratta è SE (), definita anche funzione condizionale che opera, in base al verificarsi di una condizione, una scelta fra due possibili.

Per funzionare, la funzione SE() richiede tre parametri, ossia la condizione logica da analizzare e le due scelte possibili.

Vediamo un esempio.

Se volessimo scrivere nella cella B1 il testo "sufficiente" se il valore contenuto nella cella A1 è maggiore o uguale a 6, o altrimenti il testo "insufficiente", l'espressione sarebbe:

=SE(A1>=6;"sufficiente";"insufficiente")

Con la tecnica del Copia e Incolla, potremmo estendere il ragionamento a una colonna di valori.

Riconoscere i messaggi standard di errore

Nell'uso delle funzioni possono verificarsi situazioni in grado di generare errori.

E’ utile capire quali sono gli errori che si commettono e come il software provvede a segnalarli.

Gli errori vengono visualizzati nella cella contenente la formula errata attraverso una serie di messaggi inizianti con il carattere #.

Per esempio, in Excel troviamo:

  • # div/0!

    • Nella formula si tenta di dividere un valore per zero (matematicamente non ammissibile)
  • # nd
    		•

    Manca un valore necessario per eseguire il calcolo.
  • # nome?
    		•

    Il nome della formula non è esatto, per esempio si è scritto soma( ) al posto di somma().
  • # nullo!

    • Indica errore nei riferimenti .
  • # num!

    • Indica errore nel numero utilizzato; per esempio, una funzione si attendeva un parametro numerico e invece ne ha trovato uno di un altro tipo.
  • # rif!

    • La funzione contiene un riferimento errato a una cella.
  • #valore!

    • Si fa riferimento a una cella che contiene un valore differente da quello atteso; per esempio, si tenta di sommare alcune celle tra cui una che non contiene un valore numerico.
  • #######

    • Il valore della cella è più lungo di quanto la colonna permetta di visualizzare; si deve agire sulla sua dimensione, poiché si tratta di un problema legato alla visualizzazione e non al valore.

    Quando si riscontra che una cella contiene un errore attraverso la relativa segnalazione, è possibile anche ricorrere alla guida in linea e digitarvi il messaggio; in questo modo si potrà ottenere un rapido supporto per individuare i motivi allorquando non si ricordi il suo significato.

    Nota: Non si deve mai inserire uno spazio per separare il nome della funzione dalla parentesi tonda che lo segue, altrimenti i programmi potrebbero visualizzare un messaggio di errore; in Excel appare la scritta # NOME? e in Lotus 1-2-3 appare ERR.

    Strumento di riempimento automatico

    Excel mette a disposizione dell'utente uno strumento in grado di semplificare l'inserimento di dati, in celle contigue, nel caso in cui tali dati siano sequenze di valori fra loro correlati: progressioni, giorni della settimana, mesi ecc.

    La procedura è quella di posizionare il puntatore del mouse sullo spigolo inferiore destro della cella che contiene il valore da usare come base iniziale, farvi un clic e, senza rilasciare il tasto, estendere la selezione in basso o a destra fino a raggiungere l'ultima cella con i nuovi valori.

    Il puntatore assume l'aspetto di una croce nera.

    Per fare in modo che Excel sia in grado di lavorare correttamente su un intervallo contenente valori numerici, è necessario istruirlo digitando in più celle i valori affinché sia chiara la sequenza, poi selezionarle e procedere con l'operazione di compilazione automatica dei valori delle altre.

    Nota: Se viene selezionata una sola cella contenente un valore numerico, Excel duplicherà il suo valore in tutte quelle che verranno selezionate durante la funzione di riempimento automatico . Se invece la cella contiene il nome di un giorno della settimana o di un mese, allora in quelle successive verranno inseriti i nomi nella giusta sequenza.

    Un altro impiego estremamente interessante di questo strumento consiste nella possibilità di replicare automaticamente formule simili su più righe o colonne ; se ne parlerà diffusamente in seguito, poiché è molto utile avere chiari i concetti di riferimenti relativi e assoluti .

    Riferimenti relativi

    Il riferimento è il sistema mediante il quale è possibile indicare una determinata cella .

    Si compone di due coordinate:

    Il riferimento relativo è il riferimento per eccellenza nei foglio elettronico ed è quello di cui si è trattato finora.

    La sua caratteristica consiste nel poter essere automaticamente trasformato dal software per rendere possibile la copia di una formula su più celle, in modo che non sia necessario digitarle manualmente più volte, soprattutto nei casi di formule ripetitive come accade nelle tabelle .

    Il suo impiego è fortemente intuitivo, ma si può illustrare comunque mediante un esempio:

    =A1-A2

    La cella dove si inserisce questa formula visualizzerà il valore dato dalla sottrazione dei valori delle celle A1 e A2.

    Per avere lo stesso risultato anche per i valori delle celle B1 e B2 - C1 e C2 ecc. si può ricorrere al riepimento automatico.

    Riferimenti assoluti

    L’utilità del riferimento relativo è dunque la possibilità di duplicare una formula adeguandola automaticamente alla nuova posizione.

    Può succedere però che il riferimento a una cella debba essere uguale nelle varie formule, per esempio nel caso in cui si volessero creare nuove colonne dove inserire per ogni filiale il valore percentuale del fatturato trimestrale rispetto al totale dell’anno.

    Nota: La percentuale viene calcolata dividendo il valore di un trimestre per il totale annuale e moltiplicandolo per 100.

    Si comincia creando una nuova colonna alla destra di quelle contenenti i valori di fatturato trimestrale.

     

    Nella cella E6 si può inserire la prima formula:

    =D6/L6*100

    Che cosa accade se si copia la formula della cella E6 per incollarla in G6?
    Si ottiene la seguente formula:

    =F6/N6*100

    Essa utilizza correttamente il riferimento alla cella F6 (che deriva da D6 modificata dallo spostamento a destra di due colonne), ma il riferimento alla cella L6, quella che contiene il totale dell’anno della filiale, non doveva essere modificato, infatti la cella N6 non contiene nulla.

    Quale procedura utilizzare per spiegare al programma che il riferimento L6 non deve essere modificato? Si ricorre al riferimento assoluto .

    Trasformando il riferimento alla cella L6 da relativo ad assoluto nella formula

    =D6/L6*100

    Ogni volta che questa verrà copiata il riferimento non subirà modifiche.

    Per utilizzare un riferimento assoluto al posto di uno relativo si deve far precedere il simbolo del dollaro a entrambe le coordinate.

    Riprendendo l’esempio della formula nella cella E6, essa dovrà essere modificata in:

    =D6/$L$6*100

    A questo punto la formula può essere copiata nelle celle G6, I6 e K6 senza che il riferimento alla L6 subisca modifiche, cosa che invece accade al primo riferimento perché di tipo relativo.

    Per analizzare ancora più a fondo i tipi di riferimenti e il sistema di riempimento automatico o del copia/incolla, si può parlare dei riferimenti misti.

    La formula creata per il calcolo delle percentuali e copiata nelle varie celle della riga 6 è strutturalmente simile a quelle che devono essere inserite nelle righe 7, 8, 9 ed eventualmente 10.

    Non è però possibile sfruttare la tecnica del riempimento, in quanto le nuove formule conterrebbero sempre il riferimento alla cella L6: il riferimento assoluto impone infatti al software di non modificare le coordinate quando la formula viene copiata o spostata.

    Come fare in modo che il riferimento mantenga costante la coordinata della colonna ma non quella della riga?
    Ricorrendo al riferimento misto: basta inserire il carattere $ davanti alla coordinata che non deve subire variazioni.

    Nell’esempio essa è la colonna, quindi la formula E6 assumerà il seguente aspetto:

    =D6/$L6*100

    Ogni volta che la cella E6 verrà copiata e incollata in un’altra posizione, il programma modificherà nel primo riferimento (D6) sia la coordinata di riga sia quella di colonna, mentre per il riferimento $L6 cambierà solo la coordinata della riga in quanto quella della colonna è preceduta dal carattere $.

    In questo modo è possibile scrivere la formula un’unica volta e copiarla dove è necessario, senza intervenire con modifiche manuali.

    Quando utilizzare i riferimenti assoluti e quando invece ricorrere a quelli relativi o misti?

    Molto dipende dall’esperienza, ma è possibile dare un’indicazione di massima:

    Un suggerimento è d’obbligo: l’utilizzo dei riferimenti misti è comodo per coloro che creano spesso fogli elettronici i cui dati assumono forme come quelle dell’esempio, ovvero dove le formule sono tra loro molto simili e la possibilità di utilizzare gli strumenti di copia diventa quindi un’enorme facilitazione.

    Va osservato che il documento dell’esempio poteva essere creato anche ricorrendo esclusivamente ai riferimenti relativi, realizzando manualmente le formule nelle celle E6, G6, I6 e K6 e utilizzando lo strumento di riempimento automatico a livello di colonna, ovvero la cella E6 per creare le formule per E7, E8, E9 e ripetere l’operazione per le altre colonne.

    Lavorare con le funzioni

    Excel, fornisce una grande quantità di funzioni predefinite da inserire facilmente nelle espressioni della formula.

    Due funzioni di uso comune sono

    Funzione somma

    La funzione più utilizzata nei fogli elettronici è SOMMA( ). Essa permette di effettuare la somma di un insieme contiguo di celle semplicemente indicando la prima e l’ultima nell’area da sommare.

    Funzione media

    Trova larga applicazione anche la funzione MEDIA(). Essa richiede come parametro i riferimenti di un'area e provvede a effettuare il calcolo della media aritmetica.

    In Excel la funzione MEDIA() considera solo le celle che nell'intervallo specificato contengono valori numerici, mentre in Lotus 1-2-3 essa calcola anche le celle che non contengono valori numerici.

    La sintassi per scrivere un'espressione che effettui la media fra due o più valori è la seguente :

    =MEDIA(num1;num2;...).

     

    La figura mostra la media dei valori contenuti nella riga A2, F2.

    Se fra i parametri una cella o un riferimento contiene testo, valori logici o celle vuote, tali valori sono ignorati.