Un riferimento assoluto tiene ferma una cella quando copi una formula. In =B2*$E$1, i simboli del dollaro bloccano E1: copia la formula verso il basso e ogni riga moltiplica ancora per E1, mentre B2 diventa B3, B4 e così via. Per aggiungere i simboli del dollaro, fai clic sul riferimento nella formula e premi F4 (su Mac, Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 è stata scritta una volta e copiata verso il basso. Fai clic su C4: la sua formula è =B4*$E$1. La cella delle vendite si è spostata alla riga 4, il tasso è rimasto in E1. Cambia il tasso in E1 in 8% e ogni provvigione si aggiorna.
Riferimenti relativi e assoluti
| Riferimento | Nome | Copiato una riga più in basso e una colonna a destra |
|---|---|---|
A1 | relativo | B2 |
$A$1 | assoluto | $A$1 |
A$1 | misto: riga bloccata | B$1 |
$A1 | misto: colonna bloccata | $A2 |
Un riferimento semplice come B2 è relativo: Excel lo memorizza come "la cella in questa posizione rispetto a me", quindi una copia una riga più in basso punta una riga più in basso. È esattamente ciò che vuoi per i dati riga per riga, ed è il comportamento predefinito. Il $ davanti alla lettera della colonna o al numero della riga blocca quella parte.
L'errore classico: copiare verso il basso senza $
Ecco di nuovo la tabella delle provvigioni, con =B2*E1 in C2 e senza simboli del dollaro. La prima riga è giusta. Le altre valgono 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 contiene =B3*E2: il riferimento al tasso è sceso a E2, che è vuota, e una cella vuota conta come 0. Correggila qui: fai clic su C2, cambia la formula in =B2*$E$1 e premi Invio. Tutta la colonna segue, perché C3:C6 sono copie di C2. Quando la cella fissa è un divisore, come in =B2/B7 per la quota di un totale, lo stesso errore mostra #DIV/0! invece di 0 (la percentuale sul totale è il caso tipico).
Premi F4 per aggiungere i simboli del dollaro
Mentre scrivi o modifichi una formula, metti il cursore in un riferimento (o subito dopo) e premi F4. Ogni pressione passa alla forma successiva:
E1 -> $E$1 -> E$1 -> $E1 -> E1
Su molti portatili F4 controlla lo schermo o il volume, quindi premi Fn+F4. Su Mac, usa Cmd+T, oppure Fn+F4. Puoi anche scrivere i simboli $ a mano.
Riferimenti misti: bloccare solo la riga o la colonna
Un riferimento misto ha un solo simbolo del dollaro. $A2 legge sempre la colonna A ma lascia muovere la riga; B$1 legge sempre la riga 1 ma lascia muovere la colonna. Con entrambi nella stessa formula, una sola formula copiata su una griglia costruisce una tavola pitagorica:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 contiene =$A2*B$1. Fai clic su F6: contiene =$A6*F$1, il numero di riga dalla colonna A per il numero di colonna dalla riga 1, quindi mostra 25. Togli un simbolo del dollaro in B2 e la tavola si sfascia, perché le copie iniziano a moltiplicare celle vicine invece delle intestazioni.
Lo stesso schema calcola un listino con più sconti: =$A2*(1-B$1) con i prezzi lungo la colonna A e gli sconti lungo la riga 1.
Totale progressivo con un intervallo bloccato a metà
Un intervallo può essere bloccato a un solo estremo. =SOMMA($B$2:B2) parte sempre da B2, mentre la sua fine scende man mano che la formula viene copiata, così ogni riga somma tutto fino a se stessa. La tabella mostra le formule in inglese, ma puoi scriverle anche in italiano, con il punto e virgola.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SOMMA($B$2:B2)C6 contiene =SOMMA($B$2:B6) e mostra 2230, il totale di tutti e cinque i mesi. Lo stesso intervallo bloccato a metà fa sì che =CONTA.SE($A$2:A2;A2) conti quante volte un valore è comparso finora, ed è così che si segnalano i duplicati dopo il primo.
Esercizio: una formula per tutta la tabella
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Tocca a te: In B2, scrivi il prezzo del primo prodotto dopo lo sconto in B1. Usa $ in modo che la stessa formula, copiata in orizzontale e in verticale fino a D5, dia ogni prezzo della tabella.
La tabella copia la tua formula in ogni cella di B2:D5, come farebbe il quadratino di riempimento, e Verifica legge tutti e dodici i risultati. Senza i simboli del dollaro giusti, le copie nella riga 3 o nella colonna C leggono il prezzo sbagliato o lo sconto sbagliato.
Riferimenti assoluti verso un altro foglio o una tabella di ricerca
I simboli del dollaro funzionano allo stesso modo con il nome di un foglio: =B2*Settings!$B$1. Contano soprattutto nelle ricerche, dove la tabella deve restare ferma mentre il valore cercato si sposta: =CERCA.VERT(A2;$E$2:$F$10;2;FALSO) copiata verso il basso continua a cercare in E2:F10, mentre =CERCA.VERT(A2;E2:F10;2;FALSO) fa scendere la tabella di una riga a ogni copia e comincia a perdere le prime righe (CERCA.VERT, in inglese VLOOKUP). Se una cella fissa viene usata in molte formule, puoi anche darle un nome con Formule > Definisci nome e scrivere =B2*Rate; un nome definito così punta alla stessa cella da ogni formula, come $E$1.
Domande frequenti
Cosa significa il simbolo $ in una formula di Excel?
Blocca la parte del riferimento che lo segue. In $E$1 sono bloccate sia la colonna E sia la riga 1, quindi il riferimento resta E1 ovunque venga copiata la formula. E$1 blocca solo la riga e $E1 solo la colonna.
Qual è la scorciatoia per il riferimento assoluto in Excel?
Fai clic dentro il riferimento mentre modifichi la formula e premi F4 (Fn+F4 su molti portatili). Ogni pressione passa da $A$1 a A$1, $A1 e A1. Su Mac, premi Cmd+T, oppure Fn+F4.
Che differenza c'è tra riferimenti relativi e assoluti?
Un riferimento relativo come B2 si sposta quando copi la formula: una riga più in basso diventa B3. Un riferimento assoluto come $B$2 resta $B$2. Usa i riferimenti assoluti per una singola cella che serve a ogni riga, come un tasso o un totale.
Cos'è un riferimento misto in Excel?
Un riferimento con un solo simbolo del dollaro: $A2 tiene ferma la colonna e lascia muovere la riga, B$1 tiene ferma la riga e lascia muovere la colonna. =$A2*B$1 copiata in orizzontale e in verticale su una griglia costruisce una tavola pitagorica.
Perché la mia formula mostra 0 o #DIV/0! dopo averla trascinata in basso?
Un riferimento che doveva restare fisso si è spostato con la copia. Se la riga 2 ha =B2/B7, la riga 3 riceve =B3/B8, e B8 è vuota. Blocca il totale con =B2/$B$7 e copia di nuovo.
