Blog

Dynamické oblasti v Excelu: kdy použít OFFSET a kdy tabulku

Při přidávání řádků aktualizujte součty i rozevírací seznamy. Vyzkoušejte OFFSET a COUNTA, zjistěte dopad prázdného ID a naučte se používat tabulky Excelu.

Po přidání řádku se zvýrazněná oblast odkazu rozšíří dolů.

Přidáte položku do seznamu v Excelu, ale součet i možnosti v rozevíracím seznamu zůstanou stejné. Pokud odkaz stále míří na A2:A10, jedenáctý řádek je mimo oblast. Dynamická oblast umožní Excelu určit konec seznamu, aniž by se při každé změně upravovaly vzorce.

Určení výšky a šířky pomocí OFFSET

Pokud je seznam souvislý, spočítejte záhlaví a ID produktů a zadejte výšku a šířku funkci OFFSET. Vytvořte ukázku na listu Sheet1 a vzorec pro součet zadejte na jiném listu. Japonská záhlaví opište přesně, aby fungovaly strukturované odkazy níže.

Buňka Sloupec A Sloupec B Sloupec C
Řádek 1 商品ID (ID produktu) 商品名 (název produktu) 数量 (množství)
Řádek 2 A01 Pero 4
Řádek 3 A02 Sešit 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Začněte v A1 bez posunu řádku nebo sloupce. Výšku určuje počet vyplněných buněk ve sloupci A, šířku počet záhlaví v řádku 1. Ukázka vrátí A1:C3, tři řádky a tři sloupce, se součtem 10. Přidání A03, pravítka a 2 do řádku 4 rozšíří oblast na A1:C4 a zvedne součet na 12. ID jsou text a počítané oblasti neobsahují jiné údaje. Dokumentace Microsoftu k OFFSET

Vymažte pouze ID z A3. Ve sloupci A se započítají tři buňky: záhlaví A1 a ID v A2 a A4. OFFSET tedy vrátí první tři řádky A1:C3. Množství 6 z řádku 3 zůstane v součtu, ale 2 z řádku 4 vypadne. Součet se vrátí na 10.

A3 je prázdná. Oblast OFFSET A1:C3 zahrnuje hodnotu 6 z řádku 3 a vynechává 2 z řádku 4. Součet je 10.
Zelený rámeček vyznačuje oblast A1:C3, kterou vrací OFFSET v Excelu pro Windows.

Prázdné záhlaví uprostřed může způsobit, že poslední sloupce zůstanou mimo oblast. COUNTA počítá i buňky se vzorcem vracejícím prázdný řetězec (""). U existujícího sešitu vyplňte záhlaví a ID souvisle a prověřte hranice počítaných oblastí a prázdné buňky. Prázdné množství výšku nezmění, pokud je ID vyplněné. Dokumentace Microsoftu k COUNTA

Když záhlaví začínají na druhém řádku

Pokud je na prvním řádku název sestavy, začněte seznam v A2. Výšku a šířku počítejte od druhého řádku, aby se název nezahrnul. Po posunutí ukázky o jeden řádek dolů bude pro záhlaví a dvě položky platit A2:C4 a součet zůstane 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Tabulka, která se při přidání řádků rozšíří

U nového seznamu vyberte oblast včetně záhlaví a zvolte Vložení → Tabulka. Tabulku pojmenujte Items. Následující vzorec sečte sloupec s množstvím z buňky mimo tabulku. Po přidání řádků se oblast pro součet rozšíří. Dokumentace Microsoftu ke strukturovaným odkazům

=SUM(Items[数量])

Sečtení pouze řádků s ID produktu

Když z A1:C4 vytvoříte tabulku Items, vrátí výše uvedený vzorec 12 (4 + 6 + 2), i když je A3 prázdná. Pro součet pouze řádků s ID použijte SUMIF s podmínkou neprázdného sloupce ID. V ukázce vyjde 6 (4 + 2 z řádků 2 a 4). Dokumentace Microsoftu k SUMIF

=SUMIF(Items[商品ID],"<>",Items[数量])

Propojení rozevíracího seznamu s tabulkou

Pro výběr názvů produktů na jiném listu otevřete v desktopovém Excelu Vzorce → Správce názvů. Vytvořte ItemChoices v rozsahu celého sešitu a do Odkaz na zadejte =Items[商品名]. Pro vstupní buňku nastavte v Ověření dat možnost Seznam a jako Zdroj zadejte =ItemChoices. Záhlaví nabídka vynechá a řádky přidané na konec tabulky přidají možnosti. Vyplňte název v každém novém řádku, aby nevznikly prázdné možnosti. Návod Microsoftu k vytvoření rozevíracího seznamu