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

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

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

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

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

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

| AA | AB

| 1 | Пол | Город

| 2 | F | Норвуд

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

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

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

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

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

=SUM((B2:B500="F")*(H2:H500="Norwood"))

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

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

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

SUMPRODUCT((B2:B500="F")*(H2:H500="Norwood"))

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

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

  1. Выберите любую ячейку в таблице данных.

  2. Выберите «Фильтр» в меню «Данные», затем выберите «Автофильтр». Excel должен отображать раскрывающиеся индикаторы Автофильтра рядом с каждой меткой столбца в строке 1.

  3. Используя раскрывающийся индикатор для столбца пола (столбец B), выберите F. Ваш список автоматически фильтруется, чтобы отображались только члены женского пола.

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

  5. Внизу таблицы данных (строка 501) в любом желаемом столбце введите следующую формулу:

=SUBTOTAL(3,B2:B500)

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

Пятый подход — использовать мастер условной суммы, чтобы придумать формулу для вас. (Мастер условной суммы доступен как надстройка Excel. Выберите «Инструменты | Надстройки», чтобы убедиться, что мастер установлен и доступен.) Чтобы использовать мастер условной суммы, выполните следующие действия:

  1. Выберите ячейку где-нибудь в данных, которые вы хотите проанализировать.

  2. Выберите Условная сумма в меню Инструменты. Excel отображает первый шаг мастера условной суммы. Полный диапазон ваших данных уже должен отображаться в диалоговом окне. (См. Рис. 1.)

  3. Щелкните Далее. Excel отображает следующий шаг мастера.

  4. В раскрывающемся списке Столбец для суммирования выберите Пол.

  5. В раскрывающемся списке Столбец выберите Пол.

  6. В раскрывающемся списке Is выберите знак равенства.

  7. В раскрывающемся списке «Это значение» выберите F.

  8. Щелкните по Добавить. В диалоговом окне появится указанное вами условие.

  9. В раскрывающемся списке Столбец выберите Город.

  10. В раскрывающемся списке Is выберите знак равенства.

  11. В раскрывающемся списке This Value выберите Norwood.

  12. Щелкните по Добавить. В диалоговом окне появится второе условие.

  13. Щелкните Далее. Excel отображает третий шаг мастера.

  14. Выберите «Копировать только формулу в одну ячейку».

  15. Щелкните Далее. Excel отображает четвертый (и последний) шаг мастера.

  16. На листе щелкните ячейку, в которой должен содержаться результат формулы.

  17. Щелкните Готово.

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

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

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

Этот совет (2809) относится к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

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