Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.
Давайте рассмотрим как это сделать.
У нас есть таблица с данными по продажам за каждый день. Для выполнения задачи нам потребуется функция СУММЕСЛИМН.
Содержание
- Как работает функция СУММЕСЛИМН?
- Формула для суммирования значений между двумя датами
- Как работает эта формула
- Как суммировать значения между двумя динамическими датами
- Как работает эта формула
Как работает функция СУММЕСЛИМН?
Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.
Синтаксис функции выглядит так:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
- диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
- диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
- [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;
Формула для суммирования значений между двумя датами
Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.
Скачайте пример файла по ссылке.
Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:
=СУММЕСЛИМН(B2:B28;A2:A28;»>=01.06.2018″;A2:A28;»<=15.06.2018″)
После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.
Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.
Больше лайфхаков в нашем Telegram Подписаться
Как работает эта формула
В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН, которые помогают нам суммировать данные в указанном диапазоне дат.
Давайте разложим формулу на составные части для понимания ее работы:
- Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
- Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
- Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
- Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018
Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.
Как суммировать значения между двумя динамическими датами
На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?
В этом случае нам поможет следующая формула:
=СУММЕСЛИМН(B2:B18;A2:A18;»<=»&СЕГОДНЯ();A2:A18;»>=»&СЕГОДНЯ()-6)
Как работает эта формула
В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.
Здесь, в первом критерии для вычисления нужной даты мы указываем функции условие, при котором нужно суммировать данные за период равный или меньше текущей даты.
Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.
Таким образом, диапазон двух дат в формуле теперь выглядит как: «Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня».
Если у вас остались вопросы по этому примеру оставляйте их в комментариях.
Сложить или вычесть дни из даты
Допустим, ваш счет должен быть выставлен во вторую пятницу каждого месяца. Необходимо перевести средства на счет, чтобы они были поступает за 15 календарных дней до этой даты, поэтому необходимо вычесть 15 дней из срока. В следующем примере поймем, как складывать и вычитать даты путем ввода положительных или отрицательных чисел.
-
Введите даты сходимой даты в столбце A.
-
Введите количество дней, в течение дней в которые нужно добавить или вычесть столбец B. Вы можете ввести отрицательное число, чтобы вычесть дни из даты начала, и положительное число, чтобы прибавить к дате.
-
В ячейке C2 введите =A2+B2и скопируйте его при необходимости.
Сложить или вычесть месяцы из даты с помощью функции ДАТАМЕСТ
С помощью функции ДАТАМАЙТА можно быстро прибавить к дате месяцы или вычесть их из нее.
Для функции ДАТАМЕСТ требуются два аргумента: дата начала и количество месяцев, которые нужно прибавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =ВРЕМЯ(«15.09.19»;-5) возвращает 15.04.19.
-
В этом примере даты начала можно ввести в столбец A.
-
Введите количество месяцев, которые нужно добавить или вычесть, в столбце B. Чтобы указать, следует ли вычесть месяц, можно ввести знак «минус» (-) перед числом (например, -1).
-
Введите =ВРЕМЯ(A2;B2) в ячейку C2 и скопируйте ее при необходимости.
Примечания:
-
В зависимости от формата ячеек, содержащих введенную формулу, результаты могут отображаться в Excel как порядковые номера. Например, 8-фев-2019 может отображаться как «43504».
-
В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию порядковый номер 1 января 1900 г. — 1, а 1 января 2010 г. — 40179, так как он отс0 1 января 1900 г. находится через 40 178 дней.
-
Если результаты отображаются в числовом номере, выберите ячейки, о которые идет речь, и следуйте следующим шагам:
-
Нажмите CTRL+1, чтобы запустить диалоговое окно «Формат ячеек», и щелкните вкладку «Число».
-
В категории щелкните«Дата»,выберите нужный формат даты и нажмите кнопку «ОК». Значения в ячейках должны отобразиться как даты.
-
-
Сложить или вычесть годы из даты
В этом примере мы добавляем и вычитаем годы из начальной даты с помощью следующей формулы:
=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))
Как работает формула:
-
Функция ГОД ищет дату в ячейке A2 и возвращает 2019. Затем он прибавляет 3 года к ячейке B2, в результате чего результат будет 2022.
-
Функции МЕСЯЦи ДЕНЬ возвращают только исходные значения из ячейки A2, но они требуются функцией ДАТА.
-
Наконец, функция ДАТА объединяет эти три значения в дату через 3 года в будущем — 08.02.22.
Сложить или вычесть из даты сочетание дней, месяцев и лет
В этом примере мы добавляем и вычитаем годы, месяцы и дни из начальной даты с помощью следующей формулы:
=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2)+C2;ДЕНЬ(A2)+D2)
Как работает формула:
-
Функция ГОД ищет дату в ячейке A2 и возвращает 2019. Затем она добавляет 1 год из ячейки B2, в результате чего результатом будет 2020.
-
Функция МЕСЯЦ возвращает 6, а затем добавляет в нее 7 из ячейки C2. Это интересно, поскольку 6 + 7 = 13, т. е. 1-й год и 1 месяц. В этом случае формула распознает это и автоматически добавляет к результату еще один год, урезав его с 2020-го по 2021-й.
-
Функция ДЕНЬ возвращает 8 и добавляет к ней 15. Эта формула будет работать так же, как и часть формулы «МЕСЯЦ», если вы ука между числами дней в этом месяце.
-
Затем функция ДАТА объединяет эти три значения в дату, которая составляет 1 год, 7 месяцев и 15 дней в будущем— 23.01.21.
Вот несколько способов использования формул или функций, которые работают с датами для выполнения таких задач, как поиск влияния на календарный план проекта при добавлении двух недель или времени, необходимого для выполнения задачи.
Предположим, что расчетный цикл счета составляет 30 дней, и вы хотите, чтобы средства были на счете за 15 дней до даты вычета за март 2013 г. Вот как это сделать, используя формулу или функцию для работы с датами.
-
В ячейке A1 введите 08.02.2013.
-
В ячейке B1 введите =A1-15.
-
В ячейке C1 введите =A1+30.
-
В ячейке D1 введите =C1-15.
Добавление месяцев к дате
Мы будем использовать функцию ДАТАМЕСЯТ, и вам потребуется дата начала и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:
-
В ячейке A1 введите 24.10.2013.
-
В ячейке B1 введите =ВРЕМЯ(A1;16).
-
Чтобы отформатирование результатов в виде дат, выберите ячейку B1. Щелкните стрелку рядом с кнопкой «Числовойформат> даты.
Вычитание месяцев из даты
Для вычитания месяцев из даты будет применяться та же функция ДАТАМАЙТА.
Введите дату в ячейку A1 и в ячейку B1 и формулу =ДАТАМЕС(15.04.2013;-5).
Здесь указывается значение даты начала, которая заключена в кавычках.
Можно просто обратиться к ячейке со значением даты или использовать формулу =ДАТАМЕС(A1;-5) для того же результата.
Другие примеры
Прибавь года к дате или вычесть их из нее
|
Дата начала |
Прибавь или вычесть года |
Формула |
Результат |
|---|---|---|---|
|
10/24/2013 |
3 (прибавить 3 года) |
=ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2)) |
10/24/2016 |
|
10/24/2013 |
-5 (вычитать 5 лет) |
=ДАТА(ГОД(A4)+B4;МЕСЯЦ(A4);ДЕНЬ(A4)) |
10/24/2008 |
Содержание
- Задача1 (1 текстовый критерий и 1 числовой)
- Задача2 (2 числовых критерия)
- Задача3 (2 критерия Дата)
- Задача4 (Месяц)
- Как работает функция СУММЕСЛИМН?
- Формула для суммирования значений между двумя датами
- Примеры использования функции СУММЕСЛИМН в Excel
- Динамический диапазон суммирования по условию в Excel
- СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)
- СУММЕСЛИ в Excel примеры с текстовым критерием
- СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки
- Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1
- СУММЕСЛИ примеры формул с подстановочными знаками
- Структура функции
- Примеры суммеслимн
- Постановка задачи
- Функция СУММЕСЛИМН, когда условий много
- Сумма, если Дата находится между двумя датами
- Сумма, если Дата больше, чем
- Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL
Задача1 (1 текстовый критерий и 1 числовой)
Найдем количество ящиков товара с определенным Фруктом И , у которых Остаток ящиков на складе не менее минимального. Например, количество ящиков с товаром персики ( ячейка D 2 ), у которых остаток ящиков на складе >=6 ( ячейка E 2 ) . Мы должны получить результат 64. Подсчет можно реализовать множеством формул, приведем несколько (см. файл примера Лист Текст и Число ):
1. = СУММЕСЛИМН(B2:B13;A2:A13;D2;B2:B13;”>=”&E2)
Синтаксис функции: СУММЕСЛИМН(интервал_суммирования;интервал_условия1;условие1;интервал_условия2; условие2…)
- B2:B13 Интервал_суммирования — ячейки для суммирования, включающих имена, массивы или ссылки, содержащие числа. Пустые значения и текст игнорируются.
- A2:A13 и B2:B13 Интервал_условия1; интервал_условия2; … представляют собой от 1 до 127 диапазонов, в которых проверяется соответствующее условие.
- D2 и “>=”&E2 Условие1; условие2; … представляют собой от 1 до 127 условий в виде числа, выражения, ссылки на ячейку или текста, определяющих, какие ячейки будут просуммированы.
Порядок аргументов различен в функциях СУММЕСЛИМН() и СУММЕСЛИ() . В СУММЕСЛИМН() аргумент интервал_суммирования является первым аргументом, а в СУММЕСЛИ() – третьим. При копировании и редактировании этих похожих функций необходимо следить за тем, чтобы аргументы были указаны в правильном порядке.
2. другой вариант = СУММПРОИЗВ((A2:A13=D2)*(B2:B13);–(B2:B13>=E2)) Разберем подробнее использование функции СУММПРОИЗВ() :
- Результатом вычисления A2:A13=D2 является массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение ИСТИНА соответствует совпадению значения из столбца А критерию, т.е. слову персики . Массив можно увидеть, выделив в Строке формул A2:A13=D2 , а затем нажав F9
- Результатом вычисления B2:B13 является массив {3:5:11:98:4:8:56:2:4:6:10:11}, т.е. просто значения из столбца B
- Результатом поэлементного умножения массивов (A2:A13=D2)*(B2:B13) является {0:0:0:0:4:8:56:0:0:0:0:0}. При умножении числа на значение ЛОЖЬ получается 0; а на значение ИСТИНА (=1) получается само число;
- Разберем второе условие: Результатом вычисления –( B2:B13>=E2) является массив {0:0:1:1:0:1:1:0:0:1:1:1}. Значения в столбце « Количество ящиков на складе », которые удовлетворяют критерию >=E2 (т.е. >=6) соответствуют 1;
- Далее, функция СУММПРОИЗВ() попарно перемножает элементы массивов и суммирует полученные произведения. Получаем – 64.
3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) .
4. Формула массива =СУММ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) похожа на вышеупомянутую формулу =СУММПРОИЗВ((A2:A13=D2)*(B2:B13)*(B2:B13>=E2)) После ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER
5. Формула массива =СУММ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.
6. Формула =БДСУММ(A1:B13;B1;D14:E15) требует предварительного создания таблицы с условиями (см. статью про функцию БДСУММ() ). Заголовки этой таблицы должны в точности совпадать с соответствующими заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И (см. диапазон D14:E15 ).
Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)
Другой задачей может быть нахождение сумм ящиков только тех партий товаров, у которых количество ящиков попадает в определенный интервал, например от 5 до 20 (см. файл примера Лист 2Числа ).
Формулы строятся аналогично задаче 1: =СУММЕСЛИМН(B2:B13;B2:B13;”>=”&D2;B2:B13;”
Примечание : для удобства, строки, участвующие в суммировании, выделены Условным форматированием с правилом =И($B2>=$D$2;$B2
Задача3 (2 критерия Дата)
Другой задачей может быть нахождение суммарных продаж за период (см. файл примера Лист “2 Даты” ). Используем другую исходную таблицу со столбцами Дата продажи и Объем продаж .
Формулы строятся аналогично задаче 2: = СУММЕСЛИМН(B6:B17;A6:A17;”>=”&D6;A6:A17;”
Примечание . Даты могут быть представлены в числовой форме см. статью Как Excel хранит дату и время
При необходимости даты могут быть введены непосредственно в формулу = СУММЕСЛИМН(B6:B17;A6:A17;”>=15.01.2010″;A6:A17;”
Чтобы вывести условия отбора в текстовой строке используейте формулу =”Объем продаж за период с “&ТЕКСТ(D6;”дд.ММ.гг”)&” по “&ТЕКСТ(E6;”дд.ММ.гг”)
В последней формуле использован Пользовательский формат .
Задача4 (Месяц)
Немного модифицируем условие предыдущей задачи: найдем суммарные продаж за месяц(см. файл примера Лист Месяц ).
Формулы строятся аналогично задаче 3, но пользователь вводит не 2 даты, а название месяца (предполагается, что в таблице данные в рамках 1 года).
Месяц вводится с помощью Выпадающего списка , перечень месяцев формируется с использованием Динамического диапазона (для исключения лишних месяцев).
Как работает функция СУММЕСЛИМН?
Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.
Синтаксис функции выглядит так:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
- диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
- диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
- [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;
Формула для суммирования значений между двумя датами
Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.
Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:
=СУММЕСЛИМН(B2:B28;A2:A28;”>=01.06.2018″;A2:A28;”<=15.06.2018″)
После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.
Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.
Примеры использования функции СУММЕСЛИМН в Excel
В отличие от СУММЕСЛИ, в рассматриваемой функции предусмотрена возможность внесения до 127 критериев отбора данных для суммирования. СУММЕСЛИМН удобна для работы с объемными таблицами, содержащими большое число полей данных. Например, если требуется рассчитать общую сумму средств, полученных в результате поставки определенного типа товара указанной компанией в определенную страну, удобно использовать функцию СУММЕСЛИМН.
Динамический диапазон суммирования по условию в Excel
Пример 1. В таблице содержатся данные о успеваемости студентов по некоторому предмету в университете. Определить итоговую оценку для студентов с фамилией, начинающейся на букву «А» при условии, что минимальный балл должен быть не менее 5 (успеваемость студентов оценивается по 10-бальной шкале).
Вид таблицы данных:
Для расчета суммарного балла согласно установленным критериям используем следующую формулу:
Описание аргументов:
- C3:C14 – диапазон ячеек с баллами, из которых будут автоматически выбраны значения для расчета суммы, которые соответствуют установленным критериям;
- C3:C14 – первый диапазон ячеек, к которому будет применен первый критерий;
- “>5” – первое условие отбора значений из указанного выше диапазона;
- B3:B14 – второй диапазон ячеек (с фамилиями студентов), к которому будет применен второй критерий;
- “А*” – второе условие отбора значений (все фамилии, которые начинаются с буквы «А»).
Результат расчетов:
В результате формула автоматически определила динамически изменяемый диапазон в соответствии с условиями отбора значений и просуммировала его.
СУММЕСЛИ в Excel примеры с логическими операторами (больше, меньше или равно)
Давайте рассмотрим несколько примеров формул СУММЕСЛИ, которые вы можете использовать для суммирования значений для условий больше чем, меньше чем или равно заданному значению.
Примечание. Обратите внимание, что в формулах Excel СУММЕСЛИ оператор сравнения, за которым следует число или текст, всегда должен быть заключен в двойные кавычки (“”).
|
Критерий |
Оператор |
Пример формулы СУММЕСЛИ |
Описание |
|
Сумма, если больше |
> |
=СУММЕСЛИ(A2:A10; “>5”) |
Суммирует значения больше 5 в ячейках A2:A10. |
|
Сумма, если меньше |
< |
=СУММЕСЛИ(A2:A10; “<10”; B2:B10) |
Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше 10. |
|
Сумма, если равно |
= (можно не указывать) |
=СУММЕСЛИ(A2:A10; “=”&D1) или =СУММЕСЛИ(A2:A10;D1) |
Суммирует значения в ячейках A2:A10, которые равны значению в ячейке D1. |
|
Сумма, если не равно |
<> |
=СУММЕСЛИ(A2:A10; “<>”&D1; B2:B10) |
Суммирует значения в ячейках B2:B10, если соответствующая ячейка в столбце A не равна значению в ячейке D1. |
|
Сумма если больше или равно |
>= |
=СУММЕСЛИ(A2:A10; “>=5”) |
Суммирует значения, которые больше или равны 5 в диапазоне A2:A10. |
|
Сумма если меньше или равно |
<= |
=СУММЕСЛИ(A2:A10; “<=10”; B2:B10) |
Суммирует значения в ячейках B2:B10, если соответствующее значение в столбце A меньше либо равно 10. |
СУММЕСЛИ в Excel примеры с текстовым критерием
Помимо чисел, функция СУММЕСЛИ позволяет суммировать значения в зависимости от того, содержит ли соответствующая ячейка в другом столбце определенный текст или нет. Рассмотрим примеры СУММЕСЛИ в Excel с текстом.
Обратите внимание, что вам понадобятся разные формулы СУММЕСЛИ для точного и частичного совпадения, как показано в таблице ниже.
|
Критерий |
Пример формулы СУММЕСЛИ |
Описание |
|
Сумма, если равно |
Точное совпадение: =СУММЕСЛИ(A2:A8; “бананы”; C2:C8) |
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит точное слово «бананы» и никакие другие слова или символы. Ячейки, содержащие «зеленые бананы», «бананы зеленые» или «бананы!» не будут считаться. |
|
Частичное совпадение: =СУММЕСЛИ(A2:A8; “*бананы*”; C2:C8) |
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит слово «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «зеленые бананы» или «бананы зеленые», будут учитываться для суммирования. |
|
|
Сумма, если не равно |
Точное совпадение: =СУММЕСЛИ(A2:A8; “<>бананы”; C2:C8) |
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A содержит любое значение, отличное от слова «бананы». Если ячейка содержит «бананы» вместе с некоторыми другими словами или символами, такими как «желтые бананы» или «бананы желтые», такие ячейки будут учитываться для суммирования. |
|
Частичное совпадение: =СУММЕСЛИ(A2:A8; “<>*бананы*”; C2:C8) |
Суммирует значения в ячейках C2:C8, если соответствующая ячейка в столбце A не содержит слова «бананы», отдельно или в сочетании с любыми другими словами. Ячейки, содержащие «желтые бананы» или «бананы желтые», не суммируются. |
Для получения дополнительной информации о частичном совпадении см. пункт СУММЕСЛИ примеры формул с подстановочными знаками.
А теперь, давайте посмотрим пример формулы «Сумма, если не равно» в действии. Как показано на изображении ниже, формула суммирует количество всех продуктов, кроме «Банана Дамский пальчик»:
=СУММЕСЛИ(A2:A8; “<>Банан Дамский пальчик”; C2:C8)
СУММЕСЛИ в Excel примеры операторов сравнения со ссылками на ячейки
Если вы хотите получить более универсальную формулу Excel СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:
= СУММЕСЛИ(A2:A8; “<>”&F1; C2:C8)
В этом случае вам не придется менять формулу СУММЕСЛИ, основанную на другом критерии – вы просто вводите новое значение в ссылочной ячейке.
Функция СУММЕСЛИ в Excel с примерами – Пример функции СУММЕСЛИ, суммирование исключая значение в ячейке F1
Примечание. Когда вы используете логическое выражение с ссылкой на ячейку, вы должны использовать двойные кавычки (“”), чтобы начать текстовую строку и амперсанд (&), чтобы объединить и завершить строку, например «<>» и F1.
Оператор «равенства» (=) можно не использовать до ссылки на ячейку, поэтому обе приведенные ниже формулы эквивалентны и правильны:
Формула 1: =СУММЕСЛИ(A2:A8; “=” & F1; C2:C8)
Формула 2: =СУММЕСЛИ(A2:A8; F1; C2:C8)
СУММЕСЛИ примеры формул с подстановочными знаками
Если вы намерены условно суммировать ячейки на основе «текстовых» критериев и хотите суммировать путем частичного совпадения, вам нужно использовать подстановочные знаки в формуле СУММЕСЛИ.
Доступны следующие подстановочные знаки:
Звездочка (*) – представляет любое количество символов
Знак вопроса (?) – представляет один символ в определенном месте
Пример 1. Суммирование значений, основанные на частичном совпадении
Предположим, вы хотите суммировать количество, относящиеся ко всем видам бананов. Следующие формулы СУММЕСЛИ будут очень эффективны в таких случаях:
=СУММЕСЛИ(A2:A8; “*бананы*”;C2:C8) – критерий включает текст, заключенный в звездочки (*).
=СУММЕСЛИ(A2:A8; “*”&F1&”*”; C2:C8) – критерий включает ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для конкатенации строки.
Структура функции
Прежде чем рассматривать механизм работы оператора, необходимо ознакомится с содержанием функции, а также синтаксисом формулы. Суммеслимн расшифровывается как сумма данных, если выполняется множество.
Значения аргументов следующие:
- В диапазон суммирования записываете столбец или строку, по которой будет вычисляться сумма значений.
- Диапазон условия 1 нужен для задания массива, в котором будут применены параметры отбора данных.
- Условие 1 является дополнением к предыдущему аргументу. На этой позиции указывается критерий отбора в диапазоне условия 1.
- Затем идут дополнительные аргументы, с помощью которых можно уточнить параметры расчета суммы значений.
Важно! Количество ячеек диапазона суммирования и условия должно быть одинаковым.
Примеры суммеслимн
Для начала создаете таблицу со значениями. Можно использовать различные форматы ячеек и в зависимости от этого будет меняться синтаксис внутри формулы. Для примера будем работать с таблицей следующего вида:
Как видите, ячейки содержат и даты, и текст, и цифры. Будем задавать критерии для поиска суммы, а также рассматривать структуру оператора под поставленную задачу.
- Примеры с датами продаж:
Задача: найти количество продуктов проданных 22.02.2019г.
В свободной ячейке пишете следующее: =СУММЕСЛИМН(С2:С8;B2:B8;B2)
- Примеры с двумя условиями:
Задача: определить сумму яблок, проданных 22.02.2019г.:
Функция будет выглядеть следующим образом: =СУММЕСЛИМН(C2:C8;B2:B8;B2;A2:A8;»Яблоко»)
где B2:B8;B2 – первое условие, а A2:A8;»Яблоко» – второе. Диапазон суммирования по столбцу Количество.
Важно! Все текстовые блоки внутри формулы записываются в двойных кавычках.
- Примеры с текстом и звездочкой:
Задача: найти количество яблок, проданных Артемом.
Содержимое пустой ячейки: =СУММЕСЛИМН(C2:C8;A2:A8;»Яблоко»;D2:D8;»А*»)
Использование подстановочного знака звездочка помогает искать продавца, имя которого начинается на букву А. При таком условии считается число яблок, проданных Артемом и Ангелиной, поскольку их имена начинаются на букву А. Чтобы найти именно продажи Артема, необходимо изменить аргумент на «Ар*».
- Также в числовых столбцах можно использовать операторы сравнения. Если в условие больше меньше или равно, то можно обозначить границы поиска по цифрам.
Задача: Найти дату продажи Ангелиной продуктов больше 10 единиц.
Функция имеет следующий вид: =СУММЕСЛИМН(B2:B8;D2:D8;»Ангелина»;C2:C8;»>10″)
Как видите, оператор СУММЕСЛИМН позволяет находить сумму значений исходя из нескольких параметров, выполнение которых обязательно. При этом нужно соблюдать правила одинаковости размера массивов и написания текстовых блоков. Единственным недостатком такой функции является то, что нельзя суммировать несколько диапазонов. Для этого лучше использовать СУММПРОИЗВ.
Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина “Копейка”.
Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для “Копейки”), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) – в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3–Условие3), и четвертую, и т.д. – при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться – см. следующие способы.
Сумма, если Дата находится между двумя датами
=СУММЕСЛИМН(сумма;начальная_дата;»>»&А1;конечная_дата;»<«&В1)
В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.
В примере показано, ячейка H7 содержит формулу:
=СУММЕСЛИМН(D5:D11; C5:C11; «>15.07.2019»; C5:C11; «<15.08.2019»)
Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.
Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:
«>»&Н5;Дата // критерий 1
«<«&Н6;Дата // критерий 2
Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).
Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).
Сумма, если Дата больше, чем
В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.
В примере показано, ячейка H4 содержит формулу:
=СУММЕСЛИ(C5:C11; «>1.10.2019»;D5:D11)
Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.
Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).
Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).
Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL
Смотрите также 0 соответственно и с учетом установленных отбора значений (все массива. 0*1*1 = 0 функция БДСУММ извлекает от других слов, будет суммировать всеиз поиска исключаются
Проданное количество превышает установленный уровень. в ячейке B1. 1048576 значений: 4-х задач являетсяD14:E15 этих похожих функций
Источники
- https://excel2.ru/articles/funkciya-summeslimn-slozhenie-s-neskolkimi-kriteriyami-v-ms-excel-chast-2uslovie-i-summeslimn
- https://excelhack.ru/kak-summirovat-znacheniya-mezhdu-dvumya-datami/
- https://exceltable.com/funkcii-excel/primery-funkcii-summeslimn
- https://naprimerax.org/posts/68/funktciia-summesli-v-excel
- https://mir-tehnologiy.ru/summeslimn-v-excel-primery/
- https://www.planetaexcel.ru/techniques/2/167/
- https://excelpedia.ru/formuli-v-excel/summy-s-datami
- https://my-excel.ru/excel/summesli-v-excel-po-dvum-kriterijam.html
На чтение 2 мин. Просмотров 31.5k.
Сумма, если Дата находится между двумя датами
=СУММЕСЛИМН(сумма;начальная_дата;»>»&А1;конечная_дата;»<«&В1)
В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.
В примере показано, ячейка H7 содержит формулу:
=СУММЕСЛИМН(D5:D11; C5:C11; «>15.07.2019»; C5:C11; «<15.08.2019»)
Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.
Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:
«>»&Н5;Дата // критерий 1
«<«&Н6;Дата // критерий 2
Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).
Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).
Сумма, если Дата больше, чем
В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.
В примере показано, ячейка H4 содержит формулу:
=СУММЕСЛИ(C5:C11; «>1.10.2019»;D5:D11)
Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.
Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).
Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).
ДАТА как ссылка на ячейку
Если вы хотите выставить дату на листе, так что он может быть легко изменена, используйте эту формулу:
=СУММЕСЛИ(ДАТА;»>»&А1;сумма)
где A1-ссылка на ячейку, которая содержит действительную дату.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
=СУММЕСЛИМН(сумма; ДАТА;»>»&ДАТА(2019;10;1))
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Когда вы работаете с наборами данных, в которых есть даты, вы часто обнаруживаете, что пытаетесь производить расчеты на основе дат.
Например, если у вас есть данные о продажах за месяц, вы можете узнать общие продажи, которые произошли между двумя заданными датами, или общие продажи, сделанные в выходные и будние дни.
Один из способов быстро получить эти ответы — использовать функцию СУММЕСЛИМН.
СУММЕСЛИМН (как следует из названия) позволяет суммировать диапазон на основе критериев. В СУММЕСЛИМН вы можете указать несколько условий, и он будет суммировать только те ячейки / значения, которые соответствуют всем условиям.
В этом уроке я покажу вам, как сумма значений между двумя датами с использованием функции СУММЕСЛИМН.
Итак, приступим!
СУММИРОВАТЬ все значения между двумя датами
Предположим, у вас есть набор данных, как показано ниже, и вы хотите знать продажи, которые были произведены в течение 1 января 2020 года и 31 января 2020 года (здесь я использую формат ДД-ММ-ГГГГ для даты)
Ниже приведена формула, которая даст вам сумму продаж между этими двумя датами:
= СУММЕСЛИМН (C2: C15, A2: A15, "> = 1-1-2020", $ A $ 2: $ A $ 15, "<= 31-01-2020")
Вышеупомянутая функция СУММЕСЛИ использует пять аргументов (это может измениться в зависимости от количества имеющихся у вас условий):
- Первый аргумент (C2: C15) — это диапазон значений, которые мы хотим добавить.
- Второй и третий аргументы относятся к первому условию: дата должна быть больше или равна 01-01-2020. Для каждого условия нужно указать диапазон критериев и критерии
- Четвертый и пятый аргументы относятся ко второму условию — критериям и диапазону критериев.
В приведенной выше формуле я жестко запрограммировал даты. Вы также можете указать даты в ячейке и вместо этого использовать ссылку на ячейку. Кроме того, если в вашем условии используется оператор (например, = или), вам необходимо заключить оператор в двойные кавычки.
Примечание: В формуле вы можете использовать любой допустимый формат даты в формуле, например, в ячейках я использовал дату как 01-01-2020, но в формуле я могу использовать любой формат, который все еще относится к этому Дата. Например, я могу использовать 01 января 2021-2022, 01 января 2021-2022 или 1 января 2020 года. Пока формат даты действителен, Excel сможет использовать его для вычисления суммы между двумя заданными датами.
Например, если у вас есть дата начала и дата окончания в ячейках (как показано ниже), вы можете использовать следующую формулу, чтобы получить сумму продаж в заданном диапазоне дат.
= СУММЕСЛИМН (C2: C15, A2: A15, "> =" & F1, $ A $ 2: $ A $ 15, "<=" & F2)
Помните, что оператор должен быть в двойных кавычках, а ссылка на ячейку не должна заключаться в двойные кавычки.
СУММИРОВАТЬ все значения между двумя датами для определенного продукта
Поскольку функция СУММЕСЛИМН позволяет использовать несколько условий, вы также можете добавить дополнительные критерии в ту же формулу.
Например, предположим, что у вас есть тот же набор данных (как показано ниже), но на этот раз вы хотите узнать общие продажи принтеров, которые произошли между двумя указанными датами (1 января и 31 января).
Вы можете сделать это, добавив еще одно условие в формулу, где помимо проверки даты, оно также проверяет, является ли продукт принтером или нет. Затем он даст вам результат, соответствующий всем заданным условиям.
Ниже приведена формула, которая сделает это:
= СУММЕСЛИМН (C2: C15, A2: A15, "> = 1-1-2020", $ A $ 2: $ A $ 15, "<= 31-01-2020", $ B $ 2: $ B $ 15, "Принтер" )
Приведенная выше формула проверяет даты, а также то, является ли продукт принтером. Он будет суммировать значение только при соблюдении всех трех условий.
Точно так же вы также можете получить сумму значений между датами, когда вы хотите исключить конкретный продукт.
Например, если вы хотите суммировать значения от 1 до 31 января для всего продукта, кроме сканера, вы можете использовать следующую формулу:
= СУММЕСЛИМН (C2: C15, A2: A15, "> = 1-1-2020", $ A $ 2: $ A $ 15, "<= 31-01-2020", $ B $ 2: $ B $ 15, "Сканер" )
В приведенной выше формуле я использовал оператор not-equal-to-operator (), чтобы исключить получение значений для Scanner в результате.
И, чтобы повторить, я жестко запрограммировал значения данных в формуле, но если у вас есть эти даты в ячейке, вы можете ссылаться на эту ячейку в формуле.
Это несколько примеров, в которых вы можете суммировать значения между двумя датами, и вы можете настроить формулу, чтобы добавить больше условий, если хотите.
Надеюсь, вы нашли этот урок полезным.