Рад са повезаним таблицама у Мицрософт Екцелу

Приликом обављања одређених задатака у Екцел-у, понекад морате да се позабавите са неколико табела које су такође повезане. То значи да се подаци из једне табеле вуку у друге и када се мењају, вредности у свим повезаним таблицама табеле се поново израчунавају.

Повезане табеле веома су погодне за обраду великих информација. Све информације у једној табели, осим ако није хомогено, није баш згодно. Са сличним објектима тешко је радити и тражити их. Овај проблем је управо дизајниран да уклони повезане таблице, информације између којих се дистрибуирају, али истовремено су међусобно повезане. Повезани распони таблица могу се наћи не само у једном листу нити једној књизи, већ се такође налазе у засебним књигама (датотекама). Последње две опције се најчешће користе у пракси, с обзиром да је сврха ове технологије само да се избегне акумулирање података, а њихово уништавање на једној страници у суштини не решава проблем. Хајде да сазнамо како да креирамо и како да радимо са оваквим управљањем подацима.

Креирање повезаних табела

Пре свега, да се задржимо на питању, на који начин је могуће креирати везу између различитих табеларних опсега.

Метод 1: Директно везивање табела по формули

Најлакши начин повезивања података је коришћење формула које се односе на друге таблице. Зове се директно везивање. Ова метода је интуитивно разумљива, пошто се везивање врши скоро управо као стварање референци података у једном табличном низу.

Да видимо како пример може формирати везу директним везивањем. Имамо две столице на два листа. На једном столу, платни списак се обрачунава употребом формуле помножујући стопу запослених са једним коефицијентом за све.

На другом листу постоји табеларни распон, у којем постоји листа запослених са њиховом платом. Списак запослених у оба случаја представљен је у једном редоследу.

Неопходно је учинити тако да се подаци о стопама из другог листа увлаче у одговарајуће ћелије прве.

1. На првом листу одаберите прву ћелију колоне "Бет" . Ставимо га у знак "=" . Затим кликните на етикету "Лист 2" која се налази са леве стране Екцел интерфејса изнад статусне траке.
2. Прелазак на други део документа. Кликом на прву ћелију у колони "Давање понуда" . Затим кликните на дугме Ентер на тастатури да бисте унели податке у ћелију у којој је предходно постављен знак једнакости .
3. Затим дође до аутоматског преласка на први лист. Као што видите, вредност прве стопе запосленог из друге табеле је вучена у одговарајућу ћелију. Постављањем курсора на ћелију која садржи улог, видимо да се уобичајена формула користи за приказ података на екрану. Али пре координата ћелије, из којег се излазе подаци, израз "Схеет2!" Стоји , што означава назив области документа у којем се налазе. Општа формула у нашем случају изгледа овако:

   =Лист2!B2

4. Сада морате пренети податке о стопама свих осталих запослених у предузећу. Наравно, то се може учинити на исти начин на који смо задатак завршили за првог запосленог, али с обзиром на то да се обе листе запослених налазе у истом редоследу, задатак се може знатно поједноставити и убрзати његово рјешење. То се може учинити једноставно копирањем формуле на опсег испод. Због чињенице да су референци у Екцелу подразумевано релативни, када се копирају, вредности се померају, што је оно што нам је потребно. Сама процедура копирања се може извршити помоћу маркера за пуњење.

   Дакле, ставите курсор у доњу десну област елемента са формулом. Након тога, курсор треба трансформисати у маркер пуњења у облику црног крста. Прикачимо леви тастер миша и превуцимо курсор на саму дну колоне.

5. Сви подаци из сличног ступца на Листу 2 увучени су у таблу на листу 1 . Ако промените податке на листу 2, они ће се аутоматски променити на првом.

Метод 2: Коришћење везе ИНДЕКС-МАП

Али шта ако листа запослених у низу таблица није у истом редоследу? У овом случају, као што је раније поменуто, једна опција је успостављање везе између сваке ћелије које треба повезати ручно. Али ово је погодно само за мале столове. За велике опсеге ова опција ће у најбољем случају трајати веома дуго за имплементацију, ау најгорем случају - у пракси то неће бити могуће. Али овај проблем можете решити уз помоћ гомиле оператора ИНДЕКС - ПОСИТИОН . Да видимо како се то може учинити повезивањем података у табеларним опсезима описаним у претходном начину.

1. Изаберите први елемент колоне "Бет" . Идите на чаробњака функције кликом на икону "Инсерт фунцтион" .
2. У чаробњаку функције у групи "Референце и низови" пронађемо и одаберемо име "ИНДЕКС" .
3. Овај оператор има два облика: формулар за рад са низовима и референцом. У нашем случају, прва опција је неопходна, тако да се у прозору за одабир наредне форме који се отвори, изаберите и кликните на дугме "ОК" .
4. Покренут је аргументни аргумент ИНДЕКС-а . Задатак ове функције је излазна вриједност која се налази у одабраном опсегу у линији са наведеним бројем. Општа формула оператора ИНДЕКС- а је следећа:

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

   "Арраи" је аргумент који садржи адресу опсега из које ћемо добити податке по броју наведеног низа.

   "Линијски број" је аргумент који је број ове линије. Важно је знати да број линије треба одредити не релативно у односу на цео документ, већ само у односу на изабрани низ.

   "Број колоне" је аргумент који је необавезан. Да бисмо решили наш проблем конкретно, нећемо га користити, па стога није неопходно посебно описати његову суштину.

   Поставили смо курсор у пољу "Арраи" . После тога идите на Лист 2 и држите леви тастер миша, одаберите цео садржај колоне "Бет" .

5. Након што се координате приказују у прозору оператера, поставите курсор у поље "Број линије" . Ову аргументацију излажемо уз помоћ оператора ЛИСТ . Због тога кликните на троугао који се налази лево од линије функција. Приказује се листа недавно коришћених оператора. Ако нађете име "СЕАРЦХ" међу њима, можете га кликнути. Иначе, кликните на последњу ставку на листи - "Друге функције ..." .
6. Почиње стандардни прозор Чаробњака . Пролазимо у исту групу "Референце и низови". У овом тренутку на листи изаберите ставку "СЕАРЦХ" . Кликните на дугме "ОК" .
7. Активирање прозора ОПЕРАТОР- а аргумената је активирано . Наведена функција је намијењена за приказ броја вриједности у одређеном низу по имену. Захваљујући овој функцији израчунавамо број линије одређене вредности за ИНДЕКС функцију. Синтакса МАТЦХ-а је следећа:

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

   "Захтевана вредност" је аргумент који садржи име или адресу ћелије спољашњег опсега у којем се налази. То је положај овог имена у циљном опсегу и треба га израчунати. У нашем случају, први аргумент ће бити упућивање на ћелије на листу 1 , у којима се налазе имена запослених.

   "Скенирани низ" је аргумент који се односи на низ у којем се одређена вриједност претражује како би се одредила његова позиција. Код нас ова улога извршава адресу колоне " Име" на листу 2 .

   "Матцхинг Типе" је аргумент који је необавезан, али за разлику од претходне изјаве, овај опциони аргумент ће бити потребан. Он показује како ће оператор одговарати вриједности претраживања у низу. Овај аргумент може имати једну од три вриједности: -1 ; 0 ; 1 . За неуређене низове изаберите опцију "0" . Ова опција је погодна за наш случај.

   Дакле, почнимо да попуњавамо поља прозора аргумената. Поставили смо курсор у поље "Сеарцх валуе" , кликните на прву ћелију колоне "Наме" на листу 1 .

8. Након што смо приказали координате, поставили смо курсор у поље "Скенирани низ" и идемо на етикету "Лист 2" која се налази на дну Екцел прозора изнад статусне линије. Залепите лево дугме миша и изаберите помоћу курсора све ћелије у ступцу "Име" .
9. Након што су њихове координате приказане у пољу "Скенирани низ" , идите у поље "Матцхинг типе" и подесите број "0" са тастатуре на њему. Након тога, поново се враћамо у поље "Прегледани низ" . Ствар је у томе да ћемо извршити копирање формуле, као што смо то урадили и на претходном начину. Адресице ће се померити, али морамо поправити координате поља које скенирамо. Не би требало да се помера. Изаберите координате помоћу курсора и притисните функцијски тастер Ф4 . Као што видите, знак за долар појавио се пре координата, што значи да референца од релативног до апсолутног. Затим кликните на дугме "ОК" .
10. Резултат је приказан у првој ћелији колоне "Бет" . Али пре него што направимо копију, морамо поправити још једну област, односно први аргумент функције ИНДЕКС . Да бисте то урадили, изаберите елемент колоне који садржи формулу и пређите на ред формуле. Изаберите први аргумент оператора ИНДЕКС- а ( Б2: Б7 ) и кликните на дугме Ф4 . Као што видите, знак долара се појавио у близини изабраних координата. Кликнемо на тастер Ентер . Уопштено говорећи, формула је узела следећи облик:

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

11. Сада можете копирати помоћу ручке за пуњење. Ми то зовемо на исти начин на који смо причали раније, и продужимо је до краја распона стола.
12. Као што видимо, упркос чињеници да редослед редова за двије повезане табеле не поклапа, ипак, све вриједности су пооштрене према презименима запослених. Ово је постигнуто коришћењем комбинације оператора ИНДЕКС - ПОСИТИОН .

Прочитајте такође:
Функција ИНДЕКС у Екцелу
Функција ЕКСПЛОРАТИОН у програму Екцел

Метод 3: Извођење математичких операција на сродним подацима

Директно повезивање података је такође добро јер омогућава не само приказивање вредности које су приказане у другим таблицама у једној од таблица, већ и за обављање разних математичких операција с њима (додавање, подела, одузимање, множење итд.).

Да видимо како се то ради у пракси. Хајде да то учинимо тако да на листу 3 општи подаци о платама на предузећу биће приказани без разлагања од стране запослених. За то ће се стопе особља извући из листе 2 , додати (користећи СУМ функцију) и помножити коефицијентом користећи формулу.

1. Изаберите ћелију на којој ће се приказати резултат израчунавања зарада на листу 3 . Кликните на дугме "Убаци функцију" .
2. Требали бисте покренути прозор Чаробњака . Прелазимо у групу "Математички" и изаберите назив "СУММ" . Затим кликните на дугме "ОК" .
3. Функција СУМ се помера у прозор аргумента, који се користи за израчунавање суме одабраних бројева. Има следећу синтаксу:

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

   Поља у прозору одговарају аргументима наведене функције. Иако њихов број може да достигне 255 комада, али у нашу сврху ће бити довољно само један. Поставили смо курсор у поље "Нумбер1" . Кликните на етикету "Лист 2" изнад статусне линије.

4. Након што пређемо на жељени део књиге, изаберите колону коју треба сабрати. Ми то радимо с курсором, држећи леви тастер миша. Као што видите, координате изабране области одмах се приказују у пољу прозора аргумената. Затим кликните на дугме "ОК" .
5. Након тога, аутоматски прелазимо на листу 1 . Као што видите, укупан износ плате запосленог је већ приказан у одговарајућем елементу.
6. Али то није све. Као што се сећам, плата се израчунава тако што се стопа помножи са коефицијентом. Због тога поново одаберемо ћелију у којој се налази збир. После тога идите на формулу. Додајемо симбол множења ( * ) у формулу у њему, а затим кликните на елемент у којем се налази индекс коефицијента. Да бисте извршили обрачун, кликните на тастер Ентер на тастатури. Као што видите, програм је израчунао укупну плату за предузеће.
7. Повратак на лист 2 и промену стопе било ког запосленог.
8. После тога, враћамо се на страницу са укупним износом. Као што видите, због промјена у повезани табели, резултат укупне зараде је аутоматски прерачунат.

Метод 4: посебан уметак

Такође можете повезати таблице са табелама у Екцел користећи посебан инсерт.

1. Изаберите вредности које желите "затегнути" у другој табели. У нашем случају, ово је опсег колоне "Бет" на листу 2 . Кликните на изабрани фрагмент са десним тастером миша. На отвореној листи изаберите ставку "Копирај" . Алтернативна комбинација је Цтрл + Ц. После тога пређите на лист 1 .
2. Прелазак на жељено подручје књиге изаберите ћелије у којима желите извући вриједности. У нашем случају, ово је колона "Бет" . Кликните на изабрани фрагмент са десним тастером миша. У контекстуалном менију у пољу за ставке "Уметни параметри" кликните на икону "Инсерт Линк" .

   Постоји и алтернатива. Иначе, то је једино за старије верзије Екцел-а. У контекстном менију, ми надмашимо курсор преко ставке "Специал Пасте" . У додатном менију који се отвори, изаберите ставку са истим именом.

3. Након тога отвара се прозор специјалног уметања. Кликните на дугме "Инсерт Линк" у доњем левом углу ћелије.
4. Без обзира на опцију коју изаберете, вриједности из једне таблице ће бити убачене у другу. Ако промените податке у извору, аутоматски ће се променити у уметнутом опсегу.

Лекција: Посебан уметак у Екцелу

Метод 5: однос између табела у неколико књига

Осим тога, можете организовати везу између подручја таблице у различитим књигама. Користи се посебан алат за уметање. Акције ће бити потпуно исте као и оне које смо видели у претходном начину, осим што вам неће требати навигирати између секција исте књиге, већ између датотека. Наравно, све сродне књиге требају бити отворене истовремено.

1. Изаберите опсег података које желите пренети у другу књигу. Кликните на њега са десним тастером миша и изаберите ставку "Копирај" у отвореном менију.
2. Затим прелазимо на књигу у којој ће ти подаци бити унети. Изаберите жељени опсег. Десни клик. У контекстном менију у групи "Опције постављања" изаберите ставку "Пасте Линк" .
3. Након тога, вредности ће бити убачене. Ако промените податке у изворној књизи, табеларни низ из радне свеске ће их аутоматски подићи. И није неопходно да обе књиге буду отворене за ово. Довољно је отворити само једну радну свеску и аутоматски ће повући податке из затвореног повезаног документа ако је претходно измијењен.

Али треба напоменути да ће у овом случају уметање бити направљено у облику непроменљивог поља. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

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

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

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

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

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

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

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

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

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

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

• ← Методе за упоређивање табела у програму Мицрософт Екцел
• Квадрат у Мицрософт Екцелу →