Načtení dat z externího sešitu pomocí funkce INDEX – 01

Pokud nastane situace, kdy potřebujeme načíst data z externího otevřeného nebo i zavřeného sešitu, je nezbytně nutné zadat ve správném tvaru názvy listu, sešitu a také cesty k tomuto sešitu, včetně správné syntaxe jako je oddělení názvu listu od adresy oblasti buněk znakem vykřičník nebo uvození názvu sešitu apostrofem.

Je snadné při psaní názvů udělat chybu, která následně způsobí, že propojení ke zdroji dat nebude fungovat.

Je proto rozumné přenechat vytvoření názvů přímo Excelu – existuje totiž způsob, kdy zápis všech důležitých názvů ponecháme zcela na Excelu a vyhneme se jak zbytečným chybám, tak i poměrně pracnému zapisování informací.

Postup bezpečného propojení dvou sešitů

  1. Vytvoříme dvojici sešitů, které budeme propojovat a umístíme je do stejného adresáře
Sešity určené k propojení

2. Oba sešity otevřeme a případné další sešity otevřené v Excelu zavřeme. V Excelu tedy máme otevřené jen sešity, které budeme propojovat.

3. Pomocí příkazu Uspořádat vše na kartě Zobrazení a příkazu Vedle sebe rozdělíme obrazovku Excelu na dvě samostatné části. V každé z nich bude zobrazen jeden z otevřených sešitů.

Zobrazení dvojice sešitů vedle sebe

4. Sešit Prodej_Data.xlsx obsahuje data, která budeme po jednotlivých řádcích načítat do sešitu Prodej_Suma.xlsx a zde je pomocí funkce SUMA sečteme.

Zobrazení dvojice sešitů vedle sebe

5. Zapíšeme do pomocné buňky B2 písmeno A, které představuje název produktu ve zdrojové tabulce dat a určuje tedy řádek, jeož hodnoty chceme sečíst.

=SUMA(INDEX([Prodej_Data.xlsx]vysledky!$B$2:$G$5;1;))

Zápis názvu sešitu a listu Excel vytvoří sám ihned, když při vytváření vzorce =SUMA(INDEX( klepneme do sešitu se zdrojovými daty a označíme myší oblast dat B2:G5

Načtení dat pomocí funkce INDEX

6. Vynecháním argumentu pro číslo sloupce zajistíme, že funkce INDEX načte všechny sloupce v požadovaném řádku číslo 1.

Načtení hodnot ze všech sloupců vybrané oblasti

7. Vytvořený vzorec sečte všechny hodnoty v 1. řádku odkazovaného zdroje dat

Sečtené hodnoty z externího sešitu

8. Vzorec doplníme o funkci POZVYHLEDAT, která dohledá číslo řádku pomocí názvu produktu.

=SUMA(INDEX([Prodej_Data.xlsx]vysledky!$B$2:$G$5;POZVYHLEDAT(B2;[Prodej_Data.xlsx]vysledky!$A$2:$A$5;0);))

Doplnění vzorce o funkci POZVYHLEDAT

9. Pokud zavřeme zdrojový sešit Prodej_Data.xlsx, Excel automaticky do našeho vzorce doplní i cestu k tomuto sešitu a tím zajistí propojení k datům zavřeného sešitu.

Vložení cesty k zavřenému sešitu

10. Změníme-li v sešitu Prodej_Suma.xlsx název produktu, automaticky se aktualizuje výsledný součet, i když zdrojový sešit je zavřený.

Oba v textu zmiňované sešity si můžete pro rychlejší seznámení s technikou propojení stáhnout – nahrajte je po stažení do stejné složky.