Sorodne tabele v Excelu: Podrobna navodila

Pri opravljanju nekaterih nalog v Excelu se morajo včasih ukvarjati z več tabelami, ki so povezane tudi med seboj. To pomeni, da se podatki iz ene tabele zategnejo drugim, vrednosti v vseh povezanih tabelah se ponovno izračunajo, ko se spremenijo.

Sorodne tabele so zelo primerne za uporabo velike količine informacij. Postavite vse informacije v eni tabeli, poleg tega, če to ni homogeno, ni zelo priročno. Težko je delati s takimi predmeti in jih poiskati. Navedena težava je namenjena samo za odpravo sorodnih tabel, informacije, med katerimi je razdeljena, vendar hkrati je medsebojno povezana. Sorodne tabele morda niso samo v enem listu ali eni knjigi, ampak tudi, da se nahajajo v ločenih knjigah (datoteke). Zadnja dva možnosti v praksi se najpogosteje uporabljata, saj je namen te tehnologije le, da se izogne ​​kopičenju podatkov, in njihova podajanje na eni strani ne rešuje bistveno. Naučimo se, kako ustvariti in kako delati s takšno vrsto upravljanja podatkov.

Ustvarjanje sorodnih tabel

Najprej se osredotočimo na vprašanje, na kakšen način je mogoče ustvariti povezavo med različnimi tabelami.

Metoda 1: Formula za neposredno vezavo

Najlažji način za vezanje podatkov je uporaba formul, v katerih se sklicujejo na druge tabele. Imenuje se neposredna vezava. Ta metoda je intuitivna, saj, ko jo veže skoraj tako kot ustvarjanje sklicevanja na podatke v enem tabele.

Poglejmo, kako lahko ta primer oblikujete komunikacijo z neposredno vezavo. Imamo dve tabeli na dveh listih. Na isti tabeli se plača izračuna po formuli z množenjem stopenj zaposlenih za en sam koeficient.

Na drugem listu je obseg tabele, v katerem je seznam zaposlenih s svojo plačo. Seznam zaposlenih v obeh primerih je predstavljen v enem vrstnem redu.

Treba je, da se podatki o stavah iz drugega pločevine zategnejo v ustrezne celice prvega.

1. Na prvem listu dodelimo prvo celico "stave" stolpca. V njem smo postavljeni znak "=". Nato kliknite na etiketo "list 2", ki se nahaja na levem delu vmesnika Excel v vrstici stanja.



2. V drugem območju dokumenta je gibanje. Kliknite na prvo celico v stolpcu "Stave". Nato kliknite gumb ENTER na tipkovnici, da vnesete podatke v celico, v kateri je bil predhodno nameščen znak "Enak" znak.



3. Potem je na prvem listu avtomatski prehod. Kot lahko vidimo, se vrednost prvega zaposlenega iz druge tabele potegne v ustrezno celico. Z namestitvijo kazalca na celico, ki vsebuje stavo, vidimo, da se običajno formula uporablja za prikaz podatkov na zaslonu. Toda pred koordinatami celice, od koder se podatki izhajajo, je izraz "Seznam2!", Ki označuje ime območja dokumenta, kjer se nahajajo. Splošna formula v našem primeru izgleda tako:

   = Seznam2! B2



4. Zdaj morate prenesti podatke o stopnjah vseh drugih zaposlenih v podjetju. Seveda je to mogoče storiti na enak način, da smo izpolnili nalogo prvega zaposlenega, vendar ob upoštevanju, da se tako zaposleni nahajajo v istem vrstnem redu, je mogoče nalogo bistveno poenostaviti in pospešiti s svojo odločitev. To lahko naredite preprosto s kopiranjem formule na spodnji obseg. Zaradi dejstva, da so sklicevanja na Excel relativna, ko kopirajo svoje vrednosti, se vrednosti premika premaknejo, ki jih potrebujemo. Postopek kopiranja se lahko izvede z uporabo označevalca polnilnika.

   Torej, postavimo kurzor na spodnje desno površino elementa s formulo. Po tem se mora kazalka pretvoriti v polnilni znak v obliki črnega križa. Izvajamo objemko levega gumba miške in potegnite kurzor na številko stolpca.



5. Vsi podatki iz podobnega stolpca na listu 2 so bili potegnjeni v tabelo na listu 1. Ko se podatki spremenijo na listu 2, se bodo samodejno spremenili na prvi.

Metoda 2: Uporaba Blundering operaterjev Indeks - Iskanje

Toda kaj storiti, če seznam zaposlenih v namiznih nizov se ne nahaja v istem naročilu? V tem primeru, kot je navedeno prej, je ena od možnosti namestitev razmerja med vsako od teh celic, ki jih je treba povezati ročno. Ampak to je primerno, razen majhnih tabel. Za množične razpone, bo ta možnost v najboljšem primeru veliko časa o izvajanju, in v najslabšem primeru bo v praksi na splošno nerealno. Toda ta problem je mogoče rešiti z uporabo kup operaterjev indeksa - iskanje. Poglejmo, kako je mogoče storiti z obdelavo podatkov v tabelah, o katerih je bil pogovor v prejšnji metodi.

1. Poudarite prvi element stolpca "Stave". Pojdite na čarovnika za funkcije s klikom na ikono "Vstavi funkcijo".



2. V čarovniku funkcij v skupini "Povezave in nizi" najdemo in dodelite ime "Index".



3. Ta upravljavec ima dve obrazci: obrazec za delo z nizom in referenco. V našem primeru je potrebna prva možnost, zato v naslednjem oknu izbire obrazca, ki se odpre, izberite in kliknite gumb »OK«.



4. Indeks trditve operaterja se začne izvajati. Naloga podane funkcije je izhod vrednosti, ki se nahaja v izbranem območju v vrstici z določeno številko. Indeks splošne formule take:

   = Indeks (polje; številka_name; [številko_stolbits])

   "Matrika" je argument, ki vsebuje obseg območja, od katerega bomo izvlekli informacije s številom navedene vrstice.

   "Številka vrstice" je argument, ki je število te vrstice. Pomembno je vedeti, da je treba številko vrstice določena, ne glede na celoten dokument, ampak le glede na dodeljeno matriko.

   "Število stolpca" je argument, ki je neobvezen. Da bi rešili posebej našo nalogo, ga ne bomo uporabili, zato ga ni treba posebej opisati posebej.

   Postavimo kazalec v polje »Array«. Po tem, pojdite na list 2 in držite levi gumb miške, izberite celotno vsebino stolpca "hitrost".



5. Ko so koordinate prikazane v oknu operaterja, smo kazalec postavili v polje »številka vrstice«. Ta argument bomo umaknili z izvajalcem iskanja. Zato kliknite na trikotnik, ki se nahaja na levi strani funkcijskega niza. Odpre se seznam na novo uporabljenih operaterjev. Če najdete ime "Iskanje podjetje" med njimi, lahko kliknete na to. V nasprotnem primeru kliknite na zadnjo točko seznama - "Druge funkcije ...".



6. Začne se standardna okna čarovnika za okno. Pojdite na to v isti skupini "Povezave in nizi". Tokrat na seznamu izberite element "Iskanje podjetja". Opravite klik na gumb "OK".



7. Izvede se aktiviranje argumentov argumentov uporabnika. Navedena funkcija je zasnovana tako, da izpiše vrednost vrednost v določenem nizu po imenu. Zaradi te funkcije izračunamo število niza določene vrednosti za funkcijo funkcije. Predstavljena je sintaksa iskalne odbora:

   = Iskanje deska (iskanje_name; ogled__nasive; [tipa_station])

   "Želeni" je argument, ki vsebuje ime ali naslov celice območja tretje osebe, v katerem se nahaja. To je položaj tega imena v ciljnem območju in ga je treba izračunati. V našem primeru se bo vloga prvega argumenta sklicevala na celice na listu 1, v kateri se nahajajo zaposleni.

   "Skrvana matrika" je argument, ki je sklicevanje na matriko, ki izvaja iskanje določene vrednosti za določitev njegovega položaja. To vlogo bomo izvedli, da izvedemo naslov stolpca "Ime" na listu 2.

   "Vrsta primerjave" - ​​argument, ki je neobvezen, vendar, za razliko od prejšnjega operaterja, bo ta neobvezni argument potreben. Označuje, kako se Ujemanje upravljavec je želena vrednost z matriko. Ta argument ima lahko eno od treh vrednot: -1; 0; 1. Za neurejene nize izberite možnost "0". Ta možnost je primerna za naš primer.

   Torej, nadaljujte s polnjenjem polj okna. Kursor smo postavili v polje "fogular vrednost", kliknite na prvo mesto "Ime" na listu 1.



8. Ko so koordinate prikazane, nastavite kazalko v polje "Seznam masivnega" in pojdite na etiketo "list 2", ki se nahaja na dnu okna Excel nad vrstico stanja. Klementirajte levi gumb miške in označite kazalec vseh celic stolpca "Ime".



9. Po tem, ko so njihove koordinate prikazane v polju »Navedeti masivno«, pojdite na polje »Typping Type« in nastavite številko »0« na tipkovnici. Po tem se spet vrnemo na polje "Pogled skozi matriko". Dejstvo je, da bomo opravili kopiranje formule, kot smo to storili v prejšnji metodi. Tam bo premik naslovov, vendar tukaj koordinate polja gledal, da moramo zagotoviti. Ne bi smel premakniti. Koordinate izpostavljamo s kazalcem in kliknite funkcijsko tipko F4. Kot lahko vidite, se je pred koordinatami pojavil znak dolarja, kar pomeni, da se referenca od sorodnika spremeni v absolutno. Nato kliknite na gumb "OK".



10. Rezultat je prikazan v prvi celici "stave" stolpca. Pred kopiranjem moramo popraviti drugo območje, in sicer prvi indeks funkcije argumenta. Če želite to narediti, izberite element stolpca, ki vsebuje formulo in se premaknete na niz formul. Dodelite prvi argument indeksa operaterja (B2: B7) in kliknite na gumb F4. Kot lahko vidite, se je pojavil v bližini izbranih koordinat. Kliknite tipko Enter. Na splošno je formula vzela naslednjo obliko:

    = Indeks (list2! $ B $ 2: $ B $ 7; Iskanje deska (list1! A4; Seznam2! $ $ 2: $ 7 $ 0))



11. Zdaj lahko kopirate z oznako za polnjenje. Poklicamo ga na enak način, kot smo se izgovorili prej, in se raztezajo do konca tabelarnega območja.



12. Kot lahko vidite, kljub dejstvu, da vrstni red strune v dveh povezanih tabelah ne sovpada, kljub temu, vse vrednosti se zategnejo glede na imena delavcev. To je bilo doseženo zaradi uporabe kombinacije operaterjev indeksa iskanja indeksa.

Metoda 4: Poseben vložek

Tabela tabele v Excelu lahko uporabljajo tudi posebno vstavljanje.

1. Izberite vrednosti, ki jih želite zategniti na drugo tabelo. V našem primeru je to "stavna" stolpec na listu 2. Kliknite na namenski fragment z desnim gumbom miške. Na seznamu, ki se odpre, izberite element "Copy". Alternativna kombinacija je kombinacija tipk CTRL + C. Po tem se premaknemo na list 1.



2. Premikanje na območje knjige, ki jo potrebujete, dodelite celice, v katerih bodo vrednosti zategnjene. V našem primeru je to stolpec "ponudbe". Kliknite na namenski fragment z desnim gumbom miške. V oriji orodne vrstice "Vstavi parametre" kliknite ikono "Vstavi komunikacijo".

   

   Obstaja tudi alternativa. Mimogrede, je edini za starejše različice Excela. V kontekstnem meniju pripeljemo kazalec na element "posebnega vstavljanja". V dodatnem meniju, ki se odpre, izberite položaj z istim imenom.



3. Po tem se odpre posebno vstavljeno okno. Kliknite na gumb "Vstavi komunikacijo" v spodnjem levem kotu celice.



4. Kakršnakoli možnost, ki jo izberete, bodo vrednosti iz ene tabele nista vstavile v drugo. Pri spreminjanju podatkov v viru se bodo samodejno spremenili v vstavljenem območju.

Lekcija: Poseben vložek v Excelu

Metoda 5: Komunikacija med tabelami v več knjigah

Poleg tega lahko organizirate povezavo med namiznimi območji v različnih knjigah. To uporablja posebno orodje za vstavljanje. Ukrepi bodo popolnoma podobni tistim, ki smo jih upoštevali v prejšnji metodi, razen da navigacija med formulami ne bo imela med področji ene knjige, ampak med datotekami. Seveda je treba odpreti vse povezane knjige.

1. Izberite paleto podatkov, ki se prenese v drugo knjigo. Kliknite na desni gumb miške in izberite položaj "Copy" v odprtem meniju.



2. Nato se preselimo v knjigo, v kateri je treba vstaviti te podatke. Izberite želeno območje. Kliknite desni gumb miške. V kontekstnem meniju v skupini "Vstavi nastavitve" izberite element "Vstavi komunikacijo".



3. Po tem se bodo vstavile vrednosti. Pri menjavi podatkov v izvorni knjigi se bo tabela iz delovne knjige samodejno zategnila. In sploh ni potrebno zagotoviti, da sta obe knjigi odprta. Dovolj je, da odprete en samo delovni zvezek, in bo samodejno umaknil podatke iz zaprtega sorodnega dokumenta, če je v njem predhodne spremembe.

Vendar je treba opozoriti, da bo v tem primeru, da se v primeru, da je v obliki nespremenjenega matrika. Ko poskušate spremeniti celico z vstavljenimi podatki, bo sporočilo opulirano obveščanje o nezmožnosti tega.

Spremembe v takšnem nizu, povezane z drugo knjigo, lahko samo prekine povezavo.

Naslov prelomi med tabelami

Včasih je potrebno prekiniti povezavo med tabelami. Razlog za to je lahko zgoraj opisano, ko želite spremeniti polje, vstavljeno iz druge knjige, in preprosto uporabnika nenaklonjenost uporabnika, tako da se podatki v isti tabeli samodejno posodabljajo od druge.

Metoda 1: Komunikacijska prekinitve med knjigami

Če želite prekiniti povezavo med knjigami v vseh celicah, z izvajanjem dejansko eno operacijo. V tem primeru bodo podatki v celicah ostali, vendar bodo že statične ne posodobljene vrednosti, ki niso odvisne od drugih dokumentov.

1. V knjigi, v kateri so vrednosti iz drugih datotek zategnjene, pojdite na kartico podatkov. Kliknite ikono »Spremeni povezave«, ki se nahaja na traku v orodni vrstici "Povezava". Opozoriti je treba, da če trenutna knjiga ne vsebuje povezav z drugimi datotekami, potem je ta gumb neaktiven.



2. Začelo se je okno Sprememba povezave. Izberite s seznama povezanih knjig (če jih je več) datoteka, s katero želimo prekiniti povezavo. Kliknite na gumb »Razbijte povezavo«.



3. Odpre se okno Informacije, ki vsebuje opozorilo o posledicah nadaljnjih ukrepov. Če ste prepričani, da boste storili, kliknite na gumb "Break Communication".



4. Po tem bodo vsa sklicevanja na določeno datoteko v trenutnem dokumentu zamenjana s statičnimi vrednostmi.

2. način: Vstavljanje vrednosti

Toda zgornja metoda je primerna le, če morate popolnoma prekiniti vse povezave med obema knjigama. Kaj, če morate odklopiti povezane tabele v isti datoteki? To lahko storite s kopiranjem podatkov in nato vstavite isto mesto kot vrednosti. Mimogrede, ta metoda se lahko uniči med posameznimi podatkovnimi območji različnih knjig, ne da bi prekinil skupno razmerje med datotekami. Poglejmo, kako ta metoda deluje v praksi.

1. Označujemo obseg, v katerem želimo izbrisati komunikacijo z drugo tabelo. Kliknite na desni gumb miške. V odprtem meniju izberite element "Copy". Namesto določenih dejanj lahko pokličete alternativno kombinacijo vročih ključev CTRL + C.



2. Nato, ne da bi odstranili izbor iz istega fragmenta, kliknite nanj z desnim gumbom miške. Tokrat na seznamu delovanja kliknite ikono "Vrednost", ki je objavljena v skupini Vstavi parametre.



3. Po tem bodo vsa referenca v namenskem območju zamenjana s statičnimi vrednostmi.

Kot lahko vidite, Excel ima načine in orodja za povezovanje več tabel med seboj. Hkrati pa so lahko tabelarni podatki na drugih listih in celo v različnih knjigah. Če je potrebno, se ta povezava lahko enostavno prekine.