Riferimento assoluto in Excel (come crearlo e utilizzarlo)

Quando si fa riferimento a una cella in una formula di Excel, il tipo di riferimento predefinito è relativo. Questi riferimenti si aggiornano automaticamente quando la formula viene copiata in altre celle, adattandosi alla nuova posizione in base alla colonna e alla riga relative. Per mantenere invece un riferimento fisso, indipendentemente da dove si copia la formula, è necessario utilizzare un riferimento assoluto.

• Cos’è un riferimento assoluto
• Come creare riferimenti assoluti
• Utilizzo del riferimento assoluto con esempi
• Calcola la percentuale del totale
• Cerca un valore e restituisci il valore corrispondente
• 2 clic per trasformare in blocco i riferimenti di cella in assoluti con Kutools
• Riferimento relativo e riferimento misto
• Da ricordare

Scarica gratuitamente il file di esempio

Video: Riferimento assoluto

Cos’è un riferimento assoluto

Un riferimento assoluto è un tipo di riferimento a cella in Excel.

A differenza di un riferimento relativo, che si aggiorna automaticamente in base alla posizione della cella quando la formula viene copiata altrove, un riferimento assoluto rimane fisso, indipendentemente da dove la formula venga copiata o spostata.

Un riferimento assoluto si crea inserendo il simbolo del dollaro ($) davanti alla lettera della colonna e al numero della riga nella formula. Ad esempio, per trasformare la cella A1 in un riferimento assoluto, la si indica come $A$1.

I riferimenti assoluti sono utili quando si desidera fare riferimento a una cella o a un intervallo fisso in una formula che verrà copiata in più celle, ma senza che il riferimento cambi.

Ad esempio, l’intervallo A4:C7 contiene i prezzi dei prodotti e si desidera calcolare l’imposta da pagare per ciascun prodotto in base all’aliquota indicata nella cella B2.

Se si utilizza un riferimento relativo nella formula, come «=B5*B2», si ottengono risultati errati trascinando il quadratino di riempimento automatico verso il basso per applicare la formula. Il riferimento alla cella B2, infatti, si aggiorna in modo relativo rispetto alla posizione della formula: così, nella cella C6 la formula diventa «=B6*B3» e in C7 «=B7*B4».

Tuttavia, utilizzando un riferimento assoluto alla cella B2 con la formula «=B5*$B$2», l’aliquota fiscale rimarrà invariata per tutte le celle quando la formula viene trascinata verso il basso con il quadratino di riempimento automatico, garantendo risultati sempre corretti.

Utilizzo del riferimento relativo		Utilizzo del riferimento assoluto

Come creare riferimenti assoluti

Per creare un riferimento assoluto in Excel, aggiungi il simbolo del dollaro ($) davanti alla lettera della colonna e al numero della riga all’interno di una formula. Esistono due modi per creare un riferimento assoluto:

Aggiungere manualmente il simbolo del dollaro al riferimento di cella

È possibile aggiungere manualmente il simbolo del dollaro ($) davanti ai riferimenti di colonna e riga che si desidera rendere assoluti mentre si digita la formula direttamente nella cella.

Ad esempio, se si desidera sommare i numeri nelle celle A1 e B1 rendendo entrambi i riferimenti assoluti, basta digitare la formula come «=$A$1+$B$1». In questo modo, i riferimenti alle celle rimarranno costanti quando la formula verrà copiata o spostata in altre celle.

In alternativa, per rendere assoluti i riferimenti in una formula esistente, seleziona la cella e aggiungi manualmente i simboli del dollaro ($) nella Barra delle formule.

Utilizzare il tasto di scelta rapida F4 per convertire un riferimento relativo in assoluto

1. Fai doppio clic sulla cella contenente la formula per entrare in modalità modifica;
2. Posiziona il cursore sul riferimento di cella che desideri rendere assoluto;
3. Premi il tasto "F4" sulla tastiera per cambiare il tipo di riferimento finché non compaiono i simboli del dollaro davanti sia al riferimento di colonna che a quello di riga;
4. Premi Invio per uscire dalla modalità modifica e applicare le modifiche.

Il tasto F4 alterna i riferimenti tra relativo, assoluto e misto.

A1 → $A$1 → A$1 → $A1 → A1

Per rendere tutti i riferimenti assoluti in una formula, seleziona l’intera formula nella Barra delle formule e premi il tasto "F4" per alternare i tipi di riferimento finché non compaiono i simboli del dollaro ($) davanti alle colonne e alle righe.

A1+B1 → $A$1++$B$1 → A$1++B$1 → $A1+$B1 → A1+B1

Utilizzo del riferimento assoluto con esempi

Questa sezione presenta due esempi per illustrare quando e come utilizzare i riferimenti assoluti in una formula di Excel.

Esempio 1: Calcolare la percentuale sul totale

Si supponga di avere un intervallo di dati (A3:B7) contenente le vendite di ciascun frutto e che la cella B8 riporti l’importo totale delle vendite; si desidera ora calcolare la percentuale di vendita di ciascun frutto rispetto al totale.

Formula generica per calcolare la percentuale sul totale:

Percentage = Sale/Amount

Utilizzare il riferimento relativo nella formula per ottenere la percentuale del primo frutto in questo modo:

=B4/B8

Quando si trascina il quadratino di riempimento automatico verso il basso per calcolare la percentuale degli altri frutti, compaiono errori #DIV/0!

Infatti, trascinando il quadratino di riempimento automatico per copiare la formula nelle celle sottostanti, il riferimento relativo B8 viene aggiornato automaticamente in base alla posizione delle celle (B9, B10, B11). Tuttavia, poiché le celle B9, B10 e B11 sono vuote – e quindi equivalenti a zero – il divisore diventa zero, causando un errore nella formula.

Per correggere gli errori, in questo caso è necessario rendere assoluto il riferimento alla cella B8 ($B$8) nella formula, in modo che rimanga invariato quando la formula viene spostata o copiata altrove. La formula viene quindi aggiornata a:

=B4/$B$8

Trascina quindi il quadratino di riempimento automatico verso il basso per calcolare la percentuale degli altri frutti.

Esempio 2: Cercare un valore e restituire il valore corrispondente

Si supponga di voler cercare i nomi nell’elenco D4:D5 e restituire i relativi stipendi in base ai nomi del personale e agli stipendi annuali indicati nell’intervallo A4:B8.

Formula generica per la ricerca:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Se si utilizza il riferimento relativo nella formula per cercare un valore e restituire il valore corrispondente in questo modo:

=VLOOKUP(D4,A4:B8,2,FALSE)

Trascinando il quadratino di riempimento automatico verso il basso per cercare il valore successivo, si otterrà un errore.

Quando trascini il quadratino di riempimento automatico per copiare la formula nella cella sottostante, i riferimenti vengono aggiornati automaticamente spostandosi di una riga verso il basso. Di conseguenza, l’intervallo della tabella A4:B8 diventa A5:B9. Poiché Lisa non è presente nell’intervallo A5:B9, la formula restituisce un errore.

Per evitare gli errori, utilizzare il riferimento assoluto $A$4:$B$8 al posto del riferimento relativo A4:B8 nella formula:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Trascina quindi il quadratino di riempimento automatico verso il basso per ottenere lo stipendio di Lisa.

2 clic per impostare in blocco i riferimenti di cella come assoluti con Kutools

Che si scelga di digitare manualmente o di usare la scorciatoia F4, in Excel è possibile modificare una sola formula alla volta. Se si desidera rendere assoluti i riferimenti di cella in centinaia di formule, lo strumento «Converti Riferimenti di Celle» di Kutools per Excel permette di eseguire questa operazione in soli 2 clic.

Per rendere assoluti i riferimenti di cella in più formule, seleziona le celle contenenti le formule e fai clic su **Kutools** > **Altro** > **Converti Riferimenti di Celle**. Scegli quindi l’opzione **A assoluto** e fai clic su **OK** o **Applica**. Tutti i riferimenti di cella nelle formule selezionate saranno immediatamente convertiti in assoluti.

Riferimento relativo e riferimento misto

Oltre al riferimento assoluto, esistono altri due tipi di riferimento: il riferimento relativo e quello misto.

Il riferimento relativo è il tipo di riferimento predefinito in Excel: non include il simbolo del dollaro ($) davanti ai riferimenti di riga e colonna. Quando copi o sposti una formula con riferimenti relativi in altre celle, questi si aggiornano automaticamente in base alla nuova posizione.

Ad esempio, quando si digita una formula in una cella come «=A1+1» e si trascina il quadratino di riempimento automatico verso il basso per compilare la cella successiva, la formula si aggiorna automaticamente a «=A2+1».

Il riferimento misto combina un riferimento assoluto e uno relativo. In altre parole, utilizza il simbolo del dollaro ($) per bloccare la riga o la colonna quando una formula viene copiata o riempita.

Si prenda come esempio una tavola pitagorica: le righe e le colonne riportano i numeri da 1 a 9, che verranno moltiplicati tra loro.

Per iniziare, puoi inserire la formula «=B3*C2» nella cella C3 per moltiplicare 1 (nella cella B3) per il numero (1) presente nella prima colonna. Tuttavia, quando trascini il quadratino di riempimento automatico verso destra per compilare le altre celle, noterai che tutti i risultati sono errati, tranne il primo.

Ciò accade perché, quando si copia la formula verso destra, la riga rimane invariata, mentre la colonna si sposta da B3 a C3, D3 e oltre. Di conseguenza, le formule nelle celle a destra (D3, E3, ecc.) diventano «=C3*D2», «=D3*E2» e così via, mentre l’obiettivo è che restino «=B3*D2», «=B3*E2» e così via.

In questo caso, è necessario aggiungere il simbolo del dollaro ($) per bloccare il riferimento alla colonna in “B3”. Utilizza la seguente formula:

=$B3*C2

Ora, trascinando la formula verso destra, i risultati sono corretti.

Successivamente, occorre moltiplicare il numero 1 presente nella cella C2 per i numeri nelle righe successive.

Quando si copia la formula verso il basso, la colonna della cella C2 rimane fissa, mentre la riga si aggiorna progressivamente da C2 a C3, C4 e così via. Di conseguenza, le formule nelle celle sottostanti diventano «$B4*C3», «$B5*C4», ecc., generando risultati errati.

Per risolvere questo problema, modifica «C2» in «C$2» così da mantenere fisso il riferimento alla riga quando trascini la maniglia di riempimento automatico verso il basso per compilare le formule.

=$B3*C$2

Ora puoi trascinare la maniglia di riempimento automatico a destra o in basso per ottenere tutti i risultati desiderati.

Da ricordare

• Riepilogo sui riferimenti di cella

  Tipo	Esempio	Riepilogo
  Riferimento assoluto	$A$1	Non cambia mai quando la formula viene copiata in altre celle
  Riferimento relativo	A1	Sia il riferimento di riga che quello di colonna cambiano in base alla posizione relativa quando la formula viene copiata in altre celle
  Riferimento misto	$A1/A$1	Il riferimento di riga cambia quando la formula viene copiata in altre celle, ma il riferimento di colonna è fisso / Il riferimento di colonna cambia quando la formula viene copiata in altre celle, ma il riferimento di riga è fisso;

• In generale, i riferimenti assoluti non cambiano mai quando una formula viene spostata. Tuttavia, si adattano automaticamente all’inserimento o alla rimozione di righe o colonne sopra o a sinistra nel foglio di lavoro. Ad esempio, se in una formula «=$A$1+1» si inserisce una riga all’inizio del foglio, questa diventa automaticamente «=$A$2+1».

  

• Il tasto "F4" ti permette di alternare rapidamente tra riferimenti relativi, assoluti e misti.