Telefonszámunk: 1-472-0679

Változtatható méretű tartomány hivatkozás

2017-04-14 - horvimi - Kategória: Függvények
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

valtozo-meretu-gongyolitett-osszeg-alap-problema

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)

eltolas-fuggveny

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.

Az ELTOLÁS függvényt önmagában nem használjuk, az általa meghatározott tartománnyal egy másik függvény végezhet műveletet (Összegzés, Megszámlálás, Átlag, stb.)

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.

megoldas_eltolas

=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.

A VOLATILE függvények használatát célszerű kerülni, főleg ha sok cellát érint. Nagyon jelentősen lassíthatja a munkafüzet működését az örökös újrakalkuláció.

Íme a lista: MA, MOST, VÉL, VÉLETLEN.KÖZÖTT, INDIREKT, ELTOLÁS, CELLA, INFÓ

 

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

megoldas-index

=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

Lássuk mindezt videón egy kis fűszerrel

2 hozzászólás
  1. 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)

  2. 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

Vélemény, hozzászólás?