Как использовать функции суммесли и суммеслимн в excel

Как задать простое логическое условие в Excel

​ проверке логических условий.​общие продажи​ оба ее аргумента​ значение ЛОЖЬ, функция​меняет значение своего​ таблица.​Пользователям часто приходится сравнить​ 3 – «удовлетворительно».​Обратите внимание: оператор ЕСЛИ​Разберем синтаксис функции:​:​​ кругозора спасибО)))​​ кругозора))))​​Описание​​И()​ ЛОЖЬ.​ равны, или ЛОЖЬ​ Например, это функции​

• ​НЕ​
• ​ должны быть истинными,​НЕ​
• ​ аргумента на обратное.​

Операторы сравнения в Excel

​Чтобы вбить в формулу​ две таблицы в​ Оператор ЕСЛИ проверяет​ должен проверить не​Логическое_выражение – ЧТО оператор​Гетманский​Владимир​

Как задать условие в Excel

​Шит​Результат​или​=A1<>B1​ в противном случае.​ ЕСЛИ, СЧЕТЕСЛИ, СУММЕСЛИ​

​меньше целевых​​ поэтому в данном​возвращает значение ИСТИНА;​Обычно функция​ диапазон, просто выделяем​ Excel на совпадения.​ 2 условия: равенство​ цифровой тип данных,​ проверяет (текстовые либо​,​: =ЕСЛИ(ИЛИ(И(A1=5;A2=5);A1=1);1;»»)​: Чуть не забыл​=ЗНАК(10)​ИЛИ()​

​— Формула вернет​​ Задавая такое условие,​ и т.д. Также​И​ случае она возвращает​ если он имеет​НЕ​ его первую ячейку​ Примеры из «жизни»:​ значения в ячейке​ а текстовый. Поэтому​

​ числовые данные ячейки).​И​Михалыч​ заранее Спасибо)))​Знак положительного числа.​.​ ИСТИНА, если значения​ можно сравнивать текстовые​ логические условия можно​число договоров​

​ значение ЛОЖЬ.​ значение ИСТИНА, функция​используется для расширения​ и последнюю. «=​ сопоставить цены на​ 5 и 4.​ мы прописали в​

​ строки без учета​ задавать в обычных​​НЕ​​=ЕСЛИ(ИЛИ(НЕ(A350)); A3; «Значение вне​​НЕ​​ возможностей других функций,​ 0» означает команду​ товар в разные​В этом примере мы​ формуле В2= «зач.».​ в ячейке, когда​ а не оператор​ Как записать функцию​: =ЕСЛИ(И(A1=5;А2=5);1;»не имеет никакого​=ЗНАК(4-4)​В этой статье описаны​ B1 не равны.​ регистра. К примеру,​

​ формулах, если необходимо​меньше целевого, общие​ интервала»)​возвращает значение ЛОЖЬ.​ выполняющих логическую проверку.​ поиска точных (а​ привозы, сравнить балансы​ добавили третье условие,​ В кавычки берем,​ текст или число​​ Код =ЕСЛИ(F4=10);812;813))​​ ЕСЛИ с двумя​​ значения»)​​Знак результата вычитания числа​

​ синтаксис формулы и​

office-guru.ru>

ВПР и СУММЕСЛИ – находим и суммируем значения, удовлетворяющие определённому критерию

Функция СУММЕСЛИ
(SUMIF) в Excel похожа на СУММ
(SUM), которую мы только что разбирали, поскольку она тоже суммирует значения. Разница лишь в том, что СУММЕСЛИ
суммирует только те значения, которые удовлетворяют заданному Вами критерию. Например, простейшая формула с СУММЕСЛИ
:

SUMIF(A2:A10,»>10″)
=СУММЕСЛИ(A2:A10;»>10″)

– суммирует все значения ячеек в диапазоне A2:A10
, которые больше 10
.

Очень просто, правда? А теперь давайте рассмотрим немного более сложный пример. Предположим, что у нас есть таблица, в которой перечислены имена продавцов и их номера ID
(Lookup table). Кроме этого, есть ещё одна таблица, в которой те же ID
связаны с данными о продажах (Main table). Наша задача – найти сумму продаж для заданного продавца. Здесь есть 2 отягчающих обстоятельства:

• Основная таблица (Main table) содержит множество записей для одного ID
  в случайном порядке.
• Вы не можете добавить столбец с именами продавцов к основной таблице.

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

Перед тем, как мы начнём, позвольте напомнить Вам синтаксис функции СУММЕСЛИ
(SUMIF):

SUMIF(range,criteria,)
СУММЕСЛИ(диапазон;критерий;)

• range
  (диапазон) – аргумент говорит сам за себя. Это просто диапазон ячеек, которые Вы хотите оценить заданным критерием.
• criteria
  (критерий) – условие, которое говорит формуле, какие значения суммировать. Может быть числом, ссылкой на ячейку, выражением или другой функцией Excel.
• sum_range
  (диапазон_суммирования) – необязательный, но очень важный для нас аргумент. Он определяет диапазон связанных ячеек, которые будут суммироваться. Если он не указан, Excel суммирует значения ячеек, в первом аргументе функции.

Собрав все воедино, давайте определим третий аргумент для нашей функции СУММЕСЛИ
. Как Вы помните, мы хотим суммировать все продажи, совершённые определённым продавцом, чьё имя задано в ячейке F2 (смотрите рисунок, приведённый выше).

1. range
   (диапазон) – так как мы ищем по ID
   продавца, значениями этого аргумента будут значения в столбце B основной таблицы (Main table). Можно задать диапазон B:B
   (весь столбец) или, преобразовав данные в таблицу, использовать имя столбца Main_table
   .

criteria
(критерий) – так как имена продавцов записаны в просматриваемой таблице (Lookup table), используем функцию ВПР
для поиска ID
, соответствующего заданному продавцу. Имя записано в ячейке F2, поэтому для поиска используем формулу:

VLOOKUP($F$2,Lookup_table,2,FALSE)
ВПР($F$2;Lookup_table;2;ЛОЖЬ)

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

Возможно, будет полезно почитать:

• Коррекция наращенных ресниц: типы коррекции, описание процедуры, уход ;
• История создания легендарной куклы Барби (Barbie) Друзья Барби: кто они ;
• Годовалый ребенок закатывает истерики ;
• Крики, слезы, топанье ногами, разбрасывание вещей и вспышки агрессии… ;
• Годовалый ребенок закатывает истерики ;
• Эффективные советы психолога ;
• Как воспитывают детей в италии ;
• Ребенок не хочет учиться: практические советы психолога ;

Подсчет сотрудников в каждом отделе

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

Поскольку отделов немного (три), для наглядности разместим таблицу с расчетом в диапазоне ячеек G1:H4 того же рабочего листа, на котором располагается спи-сок (рис. 5.2).

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

В ячейки G1 и H1 введите названия столбцов (Отдел и Численность), а в ячейки G2:G4 — названия отделов (Контроля, Реализации, Снабжения).

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

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

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

Рис. 5.3. Панель функции «СЧЕТЕСЛИ»

Название отдела можно внести непосредственно во второй аргумент функции «СЧЕТЕСЛИ», как показано на рис. 5.3. Но значительно проще вместо текста поместить туда ссылку на ячейку G2, в которой таковой находится. Для заполнения ячеек Н2:Н4 формулами, выполните следующие действия:

1. Выделите диапазон ячеек Н2:Н4. 2. Вызовите посредством мастера функций панель функции «СЧЕТЕСЛИ». 3. Поместите курсор в поле Диапазон и мышью выделите на рабочем листе ячейки А2:А11. 4. Нажмите функциональную клавишу для создания абсолютной ссылки на диапазон ячеек. 5. Перейдите в поле Условие и щелкните мышью на ячейке G2. 6. Нажмите комбинацию клавиш .

В результате этих действий в ячейке Н2 будет создана формула

Подобные формулы появятся также в ячейках НЗ и Н4, но во втором их аргументе будут указаны соответственно адреса ячеек G3 и G4.

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

Функция Эксель ЕСЛИ в целом хорошо справляется со своими задачами. Но вариант, когда нужно записывать длинную цепочку условий не очень приятный, т.к., во-первых, написать с первого раза не всегда получается (то условие укажешь неверно, то скобку не закроешь); во-вторых, разобраться при необходимости в такой формуле может быть непросто, особенно, когда условий много, а сами расчеты сложные.

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

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

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

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

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

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

Несколько условий в виде даты.

Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.

1.Подсчет дат в определенном интервале.

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

Следующие выражения подсчитывают в области с D2 по D21 количество дат, приходящихся на период с 1 по 7 февраля 2020 года включительно:

или

2. Подсчет на основе нескольких дат.

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

Как обычно, запишем двумя способами: со ссылками и без них:

и

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

Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.

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

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

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

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

Также рекомендуем:

БДСУММ (DSUM)

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

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

БДСУММ(database, field, criteria)

database – массив данных или диапазон. Первая строка должна содержать заголовки столбцов.

field – указание на столбец в диапазоне database , который содержит требуемую информацию.

field может быть либо текстовой меткой, соответствующей заголовку столбца в первой строке database , либо номером требуемого столбца, при том что первый столбец обозначается номером 1 .

criteria – массив или диапазон, содержащий критерии, на основании которых будут отфильтрованы исходные значения database .

Примечания

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

СУММ : Находит сумму ряда чисел или содержимого ряда ячеек.

БДДИСПП : Вычисляет дисперсию совокупности данных в табличном массиве. Для расчетов используется метод, похожий на SQL.

БДДИСП : Вычисляет дисперсию выборки данных в табличном массиве. Для расчетов используется метод, похожий на SQL.

ДСТАНДОТКЛП : Вычисляет стандартное отклонение совокупности данных в таблице. Для расчетов используется метод, похожий на SQL.

ДСТАНДОТКЛ : Вычисляет стандартное отклонение выборки данных в таблице. Для расчетов используется метод, похожий на SQL.

БДПРОИЗВЕД : Вычисляет произведение значений в табличном массиве по принципу, похожему на SQL.

ДМИН : Находит минимальное значение в табличном массиве. Для поиска используется метод, похожий на SQL.

ДМАКС : Находит максимальное значение в табличном массиве. Для поиска используется метод, похожий на SQL.

БИЗВЛЕЧЬ : Находит значение в табличном массиве. Для поиска используется метод, похожий на SQL.

БСЧЁТА : Подсчитывает количество числовых и текстовых значений в табличном массиве по принципу, похожему на SQL.

БСЧЁТ : Подсчитывает количество числовых значений в табличном массиве по принципу, похожему на SQL.

ДСРЗНАЧ : Вычисляет среднее арифметическое значений в табличном массиве по принципу, похожему на SQL.

Суммирование с множеством условий.

Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».

Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.

=СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F21;I3)

В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.

В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.

Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.

Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.

Синтаксис

Аргументы

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

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

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

Необязательный аргумент. Дополнительные диапазоны и условия для них. Разрешается использовать до 127 пар диапазонов и условий.

Замечания

• Каждая ячейка в аргументе диапазон_суммирования суммируется только в том случае, если выполнены все указанные условия, соответствующие этой ячейке. Например, формула содержит два аргумента диапазон_условий. Если первая ячейка аргумента диапазон_условий1 соответствует аргументу условия1, а первая ячейка аргумента диапазон_условий2 — аргументу условия2, первая ячейка аргумента диапазон_суммирования добавляется к сумме (и т. д. для всех остальных ячеек в указанных диапазонах).
• Ячейки в аргументе диапазон_суммирования, которым присвоено значение ИСТИНА, оцениваются как 1; ячейки в аргументе диапазон_суммирования, которым присвоено значение ЛОЖЬ, оцениваются как 0 (нуль).
• В отличие от аргументов диапазона и условий в функции СУММЕСЛИ, в функции СУММЕСЛИМН каждый аргумент диапазон_условий обязательно должен иметь то же количество строк и столбцов, что и аргумент диапазон_суммирования.
• В условии можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*) . Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности знаков. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~).

Как правильно записать?

Устанавливаем курсор в ячейку G2 и вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому как только далее будет нажата буква “е”, мы получим предложение выбрать функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.

Далее все наши действия также будут сопровождаться подсказками.

В качестве первого аргумента записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “,” и указываем второй аргумент.

Второй аргумент – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.

После этого снова через запятую указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем нажав “Enter”.

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

=ЕСЛИ(C2=»Запад»,»Местные»,»Экспорт»)

Наша ячейка G2 приняла значение «Местные».

Теперь нашу функцию можно скопировать во все остальные ячейки столбца G.

Использование операторов сравнения.

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

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

= СУММЕСЛИ (MI2: MI21; SI2: SI21; «Красный»; RE2: RE21; «> 100”)

Но рациональнее использовать ссылки, как это сделано на рисунке:

= СУММЕСЛИ (MI2: MI21; SI2: SI21; I2; RE2: RE21; I4)

Примечание

Обратите внимание, что логические выражения с операторами сравнения всегда должны заключаться в кавычки («») в формулах («»)

Синтаксис этой функции, помимо работы с числами, текстом и датами, точно такой же, как и для СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу об условной сумме.

Как еще мы можем решить нашу проблему?

Способ 2. Используем функцию СУММПРОИЗВ.

Давайте подробнее рассмотрим, как работает SUMPRODUCT():

= СУММПРОИЗВ (- (B2: B21 = $ I $ 12), — (D2: D21> I13), E2: E21)

Результат вычисления B2: B21 = $ I $ 12 — это массив

{ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ.: ЛОЖЬ

ИСТИНА означает, что код покупателя соответствует условию, например, слово Красный. Этот массив можно увидеть, выбрав в строке формул B2: B21 = $ I $ 12, а затем нажав F9.

А что это за странные минусовые знаки перед этими выражениями? Дело в том, что нам нужны не эти логические выражения, а числа, чтобы затем их можно было умножить и сложить. Если Excel выполняет математическую операцию с логическим выражением, он автоматически преобразует его в число. А знак минус означает умножение на -1. А если умножить на -1 дважды, результат не изменится. Мы помним это из школьной математики

И в результате логический массив превратится в массив чисел {0: 1: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 1: 0: 0: 0: 1: 0: 0: 0}.

Результат вычисления D2: D21> I13 — это массив

{ИСТИНА: ИСТИНА: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ИСТИНА: ЛОЖЬ: ЛОЖЬ: ЛОЖЬ}.

ИСТИНА соответствует пределу «количество больше 100». Здесь мы также используем двойное отрицание для преобразования логических значений в числа.

И, наконец, результатом вычисления B2: B13 является массив {11250: 23210: 12960: 3150: 5280: 9750: 3690: 18300: 5720: 6150: 8400: 2160: 7200: 1890: 17050: 3450: 15840: 2250 : 7200: 8250}, т.е только числа из столбца E.

Результатом умножения этих трех массивов на элемент будет {0: 23210: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 0: 15840: 0: 0: 0 }. Складываем эти продукты и получаем 39050.

Способ 3. Формула массива.

И еще один вариант расчета — применяем формулу массива. В I14 пишем:

= СУММ ((L2: L21 = I12) * (L2: L21> I13) * (L2: E21))

Не забудьте нажать CTRL + SHIFT + ENTER в конце, чтобы обозначить это выражение как формулу массива. Программа автоматически добавит фигурные скобки в начало и в конец. Опять получаем результат 39050.

Способ 4. Автофильтр.

Другой альтернативой является использование автоматического фильтра. Для этого преобразуйте диапазон данных A1: F21 в интеллектуальную таблицу. Напоминаю, что для этого в меню «Главная» выберите «Форматировать как таблицу». Далее добавляем итоговую строку (вкладка «Дизайн») и устанавливаем необходимые фильтры.

Без формул будет определено общее количество отфильтрованных строк.

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

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

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

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

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

Если вам нужно оценить более одного параметра выбора, используйте COUNTPIIF.

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

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

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

Введите СЧЁТЕСЛИМН в функцию СУММ, например:

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

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

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

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

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

Как СУММЕСЛИМН работает с датами?

Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.

Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):

Замечание. Когда вы при составлении ограничения используете другую функцию Excel вместе с логическим оператором, нужно использовать амперсанд (&) для объединения всего выражения в виде текста, например «<=»&СЕГОДНЯ().

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

Однако СУММЕСЛИМН сложение делает гораздо проще и понятнее, не так ли?

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

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

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

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

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

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

если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
при поиске текста не учитывается регистр символов;
чтобы программа искала сами знаки >,
Очень часто при работе в Excel требуется подсчитать количество ячеек на рабочем листе. Это могут быть пустые или заполненные ячейки, содержащие только числовые значения, а в некоторых случаях, их содержимое должно отвечать определенным критериям. В этом уроке мы подробно разберем две основные функции Excel для подсчета данных – СЧЕТ и СЧЕТЕСЛИ, а также познакомимся с менее популярными – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН.

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

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

А вот ячейки, содержащие значения даты и времени, учитываются:

Функция СЧЕТ может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

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

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

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

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

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

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

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

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

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

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

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

Нам важно ваше мнение:

Функция СУММЕСЛИ при условии неравенства

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

1. Определитесь с диапазоном ячеек, попадающих под рассмотрение формулой, у нас это будет прибыль за месяц.
2. Начните запись с ее указания в поле ввода, написав СУММЕСЛИ.
3. Создайте открывающую и закрывающую скобку, где введите диапазон выбранных ячеек, например C2:C25. После этого обязательно поставьте знак ;, который означает конец аргумента.
4. Откройте кавычки и в них укажите условие, что в нашем случае будет >300000.
5. Как только произойдет нажатие по клавише Enter, функция активируется. На скриншоте ниже видно, что условию >300000 соответствуют лишь две ячейки, следовательно, формула суммирует их числа и отображает в отдельном блоке.

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

Примеры использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ целенаправлено употреблять в вариантах, когда таблица содержит огромное количество данных, которые могут быть отфильтрованы по одному либо нескольким аспектам. При всем этом в итоге внедрения фильтров будет отображена лишь часть таблицы, данные в которой соответствуют установленному аспекту. Но операции с внедрением обыденных функций, таковых как СУММ, СРЗНАЧ и др. будут выполняться над всей изначальной таблицей (другими словами с учетом укрытых строк). Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ работает лишь с отфильтрованными данными.

Суммирование лишь видимых ячеек в фильтре Excel

Пример 1. В таблице содержатся данные о продажах в магазине музыкальных инструментов электрогитар 3-х марок разных моделей в протяжении 3-х дней. Высчитать промежные итоги по продажам гитары марки Ibanez.

Вид начальной таблицы данных:

Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу либо просто перейдите курсором на всякую ячейку таблицы и воспользуйтесь инвентарем «ДАННЫЕ»-«Фильтр». Сейчас начальная таблица имеет последующий вид:

Нажмем на раскрывающийся перечень в столбце B («Марка продукта») и установим флаг лишь напротив наименования «Ibanez»:

Опосля нажатия на клавишу «ОК» таблица воспримет последующий вид:

Как видно, некие строчки сейчас являются сокрытыми. Если применить обыденную функцию СУММ, будет произведен расчет для всех строк начальной таблицы:

Заместо этого в ячейке C24 будем употреблять последующую функцию:

• 9 – числовое значение, соответственное использованию функции СУММ для получения промежных итогов;
• C4:C20 – спектр ячеек, содержащих данные о цены гитар (при всем этом все остальные гитары, не считая марки Ibanez, в расчете не учитываются).

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В итоге получим:

Для сопоставления приведем результаты, приобретенные с внедрением обыкновенной функцией СУММ:

Невзирая на то, что часть строк укрыта благодаря использованию фильтра, функция СУММ учитывает все строчки в расчете.