حذف همه ردیف ها به جز پایان ماه

Question

دیوید یک کاربرگ دارد که فقط شامل دو ستون است: تاریخ و ارزش نمونه کارها برای هر تاریخ. کاربرگ دارای ردیف هایی برای هر روز معاملاتی از ژانویه 1999 تا کنون است. دیوید باید همه ردیف ها را حذف کند، به جز ردیف هایی که مربوط به آخرین روز معاملاتی هر ماه هستند. او فیلتر کردن را امتحان کرد، اما این کمکی نکرد، بنابراین او در مورد بهترین راه برای حذف ردیف های غیرضروری سردرگم است.

در ارائه راه هایی برای مقابله با این مشکل، یکی از کلیدهای سوالی که قرار است روی آن تمرکز کنم این است که دیوید گفته است که داده های او "برای هر روز معاملاتی ردیف هایی دارند." به نظر من این بدان معناست که برخی از تاریخ ها (روزهای غیرمعامله) در داده های او لحاظ نشده است. این نکته کلیدی است زیرا به این معنی است که ما نیازی به ارائه راه حلی نداریم که قبل از تصمیم گیری در مورد حفظ یا عدم حفظ یک ردیف، تعیین کند که آیا تاریخ یک روز معاملاتی است یا خیر.

این در واقع کار را بسیار آسان تر می کند. اکنون می توانیم به سادگی، از تاریخ های ستون A، آن ردیف هایی را پیدا کنیم که در واقع حاوی آخرین (یا "بالاترین") تاریخ در هر ماه هستند. من ابتدا روی یک رویکرد دستی که بر ستون کمکی متکی است تمرکز خواهم کرد. از آنجایی که دیوید گفت داده های او فقط از ستون های A (تاریخ) و B (مقدار) تشکیل شده است، پیشنهاد می کنم از ستون C به عنوان ستون کمکی استفاده کنید. (همچنین فرض می کنم که سطر 1 دارای سرفصل های ستونی است و داده های واقعی از ردیف 2 شروع می شوند.)

برای اطمینان از اینکه داده های شما مرتب شده اند تا تاریخ ها به ترتیب صعودی باشند، فرمول زیر را در سلول C2 قرار دهید:

=IF(DAY(A3)این فرمول را برای تمام داده های خود کپی کنید، و اساسا کار شما تمام شده است. اکنون می توانید، در صورت تمایل، از فیلتر بر اساس ستون C استفاده کنید. اگر فیلتر کنید تا فقط ردیف های حاوی "EOM" نشان داده شوند، مقادیر نهایی خود را برای هر ماه خواهید داشت. اگر فیلتر کنید تا فقط ردیف های حاوی «X» نشان داده شوند، می توانید آن ردیف ها را حذف کنید، فیلتر را حذف کنید و فقط ردیف هایی با مقادیر پایان ماه در داده های خود داشته باشید.همانطور که اغلب اتفاق می افتد، تعداد زیادی فرمول وجود دارد که می توانید در ستون C به جای فرمول پیشنهادی من استفاده کنید. با این حال، من این مورد را پیشنهاد دادم، زیرا یک مقایسه بسیار ساده انجام می دهد که همیشه قابل آزمایش خواهد بود - اینکه آیا ارزش روز در ردیف بعد از روز فعلی کاهش می یابد. در هر سناریوی ممکن، این تنها در پایان یک ماه صادق خواهد بود. بنابراین، آن ردیف با "EOM" و بقیه با "X" مشخص شده است.باید اشاره کنم که اگر می خواهید از فرمول دیگری استفاده کنید، مطمئن شوید که آیا تاریخ ستون A آخرین روز ماه است یا خیر. چرا؟ زیرا ممکن است اینطور نباشد - به یاد داشته باشید که در داده های دیویدز، ستون A حاوی تاریخ روزهای معاملاتی است و ممکن است آخرین روز معاملاتی یک ماه در آخرین روز ماه نباشد. (به عبارت دیگر تعطیلات آخر هفته و تعطیلات طبق تعریف از داده های دیویدز مستثنی هستند.)اگر از آفیس 365 استفاده می کنید (یا چیزی که مایکروسافت این روزها از آن به عنوان مایکروسافت 365 یاد می کند)، همچنین راهی وجود دارد که می توانید فقط تاریخ های پایان ماه و مقادیر آنها را مشخص کنید. فرض کنید اطلاعات شما در A2:B5000 است. (به یاد داشته باشید که A1:B1 حاوی سرصفحه های ستون است.) فرمول زیر را در سلول E2 قرار دهید:=FILTER(A2:B5000,DAY(A3:A5001)خودشه؛ یک فرمول واحد در یک سلول واحد ممکن است مجبور شوید ستون E را قالب بندی کنید تا تاریخ ها به درستی نمایش داده شود، اما این استفاده از تابع FILTER همان مقایسه ای را انجام می دهد که قبلاً بحث شد و فقط تاریخ ها و مقادیر پایان ماه را نشان می دهد. (اگر در مورد آن فکر کنید این کاملاً نرم است.) به یاد داشته باشید که فقط در Office 365 کار می کند. در اکسل 2019، اکسل 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 کاهش می دهد. این کار به این دلیل انجام می شود که فرض بر این است که آخرین ردیف داده همیشه آخرین روز معاملاتی داده های شما خواهد بود، بنابراین همیشه با داده های حذف شده باقی می ماند.

اجرای این ماکرو می تواند کند باشد، زیرا بیشتر سطرهای کاربرگ را یکی یکی حذف می کند. با این حال، هنگامی که کامل شد، فقط داده های پایان ماه خود را خواهید داشت.

یک نکته پایانی - رویکردهای استفاده شده در این نکته، به استثنای تابع FILTER، داده های شما را مخرب می کند. هنگامی که از آنها استفاده می کنید، داده های کاربرگ شما برای همیشه از بین می روند. این بدان معنی است که قبل از اجرای آنها روی هر چیزی به جز کپی از داده های اصلی خود، باید دو بار (یا سه بار) فکر کنید.