Î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 ceea ce înțelegem prin referințe de celule, care stau la baza unei puteri și a unei versatilități a formulelor și a funcțiilor. O înțelegere concretă a modului în care funcționează referințele de celule vă va permite să obțineți maximum de foi de calcul Excel!
ȘCOALĂ NAVIGAȚIENotă: vom presupune că deja știți că o celulă este una dintre pătratele din foaia de calcul, aranjate în coloane și rânduri care sunt menționate prin litere și cifre care rulează orizontal și vertical.
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.
Există trei tipuri de referințe pentru celule.
Absolut - Aceasta înseamnă că referința celulelor 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 modifică pe măsură ce o copiați sau o mutați; adică referința celulei este relativă la locația sa.
Amestecat - 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, iar referința rândului rămâne aceeași. Vom explica mai departe acest lucru.
Să ne referim la acel exemplu anterior - să presupunem că în celula A1 avem o formulă care spune pur și simplu = A2. Aceasta înseamnă ieșirea Excel în celula A1, indiferent care 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 cu două coloane spre dreapta și patru în jos. Aceasta înseamnă că am schimbat celula pe care o 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ă "Sunt C6".
Să încercăm un alt exemplu. Amintiți-vă de la Lecția 2 unde trebuia să împărțim un nume complet în nume și prenume? 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 din partea superioară a unei foi de calcul, în caseta de lângă unde se afișează "fx." Această cutie este mai lungă decât o celulă este largă, deci este mai ușor de editat.
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 ale lui 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) - GĂSI (",", A2) - 1).
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, calculatorul nu știe ce vrei să spui.
Noua formulă din B2, de exemplu, este = DREAPTA (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1)
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 "gama" 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 traversează cinci coloane și zece rânduri, atunci indicați intervalul scriind celula stânga sus și cea din dreapta-jos, de exemplu, A1: E10. Aceasta este o zonă pătrată care traversează rânduri și coloane și nu doar o parte dintr-o coloană sau parte dintr-un 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 obține o balanță 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 vedeți, 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:
O referință absolută nu se modifică atunci când mutați sau copiați o celulă. Folosim 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 arată 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 referința rândului și coloanei.
Rescrie 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 se calculează corect acum, deoarece rata dobânzii este ancorată la celula B1.
Acesta este un bun exemplu când 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, faceți clic cu butonul din dreapta pe celulă și apoi selectați "definiți numele".
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 scrisul = $ B $ 1 * 8.
Referințele mixte sunt 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 alimentar ș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 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 acea formulă 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 kilograme 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.
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ă repetarea deoarece calculează totul doar o singură dată.
Dacă încercați să faceți acest lucru tastând SUM (B1: B5) în celula B5:
Apare 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ă.
Un "registru de lucru" este o colecție de "foi de lucru". Pur și 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 nostru de lucru are numeroase 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 schimba numele foii de lucru la ceva util, cum ar fi "împrumutul" sau "bugetul", făcând clic dreapta pe fila foaie de lucru afișată în partea de jos a ecranului programului Excel, selectând redenumirea și introducând un nume nou.
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:
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:
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 aplicației Excel.
