5 pokročilých vzorcov v Exceli, ktoré nahradia pomocné stĺpce

Existuje bod, v ktorom sa práca v Exceli začne zasekávať. Nie preto, že by ste niečo nevedeli spočítať, ale preto, že na to potrebujete tri pomocné stĺpce, dva medzivýpočty a vzorec, ktorému o pol roka nerozumiete ani vy sami. Nasledujúcich päť funkcií presne tento bod posúva – každá z nich nahrádza celý postup jedným vzorcom.

1. AGGREGATE – súčet, ktorý sa nezasekne na chybe

Klasická situácia: v stĺpci máte tristo hodnôt, z toho tri obsahujú #N/A, pretože niečo sa nenašlo. Obyčajná funkcia SUM vráti chybu a celý report je zablokovaný, kým chyby ručne nevyriešite. AGGREGATE je verzia, ktorá vie chyby jednoducho ignorovať.

Zápis má tri povinné časti: číslo funkcie, číslo možnosti a oblasť.

=AGGREGATE(9;6;C2:C300)

Deviatka znamená SUM, šestka znamená „ignoruj chybové hodnoty". Výsledkom je súčet všetkých korektných čísel bez ohľadu na chyby medzi nimi. Číslo funkcie vyberáte zo zoznamu 1 – 19 (1 = AVERAGE, 4 = MAX, 5 = MIN, 9 = SUM, 12 = MEDIAN, 14 = LARGE, 15 = SMALL) a možnosť z rozsahu 0 – 7, pričom najpoužívanejšie sú 6 (ignoruj chyby) a 7 (ignoruj chyby aj skryté riadky).

Práve možnosť 7 robí z AGGREGATE nástroj na prácu s filtrovanými dátami: keď si tabuľku vyfiltrujete, výsledok sa prepočíta len z viditeľných riadkov. To síce vie aj staršia funkcia SUBTOTAL, ale AGGREGATE má oproti nej dve výhody – vie ignorovať chyby a ponúka funkcie, ktoré SUBTOTAL vôbec nemá.

Najlepšie to vidno na tejto úlohe: koľko je tretia najvyššia hodnota medzi viditeľnými riadkami?

=AGGREGATE(14;7;C2:C300;3)

Štrnástka je LARGE, sedmička ignoruje chyby aj skryté riadky a posledný argument hovorí, že chceme tretiu najvyššiu hodnotu. Bez AGGREGATE by ste na to potrebovali kombináciu viacerých vzorcov.

Dostupnosť: Excel 2010 a novší, čiže aj staršie firemné inštalácie.

2. SUMPRODUCT – viac podmienok bez pomocných stĺpcov

SUMPRODUCT väčšina ľudí pozná ako „vynásob dva stĺpce a spočítaj to". To je pravda, ale je to len zlomok toho, čo funkcia vie. Jej skutočná sila je v tom, že dokáže vyhodnocovať podmienky ako celé oblasti, bez pomocných stĺpcov a bez špeciálneho potvrdzovania.

Prvý typický prípad je vážený priemer. Bežný priemer predajných cien je zavádzajúci, ak ste jedného tovaru predali desať kusov a druhého tisíc. Správny výpočet váži cenu množstvom:

=SUMPRODUCT(ceny;mnozstva)/SUM(mnozstva)

Presne takto sa počíta vážená marža, vážená priemerná cena či priemerná doba splatnosti – čísla, ktoré firmy potrebujú a ktoré sa v praxi často počítajú nesprávne.

Druhý prípad je počítanie a sčítavanie podľa viacerých podmienok. Zápis vyzerá nezvyčajne, ale logika je jednoduchá: každá podmienka v zátvorke vráti sériu hodnôt PRAVDA/NEPRAVDA, ich vynásobením vzniknú jednotky a nuly a SUMPRODUCT ich spočíta.

=SUMPRODUCT((krajina="SK")*(suma>1000))

Tento vzorec spočíta, koľko záznamov spĺňa obe podmienky naraz. Ak namiesto počtu chcete súčet, pridajte oblasť s hodnotami:

=SUMPRODUCT((krajina="SK")*(suma>1000)*suma)

Namieta sa: veď na to je SUMIFS a COUNTIFS. Áno – kým sú podmienky jednoduché. SUMPRODUCT však zvláda aj to, čo funkcie s koncovkou IFS nedokážu: použiť vo vnútri podmienky ďalšiu funkciu. Chcete sčítať sumy len za rok 2026?

=SUMPRODUCT((YEAR(datumy)=2026)*sumy)

SUMIFS takto pracovať nevie, pretože kritérium musí byť porovnanie, nie výpočet. Rovnako viete porovnať dva stĺpce navzájom – napríklad spočítať riadky, kde je skutočnosť nižšia ako plán.

Dostupnosť: všetky verzie Excelu. Ide o najstaršiu funkciu v tomto článku a stále o jednu z najuniverzálnejších. Jediná podmienka: všetky oblasti musia mať rovnakú veľkosť, inak vzorec vráti chybu.

3. XLOOKUP – tri veci, ktoré o ňom väčšina ľudí nevie

Že XLOOKUP nahrádza VLOOKUP a hľadá aj doľava, vie dnes takmer každý. Zaujímavejšie sú tri jeho možnosti, ktoré zostávajú nevyužité, hoci riešia bežné problémy.

Pripomeňme si najprv úplný zápis: =XLOOKUP(čo_hľadám; kde_hľadám; čo_vrátim; [ak_sa_nenájde]; [režim_zhody]; [režim_hľadania])

Po prvé – hľadanie od konca. Posledný argument s hodnotou -1 obráti smer hľadania, takže funkcia vráti poslednú zhodu namiesto prvej:

=XLOOKUP(produkt;produkty;ceny;;;-1)

Ak máte cenník, do ktorého sa nové ceny pridávajú na koniec, tento vzorec vždy vráti tú najaktuálnejšiu. To isté platí pre poslednú platbu zákazníka či posledný stav skladu. Bez tohto argumentu by ste dostali najstarší záznam – chyba, ktorá sa v reportoch objavuje prekvapivo často.

Po druhé – vlastný text namiesto chyby. Štvrtý argument definuje, čo sa má zobraziť, keď sa hodnota nenájde:

=XLOOKUP(kod;kody;nazvy;"Nenájdené")

Odpadá tým obaľovanie celého vzorca funkciou IFERROR, ktoré vzorec zdvojnásobí a sťaží čítanie.

Po tretie – vrátenie celého riadka naraz. Tretí argument nemusí byť jeden stĺpec. Ak označíte viac stĺpcov, XLOOKUP vráti celý blok údajov naraz:

=XLOOKUP(cislo_faktury;cisla;B2:F500)

Jedným vzorcom tak vyplníte celý riadok karty faktúry – dátum, zákazníka, sumu, splatnosť – namiesto piatich samostatných vzorcov, ktoré treba samostatne udržiavať.

Dostupnosť: Microsoft 365 a Excel 2021. V starších verziách ho nahradí kombinácia INDEX a MATCH.

4. LET – vzorec, ktorému rozumiete aj o rok

LET nerobí nič nové vo výpočte. Robí niečo dôležitejšie: umožňuje pomenovať medzivýsledky priamo vo vzorci. Vďaka tomu sa z jedného dlhého reťazca stane čitateľný postup.

Predstavte si vzorec, ktorý počíta maržu a zároveň ošetruje delenie nulou. Bez LET musíte ten istý výpočet napísať dvakrát – raz v podmienke, raz vo výsledku. S LET ho napíšete raz a pomenujete:

=LET(trzba;SUM(trzby); naklady;SUM(naklady); marza;(trzba-naklady)/trzba; IF(trzba=0;"–";marza))

Logika zápisu je jednoduchá: striedajú sa dvojice názov; hodnota a na konci je jeden posledný argument – samotný výpočet, ktorý tie názvy použije.

Okrem čitateľnosti prináša LET aj rýchlosť. Každý pomenovaný medzivýsledok Excel vypočíta iba raz, aj keď ho vo vzorci použijete päťkrát. Pri zložitejších výpočtoch nad väčšími tabuľkami je rozdiel v rýchlosti prepočtu citeľný.

V praxi je LET najužitočnejší tam, kde sa vo vzorci opakuje tá istá dlhá časť – typicky vyhľadávanie alebo filtrovanie, ktorého výsledok najprv testujete a potom aj zobrazujete.

Dostupnosť: Microsoft 365 a Excel 2021.

5. LAMBDA – vlastná funkcia, ktorú si pomenujete

Ak je LET krok k prehľadnosti, LAMBDA je krok úplne inam: umožňuje vám vytvoriť si vlastnú funkciu Excelu a používať ju rovnako ako SUM alebo XLOOKUP.

Postup má dva kroky. Najprv si napíšete logiku ako LAMBDA so vstupnými parametrami, napríklad výpočet marže v percentách:

=LAMBDA(predaj;nakup;(predaj-nakup)/predaj)

Potom stlačíte Ctrl+F3 (Správca názvov), dáte tomuto vzorcu názov – povedzme MARZA – a od tej chvíle ho v celom zošite voláte ako bežnú funkciu:

=MARZA(C2;D2)

Prečo je to zásadné? Pretože zložité výpočty v bežnom zošite existujú v desiatkach kópií rozkopírovaných po hárkoch. Keď sa logika zmení, musíte nájsť a opraviť každú z nich – a jednu vždy prehliadnete. Funkcia vytvorená cez LAMBDA existuje na jednom mieste. Opravíte ju raz a prepočíta sa všade.

Druhý, nenápadnejší prínos je zrozumiteľnosť. Vzorec =MARZA(C2;D2) pochopí každý kolega. Ten istý výpočet napísaný priamo v bunke a doplnený o ošetrenie nuly a zaokrúhlenie pochopí málokto.

Praktický tip na začiatok: kým funkciu pomenujete, otestujte ju priamo v bunke tak, že za definíciu pridáte argumenty v zátvorke – =LAMBDA(predaj;nakup;(predaj-nakup)/predaj)(100;60) vráti 0,4. Ak sedí výsledok, môžete ju uložiť pod názvom.

Dostupnosť: iba Microsoft 365. Ide o najnovšiu funkciu v tomto zozname a zároveň o bod, v ktorom sa Excel prestáva správať ako tabuľkový procesor a začína ako malé programovacie prostredie.

Čo majú spoločné

Všetkých päť funkcií rieši ten istý problém z rôznych strán: ako dostať zložitosť zo zošita do vzorca. Pomocné stĺpce, medzivýpočty a rozkopírované vzorce nie sú len neestetické – sú to miesta, kde vznikajú chyby a kde sa súbor stáva nezrozumiteľným pre kohokoľvek okrem autora.

Ak si z článku máte odniesť jednu vec, nech je to AGGREGATE a SUMPRODUCT: fungujú v každej verzii Excelu a vyriešia väčšinu situácií, pre ktoré si ľudia zakladajú pomocné stĺpce. A ak máte Microsoft 365, skúste LET – prehľadnosť vlastných vzorcov oceníte pri prvej oprave o pol roka neskôr.