Как посчитать количество строк в excel по условию

Обновлено: 02.07.2024

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

Метод 1: информация в строке состояния

Проще всего посмотреть количество строк в строке состояния Эксель, расположенной внизу его основной рабочей области. Порядок действий описан ниже:

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

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

Если в строке состояния отсутствует слово “Количество”, проверяем настройки отображения информации для нее.

Для вызова настроек строки состояния нажимаем правую кнопку мыши, наведя курсор на любое место строки состояния внизу окна.
В появившемся окне с настройками строки состояния проверяем наличие галочки напротив пункта Количество. Если флажка нет, устанавливаем его, кликнув левой кнопкой мыши.

Метод 2: применение функции ЧСТРОК

Если первый метод позволяет оперативно посмотреть количество строк путем подсчета заполненных ячеек, то описываемый в этом разделе способ поможет зафиксировать результаты подсчета в виде конкретного значения в свободной ячейке листа. Причем в результат будут включаться все строки – и пустые и с введенными данными. Речь идет о встроенной функции ЧСТРОК, которая позволит реализовать эту задачу.

Функция ЧСТРОК имеет следующий синтаксис: =ЧСТРОК(массив).

Давайте на практическом примере разберем, как работает данная функция, воспользовавшись Мастером функций:

Особенностью этого метода является то, что с его помощью считаются все строки – и с данными в ячейках, и без них. Также если в выделенном диапазоне будет присутствовать несколько столбцов, это не повлияет на конечный результат работы функции “ЧСТРОК”.

Метод 3: условное форматирование и использование фильтра

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

Выделяем диапазон ячеек, данные которых станут критерием выбора строк.
Во вкладке “Главная” находим раздел “Стили”, среди инструментов находим и кликаем по кнопке “Условное форматирование”.
В появившемся перечне раскрываем пункт “Правила выделения ячеек”, который содержит список правил, определяющих условия форматирования. В нашем примере выделенный диапазон заполнен текстовыми данными, соответственно – выбираем строку “Текст содержит…”.
Далее задаем конкретные условия форматирования – для каких значений ячеек они будут окрашиваться в определенный цвет. В левой части окна указываем букву A, а в правой части оставляем установку по умолчанию (при желании – выбираем другую цветовую схему). Наша цель – чтобы все ячейки с буквой А подкрашивались в красных тонах. Нажимаем ОК.
Если ячейки с буквой А выделены цветом, значит настройки условного форматирования сделаны корректно.
Чтобы посчитать, сколько строк содержат “подкрашенные” ячейки, используем инструмент “Фильтр”.
Если выделение снято, опять выделяем требуемый диапазон ячеек. Во вкладке “Главная”, находим инструмент “Фильтр” (значок в виде воронки, группа инструментов “Редактирование”) и кликаем по нему.
На верхней ячейке выделенного столбца появится значок пока что неактивного фильтра в виде кнопки со стрелкой вниз:
- кликаем на него;
- ищем строку “Фильтр по цвету”, щелкаем по этому пункту;
- выбираем цвет, использованный для условного форматирования ранее.
В результате работы фильтра в таблице останутся только строки с окрашенными ячейками в выбранном столбце. Теперь можно просто выделить их, чтобы определить итоговое количество в строке состояния. Это и есть количество строк, которое мы хотели выяснить согласно поставленной задаче.

Заключение

Итак, мы рассмотрели основные методы, позволяющие посчитать количество строк в различных ситуациях. Первый метод позволит быстро оценить количество строк, когда нет необходимости его фиксировать. Второй – позволяет зафиксировать результат в отдельной ячейке. Третий метод сложнее первых двух, но в случае необходимости позволит посчитать количество строк, отобранных по определенному признаку. Кроме того, условное форматирование позволяет визуализировать конкретные условия выбора, “подсветив” их определенным цветом.

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

Это видео — часть учебного курса Усложненные функции ЕСЛИ.

Синтаксис

Аргументы функции СЧЁТЕСЛИМН описаны ниже.

Диапазон_условия1. Обязательный аргумент. Первый диапазон, в котором необходимо проверить соответствие заданному условию.

Условие1. Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, ">32", B4, "яблоки" или "32".

Диапазон_условия2, условие2. Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

Важно: Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и аргумент диапазон_условия1. Эти диапазоны могут не находиться рядом друг с другом.

Замечания

Каждое условие диапазона одновременно применяется к одной ячейке. Если все первые ячейки соответствуют требуемому условию, счет увеличивается на 1. Если все вторые ячейки соответствуют требуемому условию, счет еще раз увеличивается на 1, и это продолжается до тех пор, пока не будут проверены все ячейки.

Если аргумент условия является ссылкой на пустую ячейку, то он интерпретируется функцией СЧЁТЕСЛИМН как значение 0.

В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, поставьте перед ними знак тильды (

Пример 1

Скопируйте образец данных из следующих таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Встроенные функции программы Excel предназначены для выполнения разнообразных вычислений. Эти функции подразделяются на 12 категорий, одна из которых – статистические. К ним относится функция СЧЁТЕСЛИМН (COUNTIFS в англоязычной версии). Она подсчитывает и выдаёт пользователю количество ячеек таблицы Excel, удовлетворяющих заданному условию или условиям.

Для работы с описываемой функцией необходимо выполнить следующую последовательность шагов.

Выделить любую свободную ячейку для результата (например, H10), затем на строке формул Excel щёлкнуть f_x для вставки описываемой функции.

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

В выпадающем списке «Категория» выделить один из перечисленных списков и кликнуть OK.

В новом списке выделить нашу функцию и кликнуть OK.

В появившемся окне обязательных аргументов необходимо правильно заполнить два поля. В первом прописывается диапазон условия. Так называется проверяемый на соответствие критерию участок таблицы.

Для перехода к диапазону щёлкнуть наклонную красную стрелку справа от поля верхнего аргумента.

В новом компактном окне выделить исследуемый диапазон (заполненный столбец B), и щёлкнуть направленную вниз красную стрелку для возврата к предыдущему окну.

Теперь нам необходимо выставить условие подсчёта. Предположим, нам необходимо выяснить, сколько человек в данном учреждении получает зарплату в 50 тысяч рублей.

В поле условия 1 вписать соответствующее значение в том формате, в котором оно присутствует в таблице (обычное число), и кликнуть OK. (Обратите внимание, что в окне аргументов появилась третья строчка для второго условия. Пока она нам не нужна, поэтому не будем обращать на неё внимания.)

После введения правильного условия найденное значение (2) сейчас же появляется в том же окне аргументов. После клика на ОК оно возвращается и в основном рабочем окне (в ячейке H10).

Теперь несколько усложним наше условие. Предположим, что нужно узнать количество сотрудников учреждения, получающих зарплату выше 50 тысяч рублей. Очевидно, что в этом случае условие должно изменено с помощью логического оператора, как показано на следующем скриншоте. Естественно, что поменяется и возвращаемое функцией значение.

Следующий пример – критерий «не равно». Он реализуется, как показано на скриншоте. Обратите внимание на то, что в данном случае для получения правильного результата в диапазоне условия не выбрано название диапазона – слово «Зарплата» в ячейке. К такому выбору приводит простое логическое размышление: ведь при выделении текста он тоже попадёт в подсчёт, исказив тем самым нужные нам только числовые результаты.

Примеры подсчёта с множественными условиями

Все рассмотренные выше примеры касались использования функции СЧЁТЕСЛИМН с единственной парой аргументов диапазон/критерий. В таком случае эта функция ничем не отличается от другой статистической функции СЧЁТЕСЛИ, ограниченной единственной парой аргументов. Перейдём к примерам, относящимся именно к функции СЧЁТЕСЛИМН с множественными (МН) условиями.

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

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

Поэтому попытка использовать возможность множественных аргументов для подсчёта сотрудников, получающих зарплату выше 60 тысяч ИЛИ ниже 50 тысяч будет безуспешной, как видно на нижнем скриншоте.

Для решения этой конкретной задачи пользователь сам может написать простейшую формулу, просуммировав две функции СЧЁТЕСЛИМН с единственной парой аргументов, как показано на нижнем скриншоте.

Другой способ подсчёта по алгоритму ИЛИ сложнее. Он требует привлечения понятия «массив данных» и дополнительной функции СУММ.

Реализация этого способа показана на нижнем скриншоте в строке формул. Обратите внимание на то, что наши условия взяты в фигурные скобки. Это означает, что диапазон данных считается массивом, который позволяет искать сначала по первому условию (результат – 1), потом – по второму (результат – 2). Следующая ключевая особенность – функция СЧЁТЕСЛИМН оформлена как аргумент вышестоящей функции СУММ. Последняя используется для суммирования двух подсчётов, о которых речь шла выше.

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

Функция Счётесли

Счётесли (диапазон; критерий)

Диапазон – группа ячеек, для которых нужно выполнить подсчет.

Диапазон может содержать числа, массивы или ссылки на числа.
Обязательный аргумент.

Критерий – число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Обязательный аргумент.

Критерий проверки необходимо заключать в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

Примеры использования функции Счётесли.

Подсчет количества ячеек, содержащих отрицательные значения

Счётесли(А1:С2;"<0") Диапазон - А1:С2 , критерий - "<0"

Подсчет количества ячеек, значение которых больше содержимого ячейки А4:

Подсчет количества ячеек со словом "текст" (регистр не имеет значения).

Счётесли(А1:С2;"текст") Диапазон - А1:С2 , критерий - "текст"

Для текстовых значений в критерии можно использовать подстановочные символы * и ? .

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

Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак

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

Счётесли(А1:С2;"Т * ") Диапазон - А1:С2 , критерий - "Т * "

Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, можно использовать формулу:

Счётесли(А1:С2;". ") Диапазон - А1:С2 , критерий - ". "

В функции Счётесли используется только один критерий.

Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.

Функция Счётеслимн

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

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

Пример использования:

Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

Диапазон один - C1:C8 , условия - ">15.01.2015" и "<1.03.2015"

Функция СЧЁТЕСЛИ в MS Excel, позволяет посчитать количество ячеек, которые отвечают заданному критерию поиска, в указанном диапазоне. Рассмотрим, как это работает на примерах.

Как вызвать функцию СЧЁТЕСЛИ СЧЁТЕСЛИ в MS Excel.

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

Аргументы функции СЧЁТЕСЛИ в MS Excel.

После того, как была выбрана функция СЧЁТЕСЛИ, появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть два поля для заполнения:

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

Какие варианты критериев поиска существуют.

В данном пункте, на примерах рассмотрим, какие варианты критериев для поиска можно использовать в функции СЧЁТЕСЛИ. Предположим, что у нас есть простая таблица, с текстовыми данными и числами.

Больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).

Больше.

Найдем, сколько ячеек в столбце Числа, содержат в себе значение больше числа 50. Вызываем функцию СЧЁТЕСЛИ в ячейку Е2. В диалоговом окне Аргументы функции, в поле Диапазон, указываем диапазон С3:С17. Это ячейки столбца Числа, в которых мы будем осуществлять поиск. В поле Критерий, пишем знак больше «>», и число 50.

Формула функции будет выглядеть вот так:

Кавычки появятся автоматически, после того, как нажать ОК.

По такому же принципу работаю остальные варианты. Если продолжать рассматривать на нашем примере, Диапазон для всех остальных случаев остаётся без изменения. Меняется Критерий.

Меньше.

Используем знак меньше «<».

В поле Критерий, диалогового окна Аргументы функции, пишем: <50.

Формула функции будет выглядеть вот так:

В результате получим количество ячеек, числа в которых меньше 50.

Больше или равно.

В поле Критерий, диалогового окна Аргументы функции, пишем: >=50.

Формула функции будет выглядеть вот так:

В результате получим количество ячеек, числа в которых больше или равны 50.

Меньше или равно.

В поле Критерий, диалогового окна Аргументы функции, пишем: <=50.

Формула функции будет выглядеть вот так:

В результате получим количество ячеек, числа в которых меньше или равны 50.

Равно.

В поле Критерий, диалогового окна Аргументы функции, пишем: =50.

Формула функции будет выглядеть вот так:

В результате получим количество ячеек, числа в которых равны 50.

Не равно.

В поле Критерий, диалогового окна Аргументы функции, пишем: <>50.

Формула функции будет выглядеть вот так:

В результате получим количество ячеек, числа в которых не равны 50.

Ссылка на ячейку в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.

Возможно использовать ссылку на ячейку в качестве критерия поиска. В нашем примере найдем сколько ячеек в столбце Числа содержат в себе число 50. Значения в поле Диапазон, диалогового окна Аргументы функции, остаётся без изменений. В поле Критерий указываем любую ячейку из указанного диапазона, которая отвечает нашему критерию. В нашем пример выберем ячейку С11.

Формула функции будет выглядеть вот так:

Кавычки в таком варианте не нужны.

Нажимаем ОК. Получаем результат. Количество ячеек, которые содержат то же значение, что и ячейка С11. Три ячейки. Они залиты желтым цветом для наглядности.

При использование в качестве критерия поиска ссылку на ячейку, использовать знаки: <, >, =, <=, >=, <>, необходимо с знаком амперсанда (&), между этими знаками с самой ссылкой на ячейку.

Важный момент, в данном случае, кавычки возле знака больше (>) ставить нужно вручную.

Для примера, формулы с знаком больше будет выглядеть вот так:

Текстовые значения в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.

В качестве критерия поиска в поле Критерий, в диалоговом окне Аргументы функции, можно использовать текстовое значения. Например, можно найти в столбце Значения, количество ячеек, которые не содержат в себе Значение 1. Меняем Диапазон поиска.

Формула функции будет выглядеть вот так:

Кавычки возле знака не равно (<>) ставить вручную.

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

Формула функции будет выглядеть вот так:

Возможно осуществлять поиск указав в критерии только часть слова или одну букву. Например, у нас есть столбец, в котором указано название мебели.

Найдем количество ячеек, со словом Стол, указав в критерии поиска часть букв из этого слова, которые стоят в начале: Ст

Формула функции будет выглядеть вот так:

Теперь укажем в критерии поиска конец слова. Например, букву ф, из слова шкаф.

Формула функции будет выглядеть вот так:

Кавычки возле Ст* и *ф, ставятся автоматически.

Варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска описаны в статье: Функция СЧЁТЕСЛИ с использованием двух (нескольких) критериев поиска. Описание и примеры.

Читайте также: