Při provádění určitých úkolů v Excelu se někdy musí vypořádat s několika tabulkami, které jsou také spojeny. To znamená, že data z jedné tabulky jsou dotažena ostatním a hodnoty ve všech příbuzných tabulkách jsou přepočítány, když se mění.
Související tabulky jsou velmi vhodné použít pro zpracování velkého množství informací. Umístěte všechny informace v jedné tabulce, pokud to není homogenní, ne příliš pohodlný. Je těžké pracovat s těmito předměty a hledat je. Zadaný problém je jen navržen tak, aby odstranil související tabulky, informace mezi nimiž jsou distribuovány, ale zároveň jsou propojeny. Související tabulky mohou být nejen v rámci jednoho listu nebo jedné knihy, ale také umístit v samostatných knihách (souborech). Nejčastěji se používají poslední dvě možnosti v praxi, protože účelem této technologie je jen dostat pryč od akumulace dat, a jejich kývnutí na jedné stránce nevyřeší zásadně. Naučme se, jak vytvořit a jak pracovat s takovým typem správy dat.
Vytváření příbuzných tabulek
Za prvé, pojďme se zaměřit na otázku, jakým způsobem je možné vytvořit spojení mezi různými tabulkami.Metoda 1: Přímé vazebné tabulky vzorec
Nejjednodušší způsob, jak vázat data, je použití vzorců, ve kterých existují odkazy na jiné tabulky. Nazývá se přímá vazba. Tato metoda je intuitivní, protože když to váže téměř stejně jako vytváření odkazů na data v jedné tabulkovém poli.
Podívejme se, jak lze příklad vytvořit komunikaci přímou vazbou. Na dva listy máme dva stoly. Na stejném stole se platí mzda s použitím vzorce vynásobením míry zaměstnance pro jeden koeficient.
Na druhém listu je řada tabulek, ve kterém je seznam zaměstnanců s jejich platem. Seznam zaměstnanců v obou případech je uveden v jednom pořadí.
Je nutné provést, že data na sázkách z druhého listu zpřísnit do odpovídajících buněk první.
- Na prvním listu přidělíme první buňku sloupce "Sázka". Dali jsme do něj znamení "=". Dále klikněte na štítek "List 2", který je umístěn v levé části rozhraní Excel přes stavový řádek.
- Ve druhé oblasti dokumentu je pohyb. Klikněte na první buňku ve sloupci "Sázka". Poté klikněte na tlačítko Enter na klávesnici pro zadání dat do buňky, ve které bylo dříve nainstalováno znak "EQUAL".
- Pak je k prvnímu listu automatický přechod. Jak vidíme, hodnota prvního zaměstnance z druhé tabulky je tažena do odpovídající buňky. Instalací kurzoru na buňku obsahující sázku, vidíme, že obvyklý vzorec se používá k zobrazení dat na obrazovce. Ale před souřadnicm buňky, odkud jsou data výstupy, existuje výraz "list2!", Což označuje název oblasti dokumentu, kde jsou umístěny. Obecný vzorec v našem případě vypadá takto:
= List2! B2
- Nyní musíte převést údaje o sazbách všech ostatních zaměstnanců podniku. Samozřejmě, že to lze provést stejným způsobem, jakým jsme splnili úkol pro první zaměstnance, ale zvažujeme, že oba seznamy zaměstnanců jsou umístěny ve stejném pořadí, úkol může být výrazně zjednodušen a zrychlen jeho rozhodnutím. To lze provést jednoduše kopírováním vzorce do níže uvedeného rozsahu. Vzhledem k tomu, že odkazy na Excel jsou relativní, při kopírování jejich hodnot jsou posunu hodnoty posunuty, že potřebujeme. Samotný postup kopírování lze provést pomocí plnicího markeru.
Takže vložíme kurzor do dolního proudu prvku se vzorcem. Poté musí kurzor převést na plnicí marker ve formě černého kříže. Provádíme svorku levého tlačítka myši a vytáháme kurzor na číslo sloupce.
- Všechna data z podobného sloupce na listu 2 byly taženy do tabulky na listu 1. Když se datová data změní na listu 2, automaticky se změní na první.
Metoda 2: Použití Blunding of Operators Index - Hledat
Ale co dělat, pokud seznam zaměstnanců v tabulkových polích není umístěn ve stejném pořadí? V tomto případě, jak je uvedeno dříve, jedním z možností je instalovat vztah mezi každou z těchto buněk, které by měly být spojeny ručně. Ale je vhodné kromě malých stolů. Pro masivní rozsahy bude tato volba v nejlepším případě trvat hodně času na realizaci a v nejhorším případě - v praxi bude obecně nereálný. Tento problém však lze vyřešit pomocí parta operátorů indexu - vyhledávání. Podívejme se, jak to může být provedeno tak, že data v tabulkách, o kterých byl konverzace v předchozí metodě.
- Zvýrazňujeme první prvek sloupce "Sázka". Přejděte do Průvodce funkcí kliknutím na ikonu "Vložit funkci".
- V průvodci funkcí ve skupině "Odkazy a pole" najdeme a přidělujeme název "index".
- Tento operátor má dvě formy: forma pro práci s poli a reference. V našem případě je požadována první možnost, takže v dalším okně výběru formuláře, které se otevře, vyberte jej a klikněte na tlačítko "OK".
- Agumentační index operátora začíná běžet. Úkolem uvedené funkce je výstup hodnoty umístěné ve vybraném rozsahu v řádku se zadaným číslem. Index obecného vzorce operátora takový:
= Index (pole; number_name; [number_stolbits])
"Array" je argument obsahující rozsah rozsahu, od kterého získáte informace podle počtu zadaného řádku.
"Číslo řádků" je argument, který je číslo tohoto řádku. Je důležité vědět, že číslo řádku by mělo být specifikováno vůči celému dokumentu, ale pouze ve vztahu k přidělenému poli.
"Číslo sloupce" je argument, který je volitelný. Vyřešit konkrétně našeho úkolu, nebudeme ji používat, a proto není nutné jej popsat samostatně.
Kurzor vložíme do pole "Array". Poté přejděte na list 2 a podržte levé tlačítko myši, vyberte celý obsah sloupce "Rate".
- Po zobrazení souřadnic v okně obsluhy jsme vložili kurzor do pole "Číslo řádku". Tento argument vybereme pomocí operátora vyhledávání. Proto klikněte na trojúhelník, který je umístěn vlevo od řetězce funkcí. Otevře se seznam nově použitých operátorů. Pokud najdete název "Search Company" mezi nimi, můžete na něj kliknout. V opačném případě klikněte na nejnovější bod seznamu - "Ostatní funkce ...".
- Spustí se okno Průvodce standardním oknem. Jdi na to ve stejné skupině "Odkazy a pole". Tentokrát v seznamu vyberte položku "Vyhledávací společnost". Proveďte kliknutím na tlačítko "OK".
- Provádí se aktivace argumentů argumentů vyhledávacího operátora. Zadaná funkce je určena k výstupu čísla hodnoty v určitém poli podle názvu. Díky této funkci vypočítáváme číslo řetězce specifické hodnoty funkce funkce funkce. Je prezentována syntaxe vyhledávací desky:
= Vyhledávací deska (Search_Name; prohlížení __Sive; [typ_station])
"Požadovaný" je argument obsahující název nebo adresu buňky řady třetích stran, ve kterém se nachází. Je to pozice tohoto názvu v cílovém rozsahu a mělo by být vypočteno. V našem případě bude role prvního argumentu odkazován na buňky na listu 1, ve kterém se nacházejí zaměstnanci.
"Listful Array" je argument, který je odkaz na pole, který provádí hledání zadané hodnoty pro určení jeho pozice. Budeme mít tuto úlohu provést adresu sloupce "Název" na listu 2.
"Typ porovnání" - argument, který je volitelný, ale na rozdíl od předchozího operátora bude tento volitelný argument zapotřebí. Označuje, jak odpovídat operátorovi je požadovaná hodnota s polem. Tento argument může mít jeden ze tří hodnot: -1; 0; 1. Pro neuspořádané pole vyberte možnost "0". Tato volba je vhodná pro náš případ.
Tak pokračujte na vyplnění pole argumentu okna. Kurzor vložíme do pole "Fogulární hodnota", klikněte na první buňku "Name" sloupec na listu 1.
- Po zobrazení souřadnic, nastavte kurzor v poli "Seznam masivních" a přejděte na štítek "List 2", který je umístěn v dolní části okna aplikace Excel nad stavovým řádkem. Klecejte levé tlačítko myši a zvýrazněte kurzor všechny buňky sloupce "Název".
- Po zobrazení souřadnic se zobrazí v poli "Seznam masivních", přejděte na pole "Mapování typu" a nastavte číslo "0" z klávesnice. Poté se opět vrátíme do pole "Při pohledu přes pole". Skutečností je, že budeme provádět kopírování vzorce, jak jsme to udělali v předchozí metodě. Tam bude posun adres, ale zde souřadnice pole je třeba zajistit. Neměl se posunout. Zvýrazňujeme souřadnice s kurzorem a klikněte na tlačítko F4. Jak vidíte, znak dolaru se objevil před souřadnicemi, což znamená, že odkaz od relative se změnil na absolutní. Poté klikněte na tlačítko "OK".
- Výsledek se zobrazí v první buňce sloupce "Sázka". Ale před kopírováním musíme opravit jinou oblast, konkrétně první index funkce argumentu. Chcete-li to provést, vyberte sloupec prvek, který obsahuje vzorec a přesuňte se na řetězec vzorce. Přidělit první argument indexu operátora (B2: B7) a klikněte na tlačítko F4. Jak vidíte, znak dolaru se objevil v blízkosti vybraných souřadnic. Klepněte na tlačítko Enter. Obecně platí, že vzorec provedl následující formulář:
= Index (list2! $ B $ 2: $ b $ 7; vyhledávací deska (list1! A4; list2! $ A $ 2: $ 7 $ 7; 0))
- Nyní můžete kopírovat pomocí plnicího markeru. Říkáme tomu stejně, abychom mluvili dříve, a protáhnout se na konec tabulátoru.
- Jak vidíte, navzdory skutečnosti, že pořadí řetězců ve dvou souvisejících tabulkách se neshoduje, nicméně, všechny hodnoty jsou dotaženy podle názvů pracovníků. Toho bylo dosaženo díky použití kombinace operátorů index vyhledávání.
Metoda 4: Speciální vložka
Tabulka tabulka v aplikaci Excel mohou také používat speciální vložení.
- Vyberte hodnoty, které chcete "utáhnout" do jiné tabulky. V našem případě se jedná o rozsah sloupců sázek na listu 2. Klikněte na vyhrazený fragment s pravým tlačítkem myši. V seznamu, který se otevře, vyberte položku "Kopírovat". Alternativní kombinace je kombinace kláves Ctrl + C. Poté se pohybujeme na list 1.
- Stěhování do oblasti knihy, kterou potřebujete, přidělte buňky, ve kterých budou hodnoty zpřísnit. V našem případě se jedná o sloupec "BID". Klikněte na vyhrazený fragment s pravým tlačítkem myši. V kontextovém menu v panelu nástrojů "Vložit parametry" klikněte na ikonu "Vložit komunikaci".
Existuje také alternativa. Mimochodem, je jediný pro starší verze aplikace Excel. V místní nabídce přinášíme kurzor na položku "Speciální vložka". V další nabídce se otevře, vyberte pozici se stejným názvem.
- Poté se otevře speciální vkládání okna. Klikněte na tlačítko "Vložit komunikaci" v levém dolním rohu buňky.
- Bez ohledu na volbu, kterou si vyberete, budou vloženy hodnoty z jedné tabulky pole. Při změně dat ve zdroji se také automaticky změní v vloženém rozsahu.
Lekce: Speciální vložka v aplikaci Excel
Metoda 5: Komunikace mezi tabulkami v několika knihách
Kromě toho můžete zorganizovat spojení mezi oblastmi stolu v různých knihách. To používá speciální nástroj vkládání. Akce budou naprosto podobné těm, které jsme zvažovali v předchozí metodě, kromě toho, že navigace během vzorců nebude mít mezi oblastmi jedné knihy, ale mezi soubory. Přirozeně by měly být otevřeny všechny související knihy.
- Vyberte rozsah dat, který chcete přenést do jiné knihy. Klikněte pravým tlačítkem myši a vyberte polohu "Kopírovat" v otevřeném menu.
- Pak se přesuneme do knihy, ve které by měla být tato data vložena. Vyberte požadovaný rozsah. Klikněte pravým tlačítkem myši. V kontextovém menu ve skupině "Vložit nastavení" vyberte položku "Vložit komunikaci".
- Po tom budou hodnoty vloženy. Při změně dat ve zdrojové knize, pole tabulky z pracovní knihy je automaticky dotáhne. A není vůbec nutné, aby bylo zajištěno, že obě knihy jsou otevřené. Stačí otevřít jediný sešit, a bude automaticky převádět data z uzavřeného souvisejícího dokumentu, pokud v něm byly předchozí změny.
Je však třeba poznamenat, že v tomto případě bude vložka vyráběna ve formě nezměněného pole. Při pokusu o změnu libovolné buňky s vloženými daty bude zpráva osídlena informování o neschopnosti to udělat.
Změny v takové poli spojené s jinou knihou mohou přerušit spojení pouze.
Název mezi tabulkami
Někdy je nutné rozbít spojení mezi tabulkami. Důvodem může být výše popsaný, když chcete změnit pole vložené z jiné knihy a jednoduše neochota uživatele tak, aby data ve stejné tabulce byly automaticky aktualizovány z druhé.Metoda 1: Komunikace mezi knihami
Prolomit spojení mezi knihami ve všech buňkách provedením vlastně jedné operace. V tomto případě zůstanou data v buňkách, ale budou již statické ne aktualizované hodnoty, které nezávisí na jiných dokumentech.
- V knize, ve kterých jsou hodnoty z jiných souborů dotaženy, přejděte na kartu Data. Klikněte na ikonu "Změnit odkazy", která je umístěna na pásce v panelu nástrojů "Připojení". Je třeba poznamenat, že pokud aktuální kniha neobsahuje připojení s jinými soubory, pak je toto tlačítko neaktivní.
- Je spuštěno okno Změnit odkaz. Vyberte ze seznamu souvisejících knih (pokud existuje několik z nich) soubor, se kterými chceme přerušit spojení. Klikněte na tlačítko "Break připojení".
- Otevře se informační okno, které poskytuje varování o důsledcích dalších akcí. Pokud jste si jisti, že budete dělat, klikněte na tlačítko "Break Communication".
- Poté budou všechny odkazy na zadaný soubor v aktuálním dokumentu nahrazeny statickými hodnotami.
Metoda 2: Vkládání hodnot
Výše uvedená metoda je však vhodná pouze tehdy, pokud potřebujete zcela přerušit všechny vazby mezi oběma knihami. Co když potřebujete odpojit přidružené tabulky ve stejném souboru? Můžete to udělat zkopírováním dat a poté vložení stejného místa jako hodnoty. Mimochodem, tato metoda může být roztržena mezi jednotlivými datovými rozsahy různých knih, aniž by porušil společný vztah mezi soubory. Podívejme se, jak tato metoda funguje v praxi.
- Zvýrazňujeme rozsah, ve kterém chceme smazat komunikaci s jinou tabulkou. Klikněte pravým tlačítkem myši. V nabídce Otevřít vyberte položku "Kopírovat". Místo zadaných akcí můžete vytočit alternativní kombinaci horkých kláves Ctrl + C.
- Dále bez odstranění výběru ze stejného fragmentu, znovu klikněte na něj pravým tlačítkem myši. Tentokrát v seznamu akcí klikněte na ikonu "Hodnota", která je zveřejněna ve skupině INSERT Parameters.
- Poté budou všechny odkazy ve vyhrazeném rozsahu nahrazeny statickými hodnotami.
Jak vidíte, Excel má způsoby a nástroje spojit několik tabulek mezi sebou. Současně mohou být tabulková data na jiných listech a dokonce i v různých knihách. V případě potřeby lze toto spojení snadno rozbité.