Általános képlet
=SUM(firstcell:INDEX(data,rows,cols))
Összegzés
Ha egy másik cellában lévő érték alapján meghatároz egy tartományt, használhatja az INDEX függvényt. A bemutatott példában a J7 képlete a következő:
=SUM(C5:INDEX(data,J5,J6))
ahol az "adatok" a B5: G9 megnevezett tartomány.
Magyarázat
Ez a képlet az INDEX meghatározott viselkedésére támaszkodik - bár úgy tűnik, hogy az INDEX egy adott helyen adja vissza az értéket, valójában visszaadja a hely hivatkozását. A legtöbb képletben nem veszi észre a különbséget - az Excel egyszerűen kiértékeli a referenciát és visszaadja az értéket. Ez a képlet ezt a funkciót használja a dinamikus tartomány felépítésére a munkalap bevitele alapján.
Az összeg függvény belsejében az első referencia egyszerűen az első cella a tartományban, amely lefedi az összes lehetséges cellát:
=SUM(C5:
Az utolsó cellához az INDEX-et használjuk. Itt megadjuk az INDEX-nek a megnevezett "data" tartományt, amely a lehető legnagyobb értéktartomány, valamint a J5 (sorok) és a J6 (oszlopok) értékeit. Az INDEX nem ad vissza tartományt, csak egyetlen cellát ad vissza az adott helyen, az E9 a példában:
INDEX(data,J5,J6) // returns E9
Az eredeti képlet a következőre csökken:
=SUM(C5:E9)
amely 300-at ad vissza, a C5: E9 összes értékének összegét.
A J8 képlete majdnem ugyanaz, de az átlag kiszámításához a SUM helyett az ÁTLAG értéket használja. Amikor a felhasználó megváltoztatja az értékeket a J5 vagy J6 értékekben, a tartomány frissül, és új eredmények jelennek meg.
Alternatív megoldás az OFFSET-re
Hasonló képleteket készíthet az alább látható OFFSET függvénnyel:
=SUM(OFFSET(C5,0,0,J5,J6)) // sum =AVERAGE(OFFSET(C5,0,0,J5,J6)) // average
Az OFFSET-et egy tartomány visszaadására tervezték, így a képleteket talán egyszerűbb megérteni. Az OFFSET azonban volatilis funkció, és teljesítményproblémákat okozhat, ha nagyobb, összetettebb munkalapokon használják.
