Подсчет на основе критериев в столбце с фильтрами (Microsoft Excel)

У Марти есть большой рабочий лист, в котором перечислены все сотрудники (бывшие и настоящие) в его компании. Рабочий лист отслеживает различную информацию о каждом сотруднике, такую ​​как имя, адрес, отдел, пол, статус и т. Д.

Марти часто фильтрует данные в соответствии со своими потребностями. Ему нужен способ определения процентной доли мужчин и женщин в зависимости от того, какие строки отображаются после фильтрации. Функция ПРОМЕЖУТОЧНЫЙ ИТОГ может работать с отфильтрованным столбцом для предоставления различных подсчетов, но она не позволяет ему определять подсчеты на основе содержимого («M» или «F») отфильтрованного столбца.

Один из подходов — использовать сводную таблицу для определения процентов.

Сводные таблицы относительно просты в использовании, особенно для ответа на один такой вопрос, как этот. Однако они не слишком хороши, позволяя вам видеть подробную информацию о ваших сотрудниках — вы можете увидеть совокупный ответ на мужской / женский вопрос, но в то же время вы не можете увидеть подробную информацию об этих сотрудниках. Итак, я хочу сосредоточиться на использовании прямых формул в ответ на проблему Марти.

Создать формулу для получения желаемых процентов сложнее, чем кажется на первый взгляд. Например, легко определить счетчики, когда видны все записи о сотрудниках. Вы можете, например, просто использовать что-то вроде этого, предполагая, что пол указан в столбце C, чтобы определить, какой процент записей относится к сотрудникам-мужчинам:

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

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

Однако это не сработает для желаемых нужд. Основная проблема заключается в том, что функция ПРОМЕЖУТОЧНЫЙ ИТОГ подсчитывает, какие записи видны, но нет различия между тем, содержат ли эти записи «M» или «F» в столбце C. Вторичная проблема заключается в том, что COUNTA считает все записи, а не только те, которые отображаются. Таким образом, формула не дает вам процент отображаемых записей, содержащих «M» или «F»

но вместо этого процент от общего количества отображаемых записей.

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

Это можно сделать с помощью простой формулы, например такой:

Эта формула предполагает, опять же, что столбец пола — C. Предполагая, что эта формула помещена в столбец X (ваш вспомогательный столбец), вы могли бы затем использовать две функции ПРОМЕЖУТОЧНЫЙ ИТОГ следующим образом:

Первое использование SUBTOTAL дает вам количество строк, содержащих «M»

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

Если вы не можете использовать вспомогательный столбец (или предпочитаете не использовать вспомогательный столбец), немного сложнее обойти недостатки ранее обсуждавшихся подходов; для этого нужна более сложная формула. Следующая формула будет работать, но я разбил единственную формулу на четыре строки, чтобы ее было легче объяснить.

Я собираюсь подробно описать, как работает эта формула, так что потерпите немного — это займет некоторое время. Давайте сначала посмотрим на эту часть формулы, которая охватывает конец первой строки и всю вторую строку:

Использование функции СМЕЩЕНИЕ здесь зависит от 3 параметров. Первый соответствует начальной точке для вычисления возвращаемой ссылки (в этом случае начальной точкой является C2: C9999). Второй параметр — это количество строк, которые будут смещены от начала диапазона, определенного в первом параметре. В этом случае смещение строк определяется путем вычитания самого низкого номера строки в диапазоне (который всегда будет возвращать значение 2) из ​​фактической анализируемой строки. Так, например, если анализируемая строка является строкой 10, то вычитание 2 (начальная строка) из нее дает нам смещение в 8 строк от начала диапазона, указанного в первом параметре.

Третий параметр не заполнен, поэтому по умолчанию он равен 0. Это количество столбцов, которые должны быть смещены от первого столбца в диапазоне, указанном в первом параметре. Наконец, четвертый параметр — это номер 1, который указывает, что вы хотите, чтобы OFFSET возвращал диапазон, который составляет всего 1 ячейку.

Суть в том, что вся эта часть формулы включена, так что она возвращает ссылку на одну ячейку в анализируемом столбце. Для пояснения в этой формуле назовем то, что возвращается «SingleCell». Подставив это в исходную формулу, мы получим:

Затем первая функция ПРОМЕЖУТОЧНЫЙ ИТОГ возвращает результат СЧЁТ (обозначенный значением 3, используемым для первого параметра) для отдельной ячейки. В результате SUBTOTAL возвращает либо 0, либо 1, в зависимости от того, видна ячейка или нет. (Если ячейка отфильтрована из результатов, возвращается 0. Если ячейка не отфильтрована — она ​​видна — возвращается 1.)

Следующая часть формулы основана на функциях ЕЧИСЛО и ПОИСК. Эта часть кода возвращает либо 0, либо 1 в зависимости от того, содержит ли ячейка букву «M» или нет. То, что вы в итоге получаете, сводится к следующему:

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

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

Чтобы получить процент женщин в видимых строках, все, что вам нужно сделать, это изменить то, что ищется: измените «M» на «F» в формуле, и все будет в порядке.

Однако следует упомянуть одну оговорку. Функция ПОИСК не различает прописные и строчные буквы. Таким образом, если вы используете «Мужской» вместо «M» и «Женский» вместо «F» в столбце «Пол» (столбец C), то поиск «Мужской» в формуле будет соответствовать каждой отдельной строке, поскольку ячейки, содержащие « Женский »будет содержать символы« мужчина ». Таким образом, лучше всего придерживаться «M» и «F» или, если вы должны использовать «Male» и «Female», тогда просто используйте «Female» в формуле и рассчитайте процент мужских записей, равный 1 минус женский процент.

_Примечание: _

Если вы хотите знать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах WordTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / wordribbon-WordTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (13550) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.