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

Функция СЧЁТЕСЛИ

В этом курсе:

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

С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

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

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

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

Примеры

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

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! Ошибка при ссылке на другой лист.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Подсчет значений с множественными критериями (Часть 1. Условие И) в EXCEL

Произведем подсчет строк, удовлетворяющих сразу двум критериям, которые образуют Условие И.

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Задача1

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

Найдем число партий товара с определенным Фруктом И с Количеством на складе не менее минимального (Условие И — условие при котором строка считается удовлетворяющей критерию, когда оба ее поля одновременно соответствуют критериям). Например, число партий персики ( ячейка D 2 ) с количеством ящиков на складе >=5 ( ячейка Е2 ) . Результат очевиден: 2. Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Подсчет можно реализовать множеством формул, приведем несколько:

1. = СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является самым простым и понятным.

2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее, но позволяет понять работу функции СУММПРОИЗВ() , которая может быть полезна для подсчета с множественными критериями в других случаях.

Разберем подробнее применение функции СУММПРОИЗВ() :

Результатом вычисления A2_A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует персики . Результат можно увидеть, выделив A2_A13=D2 , а затем нажав клавишу F9 ;
Двойное отрицание (—) преобразует вышеуказанный массив в числовой:
Аналогично, второй массив возвращает <0:1:1:1:0:1:1:0:0:1:1:1>, где 0 соответствует значениям =5, поэтому второе значение в массиве =1 и т.д.
Далее, функция попарно перемножает элементы массивов и суммирует их. Получаем – 2.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) . Здесь, знак Умножения (*) эквивалентен Условию И.

4. Формула массива = СУММ((A2:A13=D2)*(B2:B13>=E2)) эквивалентна вышеупомянутой формуле =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, после ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива = СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула = БСЧЁТА(A1:B13;A1;D14:E15) требует предварительного создания таблицы с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И.

Здесь есть один трюк: в качестве второго аргумента функции БСЧЁТА() ( поле) нужно ввести ссылку на заголовок столбца с текстовыми значениями, т.к. БСЧЁТА() подсчитывает текстовые значения . В случае использования БСЧЁТ() нужно записать другую формулу =БСЧЁТ(A1:B13;B1;D14:E15) . Табличка с критериями не изменится.

Задача2

Рассмотрим задачу, когда критерии применяются к значениям из одного столбца.

Найдем число партий товара с Количеством на складе не менее минимального и не более максимального (Условие И — строка таблицы соответствует критерию, когда ее поле удовлетворяет обоим критериям одновременно).

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

Альтернативное решение

Подсчитать количество строк, удовлетворяющим 2-м критериям (Условие И) можно без применения формул с помощью стандартного Автофильтра .

Установите автофильтр к столбцу Количество ящиков на складе , выделив заголовок столбца и нажав CTRL+SHIFT+L. Выберите числовой фильтр Между .

Убедитесь, что результат такой же как в задаче2 — т.е. будет отобрано 7 строк (см. строку состояния в нижней части окна).

Примечание : подсчет значений с множественными критерями также рассмотрен в статьях Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ) , Часть3 , Часть4 .

Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

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

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

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

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

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

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

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

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

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

Счётесли(А1:С2;» «&A4) Диапазон — А1:С2 , критерий — «>»&A4

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

Счётесли(А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″;C1:C8;» 15.01.2015″ и » Семинары. Вебинары. Конференции

Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

Подсчет числа строк в Microsoft Excel

При работе в Excel иногда нужно подсчитать количество строк определенного диапазона. Сделать это можно несколькими способами. Разберем алгоритм выполнения этой процедуры, используя различные варианты.

Определение количества строк

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

Способ 1: указатель в строке состояния

Самый простой способ решить поставленную задачу в выделенном диапазоне – это посмотреть количество в строке состояния. Для этого просто выделяем нужный диапазон. При этом важно учесть, что система считает каждую ячейку с данными за отдельную единицу. Поэтому, чтобы не произошло двойного подсчета, так как нам нужно узнать количество именно строк, выделяем только один столбец в исследуемой области. В строке состояния после слова «Количество» слева от кнопок переключения режимов отображения появится указание фактического количества заполненных элементов в выделенном диапазоне.

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

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

Способ 2: использование функции

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

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

Для вывода результата на экран достаточно будет нажать кнопку Enter.

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

Пользователям, у которых небольшой опыт работы с формулами в Экселе, проще работать с данным оператором через Мастер функций.

«Вставить функцию»

Запускается небольшое окно Мастера функций. В поле «Категории» устанавливаем позицию «Ссылки и массивы» или «Полный алфавитный перечень». Ищем значение «ЧСТРОК», выделяем его и жмем на кнопку «OK».

Открывается окно аргументов функции. Ставим курсор в поле «Массив». Выделяем на листе тот диапазон, количество строк в котором нужно подсчитать. После того, как координаты этой области отобразились в поле окна аргументов, жмем на кнопку «OK».

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

Способ 3: применение фильтра и условного форматирования

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

Переходим во вкладку «Главная». На ленте в блоке инструментов «Стили» жмем на кнопку «Условное форматирование». Выбираем пункт «Правила выделения ячеек». Далее открывается пункт различных правил. Для нашего примера мы выбираем пункт «Больше…», хотя для других случаев выбор может быть остановлен и на иной позиции.

Открывается окно, в котором задается условие. В левом поле укажем число, ячейки, включающие в себя значение больше которого, окрасятся определенным цветом. В правом поле существует возможность этот цвет выбрать, но можно и оставить его по умолчанию. После того, как установка условия завершена, жмем на кнопку «OK».

Как видим, после этих действий ячейки, удовлетворяющие условию, были залиты выбранным цветом. Выделяем весь диапазон значений. Находясь во все в той же вкладке «Главная», кликаем по кнопке «Сортировка и фильтр» в группе инструментов «Редактирование». В появившемся списке выбираем пункт «Фильтр».

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

Как видим, не отмеченные цветом ячейки после данных действий были спрятаны. Просто выделяем оставшийся диапазон ячеек и смотрим на показатель «Количество» в строке состояния, как и при решении проблемы первым способом. Именно это число и будет указывать на численность строк, которые удовлетворяют конкретному условию.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

голоса

Рейтинг статьи