Excel посчитать ячейки с ошибками

Как подсчитать количество ячеек с ошибками / ячеек без ошибок в Excel?

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

Подсчитайте количество всех типов ошибок в диапазоне

Подсчитайте количество конкретных типов ошибок в диапазоне

Подсчитайте количество ячеек, игнорирующих ошибки, в диапазоне

Выберите и подсчитайте количество ошибок в диапазоне одним щелчком мыши

Преобразование ошибок в 0 в диапазоне с Kutools for Excel

Подсчитайте количество всех типов ошибок в диапазоне

Как все мы знаем, существует несколько типов ошибок, таких как # DIV / 0 !, #REF !, #VALUE! и так далее, когда формулы работают неправильно, вы можете подсчитать количество всех типов ошибок с помощью простой формулы массива. Пожалуйста, сделайте так:

1. В пустой ячейке введите эту формулу = СУММ (ЕСЛИ (ЕСТЬ ОШИБКА (A1: C10); 1)), см. снимок экрана:

2, Затем нажмите Ctrl + Shift + Enter вместе, и вы получите количество всех значений ошибок диапазона.

Внимание: В приведенной выше формуле A1: C10 — это диапазон, который вы хотите использовать, вы можете изменить его по своему усмотрению.

Подсчитайте количество конкретных типов ошибок в диапазоне

Иногда вам просто нужно подсчитать только определенный тип ошибок, например, чтобы узнать, сколько # DIV / 0! погрешности в диапазоне. В этом случае приведенная выше формула не будет работать, здесь вам может помочь функция СЧЁТЕСЛИ.

1. В пустой ячейке введите эту формулу = СЧЁТЕСЛИ (A1: C10; «# ДЕЛ / 0!»), см. снимок экрана:

2, Затем нажмите Enter ключ и номер # DIV / 0! Ячейки с ошибками будут подсчитаны.

Внимание: В приведенной выше формуле A1: C10 это диапазон, который вы хотите использовать, и # DIV / 0! это ошибка типа, которую вы хотите подсчитать, при необходимости вы можете заменить ее.

Подсчитайте количество ячеек, игнорирующих ошибки, в диапазоне

Если вы хотите подсчитать количество ячеек без ошибок, вы можете использовать эту формулу массива: = СУММ (ЕСЛИ (НЕ (ОШИБКА (A1: C10)); 1)), а затем нажмите Ctrl + Shift + Enter клавиши одновременно. И все ячейки, игнорирующие ячейки с ошибками, будут вычислены (включая пустые ячейки). Смотрите скриншоты:

Выберите и подсчитайте количество ошибок в диапазоне одним щелчком мыши

Если у вас есть Kutools for Excel установлен, вы можете быстро подсчитать количество ячеек с ошибками одним щелчком мыши, и в то же время вы можете выбрать ошибки.

После установки Kutools for Excel, пожалуйста, сделайте, как показано ниже Бесплатная загрузка Kutools for Excel Сейчас!)

Выберите используемый диапазон и нажмите Kutools > Выберите > Выберите ячейки со значением ошибки. Смотрите скриншот:

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

Если вы хотите выбрать и подсчитать ячейки без ошибок, после применения Kutools for Excel‘s Выберите ячейки со значением ошибки утилита, оставьте ошибки выбранными и нажмите Kutools > Выберите > Выбрать помощника по диапазону, затем в появившемся диалоговом окне отметьте Обратный выбор чтобы инвертировать выбор ячеек, и все ячейки, игнорирующие ошибки, были выбраны, и вы можете просмотреть результат подсчета в строке состояния. Смотрите скриншоты:

Преобразование ошибок в 0 в диапазоне с Kutools for Excel

В некоторых случаях вам может потребоваться преобразовать все значения ошибок в 0 или ничего или в другие специальные тексты, и Kutools for Excel‘s Мастер условий ошибки функция может помочь вам быстро с этим справиться.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон, содержащий ошибки, и нажмите Kutools > Больше (в группе Формула)> Мастер условий ошибки. Смотрите скриншот:

2. в Мастер условий ошибки в диалоговом окне укажите нужный вариант в Отображение ошибки раздел. Смотрите скриншот:

3. Нажмите Ok, и теперь все значения ошибок в выбранном диапазоне были преобразованы.

Статьи по теме:

Как изменить # DIV / 0! ошибка читабельному сообщению в excel?

Как суммировать диапазон ячеек без учета ошибок в Excel?

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

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

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

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

1. В ячейку C1 введите следующую формулу:
2. Данная формула должна быть выполнена в массиве, поэтому после ее ввода для подтверждения нажмите комбинацию горячих клавиш 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:

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

Читайте также: Как найти ошибку в таблице Excel по формуле

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

Перейти к содержанию