Telefonszámunk: 1-472-0679

Az első nem-nulla érték keresése

2016-12-27 - horvimi - Megtekintések száma: 549 - Kategória: Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016

Tegyük fel, az a feladatunk, hogy egy oszloptartományban meg szeretnénk tudni, melyik az első olyan érték, ami nullától eltérő értékű.  Kereshetjük az első ilyen elem sorszámát vagy értékét, esetleg mindkettőt.

Legyen feltétel, hogy az oszlopban számok vagy üres cellák vannak.

elsonem nulla

Szóval nem egy konkrét értéket keresünk, hanem egy feltételnek megfelelő értéket. A feltétel pedig egy egyenlőtlenség. (<>0)

Az első nem nulla sorszáma

A megoldás egy tömbképlet, ami a tartomány (jelen esetben a B2:B10) minden elemét összehasonlítja nullával, és az eredményül kapott 1/0 sorozatban megkeresi az első 1-est.

=HOL.VAN(1;--(B2:B10<>0);0)

=MATCH(1;--(B2:B10<>0);0)

Hogy működik?

A B2:B10<>0 képez egy TRUE/FALSE sorozatot vagy tömböt. Ezeket a dupla előjel váltás 1/0 sorozattá konvertálja, és a HOL.VAN ebben keresi az első 1-es sorszámát.

Mivel tömbképlet, ezért a Ctrl-Shit-ENTER-rel kell lezárni (CSE)

CSE kivédése

A Ctrl-Shift-Enter-el lezárandó képleteket azért nem szeretjük annyira, mert ha egy felhasználó belekattint a képletbe, akkor eltűnnek a tömbképletet jelző kapcsos zárójelek. Mivel nem tudja mivel áll szemben, szinte biztosan nem CSE-vel fogja visszazárni, ami viszont hibát eredményez.

Jelen esetben a fenti képlet egy egy elemű tömböt ad. Ha ezt átadnánk egy olyan függvénynek, ami CSE nélkül kezeli a tömböket, és egy 1 elemű tömböt összegez, akkor megoldottuk a problémát. Ez a függvény a SZORZATÖSSZEG / SUMPRODUCT

Tehát a CSE nélküli megoldás:

=SZORZATÖSSZEG(HOL.VAN(1;--(B2:B10<>0);0))

=SUMPRODUCT(MATCH(1;--(B2:B10<>0);0))

Az első nem nulla értéke

Ha az előző módszerrel megtaláltuk az indexét, akkor már csak egy kicsike lépés, hogy ugyanabban a tartományban megkeressük az adott sorszámú elem értékét:

=INDEX(B2:B10;SZORZATÖSSZEG(HOL.VAN(1;--(B2:B10<>0);0)))

Ez a képlet szintén CSE nélkül használható.

Letölthető munkafüzet: Elso_nem_nulla

Mindez működés közben

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