Как убрать циклическую ссылку в экселе: Как в excel убрать циклическую ссылку

19.03.2021
Комментариев нет

Содержание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Это может быть интересно:

Рекомендуем почитать:

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

Работа с циклическими ссылками в 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 как убрать

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Циклические ссылки — Хитрости работы с Excel – таблицы, функции, формулы, списки, рабочие книги, макросы

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

    Чтобы выявить циклическую ссылку:

    Нажмите вкладку Формулы

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

    Просмотрите формулу ячейки.

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

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

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

    Чтобы ввести функцию:

    Щелкните ячейку, куда вы хотите ввести функцию.

    Введите знак равенства (=), введите имя функции, а затем откройте скобку.

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

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

    Нажмите кнопку Ввод в строке формул или нажмите клавишу Enter

    Excel автоматически закроет скобку, чтобы завершить ввод функции.

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

    Чтобы ввести функцию при помощи команды Вставить функцию:

    Щелкните ячейку, в. которую вы хотите ввести функцию.

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

    Введите краткое описание нужной вам функции в окно поиска и нажмите Найти

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

    Выберите функцию, которую вы хотите использовать.

    Нажмите ОК

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

    Нажмите ОК.

    Newer news items:

    Older news items:

    Циклические ссылки в Excel — Excel works!

    Абсолютные и относительные ссылки Excel Как преобразовать текст в число в Excel?

    Для начала давайте разберемся, что собой представляют циклические ссылки в Excel. Как их можно найти на листе редактора? Чем они плохи и чем хороши?


    Теория

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

    К примеру, в формуле участвует результат вычисления этой формулы или ячейка =A3=B3, =B3=C3, =C3=A3. В результате A3 ссылается на A3. То есть циклическая зависимость. Пример на картинке показан для режима Формулы — Показать формулы

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

    Обозначение Ц/С в Excel
    Появление Ц/С довольно просто распознать. Во время их появления или присутствия в уже созданном файле Excel тотчас же возникает окно с сообщением (как на первой картинке). Если нажать на кнопку ОК, то предупреждение будет скрыто, а в ячейке, которая содержит Ц/С отобразится значение 0.
    Предупреждение возникает при изначальном появлении Ц/С, либо же открытии книги, которая её содержит (частая проблема от многих пользователей). Если вы принимаете предупреждение программы, то далее при наличии Ц/С оно может и не появляться.

    Способы найти Ц/С
    Ц/С в редакторе Eсxel могут быть созданы как преднамеренно для того, чтобы решить те или иные задачи табличных финансовых моделей (об этом далее), так и возникнуть случайно, как техническая ошибка в формулах их построения.

    Способов нахождения Ц/С существует несколько:

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

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

    Как можно полезно использовать Циклические ссылки в Excel?

    Иногда Ц/С используются в моделировании. Например, при проработке модели бизнес плана или когда нам нужно рассчитать уровень показателя, зависящий напрямую от прибыли. К примеру, вы выплачиваете процент от прибыли своим сотрудникам. Соответственно прибыль зависит от затрат на премию и наоборот. Что делать?

    Включить итеративные вычисления!

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

    Конечно же в Excel есть возможность включить Файл (круглая кнопка в левом верхнем углу) — Параметры — Формулы. Поставить галочку «Включить итеративные вычисления».

    Также необходимо установить предельное число итераций и относительную погрешность ниже,

    Не рекомендую ставить большое кол-во вычислений — система может основательно подвиснуть.

    Поделитесь нашей статьей в ваших соцсетях:

    Похожие статьи

    Абсолютные и относительные ссылки Excel Как преобразовать текст в число в Excel?

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

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

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

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

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

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

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

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

    На этом шаге мы рассмотрим циклические ссылки.

    Иногда при вводе формул на экране может появиться сообщение, подобное показанному на рисунке 1.

    Рис. 1. Excel сообщает о том, что в формуле содержится циклическая ссылка

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

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

    • Щелкнуть на кнопке ОК , чтобы попытаться обнаружить циклическую ссылку.
    • Щелкнуть на кнопке Отмена , чтобы ввести формулу в том виде, в каком она есть.

    Как правило, циклические ссылки являются ошибочными, поэтому нужно щелкнуть на кнопке ОК . В результате Excel отобразит панель инструментов Циклические ссылки (рис. 2).

    Рис. 2. Панель инструментов Циклические ссылки

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

    Если Вы решите игнорировать сообщение о циклической ссылке (щелкнув на кнопке Отмена ), то Excel позволит Вам ввести данную формулу и отобразит в строке состояния сообщение, напоминающее о существовании циклической ссылки. В данном случае это сообщение будет выглядеть так: Цикл: АЗ . Если же Вы активизируете другую рабочую книгу, то сообщение будет состоять только из одного слова Цикл (без указания адреса ячейки).

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

    Рис. 3. Вкладка Вычисления диалогового окна Параметры

    При активизации описываемой опции Excel выполняет циклические вычисления столько раз (делает столько итераций), сколько задано в поле Предельное число итераций , или до тех пор, пока изменение значения не станет меньше, чем 0,001 (либо величины, заданной в поле Относительная погрешность ). Бывают ситуации, когда циклические ссылки используются преднамеренно. В подобных случаях параметр Итерации должен быть выбран.

    Простой пример такой ситуации показан на рисунке 4.

    Рис. 4. Пример преднамеренной циклической ссылки

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

    В ячейке с именем Пожертвования содержится следующая формула: = 5% * Чистый_доход

    В ячейке с именем Чистый_доход находится следующая формула: = Прибыль — Расходы — Пожертвования

    Эти формулы создают разрешимую циклическую ссылку. Excel продолжает вычисления до тех пор, пока результаты формул перестанут изменяться. Чтобы увидеть, как это происходит, введите некоторые значения в ячейки Прибыль и Расходы . Если опция Итерации не активизирована, то Excel выведет на экран сообщение о циклической ссылке, и правильный результат не будет получен. Если же опция Итерации активизирована, то Excel будет продолжать вычисления до тех пор, пока значение Пожертвования не будет составлять 5% от величины Чистый_доход .

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

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

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

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

    Простым примером такой ситуации является следующий вариант:
    — ячейка C3 ссылается на B6
    — ячейка B6 ссылается на D6
    — ячейка D6 ссылается на C3

    Тут найти проблему просто.

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

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

    Выделение группы ячеек

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

    Эта функция расположена на вкладке «Home» в группе «Найти и выделить» — «Выделение группы ячеек».

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

    Отслеживание связей ячейки

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

    Для начала нужно идентифицировать влияющие ячейки.

    — Самый простой способ – установить курсор в ячейку для анализа и нажать кнопку F2. Влияющие ячейки будут выделены тем же цветом, что и формула в активной ячейке.
    — Обозначив активную ячейку, нажать сочетание клавиш Ctrl+[ — будут отмечены все задействованные ячейки
    — Аналогичный вариант — сочетание клавиш Ctrl+Shift+[ — в этом случае на активном листе будут отмечены и прямо, и косвенно влияющие ячейки
    — Выделение группы ячеек по формулам (как описано выше).
    — Функция «Влияющие ячейки» на вкладке «Формула» показывает все задействованные в вычислениях ячейки стрелочками.

    Проверка на ошибки

    Можно воспользоваться штатной функцией Excel версии старше 2010.

    В меню «Формула» есть проверка на наличие ошибок, включая поиск циклических ссылок.

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

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

    Фоновый поиск ошибок

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

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

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

    Это осуществляется в меню «Файл» — «Параметры» в группе настроек «Формулы».

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

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

    Удалить или разрешить круговую ссылку

    Перейти к основному содержанию Microsoft

    Поддержка

    Поддержка

    Поддержка

    • Дом
    • Microsoft 365
    • Офис
    • Windows
    • Поверхность
    • Xbox
    • Сделки
    • Больше
      • Все Microsoft

      Как найти внешние ссылки или ссылки в Excel

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

      Метод 1: Поиск внешних ссылок с помощью функции find:

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

      • Откройте лист Excel, для которого вы хотите найти внешние ссылки.
      • После этого нажмите клавиши «Ctrl + F», чтобы открыть «Найти» и заменить диалоговое окно.
      • В текстовом поле поиска введите строку « [*] » (без кавычек). Эта строка означает, что результатом будет любая строка, заключенная в длинные скобки.
      • Затем в раскрывающемся списке «Искать в» выберите формулы и нажмите кнопку «Найти все».
      • Результатом будет набор внешних ссылок, которые используются на листе.

      Метод 2: Вариант редактирования ссылок:

      На ленте Excel есть вкладка «Данные», внутри этой вкладки есть опция «Редактировать ссылки».

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

      Итак, это может стать быстрой проверкой, чтобы проверить, содержит ли ваш лист Excel внешние ссылки или нет.Использовать «Редактировать ссылки» довольно просто, просто выполните следующие шаги, чтобы удалить внешние ссылки из вашего листа Excel:

      • Откройте свой лист Excel и перейдите на вкладку «Данные», выберите опцию «Редактировать ссылки».
      • В окне «Редактировать ссылки» будут перечислены все электронные таблицы, на которые есть ссылки в вашем файле Excel.
      • В правой части этого окна редактирования ссылок есть такие параметры, как «Обновить значения (можно использовать для повторной загрузки значений)», «Изменить источник (можно использовать для изменения файла, на который указывает ссылка)», «Открыть исходный код (открывает ссылочные файлы Excel) ‘и’ Разрыв ссылок (можно использовать для разрыва ссылочных ссылок) ‘.
      • Среди всех этих опций мы будем использовать вариант «Разорвать ссылки», который разбивает ссылки и заменяет их текущими значениями.
      • Обратите внимание, что использовать эту функцию следует с особой осторожностью, так как это невозможно отменить.

      Метод 3. Поиск ссылок на внешние ссылки с помощью макроса Excel:

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

      • Открыв лист Excel, перейдите на вкладку «Просмотр» и нажмите кнопку «Макросы».
      • Теперь введите имя макроса «Fetch_Links» (без кавычек) и нажмите кнопку «Создать».
      • Откроется редактор Excel VBA, просто вставьте приведенный ниже код после первой строки.
       Dim aLinks As Variant 
       aLinks = ActiveWorkbook.LinkSources (xlExcelLinks) 
       Если не IsEmpty (aLinks), то 
       листов.Добавить 
       For i = 1 To UBound (aLinks) 
       Cells (i, aLinks) 
       Cells (i, a = 1). i) 
       Next i 
       End If
      • Весь код должен выглядеть так же, как показано на скриншоте ниже.
      • Теперь просто нажмите кнопку «F5», чтобы запустить макрос. Код создаст новый рабочий лист, содержащий все внешние ссылки, на которые есть ссылки.

      Метод 4: Надстройка поиска и удаления ссылок:

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

      Вы можете найти эту надстройку здесь.

      Вам также может понравиться

      Как удалить имя ячейки в Excel?

      1. штуковина Тачдаун! Гризер!

        Зарегистрирован:
        28 февраля 2005 г.
        Сообщений:
        12 672
        Расположение:
        Миннеаполис
        Отображаемое имя:
        Показать имя:
        iGismo
        Я изменил имя нескольких ячеек на листе Excel и теперь хочу восстановить имя по умолчанию, но не могу найти способ сделать это.Например, если я выберу ячейку F2, а затем наберу имя Vd в «поле имени» (левый конец строки формул), ячейка F2 теперь получит имя «Vd». Как я могу вернуть имя этой ячейки по умолчанию «F2» ????? Я попытался удалить имя в поле имени с выбранной ячейкой и ввести имя по умолчанию (F2), но, похоже, ни один из них не имеет никакого эффекта. Это Excel 2000.

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

      3. Shywon Контрольный список перед взлетом

        Зарегистрирован:
        13 нояб.2008 г.
        Сообщений:
        129
        Отображаемое имя:
        Показать имя:
        Шивон
        щелкните ячейку….. нажмите вставить, имя, определить …
        в появившемся поле нажмите vd или любое другое его имя и нажмите удалить … tada работала с excel 2003

      4. штуковина Тачдаун! Гризер!

        Зарегистрирован:
        28 февраля 2005 г.
        Сообщений:
        12 672
        Расположение:
        Миннеаполис
        Отображаемое имя:
        Показать имя:
        iGismo

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

        Щелкните, чтобы развернуть …

        Это сработало, спасибо.

      5. Кларк1961 Тачдаун! Гризер!

        Зарегистрирован:
        7 июня 2008 г.