Takto v aplikaci Excel vytvoříte odkaz na buňku v jiném listu ve stejné knize a provedete výpočty mezi listy.
Zvláště při práci se vzorci a funkcemi ve větších složkách budete možná potřebovat odkaz na buňku v jiném listu aktivní složky. To platí zejména při výpočtu hodnot, které jsou v různých tabulkách v sešitu.
Pokud potřebujete data ve více než jedné tabulce aplikace Excel, nemusíte je pracně přepisovat nebo kopírovat a vkládat do příslušných buněk a sloupců. V tomto článku vám ukážeme, jak lze snadno propojit tabulky aplikace Excel mezi sebou a provádět tak výpočty na více listech.
Pokyny: Jak vytvořit propojení mezi dvěma tabulkami aplikace Excel
Propojení mezi dvěma tabulkami aplikace Excel vytvoříte pomocí vzorce a externího odkazu v cílové tabulce: Chcete -li to provést, nejprve otevřete dvě tabulky, které chcete navzájem propojit. Poté klikněte na buňku v cílové tabulce, ve které chcete zobrazit data ze zdrojového souboru. V našem případě je to buňka B1.
Nyní by se měl z naší zdrojové tabulky zobrazit počet zaměstnanců z pobočky v Pasově.
Vzorec pro odkaz je vždy uveden se znaménkem rovnosti. Do dříve vybrané buňky zadejte „=“.
Takovou referenci můžete vytvořit buď myší, nebo ji zadat přímo pomocí klávesnice.
Pokud se nacházíte v pozici ve funkci nebo vzorci, kde se očekává reference (např. Po zadání znaménka rovnosti do buňky), klikněte na požadovaný list a poté na požadovanou buňku. Poté Excel automaticky vytvoří odkaz.
Pokud chcete vygenerovat odkaz pomocí klávesnice, použijte následující syntaxi:
= Název listu! Buňka
Do pole Název listu zadejte název listu, jehož buňku chcete odkazovat. Buňka nastavuje odkaz na buňky v tabulce Název listu, na které chcete odkazovat.
Následující položka například odkazuje na buňku B1 v listu „Zaměstnanci“:
= Zaměstnanec! B1
Jak provádět výpočty ve více tabulkách aplikace Excel
Sešit obsahuje několik pracovních listů, které jsou strukturovány identicky. V souhrnném listu by nyní měly být prováděny výpočty napříč všemi listy - například součty nebo maximální hodnoty.
Většina uživatelů zná základní možnost provádění výpočtů pomocí několika tabulek v sešitu a je široce používána.
Jen málo lidí ví, že lze přenést oblasti, které vzorce výrazně zkrátí a vyjasní. Chcete-li použít oblasti mezi tabulkami, postupujte následovně:
Zajistěte, aby všechny pracovní listy, které potřebujete pro své výpočty, byly strukturovány identicky. Teprve poté dodají vzorce správné výsledky.
Všimněte si, že výsledek nemusí být nutně patrný, pokud je oblast dat v jednom z listů posunuta o řádek nebo sloupec.
V tomto příkladu mají být výpočty provedeny pomocí sedmi tabulek pro srovnání, ve kterých jsou zaznamenány starší údaje o produkci od roku 2002 do roku 2008.
Následující obrázek ukazuje strukturu jednotlivých listů:
Kromě jednotné struktury tabulek je důležité i uspořádání listů v sešitu. Ujistěte se, že potřebné pracovní listy jsou v sešitu vedle sebe.
Následující obrázek ukazuje uspořádání listů v ukázkovém sešitu:
Po dokončení příprav můžete začít zadávat vzorce.
Jak vypočítat součet více listů z Excelu
V příkladové tabulce má být součet všech ročních součtů určen v souhrnném listu. Součty za jednotlivé roky jsou v odpovídajících tabulkách v buňce H16.
K tomu potřebný vzorec má následující strukturu:
= SUM ('FirstSheet: LastSheet'! Range)
Pomocí „První list“ přenesete název prvního listu, který má být zahrnut do výpočtu. Argument „poslední list“ definuje název posledního listu pro výpočet. Rovněž jsou brány v úvahu všechny listy mezi těmito dvěma listy. Pomocí "Oblast" definujte oblast, kterou chcete sčítat.
Pro výpočet součtu hodnot v buňkách H16 v pracovních listech 2002 až 2008 použijte následující vzorec:
= SUM ('2002: 2008'! H16)
Následující obrázek ukazuje použití tohoto vzorce v ukázkové tabulce:
Stejným způsobem můžete dotazovat další buňky. V případě potřeby můžete jako argumenty použít také další funkce, kterým jsou oblasti předávány.
Při výpočtu na několika pracovních listech nejste omezeni na jednotlivé buňky v uvedených tabulkách. Můžete tam také předat oblasti funkci.
Jak vypočítat maximální hodnotu více listů z Excelu
V příkladové tabulce se stanoví absolutní maximální hodnota všech jednotlivých hodnot pro všechny roky. Jednotlivé hodnoty jsou v tabulkách 2002 až 2008 v buňkách B4: G15.
Pomocí následujícího vzorce určete maximální hodnotu všech jednotlivých hodnot v sedmi pracovních listech pro odpovídající roky:
= MAX ('2002: 2008'! B4: G15)
Následující obrázek ukazuje použití tohoto vzorce v ukázkové tabulce:
Střední hodnotu lze také vypočítat z několika listů. Chcete -li to provést, postupujte takto:
= PRŮMĚRNÁ HODNOTA („Počáteční list: Koncový list“! ZellBer)
Do vzorce předáte tři argumenty: With Úvodní list předat název prvního listu, který má být zahrnut do výpočtu. Výše Koncový list definujte název posledního listu pro výpočet. Do výpočtu jsou zahrnuty všechny listy mezi těmito dvěma listy. Výše ZellBer definujte rozsah buněk pro výpočet.
Jak vypočítat průměr více listů z aplikace Excel
Střední hodnotu lze také vypočítat z několika listů. Chcete -li to provést, postupujte takto:
= PRŮMĚRNÁ HODNOTA („Počáteční list: Koncový list“! ZellBer)
Do vzorce předáte tři argumenty: With Úvodní list předat název prvního listu, který má být zahrnut do výpočtu. Výše Koncový list definujte název posledního listu pro výpočet. Do výpočtu jsou zahrnuty všechny listy mezi těmito dvěma listy. Výše ZellBer definujte rozsah buněk pro výpočet.
Jak lze propojit hodnoty z jiných dokumentů?
Někdy je třeba propojit odkazy na buňky z jiného listu v Excelu, které nejsou ve stejném dokumentu. Chcete -li to provést, stačí přidat název dokumentu do výše popsaného odkazu na buňku. Toto je označeno hranatými závorkami („[“ nebo „]“).
Příklad:
= [Document1] Původ! G9
Tuto variantu použijete, pokud je otevřena druhá složka s názvem "Document1". Pokud však dokument není otevřený, použijte cestu, kam je uložen.
Příklad:
= C: \ soubory \ název složky \ [dokument1] původ! G9
Propojte odkazy na buňky pomocí myši
Odkazy na buňky jiných dokumentů nemusíte zadávat ani ručně; buňku můžete propojit pomocí myši:
Chcete -li to provést, otevřete oba dokumenty, které potřebujete.
Klikněte na cílovou buňku a do buňky zadejte znaménko rovnosti.
Poté stačí přepnout na druhý dokument a kliknout na buňku, jejíž hodnotu potřebujete. Stiskněte klávesu Enter.
Nakonec zkontrolujte, zda Excel nastavil správnou referenci - při aktivaci cílové buňky ji znovu uvidíte ve vstupním řádku.
Chyba odkazu na buňku
Neposkytnutí jasných příkazů Excelu může mít za následek referenční chybu. Buňka může mít vždy jen jednu buňku jako referenční hodnotu. Pokud tomu tak není, s největší pravděpodobností došlo k chybě, když byla oblast označena myší.
K referenční chybě také dochází, pokud byl odkaz na buňku zadán do jiného listu, ale tento list byl odstraněn nebo přejmenován. Srovnatelné s doručením na adresu, počet půjček na poště by pak už nebyl správný.
