Праца з цыклічнымі спасылкамі ў Excel

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

Выкарыстанне цыклічных спасылак

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

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

Стварэнне цыклічнай спасылкі

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

1. Вылучаем элемент ліста A1 і запісваем у ім наступны выраз:

   = A1

   Далей ціснем на кнопку Enter на клавіятуры.



2. Пасля гэтага з'яўляецца дыялогавае акно папярэджання аб цыклічным выразе. Пстрыкаем ў ім па кнопцы «OK».



3. Такім чынам, мы атрымалі цыклічна аперацыю на лісце, у якой вочка спасылаецца сама на сябе.

Трохі ўскладніць задачу і створым цыклічнае выраз з некалькіх вочак.

1. У любы элемент ліста запісваем лік. Хай гэта будзе вочка A1, а лік 5.



2. У іншую вочка (B1) запісваем выраз:

   = C1



3. У наступны элемент (C1) вырабляем запіс такой формулы:

   = A1



4. Пасля гэтага вяртаемся ў вочка A1, у якой устаноўлена лік 5. спасылаецца ў ёй на элемент B1:

   = B1

   Ціснем на кнопку Enter.



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

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

1. Каб зацыкліліся формулу ў першым радку, вылучаем элемент ліста з колькасцю першага па ліку тавару (B2). Замест статычнага значэння (6) ўпісваем туды формулу, якая будзе лічыць колькасць тавару шляхам дзялення агульнай сумы (D2) на цану (C2):

   = D2 / C2

   Пстрыкаем па кнопцы Enter.



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



3. Скапіюем выраз ва ўсе астатнія вочкі слупка з колькасцю прадукцыі. Для гэтага ўсталёўваны курсор у ніжні правы кут таго элемента, які ўжо ўтрымлівае формулу. Курсор пераўтворыцца ў крыжык, які прынята называць маркерам запаўнення. Зацісканы левую кнопку мышы і цягнем гэты крыжык да канца табліцы ўніз.



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

Пошук цыклічных спасылак

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

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



2. Пры кліку на канкрэтны адрас адбываецца вылучэнне адпаведнай суполкі на аркушы.

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

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

Ўрок: Як знайсці цыклічныя спасылкі ў Excel

Выпраўленне цыклічных спасылак

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

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

1. У нашым выпадку, нягледзячы на ​​тое, што праграма дакладна паказала на адну з вочак цыклу (D6), рэальная памылка крыецца ў іншага вочка. Вылучаем элемент D6, каб даведацца, з якіх вочак ён падцягвае значэнне. Глядзім на выраз у радку формул. Як бачым, значэнне ў гэтым элеменце ліста фармуецца шляхам множання змесціва вочак B6 і C6.



2. Пераходзім да вочка C6. Вылучаем яе і глядзім на радок формул. Як бачым, гэта звычайнае статычнае значэнне (1000), якое не з'яўляецца прадуктам вылічэнні формулы. Таму можна з упэўненасцю сказаць, што ўказаны элемент не ўтрымлівае памылкі, якая выклікае стварэнне цыклічных аперацый.



3. Пераходзім да наступнай вочку (B6). Пасля вылучэння ў радку формул мы бачым, што яна ўтрымлівае вылічае выраз (= D6 / C6), якое падцягвае дадзеныя з іншых элементаў табліцы, у прыватнасці, з вочка D6. Такім чынам, вочка D6 спасылаецца на дадзеныя элемента B6 і наадварот, што выклікае зацыкленасць.

   

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

4. Цяпер нам трэба зразумець, у якой менавіта вочку (B6 або D6) змяшчаецца памылка. Хоць, фармальна гэта нават не памылка, а проста залішняя выкарыстанне спасылак, якое прыводзіць да зацыклення. Падчас працэсу рашэння таго, якую вочка варта адрэдагаваць, трэба ўжываць логіку. Тут няма выразнага алгарытму дзеянняў. У кожным канкрэтным выпадку гэтая логіка будзе свая.

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



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

   Акрамя таго, ці поўнасцю былі выдалены цыклічныя выразы, можна даведацца, скарыстаўшыся інструментам праверкі наяўнасці памылак. Пераходзім ва ўкладку «Формулы» і ціснем ўжо знаёмы нам трохкутнік справа ад кнопкі «Праверка наяўнасці памылак» ў групе інструментаў «Залежнасці формул» . Калі ў запусцім меню пункт «Цыклічныя спасылкі» не будзе актыўны, то, значыць, мы выдалілі ўсе падобныя аб'екты з дакумента. У адваротным выпадку, трэба будзе прымяніць працэдуру выдалення да элементаў, якія знаходзяцца ў спісе, тым жа разглядаюцца раней спосабам.

Дазвол выканання цыклічных аперацый

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

1. Перш за ўсё, перамяшчаемся ва ўкладку «Файл» прыкладання Excel.



2. Далей пстрыкаем па пункце «Параметры», размешчанага ў левай частцы адкрыўся акна.



3. Адбываецца запуск вокны параметраў Эксэля. Нам трэба перайсці ва ўкладку «Формулы».



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

   Каб дазволіць прымяненне цыклічных выразаў, трэба ўсталяваць галачку каля параметру «Уключыць итеративные вылічэнні». Акрамя таго, у гэтым жа блоку можна наладзіць лімітавае лік ітэрацый і адносную хібнасць. Па змаўчанні іх значэнні роўныя 100 і 0,001 адпаведна. У большасці выпадкаў дадзеныя параметры мяняць не трэба, хоць пры неабходнасці або пры жаданні можна ўнесці змены ў названыя поля. Але тут трэба ўлічыць, што занадта вялікая колькасць ітэрацый можа прывесці да сур'ёзнай нагрузцы на праграму і сістэму ў цэлым, асабліва калі вы працуеце з файлам, у якім размешчана шмат цыклічных выразаў.

   Такім чынам, усталёўваем галачку каля параметру «Уключыць итеративные вылічэнні», а затым, каб новыя налады ўступілі ў сілу, ціснем на кнопку «OK», размешчаную ў ніжняй частцы акна параметраў Excel.



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

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

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