Edukiak
Taulak dituzu Excel-en datuak alda daitezkeen, hau da, errenkada (zutabe) kopurua handitu edo txikiagotu daiteke lanean? Taularen tamainak "flotatzen" badira, une hau etengabe kontrolatu eta zuzendu beharko duzu:
- gure taulari erreferentzia egiten dioten txosten-formuletan estekak
- Gure taularen arabera eraikitzen diren taula dinamikoen hasierako barrutiak
- Gure taularen arabera eraikitako diagramen hasierako sortak
- Gure taula datu-iturri gisa erabiltzen duten goitibeherako barrutiak
Guzti honek ez zaitu aspertzen utziko 😉
Askoz erosoagoa eta zuzenagoa izango da "goma" barruti dinamiko bat sortzea, eta tamaina automatikoki egokituko da datuen errenkada eta zutabe kopurura. Hau gauzatzeko, hainbat modu daude.
Metodoa 1. Taula adimenduna
Nabarmendu zure gelaxka sorta eta hautatu fitxan Hasiera - Formateatu taula gisa (Home - Formateatu taula gisa):
Bigarren mailako efektu gisa mahaian gehitzen den marradun diseinua behar ez baduzu, agertzen den fitxan desaktibatu dezakezu Eraikitzailea (Diseinua). Horrela sortutako taula bakoitzak fitxako leku berean erosoago batekin ordezkatu daitekeen izen bat jasotzen du Eraikitzailea (Diseinua) zelaian Taularen izena (Taularen izena).
Orain gure "taula adimendunerako" esteka dinamikoak erabil ditzakegu:
- Table 1 - Esteka taula osoari goiburuko errenkada izan ezik (A2: D5)
- 1. taula[#Guztiak] – esteka taula osora (A1:D5)
- Taula 1[Pedro] – lehenengo gelaxka-goibururik gabeko barruti-zutabe bati erreferentzia (C2:C5)
- Taula1[#Headers] - esteka "goiburuarekin" zutabeen izenekin (A1:D1)
Erreferentzia hauek oso ondo funtzionatzen dute formuletan, adibidez:
= SUM (Taula 1[Mosku]) - "Mosku" zutabearen batura kalkulatzea
or
=VPR(F5;Table 1;3;0) - bilatu F5 gelaxkako hilabeteko taulan eta eman San Petersburgoko batura (zer da VLOOKUP?)
Estekak ondo erabil daitezke taula dinamikoak sortzean fitxan hautatuta Txertatu - Taula Pibota (Txertatu - Taula Pibota) eta datu-iturburu gisa taula adimendunaren izena sartuz:
Taula horren zati bat hautatzen baduzu (adibidez, lehenengo bi zutabeak) eta edozein motatako diagrama bat sortzen baduzu, lerro berriak gehitzean, automatikoki gehituko dira diagraman.
Goitibeherako zerrendak sortzean, ezin dira erabili taulako elementu adimendunetarako estekak zuzenak, baina muga hori erraz gainditu dezakezu trikimailu taktiko bat erabiliz - erabili funtzioa ZEHARKOAK (ZEHARKA), testua esteka bihurtzen duena:
Horiek. testu-kate moduan taula adimendun baterako esteka (komatxoen artean!) esteka osoa bihurtzen da, eta goitibeherako zerrendak normalean hautematen du.
2. metodoa: barruti izendatu dinamikoa
Zure datuak taula adimendun bihurtzea arrazoiren batengatik ez bada desiragarria, orduan metodo apur bat korapilatsuagoa, baina askoz sotilagoa eta aldakorragoa erabil dezakezu: sortu gure taulari erreferentzia egiten dion Excel-en izen-barruti dinamiko bat. Ondoren, taula adimendun baten kasuan bezala, sortutako barrutiaren izena askatasunez erabil dezakezu edozein formula, txosten, diagrama eta abarretan. Has gaitezen adibide sinple batekin:
Task: hiri-zerrenda bati erreferentzia egingo dion barruti izendun dinamiko bat egin eta hiri berriak gehitzean edo ezabatzean automatikoki tamainaz luzatu eta txikiagotu.
Edozein bertsiotan eskuragarri dauden Excel integratutako bi funtzio beharko ditugu - POICPOZ (PARTIDA) barrutiaren azken gelaxka zehazteko, eta INDEX (AURKIBIDEA) lotura dinamiko bat sortzeko.
MATCH erabiliz azken gelaxka aurkitzea
MATCH(bilaketa_balioa, barrutia, bat-etortze_mota) – barruti batean (errenkada edo zutabea) balio jakin bat bilatzen duen eta aurkitu zen gelaxkaren zenbaki ordinala itzultzen duen funtzioa. Adibidez, MATCH ("Martxoa";A1:A5;0) formulak 4 zenbakia itzuliko du ondorioz, "Martxoa" hitza A1:A5 zutabeko laugarren gelaxkan baitago. Azken funtzioaren argumentuak Match_Type = 0 esan nahi du bat-etortze zehatz bat bilatzen ari garela. Argumentu hau zehazten ez bada, funtzioa balio txikienaren bilaketa modura aldatuko da - hau da, hain zuzen, gure array-ko azken gelaxka okupatuta aurkitzeko erabil daitekeena.
Trikimailuaren funtsa sinplea da. MATCH-ek goitik beherako barrutian dauden gelaxkak bilatzen ditu eta, teorian, emandakotik hurbilen dagoen baliorik txikiena aurkitzen duenean gelditu beharko litzateke. Taulan eskuragarri dagoen edozein balio baino handiagoa den balio bat zehazten baduzu nahi den balio gisa, MATCH taularen amaierara iritsiko da, ez du ezer aurkituko eta betetako azken gelaxkaren sekuentzia-zenbakia emango du. Eta behar dugu!
Gure matrizean zenbakiak bakarrik badaude, orduan zenbaki bat zehaztu dezakegu nahi den balio gisa, eta hori, jakina, taulako edozein baino handiagoa da:
Berme bat lortzeko, 9E + 307 zenbakia erabil dezakezu (9 aldiz 10 307ren potentziarekin, hau da, 9 307 zeroekin) - Excel-ek printzipioz lan egin dezakeen gehienezko kopurua.
Gure zutabean testu-balioak badaude, orduan ahalik eta zenbaki handienaren baliokide gisa, REPEAT ("i", 255) eraikuntza txerta dezakezu - "i" 255 hizkiz osatutako testu-katea - azken letra. alfabetoa. Bilatzerakoan Excel-ek karaktere kodeak konparatzen dituenez, gure taulako edozein testu teknikoki "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" lerro luze bat baino "txikiagoa" izango da:
Sortu esteka bat INDEX erabiliz
Taulan hutsik ez dagoen azken elementuaren posizioa ezagutzen dugunean, gure sorta osorako esteka bat osatzea geratzen da. Horretarako funtzioa erabiltzen dugu:
INDEX(barrutia; errenkada_zenbakia; zutabe_zenbakia)
Barrutiaren edukia errenkada eta zutabe zenbakiaren arabera ematen du, hau da, adibidez, =INDEX(A1:D5;3;4) funtzioak aurreko metodoko hiri eta hilabeteekin gure taulan 1240 emango du - edukia. 3. ilaratik eta 4. zutabetik, hau da, D3 gelaxketatik. Zutabe bakarra badago, bere zenbakia kendu daiteke, hau da, INDEX(A2:A6;3) formulak "Samara" emango du azken pantaila-argazkian.
Eta bada guztiz agerikoa ez den ñabardura bat: AURKIBIDEa = zeinuaren ondoren gelaxkan sartzen ez bada, ohi bezala, baina bi puntuen ondoren barrutiaren erreferentziaren azken zati gisa erabiltzen bada, orduan ez du ematen. zelularen edukia, baina bere helbidea! Horrela, $A$2:INDEX($A$2:$A$100;3) bezalako formula batek A2:A4 barrutiari erreferentzia emango dio irteeran.
Eta hemen sartzen da MATCH funtzioa, INDEX barruan txertatzen duguna zerrendaren amaiera dinamikoki zehazteko:
=$A$2:INDEX ($A$2:$A$100; MATCH(REP(“NI”;255);A2:A100))
Sortu izendun barruti bat
Hori guztia osotasun bakar batean biltzea geratzen da. Ireki fitxa bat formula (Formulak) Eta egin klik Izen kudeatzailea (Izen-kudeatzailea). Irekitzen den leihoan, egin klik botoian Sortu (Berria), sartu gure barrutiaren izena eta formula eremuan Range (Erreferentzia):
Klik egiteko geratzen da OK eta prest sorta edozein formulatan, goitibeherako zerrendetan edo diagrametan erabil daiteke.
- VLOOKUP funtzioa erabiltzea taulak eta bilaketa-balioak lotzeko
- Nola sortu automatikoki betetzeko goitibeherako zerrenda
- Nola sortu taula dinamikoa datu kopuru handia aztertzeko