Определите какая ячейка является влияющей для ячейки с2
Перейти к содержимому

Определите какая ячейка является влияющей для ячейки с2

  • автор:

Отображение связей между формулами и ячейками

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

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

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

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

    Щелкните Параметры> файлов >Дополнительно.

Примечание: Если вы используете Excel 2007; Нажмите Кнопку Microsoft Office

, выберите Пункт Параметры Excel, а затем выберите категорию Дополнительно .

Трассировка ячеек, обеспечивающих формулу данными (влияющих ячеек)

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

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

Трассировка формул, ссылающихся на конкретную ячейку (зависимых ячеек)

  1. Укажите ячейку, для которой следует найти зависимые ячейки.
  2. Чтобы отобразить стрелку трассировки для каждой ячейки, зависящей от активной ячейки, на вкладке Формулы в группе Аудит формул щелкните Трассировка зависимых

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

Просмотр всех зависимостей на листе

  1. В пустой ячейке введите = (знак равенства).
  2. Нажмите кнопку Выделить все.

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

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

Если excel сигналит при нажатии кнопки Трассировка зависимых

или Трассировки прецедентов

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

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

Удаление или разрешение циклической ссылки

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

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

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

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Выберите Параметры >файлов >Формулы. Если вы используете Excel для Mac, выберите меню Excel, а затем выберите Параметры >Вычисление.
  2. В разделе Параметры вычислений установите флажок Включить итеративные вычисления. На компьютере Mac выберите Использовать итеративное вычисление.
  3. В поле Предельное число итераций введите количество итераций для выполнения при обработке формул. Чем больше предельное число итераций, тем больше времени потребуется для пересчета листа.
  4. В поле Относительная погрешность введите наименьшее значение, до достижения которого следует продолжать итерации. Это наименьшее приращение в любом вычисляемом значении. Чем меньше число, тем точнее результат и тем больше времени потребуется Excel для вычислений.

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

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

Что такое в Excel зависимые и влияющие ячейки

  • Влияющие ячейки — приводят к вычислению результата формулы. Влияющую напрямую ячейку указывают непосредственно в формуле, а косвенно влияющие ячейки не используются непосредственно в формуле, но применяются ячейкой, на которую ссылается формула.
  • Зависимые ячейки — эти ячейки с формулами зависят от конкретной ячейки (влияющей). От влияющей ячейки зависят все ячейки с формулами, которые используют данную ячейку. Ячейка с формулой может зависеть напрямую или косвенно.

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

Идентификация влияющих ячеек

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

  • Нажмите клавишу F2. Ячейки, которые используются непосредственно формулой, будут обрисованы, а цвет будет соответствовать ссылке на ячейку в формуле.
  • Откройте диалоговое окно Выделение группы ячеек (выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек). Установите переключатель в положение влияющие ячейки, а затем в положение только непосредственно или на всех уровнях. Нажмите кнопку ОК, и Excel выберет влияющие ячейки для формулы.
  • Нажмите Ctrl+[ для выбора всех влияющих напрямую ячеек на текущем листе.
  • Нажмите Ctrl+Shift+[ для выбора всех влияющих ячеек (прямых и косвенных) на текущем листе.
  • Выберите Формулы ► Зависимости формул ► Влияющие ячейки, и Excel нарисует стрелки, указывающие на влияющие ячейки. Нажмите эту кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.

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

Идентификация зависимых ячеек

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

  • Откройте диалоговое окно Выделение группы ячеек. Установите переключатель в положение зависимые ячейки, а затем в положение только непосредственно (для нахождения напрямую зависимых ячеек) или на всех уровнях (для нахождения напрямую и косвенно зависимых ячеек). Нажмите кнопку ОК. Excel выберет ячейки, которые зависят от активной ячейки.
  • Нажмите Ctrl+] для выбора всех напрямую зависимых ячеек на текущем листе.
  • Нажмите Ctrl+Shift+] для выбора всех зависимых ячеек (прямых и косвенных) на текущем листе.
  • Выберите Формулы ► Зависимости формул ► Зависимые ячейки, и Excel нарисует стрелки, указывающие на зависимые ячейки. Нажмите кнопку несколько раз, чтобы у видеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.

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

Зависимость формул в Excel и структура их вычисления

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

Анализ формул в Excel

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

В ячейку B5 введите формулу, которая просто суммирует значения нескольких ячеек (без использования функции СУММ).

Прибыль.

Теперь проследим все этапы вычисления и содержимое суммирующей формулы:

Вычислить.

  1. Перейдите в ячейку B5, в которой содержится формула.
  2. Выберите инструмент: «Формулы»-«Зависимости формул»-«Вычислить формулу». Появиться диалоговое окно «Вычисление».
  3. В данном окне периодически нажимайте на кнопку «Вычислить», наблюдая за течением расчета в области окна «Вычисление:»

Для анализа следующего инструмента воспользуемся простейшим кредитным калькулятором Excel в качестве примера:

Кредитный калькулятор Excel.

Чтобы узнать, как мы получили результат вычисления ежемесячного платежа, перейдите на ячейку B4 и выберите инструмент: «Формулы»-«Зависимости формул»-«Влияющие ячейки».

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

Теперь мы наглядно видим, с какими значениями работает формула ежемесячного платежа. Дальше нам нужно узнать, как сформировались эти значения. Для этого перейдите сначала на ячейку с первым значением – С2 и снова выберите инструмент «Влияющие ячейки».

Схема.

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

Примечание. Чтобы очистить схему нужно выбрать инструмент «Убрать стрелки».

Полезный совет. Если нажать комбинацию клавиш CTRL+«`» (апостроф над клавишей Tab) или выберите инструмент : «Показать формулы». Тогда мы увидим, что для вычисления ежемесячного платежа мы используем 3 формулы в данном калькуляторе. Они находиться в ячейках: B4, C2, D2.

Такой подход тоже существенно помогает проследить цепочку вычислений. Снова перейдите в обычный режим работы, повторно нажав CTRL+«`».

Как убрать формулы в Excel

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

На листе в ячейках B5 и C2:C5 записанные формулы. Заменим их итоговыми значениями результатов вычислений.

Убрать формулы.

Нажмите комбинацию клавиш CTRL+A (или щелкните в левом верхнем углу на пересечении номеров строк и заголовков столбцов листа), чтобы выделить все содержимое.

Теперь щелкните по выделенному и выберите опцию из контекстного меню «Специальная вставка» (или нажмите комбинацию горячих клавиш CTRL+ALT+V).

Значения.

В появившемся диалоговом окне выберите опцию «значения» и нажмите кнопку ОК.

В результате лист не содержит только значения данных.

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *