У Рода есть список рождественских открыток в Excel. (Фактически, этот вопрос может применяться к любому списку поздравительных открыток.) ​​Каждое имя имеет уникальный числовой идентификатор, связанный с ним, и имеет цветовую кодировку в зависимости от того, принадлежит ли имя семье, другу, школьному другу или другу по работе. У него также есть индикатор Верно / Неверно в столбце, который указывает, получит ли человек карту в этом году. Род хотел бы подсчитать, сколько карточек соответствует двум критериям:

индикатор «получает карту» имеет значение True, а идентификатор — 50 или ниже.

В качестве альтернативы ему нужен способ подсчета количества карточек, на которых индикатор «получает карточку» имеет значение Истина, а цветовой код — красный.

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

=SUBTOTAL(3,B2:B100)

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

Также есть формулы, которые вернут желаемую информацию. Функция СЧЁТЕСЛИМН будет рассматривать несколько критериев и возвращать количество, основанное на результате сравнений. Например, следующая формула предоставит количество членов списка, в которых «получает карту»

индикатор имеет значение True, а идентификатор равен 50 или ниже:

=COUNTIFS(A2:A100,"<50",B2:B100,TRUE)

Формула смотрит только на элементы списка в A2: A100 и предполагает, что B2: B100 содержит значения True / False, служащие «картой получения»

показатель.

Вы должны помнить, что функция СЧЁТЕСЛИМН была введена в Excel 2007. Если вы создаете книгу, которая также будет использоваться теми, кто использует более старую версию Excel, то вы можете использовать альтернативную функцию для возврата количества:

=SUMPRODUCT((A2:A100<50)B2:B1001)

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

который возвращает цвет ячейки, а затем использует его в формуле. (В других выпусках ExcelTips такие функции обсуждались.) Более простой способ избежать использования макроса — добавить в данные столбец, содержащий числовое значение, указывающее, к какой группе (семья, друг, школьный друг или друг по работе) принадлежит человек. . Затем вы можете включить значение столбца в свои формулы.

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

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