Riferimento relativo e assoluto della cella e formattazione

In questa lezione discutiamo i riferimenti alle celle, come copiare o spostare una formula e formattare le celle. Per iniziare, chiariamo cosa intendiamo per riferimenti di celle, che sono alla base di gran parte della potenza e della versatilità delle formule e delle funzioni. Una conoscenza concreta di come funzionano i riferimenti alle celle ti consentirà di ottenere il massimo dai tuoi fogli di calcolo Excel!

NAVIGAZIONE SCOLASTICA

1. Perché hai bisogno di formule e funzioni?
2. Definizione e creazione di una formula
3. Riferimento relativo e assoluto della cella e formattazione
4. Funzioni utili da conoscere
5. Ricerche, grafici, statistiche e tabelle pivot

Nota: supponiamo che tu sappia già che una cella è uno dei quadrati del foglio di calcolo, disposti in colonne e righe a cui fanno riferimento lettere e numeri che corrono orizzontalmente e verticalmente.

Cos'è un riferimento di cella?

Un "riferimento di cella" indica la cella a cui fa riferimento un'altra cella. Ad esempio, se nella cella A1 hai = A2. Quindi A1 si riferisce a A2.

Rivediamo ciò che abbiamo detto nella Lezione 2 su righe e colonne in modo da poter esplorare ulteriormente i riferimenti alle celle.

Le celle nel foglio di calcolo sono indicate da righe e colonne. Le colonne sono verticali ed etichettate con lettere. Le file sono orizzontali ed etichettate con numeri.

La prima cella del foglio di calcolo è A1, che significa colonna A, riga 1, B3 si riferisce alla cella situata sulla seconda colonna, terza riga e così via.

A scopo di apprendimento sui riferimenti alle celle, a volte li scriviamo come riga, colonna, questa non è una notazione valida nel foglio di calcolo e serve semplicemente a rendere le cose più chiare.

Tipi di riferimenti di cella

Esistono tre tipi di riferimenti di cella.

Assoluto - Ciò significa che il riferimento di cella rimane lo stesso se si copia o si sposta la cella in qualsiasi altra cella. Questo viene fatto ancorando la riga e la colonna, quindi non cambia quando copiata o spostata.

Relativo - Il riferimento relativo indica che l'indirizzo della cella cambia man mano che lo si copia o lo si sposta; cioè il riferimento di cella è relativo alla sua posizione.

Misto: ciò significa che è possibile scegliere di ancorare la riga o la colonna quando si copia o si sposta la cella, in modo che una modifica e l'altra no. Ad esempio, è possibile ancorare il riferimento di riga, quindi spostare una cella di due righe e attraverso quattro colonne e il riferimento di riga rimane lo stesso. Lo spiegheremo più avanti.

Riferimenti relativi

Facciamo riferimento a quell'esempio precedente - supponiamo nella cella A1 che abbiamo una formula che dice semplicemente = A2. Ciò significa che l'output di Excel nella cella A1 viene immesso nella cella A2. Nella cella A2 abbiamo digitato "A2" in modo che Excel visualizzi il valore "A2" nella cella A1.

Ora, supponiamo di dover fare spazio nel nostro foglio di calcolo per avere più dati. Dobbiamo aggiungere le colonne sopra e le righe a sinistra, quindi dobbiamo spostare la cella verso il basso e verso destra per fare spazio.

Mentre sposti la cella a destra, il numero della colonna aumenta. Mentre lo sposti verso il basso, il numero di riga aumenta. Anche la cella a cui punta, il riferimento di cella, cambia. Questo è illustrato di seguito:

Continuando con il nostro esempio, e guardando il grafico sottostante, se copi il contenuto della cella A1 due a destra e quattro giù lo hai spostato nella cella C5.

Abbiamo copiato la cella di due colonne a destra e quattro in basso. Ciò significa che abbiamo cambiato la cella che fa riferimento a due e quattro giù. A1 = A2 ora è C5 = C6. Invece di fare riferimento a A2, ora la cella C5 si riferisce alla cella C6.

Il valore visualizzato è 0 perché la cella C6 è vuota. Nella cella C6 digitiamo "I am C6" e ora C5 mostra "I am C6."

Esempio: formula del testo

Proviamo un altro esempio. Ricorda dalla lezione 2 dove abbiamo dovuto dividere un nome completo in nome e cognome? Cosa succede quando copiamo questa formula?

Scrivi la formula = RIGHT (A3, LEN (A3) - TROVA (",", A3) - 1) o copia il testo nella cella C3. Non copiare la cella attuale, solo il testo, copiare il testo, altrimenti aggiornerà il riferimento.

Puoi modificare il contenuto di una cella nella parte superiore di un foglio di calcolo nella casella accanto a "fx". Quella casella è più lunga di una cella è ampia, quindi è più facile da modificare.

Ora abbiamo:

Niente di complicato, abbiamo appena scritto una nuova formula nella cella C3. Ora copia C3 nelle celle C2 e C4. Osserva i risultati seguenti:

Ora abbiamo i nomi di Alexander Hamilton e Thomas Jefferson.

Usa il cursore per evidenziare le celle C2, C3 e C4. Puntare il cursore sulla cella B2 e incollare il contenuto. Guarda cosa è successo - riceviamo un errore: "#RIF". Perché è così?

Quando abbiamo copiato le celle dalla colonna C alla colonna B abbiamo aggiornato la colonna di riferimento a sinistra = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Ha cambiato ogni riferimento in A2 alla colonna a sinistra di A, ma non c'è nessuna colonna a sinistra della colonna A. Quindi il computer non sa cosa intendi.

Ad esempio, la nuova formula in B2 è = RIGHT (#REF!, LEN (#REF!) - TROVA (",", # REF!) - 1) e il risultato è #REF:

Copia di una formula in un intervallo di celle

Copiare le celle è molto utile perché puoi scrivere una formula e copiarla su una vasta area e il riferimento viene aggiornato. Questo evita di dover modificare ogni cella per assicurarsi che punti al posto giusto.

Per "intervallo" intendiamo più di una cella. Ad esempio, (C1: C10) indica tutte le celle dalla cella C1 alla cella C10. Quindi è una colonna di celle. Un altro esempio (A1: AZ1) è la riga superiore dalla colonna A alla colonna AZ.

Se un intervallo incrocia cinque colonne e dieci righe, indica l'intervallo scrivendo la cella in alto a sinistra e quella in basso a destra, ad esempio A1: E10. Questa è un'area quadrata che attraversa righe e colonne e non solo parte di una colonna o parte di una riga.

Ecco un esempio che illustra come copiare una cella in più posizioni. Supponiamo di voler mostrare le nostre spese previste per il mese in un foglio di calcolo in modo da poter fare un bilancio. Facciamo un foglio di calcolo come questo:

Ora copia la formula nella cella C3 (= B3 + C2) nel resto della colonna per ottenere un saldo di funzionamento per il nostro budget. Excel aggiorna il riferimento di cella mentre lo copi. Il risultato è mostrato di seguito:

Come puoi vedere, ogni nuova cella si aggiorna parente nella nuova posizione, quindi la cella C4 aggiorna la sua formula a = B4 + C3:

Aggiornamenti cella C5 = B5 + C4 e così via:

Riferimenti assoluti

Un riferimento assoluto non cambia quando sposti o copi una cella. Usiamo il segno $ per fare un riferimento assoluto - per ricordarlo, pensa a un segno di dollaro come un'ancora.

Ad esempio, inserisci la formula = $ A $ 1 in qualsiasi cella. Il $ davanti alla colonna A significa che non cambia la colonna, $ davanti alla riga 1 significa che non cambi la colonna quando copi o sposti la cella in qualsiasi altra cella.

Come puoi vedere nell'esempio seguente, nella cella B1 abbiamo un riferimento relativo = A1. Quando copiamo B1 nelle quattro celle sottostanti, il riferimento relativo = A1 cambia nella cella a sinistra, quindi B2 diventa A2, B3 diventa A3, ecc. Quelle celle ovviamente non hanno valore immesso, quindi l'output è zero.

Tuttavia, se usiamo = $ A1 $ 1, come in C1 e lo copiamo nelle quattro celle sottostanti, il riferimento è assoluto, quindi non cambia mai e l'output è sempre uguale al valore nella cella A1.

Supponiamo che tu tenga traccia dei tuoi interessi, come nell'esempio qui sotto. La formula in C4 = B4 * B1 è il "tasso di interesse" * "saldo" = "interesse per anno".

Ora, hai cambiato il tuo budget e hai risparmiato altri $ 2.000 per acquistare un fondo comune. Supponiamo che sia un fondo a tasso fisso e paga lo stesso tasso di interesse. Inserisci il nuovo account e il saldo nel foglio di calcolo e quindi copia la formula = B4 * B1 dalla cella C4 alla cella C5.

Il nuovo budget si presenta così:

Il nuovo fondo comune guadagna $ 0 in interessi all'anno, il che non può essere corretto dato che il tasso di interesse è chiaramente del 5%.

Excel evidenzia le celle a cui fa riferimento una formula. Puoi vedere sopra che il riferimento al tasso di interesse (B1) viene spostato nella cella vuota B2. Dovremmo aver reso il riferimento a B1 assoluto scrivendo $ B $ 1 usando il simbolo di dollari per ancorare il riferimento di riga e colonna.

Riscrivi il primo calcolo in C4 per leggere = B4 * $ B $ 1 come mostrato di seguito:

Quindi copia quella formula da C4 a C5. Il foglio di calcolo ora si presenta così:

Poiché abbiamo copiato la formula una cella in basso, cioè aumentato la riga di uno, la nuova formula è = B5 * $ B $ 1. Il tasso di interesse del fondo comune è calcolato correttamente ora, perché il tasso di interesse è ancorato alla cella B1.

Questo è un buon esempio di quando puoi usare un "nome" per riferirsi a una cella. Un nome è un riferimento assoluto. Ad esempio, per assegnare il nome "tasso di interesse" alla cella B1, fare clic con il pulsante destro del mouse sulla cella e quindi selezionare "Definisci nome".

I nomi possono fare riferimento a una cella oa un intervallo e puoi utilizzare un nome in una formula, ad esempio = tasso_interesse * 8 è la stessa cosa di scrivere = $ B $ 1 * 8.

Riferimenti misti

I riferimenti misti sono quando o la riga o la colonna è ancorata.

Ad esempio, supponiamo che tu sia un agricoltore che sta facendo un budget. Hai anche un negozio di mangimi e vendi semi. Pianterai mais, soia e erba medica. Il foglio di calcolo qui sotto mostra il costo per acro. Il "costo per acro" = "prezzo per libbra" * "libbre di semi per acro" - è quello che ti costerà piantare un acro.

Immettere il costo per acro come = $ B2 * C2 nella cella D2. Stai dicendo che vuoi ancorare la colonna del prezzo per sterlina. Quindi copia la formula nelle altre righe nella stessa colonna:

Ora vuoi sapere il valore del tuo inventario di semi. È necessario il prezzo per sterlina e il numero di sterline in inventario per conoscere il valore dell'inventario.

Aggiungiamo due colonne: "libbra di seme nell'inventario" e quindi "valore di inventario". Ora copia la cella da D2 a F4 e nota che il riferimento di riga nella prima parte della formula originale ($ B2) viene aggiornato alla riga 4 ma la colonna rimane fissa perché $ lo aggiunge a "B."

Questo è un riferimento misto perché la colonna è assoluta e la riga è relativa.

Riferimenti circolari

Un riferimento circolare è quando una formula si riferisce a se stessa.

Ad esempio, non puoi scrivere c3 = c3 + 1. Questo tipo di calcolo è chiamato "iterazione", il che significa che si ripete. Excel non supporta l'iterazione perché calcola tutto solo una volta.

Se provi a farlo, digita SUM (B1: B5) nella cella B5:

Viene visualizzata una schermata di avviso:

Excel ti dice solo di avere un riferimento circolare nella parte inferiore dello schermo in modo che tu possa non notarlo. Se hai un riferimento circolare e chiudi un foglio di calcolo e lo apri di nuovo, Excel ti dirà in una finestra pop-up che hai un riferimento circolare.

Se hai un riferimento circolare, ogni volta che apri il foglio di calcolo, Excel ti dirà con quella finestra pop-up che hai un riferimento circolare.

Riferimenti ad altri fogli di lavoro

Una "cartella di lavoro" è una raccolta di "fogli di lavoro". In poche parole, ciò significa che è possibile avere più fogli di calcolo (fogli di lavoro) nello stesso file Excel (cartella di lavoro). Come puoi vedere nell'esempio seguente, la nostra cartella di lavoro di esempio ha molti fogli di lavoro (in rosso).

I fogli di lavoro per impostazione predefinita sono denominati Foglio1, Foglio2 e così via. Ne crei uno nuovo facendo clic su "+" nella parte inferiore della schermata di Excel.

È possibile modificare il nome del foglio di lavoro in qualcosa di utile come "prestito" o "budget" facendo clic con il pulsante destro del mouse sulla scheda del foglio di lavoro visualizzata nella parte inferiore della schermata del programma Excel, selezionando rinomina e digitando un nuovo nome.

Oppure puoi semplicemente fare doppio clic sulla scheda e rinominarla.

La sintassi per un riferimento al foglio di lavoro è = foglio di lavoro! Cella. È possibile utilizzare questo tipo di riferimento quando lo stesso valore viene utilizzato in due fogli di lavoro, esempi di ciò potrebbero essere:

• Data odierna
• Tasso di conversione della valuta da Dollari ad Euro
• Tutto ciò che è rilevante per tutti i fogli di lavoro nella cartella di lavoro

Di seguito è riportato un esempio di foglio di lavoro "interesse" che fa riferimento al foglio "prestito", cella B1.

Se guardiamo il foglio di lavoro "prestito", possiamo vedere il riferimento all'importo del prestito:

In arrivo…

Ci auguriamo che ora abbia una solida conoscenza dei riferimenti alle celle, tra cui relativo, assoluto e misto. C'è sicuramente molto.

Questo è tutto per la lezione di oggi, nella Lezione 4, discuteremo alcune utili funzioni che potresti desiderare di conoscere per l'uso quotidiano di Excel.