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

Предлагая способы решения этой проблемы, одним из ключевых моментов в вопросе, на котором я собираюсь сосредоточиться, является то, что Дэвид сказал, что в его данных «есть строки на каждый торговый день». Для меня это означает, что некоторые даты (неторговые дни) не включены в его данные. Это ключевой момент, потому что это означает, что нам не нужно придумывать решение, которое перед принятием решения о том, сохранять ли строку или нет, определяет, является ли дата торговым днем.

На самом деле это значительно упрощает работу. Теперь мы можем просто найти по датам в столбце A те строки, которые фактически содержат последнюю (или «самую высокую») дату в любом заданном месяце. Сначала я сосредоточусь на ручном подходе, основанном на вспомогательном столбце. Поскольку Дэвид сказал, что его данные состоят только из столбцов A (дата) и B (значение), я предлагаю использовать столбец C в качестве вспомогательного столбца. (Я также предполагаю, что в строке 1 есть заголовки столбцов, а настоящие данные начинаются со строки 2.)

Убедившись, что ваши данные отсортированы таким образом, что даты расположены в порядке возрастания, введите следующую формулу в ячейку C2:

=IF(DAY(A3)<DAY(A2),"EOM","X")

Скопируйте эту формулу для всех ваших данных, и вы, по сути, закончили. Теперь при желании вы можете использовать фильтрацию на основе столбца C. Если вы фильтруете так, чтобы отображались только строки, содержащие «EOM», то у вас есть окончательные значения для каждого месяца. Если вы отфильтруете так, чтобы отображались только строки, содержащие «X», вы можете удалить эти строки, удалить фильтр и иметь в данных только строки со значениями на конец месяца.

Как это часто бывает, существует множество формул, которые вы можете использовать в столбце C вместо той, которую я предлагаю. Я предложил это, однако, потому что он делает очень простое сравнение, которое всегда можно будет проверить — «падает» ли значение дня в строке, следующей за текущим. Во всех возможных сценариях это будет верно только в конце месяца. Таким образом, эта строка отмечена «EOM», а остальная — «X».

Я должен указать, что если вы решите использовать другую формулу, убедитесь, что она не проверяет, является ли дата в столбце A последним днем ​​месяца. Зачем? Потому что это может быть не так — помните, что в данных Дэвида столбец A содержит даты торговых дней, и вполне возможно, что последний торговый день месяца может не приходиться на последний день месяца. (Другими словами, выходные и праздничные дни исключены по определению из данных Дэвида.)

Если вы используете Office 365 (или то, что Microsoft в наши дни называет Microsoft 365), вы также можете получить только даты окончания месяца и их значения. Предположим, что ваши данные находятся в формате A2: B5000. (Помните, что A1: B1 содержит заголовки столбцов.) Поместите следующую формулу в ячейку E2:

=FILTER(A2:B5000,DAY(A3:A5001)<DAY(A2:A5000),1)

Это оно; единственная формула в единственной ячейке. Возможно, вам придется отформатировать столбец E для правильного отображения дат, но это использование функции FILTER выполняет то же сравнение, о котором уже говорилось, и извлекает только даты и значения конца месяца. (Если подумать, это довольно хорошо.) Однако помните, что это работает только в Office 365; он не будет работать в Excel 2019, Excel 2016 или любой более ранней версии программы.

Если вы предпочитаете подход на основе макросов, то вам подойдет следующий короткий макрос:

Sub DelRows()

Dim LastRow As Long     Dim J As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row     LastRow = LastRow - 1     For J = LastRow To 2 Step -1         If Month(Cells(J, 1)) = Month(Cells(J + 1, 1)) Then             Rows(J).EntireRow.Delete         End If     Next J End Sub

Макрос определяет последнюю строку на листе (хранится в переменной LastRow), а затем использует цикл For …​ Next для перехода по строкам назад. Если месяц текущей строки равен месяцу следующей строки, то строка удаляется. Обратите внимание, что макрос уменьшает LastRow перед переходом в цикл For …​ Next. Это сделано потому, что предполагается, что последняя строка данных всегда будет последним торговым днем ​​ваших данных, поэтому всегда остается с урезанными данными.

Этот макрос может работать медленно, так как он удаляет большую часть строк на листе одну за другой. Однако, когда он будет завершен, у вас останутся данные только за конец месяца.

И последнее замечание: подходы, использованные в этом совете, за исключением функции ФИЛЬТР, разрушают ваши данные. Когда вы их используете, данные на вашем листе будут потеряны навсегда. Это означает, что вы должны подумать дважды (или трижды), прежде чем запускать их на чем-либо, кроме копии ваших исходных данных.

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (13768) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.