Práce s propojenými tabulkami v aplikaci Microsoft Excel

Při provádění určitých úloh v aplikaci Excel se někdy musíte zabývat několika tabulkami, které jsou také příbuzné. To znamená, že data z jedné tabulky jsou přitahována do ostatních a při změně jsou hodnoty ve všech propojených tabulkových rozmezích přepočítány.

Propojené tabulky jsou velmi vhodné pro zpracování velkého množství informací. Všechny informace v jedné tabulce, kromě toho, pokud není homogenní, není příliš výhodné. S podobnými předměty je obtížné pracovat a hledat je. Tento problém je určen pouze k odstranění souvisejících tabulek, jejichž informace jsou distribuovány, ale zároveň jsou vzájemně propojeny. Spojité tabulkové rozsahy mohou být umístěny nejen v jednom listu nebo v jedné knize, ale mohou být také umístěny v samostatných knihách (souborech). Poslední dvě možnosti se nejčastěji používají v praxi, neboť účelem této technologie je pouze zbavit se shromažďování dat a jejich nahromadění na jednu stránku zásadně nevyřeší problém. Zjistěte, jak vytvořit a jak pracovat s tímto druhem správy dat.

Vytváření propojených tabulek

Nejdříve se zamyslíme nad tím, jakým způsobem je možné vytvořit propojení mezi různými tabulkovými rozsahy.

Metoda 1: Přímé vazby tabulek podle vzorce

Nejjednodušší způsob, jak propojit data, je použít vzorce, které odkazují na jiné tabulkové rozsahy. Říká se tomu přímá vazba. Tato metoda je intuitivně srozumitelná, protože s tím se vazba provádí téměř přesně jako vytváření odkazů na data v jediném poli tabulky.

Podívejme se, jak příklad může vytvořit vazbu přímou vazbou. Na dvou listech máme dvě stoly. Na jednom stole se mzdy vypočítají pomocí vzorce vynásobením míry zaměstnanců jediným koeficientem pro všechny.

Na druhém listu je tabulkový rozsah, ve kterém je seznam zaměstnanců s jejich platy. Seznam zaměstnanců v obou případech je uveden v jednom pořadí.

Je třeba učinit tak, aby údaje o rychlostech z druhého listu byly čerpány do odpovídajících buněk prvního.

1. Na prvním listu vyberte první buňku sloupce "Bet" . Dali jsme do něj znamení "=" . Dále klikněte na štítek "List 2" , který se nachází na levé straně rozhraní aplikace Excel nad stavovým řádkem.
2. Přesune se do druhé oblasti dokumentu. Klikneme na první buňku ve sloupci Bidding . Pak klepnutím na tlačítko Enter na klávesnici zadáte data do buňky, kde byl předtím nastaven rovnocenný znak.
3. Potom dojde k automatickému přechodu na první list. Jak můžete vidět, hodnota sazby prvního zaměstnance z druhého stolu je vtažena do odpovídající buňky. Nastavením kurzor na buňce obsahující sázku vidíme, že obvyklý vzorec se používá k zobrazení dat na obrazovce. Ale před souřadnicemi buňky, ze které jsou data vyvedena , se označuje výraz "Sheet2!" Stands , který označuje název oblasti dokumentu, kde jsou umístěny. Obecný vzorec v našem případě vypadá takto:

   =Лист2!B2

4. Nyní je třeba předat údaje o sazbách všech ostatních zaměstnanců podniku. Samozřejmě, to lze provést stejným způsobem, jakým jsme dokončili úkol pro prvního zaměstnance, ale vzhledem k tomu, že oba seznamy zaměstnanců jsou umístěny ve stejném pořadí, může být úloha velmi zjednodušena a urychlena jeho řešení. Toho lze jednoduše provést kopírováním vzorce do níže uvedeného rozmezí. Vzhledem k tomu, že odkazy v aplikaci Excel jsou ve výchozím nastavení relativní, při kopírování jsou hodnoty posunuty, což je to, co potřebujeme. Samotný kopírovací postup lze provést pomocí značky pro vyplnění.

   Vložte kurzor do pravé dolní části prvku vzorcem. Poté by měl být kurzor přeměněn na značku plnění ve formě černého kříže. Zacvakneme levým tlačítkem myši a přetáhněte kurzor na konec sloupce.

5. Všechna data z podobného sloupce v listu 2 byla vytažena do tabulky na listu 1 . Pokud změníte údaje v listu 2, automaticky se změní na první.

Metoda 2: Použití odkazu INDEX-MAP

Ale co když seznam zaměstnanců v tabulkových polích není ve stejném pořadí? V tomto případě, jak již bylo zmíněno dříve, jednou z možností je vytvořit vazbu mezi každou buňkou, která má být spojena ručně. Ale to je vhodné pouze pro malé stoly. Pro rozsáhlé rozsahy bude tato možnost v nejlepším případě trvat velmi dlouho, než bude provedena, a v nejhorším případě - v praxi to vůbec nebude možné. Ale vy můžete tento problém vyřešit s pomocí spousty operátorů INDEX - POSITION . Podívejme se, jak to lze provést propojením dat v tabulkových rozmezích diskutovaných v předchozí metodě.

1. Vyberte první prvek sloupce "Bet" . Přejděte na Průvodce funkcí klepnutím na ikonu "Vložit funkci" .
2. V Průvodci funkcí ve skupině "Odkazy a pole" najdeme a vybíráme název "INDEX" .
3. Tento operátor má dvě formy: formulář pro práci s poli a odkaz. V našem případě je zapotřebí první volba, takže v dalším okně výběru formuláře, které se otevře, vyberte ji a klikněte na tlačítko "OK" .
4. Bylo spuštěno okno s argumenty INDEXu . Úkolem této funkce je vypsat hodnotu, která je ve zvoleném rozsahu v řádku se zadaným číslem. Obecný vzorec operátora INDEX je následující:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   "Array" je argument obsahující adresu rozsahu, ze kterého získáme informace podle čísla zadaného řetězce.

   "Číslo řádku" je argument, který je číslem tohoto řádku. Je důležité vědět, že číslo řádku by nemělo být určeno relativně k celému dokumentu, ale pouze vzhledem k vybranému poli.

   "Číslo sloupce" je argument, který je nepovinný. Abychom konkrétně vyřešili náš problém, nebudeme jej používat, a proto není nutné popisovat jeho podstatu samostatně.

   Kurzor vložíme do pole "Array" . Poté přejděte na list 2 a podržením levého tlačítka myši vyberte celý obsah sloupce "Bet" .

5. Po zobrazení souřadnic v okně operátora vložte kurzor do pole "Číslo řádku" . Tento argument vytvoříme pomocí operátoru LIST . Proto klikněte na trojúhelník, který se nachází vlevo od řádku funkce. Zobrazí se seznam posledních operátorů. Najdete-li mezi nimi jméno "SEARCH" , můžete kliknout na něj. V opačném případě klikněte na poslední položku v seznamu - "Další funkce ..." .
6. Otevře se standardní okno Průvodce . Přeneseme to do stejné skupiny "Odkazy a pole". V tomto okamžiku v seznamu vyberte položku "SEARCH" . Klikněte na tlačítko "OK" .
7. Aktivuje se okno argumentů OPERATOR . Zadaná funkce je určena k zobrazení čísla hodnoty v určitém poli podle názvu. Díky této funkci vypočítáme číslo řádku konkrétní hodnoty pro funkci INDEX . Syntaxe MATCH je následující:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

   "Požadovaná hodnota" je argument obsahující název nebo adresu buňky vnějšího rozsahu, ve kterém je umístěn. Je to pozice tohoto názvu v cílovém rozsahu a měla by být vypočtena. V našem případě bude prvním argumentem odkaz na buňky v listu 1 , ve kterém jsou umístěna jména zaměstnanců.

   "Skenované pole" je argument, který je odkazem na pole, ve kterém je vyhledávaná hodnota určena k určení její polohy. U nás tuto roli vykoná adresu sloupce " Jméno" na listu 2 .

   "Typ shody" je argument, který je volitelný, ale na rozdíl od předchozího příkazu bude tento volitelný argument zapotřebí. Označuje, jak bude operátor odpovídat vyhledávací hodnotě pole. Tento argument může mít jednu ze tří hodnot: -1 ; 0 ; 1 . U neordovaných polí zvolte možnost "0" . Tato možnost je vhodná pro náš případ.

   Takže začneme vyplňovat pole oken argumentů. V poli "Hodnota vyhledávání" vložíme kurzor, klikneme na první buňku sloupce "Jméno" na listu 1 .

8. Po zobrazení souřadnic nastavíme kurzor do pole "Scanned array" a přejdeme na štítek "Sheet 2" , který se nachází v dolní části okna aplikace Excel nad stavovým řádkem. Slepte levým tlačítkem myši a pomocí kurzoru vyberte všechny buňky ve sloupci "Název" .
9. Po zobrazení jejich souřadnic v poli "Scanned array" přejděte do pole "Matching type" a nastavte číslo "0" z klávesnice. Poté se znovu vrátíme do pole "Zobrazované pole" . Záleží na tom, že budeme kopírovat vzorec, jak jsme udělali v předchozí metodě. Adresy se posunou, ale potřebujeme opravit souřadnice pole, které kontrolujeme. Neměl by se posunout. Vyberte souřadnice pomocí kurzoru a stiskněte funkční klávesu F4 . Jak je vidět, znaménko dolaru se objevilo před souřadnicemi, což znamená, že odkaz od relativní k absolutní. Poté klikněte na tlačítko "OK" .
10. Výsledek se zobrazí v první buňce sloupce "Bet" . Před kopírováním však potřebujeme opravit ještě jednu oblast, tedy první argument funkce INDEX . Chcete-li to provést, vyberte prvek sloupce obsahující vzorec a přesuňte se do řádku vzorce. Vyberte první argument operátoru INDEX ( B2: B7 ) a klikněte na tlačítko F4 . Jak vidíte, značka dolaru se objevila poblíž vybraných souřadnic. Klepněte na tlačítko Enter . Vzorec obecně vzal následující formu:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Nyní můžete kopírovat pomocí držadla naplnění. Říkáme tomu stejnému způsobu, jak jsme o tom mluvili dříve, a rozšíříme jej na konec rozsahu stolu.
12. Jak vidíte, navzdory skutečnosti, že pořadí řádků dvou propojených tabulek se nezhoduje, jsou všechny hodnoty zpřísněny podle příjmení zaměstnanců. Toho bylo dosaženo pomocí kombinace operátorů INDEX - POSITION .

Čtěte také:
Funkce INDEX v aplikaci Excel
Funkce EXPLORATION v aplikaci Excel

Metoda 3: Provedení matematických operací na souvisejících datech

Přímá vazba dat je také dobrá v tom, že umožňuje zobrazit nejen hodnoty zobrazované v jiných tabulkových rozmezích v jedné z tabulek, ale také provádět s nimi různé matematické operace (sčítání, dělení, odečítání, násobení atd.).

Uvidíme, jak se to děje v praxi. Udělejme to tak, že na listu 3 budou zobrazeny obecné údaje o mzdách v podniku bez rozpisu zaměstnanců. Pro tento účel budou personální sazby vytaženy z listu 2 , přidány (pomocí funkce SUM ) a násobeny koeficientem pomocí vzorce.

1. Vyberte buňku, na které se zobrazí výpočet mzdové hodnoty na listu 3 . Klikněte na tlačítko "Vložit funkci" .
2. Měli byste spustit okno Průvodce . Přejdeme do skupiny "Matematická" a tam vybereme název "SUMM" . Poté klikněte na tlačítko "OK" .
3. Funkce SUM je přesunuta do okna argumentu, které se používá k výpočtu součtu vybraných čísel. Má následující syntaxi:

   =СУММ(число1;число2;…)

   Pole v okně odpovídají argumentům zadané funkce. I když jejich počet může dosáhnout 255 kusů, ale pro náš účel stačí jen jeden. Kurzor vložíme do pole "Number1" . Klikněte na štítek "List 2" nad stavovým řádkem.

4. Po přesunutí do požadované části knihy vyberte sloupec, který se má sčítat. Děláme to kurzorem, podržením levého tlačítka myši. Jak vidíte, souřadnice pole vybrané oblasti se okamžitě zobrazí v poli okna s argumenty. Poté klikněte na tlačítko "OK" .
5. Poté se automaticky přesuneme do listu 1 . Jak vidíte, celková výše mzdy zaměstnanců je již zobrazena v odpovídajícím prvku.
6. Ale to není všechno. Jak si pamatujeme, plat se vypočítá vynásobením sazbou koeficientem. Proto znovu vybereme buňku, ve které je součet umístěn. Poté přejděte na řádek vzorců. Násobící znaménko ( * ) přidáme do vzorce a klikneme na prvek, ve kterém je index koeficientu umístěn. Pro provedení výpočtu klikněte na klávesu Enter na klávesnici. Jak můžete vidět, program vypočítává celkovou mzdu pro podnik.
7. Vraťte se do listu 2 a změňte sazbu každého zaměstnance.
8. Poté se vrátíme zpět na stránku s celkovou částkou. Jak vidíte, kvůli změnám v propojené tabulce byl výsledek celkového platu automaticky přepočítán.

Metoda 4: speciální vložka

Tabulkové pole můžete také propojit s aplikací Excel pomocí speciální vložky.

1. Vyberte hodnoty, které chcete utahovat v jiné tabulce. V našem případě to je rozsah sloupce "Sázka" na listu 2 . Klepněte na vybraný fragment pravým tlačítkem myši. V otevřeném seznamu vyberte položku "Kopírovat" . Alternativní kombinací je Ctrl + C. Potom přejděte do listu 1 .
2. Přesuňte se do požadované oblasti knihy a vyberte buňky, ve kterých chcete hodnoty vytáhnout. V našem případě je to sloupec "Bet" . Klepněte na vybraný fragment pravým tlačítkem myši. V kontextovém menu v poli Nástroje vkládání parametrů klikněte na ikonu "Vložit propojení" .

   Existuje také alternativa. Mimochodem, je to jediná pro starší verze aplikace Excel. V kontextové nabídce umístíme kurzor nad položku "Speciální vložení" . V další nabídce, která se otevře, vyberte položku se stejným názvem.

3. Poté se otevře okno speciální vložky. Klikněte na tlačítko "Vložit propojení" v levém dolním rohu buňky.
4. Jakoukoli volbu zvolíte, hodnoty z jednoho tabulkového pole budou vloženy do druhé. Změníte-li data ve zdroji, automaticky se změní v vloženém rozsahu.

Lekce: Speciální vložka v aplikaci Excel

Metoda 5: vztah mezi tabulkami v několika knihách

Kromě toho můžete uspořádat spojení mezi tabulkovými oblastmi v různých knihách. Používá se speciální nástroj pro vložení. Akce budou přesně stejná jako akce, které jsme viděli v předchozí metodě, kromě toho, že nebudete muset procházet mezi částmi stejné knihy, ale mezi soubory. Samozřejmě, všechny související knihy by měly být otevřeny současně.

1. Vyberte rozsah dat, které chcete přenést do jiné knihy. Klepněte na něj pravým tlačítkem myši a v otevřené nabídce vyberte položku "Kopírovat" .
2. Pak se přesučíme do knihy, ve které budou tyto údaje vloženy. Vyberte požadovaný rozsah. Klikněte pravým tlačítkem myši. V místní nabídce ve skupině "Možnosti vložení" vyberte položku "Vložit odkaz" .
3. Poté budou hodnoty vloženy. Změníte-li data ve zdrojové knize, bude tabulkový matice ze sešitu automaticky vytahovat. A není nutné, aby obě knihy byly otevřené. Stačí, když otevřete pouze jeden sešit a automaticky vytahuje data z uzavřeného propojeného dokumentu, pokud byla dříve změněna.

Je však třeba poznamenat, že v tomto případě bude vložení provedeno ve formě nezměnitelného pole. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные» . Щелкаем по значку «Изменить связи» , который расположен на ленте в блоке инструментов «Подключения» . Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь» .
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи» .
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать» . Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C .
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения» , которая размещена в группе инструментов «Параметры вставки» .
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

• ← Metody pro porovnávání tabulek v aplikaci Microsoft Excel
• Náměstí v aplikaci Microsoft Excel →