Delo s povezanimi tabelami v programu Microsoft Excel

Pri izvajanju nekaterih nalog v Excelu se včasih ukvarjate z več tabelami, ki so tudi povezane. To pomeni, da se podatki iz ene tabele potegnejo v druge in ko se spremenijo, se vrednosti v vseh povezanih razpredelnicah tabele preračunajo.

Povezane tabele so zelo priročne za obdelavo velike količine informacij. Vse informacije v eni tabeli poleg tega, če niso homogene, niso zelo priročne. S podobnimi predmeti je težko delati in jih iskati. Ta problem je namenjen izključitvi povezanih tabel, katerih podatki so razdeljeni, hkrati pa so medsebojno povezani. Povezane razpredelnice tabele lahko najdete ne samo v enem listu ali eni knjigi, ampak tudi v ločenih knjigah (datotekah). Zadnja dva opcija sta najpogosteje uporabljena v praksi, saj je namen te tehnologije le, da se izogne ​​kopičenju podatkov in jih zgrabijo na eni strani, ne rešuje problema v osnovi. Ugotovimo, kako ustvarjati in kako delati s to vrsto upravljanja podatkov.

Ustvarjanje povezanih tabel

Najprej se osredotočimo na vprašanje, na kakšen način je mogoče ustvariti povezavo med različnimi tabelami.

Metoda 1: Neposredna vezava tabel po formuli

Najlažji način povezovanja podatkov je uporaba formul, ki se nanašajo na druge razpredelnice tabele. Imenuje se neposredno vezava. Ta metoda je intuitivno razumljiva, saj z njim povezava poteka skoraj enako kot ustvarjanje sklicevanj na podatke v eni mizi.

Oglejmo si, kako primer lahko tvori povezavo z neposrednim povezovanjem. Na dveh listih imamo dve mizi. Na eni mizi se izplačana plača izračuna s pomočjo formule, tako da pomnoži stopnjo zaposlenih z enim koeficientom za vse.

Na drugem listu je tabela, v kateri je seznam zaposlenih z njihovimi plačami. Seznam zaposlenih v obeh primerih je predstavljen v enem vrstnem redu.

Potrebno je, da se podatki o stopnjah iz drugega lista potegnejo v ustrezne celice prvega.

1. Na prvem listu izberite prvo celico stolpca »Stavi« . Vstavili smo znak "=" . Nato kliknite na etiketo "List 2" , ki se nahaja na levi strani vmesnika Excel nad vrstico stanja.
2. Premakne se na drugo področje dokumenta. Kliknemo na prvo celico v stolpcu »Ponudbe« . Nato kliknite na gumb Enter na tipkovnici, da vnesete podatke v celico, kjer ste predhodno nastavili enak znak.
3. Nato se pojavi samodejni prehod na prvi list. Kot lahko vidite, se vrednost prve zaposlene stopnje iz druge tabele potegne v ustrezno celico. Če nastavimo kazalko na celici, ki vsebuje stavo, vidimo, da se običajna formula uporablja za prikaz podatkov na zaslonu. Toda pred koordinatami celice, iz katere izvirajo podatki, se izraz "Sheet2!" Stoji , ki označuje ime območja dokumenta, v katerem se nahajajo. Splošna formula v našem primeru je videti takole:

   =Лист2!B2

4. Zdaj morate podatke prenesti na stopnje vseh drugih zaposlenih v podjetju. Seveda je to mogoče storiti na enak način, kot smo opravili nalogo prvega zaposlenega, vendar ob upoštevanju, da sta oba seznama zaposlenih v istem vrstnem redu, se lahko naloga močno poenostavi in ​​pospeši rešitev. To lahko naredite preprosto tako, da kopirate formulo v obseg spodaj. Zaradi dejstva, da so referenčne vrednosti v Excelu privzeto relativne, ko se kopirajo, se vrednosti premaknejo, kar je tisto, kar potrebujemo. Postopek kopiranja se lahko izvede z uporabo označevalnika polnila.

   Torej, kurzor postavite na spodnji desni del elementa s formulo. Po tem je treba kazalec preoblikovati v oznako za polnjenje v obliki črnega križa. Levi gumb miške pritegnemo in povlečemo kurzor na samo dno stolpca.

5. Vsi podatki iz podobnega stolpca na listu 2 so potegnili v tabelo na listu 1 . Če spremenite podatke na listu 2, se bodo samodejno spremenili na prvem.

Metoda 2: Uporaba povezave INDEX-MAP

Toda kaj, če seznam zaposlenih v razpredelnicah ni v enakem vrstnem redu? V tem primeru je, kot je bilo že omenjeno, ena od možnosti vzpostaviti povezavo med vsako od celic, ki jo je treba povezati ročno. Toda to je primerno le za majhne mize. Za množično območje bo ta možnost v najboljšem primeru trajala zelo dolgo, in v najslabšem primeru - v praksi sploh ne bo mogoče. Toda to težavo lahko rešite s pomočjo številnih operaterjev INDEX - POSITION . Poglejmo, kako je to mogoče storiti s povezovanjem podatkov v razpredelnicah tabel, obravnavanih v prejšnji metodi.

1. Izberite prvi element stolpca »Stavi« . Pojdite v Čarovnik za funkcije s klikom na ikono "Vstavi funkcijo" .
2. V čarovniku za funkcije v skupini "Reference in nizi" najdemo in izberemo ime "INDEX" .
3. Ta operater ima dve obliki: obrazec za delo z nizi in referenco. V našem primeru je potrebna prva možnost, zato se v oknu za izbiro naslednjega obrazca, ki ga odprete, jo izberite in kliknite gumb »V redu« .
4. Začelo se je okno argumentov INDEX-a . Naloga te funkcije je, da izloči vrednost, ki je v izbranem obsegu v vrstici z določeno številko. Splošna formula operaterja INDEX je naslednja:

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

   "Array" je argument, ki vsebuje naslov obsega, iz katerega bomo podatke dobili po številu določenega niza.

   "Številka vrstice" je argument, ki je številka te vrstice. Pomembno je vedeti, da je treba številko vrstice določiti ne glede na celoten dokument, ampak samo glede na izbrano polje.

   "Številka stolpca" je argument, ki je neobvezen. Če želite posebej rešiti naš problem, ga ne bomo uporabljali, zato ni treba posebej opisati njegovega bistva.

   Postavimo kazalko v polje "Array" . Po tem pojdite na list 2 in držite levi gumb miške izberite celotno vsebino stolpca »Stavi« .

5. Ko so koordinate prikazane v oknu operaterja, postavite kurzor v polje "Številka številke" . Ta argument bomo izpisali s pomočjo operaterja LIST . Zato kliknite na trikotnik, ki se nahaja levo od funkcijske vrstice. Prikaže se seznam nedavno uporabljenih operaterjev. Če med njimi najdete ime »SEARCH« , lahko kliknete nanj. V nasprotnem primeru kliknite na zadnji element na seznamu - »Druge funkcije ...« .
6. Začne se standardno okno Čarovnika . Prehodimo ga v isto skupino "Reference in nizi". V tem trenutku izberite seznam »SEARCH« na seznamu. Kliknite gumb »V redu« .
7. Aktivira se okno OPERATOR argumentov. Navedena funkcija je namenjena prikazu številke vrednosti v določeni matriki s svojim imenom. Zahvaljujoč tej funkciji izračunamo številko vrstice določene vrednosti za funkcijo INDEX . Sintaksa MATCH-a je naslednja:

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

   "Zahtevana vrednost" je argument, ki vsebuje ime ali naslov celice zunaj območja, v katerem se nahaja. Položaj tega imena je v ciljnem obsegu in ga je treba izračunati. V našem primeru bo prvi argument referenca celic v listu 1 , v katerem so imena zaposlenih.

   "Skenirana matrika" je argument, ki je sklic na matriko, v kateri se išče določena vrednost, da bi določila njen položaj. Pri nas bo ta vloga izvršila naslov stolpca » Ime« na listu 2 .

   "Vrsta ujemanja" je argument, ki je neobvezen, vendar je za razliko od prejšnje izjave potreben ta izbirni argument. Navaja, kako se bo operator ujemal z vrednostjo iskanja v matriko. Ta argument ima lahko eno od treh vrednosti: -1 ; 0 ; 1 . Za neurejene nizove izberite možnost »0« . Ta možnost je primerna za naš primer.

   Torej, začnimo zapolniti polja okna argumentov. Postavili smo kazalko v polje "Search value" , kliknite na prvo celico stolpca "Ime" na listu 1 .

8. Po prikazu koordinat smo kazalko postavili v polje »Skenirana matrika« in odprli oznako »Sheet 2« , ki se nahaja na dnu okna Excel nad vrstico stanja. Spustite levi gumb miške in s kazalcem izberite vse celice v stolpcu »Ime« .
9. Ko so bile njihove koordinate prikazane v polju »Skenirana polja« , pojdite na polje »Vrsta ujemanja« in s tipkovnice tam nastavite številko »0« . Po tem se ponovno vrnemo na polje »Pogledano polje« . Zadeva je, da bomo kopirali formulo, kot smo to storili v prejšnji metodi. Naslovi se bodo premaknili, vendar moramo popraviti koordinate matrike, ki jo pregledujemo. Ne sme se premakniti. Izberite koordinate s kazalko in pritisnite funkcijsko tipko F4 . Kot lahko vidite, se znak za dolar pojavil pred koordinatami, kar pomeni, da je referenca od relativnega do absolutnega. Nato kliknite gumb "OK" .
10. Rezultat je prikazan v prvi celici stolpca »Stavi« . Toda preden naredimo kopijo, moramo popraviti še eno področje, in sicer prvi argument funkcije INDEX . Če želite to narediti, izberite element stolpca, ki vsebuje formulo, in premaknite se v vrstico formule. Izberite prvi argument operaterja INDEX ( B2: B7 ) in kliknite gumb F4 . Kot vidite, se je v bližini izbranih koordinat pojavil znak za dolar. Kliknemo na tipko Enter . Na splošno je formula imela naslednjo obliko:

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

11. Zdaj lahko kopirate z uporabo polnilnega ročaja. To imenujemo na enak način, kot smo prej omenili, in ga razširili na konec razpona mize.
12. Kot smo videli, kljub dejstvu, da vrstni red vrstic za dve povezani tabeli ne sovpada, so vse vrednosti poostrene glede na priimke zaposlenih. To je bilo doseženo z uporabo kombinacije operaterjev INDEX - POSITION .

Preberite tudi:
Funkcija INDEX v Excelu
Funkcija EXPLORATION v Excelu

Metoda 3: Izvajanje matematičnih operacij na povezanih podatkih

Neposredna povezava podatkov je dobra tudi zato, ker omogoča ne samo prikaz vrednosti, ki so prikazane v drugih razpredelnicah tabele v eni od tabel, ampak tudi za izvajanje različnih matematičnih operacij z njimi (dodajanje, deljenje, odštevanje, množenje itd.).

Poglejmo, kako se to izvaja v praksi. Naredimo tako, da bodo na 3. listu splošni podatki o plačah na podjetju prikazani brez razčlenitve s strani zaposlenih. Za to se bo stopnja zaposlenih potegnila iz liste 2 , dodana (z uporabo funkcije SUM ) in pomnožena s koeficientom z uporabo formule.

1. Izberite celico, na kateri se prikaže rezultat izračuna plač na listu 3 . Kliknite gumb "Vstavi funkcijo" .
2. Začeti morate okno Čarovnika . Prehodimo v skupino "Mathematical" in tam izberemo ime "SUMM" . Nato kliknite gumb "OK" .
3. Funkcija SUM se premakne v okno argumentov, ki se uporablja za izračun vsote izbranih številk. Ima naslednjo skladnjo:

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

   Polja v oknu ustrezajo argumentom določene funkcije. Čeprav lahko njihovo število doseže 255 kosov, vendar je za naš namen dovolj samo eno. Postavili smo kazalko v polje "Number1" . Kliknite na etiketo "List 2" nad vrstico stanja.

4. Ko se preselimo v želeni del knjige, izberite stolpec, ki ga je treba povzeti. To naredimo s kazalko, držimo levi gumb miške. Kot vidite, so koordinate izbranega območja takoj prikazane v polju okna argumentov. Nato kliknite gumb "OK" .
5. Po tem se samodejno premaknemo na list 1 . Kot lahko vidite, je skupni znesek plač delavca že prikazan v ustreznem elementu.
6. Toda to ni vse. Kot se spominjamo, se plača izračuna z množenjem stopnje s koeficientom. Zato ponovno izberemo celico, v kateri se nahaja vsota. Po tem pojdite na formulo. Simbolu za množenje ( * ) dodamo v formulo v njej in nato kliknite na element, v katerem je indeks koeficienta. Če želite izvesti izračun, kliknite tipko Enter na tipkovnici. Kot vidite, je program izračunal skupno plačo za podjetje.
7. Vrnite se na list 2 in spremenite stopnjo vsakega zaposlenega.
8. Po tem se vrnemo na stran s celotnim zneskom. Kot vidite, zaradi sprememb v povezani tabeli se je rezultat skupne plače samodejno preračunal.

Metoda 4: posebni vložek

Tablične matrike lahko tudi povežete z Excelom s posebnim vstavkom.

1. Izberite vrednosti, ki jih želite »zategniti« v drugi tabeli. V našem primeru je to stolpec stolpca »Stava« na listu 2 . Kliknite izbrani fragment z desnim gumbom miške. Na odprtem seznamu izberite element »Kopiraj« . Druga kombinacija je Ctrl + C. Po tem prestavite na Sheet 1 .
2. Če se pomaknete na želeno področje knjige, izberite celice, v katere želite povleči vrednosti. V našem primeru je to stolpec "Bet" . Kliknite izbrani fragment z desnim gumbom miške. V kontekstnem meniju v polju »Vstavljanje parametrov« kliknite ikono »Vstavi povezavo« .

   Obstaja tudi alternativa. Mimogrede, to je edina za starejše različice Excel. V kontekstnem meniju premikamo kazalko nad elementom »Posebno prilepi« . V dodatnem meniju, ki se odpre, izberite predmet z istim imenom.

3. Po tem se odpre posebno okno za vstavljanje. Kliknite gumb »Vstavi povezavo« v spodnjem levem kotu celice.
4. Ne glede na možnost, ki jo izberete, bodo vrednosti iz ene mize tabele vstavljene v drugo. Če spremenite podatke v viru, se bodo tudi samodejno spremenili v vstavljenem obsegu.

Lekcija: Posebni vstavki v Excelu

Metoda 5: razmerje med tabelami v več knjigah

Poleg tega lahko organizirate povezavo med tabelami v različnih knjigah. Uporablja se posebno orodje za vstavljanje. Dejanja bodo popolnoma enaka tistim, ki smo jih videli v prejšnji metodi, razen, da vam ne bo treba navigirati med deli iste knjige, temveč med datotekami. Seveda morajo biti vse povezane knjige hkrati odprti.

1. Izberite obseg podatkov, ki jih želite prenesti v drugo knjigo. Kliknite z desno miškino tipko in v odprtem meniju izberite element »Kopiraj« .
2. Nato se premaknemo v knjigo, v kateri bodo ti podatki morali vstaviti. Izberite želeni obseg. Z desno tipko miške kliknite. V kontekstnem meniju skupine "Vstavljanje možnosti" izberite element "Prilepi povezavo" .
3. Po tem bodo vrednosti vstavljene. Če spremenite podatke v izvorni knjigi, se bo tabela iz delovnega zvezka samodejno potegnila. In ni potrebno, da sta obe knjigi odprta za to. Dovolj je, da odprete samo en delovni zvezek in samodejno povleče podatke iz zaprtega dokumenta, če je bil predhodno spremenjen.

Vendar je treba opozoriti, da bo v tem primeru vstavitev v obliki nespremenljive matrike. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

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

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

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

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

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

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

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

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

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

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

• ← Metode za primerjavo tabel v programu Microsoft Excel
• Kvadrat v Microsoft Excelu →