Формула счетеслимн в excel

Функция СЧЁТЕСЛИМН() в EXCEL

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

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

Синтаксис функции

СУММЕСЛИМН( диапазон_условия1; условие1; [диапазон_условия2; условие2]… )

Диапазон_условия1 . Первый диапазон, в котором необходимо проверить соответствие заданному условию1;
Условие1 . Условие в форме числа, выражения, ссылки на ячейку или текста, которые определяют, какие ячейки требуется учитывать. Например, условие может быть выражено следующим образом: 32, «>32», B4 или «яблоки»;
Диапазон_условия2, условие2. Необязательные аргументы. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

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

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

Задача1 (2 числовых критерия)

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

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

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($B2>=$D$2;$B2

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»

В формуле предполагается, что диапазон, к которому применяется первый критерий (>=10 или «>=»&D2 ) это диапазон B2:B13 . Первый и второй диапазон в данном случае совпадают, т.к. 2-й критерий (

Альтернативными решениями задачи являются следующие формулы:

= СУММПРОИЗВ((B2:B13>=D2)*(B2:B13
формула массива = СУММ((B2:B13 =D2))
формула массива = СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13
= БСЧЁТА(A1:B13;A1;D14:E15) или = БСЧЁТ(A1:B13;B1;D14:E15) , которые требуют наличия отдельной таблички с критериями.

Задача2 (2 критерия в формате Дат)

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

Найдем число партий товара с Датой поступления на склад не ранее 25.10.2012 и не позднее 24.12.2012 (строка таблицы соответствует критерию, когда ее поле Дата поступления удовлетворяет обоим критериям одновременно).

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

Так как даты хранятся в EXCEL в числовом формате , то формулы для подсчета не изменятся (см. задачу 1).

Задача3 (1 текстовый критерий, другой числовой)

Рассмотрим задачу, когда 1 текстовый критерий применяются к значениям текстового столбца, а другой (числовой) — к значениям столбца с числами (см. файл примера , лист 1текст 1числовой ).

Найдем число партий товара Яблоки с Количеством ящиков на складе не менее 10 (строка таблицы соответствует критерию, когда ее поле Фрукт совпадает с критерием Яблоки , и когда другое поле Количество ящиков на складе >=10).

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2)

Задача4 (1 текстовый критерий с подстановочным знаком, другой числовой)

Рассмотрим задачу, когда 1 текстовый критерий с подстановочным знаком применяются к значениям текстового столбца, а другой (числовой) — к значениям столбца с числами (см. файл примера , лист 1текст (с подстанов) 1числовой ).

Читать еще: Excel сумма текстовых ячеек

Найдем число партий товара начинающихся со слова Яблоки и с Количеством ящиков на складе не менее 10 .

В отличие от задачи 3, в исходной таблице присутствуют фрукты с более сложными названиями: яблоки свежие , персики сорт2 . Чтобы одновременно подсчитать партии товара Яблоки и Яблоки свежие нужно использовать подстановочные знаки.

В качестве критерия в ячейке D2 укажем Яблоки* , где знак * заменяет последовательность любых символов идущих правее.

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

Задача5 (1 текстовый критерий, 2 числовых)

Рассмотрим задачу, когда 1 текстовый критерий применяются к значениям текстового столбца, а 2 других (числовых) — к значениям столбца с числами (см. файл примера , лист 1текст 2числовых ).

Найдем число партий товара Яблоки с Количеством ящиков на складе не менее 10 и не более 90 (строка таблицы соответствует критерию, когда ее поле Фрукт совпадает с критерием Яблоки , и когда другое поле Количество ящиков на складе одновременно >=10 и =И($A2=$D$2;$B2>=$E$2;$B2

Формулу запишем в следующем виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2;B2:B13;»

Альтернативными решениями задачи являются следующие формулы:

= СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13
формула массива = СУММ((A2:A13=D2)*(B2:B13 =E2))
формула массива = СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13
=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15) , которые требуют наличия отдельной таблички с критериями.

О подсчете с множественными критериями можно почитать в этом разделе .

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г.).

Читать еще: Как посчитать пустые строки в excel

Счётеслимн(C1:C8;»>15.01.2015″;C1:C8;» 15.01.2015″ и » Семинары. Вебинары. Конференции

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

Функция CЧЕТЕСЛИМН в Excel примеры

Счетеслимн excel примеры

Рассмотрим функции: СЧЁТЕСЛИ(диапазон;критерий) и СЧЁТЕСЛИМН(диапазон_условия; условие; . ). Функция счетесли подсчитывает количество непустых ячеек в диапазоне, которые удовлетворяют заданному условию. А функция счетеслимн (счётесли множественные) подсчитывает количество ячеек в диапазоне, которые удовлетворяют нескольким условиям.

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

Пример задачи

Возьмем задание из ОГЭ по информатике.

Калорийность 1000 продуктов занесли в электронную таблицу. Название продукта указано в столбце A. Содержание жиров в нём — в столбце В. Третий столбец C — это содержание белков. Количество углеводов указано в столбце D, а в последнем Е — калорийность данного продукта. Сколько записей из таблицы содержат менее 50 грамм углеводов и менее 50 грамм белков?

В ячейку H2 электронной таблицы запишите количество таких продуктов в виде числа.

Решение:

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

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

Кликаем в ячейку H2 и вызываем Мастер функций из ленты инструментов (Вкладка Формулы — Вставить функцию или нажимаем значок Fx рядом со строкой для ручного ввода формул). Откроется окно поиска функции.

Вводим ее название и кликаем кнопку Найти. Далее выбираем найденную функцию и жмем ОК. В следующем окне задаем параметры (смотрите рисунок ниже)

После ввода первого диапазона условия и самого условия, появятся поля для второго диапазона и т. д.

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

Выделение от текущей ячейки до конца столбца

Кликаем в первую ячейку диапазона. Далее нажимаем одновременно клавиши Ctrl и Shift. Не отпуская их, нажимаем кнопку на клавиатуре «стрелка вниз».

Условие 1: здесь прописываете условие «

Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали.

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

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

По сути, функция СЧЕТЕСЛИ на английском COUNTIF, идентична во всех версиях Excel, поэтому вы можете использовать примеры из этого руководства в Excel 2016, 2013, 2010 и 2007.

Синтаксис и использование функции СЧЕТЕСЛИ в Excel

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

Читать еще: Сводный график excel

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

Синтаксис функции СЧЕТЕСЛИ очень прост:

Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:

диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, «10», A2, «>=10»).

Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;»Иванов Иван») подсчитывает, сколько заявок поступало от Иванова Ивана:

Функция СЧЕТЕСЛИ в Excel – Пример использования функции СЧЕТЕСЛИ в Excel

Примечание : Критерий нечувствителен к регистру, что означает, что если вы наберете «иванов иван» в качестве критерия в приведенной выше формуле СЧЕТЕСЛИ, это приведет к такому же результату.

Функция СЧЕТЕСЛИ в Excel – примеры

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

Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)

Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.

Вместо ввода текста вы можете использовать ссылку на любую ячейку , содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку

Аналогичные формулы СЧЕТЕСЛИ работают для чисел , также как для текстовых значений.

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел

На изображении выше формула =СЧЁТЕСЛИ(B2:B7;10) учитывает ячейки с количеством 10 в столбце D.

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

Обратите внимание, что в функции СЧЕТЕСЛИ оператор с числом всегда заключен в кавычки, например, =СЧЕТЕСЛИ(B2:B7; «>=10»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел с логическим оператором

Функция СЧЕТЕСЛИ с подстановочными знаками (частичное совпадение)

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

Предположим, у вас есть список цветов, и вы хотите узнать количество цветов, в названии которых содержится слово «синий». Поскольку эти цвета можно написать несколькими разными способами, мы вводим «*синий*» в качестве критериев поиска =СЧЕТЕСЛИ(B2:B8;»*синий*»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с частичным совпадением

Звездочка (*) используется в функции СЧЕТЕСЛИ для поиска ячеек с любой последовательностью ведущих и конечных символов, как показано в приведенном выше примере. Если вам нужно сопоставить какой-либо один символ, введите вместо него знак вопроса (?) , например, =СЧЁТЕСЛИ(A2:A7;»ст?л»).