Rad sa povezanim tabelama u programu Microsoft Excel

Prilikom izvođenja određenih zadataka u Excelu, ponekad se morate nositi s nekoliko tablica, koje su također međusobno povezane. To znači da se podaci iz jedne tabele povlače u drugu, a kada se promijene, vrijednosti u svim odnosnim rasponima tablica se ponovno izračunavaju.

Povezane tabele su veoma korisne za obradu velikih količina informacija. Nije baš zgodno imati sve informacije u jednoj tabeli, i ako nije homogena. Teško je raditi s takvim objektima i pretraživati ​​ih. Ovaj problem ima za cilj da eliminiše srodne tabele, informacije između kojih se distribuira, ali je istovremeno i međusobno povezano. Raspon povezanih tabela može biti lociran ne samo u jednom listu ili jednoj knjizi, već se može nalaziti iu odvojenim knjigama (datotekama). U praksi se najčešće koriste posljednje dvije opcije, budući da je svrha ove tehnologije da se makne od akumulacije podataka, a gomilanje na istoj stranici ne rješava problem fundamentalno. Naučimo kako da kreiramo i kako da radimo sa ovom vrstom upravljanja podacima.

Kreiranje povezanih tabela

Pre svega, hajde da se osvrnemo na pitanje kako je moguće stvoriti vezu između različitih raspona tabela.

Metod 1: Direktno povezivanje tabela sa formulom

Najlakši način povezivanja podataka je upotreba formula koje povezuju druge raspone tablica. Zove se direktno vezivanje. Ova metoda je intuitivna, jer se s njom vezivanje izvodi gotovo na isti način kao i kreiranje referenci na podatke u jednom nizu tablica.

Pogledajmo kako jedan primjer može formirati vezu izravnim vezivanjem. Imamo dva stola na dva lista. U jednoj tabeli, platni spisak se izračunava koristeći formulu množenjem stope radnika sa jednom stopom za sve.

Na drugom listu postoji tabelarni raspon u kojem se nalazi spisak zaposlenih sa njihovim platama. Lista zaposlenih u oba slučaja je predstavljena istim redoslijedom.

Potrebno je napraviti tako da se podaci o brzinama iz drugog lista povuku u odgovarajuće ćelije prvog.

1. Na prvom listu izaberite ćeliju prve kolone. "Bet". Stavili smo njen trag "=". Zatim kliknite na oznaku "List 2"Koji se nalazi na lijevoj strani Excel sučelja iznad statusne trake.



2. Prelazi na drugu oblast dokumenta. Kliknite na prvu ćeliju u koloni. "Bet". Zatim kliknite na dugme. Enter na tastaturi izvršiti unos podataka u ćeliji u kojoj je prethodno postavljen znak jednak.



3. Zatim slijedi automatski prijelaz na prvi list. Kao što možete videti, brzina prvog zaposlenog iz druge tabele je povučena u odgovarajuću ćeliju. Postavljanjem kursora na ćeliju koja sadrži okladu, vidimo da se uobičajena formula koristi za prikaz podataka na ekranu. Ali prije koordinata ćelije u kojoj se podaci prikazuju, postoji izraz "Sheet2!"koji označava naziv područja dokumenta u kojem se nalaze. Opšta formula u našem slučaju je sledeća:

   = List2! B2



4. Sada treba da prenesete podatke o stopama svih ostalih zaposlenih u preduzeću. Naravno, to se može uraditi na isti način kao što smo i obavili zadatak za prvog zaposlenika, ali s obzirom da su obje liste zaposlenih raspoređene u istom poretku, zadatak se može značajno pojednostaviti i ubrzati njegovo rješavanje. Ovo se može uraditi jednostavnim kopiranjem formule u donji opseg. Zbog činjenice da su linkovi u Excelu relativni po defaultu, kada se kopiraju, vrijednosti se pomjeraju, što je ono što nam je potrebno. Sam postupak kopiranja može se izvršiti pomoću markera za popunjavanje.

   Dakle, postavite kursor u donju desnu oblast elementa pomoću formule. Nakon toga, kursor treba pretvoriti u popunjavanje u obliku crnog križa. Izvodimo stezaljku lijeve tipke miša i povlačimo kursor do samog dna stupca.



5. Svi podaci iz iste kolone List 2 su povučeni za stolom List 1. Kada se podaci promijene u List 2 automatski će se promijeniti na prvom.

Metoda 2: koristite gomilu operatora INDEX - MATCH

Ali šta ako popis zaposlenih u tabelarnim nizovima nije uređen u istom poretku? U ovom slučaju, kao što je ranije pomenuto, jedna od opcija je da se uspostavi veza između svake od tih ćelija koje treba ručno povezati. Ali ovo je pogodno samo za male stolove. Za velike opsege, ova opcija, u najboljem slučaju, će trajati mnogo vremena za implementaciju, au najgorem slučaju - u praksi to uopće neće biti izvedivo. Ali možete riješiti ovaj problem sa hrpom operatera INDEX - MATCH. Da vidimo kako se to može uraditi povezivanjem podataka u tabelarnim opsezima, o kojima smo govorili u prethodnom postupku.

1. Izaberite prvu stavku u koloni. "Bet". Idi na Čarobnjak za funkcijeklikom na ikonu "Umetni funkciju".



2. U Čarobnjak za funkcije u grupi "Linkovi i nizovi" pronađite i izaberite ime INDEX.



3. Ovaj operator ima dva oblika: obrazac za rad s nizovima i referencu. U našem slučaju, prva opcija je obavezna, tako da u sljedećem prozoru za odabir obrasca koji će se otvoriti, odabiremo ga i kliknemo na gumb "OK".



4. Prošao se prozor argumenta operatora. INDEX. Zadatak specificirane funkcije je da prikaže vrijednost koja je u odabranom rasponu u redu s navedenim brojem. Opća formula operatora INDEX je ovo:

   = INDEX (niz; red_broj; [stupanj_broj])

   "Array" - argument koji sadrži adresu raspona iz kojeg ćemo izvlačiti informacije brojem navedenog niza.

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

   "Broj stupca" - Argument je opcionalan. Da bismo konkretno riješili naš problem, nećemo ga koristiti, pa stoga nije potrebno posebno opisivati ​​njegovu suštinu.

   Stavite kursor u polje "Array". Posle toga idite na List 2 i, držeći levi taster miša, izaberite ceo sadržaj kolone "Bet".



5. Nakon što se koordinate prikažu u prozoru operatora, postavite kursor u polje "Broj linije". Ovaj argument ćemo prikazati pomoću operatora MATCH. Zato kliknite na trougao koji se nalazi levo od linije funkcija. Otvara se lista nedavno korišćenih operatora. Ako među njima pronađete ime "MATCH"onda možete kliknuti na njega. U suprotnom, kliknite na najnoviju stavku na popisu - "Ostale karakteristike ...".



6. Pokreće se standardni prozor. Funkcioneri. Idi u istu grupu. "Linkovi i nizovi". Ovaj put u listi izaberite stavku "MATCH". Kliknite na dugme. "OK".



7. Aktivira argumente prozora operatora MATCH. Navedena funkcija je namijenjena prikazu broja vrijednosti u određenom nizu po imenu. Zahvaljujući ovoj prilici, izračunat ćemo broj reda određene vrijednosti funkcije. INDEX. Sintaksa MATCH predstavljen kao:

   = MATCH (tražena vrijednost; lookup array; [match_type])

   "Tražena vrijednost" - argument koji sadrži ime ili adresu ćelije opsega treće strane u kojoj se nalazi. To je pozicija ovog imena u ciljnom opsegu koji treba izračunati. U našem slučaju, prvi argument će biti reference na ćelije List 1u kojima se nalaze imena zaposlenih.

   "Pogledano polje" - argument koji predstavlja vezu do niza u kojem se traži određena vrijednost da bi se odredila njegova pozicija. Igracemo ovu kolonu s adresama uloga "Ime on List 2.

   "Vrsta mapiranja" - argument koji je opcionalan, ali, za razliku od prethodne izjave, potreban nam je ovaj opcionalni argument. Pokazuje kako će operater odgovarati željenoj vrijednosti s nizom. Ovaj argument može imati jednu od tri vrijednosti: -1; 0; 1. Za neuređene nizove, izaberite opciju "0". Ova opcija je pogodna za naš slučaj.

   Dakle, počnimo sa popunjavanjem polja prozora argumenata. Stavite kursor u polje "Tražena vrijednost", kliknite na prvu ćeliju kolone "Ime" on List 1.



8. Nakon što se prikažu koordinate, postavite kursor na polje "Pogledano polje" i idi na prečicu "List 2"koji se nalazi na dnu Excel prozora iznad statusne trake. Držite levi taster miša i označite sve ćelije u koloni. "Ime".



9. Nakon što se njihove koordinate prikažu u polju "Pogledano polje"idite na polje "Vrsta mapiranja" i podesite broj sa tastature "0". Nakon toga, ponovo se vraćamo na polje. "Pogledano polje". Činjenica je da ćemo kopirati formulu, kao što smo to učinili u prethodnoj metodi. Postojaće pomak adresa, ali moramo da popravimo koordinate niza koji se gleda. Ne bi trebalo da se menja. Izaberite koordinate kursora i kliknite na funkcijski taster F4. Kao što vidite, ispred koordinata se pojavio znak za dolar, što znači da je veza od relativnog postala apsolutna. Zatim kliknite na dugme "OK".



10. Rezultat se prikazuje u prvoj ćeliji kolone. "Bet". Ali pre kopiranja, moramo da popravimo drugu oblast, naime prvi argument funkcije INDEX. Da biste to uradili, izaberite element kolone koja sadrži formulu i pređite na traku formule. Izaberite prvi argument operatora INDEX (B2: B7) i kliknite na dugme F4. Kao što vidite, znak dolar se pojavio blizu odabranih koordinata. Kliknite na dugme Enter. Generalno, formula je dobila sljedeći oblik:

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



11. Sada možete kopirati pomoću markera za popunjavanje. Nazovite ga na isti način na koji smo razgovarali ranije, i protegnite ga do kraja tabele.



12. Kao što možete videti, uprkos činjenici da se redosled redova dve povezane tabele ne poklapa, međutim, sve vrednosti se pooštravaju prema imenima radnika. To je postignuto upotrebom kombinacije operatora INDEX-MATCH.

Vidi i: t
Excel funkcija INDEX
Funkcija podudaranja u Excelu

Metod 3: Izvođenje matematičkih operacija sa pridruženim podacima

Direktno vezivanje podataka je dobro u tome što omogućava ne samo prikazivanje vrijednosti koje su prikazane u drugim tabelarnim rasponima u jednoj od tablica, već i izvođenje različitih matematičkih operacija s njima (zbrajanje, dijeljenje, oduzimanje, množenje, itd.).

Da vidimo kako se to radi u praksi. Hajde da to uradimo List 3 opći podaci o plaćama u poduzeću će biti prikazani bez raspodjele zaposlenika. Za to će se povući stope osoblja List 2, sumirajte (koristeći funkciju SUM) i pomnožena sa koeficijentom koristeći formulu.

1. Izaberite ćeliju u kojoj će biti prikazan ukupan platni spisak List 3. Kliknite na dugme "Umetni funkciju".



2. Trebalo bi da pokrene prozor Funkcioneri. Idi u grupu "Matematički" i tamo odaberite ime "SUMM". Zatim kliknite na dugme "OK".



3. Prelazak na prozor argumenta funkcije SUMkoji je dizajniran za izračunavanje zbroja izabranih brojeva. Ima sljedeću sintaksu:

   = SUM (broj1; broj2; ...)

   Polja u prozoru odgovaraju argumentima specificirane funkcije. Iako njihov broj može doseći 255 komada, za našu svrhu dovoljan je samo jedan. Stavite kursor u polje "Number1". Kliknite na oznaku "List 2" iznad statusne trake.



4. Nakon što smo se preselili u željeni dio knjige, odaberite stupac koji treba zbrojiti. Postavimo ga na kursor, držeći levi taster miša. Kao što možete vidjeti, koordinate odabranog područja odmah se prikazuju u polju prozora argumenta. Zatim kliknite na dugme. "OK".



5. Nakon toga, mi automatski prelazimo na List 1. Kao što možete vidjeti, ukupan iznos plaća radnika je već 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 koeficijentom. Zato ponovo biramo ćeliju u kojoj se nalazi sumirana vrednost. Nakon toga idite u formulu. Dodamo znak množenja njegovoj formuli (*), a zatim kliknite na element u kojem se nalazi koeficijent. Za izvođenje izračuna kliknite na Enter na tastaturi. Kao što vidite, program je izračunao ukupnu platu za preduzeće.



7. Vrati se nazad List 2 i promeni veličinu stope svakog zaposlenog.



8. Nakon toga, ponovo idite na stranicu sa ukupnim iznosom. Kao što možete vidjeti, zbog promjena u odnosnoj tablici, rezultat ukupne plaće je automatski preračunat.

Metod 4: specijalni umetak

Također možete povezati tablične nizove u Excelu s posebnim umetkom.

1. Odaberite vrijednosti koje treba "zategnuti" na drugu tablicu. U našem slučaju, ovo je opseg stupaca. "Bet" on List 2. Kliknite na odabrani fragment desnom tipkom miša. Na listi koja se otvori, izaberite stavku "Kopiraj". Alternativna kombinacija tipki je Ctrl + C. Posle toga idite na List 1.



2. Krećući se do željenog područja knjige, odabiremo ćelije u koje želite povući vrijednosti. U našem slučaju, ovo je kolona. "Bet". Kliknite na odabrani fragment desnom tipkom miša. U kontekstnom izborniku na alatnoj traci "Opcije umetanja" kliknite na ikonu "Umetni vezu".

   

   Postoji i alternativa. Usput, to je jedina za starije verzije Excela. U kontekstnom meniju pomerite kursor na stavku "Posebno lijepljenje". U dodatnom meniju koji se otvori, izaberite stavku sa istim imenom.



3. Nakon toga se otvara poseban prozor za umetanje. Pritisnemo dugme "Umetni vezu" u donjem levom uglu ćelije.



4. Koju god opciju odaberete, vrijednosti iz jednog niza tablica će biti umetnute u drugu. Kada promijenite podatke u izvoru, oni će se također automatski promijeniti u umetnutom rasponu.

Lekcija: Specijalno lepljenje u Excelu

Metod 5: Odnos između tabela u više knjiga

Pored toga, možete organizovati vezu između tabelarnih prostora u različitim knjigama. Ovo koristi specijalni alat za umetanje. Akcije će biti apsolutno slične onima koje smo razmotrili u prethodnom postupku, osim što navigacija prilikom uvođenja formula neće morati da se odvija između područja jedne knjige, već između datoteka. Naravno, sve srodne knjige treba da budu otvorene.

1. Izaberite opseg podataka koji želite da prenesete u drugu knjigu. Kliknite na nju desnim dugmetom miša i izaberite poziciju u meniju koji se otvara "Kopiraj".



2. Zatim prelazimo na knjigu u kojoj će se ti podaci morati umetnuti. Izaberite željeni opseg. Kliknite desnim dugmetom miša. U kontekstnom meniju grupe "Opcije umetanja" izaberite stavku "Umetni vezu".



3. Nakon toga, vrijednosti će biti umetnute. Kada promenite podatke u izvornoj knjizi, tabelarni niz iz radne sveske će ih automatski povući. I uopšte nije neophodno da obje knjige budu otvorene za to. Dovoljno je otvoriti samo jednu radnu svesku, i ona će automatski povući podatke iz zatvorenog povezanog dokumenta, ako su ranije u njemu izvršene promene.

Međutim, treba napomenuti da će u ovom slučaju umetanje biti u obliku nepromjenljivog niza. Ako pokušate promijeniti bilo koju ćeliju s umetnutim podacima, pojavit će se poruka koja vas obavještava da to nije moguće.

Promjene u tom nizu povezane s drugom knjigom mogu se napraviti samo razbijanjem veze.

Prekid veze između tabela

Ponekad je potrebno prekinuti vezu između tabelarnih raspona. Razlog za to može biti, kao što je gore opisano, kada želite promijeniti polje umetnuto iz druge knjige ili jednostavno zato što korisnik ne želi da se podaci u jednoj tablici automatski ažuriraju s druge.

Metod 1: prekid veze između knjiga

Možete prekinuti vezu između knjiga u svim ćelijama obavljanjem gotovo jedne operacije. Istovremeno, podaci u ćelijama ostaju, ali će već biti statične ne-ažurirane vrijednosti koje ne ovise o drugim dokumentima.

1. U knjizi, u kojoj se izvlače vrijednosti iz drugih datoteka, idite na karticu "Data". Kliknite na ikonu "Uredi veze"koji se nalazi na traci u bloku alata "Veze". Treba napomenuti da ako trenutna knjiga ne sadrži linkove na druge datoteke, ovo dugme je neaktivno.



2. Pokreće se prozor za promenu linkova. Odaberite iz popisa srodnih knjiga (ako ih ima više) datoteku s kojom želite prekinuti vezu. Kliknite na dugme "Prekini vezu".



3. Otvara se informativni prozor u kojem se upozorava na posljedice daljnjih akcija. Ako ste sigurni u ono što ćete uraditi, kliknite na dugme. "Prekini veze".



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

Metod 2: Umetanje vrednosti

Ali gore navedeni metod je pogodan samo ako trebate potpuno odvojiti sve veze između ove dvije knjige. Šta učiniti ako želite da prekinete povezane tabele koje se nalaze u istoj datoteci? To možete uraditi kopiranjem podataka, a zatim ih zalijepiti na isto mjesto kao i vrijednosti.Inače, isti metod se može koristiti za prekid veze između odvojenih opsega podataka različitih knjiga bez prekida opšte veze između datoteka. Da vidimo kako ova metoda funkcioniše u praksi.

1. Odaberite raspon u kojem želimo ukloniti vezu na drugu tablicu. Kliknite na nju desnom tipkom miša. U meniju koji se otvori, izaberite stavku "Kopiraj". Umjesto ovih akcija, možete upisati alternativnu kombinaciju vrućih tipki. Ctrl + C.



2. Zatim, bez uklanjanja selekcije iz istog fragmenta, ponovo kliknite na njega desnim dugmetom miša. Ovaj put u listi akcija kliknemo na ikonu "Vrijednosti"koja se nalazi u grupi alata "Opcije umetanja".



3. Nakon toga, sve veze u odabranom rasponu će biti zamijenjene sa statičnim vrijednostima.

Kao što možete vidjeti, Excel ima metode i alate za povezivanje nekoliko tablica zajedno. U ovom slučaju, tabelarni podaci mogu biti na drugim listovima i čak u različitim knjigama. Ako je potrebno, ova veza se lako može slomiti.

Pogledajte video: Kako sumirati više tabela u jednu u Excelu (Maj 2024).