Rad sa povezanim tablicama u programu Microsoft Excel

Kada obavljate određene zadatke u Excelu, ponekad morate imati nekoliko tablica koje su također povezane. Odnosno, podaci iz jedne tablice se uvlače u drugu i kad se mijenjaju, vrijednosti se preračunavaju u sve povezane raspone tablica.

Povezane tablice vrlo su povoljne za upotrebu za obradu velike količine informacija. Staviti sve informacije u jednu tablicu, osim toga, ako nisu homogene, nije baš zgodno. Teško je raditi s takvim objektima i tražiti ih. Navedeni problem dizajniran je tako da se otkloni povezanim tablicama, informacije između kojih se distribuiraju, ali istodobno su međusobno povezane. Povezani rasponi tablica mogu se nalaziti ne samo unutar jednog lista ili u jednoj knjizi, već se mogu nalaziti i u zasebnim knjigama (datotekama). Posljednje dvije mogućnosti se najčešće koriste u praksi, jer je svrha ove tehnologije da se odmakne od nakupljanja podataka, a nakupljanje na jednoj stranici u osnovi ne rješava problem. Naučimo kako kreirati i kako raditi s ovom vrstom upravljanja podacima.

Izrada povezanih tablica

Prije svega, zaustavimo se na pitanju koje metode postoje mogućnosti za stvaranje odnosa između različitih raspona tablica.

1. metoda: direktno povezivanje tablica s formulom

Najlakši način povezivanja podataka je korištenje formula koje upućuju na druge raspone tablica. Zove se direktno vezanje. Ova je metoda intuitivna, budući da se s njom povezivanje izvodi na gotovo isti način kao i stvaranje veza do podataka u jednom tabličnom nizu.

Pogledajmo kako na primjeru veza može nastati izravnim vezivanjem. Imamo dva stola na dva lista. Na jednoj tabeli, obračun plaća se izračunava pomoću formule množenjem stope zaposlenika s jednim koeficijentom za sve.

Na drugom listu je tablica koja sadrži spisak zaposlenih sa njihovim platama. Lista zaposlenih u oba slučaja predstavljena je istim redosledom.

Neophodno je osigurati da se podaci o stopama s drugog lista povuku u odgovarajuće ćelije prve.

1. Na prvom listu odaberite prvu ćeliju u stupcu Ponuda. Stavili smo znak u to "=". Zatim kliknite na prečicu "List 2", koji se nalazi na lijevoj strani Excel sučelja iznad statusne trake.



2. Prelazi na drugo područje dokumenta. Kliknemo na prvu ćeliju u koloni Ponuda. Zatim kliknite na gumb Unesite na tastaturi za unos podataka u ćeliju u kojoj je znak prethodno postavljen jednako.



3. Zatim slijedi automatski prelazak na prvi list. Kao što vidite, stopa prvog zaposlenika iz druge tablice povlači se u odgovarajuću ćeliju. Postavljanjem kursora na ćeliju koja sadrži okladu, vidimo da se za prikaz podataka na ekranu koristi uobičajena formula. Ali prije koordinata ćelije iz koje se podaci iznose, postoji izraz "Sheet2!", što označava naziv područja dokumenta gdje se nalaze. Opća formula u našem slučaju izgleda ovako:

   = Sheet2! B2



4. Sada morate prenijeti podatke o stopama svih ostalih zaposlenih u preduzeću. Naravno, to se može učiniti na isti način na koji smo izvršili zadatak za prvog zaposlenika, ali s obzirom na to da su obje liste zaposlenih raspoređene istim redoslijedom, zadatak se može značajno pojednostaviti i ubrzati njegovo rješenje. To se može učiniti jednostavnim kopiranjem formule u niže raspon. Zbog činjenice da su veze u programu Excel prema zadanom relativne, kada se kopiraju, vrijednosti se pomiču, što je ono što nam treba. Sam postupak kopiranja može se obaviti pomoću markera za ispunjavanje.

   Dakle, stavite kursor u donji desni dio elementa s formulom. Nakon toga, kursor treba pretvoriti u marker za ispunu u obliku crnog križa. Stisnite lijevu tipku miša i povucite kursor do samog dna stupca.



5. Svi podaci iz sličnog stupca na List 2 bili su zavučeni u stol na List 1. Prilikom promjene podataka u List 2 oni će se automatski promijeniti na prvu.

Metoda 2: korištenje hrpe INDEX operatora - PRETRAŽI

Ali što ako popis zaposlenih u nizu tablica nije istim redoslijedom? U ovom slučaju, kao što je spomenuto ranije, jedna od opcija je uspostaviti vezu između svake od tih ćelija koje bi trebalo povezati ručno. Ali ovo je prikladno samo za male stolove. Za ogromne domete takva opcija će u najboljem slučaju trebati puno vremena, a u najgorem slučaju to u praksi neće biti izvedivo. Ali ovaj se problem može riješiti pomoću hrpe operatora INDEX - PRETRAŽI. Pogledajmo kako se to može učiniti povezivanjem podataka u rasponima tablica o kojima je razgovarano u prethodnoj metodi.

1. Odaberite prvi element stupca Ponuda. Idi na Čarobnjak za funkcijeklikom na ikonu "Umetanje funkcije".



2. In Čarobnjak za funkcije u grupi Upućivanja i nizovi pronađite i istaknite ime INDEX.



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



4. Pokrenut je prozor argumenata operatora INDEX. Zadatak ove funkcije je prikazati vrijednost koja se nalazi u odabranom rasponu u liniji s navedenim brojem. Opća formula operatora INDEX takav je:

   = INDEX (niz; red_number; [stupac_broj])

   Niz - argument koji sadrži adresu raspona iz kojeg ćemo izvući informacije brojem navedenog retka.

   Broj linije - argument, koji je broj ove same linije. Važno je znati da broj retka ne smije biti naveden u odnosu na cijeli dokument, već samo u odnosu na odabrani niz.

   Broj stupca - argument koji nije obavezan. Nećemo ga koristiti za rješavanje našeg specifičnog problema, pa stoga nije potrebno posebno opisivati ​​njegovu suštinu.

   Stavite kursor u polje Niz. Nakon toga, idite na List 2 a držeći levu tipku miša odaberite sav sadržaj stupca Ponuda.



5. Nakon što su koordinate prikazane u prozoru operatera, u polje postavite kursor Broj linije. Ovaj argument ćemo iznijeti pomoću operatora PRETRAŽI. Stoga kliknemo na trokut koji se nalazi s lijeve strane funkcionalne linije. Otvara se popis nedavno korištenih operatera. Ako među njima pronađete neko ime "PRETRAŽI"onda možete kliknuti na njega. U suprotnom, kliknite zadnju stavku na listi - "Ostale karakteristike ...".



6. Pokreće se standardni prozor Čarobnjaci za funkcije. Prolazimo u istoj grupi Upućivanja i nizovi. Ovog puta odaberite stavku na listi "PRETRAŽI". Kliknite na dugme. "OK".



7. Aktivira se prozor argumenata operatora PRETRAŽI. Navedena funkcija služi za prikazivanje broja vrijednosti u određenom nizu prema njenom imenu. Zahvaljujući ovoj značajki izračunat ćemo broj linije određene vrijednosti za funkciju INDEX. Sintaksa PRETRAŽI predstavljeno kako slijedi:

   = SEARCH (search_value; lookup_array; [match_type])

   "Tražim vrijednost" - argument koji sadrži ime ili adresu ćelije raspona treće strane u kojoj se nalazi. Položaj ovog imena u ciljnom rasponu trebao bi se izračunati. U našem slučaju prvi argument će biti reference na ćelije na List 1gdje se nalaze imena zaposlenih.

   Gledano polje - argument koji predstavlja referencu na niz u kojem se traži određena vrijednost da bi se odredila njena pozicija. Adresa stupca "igraće tu ulogu ovdje".Ime na List 2.

   Vrsta podudaranja - argument, koji je neobavezan, ali za razliku od prethodne izjave, trebat će nam ovaj neobavezni argument. Ukazuje kako će operator uskladiti vrijednost pretraživanja s nizom. Ovaj argument može imati jednu od tri vrijednosti: -1; 0; 1. Za neuređene nizove odaberite "0". Ova je opcija pogodna za naš slučaj.

   Dakle, krenimo s popunjavanjem polja prozora argumenata. Stavite kursor u polje "Tražim vrijednost"kliknite na prvu ćeliju stupca "Ime" na List 1.



8. Nakon prikaza koordinata, postavite kursor u polje Gledano polje i kliknite na prečicu "List 2", koji se nalazi na dnu prozora Excela iznad trake statusa. Držite lijevu tipku miša i pokazivačem odaberite sve ćelije u stupcu "Ime".



9. Nakon što su njihove koordinate prikazane u polju Gledano poljeidi na teren Vrsta podudaranja i tamo postavite broj sa tastature "0". Nakon toga ponovo se vraćamo na teren Gledano polje. Činjenica je da ćemo formulu kopirati, kao što smo to radili u prethodnoj metodi. Dogodit će se promjena adrese, ali ovdje moramo popraviti koordinate matrice koji se pregledava. Ne bi ga trebalo preseliti. Odaberite koordinate kursorom i pritisnite funkcijsku tipku F4. Kao što vidite, znak dolara prikazao se ispred koordinata, što znači da se veza pretvorila iz relativne u apsolutnu. Zatim kliknite na gumb "OK".



10. Rezultat je prikazan u prvoj ćeliji stupca. Ponuda. Prije kopiranja trebamo popraviti drugo područje, naime prvi argument funkcije INDEX. Da biste to učinili, odaberite element stupca koji sadrži formulu i prijeđite na redak formula. Odaberemo prvi argument operatora INDEX (B2: B7) i kliknite na gumb F4. Kao što vidite, znak dolara prikazao se blizu odabranih koordinata. Kliknite na dugme Unesite. Općenito, formula je imala sljedeći oblik:

    = INDEX (Sheet2! $ B $ 2: $ B $ 7; PRETRAŽI (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))



11. Sada možete kopirati pomoću markera za ispunjavanje. Nazivamo ga na isti način na koji smo ranije govorili i protežemo ga do kraja raspona tablice.



12. Kao što vidite, uprkos činjenici da se redoslijed reda dviju povezanih tablica ne podudara, sve vrijednosti se povlače prema imenima zaposlenih. To je postignuto upotrebom kombinacije operatera INDEX-PRETRAŽI.

Pročitajte i:
EXEX funkcija u Excelu
EXCEL funkcija u Excelu

Treća metoda: izvođenje matematičkih operacija sa srodnim podacima

Izravno vezanje podataka je također dobro jer vam omogućuje ne samo prikazivanje vrijednosti koje su prikazane u drugim rasponima tablica u jednoj od tablica, već i obavljanje različitih matematičkih operacija s njima (sabiranje, dijeljenje, oduzimanje, množenje itd.).

Pogledajmo kako se to provodi u praksi. Hajde da napravimo to Shema 3 opći podaci o platama za kompaniju bit će prikazani bez probijanja zaposlenika. Da biste to učinili, povući će se stope zaposlenika List 2, sažeto (pomoću funkcije) SUM) i množiti s koeficijentom koristeći formulu.

1. Odaberite ćeliju u kojoj će se prikazati rezultat obračuna plaća. Shema 3. Kliknite na dugme. "Umetanje funkcije".



2. Prozor bi trebao započeti Čarobnjaci za funkcije. Idi u grupu "Matematički" i tamo odaberite ime SUM. Zatim kliknite na dugme "OK".



3. Argumenti funkcija premještaju se u prozor SUM, koja je zamišljena za izračunavanje zbrajanja odabranih brojeva. Ima sljedeću sintaksu:

   = SUM (broj1; broj2; ...)

   Polja u prozoru odgovaraju argumentima navedene funkcije. Iako njihov broj može doseći 255, samo jedan će biti dovoljan za našu svrhu. Stavite kursor u polje "Broj1". Kliknite na prečicu "List 2" iznad statusne trake.



4. Nakon što smo prešli na željeni dio knjige, odaberite stupac koji treba sažeti. To radimo kursorom dok držimo lijevu tipku miša. Kao što vidite, koordinate odabranog područja odmah se prikazuju u polju prozora argumenata. Zatim kliknite na gumb "OK".



5. Nakon toga automatski se prelazimo na List 1. Kao što vidite, ukupni iznos ponuda zaposlenika već je prikazan u odgovarajućem elementu.



6. Ali to nije sve. Kao što se sjećamo, plaća se izračunava množenjem vrijednosti stope s faktorom. Stoga ponovo odabiremo ćeliju u kojoj se nalazi zbrojena vrijednost. Nakon toga prelazimo na red formula. Dodajte formuli u njoj znak množenja (*), a zatim kliknite na element u kojem se nalazi pokazatelj koeficijenta. Da biste izvršili izračunavanje, kliknite na dugme Unesite na tastaturi. Kao što vidite, program je izračunao ukupnu platu za preduzeće.



7. Povratak na List 2 i promenite stopu bilo kojeg zaposlenog.



8. Nakon toga opet prelazimo na stranicu s ukupnim iznosom. Kao što vidite, zbog promjena u povezanoj tablici, rezultat ukupne plaće automatski je preračunan.

Metoda 4: prilagođeni umetak

Niz tablica možete povezati i u Excelu pomoću posebnog umetka.

1. Odaberemo vrijednosti koje će biti potrebno "uvući" u drugu tablicu. U našem slučaju to je raspon kolone Ponuda na List 2. Desnim gumbom miša kliknemo na odabrani fragment. Na listi koja se otvori odaberite Kopiraj. Alternativna prečica za tipkovnicu je Ctrl + C. Nakon toga prelazimo na List 1.



2. Premještajući se na područje knjige koja nam je potrebna, odabiremo ćelije u koje ćemo morati podići vrijednosti. U našem slučaju ovo je kolona Ponuda. Desnim gumbom miša kliknemo na odabrani fragment. U kontekstnom meniju u bloku alata Umetanje opcija kliknite na ikonu Paste Link.

   

   Postoji i alternativa. Uzgred, ona je jedina za starije verzije Excela. Zadržite pokazivač u kontekstnom meniju "Specijalni umetak". U dodatnom meniju koji se otvorio odaberite položaj s istim nazivom.



3. Nakon toga se otvara poseban prozor za umetanje. Kliknite na dugme Paste Link u donjem lijevom uglu ćelije.



4. Bez obzira koju opciju odaberete, vrijednosti iz jednog niza tablica bit će umetnute u drugu. Kada mijenjaju podatke u izvoru, automatski će se mijenjati i u umetnutom rasponu.

Lekcija: Posebni ulomak u Excelu

5. način: povezivanje tablica u više knjiga

Pored toga u različitim knjigama možete organizirati komunikaciju između područja tablice. Koristi se poseban alat za umetanje. Radnje će biti apsolutno slične onima koje smo razmatrali u prethodnoj metodi, osim što ćete morati kretati dok izrađujete formule ne između područja iste knjige, već između datoteka. Naravno da bi sve knjige morale biti otvorene.

1. Odaberite raspon podataka koje želite prenijeti u drugu knjigu. Kliknite desnom tipkom miša i odaberite poziciju u meniju koji se otvori. Kopiraj.



2. Zatim prelazimo na knjigu u koju će te podatke trebati umetnuti. Odaberite željeni raspon. Kliknite desnim klikom. U kontekstnom meniju u grupi Umetanje opcija odaberite stavku Paste Link.



3. Nakon toga će se vrijednosti umetnuti. Kada se podaci iz izvorne radne knjige promijene, niz tablica iz radne knjige automatski će je povući prema gore. Štaviše, nije potrebno da obje knjige budu otvorene za to. Dovoljno je otvoriti samo jednu radnu knjižicu i automatski će izvući podatke iz zatvorenog povezanog dokumenta ako su u njega prethodno izvršene promjene.

Ali treba napomenuti da će u ovom slučaju umetak biti izrađen kao nepromjenjivi niz. Kada pokušate promijeniti bilo koju ćeliju s umetnutim podacima, pojavit će se poruka koja vas obavještava da je to nemoguće.

Promjene u takvom nizu povezanom s drugom knjigom mogu se izvršiti samo prekidom veze.

Razmak između stolova

Ponekad morate prekinuti vezu između raspona tablica. Razlog za to može biti gore opisani slučaj, kada trebate promijeniti niz umetnut iz druge knjige, ili jednostavno nevoljkost korisnika da se podaci iz jedne tablice automatski ažuriraju iz druge.

1. metoda: prekidanje veze između knjiga

Možete prekinuti vezu između knjiga u svim ćelijama izvodeći gotovo jednu operaciju. Istovremeno će podaci u ćelijama ostati, ali oni će već biti statičke vrijednosti koje se ne mogu ažurirati, a koje nikako ne ovise o drugim dokumentima.

1. U knjizi u koju su izvučene vrijednosti iz drugih datoteka idite na karticu "Podaci". Kliknite na ikonu "Promijenite komunikacije"koji se nalazi na vrpci u okviru s alatima Veze. Treba napomenuti da ako trenutna knjiga ne sadrži poveznice na druge datoteke, ovaj gumb je neaktivan.



2. Pokrenut je prozor za promjenu veza. S popisa povezanih knjiga (ako ih ima nekoliko) odabiremo datoteku s kojom želimo prekinuti vezu. Kliknite na dugme Prekini vezu.



3. Otvara se informativni prozor u kojem se upozorava na posljedice daljnjih radnji. Ako ste sigurni što ćete učiniti, kliknite na gumb "Prekid veze".



4. Nakon toga sve veze do navedene datoteke u trenutnom dokumentu bit će zamijenjene statičkim vrijednostima.

Metoda 2: Umetanje vrijednosti

Ali gornja metoda je pogodna samo ako trebate potpuno prekinuti sve veze između dviju knjiga. Što učiniti ako trebate odvojiti povezane tablice koje se nalaze u istoj datoteci? To možete učiniti tako da kopirate podatke i zatim ih zalijepite na isto mjesto kao i vrijednosti. Usput, na isti način možete prekinuti vezu između pojedinih raspona podataka različitih knjiga bez prekida opće veze između datoteka. Pogledajmo kako ova metoda funkcionira u praksi.

1. Odaberite raspon u kojem želimo ukloniti vezu do druge tablice. Kliknemo na njega desnom tipkom miša. U meniju koji se otvori odaberite Kopiraj. Umjesto ovih radnji možete upisati alternativnu kombinaciju vrućih tipki Ctrl + C.



2. Nadalje, bez uklanjanja odabira iz istog fragmenta, ponovo desnim klikom na njega. Ovaj put na listi akcija kliknite na ikonu "Vrijednosti"koja se nalazi u grupi alata Umetanje opcija.



3. Nakon toga sve veze u odabranom rasponu bit će zamijenjene statičkim vrijednostima.

Kao što vidite, u Excelu postoje načini i alati za povezivanje nekoliko tablica. Istovremeno, tablični podaci mogu se nalaziti na drugim listovima, pa čak i u različitim knjigama. Po potrebi se ova veza može lako prekinuti.