David에는 각 날짜에 대한 날짜와 포트폴리오 값이라는 두 개의 열만 포함 된 워크 시트가 있습니다. 워크 시트에는 1999 년 1 월부터 현재까지의 모든 거래일에 대한 행이 있습니다. David는 매월 마지막 거래일을 제외한 모든 행을 삭제해야합니다. 그는 필터링을 시도했지만 도움이되지 않았으므로 불필요한 행을 삭제하는 가장 좋은 방법에 대해 혼란스러워합니다.

이 문제를 해결하는 방법을 제공 할 때, 제가 집중할 질문의 핵심 중 하나는 David가 자신의 데이터가 “매일 거래일마다 행이있다”고 말했다는 것입니다. 나에게 이것은 일부 날짜 (비 거래일)가 그의 데이터에 포함되지 않음을 의미합니다. 이것은 행을 유지할지 여부를 결정하기 전에 날짜가 거래 일인지 결정하는 솔루션을 찾을 필요가 없음을 의미하기 때문에 중요합니다.

이것은 실제로 작업을 훨씬 쉽게 만듭니다. 이제 A 열의 날짜에서 특정 월의 마지막 (또는 “가장 높은”) 날짜를 실제로 포함하는 행을 간단히 찾을 수 있습니다. 먼저 도우미 열에 의존하는 수동 접근 방식에 초점을 맞출 것입니다. David는 그의 데이터가 A (날짜) 열과 B (값) 열로만 구성되어 있다고 말 했으므로 C 열을 도우미 열로 사용하는 것이 좋습니다. (또한 행 1에 열 머리글이 있고 실제 데이터는 행 2에서 시작한다고 가정하겠습니다.)

날짜가 오름차순이되도록 데이터가 정렬되었는지 확인하고 다음 수식을 셀 C2에 입력합니다.

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

모든 데이터에 대해이 공식을 복사하면 기본적으로 완료됩니다. 이제 원하는 경우 C 열을 기준으로 필터링을 사용할 수 있습니다. “EOM”이 포함 된 행만 표시되도록 필터링하면 매월 최종 값을 갖게됩니다. ‘X’가 포함 된 행만 표시되도록 필터링하면 해당 행을 삭제하고 필터를 제거하고 데이터에 월말 값이있는 행만 포함 할 수 있습니다.

종종 그렇듯이 내가 제안하는 것 대신 열 C에서 사용할 수있는 수식이 너무 많습니다. 그러나 저는이 방법을 제안했습니다. 왜냐하면 그것은 항상 테스트 할 수있는 매우 간단한 비교를 만들기 때문입니다.-그 날이 현재의 다음 행에서 가치가 “떨어지 느냐”. 가능한 모든 시나리오에서 이것은 월말에만 해당됩니다. 따라서 해당 행은 “EOM”으로 표시되고 나머지는 “X”로 표시됩니다.

다른 수식을 사용하기로 선택한 경우 A 열의 날짜가 해당 월의 마지막 날인지 테스트하지 않는 수식인지 확인해야합니다. 왜? 그렇지 않을 수도 있기 때문에 David의 데이터에서 A 열에는 거래일이 포함되어 있으며 한 달의 마지막 거래일이 해당 월의 마지막 날이 아닐 수도 있다는 점을 기억하십시오. (즉, 주말과 공휴일은 정의에 따라 David의 데이터에서 제외됩니다.)

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 루프를 사용하여 행을 뒤로 이동합니다. 현재 행의 월이 다음 행의 월과 같으면 행이 삭제됩니다. 매크로는 For …​ Next 루프로 이동하기 전에 LastRow를 감소시킵니다. 이것은 데이터의 마지막 행이 항상 데이터의 마지막 거래일이 될 것이라는 가정 때문에 수행되므로 항상 제거 된 데이터와 함께 유지됩니다.

이 매크로는 워크 시트에서 대부분의 행을 하나씩 삭제하므로 실행 속도가 느릴 수 있습니다. 그러나 완료되면 월말 데이터 만 남게됩니다.

마지막으로,이 팁에서 사용 된 접근 방식은 FILTER 함수를 제외하고 데이터를 파괴한다는 것입니다. 이를 사용하면 워크 시트의 데이터가 영원히 손실됩니다. 즉, 원본 데이터의 복사본을 제외하고는 실행하기 전에 두 번 (또는 세 번) 생각해야합니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶으면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13768)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.