Per eseguire calcoli accurati in Excel, è essenziale capire come funzionano i diversi tipi di riferimenti di cella.
Riferimenti A1 vs. R1C1
I fogli di lavoro di Excel contengono molte celle e (per impostazione predefinita) ogni cella è identificata dal suo lettera di colonna seguito dal suo numero di riga. Questo è noto come riferimento in stile A1. Esempi: LA1, SI4, DO6
Stile di riferimento A1
Facoltativamente, puoi passare a Modalità di riferimento R1C1 per fare riferimento alla riga di una cella & numero di colonna. Invece di fare riferimento alla cella A1, faresti riferimento a R1C1 (riga 1, colonna 1). La cella C4 verrebbe riferita a R4C3.
Stile di riferimento R1C1
Il riferimento in stile R1C1 è estremamente raro in Excel. A meno che tu non abbia una buona ragione, dovresti probabilmente attenerti alla modalità di riferimento predefinita in stile A1. Tuttavia, se utilizzi VBA, probabilmente incontrerai questo stile di riferimento.
Passa allo stile di riferimento R1C1
Per cambiare lo stile di riferimento, vai a File > Opzioni > Formula. Seleziona la casella accanto a Stile di riferimento R1C1.
Intervalli denominati
Una delle funzionalità più sottoutilizzate di Excel è la funzionalità Named Ranges. Invece di fare riferimento a una cella (o gruppo di celle) in base alla posizione della cella (es. B3 o R3C2), puoi denominare quell'intervallo e semplicemente fare riferimento al nome dell'intervallo nelle tue formule.
Per denominare un intervallo:
- Seleziona la cella o le celle che desideri nominare
- Fare clic all'interno della casella Nome intervallo
- Inserisci il nome che desideri
- Premi Invio
Ora puoi fare riferimento alla cella A1 digitando invece =range_name1. Questo è molto utile quando si lavora con cartelle di lavoro di grandi dimensioni con più fogli di lavoro.
Gamma di celle
Quando si utilizzano le funzioni integrate di Excel, potrebbe essere necessario fare riferimento a intervalli di celle. Gli intervalli di celle appaiono come questo "A1: B3". Questo riferimento si riferisce a tutte le celle comprese tra A1 e B3: celle A1, A2, A3, B1, B2, B3.
Per selezionare un intervallo di celle quando si immette una formula:
- Digitare l'intervallo (separare l'intervallo iniziale e finale con un punto e virgola)
- Usa il mouse per fare clic sul primo riferimento di cella, tieni premuto il pulsante del mouse e trascina l'intervallo desiderato.
- Tieni premuto il tasto Maiusc e naviga con i tasti freccia per selezionare il tuo intervallo
Riferimenti assoluti (congelati) e relativi
Quando si immettono riferimenti di cella all'interno delle formule, è possibile utilizzare riferimenti relativi o assoluti (congelati). I riferimenti relativi si sposteranno proporzionalmente quando copi la formula in una nuova cella. I riferimenti assoluti rimarranno invariati. Diamo un'occhiata ad alcuni esempi:
Riferimento relativo
Un riferimento relativo in Excel ha questo aspetto
=A1
Quando copi e incolli una formula con riferimenti relativi, i riferimenti relativi si sposteranno proporzionalmente. A meno che tu non specifichi diversamente, i tuoi riferimenti di cella saranno relativi (sbloccati) per impostazione predefinita.
Esempio: se si copia "=A1" in basso di una riga, il riferimento cambia in "=A2".
Riferimenti di cella assoluti (congelati)
Se non vuoi che i riferimenti di cella si spostino quando copi una formula, puoi "congelare" i riferimenti di cella aggiungendo i simboli del dollaro ($s) davanti al riferimento che desideri congelare. Ora quando copi e incolli la formula, il riferimento di cella rimarrà invariato. Puoi scegliere di bloccare il riferimento di riga, il riferimento di colonna o entrambi.
A1: Niente è congelato
$A1: la colonna è bloccata, ma la riga non è bloccata
A$1: la riga è bloccata, ma la colonna non è bloccata
$A$1: sia la riga che la colonna sono bloccate
Scorciatoia Riferimento Assoluto
Aggiungere manualmente i simboli del dollaro ($s) nelle formule non è molto pratico. Invece, durante la creazione della formula, usa il tasto F4 per alternare i riferimenti di cella assoluti/relativi.
Esempio di riferimento assoluto di cella
Quando avresti effettivamente bisogno di congelare un riferimento di cella? Un esempio comune è quando si hanno celle di input a cui si fa spesso riferimento. Nell'esempio seguente, vogliamo calcolare l'imposta sulle vendite per ogni quantità di voci di menu. L'imposta sulle vendite è costante su tutti gli articoli, quindi faremo riferimento ripetutamente alla cella dell'imposta sulle vendite.
Per trovare l'imposta totale sulle vendite, inserisci la formula "=(B3*C3)*$C$1" nella colonna D e copia la formula verso il basso.
Riferimento misto
Potresti aver sentito parlare di riferimenti a celle miste. Un riferimento misto è quando il riferimento di riga o di colonna è bloccato (ma non entrambi).
Riferimento misto
Ricorda, usando il tasto "F4" puoi scorrere i tuoi riferimenti di cella relativi e assoluti.
Riferimenti di cella - Inserimento ed eliminazione di righe/colonne
Ti starai chiedendo cosa succede ai tuoi riferimenti di cella quando inserisci o elimini righe/colonne?
Il riferimento di cella si aggiornerà automaticamente per fare riferimento alla cella originale. Questo è il caso indipendentemente dal fatto che il riferimento di cella sia bloccato.
Riferimenti 3D
A volte, potrebbe essere necessario lavorare con diversi fogli di lavoro con modelli di dati identici. Excel ti consente di fare riferimento a più fogli contemporaneamente senza dover inserire manualmente ciascun foglio di lavoro. Puoi fare riferimento a un intervallo di fogli in modo simile a come farebbe riferimento a un intervallo di celle. L'esempio "Foglio1:Foglio5!A1" farebbe riferimento alle celle A1 su tutti i fogli da Foglio1 a Foglio5.
Facciamo un esempio:
Vuoi sommare le unità totali vendute per ogni prodotto in tutti i negozi. Ogni negozio ha il proprio foglio di lavoro e tutti i fogli di lavoro hanno un formato identico. Potresti creare una formula simile a questa:
Questo non è troppo difficile con solo quattro fogli di lavoro, ma cosa succederebbe se avessi 40 fogli di lavoro? Vorresti davvero aggiungere manualmente ogni riferimento di cella?
Invece, puoi utilizzare un riferimento 3D per fare riferimento a più fogli contemporaneamente con facilità (in modo simile a come puoi fare riferimento a un intervallo di celle).
Stai attento! L'ordine dei fogli di lavoro è importante. Se sposti un altro foglio tra i fogli di riferimento (StoreA e StoreD) quel foglio verrà incluso. Al contrario, se si sposta un foglio al di fuori dell'intervallo dei fogli (prima di StoreA o dopo StoreD), non verrà più incluso.
Riferimento cella circolare
Un riferimento di cella circolare è quando una cella fa riferimento a se stessa. Ad esempio, se il risultato della cella B1 viene utilizzato come input per la cella B1, viene creato un riferimento circolare. La cella non ha bisogno di riferirsi direttamente a se stessa Ci possono essere passaggi intermedi.
Esempio:
In questo caso, la formula per la cella B2 è "A2+A3+B2". Poiché sei nella cella B2, potresti non utilizzare B2 nell'equazione. Ciò attiverebbe il riferimento circolare e il valore nella cella "B2" verrà impostato automaticamente su "0".
Di solito i riferimenti circolari sono il risultato di un errore dell'utente, ma in alcune circostanze potresti voler utilizzare un riferimento circolare. L'esempio principale di utilizzo di un riferimento circolare consiste nel calcolare i valori in modo iterativo. Per farlo, devi andare su File > Opzioni > Formule e Calcolo iterativo abilitato:
Riferimenti esterni
A volte, quando si calcolano i dati, potrebbe essere necessario fare riferimento a dati al di fuori della cartella di lavoro. Questo è chiamato un riferimento esterno (link).
Per selezionare un riferimento esterno durante la creazione di una formula, accedi alla cartella di lavoro esterna e seleziona il riferimento come faresti normalmente.
Per passare all'altra cartella di lavoro puoi utilizzare la scorciatoia CTRL + TAB o andare su Visualizza > Cambia Windows.
Una volta selezionata la cella, vedrai un riferimento esterno simile a questo:
Si noti che il nome della cartella di lavoro è racchiuso tra parentesi quadre [].
Una volta chiusa la cartella di lavoro di riferimento, il riferimento mostrerà il percorso del file:
Quando si riapre la cartella di lavoro contenente il collegamento esterno, verrà richiesto di abilitare l'aggiornamento automatico dei collegamenti. In tal caso, Excel aprirà il valore di riferimento con il valore corrente della cartella di lavoro. Anche se è chiuso! Stai attento! Potresti volerlo o meno.
Intervalli denominati e riferimenti esterni
Cosa succede al riferimento di cella esterno quando righe/colonne vengono aggiunte o eliminate dalla cartella di lavoro di riferimento? Se entrambe le cartelle di lavoro sono aperte, i riferimenti di cella vengono aggiornati automaticamente. Tuttavia, se entrambe le cartelle di lavoro non sono aperte, i riferimenti di cella non verranno aggiornati e non saranno più validi. Questa è una grande preoccupazione quando ci si collega a cartelle di lavoro esterne. Molti errori sono causati da questo.
Se ti colleghi a cartelle di lavoro esterne, dovresti nominare il riferimento di cella con un intervallo denominato (vedi sezione precedente per maggiori informazioni). Ora la tua formula farà riferimento all'intervallo denominato, indipendentemente dalle modifiche che si verificano nella cartella di lavoro esterna.
