Контроль ошибок эксель

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

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

Когда проверка ошибок включена, Excel постоянно оценивает таблицы, в том числе и содержащиеся в них формулы. Если возможная ошибка определена, то Excel добавит маленький треугольник в верхнем левом углу ячейки. Когда ячейка активизируется, появится смарт-тег. Щелчок на нем предоставит вам несколько команд на выбор. На рис. 195.2 показаны команды, которые появляются при выборе смарт-тега в ячейке, содержащей ошибку #ДЕЛ/0!. Команды различаются в зависимости от типа ошибки.

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

Даже если вы не используете функцию автоматической проверки ошибок, вы можете выбрать команду Формулы ► Зависимости формул ► Проверка наличия ошибок, чтобы открыть диалоговое окно, которое последовательно показывает каждую возможную ошибку ячейки, что во многом схоже с использованием функции проверки орфографии. На рис. 195.3 вы можете видеть диалоговое окно Контроль ошибок. Обратите внимание, что это немодальное диалоговое окно, и у вас остается доступ к листу, когда оно открыто.

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

Функция ЕОШ в Excel используется для проверки значений, переданных в качестве ее единственного аргумента, и возвращает один из двух вариантов значений данных логического типа (ИСТИНА или ЛОЖЬ). Таким образом можно легко составить формулу обхода ошибок. Например, формула если ошибка то пустая ячейка: =ЕСЛИ(ЕОШ(A1);»»;A1).

Как проверить ячейки на ошибки с помощью функции ЕОШ в Excel

Функция ЕОШ часто используется для предотвращения возникновения ошибок в ячейках Excel. Рассматриваемая функция возвращает логическое ИСТИНА, если ее аргумент ссылается на ячейку, содержащую один из перечисленных кодов ошибки: #ССЫЛКА!, #ЧИСЛО!, #ЗНАЧ!, #ПУСТО!, #ИМЯ?, #ДЕЛ/0!. Аналогичный результат будет возвращен, если функция используется для проверки выражения или результата вычислений другой функции, указанных в качестве аргумента рассматриваемой функции, например =ЕОШ(5/0) (ошибка #ДЕЛ/0!) или =ЕОШ(ОСПЛТ(0,12;10;5;1000)) (ошибка #ЧИСЛО!, так как номер периода выплат не находится в диапазоне допустимых значений – 10>5).

В остальных случаях результат выполнения функции ЕОШ – значение ЛОЖЬ. Это касается также ошибки #Н/Д. То есть, результат выполнения формулы =ЕОШ(#Н/Д) – возвращает значение ЛОЖЬ.

Примечание:

В Excel начиная с версии 2007 года и выше была добавлена функция ЕСЛИОШИБКА, которая является более предпочтительной для использования, поскольку она нивелирует избыточность конструируемых выражений. Например, для проверки наличия ошибки в расчетах с помощью функции ЕОШ и устранения ее требуется конструкция ЕСЛИ(ЕОШ(проверяемое_значение);значение_если_ошибка_была_найдена;значение_без_изменений). С использованием функции ЕСЛИОШИБКА запись сокращается до =ЕСЛИОШИБКА(проверяемое_значение;значение_если_ошибка_была_найдена).



Примеры использования функции ЕОШ в формулах Excel

Пример 1. В таблице Excel содержатся данные о количестве посетителей сети магазинов по городам и среднем числе клиентов в сутки. Определить среднюю сумму в чеке на одного клиента. В некоторых городах магазины данного бренда еще не открылись, поэтому данные могут отсутствовать. А в результате вычислений формул весь отчет получился запачканным ошибками: #ДЕЛ/0!

Вид таблицы данных:

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

Если проверяемое значение функцией ЕСЛИ (результат выполнения функции ЕОШ, то есть проверка частного от деления C3/B3) возвращает ИСТИНА (ошибка была найдена), будет возвращено значение 0, иначе – результат деления C3/B3. Вычислим значение для города Москва и «растянем» формулу для определения результатов по другим городам. Получим:

Вместо ошибок #ДЕЛ/0! Выводится значение 0,00.

Как заменить ошибки в ячейках Excel текстом

Пример 2. Для поиска в базе данных используется функция БИЗВЛЕЧЬ, которая может вернуть как минимум два кода ошибок: #ЗНАЧ!, если поиск не дал результатов, и #ЧИСЛО!, если количество совпадений равно 2 и более. Заменить коды ошибок на более понятное для рядового пользователя пояснение.

Вид таблицы данных:

В данной таблице (оформлена как БД в Excel) хранятся данные о телевизорах на складе. Например, чтобы найти цену телевизора LG с диагональю 21 введем функцию:

Чтобы получать разъяснение ошибок при поиске несуществующих товаров или сразу нескольких единиц (например, в таблице есть несколько телевизоров с диагональю 21) используем следующую запись:

В результате поиска, например, телевизора Philips (несуществующая позиция в данной БД) получим следующее сообщение:

Привила использования функции ЕОШ в формулах Excel

Функция имеет следующую синтаксическую запись:

=ЕОШ(значение)

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

Примечания:

1. Функция не выполняет промежуточных преобразований типов данных для значений, принимаемых в качестве аргумента. Например, для текстовой строки со значением «#ЧИСЛО!» ошибкой она вернет значение ЛОЖЬ, а для ячейки со значением #ЧИСЛО! – ИСТИНА.
2. Сама по себе рассматриваемая функция используется на практике крайне редко. Ее удобно применять в комбинации с другими логическими функциями, чаще всего с функцией ЕСЛИ.

Идентификация ошибок осуществляется несколькими способами. Один из них реализуется через отображение кода ошибки в ячейке.

Ошибка	Описание
#Н/Д	Н/Д – является сокращением термина Неопределённые данные. Помогает предотвратить использование ссылки на пустую ячейку
#ЧИСЛО!	Функция с числовым аргументом использует неприемлемый аргумент
#ИМЯ?	Ошибка в написании имени или используется несуществующее имя
#ССЫЛКА!	Используется ссылка на несуществующую ячейку
#ЗНАЧ!	Вместо числового или логического значения введён текст, и Excel не может преобразовать его к нужному типу данных
#ДЕЛ/0!	В качестве делителя используется ссылка на ячейку, в которой содержится нулевое или пустое значение (если ссылкой является пустая ячейка, то её содержимое интерпретируется как ноль)
#ПУСТО!	Используется ошибочная ссылка на ячейку
#############	Данные не помещаются в ячейку по ширине, следует увеличить ширину столбца или применить более компактное форматирование

Второй способ обнаружения ошибок – Excel отображает в левом верхнем углу ячейки зелёный треугольник (индикатор ошибки). При выборе такой ячейки появляется смарт-тег проверки ошибок.

Для проверки ошибок необходимо выполнить следующие шаги:

1. Выберите лист, который требуется проверить на наличие ошибок.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Проверка наличия ошибок. Откроется окно диалога Контроль ошибок.

3. В окне диалога Контроль ошибок просмотрите информацию о текущей ошибке в левой части окна.

4. Для просмотра более детального описания ошибки и возможных вариантов её исправления нажмите кнопку Справка по этой ошибке.

5. Нажмите кнопку Показать этапы вычисления. MS Excel откроет окно диалога Вычисление формулы, где вы сможете просмотреть значения различных частей вложенной формулы, вычисляемые в порядке расчёта формулы:

a) нажмите кнопку Вычислить, чтобы проверить значение подчёркнутой ссылки. Результат вычислений показан курсивом;

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

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

d) Чтобы снова увидеть вычисления, нажмите кнопку Заново;

e) Чтобы завершить вычисления, нажмите кнопку Закрыть.

6. Для изменения формулы в строке формул нажмите кнопку Изменить в строке формул.

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

8. Для перехода к следующей ошибке нажмите кнопку Далее. Для возврата к предыдущей – кнопку Назад.

9. Доведите до конца проверку ошибок и закройте окно диалога Контроль ошибок.

Трассировка связей между формулами и ячейками

Для удобства поиска причин ошибок, а также и в некоторых других случаях можно графически отобразить связь между

ячейками в формуле.

Для отображения ячеек, входящих в формулу в качестве аргументов, необходимо выделить

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

Один щелчок по кнопке Влияющие ячейки отображает связи с

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

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

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

Один щелчок по кнопке Зависимые ячейки отображает связи с

ячейками, непосредственно зависящими от выделенной

ячейки. Если эти

ячейки также влияют на другие

ячейки, то следующий щелчок отображает связи с зависимыми

ячейками. И так далее.

Связи в пределах текущего листа отображаются синими стрелками. Связи с

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

ячейки, вызывающие ошибки.

Для скрытия стрелок связей следует нажать кнопку Убрать все стрелки в группе Зависимости формул вкладки Формулы. Использование окна контрольных значений.

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

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

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

Добавление ячеек в окно контрольных значений

Для добавления на панель контрольных значений выполните следующие действия:

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

Чтобы выделить все ячейки листа с формулами, на вкладке Главная в группе Правка нажмите кнопку Найти и выделить и выберите команду Формулы.

2. На вкладке Формулы в группе Зависимости формул нажмите кнопку Окно контрольного значения.

.

4. В окне диалога Добавление контрольного значения проверьте правильность выбранного диапазона и нажмите кнопку Добавить.

Циклические ссылки

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

Например, ячейка С4 = Е7, Е7 = С11, С11 = С4. В итоге С4 ссылается на С4.

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

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

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

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

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

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

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

Если циклическая ссылка – одна на листе, то в строке состояния будет выведено сообщение о наличии циклических ссылок с адресом ячейки.

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

Найти циклическую ссылку можно также при помощи инструмента поиска ошибок.

На вкладке Формулы в группе Зависимости формул выберите элемент Поиск ошибок и в раскрывающемся списке пункт Циклические ссылки.

Вы увидите адрес ячейки с первой встречающейся циклической ссылкой. После её корректировки или удаления – со второй и т. д.

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

Успехов!!!

The real power of Microsoft Excel lies in its formulae. However, as a Microsoft Excel user would know well, making mistakes with formulae are common since they are complicated. You can fix this by tracing these errors and checking suggestions for improvements. This is called Background error checking. We will show you the procedure to enable or disable the same.

What is Background error checking in Excel?

Microsoft Excel is not only about arranging data in sheets. The real purpose of the software is to make calculations. Microsoft Excel is used to create information out of random data. Formulae are used for this purpose. Since formulae in Microsoft Excel can get very complicated, it is important to check them for errors. When Microsoft Excel does this process automatically in the background, then it is called Background error checking.

Background error checking is enabled on Microsoft Excel by default. However, if a third-party extension disabled it or another user disabled the option, it can be re-enabled again as follows:

1. Launch Excel
2. Click on File.
3. From the menu, select Options.
4. Go to the Formulas tab.
5. In the Error checking section, check the box associated with Enable background error checking.
6. If you wish to disable Background error checking, then simply uncheck the box.
7. Click on OK to save the settings.

If the enabling or disabling or Background error checking is caused due to a third-party extension, you will have to disable the same.

How to change the color for Background error checking in Excel?

The color for errors marked through the Background error checking process are all green. This is because green is the default color for these marked errors. You can change this color as well. The procedure is as follows:

• Go to the Formulas window as explained earlier.
• In the Error checking group, you will find an option for Indicate error using this color.
• Change the color from the drop-down menu.

How to reset Ignored errors in Microsoft Excel?

The Background error-checking process marks errors as per a set procedure. Many errors marked by the process could be genuine formulae or instructions. In this case, you can ignore them or whitelist them. Should you be willing to reset this list of Ignored errors, then the procedure is as follows:

• Go to the Formulas window as explained earlier.
• Scroll down to the Error checking section.
• You will notice a button for Reset Ignored Errors.
• Click on it to reset the Ignored errors.

How to modify Error checking rules in Microsoft Excel?

The Background error checking is dependent on a set of rules which can be modified. Basically, these rules indicate which figure or formula will be marked as an error. The procedure to change these error-checking rules is as follows:

• Go to the Formulas window as explained earlier.
• You will notice options with checkboxes.
• Check a box to enable the associated option and uncheck the box to disable the associated option.
• Click on OK to save the settings.

How to change error checking rulesin Microsoft Excel?

The meanings of the error checking rules are as follows:

1] Cells containing formulas that result in an error

Whenever the error is with the syntax of the formula itself, then this rule comes into play. It is marked with #VALUE! or #DIV/0! .

2] Inconsistent calculated column formula in tables

This rule marks cells in which the syntax of the formula may be correct, but the formula might be inconsistent with the column. Eg. If you mark a column which doesn’t fit in the formula, then you will get an error.

3] Cells containing years represented as 2 digits

Years should be represented as 4 digits. Some people prefer them as 2 digits. This rule will mark the cell if the year is marked as 2 digits. If you did this intentionally, then you can consider unchecking the checkbox associated with this rule.

4] Numbers formatted as text or preceded by apostrophe

Writing ten and mentioning 10 is read differently by Microsoft Excel. Similarly, writing 10 and “10” is read differently by Excel. Anything other than numerical representation of numbers is not read by formulas.

5} Formulas inconsistent with other formulas in the region

When you use lots of formulas in Microsoft Excel, eventually, formulas become dependent on each other. In many cases, the value obtained by one formula is used by another one. In this case, the mentioned rule will mark an error if a formula you created in not in conjunction with other formulas of the region in the Microsoft Excel sheet.

6] Cells containing data types that couldn’t refresh

A lot of data in Microsoft Excel is picked from other sources. As an example, you can add stock market data to Microsoft Excel. Since this data keeps changing, Microsoft Excel keeps refreshing the data and uses that data for other calculations. However, imagine a case where the internet is not working, or the server of the stock market is down. In this case, the data will not refresh. This rule will mark an error to indicate the same.

7] Formulas which omit cells in a region

Formulas may or may not influence all cells in a region. However, if they do not impact each cell, the mentioned rule will come to play. Should this be done intentionally by you, the rule can be left unchecked.

8] Formulas referring to empty cells

An empty cell carries a default value of 0 but that might not always be the intention for keeping the cell empty. If a cell included in a formula is empty, it will be marked if this rule is active.

9] Data entered in a table is invalid

In case formulas are used either for assessing tables or using the data mentioned in them, the data could be inconsistent with the formula/s used. In such a situation, this rule will mark the entry in color.

10] Misleading number formats

When you read dates, time, etc, they use a specific format. This is called number format. The Misleading number format rule can help with marking such number formats.

Why does Background error checking in Excel keep disabling on its own?

A lot of functions in Microsoft Excel are managed by its extensions. These extensions can also manage the Background error checking and disable the option. Merely re-enabling it will not solve the problem for long-term. You can disable the problematic extension as follows.

• Open Microsoft Excel.
• Go to File > Options.
• In the left pane, go to the Add-ins tab.
• Corresponding to the drop-down menu associated with Manage, select COM Add-ins.
• Check an Add-in and click on Remove to delete it.

Try this method till your problem gets fixed. You will be able to figure the problematic extension using the hit and trial method.

How do I enable error checking in Excel?

To enable error checking in Excel, you need to follow the steps as mentioned above. Open the Excel Options panel first and switch to the Formulas tab. Then, find the Enable background error checking option and enable it to turn the feature on. You can also follow the article for a detailed guide.

How do you enable the Formulas referring to empty cells in the error checking rules?

As of now, there is no option to enable or disable this functionality in Microsoft Excel. However, if you want to turn on or off error checking in the same app, you can go through the above-mentioned steps. For your information, you can set various conditions to enable or disable the error checking.