Excel двойное неравенство счетесли

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

​Смотрите также​Диапазон условия 1 –​ может быть исключен,​ выражения, единица в​ любое количество любых​ число меньше какого-то​ введены разные значения.​Все версии Excel, начиная​=COUNTIFS(A1:A5,»green»,B1:B5,»>9″)​ одновременно подсчитать партии​=СУММПРОИЗВ((B2:B13>=D2)*(B2:B13​ в другом диапазоне,​ то некоторые образцы​ чисел в столбце,​

​ фильтра, с той же​

​ Фрукты значится Персики​​ значение, то настройка​

​Для подсчета ЧИСЛОвых значений,​​ ячейки, которые нужно​ и тогда команда​ конечном счете получится​ символов, вопросительный знак​

​ числа и не​​ Среди них нет​ с 2007, могут​Для суммирования диапазона ячеек​ товара Яблоки и​формула массива =СУММ((B2:B13<>=D2))​ используйте функцию СУММЕСЛИМН.​ данных и формулы,​ если они удовлетворяют​ табличкой условий, что​ ИЛИ строки с​​ функции СЧЕТЕСЛИ() для​​ Дат и Текстовых​

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

​ оценить на основании​ будет работать только​​ только если оба​​ — один любой​

​ критерию, существует простая​​Условие 1 – определяет​ критерию.​ стоимости продаж осталось​ чтобы найти все​ЕСЛИ(E1>90 ЕСЛИ(E1>90&E1​ ячейки.​ результата этой формулы.​(SUM).​В качестве критерия в​ наличия отдельной таблички​ строк, поля которых​

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

​ демонстрационными данными непосредственно​ первый​В случае необходимости, можно​ (ящиков). Т.е. Партии​ примера (см. файл​

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

​ и эффективная функция​ ячейки, которые функция​​ умножить на значения​ продажи у менеджеров​Barracuda​В любом месте таблицы​ Обязательным является только​=СУММ(A1:A5)​

​ ячейке​ с критериями.​ удовлетворяют двум критериям​ в этой книге​аргумент​ задавать другие условия​

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

​ Персиков отбираются в​​ примера Лист Даты). ​​ СЧЁТЕСЛИ(), английская версия​ выделит из первого​Рассмотрим простейший пример, который​ получившегося столбца и​ с фамилией из​: =ЕСЛИ(И(E1>90;E2​ можно ввести функцию.​ первый из них.​=SUM(A1:A5)​D2​Рассмотрим задачу, когда 2​

​ и больше.​

​ веб-приложения Excel Online.​в этой формуле​ отбора. Например, подсчитать строки,​ любом случае, а​Если необходимо подсчитать количество​ COUNTIF(). Подсчитаем значения​ диапазона условия.​​ наглядно продемонстрирует, как​​ просуммировать отобранное в​ пяти букв, можно​

​Guest​ Для определения заполненных​ При этом есть​

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

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

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

​Диапазон условия 2 –​ использовать функцию СУММЕСЛИ​ зеленой ячейке:​ использовать критерий​: Спасибо уже решил​​ ячеек в диапазоне​​ разница в том,​ на основе одного​ знак * заменяет​ форме дат и​ возьмем таблицу с​ формулы или добавляйте​ данных, функция требует​ столбце Фрукты значится Персики ​

​ партии любых фруктов​ месяцу, то нужно​ случае одного критерия,​ ячейки, которые следует​ и насколько удобной​Если вы раньше не​?????​​ по другому через​​ А1:С5 нужно прописать​ как учитывается значение,​ критерия (например, больше​ последовательность любых символов идущих правее.​ применяются к значениям​ двумя столбцами: текстовым​ свои собственные, чтобы​

excel2.ru

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

​ в качестве входных​ИЛИ​ с остатком на складе​ создать дополнительный столбец​ а также покажем​ оценить на основании​ она может оказаться​ сталкивались с такой​. А чтобы найти все​ двойное условие, но​ такую формулу:​ если оно хранится​ 9), используйте функцию​Хотя формула с функцией СЧЁТЕСЛИМН()​ одного из столбцов.​ «Фрукты» и числовым​ увидеть, как мгновенно​ данных.​ строки с остатком на​ не менее 57​ для вычисления месяца,​ как ее использовать​

​ второго критерия.​ при решении определенных​ замечательной возможностью Excel​ продажи менеджеров, у​ ваш пример имхо​Введение её через Enter​

Задача

​ в ячейке или​СУММЕСЛИ​  по сравнению с​​Найдем число партий товара​​ «Количество на складе»​ изменятся результаты.​После этого необходимо найти​ складе​ (ящиков).​ затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)​​ для подсчета неповторяющихся​​Условие 2 – определяет​ задач.​ как формулы массива,​ которых фамилия начинается​ короче.​ даст результат 8.​ введено в числе​(SUMIF). В данном​ предыдущей задачей не​ с Датой поступления на​ (См. файл примера).​Скопируйте все ячейки из​ данные, которые соответствуют​не более​В качестве исходной таблицы​

​Обычно, в качестве аргумента​ значений и вычисления​ ячейки, которые функция​

​Имеем таблицу, в которой​ то советую почитать​

1. ​ на букву «П»,​
2. ​Пафнутий​
3. ​ Именно столько заполненных​

​ аргументов.​ случае для проверки​ изменится, часть альтернативных​ склад не ранее 25.10.2012 и не​СУММЕСЛИМН(​

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

• ​57 (ящиков).​ возьмем таблицу с​критерий​ ранга.​ выделит из второго​ указаны фамилии сотрудников,​ предварительно про них​
• ​ а заканчивается на​
• ​: помогите пожалуйста решить​ ячеек есть в​Числа, даты и время​ условия и суммирования​ решений работать не​ позднее 24.12.2012 (строка таблицы соответствует​диапазон_условия1; условие1; [диапазон_условия2; условие2]…​ и вставьте их​ введите свой первый​Это потребует незначительного изменения​
• ​ двумя столбцами: текстовым​у функции СЧЁТЕСЛИ()​Синтаксис​ диапазона условия.​ их пол и​ много хорошего здесь.​ «В» — критерий​​ проблему. как стандартными​​ обозначенной области.​

​ всегда учитываются функцией​​ используется один столбец,​ будет (подробнее см.​ критерию, когда ее​)​ в ячейку A1​

​ условие, указав для​ формул (условие «>=»&E2​ «Фрукты» и числовым​ указывают только одно​СЧЁТЕСЛИдиапазонкритерий​​И так далее. В​​ зарплата, начисленная за​ Ну, а в​​П*В​​ средствами Excel задать​

​Чтобы узнать количество пустых​ СЧЁТ.​ поэтому в функции​

​ здесь).​ поле Дата поступления удовлетворяет обоим​Диапазон_условия1​ нового листа Excel.​ функции расположение данных​ нужно переписать как «​

​ «Количество на складе»​​ значение. Например, =СЧЁТЕСЛИ(H2:H11;I2).​)​ зависимости от количества​ январь-месяц. Если нам​ нашем случае задача​. Строчные и прописные​

excel2.ru

Суммирование значений с учетом нескольких условий

​ для занчения больше​​ клеток, нужно ввести​Текст и ошибки никогда​ достаточно заполнить всего​Рассмотрим задачу, когда 1​ критериям одновременно). ​. Первый диапазон, в котором​ Вы можете отрегулировать​ (a2: a11), а​На листе Пример2 файла​ (См. файл примера).​ Если в качестве​Диапазон​ критериев, число аргументов​ нужно просто посчитать​ решается одной формулой:​ буквы не различаются.​ чем 7 условий.​ функцию СЧИТАТЬПУСТОТЫ:​ не учитываются.​ два аргумента:​ текстовый критерий применяются​

​В качестве исходной таблицы​ необходимо проверить соответствие​ ширину столбцов, чтобы​ также какие условием​ примера приведено универсальное​Подсчитаем строки, в которых​ критерия указать ссылку​    — диапазон, в котором​ может увеличиваться в​

​ общее количество денег,​=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)​Диапазон_суммирования​ Как обойти это​Введение формулы покажет результат​​Логические значения и текстовое​​=СУММЕСЛИ(B1:B5;»>9″)​​ к значениям текстового​​ возьмем таблицу с​ заданному условию1;​ формулы лучше отображались.​

​ является — наилучшим​ решение, которое позволяет​

​ в столбце Фрукты​

​ на целый диапазон​

​ нужно подсчитать ячейки,​ арифметической прогрессии с​

​ которые требуется выдать​После ввода этой формулы​- это те​ ограничение без VBA​ 7. Это количество​ представление числа не​=SUMIF(B1:B5,»>9″)​

​ столбца, а 2​

​ двумя столбцами: текстовым​Условие1​Регион​ образом «Южный». Обратите​ не модифицировать формулы,​ значится Персики​ ячеек с критериями,​​ содержащие числа, текст​​ шагом 2. Т.е.​ работникам, мы используем​ необходимо нажать не​ ячейки, значения которых​ ?​

​ пустых ячеек в​ учитывается, если оно​Чтобы суммировать значения ячеек​ других (числовых) — к​ «Фрукты» и «Дата​. Условие в форме числа,​Продавец​ внимание, запятые между​ а лишь менять​ИЛИ​ то функция вернет​ или даты.​

​ 5, 7, 9…​

​ функцию СУММ, указав​ Enter, как обычно,​ мы хотим сложить,​

​Guest​ таблице.​ хранится в ячейке.​ на основе одного​ значениям столбца с​ поступления» (см. файл примера, лист 2Даты).​ выражения, ссылки на​Что следует ввести​ отдельные аргументы:​ знаки сравнения.​​строки с остатком​​ массив. В файле​Критерий​Предположим, нам нужно подсчитать​

​ диапазоном все заработные​

​ а​ т.е. нашем случае -​: Промежуточными строками или​Очень часто используется такая​ Если эти же​ критерия (например, «green»),​​ числами (см. файл примера, лист 1текст​​Так как даты хранятся​ ячейку или текста,​Продажи​= SUMIFS(D2:D11,A2:A11,»South»,​

​Примечание​ на складе не​ примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18)​    — критерий в форме​ сумму заработных плат​ платы.​Ctrl + Shift +​ стоимости заказов.​ столбцами, которые можно​ разновидность функции «СЧЁТ».​ элементы ввести в​ также используйте функцию​ 2числовых).​ в EXCEL в​​ которые определяют, какие​​Южный​

​Кавычки вокруг слова «Южный»​: подсчет значений с​ менее 57 (ящиков). Отбираются​ возвращает массив {3:2:5}.​ числа, выражения, ссылки​ за январь всех​​Но как быть, если​​ Enter​Если условий больше одного​ скрыть.​ С помощью заданной​​ формулу в качестве​​СУММЕСЛИ​

​Найдем число партий товара​​ числовом формате, то​​ ячейки требуется учитывать.​Орехов​

Попробуйте попрактиковаться

​ укажите, что это​ множественными критерями также​ только те строки,​ ​ на ячейку или​

​ продавцов-женщин. У нас​ нам нужно быстро​- тогда Excel​ (например, нужно найти​Barracuda​ формулы можно узнать​ аргумента, они будут​(SUMIF). В данном​ Яблоки с Количеством ящиков​

​ формулы для подсчета​ Например, условие может​Напитки​ текстовые данные.​ рассмотрен в статьях Подсчет​ у которых в​Для ввода формулы выделите​ текста, который определяет,​

​ текстовый критерий применяются​​Диапазон_условия2, условие2… ​

• ​Молочные продукты​ C11), содержащий слово​

• ​Примечание:​ Количество ящиков на​ критерии. В Строке​ следующим образом: 32,​Значит, будем применять команду​Прописываем аргументы.​

support.office.com

Функция СЧЁТЕСЛИМН() в MS EXCEL

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

​ нужно заполнить три​ поле Фрукт совпадает с критерием Яблоки,​ к значениям текстового​   Необязательные аргументы. Дополнительные диапазоны​3338​ «Мясо», плюс само​

Синтаксис функции

​Мы стараемся как​​ складе значение >=57 (как​​ формул введите формулу​

• ​ «32», «>32», «яблоки»​​ СУММЕСЛИМН.​Диапазоном в данном случае​ надо. Легко сообразить,​
• ​ не умеет проверять​​vikttur​Диапазон. Табличная область, в​Пример 1. В таблицу​ аргумента, последний –​ и когда другое​ столбца, а другой​ и условия для​Восточный​ слово (заключено в​
• ​ можно оперативнее обеспечивать​​ бы совершаетcя 2​ и нажмите​ или​Прописываем аргументы.​ будет являться список​

​ что этот способ​ больше одного критерия.​: С помощью дополнительных​ которой будут искаться​ введены некоторые значения.​ это диапазон для​ поле Количество ящиков на​ (числовой) — к значениям​

​ них. Разрешается использовать​Песоцкий​ кавычки) таким образом,​ вас актуальными справочными​ прохода по таблице: сначала​CTRL+SHIFT+ENTER​B4​диапазон суммирования – ячейки​ всех должностей сотрудников,​ (как и предыдущий)​ Поэтому начиная с​ ячеек.​

Задача1 (2 числовых критерия)

​ определённые элементы.​ Среди них есть​ суммирования.​ складе одновременно >=10 и​ столбца с числами (см. файл​ до 127 пар​

​Напитки​ Excel может соответствовать​ материалами на вашем​ критерий применяется только​, т.е. введите ее​.​ с зарплатой;​ потому что нам​ легко масштабируется на​

​ версии Excel 2007​Guest​Критерий. Признак, который разыскивается​

​ текст, числовые выражения​=СУММЕСЛИ(A1:A5;»green»;B1:B5)​

​Для наглядности, строки в​ примера, лист 1текст 1числовой).​ диапазонов и условий.​5122​ его. Завершить формулу​​ языке. Эта страница​​ по полю Фрукты,​ как формулу массива.​Данные будем брать из​диапазон условия 1 –​

​ нужно будет определить​ три, четыре и​

• ​ в набор функций​
• ​: С помощью дополнительных​
• ​ в заданной области.​
• ​ и число, заключённое​=SUMIF(A1:A5,»green»,B1:B5)​ таблице, удовлетворяющие критериям,​

Задача2 (2 критерия в формате Дат)

​Найдем число партий товара​Каждый дополнительный диапазон должен​Северный​ с закрывающую скобку​ переведена автоматически, поэтому​

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

​ ячеек.{/post}{/quote}​Синтаксис выглядит так:​ в кавычки «22».​Для суммирования значений ячеек​ выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2;$B2​

​ Яблоки с Количеством ящиков​ состоять из такого​Песоцкий​)​ ее текст может​ Количество ящиков на​

Задача3 (1 текстовый критерий, другой числовой)

​ используется в статье​A15:A25​ должности сотрудника;​ Поэтому проставляем E2:E14.​ каких-либо ограничений.​СУММЕСЛИМН (SUMIFS)​vikttur​

​Функция может показать количество​ Именно его и​ на основе нескольких​Формулу запишем в следующем​ на складе не менее 10 (строка​ же количества строк​Молочные продукты​и нажмите клавишу​ содержать неточности и​

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

​условия 1 – продавец;​Критерий выбора в нашем​

​В категории​- в ней​: Функцией ВЫБОР()​ ячеек с заданным​ считает программа Excel​​ критериев (например, «blue»​​ виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2;B2:B13;»​ таблицы соответствует критерию,​ и столбцов, что​6239​

​ ВВОД. Результат, снова​ грамматические ошибки. Для​

• ​ которых оба поля​
• ​Возможность задать в качестве​
• ​Критерий​
• ​диапазон условия 2 –​ случае – продавец.​Базы данных (Database)​

Задача4 (1 текстовый критерий с подстановочным знаком, другой числовой)

​ количество условий проверки​Имеем таблицу по продажам,​ текстом. Для этого​ текстовым значением в​ и «green»), используйте​Альтернативными решениями задачи являются​ когда ее поле​ и аргумент диапазон_условия1. Эти​Южный​

​ нажмите — 14,719.​​ нас важно, чтобы​​ удовлетворяют критериям во​ критерия несколько значений​Формула​

​ ячейки с указанием​ Заключаем слово в​ можно найти функцию​ увеличено аж до​ например, следующего вида:​ аргумент заключается в​ ячейке.​ функцию​ следующие формулы:​ Фрукт совпадает с​

​ диапазоны могут не​Маринова​​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​​ эта статья была​ второй проход не​ открывает дополнительные возможности​

​Результат​ пола сотрудника;​ кавычки и ставим​БДСУММ (DSUM)​ 127! Функция находится​Задача​ кавычки. При этом​

Задача5 (1 текстовый критерий, 2 числовых)

​Пропишем формулу, она будет​СУММЕСЛИМН​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13​ критерием Яблоки, и​ находиться рядом друг​Сельхозпродукты​По мере ввода функции​ вам полезна. Просим​

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

​(SUMIFS). Первый аргумент​формула массива =СУММ((A2:A13=D2)*(B2:B13<>=E2))​ когда другое поле Количество​

​ с другом.​8677​

​ СУММЕСЛИМН в Microsoft​ вас уделить пару​

• ​ было задвоения)).​
• ​В файле примера на​
• ​>=10​
• ​ (ж).​Диапазон суммирования – это​ решить нашу задачу.​

​ категории​ которые менеджер Григорьев реализовал​ регистр. В синтаксисе​

excel2.ru

Функции СЧЁТ и СУММ в Excel

• ​ ячеек, и будет​
• ​ – это диапазон​
• ​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2)*(B2:B13​
• ​ ящиков на складе >=10). ​
• ​В условиях можно использовать​
• ​Южный​

​ Excel, если вы​ секунд и сообщить,​Для наглядности, строки в​ листе Специальное применение​=СЧЁТЕСЛИ($A$2:$A$12; «>=10»)​Итог: все продавцы-женщины в​ заработные платы, потому​ Нюанс состоит в​

СЧЁТ

​Математические​ для магазина «Копейка».​ формулы не может​​ выглядеть таким образом:​​ для суммирования.​

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

СЧЕТЕСЛИ

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

​4​
​ январе получили в​

СЧЁТЕСЛИМН

​ что нам нужно​ том, что для​и работает похожим​Если бы в нашей​ быть пробелов.​​После введения формулы нажатием​​=СУММЕСЛИМН(C1:C5;A1:A5;»blue»;B1:B5;»green»)​

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

СУММ

​ знак (?) и​Мясо​​ справки готов под​​ вам, с помощью​

​ выделяются Условным форматированием с правилом =ИЛИ($A2=$D$2;$B2>=$E$2)​
​ помощью функции СЧЁТЕСЛИ() вычислить количество​

СУММЕСЛИ

​Подсчитывает количество ячеек, содержащих​ сумме 51100 рублей.​ узнать сумму зарплат​ работы этой функции​​ образом, но имеет​​ задаче было только​Оба аргумента являются обязательными​ клавиши Enter, появится​=SUMIFS(C1:C5,A1:A5,»blue»,B1:B5,»green»)​ критериями.​ выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​ звездочку (*). Вопросительный​

​450​
​ рукой. После ввода​

​ кнопок внизу страницы.​Для удобства создадим 3​ повторов каждого значения​ числа равных или​​Функции СУММЕСЛИ и СУММЕСЛИМН​​ всех продавцов. Поэтому​ необходимо создать на​ больше аргументов:​ одно условие (все​ для указания. Для​ число 4. Именно​Примечание:​О подсчете с множественными​Формулу запишем в следующем​

​ знак соответствует любому одиночному​
​Южный​

СУММЕСЛИМН

​= СУММЕСЛИМН (​ Для удобства также​ динамических диапазона:​ в списке.​ более 10. Критерий​​ хороши тем, что​​ F2:F14.​ листе специальный диапазон​При помощи полосы прокрутки​

​ заказы Петрова или​
​ наглядности стоит рассмотреть​

​ столько чисел введено​​Аналогичным образом можно​ критериями можно почитать​​ виде: =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2)​​ символу; звездочка —​​Егоров​​, автозавершение формул появится​ приводим ссылку на​Количество: =СМЕЩ(пример1!$B$2;;;СЧЁТЗ(пример1!$A$2:$A$15))​Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел {1:4:4:4:4:1:3:3:3},​

​ указан в формуле​ они автоматически подстраиваются​
​Получилось 92900. Т.е. функция​
​ критериев — ячейки, содержащие​

​ в правой части​

office-guru.ru

Примеры работы функций СЧЁТ, СЧИТАТЬПУСТОТЫ и СЧЁТЕСЛИ в Excel

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

Где используется и как работает функция СЧЁТ?

​ любой последовательности символов.​Мясо​ под формулы, в​ оригинал (на английском​Фрукты: =СМЕЩ(пример1!$A$2;;;СЧЁТЗ(пример1!$A$2:$A$15))​ который говорит о​(ячейка С3)​ под изменение условий.​ автоматически проработала список​

​ условия отбора — и​

• ​ окна можно задать​ «Копейку», например), то​Пример 3. Есть ведомость​
• ​ помощи цифр, которые​СРЗНАЧЕСЛИ​
• ​СЧЁТ​

​ диапазон, к которому​ Если нужно найти​

​7673​

​ списке аргументов в​ языке) .​Таблица: =СМЕЩ(пример1!$A$1;;;СЧЁТЗ(пример1!$A$1:$A$15);2)​ том, что значение​=СЧЁТЕСЛИ( $A$2:$A$12;C3)​ Т.е. мы можем​ должностей, выбрала из​ указать затем этот​ и третью пару​ задача решалась бы​ с фамилиями студентов​ не заключены в​(AVERAGEIF) и​СЧЁТЕСЛИ​

1. ​ применяется первый критерий​ сам вопросительный знак​Восточный​
2. ​ их правильном порядке.​Допустим, вам нужно суммировать​
3. ​Это позволит в дальнейшем​ 1 из списка​8​ изменить данные в​ них только продавцов​ диапазон функции как​ (​ достаточно легко при​ и оценками за​

​ кавычки.​СРЗНАЧЕСЛИМН​СЧЁТЕСЛИМН​

​ (>=10 или «>=»&D2) это​

Пример использования функции СЧЁТ в Excel

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

​Диапазон_условия3​ помощи встроенной функции​ экзамен. В таблице​Теперь введём в ячейку​

​(AVERAGEIFS), чтобы рассчитать​СУММ​ диапазон ​ перед ними знак​Сельхозпродукты​ использования функции автозавершения​ чем одного условия,​ исходную таблицу и​

​А6:А15​ числа равных или​ будут изменяться вместе​ зарплаты.​=БДСУММ(A1:D26;D1;F1:G2)​-​ Excel​

​ 2 столбца и​ ниже D2, другую​ среднее значение ячеек​СУММЕСЛИ​B2:B13​

​ тильды (~).​664​

​ формул и списка​ например сумму продаж​ не переписывать формулы​- единственное, также​ меньших 10. Критерий​ с ними. Например,​Аналогично можно подсчитать зарплаты​Суммировать в программе Excel​Условие3​

Пример использования функций СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

​СУММЕСЛИ (SUMIF)​ 10 ячеек. Нужно​ функцию. В ней​ на основе одного​СУММЕСЛИМН​. Первый и второй​Рассмотрим задачу, когда 2​Северный​ аргументов, то в​ товара в определенной​

​ для подсчета значений.​ в диапазоне 4​ указан через ссылку​ при подсчете заработных​ всех менеджеров, продавцов-кассиров​ умеет, наверное, каждый.​

​), и четвертую, и​ из категории​ определить, какое количество​ все табличные элементы​ или нескольких критериев.​Самые часто используемые функции​

​ диапазон в данном​ числовых критерия применяются​Егоров​ нашем примере диапазон_суммирования​ области. Это хороший​

​Подсчет можно реализовать множеством​ значения 2, одно​>=​

​ плат оказалось, что​ и охранников. Когда​ Но с усовершенствованной​ т.д. — при​

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

​Математические (Math&Trig)​ студентов получили отличную​ будут прописаны в​Урок подготовлен для Вас​ в Excel –​ случае совпадают, т.к.​ к значениям одного​Сельхозпродукты​ — это диапазон​

1. ​ обращения для использования​ формул, приведем несколько:​ значение 3, три​
2. ​(ячейка С4)​ мы забыли учесть​

​ табличка небольшая, кажется,​

​ версией команды СУММ,​ необходимости.​. Выделяем пустую ячейку​ оценку 5 (по​ виде аргументов. Она​ командой сайта office-guru.ru​ это функции, которые​ 2-й критерий  (B2:B13.​ из столбцов (см.​

​1500​ D2:D11, представляющий собой​ функции СУММЕСЛИМН в​Формула =СЧЁТЕСЛИ(Фрукты;D2)+СЧЁТЕСЛИ(Количество;»>=»&E2)-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2) с помощью 2-х​

​ значения 4. Это​11​ одну сотрудницу, которая​ что все можно​ которая называется СУММЕСЛИ,​Если же у вас​ для результата, жмем​ пятибалльной системе оценивания),​ будет выглядеть так:​Источник: http://www.excel-easy.com/functions/count-sum-functions.html​ подсчитывают и складывают.​Альтернативными решениями задачи являются​ файл примера, лист​

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

​ существенно расширяются возможности​ пока еще старая​ кнопку​

• ​ а какое 4,​
• ​=СЧЁТ(12; мост; кг; крыло;​Перевела: Ольга Гелих​
• ​ Подсчитывать и складывать​
• ​ следующие формулы:​

• ​ 2числовых).​Маринова​ которые вы хотите​Просмотрите в этом примере,​

  exceltable.com

Двойное условие ЕСЛИ?

​ удовлетворяющие каждому из​​ неповторяющихся значений формулой​
​=СЧЁТЕСЛИ($A$2:$A$12; C4&C5)​ можем добавить еще​ но при работе​ данной операции.​ версия Excel 2003,​
​fx​
​ потом 3 и​

​ 33; 45678; «22»;​​Автор: Антон Андронов​

​ (суммировать) можно на​​=СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2))​Найдем число партий товара​Мясо​ просуммировать; диапазон_условия1 —​ в котором у​

​ критериев, затем вычитается​​ =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)). ​3​ одну строчку через​ со списками, в​По названию команды можно​ но задачу с​в строке формул,​ 2.​

​ сила; ампер; 16.02.1999;​​Количества чисел в таблице​ основе одного или​формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​

​ с Количеством ящиков на​​6596​ это диапазон A2.A11,​

​ нас есть двумя​​ количество строк удовлетворяющих обоим​
​Формула =СЧЁТЕСЛИ(A6:A14;»А6:А15. В этом можно​

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

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

​Для определения количества отличников​​ 14.07.1975; стена; потолок;​

planetaexcel.ru

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

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

​ Excel можно быстро​ нескольких критериев.​

​формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13))​​ складе не менее​Формула​ представляющий собой столбец​

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

​ условиями: необходимо узнать​ критериям одновременно (функция​ убедиться, выделив формулу​ числа равных или​ и команду ВСТАВИТЬ.​ сотен позиций, целесообразно​ не просто считает​ нужно, то придется​СУММЕСЛИ​ нужно провести анализ​​ 21)​​ выяснить, используя функцию​​Для подсчета количества ячеек,​​=БСЧЁТА(A1:B13;A1;D14:E15) или =БСЧЁТ(A1:B13;B1;D14:E15), которые требуют наличия​ 10 и не более​Описание​​ с данными, в​​ сумму продаж​ СЧЁТЕСЛИМН()).​​ в Строке формул​​ более 11. Критерий​

​У нас появилась дополнительная​​ использовать СУММЕСЛИ.​​ сумму, но еще​

• ​ извращаться — см.​​в списке:​ содержимого ячеек второго​Введя формулу, мы получим​​ «Счёт». Игнорируя текстовый​​ которые содержат числа,​ отдельной таблички с​ 50 (строка таблицы соответствует​Результат​
• ​ котором находится​​мяса​Вместо 2-х функций СЧЁТЕСЛИ() можно использовать формулу =СУММПРОИЗВ((Фрукты=D2)+(Количество>=E2))-СЧЁТЕСЛИМН(Фрукты;D2;Количество;»>=»&E2)​ и нажав клавишу​ указан через ссылку​ строчка. Сразу обращаем​Если к стандартной записи​ и подчиняется каким-либо​ следующие способы.​Жмем​ столбика. В отдельной​ значение 5.​ формат, она учитывает​ используйте функцию​ критериями.​ критерию, когда ее​’ = СУММЕСЛИМН (a2:​условие1​в​​Формула =БСЧЁТ(Таблица;B1;D13:E15) требует предварительного создания​​ F9. Значения совпадут​ и параметр​ внимание, что диапазон​ команды СУММЕСЛИ в​ логическим условиям.​Добавим к нашей таблице​​ОК​​ табличке нужно использовать​Разные результаты функции объясняются​
• ​ только числовые значения.​​СЧЁТ​Рассмотрим задачу, когда 1​ поле Количество ящиков​ a11; D2: D11​— “Южный”.​

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

​южном​ таблички с условиями.​ с вычисленным рангом​Примечание​ условий и суммирования​​ конце добавляются еще​​Функция СУММЕСЛИ позволяет суммировать​ еще один столбец,​ и вводим ее аргументы:​ простую функцию подсчета​ тем, что одно​Часто, для анализа табличных​(COUNT).​​ текстовый критерий с​​ на складе удовлетворяет обоим​«Южно» C2: C11,​По мере того, как​районе (из столбца​ Заголовки этой таблицы​ в столбце​​. О подсчете значений,​​ автоматически расширился до​ две буквы –​ ячейки, которые удовлетворяют​

​ который будет служить​Диапазон​ количества числовых значений​ число («22») воспринимается​ данных не нужно​​=СЧЁТ(A1:A5)​​ подстановочным знаком применяются​​ критериям одновременно). ​​ «Мясо»)​ вы вводите формулу,​ A) (из столбца​

​ должны в точности​В​ удовлетворяющих нескольким критериям​ 15 строки.​ МН (СУММЕСЛИМН), значит,​ определенному критерию (заданному​ своеобразным индикатором: если​- это те​

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

​ с условием СЧЁТЕСЛИ:​ программой, как текст,​ производить никаких математических​=COUNT(A1:A5)​ к значениям текстового​Для наглядности, строки в​Суммирует продаж мяса в​ в автозавершении формулы​ C).​ совпадать с заголовками​(с помощью функции​ читайте в статье​Копируем данные сотрудника и​

​ подразумевается функция с​

​ условию). Аргументы команды​ заказ был в​ ячейки, которые мы​После нажатия на клавиатуре​ если оно находится​ операций с числами.​Для подсчета ячеек по​ столбца, а другой​ таблице, удовлетворяющие критериям,​Столбец C в​ появятся остальные аргументы​Вот формулу можно использовать​ исходной таблицы. Размещение​ РАНГ()). Этот подход​ Подсчет значений со​ вставляем их в​

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

​ несколькими условиями. Она​ следующие:​ «Копейку» и от​ проверяем на выполнение​ Enter будет получен​ в ячейке, и​ Достаточно уточнить количество​ одному критерию (например,​ (числовой) — к значениям​ выделяются Условным форматированием с правилом =И($B2>=$D$2;$B2​

​ южном​

​ (здесь они не​ для acomplish это:​ условий в разных​ применен в статьях Динамическая​​ множественными критериями. О​ общий перечень. Суммы​​ применяется в случае,​Диапазон – ячейки, которые​ Григорьева, то в​Критерия​ результат:​ как число, если​ показателей. Именно для​ больше 9), используйте​ столбца с числами (см. файл​Формулу запишем в следующем​область в столбце​ показаны);​=SUMIFS(D2:D11,a2:a11,»South»,C2:C11,»Meat»)​

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

​ строках соответствует Условию​​ сортировка таблицы в​​ подсчете чисел с​​ в итоговых ячейках​​ когда нужно задать​ следует оценить на​ ячейке этого столбца​. В нашем случае​5 отличников;​ оно прописано в​ такого случая пригодится​ функцию​ примера, лист 1текст (с​ виде: =СЧЁТЕСЛИМН(B2:B13;»>=»&D2;B2:B13;»​ A (результат —​диапазон_условия2​

​Результатом является значение 14,719.​

planetaexcel.ru

Примеры использования функции СУММЕСЛИ в Excel с несколькими условиями

​ ИЛИ (см. статью​ MS EXCEL и Отбор уникальных​ более чем 15​ изменились. Функции среагировали​ не один критерий.​ основании критерия (заданного​ будет значение 1,​

​ — это диапазон​3 студента с оценкой​ числе аргументов функции.​ эта простая функция​СЧЕТЕСЛИ​ подстанов) 1числовой).​

СУММЕСЛИ и ее синтаксис

​В формуле предполагается, что​ 14 719).​— это диапазон​Рассмотрим изучить все части​ Функция БСЧЁТ()).​

1. ​ значений с сортировкой​ значащих цифр читайте​ на появление в​Аргументов у СУММЕСЛИМН может​
2. ​ условия).​ иначе — 0.​ с фамилиями менеджеров​ 4 балла;​
3. ​Вы сможете быстро определить​ СЧЁТ.​(COUNTIF).​Найдем число партий товара​

​ диапазон, к которому​= SUMIFS(D2:D11,A2:A11,​ C2:C11, представляющий собой​ формулы.​Также можно использовать формулу =БСЧЁТА(Таблица;A1;D13:E15)​ в MS EXCEL.​ статью Подсчет ТЕКСТовых значений​ диапазоне еще одного​

​ быть сколько угодно,​

Как работает функция СУММЕСЛИ в Excel?

​Критерий – определяет, какие​ Формула, которую надо​ продаж.​2 троечника;​ количество заполненных или​Аргументом функции может быть:​=СЧЁТЕСЛИ(A1:A5;»>9″)​

​начинающихся​ применяется первый критерий​«Южно» C2: C11,​ столбец с данными,​= СУММЕСЛИМН является формулой​ с теми же​Произведем подсчет строк таблицы,​ с единственным критерием​ продавца-женщины.​ но минимум –​ ячейки из диапазона​ ввести в этот​

​Критерий​ни одного двоечника.​ пустых клеток в​любые значения, скопированные и​=COUNTIF(A1:A5,»>9″)​со слова Яблоки и​

​ (>=10 или «>=»&D2)​

1. ​ «Мясо»)​ в котором находится​ арифметического. Он вычисляет​ условиями, но нужно​ значения которых удовлетворяют​ в MS EXCEL.​Аналогично можно не только​
2. ​ это 5.​ будут выбраны (записывается​ столбец очень простая:​- это то,​Так, всего за несколько​
3. ​ заданной области таблицы.​ поставленные на место​Чтобы подсчитать ячейки, основываясь​ с Количеством ящиков на​ это диапазон ​Примечания:​

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

​Диапазон суммирования. Если в​ в кавычках).​=(A2=»Копейка»)*(B2=»Григорьев»)​ что мы ищем​ секунд, можно получить​ Для выполнения этих​ аргумента;​ на нескольких критериях​ складе не менее 10. ​B2:B13​ ​

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

​— “Мясо”.​ в этом случае​ подсчета строк, он​ которые образуют Условие​ текстовых значений (см.​ удалять какие-либо строки​ СУММЕСЛИ он был​Диапазон суммирования – фактические​Логические равенства в скобках​ в предыдущем указанном​

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

​ данные по обширным​ операций используются функции​одна ячейка или целая​ (например, содержащие «green»​

1. ​В отличие от задачи​. Первый и второй​Дополнительные примеры можно найти​Если вы нажмете​ в столбце D.​ должен быть текстовым,​ ИЛИ. Например, в​ Подсчет ТЕКСТовых значений​
2. ​ (например, при увольнении​ в конце, то​ ячейки, которые необходимо​ дают значения ИСТИНА​
3. ​ диапазоне. Разрешается использовать​ и сложным таблицам.​ с именами «СЧЁТЗ»​ область таблицы;​
4. ​ и больше 9),​ 3, в исходной​ диапазон в данном​ в статьеФункция СУММЕСЛИМН.​
5. ​СУММЕСЛИМН​ Первым делом нужно​ т.е.​ таблице с перечнем​

​ с единственным критерием​ сотрудника), изменять значения​ здесь он стоит​ просуммировать, если они​ или ЛОЖЬ, что​ символы * (звездочка)​Сергей​

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

​ и «СЧИТАТЬПУСТОТЫ». Для​формулы и функции.​ применяйте функцию​ таблице присутствуют фрукты​ случае совпадают, т.к.​Если вы хотите подсчитать​

• ​в автозавершении формулы,​
• ​ указать расположение номера:​

​А​ Фруктов и их​

​ в MS EXCEL).​

• ​ (заменить «январь» на​ на первом месте.​
• ​ удовлетворяют критерию.​ для Excel равносильно​ и  ? (вопросительный​
• ​: Помогите с проблемкой:​
• ​ наглядности этой возможности​Синтаксис функции выглядит следующим​СЧЁТЕСЛИМН​
• ​ с более сложными​ 2-й критерий  (B2:B13.​

​ итоговое значение только​ откроется статья, справкой.​=СУММЕСЛИМН(D2:D11;​

СУММЕСЛИ в Excel с динамическим условием

​. ​ количеством на складе,​Так как любой дате​ «февраль» и подставить​ Он также означает​Получается, что у функции​ 1 и 0.​ знак) как маски​Например нужно проверить​ необходимо рассмотреть пример.​ образом:​(COUNTIFS).​ названиями: яблоки свежие,​Альтернативными решениями задачи являются​ для одного диапазона​Если вы хотите поэкспериментировать​Другими словами нужно ввести​Альтернативным решением​

​ отберем строки, в​ в MS EXCEL​ новые заработные платы)​ ячейки, которые необходимо​ всего 3 аргумента.​ Таким образом, поскольку​

​ или символы подстановки.​ не является ли​Пример 2. В таблицу​=СЧЁТ(аргумент1; аргумент2; …)​=СЧЁТЕСЛИМН(A1:A5;»green»;B1:B5;»>9″)​ персики сорт2. Чтобы​ следующие формулы:​ с учетом значения​

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

exceltable.com

​ Звездочка подменяет собой​

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

Эксель счет если двойное условие

Подсчет значений с множественными критериями (Часть 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 ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Экселе – одна из самых востребованных. Она возвращает результат (значение или другую формулу) в зависимости от условия.

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

Функция имеет следующий синтаксис.

ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])

лог_выражение – это проверяемое условие. Например, A2 30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. В этом вся суть функции ЕСЛИ. Протягивая расчет вниз, получаем результат по каждому товару.

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

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

В прогнозе запасов больше нет отрицательных значений, что в целом очень неплохо.

Формулы Excel ЕСЛИ также активно используют в формулах массивов. Здесь мы не будем далеко углубляться. Заинтересованным рекомендую прочитать статью о том, как рассчитать максимальное и минимальное значение по условию. Правда, расчет в той статье более не актуален, т.к. в Excel 2016 появились функции МИНЕСЛИ и МАКСЕСЛИ. Но для примера очень полезно ознакомиться – пригодится в другой ситуации.

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

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

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

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2 =1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

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

Эксель счет если двойное условие

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

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

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

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

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

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

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