Funzioni Excel: riferimenti relativi e assoluti

I riferimenti sono uno strumento importante per sfruttare al meglio le funzioni excel, per utilizzarne a pieno il potenziale occorre soffermarsi sulle differenze fra relativi e assoluti, interni ed esterni, diretti e indiretti.

 

 

Come descritto in un precedente articolo si può passare in input ad una funzione il contenuto di una cella o intervallo che si trova in qualsiasi punto della cartella di lavoro, ma anche in un altro file. Il riferimento è sostanzialmente una indicazione di dove si trova il dato, cioè un indirizzo che indica ad excel in che luogo si trova l’informazione da elaborare, come nella vita quotidiana le indicazioni si possono dare in due modi:

• “Via Verdi n° 3 Milano, Italia”;
• “dritto due semafori, poi a destra, avanti dritto fino alla prima a sinistra: è il palazzo che fa angolo”;

La prima indicazione è un esempio di riferimento assoluto: con l’indirizzo corretto chiunque partendo da qualsiasi parte del mondo può arrivare all’obiettivo.

La seconda è un esempio di riferimento relativo: seguendo queste indicazioni alla lettera il luogo di arrivo dipende da dove si parte, e in molti casi può anche essere impossibile arrivare da qualsiasi parte.

Tornando ad excel, in prima battuta può sembrare che per non sbagliarsi convenga usare sempre i riferimenti assoluti, ma poi si scopre che in realtà i riferimenti predefiniti sono relativi ed è molto comodo usarli.

Riferimenti relativi: esempi e utilità

Per mostrare il funzionamento il modo migliore è partire con un esempio, si può riprendere quanto visto nel precedente articolo sui riferimenti o ricostruirlo in questo modo:

• Compilare le celle come in figura e digitare in C2 =A2*B2

 

La prima chiave di lettura per quella formula sembrerebbe essere “moltiplica il contenuto di A2 per quello di B2”, però facendo i passi seguenti:

• Premere il pulsante destro del mouse in C2
• Scegliere Copia
• Portarsi in I2 e premere nuovamente
• Scegliere la prima opzione del menu incolla

Il risultato dell’operazione, visibile nell’immagine di seguito, è che in I2 excel ha scritto =G2*H2, questo si spiega perché la formula di partenza contenteva dei riferimenti relativi, cioè il contenuto di C2 non va letto “moltiplica A1 per B1” ma piuttosto “moltiplica il valore due celle a sinistra con il valore una cella a sinistra”. Quindi come nell’esempio delle indicazioni stradali “girare alla prima a sinistra” porta in luoghi diversi secondo dove ci si trova al momento di seguire l’indicazione.

Questo comportamento ha senso ed è utile, come si può vedere facilmente dall’esempio seguente:

• Selezionare A2 e B2
• Copia
• Selezionare da A3 a B10
• Incolla

• Posizionarsi in C2
• Copia
• Selezionare da C3 a C10
• Incolla

A questo punto, osservando i contenuti della colonna C si nota che tutte le formule sono aggiornare coerentemente, cioè in ogni riga il valore dell’IVA è esattamente l’aliquota per l’imponibile della riga stessa. Fare la stessa cosa senza l’ausilio dei riferimenti relativi avrebbe significato riscrivere dieci volte la formula. Analogamente se si copia da A1 a C10, ci si porta in H1 e si incolla, il risultato è un blocco di dati e formule perfettamente coerente.

Infine va ricordato che se si esegue questa operazione:

• Copiare C10
• Incollare in A12

Il risultato è un messaggio di errore in quanto il riferimento dato nella formula “valore immediatamente a sinistra” in questo caso non porta in nessun posto.

Riferimenti assoluti: esempi e utilità

Continuando sul calcolo dell’IVA si può vedere velocemente a cosa serve un riferimento assoluto in questo modo:

• Compilare l’intervallo da C1 a C10 come in figura
• Portarsi in A2;
• Selezionare la casella nome (riquadro bianco in alto a sinistra subito sopra alle intestazioni di colonna);
• Digitare Aliquota

Come evidenziato nell’articolo precedente, selezionando la freccetta sul lato destro della casella nome si può vedere un elenco a discesa con tutti i nomi inseriti prima, e usarli per selezionare le aree corrispondenti.

Dato che i nomi si possono usare direttamente nelle formule, si può procedere così:

• Selezionare D2
• Digitare =Aliquota*C2
• Ricopiare la formula fino a D10

Il risultato è leggermente diverso da quello ottenuto in precedenza, infatti esaminando una delle formule, ad esempio in D10:

• Il riferimento ad Aliquota continua a puntare in A2 (perché era assoluto)
• L’altro fattore della moltiplicazione invece si è aggiornato (perché era relativo)

Con questo modo di scrivere quindi si ordina ad excel “moltiplica il valore all’incrocio fra la colonna A e la riga due, con quello una cella a sinistra”; fatto che si è rivelato piuttosto utile per ottenere una formula che ancora una volta si è aggiornata automaticamente.

L’uso dei nomi per i riferimenti assoluti è intuitivo e sicuramente rende più facile la spiegazione, va però detto che non sempre è comodo e indicato assegnare nomi a tutte le celle da richiamare nelle formule (sia per questioni di comodità che di prestazioni del foglio elettronico). Per ottenere lo stesso risultato senza usare il nome:

• Cancellare da D2 a D10
• Digitare in D2 =$A$2*C2
• Ricopiare fino a D10

Il simbolo $ (dollaro) infatti indica ad excel che il riferimento è da considerarsi assoluto, cioè “incrocia la colonna A con la riga 2”. Utilizzando il simbolo $ si possono poi costruire dei casi “misti”, ad esempio:

• Si parta preparando un foglio come nell’immagine di seguito

• In G2 inserire =A2
• Premere il tasto funzione F4 (normalmente nella fila più in alto della tastiera)
• La formula diventa =$A$2
• Premere nuovamente F4
• La formula diventa =A$2
• Completare fino ad avere =A$2*G2

• Battere invio, poi trascinare fino a G10
• Selezionare l’intervallo da G2 a G11
• Copia
• Selezionare H2
• Incolla

Il risultato, visibile in figura, è ancora che con trascinamento e incolla le formule si sono  adeguate esattamente come serve; infatti scrivendo A$2 si intende un riferimento relativo di colonna e assoluto di riga che quindi viene letto “incrocio 6 colonne a sinistra con la riga numero 2”.

 

In conclusione, oltre a rendere interattivi i calcoli, usando opportunamente i riferimenti assoluti e relativi si riesce a incollare formule che fanno cose simili su intervalli simili senza riscriverle.

Riferimenti interni ed esterni

Fino a qui si sono utilizzati  solo riferimenti all’interno delle stessa cartella e sul medesimo foglio di lavoro, ma se ne possono inserire anche ad altri fogli della stessa cartella e anche ad altri file. Per vedere un primo esempio è sufficiente preparare una cartella simile a quello appena visto, ma con le aliquote nel foglio1 ed imponibili e formule nel foglio 2:

• Selezionare G2
• Scrivere =
• Con il mouse toccare la linguetta del foglio3
• Si noti che rimangono bianche (cioè attive) le schede di tutti e due i fogli
• Attivare A2
• Excel predispone un riferimento del tutto simile al solito ma preceduto dal nome e punto esclamativo
• Premere F4 fino a che la formula diventa =Foglio3!A$2

 

• Completare =Foglio3!A$2*E2
• Battere invio
• Excel si riporta nella pagina precedente e mostra il risultato

• Trascinare fino a G10
• Selezionare l’intervallo da G2 a G10
• Copia
• Portarsi in H2
• Incolla

Si ottiene esattamente il risultato di prima, salvo che uno dei fattori della moltiplicazione sta in un’altra pagina, cioè nel riferimento si sta indicando: “all’incrocio di quale riga con quale colonna su quale foglio”.

 

Estendendo lo stesso criterio si possono creare anche riferimenti esterni, cioè riguardanti dati che si trovano su un altro file: “all’incrocio di quale riga con quale colonna su quale foglio di quale file”. Procedendo sempre per esempi:

• Creare un nuovo file con le due colonne imponibili come in figura
• Salvarlo con nome “Formule riferimenti.xlsx”
• Creare un altro file
• Inserire le caselle Aliquota come in figura
• Salvarlo con nome “Riferimenti esterni.xlsx”
• Dal menu visualizza disponi tutto come in figura, in questo modo si vedono entrambi i fogli

 

• Di nuovo in G2 digitare =
• Poi con il mouse toccare la griglia a destra che racchiude l’altro file
• Toccare A2
• La formula viene costruita automaticamente aggiungendo anche il nome del file all’indirizzo
• Da qui in avanti funziona tutto esattamente come nei casi precedenti

 

Riferimenti diretti e indiretti

Per finire un accenno ai riferimenti indiretti: Excel mette a disposizione una intera famiglia di funzioni pensate appositamente per costruire un riferimento, in modo da aggiungere ancora più flessibilità e interattività ai fogli di calcolo. Un esempio semplice si può vedere in questo modo:

• Preparare un foglio come nell’immagine seguente

• Portarsi in G2 e digitare =INDIRETTO(C6)*F2
• Battendo invio si visualizza il risultato

• Cambiando il contenuto di C6 e scrivendo C2 si vede che la formula usa l’aliquota IVA corrispondente

 

Unendo altre funzionalità di excel, quali la possibilità di immettere in C6 un elenco a discesa delle scelte possibili si ottiene una interattività ancora migliore.

Le funzioni di ricerca e riferimento in excel sono molte e meritano una discussione più completa su un articolo a parte, in quanto permettono di realizzare fogli molto potenti e flessibili con estrema semplicità; in ogni caso, familiarizzare con i concetti discussi in questo articolo permette di usare le funzioni excel per costruire fogli di una certa complessità senza necessariamente essere dei programmatori.