В этом руководстве показано, как использоватьОтлэль Функция СЧЁТЕСЛИ и СЧЁТЕСЛИМНтионы в Excel для подсчета данных, соответствующих определенным критериям.
Обзор функции СЧЁТЕСЛИ
Вы можете использовать функцию СЧЁТЕСЛИ в Excel для подсчета ячеек, содержащих определенное значение, подсчета ячеек, которые больше или равны значению и т. Д.
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и аргументы функции СЧЁТЕСЛИ:
= СЧЁТЕСЛИ (диапазон; критерии)
диапазон - Диапазон подсчитываемых ячеек.
критерии - Критерии, определяющие, какие ячейки следует подсчитывать.
Что такое функция СЧЁТЕСЛИ?
Функция СЧЁТЕСЛИ - одна из старых функций, используемых в электронных таблицах. Проще говоря, он отлично сканирует диапазон и сообщает вам, сколько ячеек соответствует этому условию. Мы посмотрим, как эта функция работает с текстом, числами и датами; а также некоторые другие ситуации, которые могут возникнуть.
Базовый пример
Давайте начнем с рассмотрения этого списка случайных предметов. У нас есть числа, пустые ячейки и несколько текстовых строк.
Если вы хотите знать, сколько элементов точно соответствует критериям, вы можете указать, что вы хотите искать, в качестве второго аргумента. Пример этой формулы может выглядеть так:
= СЧЁТЕСЛИ (A2: A9; «Яблоко»)
Эта формула вернет число 3, поскольку в нашем диапазоне есть 3 ячейки, которые соответствуют этому критерию. В качестве альтернативы мы можем использовать ссылку на ячейку вместо жесткого кодирования значения. Если бы мы написали «Apple» в ячейке G2, мы могли бы изменить формулу на
= СЧЁТЕСЛИ (A2: A9; G2)
Имея дело с числами, важно различать числа и числа, которые хранятся в виде текста. Как правило, при написании формул не заключайте числа в кавычки. Итак, чтобы написать формулу, которая проверяет число 5, вы должны написать
= СЧЁТЕСЛИ (A2: A9; 5)
Наконец, мы также можем проверить наличие пустых ячеек с помощью строки нулевой длины. Мы бы записали эту формулу как
= СЧЁТЕСЛИ (A2: A9; "")
Примечание: Эта формула будет считать как действительно пустые, так и пустые ячейки в результате формулы, как функция ЕСЛИ.
Частичные совпадения
Функция СЧЁТЕСЛИ поддерживает использование в критериях подстановочных знаков «*» или «?». Давайте посмотрим на этот список вкусных хлебобулочных изделий:
Чтобы найти все элементы, которые начинаются с Apple, мы могли бы написать «Apple *». Итак, чтобы получить ответ 3, наша формула в D2:
= СЧЁТЕСЛИ (A2: A5; «Яблоко *»)
Примечание: Функция СЧЁТЕСЛИ не чувствительна к регистру, поэтому вы также можете написать «яблоко *», если хотите.
Возвращаясь к нашей выпечке, мы можем также захотеть узнать, сколько пирогов у нас есть в нашем списке. Мы можем найти это, поместив подстановочный знак в начало нашего поискового запроса и написав
= СЧЁТЕСЛИ (A2: A5; «* пирог»)
Эта формула дает результат 2.
Мы также можем использовать подстановочные знаки для проверки любых ячеек с текстом. Вернемся к нашему исходному списку данных.
Чтобы подсчитать количество ячеек, в которых есть хотя бы какой-то текст, не считая чисел или пустых ячеек, мы можем написать
= СЧЁТЕСЛИ (A2: A9; «*»)
Вы можете видеть, что наша формула правильно возвращает результат 4.
Операторы сравнения в COUNTIF
До сих пор при написании критериев мы подразумевали, что наш оператор сравнения - «=». Фактически, мы могли бы написать так:
= СЧЁТЕСЛИ (A2: A9; "= Apple")
Однако это дополнительный символ, который нужно записать, поэтому его обычно опускают. Однако это означает, что вы можете использовать другие операторы, такие как больше, меньше или не равно. Давайте посмотрим на этот список зарегистрированных возрастов:
Если мы хотим узнать, скольким детям по крайней мере 5 лет, мы можем написать сравнение «больше или равно» следующим образом:
= СЧЁТЕСЛИ (A2: A8; "> = 5")
Примечание: Оператор сравнения всегда задается в виде текстовой строки и поэтому должен быть заключен в кавычки.
Точно так же вы также можете проверить элементы, которые меньше заданного значения. Если нам нужно узнать, сколько из них меньше 8, мы можем написать
= СЧЁТЕСЛИ (A2: A8; «<8»)
Это дает нам желаемый результат - 5. А теперь давайте представим, что все 6-летние дети собираются на прогулку. Сколько детей останется? Мы можем выяснить это, используя сравнение типа «не равно»:
= СЧЁТЕСЛИ (A2: A8; «6»)
Теперь мы можем быстро увидеть, что у нас есть 6 детей, которым не исполнилось 6 лет.
До сих пор в этих сравнительных примерах мы жестко кодировали нужные значения. Вы также можете использовать ссылку на ячейку. Хитрость в том, что вам нужно объединить оператор сравнения со ссылкой на ячейку. Допустим, мы поместили число 7 в ячейку C2 и хотим, чтобы наша формула в D2 показывала, сколько детей младше 7 лет.
Наша формула в D2 должна выглядеть так:
= СЧЁТЕСЛИ (A2: A8; "<" & C2)
Примечание: При написании этих формул обратите особое внимание на то, нужно ли помещать элемент в кавычки или снаружи. Операторы всегда внутри кавычек, ссылки на ячейки всегда вне кавычек. Числа находятся снаружи, если вы выполняете точное соответствие, и внутри, если выполняете оператор сравнения.
Работа с датами
Мы видели, как можно указать текст или число в качестве критерия, но как насчет того, когда нам нужно работать с датами? Вот краткий примерный список, с которым мы можем работать:
Чтобы подсчитать, сколько дат осталось после 4 мая, нам нужно проявить осторожность. Компьютеры хранят даты в виде чисел, поэтому нам нужно убедиться, что компьютер использует правильные числа. Если бы мы написали эту формулу, получили бы мы правильный результат?
= СЧЁТЕСЛИ (A2: A9; "
Ответ - «возможно». Поскольку мы опустили год в наших критериях, компьютер предположит, что мы имеем в виду текущий год. Если все даты, с которыми мы работаем, относятся к текущему году, то мы получим правильный ответ. Однако, если есть какие-то даты в будущем, мы получим неправильный ответ. Кроме того, в начале следующего года эта формула вернет другой результат. Таким образом, этого синтаксиса, вероятно, следует избегать.
Поскольку правильно записать даты в формуле может быть сложно, рекомендуется записать дату, которую вы хотите использовать в ячейке, а затем вы можете использовать эту ссылку на ячейку в своей формуле СЧЁТЕСЛИ. Итак, давайте запишем дату 7 мая 2020 года в ячейку C2, а затем мы сможем поместить нашу формулу в C4.
Формула в C4:
= СЧЁТЕСЛИ (A2: A9; "<" & C2)
Теперь мы знаем, что результат 7 правильный, и ответ не изменится неожиданно, если мы когда-нибудь откроем эту таблицу.
Прежде чем мы покинем этот раздел, при работе с датами обычно используется функция СЕГОДНЯ. Мы можем использовать это так же, как ссылку на ячейку. Например, мы можем изменить предыдущую формулу на следующую:
= СЧЁТЕСЛИ (A2: A9; "<" & СЕГОДНЯ ())
Теперь наша формула будет обновляться по мере продвижения в реальном времени, и у нас будет количество элементов меньше, чем сегодня.
Несколько критериев и СЧЁТЕСЛИМН
Исходная функция СЧЁТЕСЛИ была улучшена в 2007 году, когда вышла СЧЁТЕСЛИМН. Синтаксис между ними очень похож, последний позволяет указывать дополнительные диапазоны и критерии. Вы можете легко использовать СЧЁТЕСЛИ в любой ситуации, когда СЧЁТЕСЛИ существует. Было бы неплохо знать, что обе функции существуют.
Давайте посмотрим на эту таблицу данных:
Чтобы узнать, сколько людей имеют уровни оплаты 1-2, вы можете написать суммирование функций СЧЁТЕСЛИ, например:
= СЧЁТЕСЛИ (B2: B7; "> = 1") - СЧЁТЕСЛИ (B2: B7; "> 2")
Эта формула будет работать, поскольку вы находите все, что больше 1, но затем вычитаете количество записей, которые превышают вашу точку отсечения. В качестве альтернативы вы можете использовать СЧЁТЕСЛИМН, как это:
= СЧЁТЕСЛИМН (B2: B7; "> = 1"; B2: B7; "<= 2")
Последний вариант более интуитивно понятен для чтения, поэтому вы можете использовать этот маршрут. Кроме того, СЧЁТЕСЛИМН более эффективен, когда вам нужно учитывать несколько столбцов. Допустим, мы хотим знать, сколько человек находится в управленческом составе и имеет уровень оплаты 1. Вы не можете сделать это с помощью только СЧЁТЕСЛИ; тебе нужно написать
= СЧЁТЕСЛИМН (A2: A7; «Управление»; B2: B7; 1)
Эта формула даст вам правильный результат 2. Прежде чем мы закончим этот раздел, давайте рассмотрим логику типа Or. Что, если бы мы хотели узнать, сколько человек работает в Управлении или? Вам нужно будет сложить несколько СЧЕТЧИКОВ вместе, но есть два способа сделать это. Более простой способ - написать это так:
= СЧЁТЕСЛИ (A2: A7; «HR») + СЧЁТЕСЛИ (A2: A7; «Управление»)
Вы также можете использовать массив и написать эту формулу массива:
= СУММ (СЧЁТЕСЛИ (A2: A7; {"HR"; "Управление"}))
Примечание: Формулы массива необходимо подтверждать с помощью Ctrl + Shift + Enter, а не только Enter.
Эта формула будет работать, если вы укажете массив в качестве входных данных. Таким образом, он вычислит результат для двух разных функций СЧЁТЕСЛИ и сохранит их в массиве. Затем функция SUM сложит все результаты в нашем массиве, чтобы получить единый вывод. Таким образом, наша формула будет оцениваться так:
= СУММ (СЧЁТЕСЛИ (A2: A7; {"HR", "Управление"})) = СУММ ({2, 3}) = 5
Подсчет уникальных значений
Теперь, когда мы увидели, как использовать массив с функцией СЧЁТЕСЛИ, мы можем сделать еще один шаг, чтобы помочь нам подсчитать, сколько уникальных значений находится в диапазоне. Во-первых, давайте еще раз взглянем на наш список отделов.
= СУММ (1 / СЧЁТЕСЛИ (A2: A7; A2: A7))
Мы видим, что данных насчитывается 6 ячеек, но есть только 3 разных элемента. Чтобы математика работала, нам нужно, чтобы каждый элемент оценивался в 1 / N, где N - количество повторений элемента. Например, если каждый HR стоил только 1/2, то при их сложении вы получите счет 1 для 1 уникального значения.
Вернемся к нашему СЧЁТЕСЛИ, который предназначен для определения того, сколько раз элемент появляется в диапазоне. В D2 мы напишем формулу массива
= СУММ (1 / СЧЁТЕСЛИ (A2: A7; A2: A7))
Эта формула будет работать для каждой ячейки в диапазоне A2: A7, она будет проверять, сколько раз она появляется. В нашем примере это создаст массив
{2, 2, 3, 3, 3, 1}
Затем мы превращаем все эти числа в дроби, выполняя какое-то деление. Теперь наш массив выглядит как
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Когда мы складываем все это, мы получаем желаемый результат - 3.
Countif с двумя или несколькими условиями - функция Countifs
До сих пор мы работали только с функцией СЧЁТЕСЛИ. Функция СЧЁТЕСЛИ может обрабатывать только один критерий за раз. Чтобы СЧЁТЕСЛИ с несколькими критериями, вам необходимо использовать функцию СЧЁТЕСЛИ. СЧЁТЕСЛИ действует точно так же, как СЧЁТЕСЛИ. Вы просто добавляете дополнительные критерии. Давайте посмотрим на пример ниже.
= СЧЁТЕСЛИМН (B2: B7; "= 130")
СЧЁТЕСЛИ и СЧЁТЕСЛИ в Google Таблицах
Функция СЧЁТЕСЛИ и СЧЁТЕСЛИМН работает в Google Таблицах точно так же, как и в Excel:
