Telefonszámunk: 1-472-0679

Egyedi értékek dinamikus kiválogatása dinamikus képlettel

2017-10-01 - horvimi - Megtekintések száma: 181 - Kategória: Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016 Office365

A fórumba érkezett egy kérdés, ami eszembe juttatta, hogy még 2014-ben írtam egy cikket az egyedi darabszám megállapításáról, és már akkor szerettem volna megírni a folytatást, azaz hogy hogyan megy az egyedi elemek kiválogatása. Úgy tűnik, most jött el a pillanat. Szóval a feladat:

egyedi-elemek-kivalogatása

Egyedi elemek kigyűjtése? Számtalan módja van

  • PIVOT
  • Irányított szűrő
  • Ismétlődések eltávolítása
  • MS QUERY
  • Makró

Ez igaz, de mindegyik statikus. Tehát ha megváltozik a lista, amiből gyűjteni kell, újra meg kell csinálni. Rendben, ha PIVOT vagy MS QUERY, akkor csak frissíteni kell a lekérdezést. Vagy ha a makrót eseményhez kötjük, akkor az is fél-automatizálható. De mi van, ha azt szeretnénk, hogy képlettel, azaz dinamikusan lehessen legyűjteni az egyedi elemeket?

Nos, csodák azért nincsenek, hiszen a most ismertetésre kerülő módszer is csak addig dinamikus, azaz teljesen automatikus, ha az input adatokban az egyedi elemek száma nem növekszik. Ha ez megtörténik, akkor a képletet lejjebb kell húzni.

Egyedi elemek kiválogatása tömbképlettel

A megoldást ezen az oldalon találtam. Itt nincs magyarázat, ezt már én tettem hozzá.

A fenti ábra szerint a képletet a D2-be írjuk.

=HAHIBA(INDEX($A$2:$A$12;HOL.VAN(0;DARABTELI($D$1:D1;$A$2:$A$12);0));"")

Input tartomány: A2:A12

DARABTELI

A tömböt ezzel a függvénnyel képezzük, ami a D oszlop első sorától az aktuális soráig (a képletet lehúzva bővül), hogy az Input tartomány elemei egyesével hányszor szerepelnek már. Ez kezdetben egy csupa nullából álló tömböt eredményez, mivel a D1 cellában nem fog szerepelni egyik input elem sem.

HOL.VAN

Megkeresi az első nullát az előbbi tömbben. Ahol megtalálja, az annyiadik elem még nincs kiválogatva. Elsőként ez rögtön az első lesz, úgyhogy a D2-be írt képletnél egy 1-est kapunk.

INDEX

Az INDEX pedig kiveszi az Input tartomány előbb kiszámolt sorszámú elemét, jelene eseteben az elsőt, ami Mór.

HAHIBA

Ez csak azért van benne, hogy ha lefelé húzva már minden egyedi elemet kigyűjtöttünk, (a DARABTELI tömbjében nem lesz már nulla), akkor ne hibaüzenet legyen, hanem üres szöveg.

CSE

Mivel ez egy tömbképlet, Ctrl-Shift-Enter-rel kell lezárni, majd ezután lehúzni addig, amíg üres cellát nem kapunk eredményül.

Mi van, ha bővülhet a bemeneti lista?

Csak akkor érdekes, ha új, egyedi elem is megjelenhet benne. Ebben az esetben javasolnám a forrástartományt dinamikus névvel ellátni, és a leválogatás oszlopában pedig lejjebb kell húzni a képletet. Addig húzzuk, amíg újra üres cellát nem kapunk

Egyedi elemek rendezett listája

No, ilyesmit csak a PIVOT táblával, SQL-lel vagy makróval lehetne csinálni, de az említett forrásban található egy zseniális megoldás, ami nem csak kigyűjti, de rendezi is az egyedi elemeket. A képlet, amit az E2-be írtam:

=HAHIBA(INDEX($A$2:$A$12; HOL.VAN(KICSI(HA(DARABTELI($E$1:E1; $A$2:$A$12)=0; DARABTELI($A$2:$A$12; "<"&$A$2:$A$12); ""); 1); DARABTELI($A$2:$A$12; "<"&$A$2:$A$12); 0));"")

Hát, ezt csak pedzegetem, de talán értem. Az elmagyarázása elég nagy falat, de a videóban megkísérlem.

FONTOS!

Mindkét megoldás csak akkor működik helyesen, ha a bemeneti lista NEM tartalmaz üres cellákat.
Üres aposztrof vagy az =”” esetén már jó lesz.

Letölthető munkafüzet a magyarázatokkal

egyedi_elemek_kigyujtese

És lássuk a videót

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