Formule Excel

Uno degli elementi essenziali e ragione dell’esistenza di Excel è la possibilità di eseguire dei calcoli. Possiamo affermare, parafrasando lo slogan di un famosissimo spot: “No formulas ? No Excel!”

Esamineremo perciò ora in dettaglio questo importantissimo aspetto.

Eseguiamo subito un semplice esercizio, che provvederemo successivamente a commentare ed approfondire. Vogliamo realizzare una cartella di lavoro contenente un foglio con il prodotto dei numeri da 1 a 10 per 3.

Creiamo una nuova cartella di lavoro ed eliminiamo eventuali fogli in eccesso. Scriviamo in cella A1 il numero 1; A partire da A1, con Ctrl premuto trasciniamo in basso il quadratino di riempimento per ottenere una serie di numeri da 1 a 10;

Selezioniamo l’intervallo B1:B10, digitiamo 3 e premiamo Ctrl+Invio;

In colonna C, nell’intervallo C1:C10 vogliamo fare apparire il prodotto. Attiviamo pertanto la cella C1 e digitiamo il segno “=” (senza virgolette);

Ora premiamo due volte il tasto “Freccia a sinistra”; osservare il contenuto della barra della formula;

Premere il tasto “*” in alto a destra sulla tastiera. L’asterisco è per Excel il simbolo per indicare il prodotto;

La barra della formula appare così:

Premiamo una volta il tasto “Freccia a sinistra”. La barra della formula appare così:

Premere il tasto “Invio”. La formula viene memorizzata nella cella e produce il risultato voluto.

Utilizzando il quadratino di riempimento copiamo ora la cella C1 in basso fino alla cella C10:

Osserviamo ora il nostro lavoro.

Per prima cosa clicchiamo sulla cella C1. Osserviamo il contenuto della barra della formula. Esso ci rivela il vero contenuto della cella! In questo caso esso è costituito da una formula che coinvolge i contenuti delle celle A1 e B1. In cella C1 invece osserviamo il risultato del calcolo, in questo caso il numero 3.

Proviamo ora a sostituire in B1 il numero 3 col numero 5. Immediatamente Excel ricalcolerà la formula in C1 per riflettere i cambiamenti.

Andiamo avanti, ed esaminiamo sulla barra della formula il contenuto della cella C2. Ricordiamo quello che abbiamo appena fatto: abbiamo copiato in questa cella il contenuto della cella C1 (che faceva riferimento a celle della riga 1), ma nonostante questo la cella C2 fa riferimento alle celle delle colonne A e B di riga 2. Si possono fare le stesse osservazioni riguardo alle restanti celle della colonna C.

Questo semplice esperimento ci svela una delle chiavi fondamentali del funzionamento di un foglio di calcolo. I riferimenti alle celle, cioè sono relativi alla posizione in cui la formula si trova.

Per fissare il concetto, possiamo pensare ad una formula come ad un piccolo robot specializzato nel fare una ed una sola azione; in questo caso, prendere il contenuto di due celle collocate in una precisa posizione (due celle a sinistra, una cella a sinistra), e moltiplicarlo. Muovendoci con i tasti “freccia” sul foglio abbiamo infatti “insegnato” al nostro ipotetico robot il lavoro da fare. E’ chiaro che una volta opportunamente “programmato” il nostro robot, ovunque si trovi, eseguirà le operazioni per le quali è stato istruito!

A riprova di ciò, proviamo a copiare (Copia + Incolla) la cella C1 in E12. Nessuna meraviglia che il risultato restituito sia zero! La formula moltiplica il contenuto delle due celle immediatamente a sinistra, che sono vuote. La situazione ovviamente cambia non appena immettiamo un numero in C12 e D12.

Per immettere la nostra formula in C1 abbiamo iniziato digitando il segno “=”. Questo è il carattere che fa capire ad Excel la nostra intenzione di immettere una formula. Se scrivessimo A1*B1 Excel semplicemente scriverebbe nella cella la stessa sequenza di caratteri, senza cercare di interpretarla in alcun modo.

Gli operatori aritmetici

Per moltiplicare i contenuti delle celle abbiamo utilizzato l’operatore prodotto, che in Excel, ma anche negli altri fogli di calcolo si rappresenta con l’asterisco “*”.

Gli altri operatori aritmetici sono:

“/” per la divisione;

“+” per la somma;

“-“ per la differenza

NB: è agevole digitare gli operatori aritmetici se si utilizzano i tasti collocati intorno ai tasti numerici all’estrema destra della tastiera.

Operatori e precedenze

Come si può facilmente immaginare, non tutti i calcoli sono così semplici come quelli sinora fatti.

Prima di proseguire, è necessario approfondire il funzionamento degli operatori aritmetici.

Consideriamo questo semplice calcolo: 10+6/2

In mancanza di regole condivise questo calcolo restituirebbe risultati ambigui:

  • Otterremmo, per esempio, 8 se procedessimo prima alla somma e successivamente al quoziente;
  • Otterremmo 13 se al contrario procedessimo nella maniera corretta ed eseguissimo innanzitutto la divisione.

E’ noto, infatti, che per gli operatori Quoziente (/) e Prodotto (*) hanno la priorità sulla Somma (+) e la Differenza (-).

E’ frequente, peraltro, la necessità di eccezioni a questa regola. Si pensi, ad esempio, alla banale necessità di dividere a metà fra amici il conto al ristorante. Se utilizzassimo il foglio di calcolo dovremmo introdurre una formula del tipo: =(A1+A2+A3+A4)/2

… dove l’intervallo A1:A4 contiene i prezzi delle varie pietanze.Un altro aspetto molto importante merita in questa sede una riflessione. Nell’esempio precedente avremmo egualmente ottenuto il nostro risultato (la tabellina del 3) se avessimo strutturato il nostro lavoro come illustrato qui sotto:

In questo caso Excel eseguirebbe il calcolo correttamente. Infatti le parentesi tonde consentono di cambiare l’ordine di precedenza degli operatori; quindi prima verrebbe fatta la somma e solo successivamente il risultato verrebbe diviso per 2. Senza l’utilizzo delle parentesi, al contrario, la somma delle celle A1,A2 e A3 verrebbe addizionato al risultato di A4/2.

Variabili e costanti

Un altro aspetto molto importante merita in questa sede una riflessione.

Nell’esempio precedente avremmo egualmente ottenuto il nostro risultato (la tabellina del 3) se avessimo strutturato il nostro lavoro come illustrato qui sotto:

In questo caso abbiamo inserito direttamente nella formula la parte costante del calcolo, cioè il numero 3. La cosa è perfettamente lecita, ma comporta degli svantaggi. Se volessimo ottenere la tabellina, per esempio, del 6, dovremmo non più modificare dei valori presenti in alcune celle, ma al contrario modificare una formula, operazione senz’altro più complessa ed alla portata solo di chi conosce Excel. Mentre nel caso precedente abbiamo considerato il numero 3 in colonna B una variabile, e come tale facilmente modificabile per ottenere le tabelline di altri numeri, nell’esempio qui sopra abbiamo considerato il numero 3 una costante che immaginiamo di non dovere modificare mai o molto raramente.

Chiaramente la scelta dipende dalle circostanze. In genere inserire un contenuto in una cella e fare riferimento a quest’ultima nelle formule conferisce al nostro lavoro maggiore elasticità e chiarezza. Diverso è il caso nel quale effettivamente determinati valori siano da considerarsi permanenti ed immutabili. E’ il caso, per esempio, della costante Π (Pi-greco, corrispondente al numero 3,14…..) che proviamo ad utilizzare per il calcolo dell’area del cerchio. Faremo la conoscenza, con l’occasione:

  • con l’operatore “^” – elevamento a potenza;
  • con la funzione pi.greco() che restituisce appunto la costante 3,14………

Riguardo all’operatore “^”, il suo utilizzo è molto semplice. Desiderando per esempio elevare alla quarta potenza il contenuto della cella F6, basterà scrivere la formula “=F6^4”;

Desiderando visualizzare il valore restituito dalla funzione pi.greco(), basterà inserire in una cella “=pi.greco()”.

Utilizziamo ora i concetti appena illustrati per generare un prospetto che ci consenta di calcolare facilmente l’area di un cerchio di cui forniremo il raggio.

Prepariamo innanzitutto un foglio corrispondente all’esempio qui illustrato:

L’utilizzatore immetterà in cella B4 la dimensione del raggio;

Una formula in cella B6 calcolerà l’area sulla base del contenuto dei dati immessi.

Questo esempio illustra bene un altro aspetto estremamente importante nell’utilizzo dei fogli elettronici. Descrizioni chiare mettono in grado di utilizzare facilmente il modello, e consentono inoltre di comprenderne con immediatezza il funzionamento. Si confronti l’esempio sopra illustrato con il seguente:

Ambedue gli esempi calcolano l’area del raggio, ma è evidente quanto più criptico sia il secondo esempio rispetto al primo!

Completiamo quindi il nostro prospetto immettendo in cella B6 la relativa formula, come nell’esempio.

Sinora si è illustrato, per l’immissione delle formule, l’utilizzo dei tasti “freccia”. Questo non è ovviamente il solo modo di procedere. Può essere utilizzato il mouse, cliccando direttamente sulle celle coinvolte nel calcolo, o può essere anche direttamente digitato l’indirizzo delle celle coinvolte. Tuttavia si consiglia prudenza della digitazione diretta dei riferimenti. In talune circostanze potrebbe essere non facile individuare esattamente le celle da coinvolgere nei calcoli, ed un errore (AA406 al posto di AA604, per esempio…) potrebbe portare comunque ad un risultato, sebbene errato e non facilmente individuabile. Anche per questo è caldamente consigliato, specialmente all’inizio, procedere al collaudo dei nostri modelli immettendo dei valori facilmente calcolabili a mente in modo da verificarne il corretto funzionamento prima dell’effettivo utilizzo.

Le funzioni – rinvio

Nell’esempio appena illustrato si è utilizzata la funzione =pi.greco(). L’illustrazione delle funzioni e della loro sintassi verrà ripresa in seguito.

Riferimenti relativi e misti

Riprendiamo ora l’esempio col quale abbiamo iniziato a trattare le formule, esempio che, com’è noto, ci consente di calcolare la tabellina del numero 3:

Pur ottenendo perfettamente il nostro scopo, notiamo che:

  • il foglio manca completamente di una descrizione che faccia comprendere con immediatezza scopi e funzionamento;
  • la struttura non consente facilmente di ottenere i prodotti di altri numeri. Se desiderassimo, per esempio, ottenere la tabellina del 7 dovremmo procedere a 10 sostituzioni del numero 3 col numero 7 in colonna B. Ovviamente i problemi si moltiplicherebbero se dovessimo agire su un numero più elevato di righe.

Vediamo ora una possibile soluzione:

In questo caso tutto è più chiaro e razionale. L’utilizzatore, semplicemente digitando un numero in cella C2 potrà ottenere, grazie alle formule contenute a partire dalla cella C4, il calcolo desiderato.

Occupiamoci ora delle formule che dovranno essere introdotte, ragionando come sempre in termini di posizione delle celle da coinvolgere nelle formule.

Consideriamo la formula che dovrà essere immessa in C4.

Essa, in termini di posizione:

  • Dovrà leggere il valore della cella che si trova una cella a sinistra;
  • Dovrà leggere il valore della cella che si trova due celle più in alto;
  • Moltiplicare questi due valori.

La formula risultante da quanto sopra illustrato quindi risulterà: =B4*C2

Ora consideriamo, sempre in termini di posizione, la formula in C4 una volta che essa venga copiata in C5. Ricordiamo ancora cosa fa la formula in C4: prende il contenuto di una cella a sinistra e lo moltiplica per il contenuto di due celle sopra di essa, così come illustrato nella figura qui sopra.

La formula in C4 copiata in C5 applicherà inevitabilmente la stessa logica:

Non meraviglia, pertanto, che il risultato restituito dalla formula sia zero!

In questo caso, il meraviglioso concetto di relatività delle formule rappresenta un handicap particolarmente grave, poiché non ci consente di strutturare il nostro lavoro come vorremmo.

Fortunatamente, come era del resto prevedibile, una soluzione c’è.

Per individuarla, consideriamo cosa effettivamente non funziona in questa situazione.

Riconsideriamo analiticamente cosa fa la formula:

  • prende il contenuto della cella alla sua sinistra: questo va bene…
  • prende il contenuto di due celle sopra: questo non va bene, perché dovrebbe essere preso sempre il contenuto della cella in riga 2!

Fatta la diagnosi, consideriamo il rimedio.

Dobbiamo chiedere ad Excel di considerare non più relativo (2 righe sopra) ma fisso il riferimento alla riga 2.

Per ottenere lo scopo basterà anteporre il simbolo “$” alla parte dell’indirizzo di cella che vogliamo mantenere fisso. La formula corretta, quindi, apparirà così:

E’ facile constatare l’efficacia di questa modifica, andando a consultare le relative formule una volta copiate nelle restanti celle della colonna C.

Riferimenti assoluti

Consideriamo un ulteriore esempio. Vogliamo strutturare un foglio come nell’esempio qui illustrato, sempre allo scopo di ottenere le tabelline:

Dovremo introdurre una formula idonea a produrre il risultato voluto in tutte le celle evidenziate.

Analizziamo come di consueto la formula indagando sulle posizioni delle celle da coinvolgere nel calcolo.

Iniziamo dalla cella C5:

  • Prendi il contenuto di una cella a sinistra;
  • Moltiplicalo per il contenuto di due celle sopra;
  • Come prima, dovendo copiare C5 nelle celle sottostanti, dovremmo provvedere a rendere fisso il riferimento alla riga 3. Quindi la formula diventa: =B5*C$3 … che tradotto in linguaggio umano suona come:
  • Prendi il contenuto di una cella a sinistra;
  • Moltiplicalo per il contenuto della cella in riga 3 nella stessa colonna in cui si trova la formula. Esaminiamo ora la formula =B5*C$3 copiata nell’intervallo E5:E9:
  • Prendi il contenuto di una cella a sinistra (questo va bene…)
  • Moltiplicalo per il contenuto della cella in riga 3 nella stessa colonna in cui si trova la formula. Questo non va bene! Infatti la formula punterebbe alla cella E3 e non alla cella C3. Il riferimento alla riga va bene ($3!), quello che non va è il riferimento alla colonna. Dovremo quindi, in questo caso, rendere fisso anche il riferimento alla colonna C. La formula da introdurre in C5 sarà quindi: =B5*$C$3

Procediamo ad introdurre la formula. Con l’occasione si sperimenterà un metodo veloce per introdurre i riferimenti fissi a celle, righe o colonne.

Per prima cosa, procedere alla selezione multipla delle celle come in figura precedente. Una qualsiasi cella dell’intervallo può avere lo stato attivo. Si farà riferimento nell’esempio all’introduzione della formula inizialmente in cella C5.

Introduciamo la formula:

  • Digitare “=”;
  • Premere una volta il tasto “freccia a sinistra” e premere il tasto “*” (la formula diventa “=B5*”);
  • Cliccare sulla cella C3 (la formula diventa “=B5*C3”);
  • Premere il tasto funzionale F4; il tasto funzionale F4 ci consente di introdurre ciclicamente i riferimenti fissi per l’intera cella, la colonna e la riga. Provare a premere ripetutamente F4 per sperimentare; poi controllare che il riferimento corrisponda a $C$3;
  • Premere Ctrl+Invio per introdurre la formula in tutte le celle selezionate.

Ora, dopo avere esaminato varie casistiche relative ai riferimenti nelle formule, diamo alcune definizioni, con riferimento ad un qualsiasi indirizzo di cella, nel nostro caso F6:

  • F6 prende il nome di riferimento relativo perché la formula che punta a questa cella si riferisce non tanto alla cella F6 ma ad una cella che si trova distante n righe ed m colonne dalla cella contente la formula;
  • F$6 e $F6 prende il nome di riferimento misto, perché il riferimento preceduto dal simbolo “$” punta sempre alla riga ($6) o alla colonna($F), mentre la restante parte dell’indirizzo si comporta come un indirizzo relativo;
  • $F$6 prende il nome di riferimento assoluto, perché la formula che lo utilizza punterà, ovunque venga copiata/spostata, sempre alla stessa cella (nel nostro caso, F6).

Gli esempi tramite i quali si sono illustrate le formule sono volutamente semplici. Si consiglia di familiarizzare con i concetti appresi ripetendo più volte gli esercizi ed i relativi ragionamenti applicabili prima di proseguire.

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 *