Funzioni Excel

Immaginiamo, a titolo esemplificativo:

  • di dover calcolare la somma di qualche migliaio di numeri;
  • di dover calcolare la media aritmetica di qualche migliaio di numeri;
  • di dover calcolare la rata di estinzione di un prestito dato un certo tasso, una durata e un capitale;
  • di dover trovare il valore minimo in un intervallo di qualche migliaio di numeri;
  • di dover calcolare la differenza in giorni lavorativi fra due date;
  • ecc…

Sicuramente, con quanto sinora appreso siamo in grado di risolvere la maggior parte dei compiti. Talune altre volte, pur conoscendo il nostro obiettivo, potremmo non possedere le conoscenze necessarie per impostarlo come calcolo.

Il problema, quindi, sta sia nella quantità di dati da elaborare che nella complessità dei calcoli stessi.

L’uso delle funzioni consente di superare questi problemi. Vediamo subito un esempio, che poi commenteremo:

In H23, desideriamo immettere un criterio di calcolo che ci restituisca la media aritmetica dei numeri contenuti nell’intervallo B1:J21.

Si ricorda che la media aritmetica viene calcolata dividendo la somma dei valori per la loro quantità.

Per esempio, la media dei quattro numeri 9,12,4,7 è uguale a 8 in quanto: (9+12+4+7) = 32 32/4 = 8

Potremmo senz’altro procedere con una formula, ma al prezzo di una notevole perdita di tempo. Possiamo in alternativa utilizzare una funzione.

La funzione =media() è in grado di calcolare la media aritmetica di un insieme di numeri. Vediamola in azione:

  • In cella H23 digitiamo (senza virgolette) “=media(“:
  • Col mouse selezioniamo ora la cella B1 e trasciniamo fino a J21;
  • Digitiamo una parentesi di chiusura in modo da ottenere: =media(B1:J21) e premiamo “Invio”.

La cella ora calcolerà la media aritmetica dei valori dell’area selezionata. Come al solito la formula ricalcolerà immediatamente la media non appena venga apportata una variazione ad uno o più valori contenuti nell’intervallo.

Per l’esempio abbiamo introdotto la funzione =media() che appartiene alla categoria delle funzioni incorporate di tipo statistico.

Categorie

Excel include un elevatissimo numero di funzioni, adatte agli usi più disparati:

  • Funzioni finanziarie
  • Funzioni di data ed ora;
  • Funzioni matematiche e trigonometriche;
  • Funzioni statistiche;
  • Funzioni di ricerca e riferimento;
  • Funzioni di gestione di database;
  • Funzioni logiche;
  • Funzioni informative;
  • Funzioni definite dall’utente o da terzi.

Per l’utilizzo delle funzioni è indispensabile la comprensione della loro struttura. A ciò verranno dedicati i prossimi paragrafi.

Struttura di una funzione

  • Una funzione è definita dalla sua parola chiave, che può essere digitata in maiuscolo o minuscolo o combinazioni varie di caratteri.
  • La parola chiave è immediatamente seguita, senza spazi, da un segno di parentesi aperta e di parentesi chiusa;
  • All’interno delle parentesi possono essere presenti uno o più argomenti. Gli argomenti, così come l’ordine in cui devono essere collocati sono predefiniti e non è possibile variarne la tipologia o la posizione. Ogni argomento è separato dagli altri da un segno di punto e virgola “;” senza spazi.

Vediamo alcuni esempi, con riferimento alla funzione appena utilizzata:

  • =MEDIA(<intervallo>), dove intervallo è la definizione di un’area del tipo, ad esempio, A1:N46);
  • =MEDIA(7;8;6): calcola la media degli argomenti 7,8 e 6. Come si può notare, ogni argomento, in questo caso un numero, è separato da “;”)
  • =MEDIA(A1:B4;C7:H9;F21:J44): in questo caso gli argomenti della funzione sono costituiti da tre aree, risultato di una selezione multipla, delle quali verrà restituita la media;
  • =MEDIA(1560;A3:B18). Un argomento è un numero immesso direttamente nella funzione, l’altro è costituito da una zona.

La funzione media richiede obbligatoriamente almeno uno e fino ad un massimo di 30 argomenti.

Esistono funzioni che al contrario non hanno argomenti, ma richiedono comunque la presenza, subito dopo il nome della funzione, di una coppia di parentesi.

Vediamone alcune:

=Oggi(): restituisce la data odierna (memorizzata nell’orologio interno del computer);

=Casuale(): restituisce un numero casuale nell’intervallo 0 – 1;

=Pi.greco(): abbiamo già utilizzato questa funzione…

=Rif.riga(): restituisce il numero di riga della cella in cui viene memorizzata ecc..

Esaminiamo ora alcune altre funzioni.

La prima è senz’altro la più utilizzata, e consente di sommare i numeri in uno o più intervalli. La sintassi è analoga a quella della funzione Media(): =Somma(intervallo).

Vediamo un caso tipico di utilizzo di questa funzione

Apriamo il file “Rendiconto settimanale.xls” precedentemente salvato.

In ogni foglio settimanale desideriamo aggiungere il calcolo della somma delle spese di ogni giornata e di ogni tipologia.

Iniziamo, per rendere più verosimile l’esercizio, ad inserire nel foglio “Settimana 1” un po’ di spese:

Integreremo ora i nostri fogli settimanali aggiungendo una riga ed una colonna denominata “Totali”:

A tal fine:

  • provvederemo innanzitutto a raggruppare i fogli da Settimana 1 a Settimana 4, in modo da scrivere contemporaneamente su tutti e quattro i fogli le modifiche;
  • provvederemo ad introdurre la funzione somma per calcolare i totali delle righe e delle colonne.

Procediamo dunque, dopo aver reso attivo il foglio Settimana 1, a raggruppare i fogli settimanali. Ciò fatto, occupiamoci dell’introduzione della funzione somma. Iniziamo dal calcolo dei totali di riga a partire dalla cella G3:

  • Digitiamo “=somma(”;
  • Evidenziamo l’intervallo F3:B3;
  • Chiudiamo la parentesi digitando “)”;
  • Premiamo “Invio”.

Quanto ai riferimenti, anche utilizzando le funzioni vengono applicati i concetti illustrati a proposito delle formule. Ciò significa, dunque, che la funzione inserita in G3 potrà essere copiata nelle restanti celle della colonna. Ma si raccomanda, per esercizio, di immettere manualmente le funzioni anche per le restanti righe.

Utilizzeremo invece un metodo più veloce in occasione dell’immissione dei totali delle colonne.

Procediamo ora all’inserimento delle somme delle colonne. Si accennava al fatto che la funzione somma è statisticamente la più usata dagli utenti di Excel.

E’ stata messa a disposizione pertanto una valida scorciatoia, presente sulla barra degli strumenti standard (pulsante “Somma automatica”):

Il pulsante Somma automatica, in realtà, consente di introdurre comodamente anche altre funzioni. Si provi a cliccare sulla freccia accanto al simbolo per aprire la lista a discesa.

Oltre alla funzione Somma troveremo la funzione Media già esaminata in precedenza, più ulteriori funzioni. Per difetto, comunque, alla pressione del pulsante è associata l’introduzione di una funzione Somma.

Proviamo ad utilizzare il pulsante Somma nella cella B9. Resa attiva B9 clicchiamo sul pulsante:

Come si può notare, Excel compone interamente la funzione Somma, e tenta di definire l’intervallo su cui calcolarla. In questo caso, l’intervallo non è quello desiderato.

Sarà pertanto necessario ridefinirlo selezionando l’intervallo corretto:

Introdotta la funzione col tasto “Invio”, potremo copiarla nelle celle adiacenti mediante il quadratino di riempimento.

NB: avremmo anche potuto procedere in questo modo:

  • Preselezionare l’intervallo B9:G9
  • Introdurre come sopra la funzione Somma();
  • Premere Ctrl+Invio per memorizzarla in tutte le celle selezionate.

A questo punto provvediamo ad eliminare il raggruppamento precedentemente impostato e a salvare il lavoro.

Il nostro modello però non è ancora del tutto pronto. Avevamo infatti inserito un foglio riepilogativo per calcolare i totali delle spese per categoria delle quattro settimane. Dobbiamo ora inserire in questo foglio le formule necessarie. Innanzitutto attiviamo il foglio “Riepilogo” ed inseriamo in cella B2 la voce descrittiva “Totale” ed allarghiamo un po’ le colonne A e B;

Clicchiamo quindi sulla cella B3. Essa, al pari delle altre celle della colonna, dovrà riportare la somma delle celle di ogni settimana riportanti il totale della categoria. Dovremmo pertanto scrivere una funzione Somma() che riporti il totale delle celle corrispondenti di ogni foglio.

Premiamo il pulsante Somma Automatica. Excel, non trovando nulla da sommare introduce una funzione senza argomenti:

Attiviamo il foglio Settimana 1, e qui la cella riportante il totale Vitto (G3):

Tenendo premuto il tasto “Maiuscole” cliccare sulla linguetta “Settimana 4” e premere “Invio”.

Immissione di funzioni tramite wizard

Sinora l’immissione delle funzioni è avvenuta con digitazione diretta o con l’ausilio del pulsante “Somma automatica”. Questa è senz’altro la modalità più veloce se si conosce il nome della funzione ed i sui parametri, peraltro suggeriti da Excel una volta iniziata la digitazione del nome della funzione:

Excel offre però anche la possibilità di immettere le funzioni in forma assistita. Ciò è particolarmente utile se non si conosce il nome esatto della funzione o la sua sintassi.

Vediamo come funziona tutto ciò ripetendo l’esempio prima illustrato per il calcolo della media aritmetica:

Questa volta non digitiamo direttamente la funzione, ma ci serviamo del generatore automatico associato al pulsante fx presente sulla barra della formula. Se la barra della formula non fosse presente, attivarla tramite il comando “Barra della formula” del menu “Visualizza” .

Dopo aver reso attiva la cella H23 clicchiamo sul pulsante del generatore:

Questa finestra consente molte possibilità. Excel indica le funzioni più usate di recente;

La casella a discesa consente di ricercare le funzioni in base all’appartenenza ad una specifica categoria:

La casella di testo posta in alto può essere utile a reperire la funzione in base alla sua descrizione:

Sulla base di quanto digitato Excel ci ha restituito un elenco di funzioni potenzialmente idonee. Come si può notare, la funzione evidenziata nell’elenco centrale è la funzione MEDIA. Poco più in basso, appaiono altri utili strumenti:

  • La sintassi della funzione;
  • Una sua sommaria descrizione;
  • Un link alla guida all’utilizzo della funzione.

Dopo aver scelto la funzione ed aver premuto OK Excel ci presenta lo strumento per costruirla passo-passo:

Come si può notare, Excel cercando di interpretare le nostre possibili scelte ha selezionato un intervallo, seppure errato, ed ha composto la funzione che si vede sulla barra della formula. Per tale intervallo, inoltre, fornisce in “Risultato formula” il valore della media (6,222222..). Nella parte alta della finestra di dialogo appaiono le caselle idonee a contenere gli argomenti della funzione.

La prima riporta l’attuale area selezionata in automatico, H13:H22.

Tramite il pulsante di selezione (vedasi figura precedente) possiamo facilmente rettificarne la definizione:

Un clic sul pulsante ci riporterà alla finestra di dialogo:

Ora l’intervallo è corretto. Il risultato della formula riflette il cambiamento. Non dobbiamo aggiungere ulteriori argomenti alla funzione, quindi possiamo cliccare su OK per introdurre il tutto nella cella.

Per esercizio, si consiglia di provare ad utilizzare le funzioni =Min(), =Max(), =Somma(), =Conta.Numeri(), =Media() prima servendosi della modalità assistita e della guida in linea, e poi manualmente.

Anteprima del risultato della funzione Somma( )

Una funzionalità particolarmente utile consente di calcolare il risultato di una somma senza introdurre la relativa formula o funzione. A questo scopo è sufficiente selezionare una o più aree contenenti i numeri da sommare e leggere il risultato sulla barra di stato, come nella seguente illustrazione:

Gli operatori di relazione

Sinora ci siamo serviti degli operatori aritmetici (+, -, /, *) e dell’operatore elevamento a potenza “^”.

  • Excel è in grado di gestire anche altri tipi di operatori, detti operatori di relazione. Gli operatori di relazione servono a operare dei confronti e restituiscono un valore logico “VERO” o “FALSO”.

Gli operatori relazionali sono:

  • Maggiore “>”
  • Minore “<”
  • Uguale “=”
  • Maggiore o uguale: “>=”
  • Minore o uguale: “<”
  • Diverso: “<>”

Vediamoli in azione.

In cella D3 abbiamo scritto una proposizione logica. Abbiamo dichiarato, cioè, che C3 è diverso da B3. Excel conferma restituendo VERO. Proviamo ora ad inserire in B10 il numero 11: la formula restituisce ora il valore logico FALSO.

Si consiglia di sperimentare anche gli altri operatori relazionali variando i contenuti delle celle coinvolte nelle formule.

Gli operatori logici AND e OR

Molto spesso ci troviamo, anche nella vita di ogni giorno, a fare confronti ed a prendere decisioni sulla base di più condizioni.

Per esempio:

  1. Se non piove e non fa freddo, vado a lavorare in bicicletta;
  2. Se è sabato o domenica vado al mare;
  3. Se è sabato o domenica e c’è bel tempo vado al mare.

 

  1. Nel primo esempio l’azione “vado a lavorare in bicicletta” è condizionata dall’avverarsi contemporaneo di due condizioni. Se una delle due condizioni non si verificasse, non andrei a lavorare in bicicletta;
  2. Nel secondo esempio, invece, l’azione “vado al mare” si verificherà se almeno una delle condizioni sarà vera;
  3. Nel terzo esempio, andrò al mare se è vera una delle due condizioni relative al giorno festivo (è sabato o domenica), ma contemporaneamente dovrà essere vera anche l’altra condizione (“c’è bel tempo”).

Per esprimere queste condizioni si usano gli operatori booleani AND (e) ed OR (o).

Dall’osservazione degli esempi fatti possiamo dedurre che:

  • Due relazioni unite da un operatore AND (esempio 1) restituiscono un valore VERO solo se ambedue le condizioni sono verificate;
  • Due relazioni unite da un operatore OR (esempio 2) restituiscono un valore VERO se almeno una delle due condizioni è verificata;
  • Nell’esempio 3, l’azione “vado al mare” si verificherà se almeno una delle due condizioni (è sabato o domenica) è vera e se contemporaneamente c’è bel tempo.

Tavole della verità

Il funzionamento degli operatori logici AND e OR può essere sintetizzato nelle c.d. “tavole della verità”:

AND

OR

Ora vediamo come tutto questo si possa utilizzare in Excel. AND e OR si introducono mediante le funzioni logiche incorporate E( ) ed O( ).

La sintassi delle due funzioni è analoga, pertanto si esaminerà solo la funzione E( ).

=E(relazione1;relazione2;relazione3;….. relazione.n)

Per esempio:

=E(B4>12;S8<>S16)

=E(B4>Somma(A1:A14);S8<>N24)

=E(Media(C1:E44)>Somma(D8:D50);N24+K26>=AA1000;B5>=10)

Si tratta di esempi a complessità crescente. A partire dal secondo esempio si sono nidificate all’interno della funzione E( ) altre funzioni e criteri di calcolo. Ciò è lecito fino a quando i risultati restituiti dalle funzioni e dalle formule sono compatibili con l’argomento richiesto dalla funzione. Si noti che il segno “=”, che com’è noto comunica ad Excel l’introduzione di un criterio di calcolo non è presente nelle funzioni e nelle formule nidificate.

Una guida alla comprensione di funzioni complesse

Potrebbe a prima vista essere di qualche difficoltà l’interpretazione di una funzione complessa come nel terzo esempio. Per una facile comprensione, si consiglia di:

  • Osservare innanzitutto la funzione principale (o “esterna”), in questo caso la funzione E( ) e la sua sintassi;
  • Successivamente sarà facile individuare all’interno, separati dai “;” gli argomenti della funzione;
  • Si passerà successivamente ad esaminare i contenuti di ogni singolo argomento.

Vediamo ora un esempio applicato ad Excel:

In colonna G abbiamo applicato il criterio “a”: Ambedue le condizioni devono verificarsi perché l’allievo venga promosso. Pertanto abbiamo utilizzato la funzione E( );

In colonna H abbiamo applicato il criterio “b” poiché è sufficiente che almeno una delle due condizioni si verifichi per ottenere la promozione, abbiamo utilizzato la funzione O( ).

La funzione SE( )

Dall’applicazione delle funzioni E( ), ed O( ), dall’introduzione di proposizioni logiche del tipo “=A1>B16” abbiamo ottenuto, come risultato, un valore logico VERO o FALSO.

Come si accennava in precedenza, Excel è in grado di esaminare il risultato VERO o FALSO restituito per eseguire una azione predeterminata. Nel caso appena preso in esame, per esempio, piuttosto che far apparire “VERO” o “FALSO” possiamo fare apparire la dicitura, molto più esplicativa, “Promosso” o “Rimandato”. Per ottenere lo scopo viene utilizzata la funzione “Se( )”.

Esaminiamone la sintassi:

=SE(relazione;azione_se_vera;azione_se_falsa)

Per esempio:

=SE(A3>=A4;Somma(B1:B5);Media(B1:B5))

Con riferimento alla sintassi sopra illustrata:

Se la relazione A3>=A4 (relazione) è vera, viene restituita la somma dell’area B1:B5 (azione_se_vera), altrimenti verrà restituita la media dell’area B1:B6 (azione_se_falsa).

Si noti con l’occasione l’uso delle parentesi:

  • Una prima coppia di parentesi è necessaria alla sintassi della funzione più esterna =SE();
  • Una seconda coppia di parentesi è necessaria alla sintassi della funzione nidificata SOMMA();
  • Una terza coppia di parentesi è necessaria alla sintassi della funzione nidificata MEDIA().

Anche Excel agevola l’utilizzatore provvedendo a colorare le parentesi in fase di creazione delle formule e delle funzioni.

Completiamo ora l’esempio relativo ai criteri di promozione visto in precedenza con l’obiettivo di far apparire, in corrispondenza di un risultato “VERO” o “FALSO” dei messaggi del tipo “Promosso” o “Questa volta rimandato”.

Prendiamo ad esempio in considerazione il “criterio a”: ciò che abbiamo scritto in colonna G è esattamente il primo argomento della funzione SE( ), cioè la condizione da verificare. Quindi, nidificando tale condizione nella funzione SE avremo:

=SE(E(MEDIA(B3:E3)>=6;F3>6;

Aggiungeremo ora i due restanti argomenti, separandoli come di consueto con dei “;”. Trattandosi di stringhe, dovremo comunicare ad Excel di non tentarne l’interpretazione. A tal fine racchiuderemo le due stringhe fra virgolette:

=SE(E(MEDIA(B3:E3)>=6;F3>6);”Promosso”;”Questa volta rimandato”)

Per impratichirsi, si consiglia di provare più volte ad introdurre le funzioni secondo gli esempi o secondo altri criteri predeterminati, tenendo sempre presente la sintassi delle funzioni utilizzate. Potrà essere inoltre utile alternare l’introduzione manuale e l’uso del generatore (pulsante fx).

0 commenti

Lascia un Commento

Vuoi partecipare alla discussione?
Fornisci il tuo contributo!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *