Riferimenti di cella in Excel

     

Un riferimento identifica una cella o un intervallo di celle in un foglio di lavoro e viene usato per la ricerca di valori che si vuole includere in una formula.
In virtù dei riferimenti è possibile usare in una sola formula i dati contenuti in diverse regioni di un foglio di calcolo, oppure usare il valore di un’unica cella in più formule. Inoltre è possibile fare riferimento a celle di altri fogli della stessa cartella di lavoro, ad altre cartelle di lavoro e a dati presenti in altri programmi.
I riferimenti a celle in altre cartelle di lavoro (files) sono chiamati riferimenti esterni e i riferimenti ai dati di altri programmi sono chiamati riferimenti remoti.

In base all’impostazione predefinita, in Excel viene usato lo stile di riferimento RC (riga, colonna) in cui le colonne sono identificate da lettere ( da A a IV) per un totale di 256 colonne e le righe sono identificate da numeri (da 1 a 65536).
Queste lettere e numeri costituiscono le intestazioni di riga e di colonna. Per far riferimento ad una cella, si immette la lettera della colonna seguita dal numero di riga. Ad esempio B45 si riferisce all’intersezione tra la colonna B e la riga 45.

Per far riferimento ad un intervallo di di celle si immette il riferimento alla cella posta nell’angolo superiore sinistro dell’intervallo; il segno dei due punti( : ) quindi il riferimento della cella posta nell’angolo inferiore destro dell’intervallo.

Riferimento	Notazione
Cella intersezione colonna A riga 12	A12
Intervallo di celle delimitato dalla colonna B e dalle righe da 12 a 23	B12:B23
Intervallo di celle delimitato dalla riga 15 e dalle colonne da B ad E	B15:E15
Tutte le celle della riga 5	5:5
Tutte le celle delle righe da 7 a 10	7:10
Tutte le celle della colonna H	H:H
Tutte le celle delle colonne dalla H alla J	H:J
Intervallo di celle delimitato dalle colonne da A ad E e dalle righe da 12 a 23	A12:E23
Intervallo di celle disgiunte A1, C3 e G7	A1;C3;G7

Operatori aritmetici

     

I riferimenti vengono spesso utilizzati nella formazione di formule .
Una formula è un insieme di costanti unite da operatori aritmetici.
Una formula i Excel inizia con = (uguale) ad esempio:
=(A3*5*B1+C12/2)
è una formula che inserisce nella cella il risultato delle operazioni contenute in essa; i principali operatori aritmetici sono i seguenti

Ricordiamo che una formula in Excel comincia sempre col simbolo = (uguale) e può contenere costanti, riferimenti di celle ed intervalli e funzioni.

Quando in una cella viene introdotta una formula che contiene i riferimenti, se qualche valore contenuto nei riferimenti cambia, il programma ricalcola la formula e aggiorna i valori nelle celle chela contengono.

Riferimenti relativi e riferimenti assoluti

     

I riferimenti possono essere assoluti o relativi. I riferimenti relativi identificano una cella in base alla sua collocazione in una colonna e in una riga. Ad esempio l'indirizzo C5 identifica la cella posta all'incrocio tra la colonna C e la riga 5.
Quando si copia una formula che utilizza dei riferimenti relativi, i riferimenti della formula vengono modificati automaticamente per adeguarsi alla nuova posizione della formula stessa.

In alcune situazioni è necessario che i riferimenti di una o più celle di una formula non subiscano variazioni.
Se si vuole quindi che i riferimenti non vengano adeguati durante la copia di una formula in altre celle, si usano i riferimenti assoluti.

Per inserire l'indicazione di riferimento assoluto occorre immettere davanti alla lettera e al numero della cella il simbolo del dollaro $.

● Digitare direttamente il simbolo $ prima della lettera della colonna e del numero di riga della cella.
● Premere il tasto F4.

Esiste anche il riferimento misto: esso viene contrassegnato dal simbolo $ prima della lettera corrispondente alla colonna oppure del numero corrispondente alla riga.

Ad esempio =A3*$B5 la colonna B rimane costante anche cambiando la posizione della formula stessa mentre può variare il numero di cella della riga.

Per comprendere meglio questi meccanismi facciamo l'ipotesi di essere proprietari di una azienda che si avvale dell'operato di quattro lavoratori.
Questi lavoratori eseguono per noi alcuni semplici lavoretti part-time (ad es. volantinaggio) ; per loro dobbiamo redigere un resoconto settimanale che restituisca il compenso retributivo in funzione delle ore lavorate e del loro costo orario.
Con loro abbiamo stipulato contratti separati, questa è la ragione per la quale ciascuno di loro ha un costo orario diverso.
Un prospetto del genere potrebbe apparire come segue.

Il compenso settimanale viene restituito in colonna D. Come è ovvio questo compenso è dato dal prodotto del costo orario per le ore lavorate.
Per il primo lavoratore in cella D2 inseriremo la formula
=B2*C2
se possibile cliccando direttamente sulle celle interessate; perchè il tentativo di inserire le coordinate digitandole da tastiera induce più facilmente la presenza di errori.
Ora possiamo selezionare la cella D2, copiarne il contenuto (CTRL-C) e incollarlo (CTRL-V) sulle altre celle dove è richiesto il risultato. In alternativa avviciniamo il cursore del mouse all'angolo inferiore destro della cella D2 e quando il puntatore diventa a forma di croce piccola premere il pulsante sinistro del mouse trascinandolo verso il basso portandolo sulle celle rimanenti.

Il trascinamento automatico rappresenta una prerogativa tipica di Excel ( ma anche di altri spreadsheet come Libre Office) . Esso può essere considerato un'operazione di copia/incolla eseguita in un unico atto, invece che in due battute separate come descritto prima.
Il risultato che si ottiene è il seguente:

Selezionando la cella D3 notiamo che la formula inserita è corretta e mette in relazione le due celle a sinistra relative a quella attuale, dove è stata inserita la formula).
Esplorando le formule contenute nelle celle seguenti notiamo che questo criterio è stato mantenuto anche per loro.

cioè, relativamente la cella attuale (della formula) è stato moltiplicato il contenuto delle due celle adiacenti a sinistra: così funziona un riferimento relativo.

Questo modello di operatività va avanti fino ad un certo punto. Poi i lavoratori fanno sciopero perché vogliono un aumento e vogliono essere pagati tutti la stessa cifra.
Dopo una animata contrattazione si conviene che tutti i lavoranti verranno pagati 7 euro l'ora; di conseguenza anche il modello del foglio ore deve cambiare e si decide di usare il prospetto seguente:

L'unico costo orario (valido per tutti) si trova in cella B1. Se volessimo calcolare il compenso per ciascuno applicheremo istintivamente il metodo dei riferimenti relativi precedente, scrivendo in cella C3 la formula =B3*B1 trascinando la formula sulle celle seguenti in basso.
L'effetto, sappiamo già che consiste nell'ottenere il seguente insieme di formule.

Questo pare non funzionare come si vede dal risultato.

Ci rendiamo conto che se nella prima cella (C3) inseriamo il prodotto tra la cella adiacente a sinistra e la cella a sinistra in alto di due righe questo sistema di riferimenti verrà ereditato anche dalle celle successive dove verrà copiata la formula.

Questa azione induce degli evidenti errori di calcolo (e di senso). Ci rendiamo conto che durante il trascinamento o il copia/incolla il riferimento alla cella B1 del costo orario deve rimanere costate; cioè deve essere un riferimento assoluto.

Abbiamo detto che per usare un riferimento assoluto inserendo davanti alla lettera e al numero della cella il simbolo del dollaro $.

Questo si ottiene all'inserimento della prima formula cliccando sul riferimento B1 e col cursore adiacente a questo indirizzo appena inserito premendo il tasto di funzione F4.
I calcoli che verranno eseguiti saranno allora esatti.

Esplorando il contenuto delle celle in cui abbiamo eseguito il calcolo notiamo le formule seguenti.

Si capisce che mentre le celle adiacenti a sinistra sono relazionate in modo relativo rispetto la cella attuale dove è inserita la formula l'indirizzo della cella B1 del costo orario non cambia

indipendentemente da dove verrà copiata la cella iniziale.

Si nota, in quest'ultimo caso, come per realizzare il nuovo schema abbiamo dovuto rifare il foglio di calcolo, stravolgendo quello che era il layout iniziale.
Per evitare questa faticaccia potevamo semplicemente adottare un riferimento misto assoluto di riga e relativo di colonna.

Sul layout iniziale potevamo lasciare solo il primo dato sul costo orario come si vede nell'immagine seguente.

In cella D2 inseriamo la formula
=B$2*C2
trascinando la cella D2 verso il basso oppure copiando e incollando il contenuto della cella avremmo potuto trovare ugualmente il totale dell'importo da pagare.

Le formule che verranno inserite sono qui di seguito riportate.

Anche questo metodo ha il vantaggio che modificando l'unica cella del costo orario i totali vengono istantaneamente ricalcolati.

In linea di massima possiamo dire che i riferimenti misti si adattano bene quando è la cella in cui si inserisce la formula deve essere trascinata (copiata e incollata) solo in direzione orizzontale o verticale.

Il riferimento assoluto si può adottare quando la cella da copiare deve essere trascinata sia in senso verticale che in senso orizzontale oppure quando la cella che deve essere copiata è disgiunta dall'intervallo di celle risultato che si intende calcolare (come si è visto nel nostro esempio).

Nomi e barra dei nomi

     

Le formule citate prima vanno sempre inserite nella barra della formula. In tutte le versioni di Excel esistono due zone dell’interfaccia grafica che nel corso del tempo non hanno mai subito modifiche, si tratta della barra dei nomi e della barra della formula che si trovano sempre sopra il foglio di calcolo.

La barra dei nomi è una cella che rappresenta sempre le coordinate della cella (RC riga, colonna) su cui si trova il selettore rettangolare del programma. Questa cella ha delle caratteristiche interessanti di lettura/scrittura. Tramite essa possono essere ridefiniti gli identificatori delle varie celle. Inseriamo in cella A1 un valore numerico.

Con la cella A1 selezionata digitiamo nella barra dei nomi l'identificatore "prezzo" e subito dopo battiamo invio.
D'ora in poi, quando scriveremo "prezzo" nelle formule del foglio, il programma farà riferimento alla cella A1.
Inseriamo poi una percentuale in cella A2.

Col selettore posizionato sulla cella A2, inseriamo nella barra dei nomi l'identificatore "percentuale" e battiamo invio.
D'ora in poi, quando scriveremo "percentuale" nelle formule del foglio, il programma farà riferimento alla cella A2.
Selezioniamo la cella A3 e nella barra della formula scriviamo:
=prezzo*percentuale

corrispondentemente nella barra dei nomi inseriamo l'identificatore "sconto" e battiamo invio.
D'ora in poi, quando scriveremo "sconto" nelle formule del foglio, il programma prenderà in considerazione la cella A3.

Selezioniamo la cella A4 e nella barra della formula scriviamo:
= prezzo-sconto

ottenendo in questo modo il valore finale del prezzo scontato.

Dobbiamo tener conto di questo comportamento del programma quando si inseriscono nel foglio dei nomi chiamabili. Attribuire un nome ad una cella costituisce di fatto alla definizione di una variabile. Se ad una cella viene attribuito un nome non potrà essere usata come cella iniziale di una serie di riferimenti relativi ottenuti da un trascinamento o in una operazione di copia/incolla dove deve esserci una relazionalità relativa tra i riferimenti.

Attraverso la barra dei nomi non solo si definiscono identificatori per singole celle del foglio di calcolo ma si possono definire interi intervalli di celle .

che poi possono essere elaborati da funzioni e formule.

Ricordiamo che la funzionalità "Definisci nome" può essere richiamata tramite la combinazione di tasti CTRL-F3.

attraverso questa finestra di dialogo è possibile aggiungere ed eliminare nomi dallla cartella in uso.