0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как быстро найти и найти круговую ссылку в Excel?

Как быстро найти и найти круговую ссылку в Excel?

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

Ячейки, на которые ссылается формула, являются ячейками. Если ячейка D10 содержит формулу =B5, то ячейка B5 затрагивается ячейкой D10.

Зависимыми называются ячейки, когда формулы в одной ячейке ссылаются на формулы в другой ячейке. Например, ячейка D10 является зависимой от ячейки B5, если она содержит формулу =B5.

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

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

Перейдите в меню Файл > Параметры > Дополнительные параметры.

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

Отметьте все в разделе Для объектов, покажите в разделе Показать параметры для этой книги.

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

Сделайте одно из следующих действий.

Укажите ячейку, содержащую формулу, для которой нужно найти влияющие ячейки.

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

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

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

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

Читайте так же:
Как ввести / отобразить текст или сообщение, если в Excel ячейки пусты?

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

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

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

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

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

Введите знак = (знак равенства) в пустой ячейке.

Нажмите кнопку «Выделить все».

Выделяется ячейка и дважды нажимается кнопка Трассировка на вкладке Формулы в группе Формулы отношений на вкладке Формулы.

Чтобы удалить все стрелки отслеживания, на вкладке Формулы группы Зависимости формул нажмите кнопку Удалить стрелки.

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

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

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

Отчеты, которые будут представлены в отчетах.

Ссылки на именованные константы.

Формулы в другой книге, которые ссылаются на активную ячейку, если другая книга закрыта.

Чтобы ячейки, на которые влияют аргументы формулы, были выделены цветом, выделите ячейку и нажмите на клавишу F2.

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

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

Читайте так же:
Как быстро вставить несколько флажков в Excel?

Как быстро найти и найти круговую ссылку в Excel?

В этой статье описывается синтаксис формул и использование функций Найти и НайтиВ в Microsoft Excel.

Описание

Функции SEARCH и SEARCHB находят вхождение одной текстовой строки в другую и возвращают начальную позицию искомой строки относительно первого символа другой строки.

Эти функции поддерживаются не во всех языках.

Функция SEARCH предназначена для однобайтовых языков, а функция SEARCHIB — для двухбайтовых. Язык по умолчанию, установленный на компьютере, влияет на возвращаемое значение, как показано ниже.

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

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

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

Синтаксис

Ниже приводится список аргументов функций Find и findb.

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

Browseable_text — обязательный аргумент. В тексте вы пытаетесь найти искомый текст.

Позиция_начала — необязательный аргумент. Символ, с которого должен начинаться поиск. Первым символом в «viewable_text» является 1. Если число опущено, оно принимается за 1.

Замечания

Подстановочные знаки не допускаются в функциях Find и FindB. Вы можете использовать функции ПОИСКПОЗ или ПОИСКВ для поиска без учета регистра или для использования подстановочных знаков.

Функция ПОИСКПОЗ возвращает значение 1, если аргумент «искомый_текст» пуст (Первым символом в строке поиска будет символ с номером, соответствующим аргументу «начальная_позиция»).

Текст, который вы ищете, не может содержать никаких символов подстановки.

Find и FINDB возвращают #VALUE, если не отображаются в within_text! значение ошибки #VALUE!

Find и findB возвращают #VALUE, когда start_num меньше нуля. значение ошибки — #VALUE!

Читайте так же:
Как автоматически вставлять метку времени при обновлении данных в другом столбце на листе Google?

В этом случае search и findb возвращают #VALUE, если begin_num больше within_text! значение ошибки #VALUE!

Используя аргумент «start_position», можно пропустить нужное количество символов. Предположим, например, что функция Find используется для поиска строки «MDS0093. MonthlySales». Чтобы найти номер первого вхождения буквы «M» в описательной части текстовой строки, установите «start_position» равным 8, чтобы поиск не производился в той части текста, которая содержит серийные номера. Функция ПОИСКПОЗ начнет с символа 8, найдет искомый_текст в следующем символе и вернет число 9. Если «просмотренный_текст» задает номер символа, то отсчет всегда ведется от левого края текста, а не от позиции аргумента «start_position».

Примеры

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

Как быстро найти и найти круговую ссылку в Excel?

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

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

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

Еще одна распространенная ошибка — использование функций, содержащих ссылки на самих себя, например, ячейка F3 может содержать формулу =SUM(A3:F3). Пример:

Браузер не поддерживает видео.

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

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

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

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

Читайте так же:
Как быстро вставить диагональную линию в ячейку?

Команда "Циклические ссылки"

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

Продолжайте поиск и исправление циклических ссылок в книге, повторяя шаги 1-3, пока сообщение «Cyclic References» не исчезнет из строки статуса.

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

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

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

Влияющие ячейки

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

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

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

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

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

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

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

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

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

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

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

Читайте так же:
Как автоматически пронумеровать объединенные ячейки в Excel?

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

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

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

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

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

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

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

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

Итеративное вычисление может привести к трем результатам:

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

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

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

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

Вы всегда можете задать вопрос сообществу Excel Tech или обратиться за помощью в сообщество «Ответы».

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector