Kako zamrznuti ćeliju u Excel formuli

Korisnici se često suočavaju s potrebom da prikvače ćeliju u formuli. Na primjer, događa se u situacijama kada želite kopirati formulu, ali tako da se poveznica ne pomiče gore-dolje za isti broj ćelija kao što je kopirana s izvorne lokacije.

U tom slučaju možete popraviti referencu ćelije u Excelu. A to se može učiniti na nekoliko načina odjednom. Pogledajmo pobliže kako postići ovaj cilj.

Što je Excel poveznica

List se sastoji od ćelija. Svaki od njih sadrži specifične podatke. Druge ćelije mogu ga koristiti u izračunima. Ali kako oni razumiju odakle dobiti podatke? Pomaže im u stvaranju poveznica.

Svaka poveznica označava ćeliju s jednim slovom i jednim brojem. Slovo predstavlja stupac, a broj predstavlja red. 

Postoje tri vrste veza: apsolutne, relativne i mješovite. Drugi je postavljen prema zadanim postavkama. Apsolutna referenca je ona koja ima fiksnu adresu i stupca i stupca. Prema tome, mješoviti je onaj u kojem je fiksiran ili zasebni stupac ili redak.

1 metoda

Kako biste spremili adrese stupaca i redaka, slijedite ove korake:

1. Kliknite na ćeliju koja sadrži formulu.
2. Kliknite na traku formule za ćeliju koja nam je potrebna.
3. Pritisnite F4.

Kao posljedica toga, referenca ćelije će se promijeniti u apsolutnu. Može se prepoznati po karakterističnom znaku dolara. Na primjer, ako kliknete na ćeliju B2, a zatim kliknete na F4, poveznica će izgledati ovako: $B$2.

Što znači znak dolara ispred dijela adrese po ćeliji?

1. Ako se nalazi ispred slova, označava da referenca stupca ostaje ista, bez obzira gdje je formula premještena.
2. Ako je znak dolara ispred broja, to znači da je niz prikvačen. 

2 metoda

Ova metoda je gotovo ista kao i prethodna, samo trebate dvaput pritisnuti F4. na primjer, ako imamo ćeliju B2, onda će nakon toga postati B$2. Jednostavnim riječima, na ovaj način uspjeli smo popraviti liniju. U tom će se slučaju promijeniti slovo stupca.

Vrlo je prikladno, na primjer, u tablicama u kojima trebate prikazati sadržaj druge ćelije odozgo u donjoj ćeliji. Umjesto ponavljanja takve formule mnogo puta, dovoljno je popraviti red i pustiti da se stupac mijenja.

3 metoda

Ovo je potpuno isto kao i prethodna metoda, samo trebate tri puta pritisnuti tipku F4. Tada će samo referenca na stupac biti apsolutna, a red će ostati fiksan.

4 metoda

Pretpostavimo da imamo apsolutnu referencu na ćeliju, ali ovdje ju je bilo potrebno učiniti relativnom. Da biste to učinili, pritisnite tipku F4 toliko puta da na poveznici nema znakova $. Tada će postati relativno, a kada premjestite ili kopirate formulu, promijenit će se i adresa stupca i adresa retka.

Pričvršćivanje ćelija za veliki raspon

Vidimo da gore navedene metode ne predstavljaju nikakve poteškoće za izvođenje. Ali zadaci su specifični. I, na primjer, što učiniti ako imamo nekoliko desetaka formula odjednom, veze u kojima treba pretvoriti u apsolutne. 

Nažalost, standardne Excel metode neće postići ovaj cilj. Da biste to učinili, morate koristiti poseban dodatak koji se zove VBA-Excel. Sadrži mnoge dodatne značajke koje vam omogućuju puno brže obavljanje uobičajenih zadataka s Excelom.

Uključuje više od stotinu korisnički definiranih funkcija i 25 različitih makronaredbi, a također se redovito ažurira. Omogućuje vam da poboljšate rad s gotovo svim aspektima:

1. Stanice.
2. Makro.
3. Funkcije različitih vrsta.
4. Linkovi i nizovi.

Konkretno, ovaj vam dodatak omogućuje da popravite veze u velikom broju formula odjednom. Da biste to učinili, morate izvršiti sljedeće korake:

1. Odaberite raspon.
2. Otvorite karticu VBA-Excel koja će se pojaviti nakon instalacije. 
3. Otvorite izbornik "Funkcije", gdje se nalazi opcija "Zaključaj formule".
   

   

4. Zatim će se pojaviti dijaloški okvir u kojem morate odrediti traženi parametar. Ovaj dodatak vam omogućuje da prikvačite stupac i stupac odvojeno, zajedno, kao i da uklonite već postojeće prikvačivanje s paketom. Nakon odabira traženog parametra pomoću odgovarajućeg radio gumba, trebate potvrditi svoje radnje klikom na "OK".

Primjer

Uzmimo primjer da bude jasnije. Recimo da imamo informacije koje opisuju trošak robe, njenu ukupnu količinu i prihod od prodaje. I pred nama je zadatak da tablica na temelju količine i cijene automatski odredi koliko smo novca uspjeli zaraditi bez oduzimanja gubitaka.

U našem primjeru, za to morate unijeti formulu =B2*C2. Vrlo je jednostavno, kao što vidite. Vrlo je lako upotrijebiti njezin primjer za opisivanje kako možete popraviti adresu ćelije ili njezinog pojedinačnog stupca ili retka. 

Naravno, u ovom primjeru možete pokušati povući formulu prema dolje pomoću markera za automatsko popunjavanje, ali u ovom slučaju ćelije će se automatski promijeniti. Dakle, u ćeliji D3 bit će druga formula, gdje će brojevi biti zamijenjeni, odnosno, s 3. Nadalje, prema shemi - D4 - formula će imati oblik = B4 * C4, D5 - slično, ali s broj 5 i tako dalje.

Ako je potrebno (u većini slučajeva se ispostavi), onda nema problema. Ali ako trebate popraviti formulu u jednoj ćeliji tako da se ne mijenja prilikom povlačenja, onda će to biti nešto teže. 

Pretpostavimo da trebamo odrediti dolarski prihod. Stavimo to u ćeliju B7. Budimo malo nostalgični i naznačimo cijenu od 35 rubalja po dolaru. Sukladno tome, da bi se odredio prihod u dolarima, potrebno je iznos u rubljama podijeliti s tečajem dolara.

Evo kako to izgleda na našem primjeru.

Ako mi, slično kao u prethodnoj verziji, pokušamo propisati formulu, onda nećemo uspjeti. Slično tome, formula će se promijeniti u odgovarajuću. U našem primjeru to će biti ovako: =E3*B8. Odavde možemo vidjeti. da se prvi dio formule pretvorio u E3, i postavili smo si taj zadatak, ali ne trebamo mijenjati drugi dio formule u B8. Stoga referencu trebamo pretvoriti u apsolutnu. To možete učiniti bez pritiskanja tipke F4, samo stavljanjem znaka dolara.

Nakon što smo referencu na drugu ćeliju pretvorili u apsolutnu, postala je zaštićena od promjena. Sada ga možete sigurno povući pomoću ručke za automatsko popunjavanje. Svi fiksni podaci ostat će isti, bez obzira na položaj formule, a nedodijeljeni podaci mijenjat će se fleksibilno. U svim će ćelijama prihod u rubljama opisan u ovom retku podijeliti s istim tečajem dolara.

Sama formula će izgledati ovako:

=D2/$B$7

Pažnja! Naznačili smo dva znaka dolara. Na taj način pokazujemo programu da treba popraviti i stupac i red.

Reference ćelija u makronaredbama

Makro je potprogram koji vam omogućuje automatiziranje radnji. Za razliku od standardne funkcionalnosti programa Excel, makro vam omogućuje da odmah postavite određenu ćeliju i izvršite određene radnje u samo nekoliko redaka koda. Korisno za skupnu obradu informacija, na primjer, ako ne postoji način za instaliranje dodataka (na primjer, koristi se kompjutorsko računalo, a ne osobno).

Najprije morate razumjeti da su ključni koncept makronaredbe objekti koji mogu sadržavati druge objekte. Objekt Workbooks odgovoran je za elektroničku knjigu (odnosno dokument). Uključuje objekt Sheets, koji je skup svih listova otvorenog dokumenta. 

Sukladno tome, stanice su objekt Cells. Sadrži sve ćelije određenog lista.

Svaki objekt kvalificiran je argumentima u zagradama. U slučaju ćelija, one se navode ovim redoslijedom. Prvi je naveden broj retka, a zatim broj stupca ili slovo (oba formata su prihvatljiva).

Na primjer, redak koda koji sadrži referencu na ćeliju C5 izgledao bi ovako:

Radne knjige (“Book2.xlsm”). Listovi (“List2”). Ćelije (5, 3)

Radne knjige (“Book2.xlsm”). Listovi (“List2”). Ćelije (5, “C”)

Također možete pristupiti ćeliji pomoću objekta Uredan, cist. Općenito, namijenjeno je davanje reference na raspon (čiji elementi, usput rečeno, također mogu biti apsolutni ili relativni), ali možete jednostavno dati naziv ćelije, u istom formatu kao u Excel dokumentu.

U ovom slučaju linija će izgledati ovako.

Radne knjige (“Knjiga2.xlsm”). Listovi (“Popis2”). Raspon (“C5”)

Možda se čini da je ova opcija praktičnija, ali prednost prve dvije opcije je u tome što možete koristiti varijable u zagradama i dati vezu koja više nije apsolutna, već nešto poput relativne, koja će ovisiti o rezultatima izračuni.

Stoga se makronaredbe mogu učinkovito koristiti u programima. Zapravo, sve reference na ćelije ili raspone ovdje će biti apsolutne i stoga se mogu popraviti s njima. Istina, nije tako zgodno. Korištenje makronaredbi može biti korisno pri pisanju složenih programa s velikim brojem koraka u algoritmu. Općenito, standardni način korištenja apsolutnih ili relativnih referenci mnogo je praktičniji. 

Zaključci

Shvatili smo što je referenca ćelije, kako radi, čemu služi. Razumjeli smo razliku između apsolutnih i relativnih referenci i shvatili što treba učiniti kako bi se jedan tip pretvorio u drugi (jednostavno rečeno, popravio adresu ili je otkvačio). Shvatili smo kako to možete učiniti odmah s velikim brojem vrijednosti. Sada imate fleksibilnost za korištenje ove značajke u pravim situacijama.