Rad s povezanim tablicama u programu Microsoft Excel

Prilikom obavljanja određenih zadataka u Excelu, ponekad se morate nositi s nekoliko tablica, koje su također povezane. To znači da se podaci iz jedne tablice povlače u druge, a kada se promijene, preračunavaju se vrijednosti svih povezanih tabličnih raspona.

Povezane tablice su vrlo prikladne za obradu velike količine informacija. Sve informacije u jednoj tablici, osim toga, ako nisu homogene, nisu baš prikladne. Slicnim predmetima teško je raditi i tražiti ih. Ovaj problem je samo dizajniran kako bi se uklonili povezani tablice, informacije između kojih se distribuira, ali istodobno su međusobno povezane. Povezani tablični rasponi mogu se nalaziti ne samo unutar jednog lista ili jedne knjige, nego i biti smješteni u zasebnim knjigama (datotekama). Posljednje dvije opcije se najčešće koriste u praksi, budući da je svrha ove tehnologije samo da se izbjegne nakupljanje podataka, a njihovo postavljanje na jednu stranicu ne utječe na rješavanje problema. Doznajemo kako stvoriti i kako raditi s ovom vrstom upravljanja podacima.

Izrada povezanih tablica

Prije svega, neka se zadrži na pitanju, na koji način je moguće stvoriti vezu između različitih tabularnih raspona.

Metoda 1: Izravno vezanje tablica prema formuli

Najlakši način povezivanja podataka jest korištenje formula koje se odnose na druge tablične raspone. Zove se izravno vezanje. Ta je metoda intuitivno razumljiva, budući da se s njim vezivanje gotovo izvodi točno kao stvaranje reference na podatke u jednom tabličnom nizu.

Pogledajmo kako primjer može izravno vezati vezu. Imamo dvije tablice na dva lista. Na jednoj tablici, obračun plaća izračunava se pomoću formule množenjem stopa zaposlenika po jednom koeficijentu za sve.

Na drugom listu nalazi se tablični raspon, u kojem se nalazi popis zaposlenika s njihovim plaćama. Popis zaposlenika u oba slučaja prikazan je u jednoj poretku.

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

1. Na prvom listi odaberite prvu ćeliju stupca "Bet" . Stavili smo znak "=" . Zatim kliknite oznaku "List 2" , koja se nalazi na lijevoj strani sučelja programa Excel iznad trake statusa.
2. Prebacuje se na drugo područje dokumenta. Kliknemo na prvu ćeliju u stupcu "Licitiranje" . Zatim kliknite gumb Enter na tipkovnici da biste unijeli podatke u ćeliju u kojoj je prethodno postavljen jednak znak.
3. Tada dolazi do automatskog prijelaza na prvi list. Kao što vidite, vrijednost prve stope zaposlenika iz druge tablice povučena je u odgovarajuću ćeliju. Postavljanje pokazivača na ćeliju koja sadrži okladu, vidimo da se uobičajena formula koristi za prikaz podataka na zaslonu. No, prije koordinata ćelije, iz koje se podaci šalju, izražava se izraz "Sheet2!" , Koji označava naziv područja dokumenta gdje se nalaze. Opća formula u našem slučaju izgleda ovako:

   =Лист2!B2

4. Sada morate prenijeti podatke o stopama svih ostalih zaposlenika poduzeća. Naravno, to se može učiniti na isti način na koji smo završili zadatak prvog zaposlenika, ali s obzirom na to da se oba popisa zaposlenika nalaze u istom redoslijedu, zadatak može biti uvelike pojednostavljen i ubrzao njegovo rješenje. To se može učiniti jednostavnim kopiranjem formule na donji raspon. Zbog činjenice da su referencama u programu Excel prema zadanim postavkama relativne, kada se kopiraju, vrijednosti se premještaju, što nam je potrebno. Sam postupak kopiranja može se izvesti pomoću oznake za ispunjavanje.

   Zatim stavite pokazivač u donji desni dio elementa s formulom. Nakon toga, kursor treba pretvoriti u oznaku za ispunu u obliku crnog križa. Stisnemo lijevu tipku miša i povucimo kursor do samog dna stupca.

5. Svi podaci sa sličnog stupca na listi 2 bili su povučeni u tablicu na listi 1 . Ako promijenite podatke na listi 2, oni će se automatski promijeniti na prvom.

Način 2: Korištenje veze INDEX-MAP

Ali što ako popis zaposlenika u tabličnim matricama nije u istom redoslijedu? U ovom slučaju, kao što je spomenuto ranije, jedna je mogućnost uspostaviti vezu između svake od ćelija koje treba ručno povezati. Ali to je prikladno samo za male stolove. Za masivne raspone, ova opcija u najboljem slučaju će dugo trajati, a najgore - u praksi to uopće neće biti moguće. Ali taj problem možete riješiti uz pomoć hrpe operatera INDEX - POSITION . Pogledajmo kako se to može učiniti povezivanjem podataka u tabličnim rasponima koji su opisani u prethodnoj metodi.

1. Odaberite prvi element stupca "Bet" . Idite na Čarobnjak za funkcije klikom na ikonu "Umetni funkciju" .
2. U čarobnjaku za funkciju u skupini "Reference i polja" nalazimo i odaberemo naziv "INDEX" .
3. Ovaj operator ima dva oblika: oblik za rad s poljem i referencu. U našem slučaju je potrebna prva opcija, tako da u sljedećem prozoru za odabir formata koji se otvori odaberite ga i kliknite gumb "U redu" .
4. Pokrenut je argumentni prozor INDEX . Zadatak ove funkcije je da izlazi vrijednost koja je u odabranom rasponu u retku s navedenim brojem. Opća formula operatora INDEX je sljedeća:

   =ИНДЕКС(массив;номер_строки;[номер_столбца])

   "Array" je argument koji sadrži adresu raspona iz koje ćemo dohvatiti podatke brojem navedenog niza.

   "Broj linije" argument je broj tog retka. Važno je znati da redni broj treba biti naveden ne 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 nužno opisati njezinu suštinu zasebno.

   Stavili smo pokazivač u polje "Array" . Nakon toga idite na listu 2 i držite lijevu tipku miša odaberite cijeli sadržaj stupca "Bet" .

5. Nakon što se koordinate prikazuju u prozoru operatora, stavite pokazivač u polje "Broj reda" . Izlazit ćemo ovaj argument pomoću operatora LIST . Zato kliknite na trokut koji se nalazi lijevo od linije funkcija. Prikazan je popis nedavno korištenih operatora. Ako pronađete naziv "SEARCH" među njima, možete kliknuti na njega. U suprotnom, kliknite posljednju stavku na popisu - "Ostale funkcije ..." .
6. Počinje standardni prozor čarobnjaka . Prešli smo ga u istu skupinu "Reference i polja". U ovom trenutku na popisu odaberite stavku "SEARCH" . Kliknite gumb "U redu" .
7. Aktiviranje OPERATOR prozora argumenata je aktivirano . Navedena funkcija ima za cilj prikazati broj vrijednosti u određenom nizu po imenu. Zahvaljujući ovoj značajci izračunavamo broj linije određene vrijednosti za INDEX funkciju. Sintaksa MATCH-a je sljedeća:

   =ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

   "Vrijednost zahtjeva" argument je koji sadrži naziv ili adresu ćelije izvan raspona u kojem se nalazi. To je položaj tog imena u ciljnom rasponu i trebao bi se izračunati. U našem slučaju, prvi argument bit će referenca na ćelije na listi 1 , u kojoj se nalaze imena zaposlenika.

   "Skenirani niz" je argument koji je referenca na polje u kojem se određena vrijednost pretražuje kako bi utvrdila njegov položaj. Kod nas ta će uloga izvršiti adresu stupca " Naziv" na listi 2 .

   "Vrsta podudaranja" argument je opcionalan, ali za razliku od prethodne izjave, potreban je ovaj opcionalni argument. Označava način na koji će operater podudarati vrijednost pretraživanja s poljem. Ovaj argument može imati jednu od tri vrijednosti: -1 ; 0 ; 1 . Za neuredne polja odaberite opciju "0" . Ova je opcija prikladna za naš slučaj.

   Zato počnimo popunjavati polja prozora argumenata. Stavili smo pokazivač u polje "Vrijednost pretraživanja" , kliknite prvu ćeliju stupca "Naziv" na listi 1 .

8. Nakon prikazivanja koordinata postavljamo pokazivač u polje "Skenirani niz" i idemo na oznaku "List 2" , koja se nalazi na dnu prozora programa Excel iznad linije statusa. Pritisnite lijevu tipku miša i odaberite kursorom sve ćelije u stupcu "Naziv" .
9. Nakon što su koordinate prikazane u polju "Skenirani niz" , idite na polje "Podudaranje" i podesite broj "0" s tipkovnice. Nakon toga vraćamo se ponovno na polje "Pogledano polje" . Pitanje je da ćemo izvesti kopiranje formule, kao što smo to učinili u prethodnoj metodi. Adrese će se pomaknuti, ali moramo popraviti koordinate polja koje pretražujemo. Ne smije se pomaknuti. Odaberite koordinate kursorom i pritisnite funkcijsku tipku F4 . Kao što možete vidjeti, znak dolara pojavio se pred koordinatama, što znači da je referenca od relativno prema apsolutnom. Zatim kliknite gumb "OK" .
10. Rezultat se prikazuje u prvoj ćeliji stupca "Bet" . No prije izrade kopije moramo popraviti još jedno područje, prvo argumentiranje funkcije INDEX . Da biste to učinili, odaberite stupac koji sadrži formulu i prijeđite na redak formata. Odaberite prvi argument INDEX operatora ( B2: B7 ) i pritisnite tipku F4 . Kao što vidite, znak dolara pojavio se u blizini odabranih koordinata. Kliknemo na tipku Enter . Općenito, formula ima sljedeću formu:

    =ИНДЕКС(Лист2!$B$2:$B$7;ПОИСКПОЗ(Лист1!A4;Лист2!$A$2:$A$7;0))

11. Sada možete kopirati koristeći ručicu za punjenje. Nazvali smo ga na isti način na koji smo ranije razgovarali i proširili ga do kraja tabličnog raspona.
12. Kao što vidimo, unatoč činjenici da redoslijed redaka za dvije povezane tablice ne poklapa, ipak, sve su vrijednosti stroži prema prezimenima zaposlenika. To je postignuto korištenjem kombinacije operatora INDEX - POSITION .

Pročitajte također:
Funkcija INDEX u programu Excel
Funkcija EXPLORATION u programu Excel

Treća metoda: Izvođenje matematičkih operacija na povezanim podacima

Izravno vezanje podataka također je dobro jer omogućuje ne samo prikazivanje vrijednosti koje se prikazuju u drugim tabličnim rasponima u jednoj od tablica, nego i za obavljanje različitih matematičkih operacija s njima (dodavanje, podjela, oduzimanje, razmnožavanje itd.).

Pogledajmo kako se to radi u praksi. Učinimo to tako da će na listi 3 biti prikazani opći podaci o plaći u poduzeću bez zaposlenika. Za to se stope osoblja izvlače iz liste 2 , dodaju se (pomoću SUM funkcije) i pomnožite s koeficijentom pomoću formule.

1. Izaberite ćeliju na kojoj će biti rezultat izračuna plaća na listi 3 . Kliknite gumb "Umetni funkciju" .
2. Trebali biste započeti prozor čarobnjaka . Prebacujemo u grupu "Matematički" i odaberemo ime "SUMM" . Zatim kliknite gumb "OK" .
3. SUM funkcija se premješta u prozor s argumentima, koji se koristi za izračun zbroja odabranih brojeva. Ona ima sljedeću sintaksu:

   =СУММ(число1;число2;…)

   Polja u prozoru odgovaraju argumentima određene funkcije. Iako njihov broj može doseći 255 komada, ali za našu svrhu bit će dovoljno samo jedan. Stavili smo pokazivač na polje "Number1" . Kliknite oznaku "List 2" iznad linije statusa.

4. Nakon što se preselimo u željeni dio knjige, odaberite stupac koji treba zbrajati. To radimo s pokazivačem, držite lijevu tipku miša. Kao što vidite, koordinate odabranog područja odmah se prikazuju u polju prozora argumenata. Zatim kliknite gumb "OK" .
5. Nakon toga automatski se prebacujemo na listu 1 . Kao što vidite, ukupni iznos plaće 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 stopa po koeficijentu. Stoga ponovno odaberemo ćeliju u kojoj se nalazi zbroj. Nakon toga idite na traku s formulom. Dodamo simbol množenja ( * ) na formulu u njemu, a zatim kliknite element na kojem se nalazi indeks koeficijenata. Da biste izvršili izračun, kliknite tipku Enter na tipkovnici. Kao što možete vidjeti, program je izračunao ukupnu plaću za poduzeće.
7. Vratite se na tablicu 2 i promijenite stopu bilo kojeg zaposlenika.
8. Nakon toga vratimo se na stranicu s ukupnim iznosom. Kao što vidite, zbog promjena u povezanoj tablici, rezultat ukupne plaće automatski je ponovno izračunat.

Metoda 4: posebni umetak

Također možete povezati tablične polja s programom Excel pomoću posebnog umetka.

1. Odaberite vrijednosti koje želite "zategnuti" u drugoj tablici. U našem slučaju, to je raspon stupca "Bet" na listi 2 . Desnom tipkom miša kliknite na odabrani fragment. Na otvorenom popisu odaberite stavku "Copy" . Alternativna kombinacija je Ctrl + C. Nakon toga, pomaknite se na list 1 .
2. Ako se prebacite na željeno područje knjige, odaberite ćelije u kojima želite povući vrijednosti. U našem slučaju, to je stupac "Bet" . Desnom tipkom miša kliknite na odabrani fragment. U kontekstnom izborniku u alatu "Parametri umetanja" kliknite ikonu "Umetni vezu" .

   Postoji i alternativa. Usput, to je jedini za starije verzije Excela. U kontekstnom izborniku mišemo kursorom iznad stavke "Posebna lijepljenje" . U dodatnom izborniku koji se otvori odaberite stavku s istim nazivom.

3. Nakon toga otvara se posebni prozorčić umetanja. Kliknite gumb "Umetni vezu" u donjem lijevom kutu ćelije.
4. Bez obzira na opciju koju odaberete, vrijednosti iz jednog tabličnog polja bit će umetnute u drugu. Ako promijenite podatke u izvoru, automatski će se promijeniti iu umetnutom području.

Pouka: Posebni umetak u Excelu

Način 5: odnos između tablica u nekoliko knjiga

Osim toga, možete organizirati vezu između područja tablice u različitim knjigama. Koristi se poseban umetni alat. Radnje će biti potpuno jednake onima koje smo vidjeli u prethodnoj metodi, osim što nećete morati kretati između dijelova iste knjige, već između datoteka. Naravno, sve povezane knjige trebaju biti otvorene istodobno.

1. Odaberite raspon podataka koje želite prenijeti na drugu knjigu. Pritisnite desnu tipku miša i odaberite stavku "Kopiraj" u otvorenom izborniku.
2. Zatim se prebacujemo u knjigu u kojoj će ti podaci biti umetnuti. Odaberite željeni raspon. Kliknite desnom tipkom miša. U kontekstnom izborniku u grupi "Opcije umetanja" odaberite stavku "Zalijepi vezu" .
3. Nakon toga, vrijednosti će biti umetnute. Ako promijenite podatke u izvornoj knjizi, tablični niz iz radne knjige automatski će ih privući. I nije potrebno da obje knjige budu otvorene za to. Dovoljno je otvoriti samo jednu radnu knjigu i automatski će se prikupljati podatke iz zatvorenog povezanog dokumenta ako je prethodno promijenjen.

Ali treba napomenuti da će u ovom slučaju umetanje biti u obliku nepromjenljivog polja. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Изменения в таком массиве, связанном с другой книгой, можно произвести только разорвав связь.

Разрыв связи между таблицами

Иногда требуется разорвать связь между табличными диапазонами. Причиной этого может быть, как вышеописанный случай, когда требуется изменить массив, вставленный из другой книги, так и просто нежелание пользователя, чтобы данные в одной таблице автоматически обновлялись из другой.

Способ 1: разрыв связи между книгами

Разорвать связь между книгами во всех ячейках можно, выполнив фактически одну операцию. При этом данные в ячейках останутся, но они уже будут представлять собой статические не обновляемые значения, которые никак не зависят от других документов.

1. В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные» . Щелкаем по значку «Изменить связи» , который расположен на ленте в блоке инструментов «Подключения» . Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.
2. Запускается окно изменения связей. Выбираем из списка связанных книг (если их несколько) тот файл, с которым хотим разорвать связь. Щелкаем по кнопке «Разорвать связь» .
3. Открывается информационное окошко, в котором находится предупреждение о последствиях дальнейших действий. Если вы уверены в том, что собираетесь делать, то жмите на кнопку «Разорвать связи» .
4. После этого все ссылки на указанный файл в текущем документе будут заменены на статические значения.

Способ 2: вставка значений

Но вышеперечисленный способ подходит только в том случае, если нужно полностью разорвать все связи между двумя книгами. Что же делать, если требуется разъединить связанные таблицы, находящиеся в пределах одного файла? Сделать это можно, скопировав данные, а затем вставив на то же место, как значения. Кстати, этим же способом можно проводить разрыв связи между отдельными диапазонами данных различных книг без разрыва общей связи между файлами. Посмотрим, как этот метод работает на практике.

1. Выделяем диапазон, в котором желаем удалить связь с другой таблицей. Щелкаем по нему правой кнопкой мыши. В раскрывшемся меню выбираем пункт «Копировать» . Вместо указанных действий можно набрать альтернативную комбинацию горячих клавиш Ctrl+C .
2. Далее, не снимая выделения с того же фрагмента, опять кликаем по нему правой кнопкой мыши. На этот раз в списке действий щелкаем по иконке «Значения» , которая размещена в группе инструментов «Параметры вставки» .
3. После этого все ссылки в выделенном диапазоне будут заменены на статические значения.

Как видим, в Excel имеются способы и инструменты, чтобы связать несколько таблиц между собой. При этом, табличные данные могут находиться на других листах и даже в разных книгах. При необходимости эту связь можно легко разорвать.

• ← Metode za uspoređivanje tablica u Microsoft Excelu
• Trg u Microsoft Excelu →