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

Что такое функция СЧЕТЕСЛИ в Excel ? Как использовать функцию условного подсчета СЧЕТЕСЛИ в Excel ? Давайте выясним это вместе с LuckyTemplates.com!

Microsoft Excel — известное программное обеспечение для обработки данных электронных таблиц со множеством полезных функций. Благодаря им вы можете быстро рассчитать ряд данных в таблицах продаж, месячных зарплат и многого другого. Конечно, чтобы использовать определенную функцию, вам необходимо предоставить данные и соответствующие условия.

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

Оглавление статьи

• Для чего используется функция СЧЕТЕСЛИ?
• Синтаксис функции СЧЕТЕСЛИ в Excel
• Пример использования функции СЧЕТЕСЛИ
• Объедините функцию СЧЕТЕСЛИ с другими функциями Excel.
  • Используйте функцию РАНГ в сочетании с функцией СЧЕТЕСЛИ.
  • Используйте функцию СУММПРОИЗВ с функцией СЧЕТЕСЛИ.

Для чего используется функция СЧЕТЕСЛИ?

СЧЕТЕСЛИ — это функция Excel, которая подсчитывает ячейки в диапазоне, соответствующие одному условию. СЧЕТЕСЛИ можно использовать для подсчета ячеек, содержащих даты, числа и текст. Критерии, используемые в COUNTIF, поддерживают логические операторы (>, <, > <>, =) и подстановочные знаки (*,?) для частичного совпадения.

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

COUNTIF поддерживает только одно условие. Если вам нужно использовать несколько условий, используйте функцию СЧЁТЕСЛИ . Если вам необходимо манипулировать значениями аргумента диапазона в рамках логической проверки, см. функции СУММПРОИЗВ и/или ФИЛЬТР.

Синтаксис функции СЧЕТЕСЛИ в Excel

Функция СЧЕТЕСЛИ в Excel имеет синтаксис =СЧЕТЕСЛИ(диапазон;критерий) .

Где диапазон — это область, в которой вы хотите подсчитать необходимые данные. Может содержать числа, массивы или ссылки, содержащие числа. Пустые значения будут игнорироваться. Критерий — необходимое условие для подсчета значений в диапазоне, которые могут быть числами, выражениями, ссылками на ячейки или текстовыми строками.

Примечание читателям:

• Функция СЧЕТЕСЛИ возвращает результаты после использования условий со строками символов длиной более 255 символов.
• Аргумент критериев должен быть заключен в кавычки. Не различает строчные и прописные буквы.

Символы вопросительного знака и звездочки можно использовать в условиях критериев, где 1 вопросительный знак соответствует 1 символу, а 1 звездочка соответствует 1 строке символов. В зависимости от настроек устройства разделителем в функции является , или ; использовать.

• Как использовать функцию СЧЁТ в Excel
• Как объединить функцию Sumif и функцию Vlookup в Excel
• Как автоматически отображать имена при вводе кода в Excel

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

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

1. Найдите количество наименований товаров Манго в таблице.

У нас есть формула = СЧЕТЕСЛИ (B2: B8, «манго»), затем нажмите Enter, чтобы выполнить функцию.

Результатом будет значение с именем Xoai в таблице данных.

2. Найдите в таблице количество строк, не являющихся Лимонами.

Мы используем условие, что имя строки не Lemon, как «<>lemon» , а затем вводим формулу =COUNTIF(B2:B8,"<>lemon") . В результате получится 5 элементов, у которых в таблице данных нет названия лимон.

3. Найдите количество товаров с объемом продаж >= 10 кг.

Используйте условие с содержимым «>=10» в столбце количества продаж с формулой функции =СЧЕТЕСЛИ(C1:C8,»>=10») и нажмите Enter.

Результатом будет 6 товаров с объемом продаж >= 10 кг.

4. Найдите заказы с именем Lemon, используя альтернативные символы.

Мы можем заменить его на символ * для поиска значений по формуле =COUNTIF(B2:B8,"C*") и нажать Enter.

Результат будет таким, как показано ниже.

5. Найдите элементы с именами, отличными от ячейки B2.

Здесь мы найдем элементы с именами, отличными от ячейки B2 (Lemon), используя символ & перед ссылочной ячейкой с синтаксисом функции =COUNTIF(B2:B8,"<>"&B2) . Результат также показывает правильное количество элементов — 5.

Объедините функцию СЧЕТЕСЛИ с другими функциями Excel.

Используйте функцию РАНГ в сочетании с функцией СЧЕТЕСЛИ.

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

Чтобы лучше понять это, давайте посмотрим, как RANK.EQ работает вместе с COUNTIF. Формула имеет следующий вид:

=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1

Реализация этой формулы решит проблему пропуска чисел.

В вышеуказанных рядах нет дублирования. Но Джеймс Уокер и Джиллиан Тиллман , которые должны были иметь одинаковый рейтинг, теперь занимают разные места.

Таким образом, использование RANK.EQ с COUNTIF решило половину проблемы, но не дало желаемых результатов.

Используйте функцию СУММПРОИЗВ с функцией СЧЕТЕСЛИ.

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

Ознакомьтесь с рецептом ниже:

Формула может показаться сложной, но это лучший способ точно ранжировать элементы. Таким образом, вы сможете добиться желаемого результата, позволяя дублировать рейтинги и не пропуская цифры.

Представляя результаты своим ученикам, вы можете напрямую использовать формулу СУММПРОИЗВ вместо функции РАНГ. Для расчета уникальных рейтингов вы можете использовать функцию РАНГ.EQ отдельно или вместе с функцией СЧЕТЕСЛИ.

Изменить порядок окончательных результатов

На вкладке «Данные» щелкните группу «Сортировка и фильтр» и выберите порядок возрастания, чтобы отсортировать рейтинги.

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

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

Проблема	Решение
Возвращает false для длинных строк символов	Функция СЧЕТЕСЛИ возвращает неправильные результаты, если вы используете ее для сопоставления строк длиной более 255 символов. Чтобы сопоставить строки длиной более 255 символов, используйте функцию СЦЕПИТЬ или метод &. Например: =COUNTIF(A2:A5,"длинная строка"&"другая длинная строка").
Никакое значение не возвращается	Обязательно заключите аргумент критерия в кавычки.
Формула СЧЁТЕСЛИ получает #ЗНАЧЕНИЕ! при ссылке на другой лист	Эта ошибка возникает, если формула содержит функции, ссылающиеся на функции, ссылающиеся на ячейки или диапазоны в закрытой книге или вычисляемые ячейки. Чтобы эта функция работала, необходимо открыть другую книгу.

Разница между функциями СЧЕТЕСЛИ и СЧЕТЕСЛИ в Excel

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

Функциональность COUNTIFS можно дублировать, используя множество функций AND и OR в COUNTIF, но их сложно читать и составлять. COUNTIFS предоставляет более простое решение, помогая пользователям быстро анализировать данные со многими условиями без необходимости вложения нескольких уровней функций ЕСЛИ.

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

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

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

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

Желаю вам успехов!

Узнать больше:

• Как использовать функцию СРЗНАЧЕСЛИ в Excel
• Как использовать функцию ДЛСТР в Excel
• Как использовать функцию Vlookup в Excel