Quando gestisci molti dati in Excel, puoi utilizzare riferimenti relativi, assoluti o misti per risparmiare lavoro manuale ripetitivo, garantire che le formule funzionino correttamente e acquisire grandi volumi di dati in poche rapide azioni. Esploriamo come utilizzare questi tipi di riferimento di Excel.
Cosa sono i riferimenti alle celle di Excel?
Si utilizza un riferimento di cella quando si desidera acquisire informazioni contenute in un’altra cella o intervallo di celle. Ad esempio, la formula seguente fa riferimento alle celle A1 e A2 e ne sommerà i contenuti per produrre un risultato.
=SUM(A1+A2) È possibile utilizzare i seguenti tipi di riferimenti di cella per ottenere risultati specifici:
- Per impostazione predefinita, i riferimenti in Excel sono parente, che si riferisce alla posizione relativa della cella. Se stai digitando una formula nella cella A1 che coinvolge la cella A2, stai facendo riferimento alla cella che si trova una cella sotto quella in cui stai digitando. Se copiassi la formula in un’altra cella, il riferimento eseguirebbe automaticamente la stessa azione relativa della posizione precedente.
- Puoi anche usare un assoluto riferimento, che non cambia se copi la formula in un’altra cella. Se crei una formula che fa riferimento a una cella utilizzando un riferimento assoluto, verrà fatto riferimento alla stessa cella ovunque venga utilizzata la formula.
- Infine, puoi usare a misto riferimento, che è una combinazione dei due.
Esaminiamoli più in dettaglio.
Come utilizzare i riferimenti relativi in Excel
Nell’esempio mostrato qui, puoi vedere quanto la persona ha speso per ciascun articolo in ogni mese. Quello che ora vogliono fare è calcolare la spesa totale per ogni mese.
Per prima cosa, fai clic sulla cella in cui desideri che venga effettuato il primo calcolo (in questo caso, H2) e digita la formula per creare questo calcolo. In questo esempio, utilizzeresti la seguente formula:
=SUM(E2+E12+E22) Questo ora ha fatto riferimento alle celle E2, E12 ed E22 all’interno del calcolo, sommandole insieme. Questo è un riferimento relativo: la formula nella cella H2 ha fatto riferimento alle celle che sono tre colonne a sinistra (E2), tre colonne a sinistra e dieci righe in basso (E12) e tre colonne a sinistra e venti righe in basso ( E22), rispettivamente.
Ora, se copiassi e incollassi la stessa formula altrove, raccoglierebbe i valori che si trovano nelle stesse posizioni relative a dove la incolli. Puoi anche utilizzare la funzione di riempimento automatico di Excel per completare il resto dei tuoi dati con lo stesso relativo riferimento.
Come puoi vedere qui, la formula nella cella H11 è:
=SUM(E11+E21+E31) Questo perché il riferimento relativo è posizionato nove righe più in basso rispetto a dove si trovava originariamente. Ora, invece di digitare la formula per ogni mese, devi digitarla una sola volta e poi applicare lo stesso riferimento relativo dove vuoi sul tuo foglio di lavoro Excel.
Come utilizzare i riferimenti assoluti in Excel
In questo esempio, il contabile dell’azienda vuole stabilire quanto pagare i dipendenti in base al numero di ore lavorate e ad una tariffa oraria fissa.
Inizia facendo clic sulla cella in cui desideri calcolare il primo totale (in questo caso, E2) e inizia a digitare la formula appropriata. In questo esempio, il contabile desidera moltiplicare la tariffa oraria nella cella A2 per il totale delle ore lavorate da Jacinda in D2.
=SUM(A2 Tuttavia, se qui utilizziamo un riferimento relativo, quando applichiamo la formula ai totali degli altri dipendenti, la formula farà riferimento alla cella A3 per Raul, A4 per Lucy e così via, il che significa che non otterrai i risultati desiderati.
Dobbiamo quindi assicurarci che Excel continui a fare riferimento alla cella corretta, che in questo caso è A2. Per fare ciò, devi inserire il simbolo del dollaro prima di ogni parte del tuo riferimento:
=SUM($A$2 Questo ora dice a Excel che se utilizziamo la stessa formula altrove, verrà sempre fatto riferimento ad A2.
Invece di digitare manualmente i simboli del dollaro, dopo aver digitato il riferimento della cella, premi F4 (potrebbe essere necessario scopri cosa fa il tasto funzionea seconda della tastiera) e i simboli del dollaro appariranno automaticamente.
Ora puoi completare la tua formula. In questo caso, sarà il seguente:
=SUM($A$2*D2) Da notare come la seconda parte della formula (D2) non utilizzi un riferimento assoluto. Questo perché vogliamo moltiplicare la cella A2 per le rispettive ore di ciascun dipendente, quindi deve essere relativo alle ore di ciascuna persona nella colonna D.
Ora puoi applicare la formula alle altre celle copiare e incollare it o utilizzando la funzione di riempimento automatico di Excel, sapendo che la cella A2 è un riferimento assoluto.
Come utilizzare riferimenti misti in Excel
Potrebbero esserci occasioni in cui è necessario bloccare la riga ma non la colonna all’interno della formula o viceversa. Ad esempio, in questo caso, il contabile vuole calcolare quante tasse deve pagare ciascun dipendente in base alle aliquote fiscali individuali.
Dobbiamo quindi bloccare i valori nella colonna E (le aliquote fiscali), ma non le righe, perché sono diverse per ogni dipendente, e non i riferimenti agli anni, perché devono essere adattabili.
Fai clic sulla cella in cui desideri che venga effettuato il primo calcolo (in questo caso, la cella G2) e inserisci la formula. Nell’esempio sopra, il contabile vuole iniziare calcolando l’imposta dovuta da Arjun nel 2021, quindi digiterà la seguente formula:
=SUM(B2*E2) Ora dobbiamo bloccare la colonna E. Se dovessimo compilare automaticamente a destra senza modificare la formula, non calcolerebbe correttamente l’imposta dovuta da Arjun nel 2022; mentre rileverebbe accuratamente i totali corretti del reddito annuale (perché non stiamo bloccando la cella B2), la formula farebbe riferimento anche alla cella F2. Per fare ciò, aggiungi il simbolo del dollaro prima del riferimento alla colonna E nella formula:
=SUM(B2*$E2) Non vogliamo utilizzare un riferimento assoluto qui perché ciò significherebbe che, quando calcoliamo l’imposta dovuta per gli altri dipendenti, verrebbe utilizzata l’aliquota fiscale di Arjun invece delle aliquote individuali di ciascun dipendente. In sostanza, bloccare solo la colonna E significa che rimarremo sulle aliquote fiscali ma potremo adattarci relativamente alle diverse aliquote di ciascuna persona.
Ora possiamo utilizzare la funzione di Compilazione automatica per completare i totali delle imposte dovute di Arjun, sapendo che la colonna E rimarrà bloccata nei nostri calcoli, mentre i totali per ogni anno si sposteranno rispetto a dove è posizionata la formula.
Poiché abbiamo bloccato la colonna E ma lasciato le righe relative all’interno della nostra formula, possiamo completare con sicurezza i totali dei restanti dipendenti in base alle loro aliquote fiscali individuali. Ricorda, puoi fare clic su qualsiasi cella all’interno dei risultati e visualizzare la formula nella barra della formula per verificare che venga fatto riferimento alle celle corrette.
Come passare tra diversi tipi di riferimenti di cella in Excel
Se desideri alternare facilmente tra riferimenti relativi, assoluti e misti nel tuo foglio Excel, non è necessario digitare il simbolo del dollaro ogni volta. Invece, fai clic sulla cella che desideri modificare e quindi, nella barra della formula, fai clic sulla parte della formula che desideri cambiare (funzionerà anche facendo clic direttamente prima del riferimento, al centro del riferimento o direttamente dopo il riferimento).
In questo caso, vogliamo alternare tra i tipi di riferimento di cella per la cella G11, quindi dobbiamo assicurarci di fare clic da qualche parte sopra o accanto a quel riferimento di cella.
Quindi, premi F4 e vedrai cambiare il tipo di riferimento della cella.
Passerà prima a un riferimento assoluto:
$G$11 Quindi un riferimento misto con la riga bloccata:
G$11 Quindi un riferimento misto con la colonna bloccata:
$G11 E, infine, tornerà all’impostazione predefinita, un riferimento relativo:
G11 Se desideri alternare più riferimenti nella stessa formula contemporaneamente, evidenzia semplicemente i riferimenti rilevanti nella formula e segui gli stessi passaggi.
