Takto vytvoríte v programe Excel odkaz na bunku v inom hárku tej istej knihy a vykonáte výpočty medzi listami.
Najmä pri práci so vzorcami a funkciami vo väčších priečinkoch budete možno potrebovať odkaz na bunku v inom hárku aktívneho priečinka. To platí najmä pri výpočte hodnôt, ktoré sú v zošite v rôznych tabuľkách.
Ak potrebujete údaje vo viacerých tabuľkách programu Excel, nemusíte ich namáhavo zadávať znova ani kopírovať a vkladať do príslušných buniek a stĺpcov. V tomto článku vám ukážeme, ako môžete navzájom jednoducho spájať tabuľky programu Excel a vykonávať tak výpočty vo viacerých pracovných hárkoch.
Pokyny: Ako vytvoriť prepojenie medzi dvoma tabuľkami programu Excel
Prepojenie medzi dvoma tabuľkami programu Excel vytvoríte pomocou vzorca a externého odkazu v cieľovej tabuľke: Na tento účel najskôr otvorte dve tabuľky, ktoré chcete navzájom prepojiť. Potom kliknite na bunku v cieľovej tabuľke, v ktorej chcete zobraziť údaje zo zdrojového súboru. V našom prípade je to bunka B1.
Teraz by sa mal z našej zdrojovej tabuľky zobrazovať počet zamestnancov z pobočky v Passau.
Vzorec pre odkaz je vždy uvedený so znamienkom rovnosti. Do predtým vybratej bunky zadajte „=“.
Takúto referenciu môžete vytvoriť buď myšou, alebo ju zadať priamo pomocou klávesnice.
Ak sa nachádzate v rámci funkcie alebo vzorca, kde sa očakáva referencia (napr. Po zadaní znamienka rovnosti do bunky), kliknite na požadovaný pracovný hárok a potom na požadovanú bunku. Potom Excel automaticky vytvorí referenciu.
Ak chcete vygenerovať referenciu pomocou klávesnice, použite nasledujúcu syntax:
= Názov listu! Bunka
Do poľa Názov listu zadajte názov listu, ktorého bunku chcete odkazovať. Bunka nastavuje odkaz na bunky v tabuľke Názov hárka, na ktoré chcete odkazovať.
Nasledujúca položka napríklad odkazuje na bunku B1 v hárku „Zamestnanci“:
= Zamestnanec! B1
Ako vykonávať výpočty vo viacerých tabuľkách programu Excel
Zošit obsahuje niekoľko pracovných hárkov, ktoré sú štruktúrované rovnako. V súhrnnom hárku by sa teraz mali vykonávať výpočty na všetkých hárkoch - napríklad súčty alebo maximálne hodnoty.
Väčšina používateľov pozná základnú možnosť vykonávania výpočtov pomocou niekoľkých tabuliek v zošite a je široko používaná.
Len málo ľudí vie, že je možné preniesť oblasti, ktoré vzorce výrazne skrátia a vyjasnia. Ak chcete použiť oblasti medzi tabuľkami, postupujte nasledovne:
Zaistite, aby všetky pracovné listy, ktoré potrebujete pre svoje výpočty, boli štruktúrované rovnako. Až potom vzorce poskytnú správne výsledky.
Všimnite si toho, že výsledok nemusí byť nevyhnutne viditeľný, ak je oblasť údajov v jednom z hárkov posunutá o riadok alebo stĺpček.
V tomto prípade sa výpočty majú vykonať pomocou siedmich tabuliek na porovnanie, v ktorých sú zaznamenané staršie údaje o produkcii od roku 2002 do roku 2008.
Nasledujúci obrázok zobrazuje štruktúru jednotlivých listov:
Okrem jednotnej štruktúry tabuliek je dôležité aj usporiadanie listov v pracovnom zošite. Uistite sa, že potrebné pracovné listy sú v zošite vedľa seba.
Nasledujúci obrázok ukazuje usporiadanie pracovných hárkov vo vzorovom zošite:
Po dokončení príprav môžete začať zadávať vzorce.
Ako vypočítať súčet viacerých pracovných hárkov z programu Excel
V príkladovej tabuľke je súčet všetkých ročných súčtov určený v súhrnnom hárku. Súčty za jednotlivé roky sú v zodpovedajúcich tabuľkách v bunke H16.
Na to požadovaný vzorec má nasledujúcu štruktúru:
= SUM („Prvý list: Posledný list“! Rozsah)
Pomocou „prvého listu“ prenesiete názov prvého listu, ktorý má byť zahrnutý do výpočtu. Argument „posledný list“ definuje názov posledného listu pre výpočet. Do úvahy sa berú aj všetky listy medzi týmito dvoma listami. Pomocou „Oblasti“ definujte oblasť, ktorú chcete sčítať.
Na výpočet súčtu hodnôt v bunkách H16 v pracovných hárkoch 2002 až 2008 použite nasledujúci vzorec:
= SUM ('2002: 2008'! H16)
Nasledujúci obrázok ukazuje použitie tohto vzorca v príkladovej tabuľke:
Rovnakým spôsobom sa môžete pýtať na ostatné bunky. V prípade potreby môžete ako argumenty použiť aj ďalšie funkcie, ktorým sú oblasti odovzdávané.
Pri výpočte cez niekoľko pracovných hárkov nie ste obmedzení na jednotlivé bunky v uvedených tabuľkách. Môžete tam tiež odovzdať oblasti funkcii.
Ako vypočítať maximálnu hodnotu viacerých pracovných hárkov z programu Excel
V príkladovej tabuľke sa určí absolútna maximálna hodnota všetkých individuálnych hodnôt za všetky roky. Jednotlivé hodnoty sú v tabuľkách 2002 až 2008 v bunkách B4: G15.
Na stanovenie maximálnej hodnoty všetkých jednotlivých hodnôt v siedmich pracovných listoch pre zodpovedajúce roky použite nasledujúci vzorec:
= MAX ('2002: 2008'! B4: G15)
Nasledujúci obrázok ukazuje použitie tohto vzorca v príkladovej tabuľke:
Priemernú hodnotu je tiež možné vypočítať z niekoľkých listov. Ak to chcete urobiť, postupujte nasledovne:
= PRIEMERNÁ HODNOTA („Úvodný list: Koncový list“! ZellBer)
Do vzorca odovzdáte tri argumenty: S Štartovací list odovzdajte názov prvého listu, ktorý sa má zahrnúť do výpočtu. Hore Koncový list definujte názov posledného listu pre výpočet. Do výpočtu sú zahrnuté všetky listy medzi týmito dvoma listami. Hore ZellBer definujte rozsah buniek pre výpočet.
Ako vypočítať priemer viacerých pracovných hárkov z Excelu
Priemernú hodnotu je tiež možné vypočítať z niekoľkých listov. Ak to chcete urobiť, postupujte nasledovne:
= PRIEMERNÁ HODNOTA („Úvodný list: Koncový list“! ZellBer)
Do vzorca odovzdáte tri argumenty: S Štartovací list odovzdajte názov prvého listu, ktorý sa má zahrnúť do výpočtu. Hore Koncový list definujte názov posledného listu pre výpočet. Do výpočtu sú zahrnuté všetky listy medzi týmito dvoma listami. Hore ZellBer definujte rozsah buniek pre výpočet.
Ako je možné prepojiť hodnoty z iných dokumentov?
Niekedy je potrebné prepojiť odkazy na bunky z iného pracovného hárka v programe Excel, ktoré nie sú v tom istom dokumente. Na tento účel stačí pridať názov dokumentu k vyššie uvedenému odkazu na bunku. Toto je označené hranatými zátvorkami („[“ alebo „]“).
Príklad:
= [Document1] Pôvod! G9
Tento variant použijete, ak je otvorený druhý priečinok s názvom "Document1". Ak dokument nie je otvorený, použite cestu, kam je uložený.
Príklad:
= C: \ súbory \ názov priečinka \ [dokument1] pôvod! G9
Prepojte odkazy na bunky pomocou myši
Odkazy na bunky iných dokumentov nemusíte zadávať ani ručne; bunku môžete prepojiť pomocou myši:
Ak to chcete urobiť, otvorte oba dokumenty, ktoré potrebujete.
Kliknite na cieľovú bunku a do bunky zadajte znamienko rovnosti.
Potom prepnite na ďalší dokument a kliknite na bunku, ktorej hodnotu potrebujete. Stlačte kláves Enter.
Nakoniec skontrolujte, či Excel nastavil správnu referenciu - pri aktivácii cieľovej bunky sa znova zobrazí vo vstupnom riadku.
Chyba referencie bunky
Ak nedáte programu Excel jasné príkazy, môže dôjsť k referenčnej chybe. Bunka môže mať vždy iba jednu bunku ako referenčnú hodnotu. Ak to tak nie je, s najväčšou pravdepodobnosťou došlo k chybe, keď bola oblasť označená myšou.
K referenčnej chybe dochádza aj vtedy, ak bol odkaz na bunku zadaný do iného pracovného hárka, ale tento hárok bol odstránený alebo premenovaný. Porovnateľné s doručením na adresu, počet pôžičiek na pošte by potom už nebol správny.