Зависимые ячейки на другом листе. Поиск и исправление ошибок в формулах MS Excel

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

Отслеживать зависимости удобно с помощью панели инструментов Зависимости (рис. 6.1). Чтобы открыть ее, воспользуйтесь подменюЗависимости менюСервис .

Рис. 6.1. Панель инструментов Зависимости

Функция отслеживания зависимостей позволяет графически обозначить связи между влияющими и зависимыми ячейками. Рассмотрим простой пример. Пусть в ячейках А1 и А2:А5 содержатся некоторые числа, в ячейке В2 запишем формулу =$B$1*A2, с помощью автозаполнения скопируем эту формулу в ячейки В3:В5. Активизируем ячейку А1 и выберем в меню Сервис командуЗависимости - Зависимые ячейки или щелкнем по кнопке. В таблице появятся линии трассировки со стрелками, исходящими из активной ячейки и указывающими на зависимые ячейки в таблице (рис. 6.2).

Рис. 6.2. Линии трассировки, показывающие зависимые от А1 ячейки

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

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

Использование функций

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

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

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

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

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

Создайте таблицу, изображенную на рис. 6.3.

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

Рис. 6.3. Пример использования вложенных функций

Для этого введем в ячейку В12 формулу =МИН (В4:В10). Скопируем эту формулу в ячейки С12 - Е12. В результате применения функции, определяющей минимальное значение, мы установим минимальную температуру в разных городах. Чтобы определить максимальное из минимальных значений, введем в ячейку В13 формулу:

МАКС (В12:Е12).

Полностью решить данную задачу можно и с помощью одной-единственной формулы (которую нужно вставить в ячейку В13):

МАКС (МИН (В4:В10); МИН (С4:С10); МИН (D4:D10); МИН (Е4:Е10)).

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

Существует множество задач (например, округление значений), решать которые на много легче, используя вложенные функции. Для округления чисел в Excel 97 предназначена функция ОКРУГЛ, синтаксис которой несколько отличается от синтаксиса уже знакомых нам функций СУММ, МИН и МАКС. Аргументами функции ОКРУГЛ являются число или ссылка и количество десятичных разрядов результата.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

рис.1

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

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

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

рис.2

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

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

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

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

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

рис.3

рис.4

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

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

рис. 5

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

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

рис.6

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

рис.7

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

Страница 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. Уберите показанные связи, выполнив команду Зависимости формул-Убрать все стрелки (Сервис).

Вызов команды:
-группа Ячейки/Диапазоны -Ячейки

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

Для чего вообще нужна подобная команда? К примеру, выделенная ячейка содержит формулу:
=СУММ(C6 * E19 ;СУММ(СУММ("Статьи затрат.xls"!C2;"Статьи затрат.xls"!C4;"Статьи затрат.xls"!C6;"Статьи затрат.xls"!C7;"Статьи затрат.xls"!C8);СУММ("Производственная себестоимость"!B4;"Производственная себестоимость"!B5)))
при этом от значения этой ячейки зависят значения еще нескольких ячеек(т.к. изменение значения этой ячейки изменит значение других, т.к. в них формулы ссылаются на эту ячейку), в данном случае это несколько ячеек листа Расчет .
Команда построит наглядную карту зависимостей такой ячейки:

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

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

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

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

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

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

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

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

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

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

Для каждого окна карты зависимостей так же доступна функция выделения цветом зависимых или влияющих ячеек:

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

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

Из этого несомненно можно понять большую часть, но если присмотреться к изображению выше, то видно, что стрелка к той же ячейке С8 , на которую влияет выделенная С3 перекрывается стрелками влияющих ячеек C13 , C14:C18 и практически не заметна. А сразу увидеть ячейки с других листов и книг вообще не получится - для этого надо будет сначала дважды щелкнуть мышью на стрелке(которая ведет к значку в виде таблицы ) и в появившемся окне выбрать одну(! ) ссылку. А если ссылок больше 10? Сколько раз надо будет щелкать туда-сюда? Пока будем щелкать уже забудем, что хотели узнать...Так же в этом окне никак не помечены ссылки на закрытые книги - они просто ничем не отличаются от ссылок на доступные источники(открытые книги). О том, что ссылка недоступна узнать можно будет только после того, как попробуем на неё перейти.
И еще нюанс: при отображенных зависимостях одной ячейки отобразить зависимости другой нельзя, пока не уберем стрелки от первой ячейки(нажав Убрать стрелки ). Т.е. для просмотра зависимостей второй ячейки, необходимо сначала убрать отображение зависимостей текущей. После чего заново отобразить стрелки через меню. Несколько затратно по времени, особенно если ячеек куча.