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

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

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

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

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

Диапазон_условия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”.
  • Функция СЧЁТЕСЛИМН считает количество ячеек по условию в Excel

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

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

    С помощью функции СЧЁТЕСЛИМН можно рассчитать количество ячеек, соответствующих критериям, применяемым к столбцу с числовыми значениями. Например, в ячейках A1:A9 содержится числовой ряд от 1 до 9. Функция =СЧЁТЕСЛИМН(A1:A9;»>2″;A1:A9;»

    Для поиска сразу нескольких значений в векторе данных (столбце B:B) в качестве аргумента условие1 была передана константа массива <"LG";"Samsung";"Bosch">, поэтому формулу необходимо выполнить в качестве формулы массива. Функция СУММ подсчитывает число элементов, содержащихся в массиве значений, возвращаемых функцией СЧЁТЕСЛИМН. Функция СЧЁТ возвращает число непустых ячеек в диапазоне A2:A21, то есть число строк в таблице. Соотношение полученных величин является искомым значением.

    В результате вычислений получим:

    Как посчитать количество ячеек по нескольким условиям в Excel?

    Пример 3. В таблице приведены данные о количестве отработанных часов сотрудником на протяжении некоторого периода. Определить, сколько раз сотрудник работал сверх нормы (более 8 часов) в период с 03.08.2018 по 14.08.2018.

    Вид таблицы данных:

    Для вычислений используем следующую формулу:

    02.08.2018″;A2:A19;» 8″)’ class=’formula’>

    В качестве первых двух условий проверки указаны даты, которые автоматически преобразовываются в код времени Excel (числовое значение), а затем выполняется операция проверки. Последний (третий) критерий – количество рабочих часов больше 8.

    В результате расчетов получим следующее значение:

    Особенности использования функции СЧЁТЕСЛИМН в Excel

    Функция имеет следующую синтаксическую запись:

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

    • диапазон_условия1 – обязательный аргумент, принимающий ссылку на диапазон ячеек, в отношении содержащихся данных в которых будет применен критерий, указанный в качестве второго аргумента;
    • условие1 – обязательный аргумент, принимающий условие для отбора данных из диапазона ячеек, указанных в качестве диапазон_условия1. Этот аргумент принимает числа, данные ссылочного типа, текстовые строки, содержащие логические выражения. Например, из таблицы, содержащей поля «Наименование», «Стоимость», «Диагональ экрана» необходимо выбрать устройства, цена которых не превышает 1000 долларов, производителем является фирма Samsung, а диагональ составляет 5 дюймов. В качестве условий можно указать “Samsung*” (подстановочный символ «*» замещает любое количество символов), “>1000” (цена свыше 1000, выражение должно быть указано в кавычках), 5 (точное числовое значение, кавычки необязательны);
    • [диапазон_условия2;условие2];… — пара последующих аргументов рассматриваемой функции, смысл которых соответствует аргументам диапазон_условия1 и условие1 соответственно. Всего может быть указано до 127 диапазонов и условий для отбора значений.
    1. Во втором и последующем диапазонах условий ([диапазон_условия2], [диапазон_условия3] и т. д.) число ячеек должно соответствовать их количеству в диапазоне, заданном аргументом диапазон_условия1. В противном случае функция СЧЁТЕСЛИМН вернет код ошибки #ЗНАЧ!.
    2. Рассматриваемая функция выполняет проверку всех условий, перечисленных в качестве аргументов условие1, [условие2] и т. д. для каждой строки. Если все условия выполняются, общая сумма, возвращаемая СЧЁТЕСЛИМН, увеличивается на единицу.
    3. Если в качестве аргумента условиеN была передана ссылка на пустую ячейку, выполняется преобразование пустого значения к числовому 0 (нуль).
    4. При использовании текстовых условий можно устанавливать неточные фильтры с помощью подстановочных символов «*» и «?».

    Функция СЧЁТЕСЛИМН() в 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числовых).

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

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

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

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

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

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

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

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

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

    Решение:

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

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

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

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

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

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

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

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

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

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

    Похожие статьи

    Читать еще:  Случмежду excel
    Ссылка на основную публикацию
    Похожие публикации
    Adblock
    detector