Tự động hóa điền STT khi lọc dữ liệu Excel theo điều kiện phức tạp

Chào mọi người,

Dạo này mình làm báo cáo nhiều, mà mỗi lần lọc dữ liệu theo nhiều điều kiện là cái cột STT nó nhảy lung tung beng. Dùng SUBTOTAL thì chỉ được khi lọc đơn giản, còn lọc theo kiểu phức tạp hơn thì bó tay. Mình loay hoay mãi mới tìm ra một cách kết hợp AGGREGATE và ROW để giải quyết triệt để vấn đề này.

Cách này hay ở chỗ nó có thể xử lý được cả những trường hợp lọc dữ liệu theo nhiều điều kiện lồng nhau, mà vẫn đảm bảo STT nhảy đúng và liên tục.

Công thức mình dùng đây, các bạn tham khảo nhé:

=AGGREGATE(15, 6, ROW($A$2:$A$100)/($B$2:$B$100=E1), ROW(A1))

Trong đó:

• $A$2:$A$100: Cột chứa STT ban đầu (nếu có) hoặc cột trống bạn muốn điền STT.
• $B$2:$B$100: Cột bạn dùng để lọc dữ liệu (ví dụ: cột Tên phòng ban, cột Ngày tháng...).
• E1: Ô chứa điều kiện lọc (ví dụ: Tên phòng ban bạn muốn xem).
• ROW($A$2:$A$100): Tạo ra một mảng các số thứ tự của các dòng.
• $B$2:$B$100=E1: Điều kiện lọc, chỉ lấy những dòng thỏa mãn điều kiện.
• AGGREGATE(15, 6, ..., ...): Hàm AGGREGATE với tùy chọn 15 (hàm SMALL) và 6 (bỏ qua lỗi) sẽ lấy ra số thứ tự nhỏ nhất từ mảng kết quả sau khi lọc.
• ROW(A1): Dùng để đếm xem đây là STT thứ mấy cần lấy (1, 2, 3...). Kéo công thức xuống dưới, ROW(A1) sẽ lần lượt thành ROW(A2), ROW(A3)...

Công thức này hơi phức tạp một chút nhưng nó cực kỳ hiệu quả và tự động hóa hoàn toàn việc điền STT khi lọc. Rất phù hợp cho các báo cáo động.

Các bạn có cao kiến hay cách nào khác hay hơn thì chia sẻ cho mình với nhé!

Hay quá bạn ơi! Mình cũng từng đau đầu vụ STT nhảy lung tung khi lọc phức tạp. Công thức AGGREGATE kết hợp ROW này nghe có vẻ rất hứa hẹn đấy.

Cho mình hỏi thêm một chút, nếu trong dữ liệu có những dòng trống hoặc bị trùng lặp thì công thức này có xử lý được không ạ?

Công thức AGGREGATE kết hợp ROW đúng là "cứu cánh" cho vụ STT nhảy lung tung khi lọc dữ liệu phức tạp thật!

Cảm ơn bạn đã chia sẻ cách làm rất hay này. Mình cũng đã thử áp dụng và thấy nó xử lý mượt mà các trường hợp lọc nhiều điều kiện mà SUBTOTAL không làm được.

Rất mong bạn chia sẻ thêm công thức cụ thể hoặc video hướng dẫn chi tiết hơn để mọi người cùng học hỏi ạ!