Riferimento assoluto in Excel (come crearlo e usarlo)

Quando si fa riferimento a una cella in una formula in Excel, il tipo di riferimento predefinito è un riferimento relativo. Questi riferimenti cambieranno quando la formula viene copiata in altre celle in base alla loro colonna e riga relative. Se si desidera mantenere un riferimento costante, indipendentemente da dove viene copiata la formula, è necessario utilizzare il riferimento assoluto.

• Che cos'è un riferimento assoluto
• Come creare riferimenti assoluti
• Usare il riferimento assoluto con esempi
• Calcolare la percentuale del totale
• Cercare un valore e restituire il valore corrispondente
• 2 clic per rendere assoluti i riferimenti delle celle in batch con Kutools
• Riferimento relativo e riferimento misto
• Cose da ricordare

Scarica gratuitamente il file di esempio

Video: Riferimento assoluto

Che cos'è un riferimento assoluto

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

A differenza di un riferimento relativo che cambia in base alla sua posizione relativa quando una formula viene copiata in altre celle, un riferimento assoluto rimarrà costante indipendentemente da dove viene copiata o spostata la formula.

Un riferimento assoluto viene creato aggiungendo un simbolo di dollaro ($) prima dei riferimenti di colonna e riga nella formula. Ad esempio, per creare un riferimento assoluto per la cella A1, dovresti rappresentarla come $A$1.

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

Ad esempio, l'intervallo A4:C7 contiene i prezzi dei prodotti e si vuole ottenere l'imposta pagabile di ciascun prodotto in base all'aliquota fiscale nella cella B2.

Se si utilizza un riferimento relativo nella formula come "=B5*B2", vengono restituiti risultati errati quando si trascina la maniglia di riempimento automatico verso il basso per applicare questa formula. Poiché il riferimento alla cella B2 cambierà in base alle celle nella formula. Ora, la formula nella cella C6 è "=B6*B3" e la formula nella cella C7 è "=B7*B4".

Ma se si utilizza un riferimento assoluto alla cella B2 con la formula "=B5*$B$2", si garantirà che l'aliquota fiscale rimanga la stessa per tutte le celle quando la formula viene trascinata verso il basso utilizzando la maniglia di riempimento automatico, i risultati saranno corretti.

Utilizzo del riferimento relativo		Utilizzo del riferimento assoluto

Come creare riferimenti assoluti

Per creare un riferimento assoluto in Excel, aggiungi i simboli di dollaro ($) prima dei riferimenti di colonna e riga in una formula. Ci sono due modi per creare un riferimento assoluto:

Aggiungere manualmente i simboli di dollaro al riferimento della cella

È possibile aggiungere manualmente i simboli di dollaro ($) prima dei riferimenti di colonna e riga che si desidera rendere assoluti mentre si digita la formula in una cella.

Ad esempio, se si desidera sommare i numeri nella cella A1 e B1 e renderli entrambi assoluti, basta digitare la formula come "=$A$1+$B$1". Questo garantirà che i riferimenti delle celle rimangano costanti quando la formula viene copiata o spostata in altre celle.

In alternativa, se si desidera modificare i riferimenti in una formula esistente in assoluti, è possibile selezionare la cella, quindi andare alla barra della formula per aggiungere i simboli di dollaro ($).

Utilizzare la scorciatoia F4 per convertire il riferimento relativo in assoluto

1. Fare doppio clic sulla cella con la formula per entrare in modalità di modifica;
2. Posizionare il cursore sul riferimento della cella che si desidera rendere assoluto;
3. Premere il tasto "F4" sulla tastiera per passare tra i tipi di riferimento fino a quando i simboli di dollaro vengono aggiunti prima dei riferimenti di colonna e riga;
4. Premere il tasto "Invio" per uscire dalla modalità di modifica e applicare le modifiche.

Il tasto F4 alterna i riferimenti tra relativi, assoluti e misti.

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

Se si desidera rendere assoluti tutti i riferimenti in una formula, selezionare l'intera formula nella barra della formula, premere il tasto "F4" per passare tra i tipi di riferimento fino a quando i simboli di dollaro vengono aggiunti prima dei riferimenti di colonna e riga.

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

Usare il riferimento assoluto con esempi

Questa parte fornisce 2 esempi per mostrare quando e come utilizzare i riferimenti assoluti in una formula Excel.

Esempio 1 Calcolare la percentuale del totale

Supponiamo di avere un intervallo di dati (A3:B7) contenente le vendite di ogni frutto e la cella B8 contiene l'importo totale delle vendite di questi frutti, ora si vuole calcolare la percentuale di ogni vendita di frutta rispetto al totale.

La formula generica per calcolare la percentuale del 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 la maniglia di riempimento automatico verso il basso per calcolare la percentuale degli altri frutti, verranno restituiti errori #DIV/0!.

Poiché quando si trascina la maniglia di riempimento automatico per copiare la formula nelle celle sottostanti, il riferimento relativo B8 viene automaticamente regolato su altri riferimenti di cella (B9, B10, B11) in base alle loro posizioni relative. E le celle B9, B10 e B11 sono vuote (zeri), quando il divisore è zero, la formula restituisce un errore.

Per correggere gli errori, in questo caso, è necessario rendere assoluto il riferimento della cella B8 ($B$8) nella formula per impedirne la modifica quando si sposta o si copia la formula ovunque. Ora la formula è stata aggiornata a:

=B4/$B$8

Quindi trascinare la maniglia di riempimento automatico verso il basso per calcolare la percentuale degli altri frutti.

Esempio 2 Cercare un valore e tornare al valore corrispondente

Supponendo di voler cercare l'elenco dei nomi in D4:D5 e restituire i loro stipendi corrispondenti in base ai nomi del personale e agli stipendi annuali corrispondenti forniti nell'intervallo (A4:B8).

La 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)

Quindi trascinare la maniglia di riempimento automatico verso il basso per cercare il valore sotto, verrà restituito un errore.

Quando si trascina la maniglia di riempimento verso il basso per copiare la formula nella cella sottostante, i riferimenti nella formula si regolano automaticamente di una riga in basso. Di conseguenza, il riferimento all'intervallo della tabella, A4:B8, diventa A5:B9. Poiché Lisa non può essere trovata nell'intervallo A5:B9, la formula restituisce un errore.

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

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

Quindi trascinare la maniglia di riempimento automatico verso il basso per ottenere lo stipendio di Lisa.

2 clic per rendere assoluti i riferimenti delle celle in batch con Kutools

Sia che si scelga di digitare manualmente o di utilizzare la scorciatoia F4, è possibile modificare una sola formula alla volta in Excel. Se si desidera rendere assoluti i riferimenti delle celle in centinaia di formule in Excel, lo strumento "Convert Refers" di Kutools per Excel può aiutarti a gestire il lavoro con 2 clic.

Per rendere assoluti i riferimenti delle celle in più formule, selezionare le celle delle formule e fare clic su "Kutools" > "Altro" > "Convert Refers". Quindi scegliere l'opzione "A Assoluto" e fare clic su "Ok" o "Applica". Ora tutti i riferimenti delle celle delle formule selezionate sono stati convertiti in assoluti.

Riferimento relativo e riferimento misto

Oltre al riferimento assoluto, ci sono altri due tipi di riferimenti: riferimento relativo e riferimento misto.

Il riferimento relativo è il tipo di riferimento predefinito in Excel, che non ha simboli di dollaro ($) prima dei riferimenti di riga e colonna. E quando una formula con riferimenti relativi viene copiata o spostata in altre celle, i riferimenti cambieranno automaticamente in base alla loro posizione relativa.

Ad esempio, quando si digita una formula in una cella come "=A1+1", quindi si trascina la maniglia di riempimento automatico verso il basso per riempire questa formula nella cella successiva, la formula cambierà automaticamente in "=A2+1".

Il riferimento misto è composto sia da un riferimento assoluto che da un riferimento relativo. In altre parole, il riferimento misto utilizza il simbolo di dollaro ($) per bloccare la riga o la colonna quando una formula viene copiata o riempita.

Prendiamo una tabella di moltiplicazione come esempio, le righe e le colonne elencano i numeri da 1 a 9, che si moltiplicheranno tra loro.

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

Questo perché quando si copia la formula a destra, la posizione della riga non cambia, ma la posizione della colonna cambia da B3 a C3, D3, ecc. Di conseguenza, le formule nelle celle a destra (D3, E3, ecc.) cambiano in "=C3*D2", "=D3*E2" e così via, quando in realtà si vuole che siano "=B3*D2", "=B3*E2" e così via.

In questo caso, è necessario aggiungere un simbolo di dollaro ($) per bloccare il riferimento della colonna di “B3”. Utilizzare la formula come segue:

=$B3*C2

Ora, quando si trascina la formula verso destra, i risultati sono corretti.

Quindi è necessario moltiplicare il numero 1 nella cella C2 per i numeri nelle righe sottostanti.

Quando si copia la formula verso il basso, la posizione della colonna della cella C2 non cambia, ma la posizione della riga cambia da C2 a C3, C4, ecc. Di conseguenza, le formule nelle celle sottostanti cambiano in "=$B4C3", "=$B5C4", ecc., che produrranno risultati errati.

Per risolvere questo problema, cambia “C2” in “C$2” per mantenere costante il riferimento della riga quando si trascina la maniglia di riempimento automatico verso il basso per riempire le formule.

=$B3*C$2

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

Cose da ricordare

• Riepilogo dei riferimenti delle celle

  Tipo	Esempio	Riepilogo
  Riferimento assoluto	$A$1	Non cambia mai quando la formula viene copiata in altre celle
  Riferimento relativo	A1	Entrambi i riferimenti di riga e 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, i riferimenti assoluti si adatteranno automaticamente quando una riga o una colonna viene aggiunta o rimossa dall'alto o da sinistra nel foglio di lavoro. Ad esempio, in una formula "=$A$1+1", quando si inserisce una riga nella parte superiore del foglio, la formula cambierà automaticamente in "=$A$2+1".

  

• Il tasto "F4" può passare tra riferimenti relativi, assoluti e misti.