Kaip užšaldyti langelį Excel formulėje

Dažnai vartotojai susiduria su poreikiu prisegti langelį formulėje. Pavyzdžiui, tai atsitinka tais atvejais, kai norite nukopijuoti formulę, bet taip, kad nuoroda nejudėtų aukštyn ir žemyn tiek langelių, kiek buvo nukopijuota iš pradinės vietos.

Tokiu atveju galite pataisyti langelio nuorodą programoje „Excel“. Ir tai galima padaryti keliais būdais vienu metu. Pažiūrėkime atidžiau, kaip pasiekti šį tikslą.

Kas yra Excel nuoroda

Lapas sudarytas iš ląstelių. Kiekviename iš jų yra specifinė informacija. Kitos ląstelės gali jį naudoti skaičiavimuose. Bet kaip jie supranta, iš kur gauti duomenis? Tai padeda jiems sukurti nuorodas.

Kiekviena nuoroda žymi langelį su viena raide ir vienu skaičiumi. Raidė žymi stulpelį, o skaičius – eilutę. 

Yra trijų tipų nuorodos: absoliutus, santykinis ir mišrus. Antrasis yra nustatytas pagal numatytuosius nustatymus. Absoliuti nuoroda yra ta, kuri turi fiksuotą stulpelio ir stulpelio adresą. Atitinkamai, mišrus yra tas, kuriame fiksuojamas atskiras stulpelis arba eilutė.

1 metodas

Norėdami išsaugoti stulpelių ir eilučių adresus, atlikite šiuos veiksmus:

1. Spustelėkite langelį, kuriame yra formulė.
2. Spustelėkite mums reikalingo langelio formulės juostą.
3. Paspauskite F4.

Dėl to langelio nuoroda pasikeis į absoliučią. Jį galima atpažinti iš būdingo dolerio ženklo. Pavyzdžiui, jei spustelėsite langelį B2, tada spustelėkite F4, nuoroda atrodys taip: $B$2.

Ką reiškia dolerio ženklas prieš adreso dalį langelyje?

1. Jei jis dedamas prieš raidę, tai rodo, kad stulpelio nuoroda išlieka ta pati, nesvarbu, kur formulė buvo perkelta.
2. Jei dolerio ženklas yra prieš skaičių, tai reiškia, kad eilutė yra prisegta. 

2 metodas

Šis metodas yra beveik toks pat kaip ir ankstesnis, tik reikia du kartus paspausti F4. Pavyzdžiui, jei turėtume langelį B2, tada po to jis taps B$2. Paprastais žodžiais tariant, tokiu būdu mums pavyko sutvarkyti liniją. Tokiu atveju stulpelio raidė pasikeis.

Tai labai patogu, pavyzdžiui, lentelėse, kur apatiniame langelyje reikia rodyti antrojo langelio turinį iš viršaus. Užuot atlikus tokią formulę daug kartų, užtenka pataisyti eilutę ir leisti stulpeliui pasikeisti.

3 metodas

Tai visiškai toks pat kaip ir ankstesnis metodas, tik reikia tris kartus paspausti F4 klavišą. Tada tik nuoroda į stulpelį bus absoliuti, o eilutė liks fiksuota.

4 metodas

Tarkime, kad turime absoliučią nuorodą į ląstelę, bet čia reikėjo ją padaryti santykinę. Norėdami tai padaryti, paspauskite klavišą F4 tiek kartų, kad nuorodoje nebūtų jokių $ ženklų. Tada jis taps santykinis, o perkėlus ar nukopijavus formulę pasikeis ir stulpelio, ir eilutės adresas.

Ląstelių tvirtinimas dideliam diapazonui

Matome, kad pirmiau minėti metodai nesukelia jokių sunkumų. Tačiau užduotys yra specifinės. Ir, pavyzdžiui, ką daryti, jei vienu metu turime kelias dešimtis formulių, kurių nuorodas reikia paversti absoliučiomis. 

Deja, standartiniai Excel metodai šio tikslo nepasieks. Norėdami tai padaryti, turite naudoti specialų priedą, vadinamą VBA-Excel. Jame yra daug papildomų funkcijų, kurios leidžia daug greičiau atlikti įprastas užduotis naudojant „Excel“.

Jame yra daugiau nei šimtas vartotojo nustatytų funkcijų ir 25 skirtingos makrokomandos, be to, ji reguliariai atnaujinama. Tai leidžia patobulinti darbą beveik bet kokiu aspektu:

1. Ląstelės.
2. Makro
3. Įvairių tipų funkcijos.
4. Nuorodos ir masyvai.

Visų pirma, šis priedas leidžia vienu metu taisyti nuorodas daugelyje formulių. Norėdami tai padaryti, turite atlikti šiuos veiksmus:

1. Pasirinkite diapazoną.
2. Atidarykite VBA-Excel skirtuką, kuris pasirodys įdiegus. 
3. Atidarykite meniu „Funkcijos“, kur yra parinktis „Užrakinti formules“.
   

   

4. Tada pasirodys dialogo langas, kuriame turėsite nurodyti reikiamą parametrą. Šis priedas leidžia prisegti stulpelį ir stulpelį atskirai, kartu, taip pat pašalinti jau esamą prisegimą su paketu. Pasirinkę reikiamą parametrą naudodami atitinkamą radijo mygtuką, turite patvirtinti savo veiksmus spustelėdami „Gerai“.

Pavyzdys

Paimkime pavyzdį, kad būtų aiškiau. Tarkime, turime informaciją, kuri apibūdina prekių savikainą, bendrą jų kiekį ir pardavimo pajamas. Ir mes susiduriame su užduotimi, kad lentelė pagal kiekį ir kainą automatiškai nustatytų, kiek pinigų pavyko uždirbti, neatskaičius nuostolių.

Mūsų pavyzdyje tam reikia įvesti formulę =B2*C2. Tai gana paprasta, kaip matote. Jos pavyzdžiu labai lengva aprašyti, kaip galite pataisyti langelio arba atskiro jo stulpelio ar eilutės adresą. 

Žinoma, šiame pavyzdyje galite pabandyti vilkti formulę žemyn naudodami automatinio pildymo žymeklį, tačiau tokiu atveju langeliai bus automatiškai pakeisti. Taigi langelyje D3 bus dar viena formulė, kurioje skaičiai bus atitinkamai pakeisti 3. Toliau pagal schemą – D4 – formulė bus = B4 * C4, D5 – panašiai, bet su numeris 5 ir pan.

Jei reikia (daugeliu atvejų taip išeina), tada problemų nekyla. Bet jei jums reikia pataisyti formulę viename langelyje, kad ji nepasikeistų vilkimo metu, tai bus šiek tiek sunkiau. 

Tarkime, turime nustatyti dolerio pajamas. Įdėkime jį į langelį B7. Šiek tiek nostalgiškiau ir nurodykime 35 rublių už dolerį kainą. Atitinkamai, norint nustatyti pajamas doleriais, reikia padalyti sumą rubliais iš dolerio kurso.

Štai kaip tai atrodo mūsų pavyzdyje.

Jei mes, panašiai kaip ankstesnėje versijoje, bandysime nurodyti formulę, tada mums nepavyks. Panašiai formulė pasikeis į atitinkamą. Mūsų pavyzdyje tai bus taip: =E3*B8. Iš čia matome. kad pirmoji formulės dalis virto E3, ir mes nustatome sau šią užduotį, tačiau antrosios formulės dalies keisti į B8 nereikia. Todėl nuorodą turime paversti absoliučia. Tai galite padaryti nepaspaudę klavišo F4, tiesiog įdėję dolerio ženklą.

Po to, kai nuorodą į antrą langelį pavertėme absoliučia, ji tapo apsaugota nuo pakeitimų. Dabar galite saugiai vilkti jį naudodami automatinio pildymo rankenėlę. Visi fiksuoti duomenys išliks tokie patys, nepaisant formulės padėties, o neįsipareigoti duomenys keisis lanksčiai. Visuose langeliuose šioje eilutėje aprašytos pajamos rubliais bus padalintos iš to paties dolerio kurso.

Pati formulė atrodys taip:

=D2/$B$7

Dėmesio! Mes nurodėme du dolerio ženklus. Tokiu būdu parodome programai, kad reikia pataisyti ir stulpelį, ir eilutę.

Ląstelių nuorodos makrokomandose

Makrokomandas yra paprogramė, leidžianti automatizuoti veiksmus. Skirtingai nei standartinės „Excel“ funkcijos, makrokomanda leidžia iš karto nustatyti konkretų langelį ir atlikti tam tikrus veiksmus vos keliomis kodo eilutėmis. Naudinga paketiniam informacijos apdorojimui, pavyzdžiui, jei nėra galimybės įdiegti priedų (pavyzdžiui, naudojamas įmonės kompiuteris, o ne asmeninis).

Pirmiausia turite suprasti, kad pagrindinė makrokomandos sąvoka yra objektai, kuriuose gali būti kitų objektų. Objektas Darbaknygiai yra atsakingas už elektroninę knygą (tai yra dokumentą). Tai apima objektą Skaičiuokliai, kuris yra visų atidaryto dokumento lapų rinkinys. 

Atitinkamai ląstelės yra ląstelių objektas. Jame yra visos konkretaus lapo ląstelės.

Kiekvienas objektas yra kvalifikuojamas su skliausteliuose esančiais argumentais. Ląstelių atveju jie pateikiami tokia tvarka. Pirmiausia pateikiamas eilutės numeris, po to – stulpelio numeris arba raidė (priimtini abu formatai).

Pavyzdžiui, kodo eilutė su nuoroda į langelį C5 atrodytų taip:

Darbaknygės („Knyga2.xlsm“). Lapai („Sąrašas2“). Langeliai (5, 3)

Darbaknygės („Knyga2.xlsm“). Lapai („Sąrašas2“). Langeliai (5, „C“)

Taip pat galite pasiekti langelį naudodami objektą Tvarkingas. Apskritai, ji skirta pateikti nuorodą į diapazoną (kurio elementai, beje, taip pat gali būti absoliutūs arba santykiniai), tačiau galite tiesiog nurodyti langelio pavadinimą tokiu pačiu formatu kaip ir Excel dokumente.

Šiuo atveju linija atrodys taip.

Darbaknygės („Knyga2.xlsm“). Lapai („Sąrašas2“). Diapazonas („C5“)

Gali atrodyti, kad ši parinktis yra patogesnė, tačiau pirmųjų dviejų parinkčių pranašumas yra tas, kad galite naudoti kintamuosius skliausteliuose ir pateikti nuorodą, kuri nebėra absoliuti, o kažkas panašaus į santykinę, kuri priklausys nuo rezultatų skaičiavimus.

Taigi, makrokomandos gali būti efektyviai naudojamos programose. Tiesą sakant, visos nuorodos į langelius ar diapazonus čia bus absoliučios, todėl jas taip pat galima pataisyti. Tiesa, tai nėra taip patogu. Makrokomandų naudojimas gali būti naudingas rašant sudėtingas programas, kurių algoritme yra daug žingsnių. Apskritai standartinis absoliučių ar santykinių nuorodų naudojimo būdas yra daug patogesnis. 

Išvados

Mes išsiaiškinome, kas yra langelio nuoroda, kaip ji veikia, kam ji skirta. Supratome skirtumą tarp absoliučių ir santykinių nuorodų ir išsiaiškinome, ką reikia padaryti, norint vieną tipą paversti kitu (paprasčiau tariant, pataisyti adresą arba atsegti). Mes supratome, kaip galite tai padaryti iš karto naudodami daugybę verčių. Dabar galite lanksčiai naudoti šią funkciją tinkamose situacijose.