Как выбрать последнюю дату в excel

Обновлено: 06.07.2024

Формулы для определения первого и последнего дней месяца от текущей или произвольной даты. Использовать можно в шаблонах различных документов и отчетов, при заполнении которых требуется отобразить интервал дат с участием первого и(или) последнего дня месяца.

Первый день месяца в Excel

Для формулы, преобразующей текущую или другую дату в первый день месяца, используем функции «ДАТА», «ГОД» и «МЕСЯЦ»:

Первый день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)
Первый день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())-1;1)
Первый день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ());1)
Первый день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1);1)

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

Последний день месяца в Excel

Для определения последнего месяца от текущей или произвольной даты в Excel, начиная с версии 2007 года, существует функция «КОНМЕСЯЦА» с двумя аргументами: «Начальная дата» и «Количество месяцев». Если «Количество месяцев» равно 0, то будет возвращен последний день того месяца, который содержится в аргументе «Начальная дата».

Примеры для Excel 2007-2016:

Последний день текущего месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();0)
Последний день прошлого месяца от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-1)
Последний день текущего месяца предыдущего года от текущей даты: =КОНМЕСЯЦА(СЕГОДНЯ();-12)
Последний день месяца от произвольной даты, записанной в ячейку «A1»: =КОНМЕСЯЦА(A1;0)

Функция «КОНМЕСЯЦА» возвращает значение в числовом виде, поэтому для правильного отображения последнего дня месяца следует к ячейке с формулой применить формат даты.

В версиях Excel по 2003 год функция «КОНМЕСЯЦА» по-умолчанию отсутствует, поэтому будем применять формулу, составленную, как и для первого дня месяца, из функций «ДАТА», «ГОД» и «МЕСЯЦ».

Примеры для Excel 2000-2003:

Последний день текущего месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;0)
Последний день прошлого месяца от текущей даты: =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());0)
Последний день текущего месяца предыдущего года от текущей даты: =ДАТА(ГОД(СЕГОДНЯ())-1;МЕСЯЦ(СЕГОДНЯ())+1;0)
Последний день месяца от произвольной даты, записанной в ячейку «A1»: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;0)

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

Как вариант, для определения последнего дня месяца можно использовать формулу, возвращающую первый день следующего месяца, из значения которой вычитается единица: =ДАТА(ГОД(A1);МЕСЯЦ(A1)+1;1)-1

Определение первого и последнего дня месяца имеет большую актуальность для автоматического заполнения интервала дат в диалоговых формах с помощью VBA Excel.

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

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

Подсчет дат, когда критерий сформулирован в виде текста. На рис. 12.1 показан набор данных с датами в стандартном формате Excel, то есть в виде порядковых чисел. В тоже время, критерии заданы как число (год) и текст (месяц). Цель – подсчитать, сколько дат соответствуют критерию. Проблема в том, что у нас несоответствие формата данных: в столбце A даты как порядковые номера, а критерий – смесь чисел и текста. На рис. 12.1 приведено пять различных формул, которые можно использовать для достижения цели.

Рис. 12.1. Подсчет количества дат (заданных порядковыми номерами) по двум критериям: году (число) и месяцу (текст)

Давайте подробнее изучим работу этих пяти формул.

Если вы можете позволить себе вспомогательный столбец, функция СЧЁТЕСЛИ будет самым простым решением.
Функция МЕСЯЦ возвращает число между 1 и 12, а функция ГОД – число (год).
Хотя Excel требует, чтобы аргумент функции МЕСЯЦ был представлен датой в числовом формате, этот аргумент может распознать и текст. Однако МЕСЯЦ(Окт) вернет ощибку, а вот если добавить к названию месяца любое число, например, 1, то Excel справится. Используйте, как в формуле выражение Окт1, заданное фрагментом F8&1, или 1Окт, заданное фрагментом 1&F8.
Формулы с вспомогательными столбцами как правило работают быстрее.

Если у вас Excel 2007 или более поздний, вы можете использовать функции СЧЁТЕСЛИМН и КОНМЕСЯЦА.
Вам даны год (в виде числа) и месяц (как текст). Это означает, что вы можете вычислить дату начала и конца месяца, а затем определить даты, попажающие между ними.
Месяц всегда начинается с первого числа, так что вы можете создать нижнюю границу диапазона конкатенацией: " >=1 " &F8&E8. Операции конкатенации возвращают текст, но это не страшно, т.к. функция СУММЕСЛИМН понимает даты в виде текста.
Вы используете функцию КОНМЕСЯЦА с аргументом число_месяцев равным нулю; это позволяет получить последнюю дату текущего месяца. Функция КОНМЕСЯЦА является динамической: она возвращает 28 или 29 для февраля и 30 или 31 для любого другого месяца.
Эта формула является самой быстрой, если вам нужно получить решение в одной ячейке.

Если у вас Excel версии младше 2007 г., вы можете использовать две функции СЧЁТЕСЛИ, одну – для верхнего диапазона, вторую – для нижнего. Фокус в том, чтобы сначала сосчитать все значения, которые равны или меньше верхней границы, а затем вычесть все значения, которые меньше нижней границы.
В Excel 2003 или более ранней, чтобы добавить функцию КОНМЕСЯЦА, вам нужно выбрать Инструменты → Надстройки → Анализ Данных.
Эта формула работает быстрее, чем формулы [4] и [5].

Функции МЕСЯЦ и ГОД возвращают числа, извлекая их из порядкового номера даты.
Далее сравниваются два фрагмента, каждый полкченный конкатенацией.

Функция ТЕКСТ используется для представления чисел в виде текста. Второй аргумент этой функции – формат – определяет, как будет представлено число. Вы может конвертировать весь столбец А в текст, состоящий из 7 символов: 3 буквы месяца и 4 цифры года.

Нахождение объема продаж за год. На рис. 12.4 показан пример несоответствие формата года в критерии Е6 (число) и формата дат в диапазоне А2:А6 (порядковый номер). Цель – найти сумму продаж за год. На рисунке представлены шесть вариантов формул, которые могут решить задачу. Обратите внимание, что в формулах [1] и [2] критерии начала и конца года жестко зашиты в коде, т.к. они не могут изменяться. Это 1/1 и 31/12). Формулы размещены на рисунке в порядка увеличения скорости работы.

Рис. 12.4. Формата года в критерии Е6 (число) не соответствует формату дат в диапазоне А2:А6 (порядковый номер)

Все классические функции поиска и подстановки типа ВПР (VLOOKUP) , ГПР (HLOOKUP) , ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.

Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?

Способ 1. Поиск последней строки формулой массива

Если в исходной таблице нет столбца с датой или порядковым номером строки (заказа, платежа. ), то наша задача сводится, по сути, к поиску последней строки, удовлетворяющей заданному условию. Реализовать подобное можно вот такой формулой массива:

Функция ЕСЛИ(IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА(ROW) , но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
Затем функция МАКС(MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
Функция ИНДЕКС(INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).

Всё это нужно вводить как формулу массива, т.е.:

В Office 365 с последними установленными обновлениями и поддержкой динамических массивов - можно просто жать Enter .
Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl + Shift + Enter , что автоматически добавит к ней фигурные скобки в строке формул.

Способ 2. Обратный поиск новой функцией ПРОСМОТРХ

Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP) , которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP) . При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх - за это отвечает её последний аргумент (-1):

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

Если в исходных данных у нас есть столбец с порядковым номером или датой, играющей аналогичную роль, то задача видоизменяется - нам требуется найти уже не последнюю (самую нижнюю) строку с совпадением, а строку с самой поздней (максимальной) датой.

Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .

С их помощью этой "убойной парочки" наша задача решается весьма изящно:

Сначала функция ФИЛЬТР(FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент - нужное нам имя.
Потом функция СОРТ(SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
Функция ИНДЕКС(INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант >, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.

Способ 4. Поиск последнего совпадения в Power Query

Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.

1 . Преобразуем нашу исходную таблицу в "умную" с помощью сочетания клавиш Ctrl + T или команды Главная - Форматировать как таблицу (Home - Format as Table) .

2 . Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range) .

3 . Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние сделки оказались сверху.

4 . На вкладке Преобразование выбираем команду Группировать по (Transform - Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows) . Назвать новый столбец можно как угодно - например Подробности.

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

5 . Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column - Add custom column) и вводим следующую формулу:

Здесь Подробности - это столбец, откуда мы берем таблицы по клиентам, а - это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями ( Record ), где каждая запись - первая строка из каждой таблицы:

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

. и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши - Удалить столбцы (Remove columns) .

После выгрузки результатов на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to. ) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:

При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши - команда Обновить (Refresh) или сочетанием клавиш Ctrl + Alt + F5 .

У меня есть ежедневные данные с 1 января 2005 г. по 29 декабря 2017 г. Я хочу для каждого года выбирать последний день марта, июня, сентября и декабря вместе с соответствующими данными. Часть данных:

И.Е .: На 2005 год мне нужны даты 31 марта 2005 года, 30 июня 2005 года, 30 сентября 2005 года и 30 декабря 2005 года. Желаемый вывод:

В настоящее время у меня есть сборка в формулах Excel (я не установил никаких других плагинов и т. Д.).

Более конкретно: слева у меня есть данные, а справа желаемый вывод.

3 ответа

Не уверен, что это сработает для вас, но в любом случае.

Похоже, вы всегда смотрите на последний день месяца март, июнь, сентябрь и декабрь определенного года (например, 2005).

Но вы не ищете последний естественный день каждого месяца. Вам нужен последний день каждого месяца, который появляется в ваших данных (например, это объясняет, почему вы используете 30 декабря 2005 года вместо 31, потому что 31 нет).

В Excel даты - это числа. Чем больше вы идете в будущем, тем большее число связано. Зная это, вы можете получить дату для каждого месяца , просто просматривая МАКСИМАЛЬНОЕ значение диапазона дат.

Но сначала вам нужно определить диапазон дат, используя 2 условия:

Месяц даты должен быть март, июнь, сентябрь и декабрь
Вы хотите даты для определенного года (например, 2005).

Чтобы получить это, вам нужна формула массива. Моя формула получает максимальный день определенного месяца и года. Чтобы проверить это, в моем Excel я сделал серию дат, начиная с 01.01.2005 и до 31.12.2017. Я удалил вручную 31/12/2005, потому что у этой даты нет данных.

В ячейке I4 просто введите год, который вы хотите проверить. Формула получит он в последний день месяца март, июнь, сентябрь и декабрь этого года.

Моя формула массива:

ВАЖНО! . Поскольку это формула массива, вам необходимо ввести ее как обычно, а затем вместо нажатия Enter нажмите CTRL + SHIFT + ENTER

Вам нужно 4 раза эту формулу. Просто измените 3 (март) на номер нужного вам месяца (6,9 и 12).

Теперь, когда у вас есть даты, вам просто нужно VlookUp , чтобы получить желаемое значение.

Если я изменю значение года, я получу эти новые значения:

Если вы хотите проверить файл. Я загрузил пример в Gdrive, поэтому вы можете скачать его, если хотите. Загрузить

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

Я преобразовал список конверсий в евро в таблицу и использовал структурированные ссылки. Но вы можете использовать нормальные ссылки диапазона, если вы предпочитаете.

В какой-то другой таблице введите следующую формулу, где $ 45 $ относится к дате окончания первого квартала в вашей таблице данных.

В соседнем столбце введите формулу:

И заполнять, пока не получите пробелы.

(в моей выборочной таблице последняя дата - 27.01.2006, поэтому последняя включенная «Дата окончания» - 30.12.2005, данные на 31.12.2005 отсутствуют)

Итак, есть два случая

Когда у вас есть точные последние дни месяца. (Это проще).
У меня были данные за две недели. Я принял этот простой и инновационный метод. Из всех дат я сначала извлек день, используя функцию Day() . например, Day(A1) . (Помните, что при этом не удаляйте исходный столбец даты. Сделайте это в отдельном столбце, так как это поможет вам сопоставить даты позже).
Затем я отсортировал данные, используя только что построенный столбец Day, в порядке убывания. Это поместило бы все даты окончания сначала. А затем удалили даты начала, которые были внизу. Итак, теперь у меня остались только даты окончания, но, очевидно, месяцы не в порядке.
Итак, создайте другой столбец, извлекая только месяц и год из исходного столбца даты, используя =MONTH(A1) & "/" & YEAR(A1) . Сортировать данные, используя этот столбец. И ты в порядке!

Если у вас нет точных последних дней, но максимальные даты, как показано на рисунке выше.
При этом при удалении начальных дат вам нужно будет позаботиться о том, какие даты нужно удалить.
Например, я удалил 17-й день месяца с 31 днями (включая 17-й день) и 16-й день месяца с 30 днями (если он есть), потому что, если была эта дата, предположим, 18 апреля 2018 года, то это будет последний день месяца, так как у меня были данные за две недели.

Читайте также: