Обнаружена циклическая ссылка но не получается обнаружить источник ошибки

zpd Пользователь Сообщений: 2 Регистрация: 26.11.2015	Добрый день! в 2 связанных книгах возникла ошибка — «обнаружена циклическая ссылка, не получается отобразить источник ошибки». невозможно найти источник ошибки, все связи удалены. Помогите, пожалуйста, обнаружить ошибку.

Ёк-Мок Пользователь Сообщений: 1779 Регистрация: 16.01.2013	Вариант: перемещаете ВСЕ листы с формулами из всех книг в одну книгу, а там в помощь панель инструментов «Циклические ссылки» Изменено: Ёк-Мок — 03.12.2015 11:37:02 Удивление есть начало познания © Surprise me! И да пребудет с нами сила ВПР.

The_Prist Пользователь Сообщений: 14265 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office

#3 03.12.2015 11:49:38 Цитата zpd написал: Помогите, пожалуйста, обнаружить ошибку. Как Вы себе это представляете? Ни книг, ни формул Ваших мы не видим. Как искать-то? Можно посоветовать только одно: откройте одну книгу. Просмотрите все связи этой книги с другой книгой(можно даже выписать). Главное запомнить на какие листы и ячейки идут ссылки и в каких ячейках эти ссылки записаны. Закройте файл. Далее откройте второй файл и проделайте тоже самое. Теперь посмотрите какие ссылки могут пересекаться. Что-то вроде: ячейка А1 листа1 файла1 ссылается на ячейку В2 листа2 файла2. В то время как ячейка В2 листа2 файла2 ссылается на ячейку А1 листа1 файла1. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

zpd Пользователь Сообщений: 2 Регистрация: 26.11.2015	файлы объемные, поэтому не приложены. все ссылки и связи я постаралась удалить или разорвать. ошибка продолжает возникать. видимые ее результаты отсутствуют.

The_Prist Пользователь Сообщений: 14265 Регистрация: 15.09.2012 Профессиональная разработка приложений для MS Office	Связи они такие…Может есть формулы или имена даже внутри книги, которые ссылаются на связь. Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

exxt Пользователь Сообщений: 19 Регистрация: 01.01.1970	В окне открытой книги просмотрите строку состояния. При циклической ошибке на ней будет отображена надпись «Циклические ссылки: адрес ячейки» (на активном листе) или «Циклические ссылки» (не на текущем листе)

bedvit Пользователь Сообщений: 2527 Регистрация: 02.04.2015 Виталий	Думаю создатель темы нашел то, что искал, поэтому для инфо. В 2010 (в 2007,2013 скорее всего так же) можно проверить ячейку приводящую к циклической ссылке следующим образом: вкладка «формулы»-«проверка наличия ошибок»-«циклические ссылки»-… Неважно на каком листе или в какой книге находится. Изменено: bedvit — 15.12.2015 17:15:09 «Бритва Оккама» или «Принцип Калашникова»?

exxt Пользователь Сообщений: 19 Регистрация: 01.01.1970

#8 15.12.2015 18:51:35 Так вот именно у ТС такой же случай как и у меня был, когда я открываю файл, а он мне выдает ошибку: Цитата «Приложению Microsoft Excel не удается вычислить формулу. В открытой книге обнаружена циклическая ссылка, однако не получается отобразить источник ошибки. Попробуйте изменить последнюю введенную формулу или удалите ее с помощью команды «Отменить». Используя ваш вариант — …»циклические ссылки» вообще не подсвечиваются. И выход из положения только тот, который я указал выше. Изменено: exxt — 15.12.2015 18:55:21

bedvit Пользователь Сообщений: 2527 Регистрация: 02.04.2015 Виталий	Приложите файл-пример, тогда можно будет конкретнее говорить что-то. Вашим «выходом» то же можно пользоваться, если мало листов в файлефайлах и можно прощелкать — посмотреть. Изменено: bedvit — 15.12.2015 19:35:05 «Бритва Оккама» или «Принцип Калашникова»?

Lares Пользователь Сообщений: 1 Регистрация: 20.12.2015

#10 12.01.2022 09:59:16 Цитата написал: В окне открытой книги просмотрите строку состояния. При циклической ошибке на ней будет отображена надпись «Циклические ссылки: адрес ячейки» (на активном листе) или «Циклические ссылки» (не на текущем листе) Прошу прощения за некропост, но годами добивала проблема, описанная в теме, и нигде не мог найти вразумительного ответа как определить причину. А оказалось всё так просто.   Изменено: Lares — 12.01.2022 09:59:35

Александр Истомин Пользователь Сообщений: 1 Регистрация: 12.01.2022	#11 12.01.2022 16:10:55 Огромное спасибо! Проблема решена. Для таких же, как я (кто не знает), строка состояния находится внизу, а надпись «Циклическая ссылка» появляется внизу слева. Если в книге много листов, то стоит, наблюдая за надписью, прокликать все листы, пока напротив надписи «циклическая ссылка» Не появится адрес. Например D55, как это было у меня.

Как удалить или разрешить циклическую ссылку

​Смотрите также​ Итерации выключены, формулы​Мультипликатор​ Сумма, то Цена​Есть варианты убрать​: Связи они такие…Может​ меня в 3.40​ это замкнутый круг.​ правильно вас понимаю.​ знака равенства (=).)​ поставил сумму балансов​ просто как сумма​

​ссылается на ячейку​Решение сходится, что означает​ приложение Excel прекратит​ формулу попытаться вычислить​и щелкните первую​Вы ввели формулу, но​ не изменились, а​: А что такое​ считается так:​ это предупреждение?​​ есть формулы или​​ утра и я​Т.к. логика вычислений​Можно сделать и​ необходимо ознакомиться с​​ за предыдущий месяц.​​ баланса за предыдущий​

​C1​ получение надежного конечного​ вычисление после 100​ саму себя. Если​ ячейку в подменю.​ она не работает.​ предупреждение не вылетает.​

​ УФ? И что​Цена = Сумма​kim​

• ​ имена даже внутри​ слабо понимал что​ Вами не описывается​ без итераций -​ тремя ключевыми понятиями.​ Если это будет​ день и сегодняшних​.​ результата. Это самый​ итераций либо после​ это произойдет, Excel​Проверьте формулу в ячейке.​ Вместо этого появляется​ В чем дело?​

​ вы сделали? Поставили​ / Количество.​: А что, разве​ книги, которые ссылаются​ делаю, но сейчас​ — больше нечего​ см.файлик.​Вычисление — это​ работать так, то​ доходов.​Другими словами, формула в​ желательный исход.​

• ​ того, как изменение​ оставит в ячейке​​ Если вам не​​ сообщение о «циклической​Dophin​​ количество итераций 1?​​Если вам известны​​ включение итераций не​​ на связь.​ понял что логики​

  

• ​ советовать.​Операции в таблице​ процесс расчета по​ в своей таблице​Проблема в том,​ ячейке​​Решение расходится, т. е.​​ всех значений в​

• ​ последнее успешно вычисленное​ удается определить, является​ ссылке». Миллионы людей​: формулы вроде поменял)​ А эти параметры​ только Цена и​ помогает?​

​exxt​​ в таких вычислениях​

• ​DevilByte​ должны идти по​ формулам и последующего​​ я уже все​​ что получается циклическая​C1​ при каждой последующей​

  ​ циклической ссылке с​ значение.​ ли эта ячейка​ сталкиваются с этой​ итерации вылетают если​ будут распространяться только​

• ​ Сумма, то Количество​Мультипликатор​: В окне открытой​ нет, просто делается​: макросы там для​ возрастанию дат, если​ отображения значений результатов​ сделаю как надо.​ ссылка. Комиссия зависит​косвенно ссылается на​​ итерации разность между​​ каждой итерацией составит​​Если есть подозрение, что​​ причиной циклической ссылки,​​ проблемой. Это происходит,​​ дату новую ввести.​

  

Предупреждение о циклической ссылке

​ на этот файл?​ считается так:​: А можно поподробнее​ книги просмотрите строку​​ книга из которой​​ того что бы​ в каком-то месяце​

​ в ячейках, содержащих​Итеративные вычисления не​ от оборота, оборот​ саму себя, чего​ текущим и предыдущим​ меньше 0,001 (в​ циклическая ссылка содержится​ выберите в подменю​ когда формула пытается​У меня почему​ или на другие​Количество = Сумма​ про интерации?​ состояния. При циклической​ макрос будет сам​ собирать данные из​ не было операций​ формулы. Во избежание​ помогут, т.к. сама​ от доходов за​ быть не должно.​ результатами увеличивается.​ зависимости от того,​ в ячейке, которая​

​Циклические ссылки​ посчитать собственную ячейку​ тоеж не сохраняется​ тоже?​ / Цена.​Мультипликатор​

• ​ ошибке на ней​ подставлять значения в​ других книг, листы​

​ — все равно​​ ненужных вычислений Microsoft​ формула комиссии сейчас​ день, доходы за​Примечание:​Решение колеблется между двумя​ какое из этих​ не возвращает значение​следующую ячейку.​ при отключенной функции​ галочка «включить итерации»​

• ​Михаил С.​Практически это работает​: Если включить итерации,​

• ​ будет отображена надпись​ эту таблицу. Спасибо​ я удалил лишние.​ нужно иметь хотя​ Office Excel автоматически​

• ​ подразумевает зависимость от​ день от доходов​Если вы создадите​ значениями, например, после​ условий будет выполнено​

• ​ 0, попробуйте такое​Продолжайте находить и исправлять​

• ​ итеративных вычислений. Вот​ при выходе. это​: вот еще пример​ так.​ то в тех​

Итеративные вычисления

​ «Циклические ссылки: адрес​ за разъяснение​DevilByte​ бы одну «пустую»​ пересчитывает формулы только​ самой себя. И​ от сделок, а​ такую циклическую ссылку,​ первой итерации получается​ раньше). Тем не​ решение:​ циклические ссылки в​ как это выглядит:​ так надо?)​

​ Уменшил, на сколько​Вы покупаете 1,5​ ячейках сразу выдает​ ячейки» (на активном​zpd​: логика такая- все​ запись с датой.​ при изменении влияющих​ я не знаю,​ последние — от​ Excel вернёт «0».​ значение 1, после​ менее, вы можете​Щелкните формулу в строке​ книге, повторяя действия​Формула =D1+D2+D3 не работает,​Мультипликатор​ мог​ литра пива. Вводите​ ошибку #ЗНАЧ!​ листе) или «Циклические​: Добрый день!​ эти данные берутся​Danik​ на формулу ячеек.​ как это исправить.​ комиссий в том​Чтобы найти циклические ссылки​ второй — значение 10,​

1. ​ сами задать предельное​ формулы и нажмите​ 1–3, пока из​ поскольку она расположена​​: Не, ну скажите,​​kim​​ количество = 1,5,​​И потом уже​​ ссылки» (не на​​в 2 связанных​ из дугой книги​: У меня проблема​​ Это является стандартной​​ShAM​​ числе. Как избавиться​​ в документе, на​​ после третьей — снова​​ число итераций и​

   ​ клавишу ВВОД.​ строки состояния не​​ в ячейке D3​ ​ что именно поменяли,​​: Меньше 1 итерации​​ вводите сумму =​​ это никуда не​​ текущем листе)​

2. ​ книгах возникла ошибка​​ и пишутся руками,​​ с циклическими ссылками.​​ процедурой при первом​​: У меня, вроде,​ от этого, как​​ вкладке​​ 1 и т. д.​

3. ​ относительную погрешность.​​Внимание!​​ исчезнет сообщение «Циклические​ и ссылается на​ а то я​ низзя ;)​ 60, цена за​ уходит, так и​bedvit​

4. ​ — «обнаружена циклическая​​ я бы мог​​ Мне нужно автоматизировать​ открытии книги и​ работает.​ обойти такой порядок,​Formulas​К началу страницы​Если вы работаете в​    Во многих случаях​ ссылки».​ саму себя. Чтобы​

​ же не смогу​Dophin​

• ​ 1 литр считается​ остается #ЗНАЧ!​: Думаю создатель темы​ ссылка, не получается​

• ​ сделать макрос для​ расчет платежей, так​ ее редактировании. Однако​AndreTM​ сохранив вычисления?​

• ​(Формулы) кликните по​Формула в ячейке, которая​ Excel 2010 или более​ при создании дополнительных​Советы​ устранить проблему, вы​ применить это к​

​: Действительно) обшибся :)​

support.office.com

Циклическая ссылка в Excel

​ автоматически.​Михаил С.​ нашел то, что​ отобразить источник ошибки».​ этого, но так​ чтобы после этого​ тем, как и​

1. ​:​​Заранее огромное спасибо​​ стрелке вниз рядом​ прямо или косвенно​​ поздней версии, последовательно​​ формул с циклическими​    ​ можете переместить формулу​

   

​ своему реальному файлу.​​ вот файлик​Или вы покупаете​: Ну наверно надо​

1. ​ искал, поэтому для​ невозможно найти источник​​ книга откуда беруться​​ остаток денег был​ когда Excel будет​​operkote​​ за помощь.​

   

2. ​ с иконкой ​​ ссылается на эту​​ выберите элементы​​ ссылками предупреждающее сообщение​​В строке состояния в​

   

3. ​ в другую ячейку​​kim​​Файл удален​​ палку колбасы. Вы​​ не просто включить,​

   

4. ​ инфо.​ ошибки, все связи​ данные еще на​​ положительный. но никак​​ пересчитывать формулы, можно​

   

   ​, эк вы сами​

   • ​operkote​​Error Checking​​ же самую ячейку,​​Файл​​ в приложении Excel​
   • ​ левом нижнем углу​​ (в строке формул​​: Данную опцию нельзя​​- велик размер.​
   • ​ не знаете сколько​​ а и указать​​В 2010 (в​​ удалены.​
   • ​ стадии начальной разработки,​​ не получается. Проблема​​ управлять.​​ себя запутали​
   • ​: Уточню, что на​(Проверка наличия ошибок)​​ называется циклической ссылкой.​​ >​ больше не отображается.​ отображается сообщение​

​ нажмите сочетание клавиш​​ сохранить для конкретной​ [Модераторы]​ ее вес, но​

1. ​ предельное число циклов.​ 2007,2013 скорее всего​Помогите, пожалуйста, обнаружить​​ и может сильно​​ описана в самом,​Итерация — это​Разделите сумму дохода​​ комиссию влияет оборот​​ и нажмите ​ Такого быть не​​Параметры​​ Ниже перечислены некоторые,​

​Циклические ссылки​CTRL+X​
​ книги, во всяком​
​Мультипликатор​

​ знаете сумму =​

office-guru.ru

Избавление от циклической ссылки

​ Для вас либо​​ так же) можно​ ошибку.​
​ измениться. А по​ прикрепленном, файле. Помогите,​ многократный пересчет листа​ от сделки на​ за​Circular References​ может!​ >​ но не все,​и адрес ячейки​, чтобы вырезать формулу,​ случае стандартными методами…​: Не пойму я:​ 87 и знаете​ 1 либо 2.​ проверить ячейку приводящую​Ёк-Мок​
​ логике вещей получается​ пожалуйста! Заранее спасибо!!!​ до удовлетворения определенного​ сумму без комиссии​предыдущий​(Циклические ссылки).​Например, формула в ячейке​Формулы​ ситуации, в которых​ с одной из​ выберите другую ячейку​ Это я про​ почему у вас​ ее цену =​А вообще -​ к циклической ссылке​: Вариант: перемещаете ВСЕ​ что что вычислить​Pelena​ числового условия. Excel​ (которая и является​месяц. Такой оборот​Урок подготовлен для Вас​A3​. Если вы работаете​ предупреждение появится.​ них.​
​ и нажмите сочетание​ включение/выключение итераций.​ с 5 по​ 210 руб/кг. вводите​ примерчик бы…​ следующим образом: вкладка​ листы с формулами​ не возможно не​:​ не может автоматически​ вносимым исходником) и​ складывается из доходов​ командой сайта office-guru.ru​на рисунке ниже​
​ в Excel для​Пользователь создает первый экземпляр​

​При наличии циклических ссылок​​ клавиш​Михаил С.​ 23 строку нет​​ эти цифры и​​Мультипликатор​ «формулы»-«проверка наличия ошибок»-«циклические​ из всех книг​ зная значения. простите​Danik​ рассчитать значение по​ сумму комиссии.​ из таблицы два​Источник: http://www.excel-easy.com/examples/circular-reference.html​ ссылается на ячейку​ Mac, откройте меню​

​ циклической ссылки в​​ на других листах,​​CTRL+V​​: Про галочку в​ ошибки #ЗНАЧ! А​ вес расчитывается автоматически.​
​: Нет там параметра​ ссылки»-…​ в одну книгу,​ затупил​, если решения из​

​ формуле, которая ссылается​​Сделайте всё в​ только за дни​Перевела: Ольга Гелих​A3​Excel​

​ любой открытой книге.​​ кроме активного, в​
​).​ итерациях… Чисто мой​ дальше есть ошибка​
​Мультипликатор​ «предельное число циклов».​Неважно на каком​ а там в​Wiss​

​ этой темы не​​ (прямо или косвенно)​ одной таблице (или,​ предыдущего месяца. Тогда​Автор: Антон Андронов​(т.е. на саму​, выберите пункт​Пользователь удаляет все циклические​ строке состояния выводится​Другая распространенная ошибка связана​ имхо, на основе​ #ЗНАЧ! ?​: 1. в «F23″​ Есть параметр «Предельное​ листе или в​ помощь панель инструментов​: DevilByte, вам нужно​
​ помогли, то создавайте​ на ячейку, содержащую​ по крайней мере,​ циклической ссылки не​operkote​ себя), чего быть​Настройки​

​ ссылки во всех​​ сообщение «Циклические ссылки»​ с использованием функций,​

​ не такого уж​​Я в своем​​ нет формулы, приведенной​​ число итераций» и​ какой книге находится.​
​ «Циклические ссылки»​ для себя определиться,​ свою тему.​ формулу (это называется​ вторую таблицу создавайте​ будет. Но как​
​: Добрый вечер. Помогите,​ не может.​и щелкните элемент​ открытых книгах, после​ без адресов ячеек.​ которые включают ссылки​ большого опыта.​ файле (сохранив его​
​ вами. Такая формула​ при изменении этого​exxt​The_Prist​

​ что вы будете​​На форуме запрещено​​ циклической ссылкой). Если​ не по дням,​ сделать так, чтобы​ пожалуйста.​Примечание:​Вычисление​ чего создает новую​Вы можете перемещаться по​ на самих себя,​Если окрыл файл​ под другим именем​ находится в «E23».​
​ параметра все равно​​: Так вот именно​
​: Как Вы себе​ считать формулой, а​ задавать новые вопросы​ формула содержит обратную​ а по месяцам​ формула просматривала только​Ситуация такая: есть​

​Если вы создадите​​.​ циклическую ссылку.​
​ ячейкам в циклической​ например ячейка F3​ с итерациями и​ на всякий случай)​2.Суть этой формулы​ выдает в моих​ у ТС такой​ это представляете? Ни​ что вносить руками.​ в чужих темах​ ссылку на одну​
​ — тогда поймёте,​ ячейки по предыдущему​ таблица 1 (таблица​ такую циклическую ссылку,​Если у вас Excel​Пользователь закрывает все книги,​ ссылке с помощью​ может содержать формулу​ сохранил его -​ поставил количество итераций​ =ЕСЛИ(F23<>»»;F23/D23;»») в следующем:​ циклических ячейках ошибку​ же случай как​ книг, ни формул​Можно либо посчитать​Danik​ из своих собственных​ где создали проблему).​ месяцу?​
​ сделок), куда заносятся​ Excel вернёт «0».​ 2007, нажмите​ создает новую и​ двойных щелчков по​ =СУММ(A3:F3). Пример:​ то эта галочка​ 1, все ячейки​ если значение Суммы​ #ЗНАЧ! И причем,​ и у меня​ Ваших мы не​ столбец Н на​: Pelena, спасибо за​ ячеек, необходимо определить,​Посмотрите, где необходимо​ShAM​ результаты по операциям​Ещё один пример. Формула​кнопку Microsoft Office​ вводит в нее​ стрелке трассировки, которая​Вы также можете попробовать​
​ остается, действует на​ «покрылись» #ЗНАЧ! Я​ не пустое, то​ если файл сохранить,​ был, когда я​ видим. Как искать-то?​ основе значений I,J,K.​ помощь.​ сколько раз следует​ использовать именно СУММПРОИЗВ(),​:​ (доходы). Доходы имеют​ в ячейке​

​, щелкните​​ формулу с циклической​ указывает на ячейки,​

​ один из описанных​​ все книги в​ его сохранил, Закрыл​ есть туда введены​ то эти #ЗНАЧ!​ открываю файл, а​ Можно посоветовать только​ Либо столбец J​DevilByte​ пересчитывать формулу. Циклические​ а где достаточно​Цитата​ некоторое значение 1​С2​
​Параметры Excel​ ссылкой.​ влияющие на значение​
​ ниже способов.​ компе. если после​ и зашел снова.​ данные, то Цену​ никак не убираются.​ он мне выдает​ одно: откройте одну​ на основе значений​: Доброго ВСЕМ времени​

​ ссылки могут пересчитываться​​ и ВПР().​(operkote)200?’200px’:»+(this.scrollHeight+5)+’px’);»>чтобы формула просматривала​ (в данном случае​на следующем рисунке​и выберите категорию​Пользователь открывает книгу, содержащую​ в текущей ячейке.​Если вы только что​ этого сохранил файл​ #ЗНАЧ! остались.​ находим как Сумма/Количество,​

​Файл удален​​ ошибку:Используя ваш вариант​​ книгу. Просмотрите все​​ H,I,K. Вместе нельзя.​ суток! ситуация такая-​ до бесконечности. Однако​operkote​
​ только ячейки по​ я просто взял​ ссылается на ячейку​

​Формулы​​ циклическую ссылку.​ Чтобы отобразить стрелку​

excelworld.ru

Избежать циклической ссылки в excel

​ ввели формулу, сначала​​ без итераций -​Каким образом у​ а если значение​- велик размер.​ — …»циклические ссылки»​ связи этой книги​Сугубо для примера​ есть табличка, и​ существует возможность управления​: Я не понимайн​ предыдущему месяцу?​ число 200) +​C1​.​При отсутствии других открытых​ трассировки, нажмите кнопку​ проверьте ячейку, в​

​ это опять переходит​​ вас пропали #ЗНАЧ!​ Суммы пустое, то​

​ [Модераторы]​​ вообще не подсвечиваются.​ с другой книгой(можно​ представьте, что вы​ в ней количество​ максимальным числом итераций​Даже если я​Как влияет? Какая​ комиссия. Комиссия выделена​.​В разделе​ книг пользователь открывает​Влияющие ячейки​ которой она расположена.​ на все книги.​ ????​
​ и значение Цены​Михаил С.​ И выход из​ даже выписать). Главное​

​ поставили защиту на​​ мест указывается в​ и количеством допустимых​ разделю доход на​ формула? У Вас​ также в отдельном​

​Формула в ячейке​​Параметры вычислений​ книгу и создает​или​ Проверьте, не ссылается​Еще раз повторяю​Михаил С.​ пустое.​: А в «F23″​ положения только тот,​ запомнить на какие​ лист и запретили​ ручную, количество женских,​ изменений.​ две части, то​ в таблице нет​ столбце. Комиссия зависит​C3​

​установите флажок​​ в ней циклическую​Зависимые ячейки​ ли формула на​ — из моего​: С 5 по​
​3.=ЕСЛИ((F23<>»»);F23/D23;_ссылка_на_значение_) — а​ в формуле =ЕСЛИ((F23<>»»);F23/D23;»»)​ который я указал​ листы и ячейки​ вручную вносить значения​ количество рабочих мест​
​Точность — это​ в оборот входят​ «ячеек по предыдущему​ от оборота за​ссылается на ячейку​Включить итеративные вычисления​ ссылку.​на вкладке​
​ собственную ячейку. Например,​ опыта, я поначалу​ 25 строки я​ что имеется ввиду​ откуда значения берутся?​ выше.​ идут ссылки и​
​ в столбцы H​ и количество мест​ показатель степени сходимости​
​ балансы, а в​ месяцу».​ месяц. Оборот за​
​С2​. На компьютере Mac​Иногда вам могут потребоваться​Формулы​ ячейка A3 может​ пользовался итерациями, но​ помtнял формулe, а​ под _ссылка_на_значение_?​надо изменть както​

​bedvit​​ в каких ячейках​ и J, а​ ИТР. Так вот​ вычислений. Excel хранит​ балансы и те​operkote​ месяц зависит от​.​ щелкните​ циклические ссылки, потому​.​ содержать формулу =(A1+A2)/A3.​ из-за этого отказался.​ ниже оставил ваши.​

planetaexcel.ru

обнаружена циклическая ссылка, не получается отобразить источник ошибки

​Dophin​​ так​
​: Приложите файл-пример, тогда​ эти ссылки записаны.​ оставили только формулы.​ попытался я сделать​ и выполняет вычисления​ и те доходы,​: Согласен, под ночь​ другой таблицы 2​
​Формула в ячейке​Использовать итеративное вычисление​

​ что они заставляют​​Когда Excel впервые находит​ Такие формулы, как​Dophin​ Причем поменять обязательно​: Дабы убрать сообщение​=ЕСЛИ((F23<>»»);F23/D23;_ссылка_на_значение_)​ можно будет конкретнее​

​ Закройте файл. Далее​​Столбец H посчитать​ так что бы​ с точностью 15​ короче, комиссия в​ не совсем понятно​ (таблицы учета).​C4​.​ функции выполнять итерации,​ циклическую ссылку, отображается​ =A1+1 (в ячейке​: а по самим​ только в стобце​ об ошибки включите​или =ЕСЛИ((_другая_ссылка_<>»»);F23/D23;»»)​ говорить что-то. Вашим​ откройте второй файл​ нельзя без данных​ женские места вычислялись​ значащих цифр. Однако​ любом случае будет​ оформил. Посмотрите новое​
​В таблице 2​ссылается на ячейку​В поле​ т. е. повторять​ предупреждающее сообщение. Нажмите​ A1) также приводят​ формулам не видно​ «F». И обрати​ итеративные вычисления с​

​Dophin​​ «выходом» то же​ и проделайте тоже​
​ в столбце J,​ автоматически, но получил​ существует возможность изменить​
​ входить в оборот.​ вложение — я​ также есть даты,​

​С3​​Предельное число итераций​ вычисления до тех​ кнопку​ к ошибкам циклической​ чтоли? )​

​ внимение на примечание​​ максимальным числом итераций​: Разрешите поинтересоваться -​ можно пользоваться, если​ самое. Теперь посмотрите​ а столбец J​ циклическую ссылку. Можно​ точность вычислений, так​ Возможно, я не​ постарался все уточнить.​ но если в​

​.​​введите количество итераций​ пор, пока не​ОК​ ссылки.​
​Мультипликатор​ в ячейке «D5″/​ — 0.​ а как это​ мало листов в​ какие ссылки могут​ нельзя посчитать без​ ли этого как​
​ что Excel при​ правильно вас понимаю.​ShAM​

​ первой таблице они​​Пока все нормально. Теперь​ для выполнения при​ будет выполнено заданное​или закройте окно​Проверьте наличие непрямых ссылок.​: Только ввел следующую​Мультипликатор​Еще взял на​ работает?​ файлефайлах и можно​ пересекаться.​ столбца H. Всё.​ то избежать? файл​ пересчете формул будет​

​Цитата​​: Может попробовать:​ указывают на даты​ измените значение в​ обработке формул. Чем​ числовое условие. Это​ сообщения.​ Они возникают, когда​ дату, как сразу​

planetaexcel.ru

Как убрать предупреждение о циклических ссылках?

​: Я обратил. Это​​ себя смелость немного​То есть цена=​
​ прощелкать — посмотреть.​Что-то вроде: ячейка​ Приехали.​ прилогаю​ использовать для вычислений​
​200?’200px’:»+(this.scrollHeight+5)+’px’);»>У меня, вроде, работает.​Файл / Параметры​ окончания операций, и​ ячейке​
​ больше предельное число​ может привести к​Если вы закроете сообщение,​ формула, расположенная в​ выскочило сообщение о​ ошибка, в этой​
​ подправить файлик для​ сумма/количество​

​Мультипликатор​​ А1 листа1 файла1​Так что или​wowick​

​ не хранимое, а​​Точно, почему-то сначала​ / Формулы /​

​ не обязаны идти​​C1​ итераций, тем больше​ снижению производительности компьютера,​ то Excel отобразит​
​ ячейке А1, использует​ циклической ссылке, так​ ячейке другая формула​ большей красоты.​

​а сумма= цена*на​​: Вопрос, в принципе,​ ссылается на ячейку​ одно или другое​: А зачем там​ отображаемое значение.​ не получалось. Тоже​
​ Включить итеративные вычисления​ день за днем,​

​на следующую формулу:​​ времени потребуется для​ поэтому по умолчанию​ в ячейке либо​ другую формулу в​ что не работает​ должна быть. Я​Пока одно из​ количество​ в названии темы.​ В2 листа2 файла2.​ нужно вносить руками.​ макросы?​
​operkote​​ работает теперь. Но​ЗЫ: Почему у​

​ то в таблице​​Пояснение:​ пересчета листа.​ итеративные вычисления в​
​ нулевое значение, либо​ ячейке B1, которая​
​ ваш вариант.​
​ сейчас с ней​

​ Вас в столбце​​ 2 даты идут​
​Ячейка​В поле​
​ Excel выключены.​ последнее вычисленное значение.​ снова ссылается на​
​А формулы заметил,​ разбираюсь.​ «цена» не заполнено​: Работает это просто.​
​ файле циклические ссылки,​ как ячейка В2​
​ внесения можно навесить​: можно. Не делайте​ помогли.​ считает? Что такое​
​ «оборот за предыдущий​ именно так (день​
​С1​Относительная погрешность​Если вы не знакомы​ В некоторых случаях​
​ ячейку А1. Если​ как поменяли. Но…​
​Dophin​ — там будет​
​Три ячейки: Количество,​ но эти формулы​ листа2 файла2 ссылается​ условное форматирование.​ ссылки такими, чтобы​AndreTM​ итерации, точность и​
​ месяц» суммируются данные​ за днем). Напротив​ссылается на ячейку​введите наименьшее значение,​ с итеративными вычислениями,​ формула может успешно​ это сбивает с​Мультипликатор​: дубль два​ ошибка #знач, заполняете​

​ Цена, Сумма.​​ мне нужны и​ на ячейку А1​Можно ещё извратиться​ они зависели от​
​: Я не понимайн​ число макс. повторений?​ со столбца «баланс»?​ каждого дня есть​C4​ до достижения которого​ вероятно, вы не​ работать до тех​ толку вас, представьте,​: Хоть ваш файл​Мультипликатор​
​ что нить одно​Две любые из​ они работают.​

​ Например, у Вас​​ разделю доход на​
​: Жмем F1. Пишем​​: В обороте должно​ за день и​

​С4​​ Это наименьшее приращение​

​ циклических ссылок. Если​​ не попытается вычислить​ Excel.​ я успел его​ открывая ваш файл,​ само.​ быть заполнены вручную.​ предупреждение о циклических​: файлы объемные, поэтому​ столбце Н пересчитывать​

​ общая сумма часов​​ две части, то​ «итерации». Читаем:​ вычисляться среднее значение​

​ балансом на конец​​ссылается на ячейку​ в любом вычисляемом​

​ же они вам​​ себя. Например, формула,​Если найти ошибку не​
​ скачать до удаления.​​ предупреждение о циклической​Вообщем поглядите​

​Если вам известны​​ ссылках постоянно появляется​ не приложены.​ значения в столбце​ в столбце Н​ в оборот входят​Для эффективного использования​ балансов на конец​
​ дня. Доходы за​C3​ значении. Чем меньше​ нужны, необходимо решить,​ использующая функцию «ЕСЛИ»​ удается, на вкладке​ Я не разу​ ссылке все равно​kim​ только Количество и​
​ при открытии файла.​все ссылки и​ J и наоборот​

​ зависит от значения​​ балансы, а в​ формулы (Формула. Совокупность​ дня за рабочие​ день рассчитываются, как​.​ число, тем точнее​ сколько раз может​ может работать до​Формулы​

​ не сталкивался с​​ вылетает!!!!​: УФом их скройте​ Цена, то Сумма​Я пытался сделать​ связи я постаралась​ (главное — Application.enableEvents=false​

​ в J, а​​ балансы и те​

​ значений, ссылок на​​ дни предыдущего месяца.​ сумма доходов от​С3​ результат и тем​ повторяться вычисление формулы.​

​ тех пор, пока​​щелкните стрелку рядом​ Условным Форматированием. Я​Мультипликатор​Файл удален​ считается так:​ Application.DisplayAlerts = False​ удалить или разорвать.​ не забыть), но​

​ с кнопкой​​ посмотрел, никаких новых​: Я ничего не​- велик размер.​Сумма = Количество​ на открытие книги,​ошибка продолжает возникать.​

​ это плохая идея.​​ рассчитывается исходя из​ короче, комиссия в​ объектов, функций и​ примере это усложнение​ 1, которые закончились​C2​

​ Excel для вычислений.​​ вычисления, не изменив​ аргумент (данные, необходимые​Проверка ошибок​ правил в файле​ понял, что же​
​ [Модераторы]​ * Цена.​ и на активацию​ видимые ее результаты​DevilByte​ значения суммы в​ любом случае будет​ операторов, позволяющая получить​ ни к чему,​ в этот день.​.​
​Итеративное вычисление может иметь​ предельное число итераций​ для нормальной работы​, выберите пункт​ нет. Как это​

​ изменилось? В чем​​Мультипликатор​Если вам известны​ листа — не​

​ отсутствуют.​​: Согласен, просто эта​ Н. Вот и​ входить в оборот.​ новое значение. Формула​ поэтому я просто​ Баланс же рассчитывается​
​С2​ три исход:​

​ и относительную погрешность,​​ формулы), который заставит​Циклические ссылки​ работает, объясните?​ вы сделали изменения?​: А где файлик?​ только Количество и​ помогает.​The_Prist​ гнусная идея посетила​ цикличная ссылка. Ведь​ Возможно, я не​

planetaexcel.ru

​ всегда начинается со​

В большинстве случаев циклические ссылки воспринимаются пользователями как ошибочные выражения. Это обуславливается тем, что сама программа перегружается от их наличия, предупреждая об этом специальным оповещением. Чтобы снять ненужную нагрузку с программных процессов и устранить конфликтные ситуации между ячейками, необходимо найти проблемные места и удалить их.

Что такое циклическая ссылка

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

Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 2 наиболее простых и проверенных на практике.

Важно! Раздумывать о том есть ли в таблице циклические ссылки или нет не нужно. Если подобные конфликтные ситуации присутствуют, современные версии Excel сразу уведомляют пользователя об этом предупреждающим окном с соответствующей информацией.

Визуальный поиск

Самый простой метод поиска, который подойдет при проверке небольших таблиц. Порядок действий:

1. Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
2. Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.

1. Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
2. Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.

Использование инструментов программы

В тех случаях, когда стрелки трассировки не указывают на проблемные места в таблице, необходимо воспользоваться встроенными инструментами Excel для поиска и удаления циклических ссылок. Порядок действий:

1. В первую очередь нужно закрыть окно с предупреждением.
2. Перейти на вкладку «Формулы» на основной панели инструментов.
3. Зайти в раздел «Зависимости формул».
4. Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.

1. Из списка выбрать «Циклические ссылки».
2. Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
3. Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.

Отключение блокировки и создание циклических ссылок

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

Чаще всего циклические ссылки осознанно применяются во время построения экономических моделей, для выполнения итеративных вычислений. Однако, даже если подобное выражение используется осознанно, программа все равно будет блокировать его в автоматическом режиме. Для запуска выражения в работу, необходимо отключить блокировку. Для этого нужно выполнить несколько действий:

1. Зайти во вкладку «Файл» на главной панели.
2. Выбрать пункт «Параметры».
3. Перед пользователем должно появиться окно настройки Excel. Из меню в левой части выбрать вкладку «Формулы».
4. Перейти к разделу «Параметры вычислений». Установить галочку напротив функции «Включить итеративные вычисления». Дополнительно к этому в свободных полях чуть ниже можно установить максимальное количество подобных вычислений, допустимую погрешность.

Важно! Без особой необходимости лучше не изменять максимальное количество итеративных вычислений. Если их будет слишком много, программа будет перегружена, могут появиться сбои с ее работой.

1. Чтобы изменения вступили в силу, необходимо нажат на кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, которые связаны циклическими ссылками.

Самый простой вариант создания циклической ссылки – выделить любую клетку таблицы, в нее вписать знак «=», сразу после которого добавить координаты этой же ячейки. Чтобы усложнить задачу, расширить циклическую ссылку на несколько ячеек, нужно выполнить следующий порядок действий:

1. В клетку А1 добавить цифру «2».
2. В ячейку В1 вписать значение «=С1».
3. В клетку С1 добавить формулу «=А1».
4. Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.

Заключение

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

Вы ввели формулу, но она не работает. Вместо этого вы получили сообщение о «циклической ссылке». Миллионы людей имеют ту же проблему, и это происходит потому, что ваша формула пытается вычислить себя, и у вас отключена функция, называемая итеративным вычислением. Вот как это выглядит:

Формула =D1+D2+D3 не работает, поскольку она расположена в ячейке D3 и ссылается на саму себя. Чтобы устранить проблему, можно переместить формулу в другую ячейку. Нажмите клавиши CTRL+X , чтобы вырезать формулу, выделите другую ячейку и нажмите клавиши CTRL+V , чтобы вставить ее.

Другая распространенная ошибка связана с использованием функций, которые включают ссылки на самих себя, например ячейка F3 может содержать формулу =СУММ(A3:F3). Пример:

Вы также можете попробовать один из описанных ниже способов.

• Если вы только что ввели формулу, начните с этой ячейки и проверьте, ссылаетесь ли вы на саму ячейку. Например, ячейка A3 может содержать формулу =(A1+A2)/A3. Такие формулы, как =A1+1 (в ячейке A1), также вызывают ошибки циклической ссылки.

Проверьте наличие непрямых ссылок. Они возникают, когда формула, расположенная в ячейке А1, использует другую формулу в ячейке B1, которая снова ссылается на ячейку А1. Если это сбивает с толку вас, представьте, что происходит с Excel.

• Если найти ошибку не удается, на вкладке Формулы щелкните стрелку рядом с кнопкой Проверка ошибок, выберите пункт Циклические ссылки и щелкните первую ячейку в подменю.

  

• Проверьте формулу в ячейке. Если вам не удается определить, является ли эта ячейка причиной циклической ссылки, выберите в подменю Циклические ссылки следующую ячейку.

• Продолжайте находить и исправлять циклические ссылки в книге, повторяя действия 1–3, пока из строки состояния не исчезнет сообщение «Циклические ссылки».

Советы    

• В строке состояния в левом нижнем углу отображается сообщение Циклические ссылки и адрес ячейки с одной из них.

  При наличии циклических ссылок на других листах, кроме активного, в строке состояния выводится сообщение «Циклические ссылки» без адресов ячеек.

• Вы можете перемещаться между ячейками в циклической ссылке, дважды щелкнув стрелку трассировки. Стрелка указывает ячейку, которая влияет на значение выбранной ячейки. Стрелка трассировки отображается, щелкнув Формулы, а затем — Прецеденты трассировки или Трассировка Зависимости.

  

Предупреждение о циклической ссылке

Когда Excel впервые находит циклическую ссылку, появляется предупреждающее сообщение. Нажмите кнопку ОК или закройте окно сообщения.

При закрытии сообщения Excel отображает нулевое или последнее вычисляемое значение в ячейке. И теперь вы, вероятно, говорите: «Повесьте, последнее вычисляемое значение?» Да. В некоторых случаях формула может успешно выполниться до того, как она попытается вычислить себя. Например, формула, использующая функцию IF , может работать до тех пор, пока пользователь не введет аргумент (фрагмент данных, необходимый для правильного выполнения формулы), который приведет к вычислению самой формулы. В этом случае Excel сохраняет значение из последнего успешного вычисления.

Если есть подозрение, что циклическая ссылка содержится в ячейке, которая не возвращает значение 0, попробуйте такое решение:

• Щелкните формулу в строке формулы и нажмите клавишу ВВОД.

Важно    Во многих случаях при создании дополнительных формул с циклическими ссылками предупреждающее сообщение в приложении Excel больше не отображается. Ниже перечислены некоторые, но не все, ситуации, в которых предупреждение появится.

• Пользователь создает первый экземпляр циклической ссылки в любой открытой книге.

• Пользователь удаляет все циклические ссылки во всех открытых книгах, после чего создает новую циклическую ссылку.

• Пользователь закрывает все книги, создает новую и вводит в нее формулу с циклической ссылкой.

• Пользователь открывает книгу, содержащую циклическую ссылку.

• При отсутствии других открытых книг пользователь открывает книгу и создает в ней циклическую ссылку.

Итеративные вычисления

Иногда может потребоваться использовать циклические ссылки, так как они вызывают итерацию функций— повторять до тех пор, пока не будет выполнено определенное числовое условие. Это может замедлить работу компьютера, поэтому итеративные вычисления обычно отключаются в Excel.

Если вы не знакомы с итеративными вычислениями, вероятно, вы не захотите оставлять активных циклических ссылок. Если же они вам нужны, необходимо решить, сколько раз может повторяться вычисление формулы. Если включить итеративные вычисления, не изменив предельное число итераций и относительную погрешность, приложение Excel прекратит вычисление после 100 итераций либо после того, как изменение всех значений в циклической ссылке с каждой итерацией составит меньше 0,001 (в зависимости от того, какое из этих условий будет выполнено раньше). Тем не менее, вы можете сами задать предельное число итераций и относительную погрешность.

1. Щелкните Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel и выберите Настройки > Вычисление.

2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac щелкните Использовать итеративное вычисление.

3. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.

4. В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.

Итеративное вычисление может иметь три исход:

• Решение сходится, что означает получение надежного конечного результата. Это самый желательный исход.

• Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.

• Решение переключается между двумя значениями. Например, после первой итерации результат равен 1, после следующей итерации — 10, после следующей итерации — 1 и т. д.

К началу страницы

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Дополнительные сведения

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Сочетания клавиш и горячие клавиши в Excel

Функции Excel (по алфавиту)

Функции Excel (по категориям)

В большинстве случаев циклические ссылки воспринимаются пользователями как ошибочные выражения. Это обуславливается тем, что сама программа перегружается от их наличия, предупреждая об этом специальным оповещением. Чтобы снять ненужную нагрузку с программных процессов и устранить конфликтные ситуации между ячейками, необходимо найти проблемные места и удалить их.

Что такое циклическая ссылка

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

Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 2 наиболее простых и проверенных на практике.

Важно! Раздумывать о том есть ли в таблице циклические ссылки или нет не нужно. Если подобные конфликтные ситуации присутствуют, современные версии Excel сразу уведомляют пользователя об этом предупреждающим окном с соответствующей информацией.

Визуальный поиск

Самый простой метод поиска, который подойдет при проверке небольших таблиц. Порядок действий:

1. Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
2. Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.

1. Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
2. Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.

Использование инструментов программы

В тех случаях, когда стрелки трассировки не указывают на проблемные места в таблице, необходимо воспользоваться встроенными инструментами Excel для поиска и удаления циклических ссылок. Порядок действий:

1. В первую очередь нужно закрыть окно с предупреждением.
2. Перейти на вкладку «Формулы» на основной панели инструментов.
3. Зайти в раздел «Зависимости формул».
4. Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.

1. Из списка выбрать «Циклические ссылки».
2. Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
3. Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.

Отключение блокировки и создание циклических ссылок

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

Чаще всего циклические ссылки осознанно применяются во время построения экономических моделей, для выполнения итеративных вычислений. Однако, даже если подобное выражение используется осознанно, программа все равно будет блокировать его в автоматическом режиме. Для запуска выражения в работу, необходимо отключить блокировку. Для этого нужно выполнить несколько действий:

1. Зайти во вкладку «Файл» на главной панели.
2. Выбрать пункт «Параметры».
3. Перед пользователем должно появиться окно настройки Excel. Из меню в левой части выбрать вкладку «Формулы».
4. Перейти к разделу «Параметры вычислений». Установить галочку напротив функции «Включить итеративные вычисления». Дополнительно к этому в свободных полях чуть ниже можно установить максимальное количество подобных вычислений, допустимую погрешность.

Важно! Без особой необходимости лучше не изменять максимальное количество итеративных вычислений. Если их будет слишком много, программа будет перегружена, могут появиться сбои с ее работой.

1. Чтобы изменения вступили в силу, необходимо нажат на кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, которые связаны циклическими ссылками.

Самый простой вариант создания циклической ссылки – выделить любую клетку таблицы, в нее вписать знак «=», сразу после которого добавить координаты этой же ячейки. Чтобы усложнить задачу, расширить циклическую ссылку на несколько ячеек, нужно выполнить следующий порядок действий:

1. В клетку А1 добавить цифру «2».
2. В ячейку В1 вписать значение «=С1».
3. В клетку С1 добавить формулу «=А1».
4. Останется вернуться в самую первую ячейку, через нее сослаться на клетку В1. После этого цепь из 3 ячеек замкнется.

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Поиск циклической ссылки в Excel

​Смотрите также​ книги, которые ссылаются​Ёк-Мок​ любом случае будет​ тем, как и​ и ВПР().​ Вас в столбце​ ссылка. Комиссия зависит​ ДВССЫЛ, СЕЙЧАС, сегодня,​, чтобы вернуться к​>50;СУММ(G2:G5);0)​ Смотрите статью «Функция​ в формуле с​ можем ошибочно в​ в формуле.​ в отличие от​. На ленте в​Циклические ссылки представляют собой​ на связь.​: Вариант: перемещаете ВСЕ​ входить в оборот.​ когда Excel будет​operkote​ «оборот за предыдущий​ от оборота, оборот​ СЛУЧМЕЖДУ, сведения и​

​ предыдущей ячейке и​Диапазон ячеек F2: F5​

Выявление циклических связей

​ «СЧЁТЕСЛИ» в Excel».​ ячейки Е43 на​ формулу поставить адрес​Кнопка​ первого варианта, особенно​ блоке инструментов​ формулу, в которой​exxt​ листы с формулами​ Возможно, я не​ пересчитывать формулы, можно​: Я не понимайн​

Способ 1: кнопка на ленте

1. ​ месяц» суммируются данные​ от доходов за​ СУММЕСЛИ (в некоторых​ формуле.​ содержит значения 55,​Примечание:​ ячейку D43. Получилось​ самой ячейки (в​«​ в сложных формулах.​

   

2. ​«Зависимости формул»​​ одна ячейка через​​: В окне открытой​ из всех книг​​ правильно вас понимаю.​​ управлять.​​Даже если я​​ со столбца «баланс»?​ день, доходы за​ сценарии).​Примечание:​ 35, 45 и​Мы стараемся как​​ так.​​ которой пишем формулу)​Влияющие ячейки»​Как видим, отыскать циклическую​имеется кнопка​ последовательность связей с​ книги просмотрите строку​ в одну книгу,​Можно сделать и​Итерация — это​ разделю доход на​

   

3. ​operkote​ день от доходов​operkote​ Кнопка​

   

4. ​ 25 и поэтому​ можно оперативнее обеспечивать​Все исправили, все считает​ – получится циклическая​- показывает стрелками,​ ссылку в Эксель​«Проверка наличия ошибок»​ другими ячейками, в​

Способ 2: стрелка трассировки

​ состояния. При циклической​ а там в​ без итераций -​

1. ​ многократный пересчет листа​ две части, то​: В обороте должно​ от сделок, а​​: Добрый вечер. Помогите,​​Шаг с заходом​

   

2. ​ функция СРЗНАЧ(F2:F5) возвращает​ вас актуальными справочными​ правильно.​ ссылка.​

​ из каких ячеек​ довольно просто, особенно​. Кликаем на пиктограмму​ конечном итоге ссылается​ ошибке на ней​ помощь панель инструментов​ см.файлик.​ до удовлетворения определенного​ в оборот входят​

​ вычисляться среднее значение​ последние — от​ пожалуйста.​недоступна для ссылки,​ результат — 40.​ материалами на вашем​Кнопка​Циклическая ссылка в Excel​ цифры считаются формулой​ если знать алгоритм​ в виде перевернутого​ сама на себя.​ будет отображена надпись​ «Циклические ссылки»​

​Операции в таблице​

lumpics.ru

Как проверить формулы в Excel.

​ числового условия. Excel​​ балансы, а в​ балансов на конец​ комиссий в том​​Ситуация такая: есть​ если ссылка используется​=ЕСЛИ(​ языке. Эта страница​«​– это, когда​ в выделенной ячейки.​ поиска. Можно воспользоваться​ треугольника рядом с​ В некоторых случаях​ «Циклические ссылки: адрес​The_Prist​
​ должны идти по​ не может автоматически​ балансы и те​ дня за рабочие​ числе. Как избавиться​
​ таблица 1 (таблица​​ в формуле во​Ложь​​ переведена автоматически, поэтому​
​Вычислить формулу»​ в формуле указан​Здесь, в ячейке Е52​ одним из двух​ этой кнопкой. В​ пользователи осознано применяют​ ячейки» (на активном​: Как Вы себе​ возрастанию дат, если​ рассчитать значение по​ и те доходы,​
​ дни предыдущего месяца.​​ от этого, как​​ сделок), куда заносятся​​ второй раз или​;СУММ(G2:G5);0)​ ее текст может​- считает по​​ адрес ячейки, в​ стоит формула. Стрелками​ способов нахождения подобных​ появившемся меню выбираем​ подобный инструмент для​ листе) или «Циклические​
​ это представляете? Ни​​ в каком-то месяце​ формуле, которая ссылается​ короче, комиссия в​ Но в данном​ обойти такой порядок,​
​ результаты по операциям​ если формула ссылается​40 не больше 50,​ содержать неточности и​ формуле, но выдает​​ которой записана эта​ показаны ячейки, которые​ зависимостей. Несколько сложнее​ пункт​ вычислений. Например, такой​ ссылки» (не на​ книг, ни формул​
​ не было операций​​ (прямо или косвенно)​​ любом случае будет​​ примере это усложнение​
​ сохранив вычисления?​​ (доходы). Доходы имеют​ на ячейку в​ поэтому выражение в​​ грамматические ошибки. Для​​ результат, постепенно считая​​ формула, т.е. ячейка​ посчитались, чтобы получился​
​ определить, нужна ли​
​«Циклические ссылки»​​ подход может помочь​ текущем листе)​ Ваших мы не​​ — все равно​ на ячейку, содержащую​
​ входить в оборот.​ ни к чему,​Заранее огромное спасибо​ некоторое значение 1​ отдельной книге.​ первом аргументе функции​ нас важно, чтобы​ каждое действие, каждый​
​ ссылается сама на​​ результат в ячейке​ данная формула в​. После перехода по​ при моделировании. Но,​bedvit​ видим. Как искать-то?​ нужно иметь хотя​
​ формулу (это называется​ Возможно, я не​ поэтому я просто​ за помощь.​ (в данном случае​Продолжайте этот процесс, пока​ Если (аргумент лог_выражение)​ эта статья была​​ этап вычисления.​ себя.​ Е52.​ действительно или это​
​ этой надписи в​ в большинстве случаев,​
​: Думаю создатель темы​ Можно посоветовать только​ бы одну «пустую»​ циклической ссылкой). Если​ правильно вас понимаю.​ поставил сумму балансов​operkote​ я просто взял​ не будут вычислены​ имеет значение false.​ вам полезна. Просим​У нас такая​Так делать нельзя,​Кнопка «Зависимые ячейки»​
​ просто ошибка, а​ виде меню показываются​ данная ситуация –​
​ нашел то, что​ одно: откройте одну​ запись с датой.​ формула содержит обратную​Цитата​ за предыдущий месяц.​: Уточню, что на​ число 200) +​ все части формулы.​0​ вас уделить пару​ формула.​
​ это ошибка, п.ч.​- показывает стрелками,​
​ также исправить ошибочную​​ все координаты ссылок​​ это просто ошибка​​ искал, поэтому для​ книгу. Просмотрите все​Danik​ ссылку на одну​200?’200px’:»+(this.scrollHeight+5)+’px’);»>У меня, вроде, работает.​​ Если это будет​ комиссию влияет оборот​​ комиссия. Комиссия выделена​Чтобы посмотреть вычисление еще​Функция Если возвращает значение​ секунд и сообщить,​Мы её проверяем.​ при вычислении по​​ в каких ячейках​ ссылку.​ циклического характера в​ в формуле, которую​​ инфо.​ связи этой книги​: У меня проблема​ из своих собственных​Точно, почему-то сначала​ работать так, то​ за​
​ также в отдельном​ раз, нажмите кнопку​ третьего аргумента (аргумент​ помогла ли она​ Выделяем ячейку с​ такой формуле будут​ (в каких формулах)​​Автор: Максим Тютюшев​ данной книге. При​
​ юзер допустил по​В 2010 (в​ с другой книгой(можно​ с циклическими ссылками.​ ячеек, необходимо определить,​ не получалось. Тоже​ в своей таблице​
​предыдущий​ столбце. Комиссия зависит​Начать сначала​ значение_если_ложь). Функция сумм​ вам, с помощью​ этой формулой, нажимаем​

excel-office.ru

Вычисление вложенной формулы по шагам

​ происходить бесконечные вычисления.​​ используется цифра из​Рассмотрим,​ клике на координаты​ невнимательности или по​ 2007,2013 скорее всего​ даже выписать). Главное​ Мне нужно автоматизировать​ сколько раз следует​ работает теперь. Но​ я уже все​месяц. Такой оборот​ от оборота за​.​ не вычисляется, поскольку​ кнопок внизу страницы.​ кнопку «Вычислить формулу».​ Тогда выходит окно​ этой ячейки.​как проверить формулу в​ конкретной ячейки, она​ другим причинами. В​

​ так же) можно​ запомнить на какие​ расчет платежей, так​ пересчитывать формулу. Циклические​ как он так​ сделаю как надо.​ складывается из доходов​ месяц. Оборот за​Чтобы закончить вычисление, нажмите​​ она является вторым​​ Для удобства также​ Появилось такое диалоговое​ с предупреждением о​Здесь, в примере, ячейка​ Excel, как найти​ становится активной на​ связи с этим,​ проверить ячейку приводящую​ листы и ячейки​

​ чтобы после этого​ ссылки могут пересчитываться​	​ считает? Что такое​
​Итеративные вычисления не​	​ из таблицы два​ месяц зависит от​ кнопку​ аргументом функции Если​
​ приводим ссылку на​​ окно.​​ циклической ссылке.​	​ D49 установлена только​ циклическую ссылку​ листе.​ чтобы удалить ошибку,​ к циклической ссылке​ идут ссылки и​
​ остаток денег был​​ до бесконечности. Однако​​ итерации, точность и​	​ помогут, т.к. сама​ только за дни​ другой таблицы 2​Закрыть​ (за аргументом значение_если_истина​
​ оригинал (на английском​	​В нем подчеркнут адрес​Нам нужно найти эту​ в одной формуле,​, т. д. Когда​Путем изучения результата устанавливаем​ следует сразу найти​ следующим образом: вкладка​ в каких ячейках​ положительный. но никак​ существует возможность управления​ число макс. повторений?​

1. ​ формула комиссии сейчас​ предыдущего месяца. Тогда​ (таблицы учета).​.​

2. ​ следует) и возвращается​​ языке) .​​ первой ячейки, указанной​​ формулу, где есть​​ расположенной в ячейке​​ формулы большие, трудно​​ зависимость и устраняем​

   

3. ​ саму циклическую ссылку.​​ «формулы»-«проверка наличия ошибок»-«циклические​​ эти ссылки записаны.​ не получается. Проблема​ максимальным числом итераций​

   ​ShAM​ подразумевает зависимость от​ циклической ссылки не​В таблице 2​​Примечания:​​ только в том​В некоторых случаях общее​​ в формуле. Нажимаем​​ циклическая ссылка, и​​ D52.​​ понять, на каком​ причину цикличности, если​ Посмотрим, как это​

   ​ ссылки»-…​​ Закройте файл. Далее​​ описана в самом,​​ и количеством допустимых​: Жмем F1. Пишем​ самой себя. И​ будет. Но как​ также есть даты,​ ​ случае, если выражение​

4. ​ представление о как​ кнопку «Вычислить».​ исправить её.​

5. ​А здесь, от цифры​ этапе вычисления сделали​​ она вызвана ошибкой.​​ делается.​

   ​Неважно на каком​ откройте второй файл​​ прикрепленном, файле. Помогите,​​ изменений.​

   ​ «итерации». Читаем:​​ я не знаю,​

   • ​ сделать так, чтобы​ но если в​Некоторые части формулы, воспользуйтесь​ верно.​ вложенные формула Получает​​Вместо адреса ячейки D52​​Как найти циклическую ссылку​

   • ​ в ячейке D52​ ошибку. Но в​​После выполнения необходимых действий​​Скачать последнюю версию​ листе или в​

   • ​ и проделайте тоже​ пожалуйста! Заранее спасибо!!!​Точность — это​Для эффективного использования​ как это исправить.​ формула просматривала только​

   • ​ первой таблице они​ функциями Если и​Выделите ячейку, которую нужно​ конечный результат сложно​​ (были подчеркнуты) теперь​​ в Excel.​ зависит результат двух​ Excel есть функция,​ снова переходим по​ Excel​ какой книге находится.​ самое. Теперь посмотрите​Pelena​

support.office.com

Избавление от циклической ссылки

​ показатель степени сходимости​​ формулы (Формула. Совокупность​ShAM​
​ ячейки по предыдущему​ указывают на даты​ последовательно выберите ПУНКТЫ​ вычислить. За один​ за несколько промежуточных​ стоит цифра из​Нажимаем в диалоговом​ ячеек. В двух​ которая поможет это​ кнопке проверки ошибок​Если в книге присутствует​exxt​ какие ссылки могут​:​ вычислений. Excel хранит​ значений, ссылок на​: У меня, вроде,​
​ месяцу?​ окончания операций, и​ функции не вычисляются,​ раз можно вычислить​ расчетов и логической​ этой ячейки. Теперь​ окне «Предупреждение о​ формулах, в ячейках​ сделать. «Как написать​ циклических ссылок. На​ циклическая ссылка, то​: Так вот именно​ пересекаться.​Danik​ и выполняет вычисления​ другие ячейки, именованных​ работает.​ShAM​ не обязаны идти​ а в поле​ только одну ячейку.​ поверки. Тем не​ подчеркнут адрес ячейки​ циклической ссылке» кнопку​ D53 и Е52,​ формулу в Excel»​ этот раз соответствующий​
​ уже при запуске​ у ТС такой​Что-то вроде: ячейка​, если решения из​ с точностью 15​ объектов, функций и​AndreTM​:​ день за днем,​Вычисление​На вкладке​ менее используя диалоговое​ Е52. Нажимаем кнопку​ «Отмена». Нажимаем на​
​ указана ячейка D52.​ смотрите, какими способами​

​ пункт меню должен​​ файла программа в​ же случай как​ А1 листа1 файла1​​ этой темы не​​ значащих цифр. Однако​ операторов, позволяющая получить​:​Цитата​ то в таблице​отображается # н/д.​Формулы​ окно «​ «Вычислить». Получилось так.​ стрелку, расположенную рядом​Кнопка​

​ и какие формулы​​ быть вообще не​​ диалоговом окне предупредит​​ и у меня​ ссылается на ячейку​ помогли, то создавайте​
​ существует возможность изменить​ новое значение. Формула​operkote​(operkote)200?’200px’:»+(this.scrollHeight+5)+’px’);»>чтобы формула просматривала​ 2 даты идут​

​Если ссылка пуста, в​​в группе​Вычисление формулы​Если нажмем ещё раз​ с кнопкой «Проверка​«​

​ можно написать.​​ активен.​
​ об этом факте.​ был, когда я​ В2 листа2 файла2.​
​ свою тему.​ точность вычислений, так​ всегда начинается со​, эк вы сами​ только ячейки по​

​ именно так (день​​ поле​Зависимости формул​», вы увидите​ кнопку «Вычислить», то​ наличия ошибок» (на​Убрать стрелки»​Если формула составлена​Существует и другой способ​ Так что с​ открываю файл, а​ В то время​На форуме запрещено​ что Excel при​ знака равенства (=).)​ себя запутали​ предыдущему месяцу?​
​ за днем). Напротив​Вычисление​нажмите кнопку​ различные части вложенной​ первые две цифры​ рисунке обведена зеленым​убирает стрелки.​

​ с ошибкой, то​​ определения подобных нежелательных​ определением самого наличия​

​ он мне выдает​​ как ячейка В2​​ задавать новые вопросы​​ пересчете формул будет​ необходимо ознакомиться с​
​Разделите сумму дохода​Как влияет? Какая​ каждого дня есть​отображается нулевое значение​Вычислить формулу​ формулы в порядке,​
​ сосчитаются по формуле.​ цветом), наводим мышку​Чтобы​ в ячейке с​ зависимостей.​ такой формулы проблем​ ошибку:Используя ваш вариант​ листа2 файла2 ссылается​
​ в чужих темах​ использовать для вычислений​ тремя ключевыми понятиями.​ от сделки на​

​ формула? У Вас​​ ячейка с доходами​​ (0).​.​ в котором вычисляется​ У нас, в​ на слова «Циклические​вместо чисел, в ячейках​ формулой будет стоять​В диалоговом окне, сообщающем​ не возникнет. Как​ — …»циклические ссылки»​ на ячейку А1​Danik​
​ не хранимое, а​​Вычисление — это​
​ сумму без комиссии​ в таблице нет​ за день и​Формулы, в которых циклические​Нажмите кнопку​ по формуле вычисляются.​ примере, 6-2=4. Получилось​

​ ссылки» и рядом​​ Excel были написаны​ зеленый треугольник.​
​ о наличии циклических​ же найти проблемную​ вообще не подсвечиваются.​ листа1 файла1.​: Pelena, спасибо за​ отображаемое значение.​ процесс расчета по​ (которая и является​ «ячеек по предыдущему​ балансом на конец​ ссылки не может​
​Вычислить​ Например, формула =IF(AVERAGE(F2:F5)​ так.​ появляется адрес ячейки,​ формулы​Есть несколько способов​ ссылок, жмем на​ область на листе?​ И выход из​zpd​ помощь.​operkote​ формулам и последующего​ вносимым исходником) и​ месяцу».​ дня. Доходы за​ отличаться от ожидаемых.​, чтобы проверить значение​ > 50,SUM(G2:G5),0) легче​
​И так до конца​ где находится эта​, нажимаем​проверить поэтапный расчет формул​ кнопку​Чтобы узнать, в каком​ положения только тот,​: файлы объемные, поэтому​zpd​: Спасибо, вы очень​ отображения значений результатов​ сумму комиссии.​operkote​ день рассчитываются, как​ При желании циклические​ подчеркнутой ссылки. Результат​ понять при появлении​ формулы.​ циклическая ссылка.​кнопку «Показать формулы»​ в Excel​«OK»​ именно диапазоне находится​
​ который я указал​ не приложены.​: Добрый день!​ помогли.​ в ячейках, содержащих​Сделайте всё в​: Согласен, под ночь​ сумма доходов от​ ссылки можно Включить​ вычисления отображается курсивом.​ промежуточных следующие результаты:​О других, часто​В нашем случае, в​

​(на рисунке обведена​​.​.​

​ такая формула, прежде​​ выше.​все ссылки и​в 2 связанных​AndreTM​ формулы. Во избежание​ одной таблице (или,​ не совсем понятно​ сделок из таблицы​ итеративные вычисления.​Если подчеркнутая часть формулы​Шаги, показанные в диалоговом​ встречающихся ошибках в​ ячейке Е43 стоит​
​ красным цветом).​Итак, заходим на​Появляется стрелка трассировки, которая​
​ всего, жмем на​bedvit​ связи я постаралась​ книгах возникла ошибка​: Я не понимайн​ ненужных вычислений Microsoft​ по крайней мере,​ оформил. Посмотрите новое​ 1, которые закончились​

​Следующие функции пересчитываются каждый​​ является ссылкой на​ окне​ формулах, что они​ такая формула.​Получилось так.​ закладку «Формулы» в​ указывает зависимости данных​ кнопку в виде​: Приложите файл-пример, тогда​ удалить или разорвать.​ — «обнаружена циклическая​

​ Даже если я​​ Office Excel автоматически​​ вторую таблицу создавайте​​ вложение — я​ в этот день.​ раз изменения листа​ другую формулу, нажмите​
​Описание​ означают и как​Ячейка ссылается сама на​

​Чтобы​​ раздел «Зависимости формул».​ в одной ячейки​

excelworld.ru

обнаружена циклическая ссылка, не получается отобразить источник ошибки

​ белого крестика в​​ можно будет конкретнее​
​ошибка продолжает возникать.​ ссылка, не получается​ разделю доход на​ пересчитывает формулы только​ не по дням,​ постарался все уточнить.​ Баланс же рассчитывается​ и может вызвать​
​ кнопку​=ЕСЛИ(СРЗНАЧ(F2:F5)>50;СУММ(G2:G5);0)​

​ их справить, читайте​​ себя. Нашли ошибку​убрать формулы и показать​ Сначала выделяем ячейку,​ от другой.​ красном квадрате в​ говорить что-то. Вашим​ видимые ее результаты​

​ отобразить источник ошибки».​​ две части, то​ при изменении влияющих​ а по месяцам​ShAM​ просто как сумма​ средство​Шаг с заходом​Сначала выводится вложенная формула.​ в статье «Ошибки​ – в ячейке​ результат в ячейках​ затем нажимаем кнопку​Нужно отметить, что второй​ диалоговом окне предупреждения,​ «выходом» то же​ отсутствуют.​ невозможно найти источник​ в оборот входят​ на формулу ячеек.​ — тогда поймёте,​: Может попробовать:​ баланса за предыдущий​
​Вычислить формулу​, чтобы отобразить другую​ Функции СРЗНАЧ и​ в формулах Excel».​ Е43 стоит ссылка​ Excel​ соответствующей функции. При​ способ более визуально​ тем самым закрывая​

​ можно пользоваться, если​​The_Prist​ ошибки, все связи​
​ балансы, а в​ Это является стандартной​ где создали проблему).​
​Файл / Параметры​ день и сегодняшних​, чтобы дать отличается​

​ формулу в поле​​ СУММ вложены в​В Excel можно​ на ячейку Е43​, снова нажимаем на​ работе с формулами,​

​ нагляден, но в​​ его.​ мало листов в​: Связи они такие…Может​ удалены.​ балансы и те​ процедурой при первом​Посмотрите, где необходимо​ / Формулы /​ доходов.​ от в ячейке​

​Вычисление​​ функцию ЕСЛИ.​ проводить подсчет, анализ​ (саму на себя).​ кнопку «Показать формулы».​
​ удобнее проверить формулу,​ то же время​Переходим во вкладку​ файлефайлах и можно​ есть формулы или​Помогите, пожалуйста, обнаружить​ и те доходы,​ открытии книги и​
​ использовать именно СУММПРОИЗВ(),​ Включить итеративные вычисления​Проблема в том,​

​ отобразится результат: СЛЧИС,​​. Нажмите кнопку​=ЕСЛИ(​ данных, составляя формулы​ Исправили ошибку так​Если пишем много​ когда визуально видны​ не всегда даёт​«Формулы»​ прощелкать — посмотреть.​ имена даже внутри​ ошибку.​ короче, комиссия в​ ее редактировании. Однако​ а где достаточно​

​ЗЫ: Почему у​​ что получается циклическая​ СМЕЩ, ячейка, функция​Шаг с выходом​40​ с определенными условиями.​ – изменили ссылку​ разных формул, то​ все ячейки, указанные​

planetaexcel.ru

​ четкую картину цикличности,​

Принято считать, что циклические ссылки в Экселе представляют собой ошибочное выражение. Действительно, довольно часто это именно так, но все-таки не всегда. Иногда они применяются вполне осознанно. Давайте выясним, чем же являются циклические ссылки, как их создавать, как отыскать уже существующие в документе, как работать с ними или как при необходимости удалить.

Использование циклических ссылок

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

Нужно отметить, что по умолчанию современные версии Excel автоматически блокируют процесс выполнения цикличной операции. Это связано с тем, что такие выражения в подавляющем большинстве ошибочные, а зацикливание производит постоянный процесс пересчета и вычисления, что создает дополнительную нагрузку на систему.

Создание циклической ссылки

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

Выделяем элемент листа A1 и записываем в нем следующее выражение:

Далее жмем на кнопку Enter на клавиатуре.

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

Немного усложним задачу и создадим циклическое выражение из нескольких ячеек.

В любой элемент листа записываем число. Пусть это будет ячейка A1, а число 5.

В следующий элемент (C1) производим запись такой формулы:

После этого возвращаемся в ячейку A1, в которой установлено число 5. Ссылаемся в ней на элемент B1:

Жмем на кнопку Enter.

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

Теперь перейдем к созданию циклического выражения на примере таблицы. У нас имеется таблица реализации продуктов питания. Она состоит из четырех колонок, в которых указано наименование товара, количество проданной продукции, цена и сумма выручки от продажи всего объема. В таблице в последнем столбце уже имеются формулы. В них производится расчет выручки путем умножения количества на цену.

Чтобы зациклить формулу в первой строчке, выделяем элемент листа с количеством первого по счету товара (B2). Вместо статического значения (6) вписываем туда формулу, которая будет считать количество товара путем деления общей суммы (D2) на цену (C2):

Щелкаем по кнопке Enter.

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

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

Как видим, выражение было скопировано во все элементы столбца. Но, помечена стрелкой трассировки только одна взаимосвязь. Заметим это на будущее.

Поиск циклических ссылок

Как мы уже видели выше, не во всех случаях программа помечает взаимосвязь циклической ссылки с объектами, даже если она имеется на листе. Учитывая тот факт, что в подавляющем большинстве цикличные операции вредны, их следует удалить. Но для этого их нужно сначала отыскать. Как же это сделать, если выражения не помечены линией со стрелками? Давайте разберемся с этой задачей.

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

При клике на конкретный адрес происходит выделение соответствующей ячейки на листе.

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

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

Исправление циклических ссылок

Как уже говорилось выше, в подавляющем большинстве случаев цикличные операции – это зло, от которого следует избавляться. Поэтому, закономерно, что после того, как цикличная связь обнаружена, нужно её исправить, чтобы привести формулу к нормальному виду.

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

В нашем случае, несмотря на то, что программа верно указала на одну из ячеек цикла (D6), реальная ошибка кроется в другой ячейке. Выделяем элемент D6, чтобы узнать, из каких ячеек он подтягивает значение. Смотрим на выражение в строке формул. Как видим, значение в этом элементе листа формируется путем умножения содержимого ячеек B6 и C6.

Переходим к ячейке C6. Выделяем её и смотрим на строку формул. Как видим, это обычное статическое значение (1000), которое не является продуктом вычисления формулы. Поэтому можно с уверенностью сказать, что указанный элемент не содержит ошибки, вызывающей создание циклических операций.

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

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

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

Подобную операцию проводим и над всеми другими цикличными выражениями, если они имеются на листе. После того, как абсолютно все циклические ссылки были удалены из книги, сообщение о наличие данной проблемы должно исчезнуть из строки состояния.

Кроме того, полностью ли были удалены цикличные выражения, можно узнать, воспользовавшись инструментом проверки наличия ошибок. Переходим во вкладку «Формулы» и жмем уже знакомый нам треугольник справа от кнопки «Проверка наличия ошибок» в группе инструментов «Зависимости формул». Если в запустившемся меню пункт «Циклические ссылки» не будет активен, то, значит, мы удалили все подобные объекты из документа. В обратном случае, нужно будет применить процедуру удаления к элементам, которые находятся в списке, тем же рассматриваемым ранее способом.

Разрешение выполнения цикличных операций

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

Прежде всего, перемещаемся во вкладку «Файл» приложения Excel.

Далее щелкаем по пункту «Параметры», расположенному в левой части открывшегося окна.

Происходит запуск окна параметров Эксель. Нам нужно перейти во вкладку «Формулы».

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

Чтобы разрешить применение цикличных выражений, нужно установить галочку около параметра «Включить итеративные вычисления». Кроме того, в этом же блоке можно настроить предельное число итераций и относительную погрешность. По умолчанию их значения равны 100 и 0,001 соответственно. В большинстве случаев данные параметры изменять не нужно, хотя при необходимости или при желании можно внести изменения в указанные поля. Но тут нужно учесть, что слишком большое количество итераций может привести к серьезной нагрузке на программу и систему в целом, особенно если вы работаете с файлом, в котором размещено много цикличных выражений.

Итак, устанавливаем галочку около параметра «Включить итеративные вычисления», а затем, чтобы новые настройки вступили в силу, жмем на кнопку «OK», размещенную в нижней части окна параметров Excel.

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

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Так как в Microsoft Office Excel всегда используются всяческие формулы от простой автосуммы до сложных вычислений. То соответственно и возникаем много проблем с ними. Особенно если эти формулы писали не опытные пользователи. Одна из самых частых ошибок связанна с циклическими ссылками. Звучит она примерно так при открытие документа появляется окно Предупреждение о циклической ссылки.

Одна или несколько формул содержат циклическую ссылку и могут быть вычислены неправильно. Из данного сообщения чаще всего пользователь ни чего не понимает, по этому я решил написать простом языком что это такое и как решить данную проблему. Можно конечно проигнорировать это сообщение и открыть документ. Но в дальнейшем при вычислениях в таблицах можно столкнуться с ошибками. Давайте рассмотрим что такое циклическая ссылка и как её найти в Excel.

Ошибка некоторые формулы содержат циклические ссылки

Давайте на пример рассмотрим что такое циклическая ссылка. Создадим документ сделаем небольшую табличку даже не табличку а просто напишем несколько цифр и вычислим автосумму.

Теперь немного изменим формулу и включим в нее ячейку в которой должен быть результат. И увидим то самое предупреждение о циклической ссылки.

Теперь сумма у нас вычисляется не верно. А если попробовать включить эту ячейку в другую формулу то результат этих вычислений будет не правильный. Для примера добавим еще два таких же столбца и посчитаем у них сумму. Теперь попробуем посчитать сумму всех трех столбцов в итоге получаем 0.

В нашем примере все просто и очевидно но если вы открыли большую таблицу в которой куча формул и одна считается не верно то нужно будет проверить значение всех ячеек участвующих в формуле на наличие циклических ссылок.

В общем если просто сказать то циклическая ссылка получается если в формуле участвует та ячейка в которой должен быть результат. На самом деле найти циклическую ссылку достаточно просто главное знать что это такое.

Обнаружение и отключение циклической ссылки в Excel нужно когда программа не может провести вычисление книг, так как если есть циклическая ссылка, то в таком случае формула ссылается на себя. Рассмотрим несколько способов как её найти и выключить.

В одном из листов книги нажмите на вкладку «Формулы» и перейдите в раздел «Зависимость формул», откройте пункт «Проверка на наличие ошибок» и в выданном списке откройте «Циклические ссылки». Далее нужно выбрать ячейку в которой была обнаружена циклическая ссылка. И далее изменить формулу на другую, тогда циклическая ссылка будет отключена.

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

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

Содержание

• 1 Использование циклических ссылок
  • 1.1 Создание циклической ссылки
  • 1.2 Поиск циклических ссылок
  • 1.3 Исправление циклических ссылок
  • 1.4 Разрешение выполнения цикличных операций
  • 1.5 Помогла ли вам эта статья?
• 2 Выявление циклических связей
  • 2.1 Способ 1: кнопка на ленте
  • 2.2 Способ 2: стрелка трассировки
  • 2.3 Помогла ли вам эта статья?
  • 2.4 Предупреждение о циклической ссылке
  • 2.5 Как найти циклическую ссылку
  • 2.6 Использование циклических ссылок
  • 2.7 Итеративные вычисления

Принято считать, что циклические ссылки в Экселе представляют собой ошибочное выражение. Действительно, довольно часто это именно так, но все-таки не всегда. Иногда они применяются вполне осознанно. Давайте выясним, чем же являются циклические ссылки, как их создавать, как отыскать уже существующие в документе, как работать с ними или как при необходимости удалить.

Использование циклических ссылок

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

Нужно отметить, что по умолчанию современные версии 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 и зная меру в добавлении подобных ссылок, которые при использовании в массовом количестве способны замедлить работу системы.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

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

Выявление циклических связей

Если в книге присутствует циклическая ссылка, то уже при запуске файла программа в диалоговом окне предупредит об этом факте. Так что с определением самого наличия такой формулы проблем не возникнет. Как же найти проблемную область на листе?

Способ 1: кнопка на ленте

1. Чтобы узнать, в каком именно диапазоне находится такая формула, прежде всего, жмем на кнопку в виде белого крестика в красном квадрате в диалоговом окне предупреждения, тем самым закрывая его.
2. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» имеется кнопка «Проверка наличия ошибок». Кликаем на пиктограмму в виде перевернутого треугольника рядом с этой кнопкой. В появившемся меню выбираем пункт «Циклические ссылки». После перехода по этой надписи в виде меню показываются все координаты ссылок циклического характера в данной книге. При клике на координаты конкретной ячейки, она становится активной на листе.
3. Путем изучения результата устанавливаем зависимость и устраняем причину цикличности, если она вызвана ошибкой.
4. После выполнения необходимых действий снова переходим по кнопке проверки ошибок циклических ссылок. На этот раз соответствующий пункт меню должен быть вообще не активен.

Способ 2: стрелка трассировки

Существует и другой способ определения подобных нежелательных зависимостей.

1. В диалоговом окне, сообщающем о наличии циклических ссылок, жмем на кнопку «OK».
2. Появляется стрелка трассировки, которая указывает зависимости данных в одной ячейки от другой.

Нужно отметить, что второй способ более визуально нагляден, но в то же время не всегда даёт четкую картину цикличности, в отличие от первого варианта, особенно в сложных формулах.

Как видим, отыскать циклическую ссылку в Эксель довольно просто, особенно если знать алгоритм поиска. Можно воспользоваться одним из двух способов нахождения подобных зависимостей. Несколько сложнее определить, нужна ли данная формула в действительно или это просто ошибка, а также исправить ошибочную ссылку.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

25.10.2013 Григорий Цапко Полезные советы

Среди пользователей Excel широко распространено мнение, что циклическая ссылка в excel является разновидностью ошибки, и от нее нужно непременно избавляться.

Между тем, именно циклические ссылки в excel способны облегчить нам решение некоторых практических экономических задач и финансовом моделировании.

Эта заметка как раз и будет призвана дать ответ на вопрос:  а всегда ли циклические ссылки – это плохо? И как с ними правильно работать, чтобы максимально использовать их вычислительный потенциал.

Для начала разберемся, что такое циклические ссылки в excel 2010.

Циклические ссылки возникают, когда формула, в какой либо ячейке через посредство других ячеек ссылается сама на себя.

Например, ячейка С4 = Е 7, Е7 = С11, С11 = С4. В итоге, С4 ссылается на С4.

Наглядно это выглядит так:

На практике, зачастую, не такая примитивная связь, а более сложная, когда результаты вычисления одной формулы, порою весьма замысловатые, влияют на результат вычисления другой формулы, которая в свою очередь влияет на результаты первой. Возникает циклическая ссылка.

Предупреждение о циклической ссылке

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

При нажатии на кнопку ОК, сообщение будет закрыто, а в ячейке содержащей циклическую ссылку в большинстве случаев появиться 0.

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

Как найти циклическую ссылку

Циклические ссылки в excel могут создаваться преднамеренно, для решения тех или иных задач финансового моделирования, а могут возникать случайно, в виде технических ошибок и ошибок в логике построения модели.

В первом случае мы знаем об их наличии, так как сами их предварительно создали, и знаем, зачем они нам нужны.

Во втором случае, мы можем вообще не знать где они находятся, например, при открытии чужого файла и появлении сообщения о наличии циклических ссылок.

Найти циклическую ссылку можно несколькими способами. Например, чисто визуально формулы и ячейки участвующие в образовании циклических ссылок в excel отмечаются синими стрелками, как показано на первом рисунке.

Если циклическая ссылка одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

Если циклические ссылки есть еще на других листах кроме активного, то будет выведено сообщение без указания ячейки.

Если или на активном листе их более одной, то будет выведено сообщение с указанием ячейки, где циклическая ссылка появляется в первый раз, после ее удаления – ячейка, содержащая следующую циклическую ссылку и т.д.

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После ее корректировки или удаления – со второй и т.д.

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

Использование циклических ссылок

Рассмотрим пример использования циклических ссылок в финансовом моделировании. Он поможет нам понять общий механизм итеративных вычислений  и дать толчок для дальнейшего творчества.

В финансовом моделировании не часто, но все же возникает ситуация, когда нам необходимо рассчитать уровень затрат по статье бюджета, зависящей от финансовый результата, на который эта статья влияет. Это может быть, например статья расходов на дополнительное премирование персонала отдела продаж, зависящая от прибыли от продаж.

После введения всех формул, у нас появляется циклическая ссылка:

Однако, ситуация не безнадежная. Нам достаточно изменить некоторые параметры Excel  и расчет будет осуществлен корректно.

Еще одна ситуация когда могут быть востребованы циклические ссылки – это метод взаимных или обратных распределений косвенных затрат между непроизводственными подразделениями.

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

На тему методов распределения затрат в ближайшее время появиться отдельная статья.

Пока же нас интересует сама возможность таких вычислений.

Итеративные вычисления

Для того чтобы корректный расчет был возможен, мы должны включить итеративные вычисления в параметрах Excel.

Итеративные вычисления – это вычисления повторяемые множество раз, пока не будет достигнут результат соответствующий заданным условиям (условию точности или условию количества осуществленных итераций).

Включить итеративные вычисления можно через вкладку Файл → раздел Параметры → пункт Формулы. Устанавливаем флажок «Включить итеративные вычисления».

Как правило, установленных по умолчанию предельного числа итераций и относительной погрешности достаточно для наших вычислительных целей.

Следует иметь ввиду, что слишком большое количество вычислений может существенно загружать систему и снижать производительность.

Также, говоря об итеративных вычислениях, следует отметить, что возможны три варианта развития событий.

Решение сходится, что означает получение надежного конечного результата.

Решение расходится, т. е. при каждой последующей итерации разность между текущим и предыдущим результатами увеличивается.

Решение колеблется между двумя значениями, например, после первой итерации получается значение 1, после второй — значение 10, после третьей — снова 1 и т. д.

Обнаружение и отключение циклической ссылки в Excel нужно когда программа не может провести вычисление книг, так как если есть циклическая ссылка, то в таком случае формула ссылается на себя. Рассмотрим несколько способов как её найти и выключить.

В одном из листов книги нажмите на вкладку «Формулы» и перейдите в раздел «Зависимость формул», откройте пункт «Проверка на наличие ошибок» и в выданном списке откройте «Циклические ссылки». Далее нужно выбрать ячейку в которой была обнаружена циклическая ссылка. И далее изменить формулу на другую, тогда циклическая ссылка будет отключена.

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

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

Рубрика Excel

Также статьи о ссылках в Экселе:

• Ссылка на лист в формуле Excel
• Как убрать гиперссылки в Excel?
• Как сделать ссылку на ячейку в другом файле Excel?
• Ссылка на файл в Эксель

Производя расчеты в Экселе всегда есть шанс запутаться или неправильно набрать формулу. В результате таких ошибок расчеты будут производиться неправильно или может появиться циклическая ссылка в Excel.

Циклическая ссылка в Excel представляет собой ссылку на ячейку, которая ссылается сама на себя. Также циклическими может оказаться группа ссылок, которые в итоге будут ссылаться по кругу, образуя замкнутый цикл.

По умолчанию в последних версиях Excel выполнение циклических ссылок блокируется с последующим оповещением об ошибке. При желании в настройках можно установить максимальное число итераций, которое все же выполнится для циклических ссылок.

Сообщение о найденных циклических ссылках выдается непосредственно после ввода формулы, замыкающей цикл, или при открытии документа, содержащего циклические ссылки. Сразу после ввода циклической ссылки или открытия документа с ними Эксель подсвечивает весь замкнутый цикл, если он состоит из нескольких ссылок, голубыми стрелочками.

Для поиска циклических ссылок в Excel достаточно перейти на вкладку «Формулы», зайти в меню «Проверка наличия ошибок», и просмотреть список циклических ссылок в пункте «Циклические ссылки».

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