Inserire riferimenti ai fogli di Excel in modo relativo nelle formule

Quando copi una formula da una cella a un’altra, Excel aggiorna automaticamente tutti i riferimenti relativi all’interno della formula in base alla destinazione che che gli dai. Ad esempio, supponiamo che la cella B7 contenga la seguente formula:

=B6+A7

Se copi questa formula nella cella D22, Excel aggiorna automaticamente i riferimenti in modo da essere relativi alla cella D22, come mostrato qui:

=D21+C22

Quando si copiano formule da un foglio di lavoro a un altro e la formula contiene un riferimento a un foglio di lavoro precedente, Excel non esegue questo tipo di aggiornamento delle formule, almeno non sui nomi dei fogli di lavoro. Ad esempio, supponiamo di avere tre fogli di lavoro denominati Gennaio, Febbraio e Marzo, in quest’ordine. Nel foglio di lavoro di febbraio hai la seguente formula:

=Gennaio!B7*1,075

Se copi questa cella nel foglio di lavoro di marzo, Excel cambierà automaticamente il riferimento B7 (se necessario), ma non cambierà il nome del foglio (Gennaio, che era “uno in meno” rispetto al foglio su cui era stata impostata per la prima volta la formula) nel nome del foglio relativo rettificato (Febbraio, che è “uno in meno” del foglio in cui viene copiata la formula).

Se hai solo pochi riferimenti a fogli di lavoro nelle formule copiate, è abbastanza facile modificare le formule, in modo che facciano riferimento al foglio di lavoro corretto. L’attività può diventare rapidamente un incubo, tuttavia, se hai dozzine o centinaia di tali riferimenti.

La soluzione è eseguire una semplice operazione di ricerca e sostituzione in Excel, come descritto qui:

1. Copia le formule dal foglio di lavoro di febbraio nella posizione desiderata nel foglio di lavoro di marzo.
2. Con il foglio di lavoro di marzo visibile, premi Ctrl+A. Questo seleziona tutte le celle nel foglio di lavoro.
3. Premi Ctrl+Maiusc+S. Excel visualizza la scheda Sostituisci della finestra di dialogo Trova e sostituisci.
4. Nella casella Trova, inserisci “Gennaio!” (senza le virgolette).
5. Nella casella Sostituisci con, inserisci “Febbraio!” (senza le virgolette).

6. Fare clic su Sostituisci tutto.

Le formule nel foglio di lavoro sono ora aggiornate in modo che facciano riferimento al foglio di lavoro corretto.

Nota che nei passaggi 4 e 5 ciò che stai cercando e sostituendo non sono semplicemente i nomi dei mesi. Questo viene fatto perché i soli nomi dei mesi (gennaio, febbraio, ecc.) potrebbero facilmente trovarsi in altri punti del foglio di lavoro senza far parte di una formula. Non vuoi modificare queste istanze, quindi è stato inserito il carattere extra “!” per restringere la ricerca.

Se le tue esigenze non sono molto complesse, puoi anche utilizzare un approccio macro. Ad esempio, se desideri che una formula in una cella particolare faccia riferimento a una cella del foglio precedente al foglio corrente, puoi farlo tramite macro piuttosto facilmente. Considera la seguente:

La macro esamina il foglio di lavoro corrente e quindi determina quale foglio di lavoro si trova prima di esso. Il riferimento viene quindi impostato per quel foglio di lavoro. Dopo aver creato la macro PrevSheet, ecco come la funzione possa essere utilizzata in una cella:

=PrevSheet(A1)

Questa restituisce il valore della cella A1 dal foglio di lavoro precedente. Se hai Foglio1, Foglio2 e Foglio3 e usi questa formula su Foglio3, restituirà il valore “Foglio2!A1”. Se il foglio precedente è il primo foglio della cartella di lavoro o non è un foglio di lavoro, la funzione restituisce un errore #VALORE.

Se in seguito copi questa formula su un foglio diverso (diciamo su Foglio5), essa estrarrà il valore relativo alla sua nuova posizione, il che significa “Foglio4!A1”.

Puoi anche includere un nome di foglio e la funzione funzionerà perfettamente:

=PrevSheet(Foglio3!A5)

Questa versione restituirà sempre Foglio2!A5 poiché Foglio2 è il foglio precedente di Foglio3.