Tự động điền STT theo nhóm với công thức SUBTOTAL và IF - Hướng dẫn chi tiết

Chào mọi người,

Mình có một file Excel với dữ liệu được nhóm theo nhiều tiêu chí khác nhau. Mình đang muốn tự động đánh số thứ tự (STT) cho từng nhóm này, ví dụ như 1, 2, 3... cho nhóm A, rồi lại bắt đầu lại 1, 2, 3... cho nhóm B. Sau khi tìm hiểu và thử nghiệm, mình thấy công thức kết hợp SUBTOTAL và IF khá hiệu quả cho việc này.

Cách hoạt động:

• Hàm SUBTOTAL với đối số 102 (tương đương COUNTA nhưng bỏ qua các dòng ẩn) sẽ đếm số lượng các ô không trống trong một phạm vi.
• Khi kết hợp với IF, chúng ta có thể kiểm tra xem dòng hiện tại có phải là dòng đầu tiên của một nhóm mới hay không.

Công thức ví dụ:

Giả sử cột bạn muốn nhóm là cột A (từ A2 trở xuống) và bạn muốn điền STT vào cột B (từ B2 trở xuống). Công thức tại B2 sẽ là:

=IF(A2=A1, B1+1, 1)

Tuy nhiên, công thức này chỉ hoạt động khi dữ liệu được sắp xếp liên tục. Để xử lý trường hợp dữ liệu có thể bị ẩn hoặc lọc, chúng ta sẽ dùng SUBTOTAL:

=IF(SUBTOTAL(103, A2)=1, 1, IF(A2=A1, B1+1, 1))

Trong đó:

• SUBTOTAL(103, A2): Đếm số lượng ô không trống trong A2 (103 tương đương COUNT). Nếu đây là dòng đầu tiên hiển thị của nhóm, giá trị này sẽ là 1.
• IF(SUBTOTAL(103, A2)=1, 1, ...): Nếu là dòng đầu tiên của nhóm, STT bắt đầu bằng 1.
• IF(A2=A1, B1+1, 1): Phần này vẫn dùng để kiểm tra nếu dòng hiện tại giống dòng trước thì tăng STT lên 1, nếu không thì reset về 1.

Lưu ý:

• Bạn cần điều chỉnh phạm vi A2 và A1 cho phù hợp với dữ liệu thực tế của bạn.
• Nếu cột dùng để nhóm có thể chứa ô trống, bạn cần cân nhắc điều chỉnh công thức để tránh lỗi.

Hy vọng chia sẻ này hữu ích cho mọi người. Có ai có cách nào khác hay hơn không, cùng thảo luận nhé!

Hay quá bạn ơi! Công thức SUBTOTAL kết hợp IF này đúng là "chân ái" cho bài toán đánh STT theo nhóm khi dữ liệu có lọc.

Mình cũng từng dùng cách này và thấy nó rất linh hoạt. Đặc biệt là khi mình muốn đánh STT riêng cho từng vùng dữ liệu sau khi đã group (mở rộng/thu gọn nhóm). Không biết bạn đã thử áp dụng với trường hợp group này chưa?

Cách làm này quá hay và hữu ích cho những ai hay làm việc với dữ liệu có lọc hoặc nhóm. Mình thấy công thức SUBTOTAL với đối số 102 (COUNTA) rất thông minh vì nó tự động bỏ qua các dòng ẩn, giúp STT luôn chính xác dù có lọc hay không.

Cảm ơn bạn đã chia sẻ chi tiết nhé! Mình cũng hay dùng cách này và thấy nó ổn định hơn nhiều so với việc đánh STT thủ công hoặc dùng các cách cũ.