Egy blog olvasó keresett meg egy rövid kérdéssel: sorszámozást szeretne a listája mellé. A trükk az, hogy akkor is 1-től kezdődő, egyesével haladó számokat szeretne, ha el vannak rejtve (szűrve) sorok. Másképp fogalmazva: csak a látható sorokat kell számozni.
Készítettem egy kis példát.
Tehát:
Szűrés esetén ilyen eredményt várunk:
Hogyan lehetne ezt elérni anélkül, hogy kézzel irkálnánk be a számokat? Csak egy alkalmas formulát kellene találni, aminek van olyan képessége, hogy a rejtett sorokat nem veszi figyelembe.
Nos, van ilyen, kettő is! A 2010-nél régebbi Excelben a RÉSZÖSSZEG (SUBTOTAL) formula ilyen, 2010 és újabb verziókban már az ÖSSZESÍT (AGGREGATE) is rendelkezésre áll. Ez utóbbit fogom bemutatni, de a feladat nagyon hasonlóan megoldható a RÉSZÖSSZEGgel is.
Pár szó az ÖSSZESÍT formuláról:
Praktikus, ha úgy gondolkodunk erről a formuláról, mint egy „burok-formula”. Ez a „burok” képes arra, hogy az általunk választott számítást bizonyos beállításokkal végezze el. Ezek a beállítások lehetnek többek között, hogy megkérjük a „burkot”, hogy a számítás során hagyja ki az elrejtett sorokat.
A buroknak meg kell mondani, hogy:
- milyen számítást végezzen – számkóddal lehet kiválasztani a függvényt, amit a „burokba” teszünk. (Tudom, furcsa, de így működik a „burok”.)
- mit hagyjon ki – ezt is egy szám kóddal tudod megadni
- milyen tartományon végezze a számítást – a szokásos tartomány kijelölés, amit egyébként az első pontban választott függvényben beállítanál
- ha a számításhoz további paraméter szükséges, akkor ezt itt kell megadni – erre most nem lesz szükség.
Amit itt felsoroltam, azok az ÖSSZESÍT argumentumai – ha elkezded beírni a formulát, látni fogod, hogy milyen lehetőségek vannak az egyes pontokon belül.
Mi a terv?
Meg kellene számolni, hogy az adott cella fölött hány sor van. Ehhez a DARAB2 függvényt alkalmaznánk, feltételezve, hogy a számolni kívánt tartományban nincs üres cella.
Tehát ezt tesszük a „burokba”:
- a DARAB2 függvényt fogjuk használni, aminek 3 a számkódja
- kihagyjuk a rejtett sorokat – ennek 5 a számkódja
- mindig az adott cella fölötti tartományban számoljuk meg a cellákat
Így néz ki a formula az első sorban:
=ÖSSZESÍT( 3 ; 5 ; $F$3:F3 )
=AGGREGATE( 3 ; 5 ; $F$3:F3 )
A tartomány hivatkozás első tagja abszolút hivatkozás („le van dollározva”). Ha a képletet lefele másoljuk, mindig az adott cella feletti tartományra fog hivatkozni. (Fontos, hogy tulajdonképpen DARAB2 formulát használunk, ami az üres cellákat nem számolja meg. Olyan oszlopra kell hivatkozni, ahol biztos, hogy nincs üres cella.)
Ennyi az egész – bármilyen szűrés esetén működik!
Nézzétek meg a példa fájlt, amiben megtaláljátok a felépített formulát is!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
Legutóbbi hozzászólások