Формула счетесли в excel примеры несколько условий

Формула счетесли в excel примеры несколько условий

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

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

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

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

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

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

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

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

).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Вот простейший пример функции СЧЕТЕСЛИ в 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, так как найдет «стол» и «стул».

    В случаях, когда при работе в Excel требуется подсчитать количество ячеек, удовлетворяющих заданному условию, можно использовать функцию Счётесли .

    Функция Счётесли

    Счётесли (диапазон; критерий)

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

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

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

    Критерий проверки необходимо заключать в кавычки.

    Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

    Примеры использования функции Счётесли.

    1. Подсчет количества ячеек, содержащих отрицательные значения

    Счётесли(А1:С2;" "&A4) Диапазон — А1:С2 , критерий — ">"&A4

    1. Подсчет количества ячеек со словом "текст" (регистр не имеет значения).

    Счётесли(А1:С2;"текст") Диапазон — А1:С2 , критерий — "текст"

    1. Для текстовых значений в критерии можно использовать подстановочные символы * и ? .

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

    Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак

    Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:

    Счётесли(А1:С2;"Т * ") Диапазон — А1:С2 , критерий — "Т * "

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

    Счётесли(А1:С2;". ") Диапазон — А1:С2 , критерий — ". "

    В функции Счётесли используется только один критерий.

    Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.

    Функция Счётеслимн

    Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

    Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

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

    Пример использования:

    1. Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

    Счётеслимн(C1:C8;">15.01.2015";C1:C8;" 15.01.2015" и " Семинары. Вебинары. Конференции

    Актуальные темы. Лучшие лекторы Москвы и РФ. Сертификаты ИПБР. Более 30 тематик в месяц.

    Ссылка на основную публикацию
    Форге оф импаерс великие строения
    Другое название: Кузница Империй Ниже мы приводим подробный гайд по игре Forge of Empires с советами как вам быстрее отстроить...
    Троттлинг процессора что это
    Простой компьютерный блог для души) Всем привет. Сегодня мы затронем тему процессоров, а если быть точнее, то такое явление как...
    Троянские программы и хакерские утилиты
    В данную категорию входят программы, осуществляющие различные несанкционированные пользователем действия: сбор информации и ее передачу злоумышленнику, ее разрушение или злонамеренную...
    Форза хорайзен 3 список машин
    Серия игр Forza всегда поражала количеством доступных автомобилей. На момент выхода игры доступно уже более 150 автомобилей, а разработчики обещают...
    Adblock detector