Найти дату в диапазоне дат Excel
Обновлено: 20.11.2024
Примеры показывают, как суммировать суммы или подсчитывать элементы в Excel на основе диапазона дат. Используйте функцию СУММЕСЛИМН и функцию СЧЁТЕСЛИМН для вычисления итогов.
Итого за диапазон дат
В приведенных ниже примерах показано, как суммировать суммы или подсчитывать элементы в Excel на основе диапазона дат. В формулах используются функции, допускающие 2 или более критериев:
- СУММЕСЛИМН — общая сумма по нескольким критериям
- COUNTIFS — общее количество на основе нескольких критериев
Чтобы найти элементы в диапазоне дат, формулам необходимо как минимум 2 критерия:
При необходимости можно добавить дополнительные критерии. См. приведенный ниже пример, в котором используются 3 критерия:
- Дата начала
- Дата окончания
- Название региона
Суммы в диапазоне дат
Чтобы суммировать суммы на основе диапазона дат, вы можете использовать функцию СУММЕСЛИМН. В этом видео показано, как настроить формулу СУММЕСЛИМН, чтобы получить общее количество единиц, проданных за определенный диапазон дат. Письменные шаги находятся под видео.
Поместить даты на лист
Для формул, использующих диапазон дат, лучше всего указывать даты начала и окончания в ячейках листа.
Вы можете ссылаться на эти ячейки в своей формуле, и позже легко изменить диапазон дат, когда вам нужно будет составить отчет за другой период времени.
На снимке экрана ниже даты указаны в голубых ячейках:
- Дата начала указана в ячейке D2.
- Дата окончания указана в ячейке E2.
Эти ячейки использовались в видео выше и в формуле СУММЕСЛИМН в следующем разделе.
Функция СУММЕСЛИМН
Следующий шаг — ввести формулу с функцией СУММЕСЛИМН, чтобы получить общее количество единиц, проданных в диапазоне дат, на основе дат в D2 и E2.
- Числа для суммирования указаны в столбце "Продано единиц" (B)
- Даты для проверки указаны в столбце A.
Для функции СУММЕСЛИМН требуется 3 обязательных аргумента:
- sum_range – ячейки с суммируемыми числами
- criteria_range1 – ячейки для проверки критериев
- criteria1 — критерии соответствия
Необязательные аргументы критериев
При необходимости вы можете добавить дополнительные пары диапазонов критериев и критериев.
- На этом снимке экрана выделен аргумент критерии_диапазона2.
- Эта пара аргументов не является обязательной и обозначается квадратной скобкой.
Формула СУММЕСЛИМН для диапазона дат
Вот формула СУММЕСЛИМН, которая вводится в ячейку D5, чтобы получить общее количество единиц, проданных в диапазоне дат:
Проверить результат формулы СУММЕСЛИМН
Для текущего диапазона дат, с 6 января по 8 марта, общее количество проданных единиц – 375.
Чтобы проверить результат этой формулы, выполните следующие действия:
- Выберите ячейки B3:B5. Эти 3 даты находятся в пределах дат начала и окончания.
- В строке состояния в нижней части Excel проверьте сумму.
- Он должен соответствовать результату формулы в ячейке D5.
Фигуры диапазона сумм и критериев
В формуле СУММЕСЛИМН есть несколько правил для форм диапазона:
- Все диапазоны сумм и критериев должны быть одного размера (одинаковое количество строк и столбцов).
- Каждая сумма или диапазон критериев должны представлять собой непрерывный блок ячеек.
Обычно диапазоны представляют собой вертикальный список или таблицу с одним столбцом и несколькими строками для каждого диапазона.
Также разрешены горизонтальные диапазоны, как в этом списке, где каждый диапазон состоит из одной строки и нескольких столбцов.
Также допустимы диапазоны с несколькими строками И несколькими столбцами, как в этом макете, где каждый диапазон состоит из двух строк и 4 столбцов.
СЧЁТЕСЛИМН – Подсчет в диапазоне дат
Если вам нужен подсчет на основе одного или нескольких критериев, используйте функцию СЧЁТЕСЛИМН.
В этом примере формула будет подсчитывать строки, в которых дата (столбец A) находится в пределах заданного диапазона дат.
- Дата начала указана в ячейке D2.
- Дата окончания указана в ячейке E2.
Функция СЧЁТЕСЛИМН
Для функции СЧЁТЕСЛИМН требуется 2 обязательных аргумента:
- criteria_range1 – ячейки для проверки критериев
- criteria1 — критерии соответствия
Необязательные аргументы критериев
При необходимости вы можете добавить дополнительные пары диапазонов критериев и критериев.
- На этом снимке экрана выделен аргумент критерии_диапазона2.
- Эта пара аргументов не является обязательной и обозначается квадратной скобкой.
Правила диапазона критериев
- Все диапазоны критериев должны быть одного размера (одинаковое количество строк и столбцов).
- Каждый диапазон критериев должен представлять собой непрерывный блок ячеек.
Формула СЧЁТЕСЛИМН для диапазона дат
Вот формула СЧЁТЕСЛИМН, которая вводится в ячейку D5, чтобы получить общее количество единиц, проданных в диапазоне дат:
Проверить результат формулы СЧЁТЕСЛИМН
Для текущего диапазона дат, с 6 января по 8 марта, общее количество заказов – 3.
В этом небольшом примере легко проверить это количество вручную. Чтобы получить больший набор данных, выполните следующие действия:
- Выберите ячейки B3:B5. Эти 3 даты находятся в пределах дат начала и окончания.
- В строке состояния в нижней части Excel проверьте количество или числовое значение.
- Это число должно соответствовать результату формулы в ячейке D5.
Формы диапазона критериев
В формуле СЧЁТЕСЛИМН есть несколько правил для фигур диапазона:
- Все диапазоны критериев должны быть одного размера (одинаковое количество строк и столбцов).
- Каждый диапазон критериев должен представлять собой непрерывный блок ячеек.
Вот диапазоны критериев для формулы "Регион и диапазон дат" в разделе "Дополнительные критерии" ниже. Каждый диапазон состоит из 1 столбца и 8 строк.
Допускаются и другие формы диапазонов, например горизонтальные и блоки с несколькими строками и несколькими столбцами.
Еще один пример COUNTIFS
В этом видео показано, как использовать функцию СЧЁТЕСЛИМН для подсчета ячеек на основе диапазона чисел.
Минимальные и максимальные числа вводятся на листе, и формула аналогична формуле диапазона дат, показанной выше.
Дополнительные критерии для СУММЕСЛИМН или СЧЁТЕСЛИМН
В приведенных выше примерах СУММЕСЛИМН и СЧЁТЕСЛИМН использовались два критерия: дата начала и дата окончания.
При необходимости можно добавить дополнительные критерии, и в этом примере показаны итоги в диапазоне дат для определенного региона.
Следующие критерии вводятся в рабочий лист и упоминаются в формулах:
- Дата начала — ячейка D2
- Дата окончания — ячейка E2
- Название региона — ячейка F2
Вот формула СУММЕСЛИМН из ячейки E5:
- =СУММЕСЛИМН($B$2:$B$9,$A$2:$A$9,">=" & $E$2, $A$2:$A$9," $C$2:$C$9,$G $2 )
А вот и формула СЧЁТЕСЛИМН из ячейки F5:
- =COUNTIFS($A$2:$A$9,">=" & $E$2, $A$2:$A$9," $C$2:$C$9,$G$2)
В обеих формулах последние два аргумента:
- проверьте названия регионов в столбце C
- посмотрите, совпадают ли они с названием региона, введенным в ячейку G2.
Получить образец файла
Чтобы увидеть пример раскрывающегося списка на этой странице, получите книгу "Сумма и количество в диапазоне дат".
Используйте функцию DATEDIF, если вы хотите вычислить разницу между двумя датами. Сначала укажите дату начала в одной ячейке и дату окончания в другой. Затем введите формулу, подобную одной из следующих.
Разница в днях
В этом примере дата начала указана в ячейке D9, а дата окончания — в ячейке E9. Формула находится в F9. «d» возвращает количество полных дней между двумя датами.
Разница в неделях
В этом примере дата начала указана в ячейке D13, а дата окончания — в ячейке E13. «d» возвращает количество дней. Но обратите внимание на /7 в конце. Это делит количество дней на 7, так как в неделе 7 дней. Обратите внимание, что этот результат также необходимо отформатировать как число. Нажмите CTRL + 1. Затем нажмите Число > Десятичные разряды: 2.
Разница в месяцах
В этом примере дата начала указана в ячейке D5, а дата окончания — в ячейке E5. В формуле «m» возвращает количество полных месяцев между двумя днями.
Разница в годах
В этом примере дата начала находится в ячейке D2, а дата окончания — в ячейке E2. «y» возвращает количество полных лет между двумя днями.
Рассчитать возраст в накопленных годах, месяцах и днях
Вы также можете рассчитать возраст или стаж работы. Результатом может быть что-то вроде «2 года, 4 месяца, 5 дней».
1. Используйте DATEDIF, чтобы найти общее количество лет.
В этом примере дата начала указана в ячейке D17, а дата окончания — в ячейке E17. В формуле «y» возвращает количество полных лет между двумя днями.
2. Используйте DATEDIF снова с «ym», чтобы найти месяцы.
В другой ячейке используйте формулу DATEDIF с параметром "ym". «Гм» возвращает количество оставшихся месяцев после последнего полного года.
3. Используйте другую формулу, чтобы найти дни.
Теперь нам нужно найти количество оставшихся дней. Мы сделаем это, написав другую формулу, показанную выше. Эта формула вычитает первый день последнего месяца (01.05.2016) из исходной даты окончания в ячейке E17 (06.05.2016). Вот как это делается: Сначала функция ДАТА создает дату 01.05.2016. Он создается с использованием года в ячейке E17 и месяца в ячейке E17. Тогда 1 представляет первый день этого месяца. Результат функции ДАТА — 01.05.2016. Затем мы вычитаем это из исходной даты окончания в ячейке E17, то есть 06.05.2016. 06.05.2016 минус 01.05.2016 равно 5 дням.
Предупреждение. Мы не рекомендуем использовать аргумент DATEDIF "md", так как это может привести к неточным результатам.
4. Необязательно: объедините три формулы в одну.
Вы можете поместить все три вычисления в одну ячейку, как в этом примере. Используйте амперсанд, кавычки и текст. Это более длинная формула для ввода, но, по крайней мере, все в одном. Совет: нажмите клавиши ALT+ВВОД, чтобы вставить в формулу разрывы строк. Это облегчает чтение. Кроме того, нажмите CTRL+SHIFT+U, если вы не видите всю формулу.
Скачать наши примеры
Вы можете загрузить пример книги со всеми примерами из этой статьи. Вы можете следовать инструкциям или создавать свои собственные формулы.
Другие расчеты даты и времени
Как вы видели выше, функция РАЗНДАТ вычисляет разницу между датой начала и датой окончания. Однако вместо того, чтобы вводить конкретные даты, вы также можете использовать функцию СЕГОДНЯ() внутри формулы. Когда вы используете функцию СЕГОДНЯ(), Excel использует в качестве даты текущую дату вашего компьютера. Имейте в виду, что это изменится, когда файл снова откроется в будущем.
Обратите внимание, что на момент написания этой статьи это было 6 октября 2016 года.
Используйте функцию ЧИСТРАБДНИ.МЕЖД, если вы хотите рассчитать количество рабочих дней между двумя датами. Вы также можете исключить выходные и праздничные дни.
Прежде чем начать: решите, хотите ли вы исключить праздничные дни. Если вы это сделаете, введите список дат праздников в отдельной области или листе. Поместите каждую дату праздника в отдельную ячейку. Затем выберите эти ячейки, выберите «Формулы» > «Определить имя». Назовите диапазон MyHolidays и нажмите OK. Затем создайте формулу, выполнив указанные ниже действия.
1. Введите дату начала и дату окончания.
В этом примере дата начала указана в ячейке D53, а дата окончания — в ячейке E53.
2. В другой ячейке введите следующую формулу:
Введите формулу, как в приведенном выше примере. 1 в формуле определяет субботу и воскресенье как выходные дни и исключает их из общего числа.
Примечание. В Excel 2007 нет функции ЧИСТРАБДНИ.МЕЖД. Однако у него есть ЧИСТРАБДНИ. Приведенный выше пример в Excel 2007 выглядел бы так: =ЧИСТРАБДНИ(D53,E53). Вы не указываете 1, потому что ЧИСТРАБДНИ предполагают, что выходные приходятся на субботу и воскресенье.
3. При необходимости измените 1.
Если суббота и воскресенье не являются вашими выходными днями, измените 1 на другое число из списка IntelliSense. Например, число 2 делает воскресенье и понедельник выходными днями.
Если вы используете Excel 2007, пропустите этот шаг. Функция ЧИСТРАБДНИ Excel 2007 всегда предполагает, что выходные приходятся на субботу и воскресенье.
4. Введите название диапазона праздников.
Если вы создали название диапазона праздников в разделе "Перед началом работы" выше, введите его в конце, как показано ниже. Если у вас нет праздников, вы можете не указывать запятую и MyHolidays. Если вы используете Excel 2007, приведенный выше пример будет таким: =ЧИСТРАБДНИ(D53,E53,MyHolidays).
Совет. Если вы не хотите ссылаться на название диапазона праздников, вы также можете вместо этого ввести диапазон, например D35:E:39. Или вы можете ввести каждый праздник внутри формулы. Например, если ваши праздники приходятся на 1 и 2 января 2016 года, введите их так: =NETWORKDAYS.INTL(D53,E53,1,). В Excel 2007 это будет выглядеть так: =ЧИСТРАБДНИ(D53,E53,)
Вы можете рассчитать прошедшее время, вычитая одно время из другого. Сначала укажите время начала в одной ячейке и время окончания в другой. Обязательно введите полное время, включая часы, минуты, и пробел перед AM или PM. Вот как:
1. Введите время начала и время окончания.
В этом примере время начала указано в ячейке D80, а время окончания — в ячейке E80. Обязательно введите часы, минуты и с пробелом перед AM или PM.
2. Установите формат ч:мм AM/PM.
Выберите обе даты и нажмите CTRL + 1 (или + 1 на Mac). Обязательно выберите «Пользовательский» > «ч:мм AM/PM», если он еще не установлен.
3. Вычтите два раза.
В другой ячейке вычтите ячейку времени начала из ячейки времени окончания.
4. Установите формат ч:мм.
Нажмите CTRL + 1 (или + 1 на Mac). Выберите «Пользовательский» > «ч:мм», чтобы результат не включал AM и PM.
Чтобы вычислить время между двумя датами и временем, вы можете просто вычесть одно из другого. Однако вы должны применить форматирование к каждой ячейке, чтобы Excel вернул нужный результат.
1. Введите две полные даты и время.
В одной ячейке введите полную дату/время начала. А в другой ячейке введите полную дату/время окончания. В каждой ячейке должны быть указаны месяц, день, год, час, минута, и пробел перед AM или PM.
2. Установите формат 14.03.12 13:30.
Выделите обе ячейки и нажмите CTRL + 1 (или + 1 на Mac). Затем выберите Дата > 14.03.12 13:30. Это не дата, которую вы установите, это просто пример того, как будет выглядеть формат.Обратите внимание, что в версиях, предшествующих Excel 2016, этот формат может иметь другую примерную дату, например 14 марта 2001 г., 13:30.
3. Вычтите два.
В другой ячейке вычтите дату/время начала из даты/времени окончания. Результат, вероятно, будет выглядеть как число и десятичная дробь. Вы исправите это на следующем шаге.
4. Установите формат [ч]:мм.
Нажмите CTRL + 1 (или + 1 на Mac). Выберите Пользовательский. В поле Тип введите [ч]:мм.
Бывают случаи, когда нам нужно выполнять различные операции, такие как сложение или вычитание значений даты в Excel. Установив диапазоны дат в Excel, мы можем выполнять вычисления по этим датам. Для настройки диапазонов дат в Excel мы можем сначала отформатировать ячейки с датами начала и окончания как «Дата», а затем использовать операторы: «+» или «-», чтобы определить дату окончания или продолжительность диапазона.
Примеры диапазона дат в Excel
Давайте посмотрим, как добавление числа к дате создает диапазон дат.
Предположим, у нас есть дата начала в ячейке A2.
Теперь, если мы добавим к нему число, скажем, 5, мы сможем построить диапазон дат.
Теперь, когда мы выбираем ячейку A3 и вводим ‘=B2 + 1’.
Итак, мы видим, что таким образом можно построить несколько диапазонов дат.
В Excel мы можем легко создать несколько последовательностей. Теперь мы знаем, что даты — это числа в Excel. Таким образом, мы можем использовать тот же метод для создания диапазонов дат. Таким образом, чтобы создать диапазоны дат с одинаковым диапазоном или промежутком, но даты меняются по мере продвижения вниз, мы можем выполнить следующие шаги:
Итак, мы видим, что, используя диапазоны дат в первых двух строках в качестве шаблона, Excel автоматически создает диапазоны дат для последующих строк.
Теперь предположим, что у нас есть две даты в двух ячейках, и мы хотим отобразить их объединенными в виде диапазона дат в одной ячейке. Для этого можно использовать формулу, основанную на функции «ТЕКСТ». Общий синтаксис этой формулы следующий:
= ТЕКСТ(дата1,"формат") & ” – ” & ТЕКСТ(дата2,"формат")
Случай 1:
Эта функция получает два числовых значения даты и объединяет эти две даты в форме диапазона дат в соответствии с пользовательским форматом даты (в данном случае "ммм д"):
Диапазон дат = ТЕКСТ(A2,"ммм d") и "-" и ТЕКСТ(B2,"ммм d")
Итак, на снимке экрана выше мы видим, что мы применили формулу в ячейке C2. Функция ТЕКСТ получает даты, хранящиеся в ячейках A2 и B2, оператор амперсанда «&» используется для объединения двух дат в виде диапазона дат в пользовательском формате, указанном в данном случае как «ммм д», в одной ячейке и две даты соединяются дефисом "-" в результирующем диапазоне дат, который определяется в ячейке C2.
Случай 2:
Теперь, допустим, в этом примере мы хотим объединить две даты в виде диапазона дат в одной ячейке с другим форматом, скажем, «д ммм гг». Таким образом, формула для диапазона дат в этом случае будет выглядеть следующим образом:
Диапазон дат = ТЕКСТ (A3, "д ммм, гг") & "-" и ТЕКСТ (B3, "д ммм, гг")
На приведенном выше снимке экрана видно, что функция ТЕКСТ получает даты, хранящиеся в ячейках A3 и B3, оператор амперсанд '&' используется для объединения двух дат в виде диапазона дат в пользовательском формате, указанном как « d mmm yy» в данном случае в одной ячейке. Две даты соединяются дефисом "-" в результирующем диапазоне дат, указанном в ячейке C3.
Теперь давайте посмотрим, что произойдет в случае отсутствия даты начала или окончания. Предположим, что дата окончания отсутствует следующим образом:
Формула, основанная на функции ТЕКСТ, которую мы использовали выше, не будет работать правильно, если отсутствует дата окончания, так как дефис в формуле в любом случае будет добавлен к дате начала, т. е. вместе с датой начала, мы также увидим дефис, отображаемый в диапазоне дат. Напротив, мы хотели бы видеть дату начала в качестве диапазона дат только в том случае, если дата окончания отсутствует.
Таким образом, в этом случае мы можем получить формулу, заключив конкатенацию и вторую функцию TEXT в предложение IF следующим образом:
Диапазон дат = ТЕКСТ (A2, "ммм d") и ЕСЛИ (B2<> "", "-" и ТЕКСТ (B2, "ммм d"), "")
Итак, мы видим, что приведенная выше формула создает полный диапазон дат, используя обе даты, когда они обе присутствуют. Однако он отображает только дату начала в указанном формате, если дата окончания отсутствует. Это делается с помощью предложения IF.
Диапазон дат = ЕСЛИ (A4<>», ТЕКСТ (A4», «ммм d») и ЕСЛИ (B4<> «», «-» и ТЕКСТ (B4», «ммм d»), «»)), "")
Итак, мы видим, что приведенная выше формула возвращает пустую строку, если дата начала отсутствует.
Если обе даты отсутствуют, также возвращается пустая строка.
Что нужно помнить
- Мы даже можем создать список последовательных дат, используя команду "Fill Handle". Для этого мы можем выбрать ячейку с датой начала, а затем перетащить ее в диапазон ячеек, которые мы хотим заполнить. Нажмите на вкладку "Главная" -> "Редактирование" -> "Заполнить серию", а затем выберите единицу измерения даты, которую мы хотим использовать.
- Если мы хотим рассчитать продолжительность или количество дней между двумя датами, мы можем просто вычесть две даты, используя оператор «-», и мы получим желаемый результат.
Примечание. Формат ячеек: A2 и B2 – "Дата", тогда как формат ячейки C2 – "Общий", поскольку в ней подсчитывается количество дней.
Рекомендуемые статьи
Это руководство по диапазону дат в Excel. Здесь мы обсуждаем формулы для создания диапазона дат в Excel различными способами вместе с загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей –
Вы можете выполнять аналогичные операции с датами и числами, потому что даты в Excel — это не что иное, как числа в формате даты.
Вы можете выполнять сложение, вычитание и операции над диапазонами.
В этом уроке вы узнаете, как работать с диапазонами дат.
Диапазон дат в одной ячейке
Чтобы отобразить две даты в виде диапазона дат внутри одной ячейки, необходимо использовать функцию ТЕКСТ. Он преобразует значение в текст в указанном формате.
Дата в этом примере записывается в следующем формате: гггг-мм-дд.
Давайте создадим диапазон дат в этом формате и вставим эту формулу в ячейку D2:
Заполните две оставшиеся ячейки, чтобы получить диапазоны для всех дат.
Проблема с этим форматом заключается в том, что он содержит дефисы внутри дат, которые плохо смотрятся с дефисами, представляющими диапазон. Это обозначение также является слишком широким. Мы должны сделать его уже.
Мы собираемся использовать американский формат даты MDY, где год занимает два места вместо четырех. Давайте также заменим тире на косую черту.
Вот как это выглядит после модификации.
Конечно, вы можете сделать его еще более узким, если решите избавиться от дня или года в обозначениях.
Создать список последовательных дат
Как я упоминал ранее, даты — это просто числа, и вы можете создать список последовательных чисел. Таким же образом вы можете создать список последовательных дат.
Самый простой способ сделать это – ввести дату начала и перетащить столько позиций, сколько хотите. Вы можете сделать это как вертикально, так и горизонтально.
Каждая из этих дат отличается на день, поскольку 1 соответствует 1 дню в Excel.
Есть вещь, которую вы должны помнить. Я сказал, что вы можете создавать последовательные даты так же, как и числа.
Есть небольшая разница. Эта таблица иллюстрирует это.
| Числа | Даты | |
| Перетаскивание | Копировать | Создать последовательные значения |
| Ctrl + Drag | Создать последовательные значения | Копировать |