Подсчитайте количество ячеек, не равное множеству значений в Excel
В Excel вы можете легко получить количество ячеек, не равное определенному значению, используя функцию СЧЁТЕСЛИ, но пробовали ли вы когда-нибудь подсчитать количество ячеек, которые не равны множеству значений? Например, я хочу получить общее количество продуктов в столбце A, но исключить конкретные элементы в C4: C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.
- Подсчитайте количество ячеек, не равное множеству значений с формулой 1
- Подсчитайте количество ячеек, не равное множеству значений с формулой 2
Подсчитайте количество ячеек, не равное множеству значений с формулой 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
- Подсчет количества ячеек между двумя числами - обычная задача для нас в Excel, но в некоторых случаях вы можете захотеть подсчитать ячейки не между двумя заданными числами. Например, у меня есть список продуктов с продажами с понедельника по воскресенье, теперь мне нужно получить количество ячеек, которое не находится между конкретными минимальными и высокими числами, как показано ниже. В этой статье я представлю некоторые формулы для решения этой задачи в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - Помогает вам выделиться из толпы
Kutools for Excel Имеет более 300 функций, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Office Tab - Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
