Práca s prepojenými tabuľkami v programe Microsoft Excel

Pri vykonávaní určitých úloh v programe Excel sa niekedy musíte vyrovnať s niekoľkými tabuľkami, ktoré sú tiež spojené. To znamená, že dáta z jednej tabuľky sú vytiahnuté do ostatných a keď sú zmenené, prepočítajú sa hodnoty vo všetkých prepojených tabuľkových rozsahoch.

Prepojené tabuľky sú veľmi vhodné na spracovanie veľkého množstva informácií. Všetky informácie v jednej tabuľke okrem toho, ak nie sú homogénne, nie sú veľmi výhodné. S podobnými objektmi je ťažké pracovať a hľadať ich. Tento problém je určený len na odstránenie súvisiacich tabuliek, medzi ktorými sú informácie distribuované, ale zároveň sú vzájomne prepojené. Prepojené rozsahy tabuľky môžu byť umiestnené nielen v rámci jedného hárku alebo jednej knihy, ale môžu byť tiež umiestnené v samostatných knihách (súboroch). Posledné dve možnosti sa v praxi používajú najčastejšie, pretože účelom tejto technológie je jednoducho odstrániť z hromadenia údajov a ich nahromadenie na jednu stránku zásadne nerieši problém. Dozvieme sa, ako vytvoriť a ako pracovať s týmto druhom správy dát.

Vytváranie prepojených tabuliek

Po prvé, budeme sa zaoberať otázkou, akým spôsobom je možné vytvoriť spojenie medzi rôznymi tabuľkovými rozsahmi.

Metóda 1: Priama väzba tabuliek podľa vzorca

Najjednoduchší spôsob na prepojenie údajov je použiť vzorce, ktoré odkazujú na iné rozsahy tabuľky. Nazýva sa to priama väzba. Táto metóda je intuitívne pochopiteľná, pretože s ňou sa väzba vykonáva takmer presne ako vytvorenie odkazov na dáta v jednom poli tabuľky.

Pozrime sa, ako príklad môže vytvoriť odkaz priamou väzbou. Na dvoch listoch máme dva stoly. Na jednej tabuľke sa výpočet miezd vypočíta pomocou vzorca vynásobením miery zamestnancov jednotným koeficientom pre všetkých.

Na druhom hárku je tabuľkový rozsah, v ktorom je zoznam zamestnancov s ich platmi. Zoznam zamestnancov v obidvoch prípadoch je uvedený v jednom poradí.

Je potrebné urobiť tak, aby údaje o rýchlostiach z druhého listu boli ťahané do zodpovedajúcich buniek prvej.

1. Na prvom hárku vyberte prvú bunku v stĺpci "Bet" . Vložili sme do neho znamenie "=" . Ďalej kliknite na štítok "Hárok 2" , ktorý sa nachádza na ľavej strane rozhrania programu Excel nad stavovým riadkom.
2. Pohybuje sa na druhú oblasť dokumentu. Klikneme na prvú bunku v stĺpci Ponúkanie . Potom kliknite na tlačidlo Enter na klávesnici a zadajte údaje do bunky, kde bol predtým nastavený rovnaký znak.
3. Potom nastane automatický prechod na prvý hárok. Ako vidíte, hodnota kurzu prvého zamestnanca z druhej tabuľky sa vtiahne do príslušnej bunky. Ak nastavíme kurzor na bunku obsahujúcu stávku, uvidíme, že na zobrazenie údajov na obrazovke sa používa obvyklý vzorec. Ale pred súradnicami bunky, z ktorej sú dáta výstupné, sa označuje výraz "Sheet2!" Stands , ktorý označuje názov oblasti dokumentu, na ktorom sú umiestnené. Všeobecný vzorec v našom prípade vyzerá takto:

   =Лист2!B2

4. Teraz je potrebné previesť údaje o sadzbách všetkých ostatných zamestnancov podniku. Samozrejme, toto sa dá urobiť rovnakým spôsobom, ako sme dokončili úlohu pre prvého zamestnanca, ale vzhľadom na to, že obe zoznamy zamestnancov sú umiestnené v rovnakom poradí, úloha môže byť značne zjednodušená a urýchliť jeho riešenie. Toto sa dá jednoducho urobiť kopírovaním vzorca do rozsahu nižšie. Vzhľadom na to, že referencie v programe Excel sú predvolene relatívne, pri ich kopírovaní sa hodnoty posúvajú, čo je to, čo potrebujeme. Samotný postup kopírovania možno vykonať pomocou značky naplnenia.

   Takže umiestnite kurzor do pravého dolného rohu elementu pomocou vzorca. Potom sa má kurzor premeniť na značku plnenia vo forme čierneho kríža. Zvierame ľavé tlačidlo myši a presuňte kurzor na spodnú časť stĺpca.

5. Všetky údaje z podobného stĺpca na hárku 2 boli vytiahnuté do tabuľky na liste 1 . Ak zmeníte údaje na hárku 2, automaticky sa zmení na prvú.

Metóda 2: Použitie odkazu INDEX-MAP

Ale čo, ak zoznam zamestnancov v tabuľkových poliach nie je v tom istom poradí? V tomto prípade, ako bolo spomenuté skôr, jednou z možností je vytvorenie spojenia medzi každou bunkou, ktorá má byť prepojená ručne. Ale je to vhodné len pre malé stoly. Pri masívnych rozsahoch bude táto možnosť v najlepšom prípade trvať veľmi dlho, kým bude implementovaná, a v najhoršom prípade - v praxi to nebude možné. Ale tento problém môžete vyriešiť pomocou skupiny operátorov INDEX - POSITION . Pozrime sa, ako to možno urobiť prepojením údajov v tabuľkových rozmedziach, ktoré boli diskutované v predchádzajúcej metóde.

1. Vyberte prvý prvok stĺpca "Stávka" . Prejdite na sprievodcu funkciami kliknutím na ikonu "Vložiť funkciu" .
2. V sprievodcovi funkciami v skupine "Odkazy a matice" nájdeme a vyberieme názov "INDEX" .
3. Tento operátor má dve formy: formulár na prácu s políčkami a odkaz. V našom prípade je potrebná prvá voľba, takže v nasledujúcom okne výberu formulára, ktoré sa otvorí, vyberte ho a kliknite na tlačidlo "OK" .
4. Objavilo sa okno s argumentmi INDEXu . Úlohou tejto funkcie je vygenerovať hodnotu vo zvolenom rozsahu v riadku so zadaným číslom. Všeobecný vzorec operátora INDEX je nasledovný:

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

   "Array" je argument obsahujúci adresu rozsahu, z ktorého budeme načítať informácie podľa čísla zadaného reťazca.

   "Číslo riadku" je argument, ktorý je číslom tohto riadku. Je dôležité vedieť, že číslo riadku by sa malo špecifikovať nie vo vzťahu k celému dokumentu, ale iba vzhľadom na zvolené pole.

   "Číslo stĺpca" je argument, ktorý je nepovinný. Na to, aby sme vyriešili konkrétny problém, nebudeme ho používať, a preto nie je potrebné opisovať jeho podstatu samostatne.

   Kurzor sme umiestnili do poľa "Array" . Potom prejdite na list 2 a podržaním ľavého tlačidla myši vyberte celý obsah stĺpca "Stávka" .

5. Po zobrazení súradníc v okne operátora umiestnite kurzor do poľa "Číslo linky" . Vyjadríme tento argument pomocou operátora LIST . Preto kliknite na trojuholník, ktorý sa nachádza vľavo od riadku funkcie. Zobrazí sa zoznam nedávno použitých operátorov. Ak nájdete medzi nimi názov "SEARCH" , môžete na neho kliknúť. V opačnom prípade kliknite na poslednú položku v zozname - "Ďalšie funkcie ..." .
6. Otvorí sa štandardné okno Sprievodcu . Prejdeme do tej istej skupiny "Odkazy a matice". V tomto okamihu v zozname vyberte položku "SEARCH" . Kliknite na tlačidlo "OK" .
7. Aktivácia okna OPERATOR je aktivovaná . Táto špecifikovaná funkcia slúži na zobrazenie čísla hodnoty v určitom poli podľa názvu. Práve vďaka tejto funkcii vypočítame číslo riadku konkrétnej hodnoty pre funkciu INDEX . Syntax MATCH je nasledujúci:

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

   "Požadovaná hodnota" je argument obsahujúci názov alebo adresu bunky vonkajšieho rozsahu, v ktorom je umiestnená. Je to poloha tohto názvu v cieľovom rozsahu a mala by sa vypočítať. V našom prípade bude prvým argumentom odkaz na bunky na liste 1 , v ktorej sa nachádzajú mená zamestnancov.

   "Naskenované pole" je argument, ktorý je odkazom na pole, v ktorom sa hľadaná hodnota určuje na určenie jej polohy. U nás táto úloha vykoná adresu stĺpca " Name" na hárku 2 .

   "Typ zhody" je argument, ktorý je voliteľný, ale na rozdiel od predchádzajúceho príkazu bude tento voliteľný argument potrebný. Označuje, ako operátor priradí hodnotu vyhľadávania k poľu. Tento argument môže mať jednu z troch hodnôt: -1 ; 0 ; 1 . Pre nezoradené polia zvoľte možnosť "0" . Táto možnosť je vhodná pre náš prípad.

   Takže začneme vyplňovať polia okna s argumentmi. Kurzor sme umiestnili do poľa "Vyhľadávacia hodnota" , kliknite na prvú bunku stĺpca "Názov" na hárku 1 .

8. Po zobrazení súradníc nastavíme kurzor do poľa "Scanned array" a prejdeme na štítok "Sheet 2" , ktorý sa nachádza v spodnej časti okna programu Excel nad stavovým riadkom. Uložte ľavé tlačidlo myši a pomocou kurzorov zvoľte všetky bunky v stĺpci "Názov" .
9. Po zobrazení ich súradníc v poli "Scanned array" prejdite na pole "Matching type" a nastavte číslo "0" z klávesnice. Potom sa opäť vrátime do poľa Zobrazované pole . Záležitosť spočíva v tom, že budeme kopírovať vzorec, ako sme urobili v predchádzajúcej metóde. Adresy sa posunú, ale potrebujeme opraviť súradnice poľa, ktoré kontrolujeme. Nemal by sa posúvať. Pomocou kurzora vyberte súradnice a stlačte funkčné tlačidlo F4 . Ako môžete vidieť, znak dolára sa objavil pred súradnicami, čo znamená, že odkaz z relatívnej na absolútnu. Potom kliknite na tlačidlo "OK" .
10. Výsledok sa zobrazí v prvej bunke stĺpca "Bet" . Pred kopírovaním však potrebujeme opraviť ešte jednu oblasť, a to prvý argument funkcie INDEX . Ak to chcete urobiť, vyberte prvok stĺpca, ktorý obsahuje vzorec, a prejdite na riadok vzorca. Vyberte prvý argument operátora INDEX ( B2: B7 ) a kliknite na tlačidlo F4 . Ako môžete vidieť, značka dolára sa objavila blízko vybraných súradníc. Klikneme na kláves Enter . Vo všeobecnosti vzorec nadobudol nasledujúcu formu:

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

11. Teraz môžete skopírovať pomocou rukoväte. Hovoríme to rovnakým spôsobom ako sme hovorili skôr a rozšírili ho až do konca tabuľky.
12. Ako vidíme, napriek tomu, že poradie riadkov pre obe prepojené tabuľky sa nezhoduje, napriek tomu sú všetky hodnoty sprísnené podľa priezvisk zamestnancov. To bolo dosiahnuté použitím kombinácie operátorov INDEX - POSITION .

Prečítajte si tiež:
Funkcia INDEX v programe Excel
Funkcia EXPLORATION v programe Excel

Metóda 3: Vykonávanie matematických operácií na súvisiacich údajoch

Priama väzba údajov je tiež dobrá v tom, že umožňuje nielen zobrazovanie hodnôt v jednej z tabuliek, ktoré sa zobrazujú v iných tabuľkových rozsahoch, ale aj vykonávanie rôznych matematických operácií s nimi (pridanie, delenie, odčítanie, násobenie atď.).

Pozrime sa, ako sa to v praxi vykonáva. Urobíme tak, aby sa na výkrese 3 zobrazovali všeobecné údaje o mzdách v podniku bez rozpisu zamestnancov. Za týmto účelom sa sadzba personálu vytiahne z listu 2 , pridá sa (pomocou funkcie SUM ) a vynásobí koeficientom pomocou vzorca.

1. Vyberte bunku, na ktorej sa zobrazí výsledok výpočtu miezd v tabuľke 3 . Kliknite na tlačidlo "Vložiť funkciu" .
2. Mali by ste spustiť okno Sprievodcu . Prejdeme do skupiny "Matematická" a vyberieme tam názov "SUMM" . Potom kliknite na tlačidlo "OK" .
3. Funkcia SUM sa presunie do okna argumentu, ktoré sa používa na výpočet súčtu vybratých čísel. Má nasledujúcu syntax:

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

   Polia v okne zodpovedajú argumentom zadanej funkcie. Aj keď ich počet môže dosiahnuť 255 kusov, ale na náš účel stačí len jeden. Kurzor sme umiestnili do poľa "Číslo1" . Kliknite na štítok "Hárok 2" nad stavovým riadkom.

4. Po presunutí do požadovanej časti knihy vyberte stĺpec, ktorý sa má zhrnúť. Urobíme to kurzorom, podržaním ľavého tlačidla myši. Ako vidíte, súradnice vybranej oblasti sa okamžite zobrazia v poli okna s argumentmi. Potom kliknite na tlačidlo "OK" .
5. Potom sa automaticky presunieme do zoznamu 1 . Ako vidíte, celková čiastka mzdy zamestnanca sa už zobrazuje v príslušnom prvku.
6. Ale to nie je všetko. Ako si pamätáme, plat sa vypočíta vynásobením sadzby koeficientom. Preto opäť vyberieme bunku, v ktorej sa nachádza súčet. Potom prejdite na panel vzorcov. Pridávame symbol násobenia ( * ) do vzorca v ňom a potom kliknite na prvok, v ktorom sa nachádza index koeficientov. Ak chcete vykonať výpočet, kliknite na kláves Enter na klávesnici. Ako môžete vidieť, program vypočítal celkový plat pre podnik.
7. Vráťte sa na list 2 a zmeňte mieru akéhokoľvek zamestnanca.
8. Potom sa vrátime na stránku s celkovou sumou. Ako vidíte, kvôli zmenám v prepojenej tabuľke bol výsledok celkového platu automaticky prepočítaný.

Metóda 4: špeciálna vložka

Môžete tiež prepojiť tabuľkové polia do programu Excel pomocou špeciálnej vložky.

1. Vyberte hodnoty, ktoré chcete "utiahnuť" v inej tabuľke. V našom prípade je to rozsah stĺpca "Stávka" na hárku 2 . Kliknite na vybraný fragment pravým tlačidlom myši. V otvorenom zozname vyberte položku "Kopírovať" . Alternatívnou kombináciou je Ctrl + C. Potom prejdite na list 1 .
2. Ak prejdete na požadovanú oblasť knihy, vyberte bunky, v ktorých chcete hodnoty vytiahnuť. V našom prípade je to stĺpec "Bet" . Kliknite na vybraný fragment pravým tlačidlom myši. V kontextovej ponuke v poli Nástroje na vloženie parametrov kliknite na ikonu "Vložiť odkaz" .

   Existuje aj alternatíva. Mimochodom, je to len pre staršie verzie programu Excel. V kontextovej ponuke umiestnime kurzor nad položku "špeciálna prilepka" . V ďalšej ponuke, ktorá sa otvorí, vyberte položku s rovnakým názvom.

3. Potom sa otvorí špeciálne okno. Kliknite na tlačidlo "Vložiť odkaz" v ľavom dolnom rohu bunky.
4. Bez ohľadu na to, čo si zvolíte, hodnoty z jedného tabuľkového poľa sa vložia do druhého. Ak zmeníte údaje v zdroji, automaticky sa zmení aj v vloženom rozsahu.

Lekcia: Špeciálna vložka v programe Excel

Metóda 5: vzťah medzi tabuľkami v niekoľkých knihách

Okrem toho môžete zorganizovať prepojenie medzi tabuľkovými oblasťami v rôznych knihách. Používa sa špeciálny nástroj na vloženie. Činnosti budú presne rovnaké ako tie, ktoré sme videli v predchádzajúcej metóde, okrem toho, že nebudete musieť prechádzať medzi časťami tej istej knihy, ale medzi jednotlivými súbormi. Samozrejme, všetky súvisiace knihy by mali byť otvorené súčasne.

1. Vyberte rozsah údajov, ktoré chcete preniesť do inej knihy. Kliknite na ňu pravým tlačidlom myši a v otvorenej ponuke vyberte položku "Kopírovať" .
2. Potom sa presunieme do knihy, v ktorej budú potrebné tieto údaje vložiť. Vyberte požadovaný rozsah. Kliknite pravým tlačidlom myši. V kontextovej ponuke v skupine "Možnosti vloženia" vyberte položku "Prilepiť prepojenie" .
3. Potom budú vložené hodnoty. Ak zmeníte dáta v zdrojovej knihe, tabuľkový pohľad zo sešitu ich automaticky vytiahne. A nie je potrebné, aby obe knihy boli otvorené pre toto. Stačí otvoriť iba jeden zošit a automaticky vytiahne dáta z uzavretého prepojeného dokumentu, ak už bola zmenená.

Treba však poznamenať, že v tomto prípade sa vloženie uskutoční vo forme nezmeniteľného poľa. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

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

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

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

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

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

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

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

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

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

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

• ← Metódy na porovnávanie tabuliek v programe Microsoft Excel
• Štvorec v programe Microsoft Excel →