Chi usa un foglio di calcolo prima o poi copia una formula verso il basso e scopre che i risultati sono sbagliati, oppure che compaiono zeri e errori dove non dovrebbero esserci. Nella grande maggioranza dei casi il problema non è la formula in sé, ma il tipo di riferimento che contiene. Capire la differenza tra riferimenti relativi, assoluti e misti è la base di tutto il resto: tabelle di calcolo, funzioni di ricerca, formattazione condizionale.
Che cosa succede quando copi una formula
Un foglio di calcolo non memorizza davvero «la cella B2». Memorizza una posizione relativa: «la cella che si trova una colonna a sinistra e sulla stessa riga». Se in C2 scrivi =B2*2 e copi la formula in C3, il programma mantiene la stessa distanza e la trasforma in =B3*2. Questo comportamento si chiama riferimento relativo ed è quello predefinito in Excel, LibreOffice Calc e Google Sheets.
Nella maggior parte dei casi è esattamente ciò che vuoi: calcolare il totale di ogni riga, la percentuale di ogni voce, il prezzo con IVA di ogni prodotto. Il problema nasce quando una parte della formula deve restare ferma.
Il riferimento assoluto
Immagina una colonna di prezzi netti in B2:B50 e un'aliquota IVA scritta una sola volta nella cella F1. In C2 scrivi =B2*(1+F1). Copiando verso il basso, la formula in C3 diventa =B3*(1+F2), ma F2 è vuota e il risultato coincide con il prezzo netto. Nessun messaggio di errore, solo numeri sbagliati: è il tipo di errore più pericoloso.
La soluzione è bloccare il riferimento all'aliquota con il simbolo del dollaro: =B2*(1+$F$1). Il dollaro davanti alla lettera blocca la colonna, quello davanti al numero blocca la riga. Con entrambi, il riferimento resta identico ovunque tu copi la formula.
I riferimenti misti
Tra i due estremi ci sono i riferimenti misti, in cui è bloccata solo una delle due coordinate:
| Scrittura | Colonna | Riga | Uso tipico |
|---|---|---|---|
A1 |
si sposta | si sposta | calcoli riga per riga |
$A$1 |
fissa | fissa | parametro unico (aliquota, cambio) |
$A1 |
fissa | si sposta | valore in una colonna di intestazione |
A$1 |
si sposta | fissa | valore in una riga di intestazione |
I riferimenti misti diventano indispensabili quando una sola formula deve riempire un'intera tabella a due dimensioni.
Esempio: tabella di moltiplicazione o di sconti
Supponi di avere in B1:F1 una serie di percentuali di sconto e in A2:A20 una serie di prezzi. Vuoi riempire la griglia con il prezzo scontato per ogni combinazione. In B2 scrivi:
=$A2*(1-B$1)
Il prezzo è sempre nella colonna A, quindi blocchi la colonna; lo sconto è sempre nella riga 1, quindi blocchi la riga. Ora puoi copiare B2 su tutta la griglia e ogni cella leggerà il prezzo giusto e lo sconto giusto. Una formula, cento risultati corretti.
Il tasto per cambiare tipo di riferimento
Non serve digitare i dollari a mano. In Excel, mentre modifichi una formula, posiziona il cursore su un riferimento e premi F4: il riferimento passa ciclicamente da A1 a $A$1, poi A$1, poi $A1 e di nuovo A1. In LibreOffice Calc la stessa funzione si ottiene con Maiusc+F4. In Google Sheets si usa F4 come in Excel. Su alcuni portatili bisogna premere anche il tasto Fn.
Errori frequenti
- Bloccare troppo. Chi ha appena scoperto il dollaro tende a metterlo ovunque. Se blocchi anche B2 nella formula dell'IVA, ogni riga calcolerà l'IVA sul primo prezzo.
- Dimenticare il dollaro negli intervalli di ricerca. Nelle funzioni come CERCA.VERT l'intervallo della tabella deve essere assoluto, altrimenti scorre verso il basso insieme alla formula e le ultime righe non trovano più i dati.
- Spostare celle invece di copiarle. Se trascini (tagli e incolli) una formula, i riferimenti non cambiano, relativi o no. Il comportamento relativo vale solo per la copia.
- Inserire righe sopra il parametro. I riferimenti assoluti si aggiornano comunque se inserisci righe o colonne:
$F$1diventa$F$2se aggiungi una riga in cima. È un comportamento corretto, ma sorprende chi pensa che «assoluto» significhi «immutabile».
Un'alternativa più leggibile: i nomi
Per i parametri unici, come un'aliquota o un tasso di cambio, conviene spesso assegnare un nome alla cella, per esempio Aliquota_IVA. La formula diventa =B2*(1+Aliquota_IVA): si legge meglio e il nome si comporta come un riferimento assoluto. Nel mio lavoro uso i nomi per tutti i valori che un collega potrebbe dover modificare, così è chiaro dove intervenire senza toccare le formule.
Come controllare se hai sbagliato
Un metodo rapido: seleziona una cella in fondo alla colonna copiata e premi F2 (o fai doppio clic). Il programma evidenzia con colori diversi le celle a cui la formula fa riferimento. Se il riquadro colorato dell'aliquota si è spostato in una cella vuota, hai trovato l'errore. In alternativa puoi attivare la visualizzazione delle formule (in Excel con Ctrl+ò su tastiera italiana, o dal menu Formule) e scorrere la colonna per vedere a colpo d'occhio dove cambiano i riferimenti.
In sintesi
- Il riferimento relativo si adatta quando copi la formula; è il comportamento predefinito.
- Il dollaro blocca la colonna (
$A1), la riga (A$1) o entrambe ($A$1). - I riferimenti misti permettono di riempire una tabella a due dimensioni con una sola formula.
- F4 (Maiusc+F4 in LibreOffice) cambia rapidamente il tipo di riferimento.
- Per i parametri unici i nomi di cella sono più leggibili del dollaro.
