Работа с свързани таблици в Microsoft Excel

Когато изпълнявате определени задачи в Excel, понякога трябва да се справите с няколко таблици, които също са свързани. Това означава, че данните от една маса се изтеглят в другите и когато се променят, стойностите във всички свързани диапазони на таблиците се преизчисляват.

Свързаните таблици са много удобни за обработка на голямо количество информация. Цялата информация в една таблица освен това, ако тя не е хомогенна, не е много удобна. При подобни обекти е трудно да се работи и да се търси за тях. Този проблем е предназначен само за премахване на свързани таблици, информацията между които е разпределена, но в същото време е взаимосвързана. Свързаните таблици могат да бъдат разположени не само в един лист или в една книга, но също така да бъдат разположени в отделни книги (файлове). Последните две опции се използват на практика най-често, тъй като целта на тази технология е просто да се измъкне от натрупването на данни и да ги натрупате на една страница не решава основно проблема. Нека да разберем как да създаваме и как да работим с този вид управление на данни.

Създаване на свързани таблици

На първо място, нека да разгледаме въпроса по какъв начин е възможно да се създаде връзка между различни таблични диапазони.

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

Най-лесният начин да свържете данните е да използвате формули, които се отнасят до други таблици. Тя се нарича пряко свързване. Този метод е интуитивно разбираем, тъй като с него свързването се извършва почти точно като създаване на препратки към данни в един масив таблица.

Да видим как един пример може да формира връзка чрез пряко обвързване. Имаме две маси на два листа. На една маса разплащателната сметка се изчислява, като се използва формула, като се умножи ставката на служителите с един коефициент за всички.

На втория лист има табличен диапазон, в който има списък на служителите със заплатите им. Списъкът на служителите и в двата случая е представен в един ред.

Необходимо е да се направи така, че данните за скоростите от втория лист да бъдат съставени в съответните клетки на първия.

1. На първия лист изберете първата клетка в колоната "Залог" . Слагаме в него знака "=" . След това кликнете върху етикета "Лист 2" , който се намира от лявата страна на интерфейса на Excel над лентата на състоянието.
2. Тя се придвижва до втората част на документа. Кликваме върху първата клетка в графата "Офериране" . След това кликнете върху бутона Enter на клавиатурата, за да въведете данните в клетката, където е бил настроен равно знакът.
3. След това се извършва автоматичен преход към първия лист. Както можете да видите, стойността на процента на първия служител от втората таблица се изтегля в съответната клетка. Задаването на курсора върху клетката, съдържаща залога, показва, че обичайната формула се използва за показване на данните на екрана. Но преди координатите на клетката, от която се извеждат данните, изразът "Sheet2!" Stands , който показва името на областта на документа, където се намират. Общата формула в нашия случай изглежда така:

   =Лист2!B2

4. Сега трябва да прехвърлите данните за ставките на всички останали служители на предприятието. Разбира се, това може да стане по същия начин, по който завършихме задачата за първия служител, но като се има предвид, че и двата списъка на служителите са разположени в една и съща последователност, задачата може да бъде значително опростена и ускорена. Това може да бъде направено просто чрез копиране на формулата в диапазона по-долу. Поради факта, че препратките в Excel по подразбиране са относителни, когато се копират, стойностите се преместват, което ни е необходимо. Самата процедура на копиране може да се извърши с помощта на маркера за попълване.

   Така че, поставете курсора в долната дясна част на елемента с формулата. След това курсорът трябва да се трансформира в маркер за попълване под формата на черен кръст. Захващаме левия бутон на мишката и плъзгаме курсора до най-долната част на колоната.

5. Всички данни от подобна колона на лист 2 бяха изтеглени в таблицата в лист 1 . Ако промените данните в Лист 2, те автоматично ще се променят в първия.

Метод 2: Използване на връзката INDEX-MAP

Ами ако списъкът на служителите в табличните маси не е в същия ред? В този случай, както беше споменато по-рано, една опция е да се установи връзка между всяка клетка, която да бъде свързана ръчно. Но това е подходящо само за малки маси. За масивните диапазони тази опция в най-добрия случай ще отнеме много време за изпълнение, а в най-лошия случай - на практика няма да е възможно изобщо. Но можете да разрешите този проблем с помощта на група оператори INDEX - POSITION . Да видим как може да се направи това, като се свържат данните в табличните диапазони, обсъдени в предишния метод.

1. Изберете първия елемент от графата "Залози" . Отидете в съветника за функции, като кликнете върху иконата "Вмъкване на функция" .
2. В помощника за функции в групата "Референции и масиви" откриваме и избираме името "INDEX" .
3. Този оператор има две форми: формуляр за работа с масиви и справка. В нашия случай е необходима първата опция, така че в следващия прозорец за избор на формуляра, който се отваря, изберете го и кликнете върху бутона "OK" .
4. Прозорецът на аргументите на INDEX е стартиран. Задачата на тази функция е да изведе стойност, която е в избрания диапазон, в линията със зададения номер. Общата формула на оператора INDEX е, както следва:

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

   "Array" е аргумент, съдържащ адреса на диапазона, от който ще извлечем информацията по номера на посочения низ.

   "Номер на линия" е аргумент, който е номерът на този ред. Важно е да знаете, че номерът на линията трябва да бъде посочен не по отношение на целия документ, а само по отношение на избрания масив.

   "Колона номер" е аргумент, който е по избор. За да решим проблема си конкретно, няма да го използваме и затова не е необходимо да описваме неговата същност поотделно.

   Поставяме курсора в полето "Array" . След това отидете на Лист 2 и, като държите левия бутон на мишката, изберете цялото съдържание на колоната "Залог" .

5. След като координатите се показват в прозореца на оператора, поставете курсора в полето "Номер на линия" . Ще изведем този аргумент с помощта на оператора LIST . Затова кликнете върху триъгълника, който се намира отляво на функционалната линия. Появява се списък с наскоро използвани оператори. Ако намерите името "SEARCH" сред тях, можете да кликнете върху него. В противен случай кликнете върху последния елемент в списъка - "Други функции ..." .
6. Стартира се стандартният прозорец на съветника . Прехвърляме го в същата група "Позовавания и масиви". Понастоящем в списъка изберете "ТЪРСЕНЕ" . Кликнете върху бутона "OK" .
7. Активирането на прозореца OPERATOR на аргументите е активирано . Посочената функция има за цел да покаже номера на стойност в определен масив по нейното име. Благодарение на тази функция изчисляваме номера на линия на конкретна стойност за функцията INDEX . Синтаксисът на MATCH е както следва:

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

   "Заявка стойност" е аргумент, съдържащ името или адреса на клетката от външния диапазон, в който се намира. Това е позицията на това име в целевия диапазон и трябва да бъде изчислена. В нашия случай първият аргумент ще бъде позоваването на клетките в Лист 1 , в които се намират имената на служителите.

   "Сканираният масив" е аргумент, който е препратка към масив, в който се търси зададената стойност, за да се определи нейната позиция. При нас тази роля ще изпълни адреса на колона " Име" в Лист 2 .

   "Съвпадащ тип" е аргумент, който е по избор, но за разлика от предходното изложение, този незадължителен аргумент ще бъде необходим. Показва как операторът ще съответства на стойността за търсене на масива. Този аргумент може да има една от следните три стойности: -1 ; 0 ; 1 . За неорганизирани маси, изберете опция "0" . Тази опция е подходяща за нашия случай.

   Така че, нека започнем да попълваме полетата на прозореца с аргументи. Поставяме курсора в полето "Стойност за търсене" , кликнете върху първата клетка в колоната "Име" в Лист 1 .

8. След като са показани координатите, ние сме задали курсора в полето "Scanned array" и отидете на етикета "Sheet 2" , който се намира в долната част на прозореца на Excel над линията на състоянието. Задръжте левия бутон на мишката и изберете с курсора всички клетки в колоната "Име" .
9. След като координатите им бяха показани в полето "Сканиран масив" , отидете в полето "Тип съвпадение" и задайте числото "0" от клавиатурата там. След това отново се връщаме в полето "Прегледан масив" . Въпросът е, че ще извършим копирането на формулата, както направихме в предишния метод. Адресите ще се смени, но трябва да определим координатите на масива, който сканираме. Не бива да се премества. Изберете координатите с курсора и натиснете функционалния клавиш F4 . Както можете да видите, знакът на долара се появи преди координатите, което означава, че референцията от относителната към абсолютната. След това кликнете върху бутона "OK" .
10. Резултатът се показва в първата клетка на колоната "Залог" . Но преди да направим копие, трябва да поправим още една област, а именно първия аргумент на функцията INDEX . За да направите това, изберете елемента на колоната, който съдържа формулата, и преместете до реда на формулата. Изберете първия аргумент на оператора INDEX ( B2: B7 ) и кликнете върху бутона F4 . Както можете да видите, знакът на долара се появи близо до избраните координати. Кликваме върху клавиша Enter . По принцип формулата е взела следната форма:

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

11. Сега можете да копирате с помощта на дръжката за пълнене. Ние го наричаме по същия начин, по който говорихме по-рано, и го разширяваме до края на масата.
12. Както виждаме, независимо от факта, че редът на редовете за двете свързани таблици не съвпада, въпреки това всички стойности се затягат според фамилните имена на служителите. Това бе постигнато чрез използването на комбинация от оператори INDEX - POSITION .

Прочетете още:
Функция INDEX в Excel
Функцията EXPLORATION в Excel

Метод 3: Извършване на математически операции върху свързани данни

Прякото свързване на данни също е добро, тъй като позволява не само да се показват стойности в една от таблиците, които се показват в други таблични диапазони, но и да се извършват различни математически операции с тях (добавяне, разделяне, изваждане, умножение и т.н.).

Нека видим как това се прави на практика. Нека да направим така, че на лист 3 общите данни за заплатите на предприятието да бъдат показани без разбивка на служителите. За тази цел лихвените проценти на персонала ще бъдат изтеглени от Лист 2 , добавени (използвайки функцията SUM ) и умножени по коефициента, използвайки формулата.

1. Изберете клетката, в която се получава изчислението на заплатите в Лист 3 . Кликнете върху бутона "Вмъкване на функция" .
2. Трябва да стартирате прозореца на помощника . Преминаваме в групата "Математически" и избираме името "SUMM" там . След това кликнете върху бутона "OK" .
3. Функцията SUM се премества в прозореца на аргументите, който се използва за изчисляване на сумата на избраните номера. Той има следния синтаксис:

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

   Полетата в прозореца съответстват на аргументите на зададената функция. Макар че техният брой може да достигне 255 броя, но за целта ни ще бъде достатъчно само един. Поставяме курсора в полето "Номер1" . Кликнете върху етикета "Лист 2" над линията на състоянието.

4. След като се преместихме в желаната част на книгата, изберете колоната, която трябва да се обобщи. Правим това с курсора, като държим левия бутон на мишката. Както можете да видите, координатите на избраната област се показват незабавно в полето на прозореца с аргументи. След това кликнете върху бутона "OK" .
5. След това автоматично преминаваме към Лист 1 . Както можете да видите, общата сума на заплатата на служителите вече е показана в съответния елемент.
6. Но това не е всичко. Както си спомняме, заплатата се изчислява чрез умножаване на коефициента. Следователно отново избираме клетката, в която се намира сумирането. След това отидете в лентата за формули. Добавяме символа за умножение ( * ) към формулата в него, след което кликнете върху елемента, в който се намира коефициентът. За да извършите изчислението, кликнете върху клавиша Enter на клавиатурата. Както можете да видите, програмата изчислява общата заплата за предприятието.
7. Върнете се в Лист 2 и променете процента на всеки служител.
8. След това се връщаме обратно към страницата с общата сума. Както можете да видите, поради промените в свързаната таблица, резултатът от общата заплата бе автоматично преизчислен.

Метод 4: специална вложка

Също така можете да свържете табличните решетки към Excel, като използвате специална вложка.

1. Изберете стойностите, които искате да "затегнат" в друга таблица. В нашия случай това е обхвата на колоната "Залог" в Лист 2 . Кликнете върху избрания фрагмент с десния бутон на мишката. В отворения списък изберете елемента "Копирай" . Алтернативна комбинация е Ctrl + C. След това преминете към формуляр 1 .
2. Преместване в желаната област на книгата, изберете клетките, в които искате да издърпате стойностите. В нашия случай това е колоната "Залог" . Кликнете върху избрания фрагмент с десния бутон на мишката. В контекстното меню в полето " Инструменти за вмъкване на параметри" кликнете върху иконата "Вмъкване на връзка" .

   Съществува и алтернатива. Между другото, той е единственият за старите версии на Excel. В контекстното меню преместваме курсора над елемента "Специална паста" . В допълнителното меню, което се отваря, изберете елемента със същото име.

3. След това се отваря специалният прозорец за вмъкване. Кликнете върху бутона "Вмъкване на връзка" в долния ляв ъгъл на клетката.
4. Независимо от опцията, която избирате, стойностите от един масив от таблици ще бъдат въведени в другата. Ако промените данните в източника, те ще се променят автоматично и в обхвата, в който сте добавили.

Урокът: Специална вложка в Excel

Метод 5: връзката между таблиците в няколко книги

Освен това можете да организирате връзка между табличните области в различни книги. Използва се специален инструмент за поставяне. Действията ще бъдат точно същите като тези, които видяхме в предишния метод, с изключение на това, че няма да се налага да се движите между секциите от една и съща книга, а между файловете. Естествено всички свързани книги трябва да бъдат отворени едновременно.

1. Изберете диапазона от данни, които искате да прехвърлите в друга книга. Кликнете върху него с десния бутон на мишката и изберете опцията "Копиране" в отвореното меню.
2. След това се преместваме в книгата, в която ще трябва да бъдат въведени тези данни. Изберете желания диапазон. Щракнете с десния бутон на мишката. В контекстното меню в групата "Опции за вмъкване" изберете елемента "Поставяне на връзка" .
3. След това стойностите ще бъдат добавени. Ако промените данните в изходната книга, масивът от таблицата от работната книга автоматично ще ги издърпа. И не е необходимо и двете книги да са отворени за това. Достатъчно е да отворите само една работна книга и тя автоматично ще изтегли данните от затворения свързан документ, ако е била променена преди това.

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

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

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

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

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

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

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

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

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

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

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

• ← Методи за сравняване на таблици в Microsoft Excel
• Квадратът в Microsoft Excel →