Uživatelé Microsoft Excel oceňují možnost vytvářet odkazy na jiné tabulky v aktivní složce. Zejména u velkých tabulek s mnoha vzorci, daty a funkcemi je užitečné vytvářet reference, aby bylo možné efektivně pracovat se všemi daty. To ušetří zdlouhavé ruční dvojité zadávání nebo kopírování a vkládání, které je časově náročné a náchylné k chybám.
V tomto článku se dozvíte, jak můžete propojit excelové tabulky pomocí několika kliknutí a provádět tak výpočty napříč více tabulkami.
Jak vytvoříte odkaz na jinou tabulku?
Vytvoříte propojení mezi dvěma excelovými tabulkami pomocí vzorce a externího odkazu v cílové tabulce.
V našem příkladu tabulka 1 ukazuje, kolik zaměstnanců má společnost na různých místech v Německu a dalších evropských zemích. Ve druhé tabulce by měl být uveden pouze počet zaměstnanců pobočky v Pasově. Propojení mezi dvěma tabulkami zajišťuje, že pokud se změní data v tabulce 1, informace v tabulce 2 se automaticky aktualizují.
Chcete-li vytvořit odkaz na jinou tabulku, nejprve otevřete obě tabulky v aplikaci Microsoft Excel. Poté v cílové tabulce klikněte na cílovou buňku, kde chcete zobrazit data ze zdrojového souboru. V našem praktickém příkladu se jedná o cílovou buňku B1.Vzorec pro odkaz vždy začíná znakem rovná se. Napište "="do dříve vybrané buňky.
V dalším kroku se přepněte do tabulky s údaji o zaměstnanci a zvýrazněte číslo 55 v buňce C9. To je počet zaměstnanců pobočky v Pasově.
Zkratku ukončíte stisknutím klávesy Enter. Počet 55 zaměstnanců se nyní zobrazuje v tabulce 2, protože jste účinně propojili buňky obou listů.
Pokud chcete vytvořit referenci ručně pomocí klávesnice, použijte následující syntaxi:
=název listu!cell
Pro název listu zadejte název listu, na jehož buňku chcete odkazovat. V našem příkladu je název listu "List1." Štítek buňky určuje odkaz na buňku (buňky) v Listu1, na které se chcete odkazovat.
Jak provádět výpočty ve více excelových tabulkách?
Často se stává, že výpočty musí být provedeny přes několik tabulek. V našem vzorovém sešitu je několik tabulek, které mají stejnou strukturu. Tabulka popisuje náklady na elektřinu různých závodů společnosti za jeden rok, rozdělené podle měsíců. Sešit obsahuje data za roky 2016 až 2021. Součty a maximální hodnoty se počítají v souhrnném listu.
Uživatelé, kteří s Excelem pracují sporadicky, vědí, jak provádět výpočty ve více listech v sešitu. Málokdo ví, že při výpočtu přes více tabulek lze přenést oblasti, které vzorce výrazně zkrátí a zpřehlední.
| Důležité: Je důležité, aby všechny tabulky, které zahrnete do výpočtu, byly strukturovány identicky podle stejného vzoru. Pouze v tomto případě budou vzorce poskytovat správné výsledky. Zejména u rozsáhlých tabulek s velkým množstvím dat nemusí být na výsledku nutně znát, pokud je datová oblast v jedné z tabulek posunuta o řádek nebo sloupec. Přitom výsledek je chybný. |
V následujícím příkladu je třeba sečíst náklady na elektřinu za posledních několik let. Obrázek ukazuje strukturu jednotlivých listů:
Kromě jednotné struktury tabulek je důležité uspořádání listů v sešitu. Ujistěte se, že pracovní listy, které potřebujete, jsou v sešitu vedle sebe.
Následující obrázek ukazuje uspořádání pracovních listů ve vzorovém sešitu:
Jakmile jsou přípravy hotové, můžete začít psát vzorce.
Jak vypočítat součet více listů v Excelu?
V naší vzorové tabulce má být celkový výsledek všech ročních součtů určen v souhrnném listu. Součty jednotlivých let jsou v příslušných tabulkách v buňce H16.
Vzorec požadovaný pro sčítání má následující strukturu:
=SUM('FirstSheet:LastSheet'!Rozsah)
" Pomocí FirstSheet předáte jméno prvního listu, který má být zahrnut do výpočtu. Argument lastsheet definuje název posledního listu pro výpočet. Uvažují se také všechny plechy ležící mezi těmito dvěma plechy.Oblast definuje oblast, kterou chcete sečíst."
Pro výpočet součtu hodnot v buňkách H16 listů 2016 až 2021 použijte následující vzorec:
=SUM('2016:2021'!H16)
Následující obrázek ukazuje použití tohoto vzorce ve vzorové tabulce:
Přizpůsobením tohoto jednoduchého vzorce je možné dotazovat se i na jiné buňky. Chcete-li například vědět, kolik elektřiny bylo spotřebováno v závodě ve Frankfurtu v dubnu 2016 až 2021, nahraďte ve vzorci odkaz na buňku H16 buňkou F7.
Při výpočtu ve více tabulkách nejste omezeni na jednotlivé buňky v tabulkách. Funkci můžete kdykoli předat celé rozsahy.
Jak vypočítat maximální hodnotu více listů v Excelu?
V naší vzorové tabulce, ve které jsou porovnány náklady na elektřinu různých elektráren v letech 2016 až 2021, má být stanovena absolutní maximální hodnota všech jednotlivých hodnot všech let. Jednotlivé hodnoty jsou v tabulkách 2016 až 2021 v buňkách B4:G15.
Použijte následující vzorec k určení maximální hodnoty všech jednotlivých hodnot v pěti tabulkách odpovídajících let:
=MAX('2016:2021'!B4:G15)
Následující obrázek ukazuje použití tohoto vzorce ve vzorové tabulce:
Jak vypočítáte průměr v různých tabulkách?
Kromě maximálních hodnot lze pomocí jediného vzorce vypočítat také střední hodnotu z více tabulek. K tomu použijte následující syntaxi.
=PRŮMĚR('počáteční list:koncový list'!rozsah buněk)
Se vzorcem předáte celkem tři argumenty:
- S úvodním listem předáte jméno prvního listu, který má být zahrnut do výpočtu.
- Název koncového listu se používá k definování názvu posledního listu pro výpočet. Do výpočtu jsou zahrnuty všechny plechy ležící mezi těmito dvěma plechy.
- CellRange se používá k definování rozsahu buněk pro výpočet.
V naší vzorové tabulce vypadá vzorec a výsledek takto:
=PRŮMĚR('2016:2021'!B4:G15)
Jak lze propojit hodnoty z jiných dokumentů?
Pokud často pracujete s Excelem, někdy potřebujete propojit odkazy na buňky z tabulky, které nejsou ve stejném dokumentu.To funguje bez problémů přidáním názvu dokumentu do výše popsaného odkazu na buňku. Toto je označeno hranatými závorkami (" [" nebo "]" ).
Příklad:=[Dokument1]Původ!G9
Tuto variantu použijte, pokud je otevřena druhá složka s názvem „Dokument1“. Na druhou stranu, pokud dokument není otevřený, použijte cestu, kde je uložen.
Příklad:=C:\Files\FolderName\[Document1]Původ!G9
Jak propojit odkazy na buňky pomocí myši?
Odkazy na buňky na jiné dokumenty nemusí být nutně zadávány ručně, protože buňky můžete snadno propojit pomocí několika kliknutí myší. (VLOŽTE OBRÁZEK)
Jak opravit chybu odkazu na buňku?
Při propojování tabulek a používání odkazů může Microsoft Excel zobrazit chybu v odkazu na buňku. Pokud tomu tak je, vzorec obsahuje chybu nebo došlo k chybě při výběru oblasti pomocí myši
K chybě odkazu dojde také v případě, že byl zadán odkaz na buňku na jiný list, který byl odstraněn nebo přejmenován. Tuto chybu lze přirovnat k doručení dopisu na konkrétní adresu. Jakmile se změní PSČ, dopis již nelze doručit, protože adresa je nesprávná.
Shrnuto a podtrženo, často dává smysl pracovat s referencemi v Excelu. Hlavní výhodou práce s odkazy je, že svou práci můžete dělat efektivněji a rychleji.
Odkazy vám umožňují přístup k existujícím datům, aniž byste je museli znovu zadávat. To šetří čas a zabraňuje chybám přenosu. V Excelu lze k provádění výpočtů použít také odkazy. Odkazy lze vytvořit na jiné buňky v sešitu nebo na buňky v jiném sešitu. Jsou užitečné pro organizaci dat a provádění výpočtů.Odkazy lze také použít k filtrování a třídění informací. Reference jsou tedy v Excelu mocným nástrojem, který vám pomůže dělat vaši práci efektivněji.
