Un foglio di lavoro in Excel è composto da celle. È possibile fare riferimento a queste celle specificando il valore della riga e il valore della colonna.
Ad esempio, A1 farebbe riferimento alla prima riga (specificata come 1) e alla prima colonna (specificata come A). Allo stesso modo, B3 sarebbe la terza riga e la seconda colonna.
La potenza di Excel risiede nel fatto che è possibile utilizzare questi riferimenti di cella in altre celle durante la creazione di formule.
Ora ci sono tre tipi di riferimenti di cella che puoi usare in Excel:
- Riferimenti di cella relativi
- Riferimenti di cella assoluti
- Riferimenti di celle miste
Comprendere questi diversi tipi di riferimenti di cella ti aiuterà a lavorare con le formule e a risparmiare tempo (specialmente quando le formule vengono copiate e incollate).
Cosa sono i riferimenti di cella relativi in Excel?
Consentitemi di fare un semplice esempio per spiegare il concetto di riferimenti di cella relativi in Excel.
Supponiamo di avere un set di dati mostrato di seguito:
Per calcolare il totale per ogni articolo, dobbiamo moltiplicare il prezzo di ogni articolo per la quantità di quell'articolo.
Per il primo elemento, la formula nella cella D2 sarebbe B2* C2 (come mostrato di seguito):
Ora, invece di inserire la formula per tutte le celle una per una, puoi semplicemente copiare la cella D2 e incollarla in tutte le altre celle (D3: D8). Quando lo fai, noterai che il riferimento di cella si regola automaticamente per fare riferimento alla riga corrispondente. Ad esempio, la formula nella cella D3 diventa B3*C3 e la formula in D4 diventa B4*C4.
Vengono chiamati questi riferimenti di cella che si adattano quando la cella viene copiata riferimenti di cella relativi in Excel.
Quando utilizzare i riferimenti di cella relativi in Excel?
I riferimenti di cella relativi sono utili quando è necessario creare una formula per un intervallo di celle e la formula deve fare riferimento a un riferimento di cella relativo.
In questi casi, puoi creare la formula per una cella e copiarla e incollarla in tutte le celle.
Cosa sono i riferimenti di cella assoluti in Excel?
A differenza dei riferimenti di cella relativi, i riferimenti di cella assoluti non cambiano quando copi la formula in altre celle.
Ad esempio, supponi di avere il set di dati come mostrato di seguito in cui devi calcolare la commissione per le vendite totali di ciascun articolo.
La commissione è del 20% ed è indicata nella cella G1.
Per ottenere l'importo della commissione per ogni vendita di articoli, utilizza la seguente formula nella cella E2 e copia per tutte le celle:
=D2*$G$1
Nota che ci sono due simboli del dollaro ($) nel riferimento della cella che ha la commissione - $G$2.
Cosa fa il segno del dollaro ($)?
Un simbolo del dollaro, se aggiunto davanti al numero di riga e colonna, lo rende assoluto (ovvero, impedisce al numero di riga e colonna di cambiare quando viene copiato in altre celle).
Ad esempio, nel caso precedente, quando copio la formula dalla cella E2 a E3, cambia da =D2*$G$1 a =D3*$G$1.
Nota che mentre D2 cambia in D3, $ G $ 1 non cambia.
Poiché abbiamo aggiunto un simbolo del dollaro davanti a "G" e "1" in G1, non lascerebbe cambiare il riferimento di cella quando viene copiato.
Quindi questo rende assoluto il riferimento di cella.
Quando utilizzare i riferimenti di cella assoluti in Excel?
I riferimenti di cella assoluti sono utili quando non vuoi che il riferimento di cella cambi mentre copi le formule. Questo potrebbe essere il caso quando hai un valore fisso che devi utilizzare nella formula (come aliquota fiscale, aliquota di commissione, numero di mesi, ecc.)
Sebbene tu possa anche codificare questo valore nella formula (cioè, usa il 20% invece di $ G $ 2), averlo in una cella e quindi usare il riferimento di cella ti consente di cambiarlo in una data futura.
Ad esempio, se la struttura delle commissioni cambia e ora stai pagando il 25% anziché il 20%, puoi semplicemente modificare il valore nella cella G2 e tutte le formule si aggiorneranno automaticamente.
Cosa sono i riferimenti di cella misti in Excel?
I riferimenti di cella misti sono un po' più complicati dei riferimenti di cella assoluti e relativi.
Ci possono essere due tipi di riferimenti di cella misti:
- La riga è bloccata mentre la colonna cambia quando viene copiata la formula.
- La colonna è bloccata mentre la riga cambia quando viene copiata la formula.
Vediamo come funziona usando un esempio.
Di seguito è riportato un set di dati in cui è necessario calcolare i tre livelli di commissione in base al valore percentuale nella cella E2, F2 e G2.
Ora puoi usare la potenza del riferimento misto per calcolare tutte queste commissioni con una sola formula.
Inserisci la formula seguente nella cella E4 e copia per tutte le celle.
=$B4*$C4*E$2
La formula sopra utilizza entrambi i tipi di riferimenti di cella misti (uno in cui la riga è bloccata e uno in cui è bloccata la colonna).
Analizziamo ogni riferimento di cella e capiamo come funziona:
- $B4 (e $C4) - In questo riferimento, il simbolo del dollaro si trova subito prima della notazione della colonna ma non prima del numero della riga. Ciò significa che quando copi la formula nelle celle a destra, il riferimento rimarrà lo stesso poiché la colonna è fissa. Ad esempio, se si copia la formula da E4 a F4, questo riferimento non cambierà. Tuttavia, quando lo copi, il numero di riga cambierà in quanto non è bloccato.
- E $ 2 - In questo riferimento, il simbolo del dollaro è subito prima del numero di riga e la notazione della colonna non ha il simbolo del dollaro. Ciò significa che quando si copia la formula nelle celle, il riferimento non cambierà poiché il numero di riga è bloccato. Tuttavia, se copi la formula a destra, l'alfabeto delle colonne cambierà in quanto non è bloccato.
Come cambiare il riferimento da relativo ad assoluto (o misto)?
Per modificare il riferimento da relativo ad assoluto, è necessario aggiungere il simbolo del dollaro prima della notazione della colonna e del numero di riga.
Ad esempio, A1 è un riferimento di cella relativo e diventerebbe assoluto quando lo fai $ A $ 1.
Se hai solo un paio di riferimenti da modificare, potresti trovare facile modificare questi riferimenti manualmente. Quindi puoi andare alla barra della formula e modificare la formula (o selezionare la cella, premere F2 e quindi cambiarla).
Tuttavia, un modo più rapido per farlo è utilizzare la scorciatoia da tastiera - F4.
Quando selezioni un riferimento di cella (nella barra della formula o nella cella in modalità di modifica) e premi F4, il riferimento cambia.
Supponiamo di avere il riferimento =A1 in una cella.
Ecco cosa succede quando selezioni il riferimento e premi il tasto F4.
- Premi il tasto F4 una volta: Il riferimento di cella cambia da A1 a $ A $ 1 (diventa "assoluto" da "relativo").
- Premi il tasto F4 due volte: Il riferimento di cella cambia da A1 a A$1 (cambia in riferimento misto in cui la riga è bloccata).
- Premi il tasto F4 tre volte: Il riferimento di cella cambia da A1 a $A1 (cambia in riferimento misto in cui la colonna è bloccata).
- Premi il tasto F4 quattro volte: Il riferimento di cella diventa di nuovo A1.
