Как сделать зависимость ячеек в excel. Влияющие и зависимые ячейки

Страница 1 из 2

Для того, чтобы найти место ошибки, можно использовать различные приемы. Так, для проверки правильности формул и поиска логических ошибок можно вывести на экран зависимости между ячейками. Это можно сделать командами раскрывающегося подменю Зависимости формул (Сервис). Если установить табличный курсор на ячейку, то для нее в виде стрелок на экран можно вывести: связи с ячейками, адреса которых используются в формуле, записанной в выделенной ячейке (ячейка С2 на рис. 7.17), - команда Влияющие ячейки", связи с ячейками, в которых введены формулы, содержащие адрес выделенной ячейки (ячейка С7 на рис.

7.17), - команда Зависимые ячейки; » связи с ячейками, в которых возможно находятся некорректные данные, приводящие к ошибке в выделенной ячейке (в данном случае речь идет о явной ошибке), - команда Источник ошибок. Для того, чтобы убрать стрелки связей с экрана, следует выполнить команду Зависимости формул. Убрать все стрелки (Сервис). Пошаговое вычисление формул Для более глубокого анализа формул с целью поиска логических ошибок в сложных формулах можно использовать команды Вычислить формулу и Показать окно контрольного значения раскрывающегося подменю Зависимости формул (Сервис). После выполнения команды Вычислить формулу появляется диалоговое окно, используя которое можно выполнить пошаговое вычисление по формуле. Для перехода к каждому следующему шагу следует нажимать кнопку Вычислить. При этом происходит вычисление подчеркнутого значения (ячейка А2 на рис. 7.18). В примере, показанном на рис. 7.18, после первого нажатия кнопки Вычислить будет определено значение ячейки А2, после второго - сумма числа 100 и значения из ячейки А2, после третьего - среднее значение ячеек G2:G13, после четвертого - окончательный результат. Если подчеркнутым значением является адрес ячейки и эта ячейка содержит другую формулу, то можно вычислить значение по этой вложенной формуле либо сразу, либо по шагам. Для вычисления «сразу» следует нажать кнопку Вычислить. Для вычисления «по шагам» следует нажать кнопку Шаг с заходом (произойдет вход во вложенную формулу), а затем продолжать нажимать кнопку Вычислить. Если после захода во вложенную формулу нажать кнопку Шаг с выходом, то вычисление вернется на верхний уровень, т. е. пошаговое вычисление вложенной формуле будет завершено досрочно. В примере, показанном на рис. 7.18, если на первом шаге нажать кнопку Шаг с заходом, то будет выполнен вход в формулу, записанную в ячейку А2 (заход будет выполнен, даже если там не формула, а введенное значение). Если выполнить команду Зависимости формул-Показать окно контрольного значения (Сервис), то появится диалоговое окно, в котором можно наблюдать за тем, как изменяются значения, вычисляемые по формулам в различных ячейках. При этом наблюдаемые ячейки могут находиться в разных частях таблицы, на разных листах и даже в разных книгах. Все изменения содержимого ячеек будут отражаться в окне сразу же после изменения данных во влияющих ячейках. Для добавления ячейки в окно наблюдения необходимо нажать кнопку Добавить контрольное значение... и в появившемся диалоговом окне задать адрес этой ячейки. Как нетрудно догадаться, если после выделения строки нажать кнопку Удалить контрольное значение, то эта строка будет удалена из окна наблюдения. Упомянутые уже выше команды, а также некоторые дополнительные действия, могут быть выполнены инструментами панели Зависимости. Для ее вывода можно включить команду Зависимости формул-Панелъ зависимостей (Сервис) или выполнить команду Показать панель аудита формул меню обработки ошибки. Пример 27. Поиск ошибок в формулах Действие 1 Откройте документ Первая книга. Выполните команду Параметры... (Сервис), в появившемся диалоговом окне на вкладке Проверка ошибок убедитесь, что переключатель Включить фоновую проверку ошибок включен. Если этот переключатель оказался выключен, включите его и нажмите кнопку ОК. Действие 2 На листе Лист1, в формулу ячейки D1 внесите изменение, приводящее к ошибке, например =КОРЕНЬ(МАКС(100;А2:А7; ЕЗ) -10000). После этого установите табличный курсор на эту ячейку, наведите курсор мыши на кнопку меню обработки ошибки и щелкните по ней, а в раскрывшемся меню выполните команду Показать этапы вычисления... (рис. 7.21). В появившемся диалоговом окне Вычисление формулы нажмите кнопку Вычислить, затем появившуюся на ее месте кнопку Заново, а после того как вычисление по формуле начнется, сначала три раза нажмите кнопку Вычислить.

Убедитесь, что пошаговое вычисление формулы позволяет сравнительно легко определить причину и место ошибки. Закройте окно Вычисление формулы. Формулу в ячейке D1 верните в исходное состояние Действие 3 Находясь на листе Лист1 документа Первая книга, выполните команду Параметры... (Сервис).

В появившемся диалоговом окне на вкладке Вид включите переключатель формулы и нажмите кнопку ОК. Убедитесь, что вместо значений, определяемых по формулам, на экран выведены сами формулы. Нажмите комбинацию Ctrl*" (клавиша с этим символом находится слева от клавиши с цифрой 1). Убедитесь, что на экране снова вместо формул появились определяемые по ним значения. Действие 4 Установите табличный курсор на ячейку А5 и выполните команду Зависимости формулВлияющие ячейки (Сервис). Убедитесь, что появились стрелки, указывающие на ячейки, адреса которых содержатся в формуле ячейки А5. Уберите показанные связи, выполнив команду Зависимости формул-Убрать все стрелки (Сервис).

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

К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.

Влияющие и зависимые ячейки в MS Excel

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

Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые . Различить и запомнить их просто:

  • Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
  • Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).

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

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

Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6 .

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

Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке

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


А теперь зависимые ячейки. Весь лист теперь как на ладони

Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.

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


Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой

Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки» .

Исправление ошибок возникающих в MS Excel

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


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


А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей

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

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

Также вас может заинтересовать.

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

Найти зависимые ячейки в exel 2003

Для наглядности создадим маленькую табличку (рис.1)

рис.1

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

В ячейке F7 ошибка. Чтобы определить, чем она вызвана, добавляем на панель инструментов блок «Зависимости » (обведен синим).

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


рис.2

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

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

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

  1. По клавише F2 (рис.3)
  2. Используя панель инструментов, кнопку «зависимые ячейки» рис.4
  3. По комбинации клавиш Ctrl+] или Ctrl+Shift+]

Находясь в ячейке H4 и использовав функцию F2, получим выделение синим цветом клеточек, которые используются в формулах (влияющие ячейки), а сиреневым цветом - клеточка, являющаяся и влияющей и зависимой.


рис.3


рис.4

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

На рис. Показан результат для ячейки B4.


рис. 5

Поиск зависимых ячеек в эксель, версий от 2007

В более поздних версиях приложения эксель работа с формулами вынесена в отдельный блок (выделен синим прямоугольником на рис.5)


рис.6

Для удобства, зависимости формул выделены в отдельный подблок (рис.6).


рис.7

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

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

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

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

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

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

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

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

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

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

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

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

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

Для определения зависимостей поместите табличный курсор в рассматриваемую ячейку и вызовите команду Сервис/Зависимости/Зависимые ячейки или Влияющие ячейки (рис. 6.25). После этого между зависимыми ячейками появятся стрелки. Они показывают непосредственное влияние содержимого одних ячеек на формирование результата в других ячейках.

Рис. 6.25.

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

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

В случае, когда нужно проследить большое число зависимостей, удобно применить панель Зависимости (рис. 6.26).

Рис. 6.26.

На этой панели расположены пять нужных нам кнопок: Влияющие ячейки, Убрать стрелки к влияющим ячейкам, Зависимые ячейки, Убрать стрелки к зависимым ячейкам и Убрать все стрелки (первые пять кнопок слева).

На рис. 6.27 показаны стрелки, которые появляются при выполнении команды Влияющие ячейки . Они указывают, формулы каких ячеек оказывают непосредственное влияние на формулы, находящиеся в анализируемой ячейке. Например, на вычисления в ячейке ВЗ влияет значение ячейки А1. На ячейку С4 влияют значения в ячейках С3 и В3, на которые, в свою очередь, влияет ячейка А1.


Рис. 6.27.

На рис. 6.28 показаны стрелки, которые появляются при выполнении команды Зависимые ячейки. Они указывают, на формулы каких ячеек оказывают влияние формулы или значения, находящиеся в исходной ячейке. Так, ячейка А1 влияет на вычисления в ячейках A1, B1, C1 и т. д, а ячейка ВЗ влияет на вычисления в ячейках С4 и В4.

 

Возможно, будет полезно почитать: