Chào các anh chị em, dạo này mình hay phải làm việc với dữ liệu lớn và thường xuyên phải lọc để xem. Cứ mỗi lần lọc xong là cái STT nó nhảy loạn xạ, nhìn khó chịu cực kỳ. Mình tìm hiểu và áp dụng được một vài cách để tự động điền STT mà không bị ảnh hưởng khi lọc, chia sẻ lại cho mọi người tham khảo.
Cách này mình thấy khá đơn giản và hiệu quả, không cần dùng đến VBA phức tạp.
Cách 1: Sử dụng hàm SUBTOTAL
Hàm SUBTOTAL là một hàm rất mạnh mẽ trong Excel, nó có thể tính toán dựa trên các dữ liệu hiển thị (sau khi lọc). Chúng ta sẽ kết hợp nó với MATCH để lấy số thứ tự.
Giả sử STT của bạn bắt đầu từ ô A2, và dữ liệu bắt đầu từ B2. Công thức bạn có thể áp dụng cho ô A2 là:
=IF(SUBTOTAL(3,$B2)=0,0,SUBTOTAL(3,$B2))Giải thích công thức:
SUBTOTAL(3, $B2): HàmSUBTOTALvới đối số đầu tiên là 3 sẽ đếm các ô không trống trong phạm vi$B2. Khi bạn lọc dữ liệu, nó chỉ đếm các ô hiển thị. Việc dùng$B2để cố định cột B nhưng cho phép dòng thay đổi là rất quan trọng.IF(..., 0, ...): Phần này để xử lý trường hợp nếu dòng đó bị ẩn đi (không có dữ liệu hiển thị), thì STT sẽ là 0 hoặc để trống tùy bạn muốn. Ở đây mình để là 0.
Sau khi nhập công thức cho ô A2, bạn chỉ cần kéo công thức xuống cho các dòng còn lại.
Cách 2: Sử dụng hàm AGGREGATE (mạnh hơn SUBTOTAL)
Hàm AGGREGATE cũng tương tự SUBTOTAL nhưng linh hoạt hơn, nó có thể bỏ qua các dòng bị ẩn hoặc báo lỗi.
Công thức tương tự cho ô A2:
=IF(AGGREGATE(3,5,$B2)=0,0,AGGREGATE(3,5,$B2))Giải thích:
AGGREGATE(3, 5, $B2): Số 3 tương tựSUBTOTALlà đếm các ô không trống. Số 5 là tùy chọn để bỏ qua các dòng bị ẩn.
Cả hai cách này đều giúp bạn có một dãy STT chuẩn xác sau khi lọc dữ liệu. Hy vọng mẹo nhỏ này giúp ích cho công việc của mọi người!