Pokud jste někdy pracovali s maticovými funkcemi v Excelu jako jsou funkce SORT, UNIQUE nebo FILTER, tak jste si určitě všimli, že se u nich občas objevuje křížek. Křížek je totiž nový způsob, jak se Excel odkazuje na rozsah buněk, které vznikly pomocí maticových funkcí. V dnešním videu si ukážeme, jak s křížkem pracovat a kdy se vám může hodit. Samozřejmě za předpokladu, že používáte Microsoft 365 nebo 2021 a máte k těmto maticovým funkcím přístup.
Excelový soubor ke stažení
Křížek v Excelu (#)
Začneme velmi jednoduchým příkladem, na kterém si význam křížku ukážeme. Máme sloupec s produkty, který chceme seřadit podle abecedy. K seřazení produktů můžeme použít novou dynamickou funkci SORT. Do vedlejší buňky napíšeme funkci SORT, kde označíme celý sloupec s produkty. Funkci potvrdíme a produkty se seřadily podle abecedy. Když klikneme do první buňky, kam jsme funkci SORT napsali, tak vidíme, že se funkce rozlila do buněk, což poznáme podle toho, že je výsledek funkce ohraničený modrou linkou a funkce tzv. žije pouze v první buňce. Toto celé je tzv. dynamická oblast neboli v angličtině spilled range. A právě na buňky, které vznikly pomocí dynamických funkcí můžeme použít křížek.
Křížek lze totiž použít pouze při odkazu na výsledek maticové funkce jakými jsou právě funkce SORT, UNIQUE, FILTER a další. Pokud chcete zjistit něco více o nejpoužívanějších dynamických funkcích, tak se podívejte na naše video Převratné funkce v Excelu.
Budeme pokračovat v příkladu a řekněme, že následně chceme z tohoto seřazeného seznamu vyselektovat jedinečný seznam produktů. Můžeme tedy použít druhou dynamickou funkci UNIQUE. Nejprve napíšeme funkci UNIQUE a označíme v ní nejprve pouze první buňku. Funkci potvrdíme a funkce UNIQUE vrátila pouze první produkt, jelikož jsme označili pouze jeden produkt.
Vedle napíšeme znovu funkci UNIQUE a označíme tentokrát celý sloupec hodnot, který je výsledkem funkce SORT. A vidíme, že když označíme celý sloupec, že se rozpětí buněk změní na první buňku s křížkem. Tak poznáme, že se vzorec odkazuje na dynamickou oblast. Funkci potvrdíme a vrátil se jedinečný seznam produktů.
Stejně tak bychom mohli napsat funkci UNIQUE a rovnou do ní napsat koordinace první buňky a za ní křížek. Jak vidíme, tak Excel automaticky označil celou dynamickou oblast, která je výsledkem funkce SORT. Funkci potvrdíme a opět se vrátil jedinečný seznam produktů. Úkolem křížku je tedy říci Excelu, aby nepoužil pouze jednu buňku, ale celé rozpětí buněk, které vrátila dynamická oblast.
Pokud bychom ovšem chtěli stejný postup použít na původní seznam, tedy napsali bychom funkci UNIQUE a napsali bychom do ní koordinace první buňky, tedy buňku A3 a za ní napsali křížek, tak se vrátí chyba. A důvodem je to, že se neodkazujeme na dynamickou oblast, která je výsledkem maticových vzorců, takže křížek vrátí chybu.
Použití křížku si ukážeme na příkladu z praxe. Máme zde zdrojovou tabulku, kde máme uvedené produkty, které se opakují. U produktů máme uvedené rovněž datum, kdy se produkt prodal, v jakém množství a s jakou tržbou. Máme dvojí úkol. Zaprvé potřebujeme vytvořit tabulku, kde bude uvedený jedinečný seznam produktů a jejich celkové tržby. Zadruhé potřebujeme vytvořit rozbalovací seznam s jedinečným seznamem produktů. Na základě výběru v rozbalovacím seznamu se má ve druhé tabulce zobrazit datum, počet prodaných kusů a tržba. Na tomto příkladu si ukážeme, jak můžete křížek s novými dynamickými funkcemi použít.
Nejprve začneme tím, že z obyčejné zdrojové tabulky uděláme excelovou tabulku. Klikneme do zdrojové tabulky a zmáčkneme klávesovou kombinaci CTRL+T, potvrdíme, že tabulka má záhlaví a z obyčejné tabulky vznikla excelová tabulka. Ještě rovnou změníme tento pruhovaný formát na žádný formát, takže styly tabulky a styl žádný.
V tabulkách, stejně jako v rozbalovacím seznamu chceme mít jedinečný seznam produktů, k čemuž můžeme použít funkci UNIQUE. Pomocí funkce UNIQUE vytvoříme pomocný seznam někde stranou, který následně použijeme jako zdroj pro tabulky a rozbalovací seznam. Někde vedle napíšeme funkci UNIQUE, kde označíme produkty ze zdrojové tabulky a jelikož pracujeme s excelovou tabulkou, tak najedeme šipkou na záhlaví tabulky, až se z kurzoru myši stane tato svislá černá šipka a zmáčkneme ENTER, tím se do funkce označí celý sloupec tabulky. Výhodou tohoto zápisu je, že až přidáme nové produkty do tabulky, tak se díky tomu automaticky zahrnou do vzorců. Funkci potvrdíme a máme jedinečný seznam produktů.
Teď se vrhneme na tvorbu první tabulky, kde chceme mít pro každý produkt uvedené celkové tržby. A právě zde využijeme křížek. V tabulce chceme mít jedinečný seznam produktů, který máme již vytvořený vedle. Takže se na něj v tabulce odkážeme. Napíšeme rovná se označíme první buňku pomocného seznamu a za ní napíšeme křížek. Jak si můžete všimnout, tak se tím samo označilo celé rozpětí buněk. Potvrdíme a vrátil se jedinečný seznam produktů.
Teď chceme sečíst celkové tržby. K tomu využijeme funkci SUMIF, nicméně funkce SUMIF v základním použití nereaguje na dynamická rozpětí. To znamená, že kdybychom postupovali klasicky, napsali funkci SUMIF, kde bychom nejprve označili sloupec s produkty, následně produkt a sloupec s tržbami, tak bychom museli vzorec stáhnout dolů. A teď počítá, nicméně kdybychom do tabulky přidali nový produkt, tak by se vzorec SUMIF neaktualizoval a neprotáhl pro nový produkt.
Existuje ale velmi lehký trik, jak udělat funkci SUMIF dynamickou. Toto smažeme a začneme funkci SUMIF znovu. Nejprve označíme sloupec s produkty, a teď přichází trik, kdy se místo jednoho hledaného produktu označí celý rozsah produktů. Vidíme, že se u buňky následně sám vrátil křížek, jelikož Excel pochopil, že se odkazujeme na dynamický rozsah. Teď stačí označit sloupec s tržbami. Potvrdíme a funkce SUMIF vrátila součet tržeb. To, že je funkce SUMIF dynamická poznáme podle toho, že se u ní objevilo modré tenké rámování, což signalizuje dynamické rozpětí.
Křížek využijete i v rozbalovacím seznamu. Nejprve klikneme do buňky, kde chceme mít rozbalovací seznam. Na kartě Data najdeme ověření dat a vybereme seznam. Do zdroje napíšeme rovná se funkci UNIQUE, kde označíme sloupec s produkty ze zdrojové tabulky. Potvrdíme a vrátila se chyba. Důvodem je to, že v současné době rozbalovací seznam neakceptuje funkci UNIQUE pro vytvoření seznamu. Můžeme to ale obejít právě pomocí křížku.
Řešením je odkázat se v rozbalovacím seznamu na oblast, která byla vytvořená maticovou funkcí, takže se odkážeme na pomocný seznam jedinečných produktů, který jsme vytvořili pomocí funkce UNIQUE. Odkážeme se na první buňku a napíšeme za ní křížek. Potvrdíme vytvoření rozbalovacího seznamu a do buňky se vložil rozbalovací seznam.
Teď na základě výběru v rozbalovacím seznamu vyfiltrujeme do tabulky data pro vybraný produkt. Použijeme funkci FILTER. Do první buňky napíšeme funkci FILTER, kde označíme rozsah tabulky, a to od sloupce datum až po tržbu. Napíšeme středník a jako podmínku označíme sloupec s produkty, rovná se a produkt v rozbalovacím seznamu. Potvrdíme a máme data v tabulce vyfiltrovaná pro vybraný produkt.
Křížek si můžete otestovat i v praxi, a to když přidáte nová data do zdrojové tabulky. Hned jakmile přidáte do zdrojové tabulky nová data, tak se aktualizuje pomocný seznam jedinečných produktů. A jelikož jsme v ostatních tabulkách ve funkcích použili jako referenci křížek, tak se okamžitě aktualizuje i tabulka s celkovými tržbami pro produkty. Rovněž se aktualizuje i seznam produktů v rozbalovacím seznamu a tím pádem i druhá tabulka, kde jsme použili funkci FILTER.
Jedna odpověď
😊