Подсчет записей, соответствующих нескольким критериям

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

Предположим, вы анализируете свой список участников и хотите определить количество записей, в которых столбец пола содержит “F”, а столбец города содержит конкретный город, например “Норвуд”. Это, конечно, было бы полезно, потому что это ответило бы на животрепещущий вопрос о том, сколько женщин-членов вашей группы живет в Норвуде.

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

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

= COUNTIFS (C1: C500, «F», F1: F500, «Norwood»)

Он ищет в столбце пола (C) любые ячейки, содержащие “F”, и столбец города (F) для любых ячеек, содержащих “Norwood”. Результат – количество записей, удовлетворяющих обоим критериям.

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

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

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

= DCOUNTA (A1: K500,1, AA1: AB2)

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

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

= SUM ((C2: C500 = «F») * (F2: F500 = «Norwood»))

Просто введите приведенную выше формулу в ячейке, а затем завершите его, нажав Ctrl + Shift + Enter ; это позволяет Excel узнать, что вы вводите формулу массива. Формула работает, потому что она по очереди сравнивает содержимое каждой строки в массиве в соответствии с критериями, указанными в формуле. Сначала он сравнивает содержимое столбца C с «F»; если он совпадает, то сравнение возвращает True, что является числовым значением 1. Затем содержимое столбца F сравнивается с «Norwood». Если это сравнение верно, то возвращается 1. Таким образом, 1 * 1 будет равняться 1, и это добавляется к СУММУ массива. Если какое-либо сравнение ложно, то возвращается числовое значение 0, а 1 * 0 равно 0 (как и 0 * 0 и 0 * 1), что не влияет на текущую СУММ.

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

SUMPRODUCT ((C2: C500 = «F») * (F2: F500 = «Norwood» ))

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

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

Выберите любую ячейку в таблице данных.
Отобразить вкладку «Данные» на ленте.
Щелкните инструмент «Фильтр» в группе «Сортировка и фильтр». Excel должен отображать раскрывающиеся индикаторы автофильтра рядом с каждой меткой столбца в строке 1.
Используя раскрывающийся индикатор для столбца пола (столбец C), выберите F. Ваш список автоматически фильтруется. для отображения только женщин-членов.
Используя раскрывающийся индикатор для столбца города (столбец F), выберите Norwood. В вашем списке автоматически отображаются только женщины-члены, проживающие в Норвуде.
Внизу таблицы данных (строка 501) в любом столбце введите следующую формулу:

= SUBTOTAL (3, C2: C500)

Эта формула заставляет функцию SUBTOTAL применять функцию COUNTA для получения промежуточного итога. Другими словами, он возвращает количество всех записей, отображаемых при фильтрации; это желаемый счетчик.

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

Выберите ячейку где-нибудь в данных, которые вы хотите проанализировать.
Отобразите вкладку “Формулы” ленты.
В группе “Решения” (в правой части ленты) щелкните “Условная сумма”. Excel отображает первый шаг мастера условной суммы. Полный диапазон ваших данных уже должен быть показан в диалоговом окне. (См. Рис. 2.)

Рис. 2. Мастер условной суммы.

Нажмите Далее. Excel отобразит следующий шаг мастера.
В раскрывающемся списке “Столбец для суммирования” выберите “Пол”.
В раскрывающемся списке “Столбец” выберите “Пол”.
В раскрывающемся списке “Is” выберите знак равенства.
В раскрывающемся списке “Это значение” выберите F.
Щелкните Добавить. Указанное вами условие теперь отображается в диалоговом окне.
В раскрывающемся списке “Столбец” выберите “Город”.
В раскрывающемся списке “Is” выберите знак равенства. .
В раскрывающемся списке “Это значение” выберите Norwood.
Нажмите “Добавить”. Второе условие теперь появляется в диалоговом окне.
Нажмите “Далее”. Excel отображает третий шаг мастера.
Выберите “Копировать только формулу в одну ячейку”.
Нажмите “Далее”. В Excel отобразится четвертый (и последний) шаг мастера.
На листе щелкните ячейку, в которой должен содержаться результат формулы.
Щелкните по окончании.

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

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

Источник: frestage.ru