17. fejezet · 17.16
Az első 5 megjelenítése kategóriánként
A legjobban fogyó termékek részlegenként, a legnagyobb ügyfelek régiónként, a legforgalmasabb napok havonta: nem a teljes lista első 5 elemére van szüksége, hanem az első 5-re egyetlen kategórián belül. A NAGY függvény a teljes oszlopon dolgozik, és azonos összegeknél kétszer adja vissza ugyanazt a terméket. Az Euro-Office-ban ezt egyetlen képlettel megoldhatja.
Példaként a Bloemisterij Het Zonnetje termékenkénti éves árbevételét vesszük: az A oszlopban a termék, a B oszlopban a kategória (Rozen, Tulpen, Planten), a C oszlopban pedig az árbevétel szerepel, a 2. sortól a 18. sorig. Az F1 cellában az a kategória áll, amelyet látni szeretne: Rozen.
Az első 5 egyetlen képlettel
Írja be a H2 cellába:
=ÁTHELYEZ(SORBA.RENDEZ(SZŰRŐ(A2:C18;B2:B18=F1);3;-1);5)
Olvassa belülről kifelé:
- SZŰRŐ(A2:C18;B2:B18=F1) csak az F1 cellában megadott kategória sorait tartja meg, mindhárom oszloppal.
- SORBA.RENDEZ(...;3;-1) ezeket a sorokat a harmadik oszlop (az árbevétel) szerint rendezi, a legnagyobbtól a legkisebbig (-1).
- ÁTHELYEZ(...;5) a felső 5 sort tartja meg.
Nyomja meg az Enter billentyűt: az öt termék a kategóriával és az árbevétellel együtt megjelenik a H2, J6 tartományban. Ha az F1 cellába a Planten szót írja, a lista azonnal frissül.

Az azonos összegek nem okoznak gondot: a példában a Grand Prix és az Ecuadoriaanse roos árbevétele egyaránt 3400, és mindkettő szerepel a listában. Ha kétszer használná a NAGY függvényt az INDEX és a HOL.VAN függvénnyel, kétszer jelenne meg a Grand Prix.
Csak az összegek, kategóriánként egymás mellett
Ha csak az öt legnagyobb összegre van szüksége, például több kategóriához egymás mellett, használja a NAGY függvényt egy szűrt listán. Írja a kategóriákat az F1, G1 és további cellákba, jelölje ki az F2:G6 tartományt, és írja be:
=HAHIBA(NAGY(SZŰRŐ($C$2:$C$18;$B$2:$B$18=F$1);SOROK($1:1));"")
majd nyomja meg a Ctrl+Enter billentyűkombinációt. A SZŰRŐ csak az 1. sorban megadott kategória összegeit tartja meg. A SOROK($1:1) értéke az első sorban 1, az alatta lévő sorokban pedig 2, 3, 4 és 5: a legnagyobb, a második legnagyobb és így tovább. A HAHIBA üresen hagyja a cellát, ha egy kategóriában ötnél kevesebb termék van; a Tulpen kategóriában csak négy van.
Figyelem: az Excellel foglalkozó oldalakon gyakran ez a képlet szerepel: =NAGY(HA($B$2:$B$18=F$1;$C$2:$C$18);SOROK($1:1)). Ez az Euro-Office-ban egyszerű Enter billentyűvel hibás eredményt ad: a HA ilyenkor nem a teljes oszlopot vizsgálja, így az összes kategória legnagyobb összegeit vagy #SZÁM! hibát kap. Ha a Ctrl+Shift+Enter billentyűkombinációval zárja le, a képlet kapcsos zárójelet {…} kap, és az eredmény helyes lesz. A fenti, SZŰRŐ függvényes megoldás egyszerűbb.
Csak a legnagyobb összeg
Egyetlen számhoz egyszerűbb a megoldás: a =MAXHA(C2:C18;B2:B18;F1) a kategórián belüli legnagyobb árbevételt adja vissza, itt 6150-et.
Buktatók
- 5-nél kevesebb termék egy kategóriában: HAHIBA nélkül a NAGY #SZÁM! hibát ad a nem létező soroknál. Az ÁTHELYEZ függvényt ez nem zavarja, egyszerűen kevesebb sort jelenít meg.
- Az ÁTHELYEZ függvényes képlet üres marad: abban az 5 sorban és 3 oszlopban, ahová az eredménynek kerülnie kellene, már van valami. Lásd: Önmagukat frissítő listák a SZŰRŐ, EGYEDI és SORBA.RENDEZ függvénnyel.
- Az Excel 2019-et vagy régebbi verziót használó kollégák az ÁTHELYEZ, a SORBA.RENDEZ és a SZŰRŐ függvénynél #NÉV? hibát látnak.
