Kapitola 17 · 17.5
Seznamy, které se samy aktualizují, s funkcemi FILTER, UNIQUE a SORT
Seznam zákazníků bez duplicit, přehled všech nezaplacených objednávek, seznam produktů seřazený podle tržeb: dříve jste takový seznam vytvářeli kopírováním, filtrováním a řazením a při každé změně jste to dělali znovu. S funkcemi FILTER, UNIQUE a SORT napíšete jediný vzorec, který vytvoří celý seznam a sám se aktualizuje. Euro-Office tyto takzvané dynamické matice podporuje: výsledek se „rozlije“ do tolika buněk, kolik je potřeba.
Jako příklad použijeme list Bestellingen pekárny Bakkerij De Korenaar: ve sloupci A je datum, v B zákazník, v C produkt, v D částka a v E údaj, zda je objednávka zaplacena (ja nebo nee).
Seznam jedinečných zákazníků seřazený podle abecedy
Do buňky G2 napište =SORT(UNIQUE(FILTER(B2:B500;B2:B500<>""))) a stiskněte Enter. Nic víc není potřeba: každý zákazník se zobrazí jednou, v abecedním pořadí, pod sebou od buňky G2.
- FILTER(B2:B500;B2:B500<>"") vynechá prázdné řádky. Rozsah tak můžete zvolit dostatečně velký i pro objednávky, které přibudou později.
- UNIQUE odstraní duplicity.
- SORT je seřadí.

Přibude-li objednávka nového zákazníka, například Tandarts Bos v řádku 14, objeví se okamžitě na správném místě v seznamu. Chcete-li seznam upravit, změňte vzorec v první buňce, G2.
Všechny nezaplacené objednávky tohoto měsíce
Funkce FILTER umí načíst i celé řádky, a to s více podmínkami:
=FILTER(A2:D500;(E2:E500="nee")*(A2:A500>=EOMONTH(DNES();-1)+1);"nic nezaplaceno")
- První část určuje, co chcete vidět: sloupce A až D.
- Druhá část obsahuje podmínky. Každá podmínka je v závorkách. Znak * mezi nimi znamená a, znak + znamená nebo.
- EOMONTH(DNES();-1)+1 je první den tohoto měsíce.
- Třetí část je text, který se zobrazí, když nic nevyhovuje.

Pozor: Euro-Office použije pro celý výsledek formát prvního sloupce. Začíná-li výsledek datem, zobrazí se jako datum i částky v posledním sloupci: z 36,00 se stane 05-02-1900. Vyberte tento sloupec výsledku a nastavte sami formát Měna nebo Číslo (viz Formátování čísel, dat a měn).
Řazení a výběr sloupců
- Podle částky, od nejvyšší: =SORT(FILTER(A2:D500;E2:E500="nee");4;-1). Číslo 4 je čtvrtý sloupec výsledku, -1 znamená sestupně.
- Řazení podle sloupce, který nezobrazujete: =SORTBY(FILTER(B2:B500;E2:E500="nee");FILTER(D2:D500;E2:E500="nee");-1) vrátí pouze jména zákazníků seřazená podle částky.
- Pouze zákazník a částka: =ZVOLITSLOUPCE(FILTER(A2:D500;E2:E500="nee");2;4).
Něco stojí v cestě
Pokud už je v některé buňce, kam se má výsledek rozlít, nějaký obsah, seznam se rozlít nemůže. V Excelu se pak zobrazí chybová hodnota #SPILL!. Euro-Office v takovém případě ponechá buňku se vzorcem prázdnou; když na ni kliknete, uvidíte tečkovaný rámeček kolem oblasti, kam se má výsledek rozlít. Vymažte buňky v této oblasti a seznam se okamžitě zobrazí. Pod takovým vzorcem a vedle něj proto nechávejte volné místo.

Výpočty s celými sloupci v jednom vzorci
Některé vzorce počítají s celým sloupcem najednou, například =SUMA(D2:D13*(E2:E13="nee")): součet částek všech dosud nezaplacených objednávek, zde 258,50. V Euro-Office takový vzorec funguje s běžným stisknutím klávesy Enter. Vzorce s funkcí KDYŽ nad celým sloupcem uvnitř jiné funkce, například =LARGE(KDYŽ(B2:B37="Taart";C2:C37);1), však Euro-Office po běžném stisknutí klávesy Enter nespočítá správně: dostanete #NUM! nebo chybné číslo. Takový vzorec potvrďte klávesami Ctrl+Shift+Enter. Vzorec pak dostane složené závorky {…} a vrátí správný výsledek. Často to jde i bez funkce KDYŽ, pomocí funkce FILTER: =LARGE(FILTER(C2:C37;B2:B37="Taart");1).
Co nelze
- LET a LAMBDA ani funkce, které na nich staví, jako BYROW, MAP a GROUPBY, Euro-Office nepodporuje. Soubor Excel s těmito funkcemi zobrazí #NAME?. Použijte pomocný sloupec, nebo se podívejte do článku Excel-formules met LET of LAMBDA: wat je in Euro-Office in plaats daarvan doet, co dělat místo toho.
- Kolega s Excelem 2019 nebo starším uvidí u těchto funkcí #NAME?.
Časté chyby
- Celé sloupce jako B:B vzorec zpomalují. Zvolte dostatečně velký pevný rozsah, například B2:B500.
- Nepoužívejte PRAVDA ani NEPRAVDA bez závorek v těchto vzorcích; použijte 1 a 0 nebo TRUE a FALSE (viz Přebírání vzorců z Excelu: české názvy funkcí a středník).
