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.
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ó.
Mindez működés közben
Kérdésem lenne a feladat kapcsán. A tömb első elemére tökéletesen működik. Nekem a tömb 1 sor 14 oszlop. Ebből az összes nem nulla helyére és értékére lenne szükségem. Hogyan oldható meg?
Vízszintesen elég bonyolult lenne.
Ha elforgatod és besorszámozod az adatokat, akkor irányított szűrővel meg lehet csinálni.
Köszönöm!
Igazából max 6 pár értékre számítok (ami nem 0). Vagyis az 1., 2., 3., 4., 5., 6. nem nulla értékre.Képletet írnék be 6 pár cellába.
Sikerült megoldanom. A hívatkozásnál volt a gondom CÍM().
Változtattam a tartományt az előző helye alapján.
1. nem nulla helye E69 ben, tartomány G69 ben
2. NEM NULLA
G73<- ="Ajtók!"&CÍM(6;2+E69;4;1)&":O6"
E73<- =SZORZATÖSSZEG(HOL.VAN(1;–(INDIREKT(G73)0);0))+E69
…
Köszönöm: Ispi