image

В этой статье мы узнаем, как подсчитывать строки, если совпадают хотя бы n чисел в Excel.

Сценарий:

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

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

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

  1. link: / math-and-trig-excel-sum-function [функция СУММ]

  2. link: / tips-excel-mmult-function [Функция MMULT]

  3. link: / Mathematical-functions-excel-transpose-function [Функция TRANSPOSE]

  4. link: / lookup-and-reference-excel-column-function [COLUMN function]

Мы построим формулу из этих заявленных функций.

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

\{*

=SUM (
 — ( MMULT ( — 
( (data) < crit ) ,

TRANSPOSE

(

COLUMN

(data) ^ 0 ) ) = n ) ) }

Примечание. Для фигурных скобок (\ {}) Используйте Ctrl + Shift + Enter * при работе с массивами или диапазонами в Excel. По умолчанию в формуле будут созданы фигурные скобки. НЕ пытайтесь жестко кодировать фигурные скобки.

данные: набор чисел в виде данных.

n: точное количество появлений (используйте ⇐ не более или> = для минимума)

крит: критерии для соответствия

Пример:

Все это может сбивать с толку. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь у нас есть данные о продажах для конкретного идентификатора продаж в строке. Нам нужно найти количество идентификаторов продаж, в которых продавец не завершил продажи 40 ровно 2 раза. Здесь критерий ois <40 и вхождение равно 2 (= 2 для точного появления)

Формула:

\{*

=SUM (
 — ( MMULT ( — 
( (C4:H15) < 40 ) ,

TRANSPOSE

(

COLUMN

(C4:H15) ^ 0 ) ) = 2 ) )

Пояснение:

Функция COLUMN возвращает массив номеров столбцов от первой ячейки в массиве до последней ячейки в массиве. \ {3; 4; 5; 7; 8} ^ 0, эта операция с любыми числами массива возвращает массив единиц.

Функция TRANSPOSE преобразует строки матричного массива в столбцы и столбцы в строки. Здесь массив единиц в строке преобразуется в массив из 6 единиц в столбцах. \ {1; 1; 1; 1; 1; 1} — ((C4: H15) <40), эта операция возвращает значения как единицы как ИСТИНА (совпадающие критерии) и нули как значения Ложь.

  • Функция MMULT принимает матричное умножение двух массивов и возвращает значения, как показано ниже.

СУММ (- (\ {1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))

  • Функция СУММ подсчитывает значения в массиве, совпадающем с 2, как требуется вхождение.

  • Используйте Ctrl + Shift + Enter вместо, чтобы получить результат. Поскольку это формула массива.

image

Здесь массивы функции даны как ссылки на массив. Используйте двойной (-) вручную. Нажмите Enter, чтобы получить результат.

image

Как вы можете видеть на изображении выше, есть 2 продавца, которые не завершили даже 40 продаж. Это счетчик для соответствующих строк в данных, где 2 числа меньше 40. Вы можете настроить вхождение счетчика и критерии проверки в строках таблицы. Ниже приведен пример получения счетчика с разными критериями и разными вхождениями.

Подсчитать строки, соответствующие продажам выше 100, с точным совпадением 3:

Теперь мы хотели бы узнать, сколько продавцов имеют умеренно хорошие продажи. Для этого нам нужно получить количество продавцов, которые совершили продажи более 100 ровно в 3 раза.

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

= SUM (
 — ( MMULT ( — 
( (C4:H15) > 100 ) ,

TRANSPOSE

(

COLUMN

(C4:H15) ^ 0 ) ) = 3 ) )

100: число больше 100.

3: вхождение равно 3

image

Здесь массивы функции даны как ссылки на массив. Используйте двойной (-) вручную. Нажмите Enter, чтобы получить результат.

image

Как видите, у трех продавцов все в порядке.

Это счетчик для соответствующих строк в данных, где 3 продажи больше 100.

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

Примечания:

  1. Формула работает только с числами.

  2. Функции MMULT и TRANSPOSE являются матричными функциями, поэтому проверьте их перед использованием.

  3. Если формула возвращает ошибку #VALUE, проверьте, должны ли присутствовать фигурные скобки в формуле, как показано в примерах в статье.

  4. Отрицание (-) char изменяет значения, TRUE или 1 на FALSE или 0 и FALSE или 0 на TRUE или 1.

  5. Такие операции, как равно (=), меньше, чем равно (⇐), больше (>) или не равно (<> *), могут выполняться в рамках применяемой формулы, только с числами.

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

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

link: / lookup-formulas-find-the-last-row-of-data-in-excel [Найти последнюю строку данных с числами в Excel]: * В диапазоне текстовых значений найти последние данные в отличиться.

link: / summing-excel-sumproduct-function [Как использовать функцию СУММПРОИЗВ в Excel]: возвращает СУММ после умножения значений в нескольких массивах в Excel.

link: / counting-countifs-with-dynamic-conditions-range [COUNTIFS с динамическим диапазоном критериев]: подсчет ячеек, не зависящих от других значений ячеек в Excel.

link: / counting-countifs-two-criterion-match [COUNTIFS Two Criteria Match]: Подсчет ячеек, соответствующих двум различным критериям, в списке в Excel.

link: / counting-countifs-with-or-for-множественные критерии [СЧЕТЕСЛИ С ИЛИ для нескольких критериев]: Подсчет ячеек, имеющих совпадение по нескольким критериям, с помощью функции ИЛИ.

link: / counting-the-countifs-function-n-excel [Функция COUNTIFS в Excel]: подсчет ячеек в зависимости от других значений ячеек.

link: / tips-how-to-use-countif-in-vba-in-microsoft-excel [Как использовать Countif в VBA в Microsoft Excel]: Подсчет ячеек с помощью кода Visual Basic для приложений.

link: / tips-excel-wildcards [Как использовать подстановочные знаки в Excel]: Подсчет ячеек, соответствующих фразам, с использованием подстановочных знаков в excel

Популярные статьи:

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.

link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы. link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.