Как посчитать ошибки в Excel с учетом их кодов

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

Как посчитать ошибку в формуле Excel

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

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

В ячейку C1 введите следующую формулу:

Данная формула должна быть выполнена в массиве, поэтому после ее ввода для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Таким образом получаем текущее количество ошибок в таблице.

Разбор формулы для подсчета количества всех ошибок в ячейках Excel:

С помощью функции ЕОШИБКА проверена каждая ячейка диапазона A2:A9 на наличие ошибочных значений. Результаты функции в памяти программы образуют собой массив логических значений ИСТИНА и ЛОЖЬ. После перемножения каждого логического значения на число 1 в результате получаем массив из чисел 1 и 0. Потом все элементы массива суммируются, а формула возвращает количество ошибок.

Как найти первую ошибку в значении Excel

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

Она также должна быть выполнена в массиве поэтому снова для подтверждения нажмите комбинацию горячих клавиш CTRL+SHIFT+Enter.

Первая ошибка находиться в третьей строке рабочего листа Excel.

Рассмотрим, как работает такая формула:

Наподобие первой формулы с помощью функции ЕОШИБКА в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. Далее функция СТРОКА возвращает текущие номера строк листа в диапазоне A2:A9. Благодаря функции ЕСЛИ в массиве с логическими значениями ИСТИНА заменяется на текущий номер строки. После чего функция МИН выбирает наименьшее число из этого же массива.

Как посчитать ошибки Excel с определенным кодом

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

На этот раз формула не должна выполняться в массиве поэтому после ввода для ее подтверждения достаточно просто нажать клавишу Entеr.

Третья формула возвращает количество ошибок деления на 0 (#ДЕЛ/0!). Но она не мене эффективно работает если во втором аргументе функции СЧЕТЕСЛИ указать другой тип ошибки в ячейках Excel. Например, #ИМЯ?

Как видно на рисунке все работает не менее эффективно.

Чтобы узнать в какой строке встречается первая ошибка конкретного типа и кода следует использовать четвертую формулу:

Как показано на очередном рисунке, формула возвращает значение 4 которое соответствует номеру строки где впервые встречается ошибка деления на 0.

Коды и типы ошибок Excel

Функция ТИП.ОШИБКИ проверяет каждую ячейку в диапазоне A1:A9, если она наталкивается на ошибку возвращает соответствующий ей номер (например, код ошибки деления на ноль: для типа #ДЕЛ/0! – это код 2). Ниже приведена целая таблица типов и кодов для обработки ошибок Excel:

ТИП	КОД
#ПУСТО!	1
#ДЕЛ/0!	2
#ЗНАЧ!	3
#ССЫЛКА!	4
#ИМЯ?	5
#ЧИСЛО!	6
#Н/Д	7
#ОЖИДАНИЕ_ДАННЫХ	8

Далее создается в памяти массив значений с номерами кодов ошибок. В первом аугменте функции ПОИСКПОЗ мы указываем код ошибки, которую нужно найти. В третьем аргументе мы указываем код 0 для функции ПОИСКПОЗ, который означает что возвращать нужно первое встречающееся значение 2 при наличии дубликатов в массиве.

Внимание! В четвертой формуле мы ссылались на диапазон ячеек начиная с A1 и до A9. Потому как функция ПОИСКПОЗ возвращает текущею позицию значения относительно таблицы, а не целого листа. Поэтому во втором аргументе функции ПОИСКПОЗ следует указывать диапазон просматриваемых значений так, чтобы номера позиций совпадали с номерами строк листа. Другими словами, если бы мы указали адрес диапазона A2:A9, то формула вернула бы значение 5 – что не является правильным.