Что такое в Excel зависимые и влияющие ячейки — Трюки и приемы в Microsoft Excel
На многих листах ячейки могут содержать сложные взаимосвязи. Попытка отладить формулы может оказаться проще, если вы понимаете два ключевых понятия: влияющие и зависимые ячейки.
- Влияющие ячейки — приводят к вычислению результата формулы. Влияющую напрямую ячейку указывают непосредственно в формуле, а косвенно влияющие ячейки не используются непосредственно в формуле, но применяются ячейкой, на которую ссылается формула.
- Зависимые ячейки — эти ячейки с формулами зависят от конкретной ячейки (влияющей). От влияющей ячейки зависят все ячейки с формулами, которые используют данную ячейку. Ячейка с формулой может зависеть напрямую или косвенно.
- Нажмите клавишу F2. Ячейки, которые используются непосредственно формулой, будут обрисованы, а цвет будет соответствовать ссылке на ячейку в формуле.
- Откройте диалоговое окно Выделение группы ячеек (выберите Главная ► Редактирование ► Найти и выделить ► Выделение группы ячеек). Установите переключатель в положение влияющие ячейки, а затем в положение только непосредственно или на всех уровнях. Нажмите кнопку ОК, и Excel выберет влияющие ячейки для формулы.
- Нажмите Ctrl+[ для выбора всех влияющих напрямую ячеек на текущем листе.
- Нажмите Ctrl+Shift+[ для выбора всех влияющих ячеек (прямых и косвенных) на текущем листе.
- Выберите Формулы ► Зависимости формул ► Влияющие ячейки , и Excel нарисует стрелки, указывающие на влияющие ячейки.
Нажмите эту кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки. - Откройте диалоговое окно Выделение группы ячеек. Установите переключатель в положение зависимые ячейки, а затем в положение только непосредственно (для нахождения напрямую зависимых ячеек) или на всех уровнях (для нахождения напрямую и косвенно зависимых ячеек). Нажмите кнопку ОК. Excel выберет ячейки, которые зависят от активной ячейки.
- Нажмите
- Нажмите Ctrl+Shift+] для выбора всех зависимых ячеек (прямых и косвенных) на текущем листе.
- Выберите Формулы ► Зависимости формул ► Зависимые ячейки, и Excel нарисует стрелки, указывающие на зависимые ячейки. Нажмите кнопку несколько раз, чтобы у видеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.
Выявление влияющих ячеек для ячейки, содержащей формулу, часто проливает свет на то, почему формула некорректно работает. И наоборот, также полезно знать, какие ячейки формул зависят от конкретной ячейки.
Например, если вы собираетесь удалить формулу, то можете захотеть проверить, не зависит ли что-нибудь от нее.
Идентификация влияющих ячеек
Вы можете идентифицировать ячейки, используемые формулой в активной ячейке, несколькими способами.
Нажмите эту кнопку несколько раз, чтобы увидеть дополнительные уровни влияния. Выберите Формулы ► Зависимости формул ► Убрать стрелки, чтобы скрыть стрелки.Заметьте, что все эти методы ограничены выявлением влияющих ячеек только на том листе, который содержит формулу.
Идентификация зависимых ячеек
Следующими способами вы можете определять ячейки с формулами, которые используют конкретную ячейку.
Опять же, эти методы ограничены выявлением зависимых ячеек только на текущем листе.
Зависимости формул в Excel — ОфисГуру
Перейти к содержанию
Search for:
Главная » Уроки MS Excel
Автор Антон Андронов На чтение 3 мин Опубликовано- Влияющие ячейки
- Убрать стрелки
- Зависимые ячейки
- Показать формулы
- Проверка наличия ошибок
- Вычисление формулы
Зависимости формул в Excel позволяют отображать соотношения между формулами и ячейками.
Пример ниже поможет вам понять зависимости формул быстро и легко.
=ROUNDUP(C10/C12,0)=ОКРУГЛВВЕРХ(C10/C12;0)
Содержание
- Влияющие ячейки
- Убрать стрелки
- Зависимые ячейки
- Показать формулы
- Проверка наличия ошибок
- Вычисление формулы
Влияющие ячейки
Как видно на рисунке выше, вы должны заплатить $96. Чтобы отобразились стрелки, указывающие, какие ячейки используются для вычисления этого значения, следуйте инструкции ниже:
- Выделите ячейку С13.
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Trace Precedents (Влияющие ячейки).
Результат:
Как и ожидалось, для расчета стоимости на человека используется общая стоимость заказа и размер группы.
- Снова кликните по Trace Precedents (Влияющие ячейки).
Как и ожидалось, для расчета общей стоимости используется список различных затрат.
Убрать стрелки
Чтобы убрать стрелки, выполните следующие действия:
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Remove Arrows (Убрать стрелки).
Зависимые ячейки
Чтобы отобразить стрелки, которые указывают, какие ячейки зависят от выбранной ячейки, следуйте инструкции ниже:
- Выделите ячейку С12.
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Trace Dependents (Зависимые ячейки).
Результат:
Как и ожидалось, стоимость на человека зависит от размера группы.
Показать формулы
По умолчанию Excel показывает результаты формул. Чтобы показать формулы, а не их результаты, придерживайтесь инструкции:
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Show Formulas (Показать формулы).
Результат:
Примечание: Вместо того, чтобы кликать по Show Formulas (Показать формулы), можно нажать сочетание клавиш CTRL+`. Клавиша «`» располагается над клавишей Tab.
Проверка наличия ошибок
Чтобы проверить наличие распространенных ошибок, которые закрадываются в формулы, сделайте следующее.
- Введите значение 0 в ячейку С12.
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Error Checking (Проверка наличия ошибок).
Результат:
Excel находит ошибки в ячейке С13. Формула пытается делить числа на 0.
Вычисление формулы
Для отладки формулы методом оценки каждой её части индивидуально, выполните следующие действия:
- Выберите ячейку С13.
- На вкладке Formulas (Формулы) в разделе Formula Auditing (Зависимости формул) нажмите кнопку Evaluate Formula (Вычислить формулу).
- Кликните по Evaluate Formula (Вычислить формулу) четыре раза.
Excel показывает результат формулы:
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/examples/formula-auditing.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel
Оцените качество статьи. Нам важно ваше мнение:
Adblockdetector
Показать отношения между формулами и ячейками
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Дополнительно… Меньше
Проверка формул на точность или поиск источника ошибки могут быть затруднены, если в формуле используются предшествующие или зависимые ячейки:
Зависимые ячейки — эти ячейки содержат формулы, ссылающиеся на другие ячейки. Например, если ячейка D10 содержит формулу =B5 , ячейка D10 является зависимой от ячейки B5.
org/ListItem»>предшествующих ячеек — ячеек, на которые ссылается формула в другой ячейке. Например, если ячейка D10 содержит формулу =B5 , то ячейка B5 предшествует ячейке D10.
Чтобы помочь вам в проверке ваших формул, вы можете использовать прецеденты трассировки и зависимости трассировки команды для графического отображения и отслеживания взаимосвязей между этими ячейками и формулами с помощью стрелок трассировки, как показано на этом рисунке.
Выполните следующие действия, чтобы отобразить отношения формул между ячейками:
org/ItemList»> Щелкните Файл > Параметры > Дополнительно.
Примечание. Если вы используете Excel 2007; нажмите Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Дополнительно
В разделе Параметры отображения для этой рабочей книги выберите рабочую книгу и убедитесь, что в выбрано Все . Для объектов покажите .
Чтобы указать ссылочные ячейки в другой книге, эта книга должна быть открыта. Microsoft Office Excel не может перейти к ячейке в книге, которая не открыта.
org/ListItem»>Выполните одно из следующих действий.
Выполните следующие действия:
Выберите ячейку, содержащую формулу, для которой вы хотите найти предшествующие ячейки.
Чтобы отобразить трассирующую стрелку для каждой ячейки, которая напрямую предоставляет данные для активной ячейки, в Формулы , в группе Аудит формул щелкните Прецеденты трассировки .
Чтобы определить следующий уровень ячеек, которые предоставляют данные для активной ячейки, снова щелкните Trace Precedents .
Чтобы удалить стрелки трассировки по одному уровню за раз, начните с предшествующей ячейки, наиболее удаленной от активной ячейки. Затем на На вкладке Формулы в группе Аудит формул щелкните стрелку рядом с Удалить стрелки , а затем щелкните Удалить предшествующие стрелки . Чтобы удалить другой уровень стрелок трассировки, нажмите кнопку еще раз.
Выполните следующие действия:
Выберите ячейку, для которой вы хотите определить зависимые ячейки.
Чтобы отобразить стрелку трассировки для каждой ячейки, зависящей от активной ячейки, на вкладке Формулы в группе Аудит формул щелкните Зависимые трассировки .
Синие стрелки показывают ячейки без ошибок. Красные стрелки показывают ячейки, которые вызывают ошибки. Если на выбранную ячейку ссылается ячейка на другом листе или книге, черная стрелка указывает от выбранной ячейки к значку рабочего листа. Другая книга должна быть открыта, прежде чем Excel сможет отслеживать эти зависимости.
Чтобы определить следующий уровень ячеек, зависящих от активной ячейки, снова щелкните Зависимые трассировки .
Чтобы удалить стрелки трассировки по одному уровню за раз, начиная с зависимой ячейки, наиболее удаленной от активной ячейки, на вкладке Формулы в группе Аудит формул щелкните стрелку рядом с Удалить стрелки , а затем щелкните Удалить зависимые стрелки .
Чтобы удалить другой уровень стрелок трассировки, нажмите кнопку еще раз.
Чтобы удалить другой уровень стрелок трассировки, нажмите кнопку еще раз.Выполните следующие действия:
В пустой ячейке введите = (знак равенства).
Нажмите кнопку Выбрать все .
Выберите ячейку и на вкладке Формулы в группе Аудит формул дважды щелкните Прецеденты трассировки
Чтобы удалить все стрелки трассировки на листе, на вкладке Формулы в группе Аудит формул щелкните Удалить стрелки .
Проблема: Microsoft Excel издает звуковой сигнал, когда я нажимаю команду «Отслеживать зависимые элементы» или «Отслеживать прецеденты».
Если Excel издает звуковой сигнал, когда вы нажимаете Зависимые трассировки или Прецеденты трассировки , Excel либо отследил все уровни формулы, либо вы пытаетесь отследить элемент, который невозможно отследить. Следующие элементы на листах, на которые могут ссылаться формулы, невозможно отследить с помощью инструментов аудита:
.Ссылки на текстовые поля, встроенные диаграммы или изображения на рабочих листах.
Отчеты сводной таблицы.
Формулы, расположенные в другой книге и ссылающиеся на активную ячейку, если другая книга закрыта.
org/ListItem»>Ссылки на именованные константы.
Примечания:
Чтобы увидеть выделенные цветом прецеденты аргументов в формуле, выберите ячейку и нажмите F2.
Чтобы выбрать ячейку на другом конце стрелки, дважды щелкните стрелку. Если ячейка находится на другом листе или книге, дважды щелкните черную стрелку, чтобы отобразить диалоговое окно Перейти к , а затем дважды щелкните нужную ссылку в списке Перейти к .
org/ListItem»>Все стрелки трассировщика исчезают, если изменить формулу, на которую указывают стрелки, вставить или удалить столбцы или строки, а также удалить или переместить ячейки. Чтобы восстановить стрелки трассировщика после внесения любого из этих изменений, вы должны снова использовать команды аудита на рабочем листе. Чтобы отслеживать исходные стрелки трассировки, распечатайте рабочий лист с отображаемыми стрелками трассировки, прежде чем вносить изменения.
Как отслеживать зависимости в Excel
Если вы много работаете с формулами в Excel, вы знаете, что значение одной ячейки может быть использовано в формуле во многих разных ячейках. Фактически, ячейки на другом листе также могут ссылаться на это значение. Это означает, что эти ячейки зависят от другой ячейки.
Если вы измените значение этой отдельной ячейки, это изменит значение любой другой ячейки, которая ссылается на эту ячейку в формуле.
Давайте возьмем пример, чтобы увидеть, что я имею в виду. Здесь у нас есть очень простой лист, где у нас есть три числа, а затем мы берем сумму и среднее значение этих чисел.
Содержание
Итак, допустим, вы хотите узнать зависимые ячейки ячейки C3, значение которой равно 10. Значения каких ячеек изменятся, если мы изменим значение 10 на другое? Очевидно, это изменит сумму и среднее значение.
В Excel это можно визуально увидеть, отследив иждивенцев. Вы можете сделать это, перейдя на вкладку Formulas , затем щелкнув ячейку, которую вы хотите отследить, а затем щелкнув Зависимые трассировки кнопка.
Когда вы сделаете это, вы сразу же увидите синие стрелки, нарисованные от этой ячейки к зависимым ячейкам, как показано ниже:
Вы можете удалить стрелки, просто щелкнув ячейку и нажав кнопку Удалить стрелки . Но допустим, у вас есть другая формула на Листе2, которая использует значение из C1.
Можете ли вы отследить иждивенцев на другом листе? Что вы можете! Вот как это будет выглядеть:
Как видите, черная пунктирная линия указывает на то, что выглядит как значок листа. Это означает, что есть зависимая ячейка на другом листе. Если вы дважды щелкните пунктирную черную линию, откроется диалоговое окно «Перейти», в котором вы сможете перейти к этой конкретной ячейке на этом листе.
Вот и все для иждивенцев. Трудно не говорить о прецедентах, когда речь идет о иждивенцах, потому что они очень похожи. Точно так же, как мы хотели увидеть, на какие ячейки влияет значение C3 в приведенном выше примере, мы также можем захотеть увидеть, какие ячейки влияют на значение G3 или h4.
Как видите, ячейки C3, D3 и E3 влияют на значение ячейки суммы. Эти три ячейки выделены синим прямоугольником со стрелкой, указывающей на ячейку суммы. Это довольно прямолинейно, но если у вас есть действительно сложные формулы, в которых также используются сложные функции, то повсюду может быть много стрелок.