Страница 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. Однако не всегда наши ошибки являются именно ошибками, то есть приводят и рекомендаций о том, как справится с возникшей проблемой. Гораздо хуже, когда никакого предупреждения об ошибках не выводится, а мы явно видим, что результат совершенно не соответствует реальности.
Эта область также позволяет принудительно вводить определенные комбинации букв и цифр. Для второго и третьего примеров используется одна и та же формула. Таким образом, вы можете видеть, что вам нужно точно определить, что разрешено. Поэтому, если требуются знаки минус, это должно быть определено соответствующим образом.
ПРИМЕЧАНИЕ. В примере 5 показано, как сделать записи в конкретной ячейке зависимыми от других уже заполненных ячеек. Здесь очень важно сообщить пользователю, какие входы ожидаются. Если только он не доведется до безумия. Для этого мы создаем список в соответствии с более низким шаблоном.
К счастью, в наших руках несколько отличных инструментов для поиска «хитрых» ошибок в формулах MS Excel.
Влияющие и зависимые ячейки в MS Excel
Обычно в итоговых строках таблицы выводится некая сводка данных расположенных в других местах таблицы, а то и результат каких-то промежуточных вычислений. Если таких вычислений довольно много, то ошибиться в одном из них становится проще простого. Впрочем, исправить такую ошибку тоже очень просто… но только при условии, что вы знаете где искать.
Это также устраняет проблемы, возникающие в результате опечаток. Возвращает ссылку на текстовое значение. Платежи немедленно оцениваются так, чтобы отображались значения, принадлежащие им. Значение истины, указывающее, какая ссылка содержится в ячейке.
Условное форматирование с помощью собственной формулы
При необходимости вы можете настроить ширину столбцов так, чтобы отображались все данные. Если оба они расходятся, вы должны принять специальные меры предосторожности, которые этот вклад объясняет с помощью графика смены. Теперь он должен быть обновлен, чтобы поля под ячейкой с красной датой, если дата - воскресенье. Для этого вы не можете использовать готовые функции, но в этом случае вы должны использовать самописную формулу.
Именно с этой точки зрения все ячейки в MS Excel разделяются на влияющие и зависимые . Различить и запомнить их просто:
- Влияющие ячейки, это ячейки на которые ссылается формула (т.е. если формула это А+Б, то данные в ячейках А и Б — это данные влияющие на результат вычисления формулы).
- Зависимые — содержат формулу влияющую на содержимое ячейки (т.е. если формула В+Г берет данные по В из ячейки содержащей не число, а результат вычисления А+Б, то ячейка с формулой В+Г, будет по отношению к ней зависимой, т.к. от правильности работы А+Б зависит результат вычисления в В+Г).
Зная какие ячейки влияют на результат и от чего он зависит, мы без труда найдем причину проблемы.
Вот возможная исходная таблица, которая является базовой структурой. Прежде всего, важно знать, как признать, что дата выпадает на воскресенье. В воскресенье оно возвращает значение 1. 
Поскольку это также воскресенье, поля ниже должны быть красными, как только вы введете новую дату.
Вот почему формуле нужен знак доллара
Результат - именно то, чего мы хотим достичь. Знак доллара фиксирует соответствующую координату, так сказать. Если вы хотите больше узнать о теме, вы найдете ее здесь. Прочтите здесь, как эти функции помогут вам. Поэтому он служит для поиска полей об их содержимом из определенной области ячеек.
Для иллюстрации я подготовил простейшую табличку с данными. В ней есть два условных показателя и коэффициент, а итоговый расчет осуществляется простой плюсовкой обоих показателей с последующим умножение на результат: (Показатель 1 + Показатель 2) х Коэффициент .
Дополнительно я создал ещё одну простую формулу: она умножает наш «Итог» на некую постоянную поправку, которую я задал прямо в формуле вручную: Итог х 0,6 .
Поиски всегда находятся в первом столбце или строке указанного диапазона. Первые буквы имен функций указывают на используемое направление поиска: «вертикальное» или «горизонтальное». Функция имеет следующую схему вызова. Первое значение в списке параметров - это значение сравнения, которое вы хотите использовать для поиска. Кроме того, что вы можете подозревать, здесь допускается только одно значение, но не логическое выражение.
Контрольные функции всегда проверяют только «равный или больший». Вторая - это матрица, которая является областью, которую вы хотите использовать для поиска. С индексом столбца, третьим параметром функции, вы указываете, какая ячейка должна быть возвращена относительно ячейки с найденным значением. Если значение для индекса равно 1, это сама ссылка. Если вы вводите здесь 2, это ячейка справа от поиска и т.д.
Давайте перейдем на вкладку «Формулы» и в группе «Зависимости формул» посмотрим на два крайне полезных в работе инструмента: «Влияющие ячейки» и «Зависимые ячейки».
Определяем влияющие ячейки в Excel. Влияющие они естественно на вычисления происходящие в данной ячейке
Выделяю результирующую ячейку «Итог» и нажимаю кнопку «Влияющие ячейки» . Оп, и на листе MS Excel появляются синие стрелки ведущие от трех используемых в вычислениях ячеек к итоговой формуле. Согласитесь, нагляднее представить себе понятие «влияющая ячейка» невозможно.
В противном случае функция не вернет ожидаемый результат! Вы найдете объяснение. Два варианта, зависящие от значения последнего параметра, различаются по своей функции. Общим примером этого может служить список телефонов. В нем у вас есть ключевое поле, например, количество сотрудников всех сотрудников.
Если список длинный, его будет трудно найти вручную. Для этого введите номер сотрудника, которого вы ищете в ячейке. Затем ячейка справа от нее выведет номер телефона, и в качестве чека появится другая ячейка справа, имя сотрудника. Если ваша телефонная книга, то есть область, отмеченная красным цветом, имеет разные размеры, вы должны соответствующим образом отрегулировать второй параметр.
А теперь зависимые ячейки. Весь лист теперь как на ладони
Теперь нажимаю (не убирая курсор с ячейки «итоги») кнопку «Зависимые ячейки» и на экране появляется ещё одна стрелка. Она ведет к ячейке «результат с поправкой», то есть той, результат вычислений в которой зависит от текущей.
В ближайшей к нему ячейке, которая должна получить имя сотрудника, которого вы ищете, скопируйте только что введенную формулу, но выберите 4 в качестве индекса столбца, который соответствует полю имени в списке. Список телефонов не должен иметь восходящую сортировку для первого поля с номером персонала.
Это правило можно сопоставить в следующей таблице, которая всегда содержит меньшее значение соответствующего интервала в первом столбце. 
В верхней части листа находится расчет для определенного порядка. Возвращается значение ячейки справа, поскольку индекс столбца обозначается 2. Чтобы полностью понять поведение, имейте это в виду. В качестве примера возьмем приведенный выше пример с введенным значением в 32 евро.
Теперь я намеренно «порчу» таблицу, внося в исходные данные ошибку — подставляя букву вместо цифры. Мгновение, и я уже точно знаю откуда эта ошибка взялась. Мне даже искать ничего не пришлось — все вполне наглядно и графически красиво.
Ошибка возникшая из-за замены цифры на букву. Excel подсветил «ошибочное» вычисление красной стрелкой
Несортированные значения приводят к ошибке
Взгляните на следующий список, который компания использует для определения комиссии продавцов по их продажам. 
Это будет работать - и поэтому ежедневно практикуется в бесчисленных офисах по всему миру. Однако с помощью этого метода результатом является текст, так что числовое представление может быть ограничено только в форматах ячеек. После того, как более чем одно значение будет отформатировано, оно закончено.
Отключить графику можно в любой момент нажав на кнопку «Убрать стрелки» .
Исправление ошибок возникающих в MS Excel
Впрочем, не всегда ошибка представляется нам так явно, иногда нужно ещё понять какое из промежуточных вычислений приводит к сбою. Есть в MS Excel отличный помощник и для этого.
Хотя результатом этой функции является также текст, номера могут быть отформатированы по желанию. Экран Аргумент «Формат текста» определяет способ форматирования номера до его появления, например # ## 0. 00 Обратите внимание, что форматирование числа указано в кавычках. Таким образом могут быть отформатированы не только суммы, но и произвольные даты и время или другие значения. Выберите нужное форматирование. Ячейка, содержащая ссылку, называется зависимой ячейкой. Ячейка на другом листе, который содержит данные, на которые ссылается ссылка, называется предыдущей ячейкой.
Снова выделим наш пресловутый «итог» и щелкнем мышью по кнопке «Вычислить формулу» . Открывшееся окно содержит в себе поле в котором записана «проблемная» формула. Пока ничего не обычного, верно? Нажимаем на кнопку «Вычислить» внизу и обращаем внимание как формула в окне начнет меняться — вместо идентификаторов ячеек в ней появятся цифры из соответствующих ячеек.
Зависимая ячейка изменяется автоматически, если предыдущая ячейка изменяется. Если вы хотите связать несколько ячеек в другой электронной таблице, вы можете использовать матричную функцию, которая позволяет связать диапазон ячеек с формулой. Нажмите на вкладку электронной таблицы в нижней части экрана, которая содержит ряд предыдущих ячеек, которые вы хотите связать. Диапазон - это блок или группа соседних ячеек. Перейдите на вкладку «Лист2». Определите ширину предыдущего ранга в столбцах и его высоту в строках.
Этот предшествующий ранг имеет ширину столбца на четыре строки. Нажмите на вкладку электронной таблицы в нижней части экрана, которая содержит пустые ячейки, в которые вы вставляете ссылку. В этом примере щелкните вкладку «Лист1». Выберите диапазон пустых ячеек, которые вы хотите связать с предыдущими ячейками. Этот диапазон должен быть того же размера, что и предыдущий диапазон, но он может находиться в другом месте в электронной таблице. Нажмите и удерживайте кнопку мыши в верхней левой ячейке диапазона, переместите курсор мыши в нижнюю правую ячейку этого диапазона и отпустите кнопку для выбора.
А вот и ошибка — как видите, программа ясно дает понять, что проблема возникает ещё до умножения, то есть на этапе сложения показателей
Одно нажатие на кнопку — один шаг в вычислениях. Уже на третьем шаге мы наглядно увидим в какой именно части формулы происходят вычисления порождающие ошибку, и теперь без труда сможем их исправить.
Вот и всё. Пользуйтесь этими несложными методами, и без труда «расщелкаете» любую возникшую при вычисления в MS Excel ошибку.
Также вас может заинтересовать.