Excel счет если или в формулах

Помогла ли Вам статья?

Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ) в MS EXCEL

Произведем подсчет строк таблицы, значения которых удовлетворяют сразу двум критериям, которые образуют Условие ИЛИ. Например, в таблице с перечнем Фруктов и их количеством на складе, отберем строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не менее 57 (ящиков). Т.е. Партии Персиков отбираются в любом случае, а к ним добавляются партии любых фруктов с остатком на складе не менее 57 (ящиков).

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым «Фрукты» и числовым «Количество на складе» (См. файл примера ).

Подсчитаем строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не менее 57 (ящиков). Отбираются только те строки, у которых в поле Фрукты значение Персики ИЛИ строки, у которых в поле Количество ящиков на складе значение >=57 (как бы совершаетcя 2 прохода по таблице: сначала критерий применяется только по полю Фрукты, затем по полю Количество ящиков на складе, строки в которых оба поля удовлетворяют критериям во второй проход не учитываются, чтобы не было задвоения)).

Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =ИЛИ($A2=$D$2;$B2>=$E$2)

1. Количество: =СМЕЩ(пример1!$B$2;;;СЧЁТЗ(пример1!$A$2:$A$15))
2. Фрукты: = СМЕЩ(пример1!$A$2;;;СЧЁТЗ(пример1!$A$2:$A$15))
3. Таблица: = СМЕЩ(пример1!$A$1;;;СЧЁТЗ(пример1!$A$1:$A$15);2)

Это позволит в дальнейшем добавлять значения в исходную таблицу и не переписывать формулы для подсчета значений.

Подсчет можно реализовать множеством формул, приведем несколько:

• Формула =СЧЁТЕСЛИ(Фрукты;D2)+СЧЁТЕСЛИ(Количество;»>=»&E2)-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2) с помощью 2-х функций СЧЁТЕСЛИ() подсчитывает строки удовлетворяющие каждому из критериев, затем вычитается количество строк удовлетворяющих обоим критериям одновременно (функция СЧЁТЕСЛИМН() ) .
• Вместо 2-х функций СЧЁТЕСЛИ() можно использовать формулу = СУММПРОИЗВ((Фрукты=D2)+(Количество>=E2))-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2)
• Формула = БСЧЁТ(Таблица;B1;D13:E15) требует предварительного создания таблички с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в разных строках соответствует Условию ИЛИ (см. статью Функция БСЧЁТ() ).
• Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15) с теми же условиями, но нужно заменить столбец для подсчета строк, он должен быть текстовым, т.е. А.

Альтернативным решением, является использование Расширенного фильтра, с той же табличкой условий, что и для функций БСЧЁТА() и БСЧЁТ()

В случае необходимости, можно задавать другие условия отбора. Например, подсчитать строки, в которых в столбце Фрукты значится Персики ИЛИ строки с остатком на складе не более 57 (ящиков).

Это потребует незначительного изменения формул (условие «>=»&E2 нужно переписать как » Похожие задачи

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

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

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

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

Функция СЧЕТЕСЛИ в 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» (функция «СЧЕТ»).

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

Функция СЧЕТЕСЛИ в Эксель

Автор: rf-webmaestro · Опубликовано 26.02.2018 · Обновлено 08.02.2019

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

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

Оператор СЧЕТЕСЛИ в «Эксель» применяется для поиска значений, удовлетворяющих заданным условиям. Рассмотрим детально синтаксис. Функция записывается в ячейке в виде =СЧЕТЕСЛИ(диапазон;критерий)

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

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

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

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

Как пользоваться?

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

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

Последний способ – открыть окно с функциями. Это верхняя шапка программы. Вы можете сделать следующим образом:

1. Зайти во вкладку «Формулы» и нажать на кнопку «Вставить функцию».

1. После этого воспользоваться поиском (1) или выбрать группу «Статистические» и найти нужный оператор в списке (2).

1. Снова откройте раздел «Формулы» и нажмите на кнопку «Другие функции».

1. В открывшемся списке выберите «Статистические» и найдите нужный инструмент в списке.

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

Как посчитать значения с одним критерием

Начнем с простых операций – поиск определенных слов/чисел по одному заданному условию. Считать будем на примере простой таблицы:

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

1. Встаем в нужную ячейку и нажимаем отмеченную на скриншоте кнопку.

1. Находим СЧЕТЕСЛИ (можно написать в поиске для удобства).

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

1. Задаем критерий. Вписываем то значение, которое необходимо найти в выбранном массиве.

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

Считаем текстовые значения

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

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

1. Вписываем команду «=СЧЕТЕСЛИ» и открываем скобку.

1. Выделяем диапазон столбца с названиями позиций.

1. Теперь ставим знак «;» и пишем «торшеры». Между диапазоном и критерием пробел не ставится.

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

Поиск по части слова или букве

Инструмент позволяет подсчитать количество клеток, которые начинаются или заканчиваются на определенную букву. Также поиск можно осуществить по части слова. Рассмотрим следующий пример:

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

1. Выбираем диапазон с названиями продукции.

1. Далее вписываем букву «Т» и ставим знак «*» (звездочка). Если поставить его после вписанного знака, то буква будет считаться начальной. Если поставить звездочку перед буквой, то оператор проведет поиск по последним символам слов.

1. Жмем Enter и смотрим на результат.

Поиск исключений

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

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

1. Через точку с запятой в кавычках вписать «<>столы*». Звездочка необходима для того, чтобы в выборку входила ячейка «Столы1».

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

Поиск по нескольким критериям

Зачастую пользователям требуется провести поиск по нескольким названиям или условиям сразу. В этом случае вы можете объединить несколько формул в одну с помощью знака «+». Посчитаем, сколько позиций столов в количестве более 140 встречается в таблице:

1. Вводим синтаксис в необходимую клетку и выделяем диапазон. Вписываем «столы» и закрываем оператор.

1. Далее ставим знак «+» и пишем новую формулу, в которой используем те же значения списка, но вместо критерия пишем «>140».

1. Смотрим результат и сверяем.

Несколько полезных советов:

• если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
• при поиске текста не учитывается регистр символов;
• чтобы программа искала сами знаки >,

Функция счет если в excel по нескольким критериям

Например есть таблица с двумя колонками:

VRT | 1
VRT | 1
VRT | 2
DMT | 1
DMT | 5
TIM | 2
TIM | 4
TIM | 5
VRT | 3
DMT | 1
DMT | 2
TIM | 2

Мне нужно посчитать сколько цифр по каждому блоку. Например, сколько 1 и 2 у VRT, сколько 5 у DMT и т.д.

Результат должен быть вот таким:
VRT1 = 2 шт
VRT2 = 1 шт
VRT3 = 1 шт
DMT1 = 2 шт
DMT5 = 1 шт
DMT2 = 1 шт
TIM2 = 2 шт
TIM4 = 1 шт
TIM5 = 1 шт

Как это сделать?

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

Если хотите для конкретной пары то просто вот так ($A$1:$A$12 и $B$1:$B$12- столбец значений) :

Вот вариант через СЧЁТЕСЛИМН для всех пар

1) делаем таблицу уникальных пар — копируем всю таблицу — удаляем дубликаты ( Данные-> Удалить дубликать)

2) Делаем СЧЁТЕСЛИМН для каждой уникальной пары : =СЧЁТЕСЛИМН($A$1:$A$12;H1;$B$1:$B$12;I1)

Многие пользуются Excel-2003, где нет СЧЕТЕСЛИМН.

Вариант замены недостающей функции:

С доп. столбцом, где сцеплены данные (=A2&B2):

С наличием сцепленных данных можно отобрать уникальные формулой:

Формула массива, вводится одновременным нажатием Ctrl+Shift+Enter

Без доп. столбца тоже можно, но формула сложнее.

А если уникальные отобраны, то и считать просто.

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

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

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

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

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

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

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

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

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

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

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

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

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

Данные будут взяты из диапазона A15: A25.

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

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

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

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

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

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

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

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

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

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

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

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

Также прочтите: При включенной отладке jit любые необработанные исключения

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

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

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

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

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

Формула = СЧЁТЕСЛИ (A6: A14; «вычисляет ранг в порядке убывания для каждого числа из диапазона A6: A15. Вы можете проверить это, выделив формулу в строке формул и нажав F9. Значения будут соответствовать вычисленному рангу в столбце B (с помощью функции RANK ()) Этот подход применяется в статьях «Динамическая сортировка таблицы в MS EXCEL» и «Выбор уникальных значений с сортировкой в ​​MS EXCEL.

Функция COUNT подсчитывает количество ячеек, содержащих числа, и количество чисел в списке аргументов. Функция COUNT используется для определения количества числовых ячеек в диапазонах и массивах чисел. Например, чтобы вычислить количество чисел в диапазоне A1: A20, вы можете ввести следующую формулу: = COUNT (A1: A20). Если в этом примере пять ячеек в диапазоне содержат числа, результатом будет 5.

Синтаксис

Аргументы функции COUNT перечислены ниже.

Значение1 Обязательное. Первый элемент, ссылка на ячейку или диапазон, для которого вы хотите подсчитать количество чисел.

Value2;… — необязательный аргумент. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых вы хотите подсчитать количество чисел.

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

Замечания

Подсчитываются аргументы, которые являются числами, датами или текстовыми представлениями чисел (например, число, заключенное в кавычки, например «1″).

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

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

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

Смотрите также: Смарт-устройство на телевизоре Samsung

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

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

Пример

Скопируйте образец данных из приведенной ниже таблицы и вставьте его в ячейку A1 нового листа Excel. Чтобы просмотреть результаты формул, выберите их и нажмите F2, затем нажмите Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

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

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

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

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

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

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

Рекомендации по правильному функционированию функции:

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

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

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

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

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

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

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

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

Читайте также: Как привязать боковые кнопки на мышке

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

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

В формуле подсчитывались «кровати» и «скамейки».

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

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

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

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

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

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

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

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

1. Сначала отсортируем таблицу так, чтобы одинаковые значения располагались рядом друг с другом.
2. Первым аргументом формулы «INTERMEDIATE.TOTAL» является «Номер функции». Это числа от 1 до 11, которые обозначают статистическую функцию для вычисления промежуточного результата. Подсчет количества ячеек производится под числом «2» (функция «СЧЁТ»).

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

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

• Подсчет ячеек с условиями ИЛИ в Excel
  • Подсчет ячеек с условиями ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
  • Подсчет ячеек с условиями ИЛИ с помощью функции СУММПРОИЗВ
• Подсчет ячеек с несколькими наборами условий ИЛИ в Excel
  • Подсчет ячеек с двумя наборами условий ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН
  • Подсчет ячеек с несколькими наборами условий ИЛИ с помощью функции СУММПРОИЗВ

Подсчет ячеек с условиями ИЛИ в Excel

Например, у меня есть ряд данных, как показано на скриншоте ниже, теперь я хочу подсчитать количество продукта, который является «Карандашом» или «Линейкой», здесь я рассмотрю две формулы для решения этой задачи в Excel.

Подсчет ячеек с условиями ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН

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

=SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))

• range: Диапазон данных содержит критерии, по которым производится подсчет ячеек;
• criterion1, criterion2, criterion3…: Условия, на основе которых вы хотите подсчитывать ячейки.

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

=SUM(COUNTIFS(B2:B13,{«Pencil»,»Ruler»}))

Пояснение к формуле:

= СУММ (СЧЁТЕСЛИМН (B2: B13; {«Карандаш»; «Линейка»}))

• {«Карандаш», «Линейка»}: Во-первых, вы должны упаковать все условия в константу массива следующим образом: {«Pencil», «Ruler»}, разделите элементы запятыми.
• СЧЁТЕСЛИМН (B2: B13; {«Карандаш», «Линейка»}): Эта функция СЧЁТЕСЛИМН будет получать отдельные подсчеты для «Карандаш» и «Линейка», и вы получите следующий результат: {2,3}.
• СУММ (СЧЁТЕСЛИМН (B2: B13; {«Карандаш», «Линейка»})) = СУММ ({2,3}): Наконец, эта функция СУММ суммирует все элементы в массиве и возвращает результат: 5.

Tips: Вы также можете использовать ссылки на ячейки для критериев, примените формулу массива ниже, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить правильный результат:

=SUM(COUNTIF(B2:B13,D2:D3))

Подсчет ячеек с условиями ИЛИ с помощью функции СУММПРОИЗВ

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

=SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))

• range: Диапазон данных содержит критерии, по которым производится подсчет ячеек;
• criterion1, criterion2, criterion3…: Условия, на основе которых вы хотите подсчитывать ячейки.

Скопируйте или введите следующую формулу в пустую ячейку и нажмите Enter ключ для возврата результата:

=SUMPRODUCT(1*(B2:B13={«Pencil»,»Ruler»}))

Пояснение к формуле:

= СУММПРОИЗВ (1 * (B2: B13 = {«Карандаш», «Линейка»}))

• B2: B13 = {«Карандаш», «Линейка»}: Это выражение сравнивает каждый критерий «Карандаш», «Линейка» с ячейкой диапазона B2: B13. Если критерий соблюден, он возвращает ИСТИНА, в противном случае отобразится ЛОЖЬ, вы получите следующий результат: {ИСТИНА, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ИСТИНА; ЛОЖЬ, ЛОЖЬ; ИСТИНА, ЛОЖЬ. ; ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ИСТИНА; ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ЛОЖЬ; ЛОЖЬ, ИСТИНА; ЛОЖЬ, ЛОЖЬ}.
• 1 * (B2: B13 = {«Карандаш», «Линейка»}): Умножение преобразует логические значения — ИСТИНА и ЛОЖЬ в 1 и 0, поэтому результат будет следующим: {1,0; 0,0; 0,0; 0,1; 0,0; 1,0; 0,0 , 0,1; 0,0; 0,0; 0,1; 0,0; XNUMX}.
• SUMPRODUCT(1*(B2:B13={«Pencil»,»Ruler»}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): Наконец, функция СУММПРОИЗВ складывает все числа в массиве, чтобы получить результат: 5.

Подсчет ячеек с несколькими наборами условий ИЛИ в Excel

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

Подсчет ячеек с двумя наборами условий ИЛИ с помощью функций СУММ и СЧЁТЕСЛИМН

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

Например, у меня есть диапазон данных, как показано на скриншоте ниже, теперь я хочу подсчитать людей, заказавших «Карандаш» или «Линейку», и сумма будет либо <100, либо> 200.

Введите или скопируйте следующую формулу в ячейку и нажмите Enter ключ для получения результата, см. снимок экрана:

=SUM(COUNTIFS(B2:B13,{«Pencil»,»Ruler»},C2:C13,{«<100″;»>200″}))

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

Подсчет ячеек с несколькими наборами условий ИЛИ с помощью функции СУММПРОИЗВ

Вышеупомянутая формула работает только для двух наборов критериев ИЛИ. Если вам нужно произвести подсчет с большим количеством критериев, вам может помочь сложная формула СУММПРОИЗВ вместе с функциями ПОИСКПОЗ.

Возьмите, например, приведенные ниже данные, чтобы подсчитать людей, которые заказали «Карандаш» или «Линейка», а статус — «Доставлено» или «В пути» и подписано «Бобом» или «Эко», вам следует применить сложную формулу .

Скопируйте или введите приведенную ниже формулу в пустую ячейку и нажмите Enter ключ, чтобы получить расчет, см. снимок экрана:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,»Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,»In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,»Eko»},0)))

Пояснение к формуле:

=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,»Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,»In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,»Eko»},0)))

ЕЧИСЛО (ПОИСКПОЗ (B2: B13; {«Карандаш», «Линейка»}, 0)):

• ПОИСКПОЗ (B2: B13, {«Карандаш», «Линейка»}, 0): Эта функция ПОИСКПОЗ используется для сравнения каждой ячейки в диапазоне B2: B13 с соответствующей константой массива. Если совпадение найдено, возвращается относительное положение значения в массиве, в противном случае отображается значение ошибки. Итак, вы получите следующий список массивов: {1; # N / A; # N / A; 2; # N / A; 1; # N / A; 2; 1; # N / A; 2; # N / A}.
• ISNUMBER(MATCH(B2:B13,{«Pencil»,»Ruler»},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}): Функция ЕЧИСЛО преобразует числа в ИСТИНА, а значения ошибок в ЛОЖЬ следующим образом: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ}.

Вышеупомянутая логика также может быть применена ко второму и третьему выражениям ISNUMBER.

SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{«Pencil»,»Ruler»},0))*ISNUMBER(MATCH(C2:C13,{«Delivered»,»In transit»},0))*ISNUMBER(MATCH(D2:D13,{«Bob»,»Eko»},0))):

• Затем эти три массива умножаются вместе внутри СУММПРОИЗВ, который автоматически преобразует значения ИСТИНА и ЛОЖЬ в единицы и нули как часть математической операции, подобной этой: СУММПРОИЗВ ({1; 0; 1; 0; 0; 1; 0; 1; 0; 1; 1; 0} * {1; 0; 1; 1; 0; 0; 1; 1; 0; 1; 1; 0} * {1; 1; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0}) = СУММПРОИЗВ ({1; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0}).
• Наконец, функция СУММПРОИЗВ суммирует все числа в массиве, чтобы получить результат: 3.

Используемая относительная функция:

Другие статьи:

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

​Смотрите также​​ в набор функций​ и последнюю. «=​ В кавычки берем,​ указывается ссылка на​ необходимо, чтобы эта​630 000 ₽​диапазона​ условий, то в​ выражениями, но только​ способа тем, что​ должен выводиться результат.​ Excel 2007 и​ для Вас все​ значения, дату, время,​При подсчете текстовых значений​=СЧЁТЕСЛИ(B2:B5;»>55″)​Примечание:​ была добавлена функция​ 0» означает команду​ чтобы программа правильно​

​ диапазон ячеек с​ книга была открыта.​=СУММЕСЛИ(A2:A5; «>160000»)​. Если аргумент​ этом случае придут​ такие из них,​ позволяет зафиксировать подсчет​

​На вкладке​ более поздних версиях​

• ​ тайны функций​ а также логические​

​ убедитесь в том,​

• ​Количество ячеек со значением​

• ​ Мы стараемся как можно​

Синтаксис

​СУММЕСЛИМН (SUMIFS)​

Примеры

​ содержат начальных или​ ячейках В2–В5. Результат —​ актуальными справочными материалами​ количество условий проверки​Выбираем формат и устанавливаем,​Часто на практике одного​ формулы нужно выделить​

Распространенные неполадки

Рекомендации

​ ячейку для вывода​​Открывается окно Мастера функций.​: подсчитывает количество ячеек,​ 9000 и меньше​ доброго и успехов​ ячеек рабочего листа​ диапазоны.​ результате чего получается​ и сообщить, помогла​ окна можно задать​ ЕСЛИ в Excel​Синтаксис будет выглядеть следующим​ часто используется для​ ними знак тильды​ C2.​?​ важно, чтобы эта​

support.office.com

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

​ результата и запустили​ Ищем в представленном​ содержащих числа.​ или равны 22500​ в изучении Excel.​ Excel с применением​Функция СЧЁТЕСЛИ поддерживает именованные​ формула =СЧЁТЕСЛИ(B2:B5;»<>75″). Результат —​ ли она вам,​ и третью пару​Здесь вместо первой и​ образом:​ автоматизированной и эффективной​ (~).​490 000 ₽​) и звездочку (​​ статья была вам​​ Мастер функций, выбираем​​ списке элемент​​СЧИТАТЬПУСТОТЫ​ (4). Эту функцию​Автор: Антон Андронов​​ различного вида условий.​​ диапазоны в формулах​​ 3.​​ с помощью кнопок​​ (​​ последней ячейки диапазона​

СЧЕТ()

​=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))​​ работы с данными.​​Для нормального функционирования формулы​Скопируйте образец данных из​*​ полезна. Просим вас​ запись​«СЧЁТЗ»​: подсчитывает количество пустых​ можно использовать в​Примечание:​

​ Например, приведенная ниже​ (например, =СЧЁТЕСЛИ(​=СЧЁТЕСЛИ(B2:B5;»>=32″)-СЧЁТЕСЛИ(B2:B5;»>85″)​ внизу страницы. Для​​Диапазон_условия3​​ мы вставили имя​

​Здесь оператор проверяет два​ Поэтому продвинутому пользователю​ в ячейках с​

​ приведенной ниже таблицы​​). Вопросительный знак соответствует​​ уделить пару секунд​«СЧЁТЕСЛИ»​. После того, как​

​ ячеек.​ Excel 2003 и​Мы стараемся как​ формула возвращает количество​​фрукты​​Количество ячеек со значением,​ удобства также приводим​-​ столбца, которое присвоили​ параметра. Если первое​ настоятельно рекомендуется внимательно​

​ текстовыми значениями не​ и вставьте его​ одному любому символу,​ и сообщить, помогла​. Кликаем по кнопке​​ это наименование выделено,​​СЧЁТЕСЛИ​

СЧЕТЕСЛИ()

​ более ранних версиях,​​ можно оперативнее обеспечивать​​ ячеек, содержащих отрицательные​;»>=32″)-СЧЁТЕСЛИ(​ большим или равным​ ссылку на оригинал​Условие3​ ему заранее. Можно​ условие истинно, то​ изучить все приведенные​

​ должно пробелов или​ в ячейку A1​ а звездочка — любой​ ли она вам,​

​«OK»​​ жмем на кнопку​: подсчитывает ячейки, отвечающие​ в которых функция​ вас актуальными справочными​ значения:​фрукты​ 32 и меньшим​

​ (на английском языке).​​), и четвертую, и​​ заполнять формулу любым​ формула возвращает первый​​ выше примеры.​​ непечатаемых знаков.​​ нового листа Excel.​​ последовательности символов. Если​ с помощью кнопок​.​«OK»​ заданным условиям.​

​ СЧЁТЕСЛИМН не поддерживается.​ материалами на вашем​Следующая формула возвращает количество​;»>85″). Именованный диапазон может​​ или равным 85,​​С помощью статистической функции​ т.д. — при​ из способов. Но​

​ аргумент – истину.​Посчитаем количество реализованных товаров​​ Чтобы отобразить результаты​ требуется найти непосредственно​

​ внизу страницы. Для​​Открывается окно аргументов. У​​.​Совет:​Дата​ языке. Эта страница​ ячеек, значение которых​ располагаться на текущем​ в ячейках В2–В5.​ СЧЁТЕСЛИ можно подсчитать​

​ необходимости.​ с именем проще.​ Ложно – оператор​ по группам.​​Посчитаем числовые значения в​​ формул, выделите их​ вопросительный знак (или​ удобства также приводим​ данной функции два​Запускается окно аргументов. Аргументами​

​ Чтобы ввести нескольких условий,​11.03.2011​ переведена автоматически, поэтому​ больше содержимого ячейки​

​ листе, другом листе​​ Результат — 3.​​ количество ячеек, отвечающих​Если же у вас​​Имеем таблицу по продажам,​​ проверяет второе условие.​Сначала отсортируем таблицу так,​ одном диапазоне. Условие​​ и нажмите клавишу​​ звездочку), необходимо поставить​ ссылку на оригинал​​ аргумента: диапазон, где​​ этой функции являются​ используйте вместо этого​01.01.2010​ ее текст может​​ А4.​​ этой же книги​​=СЧЁТЕСЛИ(A2:A5;»*»)​​ определенному условию (например,​

​ пока еще старая​ например, следующего вида:​Примеры несколько условий функции​ чтобы одинаковые значения​ подсчета – один​ F2, а затем —​ перед ним знак​ (на английском языке).​​ происходит подсчет ячеек,​​ ссылки на ячейки.​​ функцию​​31.12.2010​ содержать неточности и​​СЧЕТЕСЛИ​​ или листе другой​​Количество ячеек, содержащих любой​​ число клиентов в​​ версия Excel 2003,​​Задача​ ЕСЛИ в Excel:​ оказались рядом.​ критерий.​

​ клавишу ВВОД. Кроме​

office-guru.ru

Подсчет числа или даты, по условию

​ «тильда» (​​Функция​ и критерий, то​ Ссылку на диапазон​СЧЁТЕСЛИМН​30.06.2010​ грамматические ошибки. Для​позволяет подсчитывать ячейки,​ книги. Чтобы одна​ текст, в ячейках​ списке из определенного​ но задачу с​: просуммировать все заказы,​Таблица для анализа успеваемости.​Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ»​У нас есть такая​ того, вы можете​~​СУММЕСЛИ​ есть, условие, о​ можно прописать вручную,​.​

​Формула​ нас важно, чтобы​ содержащие текстовые значения.​ книга могла ссылаться​ А2–А5. Подстановочный знак​ города).​ несколькими условиями решить​ которые менеджер Григорьев реализовал​​ Ученик получил 5​​ — «Номер функции».​ таблица:​ настроить ширину столбцов​).​используется, если необходимо​ котором мы говорили​ но лучше установить​Выделите диапазон ячеек и​Описание (результат)​​ эта статья была​​ Например, следующая формула​ на другую, они​ «*» обозначает любое​

Пример

support.office.com

Способы подсчета количества ячеек в диапазоне с данными

​Функция СЧЁТ​Подсчитывает число ячеек в​ Для удобства также​может содержать групповые​ фоном или цветом​ буквами «ки», в​

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

1. ​ задача решалась бы​В этом примере мы​

2. ​ «СЧЕТ»).​​ в отдельную ячейку,​​Помидоры​​диапазон_суммирования​​ можно использовать следующую​ этого жмем на​​ ячеек в нескольких​​Функция СЧЁТЗ​ диапазоне B14:B17 с​

   • ​ приводим ссылку на​​ символы:​ шрифта. Однако Excel​

   • ​ диапазоне A2–A5. Подставочный​​Имя аргумента​ «Копейку» и от​

   • ​ достаточно легко при​​ добавили третье условие,​Скачать примеры функции СЧЕТЕСЛИ​

   • ​ можно в качестве​​23 000 ₽​может не совпадать​

     ​ формулу:​​ кнопку​ удаленных друг от​Функция СЧИТАТЬПУСТОТЫ​​ датой 31.12.2010 (1).​​ оригинал (на английском​

3. ​*​ поддерживает пользовательские функции,​​ знак «?» обозначает​​Описание​

1. ​ Григорьева, то в​ помощи встроенной функции​

2. ​ подразумевающее наличие в​​ в Excel​​ критерия использовать ссылку:​​Овощи​​ по размерам с​=СУММЕСЛИ(B2:B25;»>5″)​​«OK»​​ друга диапазонах, то​Функция СЧЁТЕСЛИ​

   • ​ Знак равенства в​​ языке) .​(звездочку) и​

   • ​ в которых используются​​ отдельный символ. Результат —​диапазон​

   • ​ ячейке этого столбца​​ Excel​ табеле успеваемости еще​

   • ​Формула нашла количество значений​​Посчитаем текстовые значения в​Сельдерей​

     ​ аргументом​​Это видео — часть​.​ координаты второго, третьего​​При выполнении определенных задач​​ условии не обязателен​

3. ​Подсчет числа или даты,​?​​ операции VBA (Visual​​ 2.​

См. также

​    (обязательный)​ будет значение 1,​

​СУММЕСЛИ (SUMIF)​

​ и «двоек». Принцип​

​ для группы «Стулья».​

​ одном диапазоне. Условие​

​55 000 ₽​

support.office.com

Подсчет заполненных ячеек в Microsoft Excel

​диапазон​ учебного курса сложение​Для ручного ввода шаблон​ и последующего диапазона​ во время работы​ и поэтому не​ которые соответствуют одного​(вопросительный знак). Звездочка​ Basic для приложений)​Проблема​Группа ячеек, для которых​

​ иначе — 0.​ из категории​

Подсчет ячеек

​ «срабатывания» оператора ЕСЛИ​ При большом числе​ поиска – один​Фрукты​. При определении фактических​ чисел в Excel.​ выглядит следующим образом:​ нужно вводить в​ с таблицей бывает​

Способ 1: счетчик на строке состояния

​ указывается (формула будет​ условия (например, равно,​ обозначает любое количество​ над ячейками, выполняемые​Возможная причина​ нужно выполнить подсчет.​ Формула, которую надо​Математические (Math&Trig)​ тот же.​ строк (больше тысячи)​ критерий.​Апельсины​ ячеек, подлежащих суммированию,​Советы:​=СЧЁТЕСЛИ(диапазон;критерий)​ поля под названием​ необходимость произвести подсчет​ работать и со​ больше, чем меньше,​ произвольных символов, а​ в зависимости от​Для длинных строк возвращается​​Диапазон​​ ввести в этот​

​. Выделяем пустую ячейку​Когда нужно проверить несколько​ подобное сочетание функций​Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:​8 000 ₽​ в качестве начальной​ ​После этого программа производит​«Значение2»​ заполненных данными ячеек.​ знаком равенства («=31.12.2010»).​ чем больше или​ вопросительный знак –​ фона или цвета​ неправильное значение.​может содержать числа,​​ столбец очень простая:​​ для результата, жмем​ истинных условий, используется​

Способ 2: функция СЧЁТЗ

​ может оказаться полезным.​Во втором случае в​Масло​ используется верхняя левая​При необходимости условия можно​ подсчет заполненных ячеек​,​ Эксель предоставляет такую​=СЧЁТЕСЛИМН(B14:B17;»>=01.01.2010″;B14:B17;»​ равно или меньше​ один произвольный символ.​ шрифта. Вот пример​

1. ​Функция СЧЁТЕСЛИ возвращает неправильные​ массивы, именованный диапазон​=(A2=»Копейка»)*(B2=»Григорьев»)​ кнопку​​ функция И. Суть​​Логический оператор ЕСЛИ в​

   

2. ​ качестве критерия использовали​4 000 ₽​ ячейка аргумента​​ применить к одному​​ выделенного диапазона, которые​«Значение3»​ возможность с помощью​​Подсчитывает число ячеек в​​ или равно), используйте​

   

3. ​Например, чтобы подсчитать количество​ подсчета количества ячеек​ результаты, если она​ или ссылки на​Логические равенства в скобках​fx​ такова: ЕСЛИ а​​ Excel применяется для​​ ссылку на ячейку.​Овощи​диапазон_суммирования​ диапазону, а просуммировать​ соответствуют заданному условию,​и т.д. Когда​ встроенных инструментов. Давайте​ диапазоне B14:B17 с​ функцию​ ячеек, содержащих текст,​ определенного цвета с​ используется для сопоставления​​ числа. Пустые и​​ дают значения ИСТИНА​​в строке формул,​​ = 1 И​ записи определенных условий.​Формула с применением знака​​Морковь​​, а затем суммируются​

   

4. ​ соответствующие значения из​ и выводит их​ все данные введены.​ выясним, как выполнить​ датами в интервале​

   ​СЧЁТЕСЛИ​

   

5. ​ который начинается с​ использованием VBA.​ строк длиннее 255 символов.​ текстовые значения игнорируются.​ или ЛОЖЬ, что​ находим функцию​

Способ 3: функция СЧЁТ

​ а = 2​ Сопоставляются числа и/или​ подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).​42 000 ₽​ ячейки части диапазона,​ другого диапазона. Например,​ в область указанную​ Жмем на кнопку​

1. ​ указанную процедуру в​ от 01.01.2010 до​. Для подсчета​ буквы​Очень часто при работе​Для работы с такими​Узнайте, как выбирать диапазоны​ для Excel равносильно​​СУММЕСЛИ​​ ТОГДА значение в​​ текст, функции, формулы​​Для расчета количества значений,​

   

2. ​Фрукты​ соответствующей по размерам​ формула​ в первом пункте​«OK»​ этой программе.​ 31.12.2010 включительно (3).​ числа или даты,​Н​ в Excel требуется​ строками используйте функцию​ на листе.​​ 1 и 0.​​в списке:​

   

   ​ ИНАЧЕ значение с.​ и т.д. Когда​

   ​ оканчивающихся на «и»,​

   

3. ​Яблоки​ аргументу​=СУММЕСЛИ(B2:B5; «Иван»; C2:C5)​ этого способа.​.​

Способ 4: функция СЧЁТЕСЛИ

​Скачать последнюю версию​=СУММПРОИЗВ((B14:B17>=ДАТАЗНАЧ(«01.01.2010»))*(B14:B17​ которые входят в​(без учета регистра),​ подсчитать количество ячеек​ СЦЕПИТЬ или оператор​критерий​ Таким образом, поскольку​Жмем​Функция ИЛИ проверяет условие​ значения отвечают заданным​ в которых содержится​12 000 ₽​диапазон​суммирует только те​Оператор СЧЁТЕСЛИМН является продвинутым​

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

   

2. ​ОК​ 1 или условие​ параметрам, то появляется​ любое число знаков:​Формула​. Пример:​ значения из диапазона​ вариантом функции СЧЁТЕСЛИ.​​ ввести вручную в​​В программе Excel количество​ диапазоне B14:B17 с​ больше чем 9000​​ формулой:​​ Это могут быть​ =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна​Число, выражение, ссылка на​​ выражения, единица в​​ и вводим ее аргументы:​

   

   ​ 2. Как только​ одна запись. Не​

   ​ =СЧЁТЕСЛИ(A1:A11;"*и"). Получаем:​

   

3. ​Описание​Диапазон​ C2:C5, для которых​ Его используют, когда​ ячейку или строку​ заполненных ячеек можно​ датами в интервале​ на том же​

Способ 5: функция СЧЁТЕСЛИМН

​Если необходимо подсчитать количество​ пустые или заполненные​ длинная строка»).​ ячейку или текстовая​ конечном счете получится​Диапазон​ хотя бы одно​ отвечают – другая.​

1. ​Формула посчитала «кровати» и​Результат​Диапазон суммирования​ соответствующие значения из​ нужно указать более​​ формул, придерживаясь следующего​​ увидеть при помощи​ от 01.01.2010 до​​ время и меньше,​​ ячеек, которые содержат​

   

2. ​ ячейки, содержащие только​Функция должна вернуть значение,​ строка, которая определяет,​ только если оба​- это те​​ условие истинно, то​​Логические функции – это​​ «банкетки».​​=СУММЕСЛИ(A2:A7;»Фрукты»;C2:C7)​Фактические ячейки​ диапазона B2:B5 равны​ одного условия соответствия​ синтаксиса:​ счетчика на строке​ 31.12.2010 включительно (3).​ чем 22500), можно​ ровно четыре символа,​​ числовые значения, а​​ но ничего не​

   

   ​ какие ячейки нужно​ условия выполняются. Теперь​

   ​ ячейки, которые мы​

   

3. ​ результат будет истинным.​ очень простой и​Используем в функции СЧЕТЕСЛИ​Объем продаж всех продуктов​A1:A5​ «Иван».​

​ для различных диапазонов.​=СЧЁТЗ(значение1;значение2;…)​ состояния или целого​ В данном примере​ использовать функцию​ то используйте эту​ в некоторых случаях,​ возвращает.​ подсчитать.​ стоимости продаж осталось​ проверяем на выполнение​ Суть такова: ЕСЛИ​ эффективный инструмент, который​ условие поиска «не​ категории «Фрукты».​

​B1:B5​

lumpics.ru

Функция СУММЕСЛИ

​Если необходимо выполнить суммирование​​ Всего можно указать​После того, как формула​ ряда функций, каждая​ показано, как обойтись​СЧЁТЕСЛИМН​ формулу:​ их содержимое должно​Аргумент​Например, критерий может быть​ умножить на значения​Критерия​ а = 1​ часто применяется в​ равно».​20 000 ₽​B1:B5​ ячеек в соответствии​ до 126 условий.​ введена, программа в​ из которых подсчитывает​

​ без функции СЧЁТЕСЛИМН,​​. Кроме того​​Функция​ отвечать определенным критериям.​критерий​ выражен как 32,​ получившегося столбца и​. В нашем случае​ ИЛИ а =​ практике. Рассмотрим подробно​Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>»​=СУММЕСЛИ(A2:A7;»Овощи»;C2:C7)​​A1:A5​

​ с несколькими условиями,​Обозначаем ячейку, в которую​ заранее выделенной области​

​ элементы, заполненные определенным​​ которая впервые появилась​

• ​ можно использовать функцию​СЧЕТЕСЛИ​ В этом уроке​должен быть заключен​ «>32», В4, «яблоки»​ просуммировать отобранное в​​ — это диапазон​​ 2 ТОГДА значение​ на примерах.​ означает «не равно».​Объем продаж всех продуктов​B1:B3​ используйте функцию СУММЕСЛИМН.​

• ​ будет выводиться результат​ показывает результат подсчета​ типом данных.​ в Excel 2007.​

Синтаксис

​ СУММПРОИЗВ слишком.​

​позволяет использовать в​​ мы подробно разберем​​ в кавычки.​

• ​ или «32».​​ зеленой ячейке:​ с фамилиями менеджеров​ в ИНАЧЕ значение​Синтаксис оператора в Excel​ Знак амперсанда (&)​ категории «Овощи».​B1:B5​СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])​ и запускаем Мастер​ заполненных ячеек указанного​Самый простой способ подсчитать​ Функция ДАТАЗНАЧ преобразует​1​ качестве условия даже​

• ​ две основные функции​​Когда формула СЧЁТЕСЛИ ссылается​В функции СЧЁТЕСЛИ используется​Если вы раньше не​ продаж.​ с.​ – строение функции,​ объединяет данный оператор​120 000 ₽​A1:B4​Аргументы функции​ функций. В нем​

  ​ диапазона.​​ ячейки, содержащие данные​ даты в числовые​2​ формулы. К примеру,​ Excel для подсчета​​ на другую книгу,​​ только один критерий.​ сталкивались с такой​Критерий​

• ​Функции И и ИЛИ​​ необходимые для ее​ и значение «стулья».​=СУММЕСЛИ(B2:B7;»*ы»;C2:C7)​C1:D4​СУММЕСЛИ​​ ищем элемент​​Кроме того, для подсчета​​ – это воспользоваться​​ значения, с которыми​3​ чтобы посчитать количество​​ данных –​​ появляется ошибка #ЗНАЧ!.​ Чтобы провести подсчет​ замечательной возможностью Excel​

• ​- это то,​​ могут проверить до​​ работы данные.​При применении ссылки формула​Объем продаж всех продуктов,​​C1:D4​​описаны ниже.​​«СЧЁТЕСЛИМН»​​ заполненных ячеек в​ информацией со счетчика,​ может работать функция​4​ ячеек, значения в​СЧЕТ​Эта ошибка возникает при​ по нескольким условиям,​ как формулы массива,​​ что мы ищем​​ 30 условий.​

Примечания

• ​=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)​ будет выглядеть так:​ названия которых заканчиваются​A1:B4​Диапазон​. Выделяем его и​

• ​ Экселе существует ещё​​ который размещен в​​ СУММПРОИЗВ.​5​ которых больше среднего​​и​​ вычислении ячеек, когда​ воспользуйтесь функцией СЧЁТЕСЛИМН.​ то советую почитать​ в предыдущем указанном​Пример использования оператора И:​​Разберем синтаксис функции:​​Часто требуется выполнять функцию​ на «ы» («Помидоры»​C1:C2​   . Обязательный аргумент. Диапазон​​ жмем на кнопку​​ функция счет. В​

​«OK»​​ отличие от предыдущей​​ состояния слева от​​ функций, позволяющих подсчитать​​7​ следующей формулой:​, а также познакомимся​ функция, которая ссылается​ в Excel, скопируйте​

Примеры

Пример 1

​ много хорошего здесь.​ символы * (звездочка)​Пользователям часто приходится сравнить​ проверяет (текстовые либо​ по двум критериям.​43 000 ₽​Однако если аргументы​ соответствие условиям. Ячейки​.​ формулы, она считает​ кнопок переключения режимов​ число пустых ячеек​8​

Пример 2

​ книге. Для работы​ вставьте их на​ решается одной формулой:​ или символы подстановки.​ Примеры из «жизни»:​ в ячейке, когда​ возможности. Рассмотрим специальные​ категория для которых​диапазон_суммирования​ имена, массивы или​ те же самые,​Как и в предыдущем​ выделен диапазон, в​ в диапазоне.​11​

​ примеры с двумя​

support.office.com

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

​К началу страницы​ пересчет листа может​ ячейки, содержащие текстовые​«Диапазон»​ данные и таким​ один содержит какое-то​Щелкните ячейку, в которой​

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

​14​

• ​ подсчитывать ячейки в​СЧЕТЕСЛИМН​Действие​
• ​Данные​ Enter, как обычно,​ — один любой​

​ (бухгалтерские отчеты) за​Значение,если_ложь – ЧТО появится​ условиями.​Функция СЧЕТЕСЛИ входит в​ занять больше времени,​ значения, пропускаются. Выбранный​

​и​ же способом запускаем​ значение, данный индикатор​ должен выводиться результат.​15​ Excel, которые удовлетворяют​.​Результат​яблоки​

​ а​ символ. Так, например,​

• ​ несколько месяцев, успеваемость​ в графе, когда​Посчитаем, сколько ячеек содержат​ группу статистических функций.​ чем ожидалось.​
• ​ диапазон может содержать​«Условие»​ Мастер функций. В​
• ​ скрыт. Счетчик автоматически​На вкладке​
• ​16​ сразу двум и​Статистическая функция​Помните о том, что​32​Ctrl + Shift +​ чтобы найти все​ учеников (студентов) разных​ текст или число​ текст «столы» и​
• ​ Позволяет найти число​Скопируйте образец данных из​ даты в стандартном​. Разница только в​ нём выбираем оператора​

​ появляется тогда, когда​

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

​Формулы​17​ более условиям.​СЧЕТ​

​ функция СЧЁТЕСЛИ не​апельсины​

​ Enter​ продажи у менеджеров​ классов, в разные​ НЕ отвечают заданному​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ ячеек по определенному​

​ следующей таблицы и​ формате Excel (см.​ том, что диапазонов​ с наименованием​

​ выделено две или​щелкните​A​К примеру, следующая формула​

​подсчитывает количество ячеек​

​ учитывает регистр символов​54​- тогда Excel​

​ с фамилией из​ четверти и т.д.​

​ условию (лживы).​ Для указания нескольких​ критерию. Работает с​ вставьте их в​ примеры ниже).​

​ и соответствующих им​«СЧЁТ»​

​ более не пустых​Другие функции​B​

​ подсчитывает ячейки, значения​ в списке аргументов,​ в текстовых строках.​персики​ воспримет ее как​

​ пяти букв, можно​Чтобы сравнить 2 таблицы​

​Пример:​ условий используется несколько​ числовыми и текстовыми​ ячейку A1 нового​Условие​ условий может быть​. Жмем на кнопку​ ячеек, и сразу​, наведите указатель мыши​Продавец​

1. ​ которых больше нуля,​ которые содержат только​Критерий​75​ формулу массива и​ использовать критерий​ в Excel, можно​Оператор проверяет ячейку А1​
2. ​ выражений СЧЕТЕСЛИ. Они​ значениями, датами.​ листа Excel. Чтобы​   . Обязательный аргумент. Условие​ много. Вводим адреса​«OK»​ показывает их число​ на пункт​
3. ​Счет​ но меньше 50:​ числовые значения. Например,​не чувствителен к​яблоки​ сам добавит фигурные​
4. ​?????​ воспользоваться оператором СЧЕТЕСЛИ.​ и сравнивает ее​ объединены между собой​Сначала рассмотрим аргументы функции:​ отобразить результаты формул,​ в форме числа,​ диапазонов и соответствующие​.​ после слова​
5. ​Статистические​Егоров​Функция​ на рисунке ниже​ регистру. Например, строкам​86​ скобки. Вводить скобки​. А чтобы найти все​ Рассмотрим порядок применения​ с 20. Это​ оператором «+».​Диапазон – группа значений​ выделите их и​ выражения, ссылки на​

​ им условия, а​Запускается окно аргументов. Аргументы​«Количество»​и выберите одну​15 000​СЧЕТЕСЛИМН​ мы подсчитали количество​ «яблоки» и «ЯБЛОКИ»​Формула​

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

​ с клавиатуры не​ продажи менеджеров, у​

1. ​ функции.​ «логическое_выражение». Когда содержимое​Условия – ссылки на​
2. ​ для анализа и​ нажмите клавишу F2,​ ячейку, текста или​ потом жмем на​ те же самые,​.​ из следующих функции:​Егоров​позволяет подсчитывать ячейки,​ ячеек в диапазоне,​

​ будут соответствовать одни​Описание​

​ надо. Легко сообразить,​ которых фамилия начинается​Для примера возьмем две​ графы больше 20,​ ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2).​ подсчета (обязательный).​

exceltable.com

Функция ЕСЛИ в Excel с примерами нескольких условий

​ а затем — клавишу​ функции, определяющее, какие​ кнопку​ что и при​Но, хотя по умолчанию​СЧЁТЗ​9 000​ используя условие​ который полностью состоит​ и те же​

​=СЧЁТЕСЛИ(A2:A5;»яблоки»)​ что этот способ​ на букву «П»,​ таблицы с техническими​ появляется истинная надпись​ Текст «столы» функция​

Синтаксис функции ЕСЛИ с одним условием

​Критерий – условие, по​ ВВОД. При необходимости​ ячейки необходимо суммировать.​«OK»​

​ использовании предыдущего способа.​

​ этот счетчик включен,​

​: подсчитывает количество непустых​Гладких​И​

​ из чисел:​ ячейки.​Количество ячеек, содержащих текст​ (как и предыдущий)​ а заканчивается на​

​ характеристиками разных кухонных​ «больше 20». Нет​ ищет в ячейке​ которому нужно подсчитать​ измените ширину столбцов,​

​ Например, условие может​

​.​ В их роли​ и только ждет​ ячеек.​8 000​. Если же требуется​В следующем примере в​Использование подстановочных знаков​ «яблоки» в ячейках​

​ легко масштабируется на​ «В» — критерий​ комбайнов. Мы задумали​ – «меньше или​ А1. Текст «стулья»​

​ ячейки (обязательный).​ чтобы видеть все​ быть представлено в​Синтаксис у этой функции​ выступают ссылки на​ того, когда пользователь​СЧЁТ​Гладких​

​ подсчитать количество с​ двух ячейках диапазона​В​ А2–А5. Результат — 2.​ три, четыре и​П*В​ выделение отличий цветом.​ равно 20».​ — на базе​

​В диапазоне ячеек могут​

Функция ЕСЛИ в Excel с несколькими условиями

​ данные.​ таком виде: 32,​ следующий:​ ячейки. Вставляем координаты​ выделит определенные элементы,​: подсчитывает количество ячеек,​20 000​ условием​ содержится текст. Как​критерии​=СЧЁТЕСЛИ(A2:A5;A4)​

​ т.д. условий без​. Строчные и прописные​

​ Эту задачу в​

​Внимание! Слова в формуле​ критерия в ячейке​ находиться текстовые, числовые​Стоимость имущества​ «>32», B5, «32»,​=СЧЁТЕСЛИМН(диапазон_условия1;условие1; диапазон_условия2;условие2;…)​ диапазонов на листе,​

​ в некоторых случаях​ содержащих числа.​

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

​ А2.​ значения, даты, массивы,​Комиссионные​ «яблоки» или СЕГОДНЯ().​После этого приложение производит​ в которых нужно​ он может быть​

Расширение функционала с помощью операторов «И» и «ИЛИ»

​СЧИТАТЬПУСТОТЫ​5 000​, необходимо задействовать несколько​СЧЕТ​ знаки: вопросительный знак​ «персики» (значение ячейки​В категории​Диапазон_суммирования​

​ форматирование.​ кавычки. Чтобы Excel​Посчитаем число ячеек в​ ссылки на числа.​Данные​Важно:​ подсчет заполненных ячеек​ подсчитать количество заполненных​ отключен вручную. Тогда​: подсчитывает количество пустых​Туманов​ функций​

​их игнорирует.​ (?) и звездочку​ A4) в ячейках​

​Базы данных (Database)​

​- это те​

Как сравнить данные в двух таблицах

​Исходные данные (таблицы, с​ понял, что нужно​ диапазоне В1:В11 со​ Пустые ячейки функция​1 000 000 ₽​ Все текстовые условия и​ указанных диапазонов, которые​ ячеек числовыми данными.​ актуальным становится вопрос​ ячеек.​22 500​СЧЕТЕСЛИ​

​А вот ячейки, содержащие​ (*). Вопросительный знак​ А2–А5. Результат — 1.​ можно найти функцию​ ячейки, значения которых​

​ которыми будем работать):​ выводить текстовые значения.​ значением большим или​ игнорирует.​70 000 ₽​ условия с логическими​ соответствуют установленным условиям.​ Жмем кнопку​

​ о его включении.​СЧЁТЕСЛИ​

​Формула​. Например, следующая формула​ значения даты и​ соответствует одному любому​=СЧЁТЕСЛИ(A2:A5;A2)+СЧЁТЕСЛИ(A2:A5;A3)​

​БДСУММ (DSUM)​ мы хотим сложить,​Выделяем первую таблицу. Условное​Еще один пример. Чтобы​ равным 100 и​В качестве критерия может​

​2 500 000 ₽​ и математическими знаками​ Результат выводится в​«OK»​ Для этого нужно​: подсчитывает ячейки, отвечающие​Описание (результат)​

​ подсчитывает ячейки, значения​ времени, учитываются:​ символу, а звездочка —​Количество ячеек, содержащих текст​, которая тоже способна​

​ т.е. нашем случае -​ форматирование – создать​ получить допуск к​ меньшим или равным​ быть ссылка, число,​

​2 000 000 ₽​ необходимо заключать в​

​ заранее отмеченную область.​.​ кликнуть правой кнопкой​ заданным условиям.​=СЧЁТЕСЛИ(B2:B7;»>9000″)​ в которых начинаются​Функция​ любой последовательности знаков.​

exceltable.com

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

​ «яблоки» (значение ячейки​ решить нашу задачу.​

​ стоимости заказов.​​ правило – использовать​ экзамену, студенты группы​ 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).​

Способ 1. Функция СУММЕСЛИ, когда одно условие

​ текстовая строка, выражение.​140 000 ₽​ двойные кавычки (​Как видим, простейший подсчет​Для ручного введения формулы​ мыши по строке​Совет:​Функция СЧЁТЕСЛИ подсчитывает число​ с буквы​СЧЕТ​​ Если требуется найти​​ A2) и «апельсины»​​ Нюанс состоит в​​Если условий больше одного​ формулу для определения​ должны успешно сдать​​Применим в формуле СЧЕТЕСЛИ​​ Функция СЧЕТЕСЛИ работает​3 000 000 ₽​​»​​ количества заполненных ячеек​

​ придерживаемся следующего синтаксиса:​​ состояния и в​​ Чтобы ввести нескольких условий,​

• ​ ячеек в диапазоне​​А​может подсчитывать количество​ непосредственно вопросительный знак​​ (значение ячейки A3)​​ том, что для​ (например, нужно найти​ форматируемых ячеек:​ зачет. Результаты занесем​
• ​ несколько диапазонов. Это​​ только с одним​210 000 ₽​). Если условием является​ выделенного диапазона можно​=СЧЁТ(значение1;значение2;…)​ открывшемся списке установить​ используйте вместо этого​ B2:B7, значения которых​или с буквы​ ячеек сразу в​ (или звездочку), необходимо​ в ячейках А2–А5.​ работы этой функции​ сумму всех заказов​В строку формул записываем:​ в таблицу с​ возможно, если диапазоны​ условием (по умолчанию).​​4 000 000 ₽​​ число, использовать кавычки​ увидеть в строке​После этого в области,​ галочку напротив пункта​ функцию​ больше 9000 (4).​​К​​ нескольких несмежных диапазонах:​ поставить перед ним​
• ​ Результат — 3. В​​ необходимо создать на​ Григорьева для «Копейки»),​ =СЧЕТЕСЛИ (сравниваемый диапазон;​ графами: список студентов,​ являются смежными. Формула:​

Способ 2. Функция СУММЕСЛИМН, когда условий много

​ Но можно ее​280 000 ₽​ не требуется.​ состояния Эксель. Если​ в которой находится​​«Количество»​​СЧЁТЕСЛИМН​=СЧЁТЕСЛИ(B2:B7;»​:​Если необходимо подсчитать количество​ знак тильды (~).​ этой формуле для​ листе специальный диапазон​​ то функция​​ первая ячейка первой​ зачет, экзамен.​ =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения​ «заставить» проанализировать 2​Формула​Диапазон_суммирования​​ же вам нужно​​ формула, отобразится количество​. После этого счетчик​.​

​Функция СЧЁТЕСЛИ подсчитывает число​Функции Excel для подсчета​ непустых ячеек в​Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все​ указания нескольких критериев,​​ критериев — ячейки, содержащие​​СУММЕСЛИ (SUMIF)​​ таблицы)=0. Сравниваемый диапазон​​Обратите внимание: оператор ЕСЛИ​ по двум критериям​ критерия одновременно.​

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

Способ 3. Столбец-индикатор

​ диапазоне, то можно​ вхождения слова «яблок»​ по одному критерию​ условия отбора — и​не поможет, т.к.​ – это вторая​ должен проверить не​ сразу в двух​Рекомендации для правильной работы​Результат​ значения из которых​ отдельную область на​ данными.​

​Подсчитать количество заполненных ячеек​

​ нажмите клавишу​ B2:B7, значения которых​ и могут пригодиться​ воспользоваться статистической функцией​ с любой буквой​ на выражение, функция​ указать затем этот​ не умеет проверять​ таблица.​ цифровой тип данных,​ столбцах. Если диапазоны​ функции:​=СУММЕСЛИ(A2:A5;»>160000″;B2:B5)​ суммируются, если они​ листе, а тем​Данная функция позволяет подсчитать​

Способ 4. Волшебная формула массива

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

​ а текстовый. Поэтому​

​ несмежные, то применяется​Если функция СЧЕТЕСЛИ ссылается​Сумма комиссионных за имущество​ отличаются от ячеек,​​ более произвести подсчет​ не просто количество​​ функции СЧЁТЗ. Она​.​=СЧЁТЕСЛИМН(B2:B7;»>=9000″;B2:B7;»​ ситуации. Надеюсь, что​. Непустыми считаются ячейки,​Убедитесь, что данные не​ Также можно использовать​ аргумент:​ Поэтому начиная с​ диапазон, просто выделяем​ мы прописали в​ функция СЧЕТЕСЛИМН.​ на диапазон в​

Способ 4. Функция баз данных БДСУММ

​ стоимостью больше 1​​ указанных в качестве​​ с учетом определенных​​ ячеек, заполненных числовыми​​ отличается от предыдущего​Щелкните ячейку, в которой​Функция СЧЁТЕСЛИМН (доступна в​ данный урок открыл​ содержащие текст, числовые​ содержат ошибочных символов.​ функцию СЧЁТЕСЛИМН.​=БДСУММ(A1:D26;D1;F1:G2)​ версии Excel 2007​ его первую ячейку​ формуле В2= «зач.».​Когда в качестве критерия​

​ другой книге, то​

planetaexcel.ru

​ 600 000 ₽.​