Kako povezati podatke između listova u Excelu. Kako napraviti vezu između ćelija u excelu? Povezivanje više radnih listova

Povezana tabela je zbirka podataka kojima se može upravljati kao jedna jedinica.

Koristite dugme da kreirate povezanu tabelu "Formatiraj kao tabelu" na panelu "stilovi" trake "Dom".

Odaberite željeni stil za buduću tablicu i odredite raspon ćelija na osnovu kojih će se kreirati povezana tablica.

Nakon podešavanja svih parametara, dobićemo pridruženu tabelu. Dugmad sa strelicama će se pojaviti pored naslova kolona (slično filtriranju), a kontekstualni alat će se pojaviti u prozoru programa "Rad sa stolovima" koji sadrži traku "konstruktor".

Svaka povezana tabela ima jedinstveno ime. Podrazumevano je "Tabela_broj". Možete promijeniti naziv tabele u panelu "Svojstva".

Na panelu "Stilovi stolova" Možete, ako je potrebno, promijeniti ili prilagoditi stil povezane tablice.

Povezanu tabelu možete ponovo pretvoriti u redovan raspon ćelija. Za ovo postoji dugme "Pretvori u domet" na panelu "Usluga" kontekstualni feed "konstruktor".

Kao što je ranije spomenuto, pored zaglavlja kolona nalaze se gumbi za sortiranje i filtriranje koji vam omogućavaju sortiranje podataka i primjenu filtera.

U povezanoj tabeli možete izračunati ukupne iznose kolona. Da biste to učinili, označite polje "Ukupni red" na panelu "Opcije stila stola".

Pomoću padajuće liste možete odabrati željenu vrijednost konačne funkcije.

Možete dodati/ukloniti redove i kolone u povezanu tabelu.

To se može učiniti na nekoliko načina.

1. Koristite dugme "Promijeni veličinu tabele" na panelu "Svojstva".

2. Postavite kursor u ćeliju povezane tabele, pored koje treba da dodate novu kolonu (red) i u panel "ćelije" trake "Dom" koristite dugme "ubaci".

Prilikom obavljanja određenih zadataka u Excel-u ponekad morate raditi s nekoliko tabela, koje su također međusobno povezane. Odnosno, podaci iz jedne tabele se povlače u druge i kada se promene, vrednosti u svim pridruženim rasponima tabele se ponovo izračunavaju.

Povezane tabele su veoma zgodne za upotrebu za obradu velikih količina informacija. Imati sve informacije u jednoj tabeli, osim toga, ako nije homogena, nije baš zgodno. Teško je raditi s takvim objektima i tražiti ih. Povezane tabele, informacije između kojih su raspoređene, ali u isto vreme međusobno povezane, dizajnirane su da eliminišu ovaj problem. Povezani opsezi tabela mogu se nalaziti ne samo unutar jednog lista ili jedne radne knjige, već i u zasebnim radnim sveskama (datotekama). Posljednje dvije opcije se najčešće koriste u praksi, jer je svrha ove tehnologije upravo da se pobjegne od gomilanja podataka, a njihovo gomilanje na jednoj stranici ne rješava suštinski problem. Naučimo kako kreirati i raditi s ovom vrstom upravljanja podacima.

Prije svega, pogledajmo pitanje kako je moguće stvoriti odnos između različitih raspona tablica.

Metoda 1: Direktno povezivanje tabela sa formulom

Najlakši način za povezivanje podataka je korištenje formula koje upućuju na druge raspone tablica. To se zove direktno vezivanje. Ova metoda je intuitivna jer izvodi povezivanje na gotovo isti način kao i kreiranje veza do podataka u jednom nizu tablice.

Pogledajmo kako se, koristeći primjer, veza može formirati direktnim vezivanjem. Imamo dva stola na dva lista. Na jednoj tabeli, plate se izračunavaju pomoću formule množenjem stope radnika sa koeficijentom zajedničkim za sve.

Drugi list sadrži raspon tabele sa spiskom zaposlenih sa njihovim platama. Spisak zaposlenih u oba slučaja je prikazan istim redosledom.

Potrebno je osigurati da se podaci o stopama iz drugog lista povuku u odgovarajuće ćelije prvog.

Metoda 2: korištenje kombinacije INDEX - SEARCH operatora

Ali šta učiniti ako lista zaposlenih u nizovima tabele nije u istom redosledu? U ovom slučaju, kao što je ranije objašnjeno, jedna opcija je da se ručno uspostavi veza između svake od onih ćelija koje treba povezati. Ali ovo je pogodno samo za male stolove. Za velike opsege, takva opcija će, u najboljem slučaju, zahtijevati dosta vremena za implementaciju, au najgorem slučaju, u praksi uopće neće biti izvodljiva. Ali ovaj problem se može riješiti korištenjem gomile operatora INDEX – TRAŽI. Pogledajmo kako se to može učiniti povezivanjem podataka u tabelarne opsege, o čemu je bilo riječi u prethodnoj metodi.

1. Odaberite prvi element kolone "Ponuda". Idemo na Čarobnjak za funkcije klikom na ikonu "Insert Function".



2. IN Čarobnjak za funkcije u grupi "Veze i nizovi" pronađite i označite ime "INDEX".



3. Ovaj operator ima dva oblika: obrazac za rad sa nizovima i referentni obrazac. U našem slučaju, prva opcija je obavezna, pa u sljedećem prozoru za odabir obrasca koji se otvori odaberite je i kliknite na dugme "UREDU".



4. Pokreće se prozor argumenata operatora INDEX. Zadatak navedene funkcije je da prikaže vrijednost koja se nalazi u odabranom rasponu u redu sa navedenim brojem. Opća formula operatora INDEX je li ovo:

   INDEX(niz, broj_reda, [broj_stupca])

   "niz"— argument koji sadrži adresu opsega iz kojeg ćemo izdvojiti informacije po broju navedenog reda.

   "broj linije"- argument koji je broj ove linije. Važno je znati da broj reda ne treba specificirati u odnosu na cijeli dokument, već samo u odnosu na odabrani niz.

   "Broj kolone"- neobavezni argument. Nećemo ga koristiti za rješavanje našeg specifičnog problema, pa stoga nema potrebe posebno opisivati ​​njegovu suštinu.

   Postavite kursor u polje "niz". Nakon ovoga idemo na List 2 i, držeći lijevu tipku miša, odaberite cijeli sadržaj kolone "Ponuda".



5. Nakon što se koordinate prikažu u prozoru operatera, postavite kursor u polje "broj linije". Ovaj argument ćemo ispisati koristeći operator TRAŽI. Stoga kliknemo na trokut koji se nalazi lijevo od funkcijske linije. Otvara se lista nedavno korištenih operatera. Ako nađete ime među njima "TRAŽI", onda možete kliknuti na njega. U suprotnom, kliknite na poslednju stavku na listi - "Druge funkcije...".



6. Otvara se standardni prozor Čarobnjaci funkcija. Idemo u istu grupu "Veze i nizovi". Ovaj put odabiremo stavku sa liste "TRAŽI". Kliknite na dugme "UREDU".



7. Prozor argumenata operatora je aktiviran TRAŽI. Ova funkcija je dizajnirana da prikaže broj vrijednosti u određenom nizu po imenu. Zahvaljujući ovoj osobini izračunat ćemo broj reda određene vrijednosti za funkciju INDEX. Sintaksa TRAŽI predstavljeno ovako:

   MATCH(vrijednost_pretraživanja, niz_potraživanja, [tip_podudaranja])

   "Vrijednost pretrage"— argument koji sadrži ime ili adresu ćelije u opsegu treće strane u kojem se nalazi. Treba izračunati poziciju ove stavke u ciljnom rasponu. U našem slučaju, prvi argument će biti reference ćelije na List 1, u kojoj se nalaze imena zaposlenih.

   "Pored pregledavanja"- argument koji je referenca na niz u kojem se traži određena vrijednost da bi se odredila njena pozicija. Za nas će tu ulogu imati adresa rubrike “ ime" on List 2.

   "Vrsta podudaranja" je neobavezan argument, ali, za razliku od prethodnog operatora, trebat će nam ovaj opcioni argument. Određuje kako će operator upariti vrijednost pretraživanja s nizom. Ovaj argument može imati jedno od tri značenja: -1 ; 0 ; 1 . Za neuređene nizove trebate odabrati opciju «0» . Ova opcija je prikladna za naš slučaj.

   Dakle, počnimo da popunjavamo polja prozora sa argumentima. Postavite kursor u polje "Vrijednost pretrage", kliknite na prvu ćeliju kolone "ime" on List 1.



8. Nakon što se koordinate prikažu, postavite kursor u polje "Pored pregledavanja" i idite na prečicu "list 2", koji se nalazi na dnu prozora programa Excel iznad statusne trake. Držite lijevu tipku miša i odaberite sve ćelije kolone pomoću kursora "ime".



9. Nakon što su njihove koordinate prikazane u polju "Pored pregledavanja", idi na teren "Vrsta podudaranja" i koristite tastaturu da postavite broj tamo «0» . Nakon toga se ponovo vraćamo na teren "Pored pregledavanja". Činjenica je da ćemo kopirati formulu, kao što smo to učinili u prethodnoj metodi. Doći će do pomaka u adresama, ali moramo popraviti koordinate niza koji se gleda. Ne bi trebalo da se pomera. Odaberite koordinate kursorom i pritisnite funkcijsku tipku F4. Kao što vidite, ispred koordinata se pojavio znak dolara, što znači da je referenca okrenuta iz relativne u apsolutnu. Zatim kliknite na dugme "UREDU".



10. Rezultat se prikazuje u prvoj ćeliji kolone "Ponuda". Ali prije nego što kopiramo, moramo osigurati još jedno područje, odnosno prvi argument funkcije INDEX. Da biste to učinili, odaberite element stupca koji sadrži formulu i pomaknite se na traku formule. Odabir prvog argumenta operatora INDEX (B2:B7) i kliknite na dugme F4. Kao što vidite, u blizini odabranih koordinata pojavljuje se znak dolara. Kliknite na ključ Enter. Generalno, formula je imala sljedeći oblik:

    INDEX(List2!$B$2:$B$7,MACH(List1!A4,List2!$A$2:$A$7,0))



11. Sada možete kopirati pomoću ručke za popunjavanje. Zovemo ga na isti način na koji smo ranije govorili i protežemo ga do kraja raspona tabele.



12. Kao što vidimo, uprkos činjenici da se redosled redova dve povezane tabele ne poklapa, sve vrednosti su ipak prilagođene prema prezimenima zaposlenih. Ovo je postignuto upotrebom kombinacije operatora INDEX—TRAŽI.

Prilikom obavljanja određenih zadataka u Excel-u ponekad morate raditi s nekoliko tabela, koje su također međusobno povezane. Odnosno, podaci iz jedne tabele se povlače u druge i kada se promene, vrednosti u svim pridruženim rasponima tabele se ponovo izračunavaju.

Povezane tabele su veoma zgodne za upotrebu za obradu velikih količina informacija. Imati sve informacije u jednoj tabeli, osim toga, ako nije homogena, nije baš zgodno. Teško je raditi s takvim objektima i tražiti ih. Povezane tabele, informacije između kojih su raspoređene, ali u isto vreme međusobno povezane, dizajnirane su da eliminišu ovaj problem. Povezani opsezi tabela mogu se nalaziti ne samo unutar jednog lista ili jedne radne knjige, već i u zasebnim radnim sveskama (datotekama). Posljednje dvije opcije se najčešće koriste u praksi, jer je svrha ove tehnologije upravo da se pobjegne od gomilanja podataka, a njihovo gomilanje na jednoj stranici ne rješava suštinski problem. Naučimo kako kreirati i raditi s ovom vrstom upravljanja podacima.

Kreiranje povezanih tabela

Prije svega, pogledajmo pitanje kako je moguće stvoriti odnos između različitih raspona tablica.

Metoda 1: Direktno povezivanje tabela sa formulom

Najlakši način za povezivanje podataka je korištenje formula koje upućuju na druge raspone tablica. To se zove direktno vezivanje. Ova metoda je intuitivna jer izvodi povezivanje na gotovo isti način kao i kreiranje veza do podataka u jednom nizu tablice.

Pogledajmo kako se, koristeći primjer, veza može formirati direktnim vezivanjem. Imamo dva stola na dva lista. Na jednoj tabeli, plate se izračunavaju pomoću formule množenjem stope radnika sa koeficijentom zajedničkim za sve.

Drugi list sadrži raspon tabele sa spiskom zaposlenih sa njihovim platama. Spisak zaposlenih u oba slučaja je prikazan istim redosledom.

Potrebno je osigurati da se podaci o stopama iz drugog lista povuku u odgovarajuće ćelije prvog.

1. Na prvom listu izaberite prvu ćeliju kolone „Oklada“. U njega stavljamo znak "=". Zatim kliknite na karticu "Sheet 2", koja se nalazi na lijevoj strani Excel sučelja iznad statusne trake.
2. Prelazi na drugo područje dokumenta. Kliknite na prvu ćeliju u koloni “Oklada”. Zatim kliknite na dugme Enter na tastaturi da unesete podatke u ćeliju u kojoj ste prethodno postavili znak jednakosti.
3. Zatim dolazi do automatskog prijelaza na prvi list. Kao što vidite, stopa prvog zaposlenika iz druge tabele se povlači u odgovarajuću ćeliju. Postavljanjem kursora na ćeliju u kojoj se nalazi opklada, vidimo da se za prikaz podataka na ekranu koristi uobičajena formula. Ali ispred koordinata ćelije iz koje se izlaze podaci, nalazi se izraz “Sheet2!”, koji označava naziv oblasti dokumenta u kojoj se nalaze. Opća formula u našem slučaju izgleda ovako:
4. Sada morate prenijeti podatke o stopama svih ostalih zaposlenih u preduzeću. Naravno, to se može uraditi na isti način kao što smo završili zadatak za prvog zaposlenog, ali s obzirom na to da se obje liste zaposlenih nalaze istim redoslijedom, zadatak se može značajno pojednostaviti i ubrzati njegovo rješavanje. To se može učiniti jednostavnim kopiranjem formule u raspon ispod. Zbog činjenice da su veze u Excel-u relativne po defaultu, kada se kopiraju, vrijednosti se pomjeraju, što nam je potrebno. Sam postupak kopiranja može se obaviti pomoću markera za popunjavanje.

   Dakle, postavite kursor u donje desno područje elementa sa formulom. Nakon toga, kursor bi se trebao transformirati u marker za popunjavanje u obliku crnog križa. Držite lijevu tipku miša i povucite kursor do samog dna kolone.

5. Svi podaci iz slične kolone na listu 2 su povučeni u tabelu na listu 1. Kada se podaci na listu 2 promijene, automatski će se promijeniti na prvom.

Metoda 2: korištenje kombinacije INDEX - SEARCH operatora

Ali šta učiniti ako lista zaposlenih u nizovima tabele nije u istom redosledu? U ovom slučaju, kao što je ranije objašnjeno, jedna opcija je da se ručno uspostavi veza između svake od onih ćelija koje treba povezati. Ali ovo je pogodno samo za male stolove. Za velike opsege, takva opcija će, u najboljem slučaju, zahtijevati dosta vremena za implementaciju, au najgorem slučaju, u praksi uopće neće biti izvodljiva. Ali ovaj problem se može riješiti kombinacijom INDEX - SEARCH operatora. Pogledajmo kako se to može učiniti povezivanjem podataka u tabelarne opsege, o čemu je bilo riječi u prethodnoj metodi.

1. Odaberite prvi element kolone “Oklada”. Idite na Čarobnjak za funkcije tako što ćete kliknuti na ikonu “Insert Function”.
2. U čarobnjaku za funkcije, u grupi “Veze i nizovi”, pronađite i odaberite naziv “INDEX”.
3. Ovaj operator ima dva oblika: obrazac za rad sa nizovima i referentni obrazac. U našem slučaju, prva opcija je obavezna, pa je u sljedećem prozoru za odabir obrasca koji se otvori odaberite i kliknite na dugme “OK”.
4. Prozor argumenata INDEX operatora je pokrenut. Zadatak navedene funkcije je da prikaže vrijednost koja se nalazi u odabranom rasponu u redu sa navedenim brojem. Opća formula za operator INDEX je:

   INDEX(niz;broj_reda;)

   “Niz” je argument koji sadrži adresu opsega iz kojeg ćemo izdvojiti informacije po broju navedene linije.

   “Broj reda” je argument koji je broj upravo ove linije. Važno je znati da broj reda ne treba specificirati u odnosu na cijeli dokument, već samo u odnosu na odabrani niz.

   “Broj kolone” je neobavezni argument. Nećemo ga koristiti za rješavanje našeg specifičnog problema, pa stoga nema potrebe posebno opisivati ​​njegovu suštinu.

   Postavite kursor u polje „Niz“. Nakon toga idite na List 2 i, držeći lijevu tipku miša, odaberite cijeli sadržaj kolone „Oklada“.

5. Nakon što se koordinate prikažu u prozoru operatera, postavite kursor u polje „Broj linije“. Ovaj argument ćemo ispisati koristeći MATCH operator. Stoga kliknemo na trokut koji se nalazi lijevo od funkcijske linije. Otvara se lista nedavno korištenih operatera. Ako među njima pronađete naziv „TRAŽI“, možete kliknuti na njega. U suprotnom, kliknite na poslednju stavku na listi – „Ostale funkcije...“.
6. Otvara se standardni prozor čarobnjaka za funkcije. U njemu idemo u istu grupu "Veze i nizovi". Ovog puta sa liste izaberite stavku “SEARCH”. Kliknite na dugme “OK”.
7. Prozor argumenata MATCH operatora je aktiviran. Ova funkcija je dizajnirana da prikaže broj vrijednosti u određenom nizu po imenu. Zahvaljujući ovoj osobini izračunat ćemo broj reda određene vrijednosti za funkciju INDEX. MATCH sintaksa je predstavljena na sljedeći način:

   MATCH(vrijednost_pretraživanja, traženi_niz;)

   “Vrijednost pretraživanja” je argument koji sadrži ime ili adresu ćelije u rasponu treće strane u kojem se nalazi. Treba izračunati poziciju ove stavke u ciljnom rasponu. U našem slučaju, prvi argument će biti veze do ćelija na Sheet-u 1 u kojima se nalaze imena zaposlenih.

   "Niz za pregled" je argument koji je referenca na niz u kojem se traži navedena vrijednost kako bi se odredila njena pozicija. Za nas će tu ulogu imati adresa kolone „Ime“ na listu 2.

   "Tip podudaranja" je neobavezni argument, ali za razliku od prethodne izjave, trebat će nam ovaj neobavezni argument. Određuje kako će operator upariti vrijednost pretraživanja s nizom. Ovaj argument može imati jednu od tri vrijednosti: -1; 0; 1. Za neuređene nizove odaberite opciju “0”. Ova opcija je prikladna za naš slučaj.

   Dakle, počnimo da popunjavamo polja prozora sa argumentima. Postavite kursor u polje „Vrednost za pretragu“, kliknite na prvu ćeliju kolone „Ime“ na listu 1.

8. Nakon što se koordinate prikažu, postavite kursor u polje „Vidljivi niz“ i idite na prečicu „List 2“, koja se nalazi na dnu prozora Excel iznad statusne trake. Držite lijevu tipku miša i označite kursorom sve ćelije u koloni „Ime“.
9. Nakon što se njihove koordinate prikažu u polju „Viewed Array“, idite na polje „Matching Type“ i pomoću tastature postavite broj „0“. Nakon toga, ponovo se vraćamo na polje „Pregledano polje“. Činjenica je da ćemo kopirati formulu, kao što smo to učinili u prethodnoj metodi. Doći će do pomaka u adresama, ali moramo popraviti koordinate niza koji se gleda. Ne bi trebalo da se pomera. Odaberite koordinate kursorom i pritisnite funkcijsku tipku F4. Kao što vidite, ispred koordinata se pojavio znak dolara, što znači da je referenca okrenuta iz relativne u apsolutnu. Zatim kliknite na dugme “OK”.
10. Rezultat se prikazuje na ekranu u prvoj ćeliji kolone „Oklada“. Ali prije nego što kopiramo, moramo osigurati još jedno područje, odnosno prvi argument funkcije INDEX. Da biste to učinili, odaberite element stupca koji sadrži formulu i pomaknite se na traku formule. Odaberite prvi argument operatora INDEX (B2:B7) i kliknite na dugme F4. Kao što vidite, u blizini odabranih koordinata pojavljuje se znak dolara. Kliknite na tipku Enter. Generalno, formula je imala sljedeći oblik:

    INDEX(List2!$B$2:$B$7,MACH(List1!A4,List2!$A$2:$A$7,0))

11. Sada možete kopirati pomoću ručke za popunjavanje. Zovemo ga na isti način na koji smo ranije govorili i protežemo ga do kraja raspona tabele.
12. Kao što vidimo, uprkos činjenici da se redosled redova dve povezane tabele ne poklapa, sve vrednosti su ipak prilagođene prema prezimenima zaposlenih. Ovo je postignuto upotrebom kombinacije INDEX-MATCH operatora.

Pročitajte također:

Funkcija INDEX u ExcelMATCH funkcija u Excelu

Metoda 3: Izvođenje matematičkih operacija nad povezanim podacima

Direktno povezivanje podataka je također dobro jer vam omogućava ne samo da prikažete vrijednosti koje su prikazane u drugim rasponima tablica u jednoj od tablica, već i da s njima izvodite različite matematičke operacije (sabiranje, dijeljenje, oduzimanje, množenje itd. ).

Pogledajmo kako se to radi u praksi. Napravimo tako da će na listu 3 biti prikazani opšti podaci o platama za preduzeće bez razlaganja po zaposlenima. Da biste to učinili, stope zaposlenika će se izvući iz Tablice 2, zbrati (pomoću funkcije SUM) i pomnožiti sa koeficijentom koristeći formulu.

1. Odaberite ćeliju u kojoj će rezultati obračuna platnog spiska biti prikazani na listu 3. Kliknite na dugme „Ubaci funkciju“.
2. Prozor čarobnjaka za funkcije trebao bi se pokrenuti. Idite na grupu "Matematički" i tamo odaberite naziv "SUM". Zatim kliknite na dugme “OK”.
3. Funkcija SUM, koja je dizajnirana da izračuna zbir odabranih brojeva, premješta se u prozor za argumente. Ima sljedeću sintaksu:

   ZBIR(broj1;broj2;…)

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

4. Nakon što smo prešli na željeni dio knjige, odabiremo kolonu koju treba zbrojiti. To radimo sa kursorom držeći pritisnutu lijevu tipku miša. Kao što vidite, koordinate odabranog područja se odmah prikazuju u polju prozora argumenata. Zatim kliknite na dugme “OK”.
5. Nakon toga, automatski se prebacujemo na list 1. Kao što vidite, ukupan iznos stopa zaposlenih je već prikazan u odgovarajućem elementu.
6. Ali to nije sve. Kao što se sjećamo, plata se izračunava množenjem opklade sa koeficijentom. Stoga ponovo biramo ćeliju u kojoj se nalazi zbrojena vrijednost. Nakon toga idite na traku formule. Formuli u njoj dodamo znak množenja (*), a zatim kliknemo na element u kojem se nalazi indikator koeficijenta. Da biste izvršili proračun, kliknite na taster Enter na tastaturi. Kao što vidite, program je izračunao ukupne plate za preduzeće.
7. Vraćamo se na list 2 i mijenjamo stopu bilo kojeg zaposlenika.
8. Nakon toga ponovo prelazimo na stranicu sa ukupnim iznosom. Kao što vidite, zbog promjena u povezanoj tabeli, ukupan rezultat plaće je automatski preračunat.

Metoda 4: specijalni umetak

Također možete povezati nizove tablica u Excelu pomoću specijalnog lijepljenja.

1. Odabiremo vrijednosti koje će biti potrebno "prevući" u drugu tabelu. U našem slučaju, ovo je raspon kolone “Rate” na listu 2. Desnim klikom na odabrani fragment. Na listi koja se otvori odaberite "Kopiraj". Alternativna prečica je Ctrl+C. Nakon toga prelazimo na list 1.
2. Pomaknuvši se na područje knjige koja nam je potrebna, odabiremo ćelije u koje ćemo morati povući vrijednosti. U našem slučaju, ovo je kolona “Rate”. Desni klik na odabrani fragment. U kontekstualnom meniju u bloku alata „Opcije umetanja“ kliknite na ikonu „Ubaci vezu“.

   Postoji i alternativna opcija. Inače, jedini je za starije verzije Excel-a. U kontekstualnom meniju, pomerite kursor na stavku „Specijalno lepljenje“. U dodatnom meniju koji se otvori izaberite poziciju sa istim imenom.

3. Ovo će otvoriti prozor za posebno lijepljenje. Kliknite na dugme "Insert Link" u donjem levom uglu ćelije.
4. Koju god opciju da odaberete, vrijednosti iz jednog niza tablice bit će umetnute u drugi. Kada se podaci u izvoru promijene, oni će se također automatski promijeniti u umetnutom rasponu.

Lekcija: Posebno zalijepite u Excel

Metod 5: Povezivanje tabela u više radnih knjiga

Osim toga, možete organizirati odnose između područja tablice u različitim radnim knjigama. Ovo koristi specijalni alat za lijepljenje. Radnje će biti potpuno slične onima koje smo razmatrali u prethodnoj metodi, osim što ćete prilikom unosa formula morati da se krećete ne između područja iste knjige, već između datoteka. Naravno, sve povezane knjige moraju biti otvorene.

1. Odaberite opseg podataka koji treba prenijeti u drugu radnu knjigu. Kliknite desnim tasterom miša na nju i izaberite „Kopiraj“ iz menija koji se otvori.
2. Zatim prelazimo na knjigu u koju će te podatke trebati ubaciti. Odaberite željeni raspon. Desni klik. U kontekstualnom meniju, u grupi „Opcije umetanja“, izaberite „Ubaci vezu“.
3. Nakon toga, vrijednosti će biti umetnute. Kada se podaci u izvornoj radnoj svesci promene, tabelarni niz iz radne sveske će ga automatski ažurirati. Štaviše, uopšte nije neophodno da obe knjige budu otvorene za ovo. Dovoljno je otvoriti samo jednu radnu svesku i ona će automatski izvući podatke iz zatvorenog povezanog dokumenta ako su prethodno u njemu napravljene promjene.

Ali treba napomenuti da će se u ovom slučaju umetanje izvršiti u obliku nepromjenjivog niza. Kada pokušate promijeniti bilo koju ćeliju s umetnutim podacima, pojavit će se poruka koja vas obavještava da je to nemoguće učiniti.

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

Prekidanje odnosa između tabela

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

Metoda 1: prekidanje veze između knjiga

Možete prekinuti vezu između knjiga u svim ćelijama tako što ćete u suštini izvršiti jednu operaciju. U ovom slučaju, podaci u ćelijama će ostati, ali će već biti statične, neažurirane vrijednosti koje ni na koji način ne ovise o drugim dokumentima.

1. U radnoj knjizi u kojoj se povlače vrijednosti iz drugih datoteka, idite na karticu "Podaci". Kliknite na ikonu “Uredi veze” koja se nalazi na traci u bloku alata “Veze”. Treba napomenuti da ako trenutna knjiga ne sadrži veze do drugih datoteka, onda ovo dugme nije aktivno.
2. Otvara se prozor za promjenu veza. Sa liste povezanih knjiga (ako ih ima nekoliko) biramo fajl sa kojim želimo da prekinemo vezu. Kliknite na dugme "Prekini vezu".
3. Otvara se informativni prozor koji sadrži upozorenje o posljedicama daljnjih radnji. Ako ste sigurni šta ćete učiniti, kliknite na dugme „Raskini veze“.
4. Nakon toga, sve reference na navedenu datoteku u trenutnom dokumentu bit će zamijenjene statičkim vrijednostima.

Metoda 2: Umetanje vrijednosti

Ali gornja metoda je prikladna samo ako trebate potpuno prekinuti sve veze između dvije knjige. Šta trebate učiniti ako trebate odvojiti povezane tablice koje se nalaze unutar iste datoteke? To možete učiniti tako što ćete kopirati podatke, a zatim ih zalijepiti na isto mjesto kao i vrijednosti. Usput, na isti način možete prekinuti vezu između pojedinačnih raspona podataka različitih knjiga bez prekidanja opće veze između datoteka. Pogledajmo kako ova metoda funkcionira u praksi.

1. Odabiremo raspon u kojem želimo izbrisati odnos s drugom tablicom. Kliknite desnim tasterom miša na njega. U meniju koji se otvori odaberite "Kopiraj". Umjesto ovih radnji, možete otkucati alternativnu kombinaciju prečaca Ctrl+C.
2. Zatim, bez poništavanja odabira istog fragmenta, ponovo kliknite desnim tasterom miša na njega. Ovaj put na listi radnji kliknite na ikonu „Vrijednosti“ koja se nalazi u grupi alata „Opcije umetanja“.
3. Nakon toga, sve reference u odabranom rasponu bit će zamijenjene statičkim vrijednostima.

Kao što vidite, Excel ima načine i alate za povezivanje nekoliko tabela. U isto vrijeme, tabelarni podaci mogu se nalaziti na drugim listovima, pa čak i u različitim knjigama. Ako je potrebno, ova veza se može lako prekinuti.

Drago nam je da smo mogli da Vam pomognemo da rešite problem.

Postavite svoje pitanje u komentarima, detaljno opišite suštinu problema. Naši stručnjaci će pokušati odgovoriti što je prije moguće.

Da li vam je ovaj članak pomogao?

Veza Excel tabele je formula koja vraća podatke iz ćelije u drugoj radnoj svesci. Kada otvorite radnu knjigu koja sadrži veze, Excel čita najnovije informacije iz izvorne radne knjige (veze za ažuriranje)

Relacije među tabelama u Excel-u se koriste za preuzimanje podataka i iz drugih listova u radnoj svesci i iz drugih Excel radnih knjiga. Na primjer, imate tabelu koja izračunava ukupan iznos prodaje. U kalkulaciji se koriste cijene proizvoda i obim prodaje. U ovom slučaju ima smisla kreirati zasebnu tabelu sa podacima o cenama, koja će se izvući pomoću veza prve tabele.

Kada kreirate odnos između tabela, Excel kreira formulu koja uključuje ime izvorne radne knjige zatvoreno u zagrade, naziv radnog lista praćen znakom uzvika i referencom ćelije.

Kreiranje veza između radnih knjiga

1. Otvorite obje radne knjige u Excelu
2. U izvornoj radnoj knjizi odaberite ćeliju koju treba povezati i kopirajte je (prečica na tipkovnici Ctrl+C)
3. Idite na ciljnu radnu svesku, kliknite desnim tasterom miša na ćeliju u koju želimo da postavimo vezu. Odaberite iz padajućeg menija Specijalni umetak
4. Specijalni umetak izabrati Umetnite vezu.

Postoji još jedna, jednostavnija opcija za kreiranje odnosa između tabela. U ćeliju u koju želimo da umetnemo vezu stavite znak jednakosti (isto kao i za običnu formulu), idite na izvornu radnu svesku, izaberite ćeliju koju želimo da povežemo i kliknite na Enter.

Možete koristiti alate za kopiranje i autodovršavanje za formule odnosa kao što to radite za obične formule.

Prije nego što kreirate odnose između tabela

Prije nego počnete širiti znanje o svojim velikim idejama, evo nekoliko savjeta za rad s odnosima u Excelu:

Napravite lako sljedive veze. Ne postoji automatska pretraga za sve ćelije koje sadrže veze. Stoga, koristite određeni format za brzo prepoznavanje veza do drugih tabela, inače dokument koji sadrži veze može narasti do te mjere da ga je teško održavati.

Automatski proračuni. Izvorna radna sveska mora raditi u automatskom načinu izračuna (podešeno prema zadanim postavkama). Da biste promijenili parametar izračuna, kliknite na karticu Formule grupi Kalkulacija. Odaberite Opcije proračuna –> Automatski.

Ažuriranje veza

Da ručno ažurirate odnos između tabela, idite na karticu Podaci grupi Veze. Kliknite na dugme Promijenite veze.

U dijaloškom okviru koji se pojavljuje Promjena veza Ažuriraj.

Prekinite veze u Excel radnim sveskama

Prekidanje veze s izvorom će uzrokovati zamjenu postojećih formula veza vrijednostima koje vraćaju. Na primjer, veza =Cijene!$B$4 će biti zamijenjena sa 16. Prekidanje veze ne može se poništiti, pa prije izvođenja operacije preporučujem da sačuvate knjigu.

Idite na karticu Podaci grupi Veze. Kliknite na dugme Promijenite veze. U dijaloškom okviru koji se pojavljuje Promjena veza odaberite vezu koja vas zanima i kliknite na dugme Prekinite vezu.

Ako još niste upoznati sa zaokretnim tablicama, počnite s ovim člankom.

Problem

Dešava se da nam analizirani podaci dolaze u obliku zasebnih tabela, koje je, ipak, potrebno povezati. MS Access to lako može učiniti, ali u Excelu ste uvijek morali koristiti formule tipa VLOOKUP za ovo. Međutim, počevši od Excela 2013, imamo priliku da koristimo nekoliko tabela povezanih ključnim poljima kao izvor prilikom izrade pivot tabele.

Primjer

U našem primjeru imamo 4 tabele: Narudžbe, Linije narudžbi, Proizvodi, Kupci.

Naručite sto:

Tabela narudžbe:

Tabela proizvoda

Clients Table

Izvorne tabele su dizajnirane kao pametne tabele: Nalozi, Redovi narudžbi, Roba i Klijenti.

Sasvim je očigledno da tabele Orders i OrderLines mogu biti povezane poljem Order_ID, tabele Orders i Clients poljem Customer_ID, a tabele OrderLines i Robe poljem Product_ID.

Preuzmite primjer

Kreiranje modela podataka

Kreirajmo stožernu tabelu na osnovu bilo koje od postojećih tabela.

Izaberite Pivot Table iz menija Umetanje. U navedenom dijaloškom okviru vidimo opciju za dodavanje ovih podataka u model podataka. Mogli bismo to izabrati, ali preporučujem drugi, zgodniji način. Samo kliknite OK.

U panelu Polja zaokretne tabele koji se pojavi, vidite natpis DRUGE TABELE...

Hajde da ga pritisnemo. Pojavit će se sljedeće pitanje:

Ako počnete da birate polja, nakon nekog vremena na listi polja će se pojaviti dugme CREATE...

Kliknite na nju i stvorimo veze između naših tabela. Ovo stvara vezu između tabele Orders i OrderLines. Imajte na umu da Excel može stvoriti odnos jedan-na-jedan ili jedan-prema-više. Štaviše, tabela sa „mnogo“ mora biti prva naznačena, inače Excel psuje i nudi da ih zameni.

Na isti način stvaramo i druge veze.

Dijaloškom okviru Upravljanje odnosima može se pristupiti preko trake ANALIZA, naredba Relationships

Da vidite više polja na panelu Polja zaokretne tabele, možete koristiti dugme Alati (u obliku zupčanika) da izaberete ovaj prikaz:

Rezultat će biti ovakav:

Kao rezultat, sve naše tablice su sada povezane i možete generirati, na primjer, takav izvještaj.

Ako jedan radni list koristi podatke iz drugog radnog lista, listovi se smatraju povezanim. Koristeći povezivanje, možete kombinirati vrijednosti ćelija iz nekoliko različitih tabela u jedan radni list.

Promjena sadržaja ćelije u istom radnom listu ili tabeli ( izvor) radne knjige uzrokuje promjenu pridruženih ćelija u listovima ili tabelama ( prijemnici). Ovaj princip razlikuje povezivanje listova od jednostavnog kopiranja sadržaja ćelija s jednog lista na drugi.

U zavisnosti od tehnike izvođenja, vezivanje može biti "direktno" i kroz tim SPECIJALNI INSERT.

Metoda 1 – “Direktno povezivanje ćelija”

Direktno povezivanje sheets se koristi direktno prilikom unosa formule u ćeliju, kada se referenca na ćeliju drugog lista koristi kao jedan od elemenata formule. Na primjer, ako ćelija tablice B4 na radnom listu2 sadrži formulu koja koristi vezu do ćelije A4 drugog radnog lista (na primjer, Sheet1) i oba lista su učitana podacima, tada se ovo povezivanje listova naziva „direktno“.

Termin “direktno” vezivanje znači da samog korisnika direktno prilikom unosa formule ukazujenaziv lista I apsolutna adresa ćelije, odvojeno uzvikom "!".

Primjeri formula: = C5*Sheet1! A4

Sheet3! V2*100%

Sheet1! A1- List2! A1

Bilješka.

Da biste označili veze do ćelija i listova koji se nalaze u neučitanim (neotvorenim) radnim knjigama, morate navesti punu putanju do lokacije datoteke u formuli bez razmaka. Putanja je navedena u jednostrukim navodnicima, što označava naziv diska, direktorija (fascikla), naziv radne knjige (ime datoteke) u uglastim zagradama i naziv lista na koji vodi veza.

Metoda 2 – Povezivanje ćelija pomoću naredbe „Specijalno zalijepi“.

Povezivanje preko komande SPECIJALNI INSERT se izvodi ako bilo koja ćelija tabele na jednom radnom listu treba da sadrži vrednost ćelije iz drugog radnog lista.

Za odraz u ćeliji C4 na radnom listu Cijena vrijednost ćelije H4 na originalnom listu Kupovina, potrebno je da postavite kursor na ćeliju H4 izvornog lista i izvršite naredbu Uredi–Kopiraj. Na listu Cijena postavite kursor na ćeliju C4, koju treba povezati s originalnom, i izvršite naredbu Uredi – Posebno zalijepi – Umetni vezu(vidi sliku 8). Zatim na listu Cijena pojavit će se indikacija ćelije izvornog lista Kupovina, Na primjer: = Kupovina!$H$4

Sa ovim povezivanjem, EXCEL automatski koristi apsolutnu adresu po ćeliji, jer relativna pristupna adresa može dovesti do grešaka, posebno ako se pristupa neučitanim datotekama (radnim knjigama).

Vježbajte. Povežite ćelije C4, C5, C6, C7, C8 u tabeli Troškovi nabavke na listu Cijena sa odgovarajućim ćelijama na listu Kupovina koristeći različite metode povezivanja ćelija (slika 8).

Rice. 8 Povezivanje ćelija iz različitih radnih listova

! Kada povezujete ćelije, odredite koje su ćelije originalne ćelije.

! Za jednu povezanu tabelu, izvorne ćelije mogu biti iz različitih tabela na različitim radnim listovima ili na trenutnom radnom listu.

Zadaci za samostalan rad.

Prije nego što završimo samostalni zadatak, pogledajmo primjer tablica Cijena povezujući ćelije.

Na primjer:

na listu Cijena u tabeli Troškovi nabavkećelije A4:A8 su povezane sa ćelijama tabele Količina kupljenih proizvoda na listu Kupovina;

ćelije B4:B8 su originalne, jer sadrže početne informacije o cijenama kupljene robe;

ćelije C4:C8 su povezane sa ćelijama H4:H8 na radnom listu Kupovina;

ćelije D4:D8 sadrže formule za izračunavanje sredstava utrošenih na kupljenu robu i odnose se na ćelije sopstvene tabele (npr. formula u ćeliji D4 izgleda kao =B4*C4, što znači množenje cene proizvoda njegovom količinom );

ćelija D9 je zbir ćelija D4:D8;

u drugoj tabeli Kalkulacija cijene na istom listu, ćelije A14:A18 su povezane slično kao u koraku 1;

ćelije B14:B18 su povezane sa izvornim ćelijama trenutnog lista B4:B8;

ćelije C4:C8 su originalne, jer sadržavati početne informacije o cijeni salona na kupljenu robu;

ćelije D14:D18 sadrže formule za izračunavanje prodajne cijene robe i odnose se na ćelije svoje tablice (npr. formula u ćeliji D14 izgleda kao =B14*C14+B14, što znači množenje nabavne cijene sa utvrđenom maržom procenat, koji daje iznos marže koja se mora dodati nabavnoj cijeni);

Nakon izvođenja svih operacija sa ovim tabelama, provjerite njihovu „operabilnost“.

Promjena Naziv proizvoda - Sofa u ćeliji A4 na listu Kupovina na nešto drugo - na primjer Sofa.

Promjena količina kupljene robe Sofa juna (u ćeliji G4 na listu Kupovina unesite broj 11).

Promjena nabavna cijena Sofe u ćeliji B4 na listu Cijena za drugu – 2500,00 rub.

Promjena Postotak oznake sofe u ćeliji C14 na listu Cijena od 50% do 32%.

Provjeri , da li je bilo promjena u povezanim tabelama ili ne?

Bilješka, na koje ćelije različitih tabela su uticale napravljene promene.

Pažnja! Kada povezujete ćelije putem PASTE SPECIAL... kopiranje u susjedne ćelije postaje problematično zbog apsolutnog adresiranja ćelija.

Vježba 1. Povežite ćelije preostalih tabela u radnoj svesci koristeći različite metode.

Bilješka. Tablice za obračun prihoda i prihoda za 1. kvartal koriste formulu za zbrajanje izvornih ćelija samo za 1. kvartal.

Zadatak 2. Kreirajte na listovima Prihod I Prihodi tabele za obračun za 2. kvartal. Povežite ove tabele sa odgovarajućim izvornim podacima.

Bilješka.

Zadatak 3. Iscrtajte tortni grafikon na radnom listu Prihodi i analizirati distribuciju prihoda prema vrsti proizvoda.

Zadatak 4. Dodajte radni list na kraj radne sveske Konsolidovana izjava. Na njoj napravite zbirnu tabelu, koja po nazivu proizvoda odražava količinu nabavke i prodaje, marže, nabavne i prodajne cijene, prihod od prodaje za 1. kvartal i za 2. kvartal. Povežite ovu tabelu sa odgovarajućim izvornim podacima na drugim radnim listovima.

Bilješka. U tabelama za obračun prihoda i prihoda za 2. kvartal koristite samo izvorne ćelije 2. kvartala.

Ako treba podaci iz jedne Excel tabele su automatski prebačeni u drugu, morate povezati ove tabele. Postoji nekoliko načina za povezivanje podataka iz dvije tabele u programu Excel. Jedan put - koristeći formulu, pogledajte članak „Kako povezati tabele u Excelu“.
Ovdje ćemo razmotriti, kako povezati dvije tabele u različitim Excel radnim knjigama, uspostavljanjem odnosa između tabela. Kada je sve postavljeno, dovoljno je otvoriti jednu tabelu kako bi se podaci u ovu tabelu preneli iz druge tabele. Ne morate otvarati sve knjige da bi veza radila. Radit će s jednom otvorenom radnom sveskom.
Dakle, imamo dvije Excel radne knjige - Book1 i Book2. U Knjizi 1 postoji takva tabela, već popunjena. Tabela 1.Pažnja!
Ako tablica ima spojene ćelije, veza u njima neće raditi ili će se izgubiti.
U knjizi 2 pravimo istu tabelu, ali je ne popunjavamo.

Funkciju “Insert Link” možete pozvati i na ovaj način. Također kopiramo, ali u kontekstnom izborniku kliknite na funkciju "Specijalno lijepljenje". U dijaloškom okviru koji se pojavi kliknite na dugme „Ubaci vezu“.