Tvorba zoznamu s nenulovými hodnotami (+VIDEO)

EXCEL – {=INDEX(A:A;SMALL(IF($B$2:$B$20<>0;ROW($A$2:$A$20);999);ROWS(A$2:A2));1)} Áno, aj takto môže vyzerať zápis funkcie. Týmto článkom však nechcem nikoho vystrašiť 🙂 Postup, ktorý tu uvidíte, v živote využije tak 1 zo 100 používateľov Excelu. Cieľom však nie je ukázať vám iba, ako použiť presne tieto funkcie, ale aj to, ako pochopiť zápis funkcií, ktoré vyzerajú podobne.

Tento týždeň sme s Ivanom (verný fanúšik fintičiek na FB stránke Excelujme s Excelom) riešili problém so zobrazením iba nenulových riadkov. Možností, ako to dosiahnuť, je viacero, ale požiadavkou bolo dynamické (okamžité) zobrazenie bez potreby nejakého obnovovania kontingenčných tabuliek alebo filtrov.

Aby ste mali predstavu, o čo sa jedná, pozrite si obrázok.

Vľavo je zoznam, kde sa dopisujú hodnoty, a stĺpce D a E sú požadovaným výsledkom, ktorý predstavuje iba riadky s nenulovým počtom.

Keď sa v stĺpci B zmení hodnota, tak sa D a E prepočítajú.

Práve funkcia uvedená v úvode rieši tento problém. Priznám sa, že sama by som to asi ťažko dala dohromady, ale mojím heslom je, že ak mám nejaký problém ja, tak ho už určite mal niekto predo mnou 🙂 . Z toho vyplýva, že sa treba spýtať uja googla a riešenie, ktoré nájdete, si upraviť pre svoje potreby.

Aj tento postup som našla na internete na stránke https://stackoverflow.com (po kliknutí sa otvorí originálna diskusia, kde sa rieši uvedený problém).

Niekomu stačí, že to funguje, a niekto (ako Ivan alebo aj ja) potrebuje vedieť, ako to funguje.

Vo videu nižšie uvidíte postup, ako pochopiť a rozkúskovať takúto alebo obdobnú funkciu.

Tu si môžete stiahnuť ukážkový súbor, s ktorým pracujem aj vo videu. Nájdete tam aj bonusové riešenia a ďalšie príklady.

Zo spomínanej stránky som skopírovala zápis funkcie =INDEX(A:A;SMALL(IF($B$2:$B$20<>0;ROW($A$2:$A$20);999);ROWS(A$2:A2));1) a vložila do bunky D2, potvrdiť Ctrl+Shift+Enter a skopírovať doprava a dolu.

Základom pre pochopenie celého princípu je poznať, čo použité funkcie robia. To urobte vždy ako prvé – zistite, k čomu funkcie slúžia.

Konkrétne v tomto postupe (v tom, čo si sami nájdete, môžu byť aj iné):

• INDEX – výsledkom je hodnota podľa zadaných súradníc (súradnice sú riadok a stĺpec)

• SMALL – najnižšia hodnota v poradí (tretia najnižšia, piata najnižšia a pod.)

• IF – podmienková funkcia, ktorej výsledok je podľa toho, či je zadaná podmienka splnená alebo nesplnená

• ROW – číslo riadku zadanej bunky

• ROWS – počet riadkov v zadanom rozsahu

Dôležité je, že ak v postupe uvidíte zložené zátvorky {} alebo informáciu, že vzorec nemáte potvrdiť Enterom, ale Ctrl+Shift+Enter, tak sa jedná o vektorový vzorec.

Vektorové vzorce sú inak nazývané aj maticové vzorce, vzorce poľa, polové vzorce, v anglických textoch array formulas.

Veľmi zjednodušene povedané: bežná funkcia alebo vzorec má jeden výsledok. Vektorový vzorec (funkcia) má viac výsledkov, alebo pracuje s vnorenou funkciu, ktorá má viac výsledkov v tvare vektora (jeden riadok alebo jeden stĺpec) alebo v tvare matice (viac riadkov a viac stĺpcov).

Vektory a matice zo strednej školy máme zopakované, vieme, o čom sú použité funkcie, tak poďme ďalej a pustíme sa do vysvetľovania, ako to celé funguje 🙂

Pozrite si video (čas 14:43).

Poznámka: Ak nejde video prehrať, môžete si ho pozrieť priamo na YouTube >>

Verím, že vám postup pomohol, či už potrebujete riešiť rovnaký problém, alebo iný, kde je tiež povnáraných veľa funkcií.

Každý má na niečo nejakú svoju vychytávku a toto je môj postup, ktorým zvyknem komplikované zápisy „rozkúskovať“. Netvrdím, že je to jediná možnosť. Možno používate niečo iné. Tiež sa rada inšpirujem, tak budem rada, ak mi napíšete na zuzana@zuzanapogranova.sk, čo používate vy, prípadne či vám postup pomohol (alebo niečo z toho).

Na záver dodám už len jednu dôležitú informáciu. Osobne najradšej hľadám na anglických stránkach, ale našťastie v poslednej dobe vzniklo aj veľa slovenských venovaných Excelu 🙂

Ak by ste tiež hľadali v angličtine, tak sa často stretnete s tým, že na oddelenie argumentov nie je použitá bodkočiarka, ale čiarka. Niekedy to prepisujem ručne, ale v poslednej dobe som si zvykla vložiť to do poznámkového bloku (Notepad) >> Ctrl+H >> nahradiť čiarku za bodkočiarku.

Ďakujem vám za prečítanie tohto článku. Verím, že video je pre vás užitočné.