Excel как удалить циклические ссылки в - Все про Эксель

Excel как удалить циклические ссылки в

Циклическая ссылка в Excel как убрать

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

Вставка ссылки в ячейку

Ссылка на сайт

  1. Выделите нужную клетку. Можете оставить её пустой или что-то написать. Текст вы всегда сумеете удалить.
  2. Кликните по ней правой кнопкой мыши.
  3. Строка «Добавить гиперссылку» («Hyperlink»). Аналогичная кнопка есть в меню «Вставить».
  4. В блоке «Связать» нажмите «Веб-страница» («Web Page»).
  5. Чуть правее кликните на «Просмотренные страницы» и выберите нужную.
  6. Если вы хотите задать URL сайта, который не посещали до этого, напишите его в поле «Адрес» («Asdress»).
  7. В пункте «Замещающий текст» укажите то, что должно замещать гиперссылку. Эта фраза отобразится в ячейке. При клике на неё откроется веб-страница. Можете оставить поле пустым.
  8. Кнопка «Подсказка» («ScreenTip») служит для создания всплывающих подсказок Excel.

Ссылка на файл

  1. Клик правой кнопкой мыши — Добавить гиперссылку.
  2. В разделе «Связать» отметьте «Файл».
  3. Отобразится папка, в которой сейчас находится документ.
  4. Кнопка «Недавние файлы» откроет список данных, которые вы использовали в последнее время. Их нельзя удалить из этого раздела.
  5. Если и там нет того, что вы ищите, задайте путь самостоятельно. Для этого кликните «Текущая папка». Под замещающим текстом будет выпадающий список. В нём выберите каталог.
  6. Задать путь можно и при помощи кнопки «Найти». Она находится чуть правее выпадающего списка.

В Excel можно сослаться на ещё несуществующий документ и сразу его создать.

Когда вы нажмёте на ячейку, к которой привязаны данные на компьютере, система безопасности Excel выдаст предупреждение. Оно сообщает о том, что вы открываете сторонний файл, и он может быть ненадёжным. Это стандартное оповещение. Если вы уверены в данных, с которыми работаете, в диалоговом окне на вопрос «Продолжить?» ответьте «Да».

Можно связать ячейку с e-mail. Тогда при клике на неё откроется ваш почтовый клиент, и в поле «Кому» уже будет введён адрес.

Ссылка на другую ячейку

  1. В разделе «Связать» нажмите «Местом в документе» («Place in This Document»).
  2. В области «Выберите место в документе» отметьте желаемый лист.
  3. В «Адрес» введите название ячейки. Оно состоит из буквы, обозначающей столбец, и номера строки. Чтобы узнать это название, кликните на нужную клетку. Оно отобразится в специальном поле слева вверху.
  4. Нажмите «OK».

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

После этого сошлитесь на диапазон так же, как на клетку.

Вот как сделать гиперссылку в Excel на другую таблицу:

Так можно сделать связь не со всем файлом, а с конкретным местом в файле.

Циклические ссылки

Для начала такие объекты нужно найти.

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

Вот как удалить гиперссылку в Excel, оставив текст, отредактировать её, или вовсе стереть:

Как изменить цвет и убрать подчёркивание?

  1. Выделите клетку.
  2. На панели инструментов во вкладке «Меню» (в Office 2007 «Главная») отыщите букву «Ч» с чёрточкой внизу. Она отвечает за подчёркивание.
  3. На этой же панели надо найти кнопку с пиктограммой в виде символа «А» с полоской под ним.

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

Источник: officeassist.ru

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

Понимание ссылок на Excel

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

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

Устранить циркулярную ссылку в Excel

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

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

Откройте электронную таблицу

Откройте электронную таблицу Microsoft Excel, в которой содержится круговая ссылка.

Используйте меню формул

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

Ищите циркулярную ссылку

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

Трассировка Прецедентов с Excel

Нажмите кнопку «Прецеденты трассировки» в области «Аудит формул» на вкладке «Формула» в меню ленты. Это создаст стрелку в электронной таблице от каждой ячейки, указанной в указанной ячейке, до указанной ячейки. Нажмите кнопку «Trace Dependents», которая создаст стрелки из указанной ячейки для каждой ячейки, которая ссылается на указанную ячейку.

Следуйте ссылочной цепочке

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

Найти больше кругов по мере необходимости

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

Итеративный расчет Excel

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

Чтобы включить итеративный расчет в Excel, перейдите на вкладку «Файл», затем «Параметры» и «Формулы». В разделе «Параметры расчета» установите флажок «Включить итеративный расчет». Введите максимальное количество раз, которое вы хотите, чтобы Excel пересчитал формулу, в поле «Максимальное количество итераций» и максимальное количество изменений, которое вы допустите, прежде чем Excel необходимо будет снова пересчитать в разделе «Максимальное изменение».

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

Источник: ru.halvescoinsmoney.com

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

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

Формула =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 сохраняет значение из последнего успешного вычисления.

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

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

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

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

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

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

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

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

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

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

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

Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию формулы .

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Циклические ссылки в excel

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-training.ru

Работа с циклическими ссылками в Excel

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

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

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

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

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

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

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

Устраняем циклические ссылки

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

Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.

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

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

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

Для включения функции следует открыть параметры программы и в разделе «Формулы» активировать «Включить итеративные вычисления».

Заключение

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

Источник: tehno-bum.ru

Excel как удалить циклические ссылки в

когда открываю файл с данными пишет “Предупреждение о циклической ссылке”. как его убрать?? надоел уже каждый раз выходит!

И при закрити документа пишет сохранить файл? я ничего не делал просто открыл файл при закритии сочет сохранить файл.

ни где не могу найти ответа спасибо_)

когда открываю файл с данными пишет “Предупреждение о циклической ссылке”. как его убрать?? надоел уже каждый раз выходит!

И при закрити документа пишет сохранить файл? я ничего не делал просто открыл файл при закритии сочет сохранить файл.

ни где не могу найти ответа спасибо_) art22

Сообщение когда открываю файл с данными пишет “Предупреждение о циклической ссылке”. как его убрать?? надоел уже каждый раз выходит!

И при закрити документа пишет сохранить файл? я ничего не делал просто открыл файл при закритии сочет сохранить файл.

ни где не могу найти ответа спасибо_) Автор – art22Дата добавления – 22.03.2018 в 20:36

glingДата: Четверг, 22.03.2018, 22:27 | Сообщение № 2
ЯД-41001506838083

Ответить

art22Дата: Пятница, 23.03.2018, 10:10 | Сообщение № 3

Спасибо огромное!) Оказывается на 2 -3 страницах я сохранял какие то цифры. из-за них было.

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

Спасибо огромное!) Оказывается на 2 -3 страницах я сохранял какие то цифры. из-за них было.

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

Сообщение Спасибо огромное!) Оказывается на 2 -3 страницах я сохранял какие то цифры. из-за них было.

Еще через программу сохраняет цифры в ексел, и выхрдят такие предупреждения. в настройках я отключал их но все равно эти ошибки существуют можно как то сделать так чтобы было все равно число или текст в ячейке. Автор – art22Дата добавления – 23.03.2018 в 10:10

Источник: www.excelworld.ru

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

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

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

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

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

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

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

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

    =A1

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

  2. Создание простейшей циклической ссылки в Microsoft Excel

  3. После этого появляется диалоговое окно предупреждения о циклическом выражении. Щелкаем в нем по кнопке «OK».
  4. Диалогове окно предупреждения о циклической ссылке в Microsoft Excel

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

Ячейка ссылается сама на себя в Microsoft Excel

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

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

  3. В другую ячейку (B1) записываем выражение:

    =C1

  4. Ссылка в ячейке в Microsoft Excel

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

    =A1

  6. Одна ячейка ссылается на другую в Microsoft Excel

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

    =B1

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

  8. Установка ссылки в ячейке в Microsoft Excel

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

Пометка циклической связи в Microsoft Excel

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

Расчет выручки в таблице в Microsoft Excel

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

    =D2/C2

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

  2. Вставка циклической ссылки в таблицу в Microsoft Excel

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

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

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

Циклические ссылки скопированы в таблице в Microsoft Excel

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

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

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

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

Переход к ячейке с циклической ссылкой в Microsoft Excel

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

Сообщение о циклической ссылке на панели состояния в Microsoft Excel

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

Циклическая ссылка на другом листе в Microsoft Excel

Урок: Как найти циклические ссылки в Excel

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

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

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

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

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

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

    Циклическая ссылка в ячейке таблицы в Microsoft Excel

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

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

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

  7. Ссылка заменена на значения в Microsoft Excel

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

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

Циклических ссылок в книге нет в Microsoft Excel

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

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

Блокировка циклических ссылок в Microsoft Excel

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

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

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

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

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

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

  8. Включение итеративных вычислений в Microsoft Excel

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

Ячейки с циклическими формулами отображают корректные значения в Microsoft Excel

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

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

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

Наши специалисты постараются ответить максимально быстро.

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

ДА НЕТ

Excel как удалить циклические ссылки в

Работа с циклическими ссылками в Excel

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

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

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

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

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

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

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

Устраняем циклические ссылки

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

Например, эта формула не является работоспособной поскольку находится в ячейке D3 и ссылается на себя же. Ее нужно вырезать из этой ячейки, выделив формулу в строке формул и нажав CTRL+X, после чего вставить в другой ячейке, выделив ее и нажав CTRL+V.

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

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

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

Для включения функции следует открыть параметры программы и в разделе «Формулы» активировать «Включить итеративные вычисления».

Заключение

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

Как найти и удалить циклическую ссылку в Excel

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

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

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

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

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

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

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

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

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

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

Формула =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 сохраняет значение из последнего успешного вычисления.

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

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

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

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

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

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

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

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

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

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

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

Если вы работаете в Excel 2010 или более поздней версии, последовательно выберите элементы Файл > Параметры > Формулы. Если вы работаете в Excel для Mac, откройте меню Excel, выберите пункт Настройки и щелкните элемент Вычисление.

Если вы используете Excel 2007, нажмите кнопку Microsoft Office , щелкните Параметры Excelи выберите категорию формулы .

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

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

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

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Как найти и убрать циклические ссылки в Excel?

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

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

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

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

Также сообщение о наличие циклических ссылок в документе находится на нижней панели.

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

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

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

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

Еще много полезных статей о редакторе Excel:

Циклическая ссылка в Excel как убрать

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

Вставка ссылки в ячейку

Ссылка на сайт

  1. Выделите нужную клетку. Можете оставить её пустой или что-то написать. Текст вы всегда сумеете удалить.
  2. Кликните по ней правой кнопкой мыши.
  3. Строка «Добавить гиперссылку» («Hyperlink»). Аналогичная кнопка есть в меню «Вставить».
  4. В блоке «Связать» нажмите «Веб-страница» («Web Page»).
  5. Чуть правее кликните на «Просмотренные страницы» и выберите нужную.
  6. Если вы хотите задать URL сайта, который не посещали до этого, напишите его в поле «Адрес» («Asdress»).
  7. В пункте «Замещающий текст» укажите то, что должно замещать гиперссылку. Эта фраза отобразится в ячейке. При клике на неё откроется веб-страница. Можете оставить поле пустым.
  8. Кнопка «Подсказка» («ScreenTip») служит для создания всплывающих подсказок Excel.

Ссылка на файл

  1. Клик правой кнопкой мыши — Добавить гиперссылку.
  2. В разделе «Связать» отметьте «Файл».
  3. Отобразится папка, в которой сейчас находится документ.
  4. Кнопка «Недавние файлы» откроет список данных, которые вы использовали в последнее время. Их нельзя удалить из этого раздела.
  5. Если и там нет того, что вы ищите, задайте путь самостоятельно. Для этого кликните «Текущая папка». Под замещающим текстом будет выпадающий список. В нём выберите каталог.
  6. Задать путь можно и при помощи кнопки «Найти». Она находится чуть правее выпадающего списка.

В Excel можно сослаться на ещё несуществующий документ и сразу его создать.

Когда вы нажмёте на ячейку, к которой привязаны данные на компьютере, система безопасности Excel выдаст предупреждение. Оно сообщает о том, что вы открываете сторонний файл, и он может быть ненадёжным. Это стандартное оповещение. Если вы уверены в данных, с которыми работаете, в диалоговом окне на вопрос «Продолжить?» ответьте «Да».

Можно связать ячейку с e-mail. Тогда при клике на неё откроется ваш почтовый клиент, и в поле «Кому» уже будет введён адрес.

Ссылка на другую ячейку

  1. В разделе «Связать» нажмите «Местом в документе» («Place in This Document»).
  2. В области «Выберите место в документе» отметьте желаемый лист.
  3. В «Адрес» введите название ячейки. Оно состоит из буквы, обозначающей столбец, и номера строки. Чтобы узнать это название, кликните на нужную клетку. Оно отобразится в специальном поле слева вверху.
  4. Нажмите «OK».

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

После этого сошлитесь на диапазон так же, как на клетку.

Вот как сделать гиперссылку в Excel на другую таблицу:

Так можно сделать связь не со всем файлом, а с конкретным местом в файле.

Циклические ссылки

Для начала такие объекты нужно найти.

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

Вот как удалить гиперссылку в Excel, оставив текст, отредактировать её, или вовсе стереть:

Как изменить цвет и убрать подчёркивание?

  1. Выделите клетку.
  2. На панели инструментов во вкладке «Меню» (в Office 2007 «Главная») отыщите букву «Ч» с чёрточкой внизу. Она отвечает за подчёркивание.
  3. На этой же панели надо найти кнопку с пиктограммой в виде символа «А» с полоской под ним.

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

Циклические ссылки в excel

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 и т. д.

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

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

Формула =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 отображает в ячейке нулевые значения или последнее вычисляемого значения. А теперь вы, вероятно, сказали: "Повесить трубку, последнее вычисляемая величина?" Да. В некоторых случаях формула может выполниться успешно, прежде чем она пытается вычислить себя. Например, формула, использующая функцию ЕСЛИ, может работать, пока пользователь не вложит аргумент (часть данных, которая должна правильно работать) для вычисления формулы. В этом случае Excel сохраняет значение последнего успешного вычисления.

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

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

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

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

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

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

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

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

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

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

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

    Если вы используете Excel 2007, нажмите Microsoft Office кнопку Изображение кнопки Office , выберите "Параметры Excel"и щелкните категорию "Формулы".

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

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

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

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

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

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

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

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

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

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

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

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

Также сообщение о наличие циклических ссылок в документе находится на нижней панели.

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

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

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

Выводы

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

Еще много полезных статей о редакторе Excel:

Как импортировать данные из Excel в Word.

Excel Online — всё то же, только в браузере.

Функция ВПР в EXCEL: описание и синтаксис.

Создание календаря в Excel.

Циклические ссылки в excel

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

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

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

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

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

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

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

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

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

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

https://youtube.com/watch?v=-TxB1DIEY8g

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Циклические ссылки и нестирающиеся формулы (Формулы/Formulas)

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

в​ с определенными условиями.​ – в ячейке​(на рисунке обведена​ формулой будет стоять​ быть вообще не​Скачать последнюю версию​​ пересчета листа.​ приложение Excel прекратит​Внимание!​ сообщение «Циклические ссылки»​ собственную ячейку. Например,​Мне это нужно​ проблема

Если я​ (if) адреса ячейки,​​ В контекстном меню​

​ найти циклическую ссылку.​​ настройках я отключал​ Смотрите статью «Функция​

​ Е43 стоит ссылка​​ красным цветом).​​ зеленый треугольник.​ активен.​​ Excel​В поле​ вычисление после 100​

​    Во многих случаях​​ без адресов ячеек.​ ячейка A3 может​ для расчета сторон,​ в ячейке A2​​ которую изменили. По​ выбрать «Исходный текст»​ Удачи.​

​ их но все​​ «СЧЁТЕСЛИ» в Excel».​ на ячейку Е43​Получилось так.​Есть несколько способов​Существует и другой способ​Если в книге присутствует​Относительная погрешность​ итераций либо после​ при создании дополнительных​Вы можете перемещаться по​ содержать формулу =(A1+A2)/A3.​ высот, углов геометрических​​ вбиваю все руками,​

​ результату проверки выбираем​​Откроется редактор VBA.​Не нажимать​

​ равно эти ошибки​​art22​ (саму на себя).​Чтобы​проверить поэтапный расчет формул​ определения подобных нежелательных​ циклическая ссылка, то​введите наименьшее значение,​

​ того, как изменение​​ формул с циклическими​ ​ всех значений в​​ ссылками предупреждающее сообщение​ ссылке с помощью​ =A1+1 (в ячейке​Там много используется​​ нормально. Но если​​ чего вычитаем и​ увидите макрос.​​ скопируй туда свои​​ то сделать так​​ с данными пишет​ – изменили ссылку​ результат в ячейках​.​В диалоговом окне, сообщающем​ файла программа в​ следует продолжать итерации.​

​ циклической ссылке с​​ в приложении Excel​ двойных щелчков по​ A1) также приводят​

​ тригонометрических формул Excel.​​ ячейка А2 рассчитывается​ куда заносим.​kontrabasss​

​ данные, циклическую ссылку​​ чтобы было все​​ «Предупреждение о циклической​ в формуле с​ Excel​Итак, заходим на​ о наличии циклических​

​ диалоговом окне предупредит​​ Это наименьшее приращение​ каждой итерацией составит​

​ больше не отображается.​​ стрелке трассировки, которая​ к ошибкам циклической​Например можно построить​

​ — не копируй,​​ равно число или​ ссылке»… как его​ ячейки Е43 на​

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

​gling​​ каждый раз выходит!​ так.​Если пишем много​​ Сначала выделяем ячейку,​​«OK»​ определением самого наличия​​ число, тем точнее​ какое из этих​ ситуации, в которых​ в текущей ячейке.​ Они возникают, когда​ по двум и​​: А еще стало​ провалишься​

​ просто скопировать и​​: Здравствуйте. Все никак​: Можно при расчете​

​И при закрити​​Все исправили, все считает​ разных формул, то​ затем нажимаем кнопку​

​.​​ такой формулы проблем​

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

excelworld.ru>

​ функция у Range?​

  • Excel ссылка на другой файл excel
  • Excel ссылка на ячейку в другом файле
  • Excel ссылка на ячейку в другом листе
  • Ссылку в текст в excel
  • Как в excel обновить ссылки
  • Как в excel сделать ссылку на ячейку другого листа
  • Как найти циклические ссылки в excel
  • Как в excel убрать ссылки
  • Excel ссылка на лист
  • Excel ссылка на ячейку на другом листе
  • Для чего в excel используют абсолютные ссылки в формулах
  • Excel ссылка на другой файл

Как найти и удалить циклическую ссылку в Excel

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

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

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; ), где

  • ключ — обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон — обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца — обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр — необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ — функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА — функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

Пример использования:

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

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

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж № 2», хотя в первой таблице нет категории «Лук»

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

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы — * (звездочка — любое количество любых символов) и ? (один любой символ). Например, для искомого значения »*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах — =ВПР(1;{2;»Два»:1;»Один«};2;ЛОЖЬ) — результат выполнения строка «Два».

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

​ Excel автоматически блокируют​Brownie​ страницах я сохранял​ выделенные красным нормальные​ быть ошибки в​ и нажмите ​C1​

  1. ​ бы заблокировано программой.​​ необходимости или при​​ неосознанно вы используете​ циклические ссылки были​​Тут взаимосвязь мы вычислили​​), реальная ошибка кроется​ окна Excel. Правда,​ Учитывая тот факт,​

​) вписываем туда формулу,​​ формулы:​ процесс выполнения цикличной​: Привет! Просто какой-то​

  1. ​ какие то цифры…​ формулы внесите.​​ расчетах, если циклические​​Circular References​.​​Как мы видим, в​​ желании можно внести​

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

  3. ​ что в подавляющем​​ которая будет считать​​=A1​​ операции. Это связано​​ ячейке в формуле​

  4. ​ из-за них было…​Файл удален​ сделаны не специально.​​(Циклические ссылки).​​Формула в ячейке​

    ​ подавляющем большинстве случаев,​

    • ​ изменения в указанные​​ по умолчанию все​​ сообщение о наличие​​ в реальности бывают​​ Выделяем элемент​
    • ​ предыдущего варианта, на​​ большинстве цикличные операции​​ количество товара путем​​После этого возвращаемся в​
    • ​ с тем, что​​ ссылка на саму​​Еще через программу​​- велик размер.​
    • ​Ищите ошибки в​​Урок подготовлен для Вас​​C3​​ циклические ссылки представляют​
    • ​ поля. Но тут​ равно будет блокировать​​ данной проблемы должно​​ случаи, когда в​D6​ строке состояния отображаться​

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

  1. ​ себя. Открой Вид​ сохраняет цифры в​ ​​ формулах.​​ командой сайта office-guru.ru​ссылается на ячейку​ собой явление, с​​ нужно учесть, что​​ операцию по ним,​ исчезнуть из строки​​ процессе вычисления участвует​​, чтобы узнать, из​

​ будут адреса не​ удалить. Но для​​ (​​A1​

​ подавляющем большинстве ошибочные,​

office-guru.ru>

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

​ в ней циклическую​Если есть подозрение, что​ трассировки, нажмите кнопку​ Если вам не​ ниже способов.​Вы ввели формулу, но​ существуют можно как​ ошибок—Источник ошибки. Должна​Формулы, в которых циклические​Шаг с выходом​​ Если (аргумент лог_выражение)​​ конечный результат сложно​Formulas​С3​ второй — значение 10,​​Использовать итеративное вычисление​​ условий будет выполнено​

​ ссылку.​ циклическая ссылка содержится​Влияющие ячейки​ удается определить, является​Если вы только что​ она не работает.​ то сделать так​

​ быть прописана ячейка​ ссылки не может​, чтобы вернуться к​

​ имеет значение false.​ за несколько промежуточных​(Формулы) кликните по​.​ после третьей — снова​.​ раньше). Тем не​Иногда вам могут потребоваться​ в ячейке, которая​или​ ли эта ячейка​ ввели формулу, сначала​ Вместо этого появляется​ чтобы было все​

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

  • ​Зависимые ячейки​ причиной циклической ссылки,​​ проверьте ячейку, в​​ сообщение о «циклической​ равно число или​​ Исправьте ошибку, чтобы​​ При желании циклические​​ формуле.​​Функция Если возвращает значение​ поверки. Тем не​

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

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

​ третьего аргумента (аргумент​​ менее используя диалоговое​

  • ​Error Checking​ ячейке​Формула в ячейке, которая​​введите количество итераций​​ число итераций и​ функции выполнять итерации,​ решение:​

    ​Формулы​Циклические ссылки​ Проверьте, не ссылается​ сталкиваются с этой​gling​ т.е. в расчете​

  • ​ итеративные вычисления.​ Кнопка​ значение_если_ложь). Функция сумм​ окно «​(Проверка наличия ошибок)​C1​ прямо или косвенно​ для выполнения при​ относительную погрешность.​ т. е. повторять​​Щелкните формулу в строке​​.​​следующую ячейку.​​ ли формула на​​ проблемой. Это происходит,​​: Можно при расчете​

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

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

​ ссылается на эту​ обработке формул. Чем​Если вы работаете в​ вычисления до тех​ формулы и нажмите​Когда Excel впервые находит​Продолжайте находить и исправлять​ собственную ячейку. Например,​ когда формула пытается​ формулы приводить значение​ участвовать результирующая ячейка.​ раз изменения листа​недоступна для ссылки,​ она является вторым​», вы увидите​Circular References​Пояснение:​ же самую ячейку,​ больше предельное число​ Excel 2010 или более​ пор, пока не​ клавишу ВВОД.​ циклическую ссылку, отображается​ циклические ссылки в​

​ ячейка A3 может​ посчитать собственную ячейку​ ячейки либо к​ Сохранение файла может​ и может вызвать​ если ссылка используется​

​ аргументом функции Если​ различные части вложенной​(Циклические ссылки).​

​Ячейка​​ называется циклической ссылкой.​ итераций, тем больше​ поздней версии, последовательно​ будет выполнено заданное​Внимание!​ предупреждающее сообщение. Нажмите​ книге, повторяя действия​ содержать формулу =(A1+A2)/A3.​ при отключенной функции​ числу либо к​

  • ​ требоваться если в​ средство​ в формуле во​

  • ​ (за аргументом значение_если_истина​ формулы в порядке,​Урок подготовлен для Вас​С1​ Такого быть не​

  • ​ времени потребуется для​ выберите элементы​ числовое условие. Это​    Во многих случаях​ кнопку​

  • ​ 1–3, пока из​ Такие формулы, как​

  • ​ итеративных вычислений. Вот​ тексту, но для​ формулах файла есть​Вычислить формулу​ второй раз или​

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

​ следует) и возвращается​ в котором вычисляется​ командой сайта office-guru.ru​ссылается на ячейку​ может!​ пересчета листа.​Файл​ может привести к​ при создании дополнительных​ОК​ строки состояния не​ =A1+1 (в ячейке​ как это выглядит:​ того чтобы показать,​

​ данных и тогда​ пересчитываются, от сюда​

  • ​ СЛУЧМЕЖДУ, сведения и​ все части формулы.​ раз можно вычислить​Шаги, показанные в диалоговом​

  • ​ можно оперативнее обеспечивать​.​(т.е. на саму​ Это наименьшее приращение​ в Excel для​

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

​ возникает и вопрос​

support.office.com>

Разновидности ссылок

Ссылки делятся две основные категории:

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

Также, все ссылки делятся на внутренние и внешние, и их применяют в разных случаях:

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

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

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

​ помогли.​ Office Excel автоматически​ — тогда поймёте,​ Включить итеративные вычисления​ от оборота, оборот​operkote​ помогла ли она​

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

​ не равно 20,​​ помощь.​AndreTM​ пересчитывает формулы только​

  1. ​ где создали проблему).​ЗЫ: Почему у​​ от доходов за​​: Добрый вечер. Помогите,​ вам, с помощью​​C2​​ называется циклической ссылкой.​

  2. ​Включить итеративные вычисления​​ Если включить итеративные​​ в приложении Excel​​Зависимые ячейки​​щелкните стрелку рядом​

  3. ​ в другую ячейку​​ 25 или 30,​​adilkarim​​: Я не понимайн​​ при изменении влияющих​

  4. ​Посмотрите, где необходимо​ Вас в столбце​ день, доходы за​​ пожалуйста.​​ кнопок внизу страницы.​

    ​.​

    • ​ Такого быть не​​. На компьютере Mac​​ вычисления, не изменив​​ больше не отображается.​​на вкладке​
    • ​ с кнопкой​​ (в строке формул​​ то возвращается то​​: ДД.​
    • ​ Даже если я​​ на формулу ячеек.​​ использовать именно СУММПРОИЗВ(),​​ «оборот за предыдущий​
    • ​ день от доходов​​Ситуация такая: есть​​ Для удобства также​​С2​
    • ​ может!​ щелкните​​ предельное число итераций​​ Ниже перечислены некоторые,​Формулы​Проверка ошибок​

​ нажмите сочетание клавиш​​ число, которое написано​При каждом открытии​ разделю доход на​

  1. ​ Это является стандартной​ а где достаточно​ месяц» суммируются данные​​ от сделок, а​​ таблица 1 (таблица​ приводим ссылку на​ссылается на ячейку​​Например, формула в ячейке​​Использовать итеративное вычисление​ и относительную погрешность,​​ но не все,​​.​

​, выберите пункт​CTRL+X​​ в В1, а​​ документа выскакивает ошибка​

​ две части, то​

office-guru.ru>

Циклические ссылки

​, расположенной в блоке​​):​​A1​ так, но все-таки​​«Проверка наличия ошибок»​ скорее всего.​ ячейки либо к​ выделенные красным нормальные​Guest​Продолжайте этот процесс, пока​​Сначала выводится вложенная формула.​Другими словами, формула в​ и производиться пользователем​ параметры изменять не​ способом.​Теперь нам нужно понять,​В нашем случае, несмотря​ инструментов​=D2/C2​, а число​ не всегда. Иногда​. Кликаем на пиктограмму​Pelena​ числу либо к​​ формулы внесите.​: а если не​ не будут вычислены​ Функции СРЗНАЧ и​

​ ячейке​​ осознанно. Но даже​ нужно, хотя при​

​В предшествующей части урока​​ в какой именно​ на то, что​«Зависимости формул»​Щелкаем по кнопке​

​5​​ они применяются вполне​ в виде перевернутого​: Именно в этой​ тексту, но для​Файл удален​ обновлять?​ все части формулы.​ СУММ вложены в​C1​ тогда стоит к​ необходимости или при​ мы рассказывали, в​​ ячейке (​ программа верно указала​

​. Открывается меню, в​​Enter​.​ осознанно. Давайте выясним,​ треугольника рядом с​ ячейке формула с​ того чтобы показать,​

​- велик размер.​​Guest​​Чтобы посмотреть вычисление еще​ функцию ЕСЛИ.​косвенно ссылается на​ их использованию подходить​ желании можно внести​ основном, как бороться​​B6​ на одну из​ котором следует навести​.​​В другую ячейку (​ чем же являются​ этой кнопкой. В​ циклической ссылкой​ нужен файл в​ ​​: если «не обновлять»,​ раз, нажмите кнопку​

​=ЕСЛИ(​​ саму себя, чего​

​ с осторожностью, правильно​​ изменения в указанные​

​ с циклическими ссылками,​или​ ячеек цикла (​

​ курсор на пункт​​У нас получилась первая​B1​ циклические ссылки, как​ появившемся меню выбираем​Vmir​ формате Exsel, а​​art22​​ все равно выходит​Начать сначала​

​40​​ быть не должно.​​ настроив Excel и​

​ поля. Но тут​​ или как их​D6​D6​​«Циклические ссылки»​​ циклическая ссылка, взаимосвязь​) записываем выражение:​

planetaexcel.ru>

Использование оператора ДВССЫЛ

Помимо использования формул в Excel есть возможность использовать специальный оператор ДВССЫЛ для того, чтобы создать ссылку. Формула оператора выглядит следующим образом:

, где:

  1. “Ссылка_на_ячейку” – аргумент, который который содержит адрес ячейки (указывается в кавычках).
  2. “A1” – определяет стиль используемых координат:
    • “Истина” – для стиля A1
    • “Ложь” – для стиля R1C1
    • при незаполненном значении будет применяться стиль A1.

Давайте теперь попробуем применить функцию на практике.

  1. Выделяем нужную ячейку и кликаем по значку “Вставить функцию” (рядом со строкой формул).
  2. Откроется окно мастера функций. Кликаем по текущей категории и в раскрывшемся перечне выбираем строку “Ссылки и массивы”.
  3. В предложенном списке операторов выбираем ДВССЫЛ и жмем кнопку OK.
  4. Перед нами появится окно для редактирования аргументов функци, после заполнения которых нажимаем OK.
    • ставим курсор в область “Ссылка_на_ячейку”, затем пишем вручную адрес ячейки, на которую планируем сослаться. Также можно вместо ручного прописывания координат просто кликнуть по требуемому элементу.
    • в поле “A1” можем написать одно из двух значений, рассмотренных выше, либо оставить его незаполненным.
  5. В выбранной ячейке появится результат согласно заданным нами настройкам.

Предупреждение о циклической ссылке как убрать? (Формулы/Formulas)

​ их создавать, как​​ пункт​: Гуру подскажите как​ не картинка. Нужно​: когда открываю файл​ сообщение о циклических​.​​>50;СУММ(G2:G5);0)​Примечание:​ зная меру в​ нужно учесть, что​ найти. Но, ранее​) содержится ошибка. Хотя,​), реальная ошибка кроется​​. После этого в​ в которой привычно​=C1​

​ отыскать уже существующие​​«Циклические ссылки»​ мне эту ячейку​ видеть реальный формат​ с данными пишет​ ссылках.​Чтобы закончить вычисление, нажмите​Диапазон ячеек F2: F5​Если вы создадите​ добавлении подобных ссылок,​ слишком большое количество​ разговор шел также​ формально это даже​ в другой ячейке.​ следующем меню открывается​ обозначена стрелкой трассировки.​В следующий элемент (​ в документе, как​. После перехода по​ то найти?​ данных и тогда​ «Предупреждение о циклической​vikttur​ кнопку​ содержит значения 55,​ такую циклическую ссылку,​ которые при использовании​ итераций может привести​ о том, что​

​ не ошибка, а​​ Выделяем элемент​ список адресов элементов​ Но как видим,​C1​ работать с ними​​ этой надписи в​Pelena​ определить к чему​ ссылке»… как его​: Циклическая ссылка –​Закрыть​ 35, 45 и​ Excel вернёт «0».​ в массовом количестве​ к серьезной нагрузке​ в некоторых случаях​ просто избыточное использование​

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

excelworld.ru>

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

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

Как отключить циклические ссылки в Excel (Эксель)

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

Как отключить циклические ссылки в Excel (Эксель)

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

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

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

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

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

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

ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Окно оповещения о наличии циклических ссылок в таблице

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

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

  1. Когда появится предупреждающее окно, необходимо закрыть его нажатием кнопки «ОК».
  2. Программа автоматически обозначит те ячейки, между которыми возникла конфликтная ситуация. Они будет подсвечены специальной стрелкой трассировки.
ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Обозначение проблемных ячеек стрелкой трассировки
  1. Чтобы убрать цикличность, необходимо зайти в обозначенную ячейку и исправить формулу. Для этого необходимо убрать координаты конфликтной клетки из общей формулы.
  2. Останется перевести курсор мыши на любую свободную ячейку таблицы, нажать ЛКМ. Циклическая ссылка будет удалена.
ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Исправленный вариант после удаления циклической ссылки

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

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

  1. В первую очередь нужно закрыть окно с предупреждением.
  2. Перейти на вкладку «Формулы» на основной панели инструментов.
  3. Зайти в раздел «Зависимости формул».
  4. Найти кнопку «Проверка ошибок». Если окно программы находится в сжатом формате, данная кнопка будет обозначена восклицательным знаком. Рядом с ней должен находиться маленький треугольник, который направлен вниз. Нужно нажать на него, чтобы появился список команд.
ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Меню для отображения всех циклических ссылок с их координатами по ячейкам
  1. Из списка выбрать «Циклические ссылки».
  2. Выполнив все описанные выше действия, перед пользователем появится полный список с ячейками, которые содержат циклические ссылки. Для того чтобы понять, где точно находится данная клетка, нужно найти ее в списке, кликнуть по ней левой кнопкой мыши. Программа автоматически перенаправит пользователя в то место, где возник конфликт.
  3. Далее необходимо исправить ошибку для каждой проблемной ячейки, как описывалось в первом способе. Когда конфликтные координаты будут удалены из всех формул, которые есть в списке ошибок, необходимо выполнить заключительную проверку. Для этого возле кнопки «Проверка ошибок» нужно открыть список команд. Если пункт «Циклические ссылки» не будет показан как активный – ошибок нет.
ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Если ошибок нет, пункт поиска циклических ссылок выбрать нельзя

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

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

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

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

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

ciklicheskaya-ssylka-v-excel-kak-najti-i-udalit-2-sposoba
Окно настроек блокиратора циклических ссылок, их допустимого количества в документе
  1. Чтобы изменения вступили в силу, необходимо нажат на кнопку «ОК». После этого программа перестанет автоматически блокировать вычисления в ячейках, которые связаны циклическими ссылками.

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

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

Заключение

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

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

Добавить комментарий