Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016
Nemrég írtam egy cikket a dinamikus tartománynév létrehozásáról. Ott az volt a lényeg, hogy egy változó méretű tartomány határait meg tudjuk állapítani, és a hozzá tartozó név mindig az aktuális területet jelentse.
Most azt az esetet mutatnám be, amikor mi szeretnénk megmondani, hogy kell olyan hivatkozást előállítani, aminek a kezdete fix, de a végét mi szeretnénk változtatni pl. egy cellába beírt értékkel, mint paraméterrel.
Lássuk a problémát
A példában göngyölített összeget szeretnénk számítani, de csak a megadott hónapig. Tehát a SZUM függvény mindig a C4-től indul, de a B3-ban megadott számú oszlopot adja össze.
De hogy lehet változó méretű tartományt definiálni?
Első próbálkozás – ELTOLÁS (OFFSET) függvény
Az ELTOLÁS függvénnyel egy tartományt tudunk kijelölni úgy, hogy megadunk egy kiindulási cellát, és ahhoz képest relatív módon határozzuk meg a tartományt.
=ELTOLÁS(bázis_cella;ennyi_sorral_arrébb;ennyi_oszloppal arrébb;tartomány_magassága;tartomány_szélessége)
Az ábra szerint tehát meghatároztunk egy olyan tartományt, ami az A1-ből indulva, 2 sort le, majd 2 oszlopot jobbra lépve, eljut a C3-ba, ahonnét kijelöl egy 4 sor magas és 3 oszlop széles tartományt.
A megoldás
Összegezni kell a C4-től indulva, és ott is maradva( nulla sorral és oszloppal arrébb) egy 1 sor magas és a B1-ben adott oszlopnyi széles (esetünkben éppen három) tartományt. B1 értékét változtatva, változik az összegzendő tartomány oszlopainak száma, így adhatjuk meg, hogy hány hónapot szeretnénk összegezni.
=SZUM(ELTOLÁS(C4;0;0;1;B1))
A nulla argumentumok helyére üres pontosvessző is kerülhetne, mint elhagyott argumentum, de Én így érthetőbbnek gondolom.
A probléma az ELTOLÁS függvénnyel
Csak egy baj van vele, mégpedig az, hogy ez a függvény un. VOLATILE tulajdonságú. Ez azt jelenti, hogy nem csak akkor számolódik újra, ha valamely argumentuma megváltozik, hanem minden, a munkafüzetet érintő változáskor újra kalkulál. Tehát ha a munkafüzet bármely celláját változtatjuk.
Másik próbálkozás – INDEX
A cikk elején hivatkozott cikkben is volt arról szó, hogy az INDEX függvény tulajdonképpen egy hivatkozást szolgáltat. Legtöbbször abban a formában látjuk, amikor egy tartomány valahányadik elemét lekérdezzük, például, amikor az FKERES helyett az INDEX/HOL.VAN kombót használjuk.
Kevéssé ismert, hogy lehet így is használni:
=SZUM(B1:INDEX(B:B;DARAB2(B:B)))
Ez azt jelenti, hogy az összegzendő tartomány kezdő cellája a B1, és a végcellája a B oszlop annyiadik cellája, ahány kitöltött elemet talál benne. Magyarul dinamikusan megméri a B oszlop méretét.
Ez persze csak akkor lesz jó, ha nincsenek kitöltetlen cellák.
Megoldás a mi problémánkra
=SZUM(C4:INDEX(C4:H4;B1))
Tehát a kezdő cella a C4, a végcella pedig a C4:H4 tartomány annyiadik cellája, ami a B3-ban meg van adva.
Letölthető munkafüzet
Valtoztathato-tartomanyhivatkozas
Lenne egy kérdésem: mi helyzet akkor, ha a bázis cella nem fix, azaz, ha kitöltés párhuzamosan szeretném mozgatni az összegzendő cellákat. Pl. mindig az utoljára kitöltött, tehát utolsó nem üres mezőtől számolja (visszafele) a 12 mező értékét, vagy erre teljesen más képlet alkalmazandó?
Valójában a DARABTELI függvényt szeretném használni úgy, hogy mindig azt 12 hónap adatát vegye figyelembe ahol van érték. (hónapról, hónapra változik a kitöltött mezők száma)
Ezt most így nem igazán értem. Sejtem ugyan, de nem értem 🙂
A fórumban tedd fel a kérdést, tölts fel minta fájlt, és statikusan beírt példát, hogy mit szeretnél látni eredményként.
Imre