Note: The other languages of the website are Google-translated. Back to English

Подсчитайте количество ячеек, не равное множеству значений в Excel

В Excel вы можете легко получить количество ячеек, не равное определенному значению, используя функцию СЧЁТЕСЛИ, но пробовали ли вы когда-нибудь подсчитать количество ячеек, которые не равны множеству значений? Например, я хочу получить общее количество продуктов в столбце A, но исключить конкретные элементы в C4: C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.


Подсчитайте количество ячеек, не равное множеству значений с формулой 1

Чтобы подсчитать ячейки, не равные множеству указанных значений, вы можете использовать комбинацию функций ПОИСКПОЗ, ISNA и СУММПРОИЗВ, общий синтаксис:

= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
  • range: Список ячеек, которые вы хотите подсчитать;
  • exclude_values: Значения из списка диапазонов, которые следует игнорировать.

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

=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))

2, Затем нажмите Enter ключ, чтобы получить правильный результат, см. снимок экрана:


Пояснение к формуле:
  • ПОИСКПОЗ (A2: A13; C2: C4,0): Эта функция ПОИСКПОЗ возвращает положение каждого совпадающего значения в виде числа и возвращает значение ошибки # Н / Д, если оно не найдено. Это означает, что когда значения в A2: A13 равны значениям в C2: C4, он вернет число, в противном случае он вернет ошибку #NA. Вот результат: {1; # N / A; # N / A; 1; 3; 2; # N / A; # Н / Д; 3; # Н / Д; # N / A; # Н / Д;}.
  • ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): Эта функция ISNA заставляет значения ошибок становиться ИСТИННЫМИ, а числа - НЕОБХОДИМЫМИ, поэтому вы получите следующее: {ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА} .
  • - (ISNA (MATCH (A2: A13, C2: C4,0)))) = - ({ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА}): Двойной отрицательный оператор преобразует все ИСТИНА в 1 и ЛОЖЬ в 0 следующим образом: СУММПРОИЗВ (- (ISNA (ПОИСКПОЗ (A2: A13, C2: C4,0)))) = СУММПРОИЗВ ({0; 1; 1 ; 0; 0; 0; 1; 1; 0; 1; 1; 1;}).
  • Затем функция СУММПРОИЗВ используется для суммирования чисел в массиве и получает результат: 7.

Подсчитайте количество ячеек, не равное множеству значений с формулой 2

Чтобы решить эту задачу в Excel, вы также можете применить комбинацию функций СЧЁТ, СУММПРОИЗВ и СЧЁТЕСЛИ, общий синтаксис:

=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
  • range: Список ячеек, которые вы хотите подсчитать;
  • exclude_values: Значения из списка диапазонов, которые следует игнорировать.

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

=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))

2, Затем нажмите Enter ключ, чтобы получить результат, как показано на следующем снимке экрана:


Пояснение к формуле:
  • СЧЕТ (A2: A13): Эта функция COUNTA возвращает количество всех непустых ячеек в диапазоне A2: A13. И получит результат: 12.
  • СЧЁТЕСЛИ (A2: A13; C2: C4): Эта функция СЧЁТЕСЛИ подсчитывает ячейки в диапазоне A2: A13, которые равны критерию в C2: C4, поэтому вы получите следующий результат: {2; 1; 2}, что означает, что яблоко появляется дважды, виноград появляется один раз и появляется лимон дважды.
  • SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): Эта функция СУММПРОИЗВ суммирует все элементы в массиве и возвращает окончательный результат: 5.
  • COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 : Результат (5) функции СУММПРОИЗВ вычитается из количества всех непустых ячеек (12) для получения необходимого окончательного результата.

Примечание. Обе приведенные выше формулы не чувствительны к регистру.


Используемая относительная функция:

  • МАТЧ:
  • Функция ПОИСКПОЗ ищет определенное значение в диапазоне ячеек и возвращает относительное положение этого значения.
  • ИСНА:
  • Функция ISNA используется для проверки того, возвращает ли формула ошибку # N / A.
  • SUMPRODUCT:
  • Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
  • COUNTIF:
  • Функция СЧЁТЕСЛИ - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию.
  • COUNTA:
  • Функция COUNTA возвращает количество ячеек, исключая пустые ячейки.

Другие статьи:

  • Подсчитать количество ячеек равно одному из многих значений в Excel
  • Предположим, у меня есть список продуктов в столбце A, теперь я хочу получить общее количество конкретных продуктов Apple, Grape и Lemon, которые перечислены в диапазоне C4: C6 из столбца A, как показано на скриншоте ниже. Обычно в Excel простые функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН не работают в этом сценарии. В этой статье я расскажу о том, как быстро и легко решить эту задачу с помощью комбинации функций СУММПРОИЗВ и СЧЁТЕСЛИ.
  • Подсчитайте количество ячеек не между двумя заданными числами в Excel
  • Подсчет количества ячеек между двумя числами - обычная задача для нас в Excel, но в некоторых случаях вы можете захотеть подсчитать ячейки не между двумя заданными числами. Например, у меня есть список продуктов с продажами с понедельника по воскресенье, теперь мне нужно получить количество ячеек, которое не находится между конкретными минимальными и высокими числами, как показано ниже. В этой статье я представлю некоторые формулы для решения этой задачи в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0  Характеристики
Предлагаемые места