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.
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.
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