Súvisiace tabuľky v programe Excel: Podrobné pokyny

Pri vykonávaní určitých úloh v programe Excel sa niekedy musia vysporiadať s niekoľkými tabuľkami, ktoré sú navzájom spojené. To znamená, že údaje z jednej tabuľky sú dotiahnuté iným a hodnoty vo všetkých súvisiacich tabuľkách sa prepočítajú, keď sa zmenia.

Súvisiace tabuľky sú veľmi vhodné na použitie na zvládnutie veľkého množstva informácií. Umiestnite všetky informácie do jednej tabuľky, okrem toho, ak nie je homogénny, nie veľmi pohodlný. Je ťažké pracovať s takýmito objektmi a hľadať ich. Zadaný problém je určený len na odstránenie súvisiacich tabuliek, informácie, ktoré sú distribuované, ale zároveň sú prepojené. Súvisiace tabuľky môžu byť nielen v rámci jedného listu alebo jednej knihy, ale tiež sa nachádzajú v samostatných knihách (súboroch). Posledné dve možnosti v praxi sa najčastejšie používajú, pretože účelom tejto technológie je len preč od akumulácie údajov a ich kývnutie na jednej stránke nerieši zásadne. Naučte sa, ako vytvoriť a ako pracovať s takýmto typom správy dát.

Vytvorenie súvisiacich tabuliek

V prvom rade sa zameriame na otázku, akým spôsobom je možné vytvoriť spojenie medzi rôznymi tabuľkami.

Metóda 1: Priame viazacie tabuľky Vzorec

Najjednoduchší spôsob, ako viazať dáta, je použitie vzorcov, v ktorých sú odkazy na iné tabuľky. Nazýva sa priama väzba. Táto metóda je intuitívna, pretože keď sa viaže, takmer rovnako ako vytváranie odkazov na údaje v jednom poli tabuľky.

Pozrime sa, ako môžete vytvoriť komunikáciu priamou väzbou. Máme dve tabuľky na dvoch listoch. Na tej istej tabuľke sa plat vypočíta pomocou vzorca vynásobením sadzieb zamestnancov za jeden koeficient.

Na druhom hárku je rozsah tabuľky, v ktorom je zoznam zamestnancov s ich platom. Zoznam zamestnancov v oboch prípadoch je uvedený v jednej objednávke.

Je potrebné, aby sa tieto údaje o stávkach z druhého listu dotiahli do zodpovedajúcich buniek prvého.

1. Na prvom hárku prideľujeme prvú bunku stĺpca "BET". Dali sme do neho označenie "=". Ďalej kliknite na štítok "list 2", ktorý je umiestnený na ľavej časti rozhrania programu Excel cez stavový riadok.



2. V druhej oblasti dokumentu je pohyb. Kliknite na prvú bunku v stĺpci "Bet". Potom kliknutím na tlačidlo ENTER na klávesnici zadajte údaje do bunky, v ktorom bol predtým nainštalovaný "rovnaký" znak.



3. Potom existuje automatický prechod na prvý list. Ako vidíme, hodnota prvého zamestnanca z druhej tabuľky sa vytiahne do príslušnej bunky. Inštaláciou kurzora na bunku obsahujúcu stávku, vidíme, že zvyčajný vzorec sa používa na zobrazenie údajov na obrazovke. Ale pred súradnicami bunky, z miesta, kde sú údaje na výstupe, existuje výraz "zoznam2!", Ktorý označuje názov oblasti dokumentov, kde sa nachádzajú. Všeobecný vzorec v našom prípade vyzerá takto:

   = Zoznam2! B2



4. Teraz musíte previesť údaje o sadzbách všetkých ostatných zamestnancov podniku. Samozrejme, to možno urobiť rovnakým spôsobom, že sme splnili úlohu pre prvého zamestnanca, ale vzhľadom na to, že zoznamy zamestnancov sa nachádzajú v tom istom poradí, úloha je možné výrazne zjednodušiť a urýchliť svojím rozhodnutím. To sa dá urobiť jednoduchým kopírovaním vzorec do rozsahu nižšie. Vzhľadom na skutočnosť, že odkazy na program Excel sú relatívne, pri kopírovaní ich hodnôt sa posúvajú hodnoty, ktoré potrebujeme. Samotný postup kopírovania je možné vykonať pomocou výplňového markera.

   Takže, vložíme kurzor do dolnej pravej oblasti prvku so vzorcom. Potom musí kurzor previesť na výplňový marker vo forme čierneho kríža. Vykonávame svorku ľavého tlačidla myši a vytiahneme kurzor na číslo stĺpca.



5. Všetky údaje z podobného stĺpca na hárku 2 boli vytiahnuté do tabuľky na hárku 1. Keď sa zmeny údajov zmení na list 2, automaticky sa zmenia na prvom mieste.

METÓDA 2: POUŽÍVAŤ POUŽÍVAŤ NÁKLADY OPRAVY OPRAVY - Hľadať

Ale čo robiť, ak zoznam zamestnancov v tabuľkových poliach nie je umiestnený v rovnakom poradí? V tomto prípade, ako už bolo uvedené, jedna z možností je inštalovať vzťah medzi každým z týchto buniek, ktoré by mali byť ručne spojené. Ale je vhodné okrem malých tabuliek. V prípade masívnych rozsahov bude táto možnosť v najlepšom prípade trvať veľa času na implementácii a v najhoršom prípade bude to všeobecne nereálne. Ale tento problém možno vyriešiť pomocou banda indexu operátorov - vyhľadávanie. Pozrime sa, ako to môže byť vykonané tak, aby sa údaje v tabuľkách, o ktorých bola konverzácia v predchádzajúcej metóde.

1. Zvýrazňujeme prvý prvok stĺpca "BET". Prejdite na Sprievodcu funkcií kliknutím na ikonu "Vložiť funkciu".



2. V sprievodcovi funkcií v skupine "Odkazy a polia" nájdeme a prideľujeme názov "Index".



3. Tento operátor má dve formy: formulár pre prácu s policami a odkazom. V našom prípade sa vyžaduje prvá možnosť, takže v nasledujúcom okne výberu formulára, ktoré otvorí, vyberte ho a kliknite na tlačidlo "OK".



4. Indikátor argumentov prevádzkovateľa začnú bežať. Úlohou zadanej funkcie je výstup hodnoty umiestnenej v zvolenom rozsahu v riadku so zadaným číslom. Všeobecný index operátora vzorec Takýto:

   = Index (pole, číslo_name; [number_stolbits])

   "Array" je argument obsahujúci rozsah rozsahu, z ktorého získame informácie o čísle zadaného riadku.

   "Číslo riadka" je argument, ktorý je číslo tohto riadku. Je dôležité vedieť, že číslo riadku by malo byť špecifikované nie je relatívne k celému dokumentu, ale iba vzhľadom na pridelené pole.

   "Počet stĺpca" je argument, ktorý je voliteľný. Ak chcete vyriešiť konkrétne našej úlohe, nebudeme ho používať, a preto nie je potrebné ho opísať samostatne.

   Vložíme kurzor do poľa "Array". Potom prejdite na list 2 a držte ľavé tlačidlo myši, vyberte celý obsah stĺpca "Rate".



5. Po zobrazení súradníc v okne operátora vložíme kurzor do poľa "Číslo riadka". Tento argument vyberáme pomocou operátora vyhľadávania. Preto kliknite na trojuholník, ktorý sa nachádza na ľavej strane funkčného reťazca. Otvorí sa zoznam novo používaných operátorov. Ak nájdete meno "Hľadať spoločnosť" medzi nimi, môžete ho kliknúť. V opačnom prípade kliknite na najnovší bod zoznamu - "Iné funkcie ...".



6. Spustí sa štandardné okno Sprievodca okna. Choďte na to v rovnakej skupine "Odkazy a polia". Tentoraz v zozname vyberte položku "Hľadať spoločnosť". Vykonajte kliknutím na tlačidlo "OK".



7. Vykonáva sa aktivácia argumentov argumentov. Zadaná funkcia je určená na výstup hodnoty hodnoty v konkrétnom poli jeho menom. Je to vďaka tejto funkcii, ktorú vypočítame číslo reťazca špecifickej hodnoty pre funkciu funkcie. Syntax vyhľadávacej rady je prezentovaná:

   = Searchová doska (Search_name; Viewing__NaSIVE; [TYPY_STATION])

   "Požadovaný" je argument obsahujúci názov alebo adresu bunky sortimentu tretej strany, v ktorom sa nachádza. Je to pozícia tohto názvu v cieľovom rozsahu a mala by sa vypočítať. V našom prípade sa úloha prvého argumentu odkazuje na bunky na hárku 1, v ktorom sa zamestnanci nachádzajú.

   "Zoznamové pole" je argument, ktorý je odkazom na pole, ktoré vykonáva vyhľadávanie zadanej hodnoty na určenie jeho polohy. Túto úlohu budeme mať vykonať adresu stĺpca "Názov" na hárku 2.

   "Typ porovnania" - argument, ktorý je voliteľný, ale na rozdiel od predchádzajúceho operátora, bude potrebné toto voliteľné argument. Označuje, ako sa zhoduje s operátorom, je požadovaná hodnota s polevou. Tento argument môže mať jednu z troch hodnôt: -1; 0; 1. Pre neusporiadané polia vyberte možnosť "0". Táto možnosť je vhodná pre náš prípad.

   Pokračujte v plnení polí okna argumentu. Vložíme kurzor do poľa "Hodnota hmoty", kliknite na stĺpec prvého bunky "Názov" na hárku 1.



8. Po zobrazení súradníc nastavte kurzor do poľa "Zoznam Massive" a prejdite na štítok "List 2", ktorý sa nachádza v spodnej časti okna programu Excel nad stavový riadok. Clement ľavým tlačidlom myši a zvýraznite kurzor všetky bunky stĺpca "Názov".



9. Po zobrazení ich súradníc v poli "Uvedenie Massive", prejdite na pole "Typ mapovania" a nastavte číslo "0" z klávesnice. Potom sa opäť vrátime na pole "Pri pohľade cez pole". Faktom je, že vykonáme kopírovanie vzorec, ako sme urobili v predchádzajúcej metóde. Bude to posun adresy, ale tu súradnice pohľadu poľa, musíme zabezpečiť. Nemal by sa posunúť. Zvýrazňujeme súradnice s kurzorom a kliknite na funkčné tlačidlo F4. Ako vidíte, znamenie dolára sa objavil pred súradnicami, čo znamená, že odkaz z relatívnej sa zmenil na absolútne. Potom kliknite na tlačidlo "OK".



10. Výsledok sa zobrazí v prvej bunke stĺpca "BET". Ale pred kopírovaním musíme opraviť inú oblasť, konkrétne prvý index funkcie argumentu. Ak to chcete urobiť, vyberte stĺpový prvok, ktorý obsahuje vzorec a prejdite na reťazec vzorca. Prideliť prvý argument indexu operátora (B2: B7) a kliknite na tlačidlo F4. Ako vidíte, znak dolára sa objavil v blízkosti vybraných súradníc. Kliknite na tlačidlo ENTER. Všeobecne platí, že vzorec vzal nasledujúci formulár:

    = Index (list2! $ B $ 2: $ B $ 7; Hľadajúca doska (List1! A4; zoznam2! $ A $ 2: $ A $ 7; 0))



11. Teraz môžete kopírovať pomocou výplňového markera. Nazývame to rovnakým spôsobom, ktorý sme hovorili skôr a natiahli sme na koniec tabuľkového rozsahu.



12. Ako môžete vidieť, napriek tomu, že poradie reťazcov v dvoch súvisiacich tabuľkách sa nezhoduje, napriek tomu sú všetky hodnoty dotiahnuté podľa mien pracovníkov. To bolo dosiahnuté vďaka použitiu kombinácie vyhľadávačov prevádzkovateľov.

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

Súbory Tabuľky v programe Excel môžu tiež používať špeciálne vkladanie.

1. Vyberte hodnoty, ktoré chcete "dotiahnuť" inú tabuľku. V našom prípade je to rozsah stĺpcov "BET" na hárku 2. Kliknite na vyhradený fragment pravým tlačidlom myši. V zozname, ktorý sa otvorí, vyberte položku "Kopírovať". Alternatívnou kombináciou je kombinácia Key CTRL + C. Potom sa pohybujeme na list 1.



2. Presunutie do oblasti knihy, ktorú potrebujete, prideľte bunky, v ktorých budú musieť byť potrebné utiahnuté. V našom prípade je to stĺpec "BID". Kliknite na vyhradený fragment pravým tlačidlom myši. V kontextovom menu v paneli nástrojov "Vložiť parametre" kliknite na ikonu "Vložiť komunikáciu".

   

   Existuje aj alternatíva. Mimochodom, je jediná pre staršie verzie programu Excel. V kontextovom menu prinášame kurzor na položku "Špeciálna vložka". V ďalšom menu, ktorá sa otvorí, vyberte polohu s rovnakým názvom.



3. Po tom sa otvorí okno Špeciálne vložky. Kliknite na tlačidlo "Vložiť komunikáciu" v ľavom dolnom rohu bunky.



4. Bez ohľadu na možnosť, ktorú si vyberiete, hodnoty z jednej tabuľky poľa budú vložené do inej. Pri zmene údajov v zdroji sa automaticky zmenia v vloženom rozsahu.

Lekcia: Špeciálna vložka v Excel

Metóda 5: Komunikácia medzi tabuľkami v niekoľkých knihách

Okrem toho môžete organizovať prepojenie medzi oblasťami tabuľky v rôznych knihách. To používa špeciálny vložkový nástroj. Akcie budú absolútne podobné tým, ktoré sme uvažovali v predchádzajúcej metóde, s výnimkou, že navigácia počas vzorcov nebude mať medzi oblasťami jednej knihy, ale medzi súbormi. Samozrejme, všetky súvisiace knihy by mali byť otvorené.

1. Vyberte rozsah dát, ktorý sa má preniesť do inej knihy. Kliknite na tlačidlo IT doprava a vyberte polohu "Kopírovať" v otvorenej ponuke.



2. Potom sa presunieme na knihu, v ktorej by sa mali vložiť tieto údaje. Vyberte požadovaný rozsah. Kliknite pravým tlačidlom myši. V kontextovom menu v skupine "Vložiť nastavenia" vyberte položku "Vložiť komunikáciu".



3. Potom budú hodnoty vložené. Pri zmene údajov v zdrojovej knihe sa automaticky dotiahnete pole tabuľky z pracovnej knihy. A nie je vôbec potrebné, aby sa zabezpečilo, že obe knihy sú otvorené. Stačí otvoriť jeden iba zošit, a automaticky sa dostane dáta z uzavretého príslušného dokumentu, ak v ňom boli predchádzajúce zmeny.

Treba však poznamenať, že v tomto prípade sa vloží v rámci nezmeneného poľa. Pri pokuse o zmenu ľubovoľnej bunky s vloženými údajmi bude správa naplnená informovaním o neschopnosti.

Zmeny v takomto poli spojenej s inou knihou môžu prerušiť iba pripojenie.

Názov prestávky medzi tabuľkami

Niekedy je potrebné prerušiť spojenie medzi tabuľkami. Dôvodom môže byť vyššie opísané, ak chcete zmeniť pole vložené z inej knihy a jednoducho relaxáciu používateľa, aby sa údaje v tej istej tabuľke automaticky aktualizovali od druhej.

Metóda 1: Komunikačné prestávky medzi knihami

Prelomiť spojenie medzi knihami vo všetkých bunkách, vykonaním vlastne jednej operácie. V tomto prípade zostanú údaje v bunkách, ale už budú statické neaktualizované hodnoty, ktoré nezávisia od iných dokumentov.

1. V knihe, v ktorej sú hodnoty z iných súborov dotiahnuté, prejdite na kartu DATA. Kliknite na ikonu "Zmeniť odkazy", ktorá sa nachádza na páske v paneli s nástrojmi "Connection". Treba poznamenať, že ak aktuálna kniha neobsahuje pripojenia s inými súbormi, potom toto tlačidlo je neaktívne.



2. Spustí sa okno Zmena odkazu. Vyberte zo zoznamu súvisiacich kníh (ak existuje niekoľko z nich) súbor, s ktorým chceme rozbiť pripojenie. Kliknite na tlačidlo "Break pripojenie".



3. Otvorí sa informačné okno, ktoré poskytuje upozornenie o dôsledkoch ďalších opatrení. Ak ste si istí, že budete robiť, kliknite na tlačidlo "Break Communication".



4. Po tom, všetky odkazy na zadaný súbor v aktuálnom dokumente budú nahradené statickými hodnotami.

Metóda 2: Vloženie hodnôt

Vyššie uvedená metóda je však vhodná len vtedy, ak potrebujete úplne prerušiť všetky odkazy medzi oboma knihami. Čo ak potrebujete odpojiť pridružené tabuľky v rámci toho istého súboru? Môžete to urobiť kopírovaním údajov a potom vložením rovnakého miesta ako hodnoty. Mimochodom, táto metóda môže byť roztrhnutá medzi jednotlivými dátovými rozsahmi rôznych kníh bez porušenia spoločného vzťahu medzi súbormi. Pozrime sa, ako táto metóda funguje v praxi.

1. Zvýrazňujeme rozsah, v ktorom chceme odstrániť komunikáciu s inou tabuľkou. Kliknite na tlačidlo IT doprava. V ponuke Otvorenie vyberte položku "Kopírovať". Namiesto zadaných akcií môžete vytočiť alternatívnu kombináciu klávesov HOT CTRL + C.



2. Ďalej, bez odstránenia výberu z toho istého fragmentu znova naň kliknite pravým tlačidlom myši. Tento čas v zozname akcie kliknite na ikonu "Hodnota", ktorá je odoslaná v skupine Parametre vloženia.



3. Po tom, všetky odkazy v vyhradenom rozsahu budú nahradené statickými hodnotami.

Ako môžete vidieť, Excel má spôsoby a nástroje na spáchanie viacerých tabuliek medzi sebou. Súčasne môžu byť tabuľkové údaje na iných listoch a dokonca aj v rôznych knihách. V prípade potreby sa toto spojenie môže ľahko rozbiť.