Счетесли в excel — примеры функции с одним и несколькими условиями

СРЕДНИЕ

Функция =СРЗНАЧЕСЛИМН позволяет найти среднее значение для определенного набора данных на основе одного или нескольких критериев. При использовании этой функции следует помнить, что каждый критерий и средний диапазон могут быть разными. Однако в функции =СРЗНАЧЕСЛИ и диапазон критериев, и диапазон суммы должны иметь один и тот же диапазон размеров. Заметили разницу в единственном и множественном числе между этими функциями? Ну, тут нужно быть осторожным.

В этом примере я хотел найти средний балл в зависимости от пола учащихся. Для этого я использовал формулу =СРЗНАЧЕСЛИМН(C2:C10, B2:B10, «M») и в результате получил 56,8. Здесь C2:C10 — это диапазон, в котором формула будет искать среднее значение, B2:B10 — это диапазон критериев, а «M» — это критерии.

Формула: =СРЗНАЧЕСЛИ(средний_диапазон, критерий_диапазон1, критерий1,…)

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это — 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

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

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

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

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

СУММ(СЧЁТЕСЛИМН(диапазон;{«условие1″;»условие2″;»условие3»;…}))

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

=СУММ(СЧЁТЕСЛИМН(E2:E11;{«Отменено»;»Ожидание»}))

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

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

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

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

​ ли у нас​04.05.2017​

​ решить нашу задачу.​​ (​fx​ выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).​

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

​ с оценками за​ и сложным таблицам.​ ячейки.​ аргументов.​ формулы нужно выделить​ Результат:​ решение, которое позволяет​ИЛИ​ например, на 2​ выбираем функцию «СЧЁТЕСЛИ».​​3​​ дублей по наименованию​​5​​ Нюанс состоит в​Диапазон_условия3​в строке формул,​​Если в качестве критерия​​ экзамен;​Функция СЧЁТЕСЛИ в Excel​​В любом месте таблицы​​Числа, даты и время​

​ такое количество ячеек,​​Если условие подсчета внести​​ не модифицировать формулы,​

• ​ строки, у которых в поле​​ часа). А ячейки​ Заполняем диалоговое окно​Количество ячеек, содержащих букву​​ товаров (если 1​​Бананы мини​ том, что для​-​ находим функцию​
• ​ указана текстовая строка,​​D2:D5 – диапазон ячеек,​ используется для подсчета​ можно ввести функцию.​ всегда учитываются функцией​ как в диапазоне​ в отдельную ячейку,​ а лишь менять​ Количество ящиков на​ с числом «7»​ так.​ «й» в любой​ – то дублей​74,5​ работы этой функции​Условие3​СУММЕСЛИ​ следует учитывать, что​ содержащих критерии для​​ количества ячеек в​​ Для определения заполненных​ СЧЁТ.​ с критериями. После​ можно в качестве​ знаки сравнения.​ складе значение >=57 (как​​ умножить на 6​​Указали диапазон столбца В.​ части текста. Часто​
• ​ нет)​​42​ необходимо создать на​), и четвертую, и​в списке:​ регистр символов не​

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

​ подсчета числа совпадений.​ рассматриваемом диапазоне, содержащиеся​ ячеек в диапазоне​Текст и ошибки никогда​ введения аргументов нажать​​ критерия использовать ссылку:​​Примечание​ бы совершаетcя 2​ (на 6 часов).​ «Критерий» — поставили​ используется для поиска​=СЧЁТЕСЛИ(D2:D9; СЕГОДНЯ())​14.05.2017​​ листе специальный диапазон​​ т.д. — при​Жмем​ имеет значения. Например,​В результате получим таблицу:​ данные в которых​ А1:С5 нужно прописать​​ не учитываются.​​ одновременно сочетание клавиш​Посчитаем текстовые значения в​: подсчет значений с​

​ прохода по таблице: сначала​В ячейке F1​ «>300» — это​ и дальнейшей замены​2​​6​​ критериев — ячейки, содержащие​​ необходимости.​​ОК​ функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет​Пример 3. В таблице​

​ соответствуют критерию, переданному​ такую формулу:​Логические значения и текстовое​ Shift + Ctrl​ одном диапазоне. Условие​ множественными критерями также​ критерий применяется только​ пишем такую формулу.​

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

​ значит, посчитать все​ «й» на «и»​Количество ячеек с товарами,​Яблоки глостер​ условия отбора — и​Если же у вас​ и вводим ее аргументы:​ значение 2, если​ Excel хранятся данные​ в качестве второго​Введение её через Enter​ представление числа не​ + Enter. Excel​

​ поиска – один​

​ рассмотрен в статьях Подсчет​ по полю Фрукты,​ =СЧЁТЕСЛИ(B1:E1;»4″)*2+СЧЁТЕСЛИ(B1:E1;»7″)*6​ ячейки в столбце​ и «ё» на​ поступившими на склад​54,2​ указать затем этот​ пока еще старая​Диапазон​ в ячейках A1​ о просмотрах страниц​ аргумента данной функции,​ даст результат 8.​ учитывается, если оно​ распознает формулу массива.​

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

​ критерий.​ значений с множественными​ затем по полю​Получилось 16 часов.​ В, где цифры​ «е»​ сегодня (используется системная​34​ диапазон функции как​ версия Excel 2003,​

​- это те​

​ и A2 записаны​ сайта за день​ и возвращает соответствующее​ Именно столько заполненных​​ хранится в ячейке.​СЧЕТЕСЛИ с двумя условиями​​Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:​ критериями (Часть 1.​ Количество ящиков на​Как посчитать количество​ больше 300.​=СЧЁТЕСЛИ(А2:А9; «??????»)​ дата компьютера)​15.05.2017​ аргумент:​ но задачу с​ ячейки, которые мы​ строки «петров» и​ пользователями. Определить число​

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

​ числовое значение.​​ ячеек есть в​​ Если эти же​​ в Excel очень​​Во втором случае в​ Условие И), Часть3, Часть4.​ складе, строки в​ не пустых ячеек,​Получилось такая формула.​1​=СЧЁТЕСЛИ(С2:С9; «»)​7​=БДСУММ(A1:D26;D1;F1:G2)​ несколькими условиями решить​ проверяем на выполнение​ «Петров» соответственно.​

​ пользователей сайта за​

planetaexcel.ru>

​Функция СЧЁТЕСЛИ может быть​

• Excel два условия в если
• Excel выделить цветом по условию excel
• Excel двойное условие
• Excel ранг с условием
• Excel суммирование по условию
• Excel сумма по условию
• В excel двойное условие если
• Excel если и несколько условий
• Выделить цветом ячейку в excel по условию
• Если 2 условия если excel
• Двойное условие в excel
• Несколько условий if excel

Подсчет ячеек с помощью ИЛИ, а также логики И

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

В качестве примера получим количество «яблок», «бананов» и «лимонов», которые «доставлены». Как мы это делаем? Для начала переведем наши условия на язык Excel:

• Столбец A: «яблоки», «бананы» или «лимоны».
• Колонка C: «доставлено»

Глядя под другим углом, нам нужно посчитать строки с «яблоками и доставленными» ИЛИ «бананами и доставленными» ИЛИ «лимонами и доставленными». Таким образом, задача сводится к подсчету ячеек с тремя условиями ИЛИ — именно то, что мы делали в предыдущем разделе! Единственное отличие состоит в том, что вы будете использовать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ для оценки критерия И в каждом условии ИЛИ.

Формула 1. СЧЁТЕСЛИМН + СЧЁТЕСЧЕСЛИМН

Это самая длинная формула, которую проще всего написать

=СЧЁТЕСЧЕСЛИМН(A2:A10, “яблоки”, C2:C10, “доставлено”) + СЧЁТЕСЧЕСЛИМН(A2:A10, “бананы”, C2:C10, “доставлено”)) + СЧЁТЕСЛИМН(A2:A10, “лимоны”, С2:С10, “доставлено”))

На снимке экрана ниже показана та же формула со ссылками на ячейки:

=СЧЁТЕСЧЕСЛИМН(A2:A10, K1, C2:C10, K2) + СЧЁТЕСЛИМН(A2:A10, L1, C2:C10, K2) + СЧЁТЕСЛИМН(A2:A10, M1,C2:C10, K2)

Формула 2. СЧЁТЕСЛИМН с константой массива

Более компактную формулу СЧЁТЕСЛИМН с логикой И/ИЛИ можно создать, упаковав критерий ИЛИ в константу массива:

=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”бананы”,”лимоны”}, C2:C10, “доставлено”))

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

=СУММ(СЧЁТЕСЛИМН(A2:A10,F1:H1,C2:C10,F2))

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

=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”*бананы*”,”лимоны”}, C2:C10, “доставлено”))

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

=СУММ(СЧЁТЕСЛИМН(A2:A10, {“яблоки”,”*бананы*”,”лимоны”}, C2:C10, “доставлено”, B2:B10, “>200”))

Или используйте эту формулу массива (вводится через Ctrl + Shift + Enter):

=СУММ(СЧЁТЕСЛИМН(A2:A10,F1:H1,C2:C10,F2, B2:B10, “>”&F3))

Формулы для подсчета пустых ячеек.

Функция СЧИТАТЬПУСТОТЫ.

Функция СЧИТАТЬПУСТОТЫ предназначена для подсчета пустых ячеек в указанном диапазоне. Она относится к категории статистических функций и доступна во всех версиях Excel начиная с 2007.

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

СЧИТАТЬПУСТОТЫ(диапазон)

Где диапазон — это область вашего рабочего листа, в которой должны подсчитываться позиции без данных.

Вот пример формулы в самой простейшей форме:

Чтобы эффективно использовать эту функцию, важно понимать, что именно она подсчитывает

1. Содержимое в виде текста, чисел, дат, логических значений или ошибок, не учитывается.
2. Нули также не учитываются, даже если скрыты форматированием.
3. Формулы, возвращающие пустые значения («»), — учитываются.

Глядя на рисунок выше, обратите внимание, что A7, содержащая формулу, возвращающую пустое значение, подсчитывается по-разному:

• СЧИТАТЬПУСТОТЫ считает её пустой, потому что она визуально кажется таковой.
• СЧЁТЗ  обрабатывает её как имеющую содержимое, потому что она фактически содержит формулу.

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

Как вы видите на рисунке выше, для подсчета непустых ячеек отлично подходит функция СЧЁТЗ:

СЧИТАТЬПУСТОТЫ — наиболее удобный, но не единственный способ подсчета пустых ячеек в Excel. Следующие примеры демонстрируют несколько других методов и объясняют, какую формулу лучше всего использовать в каждом сценарии.

Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.

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

В нашем случае формулы выглядят следующим образом:

или

Возвращаясь к ранее сказанному, вы можете также использовать выражение

Результаты всех трёх формул, представленных выше, будут совершенно одинаковыми. Поэтому какую из них использовать — дело ваших личных предпочтений.

Подсчёт пустых ячеек с условием.

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

Например, чтобы определить количество позиций, в которых записано «Бананы» в столбце A и ничего не заполнено в столбце C, используйте эту формулу:

Или введите условие в предопределенную позицию, скажем F1, что будет гораздо правильнее:

Считаем с учетом всех критериев (логика И).

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

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

Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).

Задача может быть выполнена таким образом:

или

Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.

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

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

Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:

Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.

Использование упрощенного варианта с одним ограничением выбора, например =СЧЁТЕСЛИМН(B2:D11;0), даст другой результат — общее количество ячеек в B2: D11, содержащих ноль (в данном примере это 5).

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

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

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

Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2

они входят в интервал B2

При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

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

​ от проданной продукции​ 2-м критериям (Условие​) =5, которое удовлетворяет​

Синтаксис

​В качестве исходной таблицы​

​=БСЧЁТА(A1:B13;A1;D14:F15) или БСЧЁТ(A1:B13;B1;D14:F15), которые требуют наличия​ критериями.​

• ​формула массива =СУММ((B2:B13<>=D2))​​ ячейку или текста,​ и Климов превысили​В условии можно использовать​Функция​

• ​ еженедельно и даже​​Прописываем аргументы.​ зарплаты.​Критерий – определяет, какие​ на пустую ячейку​ клавиш Ctrl+Shift+Enter:​ составляет техника фирмы​ И) можно без​ критерию >=5, поэтому​ возьмем таблицу с​ отдельной таблички с​

• ​Рассмотрим задачу, когда 1​​формула массива =СЧЁТ(ЕСЛИ((B2:B13>=D2)*(B2:B13​ которые определяют, какие​ квоту для периодов​ подстановочные знаки: вопросительный​СЧЁТЕСЛИМН​

​ чаще. Пользуйтесь поиском​​диапазон суммирования – ячейки​Аналогично можно подсчитать зарплаты​ ячейки из диапазона​ или пустая строка​Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив​​ Samsung.​​ применения формул с​ второе значение в​ двумя столбцами: текстовым​

Замечания

• ​ критериями.​ текстовый критерий с​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют​ ячейки требуется учитывать.​ Q1, Q2 и​ знак (?) и​применяет критерии к​ по форуму.​ с зарплатой;​ всех менеджеров, продавцов-кассиров​ будут выбраны (записывается​ «», результат вычисления​ дробных чисел 1/количество_вхождений,​Вид исходной таблицы данных:​ помощью стандартного Автофильтра.​

• ​ массиве =1 и​ «Фрукты» и числовым​О подсчете с множественными​ подстановочным знаком применяются​​ наличия отдельной таблички​​ Например, условие может​

• ​ Q3 (только в​ звездочку (*). Вопросительный​ ячейкам в нескольких​r_kot​диапазон условия 1 –​ и охранников. Когда​ в кавычках).​ для любого диапазона​ например, для пользователя​Для расчета используем формулу:​Установите автофильтр к столбцу​ т.д.​​ «Количество на складе»​​ критериями можно почитать​

Пример 1

​ к значениям текстового​ с критериями.​ быть выражено следующим​ Q2).​ знак соответствует любому одиночному​ диапазонах и вычисляет​: Поправил формулу. Вместо​ ячейки с указанием​ табличка небольшая, кажется,​Диапазон суммирования – фактические​ ячеек будет числовое​ с ником sam​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

​ Количество ящиков на​	​Далее, функция попарно перемножает​	​ (См. файл примера).​	​ в этом разделе.​
​ столбца, а другой​	​Рассмотрим задачу, когда 2​	​ образом: 32, «>32»,​	​1​
​ символу; звездочка —​	​ количество соответствий всем​	​ enter жми ctrl+shift+enter​	​ должности сотрудника;​
​ что все можно​	​ ячейки, которые необходимо​	​ значение 0 (нуль).​	​ это значение равно​
​Описание аргументов:​	​ складе, выделив заголовок​	​ элементы массивов и​	​Рассмотрим задачу, когда критерии​
​В этом разделе перечислены​	​ (числовой) — к значениям​	​ критерия заданы в​
​ B4 или «яблоки»;​	​Данные​ любой последовательности символов.​ критериям.​ и наступит результат!​условия 1 – продавец;​	​ сосчитать и вручную,​
​ просуммировать, если они​	​Функция может быть использована​ 0,25 (4 вхождения).​C3:C17 – диапазон ячеек,​ столбца и нажав​ суммирует их. Получаем​	​ применяются к значениям​
​ распространенные причины ошибки​	​ столбца с числами (см. файл​ форме дат и​Диапазон_условия2, условие2… ​1​ Если нужно найти​Это видео — часть учебного​	​r_kot​

Пример 2

​диапазон условия 2 –​
​ но при работе​	​ удовлетворяют критерию.​
​ в качестве формулы​	​ Общая сумма таких​
​ содержащих названия фирм​	​CTRL+SHIFT+L. ​
​ – 2.​	​ из разных столбцов.​
​ #ЗНАЧ! в функциях​	​ примера, лист 1текст (с​
​ применяются к значениям​	​   Необязательные аргументы. Дополнительные диапазоны​
​01.05.2011​	​ сам вопросительный знак​	​ курса Усложненные функции​
​: Поправил формулу. Вместо​	​ ячейки с указанием​ со списками, в​Получается, что у функции​ массива, если требуется​ значений, вычисляемая функцией​	​ проданной техники;​
​Выберите числовой фильтр Между.​	​3. Другим вариантом использования​Найдем число партий товара​ СЧЁТЕСЛИ и СЧЁТЕСЛИМН.​ подстанов) 1числовой).​ одного из столбцов.​ и условия для​	​2​
​ или звездочку, поставьте​	​ ЕСЛИ.​ enter жми ctrl+shift+enter​ пола сотрудника;​ которых по несколько​ всего 3 аргумента.​ выполнить расчет числа​	​ СУММ, соответствует количеству​

support.office.com>

Что такое автосумма в таблице Excel?

Вторым по простоте способом является автосуммирование. Установите курсор в то место, где вы хотите увидеть расчет, а затем используйте кнопку «Автосумма» на вкладке Главная, или комбинацию клавиш  ALT + =.

В активной ячейке появится функция СУММ со ссылками на смежные ячейки. Эксель попытается угадать, что же именно вы хотите сделать. Нажмите ENTER, чтобы закончить ввод формулы.

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

=СУММ()

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

Кроме того, ALT + = можно просто нажать, чтобы быстро поставить формулу в ячейку Excel и не вводить ее руками. Согласитесь, этот небольшой хак в Экселе может сильно ускорить работу.

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

Под видео на всякий случай дано короткое пояснение.

Итак, выберите диапазон ячеек и дополнительно ряд пустых ячеек снизу и справа (то есть, В2:Е14 в приведенном примере).

Нажмите кнопку «Автосумма» на вкладке «Главная» ленты. Формула сложения будет сразу введена.

Счетеслимн в excel — IT Справочник

• Создадим табличку критериев (желательно над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), например в диапазоне A2:A3 (см. рисунок выше). Табличка должна состоять из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
• Условия отбора могут быть записаны в текстовом формате. В нашем случае, просто >3000
• Теперь можно записать саму формулу =БСЧЁТ(B8:B13;B8;A2:A3) В B8:B13 находится База_данных (исходная таблица). Столбец А (Продавец) можно в данном случае не включать в базу_данных , т.к. по нему не производится подсчет и он не участвует в отборе. B8 – это ссылка на заголовок столбца по которому будет производиться подсчет ( Продажи ), в нем должны быть числовые значения. A2:A3 – ссылка на табличку критериев.

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;« <70»).

Как сделать счетесли в excel?

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

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

​ текст «столы» и​ на диапазон в​ совпадать с заголовками​ сразу двум критериям,​ считает ячейки, в​ в Excel​ текстовыми данными! Если​

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

​ можно комбинировать в​

• ​ примеры. Допустим, у​Если вы раньше не​ буквы не различаются.​
• ​ с числами больше​ формулу:​Для расчета используем формулу:​

​ для указания. Для​ все табличные элементы​ «Счёт». Игнорируя текстовый​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ другой книге, то​ исходной таблицы. Размещение​

​ которые образуют Условие​ которых написано «да».​.​ в ячейке нет​ условии различные функции.​ нас есть список​ сталкивались с такой​Диапазон_суммирования​ 3 и менее​

​В результате расчета получим:​=СЧЁТЕСЛИ(C3:C17;»Samsung»)/A17​

• ​ наглядности стоит рассмотреть​ будут прописаны в​ формат, она учитывает​ Для указания нескольких​ необходимо, чтобы эта​
• ​ условий в разных​ ИЛИ. Например, в​ Получилось три слова​
• ​Например, нужно посчитать​ ни одной буквы,​
• ​Введенный в предыдущем разделе​ овощей и фруктов,​ замечательной возможностью Excel​- это те​ 8 необходимо выполнить​Функция имеет следующую синтаксическую​Описание аргументов:​ следующий пример.​ виде аргументов. Она​ только числовые значения.​
• ​ условий используется несколько​ книга была открыта.​ строках соответствует Условию​ таблице с перечнем​ «да».​

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

​ покажет всегда 0​ набор данных поможет​ хранящихся на складе​ как формулы массива,​

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

​ запись:​C3:C17 – диапазон ячеек,​Пример 3. Есть ведомость​ будет выглядеть так:​Часто, для анализа табличных​ выражений СЧЕТЕСЛИ. Они​

​Аргумент «Критерий» нужно заключать​ ИЛИ (см. статью​ Фруктов и их​Если нужно посчитать​

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

​ то советую почитать​

​ мы хотим сложить,​записать первую функцию СЧЁТЕСЛИ​=СЧЕТЕСЛИ(диапазон; критерий)​

​ содержащих названия фирм​ с фамилиями студентов​

​=СЧЁТ(12; мост; кг; крыло;​ данных не нужно​ объединены между собой​ в кавычки (кроме​ Функция БСЧЁТ()).​

​ количеством на складе,​ процентное отношение ответов​

​ посчитать результат голосования​ в Excel и​ «СЧЕТЕСЛИ» в Excel.​

​ закупочной цены, продажной​ предварительно про них​ т.е. нашем случае -​ с критерием «>3»;​Описание аргументов:​

​ проданной техники;​ и оценками за​

​ 33; 45678; «22»;​ производить никаких математических​ оператором «+».​ ссылок).​Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15)​ отберем строки, в​ «да» по отношению​ или ответов в​ анализ данных, хранящихся​ Примеры, рассматриваемые в​

1. ​ надбавки и датой​ много хорошего здесь.​ стоимости заказов.​записать вторую функцию с​диапазон – обязательный аргумент,​»Samsung» – критерий поиска​ экзамен. В таблице​ сила; ампер; 16.02.1999;​
2. ​ операций с числами.​Условия – ссылки на​Функция не учитывает регистр​ с теми же​ которых в столбце​ ко всем ответам,​ анкете, опросе, провести​ в них, практически​
3. ​ текущем разделе, будут​ закупки. Начинается работа​ Ну, а в​Если условий больше одного​ критерием «>=8»;​ принимающий ссылку на​
4. ​ (точное совпадение);​ 2 столбца и​ 14.07.1975; стена; потолок;​ Достаточно уточнить количество​ ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2).​ текстовых значений.​ условиями, но нужно​ Фрукты значится Персики​ то формула будет​ другой анализ данных,​
5. ​ невозможен без использования​ довольно простыми, однако​ с таблицами в​ нашем случае задача​ (например, нужно найти​определить разницу между возвращаемыми​ одну либо несколько​A17 – ячейка, хранящая​ 10 ячеек. Нужно​ 21)​ показателей. Именно для​ Текст «столы» функция​При формулировании условия подсчета​ заменить столбец для​

​ ИЛИ строки с​ сложнее. Смотрите формулу​ т.д.​ подстановочных знаков.​ они основополагающие, и​ Excel — переносим​ решается одной формулой:​ сумму всех заказов​ значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То​

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

​ ячеек, в которых​ номер последней продажи,​

1. ​ определить, какое количество​Введя формулу, мы получим​ такого случая пригодится​
2. ​ ищет в ячейке​ можно использовать подстановочные​ подсчета строк, он​ остатком на складе​ в строке 22​Эта функция нужна​Комбинирование с другими функциями​ рассмотреть их необходимо,​ на рабочий лист​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​

​ Григорьева для «Копейки»),​ есть, вычесть из​

​ требуется определить число​ соответствующий общему числу​ студентов получили отличную​ значение 5.​ эта простая функция​ А1. Текст «стулья»​

exceltable.com>

Как посчитать, содержит ли ячейка текст или часть текста в Excel?

Предположим, у вас есть данные, указанные ниже, и вы хотите подсчитать количество ячеек, содержащих текст «Apple», количество ячеек, содержащих текст»Оранжевая»и ячейки содержат»персик»отдельно, как это сделать? В этом руководстве мы объясняем COUNTIF Подробная функция, которая поможет вам быстро подсчитать количество ячеек, если они содержат текст или часть определенного текста в Excel. Кроме того, мы представляем замечательную функцию, позволяющую легко достичь ее с помощью только щелчков мышью.

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

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

1. Выберите пустую ячейку (например, E5), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы получить все результаты.

=COUNTIF(B5:B10,»*»&D5&»*»)

Синтаксис

=COUNTIF (range, criteria)

аргументы

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

Заметки:

• В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
• Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
• В этой формуле регистр не учитывается.

Освободи Себя Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро подсчитать количество ячеек в диапазоне, если они содержат определенный текст или часть текста. После получения результата во всплывающем диалоговом окне все совпавшие ячейки будут выбраны автоматически. .Загрузите Kutools for Excel прямо сейчас! (30-дневная бесплатная трасса)

Счетные ячейки содержат текст с функцией СЧЁТЕСЛИ

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

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

=COUNTIF(B5:B10,»*»)

Подсчитайте, если ячейка содержит текст или часть текста с помощью Kutools for Excel

Чаевые: Помимо приведенной выше формулы, здесь представлена ​​замечательная функция, позволяющая легко решить эту проблему. С Выбрать определенные ячейки полезности Kutools for Excel, вы можете быстро подсчитать, содержит ли ячейка текст или часть текста, щелкнув мышью. С помощью этой функции вы даже можете подсчитать с помощью OR или AND, если вам нужно. Пожалуйста, сделайте следующее.

Перед использованием Kutools for Excel, вам нужно потратить несколько минут, чтобы в первую очередь.

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

2. Нажмите Kutools > Выберите > Выбрать определенные ячейки.

3. в Выбрать определенные ячейки диалоговое окно, вам необходимо:

• Выберите Ячейка вариант в Тип выбора раздел;
• В Конкретный тип раздел, выберите Комплект в раскрывающемся списке введите Apple в текстовом поле;
• Нажмите OK кнопку.
• Затем появляется окно подсказки, в котором указано, сколько ячеек соответствует условию. Щелкните значок OK кнопка и все соответствующие ячейки выбираются одновременно.

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

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

Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …

Подсчитайте конкретное значение на нескольких листах Как можно подсчитать конкретные значения на нескольких листах? Например, получить количество вхождений определенного значения «Excel» из нескольких листов. В этом руководстве представлены методы, позволяющие это сделать.Нажмите, чтобы узнать больше …

Метод 2: применение функции СЧЕТЗ

ИНДЕКС/СООТВЕТСТВИЕ

Как и функция ВПР, функции ИНДЕКС и ПОИСКПОЗ удобны для поиска конкретных данных на основе входного значения. ИНДЕКС и ПОИСКПОЗ при совместном использовании могут преодолеть ограничения ВПР, связанные с получением неправильных результатов (если вы не будете осторожны). Таким образом, когда вы объединяете эти две функции, они могут точно определять ссылку на данные и искать значение в одномерном массиве. Это возвращает координаты данных в виде числа.

В приведенном выше примере я хотел посмотреть количество просмотров в январе. Для этого я использовал формулу = ИНДЕКС (A2: C13, ПОИСКПОЗ («Янв», A2: A13,0), 3). Здесь A2: C13 — это столбец данных, который я хочу, чтобы формула возвращала, «Jan» — это значение, которое я хочу сопоставить, A2: A13 — это столбец, в котором формула найдет «Jan», а 0 означает, что я хочу формула для поиска точного совпадения значения.

Если вы хотите найти приблизительное совпадение, вам придется заменить 0 на 1 или -1. Таким образом, 1 найдет наибольшее значение, меньшее или равное искомому значению, а -1 найдет наименьшее значение, меньшее или равное искомому значению

Обратите внимание, что если вы не используете 0, 1 или -1, формула будет использовать 1, by

Теперь, если вы не хотите жестко указывать название месяца, вы можете заменить его номером ячейки. Таким образом, мы можем заменить «Jan» в формуле, упомянутой выше, на F3 или A2, чтобы получить тот же результат.

Формула: =ИНДЕКС(столбец данных, которые вы хотите вернуть, MATCH (общая точка данных, которую вы пытаетесь сопоставить, столбец другого источника данных, который имеет общую точку данных, 0))

Заключение

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

Чтобы получить больше пользы, воспользуйтесь WPS Office, мощным пакетом, который позволяет легко редактировать документы Word, электронные таблицы Excel и презентации PowerPoint. С его дружественным интерфейсом и широкой совместимостью с ПК, мобильными устройствами, Windows, Mac, Android и iOS,  WPS Office предоставляет легкий и приятный опыт работы. Скачайте программу сейчас бесплатно и откройте для себя мир бесшовного редактирования документов, улучшая вашу продуктивность и оптимизируя рабочие процессы.