Кога извршувате одредени задачи во Excel, понекогаш мора да се справите со неколку табели, кои исто така се поврзани. Односно, податоците од една табела се влеваат во друга и кога тие се менуваат, вредностите се пресметуваат во сите релевантни табели.
Поврзаните табели се многу погодни за употреба за обработка на голема количина на информации. Сите информации да ги поставите во една табела, покрај тоа, ако не се хомогени, не е многу погодно. Тешко е да се работи со такви предмети и да се бараат нив. Покажаниот проблем е дизајниран да се елиминира со поврзани табели, информации помеѓу кои се дистрибуираат, но во исто време се меѓусебно поврзани. Опсегот на врски на табели може да се наоѓа не само во еден лист или единствена книга, туку може да се наоѓа и во посебни книги (датотеки). Последните две опции најчесто се користат во пракса, бидејќи целта на оваа технологија е да се оддалечи од акумулацијата на податоците, а собирањето на истите на една страница не го решава фундаментално проблемот. Ајде да научиме како да создадеме и како да работиме со овој вид управување со податоци.
Креирање на поврзани табели
Како прво, да се осврнеме на прашањето какви методи постои можност да се создаде врска помеѓу различни табели.
Метод 1: директно поврзувајте табели со формула
Најлесен начин за врзување на податоците е да се користат формули што упатуваат на други опсези на табели. Се нарекува директно врзување. Овој метод е интуитивен, бидејќи со него поврзувањето се изведува на скоро ист начин како и создавање врски до податоци во една низа на табели.
Да видиме како, на пример, може да се формира врска преку директно врзување. Имаме две табели на два листови. На една табела, платниот список се пресметува со употреба на формулата со множење на стапката на вработените со единствен коефициент за сите.
На вториот лист е опсег на табели, кој содржи список на вработени со нивните плати. Списокот на вработени во двата случаи е претставен во истиот редослед.
Неопходно е да бидете сигурни дека податоците за стапките од вториот лист се влечат во соодветните ќелии од првиот.
- На првиот лист изберете ја првата ќелија во колоната Понуда. Ставаме знак во него "=". Следно, кликнете на кратенката "Лист 2", што се наоѓа на левата страна од Excel интерфејсот над лентата за статус.
- Се преместува во втората област на документот. Ние кликнуваме на првата ќелија во колоната Понуда. Потоа кликнете на копчето Внесете на тастатурата за да внесете податоци во ќелијата во која претходно бил поставен знакот еднакви.
- Потоа, следува автоматска транзиција кон првиот лист. Како што можете да видите, стапката на првиот вработен од втората табела се влече во соодветната ќелија. Со поставување на курсорот на ќелијата што го содржи облогот, гледаме дека вообичаената формула се користи за прикажување на податоци на екранот. Но, пред координатите на ќелијата од каде што излегуваат податоците, постои израз "Лист2!", што го означува името на областа на документот каде се наоѓаат. Општата формула во нашиот случај изгледа вака:
= Лист2! Б2 - Сега треба да ги пренесете податоците за стапките на сите други вработени во претпријатието. Секако, ова може да се направи на ист начин како што ја завршивме задачата за првиот вработен, но со оглед дека и двата списоци на вработени се распоредени по истиот редослед, задачата може значително да се поедностави и да се забрза нејзиното решавање. Ова може да се направи со едноставно копирање на формулата во опсегот подолу. Поради фактот што врските во Excel стануваат релативно по дифолт, кога тие се копираат, вредностите се менуваат, што е она што ни треба. Самата постапка за копирање може да се направи со помош на маркерот за пополнување.
Значи, ставете го курсорот во долниот десен дел на елементот со формулата. После тоа, покажувачот треба да се претвори во маркер за полнење во форма на црн крст. Притиснете го левото копче на глувчето и повлечете го покажувачот до самиот крај на колоната.
- Сите податоци од слична колона на Лист 2 беа влечени во табела на Лист 1. Кога менувате податоци во Лист 2 тие автоматски ќе се променат на првиот.
Метод 2: користење на куп оператори Индекс - ПРЕБАРУВАЕ
Но, што ако списокот на вработени во низата на табели не е во истиот редослед? Во овој случај, како што споменавме порано, една од опциите е да се воспостави врска помеѓу секоја од оние клетки што треба да се поврзат рачно. Но, ова е погодно само за мали маси. За масивни опсези, таквата опција во најдобар случај ќе потрае многу време за спроведување, а во најлош случај, во пракса нема да биде изводливо. Но, овој проблем може да се реши со помош на оператори Индекс - ПРЕБАРУВАЕ. Ајде да видиме како ова може да се направи со поврзување на податоците во табелите на табели за кои се дискутираше во претходниот метод.
- Изберете го првиот елемент на колоната Понуда. Оди до Волшебник на карактеристикисо кликнување на иконата "Вметни функција".
- Во Волшебник за функции во групата Референци и низи најдете и означете го името Индекс.
- Овој оператор има две форми: формулар за работа со низи и референца. Во нашиот случај, потребна е прва опција, затоа, во следниот прозорец за избор на формата што се отвора, изберете ја и кликнете на копчето „ОК“.
- Стартуваше прозорецот за аргументи на операторот Индекс. Задачата на оваа функција е да испорача вредност што е во избраниот опсег во линија со наведениот број. Општа формула на операторот Индекс таков е:
= INDEX (низа; ред_број; [колона_број])Низа - аргумент во кој се наоѓа адресата на опсегот од кој ќе извлечеме информации по бројот на наведената линија.
Реден број - аргументот, кој е бројот на оваа линија. Важно е да се знае дека бројот на линијата не треба да биде наведен во однос на целиот документ, туку само во однос на избраната низа.
Број на колона - аргумент кој не е задолжителен. Ние нема да го искористиме за да го решиме нашиот специфичен проблем и затоа не е неопходно да се опише неговата суштина одделно.
Ставете го курсорот на полето Низа. После тоа, одете до Лист 2 и држејќи го левото копче на глувчето, изберете ја целата содржина на колоната Понуда.
- Откако ќе се прикажат координатите во прозорецот на операторот, ставете го курсорот во полето Реден број. Овој аргумент ќе го испорачаме со користење на операторот ПРЕБАРУВАЕ. Затоа, ние кликнуваме на триаголникот, кој се наоѓа лево од функцијата. Се отвора список на неодамна користени оператори. Ако најдете име меѓу нив „ПРЕБАРУВАЕ“тогаш можете да кликнете на неа. Инаку, кликнете на последната ставка на списокот - "Други карактеристики ...".
- Стандардниот прозорец започнува Волшебници за функции. Ние преминуваме во истата група Референци и низи. Овој пат, одберете ја ставката во списокот „ПРЕБАРУВАЕ“. Кликнете на копчето. „ОК“.
- Активиран е прозорецот за аргументи на операторот ПРЕБАРУВАЕ. Наведената функција е наменета за прикажување на бројот на вредност во одредена низа според неговото име. Благодарение на оваа одлика, ќе ја пресметаме бројот на линијата на одредена вредност за функцијата Индекс. Синтакса ПРЕБАРУВАЕ претставен на следниов начин:
= ПРЕБАРУВАЕ (search_value; lookup_array; [match_type])"Барам вредност" - аргумент во кој се содржат името или адресата на ќелијата од опсегот на трети лица во кои се наоѓа. Позицијата на ова име е во целниот опсег што треба да се пресмета. Во нашиот случај, првиот аргумент ќе биде упатување на ќелии натаму Лист 1каде се наоѓаат имињата на вработените.
Гледаше низа - аргумент што претставува упат на низа во која се бара одредена вредност за да се утврди нејзината позиција. Адресата во колоната „ќе ја игра оваа улога овде“.Име на Лист 2.
Тип на натпревар - аргумент, кој е незадолжителен, но, за разлика од претходната изјава, ќе ни треба овој по избор. Тоа означува како операторот ќе одговара на вредноста на пребарување со низата. Овој аргумент може да има една од трите вредности: -1; 0; 1. За неорганирани низи, изберете "0". Оваа опција е погодна за нашиот случај.
Значи, да започнеме со пополнување на полињата на прозорецот за аргументи. Ставете го курсорот на полето "Барам вредност"кликнете на првата ќелија на колоната "Име" на Лист 1.
- Откако ќе се прикажат координатите, поставете го покажувачот на полето Гледаше низа и кликнете на кратенката "Лист 2", што се наоѓа на дното на прозорецот Ексел над лентата за статус. Држете го левото копче на глувчето и одберете ги сите ќелии во колоната со покажувачот "Име".
- Откако нивните координати се прикажани во полето Гледаше низаоди на терен Тип на натпревар и поставете го бројот од тастатурата "0". После тоа, повторно се враќаме на теренот Гледаше низа. Факт е дека ќе ја копираме формулата, како што правевме во претходниот метод. Addressе се случи смената на адресата, но тука треба да ги поправиме координатите на низата што се гледаат. Не треба да се раселува. Изберете ги координатите со покажувачот и притиснете го функциското копче Ф4. Како што можете да видите, знакот на доларот се појави пред координатите, што значи дека врската се претвори од релативна во апсолутна. Потоа кликнете на копчето „ОК“.
- Резултатот е прикажан во првата ќелија на колоната. Понуда. Но, пред да копираме, треба да поправиме друга област, имено првиот аргумент на функцијата Индекс. За да го направите ова, изберете го елементот на колоната што ја содржи формулата и преместете се на линијата на формулите. Го избираме првиот аргумент на операторот Индекс (Б2: Б7) и кликнете на копчето Ф4. Како што можете да видите, знакот на доларот се појави во близина на избраните координати. Кликнете на копчето Внесете. Општо, формулата ја има следната форма:
= INDEX (лист 2! $ B $ 2: $ B $ 7; ПРЕБАРУВА (Е (лист 1! A4; лист2! $ A $ 2: $ A 7 $; 0)) - Сега можете да копирате со помош на маркерот за пополнување. Ние го нарекуваме на ист начин на кој разговаравме порано и го истегнуваме до крајот на опсегот на табелата.
- Како што можете да видите, и покрај фактот дека редот на редот на двете поврзани табели не одговара, сепак, сите вредности се подигнуваат според имињата на вработените. Ова беше постигнато преку употреба на комбинација на оператори Индекс-ПРЕБАРУВАЕ.
Прочитајте исто така:
ЕКСЕКС функција во Excel
Функција EXCEL во Excel
Метод 3: изврши математички операции со поврзани податоци
Директното врзување на податоците е исто така добро затоа што ви овозможува не само прикажување на вредностите што се прикажани во други опсези на табели во една од табелите, туку исто така изведувајте различни математички операции со нив (додавање, поделба, одземање, множење, итн.).
Ајде да видиме како ова се спроведува во пракса. Ајде да го направиме тоа натаму Лист 3 општите податоци за платите за компанијата ќе бидат прикажани без дефект на вработените. За да го направите ова, ќе се повлечат стапките на вработените Лист 2, сумирани (со користење на функцијата СУМ) и помножете се со коефициент користејќи ја формулата.
- Изберете ја ќелијата каде ќе се прикаже резултатот од пресметката за платен список. Лист 3. Кликнете на копчето. "Вметни функција".
- Прозорецот треба да започне Волшебници за функции. Одете во групата „Математички“ и изберете го името таму СУМ. Следно, кликнете на копчето „ОК“.
- Аргументите на функцијата се преместуваат на прозорецот СУМ, кој е дизајниран да го пресмета збирот на избраните броеви. Ја има следната синтакса:
= СУМ (број 1; број 2; ...)Полињата во прозорецот одговараат на аргументите на одредената функција. Иако нивниот број може да достигне 255, само еден ќе биде доволен за нашата цел. Ставете го курсорот на полето "Број 1". Кликнете на кратенката "Лист 2" над лентата за статус.
- Откако се преселивме во саканиот дел од книгата, изберете ја колоната што треба да се сумира. Ова го правиме со покажувачот додека го држиме левото копче на глувчето. Како што можете да видите, координатите на избраната област веднаш се прикажани во полето на прозорецот за аргументи. Потоа кликнете на копчето „ОК“.
- После тоа, автоматски се префрламе во Лист 1. Како што можете да видите, вкупниот износ на понудите за вработените е веќе прикажан во соодветниот елемент.
- Но, тоа не е сè. Како што се сеќаваме, платата се пресметува со множење на вредноста на стапката за фактор. Затоа, повторно ја избираме ќелијата во која се наоѓа сумираната вредност. После тоа, ние преминуваме до линијата на формули. Додадете во формулата во него знак за множење (*), а потоа кликнете на елементот во кој се наоѓа индикаторот за коефициентот. За извршување на пресметката, кликнете на копчето Внесете на тастатурата. Како што можете да видите, програмата ја пресмета вкупната плата за претпријатието.
- Назад на Лист 2 и промена на стапката на кој било вработен.
- После тоа, повторно се префрламе на страницата со вкупната сума. Како што можете да видите, поради промените во поврзаната табела, резултатот од вкупната плата автоматски се пресмета.
Метод 4: обичај вметнете
Можете исто така да водите низи на табели во Excel со помош на специјален вметнете.
- Ние ги избираме вредностите што ќе треба да се „влечат“ во друга табела. Во нашиот случај, ова е опсегот на колоната Понуда на Лист 2. Ние кликнуваме на избраниот фрагмент со десното копче на глувчето. Во списокот што се отвора, изберете Копирај. Алтернативна кратенка на тастатурата е Ctrl + C. После тоа се префрламе во Лист 1.
- Откако се преселивме во областа на книгата што ни е потребна, ги избираме ќелиите во кои ќе треба да ги подигнеме вредностите. Во нашиот случај, ова е колона Понуда. Ние кликнуваме на избраниот фрагмент со десното копче на глувчето. Во контекстното мени во блокот со алатки Вметнете опции кликнете на иконата Залепи врска.
Исто така постои и алтернатива. Патем, тој е единствен за постарите верзии на Excel. Во контекстното мени, лебди "Специјален инсерт". Во дополнителното мени што се отвора, одберете ја позицијата со истото име.
- После тоа, се отвора специјалниот прозорец за вметнување. Кликнете на копчето Залепи врска во долниот лев агол на ќелијата.
- Кое и да ја изберете опцијата, вредностите од една низа на табели ќе бидат вметнати во друга. Кога менувате податоци во изворот, тие исто така автоматски се менуваат во вметнатиот опсег.
Лекција: Специјално вметнување во Excel
Метод 5: врска помеѓу табели во повеќе книги
Покрај тоа, можете да организирате комуникација помеѓу области во табели во различни книги. Се користи специјална алатка за вметнување. Акциите ќе бидат апсолутно слични на оние што ги разгледавме во претходниот метод, освен тоа што ќе мора да се движите додека правите формули не помеѓу области од истата книга, туку помеѓу датотеки. Секако, сите поврзани книги треба да бидат отворени.
- Изберете го опсегот на податоци што сакате да ги пренесете во друга книга. Кликнете со десното копче на неа и одберете ја позицијата во менито што се отвора. Копирај.
- Потоа се префрламе во книгата во која ќе треба да се вметнат овие податоци. Изберете го саканиот опсег. Десен клик Во контекстното мени во групата Вметнете опции изберете ставка Залепи врска.
- После тоа, вредностите ќе бидат вметнати. Кога се менуваат податоците во работната книга со извори, низата на табелата од работната книга автоматски ќе ги повлече. Покрај тоа, не е неопходно и двете книги да бидат отворени за ова. Доволно е да се отвори само една работна книга, и автоматски ќе се повлечат податоците од затворен поврзан документ ако претходно се направени промени во неа.
Но, треба да се напомене дека во овој случај инсертот ќе се направи како непроменлива низа. Кога се обидувате да промените која било ќелија со вметнатите податоци, се појавува порака која ве известува дека е невозможно да се стори тоа.
Промени во таква низа поврзани со друга книга може да се направат само со кршење на врската.
Јаз меѓу табелите
Понекогаш треба да ја прекинете врската помеѓу опсезите на табели. Причината за ова може да биде или горе опишаниот случај, кога е неопходно да се смени низата вметната од друга книга, или едноставно неподготвеноста на корисникот дека податоците во една табела автоматски се ажурираат од друга.
Метод 1: раскинување на врската помеѓу книгите
Може да ја прекинете врската помеѓу книгите во сите ќелии со извршување на буквално една операција. Во исто време, податоците во ќелиите ќе останат, но тие веќе ќе бидат статички необновливи вредности, кои во никој случај не зависат од другите документи.
- Во книгата, во која се влечат вредностите од другите датотеки, одете на јазичето „Податоци“. Кликнете на иконата "Промени ги комуникациите"сместена на лентата во алатникот Врски. Треба да се напомене дека ако тековната книга не содржи линкови до други датотеки, тогаш ова копче е неактивно.
- Отворен е прозорецот за промена на врските. Ние ја избираме датотеката со која сакаме да ја прекинеме врската од списокот со сродни книги (ако има неколку). Кликнете на копчето Прекини ја врската.
- Се отвора прозорец со информации, во кој има предупредување за последиците од понатамошните активности. Ако сте сигурни што сакате да направите, кликнете на копчето „Раскинување врски“.
- После тоа, сите врски до наведената датотека во тековниот документ ќе бидат заменети со статички вредности.
Метод 2: Вметни вредности
Но, горенаведениот метод е погоден само ако треба целосно да ги раскинете сите врски помеѓу двете книги. Што да сторите ако треба да одделите сродни табели што се во иста датотека? Можете да го направите ова со копирање на податоците и потоа залепување на истото место како вредностите. Патем, на ист начин, можете да ја прекинете врската помеѓу индивидуалните опсези на податоци од различни книги без да ја нарушите општата врска помеѓу датотеките. Ајде да видиме како овој метод работи во пракса.
- Изберете го опсегот во кој сакаме да ја отстраниме врската до друга табела. Ние кликнуваме на него со десното копче на глувчето. Во менито што се отвора, изберете Копирај. Наместо овие активности, можете да напишете алтернативна комбинација на топли клучеви Ctrl + C.
- Понатаму, без да го отстраните изборот од истиот фрагмент, повторно кликнете со десното копче на неа. Овој пат во списокот со активности кликнете на иконата „Вредности“која се наоѓа во групата алатки Вметнете опции.
- После тоа, сите врски во избраниот опсег ќе бидат заменети со статички вредности.
Како што можете да видите, во Excel постојат начини и алатки за поврзување на неколку табели заедно. Во исто време, табеларните податоци можат да бидат на други листови, па дури и во различни книги. Доколку е потребно, оваа врска лесно може да се расипе.
