Формула Excel для подсчета ячеек, если не null, недействительна или 0

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

=COUNTIF(A2:A200,"<>0") 

Это проверяет, не имеет ли ячейка значение «0». Что я могу добавить к нему, чтобы он не считал пустые ячейки или ячейки со значениями, такими как:

 ####### VALUE? r 

и т. д. Все, что я хочу, – подсчитать, сколько ячеек имеет число в них, которое не равно 0, или ошибка.

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

Вам нужно нажать CTRL + SHIFT + ENTER, чтобы правильно выполнить эту формулу:

 =COUNTIFS(A2:A200,"<>0", A2:A200,"<>"&"", A2:A200,"<>"&"NIL") - SUM(IF(ISERROR(A2:A200),1,"")) - SUM(IF(ISNA(A2:A200),1,"")) 

Эта формула будет считать только числа <> 0, исключая пробелы, сообщения об ошибках и т. Д., Но это не исключает ячейки, которые отображают ###### (но действительно содержат число), если причиной этого является столбец, который является слишком узким или отрицательным значением даты или времени.

  =SUMPRODUCT(--ISNUMBER(A2:A200))-COUNTIF(A2:A200,0) 

Если вы действительно хотите избежать подсчета ячеек, отображающих #######, когда базовое содержимое – это число, не равное нулю, вам нужно будет использовать UDF, чтобы действовать на свойство Text ячейки. Кроме того, сужение или расширение столбца для создания этого эффекта не вызовет событие вычисления, которое обновит формулу, поэтому вам нужно как-то это сделать, чтобы убедиться, что результаты формулы верны.

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

Чтобы ввести эту пользовательскую функцию (UDF), alt-F11 открывает редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите «Вставить / Модуль» и вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу типа

  =CountNumbersNEZero(A2:A200) 

в некоторой ячейке.

 Option Explicit Function CountNumbersNEZero(rg As Range) As Long Application.Volatile Dim C As Range Dim L As Double For Each C In rg If IsNumeric(C.Text) Then If C.Text <> 0 Then L = L + 1 End If Next C CountNumbersNEZero = L End Function 

Вы можете вложить до 7 действительных или недопустимых записей. Если вам нужно больше, чем вы, возможно, вы должны указать столбец для вашего «черного списка» записей, которые вы можете добавить, когда возникновение вызывает подсчет, который, как вы считаете, недействителен. Например:

 =IF(ISERR(VLOOKUP(A1,Sheet1!E:E,1,FALSE))=FALSE,1,0) 

Если столбец «E» – это ваш список значений, которые считаются недействительными. Перетащите это рядом с вашими критериями и суммой.

Изменить: я не знал о счетах. Итак, у вас есть несколько решений, здесь, в зависимости от ваших предпочтений.

  • Остановить Excel после обновления формулы после согласования критериев
  • Добавить гиперссылку и формулу внутри одной ячейки
  • Поиск значения с похожими заголовками в нескольких столбцах
  • Многоуровневая сумма (связанные дочерние уровни с родительским уровнем) без макроса
  • Заполнить функцию соответствия индексу
  • формула для нахождения первой непустой ячейки
  • Как использовать функции, такие как NUMBERVALUE () для всего столбца / диапазона в Excel?
  • Поиск второго по величине значения в определенном наборе значений столбцов (без создания нового столбца)
  • Запишите имя каждого значения, которое появляется 5 раз в 5 столбцах
  • Индексное совпадение, если начинается с
  • используя vlookup с excel
  • Interesting Posts

    Файл VBA Excel в CSV хранит имя файла CSV так же, как оригинальная книга

    displaytag экспортирует excel показывает номера в научном формате

    Номер недели, который перекрывает 2 года в excel

    Спецификация Excel VBA – RegExp

    Excel – VBA: упростить это утверждение If, сравнивающее ячейку со словами

    Как определить, завершился ли расчет, или определить прерывание расчета?

    Excel Преобразование изображений

    сохранить файл перед автоматическим закрытием рабочей книги

    Excel VBA для выбора диапазона на основе значения ячейки

    Скопировать и вставить сбой vba

    Excel VBA – вставить строку над определенными форматами текста и копирования и формулой

    Данные TextBox обрезаются при экспорте в Excel 2010 из SSRS 2008

    Редактировать параметры автозаполнения excel

    Прокрутите все вложенные папки и файлы в папке и напишите последнюю измененную информацию о дате в электронную таблицу Excel

    Макрос сохраняется в личном кабинете, но не отображается в списке макросов

    Давайте будем гением компьютера.