Nažalost, ako je izvorna knjiga izbrisana/premještena ili preimenovana, veza će biti prekinuta. Također, veza će se izgubiti ako premjestite ciljnu datoteku (koja sadrži vezu). Ako konačnu datoteku pošaljete samo poštom, primalac također neće moći ažurirati veze.
Kako prekinuti vezu
Jedan od načina za rješavanje ovog problema je prekid veze. Ako postoji samo jedna veza u datoteci, onda je ovo prilično jednostavno:
- Idite na karticu Podaci.
- Odaberite tim Promijenite veze U poglavlju Veze.
- Kliknite Prekini vezu.
BITAN! Kada se veza prekine, sve formule koje se odnose na izvornu knjigu će biti pretvorene u vrijednosti! Otkazivanje ove operacije je nemoguće!
Kako prekinuti vezu sa svim knjigama
Radi praktičnosti, možete koristiti makro koji će prekinuti veze sa svim knjigama. Makro je dio VBA-Excel dodatka. Da biste ga koristili potrebno vam je:
VBA kod
Kôd za makro koji briše sve veze do knjige predstavljen je u nastavku. Možete ga kopirati u svoj projekat.
Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("Sve reference na druge radne knjige će biti uklonjene iz ove datoteke, a formule koje se pozivaju na druge radne knjige će biti zamijenjene vrijednostima." & vbCrLf & "Da li ste sigurni da želite da nastavite ? ", 36, "Raskinuti vezu?") Slučaj 7 " Nema izlaza Sub End Odaberite WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) Ako nije IsEmpty(WbLinks) Onda For i = 1 To UBound(WbLinks.B ActiveWork) Ime: =WbLinks(i), Tip:=xlLinkTypeExcelLinks Next Else MsgBox "Ova datoteka ne sadrži veze ka drugim radnim knjigama.", 64, "Veze ka drugim radnim knjigama" Kraj ako Kraj pod
Kako prekinuti veze samo u odabranom opsegu
Ponekad postoji mnogo veza u knjizi i postoji zabrinutost da bi uklanjanje veze moglo ukloniti dodatni. Da biste to izbjegli, možete koristiti dodatak za brisanje veza samo u odabranom rasponu. Za ovo.
Ponekad se podaci koje trebate pronaći mogu nalaziti u različitim tabelama. Često morate odabrati podatke iz nekoliko Excel tabela. Korisnik treba da bude u mogućnosti da odredi iz koje tabele da izvrši izbor na osnovu kriterijuma uslova za određenu vrstu podataka. Za to možete kreirati posebnu formulu.
Kako povezati dvije tablice s jednom formulom da odaberete VLOOKUP prema uvjetu
Na slici ispod prikazana je tabela za obračun iznosa poreza. Korisnik ima mogućnost da utvrdi bračni status zaposlenog (oženjen ili neoženjen). Ako korisnik odabere uslov “Single”, odabir treba izvršiti prema tabeli “Single Employees”. Ako se odabere kriterijum „U braku“, uzorak će biti napravljen prema tabeli „Oženjeni zaposleni“. Formula za obračun poreza pod pretpostavkom da je oženjen ili neoženjen zaposlenik kompanije:
Da biste kreirali prebacivanje između tabela, možete koristiti nazive raspona ćelija i funkciju INDIRECT. Zatim morate kreirati formulu. Prvo morate kreirati dva imenovana raspona:
- Oženjen – za tabelu „Oženjeni zaposleni“.
- Neoženjeni – za tabelu “Neoženjeni zaposleni”.
Da biste dodijelili odvojena imena za svaki od raspona ove dvije tablice, učinite sljedeće:
Za tačnost i praktičnost unosa ulaznih vrijednosti u ćeliju... koristi se padajuća lista koju kreira alat: “DATA” - “Rad s podacima” - “Provjera podataka” - “Tip podataka:” - “ Lista”.
Padajuća lista se sastoji od samo dvije vrijednosti: “U braku” “Single”. Potpuno isto kao i imena imena raspona ćelija kreiranih ranije. Vrijednost ćelije E12 koristit će se za prebacivanje između tabela prilikom pretraživanja po uvjetu. Stoga vrijednosti i nazivi raspona moraju biti identični.
Ova formula se zasniva na funkciji VLOOKUP. Njegov drugi argument, koji specificira izvornu tablicu, sadrži funkciju INDIRECT. Ova funkcija ima prvi argument, Referenca ćelije, koji pretvara dolazni tekst u ćeliju ili referencu opsega. Na prvoj slici ćelija E12 sadrži vrijednost "Single". INDIRECT funkcija pokušava pretvoriti ovaj tekst u referencu ćelije ili naziv raspona. Ako se tekst ne konvertuje u referencu ćelije (kao u ovom primjeru), tada funkcija INDIRECT provjerava da li u radnoj knjizi postoje nazivi raspona ćelija sa istim imenom. Da takva imena opsega nisu kreirana, onda bi funkcija vratila grešku sa kodom #REF!
Sintaksa funkcije INDIRECT ima drugi neobavezni argument koji se zove "A1". Vrijednost TRUE u ovom argumentu znači da je referenca ćelije napisana u formatu A1, a vrijednost FALSE znači da je referenca ćelije napisana u formatu R1C1. U slučaju imenovanih imena raspona ćelija, funkcija INDIRECT će vratiti ispravan rezultat bez obzira da li je njen drugi opcijski argument "A1" TRUE ili FALSE.
Funkcija INDIRECT također može vratiti vanjske reference na druge listove, pa čak i druge Excel radne knjige. Ali pod uvjetom da je radna knjiga na koju se poziva funkcija otvorena. U suprotnom, greška će biti vraćena sa kodom #LINK!
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 "Insert".
U ovom članku ćete naučiti kako brzo spojiti podatke iz dvije Excel tablice kada se ključni stupci ne podudaraju u potpunosti. Na primjer, kada je jedinstveni identifikator iz prve tablice prvih pet znakova identifikatora iz druge tablice. Sva rješenja predložena u ovom članku testirala sam u Excelu 2013, 2010 i 2007.
Dakle, postoje dva Excel lista koje je potrebno kombinovati za dalju analizu podataka. Pretpostavimo da jedna tabela sadrži cene (kolona Cena) i opise robe (kolona Pivo) koju prodajete, a druga tabela prikazuje podatke o dostupnosti robe na zalihama (kolona Na zalihama). Ako ste vi ili vaše kolege sastavili obje tabele za katalog, tada obje moraju sadržavati barem jednu ključnu kolonu s jedinstvenim identifikatorima proizvoda. Opis proizvoda ili cijena se mogu promijeniti, ali jedinstveni identifikator uvijek ostaje isti.
Poteškoće počinju kada dobijete neke tablice od proizvođača ili od drugih odjela kompanije. Stvari mogu postati još složenije ako se iznenada uvede novi format za jedinstvene identifikatore ili ako se jedinice skladištenja (SKU) čak i neznatno promijene. I vi ste suočeni sa zadatkom kombiniranja novih i starih tablica s podacima u Excelu. Na ovaj ili onaj način, nastaje situacija kada postoji samo djelomično podudaranje zapisa u ključnim stupcima, na primjer, “ 12345 " i " 12345-novi_sufiks“. Shvaćate da je ovo isti SKU, ali računar nije tako pametan! Ovo neprecizno podudaranje onemogućava korištenje običnih Excel formula za kombiniranje podataka iz dvije tablice.
A ono što je stvarno loše je da korespondencije mogu biti potpuno nejasne, i “ Neko društvo” u jednoj tabeli može se pretvoriti u “ CJSC "Neka kompanija"” u drugoj tabeli, i “ Nova kompanija (ranije neka kompanija)" i " Old Company” također će se pokazati kao ploča iste kompanije. Znate ovo, ali kako Excel to može objasniti?
komentar: Rješenja opisana u ovom članku su univerzalna. Možete ih prilagoditi za dalju upotrebu sa bilo kojim standardnim formulama, kao npr VLOOKUP(VLOOKUP) TRAŽI(UTAKMICA) GPR(HLOOKUP) i tako dalje.
Odaberite odgovarajući primjer da pređete direktno na rješenje koje vam je potrebno:
Ključna kolona u jednoj od tabela sadrži dodatne znakove
Pogledajmo dvije tabele. Kolone prve tabele sadrže broj proizvoda (SKU), naziv piva (Beer) i njegovu cenu (Price). Druga tabela bilježi SKU i broj boca na zalihama (In stock). Umjesto piva može biti bilo koji proizvod, a broj kolona u stvarnom životu može biti mnogo veći.
U tabeli sa dodatnim simbolima kreirajte pomoćnu kolonu. Možete ga dodati na kraj tabele, ali je najbolje da ga umetnete odmah posle ključnog stupca tako da bude vidljiv.
Ključni stupac u tabeli u našem primjeru je A sa podacima o SKU-u, a iz njega morate izdvojiti prvih 5 znakova. Dodajmo pomoćnu kolonu i imenujemo je SKU pomoćnik:
Spremni! Sada imamo ključne stupce s tačnim podudaranjem vrijednosti - stupac SKU pomoćnik u glavnoj tabeli i koloni SKU u tabeli u kojoj će se izvršiti pretraga.
Sada koristite funkciju VLOOKUP(VLOOKUP) dobijamo željeni rezultat:
Druge formule
- Prvo izvucite X znakova desno: na primjer, 6 znakova desno od unosa “DSFH-164900”. Formula će izgledati ovako:
DESNO (A2,6)
=DESNO(A2,6) - Prvo preskoči X znakova, izdvojite sljedeće Y znakova: na primjer, trebate izdvojiti “0123” iz unosa “PREFIX_0123_SUFF”. Ovdje trebamo preskočiti prvih 8 znakova i izdvojiti sljedeća 4 znaka. Formula će izgledati ovako:
PSTR(A2;8;4)
=MID(A2,8,4) - Izdvojite sve znakove do graničnika dužina rezultirajućeg niza može varirati. Na primjer, trebate izdvojiti “123456” i “0123” iz zapisa “123456-sufiks” i “0123-sufiks”, respektivno. Formula će izgledati ovako:
LIJEVO SIMB(A2,PRONAĐI("-",A2)-1)
=LEVO(A2,PRONAĐI("-",A2)-1)
Ukratko, možete koristiti Excel funkcije kao što su LEVSIMV(LIJEVO), PRAVO(DESNO), PSTR(MID), FIND(FIND) za dohvat bilo kojeg dijela kompozitnog indeksa. Ako imate bilo kakvih poteškoća s ovim, mi ćemo učiniti sve da vam pomognemo.
Podaci iz ključne kolone u prvoj tabeli se dijele na dvije ili više kolona u drugoj tablici
Pretpostavimo da tabela koju tražite sadrži kolonu sa identifikatorima. Ćelije u ovoj koloni sadrže unose poput XXXX-YYYY, Gdje XXXX– ovo je kodna oznaka grupe roba (mobilni telefoni, televizori, video kamere, fotoaparati), i YYYY– ovo je šifra proizvoda unutar grupe. Glavna tabela se sastoji od dvije kolone: jedna sadrži šifre grupa proizvoda (Grupa), druga sadrži šifre proizvoda (ID). Ne možemo jednostavno odbaciti šifre grupa proizvoda, jer se isti kod proizvoda može ponoviti u različitim grupama.
U ćeliji C2 Napišimo sljedeću formulu:
UKLJUČITI (A2;"-";B2)
=KONKATENIRAJ(A2,"-",B2)
Evo A2– ovo je adresa ćelije koja sadrži kod grupe; simbol " – ” je separator; B2– ovo je adresa ćelije koja sadrži šifru proizvoda. Kopirajmo formulu u preostale redove.
Sada neće biti teško kombinirati podatke iz naše dvije tabele. Uskladit ćemo kolonu Full ID prva tabela sa kolonom ID drugi sto. Ako se pronađe podudaranje, unosi iz kolona Opis I Cijena druga tabela će biti dodata prvoj tabeli.
Podaci u ključnim kolonama se ne podudaraju
Evo primjera: Vi ste vlasnik male trgovine, primate robu od jednog ili više dobavljača. Svaki od njih ima svoju nomenklaturu, različitu od vaše. Kao rezultat toga, pojavljuju se situacije u kojima se vaš unos “Case-Ip4S-01” podudara sa unosom “SPK-A1403” u Excel datoteci primljenoj od dobavljača. Takva odstupanja se javljaju nasumično i ne postoji opšte pravilo za automatsko pretvaranje „SPK-A1403“ u „Case-Ip4S-01“.
Loše vijesti: Podaci sadržani u ove dvije Excel tablice morat će se obraditi ručno kako bi se kasnije mogli kombinirati.
Dobre vijesti: Ovo je potrebno uraditi samo jednom, a rezultirajuća pomoćna tabela se može sačuvati za buduću upotrebu. Zatim možete automatski spojiti ove tabele i tako uštedjeti mnogo vremena
1. Kreirajte pomoćnu tabelu za pretragu.
Kreirajte novi Excel list i dajte mu naziv SKU konverter. Kopiranje cijele kolone Our.SKU sa čaršava Store na novi list, izbrišite duplikate i ostavite samo jedinstvene vrijednosti u njemu.
Dodajte kolonu pored nje Supp.SKU i ručno potražite korespondenciju između vrijednosti stupaca Our.SKU I Supp.SKU(opisi iz kolone će nam pomoći u tome Opis). Ovo je dosadan posao, neka vas raduje pomisao da ga morate obaviti samo jednom :-).
Kao rezultat, imamo sljedeću tabelu:
2. Ažurirajte glavnu tablicu koristeći podatke iz tabele za pretraživanje.
Umetnite novu kolonu u glavnu tabelu (Store sheet) Supp.SKU.
Zatim, koristeći funkciju VLOOKUP(VLOOKUP) uporedi listove Store I SKU konverter Our.SKU, a za ažurirane podatke – stupac Supp.SKU.
Kolona Supp.SKU popunjen originalnim kodovima proizvođača.
komentar: Ako je u koloni Supp.SKU pojavljuju se prazne ćelije, tada morate uzeti sve kodove SKU koje odgovaraju ovim praznim ćelijama, dodajte ih u tabelu SKU konverter i pronađite odgovarajući kod iz tabele dobavljača. Nakon toga ponovite korak 2.
3. Prenesite podatke iz pregledne tablice u glavnu tablicu
Naša glavna tabela ima ključnu kolonu sa tačnim podudaranjem za unose tabele pretraživanja, tako da je ovaj zadatak sada lak
Korištenje funkcije VLOOKUP(VLOOKUP) spajanje podataka lista Store sa podacima o listovima Dobavljač na veliko 1, koristeći kolonu za pronalaženje podudaranja Supp.SKU.
Evo primjera ažuriranih podataka u koloni Veleprodajna cijena:
Jednostavno je, zar ne? Postavite svoja pitanja u komentarima na članak, pokušat ću odgovoriti što je prije moguće.
Ponekad morate stvoriti nepromjenjivu vezu između jedne ćelije i vrijednosti u drugoj ćeliji, bilo da je unesena ručno ili dobijena iz formule.
- U otvorenom prozoru radnog lista odaberite ćeliju s originalnom vrijednošću.
- Koristite bilo koju poznatu metodu kopiranja (dugme „Kopiraj“ na kartici „Početna“, Ctrl+C, itd.).
- Odaberite ćeliju koja će biti povezana s originalnom.
- Idite na karticu "Početna" i u grupi "Međuspremnik" proširite meni dugmeta "Zalijepi".
- Na listi komandi izaberite „Insert Link“ (Slika 4.4).
- Spremite rezultat klikom na tipku Esc.
Bilješka
Dalje promjene vrijednosti u izvornoj ćeliji automatski će se odraziti na povezanu ćeliju.
Rice. 4.4. Početna kartica. Meni dugmeta za umetanje. Stavka "Ubaci vezu"
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 referencu ćelije.
Kreiranje veza između radnih knjiga
- Otvorite obje radne knjige u Excelu
- U izvornoj radnoj knjizi odaberite ćeliju koju treba povezati i kopirajte je (prečica na tipkovnici Ctrl+C)
- 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
- 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 veze 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 veze odaberite vezu koja vas zanima i kliknite na dugme Prekinite vezu.
Prilikom obavljanja određenih zadataka u Excel-u ponekad morate raditi s nekoliko tabela koje su također međusobno povezane. To jest, 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 sveske, 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 nizu jedne 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 prikazan je istim redosledom.
Potrebno je osigurati da se podaci o stopama iz drugog lista povuku u odgovarajuće ćelije prvog.
- 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.
- 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.
- 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:
- 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.
- Svi podaci iz slične kolone na listu 2 povučeni su 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.
- Odaberite prvi element kolone “Oklada”. Idite na Čarobnjak za funkcije klikom na ikonu „Insert Function“.
- U čarobnjaku za funkcije, u grupi "Veze i nizovi", pronađite i odaberite naziv "INDEX".
- 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”.
- 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“.
- 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...“.
- Otvara se standardni prozor čarobnjaka za funkcije. U njemu idemo u istu grupu "Veze i nizovi". Ovaj put sa liste izaberite stavku “SEARCH”. Kliknite na dugme “OK”.
- Prozor argumenta operatora MATCH 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 pretrage” 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 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 argumenata. Postavite kursor u polje „Vrednost za pretragu“, kliknite na prvu ćeliju kolone „Ime“ na listu 1.
- 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“.
- 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 “Viewed array”. Č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”.
- 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))
- Sada možete kopirati pomoću ručke za popunjavanje. Zovemo ga na isti način na koji smo ranije govorili i proširujemo ga do kraja raspona tabele.
- Kao što vidimo, uprkos činjenici da se redosled redova u 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 u Tabeli 3 budu prikazani opšti podaci o platama za preduzeće bez raščlanjivanja po zaposlenima. Da biste to učinili, stope zaposlenika će se izvući iz Tablice 2, sabrati (pomoću funkcije SUM) i pomnožiti sa koeficijentom koristeći formulu.
- Odaberite ćeliju u kojoj će rezultati obračuna platnog spiska biti prikazani na listu 3. Kliknite na dugme „Ubaci funkciju“.
- Prozor čarobnjaka za funkcije trebao bi se pokrenuti. Idite na grupu "Matematički" i tamo odaberite naziv "SUM". Zatim kliknite na dugme “OK”.
- 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 namjenu će biti dovoljan samo jedan. Postavite kursor u polje „Broj1“. Kliknite na prečicu “Sheet 2” iznad statusne trake.
- 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”.
- Nakon toga, automatski se prebacujemo na list 1. Kao što vidite, ukupan iznos stopa zaposlenih je već prikazan u odgovarajućem elementu.
- 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.
- Vraćamo se na list 2 i mijenjamo stopu bilo kojeg zaposlenika.
- 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.
- 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.
- 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 „Insert Link“.
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 odaberite poziciju sa istim imenom.
- Ovo će otvoriti prozor za posebno lijepljenje. Kliknite na dugme „Umetni vezu“ u donjem levom uglu ćelije.
- 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
Metoda 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.
- 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.
- 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“.
- 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.
- 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 je ovo dugme neaktivno.
- 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".
- Otvara se informativni prozor koji sadrži upozorenje o posljedicama daljnjih radnji. Ako ste sigurni šta ćete učiniti, kliknite na dugme „Raskini veze“.
- 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.
- 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.
- Zatim, bez poništavanja odabira istog fragmenta, ponovo kliknite desnim tasterom miša na njega. Ovog puta na listi radnji kliknite na ikonu „Vrijednosti“ koja se nalazi u grupi alata „Opcije umetanja“.
- 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. Istovremeno, 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.
