Kako zamrzniti celico v Excelovi formuli

Uporabniki se pogosto soočajo s potrebo po pripenjanju celice v formulo. Pojavi se na primer v situacijah, ko želite kopirati formulo, vendar tako, da se povezava ne premakne gor in dol za enako število celic, kot je bila kopirana s prvotne lokacije.

V tem primeru lahko popravite sklic na celico v Excelu. In to je mogoče storiti na več načinov hkrati. Oglejmo si podrobneje, kako doseči ta cilj.

Kaj je Excelova povezava

List je sestavljen iz celic. Vsak od njih vsebuje posebne informacije. Druge celice ga lahko uporabljajo pri izračunih. Toda kako razumejo, od kod dobiti podatke? Pomaga jim ustvarjati povezave.

Vsaka povezava označuje celico z eno črko in eno številko. Črka predstavlja stolpec, številka pa vrstico. 

Obstajajo tri vrste povezav: absolutne, relativne in mešane. Drugi je privzeto nastavljen. Absolutna referenca je tista, ki ima fiksen naslov stolpca in stolpca. V skladu s tem je mešana tista, kjer je določen ločen stolpec ali vrstica.

Metoda 1

Če želite shraniti naslove stolpcev in vrstic, sledite tem korakom:

1. Kliknite na celico, ki vsebuje formulo.
2. Kliknite vrstico formule za celico, ki jo potrebujemo.
3. Pritisnite F4.

Posledično se bo referenca celice spremenila v absolutno. Prepoznamo ga po značilnem znaku dolarja. Če na primer kliknete celico B2 in nato F4, bo povezava videti takole: $B$2.

Kaj pomeni znak za dolar pred delom naslova na celico?

1. Če je postavljen pred črko, pomeni, da sklic na stolpec ostaja enak, ne glede na to, kam je bila formula premaknjena.
2. Če je znak za dolar pred številko, pomeni, da je niz pripet. 

Metoda 2

Ta metoda je skoraj enaka prejšnji, le dvakrat morate pritisniti F4. na primer, če bi imeli celico B2, bo potem postala B$2. Preprosto povedano, na ta način nam je uspelo popraviti linijo. V tem primeru se črka stolpca spremeni.

Zelo priročno je na primer v tabelah, kjer morate v spodnji celici prikazati vsebino druge celice od zgoraj. Namesto večkratne uporabe take formule je dovolj, da popravite vrstico in pustite, da se stolpec spremeni.

Metoda 3

To je popolnoma enako kot prejšnji način, le da morate trikrat pritisniti tipko F4. Potem bo samo sklicevanje na stolpec absolutno, vrstica pa bo ostala fiksna.

Metoda 4

Recimo, da imamo absolutno sklicevanje na celico, vendar je bilo treba to narediti relativno. To storite tako, da tolikokrat pritisnete tipko F4, da na povezavi ni znakov $. Potem bo postalo relativno in ko premaknete ali kopirate formulo, se bosta naslov stolpca in vrstice spremenila.

Pripenjanje celic za velik obseg

Vidimo, da zgornje metode ne predstavljajo nobenih težav pri izvedbi. Toda naloge so specifične. In na primer, kaj storiti, če imamo naenkrat več deset formul, katerih povezave je treba spremeniti v absolutne. 

Na žalost standardne Excelove metode ne bodo dosegle tega cilja. Če želite to narediti, morate uporabiti poseben dodatek, imenovan VBA-Excel. Vsebuje veliko dodatnih funkcij, ki vam omogočajo veliko hitrejše izvajanje pogostih nalog z Excelom.

Vključuje več kot sto uporabniško definiranih funkcij in 25 različnih makrov, poleg tega pa se redno posodablja. Omogoča vam izboljšanje dela s skoraj vsemi vidiki:

1. Celice.
2. Makro
3. Funkcije različnih vrst.
4. Povezave in polja.

Zlasti ta dodatek vam omogoča, da popravite povezave v velikem številu formul hkrati. Če želite to narediti, morate izvesti naslednje korake:

1. Izberite obseg.
2. Odprite zavihek VBA-Excel, ki se prikaže po namestitvi. 
3. Odprite meni »Funkcije«, kjer se nahaja možnost »Zakleni formule«.
   

   

4. Nato se prikaže pogovorno okno, v katerem morate določiti zahtevani parameter. Ta dodatek vam omogoča, da pripnete stolpec in stolpec ločeno skupaj ter odstranite že obstoječe pripenjanje s paketom. Ko je želeni parameter izbran z ustreznim izbirnim gumbom, morate svoja dejanja potrditi s klikom na »V redu«.

Primer

Vzemimo primer, da bo bolj jasno. Recimo, da imamo informacije, ki opisujejo stroške blaga, njegovo skupno količino in prihodke od prodaje. In postavljeni smo pred nalogo, da iz tabele na podlagi količine in stroškov samodejno ugotovimo, koliko denarja smo uspeli zaslužiti brez odbitka izgub.

V našem primeru morate za to vnesti formulo =B2*C2. Kot lahko vidite, je povsem preprosto. Zelo enostavno je uporabiti njen primer za opis, kako lahko popravite naslov celice ali njenega posameznega stolpca ali vrstice. 

Seveda lahko v tem primeru poskusite formulo povleči navzdol z oznako za samodejno izpolnjevanje, vendar bodo v tem primeru celice samodejno spremenjene. Torej, v celici D3 bo druga formula, kjer bodo številke zamenjane s 3. Nadalje, glede na shemo - D4 - bo formula dobila obliko = B4 * C4, D5 - podobno, vendar z številka 5 in tako naprej.

Če je potrebno (v večini primerov se izkaže), potem ni težav. Če pa morate formulo popraviti v eni celici, tako da se med vlečenjem ne spremeni, bo to nekoliko težje. 

Recimo, da moramo določiti prihodek v dolarjih. Vstavimo ga v celico B7. Postanimo malo nostalgični in navedemo stroške 35 rubljev na dolar. V skladu s tem je za določitev prihodka v dolarjih potrebno znesek v rubljih deliti s tečajem dolarja.

Tako je videti v našem primeru.

Če bomo, podobno kot v prejšnji različici, poskušali predpisati formulo, potem nam ne bo uspelo. Podobno se bo formula spremenila v ustrezno. V našem primeru bo tako: =E3*B8. Od tu lahko vidimo. da se je prvi del formule spremenil v E3 in smo si zadali to nalogo, vendar nam drugega dela formule ni treba spremeniti v B8. Zato moramo referenco spremeniti v absolutno. To lahko storite brez pritiska na tipko F4, samo z znakom za dolar.

Ko smo sklic na drugo celico spremenili v absolutno, je postala zaščitena pred spremembami. Zdaj ga lahko varno povlečete z ročico za samodejno izpolnjevanje. Vsi fiksni podatki bodo ostali enaki, ne glede na položaj formule, nedodeljeni podatki pa se bodo spreminjali prilagodljivo. V vseh celicah bodo prihodki v rubljih, opisani v tej vrstici, deljeni z istim tečajem dolarja.

Sama formula bo videti takole:

=D2/$B$7

Pozor! Navedli smo dva znaka za dolar. Na ta način pokažemo programu, da je treba popraviti tako stolpec kot vrstico.

Sklici na celice v makrih

Makro je podprogram, ki vam omogoča avtomatizacijo dejanj. Za razliko od standardne funkcije Excela vam makro omogoča takojšnjo nastavitev določene celice in izvajanje določenih dejanj v le nekaj vrsticah kode. Uporabno za paketno obdelavo informacij, na primer, če ni možnosti za namestitev dodatkov (na primer, uporablja se službeni računalnik, ne osebni).

Najprej morate razumeti, da so ključni koncept makra predmeti, ki lahko vsebujejo druge predmete. Objekt Workbooks je odgovoren za elektronsko knjigo (to je dokument). Vključuje objekt Sheets, ki je zbirka vseh listov odprtega dokumenta. 

V skladu s tem so celice objekt Cells. Vsebuje vse celice določenega lista.

Vsak objekt je kvalificiran z argumenti v oklepajih. V primeru celic so navedene v tem vrstnem redu. Najprej je navedena številka vrstice, ki ji sledi številka stolpca ali črka (obe obliki sta sprejemljivi).

Na primer, vrstica kode, ki vsebuje sklic na celico C5, bi bila videti takole:

Delovni zvezki (»Book2.xlsm«). Listi (»List2«). Celice (5, 3)

Delovni zvezki (“Knjiga2.xlsm”). Listi (“Seznam2”).Celice (5, “C”)

Do celice lahko dostopate tudi z uporabo predmeta Urejeno. Na splošno je namenjen podajanju sklicevanja na obseg (katerih elementi so mimogrede lahko tudi absolutni ali relativni), lahko pa preprosto podate ime celice v enaki obliki kot v Excelovem dokumentu.

V tem primeru bo vrstica videti takole.

Delovni zvezki (“Knjiga2.xlsm”). Listi (“Seznam2”). Obseg (“C5”)

Morda se zdi, da je ta možnost bolj priročna, vendar je prednost prvih dveh možnosti ta, da lahko uporabite spremenljivke v oklepajih in podate povezavo, ki ni več absolutna, ampak nekaj podobnega relativni, ki bo odvisna od rezultatov izračuni.

Tako lahko makre učinkovito uporabljamo v programih. Pravzaprav bodo vsi sklici na celice ali obsege tukaj absolutni in jih je zato mogoče z njimi tudi popraviti. Res je, da ni tako priročno. Uporaba makrov je lahko koristna pri pisanju kompleksnih programov z velikim številom korakov v algoritmu. Na splošno je standardni način uporabe absolutnih ali relativnih referenc veliko bolj priročen. 

Sklepi

Ugotovili smo, kaj je sklic na celico, kako deluje, čemu služi. Razumeli smo razliko med absolutnimi in relativnimi referencami in ugotovili, kaj je treba narediti, da eno vrsto spremenimo v drugo (preprosto povedano, popravimo naslov ali ga odpnemo). Ugotovili smo, kako lahko to storite takoj z velikim številom vrednosti. Zdaj imate možnost, da to funkcijo uporabite v pravih situacijah.