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