Подсчет записей по нескольким критериям (Microsoft Excel)

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

Предположим, вы анализируете свой список участников и хотите определить количество записей, в которых в столбце «Пол» содержится «F»

а столбец города содержит конкретный город, например «Норвуд». Это, конечно, было бы полезно, потому что ответило бы на животрепещущий вопрос о том, сколько женщин в вашей группе живет в Норвуде.

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

Давайте рассмотрим шесть конкретных способов достижения желаемой цели участниц из Норвуда. (Предположим, что столбец C — это столбец пола, а столбец F — столбец города.)

Первый способ решить проблему — использовать функцию СЧЁТЕСЛИМН. Если ваш столбец пола — столбец C, а столбец вашего города — столбец F, вы можете использовать следующую формулу:

Он ищет в столбце пола © любые ячейки, содержащие «F», и столбец города (F) — на наличие любых ячеек, содержащих «Норвуд». В результате получается количество записей, удовлетворяющих обоим критериям.

Второй подход — использовать функцию DCOUNTA. Эта функция позволяет вам определять набор критериев и использовать эти критерии в качестве основы для анализа списка данных. Как и все функции данных в Excel, DCOUNTA полагается на три параметра: диапазон данных, столбец, используемый при сравнении, и диапазон критериев. Чтобы использовать эту функцию, настройте таблицу критериев в неиспользуемой области рабочего листа. Например, вы можете установить следующее в ячейках с AA1 по AB2: (См. Рисунок 1.)

Рисунок 1. Таблица критериев для функции DCOUNTA.

Затем, предполагая, что ваша исходная таблица данных находится в ячейках A1: K500 (очевидно, клуб заводчиков крупных пуделей), вы можете использовать следующее для определения количества:

Результатом будет счетчик, который соответствует критериям, указанным в AA1: AB2.

Также обратите внимание, что имена, которые вы использовали в AA1 и AB1, должны точно соответствовать меткам, которые вы использовали в записях таблиц. Когда они это сделают, содержимое столбца «Пол» (столбец C) должно быть «F», а содержимое столбца «Город» (столбец F) должно быть «Norwood», чтобы запись была добавлена ​​к счетчику.

Третье решение — использовать формулу массива для возврата единственного ответа.

В формуле массива интересно использовать функцию СУММ и немного логической арифметики, чтобы определить, следует ли считать запись.

Рассмотрим следующее:

Просто введите указанную выше формулу в ячейку, а затем завершите ее, нажав Ctrl + Shift + Enter; это позволяет Excel узнать, что вы вводите формулу массива. Формула работает, поскольку она по очереди сравнивает содержимое каждой строки в массиве в соответствии с критериями, указанными в формуле. Сначала он сравнивает содержимое столбца C с «F»; если оно совпадает, то сравнение возвращает True, что является числовым значением 1.

Затем содержимое столбца F сравнивается с «Норвудом». Если это сравнение верно, то возвращается 1. Таким образом, 1 1 будет равняться 1, и это добавляется к SUM массива. Если какое-либо сравнение ложно, то возвращается числовое значение 0, а 1 0 равно 0 (как и 0 0 и 0 1), что не влияет на текущую СУММ.

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

Помните, что это не формула массива, поэтому вам не нужно нажимать Ctrl + Shift + Enter. Формула снова работает благодаря магии логической математики.

Пятое возможное решение, которое требует больше ручного труда, чем те, которые уже обсуждались, — это использование функции автофильтра вместе с промежуточным итогом. Предполагая, что ваши записи данных находятся в формате A1: K500 с метками столбцов в строке 1, вы должны выполнить следующие шаги:

Эта формула приводит к тому, что функция ПРОМЕЖУТОЧНЫЙ ИТОГ применяет функцию СЧЁТ, чтобы получить промежуточный итог. Другими словами, он возвращает количество всех записей, отображаемых при фильтрации; это желаемое количество.

Шестой подход заключается в использовании мастера условной суммы, чтобы придумать для вас формулу. (Мастер условной суммы доступен как надстройка Excel для Excel 2007 и более ранних версий; он включен в большинстве систем по умолчанию. Он был удален из Excel 2010.) Чтобы использовать мастер условной суммы, выполните следующие действия:

Результатом является формула, соответствующая указанным вами условиям, в ячейке, выбранной на шаге 1.

Несомненно, существует бесчисленное множество других возможных решений, которые вы могли бы использовать для подсчета количества записей. Это, однако, «выбор из всех», позволяющий быстро и легко определить ответ.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (7759) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Counting_Records_Matching_Multiple_Criteria [Подсчет записей, соответствующих нескольким критериям].