Povezane tablice u Excelu: Detaljne upute

Prilikom obavljanja određenih zadataka u Excelu ponekad se moraju nositi s nekoliko tablica koje su također povezane jedni s drugima. To jest, podaci iz jedne tablice su zategnuti na druge, a vrijednosti u svim srodnim tablicama se ponovno izračunavaju kada se promijene.

Srodne tablice su vrlo prikladne za rješavanje velike količine informacija. Stavite sve informacije u jednu tablicu, osim, ako nije homogena, ne baš zgodna. Teško je raditi s takvim objektima i tražiti ih. Navedeni problem je samo osmišljen kako bi se uklonile povezane tablice, a informacije između koje se distribuira, ali u isto vrijeme je međusobno povezana. Srodne tablice mogu biti ne samo unutar jednog lista ili jedne knjige, već i biti smješteni u zasebnim knjigama (datoteke). Posljednje dvije opcije u praksi se najčešće koriste, budući da je svrha ove tehnologije samo da se udaljite od akumulacije podataka, a njihovo kimanje na jednoj stranici ne rješava temeljno. Lečimo kako stvoriti i kako raditi s takvom vrstom upravljanja podacima.

Stvaranje srodnih tablica

Prije svega, usredotočimo se na pitanje, na koji način je moguće stvoriti vezu između različitih tablica.

Metoda 1: Izravna formula vezanja vezanja

Najlakši način za vezanje podataka je korištenje formula u kojima postoje reference na druge tablice. Naziva se izravno vezanjem. Ova metoda je intuitivna, jer kada ga veže gotovo samo kao stvaranje referenci na podatke u jednom nizu.

Da vidimo kako primjer možete oblikovati komunikaciju putem izravnog vezanja. Imamo dvije tablice na dva lista. Na istoj tablici, plaća se izračunava pomoću formule množenjem stopa zaposlenika za jedan koeficijent.

Na drugom listu nalazi se raspon tablice u kojem se nalazi popis zaposlenika s plaćama. Popis zaposlenika u oba slučaja prikazan je u jednom redoslijedu.

Potrebno je učiniti da se podaci o okladama iz drugog lista zategnite u odgovarajuće stanice prvog.

1. Na prvom listu dodijelimo prvu ćeliju kolone "oklade". Stavili smo ga znak "=". Zatim kliknite na oznaku "list 2", koja se nalazi na lijevom dijelu Excel sučelja preko statusne trake.



2. Postoji pokret u drugom području dokumenta. Kliknite na prvu ćeliju u stupcu "oklade". Zatim kliknite na gumb ENTER na tipkovnici da biste unijeli podatke u ćeliju u kojoj je prethodno instaliran "jednak" znak.



3. Zatim tu je automatski prijelaz na prvi list. Kao što možemo vidjeti, vrijednost prvog zaposlenika iz druge tablice je uvučena u odgovarajuću ćeliju. Instaliranjem pokazivača na ćeliju koja sadrži okladu, vidimo da se uobičajena formula koristi za prikaz podataka na zaslonu. Ali ispred koordinata stanice, odakle su podaci izlaz, postoji izraz "popis2!", Što ukazuje na naziv područja dokumenta gdje se nalaze. Opća formula u našem slučaju izgleda ovako:

   = Popis2! B2



4. Sada trebate prenijeti podatke o stopama svih ostalih zaposlenika poduzeća. Naravno, to se može učiniti na isti način na koji smo ispunili zadatak za prvi zaposlenik, ali s obzirom da se i popisi zaposlenika nalaze u istom redoslijedu, zadatak se može značajno pojednostaviti i ubrzati njezinom odlukom. To se može učiniti jednostavnim kopiranjem formule u donji raspon. Zbog činjenice da su reference na Excel relativne, prilikom kopiranja njihovih vrijednosti, promjene vrijednosti se pomaknu da trebamo. Sam postupak kopiranja može se izvršiti pomoću marker za punjenje.

   Dakle, stavljamo pokazivač na donjem desnom dijelu elementa s formulom. Nakon toga, pokazivač se mora pretvoriti u marker za punjenje u obliku crnog križa. Izvodimo stezaljku lijeve tipke miša i povucite pokazivač na broj stupca.



5. Svi podaci iz sličnog stupca na listu 2 su povučeni u tablicu na listu 1. Kada se podaci promijeni na listu 2, automatski će se promijeniti na prvom mjestu.

Metoda 2: Korištenje Blunder of Index operatora - Traži

Ali što učiniti ako se popis zaposlenika u tablicama ne nalazi u istom redoslijedu? U tom slučaju, kao što je ranije navedeno, jedna od opcija je instalirati odnos između svake od tih stanica koje bi trebalo ručno povezati. Ali pogodan je osim malih tablica. Za masivne raspone, ova opcija će najbolje uzeti puno vremena na provedbi, a u najgorem slučaju - u praksi će općenito biti nerealno. Ali ovaj se problem može riješiti pomoću hrpe indeksa operatora - pretraživanja. Da vidimo kako se to može obaviti obradom podataka u tablicama o kojima je razgovor bio u prethodnoj metodi.

1. Mi naglašavamo prvi element stupca "oklade". Idite na čarobnjaka funkcija klikom na ikonu "Insert Funkcija".



2. U čarobnjaku funkcija u grupi "Links i nizovi" nalazimo i dodijelimo naziv "indeks".



3. Ovaj operator ima dva oblika: obrazac za rad s nizovima i referencom. U našem slučaju, potrebna je prva opcija, tako da u sljedećem prozoru odabira obrasca koji se otvara, odaberite ga i kliknite na gumb "OK".



4. Indeks argumenata operatera počinju trčati. Zadatak navedene funkcije je izlaz vrijednosti koji se nalazi u odabranom rasponu u skladu s navedenim brojem. Indeks operatera opće formule, takav:

   = Indeks (niz; broj_name; [broj_stolbits])

   "Array" je argument koji sadrži raspon raspona od kojih ćemo izvući informacije po broju navedenog retka.

   "Broj retka" je argument koji je broj ove linije. Važno je znati da broj linije treba navesti ne u odnosu na cijeli dokument, ali samo u odnosu na dodijeljeni niz.

   "Broj stupca" je argument koji je opcionalan. Da bismo riješili posebno na naš zadatak, nećemo ga koristiti, i stoga ga nije potrebno opisati zasebno.

   Stavljamo kursor u polje "polja". Nakon toga, idite na list 2 i držite lijevu tipku miša, odaberite cijeli sadržaj stupca "stopa".



5. Nakon što su koordinate prikazane u prozoru operatera, stavljamo kursor u polje "Broj retka". Ovaj argument povučemo pomoću operatora pretraživanja. Stoga kliknite na trokut koji se nalazi s lijeve strane funkcijskog niza. Otvara se popis novoobičnih operatera. Ako pronađete ime "Traži tvrtka" među njima, možete kliknuti na njega. U suprotnom slučaju kliknite na najnoviju točku popisa - "Ostale funkcije ...".



6. Počinje prozor Standard Window Wizard. Idite na to u istoj grupi "linkovi i nizovi". Ovaj put na popisu odaberite stavku "Traži tvrtka". Izvršite klik na gumb "OK".



7. Izvodi se aktivacija argumenata argumenata pretraživača. Navedena funkcija je dizajnirana za izlaznu vrijednost broja u određenom nizu po imenu. Zahvaljujući ovoj značajci izračunavamo broj niza određene vrijednosti za funkciju funkcije. Prikazana je sintaksa odbora za pretraživanje:

   = Ploča pretraživanja (Search_name; pregledavanje__nasive; [type_station])

   "Željeni" je argument koji sadrži ime ili adresu ćelije treće strane raspona u kojem se nalazi. To je položaj ovog imena u ciljnom rasponu i treba ga izračunati. U našem slučaju, uloga prvog argumenta odnosi se na stanice na listu 1, u kojem se zaposlenici nalaze.

   "Napušten niz" je argument, koji je referenca na niz, koji obavlja traženje određene vrijednosti za određivanje položaja. Imat ćemo tu ulogu izvršiti adresu stupca "Ime" na listu 2.

   "Vrsta usporedbe" - argument koji je opcionalan, ali, za razliku od prethodnog operatora, ovaj dodatni argument će biti potreban. Označava kako se podudara s operaterom je željena vrijednost s nizom. Ovaj argument može imati jednu od tri vrijednosti: -1; 0; 1. Za neuredne nizove odaberite opciju "0". Ova opcija je prikladna za naš slučaj.

   Dakle, nastavite do popunjavanja polja prozora argumenta. Stavljamo kursor u polje "Fogularna vrijednost", kliknite na prvu stanicu "Name" stupac na listu 1.



8. Nakon prikazivanja koordinata, postavite kursor u polje "Popis masivnog" i idite na oznaku "List 2", koja se nalazi na dnu Excel prozora iznad statusne trake. Klement lijeve tipke miša i označite pokazivač svih stanica stupca "Ime".



9. Nakon što su njihove koordinate prikazane u polje "Popis masivnog", idite na polje "Mapiranje" i postavite broj "0" iz tipkovnice. Nakon toga ponovno se vraćamo na polje "gledajući kroz niz". Činjenica je da ćemo obavljati kopiranje formule, kao što smo učinili u prethodnoj metodi. Bit će pomak adresa, ali ovdje su koordinate polja pregledali moramo osigurati. Ne bi se trebao pomaknuti. Mi naglašavaju koordinate s pokazivačem i kliknite na F4 funkcijsku tipku. Kao što možete vidjeti, znak dolara pojavio se prije koordinata, što znači da se referenca od relativnog pretvorila u apsolutnu. Zatim kliknite na gumb "OK".



10. Rezultat se prikazuje u prvoj ćeliji stupca "oklade". Ali prije kopiranja, moramo popraviti drugo područje, odnosno prvi indeks funkcije argumenta. Da biste to učinili, odaberite element stupca, koji sadrži formulu i premjestite se na niz formula. Dodijelite prvi argument indeksa operatera (B2: B7) i kliknite na gumb F4. Kao što možete vidjeti, znak dolara pojavio se u blizini odabranih koordinata. Kliknite tipku Enter. Općenito, formula je uzela sljedeći oblik:

    = Indeks (Liet2! $ B $ 2: $ B $ 7; tražilica (list1! A4; popis2! $ 2: $ 7; 0))



11. Sada možete kopirati pomoću markera za punjenje. Zovemo ga na isti način na koji smo ranije govorili i protežu se do kraja tabličnog raspona.



12. Kao što možete vidjeti, unatoč činjenici da se redoslijed žica u dva srodna tablica ne podudara, sve se vrijednosti zateže prema imenima radnika. To je postignuto zahvaljujući korištenju kombinacije pretrage indeksa operatora.

Metoda 4: Posebni umetak

Priključnici kravata u Excelu također mogu koristiti poseban umetanje.

1. Odaberite vrijednosti koje želite "zategnuti" na drugu tablicu. U našem slučaju, ovo je "oklada" raspon stupca na listu 2. Kliknite na namjenski fragment s desnom tipkom miša. Na popisu koji se otvara, odaberite stavku "Kopiraj". Alternativna kombinacija je kombinacija tipki Ctrl + C. Nakon toga se krećemo na list 1.



2. Prelazak na područje knjige koju trebate, dodijelite stanice u kojima će se potrebno zategnuti vrijednosti. U našem slučaju, ovo je "ponuda" stupac. Kliknite na namjenski fragment s desnom tipkom miša. U kontekstnom izborniku u alatnoj traci "Umetni parametri" kliknite na ikonu "Umetni komunikacija".

   

   Tu je i alternativa. Usput, jedini je za starije verzije Excela. U kontekstnom izborniku donosimo kursor na stavku "Posebna umetnuta". U dodatnom izborniku koji se otvara, odaberite položaj s istim imenom.



3. Nakon toga otvara se poseban prozor umetanja. Kliknite na gumb "Umetni komunikacija" u donjem lijevom kutu ćelije.



4. Bez obzira na opciju koju odaberete, vrijednosti iz jedne tablice polja će se umetnuti u drugi. Prilikom mijenjanja podataka u izvoru, automatski će se promijeniti u umetnutom rasponu.

Lekcija: Posebni umetak u Excel

Metoda 5: Komunikacija između tablica u nekoliko knjiga

Osim toga, možete organizirati vezu između područja tablice u različitim knjigama. Ovo koristi poseban alat za umetanje. Akcije će biti apsolutno slične onima koje smo razmotrili u prethodnoj metodi, osim što navigacija tijekom formula neće imati između područja jedne knjige, već između datoteka. Naravno, trebaju biti otvorene sve povezane knjige.

1. Odaberite raspon podataka koji će se prenijeti u drugu knjigu. Kliknite na to desno miša i odaberite položaj "Kopiranje" u otvorenom izborniku.



2. Tada se preselimo u knjigu u kojoj bi se ta podaci trebali umetnuti. Odaberite željeni raspon. Kliknite desnu tipku miša. U kontekstnom izborniku u grupi "Insert Settings" odaberite stavku "Umetni komunikacija".



3. Nakon toga, vrijednosti će biti umetnute. Kada mijenjate podatke u izvornoj knjizi, tablični niz iz radne knjige automatski će ih pričvrstiti. I uopće nije potrebno osigurati da su obje knjige otvorene. Dovoljno je otvoriti samo jednu radnu knjigu, a to će automatski okajati podatke iz zatvorenog povezanog dokumenta ako je u njemu bilo prethodnih promjena.

Ali treba napomenuti da će se u ovom slučaju inset proizvesti u obliku nepromijenjenog niza. Kada pokušavate promijeniti bilo koju ćeliju s umetnutim podacima, poruka će biti popunjena informiranje o nemogućnosti da to učini.

Promjene u takvom nizu povezane s drugom knjigom mogu samo prekinuti vezu.

Naslov prekida između tablica

Ponekad je potrebno prekinuti vezu između tablica. Razlog tome može biti gore opisano kada želite promijeniti niz umetnuti iz druge knjige i jednostavno korisnikovo nevoljkost, tako da se podaci u istoj tablici automatski ažuriraju od druge.

Metoda 1: Komunikacijske prekide između knjiga

Razbiti vezu između knjiga u svim ćelijama, izvođenjem zapravo jedne operacije. U tom slučaju, podaci u stanicama će ostati, ali oni će već biti statični ne ažurirane vrijednosti koje ne ovise o drugim dokumentima.

1. U knjizi u kojoj su vrijednosti s drugih datoteka zategnute, idite na karticu podatke. Kliknite ikonu "Promijeni linkove", koja se nalazi na traci u alatnoj traci "Priključak". Treba napomenuti da ako trenutna knjiga ne sadrži veze s drugim datotekama, tada je ovaj gumb neaktivan.



2. Pokrenut je prozor za promjenu veze. Odaberite s popisa srodnih knjiga (ako postoji nekoliko) datoteku s kojom želimo prekinuti vezu. Kliknite na gumb "Razbijte vezu".



3. Otvara se informacijski prozor koji pruža upozorenje o posljedicama daljnjih postupaka. Ako ste sigurni da ćete učiniti, kliknite na gumb "Break Communication".



4. Nakon toga, sve reference na navedenu datoteku u trenutnom dokumentu bit će zamijenjene statičkim vrijednostima.

Metoda 2: Umetanje vrijednosti

Ali gore navedena metoda je prikladna samo ako trebate potpuno razbiti sve veze između dvije knjige. Što ako trebate odspojiti pridružene tablice unutar iste datoteke? To možete učiniti kopiranjem podataka, a zatim umetanje istog mjesta kao vrijednosti. Usput, ova metoda se može rupnuti između pojedinačnih raspona podataka različitih knjiga bez razbijanja zajedničkog odnosa između datoteka. Da vidimo kako ova metoda radi u praksi.

1. Mi naglašavaju raspon u kojem želimo izbrisati komunikaciju s drugom tablicom. Kliknite na to desno miša. U otvorenom izborniku odaberite stavku "Kopiraj". Umjesto navedenih radnji, možete birati alternativnu kombinaciju vrućih tipki Ctrl + C.



2. Zatim, bez uklanjanja odabira iz istog fragmenta, ponovno kliknite na nju s desnom tipkom miša. Ovaj put na popisu akcije, kliknite na ikonu "Vrijednost", koja je objavljena u grupi parametara umetanja.



3. Nakon toga, sve reference u namjenskom rasponu bit će zamijenjene statičkim vrijednostima.

Kao što možete vidjeti, Excel ima načine i alate za povezivanje nekoliko stolova među sobom. U isto vrijeme, tablični podaci mogu biti na drugim listovima, pa čak iu različitim knjigama. Ako je potrebno, ova veza se može lako slomiti.