Excel счетеслимн

Функция СЧЁТЕСЛИМН() в MS 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 =»&D2;B2:B13;» =10 или «>=»&D2 ) это диапазон B2:B13. Первый и второй диапазон в данном случае совпадают, т.к. 2-й критерий ( =D2)*(B2:B13 =D2))
формула массива = СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13 =10).

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

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

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

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

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

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

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

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

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

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

Читать еще: Excel выделение цветом ячейки по условию

Найдем число партий товара Яблоки с Количеством ящиков на складе не менее 10 и не более 90 (строка таблицы соответствует критерию, когда ее поле Фрукт совпадает с критерием Яблоки, и когда другое поле Количество ящиков на складе одновременно >=10 и =$E$2;$B2 =»&E2;B2:B13;» =E2)*(B2:B13 =E2))
формула массива = СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13 Похожие задачи

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

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

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

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

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

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

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

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

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

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

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

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

Функция COUNTIFS (СЧЁТЕСЛИМН) в Excel. Как использовать?

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

Что возвращает функция

Возвращает числовое значение обозначающее количество ячеек в определенном диапазоне соответствующих нескольким критериям.

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) – английская версия

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…) – русская версия

Аргументы функции

criteria_range1 (диапазон_условия1) – первый диапазон, по которому вы хотите посчитать количество ячеек, отвечающих первому критерию;
criteria1 (условие1) – критерий, по которому будут отбираться ячейки из первого диапазона для подсчета;
[criteria_range2] ([диапазон_условия2]) – второй диапазон, по которому вы хотите посчитать количество, ячеек, отвечающих условию второго критерия;
[criteria2] ([условие2]) – второй критерий, для второго диапазона, по которому будут определены ячейки для подсчета.

Читать еще: Общий доступ к файлу эксель

Дополнительная информация

Критерием могут быть числа, выражения, ссылки на ячейки, текст или формула;
Критерий, указанный как текст или математический/логический символ (=,+,-,*,/) должны быть указаны в двойных кавычках
В критерии могут использоваться подстановочные знаки. В Excel существует три подстановочных знака – ?, *,

знак “?” – сопоставляет любой одиночный символ;
знак “*” – сопоставляет любые дополнительные символы;
знак “

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

Для функции не важно, с заглавной или строчной буквы написан критерий (“Привет” или “привет”).

Функция подсчитывает ячейки, только при выполнении всех критериев;

В одной функции может быть использовано до 127 пар критериев и диапазонов;

Если критерий функции ссылается на пустую ячейку, то функция воспринимает эту ячейку как ячейку со значением “0”.

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

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

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

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

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

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

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

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

Решение:

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

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

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

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

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

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

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

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

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

Дополнительная информация:

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″ и «<1.03.2015"

Понравилось? Поделись с друзьями

Семинары. Вебинары. Конференции

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