Страница 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. Уберите показанные связи, выполнив команду Зависимости формул-Убрать все стрелки (Сервис).
Группа Зависимости формул вкладки Формулы тоже существует для того, чтобы облегчить вам жизнь (рис. 3.21).
Команда Влияющие ячейки стрелками покажет вам, какие именно ячейки влияют на результат вычисления формулы в выделенной вами ячейке (рис. 3.22).
Команда Зависимые ячейки покажет вам, какие ячейки зависят от той, что вы выделили (рис. 3.23).
Эти стрелки нужны для наглядности и для проверки формул. Вдруг вы ошиблись и нечаянно ввели в формулу не тот адрес. А стрелки вам все покажут. Когда вы убедитесь, что все сделали правильно, стрелки можно убрать. Команда Убрать стрелки позволяет убрать все стрелки или отдельно стрелки к влияющим ячейкам и отдельно стрелки к зависимым ячейкам. Нужный вариант можно выбрать из списка кнопки.
Кнопка Показать формулы уберет из ячеек результат вычисления и покажет вам
формулы. Это тоже удобно для контроля над собой в процессе работы. Кнопка Проверка наличия ошибок запустит поиск самых распространенных ошибок во всех формулах открытого листа. Кнопка Вычислить формулу поможет вам следить за процессом вычисления в сложных формулах. Она дает возможность вычислять формулу по частям (рис. 3.24).
На рис. 3.24а вы видите формулу = 1000-Сумма_покупок. Если вы нажмете кнопку Вычислить, то программа посчитает подчеркнутое действие, то есть вычислит то, что находится в ячейке Сумма_покупок. На рис. 3.24б Сумма_покупок уже посчитана, и теперь при нажатии кнопки Вычислить программа посчитает второе действие, в этом примере оно же и последнее. На рис. 3.24, в вы видите результат вычислений.
Такое пошаговое вычисление очень удобно для больших, громоздких формул, особенно в тот момент, когда вы ищете в них ошибку. Получили вы, например, отрицательную цену на товар и напряженно размышляете, как такое могло случиться. На каком этапе вычислений ошибка? Теперь вы знаете, что можете пошагово проверить все формулы, и быстро найдете, в чем причина. Кстати, если вы глянете на рис. 3.24, в, то увидите внизу две кнопки - Заново и Закрыть. Первую вы нажмете, если не разобрались сразу, в чем проблема, а вторую - если разобрались.
В группе Зависимости формул вкладки Формулы (см. рис. 3.21) у нас осталась одна нерассмотренная кнопка - Окно контрольного значения. В окно, которое появляется после нажатия данной кнопки, вы можете ввести значимые для вас результаты, то есть те, которые хотите иметь перед глазами и сравнивать (рис. 3.25).
В этом окне нужно нажать кнопку Добавить контрольное значение (рис. 3.26).
В открывшемся окне Добавление контрольного значения будет высвечиваться абсолютный адрес ячейки, которая выделена в таблице. Как только вы выберете ту, которая достойна быть в окне контрольных значений, жмите кнопку Добавить. Удалить лишние данные вы потом сможете в окне Окно контрольного значения с помощью команды Удалить контрольное значение.
24.10.2012
Иногда мы все ошибаемся, в том числе и при использовании формул в MS Excel. Однако не всегда наши ошибки являются именно ошибками, то есть приводят и рекомендаций о том, как справится с возникшей проблемой. Гораздо хуже, когда никакого предупреждения об ошибках не выводится, а мы явно видим, что результат совершенно не соответствует реальности.
К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.
Влияющие и зависимые ячейки в MS Excel
Обычно в итоговых строках таблицы выводится некая сводка данных расположенных в других местах таблицы, а то и результат каких-то промежуточных вычислений. Если таких вычислений довольно много, то ошибиться в одном из них становится проще простого. Впрочем, исправить такую ошибку тоже очень просто… но только при условии, что вы знаете где искать.
Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые . Различить и запомнить их просто:
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
- Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Зная какие ячейки влияют на результат и от чего он зависит, мы без труда найдем причину проблемы.
Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент .
Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6 .
Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».
Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке
Выделяю результирующую ячейку «Итог» и нажимаю кнопку «Влияющие ячейки» . Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие «влияющая ячейка» невозможно.
А теперь зависимые ячейки. Весь лист теперь как на ладони
Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.
Теперь я намеренно «порчу» таблицу, внося в исходные данные ошибку — подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось — все вполне наглядно и графически красиво.
Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой
Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки» .
Исправление ошибок возникающих в MS Excel
Впрочем, не всегда ошибка представляется нам так явно, иногда нужно ещё понять какое из промежуточных вычислений приводит к сбою. Есть в MS Excel отличный помощник и для этого.
Снова выделим наш пресловутый «итог» и щелкнем мышью по кнопке «Вычислить формулу» . Открывшееся окно содержит в себе поле в котором записана «проблемная» формула. Пока ничего не обычного, верно? Нажимаем на кнопку «Вычислить» внизу и обращаем внимание как формула в окне начнет меняться — вместо идентификаторов ячеек в ней появятся цифры из соответствующих ячеек.
А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей
Одно нажатие на кнопку — один шаг в вычислениях. Уже на третьем шаге мы наглядно увидим в какой именно части формулы происходят вычисления порождающие ошибку, и теперь без труда сможем их исправить.
Вот и всё. Пользуйтесь этими несложными методами, и без труда «расщелкаете» любую возникшую при вычисления в MS 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.
Словосочетание «зависимые ячейки» употребляется в контексте использования формул на листах 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
Если формулы связаны в нескольких книгах эксель, то для отображения зависимостей, нужно, чтобы все эти книги были открыты.