Найти счетчик с несколькими критериями ISNA с логикой в Excel

Posted on: 2020年10月22日 2020年11月12日
Tags: counting

В этой статье мы научимся находить счетчик по нескольким критериям с логикой ISNA в Excel.

Сценарий:

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

Как решить проблему?

Для этой статьи нам потребуется использовать link: / lookup-formulas-excel-match-function [MATCH function] & link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Теперь составим формулу из упомянутой функции.

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

Используйте формулу:

SUMPRODUCT

( ( range = criteria ) * (

ISNA

(

MATCH

( rng , cri_rng , 0 ) ) )

диапазон: первый диапазон для соответствия первому критерию критерии: критерии для совпадения с первым диапазоном rng: второй диапазон, где критерии для исключения cri_rng: значения для исключения из второго диапазона

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже.

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

Должна быть сотрудница женского пола, чтобы массив Gender соответствовал F.
Не должны включать сотрудников из групп А и Б.

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

Формула:

SUMPRODUCT

( ( Gender = H5 ) * (

ISNA

(

MATCH

( Groups , I5:I6 , 0 ) ) )

Пояснение:

link: / lookup-formulas-excel-match-function [MATCH]

(Группы, I5: I6,0) сопоставляет все значения группы с заданными значениями критериев и возвращает массив чисел и значений #NA.

Функция ПОИСКПОЗ возвращает индекс для совпавших значений и #NA! Ошибка несогласованных значений.
ISNA возвращает ИСТИННОЕ значение для соответствующего совпавшего #NA! Значения и FALSE для возвращенных значений индекса.
Gender = H5, это возвращает все совпадающие значения как ИСТИНА и несопоставленные как ЛОЖЬ.
Функция СУММПРОИЗВ возвращает количество совпавших ИСТИННЫХ значений, соответствующих обоим массивам.

Здесь массив функции дается в виде именованных диапазонов, как указано в ячейках желтого цвета.

Именованные диапазоны, используемые в формуле:

Пол (E4: E17)

ID (D4: D17)

Группы (F4: F17)

Значение одного аргумента, указанное как ссылка на ячейку. Нажмите Enter, чтобы получить результат.

Как вы можете видеть на снимке выше, количество сотрудников составляет 3. Три сотрудника — женщины, не принадлежащие ни к группе A, ни к группе B.

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

Вот все заметки по использованию формулы.

Примечания:

Формула работает только с числами и текстовыми значениями.
Массив с полем и группами должен быть одинаковой длины, иначе функция СУММПРОИЗВ вернет ошибку.
Такие операторы, как равно (=), меньше, чем равно (⇐), больше (>) или не равно (<>), могут выполняться внутри функции, применяемой только с числами.

Надеюсь, эта статья о том, как найти счетчик с несколькими критериями с помощью логики ISNA в Excel, будет пояснительной. Дополнительные статьи о функциях СЧЁТЕСЛИ здесь. Если вам понравились наши блоги, поделитесь ими со своими друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected]