Excel, Informatica di Base

• RIFERIMENTI RELATIVI E ASSOLUTI

Negli esempi mostrati in precedenza, sono state utilizzate formule e funzioni contenentiriferimenti relativi alle celle. Questo consente di fare in modo che, in caso di

trascinamento della formula o della funzione, i riferimenti siano automaticamente traslati. Tale comportamento può non essere auspicabile quando, trascinando una

formula, si vuole mantenere fisso il riferimento a una o più celle. In questi casi, è necessario utilizzare nella formula o funzione un riferimento assoluto alle celle per

fare in modo che, durante l’operazione di trascinamento, tale riferimento resti invariato e non subisca traslazione. Un riferimento assoluto è creato anteponendo il

carattere $ alla coordinata della cella che deve restare fissa durante l’operazione di trascinamento. Nell’inserire le coordinate di una cella, è possibile combinare l’uso di

riferimenti relativi e assoluti.

Supponiamo di voler inserire in una formula o funzione il riferimento alla cella B5. Abbiamo quattro possibilità.

• Inserire il riferimento B5. Il riferimento è completamente relativo, sia il riferimento alla colonna sia quello alla riga scorrono in caso di trascinamento.

• Inserire il riferimento $B5. Il riferimento alla colonna è assoluto ($B) e resta fisso, il riferimento alla riga è relativo (5) e scorre in caso di trascinamento.

• Inserire il riferimento B$5. Il riferimento alla colonna è relativo (B) e scorre, il riferimento alla riga è assoluto ($5) e resta fisso in caso di trascinamento.

• Inserire il riferimento $B$5. Il riferimento è completamente assoluto, sia il riferimento alla colonna sia quello alla riga restano fissi in caso di

trascinamento.

• ESEMPI

Consideriamo l’esempio della Figura 1.5. Supponiamo che, in base a una disposizione interna, il centro di analisi riceva ogni mese un premio pari a una

percentuale fissata rispetto all’importo complessivo delle prestazioni erogate in quel mese da parte di tutti i laboratori. Come mostrato nella Figura 2.3,

inseriamo nella cella B13 la percentuale da utilizzare per il calcolo del premio mensile, e nella cella B14 la formula per calcolare l’importo effettivo del premio.

Figura 2.3 . Riferimenti assoluti alle celle

La formula della cella B14 è la seguente:

= B11 * $B$13

Calcolando la formula, otteniamo che l’importo del premio per il mese di Gennaio è pari a € 73.740. Per calcolare l’importo del premio per i mesi di Febbraio e Marzo

dobbiamo trascinare la formula della cella B14 nelle celle C14 e D14, rispettivamente. Nella formula inserita, i riferimenti alla cella $B$13 sono assoluti. In questo

caso, l’uso dei riferimenti assoluti è necessario poiché trascinando la formula in C14 e D14 vogliamo che i riferimenti alla cella $B$13 restino fissi e non siano traslati.

Al contrario, i riferimenti alla cella B11 sono relativi perché vogliamo che siano traslati e che vadano a considerare gli importi complessivi delle prestazioni per i mesi

di Febbraio e Marzo (celle C11 e D11, rispettivamente).

Come mostrato nella Figura 2.3, l’importo del premio per i mesi di Febbraio e Marzo è pari a € 73.520 e € 88.880, rispettivamente.

• LA FUNZIONE CONDIZIONALE SE

La funzione condizionale SE è una funzione logica che consente di variare il contenuto di una cella in base al verificarsi o meno di una condizione prefissata. La

sintassi della funzione è la seguente:

=SE(test;se_vero;se_falso)

La funzione prevede tre argomenti. Il primo argomento è test che esprime la condizione da verificare. Generalmente, test è costituito da un’operazione di confronto

aritmetico nel quale il contenuto di una cella è comparato con un valore costante o con il contenuto di un’altra cella. Esempi di test sono B11>50 e F4=D7. L’esito

di test è un valore booleano, questo significa che il risultato di test può assumere soltanto due valori: il valore VERO o il valore FALSO. Il secondo

argomento se_vero esprime la formula o funzione che deve essere eseguita nel caso in cui test sia risultato VERO. Viceversa, il terzo argomento se_falso esprime la

formula o funzione che deve essere eseguita quando test produce un risultato FALSO. In pratica, la funzione condizionale SE consente di eseguire la formula o

funzione espressa inse_vero o se_falso in base al valore di verità (VERO o FALSO) della condizione test.

ESEMPI

Con riferimento all’esempio della Figura 2.3, supponiamo che il premio mensile da destinare al centro di analisi sia pari al 20% dell’importo complessivo delle

prestazioni (premio minimo) se queste sono inferiori a € 400.000, e sia pari al 30% (premio massimo) in caso contrario. In questo caso, per calcolare l’importo del premio

per il mese di Gennaio, dovremo utilizzare la funzione SE inserendo nella cella B14 la seguente formula:

=SE(B11<400000;B11*20%;B11*30%)

Per calcolare gli importi del premio per i mesi di Febbraio e Marzo è necessario trascinare la formula nella cella B14 nelle celle C14 e D14, rispettivamente. Sia la soglia

di € 400.000 sia le percentuali del 20% e 30% possono subire variazioni nel tempo a seguito di cambiamenti nella politica di assegnazione dei premi. Per rendere il

nostro esempio più flessibile rispetto a questo genere di modifiche, consideriamo l’esempio della Figura 2.4.

Figura 2.4 . Esempio di funzione condizionale SE

In questo esempio, la soglia e le percentuali per il calcolo del premio sono inserite rispettivamente nelle celle B13, B14 e B15. La formula nella cella B16 che calcola

l’importo del premio è la seguente:

=SE(B11<$B$13;B11*$B$14;B11*$B$15)

In caso di variazioni alla soglia o alle percentuali, è sufficiente modificare il contenuto delle celle B13, B14 e B15 per aggiornare l’importo dei premi mensili. Si noti

l’uso di riferimenti assoluti per consentire il corretto trascinamento della formula nelle celle C14 e D14.

F3. FUNZIONI STATISTICHE IN EXCEL

• LE FUNZIONI STATISTICHE

Una categoria di funzioni Excel particolarmente utile è quella delle funzioni statistiche. Essa contiene numerose funzioni di utilizzo molto frequente sia per utenti

generici sia per utenti esperti con competenze professionali in materia statistica. Ad esempio, le funzioni che permettono di eseguire conteggi e calcolare i principali

indicatori statistici, come media, massimo e minimo, risultano utili in numerosi contesti, ogni qualvolta si rende necessario derivare dati di sintesi a partire da una

certa serie dati. Si pensi a un ciclo di esperimenti medici in laboratorio, dove si vuole calcolare e mettere in evidenza il comportamento medio e i picchi positivi/negativi

di una serie consecutiva di osservazioni. Inoltre, le funzioni statistiche di Excel consentono di supportare l’utente in analisi statistiche pi ù avanzate quali modelli di

regressione, analisi della varianza, test di ipotesi, stima di parametri e calcolo di intervalli di confidenza. Si pensi alla divisione marketing di una azienda di vendite

nella quale l’analisi dei dati è fondamentale per eseguire valutazioni e stimare le linee di tendenza e le previsioni di crescita. Nel seguito, saranno discusse e illustrate le

funzioni statistiche di Excel di utilità generale. Per una trattazione più approfondita degli strumenti di analisi statistica nei fogli di calcolo si rimanda a

pubblicazioni specializzate (ad esempio, M.R. Middleton, “Analisi statistica con Excel”, Apogeo, 2004).

• FUNZIONI PER DETERMINARE MAX E MIN DI UNA SERIE DI DATI

Le funzioni statistiche MAX e MIN restituiscono rispettivamente il massimo e minimo dei valori contenuti in un insieme di celle considerato. La sintassi della

funzione MAX è la seguente:

=MAX(num1;num2;…)

Nella formula, num1;num2;… sono i riferimenti alle celle contenenti i valori da considerare. Il risultato della funzione MAX è il valore numerico massimo tra quelli

contenuti nelle celle passate alla funzione MAX come argomento (num1;num2;…). In alternativa, è possibile utilizzare una sintassi più sintetica in cui la funzione

MAX riceve un unico argomento num1 che rappresenta un intervallo di celle. In questo caso, i valori numerici contenuti nelle celle dell’intervallo specificato saranno

considerati nella determinazione del valore massimo.

• ESEMPIO CN LE FUNZIONI MAX E MIN

Consideriamo l’esempio della Figura 3.1 nel quale sono mostrati i dati delle rilevazioni relative a tre test di laboratorio nel periodo dal 01/09/2010 al 15/09/2010.

Figura 3.1. Esempio di funzione statistica MAX

Per ognuno dei tre test vogliamo calcolare mediante la funzione MAX il valore massimo rilevato nei giorni in cui è stata eseguita l’osservazione (1­15/09/2010). Per

quanto riguarda Test1, i dati rilevati sono contenuti nelle celle B4:B18. Come mostrato nella barra della formula della Figura 3.1, la funzione MAX inserita nella

cella B20 è la seguente:

=MAX(B4:B18)

Il risultato è il valore 32. Per calcolare il valore massimo relativo a Test2 e Test3, copiamo mediante trascinamento la funzione in B20 nelle celle C20 e D20,

rispettivamente. Osserviamo che i valori massimi delle rilevazioni relative a Test2 e Test3 sono 35.8 e 33.9, rispettivamente.

La funzione MIN ha sintassi identica a quella della funzione MAX. Nell’esempio della Figura 3.1, i valori minimi rilevati per ciascuno dei tre test sono calcolati

mediante la funzione MIN e i risultati sono contenuti nelle celle B21:D21.

Nella determinazione del valore massimo e minimo, si tenga presente che MAX e MIN considerano soltanto valori numerici, ignorando i valori logici o testuali

eventualmente contenuti nell’intervallo di celle considerato. Nell’esempio della Figura 3.1, notiamo che alcuni valori di rilevazione non sono disponibili (celle C12 e

C13) o non sono state inseriti (cella C8). Questo non è un problema per il calcolo delle funzioni MAX e MIN che semplicemente ignorano il contenuto di queste celle.

• FUNZIONI PER IL CALCOLO DI MEDIE

Le funzioni statistica MEDIA restituisce la media aritmetica dei valori contenuti in un insieme di celle considerato. La sintassi della funzione MEDIA è la

seguente:

=MEDIA(num1;num2;…)

Nella formula, num1;num2;… sono i riferimenti alle celle contenenti i valori da considerare. In alternativa, è possibile utilizzare una sintassi più sintetica in cui la

funzione MEDIA riceve un unico argomento num1 che rappresenta un intervallo di celle. In questo caso, i valori numerici contenuti nelle celle dell’intervallo

specificato saranno considerati nella determinazione del valore di media.

Nell’esempio della Figura 3.1, utilizziamo la funzione MEDIA per calcolare la media aritmetica delle rilevazioni osservate sui tre test considerati. Per Test1, la

funzione MEDIA è inserita nella cella B22 come segue:

=MEDIA(B4:B18)

Il risultato è il valore 30.32. Per calcolare la media aritmetica relativa a Test2 e Test3, copiamo mediante trascinamento la funzione in B22 nelle celle C22 e D22,<