Referință celulară relativă și absolută și formatare

În această lecție discutăm referințele de celule, cum să copiem sau să mutați o formulă și să formați celule. Pentru a începe, să clarificăm ce înțelegem prin referințele de celule, care stau la baza unei puteri și a unei versatilități a formulelor și funcțiilor. O înțelegere concretă a modului în care funcționează referințele de celule vă va permite să profitați la maximum de foile de calcul Excel!

ȘCOLAREA NAVIGAȚIEI

1. De ce aveți nevoie de formule și funcții?
2. Definirea și crearea unei formule
3. Referință celulară relativă și absolută și formatare
4. Funcții utile pe care ar trebui să le cunoașteți
5. Căutări, diagrame, statistici și tabele pivot

Notă: vom presupune că deja știți că o celulă este una din pătratele din foaia de calcul, aranjate în coloane și rânduri care se referă la litere și numere care rulează orizontal și vertical.

Ce este o referință de celule?

O "referință de celule" înseamnă celula la care se referă o altă celulă. De exemplu, dacă în celula A1 aveți = A2. Atunci A1 se referă la A2.

Să revizuim ceea ce am spus în Lecția 2 despre rânduri și coloane pentru a putea explora în continuare referințele celulare.

Celulele din foaia de calcul sunt menționate prin rânduri și coloane. Coloanele sunt verticale și etichetate cu litere. Rândurile sunt orizontale și etichetate cu numere.

Prima celulă din foaia de calcul este A1, ceea ce înseamnă că coloana A, rândul 1, B3 se referă la celula din a doua coloană, al treilea rând și așa mai departe.

În scopuri de învățare despre referințele de celule, le vom scrie uneori ca rând, coloană, aceasta nu este notație validă în foaia de calcul și este menită să facă lucrurile mai clare.

Tipuri de referințe pentru celule

Există trei tipuri de referințe pentru celule.

Absolut - Aceasta înseamnă că referința celulei rămâne aceeași dacă copiați sau mutați celula în orice altă celulă. Acest lucru se face prin ancorarea rândului și coloanei, astfel încât să nu se schimbe atunci când sunt copiate sau mutate.

Relativ - Referința relativă înseamnă că adresa celulei se schimbă pe măsură ce o copiați sau o mutați; adică referința celulei este relativă la locația sa.

Mixt - Aceasta înseamnă că puteți alege să ancorați rândul sau coloana atunci când copiați sau mutați celula, astfel încât una să se schimbe și cealaltă să nu o facă. De exemplu, puteți ancora referința rândului, apoi mutați o celulă în două rânduri și peste patru coloane și referința rândului rămâne aceeași. Vom explica mai departe acest lucru.

Referințe relative

Să ne referim la acel exemplu anterior - să presupunem că în celula A1 avem o formulă care spune pur și simplu = A2. Asta înseamnă că ieșirea Excel în celula A1 este introdusă în celula A2. În celula A2 am tastat "A2" astfel încât Excel afișează valoarea "A2" în celula A1.

Acum, să presupunem că trebuie să facem loc în foaia de calcul pentru mai multe date. Trebuie să adăugăm coloane deasupra și rânduri în stânga, așa că trebuie să mutăm celula în jos și în dreapta pentru a face loc.

Pe măsură ce mutați celula spre dreapta, numărul coloanei crește. Pe măsură ce o mutați, numărul rândului crește. Celula la care se referă, referința celulei, se schimbă și ea. Acest lucru este ilustrat mai jos:

Continuând cu exemplul nostru și uitându-vă la graficul de mai jos, dacă copiați conținutul celulei A1 două în dreapta și patru în jos, l-ați mutat în celula C5.

Am copiat celula două coloane spre dreapta și patru în jos. Aceasta înseamnă că am schimbat celula la care se referă două și patru în jos. A1 = A2 este acum C5 = C6. În loc să se refere la A2, acum celula C5 se referă la celula C6.

Valoarea afișată este 0 deoarece celula C6 este goală. În celula C6 introducem textul "Eu sunt C6" iar acum C5 afișează "Eu sunt C6".

Exemplu: Formula de text

Să încercăm un alt exemplu. Amintiți-vă din Lecția 2, unde trebuia să împărțim un nume complet în numele și prenumele? Ce se întâmplă când copiem această formulă?

Scrieți formula = DREAPTA (A3, LEN (A3) - FIND (",", A3) - 1) sau copiați textul în celula C3. Nu copiați celula reală, doar textul, copiați textul, altfel va actualiza referința.

Puteți edita conținutul unei celule aflat în partea de sus a unei foi de calcul, în caseta din dreptul căreia se afișează "fx." Această cutie este mai lungă decât o celulă este largă, deci este mai ușor să editați.

Acum avem:

Nimic complicat, tocmai am scris o nouă formulă în celula C3. Acum copiați C3 în celulele C2 și C4. Observați rezultatele de mai jos:

Acum avem numele lui Alexander Hamilton și Thomas Jefferson.

Utilizați cursorul pentru a evidenția celulele C2, C3 și C4. Îndreptați cursorul spre celula B2 și lipiți conținutul. Uită-te la ce sa întâmplat - avem o eroare: "#REF". De ce este asta??

Când am copiat celulele din coloana C în coloana B, am actualizat coloana de referință la stânga = DREAPTA (A2, LEN (A2) - GHID ("," A2).

A schimbat fiecare referință la A2 la coloana din stânga lui A, dar nu există o coloană în partea stângă a coloanei A. Deci, computerul nu știe ce vrei să spui.

Noua formula din B2, de exemplu, este = DREAPTA (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1)

Copierea unei formule într-o gamă de celule

Copierea celulelor este foarte utilă deoarece puteți scrie o formulă și copiați-o într-o zonă extinsă și referința este actualizată. Acest lucru evită necesitatea de a edita fiecare celulă pentru a vă asigura că indică locul potrivit.

Prin "interval" se înțelege mai mult de o celulă. De exemplu, (C1: C10) înseamnă toate celulele de la celula C1 la celula C10. Deci este o coloană de celule. Un alt exemplu (A1: AZ1) este rândul de sus de la coloana A la coloana AZ.

Dacă un interval trece cinci coloane și zece rânduri, atunci indicați intervalul scriind celula din stânga sus și cel din dreapta jos, de exemplu, A1: E10. Aceasta este o zonă pătrată care traversează rânduri și coloane și nu doar o parte a unei coloane sau a unei părți a unui rând.

Iată un exemplu care ilustrează cum să copiați o celulă în mai multe locații. Să presupunem că vrem să arătăm cheltuielile noastre proiectate pentru luna într-o foaie de calcul, astfel încât să putem face un buget. Facem o foaie de calcul ca aceasta:

Acum, copiați formula în celula C3 (= B3 + C2) la restul coloanei pentru a da un echilibru de funcționare pentru bugetul nostru. Excel actualizează referința celulară pe măsură ce o copiați. Rezultatul este prezentat mai jos:

După cum puteți vedea, fiecare nouă celulă se actualizează rudă la noua locație, astfel încât celula C4 își actualizează formula la = B4 + C3:

Celulele C5 se actualizează la = B5 + C4 și așa mai departe:

Referințe absolute

O referință absolută nu se modifică atunci când mutați sau copiați o celulă. Utilizăm semnul $ pentru a face o referință absolută - să ne amintim că, gândiți-vă la un semn de dolar ca o ancoră.

De exemplu, introduceți formula = $ A $ 1 în orice celulă. $ În fața coloanei A înseamnă că nu modificați coloana, $ în fața rândului 1 înseamnă că nu modificați coloana atunci când copiați sau mutați celula în orice altă celulă.

După cum puteți vedea în exemplul de mai jos, în celula B1 avem o referință relativă = A1. Când copiem B1 la cele patru celule de sub ea, referința relativă = A1 se schimbă în celula din stânga, astfel încât B2 devine A2, B3 deveniți A3, etc. Aceste celule, în mod evident, nu au nici o valoare introdusă, deci producția este zero.

Cu toate acestea, dacă folosim = $ A1 $ 1, cum ar fi în C1 și îl copiem în cele patru celule de dedesubt, referința este absolută, deci nu se schimbă niciodată și ieșirea este întotdeauna egală cu valoarea din celula A1.

Să presupunem că țineți evidența interesului dvs., cum ar fi în exemplul de mai jos. Formula din C4 = B4 * B1 este "rata dobânzii" * "soldul" = "dobânda pe an".

Acum v-ați schimbat bugetul și ați economisit încă 2.000 de dolari pentru a cumpăra un fond mutual. Să presupunem că este un fond cu rată fixă ​​și plătește aceeași rată a dobânzii. Introduceți noul cont și soldul în foaia de calcul și apoi copiați formula = B4 * B1 de la celula C4 la celula C5.

Noul buget pare astfel:

Noul fond mutual câștigă 0 $ în dobândă pe an, ceea ce nu poate fi corect, deoarece rata dobânzii este în mod clar de 5%.

Excel evidențiază celulele la care se referă o formulă. Puteți vedea mai sus că trimiterea la rata dobânzii (B1) este mutată în celula goală B2. Ar fi trebuit să facem trimiterea la B1 absolut scriind $ B $ 1 folosind semnul de dolari pentru a ancora rândul și referința coloanei.

Rescrieți primul calcul în C4 pentru a citi = B4 * $ B $ 1 după cum se arată mai jos:

Apoi copiați formula de la C4 la C5. Foaia de calcul acum arată astfel:

Deoarece am copiat formularul cu o celulă în jos, adică a mărit rândul cu unul, noua formulă este = B5 * $ B $ 1. Rata dobânzii la fondurile mutuale este corect calculată acum, deoarece rata dobânzii este ancorată la celula B1.

Acesta este un bun exemplu în care puteți folosi un "nume" pentru a vă referi la o celulă. Un nume este o referință absolută. De exemplu, pentru a atribui numele "rata dobânzii" la celula B1, dați clic dreapta pe celulă și apoi selectați "defini nume."

Numele se pot referi la o celulă sau la o gamă și puteți utiliza un nume într-o formulă, de exemplu = interest_rate * 8 este același lucru ca și scrierea = $ B $ 1 * 8.

Referințe mixte

Referințele mixte sunt atunci când fie randul sau coloana este ancorată.

De exemplu, să presupunem că sunteți un agricultor care face un buget. De asemenea, dețineți un magazin de hrană pentru animale și vindeți semințe. Voi plantați porumb, soia și lucernă. Foaia de calcul de mai jos arată costul pe acru. "Costul pe acru" = "prețul pe kilogram" * "kilograme de semințe pe acru" - asta este ceea ce vă va costa să plantați un acru.

Introduceți costul pe acru ca fiind = B2 * C2 în celula D2. Vrei să spui că vrei să ancorați coloana prețului pe kilogram. Apoi copiați formula la celelalte rânduri din aceeași coloană:

Acum doriți să știți valoarea inventarului dvs. de semințe. Aveți nevoie de prețul pe kilogram și de numărul de lire sterline din inventar pentru a cunoaște valoarea inventarului.

Adăugăm două coloane: "cantitatea de semințe din inventar" și apoi "valoarea inventarului". Acum, copiați celula D2 în F4 și rețineți că referința rândului din prima parte a formulei originale ($ B2) este actualizată la rând 4 dar coloana rămâne fixă ​​deoarece $ ancorează la "B."

Aceasta este o referință mixtă deoarece coloana este absolută și rândul este relativ.

Referințe circulară

O referință circulară este atunci când o formulă se referă la ea însăși.

De exemplu, nu puteți scrie c3 = c3 + 1. Acest tip de calcul se numește "iterație", ceea ce înseamnă că se repetă. Excel nu acceptă iterație, deoarece calculează totul doar o singură dată.

Dacă încercați să faceți acest lucru tastând SUM (B1: B5) în celula B5:

Se afișează un ecran de avertizare:

Excel vă spune doar că aveți o referință circulară în partea de jos a ecranului, astfel încât să nu observați acest lucru. Dacă aveți o referință circulară și închideți o foaie de calcul și deschideți-o din nou, Excel vă va spune într-o fereastră pop-up că aveți o referință circulară.

Dacă aveți o referință circulară, de fiecare dată când deschideți foaia de calcul, Excel vă va spune cu fereastra pop-up că aveți o referință circulară.

Referințe la alte foi de lucru

Un "registru de lucru" este o colecție de "foi de lucru". În mod simplu, aceasta înseamnă că aveți mai multe foi de calcul (foi de lucru) în același fișier Excel (registru de lucru). După cum puteți vedea în exemplul de mai jos, fișierul de lucru al exemplului are mai multe foi de lucru (în roșu).

Foile de lucru în mod implicit sunt numite Sheet1, Sheet2, și așa mai departe. Creați unul nou făcând clic pe "+" din partea de jos a ecranului Excel.

Puteți modifica numele foii de lucru la ceva util, cum ar fi "împrumut" sau "buget", făcând clic dreapta pe fila foaie de lucru afișată în partea de jos a ecranului programului Excel, selectând redenumirea și tastând un nou nume.

Sau puteți să faceți dublu clic pe fila și să o redenumiți.

Sintaxa pentru o referință a foii de lucru este = celula de lucru! Puteți utiliza acest tip de referință atunci când aceeași valoare este utilizată în două foi de lucru, exemple care ar putea fi:

• Data de azi
• Cursul de schimb valutar de la Dolari la Euro
• Orice este relevant pentru toate foile de lucru din registrul de lucru

Mai jos este un exemplu de foaie de lucru "interes" făcând referire la foaia de lucru "împrumut", celula B1.

Dacă ne uităm la foaia de lucru "împrumut", putem vedea referința la suma creditului:

Urmeaza…

Sperăm că acum aveți o înțelegere fermă a referințelor celulare, inclusiv relativă, absolută și mixtă. Există cu siguranță multe.

Asta pentru lecția de astăzi, în Lecția 4, vom discuta câteva funcții utile pe care ați dori să le cunoașteți pentru utilizarea zilnică a programului Excel.