Трассировщик ошибок встретил либо стрелку либо циклическую ссылку

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

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

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

Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 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, как найти​ становится активной на​ связи с этим,​ проверить ячейку приводящую​ листы и ячейки​

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 нужно когда программа не может провести вычисление книг, так как если есть циклическая ссылка, то в таком случае формула ссылается на себя. Рассмотрим несколько способов как её найти и выключить.

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

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

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

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

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

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

Если циклическая ссылка – ошибка, которую пользователь допустил случайно при заполнении таблицы, введении определенных функций, формул, необходимо найти ее и удалить. На этот случай существует несколько эффективных способов. Стоит подробно рассмотреть 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 2010  

  Есть лист excel с большим количеством столбцов (около 7 тыс.). В определенный момент в одной из ячеек с формулой excel выдал ошибку, причина которой по его мнению:  

  «Трассировщик ошибок встретил либо стрелки, либо циклическую ссылку. Удалите все стрелки трассировки или исправьте циклическую ссылку и выполните поиск ошибки снова».  

  При этом появляется красная стрелка связывающая включенные в формулу ячейки.  

  Что это такое и как исправить? Спасибо!

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