Суммы с датами в Excel
В суммировании значений, которые находятся между двумя датами, можно использовать функцию СУММЕСЛИМН.
В примере показано, ячейка H7 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.
Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.
Чтобы соответствовать времени между двумя значениями, нам нужно использовать два критерия. СУММЕСЛИМН требует, чтобы каждому критерию вводился в качестве критерия/пара диапазон:
Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).
Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).
Сумма, если Дата больше, чем
В сумме, если дата превышает определенную дату, вы можете использовать функцию СУММЕСЛИ.
В примере показано, ячейка H4 содержит формулу:
Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.
Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.
В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).
Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).
ДАТА как ссылка на ячейку
Если вы хотите выставить дату на листе, так что он может быть легко изменена, используйте эту формулу:
где A1-ссылка на ячейку, которая содержит действительную дату.
Альтернатива с СУММЕСЛИМН
Вы также можете использовать функцию СУММЕСЛИМН. СУММЕСЛИМН может обрабатывать несколько критериев, и порядок аргументов отличается от СУММЕСЛИ. Эквивалентная формула СУММЕСЛИМН:
Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.
Как складывать даты в excel
Сообщений: 6 Регистрация: 01.01.1970
03.04.2011 21:46:19
Столкнулся с такой проблемой:
есть таблица с датами, каждой дате соответствуют различные суммы денег
Например: 01.02.11 — 256.13
02.02.11 — 302.00
02.02.11 — 1000.01
03.02.11 — 115.00
Мне нужно в отдельной таблице вывести суммы по датам, если чисел в дате несколько чтобы они сложились.
Примерно, должно получиться так: 01.02.11 — 256.13
02.02.11 — 1302.01
03.02.11 — 115.00
Можно ли в Excel решить мою задачу?
Работа с датами в электронной таблице Microsoft Excel
В электронной таблице Microsoft Excel можно работать не только с числами и текстами, но и с датами. Даты можно сравнивать между собой, складывать и вычитать, а также использовать в других вычислениях. Например, можно вычислить число дней между двумя датами, определить, какой день недели приходился на ту или иную дату, и т.п. Эта статья познакомит вас с соответствующими возможностями популярной офисной программы.
Ввод значений даты
Для того чтобы ввести в ячейку дату, следует указать номер дня, номер месяца и две последние цифры года через точку (12.12.87), дефис (12-12-87) или символ “/” (12/12/87). Можно вводить также первые три буквы названия месяца (12-дек-87
и т.п.; для даты в мае месяце необходимо написать слово май). Текущий год можно не указывать — он будет добавлен к введенной дате автоматически. При вводе значений даты происходит их автоматическое распознавание, и общий формат ячейки 1 заменяется на встроенный формат даты. Так, если ввести, например, значение 12-12-87 или 12 дек 87, то в ячейке отобразится 12.12.87 2 , а в строке формул для данной ячейки будет выведено: 12.12.1987. Но если в ячейке указать 22.10.28, то в строке формул вместо ожидаемой даты 22.10.1928 вы увидите другую — 22.10.2028. Дело в том, что, если при вводе даты указаны только две последние цифры года, Microsoft Excel добавит первые две цифры по следующим правилам:
— если введенное число лежит в интервале от 00 до 29, то оно интерпретируется как год с 2000-го по 2029-й;
— если введенное число лежит в интервале от 30 до 99, то оно интерпретируется как год с 1930-го по 1999-й.
Таким образом фирма Microsoft в свое время позаботилась о переходе в третье тысячелетие. Поэтому года с 1900-го по 1929-й следует указывать полностью.
По умолчанию значения даты выравниваются в ячейке по правому краю. Если не происходит автоматического распознавания формата даты, то введенные значения интерпретируются как текст, который выравнивается в ячейке по левому краю.
Представление дат в ячейках
Формат представления даты в ячейке, отображаемый после ввода значения, может быть изменен с помощью меню, пункт Формат, подпункт Ячейки, вкладка Число, раздел Числовые форматы, пункт Дата. Так, вместо значения 05.12.87 можно получить 5.12.87; 5 дек 87; 5 Декабрь, 1987 и другие представления одной и той же даты. При этом значение даты, отображаемое в строке формул, не меняется (оно не зависит от формата ее представления в ячейке).
Действия с датами
Как уже отмечалось, даты можно складывать и вычитать, сравнивать между собой. Можно также умножать и делить их на числа! Для того чтобы понять, как это реализуется, необходимо разобраться, как хранятся даты в компьютере. Введем в ячейку A1 дату “1 января 1900 года” (напомним, что для этого следует ввести 1-1-1900, а не 1-1-00). С помощью маркера заполнения распространим (скопируем) введенное значение на ячейки A2:A10 (в них появятся даты, в которых будут значения, соответствующие 2, 3, …, 10 января 1900 года). Скопируем блок ячеек A1:A10 в ячейки B1:B10. Изменим формат представления данных в блоке B1:B10 на Общий (Формат | Ячейки | Число | Общий). Мы увидим, что в этом блоке появятся значения 1, 2, …, 10. Из этого можно сделать важный вывод: дата в Excel — это количество дней, прошедших от 1 января 1900 года. Такая форма внутреннего представления дат и позволяет выполнять над ними различные арифметические операции и операции сравнения.
Основные функции для работы с датами
В Excel имеется ряд функций для работы с датами. Рассмотрим некоторые из них.
I. Функции ДЕНЬ, МЕСЯЦ и ГОД
Эти функции возвращают соответственно номер дня в месяце, номер месяца в году и год для некоторой даты.
Их синтаксис: ДЕНЬ(дата), МЕСЯЦ(дата) и ГОД(дата), — где аргумент дата — адрес ячейки, содержащей дату, либо дата, заданная в общем или числовом формате (12345), либо как текст (например, “15-4-93” или “15-Апр-1993”).
День возвращается как целое число в диапазоне от 1 до 31. Месяц определяется как целое в интервале от 1 (Январь) до 12 (Декабрь). Значение года возвращается как целое число в интервале 1900–9999.
1. Если в ячейке А2 указана дата 26.10.49, то ДЕНЬ(А2) равняется 26, МЕСЯЦ(А2) равняется 10, ГОД(А2) равняется 1949.
2. ДЕНЬ(«4-Янв») равняется 4, МЕСЯЦ(«4-Янв») равняется 1.
3. ДЕНЬ(«15-Апр-1993») равняется 15, МЕСЯЦ(«15-Апр-1993») равняется 4, ГОД(«15-Апр-1993») равняется 1993.
4. ДЕНЬ(«11.8.93») равняется 11, МЕСЯЦ(«11.8.93») равняется 8, ГОД(«11.8.93») равняется 1993.
II. Функция ДЕНЬНЕД
Функция возвращает номер дня недели, соответствующий некоторой дате. Ее синтаксис: ДЕНЬНЕД(дата; тип),
— где дата — аргумент, аналогичный используемому в описанных выше функциях;
тип — число, которое определяет вариант возвращаемых значений:
1. Если в ячейке А2 указана дата 26.10.49, то ДЕНЬНЕД(А2) равняется 3 (Среда).
2. ДЕНЬНЕД(«15.2.90») равняется 5 (Четверг).
3. ДЕНЬНЕД(«15.2.90»; 2) равняется 4 (Четверг).
III. Функция СЕГОДНЯ
Функция возвращает дату текущего дня, отслеживаемую компьютером.
Ее синтаксис: СЕГОДНЯ () — без аргументов, но с обязательными скобками.
IV. Функция ДАТА
Функция позволяет “собрать” дату из значений года, номера месяца и номера дня. Ее синтаксис: ДАТА(год; месяц; день), где:
— год — это число от 1900 до 2078;
— месяц — это число, представляющее номер месяца в году;
— день — это число, представляющее номер дня в месяце.
Например, ДАТА(45; 5; 9) есть 9 мая 1945 года.
Задачи для самостоятельной работы
1. В ячейках В2 и В3 получить число 37 135 (указанное число ни в одну из ячеек не вводить):
2. В ячейках А2 и А3 получить число 18 197 (указанное число ни в одну из ячеек не вводить):
3. В ячейке В2 будет записана некоторая дата. Получить в ячейках В3:В5 соответственно номер дня в месяце, номер месяца и год этой даты:
4. Оформите лист для определения года, номера месяца и номера дня рождения:
Искомые значения должны быть получены в ячейках В3:В5.
5. Сотрудники отдела кадров обычно подсчитывают стаж работы на предприятии следующим образом. Выписывается текущая дата в виде 2007, 27 сентября, а под ней — дата начала работы работника на этом предприятии в аналогичном виде. Затем попарно вычитаются значения года, номера месяцев и номера дней в месяце. Например, если работник начал работать на предприятии 19 мая 2003 года, то его стаж работы составляет 4 года 8 месяцев и 6 дней. Оформите лист для расчета стажа работы по описанной методике с использованием данных типа Дата. Принять, что номер месяца и номер текущего дня больше соответствующих значений момента поступления на работу.
6. По дате, указанной в ячейке, определить номер дня недели, на который приходилась эта дата (понедельник — 1, вторник — 2, …, воскресенье — 7).
7. Научный сотрудник забыл точную дату конференции, на которой ему необходимо присутствовать, но помнит, что она должна начаться в четверг в период с 1 по 8 февраля 2008 года. Помогите ему определить точную дату начала конференции.
8. В ячейке В5 будет записана некоторая дата.
В ячейке В3 получить дату дня, который будет через 100 дней после указанной даты:
9. В ячейке В2 будет записана некоторая дата.
В ячейке В3 получить дату дня, который был за 200 дней до указанной даты.
10. В ячейке В2 получить дату текущего дня, в ячейке В4 — номер дня недели (понедельник — 1, вторник — 2, …, воскресенье — 7), который будет через некоторое число дней после текущего дня (это число будет указано в ячейке В3):
11. В ячейке В2 получить дату текущего дня, в ячейке В4 — номер дня недели (понедельник — 1, вторник — 2, …, воскресенье — 7), который был за некоторое число дней до текущего дня (это число будет указано в ячейке В3):
12. В ячейке В2 и В3 будут указаны даты двух событий. Определить, сколько дней прошло между этими событиями.
13. Определите свой возраст в днях и неделях.
14. Для текущей даты вычислить:
а) порядковый номер дня с начала года;
б) сколько дней осталось до конца года.
15. Определить, сколько дней длится первое полугодие года и сколько — второе.
16. В ячейке В2 указана дата некоторого события, произошедшего в первой половине ХХ века. Необходимо в ячейке В3 получить дату дня, до которого от 1 января 1900 года прошло в 2 раза больше дней, чем от 1 января 1900 года до дня данного события.
17. В ячейке В2 запишите дату вашего рождения, а в ячейке В3 получите дату текущего дня. Определить дату того дня, когда число дней вашей жизни станет в 2 раза больше, чем число прожитых дней до текущего дня. Дату получить в формате вида “12 Апрель, 2017”.
18. Известна дата рождения Пети. Определить дату рождения Коли, если известно, что число дней, прожитых им до текущего дня, в 2 раза меньше, чем число дней, прожитых Петей.
19. В ячейке В2 запишите дату вашего рождения, а в ячейке В3 получите дату текущего дня. Определить номера дней недели (понедельник — 1, вторник — 2, …, воскресенье — 7), которые будут, когда число дней вашей жизни станет в 2, 3, 4 и 5 раз больше, чем число прожитых дней до текущего дня.
20. После того, как в ячейки В2 и В3 будут введены даты двух событий, определить, какое событие произошло раньше.
21. Производственное совещание проходит по вторникам и пятницам. Составьте их расписание на февраль 2008 года в виде:
— где в строке 2 в ячейках, соответствующих вторникам и пятницам, должен быть указан какой-нибудь символ (“С”, “+” или т.п.).
22. На листе представлены сведения о дате рождения учеников класса:
В диапазоне ячеек С3:С27 поставить знак “+” для тех учеников, дата рождения которых:
а) приходится на среду;
б) приходится на 10-е число месяца;
в) приходится на август.
23. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который будет через 3 года после этого события.
24. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который был за 5 месяцев до этого события.
25. В ячейке В2 указана дата некоторого события. В ячейке В3 получить дату дня, который будет через n лет, m месяцев и k дней после этого события. Значения n, m и k вводятся в отдельные ячейки.
Решения, пожалуйста, присылайте в редакцию (можно решать не все задачи). Читателям, не имеющим доступа в Интернет, предлагаем в письме указать в соответствующих ячейках необходимые формулы. Фамилии всех приславших ответы будут опубликованы, а лучшие ответы мы поощрим.
1 Если в ячейке не установлен какой-либо специальный формат (числовой, процентный, финансовый, формат дат и т.д.), то данные в ней выводятся в так называемом “общем” формате, используемом для отображения как текстовых, так и числовых значений различного типа.
Как суммировать значения между двумя датами
Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.
Давайте рассмотрим как это сделать.
У нас есть таблица с данными по продажам за каждый день. Для выполнения задачи нам потребуется функция СУММЕСЛИМН.
Как работает функция СУММЕСЛИМН?
Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.
Синтаксис функции выглядит так:
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)
- диапазон_суммирования – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
- диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
- [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;
Формула для суммирования значений между двумя датами
Итак, как я уже писал выше, у нас есть таблица с данными продаж по каждому дню. Наша задача посчитать сумму продаж за период с 1 июня 2018 по 15 июня 2018 года.
Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:
После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.
Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.
Больше лайфхаков в нашем Telegram Подписаться
Как работает эта формула
В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН , которые помогают нам суммировать данные в указанном диапазоне дат.
Давайте разложим формулу на составные части для понимания ее работы:
- Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
- Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
- Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
- Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018
Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.
Как суммировать значения между двумя динамическими датами
На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?
В этом случае нам поможет следующая формула:
Как работает эта формула
В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.
Здесь, в первом критерии для вычисления нужной даты мы указываем функции условие, при котором нужно суммировать данные за период равный или меньше текущей даты.
Во втором критерии мы указываем функции, что нужно суммировать данные больше или равные текущей дате минус 6 дней.
Таким образом, диапазон двух дат в формуле теперь выглядит как: «Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня».
Если у вас остались вопросы по этому примеру оставляйте их в комментариях.
Больше лайфхаков в нашем ВК Подписаться
Оцени запись
Написать комментарий Отменить ответ
Любовь 22.07.2019 в 10:39
Формула работает прекрасно. Спасибо. Ваши примеры значительно облегчают работу. Хотелось бы узнать, каким образом возможно производить одновременно отборку по диапазону и видам товара, проданного в этот период?
Владислав Каманин автор 21.06.2020 в 22:56
Любовь, добрый день, достаточно добавить условий в функцию СУММЕСЛИМН относящиеся к видам товара
Елена 20.08.2019 в 15:31
Спасибо! За формулу и доступное объяснение.
Владислав Каманин автор 15.02.2021 в 23:14
Елена, рад, что статья вам пригодилась!
Владислав 31.01.2020 в 16:09
А есть ли способ, все-таки считать динамически сумму по интервалу, не прибегая к вводу в формулу константных значений и через сегодня() У меня к примеру задача — обращаться к такому же отрезку в прошлом году (понятное дело, можно идентифицировать сами отрезки и их сопоставлять), но возможно есть способ именно к датам привязаться? потому что формула не работает с ссылками на ячейки
Яна 18.06.2020 в 16:58
Владислав, я сама долго искала, как эту формулу привязать к ячейке с датой. И нашла)). Используйте эту формулу из примера в статье: =СУММЕСЛИМН(B2:B18;A2:A18;”=”&СЕГОДНЯ()-6). Только вместо СЕГОДНЯ() вставьте ссылку на ячейку с датой.
Я считала средневзвешенный курс валюты для заданного диапазона дат, т.е. использовала функцию СРЗНАЧЕСЛИМН. Синтаксис аналогичный СУММЕСЛИМН. Моя формула: =СРЗНАЧЕСЛИМН(‘курс валюты’!$C:$C;’курс валюты’!$A:$A;»>=»&A17;’курс валюты’!$A:$A;»<="&B17), где в столбце С — курсы валют на конкретную дату в столбце А, заданный интервал дат: от А17 до В17 на другом листе — т.е. в формуле есть ссылки на конкретные ячейки с датами и они работают. Удачи!
Руслан 05.01.2021 в 17:19
Огромное спасибо. Хотелось бы понять, почему не срабатывает, если знаки =, не заключить в кавычки? Просто я долго бился над этой задачей пока нашел Ваш пример, единственное что я не догадался заключить в кавычки, и так и не понял почему их нужно ограничивать?
Владислав Каманин автор 06.03.2021 в 22:46
Руслан, здравствуйте, в кавычки важно заключать, так как мы строим выражение из двух элементов: знаков и функции СЕГОДНЯ через амперсанд.
Николай 14.01.2021 в 22:00
Спасибо за доступное объяснение как пользоваться формулой.
Остался один вопрос, мне данные выгружаются с датой в формате 2021-01-12T13:01:00, время продаж у всех разное, использую формулу что б разнести продажи по каждому товару по дням, в формате 2021-01-12 даты проблем нет(по апи данные подтягивают формат даиы вместе со временем), но когда присутствует время формула не работает, подскажите пожалуйста как решить эту задачу
Владислав Каманин автор 06.03.2021 в 22:47
Николай, добрый день, важно убедиться, что значение со временем в формате даты, а не текста.
Ученик 22.08.2021 в 13:35
Отлично .спасибо
Владислав Каманин автор 27.08.2021 в 17:54
Рад помочь!
Елена 26.08.2021 в 09:37
А с числовыми данными эта формула работает? Попробовала — вышла ошибка #Знач!
Владислав Каманин автор 27.08.2021 в 17:55
Да, конечно, проверьте вашу дату. Скорее всего она указана в текстовом формате.
алекесандр 16.12.2021 в 10:18
Добрый день Владислав! Интересная статья!
Мне надо посчитать сумму данных за неделю (с пятницы по текущую пятницу) ,для вашего примера я сделал формулу
=СУММЕСЛИМН($B:$B;$A:$A;»=»&СЕГОДНЯ()-6)
Владислав Каманин автор 18.12.2021 в 12:39
Александр, спасибо ��
Олег 04.02.2022 в 13:14
Владислав, спасибо Вам огромное за пример с амперсандом. Не знал, как ячейку с датой привязать к условию. Еще раз огромное спасибо.
Владислав Каманин автор 04.02.2022 в 15:07
Олег, рад помочь!
Болот 15.06.2022 в 07:18
Здравствуйте Владислав.
Подскажите пожалуйста, у меня есть два листа в одной книге. В 1-листе таблица по датам и видам товаров. Во 2-листе я используя вашу формулу беру данные (суммы) сперва по нужному мне товару и в нужной мне промежутке дат. Также вместо указания даты в условии я указал ссылку в ячейки где вводятся нужные даты. Но результат дает 0. Как сделать правильно?
Вот пример формулы: СУММЕСЛИМН(Лист1!2:С28;A2:A28;Лист2!А2;Лист1!B2:B28;”Лист2!>=В1″;В2:В28;”Лист2!<=В2″) Формула стоит в Лист2
Болот 15.06.2022 в 07:43
Вроде нашел причину. Спасибо за ваш труд
Дмитрий 25.09.2022 в 17:11
Добрый день есть такой вопрос, у меня таблица по продаже и закупке NFT рынка и у меня есть много позиций которые не объединены в одну таблицу, Могли бы вы помочь найти ошибку формуле или дать ответ на вопрос (формула) =СУММЕСЛИМН(B12+I12+P12+U12+P22+U22+I22+B28+B20+I30+P32+U32+I40+B38+B46+I48+P42+U42+B54;B12+I12+P12+U12+P22+U22+I22+B28+B20+I30+P32+U32+I40+B38+B46+I48+P42+U42+B54;Y50)
Мне нужна формула которая будет работать как механизм то есть у меня есть итоги за месяца но они привязаны к одной таблице и мне нужно что бы когда бюджет заканчивался и я начинал использовать новый бюджет (Старый бюджет+ Чист прибыль с зароботка), то формула фиксировала заработок и больше не прибавляла новые позиции и т д
Максим 17.11.2022 в 16:45
Подскажите пожалуйста, из-за чего может не работать формула?
=СУММЕСЛИ(ABC!C2:C100;»>=25.10.2022″;ABC!D2:D100), где ABC!C — столбец с датами, а ABC!D — столбец со значениями. когда принудительно сравниваешь дату из таблицы с датой обычной — работает корректно, как только запихиваешь в СУММЕСЛИ — нули. Если убрать условие , оставив только = — работает без проблем.
Полина 03.05.2023 в 19:43
Добрый день. подскажите в чем ошибка =СУММЕСЛИМН(D4:D;A4:A;″>=01.01.2022″;A4:A;″ <=31.12.2022″)
нужно сложить продажи за определенный год, соответственно D4:D это столбец с значением продаж
а столбец A4:A это даты
если я хочу вывести данные за 22 год я ставлю диапазон с 1.01.22 по 31.21.22
но почему то формула пишет Ошибка.Синтаксическая ошибка в формуле. что я делаю не так?
Света 02.08.2023 в 16:48
Не работает, мне не нужно чтобы дата писалась в ручную, мне нужно установить диапазон из ячеек, а такой формат. Это просто ерунда.