Si të ngrini një qelizë në një formulë Excel

Shpesh, përdoruesit përballen me nevojën për të vendosur një qelizë në një formulë. Për shembull, ndodh në situata kur dëshironi të kopjoni një formulë, por në mënyrë që lidhja të mos lëvizë lart e poshtë në të njëjtin numër qelizash siç është kopjuar nga vendndodhja e saj origjinale.

Në këtë rast, mund të rregulloni referencën e qelizës në Excel. Dhe kjo mund të bëhet në disa mënyra në të njëjtën kohë. Le të hedhim një vështrim më të afërt se si ta arrijmë këtë qëllim.

Çfarë është një lidhje Excel

Fleta përbëhet nga qeliza. Secila prej tyre përmban informacion specifik. Qelizat e tjera mund ta përdorin atë në llogaritjet. Por si e kuptojnë ata se nga t'i marrin të dhënat? Ai i ndihmon ata të krijojnë lidhje.

Çdo lidhje cakton një qelizë me një shkronjë dhe një numër. Një shkronjë përfaqëson një kolonë dhe një numër përfaqëson një rresht. 

Ekzistojnë tre lloje lidhjesh: absolute, relative dhe të përziera. E dyta është vendosur si parazgjedhje. Një referencë absolute është ajo që ka një adresë fikse të një kolone dhe një kolone. Prandaj, i përzier është ai ku fiksohet ose një kolonë e veçantë ose një rresht.

Metoda 1

Për të ruajtur adresat e kolonave dhe të rreshtave, ndiqni këto hapa:

1. Klikoni në qelizën që përmban formulën.
2. Klikoni në shiritin e formulës për qelizën që na nevojitet.
3. Shtypni F4.

Si pasojë, referenca e qelizës do të ndryshojë në absolute. Mund të njihet nga shenja karakteristike e dollarit. Për shembull, nëse klikoni në qelizën B2 dhe më pas klikoni në F4, lidhja do të duket kështu: $B$2.

Çfarë do të thotë shenja e dollarit përpara pjesës së adresës për qelizë?

1. Nëse vendoset përpara një shkronje, tregon se referenca e kolonës mbetet e njëjtë, pavarësisht se ku është zhvendosur formula.
2. Nëse shenja e dollarit është përpara numrit, kjo tregon se vargu është i fiksuar. 

Metoda 2

Kjo metodë është pothuajse e njëjtë me atë të mëparshme, vetëm duhet të shtypni F4 dy herë. për shembull, nëse kishim qelizë B2, atëherë pas kësaj do të bëhet B$2. Me fjalë të thjeshta, në këtë mënyrë arritëm të rregullojmë vijën. Në këtë rast, shkronja e kolonës do të ndryshojë.

Është shumë i përshtatshëm, për shembull, në tabelat ku duhet të shfaqni përmbajtjen e qelizës së dytë nga lart në qelizën e poshtme. Në vend që ta bëni një formulë të tillë shumë herë, mjafton të rregulloni rreshtin dhe të lini kolonën të ndryshojë.

Metoda 3

Kjo është plotësisht e njëjtë me metodën e mëparshme, vetëm duhet të shtypni butonin F4 tre herë. Atëherë vetëm referenca në kolonë do të jetë absolute dhe rreshti do të mbetet i fiksuar.

Metoda 4

Supozoni se kemi një referencë absolute për një qelizë, por këtu ishte e nevojshme ta bënim atë relative. Për ta bërë këtë, shtypni tastin F4 aq herë sa të mos ketë shenja $ në lidhje. Pastaj do të bëhet relative dhe kur të zhvendosni ose kopjoni formulën, adresa e kolonës dhe adresa e rreshtit do të ndryshojnë.

Mbyllja e qelizave për një gamë të madhe

Shohim që metodat e mësipërme nuk paraqesin fare vështirësi për t'u kryer. Por detyrat janë specifike. Dhe, për shembull, çfarë të bëjmë nëse kemi disa dhjetëra formula në të njëjtën kohë, lidhjet në të cilat duhet të shndërrohen në ato absolute. 

Fatkeqësisht, metodat standarde të Excel nuk do ta arrijnë këtë qëllim. Për ta bërë këtë, duhet të përdorni një shtesë të veçantë të quajtur VBA-Excel. Ai përmban shumë veçori shtesë që ju lejojnë të kryeni detyra të zakonshme me Excel shumë më shpejt.

Ai përfshin më shumë se njëqind funksione të përcaktuara nga përdoruesi dhe 25 makro të ndryshme, dhe gjithashtu përditësohet rregullisht. Kjo ju lejon të përmirësoni punën me pothuajse çdo aspekt:

1. Qelizat.
2. Macro.
3. Funksionet e llojeve të ndryshme.
4. Lidhjet dhe vargjet.

Në veçanti, kjo shtesë ju lejon të rregulloni lidhjet në një numër të madh formulash menjëherë. Për ta bërë këtë, duhet të kryeni hapat e mëposhtëm:

1. Zgjidhni një gamë.
2. Hapni skedën VBA-Excel që do të shfaqet pas instalimit. 
3. Hapni menynë "Funksionet", ku ndodhet opsioni "Formulat e kyçjes".
   

   

4. Më pas, do të shfaqet një kuti dialogu në të cilën duhet të specifikoni parametrin e kërkuar. Kjo shtesë ju lejon të vendosni një kolonë dhe një kolonë veç e veç, së bashku, dhe gjithashtu të hiqni një ngjitje tashmë ekzistuese me një paketë. Pasi të zgjidhet parametri i kërkuar duke përdorur butonin përkatës të radios, duhet të konfirmoni veprimet tuaja duke klikuar "OK".

Shembull

Le të marrim një shembull për ta bërë më të qartë. Le të themi se kemi informacion që përshkruan koston e mallrave, sasinë totale të tij dhe të ardhurat nga shitjet. Dhe ne jemi përballë detyrës që tabela, në bazë të sasisë dhe kostos, të përcaktojë automatikisht se sa para kemi arritur të fitojmë pa zbritur humbjet.

Në shembullin tonë, për këtë ju duhet të vendosni formulën =B2*C2. Është mjaft e thjeshtë, siç mund ta shihni. Është shumë e lehtë të përdoret shembulli i saj për të përshkruar se si mund të rregulloni adresën e një qelize ose kolonën ose rreshtin e saj individual. 

Sigurisht, në këtë shembull, mund të përpiqeni të tërhiqni formulën poshtë duke përdorur shënuesin e plotësimit automatik, por në këtë rast, qelizat do të ndryshohen automatikisht. Pra, në qelizën D3 do të ketë një formulë tjetër, ku numrat do të zëvendësohen, përkatësisht, me 3. Më tej, sipas skemës - D4 - formula do të marrë formën = B4 * C4, D5 - në mënyrë të ngjashme, por me numri 5 e kështu me radhë.

Nëse është e nevojshme (në shumicën e rasteve rezulton), atëherë nuk ka probleme. Por nëse duhet ta rregulloni formulën në një qelizë në mënyrë që të mos ndryshojë kur tërhiqet, atëherë kjo do të jetë disi më e vështirë. 

Supozoni se duhet të përcaktojmë të ardhurat nga dollari. Le ta vendosim në qelizën B7. Le të bëhemi pak nostalgjikë dhe të tregojmë koston prej 35 rubla për dollar. Prandaj, për të përcaktuar të ardhurat në dollarë, është e nevojshme të ndahet shuma në rubla me kursin e këmbimit të dollarit.

Ja se si duket në shembullin tonë.

Nëse ne, ngjashëm me versionin e mëparshëm, përpiqemi të përshkruajmë një formulë, atëherë do të dështojmë. Në mënyrë të ngjashme, formula do të ndryshojë në atë të përshtatshme. Në shembullin tonë, do të jetë kështu: =E3*B8. Nga këtu mund të shohim. se pjesa e parë e formulës është kthyer në E3, dhe ne i kemi vënë vetes këtë detyrë, por nuk kemi nevojë të ndryshojmë pjesën e dytë të formulës në B8. Prandaj, ne duhet ta kthejmë referencën në një referencë absolute. Këtë mund ta bëni pa shtypur tastin F4, thjesht duke vendosur një shenjë dollari.

Pasi e kthyem referencën për qelizën e dytë në një absolute, ajo u mbrojt nga ndryshimet. Tani mund ta tërhiqni me siguri duke përdorur dorezën e plotësimit automatik. Të gjitha të dhënat fikse do të mbeten të njëjta, pavarësisht nga pozicioni i formulës, dhe të dhënat e pakomponuara do të ndryshojnë në mënyrë fleksibël. Në të gjitha qelizat, të ardhurat në rubla të përshkruara në këtë rresht do të ndahen me të njëjtin kurs këmbimi të dollarit.

Vetë formula do të duket si kjo:

=D2/$B$7

Kujdes! Ne kemi treguar dy shenja dollari. Në këtë mënyrë, ne i tregojmë programit se kolona dhe rreshti duhet të rregullohen.

Referencat e qelizave në makro

Një makro është një nënprogram që ju lejon të automatizoni veprimet. Ndryshe nga funksionaliteti standard i Excel, një makro ju lejon të vendosni menjëherë një qelizë specifike dhe të kryeni veprime të caktuara në vetëm disa rreshta kodi. E dobishme për përpunimin grupor të informacionit, për shembull, nëse nuk ka asnjë mënyrë për të instaluar shtesa (për shembull, përdoret një kompjuter kompanie, jo një personal).

Së pari ju duhet të kuptoni se koncepti kryesor i një makro është objektet që mund të përmbajnë objekte të tjera. Objekti i Librave të Punës është përgjegjës për librin elektronik (domethënë dokumentin). Ai përfshin objektin Sheets, i cili është një koleksion i të gjitha fletëve të një dokumenti të hapur. 

Prandaj, qelizat janë një objekt i qelizave. Ai përmban të gjitha qelizat e një fletë të veçantë.

Çdo objekt cilësohet me argumente në kllapa. Në rastin e qelizave, ato referohen në këtë renditje. Numri i rreshtit renditet së pari, i ndjekur nga numri ose shkronja e kolonës (të dy formatet janë të pranueshme).

Për shembull, një rresht kodi që përmban një referencë për qelizën C5 do të duket kështu:

Librat e punës ("Libri2.xlsm"). Fletë ("Lista2"). Qelizat (5, 3)

Librat e punës ("Libri2.xlsm"). Fletë ("Lista2"). Qelizat (5, "C")

Ju gjithashtu mund të përdorni një qelizë duke përdorur një objekt E rregullt. Në përgjithësi, synohet të jepet një referencë për një gamë (elementet e të cilit, meqë ra fjala, mund të jenë gjithashtu absolute ose relative), por thjesht mund të jepni një emër qelize, në të njëjtin format si në një dokument Excel.

Në këtë rast, linja do të duket kështu.

Librat e punës ("Libri2.xlsm"). Fletë ("Lista2"). Gama ("C5")

Mund të duket se ky opsion është më i përshtatshëm, por avantazhi i dy opsioneve të para është se mund të përdorni variabla në kllapa dhe të jepni një lidhje që nuk është më absolute, por diçka si ajo relative, e cila do të varet nga rezultatet e llogaritjet.

Kështu, makro mund të përdoren në mënyrë efektive në programe. Në fakt, të gjitha referencat për qelizat ose vargjet këtu do të jenë absolute, dhe për këtë arsye mund të rregullohen edhe me to. Vërtetë, nuk është aq i përshtatshëm. Përdorimi i makrove mund të jetë i dobishëm kur shkruani programe komplekse me një numër të madh hapash në algoritëm. Në përgjithësi, mënyra standarde e përdorimit të referencave absolute ose relative është shumë më e përshtatshme. 

Konkluzione

Ne kuptuam se çfarë është një referencë qelize, si funksionon, për çfarë shërben. Kuptuam ndryshimin midis referencave absolute dhe relative dhe kuptuam se çfarë duhet bërë për të kthyer një lloj në një tjetër (me fjalë të thjeshta, rregulloni adresën ose zhgozhdoni atë). Ne kuptuam se si mund ta bëni menjëherë me një numër të madh vlerash. Tani ju keni fleksibilitetin për ta përdorur këtë veçori në situatat e duhura.