ЗВЯЗАНЫЯ ТАБЛІЦЫ Ў EXCEL

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

Звязаныя табліцы вельмі зручна выкарыстоўваць для апрацоўкі вялікага аб'ёму інфармацыі. Размяшчаць ўсю інфармацыю ў адной табліцы, да таго ж, калі яна не аднастайная, не вельмі зручна. З падобнымі аб'ектамі цяжка працаваць і вырабляць па іх пошук. Паказаную праблему як раз закліканы ліквідаваць звязаныя табліцы, інфармацыя паміж якімі размеркавана, але ў той жа час з'яўляецца узаемазлучанай. Звязаныя таблічныя дыяпазоны могуць знаходзіцца не толькі ў межах аднаго ліста або адной кнігі, але і размяшчацца ў асобных кнігах (файлах). Апошнія два варыянты на практыцы выкарыстоўваюць часцей за ўсё, так як мэтай паказанай тэхналогіі з'яўляецца як раз сысці ад навалы дадзеных, а нагрувашчванне іх на адной старонцы прынцыпова праблему не вырашае. Давайце даведаемся, як ствараць і як працаваць з такім выглядам кіравання дадзенымі.

Стварэнне звязаных табліц

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

Спосаб 1: прамое злучэнне табліц формулай

Самы просты спосаб звязвання дадзеных - гэта выкарыстанне формул, у якіх маюцца спасылкі на іншыя таблічныя дыяпазоны. Ён завецца прамым звязваннем. Гэты спосаб інтуітыўна зразумелы, так як пры ім звязванне выконваецца практычна сапраўды гэтак жа, як стварэнне спасылак на дадзеныя ў адным таблічным масіве.

Паглядзім, як на прыкладзе можна ўтварыць сувязь шляхам прамога звязвання. Маем дзве табліцы на двух лістах. На адной табліцы вырабляецца разлік заработнай платы з дапамогай формулы шляхам множання стаўкі работнікаў на адзіны для ўсіх каэфіцыент.

На другім лісце размешчаны таблічны дыяпазон, у якім знаходзіцца пералік супрацоўнікаў з іх акладамі. Спіс супрацоўнікаў у абодвух выпадках прадстаўлены ў адным парадку.

Трэба зрабіць так, каб дадзеныя аб стаўках з другога ліста падцягваліся ў адпаведныя ячэйкі першага.

На першым лісце вылучаем першую вочка слупка "Стаўка". Ставім у ёй знак "=". Далей клікаем па ярлычка "Ліст 2", Які размяшчаецца ў левай частцы інтэрфейсу Excel над радком стану.

Адбываецца перамяшчэння ў другую вобласць дакумента. Пстрыкаем па першай вочку ў Стоўбцах "Стаўка". Затым клікаем па кнопцы Enter на клавіятуры, каб вырабіць ўвод дадзеных у вочка, у якой раней ўсталявалі знак "Роўна".

Затым адбываецца аўтаматычны пераход на першы ліст. Як бачым, у адпаведную вочка падцягваецца велічыня стаўкі першага супрацоўніка з другой табліцы. Усталяваўшы курсор на вочка, якая змяшчае стаўку, бачым, што для высновы дадзеных на экран ужываецца звычайная формула. Але перад каардынатамі ячэйкі, адкуль выводзяцца дадзеныя, варта выраз "Лист2!", Якое паказвае найменне з дакумэнтаў, дзе яны размешчаны. Агульная формула ў нашым выпадку выглядае так:
= Лист2! B2

Зараз трэба перанесці дадзеныя аб стаўках ўсіх астатніх работнікаў прадпрыемства. Вядома, гэта можна зрабіць тым жа шляхам, якім мы выканалі пастаўленую задачу для першага работніка, але ўлічваючы, што абодва спісу супрацоўнікаў размешчаныя ў аднолькавым парадку, задачу можна істотна спрасціць і паскорыць яе рашэнне. Гэта можна зрабіць, проста скапіраваўшы формулу на дыяпазон ніжэй. Дзякуючы таму, што спасылкі ў Excel па змаўчанні з'яўляюцца адноснымі, пры іх капіяванні адбываецца зрух значэнняў, што нам і трэба. Саму працэдуру капіявання можна вырабіць з дапамогай маркера запаўнення.
Такім чынам, ставім курсор у ніжнюю правую вобласць элемента з формулай. Пасля гэтага курсор павінен пераўтварыцца ў маркер запаўнення ў выглядзе чорнага крыжыка. Выконваем заціск левай кнопкі мышы і цягнем курсор да самага нізу слупка.

Усе дадзеныя з аналагічнага слупка на лісце 2 былі падцягнуты ў табліцу на аркушы 1. Пры змене дадзеных на лісце 2 яны аўтаматычна будуць змяняцца і на першым.

Спосаб 2: выкарыстанне звязкі аператараў індэкс - ПОИСКПОЗ

Але што рабіць, калі пералік супрацоўнікаў у таблічных масівах размешчаны не ў аднолькавым парадку? У гэтым выпадку, як гаварылася раней, адным з варыянтаў з'яўляецца ўстаноўка сувязі паміж кожнай з тых вочак, якія варта звязаць, ўручную. Але гэта падыдзе хіба што для невялікіх табліц. Для масіўных дыяпазонаў падобны варыянт у лепшым выпадку адбярэ вельмі шмат часу на рэалізацыю, а ў горшым - на практыцы наогул будзе немагчымы. Але вырашыць дадзеную праблему можна пры дапамозе звязка аператараў індэкс - ПОИСКПОЗ. Паглядзім, як гэта можна ажыццявіць, звязаўшы дадзеныя ў таблічных дыяпазонах, пра якія ішла размова ў папярэднім спосабе.

Вылучаем першы элемент слупка "Стаўка". пераходзім у майстар функцый, Клікнуўшы па піктаграме "Уставіць функцыю".

У майстру функцый ў групе "Спасылкі і масівы" знаходзім і вылучаем найменне "Індэкс".

Дадзены аператар мае дзве формы: форму для працы з масівамі і спасылачныя. У нашым выпадку патрабуецца першы варыянт, таму ў наступным акенцы выбару формы, якое адкрыецца, выбіраем менавіта яго і ціснем на кнопку "OK".

Выкананы запуск акенца аргументаў аператара індэкс. Задача названай функцыі - выснова значэння, які знаходзіцца ў абраным дыяпазоне ў радку з паказаным нумарам. Агульная формула аператара індэкс такая:
= Індэкс (масіў; номер_строки; [номер_столбца])
"Масіў" - аргумент, які змяшчае адрас дыяпазону, з якога мы будзем здабываць інфармацыю па нумары названай радка.
"Нумар радка" - аргумент, які з'яўляецца нумарам гэтай самай радкі. Пры гэтым важна ведаць, што нумар радка варта паказваць не адносна ўсяго дакумента, а толькі адносна вылучанага масіва.
"Нумар слупка" - аргумент, які носіць неабавязковы характар. Для вырашэння канкрэтна нашай задачы мы яго выкарыстоўваць не будзем, а таму апісваць яго сутнасць асобна не трэба.
Ставім курсор у полі "Масіў". Пасля гэтага пераходзім на ліст 2 і, заціснуўшы левую кнопку мышы, вылучаем усё змесціва слупка "Стаўка".

Пасля таго, як каардынаты адлюстраваліся ў акенцы аператара, ставім курсор у полі "Нумар радка". Дадзены аргумент мы будзем выводзіць з дапамогай аператара ПОИСКПОЗ. Таму клікаем па трыкутніку, які размешчаны злева ад радка функцый. Адкрываецца пералік нядаўна выкарыстаных аператараў. Калі вы сярод іх знойдзеце найменне "ПОИСКПОЗ", То можаце клікаць па ім. У адваротным выпадку клічце па самому апошнім пункце пераліку - "Іншыя функцыі ...".

Запускаецца стандартнае акно майстры функцый. Пераходзім у ім у тую ж самую групу "Спасылкі і масівы". На гэты раз у пераліку выбіраем пункт "ПОИСКПОЗ". Выконваем пстрычка па кнопцы "OK".

Вырабляецца актывацыя акенца аргументаў аператара ПОИСКПОЗ. Указаная функцыя прызначана для таго, каб выводзіць нумар значэння ў пэўным масіве па яго назве. Менавіта дзякуючы дадзенай магчымасці мы вылічым нумар радка вызначанага значэння для функцыі індэкс. сінтаксіс ПОИСКПОЗ прадстаўлены так:
= ПОИСКПОЗ (искомое_значение; просматриваемый_массив; [тип_сопоставления])
"Пазовам значэнне" - аргумент, які змяшчае найменне або адрас ячэйкі іншага дыяпазону, у якой яно знаходзіцца. Менавіта пазіцыю дадзенага найменні ў мэтавым дыяпазоне і варта вылічыць. У нашым выпадку ў ролі першага аргументу будуць выступаць спасылкі на ячэйкі на аркушы 1, У якіх размешчаны імёны супрацоўнікаў.
"Праглядаю масіў" - аргумент, які ўяўляе сабой спасылку на масіў, у якім выконваецца пошук названага значэння для вызначэння яго пазіцыі. У нас гэтую ролю будзе выконваць адрас слупка "імя " на лісце 2.
"Тып супастаўлення" - аргумент, які з'яўляецца неабавязковым, але, у адрозненне ад папярэдняга аператара, гэты неабавязковы аргумент нам будзе патрэбны. Ён паказвае на тое, як будзе супастаўляць аператар шуканае значэнне з масівам. Гэты аргумент можа мець адно з трох значэнняў: -1; 0; 1. Для неўпарадкаваных масіваў варта абраць варыянт "0". Менавіта дадзены варыянт падыдзе для нашага выпадку.
Такім чынам, прыступім да запаўнення палёў вокны аргументаў. Ставім курсор у полі "Пазовам значэнне", Клікаем па першай вочку слупка "Імя" на аркушы 1.

Пасля таго, як каардынаты адлюстраваліся, усталёўваем курсор у полі "Праглядаю масіў" і пераходзім па цэтліку "Ліст 2", Які размешчаны ўнізе вокны Excel над радком стану. Зацісканы левую кнопку мышы і вылучаем курсорам усе вочкі слупка "Імя".

Пасля таго, як іх каардынаты адлюстраваліся ў полі "Праглядаю масіў", Пераходзім да поля "Тып супастаўлення" і з клавіятуры усталёўваем там лік "0". Пасля гэтага зноў вяртаемся да поля "Праглядаю масіў". Справа ў тым, што мы будзем выконваць капіраванне формулы, як мы гэта рабілі ў папярэднім спосабе. Будзе адбывацца зрушэнне адрасоў, але вось каардынаты праглядваемыя масіва нам трэба замацаваць. Ён не павінен перамяшчацца. Вылучаем каардынаты курсорам і ціснем на функцыянальную клавішу F4. Як бачым, перад каардынатамі з'явіўся знак даляра, што азначае тое, што спасылка з адноснай ператварылася ў абсалютную. Затым ціснем на кнопку "OK".

Вынік выведзены на экран у першую вочка слупка "Стаўка". Але перад тым, як вырабляць капіраванне, нам трэба замацаваць яшчэ адну вобласць, а менавіта першы аргумент функцыі індэкс. Для гэтага вылучаем элемент калонкі, які змяшчае формулу, і перамяшчаемся ў радок формул. Вылучаем першы аргумент аператара індэкс (B2: B7) І пстрыкаем па кнопцы F4. Як бачым, знак даляра з'явіўся каля выбраных каардынатаў. Пстрыкаем па клавішы Enter. У цэлым формула прыняла наступны выгляд:
= Індэкс (Лист2! $ B $ 2: $ B $ 7; ПОИСКПОЗ (Лист1! A4; Лист2! $ A $ 2: $ A $ 7; 0))

Зараз можна вырабіць капіяванне з дапамогай маркера запаўнення. Выклікаем яго тым жа спосабам, пра які мы казалі раней, і працягваем да канца таблічнага дыяпазону.

Як бачым, нягледзячы на тое, што парадак радкоў ля двух звязаных табліц не супадае, тым не менш, усе значэння падцягваюцца адпаведна прозвішчах работнікаў. Гэтага ўдалося дасягнуць дзякуючы ўжыванню спалучэння аператараў індэкс-ПОИСКПОЗ.

Чытайце таксама:
Функцыя індэкс ў Эксэля
Функцыя ПОИСКПОЗ ў Эксэля

Спосаб 3: выкананне матэматычных аперацый са звязанымі дадзенымі

Прамое звязванне дадзеных добра яшчэ тым, што дазваляе не толькі выводзіць у адну з табліц значэння, якія адлюстроўваюцца ў іншых таблічных дыяпазонах, але і вырабляць з імі розныя матэматычныя аперацыі (складанне, дзяленне, адніманне, множанне і г.д.).

Паглядзім, як гэта ажыццяўляецца на практыцы. Зробім так, што на аркушы 3 будуць выводзіцца агульныя дадзеныя заработнай платы па прадпрыемстве без разбіўкі па супрацоўнікам. Для гэтага стаўкі супрацоўнікаў будуць падцягвацца з ліста 2, Падсумоўвацца (пры дапамозе функцыі сум) І множыцца на каэфіцыент з дапамогай формулы.

Вылучаем вочка, дзе будзе выводзіцца вынік разліку заработнай платы на аркушы 3. Вырабляем клік па кнопцы "Уставіць функцыю".

Варта запуск вокны майстры функцый. Пераходзім у групу "Матэматычныя" і выбіраем там найменне "Сум". Далей ціснем па кнопцы "OK".

Вырабляецца перасоўванне ў акно аргументаў функцыі сум, Якая прызначана для разліку сумы выбраных лікаў. Яна мае ніжэйпаказаныя сінтаксіс:
= Сум (число1; число2; ...)
Поля ў акне адпавядаюць аргументаў названай функцыі. Хоць іх колькасць можа дасягаць 255 штук, але для нашай мэты дастаткова будзе ўсяго аднаго. Ставім курсор у полі "Число1". Клікаем па цэтліку "Ліст 2" над радком стану.

Пасля таго, як мы перамясціліся ў патрэбны раздзел кнігі, вылучаем слупок, які варта прасумаваць. Робім гэта курсорам, заціснуўшы левую кнопку мышы. Як бачым, каардынаты вылучанай вобласці тут жа адлюстроўваюцца ў поле акна аргументаў. Затым пстрыкаем па кнопцы "OK".

Пасля гэтага мы аўтаматычна перамяшчаемся на ліст 1. Як бачым, агульная сума памеру ставак работнікаў ужо адлюстроўваецца ў адпаведным элеменце.

Але гэта яшчэ не ўсё. Як мы памятаем, зарплата вылічаецца шляхам множання велічыні стаўкі на каэфіцыент. Таму зноў вылучаем вочка, у якой знаходзіцца сумаванага велічыня. Пасля гэтага пераходзім да радка формул. Дапісваем да наяўнай у ёй формуле знак множання (*), А затым пстрыкаем па элементу, у якім размяшчаецца паказчык каэфіцыента. Для выканання вылічэнні пстрыкаем па клавішы Enter на клавіятуры. Як бачым, праграма разлічыла агульную заработную плату па прадпрыемстве.

вяртаемся на ліст 2 і змяняны памер стаўкі любога работніка.

Пасля гэтага зноў перамяшчаемся на старонку з агульнай сумай. Як бачым, з-за змен у звязанай табліцы вынік агульнай заработнай платы быў аўтаматычна пералічаны.

Спосаб 4: спецыяльная ўстаўка

Звязаць таблічныя масівы ў Excel можна таксама пры дапамозе спецыяльнай ўстаўкі.

Вылучаем значэння, якія трэба будзе "зацягнуць" у іншую табліцу. У нашым выпадку гэта дыяпазон слупка "Стаўка" на лісце 2. Клікаем па вылучаным фрагменту правай кнопкай мышы. У якое адкрылася спісе выбіраем пункт "Капіяваць". Альтэрнатыўнай камбінацыяй з'яўляецца спалучэнне клавіш Ctrl + C. Пасля гэтага перамяшчаемся на ліст 1.

Перамясціўшыся ў патрэбную нам вобласць кнігі, вылучаем ячэйкі, у якія трэба будзе падцягваць значэння. У нашым выпадку гэта слупок "Стаўка". Пстрыкаем па вылучаным фрагменту правай кнопкай мышы. У кантэкстным меню ў блоку інструментаў "Параметры ўстаўкі" пстрыкаем па піктаграме "Уставіць сувязь".
Існуе таксама альтэрнатыўны варыянт. Ён, дарэчы, з'яўляецца адзіным для больш старых версій Excel. У кантэкстным меню наводзім курсор на пункт "Спецыяльная ўстаўка". У якое адкрылася дадатковым меню выбіраем пазіцыю з аднайменнай назвай.

Пасля гэтага адкрываецца акно спецыяльнай ўстаўкі. Ціснем на кнопку "Уставіць сувязь" у ніжнім левым куце вочка.

Які б варыянт вы не выбралі, значэння з аднаго таблічнага масіва будуць устаўленыя ў іншы. Пры змене дадзеных у зыходніку яны таксама аўтаматычна будуць змяняцца і ва устаўленым дыяпазоне.

Урок: Спецыяльная ўстаўка ў Эксэля

Спосаб 5: сувязь паміж табліцамі ў некалькіх кнігах

Акрамя таго, можна арганізаваць сувязь паміж таблічных абласцямі ў розных кнігах. Пры гэтым выкарыстоўваецца інструмент спецыяльнай ўстаўкі. Дзеянні будуць абсалютна аналагічнымі тым, якія мы разглядалі ў папярэднім спосабе, за выключэннем таго, што вырабляць навігацыю падчас зьменаў формул прыйдзецца не паміж абласцямі адной кнігі, а паміж файламі. Натуральна, што ўсе звязаныя кнігі пры гэтым павінны быць адчыненыя.

Вылучаем дыяпазон дадзеных, які трэба перанесці ў іншую кнігу. Пстрыкаем па ім правай кнопкай мышы і выбіраем ў якое адкрылася меню пазіцыю "Капіяваць".

Затым перамяшчаемся да той кнізе, у якую гэтыя дадзеныя трэба будзе ўставіць. Вылучаем патрэбны дыяпазон. Клікаем правай кнопкай мышы. У кантэкстным меню ў групе "Параметры ўстаўкі" выбіраем пункт "Уставіць сувязь".

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

Але трэба адзначыць, што ў гэтым выпадку ўстаўка будзе праведзена ў выглядзе нязменлівымі масіва. Пры спробе змяніць любую вочка з устаўленымі звесткамі будзе ўсплываць паведамленне, якое інфармуе аб немагчымасці зрабіць гэта.

Змены ў такім масіве, звязаным з другога кнігай, можна вырабіць толькі разарваўшы сувязь.

Разрыў сувязі паміж табліцамі

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

Спосаб 1: разрыў сувязі паміж кнігамі

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

У кнізе, у якой падцягваюцца значэння з іншых файлаў, пераходзім ва ўкладку "Дадзеныя". Пстрыкаем па значку "Змяніць сувязі", Які размешчаны на стужцы ў блоку інструментаў "Падлучэння". Трэба адзначыць, што калі бягучая кніга не ўтрымлівае сувязяў з іншымі файламі, то гэтая кнопка з'яўляецца неактыўнай.

Запускаецца акно змены сувязяў. Выбіраем з спісу звязаных кніг (калі іх некалькі) той файл, з якім хочам разарваць сувязь. Пстрыкаем па кнопцы "Разарваць сувязь".

Адкрываецца інфармацыйнае акенца, у якім знаходзіцца папярэджанне аб наступствах далейшых дзеянняў. Калі вы ўпэўнены ў тым, што збіраецеся рабіць, то цісніце на кнопку "Разарваць сувязі".

Пасля гэтага ўсе спасылкі на паказаны файл у бягучым дакуменце будуць замененыя на статычныя значэння.

Спосаб 2: устаўка значэнняў

Але вышэйпералічаныя спосаб падыходзіць толькі ў тым выпадку, калі трэба цалкам разарваць усе сувязі паміж двума кнігамі. Што ж рабіць, калі патрабуецца раз'яднаць звязаныя табліцы, якія знаходзяцца ў межах аднаго файла? Зрабіць гэта можна, скапіяваўшы дадзеныя, а затым уставіўшы на тое ж месца, як значэння.Дарэчы, гэтым жа спосабам можна праводзіць разрыў сувязі паміж асобнымі дыяпазонамі дадзеных розных кніг без разрыву агульнай сувязі паміж файламі. Паглядзім, як гэты метад працуе на практыцы.

Вылучаем дыяпазон, у якім жадаем выдаліць сувязь з другога табліцай. Пстрыкаем па ім правай кнопкай мышы. У раскрыць меню выбіраем пункт "Капіяваць". Замест паказаных дзеянняў можна набраць альтэрнатыўную камбінацыю гарачых клавіш Ctrl + C.

Далей, не здымаючы вылучэнні з таго ж фрагмента, зноў клікаем па ім правай кнопкай мышы. На гэты раз у спісе дзеянняў пстрыкаем па абразку "Значэнні", Якая размешчана ў групе інструментаў "Параметры ўстаўкі".

Пасля гэтага ўсе спасылкі ў вылучаным дыяпазоне будуць замененыя на статычныя значэння.

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