Riferimenti Excel

Riferimenti relativi, riferimenti misti, riferimenti assoluti

Autore: Andrea Pacchiarotti
Ultimo aggiornamento: 07 Gennaio 2023
Categoria: Microsoft Office Excel Riferimenti

La guida di Microsoft Office sui riferimenti di Excel non è proprio il massimo e anche le guide in Rete non spiegano bene la differenza tra riferimenti relativi, riferimenti misti e riferimenti assoluti in Excel utilizzando spesso indistintamente l’uno o l’altro per la medesima operazione. Eppure l’argomento riveste particolare importanza per le formule in Excel perché svolgono un compito fondamentale nelle operazioni di copia e incolla di funzioni, gestendo in maniera intelligente l’incremento delle coordinate dei riferimenti presenti in una formula quando questa si copia verso il basso o verso destra; in tal modo si potranno ottenere infiniti calcoli senza possibilità d’errore. Per cui quando bisogna eseguire l’identica operazione su tante celle adiacenti non sarà  necessario riscrivere tutte le volte la stessa formula, ma si potrà utilizzare la funzionalità di riempimento automatico per far eseguire i calcoli da Excel stesso. Non perdiamo tempo e scopriamo i Riferimenti Excel: Riferimenti relativi, misti e assoluti.

Sommario Excel e riferimenti di cella

• Terminologia riferimenti
• Perché è necessario bloccare un riferimento
• Quando usare i riferimenti misti
• Quando usare i riferimenti assoluti
• Riferimenti provenienti da altri fogli o file
• Macro Excel con riferimenti relativi e riferimenti assoluti (Video)
• Conclusione

Terminologia riferimenti

Lo spreadsheet di casa Microsoft, almeno fino alla versione odierna, comprende in ogni file 255 fogli e in ogni foglio le colonne sono identificate da lettere (da A ad XFD, per un totale di 16.384 colonne) e da righe numerate (da 1 a 1.048.576). Nei calcoli, per riferirsi al valore contenuto in una cella, Excel usa l’indirizzo della cella, ovvero la coppia  lettera (cioè la colonna) e numero (cioè la riga); il foglio elettronico considera quest’indirizzo come riferimento relativo, ovvero non memorizza l’indirizzo assoluto della cella, ma la sua posizione relativa rispetto alla cella con la formula. Scrivere le funzioni o fare le operazioni adottando nel calcolo i riferimenti di cella al posto dei valori veri e propri, dà l’enorme vantaggio di aggiornare automaticamente i risultati.Chiarito questo punto, vediamo come si comporta Microsoft Excel. Si è appena detto che per default un qualsiasi riferimento di cella, ad esempio B2, viene considerato dall’applicativo come un riferimento relativo, non è però raro il caso in cui è necessario anticipare alla lettera di colonna e/o al numero di riga un simbolo di $ (non l’euro o la sterlina come a volte il formatore si sente chiedere da allievi alle prime armi; perché il $ e non un altro simbolo? Excel l’hanno inventato in U.S.A.) che serve a bloccare il riferimento stesso. La terminologia che si adotta è la seguente:

• B2 Riferimento relativo
• $B2 Riferimento misto assoluto di colonna, relativo di riga
• B$2 Riferimento misto assoluto di riga, relativo di colonna
• $B$2 Riferimento assoluto

È possibile passare da un riferimento all’altro attraverso un tasto di scelta rapida: posizionandosi dentro il riferimento medesimo (con il doppio clic o tramite il tasto funzione F2) all’inizio, all’interno o alla fine del riferimento nel quale si desidera inserire il $ e cliccare ripetutamente il tasto funzione F4; al primo clic Excel inserisce il doppio dollaro (riferimento assoluto), continuando a premere F4 vengono proposti anche gli altri possibili stati (i due riferimenti misti e il riferimento relativo).

Perché è necessario bloccare un riferimento

Da quanto scritto si è evinto che un riferimento relativo di cella in una formula, ad esempio B2, si basa sulla posizione relativa della cella che contiene la formula e della cella del riferimento. Se cambia la posizione della cella che contiene la formula, cambia anche il riferimento. Se si copia la formula nelle righe o nelle colonne adiacenti, il riferimento verrà automaticamente adattato. Ad esempio si supponga che nella cella C1 si voglia eseguire l’operazione =A1*B1 per calcolare il rimborso da percepire in base al numero di chilometri percorsi e la tariffa di rimborso di 25 centesimi

Cosa è accaduto? Trascinando la funzione verso il basso si sono incrementati i numeri di riga mentre le lettere di colonna sono rimaste le stesse (perché la formula viene spostata di riga, ma non di colonna); se la tabella con i dati si fosse sviluppata in orizzontale e quindi si sarebbe stati costretti a trascinare la formula verso destra, si sarebbero incrementate invece le lettere della colonna mentre i numeri di riga sarebbero restati i medesimi (perché la formula viene spostata di colonna, ma non di riga).
È palese che in questo tipo di esempio si otterrebbero dei risultati errati perché mentre in C1 il calcolo di =A1*B1 avrebbe dato il risultato corretto di 2,5, in C2 ed in C3 si avrebbe una moltiplicazione per una cella vuota (B2 e B3) che ovviamente darebbe un messaggio d’errore.
Allo stesso modo si sarebbero ottenuti degli errori anche sviluppando la tabella in senso orizzontale: =A1*A2 avrebbe fornito il risultato corretto, mentre in B3 e C3 si avrebbero messaggi d’errore.
In tali casi è dunque necessario adottare un metodo che blocchi la cella per la quale si esegue la moltiplicazione, in particolare la B1, ancor meglio solo l’1 nel caso si trascini verso il basso e solo la A2 in caso si trascini verso destra. Ed è proprio qui che entra in ballo il simbolo del $.

Quando usare i riferimenti misti

Si è visto che un riferimento misto contiene una colonna assoluta e una riga relativa o una riga assoluta e una colonna relativa. Un riferimento assoluto di colonna assume la forma $A1, $B1 e così via, mentre un riferimento assoluto di riga assume la forma A$1, B$1 e così via. Se cambia la posizione della cella che contiene la formula, cambierà anche il riferimento relativo, ma non il riferimento assoluto. Se si copia la formula nelle righe adiacenti o nelle colonne sottostanti, il riferimento relativo verrà automaticamente adattato mentre il riferimento assoluto no. Se ad esempio si copia un riferimento misto dalla cella A2 alla cella B3, verrà modificato da =A$1 in =B$1.
Per far in modo che nell’esempio della tabella precedente i risultati siano corretti bisogna allora utilizzare un simbolo di $ davanti alla lettera di colonna o al numero di riga della tariffa di rimborso, a seconda che si trascini verso il basso o verso destra

Così facendo è possibile rendere fisso il riferimento della tariffa di rimborso.
Nel primo caso =A1*B$1 si è adottato un riferimento misto assoluto di riga, relativo di colonna, mentre nel secondo un riferimento misto assoluto di colonna, relativo di riga ovvero =A1*$A2.
Molti per motivi forse di pigrizia o forse perché non hanno ben chiaro il ruolo del simbolo $, adottano dei riferimenti assoluti (=A1*$B$1 o =A1*$A$2 a seconda dei casi). Diciamo che nella pratica può andare bene, ma in un esame come l’ECDL o simili la risposta sarebbe considerata errata perché usare un riferimento assoluto quando esso non necessita significa non aver capito l’uso del simbolo in questione.
Sforzandosi di capire dove vada precisamente messo il dollaro si riuscirà a padroneggiare il suo utilizzo e ci si saprà districare in tutti quei casi in cui mettere un $ semplifica il lavoro; in tal modo si eviterà anche di ottenere un risultato errato perché si è bloccato erroneamente un riferimento di troppo.
E allora quando si usano i riferimenti assoluti?

Quando usare i riferimenti assoluti

Un riferimento assoluto di cella in una formula, ad esempio $A$1, si riferisce sempre a una cella in una posizione specifica. Se cambia la posizione della cella che contiene la formula, il riferimento assoluto rimarrà invariato. Se si copia la formula nelle righe adiacenti o nelle colonne sottostanti, il riferimento non verrà adattato. Se ad esempio si copia un riferimento assoluto dalla cella B2 alla cella B3, tale riferimento rimarrà invariato in entrambe le celle =$A$1.
Proseguendo con un esempio pratico si scopre che un riferimento assoluto si usa quando si è costretti a trascinare sia in basso che a destra.
Si supponga una tabella di partenza con degli agenti che vengono pagati ad ora un tot al giorno e si voglia moltiplicare questo valore per il 3% presente, si supponga, in A6

Impostando un riferimento assoluto $A$6 si può successivamente trascinare in basso e poi a destra ottenendo dei risultati corretti, cosa impossibile se si fosse adottato un riferimento misto.

Riferimenti provenienti da altri fogli o file

In questi esempi si sono usati riferimenti presenti all’interno del medesimo foglio di lavoro ma, ovviamente, come in tutte le altre formule e funzioni di Excel è possibile utilizzare anche riferimenti presenti in fogli diversi della stessa cartella di lavoro o addirittura presenti in altri file.
La struttura del riferimento si trasforma in qualcosa di più lungo.
Nel caso il riferimento interessato, ad esempio l’assoluto $A$1 sia presente in Foglio2 del file stipendi.xlsx, mentre la formula in Foglio1, non si potrà utilizzare semplicemente A1, ma Foglio2!$A$1, cioè andrà anteposto il nome del foglio.
Invece se il riferimento $A$1 risiede nel Foglio2 della cartella di lavoro agenti.xlsx e la formula in un altro file, si dovrà utilizzare la dicitura [agenti.xlsx]Foglio2!$A$1, ovvero al nome del foglio andrà anteposto il nome del file, comprensivo del formato, tra parentesi quadre.
Se per un principiante sembra una struttura troppo difficile da scrivere c’è la possibilità di farla scrivere direttamente dall’applicativo Excel semplicemente cliccando la cella interessata mentre si sta scrivendo la formula e dando Invio da tastiera.

Macro Excel con riferimenti relativi e riferimenti assoluti (Video)

Conclusione

Il dollaro $ permette di risolvere tantissime situazioni senza dover scrivere formule in modo reiterato; in quest’ottica di ottimizzazione del codice (questo termine denota la mia formazione da programmatore e la mia altra grande passione, il web) è bene capire quando usare e non usare tale simbolo, si valuti cosa dev’essere aggiornato con il trascinamento e si eviti di bloccare qualcosa quando non si presenta tale necessità.
Ti è piaciuto Riferimenti Excel: Riferimenti relativi, misti e assoluti? Ricorda, inoltre, che esistono anche i riferimenti strutturati nelle tabelle Excel.