Stáhněte si ukázkový sešit
Tento tutoriál vám poskytne Úvod do vzorců dynamického pole v Excelu a Tabulkách Google.
Úvod
V září 2022 představila společnost Microsoft vzorce dynamického pole do Excelu. Jejich účelem je usnadnit psaní složitých vzorců a s menší pravděpodobností chyby.
Dynamické maticové vzorce mají případně nahradit maticové vzorce, tj. Pokročilé vzorce, které vyžadují použití Ctrl + Shift + Enter (CSE).
Zde je rychlé srovnání mezi vzorcem pole a vzorcem dynamického pole použitým k extrahování seznamu unikátních oddělení z našeho seznamu v dosahu A2: A7.
Legacy Array Formula (CSE):
Do buňky je vložen následující vzorec D2 a zadává se stisknutím kláves Ctrl + Shift + Enter a zkopírováním z D2 na D5.
| 1 | {= IFERROR (INDEX ($ A $ 2: $ A $ 7, MATCH (0, COUNTIF ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Vzorec dynamického pole:
Následující vzorec je pouze vstup do buňky D2 a vstoupíte stisknutím klávesy Enter. Z rychlého pohledu poznáte, jak snadné a přímočaré je napsat vzorec Dynamic Array.
| 1 | = JEDINEČNÉ (A2: A7) |
Dostupnost
Od srpna 2022 jsou vzorce Dynamic Array k dispozici pouze uživatelům Office 365.
Rozlití a rozsah úniku
Dynamické maticové vzorce fungují tak, že vrací více výsledků do řady buněk na základě jednoho vzorce zadaného do jedné buňky.
Toto chování se označuje jako „Rozlití“ a rozsah buněk, kde jsou umístěny výsledky, se nazývá „Rozsah úniku“. Když vyberete libovolnou buňku v rozsahu rozlití, Excel ji zvýrazní tenkým modrým okrajem.
V níže uvedeném příkladu vzorec dynamického pole TŘÍDIT je v buňce D2 a výsledky byly rozlité v rozsahu D2: D7
| 1 | = TŘÍDIT (A2: A7) |
Výsledky vzorce jsou dynamické, což znamená, že pokud dojde ke změně ve zdrojovém rozsahu, výsledky se také změní a změní se rozsah rozlití.
#ROZLÍT!
Měli byste si uvědomit, že pokud váš rozsah úniku není zcela prázdný, vrátí se chyba #SPILL.
Když vyberete chybu #SPILL, požadovaný rozsah rozlití vzorce se zvýrazní přerušovaným modrým okrajem. Přesunutím nebo odstraněním dat v prázdné buňce se tato chyba odstraní a vzorec se může rozlít.
Záznam referenční skvrny
Abychom odkazovali na rozsah rozlití vzorce, umístíme # symbol za odkazem na buňku první buňky v rozlití.
Na únik můžete také odkazovat výběrem všech buněk v rozsahu rozlití a automaticky se vytvoří odkaz na únik.
V níže uvedeném příkladu bychom chtěli spočítat počet zaměstnanců v naší firmě pomocí vzorce COUNTA poté, co byly seřazeny podle abecedy pomocí vzorce dynamického pole TŘÍDIT.
Vstupujeme do TŘÍDIT vzorec v D2 k objednání zaměstnanců v našem seznamu:
| 1 | = TŘÍDIT (A2: A7) |
Poté vstoupíme do COUNTA vzorec v G2 spočítat počet zaměstnanců:
| 1 | = COUNTA (D2#) |
Všimněte si použití # v D2# odkazovat na výsledky rozlité pomocí SORT v rozsahu D2: D7.
Nové vzorce
Níže je uveden úplný seznam nových vzorců Dynamic Array:
- UNIKÁTNÍ - Vrátí seznam jedinečných hodnot z rozsahu
- TŘÍDIT - Seřadí hodnoty v rozsahu
- SEŘAZENO PODLE - Seřadí hodnoty na základě odpovídajícího rozsahu
- FILTR - Filtruje rozsah na základě zadaných kritérií
- RANDARRAY - Vrátí pole náhodných čísel mezi 0 a 1
- SEKVENCE - Vygeneruje seznam pořadových čísel, například 1, 2, 3, 4, 5
Vzorec dynamického poles v Tabulkách Google
Všechny výše uvedené příklady fungují v Tabulkách Google úplně stejně jako v Excelu.
