Счет если эксель

Функция СЧЁТЕСЛИ() в MS Excel — Подсчет значений с единственным критерием

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

СЧЁТЕСЛИ(диапазон;критерий)

Диапазон — диапазон, в котором нужно подсчитать ячейки, содержащие числа, текст или даты.

Критерий — критерий в форме числа, выражения, ссылки на ячейку или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, «32», «>32», «яблоки» или B4.

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

Данные будем брать из диапазона A15:A25 (см. файл примера ).

Критерий

Формула

Результат

Примечание

Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле

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

Примечание. О подсчете значений, удовлетворяющих нескольким критериям читайте в статье Подсчет значений со множественными критериями. О подсчете чисел с более чем 15 значащих цифр читайте статью Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL.

Подсчет Текстовых значений с одним критерием

Функция СЧЁТЕСЛИ() также годится для подсчета текстовых значений (см. Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL).

Подсчет дат с одним критерием

Так как любой дате в MS EXCEL соответствует определенное числовое значение, то настройка функции СЧЕТЕСЛИ() для дат не отличается от рассмотренного выше примера (см. файл примера Лист Даты ).

Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу = СЧЁТЕСЛИ(B20:B30;2)

Подсчет с несколькими условиями

Обычно, в качестве аргумента критерий у функции СЧЁТЕСЛИ() указывают только одно значение. Например, =СЧЁТЕСЛИ(H2:H11;I2) . Если в качестве критерия указать ссылку на целый диапазон ячеек с критериями, то функция вернет массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив <3:2:5>.

Для ввода формулы выделите диапазон ячеек такого же размера как и диапазон содержащий критерии. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER, т.е. введите ее как формулу массива.

Это свойство функции СЧЁТЕСЛИ() используется в статье Отбор уникальных значений.

Специальные случаи использования функции

Возможность задать в качестве критерия несколько значений открывает дополнительные возможности использования функции СЧЁТЕСЛИ() .

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

Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел <1:4:4:4:4:1:3:3:3>, который говорит о том, что значение 1 из списка в диапазоне А6:А15 — единственное, также в диапазоне 4 значения 2, одно значение 3, три значения 4. Это позволяет подсчитать количество неповторяющихся значений формулой =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)) .

Функция СЧЕТЕСЛИ в Excel

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

По сути, функция СЧЕТЕСЛИ на английском COUNTIF, идентична во всех версиях Excel, поэтому вы можете использовать примеры из этого руководства в Excel 2016, 2013, 2010 и 2007.

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

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

Читать еще: Отобразить скрытые листы в excel

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

Синтаксис функции СЧЕТЕСЛИ очень прост:

Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:

диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, «10», A2, «>=10»).

Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;»Иванов Иван») подсчитывает, сколько заявок поступало от Иванова Ивана:

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

Примечание : Критерий нечувствителен к регистру, что означает, что если вы наберете «иванов иван» в качестве критерия в приведенной выше формуле СЧЕТЕСЛИ, это приведет к такому же результату.

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

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

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

Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.

Вместо ввода текста вы можете использовать ссылку на любую ячейку , содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку

Аналогичные формулы СЧЕТЕСЛИ работают для чисел , также как для текстовых значений.

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел

На изображении выше формула =СЧЁТЕСЛИ(B2:B7;10) учитывает ячейки с количеством 10 в столбце D.

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

Обратите внимание, что в функции СЧЕТЕСЛИ оператор с числом всегда заключен в кавычки, например, =СЧЕТЕСЛИ(B2:B7; «>=10»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел с логическим оператором

Функция СЧЕТЕСЛИ с подстановочными знаками (частичное совпадение)

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

Предположим, у вас есть список цветов, и вы хотите узнать количество цветов, в названии которых содержится слово «синий». Поскольку эти цвета можно написать несколькими разными способами, мы вводим «*синий*» в качестве критериев поиска =СЧЕТЕСЛИ(B2:B8;»*синий*»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с частичным совпадением

Звездочка (*) используется в функции СЧЕТЕСЛИ для поиска ячеек с любой последовательностью ведущих и конечных символов, как показано в приведенном выше примере. Если вам нужно сопоставить какой-либо один символ, введите вместо него знак вопроса (?) , например, =СЧЁТЕСЛИ(A2:A7;»ст?л»).

Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с подстановочным знаком

В данном случае функция СЧЕТЕСЛИ вернет значение 2, так как найдет «стол» и «стул».

Счет если эксель

Send a Message

This message will be pushed to the admin’s iPhone instantly.

Всем добрый день, сегодня я открываю рубрику «Функции» и начну с функции СЧЁТЕСЛИ. Честно говоря, не очень-то и хотел, ведь про функции можно почитать просто в справке Excel. Но потом вспомнил свои начинания в Excel и понял, что надо. Почему? На это есть несколько причин:

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

Читать еще: Программирование эксель

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

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

Если с первым аргументом более-менее понятно, можно подставить диапазон типа A1:A5 или просто название диапазона, то со вторым уже не очень, потому что возможности задания критерия достаточно обширны и часто незнакомы тем, кто не сталкивается с логическими выражениями.

Самые простые форматы «Критерия»:

Ячейка строго с определенным значением, можно поставить значения («яблоко»), (B4),(36). Регистр не учитывается, но даже лишний пробел уже включит в подсчет ячейку.
Больше или меньше определенного числа. Тут уже идет в ход знак равенства, точнее неравенств, а именно («>5»);(«<>10″);(» «&СРЗНАЧ(A1:A100))
Содержит определенное количество символов, например 5 символов:(«. «)
Определенный текст, который содержится в ячейке: («*солнце*»)
Текст, который начинается с определенного слова: («Но*»)
Ошибки: («#ДЕЛ/0!»)
Логические значения («ИСТИНА»)

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

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

Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.

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

Сначала рассмотрим аргументы функции:

Диапазон – группа значений для анализа и подсчета (обязательный).
Критерий – условие, по которому нужно подсчитать ячейки (обязательный).

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

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

Рекомендации для правильной работы функции:

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

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

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

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:

Формула посчитала «кровати» и «банкетки».

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

Формула: =СЧЁТЕСЛИ(A1:A11;»<>«&»стулья»). Оператор «<>» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

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

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

Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

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

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

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

Что возвращает функция

Функция СЧЁТЕСЛИ в Excel возвращает числовое значение обозначающее количество ячеек, отвечающих условию в заданном вами критерии.

=COUNTIF(range,criteria) – английская версия

=СЧЁТЕСЛИ(где нужно искать;что нужно найти) – русская версия

Аргументы функции

range (где нужно искать) – диапазон в рамках которого вы хотите осуществить подсчет ячеек;
criteria (что нужно найти) – критерий, по которому все ячейки в указанном вами диапазоне будут проверены на соответствие и только после этого функция подсчитает количество ячеек.

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

Критерием может служить число, выражение, ссылка на ячейку, текст или формула;
Критерий, указанный как текст или математический/логический символ (=,+,-,/,*) должны быть указаны в двойных кавычках.
Подстановочные знаки могут быть использованы в качестве критерия. В Excel существует три подстановочных знака – ?, *,