127 условий подсчета ячеек в 1 формуле СЧЕТЕСЛИ Excel

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

На данном сайте вы найдете и другие статьи по работе с программой Excel.

Синтаксис и особенности функции

Прежде всего разберемся с аргументами функции СЧЕТЕСЛИ Excel. Всего здесь предусмотрено 2 аргумента:

• Диапазон – указываются группы данных, необходимых, чтобы выполнить анализ и подсчет. Здесь разрешается указывать значение в виде текста, числа, даты, массива либо ссылки на число. Если ячейка будет пустой, то счетесли функция просто ее проигнорирует (обязательно заполняется);
• Критерий – в этом аргументе указывается условие, по которому программе нужно будет считать ячейки. Значение разрешается записать в виде ссылки, числа, текстовой строки либо выражения. Работать программа сможет только с одним условием (в большинстве случаев). Но если это необходимо, то можно «принудить» ее анализировать до 2 условий сразу (обязательно заполняется).

Несколько полезных советов, которые помогут правильно пользоваться функцией если счет:

• Когда в функции СЧЕТЕСЛИ нужно сослаться на диапазон, который расположен в другой книге, то потребуется заранее эту книгу открыть;
• Любые аргументы «Критерий» всегда заключаются в кавычки (со ссылками так делать не нужно);
• Функция не умеет учитывать регистр текста;
• Во время формулирования списка разрешается пользоваться подстановочными знаками. Например, «?» будет обозначать любые символы, а «*» указывает на то, что здесь разрешается любая последовательность значений. Если нужно, чтобы формула смогла искать прямо по таким знакам – нужно, чтобы перед ними стояла «тильда» (~);
• Чтобы формула могла нормально функционировать в формулах, где имеются текстовые значения, нужно позаботиться о том, чтобы там не было пробелов или других знаков, которые нельзя напечатать.

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

Рассмотрим один наглядный пример, в котором пригодится функция Excel счетесли:

Допустим, у пользователя имеется таблица, в которой описываются продажи магазина (назовем его «Shop»). В этой таблице описывается очередность покупок, какой товар покупали и какой фирмой он был сделан.

И допустим, нам требуется выяснить, какой % среди всех продаж составила продукция фирмы “Tecno”. Как раз в этом случае нам и пригодится функция СЧЕТЕСЛИ. Чтобы выполнить расчет, воспользуемся следующей формулой:

=СЧЁТЕСЛИ(C3:C12;»Tecno»)/A12

Рассмотрим аргументы:

• «C3:C12» — здесь мы указываем диапазон ячеек, в которых содержаться наименования компаний. Здесь нужно посчитать количество значений в диапазоне в excel.
• «Tecno» — является критерием для поиска (программа будет искать точное совпадение в указанном диапазоне).
• «А12» — здесь была указана ячейка, в которой хранится номер, указывающий на общее число продаж.

Прописав такую формулу в ответ, мы получаем такой результат:

Как видно формула, отобразила корректный результат – в нашем случае получилось 40%. Условием для подсчета в этом случае был один установленный критерий.

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

Допустим, у нас имеется список магазинов, а также кол-во заказов, которые у них имеются.

Наша задача, узнать кол-во магазинов, число заказов которых превышает 100. Для этого воспользуемся написанной ниже формулой:

=СЧЁТЕСЛИ(B2:B11;» >100 «)

Пройдемся по всем аргументам формулы и посмотрим счетесли как работает:

• «В2:В11» — указывает на диапазон ячеек, где нужно производит подсчет.
• «>» — данный знак обозначает, что считать нужно те значения, которые больше указанного. Пример, >0, счетесли больше 0.
• «100» — это простое число, исходя из которого программа начнет сравнение чисел.

Если все сделать правильно, то получится следующий результат:

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

Промежуточные итоги и СЧЕТЕСЛИ

Вначале рассмотрим, для чего вообще пользуются функцией «Промежуточные итоги». Чаще всего она пригождается, чтобы вычислить промежуточный итог (например, сумму, среднее значение или их количество). При этом поиск выполняется в диапазоне, где могут присутствовать скрытые строки. Главной особенностью функции стало то, что ее можно и даже нужно использовать вместе с остальными функциями, например, с функцией countif excel в переводе будет эксель СЧЕТЕСЛИ.

Теперь разберем «Промежуточные итоги» на примере.

Предположим, что нам необходимо в excel посчитать количество ячеек со значением купленной продукции по группам.

Вот инструкция, как это сделать:

• Прежде всего нам нужно отсортировать имеющиеся данные таким образом, чтобы схожие значения находились рядом друг с другом. Чтобы это сделать, выделяем таблицу. Далее в разделе «Главная» кликаем на пункт «Сортировка и фильтр». После нажатия откроется меню, где нужно нажать на «Сортировка по возрастанию».

  

Выполнив это действие, данные в таблицы будут отсортированы нужным нам образом.

• Теперь поговорим про аргументы формулы. Первый из них – это «Номер функции». Здесь нужно указать число от 1 до 11. Они отвечают за выбор статической функции, которая рассчитает промежуточный результат. В нашем случае напишем цифру «2» («СЧЕТ»). Вторым аргументом нужно выделить требуемый диапазон ячеек. В нашем примере счетесли формула получилась следующей:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;A5:B7)

В итоге мы получаем желаемый результат:

Как работает функция СЧЕТЕСЛИМН

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

По синтаксису тоже ничего сложного нет:

=СЧЕТЕСЛИМН(пишем Диапазон_1;Условие_1; [Диапазон_2; Условие_2]…и так далее)

Разберем подробнее аргументы:

• Диапазон_1 – здесь мы определяем начальную область, где программа будет применять Условие_1 (вводить обязательно).
• Условие_1 – устанавливаем требования, по которым программа будет вести счет: например, это может быть число, ссылка на ячейку, текстовая строка, выражение или любая другая функция Excel. (заполнять обязательно).
• [Диапазон_2; Условие_2] – если требуется, пользователь может ввести дополнительные параметры. Достигнуть предела можно, если написать до 127 вот таких аргументов. (вводить необязательно).

Можно отметить, что пользователь не должен заучивать синтаксис программы. Достаточно просто понимать, для чего это указывается. Ведь Excel будет самостоятельно отображать подходящие аргументы.

Что нужно запомнить?

Несколько полезных фактов, которые следует знать при работе с функцией СЧЕТЕСЛИМН:

• Диапазон поиска в программе ограничен и заканчивается на 127 паре.
• В критериях разрешено пользоваться символами подстановки. Имеется в виду символы звездочка (*) и знак вопроса (?). Подробнее будет рассмотрено позже.
• Все дополнительные диапазоны обязательно должны иметь такое же количество строчек и столбцов, как и у первой пары. Если не соблюдать это условие, то будет выдаваться ошибка #ЗНАЧ!.
• Если пользователь не указал в аргументе ничего (или там имеется ссылка на пустые ячейки), то программа будет подставлять значение «0».
• Не запрещено пользоваться смежными и несмежными диапазонами.

Считаем с учетом всех критериев

Данный способ хорош тем, что он довольно прост в выполнении, так как excel функция СЧЕТЕСЛИМН будет нужна для того, чтобы подсчитывать только те ячейки, в которых указанные аргументы будут иметь значение «ИСТИНА».

Для каждого диапазона – свой критерий

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

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

=СЧЁТЕСЛИМН(B2:B10;»>0″;D2:D10;0)

Вкратце рассмотрим формулу, используемую выше. «B2:B11» — указывает на первый диапазон, а «>0» создает условие, что значение должно быть выше «0». Вторая часть формулы – это вторая пара аргументов. В итоге получаем желаемый результат.

Как видно, функция учла каждый счетесли критерий и показала верный ответ.

Одинаковый критерий для всех диапазонов

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

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

=СЧЁТЕСЛИМН(B2:B10;0;D2:D10;0)

Получаем значение «2», поскольку во всей таблице находиться только 2 товара, которые соответствуют установленным критериям.

ВАЖНО! Не стоит пытаться как-то упростить описанную выше формулу, поскольку при сокращении («=СЧЁТЕСЛИМН(B2:D10;0)») будет получаться совершенно другой результат. А все потому, что программа начнет искать любые ячейки в этом диапазоне со значением «0». В итоге получается ответ «5», но он не будет правильным…

Если достаточно выполнения хотя бы одного условия

Исходя из тех примеров, что мы рассматривали выше, стало понятно, что вести счет ячеек, которые соответствуют выбранным критериям, очень просто. А все потому, что функция счет если в excel была создана, как раз для этого. Но что, если пользователю нужно посчитать количество ячеек с определенным значением, данные которые совпадают с одним из имеющихся условий?

Существует 2 способа решения:

1. складываем сразу несколько функций СЧЕТЕСЛИ
2. пользуемся комбинацией СУММ+СЧЕТЕСЛИМН с константами массива.

Рассмотрим каждый из способов подробнее.

Вариант 1 — две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН

Вернемся, к примеру, со списком заказов. Предположим, что пользователь добавил столбец «Статус», где указывается, в каком состоянии находится заказ («доставлен», «готовиться к отправке» или «ожидает доставки»).

Нам нужно узнать, счетесли с несколькими условиями, сколько товаров «готовиться к отправке» либо «ожидает доставки». Чтобы выполнить задание, будет достаточно просто соединить 2 простых функции СЧЕТЕСЛИ. Вот так будет выглядеть формула:

=СЧЁТЕСЛИ(E2:E10;»Ожидает доставки»)+СЧЁТЕСЛИ(E2:E10;»Готовится к отправке»)

СОВЕТ! Если нужно вести счет сразу по нескольким параметрам, то можно вместо функции Excel СЧЕТЕСЛИ, воспользоваться СЧЕТЕСЛИМН.

Вариант 2 сумм счетеслимн с константой массива

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

Чтобы вставить СЧЕТЕСЛИМН в СУММ, требуется написать так:

СУММ(СЧЕТЕСЛИМН(Диапазон_1;{«Условие_1»; «Условие_2»; «Условие_3»;}))

Если опять же вернуться к нашей таблице, которая оказала нам сегодня «неоценимую услугу», то счет если формула для счета заказов с нужным статусом будет выглядеть так:

=СУММ(СЧЁТЕСЛИМН(E2:E10;{«Готовится к отправке»;»Ожидает доставки»}))

Массив будет обозначать, что пользователь хочет вначале найти заказы со статусом «Готовится к отправке», а потом с «Ожидает доставки». В итоге программа получает две цифры. Задача функции СУММ сложить получившиеся значения.

ВНИМАНИЕ! Подобным образом у пользователя получится применить счетесли с двумя условиями или более пар диапазонов.

Как сосчитать числа в интервале

Формула СЧЕТЕСЛИМН позволяет достичь 2 вида результатов: либо он будет основан на большом количестве ограничений (рассмотрели примеры выше), либо на числах, которые располагаются среди двух указанных значений. Сейчас более подробно обсудим последний вариант. Достичь такого результата можно при помощи двух способов:

• Применение функции СЧЕТЕСЛИМН;
• Вычитать одну СЧЕТЕСЛИ из другой.

СЧЕТЕСЛИМН для подсчета ячеек между двумя числами

К примеру, нам хочется узнать, сколько было заказов на товары в интервале от 20 до 30. Для этого воспользуемся этой формулой:

=СЧЁТЕСЛИМН(D2:D10;»>20″;D2:D10;»<30″)

Как видно, из всего диапазона, подобному отбору соответствует только 1 из них.

СЧЕТЕСЛИ для подсчета в интервале

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

=СЧЁТЕСЛИ(D2:D10;»>20″)-СЧЁТЕСЛИ(D2:D10;»>30″)

Как использовать ссылки в формулах СЧЕТЕСЛИМН

Когда пользователь использует логические операторы (например, «>», «<», «<=» либо «>=») можно использовать ссылку на ячейку. Но важно не забывать заключать операторы в кавычки и добавлять амперсанды перед ссылками (&). Рассмотрим, как пользоваться ссылками на примере.

Немного выше мы использовали следующую формулу:

=СЧЁТЕСЛИМН(D2:D10;»>20″;D2:D10;»<30″)

Но, что, если нам нужно вставить ссылки на ячейки со значениями, вместо того, чтобы вводить их ручную? Следуем следующей инструкции:

• Создаем ячейки и добавляем в них необходимое значение.

  

• Теперь просто заменяем в формуле цифры на ссылки, помня о советах, написанных выше.

=СЧЁТЕСЛИ(D2:D10;»>»&G2)-СЧЁТЕСЛИ(D2:D10;»>»&G3)

Получаем желаемый результат!

Как использовать счетеслимн со знаками подстановки

Чаще всего пользователи Excel пользуются следующими символами подстановки:

• Вопросительным знаком (?) — он обозначает любой отдельный символ. Применяется, чтобы подсчитывать ячейки, которые начинаются или заканчиваются на определенные символы.
• Звездочкой (*) – обозначает любую последовательность символов (также и нулевую). Разрешает заменять содержимое формулы.

ВАЖНО! Если нужно посчитать количество ячеек в excel, которые содержат в себе указанные выше символы, то нужно перед этими знаками поставить тильду (~).

В каких же ситуациях нам могут пригодиться символы подстановки.

Представим, что у нас имеется таблица с менеджерами, где мы указываем, у кого сколько заказов на данный момент. И нам с вами нужно вычислить, сколько менеджеров занимаются выполнением заказов.

РИС20

Для этого воспользуемся следующей формулой:

=СЧЁТЕСЛИМН(A2:A10;»*»;B2:B10;»<>»&»»)

Хочется также уточнить, что в первом случае был использован символ «*», потому что в том столбце все значения написаны в текстовом виде. А вот во второй раз был использован знак «&», поскольку мы анализировали цифровые значения. Запись «<>&» обозначает, что мы ищем те ячейки, которые не будут пустыми.

Программа показала абсолютно корректный результат.

Несколько условий в виде даты

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

Подсчет дат в определенном интервале

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

Например, у нас имеется таблица, в которой указано количество заказов на выполнение и сроки их сдачи. Нам нужно определить, какое количество заказов нужно сдать до 24–25 мая 2024 года. Для этого мы в отдельных ячейках записываем нужные даты.

Теперь применяем эту функцию и получаем готовый ответ:

=СЧЁТЕСЛИМН(C2:C10;»>=24.05.2024″;C2:C10;»<=25.05.2024″)

Либо же мы можем воспользоваться ссылками на ячейки со значениями:

=СЧЁТЕСЛИМН(C2:C10;»>=»&F2;C2:C10;»<=»&F3)

Подсчет на основе нескольких дат

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

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

Теперь, как и в прошлые разы, воспользуемся двумя формулами (одна будет с применением ссылок, а вторая без).

С использованием ссылок:

=СЧЁТЕСЛИМН(C2:C10;»>=»&G2;D2:D10;»>=»&G3)

Без использования ссылок:

=СЧЁТЕСЛИМН(C2:C10;»>=20.05.2024″;D2:D10;»>=25.05.2024″)

Подсчет дат с различными критериями на основе текущей даты

Кроме того, можно не записывать аргументы в отдельные ячейки, а воспользоваться интересной функцией СЕГОДНЯ(), чтобы вести счет относительно сегодняшнего дня.

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

=СЧЁТЕСЛИ(C2:C10;»<«&СЕГОДНЯ())

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

На этом эта статья подходит к своему концу. Надеемся, что описанные выше советы смогут вам помочь и буду полезны. Если имеются какие-то вопросы или предложения, можете написать их в комментариях. Успехов!