Thủ thuật tự động cập nhật STT khi thêm/xóa dòng trong Excel (Phiên bản nâng cao)

Chào mọi người,

Mình thấy nhiều anh em trên diễn đàn hay hỏi về cách tự động cập nhật số thứ tự (STT) khi thêm hoặc xóa dòng trong Excel. Trước đây mình cũng hay dùng các cách thủ công hoặc các công thức đơn giản. Tuy nhiên, hôm nay mình muốn chia sẻ một cách làm nâng cao hơn một chút, áp dụng cho các trường hợp phức tạp hơn, ví dụ như khi bạn lọc dữ liệu.

Cách phổ biến nhất mà mọi người hay dùng là:

=ROW()-ROW($A$1)

Hoặc với bảng có tiêu đề:

=ROW()-ROW($A$2)+1

Cách này hoạt động tốt khi bạn thêm hoặc xóa dòng ở giữa bảng. Tuy nhiên, khi bạn sử dụng bộ lọc (Filter) và ẩn bớt các dòng, công thức trên sẽ trả về STT bị nhảy, không liên tục.

Để khắc phục điều này, chúng ta có thể kết hợp hàm SUBTOTAL với OFFSET hoặc INDEX. Tuy nhiên, một cách đơn giản và hiệu quả hơn là sử dụng hàm AGGREGATE (từ Excel 2010 trở lên).

Công thức đề xuất:

=AGGREGATE(15, 6, 1/(ROW($A$2:$A$1000)-ROW($A$2)+1), SUBTOTAL(3, $A$2:$A$1000))

Trong đó:

• $A$2:$A$1000: Là vùng dữ liệu bạn muốn đánh STT (thay đổi cho phù hợp).
• 15: Mã hàm SUBTOTAL cho SMALL.
• 6: Bỏ qua các lỗi.
• 1/(ROW($A$2:$A$1000)-ROW($A$2)+1): Tạo ra một mảng các giá trị, khi lọc sẽ loại bỏ các dòng ẩn.
• SUBTOTAL(3, $A$2:$A$1000): Đếm số lượng các ô không trống trong vùng dữ liệu, tương ứng với STT cuối cùng cần hiển thị.

Cách này sẽ đảm bảo STT luôn liên tục và chính xác ngay cả khi bạn lọc dữ liệu. Rất hữu ích cho việc làm báo cáo động.

Anh em thử nghiệm và cho mình xin ý kiến nhé!

Cảm ơn bạn đã chia sẻ cách làm này, rất hữu ích cho anh em nào đang gặp vấn đề với STT khi thao tác trên Excel!

Với trường hợp lọc dữ liệu mà bạn đề cập, mình thấy công thức =SUBTOTAL(103, $A$2:A2) cũng là một lựa chọn hay. Nó có khả năng đếm các dòng hiển thị sau khi lọc, giúp STT luôn chính xác. Tuy nhiên, mình vẫn tò mò muốn biết "cách làm nâng cao hơn" mà bạn đang nói tới là gì. Liệu nó có giải quyết được những trường hợp nào mà cả hai công thức trên đều "bó tay" không? Rất mong bạn chia sẻ thêm chi tiết!

Hay quá bạn ơi! Mình cũng hay gặp tình huống này, đặc biệt là khi làm việc với bảng dữ liệu lớn và có lọc. Công thức =ROW()-ROW($A$1) đúng là thần thánh cho các trường hợp đơn giản.

Cho mình hỏi thêm chút, với trường hợp lọc dữ liệu mà bạn nói, liệu công thức =SUBTOTAL(103, $A$2:A2) có hoạt động hiệu quả không? Mình thấy nó cũng hay được dùng để đếm các dòng hiển thị khi lọc.