При извършване на определени задачи в Excel понякога трябва да се справят с няколко таблици, които също са свързани помежду си. Това означава, че данните от една таблица са затегнати към другите и стойностите във всички свързани таблици се преизчисляват, когато се променят.
Свързаните таблици са много удобни за използване, за да се справят с голямо количество информация. Поставете цялата информация в една таблица, освен това, ако не е хомогенна, не е много удобна. Трудно е да се работи с такива обекти и да ги търси. Посоченият проблем е предназначен просто да елиминира свързаните таблици, информацията между която е разпределена, но в същото време е взаимосвързана. Свързаните таблици могат да бъдат не само в един лист или една книга, но и да бъдат разположени в отделни книги (файлове). Последните две опции на практика се използват най-често, тъй като целта на тази технология е само за да се измъкне от натрупването на данни и тяхното кимване на една страница не решава фундаментално. Нека да научим как да създаваме и как да работим с такъв вид управление на данните.
Създаване на свързани таблици
На първо място, нека да се съсредоточим върху въпроса, по какъв начин е възможно да се създаде връзка между различни таблици.Метод 1: Директна формула за таблици
Най-лесният начин за обвързване на данните е използването на формули, в които има препратки към други таблици. Тя се нарича пряко свързване. Този метод е интуитивен, тъй като когато свързва почти просто като създаване на препратки към данни в една масивна маса.
Нека видим как примерът можете да формирате комуникация чрез директно свързване. Имаме две маси на две листа. На една и съща таблица, заплатата се изчислява, използвайки формулата чрез умножаване на лихвените проценти на служителите за един коефициент.
На втория лист има табличен диапазон, в който има списък на служителите с тяхната заплата. Списъкът на служителите в двата случая е представен в една поръчка.
Необходимо е да се направят тези данни за залозите от втория лист да се затягат в съответните клетки от първия.
- На първия лист, ние разпределяме първата клетка на колоната "залог". Поставихме в него знака "=". След това кликнете върху етикета "лист 2", който се поставя в лявата част на Excel интерфейса върху лентата на състоянието.
- Има движение във втората област на документа. Кликнете върху първата клетка в колоната "Залог". След това кликнете върху бутона ENTER на клавиатурата, за да въведете данните в клетката, в която преди това е монтиран "равен" знак.
- След това има автоматичен преход към първия лист. Както виждаме, стойността на първия служител от втората маса се изтегля в съответната клетка. Чрез инсталиране на курсора на клетка, съдържаща залог, виждаме, че обичайната формула се използва за показване на данни на екрана. Но пред координатите на клетката, откъдето се извеждат данните, има израз "List2!", Който показва името на зоната на документа, в която се намират. Общата формула в нашия случай изглежда така:
= List2! B2
- Сега трябва да прехвърлите данни за цените на всички останали служители на предприятието. Разбира се, това може да се направи по същия начин, по който изпълнихме задачата за първия служител, но предвид, че и двамата служители са разположени в същия ред, задачата може да бъде значително опростена и ускорена от нейното решение. Това може да стане просто чрез копиране на формулата към диапазона по-долу. Поради факта, че препратките към Excel са роднини, когато се копират своите ценности, преместването на стойностите се изменят, което ни е необходимо. Самата процедура за копиране може да бъде направена с помощта на маркер за пълнене.
Така че, поставяме курсора в долната дясна област на елемента с формулата. След това курсорът трябва да се обърне към пълнещия маркер под формата на черен кръст. Извършваме скобата на левия бутон на мишката и издърпайте курсора към номера на колоната.
- Всички данни от подобна колона на лист 2 бяха извадени в таблица на лист 1. Когато данните се променят на лист 2, те автоматично ще се променят на първия.
Метод 2: Използване на разбиването на индекса на операторите - Търсене
Но какво да правите, ако списъкът на служителите в масивите не се намира в същия ред? В този случай, както е посочено по-рано, една от опциите е да се инсталира връзката между всяка от тези клетки, които трябва да бъдат свързани ръчно. Но тя е подходяща, с изключение на малките маси. За масивни диапазони тази опция най-добре ще отнеме много време за изпълнението и най-лошото - на практика това обикновено ще бъде нереалистично. Но този проблем може да бъде решен с помощта на индекс на операторите - търсене. Нека да видим как може да се направи чрез обработка на данните в таблиците, за които разговорът е бил в предишния метод.
- Ние подчертаваме първия елемент от колоната "залог". Отидете в съветника за функции, като кликнете върху иконата "Insert Function".
- В съветника на функциите в групата "Връзки и масиви" откриваме и разпределяме името "Индекс".
- Този оператор има две форми: форма за работа с масиви и справка. В нашия случай, първата опция е необходима, така че в следващия прозорец за избор на формуляри, който се отваря, изберете го и кликнете върху бутона "OK".
- Индексът на аргументите на оператора започват да се изпълняват. Задачата на зададената функция е изходната стойност, разположена в избрания диапазон в линията с посочения номер. Индекс на оператора с обща формула:
= Индекс (номер); [number_stolbits]
"Масивът" е аргумент, съдържащ диапазона от обхвата, от който ще извлечем информация по броя на посочения ред.
"Номер на реда" е аргумент, който е номерът на тази линия. Важно е да се знае, че номерът на линията трябва да бъде уточнен не по отношение на целия документ, но само по отношение на разпределената масива.
"Броят на колоната" е аргумент, който не е задължителен. За да разрешите конкретно нашата задача, ние няма да го използваме и затова не е необходимо да го описваме отделно.
Сложихме курсора в полето "Масий". След това отидете на листа 2 и, като държите левия бутон на мишката, изберете цялото съдържание на колоната "Скорост".
- След като координатите се показват в прозореца на оператора, поставяме курсора в полето "номер на ред". Ще оттеглим този аргумент, използвайки оператора за търсене. Затова кликнете върху триъгълник, който се намира вляво от функционалния низ. Отваря се списък с новоприети оператори. Ако откриете името "Търсене на фирма" сред тях, можете да кликнете върху него. В обратния случай кликнете върху най-новата точка на списъка - "Други функции ...".
- Стартира прозорецът Standard Window Wizard. Отидете при нея в същата група "Връзки и масиви". Този път в списъка изберете елемента "Търсене на фирма". Изпълнете бутона "OK".
- Извършва се активиране на аргументите на аргументите на оператора за търсене. Посочената функция е предназначена да изведе номера на стойността в конкретен масив по своето име. Благодарение на тази функция изчисляваме броя на низ от определена стойност за функцията функция. Представен е синтаксисът на борда за търсене:
= Търсеща дъска (Search_name; Преглед __nassive; [type_station])
"Желаният" е аргумент, съдържащ името или адреса на клетката на диапазона на трета страна, в който се намира. Това е позицията на това име в целевия диапазон и трябва да се изчисли. В нашия случай ролята на първия аргумент ще бъде посочена с клетките на лист 1, в който се намират служителите.
"Свободно масив" е аргумент, който е позоваване на масив, който извършва търсенето на определената стойност за определяне на нейната позиция. Тази роля ще изпълним адреса на колоната "Име" на лист 2.
"Тип сравнение" - аргумент, който не е задължителен, но за разлика от предишния оператор ще е необходим този незадължителен аргумент. Това показва как да съответства на оператора е желаната стойност с масив. Този аргумент може да има една от трите стойности: -1; 0; 1. За разрушени масиви изберете опцията "0". Тази опция е подходяща за нашия случай.
Така че, продължете към попълване на полетата на прозореца на аргумента. Поставихме курсора в полето "Mougular value", кликнете върху първата колона "Име" на клетка на лист 1.
- След като се покажат координатите, задайте курсора в полето "Списък с масивни" и отидете на етикета "лист 2", който се намира в долната част на прозореца на Excel над лентата на състоянието. Климент левия бутон на мишката и маркирайте курсора на всички клетки на колоната "Име".
- След като координатите им се показват в полето "Още Massive", отидете на полето "Mapping Type" и задайте номера "0" от клавиатурата. След това отново се връщаме в областта "гледаме през масива". Факт е, че ще извършим копиране на формулата, както направихме в предишния метод. Ще има промяна на адресите, но тук координатите на най-гледания масив трябва да осигурим. Той не трябва да се променя. Ние подчертаваме координатите с курсора и кликнете върху функционалния бутон F4. Както можете да видите, знак за долар се появи пред координатите, което означава, че позоваването от роднина се превърна в абсолютно. След това кликнете върху бутона "OK".
- Резултатът се показва в първата клетка на колоната "залог". Но преди копиране трябва да коригираме друга област, а именно индекса на първия аргумент. За да направите това, изберете елемента на колоната, който съдържа формула и преместете във нишката с формула. Разпределете първия аргумент на индекса на оператора (B2: B7) и кликнете върху бутона F4. Както можете да видите, знакът долар се появи в близост до избраните координати. Кликнете върху клавиша Enter. Като цяло формулата е приела следната форма:
= Индекс (лист2! $ B $ 2: $ B $ 7; Търсеща дъска (лист1! A4; list2! $ 2: $ $ 7; 0))
- Сега можете да копирате с помощта на маркер за пълнене. Ние го наричаме по същия начин, по който сме говорили по-рано и се разтяга до края на табличната гама.
- Както можете да видите, въпреки факта, че редът на струните в две свързани таблици не съвпада, въпреки това всички ценности се затягат според имената на работниците. Това е постигнато благодарение на използването на комбинацията от търсенето на индекси на операторите.
Метод 4: Специална вложка
Масивите за таблици в Excel могат също да използват специално вмъкване.
- Изберете стойностите, които искате да "затегнете" към друга таблица. В нашия случай, това е диапазонът на колоната "залог" на лист 2. Кликнете върху специалния фрагмент с десния бутон на мишката. В списъка, който се отваря, изберете елемента "Копиране". Алтернативна комбинация е комбинацията ctrl + c. След това се придвижваме към листа 1.
- Преместване в областта на книгата, от която се нуждаете, разпределете клетките, в които стойностите ще трябва да бъдат затегнати. В нашия случай, това е колоната "оферта". Кликнете върху специалния фрагмент с десния бутон на мишката. В контекстното меню в лентата с инструменти "Insert parameters" кликнете върху иконата "Вмъкване на комуникация".
Има и алтернатива. Между другото, е единственият за по-старите версии на Excel. В контекстното меню ние носим курсора към елемента "специална вложка". В допълнителното меню, което се отваря, изберете позицията със същото име.
- След това се отваря специален прозорец за вмъкване. Кликнете върху бутона "Вмъкване на комуникация" в долния ляв ъгъл на клетката.
- Каквато и да изберете опцията, стойностите от една масив за маса ще бъдат вкарани в друга. Когато променяте данните в източника, те автоматично ще се променят в вмъкнатата диапазон.
Урок: Специална вложка в Excel
Метод 5: Комуникация между таблици в няколко книги
Освен това можете да организирате връзка между площите за маса в различни книги. Това използва специален инструмент за вмъкване. Действията ще бъдат абсолютно подобни на тези, които разглеждаме в предишния метод, с изключение на това, че навигацията по време на формулите няма да има между областите на една книга, но между файловете. Естествено, всички свързани с тях книги трябва да бъдат отворени.
- Изберете диапазона за данни, който трябва да бъде прехвърлен в друга книга. Кликнете върху него десния бутон на мишката и изберете позицията "Copy" в отвореното меню.
- След това преместваме в книгата, в която трябва да бъдат поставени тези данни. Изберете желания диапазон. Кликнете върху десния бутон на мишката. В контекстното меню в групата "Вмъкване на настройки" изберете елемента "Inser Communication".
- След това ще бъдат поставени стойностите. Когато променяте данните в книгата Източник, масивът за маса от работната книга ще ги затегне автоматично. И всичко не е необходимо да се гарантира, че и двете книги са отворени. Достатъчно е да се отвори само една работна книга и тя автоматично ще включва данни от затворен свързан документ, ако в него имаше предишни промени.
Но трябва да се отбележи, че в този случай вмъкването ще бъде произведено под формата на непроменен масив. Когато се опитвате да промените всяка клетка с поставените данни, ще бъде населено съобщение за невъзможността да се направи това.
Промените в такава масив, свързани с друга книга, могат само да прекъснат връзката.
Заглавие между таблиците
Понякога е необходимо да се прекъсне връзката между таблиците. Причината за това може да бъде описаната по-горе, когато искате да промените масив, вмъкнат от друга книга и просто нежеланието на потребителя, така че данните в една и съща таблица да се актуализират автоматично от другата.Метод 1: Комуникация между книгите
Да се прекъсне връзката между книги във всички клетки, като действително е една операция. В този случай данните в клетките ще останат, но те вече ще бъдат статични не актуализирани стойности, които не зависят от други документи.
- В книгата, в която стойностите от други файлове са затегнати, отидете в раздела Данни. Кликнете върху иконата "Промяна на връзки", която се намира на лентата с инструменти "Свързване". Трябва да се отбележи, че ако текущата книга не съдържа връзки с други файлове, този бутон е неактивен.
- Стартира прозорецът за промяна на връзката. Изберете от списъка с свързани книги (ако има няколко от тях) файла, с която искаме да прекъснем връзката. Кликнете върху бутона "Прекъсване на връзката".
- Отваря се прозорец, който осигурява предупреждение за последиците от по-нататъшни действия. Ако сте сигурни, че ще направите, кликнете върху бутона "Reak Communication".
- След това всички препратки към посочения файл в текущия документ ще бъдат заменени със статични стойности.
Метод 2: Поставяне на стойности
Но горният метод е подходящ само ако трябва напълно да прекъснете всички връзки между двете книги. Какво ще стане, ако трябва да изключите свързаните таблици в същия файл? Можете да направите това чрез копиране на данните и след това поставете същото място като стойности. Между другото, този метод може да бъде разкъсан между отделните диапазони на данни от различни книги, без да се прекъсва обща връзка между файловете. Нека видим как този метод работи на практика.
- Ние подчертаваме диапазона, в който искаме да изтрием комуникация с друга таблица. Кликнете върху него десния бутон на мишката. В отвореното меню изберете "Копиране". Вместо зададените действия можете да наберете алтернативна комбинация от горещи клавиши Ctrl + C.
- След това, без да се изважда избора от същия фрагмент, отново кликнете върху него с десния бутон на мишката. Този път в списъка с действия кликнете върху иконата "Стойност", която се публикува в групата на вмъкване на параметри.
- След това всички препратки в специалния диапазон ще бъдат заменени със статични стойности.
Както можете да видите, Excel има начини и инструменти за свързване на няколко маси помежду си. В същото време табличните данни могат да бъдат върху други листове и дори в различни книги. Ако е необходимо, тази връзка може лесно да бъде счупена.