Как работает формула счетеслимн в excel

REDMOND

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

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

  1. Что возвращает функция
  2. Синтаксис
  3. Аргументы функции
  4. Дополнительная информация
  5. Примеры использования функции СЧЁТЕСЛИМН в 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

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

    Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

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

    history 21 апреля 2014 г.
      Группы статей

    • Подстановочные знаки (*, ?)
    • Условное форматирование
    • стандартные функции
    • Подсчет с множественными условиями

    Функция СЧЁТЕСЛИМН( ) , английская версия 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-й критерий (

    REDMOND

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

    • = СУММПРОИЗВ((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числовой ).

    Найдем число партий товара начинающихся со слова Яблоки и с Количеством ящиков на складе не менее 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) , которые требуют наличия отдельной таблички с критериями.

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

    REDMOND

    Ссылка на основную публикацию