Nola izoztu gelaxka bat Excel formula batean

Sarritan, erabiltzaileak formula batean gelaxka bat ainguratzeko beharraren aurrean aurkitzen dira. Adibidez, formula bat kopiatu nahi duzun egoeretan gertatzen da, baina esteka ez dadin gora eta behera bere jatorrizko kokapenetik kopiatu zen gelaxka-kopuru berean mugitu.

Kasu honetan, gelaxka-erreferentzia Excel-en konpondu dezakezu. Eta hau hainbat modutan egin daiteke aldi berean. Ikus dezagun nola lortu helburu hori.

Zer da Excel esteka bat

Xafla zelulez osatuta dago. Horietako bakoitzak informazio zehatza dauka. Beste gelaxka batzuek erabil dezakete kalkuluetan. Baina nola ulertzen dute nondik atera datuak? Loturak egiten laguntzen die.

Esteka bakoitzak gelaxka bat izendatzen du letra eta zenbaki batekin. Letra batek zutabe bat adierazten du eta zenbaki batek errenkada bat. 

Hiru lotura mota daude: absolutuak, erlatiboak eta mistoak. Bigarrena lehenespenez ezarrita dago. Erreferentzia absolutua zutabe baten eta zutabe baten helbide finkoa duena da. Horren arabera, mistoa da zutabe bereizi bat edo errenkada bat finkatzen dena.

1 metodoa

Zutabe zein errenkaden helbideak gordetzeko, jarraitu urrats hauek:

1. Egin klik formula duen gelaxkan.
2. Egin klik behar dugun gelaxkaren formula-barran.
3. Prentsa F4.

Ondorioz, gelaxka-erreferentzia absolutu bihurtuko da. Dolarraren zeinu bereizgarriarekin antzeman daiteke. Adibidez, B2 gelaxkan klik egiten baduzu eta ondoren F4 sakatzen baduzu, estekak honela izango du: $B$2.

Zer esan nahi du dolarraren seinaleak gelaxka bakoitzeko helbidearen zatiaren aurretik?

1. Letra baten aurrean jartzen bada, zutabe-erreferentziak berdin jarraitzen duela adierazten du, formula nora eraman den ere.
2. Dolarraren ikurra zenbakiaren aurrean badago, katea loturik dagoela adierazten du. 

2 metodoa

Metodo hau aurrekoaren ia berdina da, bakarrik F4 bi aldiz sakatu behar duzu. adibidez, B2 gelaxka bagenuen, ondoren B$2 bihurtuko da. Hitz soiletan, horrela marra finkatzea lortu dugu. Kasu honetan, zutabearen letra aldatuko da.

Oso erosoa da, adibidez, bigarren gelaxkako edukia goitik beheko gelaxkan bistaratu behar duzun tauletan. Halako formula bat askotan egin beharrean, nahikoa da errenkada konpondu eta zutabea aldatzen uztea.

3 metodoa

Hau aurreko metodoaren guztiz berdina da, soilik F4 tekla hiru aldiz sakatu behar duzu. Orduan, zutabearen erreferentzia bakarrik izango da absolutua, eta errenkada finkoa geratuko da.

4 metodoa

Demagun gelaxka baten erreferentzia absolutua dugula, baina hemen beharrezkoa zen erlatiboa egitea. Horretarako, sakatu F4 tekla hainbeste aldiz estekan $ seinalerik ez dagoela. Orduan erlatibo bihurtuko da, eta formula mugitzen edo kopiatzen duzunean, zutabe helbidea zein errenkada helbidea aldatuko dira.

Gelaxkak ainguratzea sorta handi baterako

Ikusten dugu goiko metodoek ez dutela batere zailtasunik aurkezten. Baina zereginak zehatzak dira. Eta, adibidez, zer egin aldi berean hainbat dozena formula baditugu, zeinetan loturak absolutu bihurtu behar diren. 

Zoritxarrez, Excel metodo estandarrak ez du helburu hori lortuko. Horretarako, VBA-Excel izeneko gehigarri berezi bat erabili behar duzu. Excel-ekin ohiko zereginak askoz azkarrago egiteko aukera ematen duten funtzio gehigarri asko ditu.

Erabiltzaileak definitutako ehun funtzio baino gehiago eta 25 makro ezberdin biltzen ditu, eta, gainera, aldizka eguneratzen da. Ia edozein alderdirekin lana hobetzeko aukera ematen du:

1. Zelulak.
2. Makroa.
3. Mota ezberdinetako funtzioak.
4. Estekak eta arrayak.

Bereziki, gehigarri honek formula ugaritako estekak aldi berean konpon ditzakezu. Horretarako, urrats hauek egin behar dituzu:

1. Hautatu barruti bat.
2. Ireki instalatu ondoren agertuko den VBA-Excel fitxa. 
3. Ireki "Funtzioak" menua, non "Blokeatu formulak" aukera dagoen.
   

   

4. Ondoren, elkarrizketa-koadro bat agertuko da eta bertan beharrezko parametroa zehaztu behar duzu. Gehigarri honek zutabe bat eta zutabe bat banan-banan ainguratzeko aukera ematen du, eta dagoeneko dagoen ainguraketa pakete batekin kentzeko aukera ematen du. Beharrezko parametroa dagokion irrati-botoia erabiliz hautatu ondoren, zure ekintzak berretsi behar dituzu "Ados" sakatuz.

Adibidea

Har dezagun adibide bat argiago uzteko. Demagun salgaien kostua, bere kantitate osoa eta salmenten diru-sarrerak deskribatzen dituen informazioa dugula. Eta mahaia egiteko zereginaren aurrean gaude, kantitatearen eta kostuaren arabera, galerak kendu gabe zenbat diru irabaztea lortu genuen automatikoki zehaztea.

Gure adibidean, horretarako = formula idatzi behar duzuB2*C2. Nahiko sinplea da, ikusten duzuenez. Oso erraza da bere adibidea erabiltzea gelaxka baten helbidea edo bere zutabe edo errenkada banaka nola konpondu dezakezun deskribatzeko. 

Jakina, adibide honetan, formula behera arrastatzen saia zaitezke betetze automatikoko markatzailea erabiliz, baina kasu honetan, gelaxkak automatikoki aldatuko dira. Beraz, D3 gelaxkan beste formula bat egongo da, non zenbakiak ordezkatuko diren, hurrenez hurren, 3. Gainera, eskemaren arabera - D4 - formulak = B4 * C4, D5 forma hartuko du - antzera, baina 5 zenbakia eta abar.

Beharrezkoa bada (kasu gehienetan gertatzen da), orduan ez dago arazorik. Baina formula gelaxka batean konpondu behar baduzu arrastatzean aldatu ez dadin, orduan zailagoa izango da. 

Demagun dolarraren diru-sarrerak zehaztu behar ditugula. Jar dezagun B7 gelaxkan. Jar gaitezen nostalgiko apur bat eta adierazi dolar bakoitzeko 35 errubloren kostua. Horren arabera, diru-sarrerak dolarretan zehazteko, beharrezkoa da errubloen zenbatekoa dolarraren truke-tasaren arabera zatitzea.

Hona hemen gure adibidean nolakoa den.

Gu, aurreko bertsioaren antzera, formula bat errezetatzen saiatzen bagara, orduan huts egingo dugu. Era berean, formula egokia den batera aldatuko da. Gure adibidean, honela izango da: =E3*B8. Hemendik ikus dezakegu. formularen lehen zatia E3 bihurtu dela, eta geure buruari egin genion zeregin hori, baina ez dugu formularen bigarren zatia B8ra aldatu behar. Horregatik, erreferentzia absolutu bihurtu behar dugu. Hau egin dezakezu F4 tekla sakatu gabe, dolarraren seinalea jarriz.

Bigarren gelaxkaren erreferentzia absolutu bihurtu genuenean, aldaketetatik babestu zen. Orain segurtasunez arrastatu dezakezu betetze automatikoko heldulekua erabiliz. Datu finko guztiak berdin jarraituko dute, formularen posizioa edozein dela ere, eta konprometitu gabeko datuak malgutasunez aldatuko dira. Gelaxka guztietan, lerro honetan deskribatutako errubloen diru-sarrerak dolarraren truke-tasa berdinarekin banatuko dira.

Formula bera honela izango da:

=D2/$B$7

Arreta! Bi dolarraren zeinu adierazi ditugu. Horrela, zutabea zein errenkada konpondu behar direla erakusten diogu programari.

Gelaxken erreferentziak makroetan

Makro bat ekintzak automatizatzeko aukera ematen duen azpierrutina da. Excel-en funtzionalitate estandarrak ez bezala, makro batek gelaxka zehatz bat berehala ezartzeko aukera ematen du eta zenbait ekintza kode lerro gutxitan egiteko. Informazioa multzoka prozesatzeko erabilgarria, adibidez, gehigarriak instalatzeko modurik ez badago (adibidez, enpresako ordenagailua erabiltzen da, ez pertsonala).

Lehenik eta behin, makro baten funtsezko kontzeptua beste objektu batzuk izan ditzaketen objektuak direla ulertu behar duzu. Lan-koadernoak objektua liburu elektronikoaren (hau da, dokumentuaren) arduraduna da. Orriak objektua barne hartzen du, hau da, irekitako dokumentu baten orri guztien bilduma. 

Horren arabera, zelulak Cells objektu bat dira. Fitxa jakin bateko gelaxka guztiak ditu.

Objektu bakoitza parentesi arteko argumentuekin kalifikatzen da. Gelaxken kasuan, ordena honetan aipatzen dira. Errenkada-zenbakia agertzen da lehenik, eta ondoren zutabe-zenbakia edo letra (formatu biak onargarriak dira).

Adibidez, C5 gelaxkaren erreferentzia bat duen kode-lerroa honelakoa litzateke:

Lan-koadernoak ("Book2.xlsm"). Fitxak ("List2"). Gelaxkak (5, 3)

Lan-koadernoak ("Book2.xlsm"). Fitxak ("List2"). Gelaxkak (5, "C")

Objektu bat erabiliz gelaxka batera ere sar zaitezke Txukuna. Oro har, barruti bati erreferentzia bat eman nahi zaio (haren elementuak, bide batez, absolutuak edo erlatiboak ere izan daitezke), baina besterik gabe, gelaxka-izen bat eman dezakezu, Excel dokumentu batean dagoen formatu berean.

Kasu honetan, lerroa honela izango da.

Lan-koadernoak ("Book2.xlsm"). Fitxak ("List2"). Barrutia ("C5")

Aukera hau erosoagoa dela dirudi, baina lehen bi aukeren abantaila da parentesi artean aldagaiak erabil ditzakezula eta jada absolutua ez den esteka bat ematea, baina erlatibo baten antzeko zerbait, emaitzen araberakoa izango dena. kalkuluak.

Horrela, makroak eraginkortasunez erabil daitezke programetan. Izan ere, hemen gelaxka edo barrutietako erreferentzia guztiak absolutuak izango dira, eta, beraz, haiekin ere konpondu daitezke. Egia da, ez da hain erosoa. Makroak erabiltzea erabilgarria izan daiteke algoritmoan pauso kopuru handia duten programa konplexuak idaztean. Oro har, erreferentzia absolutuak edo erlatiboak erabiltzeko modu estandarra askoz erosoagoa da. 

Ondorioak

Zelula erreferentzia bat zer den, nola funtzionatzen duen eta zertarako balio duen jakin dugu. Erreferentzia absolutu eta erlatiboen arteko aldea ulertu eta mota bat beste bat bihurtzeko zer egin behar den asmatu dugu (hitz soiletan, helbidea konpondu edo aingura kendu). Balio kopuru handi batekin berehala nola egin dezakezun asmatu dugu. Orain funtzio hau egoera egokietan erabiltzeko malgutasuna duzu.