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 relativa colonna e riga. Se si desidera mantenere un riferimento costante, indipendentemente da dove viene copiata la formula, è necessario utilizzare il riferimento assoluto.

• Cos'è un riferimento assoluto
• Come fare riferimenti assoluti
• Usa riferimenti assoluti con esempi
• Calcolare la percentuale del totale
• Cerca un valore e torna al valore di corrispondenza corrispondente
• 2 clic per rendere in batch i riferimenti di cella assoluti con Kutools
• Riferimento relativo e riferimento misto
• Cose da ricordare

Scarica gratuitamente il file di esempio

Video: riferimento assoluto

Cos'è un riferimento assoluto

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

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

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 desidera fare riferimento a una cella fissa o a un intervallo in una formula che verrà copiata in più celle, ma non si desidera che il riferimento venga modificato.

Ad esempio, l'intervallo A4:C7 contiene i prezzi dei prodotti e si desidera ottenere l'imposta dovuta per ogni prodotto in base all'aliquota fiscale nella cella B2.

Se si utilizza un riferimento relativo nella formula come "=B5*B2", vengono restituiti alcuni risultati errati quando si trascina verso il basso la maniglia di riempimento automatico per applicare questa formula. Poiché il riferimento alla cella B2 cambierà rispetto 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 assicurerà 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 sono corretti.

Usando il riferimento relativo		Usando il riferimento assoluto

Come fare riferimenti assoluti

Per creare un riferimento assoluto in Excel, è necessario aggiungere i simboli del dollaro ($) prima dei riferimenti di colonna e riga nella formula. Esistono due modi per creare un riferimento assoluto:

Aggiungi manualmente i simboli del dollaro al riferimento di cella

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

Ad esempio, se desideri sommare i numeri nella cella A1 e B1 e renderli entrambi assoluti, digita semplicemente la formula come "=$A$1+$B$1". Ciò assicurerà che i riferimenti di cella rimangano costanti quando la formula viene copiata o spostata in altre celle.

Oppure, se desideri modificare i riferimenti in una formula esistente in una cella in assoluto, puoi selezionare la cella, quindi andare alla barra della formula per aggiungere i simboli del dollaro ($).

Utilizzo della scorciatoia F4 per convertire il riferimento relativo in assoluto

1. Fare doppio clic sulla cella con la formula per accedere alla modalità di modifica;
2. Posiziona il cursore sul riferimento di cella che vuoi rendere assoluto;
3. STAMPA F4 tasto sulla tastiera per cambiare i tipi di riferimento finché i simboli del dollaro non vengono aggiunti prima dei riferimenti di colonna e di riga;
4. STAMPA Entra tasto per uscire dalla modalità di modifica e applicare le modifiche.

Il tasto F4 può alternare il riferimento tra riferimento relativo, riferimento assoluto e riferimento misto.

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

Se vuoi rendere assoluti tutti i riferimenti in una formula, seleziona l'intera formula nella barra della formula, premi F4 tasto per alternare i tipi di riferimento finché i simboli del dollaro non vengono aggiunti prima dei riferimenti di colonna e di riga.

LA1+SI1 → $LA$1+$SI$1 → LA$1+SI$1 → $LA1+$SI1 → LA1+SI1

Usa riferimenti assoluti con esempi

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

Esempio 1 Calcolare la percentuale del totale

Supponiamo di avere un intervallo di dati (A3:B7) contenente le vendite di ciascun frutto e che la cella B8 contenga l'importo totale delle vendite di questi frutti, ora si desidera calcolare la percentuale di ciascuna vendita di frutta sul totale.

La formula generica per calcolare la percentuale del totale:

Percentage = Sale/Amount

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

=B4/B8

Quando si trascina verso il basso la maniglia di riempimento automatico per calcolare la percentuale di altri frutti, #DIV/0! gli errori verranno restituiti.

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

Per correggere gli errori, in questo caso, è necessario rendere assoluto il riferimento di cella B8 ($ B $ 8) nella formula per evitare che cambi quando si sposta o si copia la formula in qualsiasi punto. Ora la formula è aggiornata a:

=B4/$B$8

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

Esempio 2 Cercare un valore e tornare al valore di corrispondenza corrispondente

Supponendo che tu voglia cercare l'elenco dei nomi in D4:D5 e restituire gli stipendi corrispondenti in base ai nomi del personale e allo stipendio annuo corrispondente forniti nell'intervallo (A4:B8).

La formula generica da cercare è:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Se utilizzi il riferimento relativo nella formula per cercare un valore e restituire il valore di corrispondenza corrispondente in questo modo:

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

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

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

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

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

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

2 clic per rendere in batch i riferimenti di cella assoluti con Kutools

Sia che tu scelga di digitare manualmente o di utilizzare la scorciatoia F4, puoi modificare solo una formula alla volta in Excel. Se vuoi rendere assoluti i riferimenti di cella in centinaia di formule in Excel, il Converti Refers strumento di Kutools for Excel può aiutarti a gestire il lavoro con 2 clic.

Selezionare le celle della formula che si desidera rendere assoluti i riferimenti di cella, fare clic Kutools > Altro (fx) > Converti Refers. Quindi scegli il file All'assoluto opzione e clicca Ok or APPLICA. Ora tutti i riferimenti di cella delle formule selezionate sono stati convertiti in assoluto.

Riferimento relativo e riferimento misto

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

Riferimento relativo è il tipo di riferimento predefinito in Excel, ovvero senza i simboli del 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 digiti una formula in una cella come "=A1+1", quindi trascini la maniglia di riempimento automatico verso il basso per riempire questa formula nella cella successiva, la formula cambierà automaticamente in "=A2+1".

Riferimento misto è composto sia da un riferimento assoluto che da un riferimento relativo. In altre parole, i riferimenti misti utilizzano il simbolo del dollaro ($) per correggere la riga o la colonna quando una formula viene copiata o compilata.

Prendi una tavola pitagorica come esempio, le righe e le colonne elencano i numeri da 1 a 9, che moltiplicherai a vicenda.

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 non sono corretti tranne il primo.

Questo perché quando copi 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.) cambia in "=Do3*Re2", "=Re3*Mi2" e così via, quando in realtà vuoi che siano "=Si3*Re2", "=Si3*Mi2" e così via.

In questo caso, è necessario aggiungere il simbolo del dollaro ($) per bloccare il riferimento di colonna di "B3". Utilizzare la formula come di seguito:

=$B3*C2

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

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

Quando copi la formula verso il basso, la posizione della colonna della cella C2 non cambierà, 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 produrrà risultati errati.

Per risolvere questo problema, cambia "C2" in "C$2" per evitare che il riferimento alla riga cambi quando trascini verso il basso la maniglia di riempimento automatico per riempire le formule.

=$B3*C$2

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

Cose da ricordare

• Riepilogo dei riferimenti di cella

  Tipologia	Esempio	sommario
  Riferimento assoluto	$ A $ 1	Non cambiare 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 alla riga cambia quando la formula viene copiata in altre celle ma il riferimento alla colonna è fisso/Il riferimento alla colonna cambia quando la formula viene copiata in altre celle ma il riferimento alla riga è fisso;

• In genere, i riferimenti assoluti non cambiano mai quando una formula viene spostata. Tuttavia, i riferimenti assoluti verranno regolati automaticamente quando una riga o colonna viene aggiunta o rimossa dalla parte superiore o sinistra del foglio di lavoro. Ad esempio, in una formula "=$A$1+1", quando inserisci una riga nella parte superiore del foglio, la formula cambierà automaticamente in "=$A$2+1".

  

• Le F4 il tasto può commutare tra riferimento relativo, riferimento assoluto e riferimento misto.