David có một trang tính chỉ bao gồm hai cột: ngày tháng và giá trị danh mục đầu tư cho mỗi ngày. Trang tính có các hàng cho mỗi ngày giao dịch từ tháng 1 năm 1999 đến nay. David cần xóa tất cả các hàng ngoại trừ những hàng dành cho ngày giao dịch cuối cùng của mỗi tháng. Anh ấy đã thử lọc, nhưng điều đó không giúp được gì, vì vậy anh ấy bối rối về cách tốt nhất để xóa các hàng không cần thiết.

Khi đưa ra các cách để giải quyết vấn đề này, một trong những chìa khóa trong câu hỏi mà tôi sẽ tập trung là David cho biết dữ liệu của anh ấy “có hàng cho mỗi ngày giao dịch.” Đối với tôi, điều này có nghĩa là một số ngày (ngày không giao dịch) không được bao gồm trong dữ liệu của anh ấy. Đây là điều quan trọng vì nó có nghĩa là chúng ta không cần phải đưa ra giải pháp mà trước khi quyết định có giữ hàng hay không, hãy xác định xem một ngày có phải là ngày giao dịch hay không.

Điều này thực sự làm cho công việc dễ dàng hơn nhiều. Bây giờ chúng ta có thể đơn giản tìm thấy, từ các ngày trong cột A, những hàng thực sự chứa ngày cuối cùng (hoặc “cao nhất”) trong bất kỳ tháng nhất định nào. Đầu tiên tôi sẽ tập trung vào cách tiếp cận thủ công dựa vào cột trợ giúp. Vì David cho biết dữ liệu của anh ấy chỉ bao gồm các cột A (ngày) và B (giá trị), tôi sẽ đề xuất sử dụng cột C làm cột trợ giúp. (Tôi cũng sẽ giả sử rằng hàng 1 có tiêu đề cột trong đó và dữ liệu thực bắt đầu ở hàng 2.)

Đảm bảo rằng dữ liệu của bạn được sắp xếp sao cho ngày tháng theo thứ tự tăng dần, hãy đặt công thức sau vào ô C2:

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

Sao chép công thức này xuống cho tất cả dữ liệu của bạn và về cơ bản bạn đã hoàn thành. Bây giờ, nếu muốn, bạn có thể sử dụng tính năng lọc dựa trên cột C. Nếu bạn lọc để chỉ các hàng có chứa “EOM” được hiển thị, thì bạn có các giá trị cuối cùng của mình cho mỗi tháng. Nếu bạn lọc để chỉ các hàng chứa “X” được hiển thị, thì bạn có thể xóa các hàng đó, xóa bộ lọc và chỉ có các hàng có giá trị cuối tháng trong dữ liệu của mình.

Như thường lệ, có rất nhiều công thức mà bạn có thể sử dụng trong cột C thay vì công thức mà tôi đề xuất. Tuy nhiên, tôi đã đề xuất điều này bởi vì nó tạo ra một phép so sánh rất đơn giản mà sẽ luôn có thể kiểm tra được — liệu ngày đó có “giảm” giá trị trong hàng sau ngày hiện tại hay không. Trong mọi trường hợp có thể xảy ra, điều này sẽ chỉ đúng vào cuối tháng. Vì vậy, hàng đó được đánh dấu bằng “EOM” và phần còn lại bằng “X.”

Tôi nên chỉ ra rằng nếu bạn chọn sử dụng một công thức khác, hãy đảm bảo rằng đó là công thức không kiểm tra xem ngày trong cột A có phải là ngày cuối cùng của tháng hay không. Tại sao? Bởi vì có thể không — hãy nhớ rằng trong dữ liệu của David, cột A chứa ngày của các ngày giao dịch và rất có thể ngày giao dịch cuối cùng của tháng có thể không rơi vào ngày cuối cùng của tháng. (Nói cách khác, theo định nghĩa, các ngày cuối tuần và ngày lễ bị loại trừ khỏi dữ liệu của David.)

Nếu bạn đang sử dụng Office 365 (hoặc cái mà Microsoft, ngày nay, gọi là Microsoft 365), thì cũng có một cách bạn có thể chỉ lấy các ngày cuối tháng và giá trị của chúng. Giả sử rằng dữ liệu của bạn ở A2: B5000. (Hãy nhớ rằng A1: B1 chứa tiêu đề cột.) Đặt công thức sau vào ô E2:

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

Đó là nó; một công thức duy nhất trong một ô. Bạn có thể phải định dạng cột E để hiển thị ngày chính xác, nhưng cách sử dụng hàm FILTER này thực hiện cùng một phép so sánh đã được thảo luận và chỉ lấy các giá trị và ngày cuối tháng. (Điều này khá thú vị, nếu bạn nghĩ về nó.) Tuy nhiên, hãy nhớ rằng nó sẽ chỉ hoạt động trong Office 365; nó sẽ không hoạt động trong Excel 2019, Excel 2016 hoặc bất kỳ phiên bản nào trước đó của chương trình.

Nếu bạn thích cách tiếp cận dựa trên macro, thì macro ngắn sau sẽ thực hiện thủ thuật:

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

Macro xác định hàng cuối cùng trong trang tính (được lưu trữ trong biến LastRow) và sau đó sử dụng vòng lặp For …​ Next để chuyển qua các hàng trở lại. Nếu tháng của hàng hiện tại bằng tháng của hàng tiếp theo, thì hàng đó sẽ bị xóa. Lưu ý rằng macro giảm LastRow trước khi chuyển sang vòng lặp For …​ Next. Điều này được thực hiện bởi vì giả định là hàng dữ liệu cuối cùng sẽ luôn là ngày giao dịch cuối cùng của dữ liệu của bạn, do đó nó luôn ở cùng với dữ liệu đã rút gọn.

Macro này có thể chạy chậm, vì nó đang xóa hầu hết các hàng trong trang tính, từng hàng một. Tuy nhiên, khi hoàn tất, bạn sẽ chỉ còn lại dữ liệu cuối tháng của mình.

Một lưu ý cuối cùng — các phương pháp được sử dụng trong mẹo này, ngoại trừ chức năng FILTER, sẽ phá hủy dữ liệu của bạn. Khi bạn sử dụng chúng, dữ liệu trong trang tính của bạn sẽ bị mất vĩnh viễn. Điều này có nghĩa là bạn nên suy nghĩ hai lần (hoặc ba lần) trước khi chạy chúng trên bất kỳ thứ gì ngoại trừ bản sao dữ liệu gốc của bạn.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13768) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.