Kapitola 17 · 17.5
Zoznamy, ktoré sa aktualizujú samy, pomocou FILTER, UNIQUE a SORT
Pozrieť si krátke video
Zoznam zákazníkov bez duplicít, prehľad všetkých nezaplatených objednávok, zoznam produktov zoradený podľa obratu: kedysi ste takýto zoznam vytvárali kopírovaním, filtrovaním a zoraďovaním a robili ste to znova pri každej zmene. S funkciami FILTER, UNIQUE a SORT napíšete jeden vzorec, ktorý vytvorí celý zoznam a sám sa aktualizuje. Euro-Office pozná tieto takzvané dynamické polia: výsledok sa „rozleje“ do toľkých buniek, koľko je potrebné.
Ako príklad použijeme hárok Bestellingen pekárne Bakkerij De Korenaar: v stĺpci A je dátum, v B zákazník, v C produkt, v D suma a v E informácia, či je objednávka zaplatená (ja alebo nee).
Jedinečný, zoradený zoznam zákazníkov
Do bunky G2 zadajte =SORT(UNIQUE(FILTER(B2:B500;B2:B500<>""))) a stlačte Enter. Viac netreba: každého zákazníka dostanete raz, v abecednom poradí, pod sebou od bunky G2.
- FILTER(B2:B500;B2:B500<>"") vynechá prázdne riadky. Rozsah tak môžete zvoliť s rezervou pre objednávky, ktoré pribudnú neskôr.
- UNIQUE odstráni duplicity.
- SORT ich zoradí.

Ak pribudne objednávka nového zákazníka, napríklad Tandarts Bos v riadku 14, okamžite sa zobrazí na správnom mieste v zozname. Ak chcete zoznam upraviť, zmeňte vzorec v prvej bunke, G2.
Všetky nezaplatené objednávky tohto mesiaca
Funkcia FILTER dokáže načítať aj celé riadky, a to s viacerými podmienkami:
=FILTER(A2:D500;(E2:E500="nee")*(A2:A500>=EOMONTH(TODAY();-1)+1);"nič nezaplatené")
- Prvá časť je to, čo chcete vidieť: stĺpce A až D.
- Druhá časť sú podmienky. Každá podmienka je v zátvorkách. Znak * medzi nimi znamená a zároveň, znak + znamená alebo.
- EOMONTH(TODAY();-1)+1 je prvý deň tohto mesiaca.
- Tretia časť je text, ktorý sa zobrazí, ak nič nevyhovuje.

Upozornenie: výsledok nepreberá formát vášho zoznamu. Sumy zvyčajne dostanú formát Všeobecné: 64,50 € sa zobrazí ako 64,5. Niekedy celý výsledok dostane formát prvého stĺpca a sumy sa zobrazia ako dátum, ako na obrázku: 36,00 sa potom zmení na 05-02-1900. V oboch prípadoch vo výsledku vyberte stĺpec so sumami a sami zvoľte formát Mena alebo Číslo (pozrite Formátovanie čísel, dátumov a meny).
Zoraďovanie a výber stĺpcov
- Podľa sumy, od najvyššej: =SORT(FILTER(A2:D500;E2:E500="nee");4;-1). Číslo 4 je štvrtý stĺpec výsledku, -1 znamená zostupne.
- Zoradenie podľa stĺpca, ktorý nezobrazujete: =SORTBY(FILTER(B2:B500;E2:E500="nee");FILTER(D2:D500;E2:E500="nee");-1) vráti iba mená zákazníkov zoradené podľa sumy.
- Iba zákazník a suma: =CHOOSECOLS(FILTER(A2:D500;E2:E500="nee");2;4).
Tip: v ukážke sa poradie existujúceho vzorca =SORT(FILTER(...);4;-1) pomiešalo po tom, čo pod zoznam pribudla objednávka. Vzorec zadaný až po pridaní zoraďuje správne. Preto po pridaní riadkov skontrolujte poradie. Ak nesedí, zadajte vzorec znova: kliknite na bunku so vzorcom, stlačte F2 a potom Enter.
Niečo prekáža
Ak už je niečo v bunke, kam sa má výsledok rozliať, zoznam sa nemôže rozliať. V Exceli sa vtedy zobrazí chybová hodnota #SPILL!. Euro-Office v takom prípade nechá bunku so vzorcom prázdnu; keď na ňu kliknete, uvidíte bodkovanú čiaru okolo oblasti, kam sa má výsledok rozliať. Vyprázdnite bunky v tejto oblasti a zoznam sa okamžite zobrazí. Pod takýmto vzorcom a vedľa neho preto nechávajte voľné miesto.

Výpočty s celými stĺpcami v jednom vzorci
Niektoré vzorce počítajú s celým stĺpcom naraz, napríklad =SUM(D2:D13*(E2:E13="nee")): súčet súm všetkých objednávok, ktoré ešte nie sú zaplatené, tu 258,50. V Euro-Office takýto vzorec funguje s obyčajným stlačením Enter. Vzorce s funkciou IF nad celým stĺpcom vnútri inej funkcie, napríklad =LARGE(IF(B2:B37="Taart";C2:C37);1), Euro-Office po obyčajnom stlačení Enter nevypočíta správne: dostanete #NUM! alebo nesprávne číslo. Takýto vzorec ukončite stlačením Ctrl+Shift+Enter. Vzorec potom dostane zložené zátvorky {…} a vráti správny výsledok. Často sa to dá aj bez IF, pomocou FILTER: =LARGE(FILTER(C2:C37;B2:B37="Taart");1).
Čo nie je možné
- LET a LAMBDA, ani funkcie, ktoré na nich stavajú, ako BYROW, MAP a GROUPBY, Euro-Office nepozná. Súbor Excelu s týmito funkciami zobrazí #NAME?. Použite pomocný stĺpec alebo si v článku Vzorce Excelu s LET alebo LAMBDA: čo v Euro-Office použiť namiesto nich pozrite, čo robiť namiesto toho.
- Kolega s Excelom 2019 alebo starším uvidí pri týchto funkciách #NAME?.
Úskalia
- Celé stĺpce ako B:B spomaľujú vzorec. Zvoľte dostatočne veľký, pevný rozsah, napríklad B2:B500.
- Žiadne TRUE ani FALSE bez zátvoriek v týchto vzorcoch; použite 1 a 0 alebo TRUE a FALSE (pozrite Preberanie vzorcov z Excelu: jazyk funkcií a bodkočiarka).
