Collegamenti in Excel: assoluti, relativi e misti. Errori durante l'utilizzo di collegamenti relativi in ​​Excel

Per facilitare i calcoli utilizzando le formule in modalità automatica, vengono utilizzati i riferimenti alle celle. A seconda del tipo di scrittura, si dividono in tre tipologie principali:

1. Collegamenti relativi. Usato per calcoli semplici. La copia di una formula comporta la modifica delle coordinate.
2. Collegamenti assoluti. Se è necessario produrre calcoli più complessi, questa opzione è adatta. Per il fissaggio utilizzare il simbolo “$”. Esempio: $A$1.
3. collegamenti misti. Questo tipo di indirizzamento viene utilizzato nei calcoli quando è necessario fissare separatamente una colonna o una riga. Esempio: $A1 o A$1.

Se è necessario copiare i dati della formula inserita, vengono utilizzati riferimenti con indirizzamento assoluto e misto. L'articolo rivelerà con esempi come vengono eseguiti i calcoli utilizzando vari tipi di collegamenti.

Riferimento di cella relativo in Excel

Questo è un insieme di caratteri che definiscono la posizione di una cella. I link nel programma vengono scritti automaticamente con relativo indirizzamento. Ad esempio: A1, A2, B1, B2. Lo spostamento in una riga o colonna diversa cambia i caratteri nella formula. Ad esempio, posizione di partenza A1. Lo spostamento in orizzontale cambia la lettera in B1, C1, D1, ecc. Allo stesso modo, i cambiamenti si verificano quando ci si sposta lungo una linea verticale, solo in questo caso il numero cambia: A2, A3, A4, ecc. Se è necessario duplicare un calcolo dello stesso tipo in una cella adiacente, un calcolo viene eseguito utilizzando un riferimento relativo. Per utilizzare questa funzione, segui alcuni passaggi:

1. Non appena i dati vengono inseriti nella cella, spostare il cursore e fare clic con il mouse. L'evidenziazione con un rettangolo verde indica l'attivazione della cella e la disponibilità per ulteriori lavori.
2. Premendo la combinazione di tasti Ctrl + C, copiamo il contenuto negli appunti.
3. Attiviamo la cella in cui vuoi trasferire i dati o una formula precedentemente scritta.
4. Premendo la combinazione Ctrl + V trasferiamo i dati salvati negli appunti di sistema.

Parere di esperti! Per eseguire lo stesso tipo di calcoli nella tabella, usa un life hack. Seleziona la cella contenente la formula inserita in precedenza. Passando il cursore su un quadratino che appare nell'angolo in basso a destra e tenendo premuto il pulsante sinistro del mouse, trascinare sulla riga inferiore o sulla colonna estrema, a seconda dell'azione eseguita. Rilasciando il pulsante del mouse, il calcolo verrà eseguito automaticamente. Questo strumento è chiamato indicatore di riempimento automatico.

Esempio di collegamento relativo

Per renderlo più chiaro, si consideri un esempio di calcolo che utilizza una formula con un riferimento relativo. Supponiamo che il proprietario di un negozio di articoli sportivi dopo un anno di lavoro debba calcolare il profitto dalle vendite.

L'ordine delle azioni:

1. L'esempio mostra che le colonne B e C sono state utilizzate per inserire la quantità di merce venduta e il suo prezzo. Di conseguenza, per scrivere la formula e ottenere la risposta, selezionare la colonna D. La formula si presenta così: = SI2 *C

Fai attenzione! Per facilitare il processo di scrittura di una formula, usa un piccolo trucco. Metti il ​​segno “=”, clicca sulla quantità di merce venduta, imposta il segno “*” e clicca sul prezzo del prodotto. La formula dopo il segno di uguale verrà scritta automaticamente.

2. Per la risposta finale, premere "Invio". Successivamente, è necessario calcolare l'importo totale del profitto ricevuto da altri tipi di prodotti. Bene, se il numero di righe non è elevato, tutte le manipolazioni possono essere eseguite manualmente. Per compilare un gran numero di righe contemporaneamente in Excel, esiste un'utile funzione che consente di trasferire la formula in altre celle.
3. Sposta il cursore sull'angolo inferiore destro del rettangolo con la formula o il risultato finale. L'aspetto di una croce nera serve come segnale che il cursore può essere trascinato verso il basso. Pertanto, viene effettuato un calcolo automatico del profitto ricevuto per ciascun prodotto separatamente.
4. Rilasciando il pulsante premuto del mouse, otteniamo i risultati corretti in tutte le righe.

Facendo clic sulla cella D3, puoi vedere che le coordinate della cella sono state modificate automaticamente e ora appaiono così: =SI3 *C3. Ne consegue che i collegamenti erano relativi.

Possibili errori quando si lavora con link relativi

Indubbiamente, questa funzione di Excel semplifica notevolmente i calcoli, ma in alcuni casi possono sorgere delle difficoltà. Consideriamo un semplice esempio di calcolo del coefficiente di profitto di ogni articolo:

1. Crea una tabella e compila: A – nome del prodotto; B – quantità venduta; C – costo; D è l'importo ricevuto. Supponiamo che ci siano solo 11 articoli nell'assortimento. Pertanto, tenendo conto della descrizione delle colonne, vengono riempite 12 righe e l'importo totale dell'utile è D
2. Fare clic sulla cella E2 ed entrare =D2 /D13.
3. Dopo aver premuto il pulsante “Invio”, compare il coefficiente della relativa quota di vendita del primo articolo.
4. Allunga la colonna verso il basso e attendi il risultato. Tuttavia, il sistema restituisce l'errore "#DIV/0!"

Il motivo dell'errore è l'uso di un riferimento relativo per i calcoli. Come risultato della copia della formula, le coordinate cambiano. Cioè, per l'E3, la formula sarà simile a questa =D3 /D13. Poiché la cella D13 non è riempita e teoricamente ha un valore zero, il programma darà un errore con l'informazione che la divisione per zero è impossibile.

Importante! Per correggere l'errore, è necessario scrivere la formula in modo tale che le coordinate D13 siano fisse. L'indirizzamento relativo non ha tale funzionalità. Per fare ciò, esiste un altro tipo di collegamenti: assoluti. 

Come si crea un collegamento assoluto in Excel

Grazie all'uso del simbolo $, è stato possibile fissare le coordinate della cella. Come funziona, considereremo ulteriormente. Poiché il programma utilizza l'indirizzamento relativo per impostazione predefinita, di conseguenza, per renderlo assoluto, sarà necessario eseguire una serie di azioni. Analizziamo la soluzione dell'errore "Come trovare il coefficiente dalla vendita di più articoli", eseguendo il calcolo utilizzando l'indirizzamento assoluto:

1. Fare clic su E2 e inserire le coordinate del collegamento =D2 /D13. Poiché il collegamento è relativo, è necessario impostare un simbolo per correggere i dati.
2. Fissare le coordinate della cella D Per eseguire questa azione, far precedere la lettera che indica la colonna e il numero della riga apponendo un segno “$”.

Parere di esperti! Per facilitare il compito di inserimento è sufficiente attivare la cella per la modifica della formula e premere più volte il tasto F4. Fino a quando non ottieni valori soddisfacenti. La formula corretta è la seguente: =D2/$D$13.

3. Premere il pulsante "Invio". Come risultato delle azioni eseguite, dovrebbe apparire il risultato corretto.
4. Trascina l'indicatore sulla riga inferiore per copiare la formula.

A causa dell'uso dell'indirizzamento assoluto nei calcoli, i risultati finali nelle righe rimanenti saranno corretti.

Come inserire un collegamento misto in Excel

Per i calcoli delle formule, vengono utilizzati non solo i riferimenti relativi e assoluti, ma anche quelli misti. La loro caratteristica distintiva è che fissano una delle coordinate.

• Ad esempio, per modificare la posizione di una riga, è necessario scrivere il segno $ davanti alla designazione della lettera.
• Al contrario, se il simbolo del dollaro viene scritto dopo la designazione della lettera, gli indicatori nella riga rimarranno invariati.

Da ciò ne consegue che per risolvere il precedente problema di determinazione del coefficiente di vendita delle merci utilizzando l'indirizzamento misto, è necessario fissare il numero di riga. Cioè, il segno $ viene posizionato dopo la lettera della colonna, perché le sue coordinate non cambiano nemmeno in un riferimento relativo. Facciamo un esempio:

1. Per calcoli accurati, immettere =D1/$D$3 e premere “Invio”. Il programma fornisce la risposta esatta.
2. Per spostare la formula nelle celle successive lungo la colonna e ottenere risultati accurati, trascina la maniglia nella cella inferiore.
3. Di conseguenza, il programma fornirà i calcoli corretti.

Attenzione! Se imposti il ​​segno $ davanti alla lettera, Excel visualizzerà un errore "#DIV/0!", il che significa che questa operazione non può essere eseguita.

Indirizzamento “SuperAbsolute”.

Alla fine, diamo un'occhiata a un altro esempio di collegamento assoluto: l'indirizzamento "SuperAbsolute". Quali sono le sue caratteristiche e differenze. Prendi il numero approssimativo 30 e inseriscilo nella cella B2. È questo numero che sarà il principale, è necessario eseguire una serie di azioni con esso, ad esempio, per elevarlo a una potenza.

1. Per la corretta esecuzione di tutte le azioni, inserire la seguente formula nella colonna C: =$B$2^$D2. Nella colonna D inseriamo il valore dei gradi.

2. Dopo aver premuto il pulsante "Invio" e aver attivato la formula, allunghiamo il marker lungo la colonna.
3. Otteniamo i risultati corretti.

La conclusione è che tutte le azioni eseguite si riferivano ad una cella fissa B2, quindi:

• La copia di una formula dalla cella C3 alla cella E3, F3 o H3 non cambierà il risultato. Rimarrà invariato – 900.
• Se devi inserire una nuova colonna, le coordinate della cella con la formula cambieranno, ma rimarrà invariato anche il risultato.

Questa è la particolarità del link “SuperAbsolute”: se è necessario spostare il risultato non cambierà. Tuttavia, ci sono situazioni in cui i dati vengono inseriti da fonti di terze parti. Pertanto, le colonne vengono spostate di lato e i dati vengono impostati alla vecchia maniera nella colonna B2. Cosa succede in questo caso? Quando miscelato, la formula cambia in base all'azione eseguita, ovvero non punterà più a B2, ma a C2. Ma siccome l'inserimento è stato fatto in B2, il risultato finale sarà sbagliato.

Riferimento! Per poter inserire macro da fonti di terze parti, devi abilitare le impostazioni dello sviluppatore (sono disabilitate per impostazione predefinita). Per fare ciò, vai su Opzioni, apri le impostazioni della barra multifunzione e seleziona la casella nella colonna di destra di fronte a "Sviluppatore". Successivamente, si aprirà l'accesso a molte funzioni che in precedenza erano nascoste agli occhi dell'utente medio.

Ciò pone la domanda: è possibile modificare la formula dalla cella C2 in modo che il numero originale venga raccolto dalla cella B, nonostante l'inserimento di nuove colonne di dati? Per garantire che le modifiche nella tabella non influiscano sulla determinazione del totale durante l'installazione di dati da fonti di terze parti, è necessario eseguire i seguenti passaggi:

1. Invece delle coordinate della cella B2, inserisci i seguenti indicatori: =INDIRETTO(“B2”). Di conseguenza, dopo aver spostato la composizione di formulazione apparirà così: =INDIRETTO(“B2”)^$E2.
2. Grazie a questa funzione, il collegamento punta sempre al quadrato con coordinate B2, indipendentemente dal fatto che nella tabella vengano aggiunte o rimosse colonne.

Va inteso che una cella che non contiene alcun dato riporta sempre il valore “0”.

Conclusione

Grazie all'uso di tre varietà dei collegamenti descritti, vengono visualizzate molte opportunità che semplificano il lavoro con i calcoli in Excel. Pertanto, prima di iniziare a lavorare con le formule, leggi prima i collegamenti e le regole per la loro installazione.