Как выбрать последний день выбранных месяцев в ежедневных данных в EXCEL

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

   Date    Variable
30-Mar-2005 1.2943
31-Mar-2005 1.2964
1-Apr-2005  1.2959
4-Apr-2005  1.2883
5-Apr-2005  1.281

IE: на 2005 год мне нужны даты 31 марта 2005 года, 30 июня 2005 года, 30 сентября 2005 года и 30 декабря 2005 года. Желаемый результат:

Date        Variable
31-Mar-2005 1.2964
30-Jun-2005 1.9859
30-Sep-2005 1.2233
30-Dec-2005 1.2814

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

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

Источник

2 ответа

Решение

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

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

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

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

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

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

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

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

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

=MAX(IF(MONTH(IF(YEAR($A$4:$A$4750)=$I$4;$A$4:$A$4750))=3;$A$4:$A$4750))

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

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

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

=VLOOKUP(G5;$A$4:$B$4750;2;false)

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

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

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

Источник

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

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

F2: =IF(EOMONTH($A$45,(ROWS($1:1)-1)*3)>MAX(Table1[Date]),"",LOOKUP(2,1/(EOMONTH($A$45,(ROWS($1:1)-1)*3)>=Table1[Date]),Table1[Date]))

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

G2: =IFERROR(VLOOKUP(F3,Table1,2,FALSE),"")

И заполните, пока не получите пустых мест.

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

Источник

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

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

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

Источник
Другие вопросы по тегам