Kako zamrznuti ćeliju u Excel formuli

Često se korisnici suočavaju s potrebom da zakače ćeliju u formulu. Na primjer, javlja se u situacijama kada želite da kopirate formulu, ali tako da se veza ne pomiče gore-dolje za isti broj ćelija kao što je kopirana sa svoje originalne 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 bliže kako postići ovaj cilj.

Šta je Excel veza

List se sastoji od ćelija. Svaki od njih sadrži specifične informacije. Druge ćelije ga mogu koristiti u proračunima. Ali kako razumjeti odakle dobiti podatke? Pomaže im da prave veze.

Svaka veza označava ćeliju sa jednim slovom i jednim brojem. Slovo predstavlja kolonu, a broj red. 

Postoje tri vrste veza: apsolutne, relativne i mješovite. Drugi je postavljen po defaultu. Apsolutna referenca je ona koja ima fiksnu adresu i kolone i kolone. Shodno tome, mješoviti je onaj gdje je fiksiran ili zaseban stupac ili red.

1 metoda

Da biste sačuvali adrese kolona i reda, 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, veza će izgledati ovako: $B$2.

Kako zamrznuti ćeliju u Excel formuli
1
Kako zamrznuti ćeliju u Excel formuli
2

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

  1. Ako se stavi ispred slova, to znači da referenca kolone ostaje ista, bez obzira gdje je formula pomjerena.
  2. Ako je znak dolara ispred broja, to znači da je niz zakačen. 

2 metoda

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

Kako zamrznuti ćeliju u Excel formuli
3

Vrlo je zgodno, na primjer, u tabelama u kojima je potrebno prikazati sadržaj druge ćelije odozgo u donjoj ćeliji. Umjesto da takvu formulu radite mnogo puta, dovoljno je popraviti red i pustiti stupac da se promijeni.

3 metoda

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

Kako zamrznuti ćeliju u Excel formuli
4

4 metoda

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

Kako zamrznuti ćeliju u Excel formuli
5

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 desetina 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, trebate koristiti poseban dodatak koji se zove VBA-Excel. Sadrži mnoge dodatne funkcije koje vam omogućavaju da obavljate uobičajene zadatke s Excelom mnogo brže.

Uključuje više od stotinu korisnički definiranih funkcija i 25 različitih makroa, a također se redovno ažurira. Omogućava vam da poboljšate rad s gotovo bilo kojeg aspekta:

  1. Ćelije.
  2. Makro.
  3. Funkcije različitih tipova.
  4. Linkovi i nizovi.

Konkretno, ovaj dodatak vam omogućava 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 meni „Funkcije“, gde se nalazi opcija „Zaključaj formule“.
    Kako zamrznuti ćeliju u Excel formuli
    6
  4. Zatim će se pojaviti dijaloški okvir u kojem trebate navesti traženi parametar. Ovaj dodatak vam omogućava da zakačite kolonu i kolonu odvojeno, zajedno, kao i da uklonite već postojeće kačenje s paketom. Nakon što je traženi parametar odabran pomoću odgovarajućeg radio dugmeta, morate potvrditi svoje radnje klikom na „OK“.

primjer

Uzmimo primjer da bude jasnije. Recimo da imamo informaciju koja opisuje trošak robe, njenu ukupnu količinu i prihod od prodaje. I pred nama je zadatak da tabela na osnovu količine i troškova automatski odredi koliko smo novca uspjeli zaraditi bez oduzimanja gubitaka.

Kako zamrznuti ćeliju u Excel formuli
7

U našem primjeru, za to trebate unijeti formulu =B2*C2. Prilično je jednostavno, kao što vidite. Vrlo je lako koristiti njen primjer da opišete kako možete popraviti adresu ćelije ili njenog pojedinačnog stupca ili reda. 

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 će biti još jedna formula, gdje će brojevi biti zamijenjeni, odnosno, sa 3. Nadalje, prema šemi - D4 - formula će imati oblik = B4 * C4, D5 - slično, ali sa 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 prihod u dolarima. Stavimo to u ćeliju B7. Budimo malo nostalgični i naznačimo cijenu od 35 rubalja po dolaru. Shodno tome, da bi se odredio prihod u dolarima, potrebno je podijeliti iznos u rubljama s kursom dolara.

Evo kako to izgleda u našem primjeru.

Kako zamrznuti ćeliju u Excel formuli
8

Ako, slično kao u prethodnoj verziji, pokušamo propisati formulu, 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 mi smo sebi postavili 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, ona je postala zaštićena od promjena. Sada ga možete bezbedno prevući pomoću ručke za automatsko popunjavanje. Svi fiksni podaci će ostati isti, bez obzira na poziciju formule, a nepovezani podaci će se fleksibilno mijenjati. U svim ćelijama, prihod u rubljama opisan u ovom redu bit će podijeljen istim kursom dolara.

Sama formula će izgledati ovako:

=D2/$B$7

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

Reference ćelije u makroima

Makro je potprogram koji vam omogućava da automatizujete radnje. Za razliku od standardne funkcionalnosti Excela, makro vam omogućava da odmah postavite određenu ćeliju i izvršite određene radnje u samo nekoliko redova koda. Korisno za grupnu obradu informacija, na primjer, ako ne postoji način da se instaliraju dodaci (na primjer, koristi se računar kompanije, a ne lični).

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

U skladu s tim, ćelije su objekt ćelije. Sadrži sve ćelije određenog lista.

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

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

Radne sveske(“Book2.xlsm”).Stabovi(“Lista2”).Ćelije(5, 3)

Radne sveske(“Book2.xlsm”).Stabovi(“Lista2”).Ćelije(5, “C”)

Također možete pristupiti ćeliji pomoću objekta Uredno. Općenito, namjerava se dati referenca na raspon (čiji elementi, inače, također mogu biti apsolutni ili relativni), ali možete jednostavno dati ime ćelije, u istom formatu kao u Excel dokumentu.

U ovom slučaju, linija će izgledati ovako.

Radne sveske(“Book2.xlsm”).Sheets(“List2”).Raspon(“C5”)

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

Dakle, makroi se mogu efikasno koristiti u programima. Zapravo, sve reference na ćelije ili opsege ovdje će biti apsolutne, pa se stoga mogu i fiksirati s njima. Istina, nije tako zgodno. Upotreba makroa može biti korisna pri pisanju složenih programa sa velikim brojem koraka u algoritmu. Općenito, standardni način korištenja apsolutnih ili relativnih referenci je mnogo prikladniji. 

zaključci

Shvatili smo šta je referenca ćelije, kako funkcioniše, čemu služi. Shvatili smo razliku između apsolutnih i relativnih referenci i shvatili šta treba učiniti da bi se jedan tip pretvorio u drugi (jednostavnim riječima, popraviti adresu ili je otkačiti). Shvatili smo kako to možete učiniti odmah s velikim brojem vrijednosti. Sada imate fleksibilnost da koristite ovu funkciju u pravim situacijama.

Ostavite odgovor